<?php

use EasyWeChat\Factory;
use EasyWeChat\Kernel\Exceptions\InvalidConfigException;
use GuzzleHttp\Exception\GuzzleException;

class CrmContract extends CrmBase
{   
    /**
     * @method 上传合同
     * @author zhengweihua 
     * @copyright 2024-01-26
     */
    public function uploadCont($dataArr,$conId=0)
    {

        if(!is_array($dataArr)) return LibFc::ReturnData(false,'参数错误');

        $filekey = $dataArr['name'];
        $insertData = $dataArr;
        unset($insertData['name']);
        if($_FILES[$filekey]){
            $res  = $this->upload($_FILES[$filekey],$conId);
            $resData = $res['data'];
            $othData = array(
				'fcf_ext'=>$resData['ext'],
				'fcf_size'=>$resData['size'],
				'fcf_atime'=>time(),
				'fcf_path'=>$resData['path'],
				'fcf_name'=>$resData['name']
			);
			$insertData = array_merge($insertData, $othData);
        }
        $this->CRMDb->Begin();
        if($conId>0){
            $insertData['fcf_fct_id'] = $conId;
            $ress =  $this->add('fad_contract_file',$insertData);
        }else{
            $ress =  $this->add('fad_contract_tpl',$insertData);
        }  
        $this->CRMDb->End();

        return LibFc::ReturnData(true,$ress['data']);
    }

    public function delCon($idArr,$condition = '',$type=1)
    {   
        $where = $condition;
        if($idArr){
            $ids = implode(',',$idArr);
            $where =  sprintf(" and fcf_id in (%s)",$ids);
        }
        $table = $type==1 ? 'fad_contract_tpl' : 'fad_contract_file';  
        $this->CRMDb->Begin();
        $res = $this->get($table,['*'],$where);
        $delFile = $res['data'];
        

        if (!empty($delFile)) {
            $this->del($table,$where);

            $cfg =  mvc::$cfg['FILE_CENTER']['yp'];
            $data_arr = array(
                'token' =>$cfg['token'],
                'cate' => 'crm',
                'files' => array()
            );
            foreach ($delFile as $v) {
                $data_arr['files'][] = $v['fcf_path'] . $v['fcf_name'] . '.' . $v['fcf_ext'];
            }
            $LibHttp = new LibHttp(false, false);
            $res = $LibHttp->delete_file($cfg['url_del'], $data_arr);
        }
        $this->CRMDb->End();
        return LibFc::ReturnData(true, 'success');
    }


    public function upload($fileArr,$conId = 0)
    {
        $cfg = mvc::$cfg['FILE_CENTER']['yp'];
        $data_arr = array(
            'token'=>$cfg['token'],
            'file'=>$fileArr,
            'cate'=>'crm',
            'path'=>'ConTpl/'
        );
        if($conId>0){
            $res = $this->get('fad_contract',['fct_ctr_id'],sprintf(" and fct_id=%d",$conId),true); 
            $ctrId = (int) $res['data']['fct_ctr_id'];
			$data_arr['path'] = 'CtrCon/'.$ctrId.'/'.$conId.'/';
        }
        $LibHttp = new LibHttp(false, false);     
        $res = $LibHttp->upload($cfg['url_put'], $data_arr);
        if(!$res['status'])
        {
            return LibFc::ReturnData(false, $res['data'].'获取数据失败，请重新上传。');
        }
        $resData = $res['data'];
        return LibFc::ReturnData(true,$resData);
    }

    function getAddrByIds($ids)
    {
        $IdArr = [];
        foreach(explode(',', $ids) as $V)
        {
        if(LibFc::Int($V) && $V > 0)
        {
            $IdArr[$V] = '';
        }
        }
        $ids=implode(',',array_filter(explode(',', $ids)));
        if(count( $IdArr) > 0)
        {
            $data=array();
            $PjlBase = new PjlBase();
            $result=$PjlBase->get('yp_province_city',['pc_id','pc_name'], sprintf(' and pc_id in(%s)',$ids));
            foreach($result['data'] as $k => $v)
            {
                $IdArr[$v['pc_id']]=$v['pc_name'];
            }
        }
        return LibFc::ReturnData(true, $IdArr);
    }


    public function bindAds($pla_id,$fsl_id)
    {
        if(!$fsl_id||!$pla_id) return LibFc::ReturnData(false,'参数错误');

        $res = $this->get('fad_link_fct_sve',['fsl_fct_id'],sprintf(" AND fsl_id=%d",$fsl_id),true);
        $con_id = $res['data']['fsl_fct_id'];

        $AdsBase = new AdsBase();
        $res = $AdsBase->get('pj_place_ads_time',['*'], sprintf(" and pat_pla_id=%d", $pla_id));
        $min = [];
        $max = [];
        foreach ($res['data'] as $val) 
        {
            $min[] = $val['pat_btime'];
            $max[] = $val['pat_etime'];
        }
        $this->CRMDb->Begin();
        $AdsBase->set('pj_place_ads',['pla_con_id'=>$con_id,'pla_ser_link_id'=>$fsl_id], sprintf(" and pla_id=%d", $pla_id));
        $res = $this->set('fad_link_fct_sve',['fsl_des_id'=>$pla_id,'fsl_btime'=>min($min),'fsl_etime'=>max($max)],sprintf(" AND fsl_id =%d",$fsl_id));
        $this->CRMDb->End();
        return LibFc::ReturnData(true);
    }

