";
    echo "rows";
    //print_r($rows);
    //exit;
    $total = 4335;
    for($i=1;$i<=$total;$i++){
        $sql = "select * from lbx_cainiaowarehouse where 1 and status='0' and optype in('收货入库','2B单出库') and kucunchenci='仓储物理库存' GROUP BY lbxrelcode limit 1;";
        //$sql = "select * from lbx_cainiaowarehouse where 1 and lbxrelcode='LBX0221375890146614' and optype in('收货入库','2B单出库') GROUP BY lbxrelcode limit 1;";
        echo $sql."\r\n";
        $rows = $db->fetchRow($sql);
        print_r($rows);
        //exit;
        if(!empty($rows)){
            creatediff($rows);
        }else{
            echo $i."无对比数据"."\r\n";
        }
        //exit;
        //exit;
    }
    /*if(!empty($rows)){
        foreach ($rows as $k=>$row){
            creatediff($row);
            //exit;
        }
    }*/
}

//execreatediff();
function jiluLbx(){
    $db = Common_Common::getAdapter();
    $sql = "SELECT count(*) FROM lbx_cainiaowarehouse WHERE `status`=0 AND kucunchenci='仓储物理库存'";
    $total = $db->fetchOne($sql);
    $page = ceil($total/1000);
    for($i=1;$i<=$page;$i++){
        $listSql = "SELECT * FROM lbx_cainiaowarehouse WHERE `status`=0 AND kucunchenci='仓储物理库存' limit ".(($i-1)*1000).',1000';
        $list = $db->fetchAll($listSql);
        echo $i;
        echo PHP_EOL;
        foreach ($list as $li){
            if($li['lbxrelcode']) {
                echo $li['lbxrelcode'];
                echo PHP_EOL;
                $lbxSql = "select * from qg_detail_cainiao where lbx='" . $li['lbxrelcode'] . "'";
                $lbxInfo =$db->fetchRow($lbxSql);
                if(empty($lbxInfo)){
                    $luruLbx = $db->fetchRow("select * from lbx_cainiao where ru_lbx='".$li['lbxrelcode']."'");
                    if($luruLbx) {
                            $db->update('lbx_cainiaowarehouse', [
                                'jilu_code'=>$li['lbxrelcode'],
                            ],'id='.$li['id']);
                    }
                }
            }

        }
    }
}
// jiluLbx();

//检查lbx的货品数量和通关子单里面的lbx里面的数量
function checklbxgroup(){
    $db = Common_Common::getAdapter();
    $sql = "select * from lbx_cainiaowarehousegrop ";
    $wareRows = $db->fetchAll($sql);
    if(!empty($wareRows)){
        foreach ($wareRows as $k=>$rows){
            $lbx = $rows['lbxrelcode'];
            $sqlbx = "c";
            $lbxRows = $db->fetchAll($sqlbx);
            $lbxhuopin = [];
            foreach ($lbxRows as $klb=>$rlb){
                $outid = $rlb['outid']+0;
                if(!in_array($outid,$lbxhuopin)){
                    $lbxhuopin[] = $outid;
                }
            }
            $lbxhuopin = array_unique($lbxhuopin);
            sort($lbxhuopin);
            //查找qg_detail_cainiao
            $sqlqg = "select * from qg_detail_cainiao where lbx='$lbx';";
            $qgRows = $db->fetchAll($sqlqg);
            $cbCodes = [];
            if(!empty($qgRows)){
                $qghuopin = [];
                foreach ($qgRows as $kg=>$rg){
                    $huopinid = $rg['huopinid']+0;
                    $cbcode = $rg['cbCode'];
                    if(!in_array($huopinid,$qghuopin)){
                        $qghuopin[] = $huopinid;
                    }
                    if(!in_array($cbcode,$cbCodes)){
                        $cbCodes[] = $cbcode;
                    }
                }
                $qghuopin = array_unique($qghuopin);
                sort($qghuopin);
                sort($cbCodes);
            }
            //查关务记录的数据
            $sqljilu = "select * from lbx_cainiao where 1 and ru_lbx='$lbx';";
            echo $sqljilu;
            $rowjilu = $db->fetchAll($sqljilu);
            $jiluCbs = [];
            if(!empty($rowjilu)){
                foreach ($rowjilu as $kr=>$vkr){
                    $jilucbcode = $vkr['cb_code'];
                    if(!in_array($jilucbcode,$jiluCbs)){
                        $jiluCbs[]=$jilucbcode;
                    }
                }
                sort($jiluCbs);
            }


            $upRow = array(
                'lbxhuopinid'=>!empty($lbxhuopin)?implode("|",$lbxhuopin):"",
                'cbhuopinid'=>!empty($qghuopin)?implode("|",$qghuopin):"",
                'guanwucbcode'=>!empty($cbCodes)?implode("|",$cbCodes):"",
                'jilucbcode'=>!empty($jiluCbs)?implode("|",$jiluCbs):"",
            );
            $db->update("lbx_cainiaowarehousegrop",$upRow,"lbxrelcode='$lbx'");
            echo $lbx."over \r\n";
        }
    }
}

