error = '恒成塑业数据库连接用户参数不能为空!'; return; } //获取用友账号密码 $emp = Employee::where('id', $user_id['id'])->select('sqlserver_account', 'sqlserver_password')->first(); if (empty($emp) || empty($emp->sqlserver_account)) { $this->error = '恒成塑业连接构造失败,未找到账号对应的用友账号信息'; return; } $this->host = env('Yongyou_database_ip'); $this->port = env('Yongyou_database_port'); $this->host_api = env('Yongyou_api_ip'); $this->database = env('Yongyou_database'); //映射ip是否通畅 $bool = $this->isHostReachable($this->host); if(! $bool) { $this->error = $this->host . "连接不可达,请稍后重新操作!"; return; } //用友接口统一登录账号密码 $this->sUserID = $emp->sqlserver_account ?? ''; $this->sPassword = $emp->sqlserver_password ?? ''; $this->url = $this->host_api . "/U8Sys/U8API"; $this->createConnection(); } catch (\Throwable $e) { $this->error = $e->getMessage(); } } private function createConnection() { // 主数据库连接 $mainConnName = 'sqlsrv_main_' . uniqid(); $mainConfig = [ 'driver' => 'sqlsrv', 'host' => $this->host, 'port' => $this->port, 'database' => $this->database, 'username' => env('SQLSRV_USERNAME'), 'password' => env('SQLSRV_PASSWORD'), 'options' => [ // \PDO::ATTR_TIMEOUT => 30, // 查询超时30秒 \PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 30, // SQL Server专用超时 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, // \PDO::ATTR_PERSISTENT => false, // 重要:禁用持久连接 ], ]; Config::set("database.connections.{$mainConnName}", $mainConfig); $this->db = DB::connection($mainConnName); // 测试连接有效性 $this->validateConnection($this->db); } private function validateConnection($connection) { try { $pdo = $connection->getPdo(); $stmt = $pdo->prepare("SELECT 1 AS connection_test"); $stmt->execute(); $result = $stmt->fetch(\PDO::FETCH_ASSOC); if (empty($result) || $result['connection_test'] != 1) { $this->error = "数据库连接失败"; } } catch (\Throwable $e) { $this->error = "数据库连接验证失败: " . $e->getMessage(); } } public function __destruct() { // 主动关闭连接 $this->safeDisconnect($this->db); } private function safeDisconnect(&$connection) { try { if ($connection instanceof \Illuminate\Database\Connection) { // 物理断开连接 $connection->disconnect(); // 清除连接引用 $connection = null; // Log::channel('sendData')->info('动作', ["param" => "执行了析构"]); } } catch (\Throwable $e) { // 静默处理断开错误 Log::channel('sendData')->info('错误', ["param" => $e->getMessage()]); } } public function is_same_month($timestamp1, $timestamp2) { // 格式化时间戳为年份和月份 $year1 = date('Y', $timestamp1); $month1 = date('m', $timestamp1); $year2 = date('Y', $timestamp2); $month2 = date('m', $timestamp2); if ($year1 === $year2 && $month1 === $month2) { return true; } else { return false; } } //获取数据(点击引入) public function getDataFromSqlServer($data) { if (!empty($this->error)) return [false, $this->error, '']; if (empty($data['out_order_no_time'][0]) || empty($data['out_order_no_time'][1])) return [false, '制单日期不能为空!', '']; $bool = $this->is_same_month($data['out_order_no_time'][0], $data['out_order_no_time'][1]); if (!$bool) return [false, '制单日期必须同月!', '']; //查询产品主表副表数据 $start = date('Y-m-d H:i:s.000', $data['out_order_no_time'][0]); $end = date('Y-m-d H:i:s.000', $data['out_order_no_time'][1]); $model = $this->db->table('SO_SOMain as a') ->leftJoin('SO_SODetails as b', 'b.cSOCode', 'a.cSOCode') ->whereBetween('a.dDate', [$start, $end]) ->whereNotNull('a.cVerifier') ->select('a.cSOCode as out_order_no', 'a.dDate as out_order_no_time', 'a.cCusCode as customer_no', 'a.cCusName as customer_name', 'a.cMemo as table_header_mark', 'a.cMaker as out_crt_man', 'a.cVerifier as out_checker_man', 'a.dverifydate as out_checker_time', 'b.cInvCode as product_no', 'b.iQuantity as order_quantity', 'b.cDefine28 as technology_material', 'b.cFree1 as technology_name', 'b.cFree2 as wood_name', 'b.cDefine30 as process_mark', 'b.cMemo as table_body_mark', 'b.iTaxUnitPrice as price','b.dPreDate as pre_shipment_time'); if (!empty($data['out_order_no'])) $model->where('a.cSOCode', 'LIKE', '%' . $data['out_order_no'] . '%'); if (!empty($data['customer_no'])) $model->where('a.cCusCode', 'LIKE', '%' . $data['customer_no'] . '%'); if (!empty($data['customer_name'])) $model->where('a.cCusName', 'LIKE', '%' . $data['customer_name'] . '%'); if (!empty($data['table_header_mark'])) $model->where('a.cMemo', 'LIKE', '%' . $data['table_header_mark'] . '%'); if (!empty($data['out_crt_man'])) $model->where('a.cMaker', 'LIKE', '%' . $data['out_crt_man'] . '%'); if (!empty($data['out_checker_man'])) $model->where('a.cVerifier', 'LIKE', '%' . $data['out_checker_man'] . '%'); if (!empty($data['out_checker_time'][0]) && !empty($data['out_checker_time'][1])) { $start1 = date('Y-m-d H:i:s.000', $data['out_checker_time'][0]); $end1 = date('Y-m-d H:i:s.000', $data['out_checker_time'][1]); $model->whereBetween('a.dverifydate', [$start1, $end1]); } if (!empty($data['pre_shipment_time'][0]) && !empty($data['pre_shipment_time'][1])) { $start1 = date('Y-m-d H:i:s.000', $data['pre_shipment_time'][0]); $end1 = date('Y-m-d H:i:s.000', $data['pre_shipment_time'][1]); $model->whereBetween('b.dPreDate', [$start1, $end1]); } $result = $model->get()->toArray(); if (empty($result)) return [false, '暂无数据,更新结束!', '']; list($status, $msg) = $this->orderRule($result); if (empty($msg)) return [false, '暂无数据,更新结束!', '']; $result = $msg; //查询附带的一些信息(比较少) $product_no = array_filter(array_column($result, 'product_no')); $chunkSize = 1000; // 每个子集的大小 $chunks = array_chunk($product_no, $chunkSize); // 将原始数组拆分成多个较小的子数组 $results = []; // 存储查询结果的数组 foreach ($chunks as $chunk) { $tmp = $this->db->table('Inventory as a') ->join('ComputationUnit as b', function ($join) { $join->on('a.cGroupCode', '=', 'b.cGroupCode') ->on('a.cComUnitCode', '=', 'b.cComUnitCode'); }, null, null, 'left') ->whereIn('a.cInvCode', $chunk) ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit') ->get() ->toArray(); $results = array_merge($results, $tmp); // 将每个子集的结果合并到总结果数组中 } $messageMap = array_column($results, null, 'product_no'); unset($results); //现存量查询开始 ---组织查询条件 $args = ''; foreach ($result as $value) { $product = $value->product_no; $technology_name = $value->technology_name ?? ''; // $wood_name = $value->wood_name ?? ''; $args .= "(a.cInvCode = '{$product}' and a.cFree1 = '{$technology_name}') OR "; } $args = rtrim($args, 'OR '); $messageTwo = $this->db->table('CurrentStock as a') ->leftJoin('Warehouse as b', 'b.cWhCode', 'a.cWhCode') ->whereRaw("($args)") ->where('a.iQuantity', '>', 0) ->select('a.iQuantity as product_quantity_on_hand', 'a.cInvCode as product_no', 'a.cFree1 as technology_name', 'a.cFree2 as wood_name', 'b.cWhName as warehouse_name') ->get()->toArray(); if (!empty($messageTwo)) { foreach ($messageTwo as $key => $value) { $messageTwo[$key] = (array)$value; } } //现存量查询结束 foreach ($result as $key => $value) { $result[$key]->technology_material = $value->technology_material ?? ''; $result[$key]->technology_name = $value->technology_name ?? ''; $result[$key]->wood_name = $value->wood_name ?? ''; $result[$key]->process_mark = $value->process_mark ?? ''; $result[$key]->table_body_mark = $value->table_body_mark ?? ''; $result[$key]->table_header_mark = $value->table_header_mark ?? ''; $keys = $value->product_no . $value->technology_name . $value->wood_name; $result[$key]->out_order_no_time = $value->out_order_no_time ? strtotime($value->out_order_no_time) : 0; $result[$key]->out_checker_time = $value->out_checker_time ? strtotime($value->out_checker_time) : 0; $result[$key]->pre_shipment_time = $value->pre_shipment_time ? strtotime($value->pre_shipment_time) : 0; $result[$key]->product_title = $messageMap[$value->product_no]->product_title ?? ''; $result[$key]->product_size = $messageMap[$value->product_no]->product_size ?? ''; $result[$key]->product_unit = $messageMap[$value->product_no]->product_unit ?? ''; $result[$key] = (array)$value; } return [true, $result, $messageTwo]; } public function orderRule($data) { $result = Orders::where('del_time', 0) ->whereIn('out_order_no', array_column($data, 'out_order_no')) ->select('out_order_no') ->get()->toArray(); $out_order_no = array_column($result, 'out_order_no'); if (!empty($out_order_no)) { foreach ($data as $key => $value) { if (in_array($value->out_order_no, $out_order_no)) { unset($data[$key]); } } } return [true, $data]; } //获取数据(刷新现存量) public function getDataFromSqlServerForOnHand($data) { if (!empty($this->error)) return [false, $this->error, '']; if (empty($data['id'])) return [false, '数据不能为空!', '']; $product = SaleOrdersProduct::whereIn('id', $data['id']) ->select('product_no', 'technology_name') ->get()->toArray(); //现存量查询开始 ---组织查询条件 $args = ''; foreach ($product as $value) { $args .= "(a.cInvCode = '{$value['product_no']}' and a.cFree1 = '{$value['technology_name']}') OR "; } $args = rtrim($args, 'OR '); $message = $this->db->table('CurrentStock as a') ->leftJoin('Warehouse as b', 'b.cWhCode', 'a.cWhCode') ->whereRaw("($args)") ->where('a.iQuantity', '>', 0) ->select('a.iQuantity as product_quantity_on_hand', 'a.cInvCode as product_no', 'a.cFree1 as technology_name', 'a.cFree2 as wood_name', 'b.cWhName as warehouse_name') ->get()->toArray(); if (!empty($message)) { foreach ($message as $key => $value) { $message[$key] = (array)$value; } } //现存量查询结束 return [true, $message, $product]; } //获取用友账号的人名 public function getYongyouName(){ if (!empty($this->error)) return [false, $this->error, '']; $str = ""; $model = $this->db->table('UA_User') ->where('cUser_Id',$this->sUserID) ->select('cUser_Name') ->first(); if(! empty($model)) $str = $model->cUser_Name; return $str; } //产成品入库单保存接口以及审核 public function U8Rdrecord10Save($data, $data_detail, $bredvouch = 0) { if (! empty($this->error)) return [false, $this->error]; if ($bredvouch) { $cmemo = '来源:恒成塑业完工操作撤回'; } else { $cmemo = '来源:恒成塑业包装操作 包装单号:' . $data['order_no']; } //数据 $bodys = []; foreach ($data_detail as $value){ $key = $value['ext_1'] . $value['ext_3']; if(! isset($bodys[$key])){ $bodys[$key] = [ "cinvcode" => $value["ext_1"], "cposition" => "", "cbatch" => "", "iquantity" => $value["num"], "inum" => $value["num"], "iunitcost" => 0, "iprice" => 0, "iinvexchrate" => 0, "impoids" => "", "cmocode" => "", "imoseq" => "", "cbmemo" => "", "cfree1" => $value['ext_3'], //颜色 "cfree2" => "", "cdefine28" => "", ]; }else{ $bodys[$key]['iquantity'] += $value['num']; $bodys[$key]['inum'] += $value['num']; } } $bodys = array_values($bodys); $post = [ "password" => "cloud@123456", "entity" => "U8Rdrecord10Save", "login" => [ "sAccID" => $this->sAccID, "sDate" => date("Y-m-d"), "sServer" => '127.0.0.1', "sUserID" => $this->sUserID, "sSerial" => "", "sPassword" => $this->sPassword ], "data" => [ "ccode" => '', "ddate" => date("Y-m-d"), "cmaker" => $data['create_name'], "dnmaketime" => date("Y-m-d"), "IsExamine" => true, "chandler" => $data['create_name'], "dnverifytime" => date("Y-m-d"), "bredvouch" => $bredvouch, "cwhcode" => "002", "cdepcode" => "03", "crdcode" => "102", //生产入库 "cmemo" => $cmemo, "cdefine10" => $data['ext_1'] ?? "", //客户名称 "bodys" => $bodys ] ]; Log::channel('apiLog')->info('产成品入库:源数据', ["param" => $post]); $return = $this->post_helper($this->url, json_encode($post), ['Content-Type:application/json'],70); Log::channel('apiLog')->info('产成品入库:返回结果', ["param" => $return]); if (empty($return)) return [false, '异常错误,请确认请求接口地址!']; return [$return['flag'], $return['msg']]; } //销售出库单保存接口给以及审核 public function U8Rdrecord32Save($data,$create_name, $bredvouch = 0) { if (!empty($this->error)) return [false, $this->error]; if ($bredvouch) { $cmemo = '来源:恒成塑业发货出库操作(撤回)'; } else { $cmemo = '来源:恒成塑业发货出库操作'; } foreach ($data as $value) { $bodys_tmp = []; foreach ($value['product'] as $v){ $bodys_tmp[] = [ "idlsid" => "", "cdlcode" => $value['cdlcode_string'], "dlrowno" => $v['line'], "cbdlcode" => "", "cinvcode" => $v['cinvcode'], "cposition" => "", "cbatch" => "", "iquantity" => $v['iquantity'], "inum" => 0, "iinvexchrate" => 0, "iunitcost" => 0, "iprice" => 0, "cbmemo" => "", "cfree1" => $v['cfree1'], "cfree2" => "", ]; } $cmemo = $cmemo . '(发货单信息:' . $value['cdlcode_string'] . ')'; $post_tmp = [ "password" => "cloud@123456", "entity" => "U8Rdrecord32Save", "login" => [ "sAccID" => $this->sAccID, "sDate" => date("Y-m-d"), "sServer" => '127.0.0.1', "sUserID" => $this->sUserID, "sSerial" => "", "sPassword" => $this->sPassword ], "data" => [ "ccode" => '', "ddate" => date("Y-m-d"), "cmaker" => $create_name, "dnmaketime" => date("Y-m-d"), "IsExamine" => true, "chandler" => $create_name, "dnverifytime" => date("Y-m-d"), "bredvouch" => $bredvouch, "cdepcode" => "03", "ccuscode" => $value['customer_code'], "crdcode" => '202', "cmemo" => $cmemo, "cwhcode" => "002", "bodys" => $bodys_tmp, ] ]; } Log::channel('apiLog')->info('销售出库单:源数据', ["param" => $post_tmp]); $return = $this->post_helper($this->url, json_encode($post_tmp), ['Content-Type:application/json'], 70); Log::channel('apiLog')->info('销售出库单:返回结果', ["param" => $return]); if (empty($return)) return [false, '异常错误,请确认请求接口地址!']; if (! $return['flag']) return [false, $return['msg']]; return [true, '']; } public function getBoxData($data) { $boxData = BoxDetail::from('box_detail as a') ->leftJoin('sale_orders_product as b', 'b.id', 'a.top_id') ->where('a.del_time', 0) ->where('a.order_no', $data['order_number']) //包装单号 ->select('a.num as iquantity', 'b.product_no as cinvcode', 'b.technology_name as cfree1', 'b.wood_name as cfree2', 'b.out_order_no as cSOCode'); return $boxData; } //获取发货单数据 还没发的 public function getDataFromDispatchList($data) { $model = $this->db->table('DispatchList as a') ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID') ->leftJoin('Inventory as c', 'c.cInvCode', 'b.cInvCode') ->whereNotNull('a.cVerifier'); // ->whereColumn('b.iQuantity', '>', 'b.fOutQuantity'); //检索条件 if (!empty($data['time'][0]) && !empty($data['time'][1])) { $model->where('a.dDate', '>=', $data['time'][0]); $model->where('a.dDate', '<=', $data['time'][1]); } if (!empty($data['order_no'])) $model->where('b.cSOcode', $data['order_no']); if (!empty($data['out_order_no'])) $model->where('b.cSOcode', $data['out_order_no']); $message = $model->select('a.cDLCode as cdlcode', 'a.DLID as id', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cDepCode as cdepcode', 'a.cCusCode as cuscode', 'b.iDLsID as idlsid', 'b.cWhCode as cwhcode', 'b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1', 'b.cFree2 as cfree2', 'b.cPosition as cposition', 'b.cBatch as cbatch', 'b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney', 'b.cDefine28 as technology_material', 'b.cDefine30 as process_mark', 'c.cInvStd as product_size') ->get()->toArray(); if (!empty($message)) { foreach ($message as $key => $value) { // $message[$key]->iquantity = $value->iquantity - $value->out_quantity; $message[$key] = (array)$value; } } return $message; } public function post_helper($url, $data, $header = [], $timeout = 60) { $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); curl_setopt($ch, CURLOPT_ENCODING, ''); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST'); curl_setopt($ch, CURLOPT_HTTPHEADER, $header); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); curl_setopt($ch, CURLOPT_TIMEOUT, $timeout); if (!is_null($data)) curl_setopt($ch, CURLOPT_POSTFIELDS, $data); $r = curl_exec($ch); curl_close($ch); return json_decode($r, true); } //获取发货单数据 还没发的 分页 public function getDataFromDispatchListPage($data) { //检索条件 if (empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间区间不能为空!']; if(empty($data['page_size'])) $data['page_size'] = 20; if(empty($data['page_index'])) $data['page_index'] = 1; $model = $this->db->table('DispatchList as a') ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID') ->leftJoin('Inventory as c', 'c.cInvCode', 'b.cInvCode') ->select('a.cDLCode as cdlcode', 'a.DLID as id', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cDepCode as cdepcode', 'a.cCusCode as cuscode', 'a.dDate as date','b.irowno', 'b.iDLsID as idlsid', 'b.cWhCode as cwhcode', 'b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1', 'b.cFree2 as cfree2', 'b.cPosition as cposition', 'b.cBatch as cbatch', 'b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney', 'b.cDefine28 as technology_material', 'b.cDefine30 as process_mark', 'c.cInvStd as product_size', DB::raw('(b.iQuantity - b.fOutQuantity) as quantity'), 'a.cMemo as table_header_mark', 'b.cMemo as table_body_mark') ->whereNotNull('a.cVerifier') // ->whereColumn('b.iQuantity', '>', 'b.fOutQuantity') ->where('a.dDate', '>=', $data['time'][0]) ->where('a.dDate', '<=', $data['time'][1]); if (!empty($data['cdlcode'])) $model->where('a.cDLCode', 'Like', '%' . $data['cdlcode'] . '%'); if (!empty($data['cinvcode'])) $model->where('b.cInvCode', 'Like', '%' . $data['cinvcode'] . '%'); if (!empty($data['product_title'])) $model->where('b.cInvName', 'Like', '%' . $data['product_title'] . '%'); if (!empty($data['product_size'])) $model->where('c.cInvStd', 'Like', '%' . $data['product_size'] . '%'); if (!empty($data['technology_material'])) $model->where('b.cDefine28', 'Like', '%' . $data['technology_material'] . '%'); if (!empty($data['cfree1'])) $model->where('b.cFree1', 'Like', '%' . $data['cfree1'] . '%'); if (!empty($data['cfree2'])) $model->where('b.cFree2', 'Like', '%' . $data['cfree2'] . '%'); if (!empty($data['process_mark'])) $model->where('b.cDefine30', 'Like', '%' . $data['process_mark'] . '%'); $list = $this->limit($model, '', $data); if (! empty($list['data'])) { $product_no = array_unique(array_column($list['data'], 'cinvcode')); $messageMap = $this->db->table('Inventory as a') ->join('ComputationUnit as b', function ($join) { $join->on('a.cGroupCode', '=', 'b.cGroupCode') ->on('a.cComUnitCode', '=', 'b.cComUnitCode'); }, null, null, 'left') ->whereIn('a.cInvCode', $product_no) ->pluck('b.cComUnitName as product_unit', 'a.cInvCode') ->toArray(); $list['data'] = Collect($list['data'])->map(function ($object) { return (array)$object; })->toArray(); $new_data = []; foreach ($list['data'] as $value) { $unit = $messageMap[$value['cinvcode']] ?? ''; $iquantity = floatval($value['iquantity']); $out_quantity = floatval($value['out_quantity']); $quantity = floatval($value['quantity']); $inum = floatval($value['inum']); $pro = [ "cinvcode" => $value['cinvcode'], "product_title" => $value['product_title'], "cfree1" => $value['cfree1'], "iquantity" => $iquantity, "inum" => $inum, "out_quantity" => $out_quantity, "product_size" => $value['product_size'] ?? "", "quantity" => $quantity, "unit" => $unit, "line" => $value['irowno'], ]; if(isset($new_data[$value['cdlcode']])){ $new_data[$value['cdlcode']]['product'][] = $pro; }else{ $new_data[$value['cdlcode']] = [ "cdlcode" => $value['cdlcode'], "csocode" => $value['csocode'], "customer_code" => $value['cuscode'] ?? "", "customer_name" => $value['customer_name'] ?? "", "product" => [$pro] ]; } } $list['data'] = array_values($new_data); } return $list; } //获取发货单数据 做校验 public function getDataFromDispatchListForCheck($data) { $model = $this->db->table('DispatchList as a') ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID') ->select('a.cDLCode as cdlcode', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cCusCode as cuscode','b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1','b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney'); if (!empty($data['cdlcode'])) $model->whereIn('a.cDLCode', $data['cdlcode']); //颜色 if (!empty($data['cfree1'])) $model->where('b.cFree1', 'Like', '%' . $data['cfree1'] . '%'); $list = $model->get()->toArray(); if (! empty($list)) { $list = Collect($list)->map(function ($object) { return (array)$object; })->toArray(); $new_data = []; foreach ($list as $value) { $iquantity = floatval($value['iquantity']); $out_quantity = floatval($value['out_quantity']); $inum = floatval($value['inum']); $pro = [ "cinvcode" => $value['cinvcode'], "product_title" => $value['product_title'], "cfree1" => $value['cfree1'], "iquantity" => $iquantity, "inum" => $inum, "out_quantity" => $out_quantity, ]; if(isset($new_data[$value['cdlcode']])){ $new_data[$value['cdlcode']]['product'][] = $pro; }else{ $new_data[$value['cdlcode']] = [ "cdlcode" => $value['cdlcode'], "csocode" => $value['csocode'], "customer_code" => $value['cuscode'] ?? "", "customer_name" => $value['customer_name'] ?? "", "product" => [$pro] ]; } } $list = $new_data; } return $list; } public function recordErrorTable($msg,$user,$data,$time,$type){ // 连接到指定数据库连接 ErrorTable::insert([ 'msg' => $msg, 'data' => json_encode($data), 'user_id' => $user['id'], 'user_operation_time' => $time, 'type' => $type, 'order_no' => $data['order_no'] ?? "" ]); } public function getStorehouseDataFromSqlServer($data) { if (!empty($this->error)) return [false, $this->error, '']; $model = $this->db->table('Warehouse as a') ->select('cWhCode as code', 'cWhName as name'); if (!empty($data['code'])) $model->where('cWhCode', 'LIKE', '%' . $data['code'] . '%'); if (!empty($data['name'])) $model->where('cWhName', 'LIKE', '%' . $data['name'] . '%'); $list = $this->limit($model, '', $data); return $list; } //获取产品的原材料 public function getProductFromSqlServer($data){ if (!empty($this->error)) return [false, $this->error, '']; $return = []; $result = $this->db->table('Inventory') ->whereIn('cInvCode', $data['product_no']) ->select('cInvCode as product_code','CINVDEFINE1 as placode', 'CINVDEFINE2 as paper') ->get() ->toArray(); $product_code = []; foreach ($result as $value){ if(! in_array($value->placode, $product_code) && $value->placode) $product_code[] = $value->placode; if(! in_array($value->paper, $product_code) && $value->paper) $product_code[] = $value->paper; } if(! empty($product_code)){ $product_list = $this->db->table('Inventory as a') ->join('ComputationUnit as b', function ($join) { $join->on('a.cGroupCode', '=', 'b.cGroupCode') ->on('a.cComUnitCode', '=', 'b.cComUnitCode'); }, null, null, 'left') ->whereIn('a.cInvCode', $product_code) ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit') ->get() ->toArray(); $map = array_column($product_list, null, 'product_no'); foreach ($result as $value){ $tmp = []; if(! empty($value->placode)) $tmp[] = (array)$map[$value->placode] ?? []; if(! empty($value->paper)) $tmp[] = (array)$map[$value->paper] ?? []; $return[$value->product_code] = $tmp; } } return $return; } //获取产品的包装材料 public function getProductBzFromSqlServer($data){ if (!empty($this->error)) return [false, $this->error, '']; $return = []; $result = $this->db->table('Inventory') ->whereIn('cInvCode', $data['product_no']) ->select('cInvCode as product_code','CINVDEFINE3 as bz') ->get() ->toArray(); $product_code = []; foreach ($result as $value){ if(! in_array($value->bz, $product_code) && $value->bz) $product_code[] = $value->bz; } if(! empty($product_code)){ $product_list = $this->db->table('Inventory as a') ->join('ComputationUnit as b', function ($join) { $join->on('a.cGroupCode', '=', 'b.cGroupCode') ->on('a.cComUnitCode', '=', 'b.cComUnitCode'); }, null, null, 'left') ->whereIn('a.cInvCode', $product_code) ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit') ->get() ->toArray(); $map = array_column($product_list, null, 'product_no'); foreach ($result as $value){ if(isset($map[$value->bz])){ $return[$value->product_code][] = (array)$map[$value->bz]; } } } return $return; } //获取销售单客户来源 public function getCustomerFromSqlServer1($data){ if (! empty($this->error)) return [false, $this->error, '']; $result = $this->db->table('SO_SOMain') ->whereIn('cSOCode', $data['sale_order']) ->select('cCusCode as customer_no', DB::raw("count(cCusCode) as num")) ->groupBy('cCusCode') ->get() ->toArray(); if(empty($result)) return [true, []]; $map = []; foreach ($result as $value){ $map[$value->customer_no] = $value->num; } $return = []; $customer = $this->db->table('Customer') ->whereIn('cCusCode', array_column($result,'customer_no')) ->select('cCusCode as customer_no', 'cDCCode as address') ->get() ->toArray(); foreach ($customer as $value){ if(empty($value->address)) continue; $num = $map[$value->customer_no]; if(isset($return[$value->address])){ $return[$value->address] += $num; }else{ $return[$value->address] = $num; } } return [true, $return]; } public function insertSaleOrderFrom1(){ $time = time(); $return = []; $address_map = config('address'); foreach ($address_map as $value){ $return[$value['value']] = 0; } try { Orders::where('del_time', 0) ->select('out_order_no') ->orderBy('id','desc') ->chunk(200, function ($records) use(&$return) { $out_order_no = []; foreach ($records as $record){ $out_order_no[] = $record->out_order_no; } $sqlServerModel = new FyySqlServerService(['id' => 1, 'zt' => '001']); list($status,$msg) = $sqlServerModel->getCustomerFromSqlServer(['sale_order' => $out_order_no]); if($status){ foreach ($return as $key => $value){ if(isset($msg[$key])){ $return[$key] += $msg[$key]; } } } echo '更新中--------' . "\n"; }); $insert = []; foreach ($return as $key => $value){ $insert[] = [ 'code' => $key, 'num' => $value, 'crt_time' => $time ]; } SalesFrom::where('del_time', 0) ->update(['del_time' => $time]); SalesFrom::insert($insert); echo '更新结束--------' . "\n"; }catch (\Throwable $exception){ echo $exception->getMessage() . "\n"; } } //获取客户来源 public function getCustomerFromSqlServer(){ if (! empty($this->error)) return [false, $this->error, '']; $return = []; $customer = $this->db->table('Customer') ->select('cCusCode as customer_no', 'cDCCode as address') ->get()->toArray(); foreach ($customer as $value){ if(empty($value->address)) continue; if(isset($return[$value->address])){ $return[$value->address] += 1; }else{ $return[$value->address] = 1; } } return [true, $return]; } public function insertSaleOrderFrom(){ $time = time(); $return = []; $address_map = config('address'); foreach ($address_map as $value){ $return[$value['value']] = 0; } try { $sqlServerModel = new FyySqlServerService(['id' => 1, 'zt' => '001']); list($status,$msg) = $sqlServerModel->getCustomerFromSqlServer(); if($status){ foreach ($return as $key => $value){ if(isset($msg[$key])){ $return[$key] += $msg[$key]; } } } echo '更新中--------' . "\n"; $insert = []; foreach ($return as $key => $value){ $insert[] = [ 'code' => $key, 'num' => $value, 'crt_time' => $time ]; } SalesFrom::where('del_time', 0) ->update(['del_time' => $time]); SalesFrom::insert($insert); echo '更新结束--------' . "\n"; }catch (\Throwable $exception){ echo $exception->getMessage() . "\n"; } } }