    public function getUserList()
    {   
        $work = Factory::work(array_merge(mvc::$cfg['chat'], [
            'agent_id' => 3010040,
            'secret' => '9MwxW6yneUvpyDA1huQDJ54ptUV3qKhPmNC3L16P6FI',
        ]));
        $params = [
            'limit'=>200
        ];
        try {
            $data = $work->oa->httpPostJson('cgi-bin/user/list_id',$params);
        } catch (InvalidConfigException|GuzzleException $e) {}
        var_dump($data);die;
    }
    public function getApproval($start, $end,$departUserId=null)
    {
        // 获取企业微信的外出审批数据
        $work = Factory::work(array_merge(mvc::$cfg['chat'], [
            'agent_id' => 3010040,
            'secret' => '9MwxW6yneUvpyDA1huQDJ54ptUV3qKhPmNC3L16P6FI',
        ]));
       
        $flag = 1;
        $nextCursor='';
        $arr = [];
        while ($flag) {
            $params = [
                'starttime' => $start,
                'endtime' => $end,
                'new_cursor' => $nextCursor,
                'size' => 100,
                'filters'=>[
                    [
                        "key" =>"sp_status",
                        "value"=>"2"
                    ],
                    [
                        "key"=>"department",
                        "value"=>3
                    ],
                    [
                        "key"=>"department",
                        "value"=>5
                    ]
                ]
            ];
            if($departUserId){
                $params['filters'][] = [
                    "key"=>"creator",
                    "value"=>$departUserId
                ];
            }
            try {
                $data = $work->oa->httpPostJson('cgi-bin/oa/getapprovalinfo',$params);
            } catch (InvalidConfigException|GuzzleException $e) {}
         
            if($data['sp_no_list']){
                $arr = array_merge($arr,$data['sp_no_list']);
                $nextCursor = $data['new_next_cursor']??'';
                if(!$nextCursor){$flag=0;}
            }else{
                $flag = 0;
                $nextCursor = ''; 
            }
        }

        return $arr;
    }

    public function approvalDetail(int $number)
    {
        $params = [
            'sp_no' => $number,
        ];
        $work = Factory::work(array_merge(mvc::$cfg['chat'], [
            'agent_id' => 3010040,
            'secret' => '9MwxW6yneUvpyDA1huQDJ54ptUV3qKhPmNC3L16P6FI',
        ]));

        return $work->oa->httpPostJson('cgi-bin/oa/getapprovaldetail', $params);
    }