//checklbxgroup();

class diffbycb{

    function getCBs(){
        $db = Common_Common::getAdapter();
        $sql = "select * from lbx_cainiao";
        $rows = $db->fetchAll($sql);
        /*if(!empty($rows)){

        }*/
    }
    function checkcb(){

    }
    function getCb($row){
        //找到所有

    }
}

/*`shenjinlbx` varchar(255) DEFAULT NULL,
  `guanwulbx` varchar(255) DEFAULT NULL,
  `sjcbcodes` varchar(255) DEFAULT NULL,
  `sjnumber` varchar(255) DEFAULT NULL,
  `gwcbcodes` varchar(255) DEFAULT NULL,
  `gwjilunumber` varchar(255) DEFAULT NULL,
  `sjuserqdc_ids` text NOT NULL,
  `gwusergdc_ids` text NOT NULL,
  `msg` text,*/


//获取神鲸lbx
function getsjlbx($row,$db){
    $lbx = $row["lbx"];
    $result = array(
        'msg'=>'qg_detail_cainiao的lbx无数据',
        'data'=>[],
    );
    $huopinid = $row['huopinid'];
    if(!empty($lbx)){
        $sql = "select * from qg_detail_cainiao where lbx='$lbx'";
        $rows = $db->fetchAll($sql);
        $lbxs = [];
        if(!empty($rows)){
            $cb = [];
            foreach ($rows as $k=>$row){
                if(!in_array($row['lbx'],$lbxs)){
                    $lbxs[] = $row['lbx'];
                }
                $qghuopinid = $row['huopinid'];
                if(isset($cb[$qghuopinid])){
                    $cb[$qghuopinid]['shuliang'] = $cb[$qghuopinid]['shuliang']+$row['shuliang'];
                    $cb[$qghuopinid]['qdc_ids'][] =  $row['qdc_id'];
                    //$cb['']
                }else{
                    $cb[$qghuopinid]['shuliang'] = $row['shuliang'];
                    $cb[$qghuopinid]['qdc_ids'][] =  $row['qdc_id'];
                }
            }
            asort($lbxs);
            $result['data']['lbxs'] = $lbxs;
            $result['data']['cb'] = $cb;
        }
    }
    return $result;
}

function dealwarehousesheet(){
    /*require_once("PHPExcel.php");
    require_once("PHPExcel/Reader/Excel2007.php");
    require_once("PHPExcel/Reader/Excel5.php");
    $fileName = "F:/baiducloud/百度云同步盘/工作电脑2016/F/工作文档/进口系统/2018年/沙田/处理/2021年/09月/15/处理明细/";
    $phpExcel = PHPExcel_IOFactory::load($fileName);
    $sheet0 = $phpExcel->getSheet(4);*/
    try{
        $db = Common_Common::getAdapter();
        $sql = "select * from lbx_sumtoshenjin where msg is not null;";
        $rows = $db->fetchAll($sql);
        if(!empty($rows)){
            foreach ($rows as $k=>$row){
                $lbx = getsjlbx($row,$db);

            }
        }
    }catch (Exception $e){

    }
    echo "run over";


}

