<?php

use Respect\Validation\Rules\Uppercase;
require "cmdLibs/autoload.php";

class getCrm{
    public $moneyDb;
    public $crmLocal;
    public $crmNet;
    public $pinyin;
    public $tree;
    public $q;
    public $tables = [];
	public function __construct()
    {
        $this->moneyDb = new db('main');
        $this->crmLocal = new db('money_crm');
        $this->crmNet = new db('crm_sb');
        $this->pinyin = new \Overtrue\Pinyin\Pinyin();
        $this->tables = [
            ['name' => 'do_user', 'key' => ['usr_id', 'usr_username'], 'isbig' => 0, 'remark' => '职员表', 'function' => null],
            ['name' => 'yj_team', 'key' => 'te_id', 'isbig' => 0, 'remark' => '团队表', 'function' => null],
            ['name' => 'yj_user', 'key' => 'u_id', 'isbig' => 0, 'remark' => '业务员表', 'function' => null],
            ['name' => 'yja_department', 'key' => 'dep_id', 'isbig' => 0, 'remark' => '部门表', 'function' => null],
            ['name' => 'yja_money', 'key' => 'mon_id', 'isbig' => true, 'remark' => '业绩表', 'function' => null],
            ['name' => 'yja_money_ser', 'key' => 'mos_id', 'isbig' => true, 'remark' => '业绩项目表', 'function' => null],
            ['name' => 'yja_paytype', 'key' => 'ypt_id', 'isbig' => 0, 'remark' => '付款方式表', 'function' => null],
            ['name' => 'yja_service', 'key' => 'ser_id', 'isbig' => 0, 'remark' => '服务项目表', 'function' => null],
            ['name' => 'yja_site', 'key' => 'site_id', 'isbig' => 0, 'remark' => '宜配的公司表', 'function' => null],
            ['name' => 'out_items', 'key' => 'ois_id', 'isbig' => 0, 'remark' => '支出类目', 'function' => null],
            ['name' => 'out_payment', 'key' => 'pay_id', 'isbig' => true, 'remark' => '支出单', 'function' => null],
            ['name' => 'out_payment_detail', 'key' => '', 'isbig' => true, 'remark' => '支出方式', 'function' => null],
            ['name' => 'out_payment_links', 'key' => '', 'isbig' => 0, 'remark' => '支出部门与员工', 'function' => null],
            [
                'name' => 'yja_money_tcbill',
                'key' => 'ymt_id',
                'isbig' => 0,
                'remark' => '业务提成',
                'function' => null,
                'sql' => 'SELECT ymt_id,ymt_mst_mos_id,ymt_month,ymt_status        
                        ,ymt_mos_id,ymt_u_id,ymt_usr_id,ymt_type,ymt_rate,ymt_money,ymt_remark,ymt_atime,ymt_add_usr_id,ymt_etime,ymt_edit_usr_id,ymt_chk_begin_time,ymt_confirm_time,ymt_chk_ok_usr_id,ymt_chk_ok_time,ymt_chk,ymt_chk_time      
                        FROM yja_money_tcbill'
            ],
        ];

        echo "Copy Tables To Money Server.\r\n";
        $startAllTime = time();
        foreach($this->tables as $table){
            $startTime = time();
            echo 'Start: ' . $table['name'] .' ';
            if(empty($table['function'])){
                $this->getTable($table['name']);
            }
            echo " end, time: ".(time()-$startTime)."s\r\n";
            flush();
        }
        echo "copy tables all time: ".(time()-$startAllTime)."s\r\n";
        flush();
        echo "syncCrmBaseToMoney\r\n";
        $startAllTime = time();
        $this->syncCrmBaseToMoney(); //同步基础数据到money库
        echo "syncCrmBaseToMoney time: " . (time() - $startAllTime) . "s\r\n";
        flush();
    }
    function log(string $msg){
        tolog($msg,'getCrm',['y','y-m-d']);
    }
    /**将远程 crm_sb 库中的一引起表数据，原样复制到 本地 money_crm 库中 */
    function getTable($tableName){
        $idx = array_search($tableName, array_column($this->tables,'name'));
        $q = (new sqler())->table($tableName);
        $this->crmNet->closeUserBuff();
        if(isset($this->tables[$idx]['sql'])){
            $res = $this->crmNet->sql($this->tables[$idx]['sql']);
        }else{
            $res = $this->crmNet->select($q);
        }
        while($row = $res->row()){
            $q->push($row);
        }
        $this->crmNet->openUserBuff();

        $this->crmLocal->truncate($q);
        $this->crmLocal->insert($q);
    }