    /**
     * @param  int $start 开始时间
     * @param  int $end 结束时间
     * @param  string $auIds 用户字符串"1,2,3"
     * @method 导出跟进统计数据
     * @author zhengweihua 
     * @copyright 2024-04-28
     */
    public function exportFollow($start,$end,$auIds)
    {
        if(!$start||!$end||!$auIds) return LibFc::ReturnData(false,'参数错误');
        $B2cBase = new B2cBase();
        $res = $B2cBase->get('an_user',['au_uid,au_name'],sprintf(" AND au_uid IN (%s)",$auIds));
        $unameArr = array_column($res['data'],'au_name','au_uid');
        //获取跑街Crm的跟进记录
        $where = sprintf(" AND cfo_pass=1 AND cfo_check=1 AND cfo_atime>=%d AND cfo_atime<=%d",$start,$end);
        $where .= sprintf(" AND cfo_au_uid IN (%s)",$auIds);
        //同一个客户同一天多条有效记录只计算一条
        $res = $this->get('crm_follow',["count(cfo_id) as counts,cfo_au_uid,cfo_au_name,FROM_UNIXTIME(cfo_atime, '%m月%d日') as adate"],$where." group by cfo_au_uid,cfo_ctr_id,adate");
        if($res['data'])
        {   
            $pjdArr = [];
            foreach ($res['data'] as $val) 
            {   
                if($val['counts']>0){
                    $pjdArr[$val['adate']][$val['cfo_au_name']]++;
                }
                
            }

            //宜配Crm的有效跟进
            $CrmsBase = new BaseDb(mvc::$cfg['DB']['CRMS']);
            $uname = implode("','",$unameArr);
            $res = $CrmsBase->get('crm_follow',["count(afw_id) as counts,afw_usr_id,afw_usr,FROM_UNIXTIME(afw_modified,'%m月%d日') as adate"],sprintf(" AND afw_ischeck=1 AND afw_pass=1 AND afw_usr IN ('%s') AND afw_modified>=%d AND afw_modified<=%d group by afw_usr_id,adate",$uname,$start,$end));
            $ypArr = [];
            foreach ($res['data'] as $key => $val) 
            {
                $ypArr[$val['adate']][$val['afw_usr']] = $val['counts'];
            }
        }
        //每日总结
        $res = $this->get('crm_report',['rpt_date,rpt_au_uid, count(*) as counts']," group by rpt_date,rpt_au_uid");
        $rijieArr = [];
        if($res['data']){
            foreach ($res['data'] as $key => $val) {
                if($unameArr[$val['rpt_au_uid']]){
                    $rijieArr[$val['rpt_date']][$unameArr[$val['rpt_au_uid']]] = $val['counts'];
                }
            }
        }
        $work = Factory::work(array_merge(mvc::$cfg['chat'], [
            'agent_id' => 3010040,
            'secret' => '9MwxW6yneUvpyDA1huQDJ54ptUV3qKhPmNC3L16P6FI',
        ]));
        $allDepartUser = [];
        //只获取这几个部门的成员
        foreach ([3,5,13,14,15] as $departId) {
            $data = $work->user->getDepartmentUsers($departId);
            $allDepartUser = array_merge($allDepartUser,$data['userlist']);
        };
        $allDepartUser = array_column($allDepartUser,'userid','name');
        //能够查询审批的用户
        foreach ($unameArr as $name) {
            if($allDepartUser[$name]){
                $canSearch[$allDepartUser[$name]] = $name;
            }
        }

        if(!$canSearch){return LibFc::ReturnData(false,'获取企业微信用户数据失败');}
        //获取企业微信的审批数据
        $chatArr = [];

        //存在申请时间比审批时间晚的情况，因此按当前时间取数据,例如9月份申请8月的假期
        $tempEnd = time();
        //企业微信这个接口只能取31天的数据，因此分多次取
        // $tempEnd = $end;
       
        if($tempEnd-$start<=86400*31){
            $apps['data'] = [];
            foreach ($canSearch as $canUserid =>$userName) {
    
                $appres = $this->getApproval($start,$end,$canUserid);
                $apps['data'] = array_merge($apps['data'],$appres);
            }
           
        }else{

            $apps['data'] = [];
            $max = ceil(($tempEnd-$start)/(86400*30));
            foreach ($canSearch as $canUserid =>$userName) {    
                $cstart = $start;
                for ($i=1; $i <= $max; $i++) { 
                    $cend = $cstart+$i*86400*30;
                    if($cend>$tempEnd){
                        $cend = $tempEnd;
                    }
                
                    $appres = $this->getApproval($cstart,$cend,$canUserid);

                    $apps['data'] = array_merge($apps['data'],$appres);
                    $cstart = $cend;
                }
               
            }
        }
        $tempChatArr = [];
        foreach ($apps['data'] as $num)
        {
            $res = $this->approvalDetail($num);
            $item = $res['info'];

            $item['apply_name'] = $canSearch[$item['applyer']['userid']];
            
            if ($item['sp_status'] != 2 || !in_array($item['sp_name'],['外出','请假','调休','出差','加班']) || !$item['apply_name']) {
                continue;
            }
            // 事由
            // $json = json_decode($item['comm']['apply_data'], true);
            $apply_data = $item['apply_data']['contents'];
            if (!is_array($apply_data) || !$apply_data) {
                continue;
            }
            $temp = [];
            $vtext ='';
            foreach ($apply_data as $comm)
            {       
                if(!in_array($comm['control'],['Attendance','Vacation'])) continue;
                if(is_array($comm['value']) && $comm['control'] == 'Vacation')
                {   
                    $vtext = $comm['value']['vacation']['selector']['options'][0]['value'][0]['text'];
                    $temp['stime'] = $comm['value']['vacation']['attendance']['date_range']['new_begin'];
                    $temp['etime'] = $comm['value']['vacation']['attendance']['date_range']['new_end'];
                    $temp['duration'] =$comm['value']['vacation']['attendance']['date_range']['new_duration']/(60*60);
                }
                elseif(is_array($comm['value']) &&$comm['control'] == 'Attendance')
                {   
                    $temp['stime'] = $comm['value']['attendance']['date_range']['new_begin'];
                    $temp['etime'] = $comm['value']['attendance']['date_range']['new_end'];
                    $temp['duration'] =$comm['value']['attendance']['date_range']['new_duration']/(60*60);
                    
                }
            }
            if($temp)
            {
                $sdate = date('Y-m-d',$temp['stime']);
                $edate = date('Y-m-d',$temp['etime']);
                if($sdate===$edate)
                {   
                    $adate = date('m月d日',$temp['stime']);
                    //存在多条记录的情况下，时间叠加
                    if(isset($tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['duration'])){
                        $tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['duration'] += $temp['duration'];
                    }else{
                        $tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['duration'] = $temp['duration'];
                    }
                    //取最早申请的时间   
                    if(isset($tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['stime'])){
                        if($tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['stime'] < $temp['stime'])
                        {
                            $temp['stime'] = $tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['stime'];
                        }else{
                            $tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['stime'] = $temp['stime'];
                        }   
                    }else{
                        $tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['stime'] = $temp['stime'];
                    }

                    $chatArr[$adate][$item['apply_name']][$item['sp_name']] = ($vtext?:$item['sp_name']).":开始时间:".date('H:i',$temp['stime'])."总时长：".$tempChatArr[$adate][$item['apply_name']][$item['sp_name']]['duration'];
                }
                else{
                    $dayDiff = ceil(($temp['etime']-$temp['stime'])/86400);

                    $temp['duration'] =  $temp['duration']/$dayDiff;
                    for ($i = 0; $i < $dayDiff; $i++) 
                    {
                        $currentTimestamp = ($temp['stime'] + ($i * 60 * 60 * 24));
                        $currentDate = date('m月d日', $currentTimestamp);
                        $chatArr[$currentDate][$item['apply_name']][$item['sp_name']] = ($vtext?:$item['sp_name']).":开始时间:".date('H:i', $temp['stime'])."总时长：".$temp['duration'];
                    }
                }
            }
        }
        $dayDiff =  floor(($end - $start) / (60 * 60 * 24));
        $datesArray = [];
        foreach ($unameArr as $val) 
        {
            for ($i = 0; $i <= $dayDiff; $i++) 
            {
                $currentTimestamp = $start + ($i * 60 * 60 * 24);
                $currentDate = date('m月d日', $currentTimestamp);
                $dayofWeekend = date('N', $currentTimestamp);
                $currentDateString = date('Ymd',$currentTimestamp);
                // if(!in_array($dayofWeekend, [6,7])){
                //     $remark = $chatArr[$currentDate][$val]?implode("\r\n",$chatArr[$currentDate][$val]):'';
                // }else{
                //     $remark = ['text'=>$chatArr[$currentDate][$val]?implode("\r\n",$chatArr[$currentDate][$val]):'','color'=>'DCE6F1'];
                // }
                $datesArray[$val][$currentDate] = [
                    'date'=>$currentDate.' （周'.$dayofWeekend.')',
                    'uname'=>$val,'pjd'=>$pjdArr[$currentDate][$val]??'',
                    'yp'=>$ypArr[$currentDate][$val]??'',
                    'heji'=>$pjdArr[$currentDate][$val]+$ypArr[$currentDate][$val],
                    'complete'=>'',
                    'remark'=>$chatArr[$currentDate][$val]?implode("\r\n",$chatArr[$currentDate][$val]):'',
                    'rijie'=>$rijieArr[$currentDateString][$val]>0?'有':''
                ];
            }
        }
        
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $objSheet = $spreadsheet->getActiveSheet();  //获取当前操作sheet的对象
        $header = [
            ['日期','姓名','跑街令电话','宜配电话','合计','完成情况','备注：调休不计算工作日','日结'],
        ];
        $row = 1;
        foreach ($header as $val) {
            $col = 'A';
            foreach ($val as $vv) {
                $objSheet->setCellValue($col.$row, $vv);
                $col++;
            }
            $row++;
        }

        foreach ($datesArray as $uname => $val) 
        {
            foreach ($val as $date=> $vv) 
            {   
                $col = 'A';
                foreach ($vv as $vvv) {
         
                    $objSheet->setCellValue($col.$row, $vvv);
                    
                    $col++;
                }
                $row++;
            }  
        }
        $filename = date('YmdHis').'.xlsx';
        $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        ob_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $objWriter->save("php://output");  
    }
   