//校验实际数据的错误情况
class checkCB{
    //校验一个lbx在多个通关子单里面
    function checkrulbxgroup(){
        try {
            $db = Common_Common::getAdapter();
            $sql = "select * from lbx_cainiao_rulbxgroup;";
            $rows = $db->fetchAll($sql);
            if(!empty($rows)){
                $k =1;
                foreach ($rows as $k=>$r){
                    $ids = $r['gcid'];
                    $msg = $this->checkidRow($db,$ids,$r['id']);
                    if(!empty($msg)){
                        var_dump($msg);
                    }else{
                        echo $r['id']."check ok{$k};\r\n";
                    }
                    $k++;
                }
            }
        }catch (Exception $e){
            var_dump($e->getMessage());
        }
    }
    //校验一个lbx在多个通关子单里面
    function checkidRow($db,$ids,$lbxgid){
        $idRow = explode($ids,",");
        $msg=[];
        if(!empty($idRow)){
            foreach ($idRow as $k=>$id){
                $sql = "select * from lbx_cainiao_rulbxgroup where id!=$lbxgid";
                $exist = $this->checkloop($db,$sql,$id);
                if(!empty($exist)){
                    $msg[] = $id."在lbx_cainiao_rulbxgroup存在".implode(",",$exist);
                }
                $sql = "select * from lbx_cainiao_rucbgroup";
                $exist = $this->checkloop($db,$sql,$id);
                if(!empty($exist)){
                    $msg[] = $id."在lbx_cainiao_rucbgroup存在".implode(",",$exist);
                }
            }
        }
        return $msg;
    }
    function checkloop($db,$sql,$id){
        $rows = $db->fetchAll($sql);
        $exist = [];
        if(!empty($row)){
            foreach ($rows as $k=>$row){
                $gcid = $row['gcid'];
                $gcids = explode(",",$gcid);
                if(in_array($id,$gcids)){
                    $exist[] = $row['id'];
                }
            }
        }
        return $exist;
    }

    //校验一个cb多个lbx的情况
    function checkrucbgroup(){
        try {
            $db = Common_Common::getAdapter();
            $sql = "select * from lbx_cainiao_rucbgroup;";
            $rows = $db->fetchAll($sql);
            if(!empty($rows)){
                $k =1;
                foreach ($rows as $k=>$r){
                    $ids = $r['gcid'];
                    $msg = $this->checkcbidRow($db,$ids,$r['id']);
                    if(!empty($msg)){
                        var_dump($msg);
                    }else{
                        echo $r['id']."check ok{$k};\r\n";
                    }
                    $k++;
                }
            }
        }catch (Exception $e){
            var_dump($e->getMessage());
        }
    }
    //校验一个lbx在多个通关子单里面
    function checkcbidRow($db,$ids,$lbxgid){
        $idRow = explode($ids,",");
        $msg=[];
        if(!empty($idRow)){
            foreach ($idRow as $k=>$id){
                $sql = "select * from lbx_cainiao_rucbgroup where id!=$lbxgid";
                $exist = $this->checkloop($db,$sql,$id);
                if(!empty($exist)){
                    $msg[] = $id."在lbx_cainiao_rulbxgroup存在".implode(",",$exist);
                }
                $sql = "select * from lbx_cainiao_rulbxgroup";
                $exist = $this->checkloop($db,$sql,$id);
                if(!empty($exist)){
                    $msg[] = $id."在lbx_cainiao_rulbxgroup存在".implode(",",$exist);
                }
            }
        }
        return $msg;
    }
}