    /**本地 money_crm 库中的数据生成与合并成凭证所需要的数据 */
    function syncCrmBaseToMoney(){
        $error = [];
        $this->log('============================================================================');
        $this->log('=============================**** CRM 更新日志 ****================================');
        $this->log('============================================================================');
        //库存现金
        $objId = 10091;
        $level = 2;
        $maxCode = $this->getMaxCode($objId,'1001.00');
        $q = (new sqler())->table('yja_paytype')->where('ypt_type', '现金账户')->order('ypt_seq');
        $rows = $this->crmLocal->select($q)->all();
        foreach($rows as $row){
            $crmKey = $row['ypt_id'];
            $name = $row['ypt_name'];
            if($crmKey=='' || $name==''){
                continue;
            }
            $item = $this->findLocalBycrm($objId,$crmKey);
            if(empty($item)){
                $code = $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > 库存现金: <'.$name.'>';
                $this->log($msg);
            }else{
                $code = $item['o_code'];
                if($item['o_name']!=$name){
                    $msg = '更新科目名称 > 库存现金: <'.$name.'>, 旧值: <'.$item['o_name'].'>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code,$name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId,$level,$code,$name,$keyword,$crmKey,empty($item) );
            if(empty($id)){
                $msg = "<库存现金科目>未找到或者未生成objId, ".$code.','.$name;
                $this->log($msg);
            }else{
                //科目类别 > 损益
                $msg= $this->set_object_ext_val($id,1,'资产');
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //余额方向 > 借
                $msg = $this->set_object_ext_val($id,2,203);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //是否结出余额  > 是
                $msg = $this->set_object_ext_val($id,4,521);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
            }
            $this->moneyDb->end();
        }    

        //银行存款
        $objId = 10092;
        $level = 2;
        $maxCode = $this->getMaxCode($objId,'1002.00');
        $q = (new sqler())->table('yja_paytype')->where('ypt_type', '银行账户')->order('ypt_seq');
        $rows = $this->crmLocal->select($q)->all();
        foreach($rows as $row){
            $crmKey = $row['ypt_id'];
            $name = $row['ypt_name'];
            if($crmKey=='' || $name==''){
                continue;
            }
            $item = $this->findLocalBycrm($objId,$crmKey);
            if(empty($item)){
                $code = $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > 银行存款: <'.$name.'>';
                $this->log($msg);
            }else{
                $code = $item['o_code'];
                if($item['o_name']!=$name){
                    $msg = '更新科目名称 > 银行存款: <'.$name.'>, 旧值: <'.$item['o_name'].'>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code,$name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId,$level,$code,$name,$keyword,$crmKey,empty($item) );
            if(empty($id)){
                $msg = "<银行存款科目>未找到或者未生成objId, ".$code.','.$name;
                $this->log($msg);
            }else{
                //科目类别 > 损益
                $msg= $this->set_object_ext_val($id,1,'资产');
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //余额方向 > 借
                $msg = $this->set_object_ext_val($id,2,203);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //是否结出余额  > 是
                $msg = $this->set_object_ext_val($id,4,521);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
            }
            $this->moneyDb->end();
        }
        //科目 > 主营业务收入
        $level = 2;
        $maxCode = $this->getMaxCode($objId,'6001.00');
        $q = (new sqler())->table('yja_service')->order('ser_seq');
        $rows = $this->crmLocal->select($q)->all();
        foreach($rows as $row){
            $crmKey = $row['ser_id'];
            $name_lnk = [];
            if ($row['ser_pser_id']) {
                $name_lnk[] = (new sqler($this->crmLocal))->table('yja_service')->field('ser_name')->where('ser_id', $row['ser_pser_id'])->one();
            }else{
                $name_lnk[] = $row['ser_site_id'] == '1' ? '宜配' : '跑街';
            }
            $name_lnk[] = $row['ser_name'];
            $ser_code = $row['ser_code'] ; //人工编码
            $name = implode('-', $name_lnk);
            if($crmKey=='' || $name==''){
                continue;
            }
            $item = [];
            $this->moneyDb->begin();
            if($ser_code) {
                //有自定义编码的，优先使用自定义编码匹配科目
                $code = $ser_code;
                $keyword = $this->getKeyWord($code, $name);
                $q = (new sqler())->table('`object`')
                    ->where('o_code', $code);
                $item = $this->moneyDb->select($q)->row();
                if (empty($item)) {
                    $msg = '新增科目名称 >  <' . $name . '>';
                    $this->log($msg);
                    $t = explode('.', $code);
                    $parentCode = $t[0];
                    $q = (new sqler())->table('`object`')
                        ->where('o_code', $parentCode);
                    $parentItem = $this->moneyDb->select($q)->row();
                    $objId = $parentItem['o_id'];
                    $q = (new sqler())->table('`object`')
                        ->set('o_parent_id', $objId)
                        ->set('o_code', $code)
                        ->set('o_name', $name)
                        ->set('o_keyword', $keyword)
                        ->set('o_level', $level)
                        ->set('o_crm_key', $crmKey);
                    $this->moneyDb->insert($q);
                    $id = $this->moneyDb->insertId();
                } else {
                    $code = $item['o_code'];
                    if ($item['o_name'] != $name) {
                        $msg = '更新科目名称 > <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                        $this->log($msg);
                    }
                    $objId = $item['o_parent_id'];
                    $q = (new sqler())->table('`object`')
                        ->set('o_name', $name)
                        ->set('o_keyword', $keyword)
                        ->set('o_crm_key', $crmKey)
                        ->where('o_id', $item['o_id']);
                    $this->moneyDb->update($q);
                    $id = $item['o_id'];

                    //如果有默认 默认主科目 10094 主营业务收入中存在，则原记录o_crm_key置空
                    $q = (new sqler())->table('`object`')
                        ->where('o_parent_id', 10094)
                        ->where('o_crm_key', $crmKey);
                    $oldItem = $this->moneyDb->select($q)->row();
                    if($oldItem) {
                        $q = (new sqler())->table('`object`')
                            ->set('o_crm_key', '')
                            ->where('o_id', $oldItem['o_id']);
                        $this->moneyDb->update($q);
                    }
                }
            } else {
                //没有自定义编码的
                $objId = 10094; //默认主科目
                $item = $this->findLocalBycrm($objId, $crmKey);
                if (empty($item)) {
                    $code = $this->nextCode($maxCode);
                    $maxCode = $code;
                    $msg = '新增科目名称 > 主营业务收入: <' . $name . '>';
                    $this->log($msg);
                } else {
                    $code = $item['o_code'];
                    if ($item['o_name'] != $name) {
                        $msg = '更新科目名称 > 主营业务收入: <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                        $this->log($msg);
                    }
                }
                $keyword = $this->getKeyWord($code, $name);
                $id = $this->saveToObject($objId, $level, $code, $name, $keyword, $crmKey, empty($item));
            }
            if(empty($id)){
                $msg = "<主营业务收入科目>未找到或者未生成objId, ".$code.','.$name;
                $this->log($msg);
            }else{
                //科目类别 > 损益
                $msg= $this->set_object_ext_val($id,1,'损益');
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //余额方向 > 贷
                $msg = $this->set_object_ext_val($id,2,204);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //核算对象 
                $ser_tj_style = '[]';
                if($row['ser_tj_style']){
                    $arr = explode(',',$row['ser_tj_style']);
                    foreach($arr as $k=>$v){
                        $v = trim($v);
                        if($v=='部门') $v='"1"';
                            elseif($v=='职员') $v='"2"';
                                elseif($v=='客户') $v='"4"';
                                    else $v='';
                        if($v) $arr[$k] = $v;
                            else unset($arr[$k]);
                    }
                    $ser_tj_style = '['.implode(',',$arr).']';
                }
                $msg = $this->set_object_ext_val($id,3,$ser_tj_style);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //是否结出余额  > 否 
                $msg = $this->set_object_ext_val($id,4,522);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
                //销售收入计入
                $ext5 = $row['ser_site_id']=='1' ? 301 : 302;
                $msg = $this->set_object_ext_val($id,5,$ext5);               
                if($msg!='') $this->log("科目:".$name.','. $msg);
            }
            $this->moneyDb->end();
        }

        //支出项目转凭证
        $q = (new sqler())->table('out_items')->order('ois_code')->map('ois_id');
        $tq = new sqler($this->moneyDb);
        $rows = $this->crmLocal->select($q)->all();
        foreach ($rows as $row) {
            if($row['ois_parent']==0){
                continue;
            }
            $objRow = $tq->table('object')->where('o_code', trim($rows[$row['ois_parent']]['ois_code']))->row();
            $objId = $objRow['o_id'];;
            $objTitle = $objRow['o_name'];
            $level = 2;
            $maxCode = $this->getMaxCode($objId, $objRow['o_code']);
            $crmKey = $row['ois_id'];
            $name = $row['ois_name'];
            $code = $row['ois_code'];
            if ($crmKey == '' || $name == '') {
                continue;
            }
            $item = $this->findLocalBycrm($objId, $crmKey);
            if (empty($item) && $code){ //是否有编码对应的科目
               $item =  $this->findLocalByCode($code);
               $tq = (new sqler())->table('object')->where('o_id',$item['o_id'])->set('o_crm_key',$crmKey);
               $this->moneyDb->update($tq);
            }
            if (empty($item)) {
                $code = $code ?: $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > ' . $objTitle . ': <' . $name . '>';
                $this->log($msg);
            } else {
                $code = $item['o_code'];
                if ($item['o_name'] != $name) {
                    $msg = '更新科目名称 > ' . $objTitle . ': <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code, $name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId, $level, $code, $name, $keyword, $crmKey, empty($item));
            if (empty($id)) {
                $msg = "<'.$objTitle.'>未找到或者未生成objId, " . $code . ',' . $name;
                $this->log($msg);
            } else {
                //科目类别 > 负债
                $msg = $this->set_object_ext_val($id, 1, '负债');
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //余额方向 > 贷
                $msg = $this->set_object_ext_val($id, 2, 203); //203借 ，204贷
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //核算对象 1 部门, 2 职员, 3 客户
                /*
                if ($row['ois_require']) {
                    $calcObj = '[1,2]';
                } else {
                    $calcObj = '[1]';
                }
                $msg = $this->set_object_ext_val($id, 3, $calcObj);
                */
                $msg = $this->set_object_ext_val($id, 3, '[]');
                if ($msg != '') $this->log("科目:" . $name . ', 核算对象: ' . $msg);
                //是否结出余额  521 是, 522 否 
                $msg = $this->set_object_ext_val($id, 4, 522);
                if ($msg != '') $this->log("科目:" . $name . ', 是否结出余额: ' . $msg);
            }
            $this->moneyDb->end();
        }

        /*
        //管理费用
        $objTitle = '管理费用';
        $objId = 10098;
        $level = 2;
        $maxCode = $this->getMaxCode($objId, '6602.00');
        $q = (new sqler())->table('out_items')->where('ois_parent', 1)->order('ois_code');
        $rows = $this->crmLocal->select($q)->all();
        foreach ($rows as $row) {
            $crmKey = $row['ois_id'];
            $name = $row['ois_name'];
            $code = $row['ois_code'];
            if ($crmKey == '' || $name == '') {
                continue;
            }
            $item = $this->findLocalBycrm($objId, $crmKey);
            if (empty($item)) {
                $code = $code ?: $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > ' . $objTitle . ': <' . $name . '>';
                $this->log($msg);
            } else {
                $code = $item['o_code'];
                if ($item['o_name'] != $name) {
                    $msg = '更新科目名称 > ' . $objTitle . ': <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code, $name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId, $level, $code, $name, $keyword, $crmKey, empty($item));
            if (empty($id)) {
                $msg = "<'.$objTitle.'>未找到或者未生成objId, " . $code . ',' . $name;
                $this->log($msg);
            } else {
                //科目类别 > 损益
                $msg = $this->set_object_ext_val($id, 1, '损益');
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //余额方向 > 贷
                $msg = $this->set_object_ext_val($id, 2, 203); //203借 ，204贷
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //核算对象 1 部门, 2 职员, 3 客户
                if ($row['ois_require']) {
                    $calcObj = '[1,2]';
                } else {
                    $calcObj = '[1]';
                }
                $msg = $this->set_object_ext_val($id, 3, $calcObj);
                if ($msg != '') $this->log("科目:" . $name . ', 核算对象: ' . $msg);
                //是否结出余额  521 是, 522 否 
                $msg = $this->set_object_ext_val($id, 4, 522);
                if ($msg != '') $this->log("科目:" . $name . ', 是否结出余额: ' . $msg);
            }
            $this->moneyDb->end();
        }

        //销售费用
        $objTitle = '销售费用';
        $objId = 10099;
        $level = 2;
        $maxCode = $this->getMaxCode($objId, '6601.00');
        $q = (new sqler())->table('out_items')->where('ois_parent', 2)->order('ois_code');
        $rows = $this->crmLocal->select($q)->all();
        foreach ($rows as $row) {
            $crmKey = $row['ois_id'];
            $name = $row['ois_name'];
            $code = $row['ois_code'];
            if ($crmKey == '' || $name == '') {
                continue;
            }
            $item = $this->findLocalBycrm($objId, $crmKey);
            if (empty($item)) {
                $code = $code ?: $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > ' . $objTitle . ': <' . $name . '>';
                $this->log($msg);
            } else {
                $code = $item['o_code'];
                if ($item['o_name'] != $name) {
                    $msg = '更新科目名称 > ' . $objTitle . ': <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code, $name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId, $level, $code, $name, $keyword, $crmKey, empty($item));
            if (empty($id)) {
                $msg = "<'.$objTitle.'>未找到或者未生成objId, " . $code . ',' . $name;
                $this->log($msg);
            } else {
                //科目类别 > 损益
                $msg = $this->set_object_ext_val($id, 1, '损益');
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //余额方向 > 贷
                $msg = $this->set_object_ext_val($id, 2, 203); //203借 ，204贷
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //核算对象 1 部门, 2 职员, 3 客户
                if ($row['ois_require']) {
                    $calcObj = '[1,2]';
                } else {
                    $calcObj = '[1]';
                }
                $msg = $this->set_object_ext_val($id, 3, $calcObj);
                if ($msg != '') $this->log("科目:" . $name . ', 核算对象: ' . $msg);
                //是否结出余额  521 是, 522 否 
                $msg = $this->set_object_ext_val($id, 4, 522);
                if ($msg != '') $this->log("科目:" . $name . ', 是否结出余额: ' . $msg);
            }
            $this->moneyDb->end();
        }

        //其他业务成本
        $objTitle = '其他业务成本';
        $objId = 10768;
        $level = 2;
        $maxCode = $this->getMaxCode($objId, '6402.00');
        $q = (new sqler())->table('out_items')->where('ois_parent', 3)->order('ois_code');
        $rows = $this->crmLocal->select($q)->all();
        foreach ($rows as $row) {
            $crmKey = $row['ois_id'];
            $name = $row['ois_name'];
            $code = $row['ois_code'];
            if ($crmKey == '' || $name == '') {
                continue;
            }
            $item = $this->findLocalBycrm($objId, $crmKey);
            if (empty($item)) {
                $code = $code ?: $this->nextCode($maxCode);
                $maxCode = $code;
                $msg = '新增科目名称 > ' . $objTitle . ': <' . $name . '>';
                $this->log($msg);
            } else {
                $code = $item['o_code'];
                if ($item['o_name'] != $name) {
                    $msg = '更新科目名称 > ' . $objTitle . ': <' . $name . '>, 旧值: <' . $item['o_name'] . '>';
                    $this->log($msg);
                }
            }
            $keyword = $this->getKeyWord($code, $name);
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId, $level, $code, $name, $keyword, $crmKey, empty($item));
            if (empty($id)) {
                $msg = "<'.$objTitle.'>未找到或者未生成objId, " . $code . ',' . $name;
                $this->log($msg);
            } else {
                //科目类别 > 损益
                $msg = $this->set_object_ext_val($id, 1, '损益');
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //余额方向 > 贷
                $msg = $this->set_object_ext_val($id, 2, 203); //203借 ，204贷
                if ($msg != '') $this->log("科目:" . $name . ',' . $msg);
                //核算对象 1 部门, 2 职员, 3 客户
                if ($row['ois_require']) {
                    $calcObj = '[1,2]';
                } else {
                    $calcObj = '[1]';
                }
                $msg = $this->set_object_ext_val($id, 3, $calcObj);
                if ($msg != '') $this->log("科目:" . $name . ', 核算对象: ' . $msg);
                //是否结出余额  521 是, 522 否 
                $msg = $this->set_object_ext_val($id, 4, 522);
                if ($msg != '') $this->log("科目:" . $name . ', 是否结出余额: ' . $msg);
            }
            $this->moneyDb->end();
        }            
        */
        
        //部门
        $objId = 1;
        $level = 2;
        $q = (new sqler())->table('yja_department');
        $rows = $this->crmLocal->select($q)->all();
        foreach($rows as $row){
            $crmKey = $row['dep_id'];
            $code = $row['dep_code'];
            $name = $row['dep_name'];
            if($crmKey=='' || $name==''){
                continue;
            }
            $item = $this->findLocalBycrm($objId,$crmKey);
            //如果不存在或者编码和名称有变化, 则添加或者更新
            if(empty($item) || $code != $item['o_code'] || $name != $item['o_name']){
                    $keyword = $this->getKeyWord($code,$name);
                    if($this->saveToObject($objId,$level,$code,$name,$keyword,$crmKey,empty($item) ) ){
                    if(empty($item)){
                        $msg = '新增部门: <'.$code.'>,<'.$name.'>';
                        $this->log($msg);
                    }else{
                        if($item['o_name']!=$name){
                            $msg = '更新部门名称: <'.$name.'>, 旧值: <'.$item['o_name'].'>';
                            $this->log($msg);
                        }
                        if($item['o_name']!=$name){
                            $msg = '更新部门编码: <'.$code.'>, 旧值: <'.$item['o_code'].'>';
                            $this->log($msg);
                        }
                    }
                }else{
                    $error[$objId]['error'] = true; 
                    $msg = "<部门>未找到或者未生成objId, ".$code.','.$name;
                    $this->log($msg);
                }
            }
        }
        //业务员
        $objId = 2;
        $level = 2;
        $q = (new sqler())->table('do_user')
            ->leftJoin('yj_user',['u_jobnum'=>'@usr_jobnum'])
            ->where('do_user.usr_status',1)
            ->where('yj_user.u_status',1)
            ->group('usr_jobnum');
        $rows = $this->crmLocal->select($q)->all();
        foreach($rows as $row){
            $name = $row['usr_nickname'] ?: $row['u_name'];
            $jobnum = $row['usr_jobnum'] ?: $row['u_jobnum'];
            $crmKey = $name . $jobnum ?: '';
            $name = $name;
            $code = strtoupper($this->pinyin->abbr($name));
            if($crmKey=='' || $name==''){
                continue;
            }

            $item = $this->findLocalBycrm($objId,$crmKey);
            $keyword = $this->getKeyWord($code,$name).','.$row['u_jobnum'];
            $this->moneyDb->begin();
            $id = $this->saveToObject($objId,$level,$code,$name,$keyword,$crmKey,empty($item) );
            if(empty($id)){
                $error[$objId]['error'] = true; 
                $msg = "<业务员>未找到或者未生成objId, ".$code.','.$name;
            }else if($row['u_dep_id']){
                $depItem = $this->findLocalBycrm(1,$row['u_dep_id']);
                //部门
                $sql = qgprintf("REPLACE INTO object_ext_val SET oev_o_id ={:1} ,oev_oe_id =6,oev_type = 'int',oev_editor='radio',oev_int={:2}"
                    ,$id,$depItem['o_id']);
                $this->moneyDb->exec($sql);  
            }
            if(empty($item)){
                $msg = '新增业务员: <'.$name.'>';
                $this->log($msg);
            }else{
                if($item['o_name']!=$name){
                    $msg = '更新业务员: <'.$name.'>, 旧值: <'.$item['o_name'].'>';
                    $this->log($msg);
                }
            }
            $this->moneyDb->end();
        }

        $this->restLR();
    }
    

     /**
      * 
      * object_ext_val处理，返回是否修改的结果
      * @param int $o_id object's id
      * @param int $oe_id object_ext's id
      * @param mixed $value 值
      * @return string 返回新增或是修改了哪些内容
      */
    function set_object_ext_val($o_id,$oe_id,$value):string
    {
        $msg = '';
        $q = new sqler($this->moneyDb);
        $q->params([
            'o_id'=>$o_id,
            'oe_id'=>$oe_id,
            'oev_o_id'=>$o_id,
            'oev_oe_id'=>$oe_id,
            'value'=>$value
        ]);
        //对象表
        $objRow = $q->table('object')->where('?o_id')->row();

        //扩展配置表
        $extRow = $q->table('object_ext')->where('?oe_id')->row();
        if(empty($extRow)){
            return '没有找到 object_ext.o_id 的值 '.$o_id;
        }
        $type = $extRow['oe_type'];
        //扩展值表
        $extValueRow = $q->table('object_ext_val')->where('?oev_o_id,oev_oe_id')->row();
        $valueField = 'oev_' . $type;
        $data = [
            'oev_o_id' => $o_id,
            'oev_oe_id' => $oe_id,
            'oev_type' => $type,
            'oev_precision' => $extRow['oe_precision'],
            'oev_editor' => $extRow['oe_editor'],
            $valueField=> $value,
        ];

        $msgValue = $value;
        $msgOldValue = empty($extValueRow) ? '' : $extValueRow[ $valueField];
        if($type=='int' && in_array($oe_id,[2,4,5,6])){
            $msgValue = $q->table('object')->field('o_name')->where('o_id',$msgValue)->one();
            $msgOldValue = $q->table('object')->field('o_name')->where('o_id',$msgOldValue)->one();
        }else if($oe_id==3){
            if($msgValue){
                $arr = json_decode($msgValue,true);
                foreach($arr as $k=>$v){
                    $arr[$k] = $q->table('object')->field('o_name')->where('o_id',$v)->one();
                }
                $msgValue = implode(',',$arr);
            }
            if($msgOldValue){
                $arr = json_decode($msgOldValue,true);
                foreach($arr as $k=>$v){
                    $arr[$k] = $q->table('object')->field('o_name')->where('o_id',$v)->one();
                }
                $msgOldValue = implode(',',$arr);
            }
        }

        $q->table('object_ext_val')->where('?oev_o_id,oev_oe_id')->set($data);
        if(empty($extValueRow)){
            $this->moneyDb->insert($q);
            $msg = $objRow['o_name'].' > 新增属性['.$extRow['oe_name'].']: <'.$msgValue.'>';
        }else if($extValueRow[$valueField]!=$value){
            //不更新: 科目类别 ，余额方向不更新, 是否结出余额
            //更新: 核算对象, 销售收入计入
            if($oe_id==4 || $oe_id==5){
                $this->moneyDb->update($q);
                $msg = $objRow['o_name'].' > 更新属性['.$extRow['oe_name'].']: <'.$msgValue.'>';
                $msg .= ', 旧值: "'.$msgOldValue.'"';
            }
        }
        return $msg;
    }


     /**
     * 重构树的左右值
     */
    function restLR()
    {
        $libCfg = require(realpath(DIR_COMPOSER.'/../../config/').'/database.php');
        $GLOBALS['mainDbWriteLink'] = new LibDb($libCfg['db']['main_write']);
        $GLOBALS['mainDbReadLink'] = $GLOBALS['mainDbWriteLink'];
        $tree = new LibLRTree('root','object',
            [ //树结构字段
                'fieldId'=> 'o_id',
                'fieldParentId'=> 'o_parent_id',
                'fieldCode'=> 'o_code',
                'fieldText'=> 'o_name',
                'fieldKeyword'=> 'o_keyword',
                'fieldLevel'=> 'o_level',
                'fieldLft'=> 'o_lft',
                'fieldRgt'=> 'o_rgt',
                'fieldSort'=> 'o_sort', //排序字段
                'order'=> 'o_level,o_sort,o_code,o_name' //排序方式
        ]);
        $tree->restLR();
    }   

    /**
     * 找本地object是否有crm对应的记录 
     * @param int $parentID object表中的父ID,例 1 部门, 2 职员
     * @param string crm 表中的关键字段值
     */
    function findLocalBycrm(int $parentID, string $crmKey):array
    {
        $q = (new sqler())->table('`object`')
        ->where('o_parent_id',$parentID)
        ->where('o_crm_key',$crmKey)
        ;
        return $this->moneyDb->select($q)->row();
    }

    /**
     * 找本地object是否有 code 对应的记录 
     */
    function findLocalByCode(string $code):array
    {
        $q = (new sqler())->table('`object`')
        ->where('o_code',$code)
        ;
        return $this->moneyDb->select($q)->row();
    }
    
	/**
	 * 获取 keyword
	 */
	function getKeyWord($code,$name){
    	$result = strtoupper($name.','.$this->pinyin->abbr($name).','.$this->pinyin->permalink($name,''));
        if($code) $result = $code.','.$result;
        return $result;
    }
    
	/**
	 * 
     * 保存
     * @param bool $isNew 是否为新记录
     * @return bool int 如果成功则返回记录的ID, 失败返回0
	 */
	function saveToObject($objId,$level,$code,$name,$keyword,$crmKey,bool $isNew,$othFieldsData=[]):int
    {
        $result = 0;
        $q = (new sqler())->table('`object`')
        ->set('o_parent_id',$objId)
        ->set('o_code',$code)
        ->set('o_name',$name)
        ->set('o_keyword',$keyword)
        ->set('o_level',$level)
        ->set('o_crm_key',$crmKey)
        ;
        foreach($othFieldsData as $k=>$v){
            $q->set($k,$v);
        }
        if($isNew){
            if($this->moneyDb->insert($q)){
                $result = $this->moneyDb->insertId();
            }
        }else{
            $q->where('o_parent_id',$objId)->where('o_crm_key',$crmKey);
            $this->moneyDb->update($q);
            $q->field('o_id');
            $result = $this->moneyDb->select($q)->one();
        }
        return $result;
    }

	function nextCode($maxCode){
        $val = $maxCode;
        $pos = -1;
        for($i=strlen($val)-1;$i>=0;$i--){
            if(is_numeric($val[$i])){
                $pos = $i;
            }
            else
                break;
        }
        if($pos==-1){
            //如果是字母,得到最后一个字母上ascii码+1的字符，如果最后一个字母是Z，刚在整编码后加字符串 1
            $c = substr($val,-1,1);
            if(ord($c)<90)
                $code = chr(ord($c)+1);
            else
                $code = $c.'1';
        }else{
            //如果是数字
            $num= substr($val,$pos,strlen($val));
            $seq = ltrim($num,'0');
            $seq = empty($seq) ? 0 : $seq;
            $seq = $seq * 1 + 1;
            $code = str_pad($seq, strlen($num), "0", STR_PAD_LEFT);
        }
        $val = substr($val,0,$pos) . $code;
        return $val;
	}    

	function getMaxCode($objId,$default='0001'){
        $q = (new sqler())->table('`object`')
        ->field('@MAX(o_code)','max_code')
        ->where('o_parent_id',$objId)
        ;
        $result = $this->moneyDb->select($q)->one();
        if(empty($result)) $result = $default;
        return $result;
	}    
}
/**执行 */
new getCrm();