    /**
     * @param  int $start
     * @param  int $end
     * @param  string $auIds
     * @method 导出业绩统计数据
     * @author zhengweihua 
     * @copyright 2024-07-16
     */
    public function exportYj($start,$end,$auIds)
    {
        if(!$start||!$end||!$auIds) return LibFc::ReturnData(false,'参数错误');

        $B2cBase = new B2cBase();
        $res = $B2cBase->get('an_user', ['au_uid,au_name'], sprintf(" AND au_uid IN (%s)", $auIds));
        $unameArr = array_column($res['data'], 'au_name');
        if (date('Y-m', $start) == date('Y-m', $end)) {
            $dateTime = date('Y年m月',$start);
        } else {
            $dateTime = date('Y年m月', $start) . '至' . date('Y年m月', $end);
        }
        
        $res = $this->getYeji($start,$end,$auIds);
        
        $showArr = $res['data'];

        $header = [
            [ 
                ['title'=>sprintf('%s业绩汇总',$dateTime),'scell'=>'A1','ecell'=>'U1','isMerge'=>true]
            ],
            [
                ['title'=>'序号','scell'=>'A2','ecell'=>'A3','isMerge'=>true],
                ['title'=>'业务员','scell'=>'B2','ecell'=>'B3','isMerge'=>true],
                ['title'=>'宜配','scell'=>'C2','ecell'=>'I2','isMerge'=>true],
                ['title'=>'跑街','scell'=>'J2','ecell'=>'S2','isMerge'=>true],
                ['title'=>'续费','scell'=>'T2','ecell'=>'T3','isMerge'=>true],
                ['title'=>'合计','scell'=>'U2','ecell'=>'U3','isMerge'=>true,'color'=>'FF0000'],
            ],
            ['','','网站','联动服务','广告','PDF目录','软件','软件定制',['title'=>'小计','color'=>'FF0000'],'商家入驻','PC会员','广告','招商加盟','机器人','资源包','推送服务','打电话','其它',['title'=>'小计','color'=>'FF0000']]
        ];

    
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置标题
        $row = 1;
        foreach ($header as $val) {
            $col = 'A';
            foreach ($val as $vv) {
                
                if(!$vv){
                    $col++;
                    continue;
                } 
                if(is_array($vv)){
                    if($vv['isMerge']){
                        $sheet->mergeCells(sprintf("%s:%s",$vv['scell'],$vv['ecell'])); // 合并单元格
                    }
                    $sheet->setCellValue($vv['scell']??$col.$row, $vv['title']); 
                    $style = $sheet->getStyle($vv['scell']??$col.$row);
                    if($vv['color']) $style->getFont()->getColor()->setARGB($vv['color']);
                }else{
                    $sheet->setCellValue($col.$row, $vv);
                    $style = $sheet->getStyle($col.$row);  
                }
                $style->getAlignment()->setHorizontal('center');  
                $style->getAlignment()->setVertical('center');  
                $col++;
            }
            $row++;
        }
        
        foreach ($showArr as $val) 
        {
            $col = 'A';
            foreach ($val as $key => $vv) {
                if(!is_array($vv)){
                    $sheet->setCellValue($col.$row, $vv); 
                    $style = $sheet->getStyle($col.$row); 
                }else{
                    $sheet->setCellValue($col.$row, $vv['value']); 
                    $style = $sheet->getStyle($col.$row); 
                    if($vv['color']){
                        $style->getFont()->getColor()->setARGB($vv['color']);
                    }
                }
                
                $style->getAlignment()->setHorizontal('center');  
                $style->getAlignment()->setVertical('center');
                
                $col++;
            }
            $row++;
        }

        $filename = sprintf('%s业绩汇总',$dateTime).'.xlsx';
        $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        ob_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $objWriter->save("php://output"); 
    }


