cf-platform/Application/Payment/View/ExcelPayment/add.html

<extend name="Public/base" />

<block name="body">
    <link rel="stylesheet" type="text/css" href="__CSS__/admin_table.css" media="all">
    <link href="__STATIC__/datetimepicker/css/datetimepicker.css" rel="stylesheet" type="text/css">
    <php>if(C('COLOR_STYLE')=='blue_color') echo '
        <link href="__STATIC__/datetimepicker/css/datetimepicker_blue.css" rel="stylesheet" type="text/css">';</php>
    <link href="__STATIC__/datetimepicker/css/dropdown.css" rel="stylesheet" type="text/css">
    <style>
        .tabcon1711 td:first-child {
            width: auto;

        }
    </style>

    <div class="cf main-place top_nav_list navtab_list">
        <h3 class="page_title">新增excel导入</h3>
        <p class="description_text">说明：导入预付款数据</p>
    </div>



    <!-- 标签页导航 -->
    <div class="tab-wrap" style="display: flex;">

        <div class="tab-content tabcon1711" style="min-width:700px;">
            <!-- 基础文档模型 -->
            <div id="tab1" class="tab-pane in tab1">
                <table border="0" cellspacing="0" cellpadding="0">
                    <tbody>
                        <tr>
                            <td class="l">本次批次号：</td>
                            <td class="r">
                                <div class="input-list" id="batch" style="margin-left: 0;line-height: 30px;font-size: 16px;">
                                </div>
                                <span class="notice-text" style="margin-left: 20px;">用于检索使用</span>
                            </td>
                        </tr>
                        <tr>
                            <td class="l"><i class="mustmark" style="margin-left:-7px">*</i>文件上传:</td>
                            <td class="r">
                                <div class="input-list" style="margin-left: 0;">
                                    <input type="file" id="fileinput" name="file">
                                </div>
                                <span class="notice-text" style="margin-left: 0;">暂只支持.xls .xlsx 格式文件</span>
                            </td>
                        </tr>
                        
                    </tbody>
                </table>
            </div>
            <div class="form-item cf">
                <button class="submit_btn" id="submit_btn">
                    保存
                </button>
                <a class="submit_btn " alt="下载模板" title="下载模板" href="{:U('downloadTemplate')}">
                    下载模板
                </a>
                <a class="submit_btn " alt="返回上一页" title="返回上一页" href="javascript:window.history.back();">
                    返回
                </a>
            </div>
        </div>

           
    </div>
    <div style="margin-top: 60px;" id="firle">
        <div style="width: 100%;height: 45px;font-size: 16px;">
            文件格式参考:<span style="color: #777;font-size: 12px;margin-left: 20px;">请严格按此格式，文件的第一行必须是字段名,且字段名不允许修改</span>
        </div>
        <style>
            #firle td,#firle th{
                padding: 10px 5px;
            }
        </style>
        <table id="cktable" style="width: 100%;margin: 0;padding: 0;text-align: center;border: 1px solid #777;" border="1px">
            <thead>
                <tr>
                    <th>序号</th>
                    <th>下游名称</th>
<!--                    <th>会长账号</th>-->
<!--                    <th>下游类型</th>-->
<!--                    <th>市场员</th>-->
<!--                    <th>下游性质</th>-->
<!--                    <th>产品</th>-->
<!--                    <th>产品类型</th>-->
                    <th>结算时间</th>
<!--                    <th>推广流水</th>-->
<!--                    <th>分成比例</th>-->
<!--                    <th>补点</th>-->
<!--                    <th>奖励</th>-->
<!--                    <th>罚款</th>-->
                    <th>结算金额</th>
                    <th>支付宝真实姓名</th>
                    <th>支付宝账号</th>
                    <th>备注</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>1</td>
                    <td>橙枫科技</td>