//生成一个lbx在多个通关子单里面
function lbx_rulbx_differ() {
    //一个LBX对用多个Cb
    $db = Common_Common::getAdapter();
    $list = $db->fetchAll("select * from lbx_cainiao_rulbxgroup");
    foreach ($list as $item) {
        $lbx = $item['ru_lbx'];
        $lbxId = $item['gcid'];
        $cbList = $db->fetchAll("select * from lbx_cainiao where id in(" . $lbxId . ")");
        $cbCodeList = [];
        foreach ($cbList as $cb) {
            $cbCodeList[] = $cb['cb_code'];
        }
        //先按仓库的lbx对比
        $warehouseLbx = $db->fetchAll("select * from lbx_sumtoshenjin where lbx='" . $lbx . "'");
        $qgDetail = "select *,sum(shuliang) as totalShui,group_concat(qdc_id) as gidTotal,group_concat(cbCode) as cbTotal,group_concat(lbx) lbxTotal  from qg_detail_cainiao where cbCode in('".implode("','",$cbCodeList)."') group by huopinid";
        $qgDetailList = $db->fetchAll($qgDetail);
        $qgFormart=[];
        $gLbx=[];
        foreach ($qgDetailList as $gitem){
            $qgFormart[$gitem['huopinid']]=$gitem;
            $gLbx[]=$gitem['lbxTotal'];
        }
        $glbxList=explode(",",implode(",",$gLbx));
        $glbxList = array_unique($glbxList);

        $warehouseLbxCopy =[];
        foreach ($warehouseLbx as $w){
            $warehouseLbxCopy[$w['huopinid']]=$w;
        }
        foreach ($warehouseLbx as $witem) {
            echo $witem['huopinid'];
            echo PHP_EOL;
            if(isset($qgFormart[$witem['huopinid']])) {
                $gItem = $qgFormart[$witem['huopinid']];
                $insert = [
                    'biao' => 'lbx_cainiao_rulbxgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => $witem['lbx'],
                    'c_good_id' => $witem['huopinid'],
                    'c_good_num' => $witem['sumnumber'],
                    'c_id_list' => $witem['ls_id'],
                    'g_lbx'=>implode(",",$glbxList),
                    'g_cb_list' => implode(",",array_unique(explode(',',$gItem['cbTotal']))),
                    'g_id_list' => $gItem['gidTotal'],
                    'g_goods_num' => $gItem['totalShui'],
                    'g_good_id' => $witem['huopinid'],
                    'add_time'=>$witem['createtime'],
                ];
                $db->insert('lbx_rulbx_differ', $insert);
                unset($qgFormart[$witem['huopinid']]);
            }else{
                $insert = [
                    'biao' => 'lbx_cainiao_rulbxgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => $witem['lbx'],
                    'c_good_id' => $witem['huopinid'],
                    'c_good_num' => $witem['sumnumber'],
                    'c_id_list' => $witem['ls_id'],
                    'g_lbx'=>implode(",",$glbxList),
                    'g_cb_list' => '',
                    'g_id_list' => '',
                    'g_goods_num' => '',
                    'g_good_id' => '',
                    'add_time'=>$witem['createtime'],
                ];
                $db->insert('lbx_rulbx_differ', $insert);
            }
        }
        if(!empty($qgFormart)){
            foreach ($qgFormart as $fitem){
                $insert = [
                    'biao' => 'lbx_cainiao_rulbxgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => '',
                    'c_good_id' => '',
                    'c_good_num' => '',
                    'c_id_list' => '',
                    'g_lbx'=>implode(",",$glbxList),
                    'g_cb_list' => implode(",",array_unique(explode(',',$fitem['cbTotal']))),
                    'g_id_list' => $fitem['gidTotal'],
                    'g_goods_num' => $fitem['totalShui'],
                    'g_good_id' => $fitem['huopinid']
                ];
                $db->insert('lbx_rulbx_differ', $insert);
            }
        }
    }
}
// lbx_rulbx_differ();
//生成一个通关子单存在多个lbx
function lbx_cainiao_rucbgroup() {
    //一个cb对应多个lbx
    $db = Common_Common::getAdapter();
    $list = $db->fetchAll("select * from lbx_cainiao_rucbgroup");
    foreach ($list as $item) {
        $lbx = $item['ru_lbx'];
        $lbxId = $item['gcid'];
        $cbList = $db->fetchAll("select * from lbx_cainiao where id in(" . $lbxId . ")");
        $cbCodeList = [];
        $lbxs = [];
        foreach ($cbList as $cb) {
            $cbCodeList[] = $cb['cb_code'];
            $lbxs[] = $cb['ru_lbx'];
        }
        $sql = "select *,sum(sumnumber) sumnumbertotal,group_concat(lbx) c_lbx,group_concat(ls_id) c_id_list from lbx_sumtoshenjin where lbx in('".implode("','",$lbxs)."') GROUP BY huopinid";
        //先按仓库的lbx对比
        $warehouseLbx = $db->fetchAll($sql);

        $qgDetail = "select *,sum(shuliang) as totalShui,group_concat(qdc_id) as gidTotal,group_concat(cbCode) as cbTotal,group_concat(lbx) lbxTotal  from qg_detail_cainiao where cbCode in('".implode("','",$cbCodeList)."') group by huopinid";
        $qgDetailList = $db->fetchAll($qgDetail);
        $qgFormart=[];
        $gLbx=[];
        foreach ($qgDetailList as $gitem){
            $qgFormart[$gitem['huopinid']]=$gitem;
            $gLbx[]=$gitem['lbxTotal'];
        }
        $glbxList=explode(",",implode(",",$gLbx));
        $glbxList = array_unique($glbxList);
        $warehouseLbxCopy =[];
        foreach ($warehouseLbx as $w){
            $warehouseLbxCopy[$w['huopinid']]=$w;
        }
        foreach ($warehouseLbx as $witem) {
            echo $witem['huopinid'];
            echo PHP_EOL;
            if(isset($qgFormart[$witem['huopinid']])) {
                $gItem = $qgFormart[$witem['huopinid']];
                $c_lbx = array_unique(explode(',',$witem['c_lbx']));
                sort($c_lbx);
                $insert = [
                    'biao' => 'lbx_cainiao_rucbgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => implode(",",$c_lbx),//$witem['lbx'],
                    'c_good_id' => $witem['huopinid'],
                    'c_good_num' => $witem['sumnumbertotal'],
                    'c_id_list' => implode(",",array_unique(explode(',',$witem['c_id_list']))),//$witem['ls_id'],
                    'g_cb_list' => implode(",",array_unique(explode(',',$gItem['cbTotal']))),
                    'g_lbx'=>implode(",",$glbxList),
                    'g_id_list' => $gItem['gidTotal'],
                    'g_goods_num' => $gItem['totalShui'],
                    'g_good_id' => $witem['huopinid'],
                    'add_time'=>$witem['createtime'],
                ];
                $db->insert('lbx_rulbx_differ', $insert);
                unset($qgFormart[$witem['huopinid']]);
            }else{
                $c_lbx = array_unique(explode(',',$witem['c_lbx']));
                sort($c_lbx);
                $insert = [
                    'biao' => 'lbx_cainiao_rucbgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => implode(",",$c_lbx),//$witem['lbx'],
                    'c_good_id' => $witem['huopinid'],
                    'c_good_num' => $witem['sumnumbertotal'],
                    'g_lbx'=>implode(",",$glbxList),
                    'c_id_list' => implode(",",array_unique(explode(',',$witem['c_id_list']))),//$witem['ls_id'],
                    'g_cb_list' => '',
                    'g_id_list' => '',
                    'g_goods_num' => '',
                    'g_good_id' => '',
                    'add_time'=>$witem['createtime'],
                ];
                $db->insert('lbx_rulbx_differ', $insert);
            }
        }
        if(!empty($qgFormart)){
            foreach ($qgFormart as $fitem){
                $insert = [
                    'biao' => 'lbx_cainiao_rucbgroup',
                    'biao_id' => $item['id'],
                    'c_lbx' => '',
                    'c_good_id' => '',
                    'c_good_num' => '',
                    'c_id_list' => '',
                    'g_lbx'=>implode(",",$glbxList),
                    'g_cb_list' => implode(",",array_unique(explode(',',$fitem['cbTotal']))),
                    'g_id_list' => $fitem['gidTotal'],
                    'g_goods_num' => $fitem['totalShui'],
                    'g_good_id' => $fitem['huopinid']
                ];
                $db->insert('lbx_rulbx_differ', $insert);
            }
        }
    }
}
function checklbxDiff(){
    $db = Common_Common::getAdapter();
    $total= $db->fetchOne("select count(*) from lbx_rulbx_differ");
    $pageSize = 1000;
    $totalPage = ceil($total/$pageSize);
    for($i=1;$i<=$totalPage;$i++){
        $listSql = "SELECT * FROM lbx_rulbx_differ  limit ".(($i-1)*1000).',1000';
        $listData = $db->fetchAll($listSql);
        foreach($listData as $item){
            $id=$item['c_id_list'];
            $idList = explode(',',$id);
            echo $item['id'];
            echo PHP_EOL;
            $checkResult = checkId($item['id'],$idList);
            if($checkResult){
                $db->update('lbx_rulbx_differ',[
                    'c_status'=>1
                ],'id='.$item['id']);
            }
        }
    }
}
function checkId($differId,$idList){
    $db = Common_Common::getAdapter();
    foreach ($idList as $id){
        $check ="select * from lbx_rulbx_differ where c_id_list like '%{$id}%' and id!={$differId}";
        echo $check;
        $checkList = $db->fetchAll($check);
        if(!empty($checkList)){
            foreach ($checkList as $checkItem){
                    $idExplode = explode(',',$checkItem['c_id_list']);
                    if(in_array($id,$idExplode)){
                        return true;
                    }
            }
        }
    }
    return false;
}
//checklbxDiff();
/*$checkcb = new checkCB();
//$checkcb->checkrulbxgroup();
$checkcb->checkrucbgroup();*/
lbx_rulbx_differ();
lbx_cainiao_rucbgroup();