    /**
     * @param  int $start
     * @param  int $end
     * @param  string $auIds au_id: 1,2,23,
     * @method 获取员工业绩
     * @author zhengweihua 
     * @copyright 2024-08-07
     */
    public function getYeji($start, $end,$auIds,$showField = [],$type='')
    {
        $B2cBase = new B2cBase();
        $res = $B2cBase->get('an_user', ['au_uid,au_name'], sprintf(" AND au_uid IN (%s)", $auIds));
        $unameArr = array_column($res['data'],'au_name');
        $unameArr2 = array_column($res['data'],'au_uid','au_name');
        
        if($type=='targetMoney'){
            $res = $this->get('user_sales_target',['sum(ust_money) as ust_money,ust_au_uid'],sprintf(" AND ust_month>='%s' AND ust_month<='%s'  AND ust_au_uid IN (%s) group by ust_au_uid",date('Ym',$start),date('Ym',$end),$auIds));
            $targetArr = $res['data']?array_column($res['data'],'ust_money','ust_au_uid'):[];
        }

        $serArrList = [
            ['ser_id' => 1,'type'=>'new', 'site'=>1,'ser_name' => '网站', 'ser_new' => 1, 'new_ser_id' => [16, 17, 19, 20, 21]],
            ['ser_id' => 3,'type'=>'new', 'site'=>1,'ser_name' => '联动服务', 'ser_new' => 1, 'new_ser_id' => [28]],
            ['ser_id' => 4,'type'=>'new', 'site'=>1,'ser_name' => '宜配广告', 'ser_new' => 1, 'new_ser_id' => [22, 23]],
            ['ser_id' => 7,'type'=>'new', 'site'=>1,'ser_name' => 'PDF目录', 'ser_new' => 1, 'new_ser_id' => [12]],
            ['ser_id' => 8,'type'=>'new', 'site'=>1,'ser_name' => '软件', 'ser_new' => 1, 'new_ser_id' => [7, 10, 37]],
            ['ser_id' => 9,'type'=>'new', 'site'=>1,'ser_name' => '软件定制', 'ser_new' => 1, 'new_ser_id' => [8]],
            ['ser_id' => 26,'type'=>'new', 'site'=>2,'ser_name' => '商家入驻', 'ser_new' => [0,1,2], 'new_ser_id' => [26]],
            ['ser_id' => 27,'type'=>'new', 'site'=>2,'ser_name' => 'PC会员', 'ser_new' => [0,1,2], 'new_ser_id' => [27]],
            ['ser_id' => 25,'type'=>'new','site'=>2, 'ser_name' => '跑街广告', 'ser_new' => [0,1,2], 'new_ser_id' => [25]],
            ['ser_id' => 30,'type'=>'new', 'site'=>2,'ser_name' => '招商加盟', 'ser_new' => [0,1,2], 'new_ser_id' => [30]],
            ['ser_id' => 38,'type'=>'new', 'site'=>2,'ser_name' => '机器人', 'ser_new' => [0,1,2], 'new_ser_id' => [38]],
            ['ser_id' => 33,'type'=>'new', 'site'=>2,'ser_name' => '资源包', 'ser_new' => [0,1,2], 'new_ser_id' => [33]],
            ['ser_id' => 31,'type'=>'new', 'site'=>2,'ser_name' => '推送服务', 'ser_new' => [0,1,2], 'new_ser_id' => [31]],
            ['ser_id' => 34,'type'=>'new', 'site'=>2,'ser_name' => '打电话', 'ser_new' => [0,1,2], 'new_ser_id' => [34]],
            ['ser_id' => 35,'type'=>'new', 'site'=>2,'ser_name' => '其它', 'ser_new' => [0,1,2], 'new_ser_id' => [35]],


            ['ser_id' => 2,'type'=>'xufei','site'=>1, 'ser_name' => '网站续费', 'ser_new' => 2, 'new_ser_id' => [16, 17, 19, 20, 21]],
            ['ser_id' => 5,'type'=>'other','site'=>1, 'ser_name' => '软件年费', 'ser_new' => 2, 'new_ser_id' => [7, 10, 37]],
            ['ser_id' => 6,'type'=>'other','site'=>1, 'ser_name' => '软件服务费', 'ser_new' => [0,1,2], 'new_ser_id' => [9,41]],
            ['ser_id' => 10,'type'=>'other', 'site'=>1,'ser_name' => '联动服务续费', 'ser_new' => 2, 'new_ser_id' => [28]],
            ['ser_id' => 11,'type'=>'other','site'=>1,'ser_name' => '宜配其他', 'ser_new' => [0,1,2], 'new_ser_id' => [5,6,13,14]],
        ];
        $temp  = [];
		$serArr = [];
		foreach ($serArrList as $k=> $v) 
		{   
            foreach ($v['new_ser_id'] as $vv) {
                if(is_array($v['ser_new'])){
                    foreach ($v['ser_new'] as $sval) {
                        $temp[$sval.'_'.$vv] = $v;
                    }
                }else{
                    $temp[$v['ser_new'].'_'.$vv] = $v;
                }
            }
			$serArr[$v['type']][$v['ser_id']] = $v;
			$ToTalArr['ser'][$v['ser_id']] = 0;
		}
        //业绩
        $CrmsBase = new BaseDb(mvc::$cfg['DB']['CRMS']);
        $uname = implode("','",$unameArr);
        $res = $CrmsBase->Get('yj_user',array('u_id','u_name'),sprintf(" AND u_name IN ('%s')",$uname));
        $uid = array_column($res['data'],'u_id');
        if(!$uid){
            $arr = [];
            if($type=='targetMoney'){

                foreach ($unameArr2 as $key => $val) {
                    $arr[] = [
                        'mbyj'=>$targetArr[$key]??0
                    ];
                }

            }
            return LibFc::ReturnData(true,$arr);
        }
           
        
		foreach ($res['data'] as $v) 
		{
			foreach($serArr as $kk=>$vv)
			{	
				$v['ser'][$kk] = 0;
			}
			$v['yiparts_money'] = 0;
			$v['xufei_money'] = 0;
			$v['all_money'] = 0;
			$ReportArr[$v['u_id']] = $v;
		}
        $table = 'yja_money 
        join yja_money_ser on mos_mon_id = mon_id
        join yja_service on ser_id = mos_ser_id';
        $YjaRes = $CrmsBase->Get($table, ['*'],sprintf(" AND mon_u_id in (%s) AND mon_date >=%d AND mon_date <=%d",implode(',',$uid),$start, $end ));	
		
        foreach($YjaRes['data'] as $v)
		{   
            $oldSer =  $temp[$v['mos_sertype'].'_'.$v['mos_ser_id']];
            if(empty($oldSer)) continue;
            $ser_id = $oldSer['ser_id'];
            //续费
            if($v['mos_sertype'] == 2 && $oldSer['type']=='xufei')
			{
				$ReportArr[$v['mon_u_id']]['xufei_money'] += $v['mos_pay_money'];
			}
            $ReportArr[$v['mon_u_id']]['ser'][$ser_id] += $v['mos_pay_money'];

            //宜配新业绩
            if($v['mos_sertype'] == 1 && $oldSer['type']=='new' && $oldSer['site']==1)
			{
				$ReportArr[$v['mon_u_id']]['yiparts_money'] += $v['mos_pay_money'];
      
			}
            // if($v['mos_sertype'] == 1 && $oldSer['type']=='new' && $oldSer['site']==1)
            //宜配总业绩
			if($oldSer['site']==1)
			{
				$ReportArr[$v['mon_u_id']]['yiparts_total_money'] += $v['mos_pay_money'];
                $ReportArr[$v['mon_u_id']]['all_money'] += $v['mos_pay_money'];
			}
            //跑街业绩
            if($oldSer['type']=='new' && $oldSer['site']==2)
			{
				$ReportArr[$v['mon_u_id']]['paojd_money'] += $v['mos_pay_money'];
                $ReportArr[$v['mon_u_id']]['all_money'] += $v['mos_pay_money'];
			}
		}
        //整理数组
        $serArr = array_column($serArrList,'ser_id','ser_name');
        if(!$showField){
            $showField = ['序号','业务员','网站','联动服务','宜配广告','PDF目录','软件','软件定制','宜配小计','商家入驻','PC会员','跑街广告','招商加盟','机器人','资源包','推送服务','打电话','其它','跑街小计','续费','总计'];
        }
       
        $showArr = []; 
        //调整排序
        array_multisort(array_column($ReportArr,'all_money'),SORT_DESC,$ReportArr);  
        $i=1; 
        foreach ($ReportArr as $key => $val) 
        {   
            $arr = [];
            foreach ($showField as $kk=> $vv) {
                switch ($vv) {
                    case '序号':
                        $arr[$kk] = $i++;
                        break;
                    case '业务员':
                        $arr[$kk] = $val['u_name'];
                        break;
                    case '网站':
                    case '联动服务':
                    case '宜配广告':
                    case 'PDF目录':
                    case '软件':
                    case '软件定制':
                    case '商家入驻':
                    case 'PC会员': 
                    case '跑街广告':  
                    case '招商加盟':
                    case '机器人':
                    case '资源包':
                    case '推送服务':
                    case '打电话': 
                    case '其它':
                    case '网站续费':
                    case '软件年费':
                    case '软件服务费':
                    case '联动服务续费':
                    case '宜配其他':
                        $arr[$kk] = $val['ser'][$serArr[$vv]]??'';
                        break;  
                    case '宜配小计':
                        $arr[$kk] = ['value'=>$val['yiparts_money'],'color'=>'FF0000'];
                        break;
                    case '宜配总计':
                        $arr[$kk] = ['value'=>$val['yiparts_total_money'],'color'=>'FF0000'];
                        break;
                    case '跑街小计':
                        $arr[$kk] = ['value'=>$val['paojd_money'],'color'=>'FF0000'];
                        break;
                    case '续费':
                        $arr[$kk] = $val['xufei_money'];
                        break;
                    case '总计':
                        $arr[$kk] = ['value'=>$val['all_money'],'color'=>'FF0000','font-weight'=>'700'];
                        break;
                    case '业绩目标':
                        $arr[$kk] = $targetArr[$unameArr2[$val['u_name']]]??0;
                        break;
                }
            }
            
            $showArr[$val['u_name']]= $arr;
        }

        return LibFc::ReturnData(true,array_values($showArr));
    }