<!--                    <td>leilihua</td>-->
<!--                    <td>外团开发</td>-->
<!--                    <td>小明</td>-->
<!--                    <td>个人</td>-->
<!--                    <td>秦汉风云</td>-->
<!--                    <td>策略</td>-->
                    <td>2020.6.22-2020.6.28	5626</td>
<!--                    <td>5626</td>-->
<!--                    <td>1%</td>-->
<!--                    <td>0%</td>-->
<!--                    <td>0</td>-->
<!--                    <td>0</td>-->
                    <td>56.26</td>
                    <td>小花</td>
                    <td>12345678910</td>
                    <td>不重要的备注</td>
                </tr>
            </tbody>
        </table>
    </div>
    <div style="width: 100vw;height: 100vh;padding: 0;position: absolute;top: 0;z-index: 9999;display: none;" id="creat_msg">
        <div class="layui-layer layui-layer-dialog layui-layer-border layui-layer-msg layui-layer-hui" 
            type="dialog" times="1" showtime="50000" contype="string" style="z-index: 19891015; top:30%; left:30%;">
            <div class="layui-layer-content" style="padding: 15px 0 5px;font-size: 20px;font-weight: 600;">执行进度</div>
            <div id="tip_msg" class="layui-layer-content">开始时间与结束时间都不允许为空</div>
            <div class="layui-layer-content" style="padding: 5px 10px 15px;font-size: 10px;color: #BBB;">TIP:未执行完成之前请勿刷新或关闭此页面</div>
            <span class="layui-layer-setwin"></span>
        </div>
    </div>

</block>