    /**
     * @param  [type] $dataArr
     * @method 设置用户目标业绩
     * @author zhengweihua 
     * @copyright 2024-08-06
     */
    public function setUserTarget($dataArr)
    {   
        
        $where = '';
        if($dataArr['year']){
            $start = $dataArr['year'].'01';
            $end = $dataArr['year'].'12';
            $where = sprintf(" AND ust_month>=%d AND ust_month<=%d",(int)$start,(int)$end);
        }
        $auIds = array_unique(array_keys($dataArr['months']));
        if($auIds){
            $where .= sprintf(" AND ust_au_uid IN (%s)",implode(',',$auIds));
        }
        $res = $this->get('user_sales_target',['*'],$where);
        foreach ($res['data'] as $key => $val) {
            $targetArr[$val['ust_au_uid']][$val['ust_month']] = $val;
        }
        $this->CRMDb->Begin();
        foreach ($dataArr['months'] as $auid => $val) {
            foreach ($val as $month => $money) {
                $item = [ 
                    'ust_au_uid'=>$auid,
                    'ust_month'=>$month,
                    'ust_money'=>$money?:0,
                ];
                if(!$targetArr[$auid][$month] && $money>0){
                    $this->add('user_sales_target',$item);
                }elseif ($targetArr[$auid][$month]) {
                    $this->set('user_sales_target',$item,sprintf(" AND ust_id=%d",$targetArr[$auid][$month]['ust_id']));
                }
            }          
        }
        $this->CRMDb->End();
        return LibFc::ReturnData(true);
    }

    /**
     * @param  int $auUid 业务员
     * @param  int $dataArr 数组
     * @method 设置意向客户
     * @author zhengweihua 
     * @copyright 2024-08-08
     */
    public function setYixiangCtr($auUid,$dataArr)
    {
        
        $ctrId = (int)$dataArr['ctr_id'];
        $ccrId = (int)$dataArr['ccr_id'];
        $data = [
            'ccr_ctr_id'=>$ctrId,
            'ccr_cla_id'=>$dataArr['ccr_cla_id'],
            'ccr_au_uid'=>$auUid,
            'ccr_sve_text'=>$dataArr['ccr_sve_text'],
            'ccr_etime'=>time(),
            'ccr_remark'=>LibFc::Escape($dataArr['ccr_remark']),
        ];
        //判断当前用户是否成交
        $res = $this->get('crm_customer',['*'],sprintf(" AND ctr_id=%d AND ctr_level_id=5",$ctrId),true);
        if($res['data']){
            return LibFc::ReturnData(false,'当前客户已成交，不能设置意向客户');
        }
        //查询如果当前用户当前月份没有跟进记录不可添加到意向客户
        $existRes = $this->get('crm_follow',['count(*) as nums'],sprintf(" AND cfo_atime BETWEEN %d AND %d AND cfo_ctr_id=%d AND cfo_au_uid=%d",strtotime(date('Y-m-1')),strtotime(date('Y-m-t'))+86399,$ctrId,$auUid),true);
        if(empty($existRes['data']['nums'])){
            return LibFc::ReturnData(false,'本月您还未跟进该客户，不可设置意向客户');
        }
        //判断客户是否存在意向客户
        $this->CRMDb->Begin();
        if($ccrId){
            $where = sprintf(" AND ccr_id=%d",$ccrId);
            unset($data['ccr_au_uid']);
        }else{
            $where = sprintf(" AND ccr_ctr_id=%d AND ccr_au_uid=%d",$ctrId,$auUid);
        }
        $res = $this->get('crm_customer_rank',['*'],$where,true);
  
        if($res['data']){
            //此前无意向变更为有意向
            if($res['data']['ccr_cla_id']==0 && $data['ccr_cla_id']!=0){
                $data['ccr_count'] = '#@ccr_count+1';
            }
            $this->set('crm_customer_rank',$data,sprintf(" AND ccr_id=%d",$res['data']['ccr_id']));
            $ccr_id = $res['data']['ccr_id'];            
        }else{
            $data['ccr_count'] = 1;
            $data['ccr_au_uid'] = $auUid;
            $res = $this->add('crm_customer_rank',$data);
            $ccr_id = $res['data'];
        }
        
        $this->CRMDb->End();

        return LibFc::ReturnData(true,$ccr_id);
    }

    public function getYixiangCtr($auUid)
    {   
        if(!$auUid){return LibFc::ReturnData(false,'auUid is null');}

        if(is_array($auUid)){
            $where =  sprintf(" AND ccr_au_uid IN (%s)",implode(',',$auUid));
        }else{
            $where =  sprintf(" AND ccr_au_uid =%d",$auUid);
        }
        $res = $this->get('crm_customer_rank join crm_label on cla_id=ccr_cla_id and cla_type="rank"',['*'],$where);
        $arr = [];
        foreach ($res['data'] as $key => $val) {
            $arr[$val['ccr_au_uid']][$val['ccr_ctr_id']] = $val;
        }
    
        return LibFc::ReturnData(true,$arr);
    }