<block name="script">
    <script type="text/javascript" src="__STATIC__/xlsx.core.min.js" charset="UTF-8"></script>
    <script type="text/javascript" src="__STATIC__/datetimepicker/js/bootstrap-datetimepicker.min.js"></script>
    <script type="text/javascript" src="__STATIC__/datetimepicker/js/locales/bootstrap-datetimepicker.zh-CN.js"
        charset="UTF-8"></script>
    <script type="text/javascript" src="__STATIC__/layer3/layer.js"></script>
    <script type="text/javascript">
        //导航高亮
        highlight_subnav("{:U('lists')}");
        var MSG={
            showmsg:function(str){
                $("#creat_msg").show();
                $("#tip_msg").html(str);
            },
            hidemsg:function(){
                $("#creat_msg").hide();
                $("#tip_msg").html('');
            }
        }
        var COMPARE={
            publiucAjax:function(url,senddata,callback){
                $.ajax({
                    type: 'post',
                    url: url,
                    data:senddata,
                    success: function(data) {
                        if(data.status == 1){
                            callback();
                        }else{
                            MSG.hidemsg();
                            layer.alert(data.msg);
                            return false;
                        }
                        
                    },
                    error:function(){
                        MSG.hidemsg();
                        layer.alert("网络错误或超时");
                        return false;
                    }
                });
            },
            //循环比较
            loopCheck:function(page,callback){
                if(page > sendDefaultData.check_page){
                    MSG.showmsg("添加成功,执行跳转中");
                    callback();
                    return;
                }
                var senddata = {
                    statement_begin_time:sendDefaultData.begin_time,
                    statement_end_time:sendDefaultData.end_time,
                    batch:sendDefaultData.batch,
                    checkarr:sendDefaultData.filedata.splice(0,check_size)
                }
                COMPARE.publiucAjax("{:U('loopAdd')}",senddata,function(data){
                    var checkok = page*check_size > sendDefaultData.filedata_count ? sendDefaultData.filedata_count : page*check_size;
                    MSG.showmsg("数据插入 ["+checkok+"/"+sendDefaultData.filedata_count+"] ......");
                    page++;
                    COMPARE.loopCheck(page,callback)
                });
            },
            checkData:function(callback){
                MSG.showmsg("数据校验中...");
                var d = sendDefaultData.filedata;
                for (const key in d) {
                    var c = d[key].company_info;
                    if(!c.hasOwnProperty('ali_user') || c.ali_user == '' || !c.hasOwnProperty('ali_account') || c.ali_account == ''){
                        MSG.hidemsg();
                        layer.msg('支付宝真实姓名及登陆账号不允许为空');
                        return false;
                    }
                }
                callback();
            }
        }
    </script>
    <script type="text/javascript">
        var isfile = false;
        var rABS = false; //是否将文件读取为二进制字符串
        var check_size = 10;//每次验证的数量
        var  batch = '{$batch}';
        var sendDefaultData = {
            "filedata_count": 0,
            "check_page":0,
            "filedata":[],
            "pay_way":-1,
            "batch":batch
        }
        $("#batch").html(batch);
        

        $(function () {


            $("#submit_btn").on("click", function () {
                //获取文件
                if (!isfile) {
                    layer.msg('excel文件不能为空');
                    return false;
                }
                //执行
                readFileInput(function () {
                    if(sendDefaultData.filedata_count == 0){
                        MSG.hidemsg();
                        layer.msg('excel内容不能为空');
                        return false;
                    }
                    //获取需要循环的次数
                    sendDefaultData.check_page = Math.ceil(sendDefaultData.filedata_count/check_size);
                    COMPARE.checkData(function(){
                        COMPARE.loopCheck(1,function(){
                            setTimeout(function(){
                                window.location.href="{:U('lists',['row'=>999999,'page'=>1,'batch_num'=>$batch])}";
                            },1500);
                        })
                    });
                    

                })
            })


            $("#fileinput").change(function () {
                var p = $(this).val();
                var ext = p.substr(p.lastIndexOf(".")).toLowerCase();
                if (ext != ".xls" && ext != ".xlsx") {
                    layer.msg("文件仅支持excel文件格式");
                    $(this).val('');
                    p = '';
                }

                if (p != '') {
                    isfile = true;
                } else {
                    isfile = false;
                }
            })
        });
        function readFileInput(callback) {
            MSG.showmsg("读取excle中。。。");
            var wb;//读取完成的数据
            var f = $("#fileinput")[0].files[0];
            var reader = new FileReader();
            if (rABS) {
                reader.readAsArrayBuffer(f);
            } else {
                reader.readAsBinaryString(f);
            }
            reader.onload = function (e) {
                MSG.showmsg("文件读取完成，添加命令初始化。。。");
                var data = e.target.result;
                if (rABS) {
                    wb = XLSX.read(btoa(fixdata(data)), {
                        type: 'base64'
                    });
                } else {
                    wb = XLSX.read(data, {
                        type: 'binary'
                    });
                }
                // console.log(wb);
                try {
                    var filedata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
                    sendDefaultData.filedata = resetCount(filedata);
                    sendDefaultData.filedata_count = sendDefaultData.filedata.length;
                    callback();
                } catch (error) {
                    MSG.hidemsg();
                    isfile = false;
                    layer.msg('excel内容不符或不能为空');
                    return false;
                }
                
                // document.getElementById("demo").innerHTML= JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) );
            };
        }
        
        function resetCount(fdata){
            var titleKey={
                "序号": "sort",
                "下游名称": "company_name",
                // "会长账号": "account",
                // "下游类型": "company_relation_str",
                // "市场员": "nickname",
                // "下游性质": "company_type_str",
                // "产品": "game_name",
                // "产品类型": "game_type_name",
                "结算时间": "statement_time",
                // "推广流水": "pay_amount",
                // "分成比例": "ratio",
                // "补点": "increment_ratio",
                // "奖励": "reward",
                // "罚款": "fine",
                "结算金额":"sum_money",
                "账户名": "payee_name",
                "银行卡号": "bank_account",
                "开户支行": "opening_bank",
                "账户类型": "bank_type",
                "支付宝真实姓名": "ali_user",
                "支付宝账号": "ali_account",
                "备注": "remark",
            }
            var data = [];
            //临时公司
            var temp ={
                statement_money:0,
                platform_amount:0,
                fine:0,
                reward:0,
                statement_info:[],
                company_info:{}
            };
            var game_name = '';
            var game_type= '';
            for (let index = 0; index < fdata.length; index++) {
                const e = fdata[index];
                //全量公司
                if (e.hasOwnProperty("序号")) {
                    if(index != 0){
                        temp.statement_money = temp.statement_money/100;
                        temp.platform_amount = temp.platform_amount/100;
                        temp.fine = temp.fine/100;
                        temp.reward = temp.reward/100;
                        data.push(temp);//上次的走掉
                        temp ={
                            statement_money:0,
                            platform_amount:0,
                            fine:0,
                            reward:0,
                            statement_info:[],
                            company_info:{}
                        };//重置
                    }
                    var acc = {
                        game_list:[]
                    };
                    var gamet = {}
                    for (const k in e) {
                        if(k == "会长账号"){
                            acc[titleKey[k]] = e[k];
                        }else if(k=="产品" || k=="产品类型" || k == "结算时间" || k == "推广流水" || k == "分成比例" || k == "补点" || k == "奖励" || k == "罚款" || k == "结算金额"){
                            if(k=="产品") game_name = e[k];
                            if(k=="产品类型") game_type = e[k];
                            
                            if(k=="奖励") temp.reward += Math.round(e[k]*100);
                            if(k=="罚款") temp.fine += Math.round(e[k]*100);
                            if(k=="推广流水") temp.platform_amount +=Math.round(e[k]*100);
                            if(k=="结算金额") temp.statement_money += Math.round(e[k]*100);
                            if(k == "推广流水"  || k == "奖励" || k == "罚款" || k == "结算金额"){
                                e[k] = Math.round(e[k]*100)/100;
                            }
                            gamet[titleKey[k]] = e[k];
                        }else if(k=="下游类型" ||k=="市场员" || k=="下游性质" || k == "账户名" || k == "银行卡号" || k == "开户支行" || k == "账户类型" || k == "支付宝真实姓名" || k == "支付宝账号"){
                            temp.company_info[titleKey[k]] = e[k];
                        }else{
                            temp[titleKey[k]] = e[k];
                        }
                    }
                    if(!gamet.hasOwnProperty("game_name")){
                     gamet.game_name =game_name;
                    }
                    if(!gamet.hasOwnProperty("game_type_name")){
                        gamet.game_type_name =game_type;
                    }
                    acc.game_list.push(gamet);
                    temp.statement_info.push(acc);
                    
                    continue;
                }
                //含会长信息
                if (e.hasOwnProperty("会长账号")) {
                    
                    var acc = {
                        game_list:[]
                    };
                    var gamet = {}
                    for (const k in e) {
                        if(k == "会长账号"){
                            acc[titleKey[k]] = e[k];
                        }else if(k=="产品" || k=="产品类型" || k == "结算时间" || k == "推广流水" || k == "分成比例" || k == "补点" || k == "奖励" || k == "罚款" || k == "结算金额"){
                            if(k=="产品") game_name = e[k];
                            if(k=="产品类型") game_type = e[k];
                            if(k=="奖励") temp.reward += Math.round(e[k]*100);
                            if(k=="罚款") temp.fine += Math.round(e[k]*100);
                            if(k=="推广流水") temp.platform_amount += Math.round(e[k]*100);
                            if(k=="结算金额") temp.statement_money += Math.round(e[k]*100);
                            if(k == "推广流水"  || k == "奖励" || k == "罚款" || k == "结算金额"){
                                e[k] = Math.round(e[k]*100)/100;
                            }
                            gamet[titleKey[k]] = e[k];
                        }else if(k=="下游类型" || k=="市场员" || k=="下游性质" || k == "账户名" || k == "银行卡号" || k == "开户支行" || k == "账户类型" || k == "支付宝真实姓名" || k == "支付宝账号"){
                            temp.company_info[titleKey[k]] = e[k];
                        }else{
                            temp[titleKey[k]] = e[k];
                        }
                    }
                    
                    if(!gamet.hasOwnProperty("game_name")){
                        gamet.game_name =game_name;
                    }
                    if(!gamet.hasOwnProperty("game_type_name")){
                        gamet.game_type_name =game_type;
                    }
                    acc.game_list.push(gamet);
                    temp.statement_info.push(acc);
                    continue;
                }
                //只含有游戏的信息
                var gamet = {}
                for (const k in e) {
                    if(k == "会长账号"){
                        acc[titleKey[k]] = e[k];
                    }else if(k=="产品" || k=="产品类型" || k == "结算时间" || k == "推广流水" || k == "分成比例" || k == "补点" || k == "奖励" || k == "罚款" || k == "结算金额"){
                        if(k=="产品") game_name = e[k];
                        if(k=="产品类型") game_type = e[k];
                        if(k=="奖励") temp.reward += Math.round(e[k]*100);
                        if(k=="罚款") temp.fine += Math.round(e[k]*100);
                        if(k=="推广流水") temp.platform_amount +=Math.round(e[k]*100);
                        if(k=="结算金额") temp.statement_money += Math.round(e[k]*100);
                        if(k == "推广流水"  || k == "奖励" || k == "罚款" || k == "结算金额"){
                            e[k] = Math.round(e[k]*100)/100;
                        }
                        gamet[titleKey[k]] = e[k];
                    }else if( k=="下游类型" || k=="市场员" || k=="下游性质" || k == "账户名" || k == "银行卡号" || k == "开户支行" || k == "账户类型" || k == "支付宝真实姓名" || k == "支付宝账号"){
                        temp.company_info[titleKey[k]] = e[k];
                    }else{
                        temp[titleKey[k]] = e[k];
                    }
                }
                if(!gamet.hasOwnProperty("game_name")){
                    gamet.game_name =game_name;
                }
                if(!gamet.hasOwnProperty("game_type_name")){
                    gamet.game_type_name =game_type;
                }

                temp.statement_info[temp.statement_info.length-1].game_list.push(gamet);
            }
            //判断
            temp.statement_money = temp.statement_money/100;
            temp.platform_amount = temp.platform_amount/100;
            temp.fine = temp.fine/100;
            temp.reward = temp.reward/100;
            data.push(temp);//最后一次
            return data;
        }
        
        function getNowDate(){
            var da = new Date();
            var trade =da.getFullYear();
            var mt =da.getMonth() + 1 >10 ? da.getMonth() + 1 : "0"+(da.getMonth() + 1);
            var dt =da.getDate()  >=10 ? da.getDate()  : "0"+da.getDate();
            var dh =da.getHours()  >=10 ? da.getHours()  : "0"+da.getHours();
            var dm =da.getMinutes()  >=10 ? da.getMinutes()  : "0"+da.getMinutes();
            var ds =da.getSeconds()  >=10 ? da.getSeconds()  : "0"+da.getSeconds();
            var dms =(Array(3).join(0) + da.getMilliseconds()).slice(-3);
            return  trade+=""+mt+""+dt+""+dh+""+dm+""+ds+''+dms;
        }

        function fixdata(data) { //文件流转BinaryString
            var o = "",
                l = 0,
                w = 10240;
            for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
            o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
            return o;
        }

        $('#time_start').datetimepicker({
            format: 'yyyy-mm-dd',
            language: "zh-CN",
            minView: 2,
            autoclose: true
        });

        $('#datetimepicker').datetimepicker({
            format: 'yyyy-mm-dd',
            language: "zh-CN",
            minView: 2,
            autoclose: true,
            pickerPosition: 'bottom-left'
        })

    </script>
</block>