    /**
     * @param  Array $addArr 
     * @method 添加跟进记录
     * @author zhengweihua 
     * @copyright 2024-08-08
     */
    public function addFollow($dataArr,$usergroup='')
    {
        $ctr_id = (int)$dataArr['ctr_id'];
        $cfo_id = (int)$dataArr['cfo_id'];
        $addArr = [
			'cfo_ctr_id' => $dataArr['ctr_id'],
			'cfo_pass'=>(int)$dataArr['cfo_pass'],
			'cfo_txt' => $dataArr['cfo_txt'],
			'cfo_type' => $dataArr['cfo_type'],
			'cfo_val' => $dataArr['cfo_val'],
			'cfo_au_uid' => $dataArr['cfo_au_uid'],
			'cfo_au_name' => $dataArr['cfo_au_name'],
			'cfo_atime' => time()
		];
        if($addArr['cfo_pass']==0){
			$addArr['cfo_check'] = 1;
			$addArr['cfo_checktime'] = time();
		}
		$cfoArr = [];
		if($cfo_id){
			$res = $this->get('crm_follow',['*'],sprintf(" AND cfo_id=%d",$cfo_id),true);
			$cfoArr = $res['data'];
		}

        $this->CRMDb->Begin();

		if($cfoArr){
			$res = $this->set('crm_follow', $addArr,sprintf(" AND cfo_id=%d",$cfo_id));
		}else{
			$res = $this->add('crm_follow', $addArr);
            
            //是否存在计划任务
            $planres = $this->get('crm_plan',['*'],sprintf(" AND cp_ok!=1 AND cp_ctr_id=%d AND cp_au_uid=%d",$dataArr['ctr_id'],$dataArr['cfo_au_uid']));
            if($planres['data']){
                $cpIds = array_column($planres['data'],'cp_id');
                $this->set('crm_plan',['cp_ok'=>1,'cp_ok_time'=>time(),'cp_ok_au_id'=>$dataArr['cfo_au_uid']],sprintf(" AND cp_id IN (%s)",implode(',',$cpIds)));
            }
		}
		if($usergroup=='kefu')
		{
			//客户如果客户归属公司，客服跟进时如果没有客服直接赋值
			$table = 'crm_customer join crm_customer_admin on cca_ctr_id= ctr_id';
			$res = $this->get($table,['*'],sprintf(" AND cca_end_time>%d AND ctr_id=%d",time(),$ctr_id),true);
			if ($res['data']['ctr_kefu'] == 0 && $res['data']['cca_au_uid']==172999) {
				$this->set('crm_customer', ['ctr_kefu' => $_SESSION['_userid']], sprintf(" AND ctr_id=%d", $ctr_id));
			}
		}
        //
        if($dataArr['joinPlan']==1){
            $planArr =  [
                'cp_date'=>date('Ymd',strtotime($dataArr['cp_date'])),
                'cp_ctr_id'=>$ctr_id,
                'cp_au_uid'=>$dataArr['cfo_au_uid'],
                'cp_title'=>$dataArr['cp_title']??'客情服务：跟进',
                'cp_by_uid'=>$dataArr['cfo_au_uid'],
                'cp_atime'=>time(),
            ];
            $res = $this->setPlan($planArr);
        }
        $this->CRMDb->End();
        return LibFc::ReturnData(true);
    }

    /**
     * @param  [type] $dataArr
     * @method 添加计划任务
     * @author zhengweihua 
     * @copyright 2024-08-08
     */
    public function setPlan($dataArr)
    {

        $ctrIdArr = explode(',',$dataArr['cp_ctr_id']);
        $date = $dataArr['cp_date'];

        $res = $this->get('crm_plan',['cp_ctr_id'],sprintf(" AND cp_date='%s' AND cp_ctr_id IN (%s)",$date,implode(',',$ctrIdArr)));
        $hasArr = $res['data']?array_column($res['data'],'cp_ctr_id'):[];
        $addArr = array_diff($ctrIdArr,$hasArr);
        $planArr = [];
        if($addArr){
            
            foreach ($addArr as $key => $val) {
                $planArr =  [
                    'cp_date'=>$date,
                    'cp_ctr_id'=>$val,
                    'cp_au_uid'=>$dataArr['cp_au_uid'],
                    'cp_title'=> $dataArr['cp_title']?:'客情服务：跟进',
                    'cp_by_uid'=>$dataArr['cp_by_uid'],
                    'cp_atime'=>time(),
                ];
                $this->add('crm_plan',$planArr);
            }
        }else{
            return LibFc::ReturnData(true,'已存在提醒计划',-1);
        }    
        return LibFc::ReturnData(true);
    }

    public function exportAds($data)
    {
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $header = $data['header'];
        // 设置标题
        $row = 1;
        foreach ($header as $val) {
            $col = 'A';
            foreach ($val as $vv) {
                
                if(!$vv){
                    $col++;
                    continue;
                } 
                if(is_array($vv)){
                    if($vv['isMerge']){
                        if($vv['scell'] && $vv['ecell']){
                            $sheet->mergeCells(sprintf("%s:%s",$vv['scell'],$vv['ecell'])); // 合并单元格
                        }else{
                            $sheet->mergeCells(sprintf("%s:%s",$col.$row,$col.($row+$vv['rowspan']-1))); // 合并单元格
                        }
                        
                    }
                    $sheet->setCellValue($vv['scell']??$col.$row, $vv['title']); 
                    $style = $sheet->getStyle($vv['scell']??$col.$row);
                    if($vv['color']) $style->getFont()->getColor()->setARGB($vv['color']);
                }else{
                    $sheet->setCellValue($col.$row, $vv);
                    $style = $sheet->getStyle($col.$row);  
                }
                $style->getAlignment()->setHorizontal('center');  
                $style->getAlignment()->setVertical('center');  
                $col++;
            }
            $row++;
        }
        
        $showArr = $data['data'];
        foreach ($showArr as $val) 
        {
            $col = 'A';
            foreach ($val as $key => $vv) {
                if(!is_array($vv)){
                    $sheet->setCellValue($col.$row, $vv); 
                    $style = $sheet->getStyle($col.$row); 
                }else{
                    if($vv['isMerge']){
                        if($vv['scell'] && $vv['ecell']){
                            $sheet->mergeCells(sprintf("%s:%s",$vv['scell'],$vv['ecell'])); // 合并单元格
                        }else{
                            $sheet->mergeCells(sprintf("%s:%s",$col.$row,$col.($row+$vv['rowspan']-1))); // 合并单元格
                        }
                    }
                    $sheet->setCellValue($col.$row, $vv['value']); 
                    $style = $sheet->getStyle($col.$row); 
                    if($vv['color']){
                        $style->getFont()->getColor()->setARGB($vv['color']);
                    }
                }
                
                $style->getAlignment()->setHorizontal('center');  
                $style->getAlignment()->setVertical('center');
                $sheet->getColumnDimension($col)->setWidth(20);
                
                $col++;
            }
            $row++;
        }
        $dateTime = date('YmdHis');
        $filename = sprintf('%s广告报价',$dateTime).'.xlsx';
        $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        ob_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $objWriter->save("php://output"); 
    }
}