db->get('pdf_cate', '*', ['pdfc_id' => $cateid]); $this->cates = $row['pdfc_cates']; $this->image = new image(); $this->staticDir = \mvc::$cfg['dir']['static'] . '/images/pdf/ss'; $this->transparentPng = $this->staticDir . '/transparent.png'; $this->tmpDir = \mvc::$cfg['dir']['cache'] . '/tmp_pdf_ss'; mkdirs($this->tmpDir); $this->modelHeader = [ 1 => [ 'filename' => $this->staticDir . '/1.png', 'width' => 820 * SCALING_FACTOR, 'height' => 61, ], 2 => [ 'filename' => $this->staticDir . '/2.png', 'width' => 820 * SCALING_FACTOR, 'height' => 97, ], 4 => [ 'filename' => $this->staticDir . '/4.png', 'width' => 820 * SCALING_FACTOR, 'height' => 96, ], 6 => [ 'filename' => $this->staticDir . '/6.png', 'width' => 820 * SCALING_FACTOR, 'height' => 94, ], 9 => [ 'filename' => $this->staticDir . '/9.png', 'width' => 820 * SCALING_FACTOR, 'height' => 129, ], ]; } //数字转字母列 protected function int2Excel($num) { $num -= 1; $az = 26; $m = (int)($num % $az); $q = (int)($num / $az); $letter = chr(ord('A') + $m); if ($q > 0) { return int2Excel($q - 1) . $letter; } return $letter; } public function getPos($row) { $ssc_pos = $row['ss_pos']; $row['pos1'] = ''; //前后轴 $row['pos2'] = ''; //左右 $row['pos3'] = ''; //外中内 $row['pos4'] = ''; //上中下 $arr = explode(' ', $ssc_pos); if (stripos($ssc_pos, 'Front Axle') !== false) { $row['pos1'] = 'F'; } if (stripos($ssc_pos, 'Rear Axle') !== false) { $row['pos1'] = 'R'; } if (stripos($ssc_pos, 'Left') !== false) { $row['pos2'] = 'L'; } if (stripos($ssc_pos, 'Right') !== false) { $row['pos2'] = 'R'; } if (stripos($ssc_pos, 'Left And Right') !== false) { $row['pos2'] = 'L/R'; } if (stripos($ssc_pos, 'Outer') !== false) { $row['pos3'] = 'O'; } if (stripos($ssc_pos, 'Inner') !== false) { $row['pos3'] = 'I'; } if (stripos($ssc_pos, 'Inner And Outer') !== false) { $row['pos3'] = 'I/O'; } if (stripos($ssc_pos, 'Lower') !== false) { $row['pos4'] = 'L'; } if (stripos($ssc_pos, 'Upper') !== false) { $row['pos4'] = 'U'; } if (($arr[count($arr) - 1] == 'Center' || $arr[count($arr) - 1] == 'Centre')) { if ($row['pos4'] == '') { $row['pos4'] = 'C'; } else { $row['pos3'] = 'C'; } } if ($row['pos3'] == '' && $arr[count($arr) - 2] == 'Center' || $arr[count($arr) - 2] == 'Centre') { $row['pos3'] = 'C'; } return $row; } public function getNameByPos($item) { $name = $item['ss_name']; if (in_array($name, [ 'Control Arm Bushing', 'Track Control Arm', 'Control Arm Bushing Kit', 'Control Arm Bush', 'Control Arm', 'Control Arm Bush Kit', 'Ball Joint', 'Stabiliser Bushing', 'Stabiliser Bushing Kit', 'Tie Rod End', 'Rod Assembly', ])) { switch ($name) { case 'Control Arm Bushing': $name = 'Control Arm Bush'; break; case 'Track Control Arm': $name = 'Control Arm'; break; case 'Control Arm Bushing Kit': $name = 'Control Arm Bush Kit'; break; } if ($item['pos3'] == 'O') { $name = 'Outer ' . $name; } elseif ($item['pos3'] == 'I') { $name = 'Inner ' . $name; } elseif ($item['pos3'] == 'I/O') { $name = 'Inner And Outer ' . $name; } elseif ($item['pos3'] == 'C') { $name = 'Centre ' . $name; } if ($item['pos4'] == 'L') { $name = 'Lower ' . $name; } elseif ($item['pos4'] == 'U') { $name = 'Upper ' . $name; } elseif ($item['pos4'] == 'C') { $name = 'Centre ' . $name; } } return $name; } protected function sortItems($array) { $ssNameOrder = array_map('strtolower', [ 'Steering Rack Bellow Kit', 'Steering Boot', 'Tie Rod End', 'Tie Rod Axle Joint', 'Rod Assembly', 'Drag Link', 'Pitman Arm', 'Idler Arm', 'Control Arm', 'Track Control Arm', 'Ball Joint', 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', 'Suspension Repair Kit', 'Rod/Strut,stabiliser', 'Stabiliser Bushing', 'Stabiliser Bushing Kit' ]); $groupNames = array_map('strtolower', [ 'Control Arm', 'Control Arm Bush', 'Control Arm Bush Kit', 'Ball Joint' ]); $pos1Order = ['', 'F', 'R']; $pos4Order = ['U', 'C', 'L', '']; $pos5Order = ['A', 'B', 'C', '']; // 如果没有pos5，可去掉 $pos2Order = ['', 'L/R', 'L', 'R']; $pos3Order = ['', 'I', 'C', 'O']; uasort($array, function ($a, $b) use ($ssNameOrder, $groupNames, $pos1Order, $pos2Order, $pos3Order, $pos4Order, $pos5Order) { $aName = strtolower($a['ss_name']); $bName = strtolower($b['ss_name']); $aInGroup = in_array($aName, $groupNames); $bInGroup = in_array($bName, $groupNames); // 1️⃣ 第一优先级：pos1 $aPos1Index = array_search($a['pos1'], $pos1Order); $bPos1Index = array_search($b['pos1'], $pos1Order); if ($aPos1Index !== $bPos1Index) { if ($aPos1Index === false) return 1; if ($bPos1Index === false) return -1; return $aPos1Index - $bPos1Index; } // 2️⃣ 如果两者都属于“Control Arm组” if ($aInGroup && $bInGroup) { // 按 pos1 -> pos4 -> pos5 -> pos2 顺序排序 foreach ( [ ['key' => 'pos4', 'order' => $pos4Order], ['key' => 'pos5', 'order' => $pos5Order], ['key' => 'pos2', 'order' => $pos2Order], ] as $rule ) { $aIndex = array_search($a[$rule['key']] ?? '', $rule['order']); $bIndex = array_search($b[$rule['key']] ?? '', $rule['order']); if ($aIndex !== $bIndex) { if ($aIndex === false) return 1; if ($bIndex === false) return -1; return $aIndex - $bIndex; } } return 0; // 同组内部排序完毕 } // 3️⃣ 如果一个在组里、一个不在组里 → 按 ssNameOrder 顺序（组中第一个“Control Arm”的位置代表整个组） if ($aInGroup || $bInGroup) { $groupIndex = array_search('control arm', $ssNameOrder); // 组位置 $aIndex = $aInGroup ? $groupIndex : array_search($aName, $ssNameOrder); $bIndex = $bInGroup ? $groupIndex : array_search($bName, $ssNameOrder); if ($aIndex === false) return 1; if ($bIndex === false) return -1; return $aIndex - $bIndex; } // 4️⃣ 普通品类按 ssNameOrder 排 $aIndex = array_search($aName, $ssNameOrder); $bIndex = array_search($bName, $ssNameOrder); if ($aIndex !== $bIndex) { if ($aIndex === false) return 1; if ($bIndex === false) return -1; return $aIndex - $bIndex; } // 5️⃣ 其他位置条件 foreach ( [ ['key' => 'pos4', 'order' => $pos4Order], ['key' => 'pos3', 'order' => $pos3Order], ['key' => 'pos2', 'order' => $pos2Order], ] as $rule ) { $aIndex = array_search($a[$rule['key']], $rule['order']); $bIndex = array_search($b[$rule['key']], $rule['order']); if ($aIndex !== $bIndex) { if ($aIndex === false) return 1; if ($bIndex === false) return -1; return $aIndex - $bIndex; } } // 6️⃣ 最后按日期 $aDate = preg_match('/Date from:(\d{4}\.\d{2})/', $a['ss_remark'], $aMatches) ? $aMatches[1] : null; $bDate = preg_match('/Date from:(\d{4}\.\d{2})/', $b['ss_remark'], $bMatches) ? $bMatches[1] : null; if ($aDate !== null && $bDate !== null) return strcmp($bDate, $aDate); if ($aDate !== null) return -1; if ($bDate !== null) return 1; return 0; }); return $array; } protected function sortItems2($array) { $ssNameOrder = array_map('strtolower', [ 'Steering Rack Bellow Kit', 'Steering Boot', 'Tie Rod End', 'Tie Rod Axle Joint', 'Rod Assembly', 'Drag Link', 'Pitman Arm', 'Idler Arm', 'Control Arm', 'Track Control Arm', 'Ball Joint', // 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', //'Ball Joint', 'Suspension Repair Kit', 'Rod/Strut,stabiliser', 'Stabiliser Bushing', 'Stabiliser Bushing Kit' ]); $pos1Order = ['', 'F', 'R']; $pos4Order = ['U', 'C', 'L', '']; $pos3Order = ['', 'I', 'C', 'O']; $pos2Order = ['', 'L/R', 'L', 'R']; //usort,uasort uasort($array, function ($a, $b) use ($ssNameOrder, $pos1Order, $pos2Order, $pos4Order, $pos3Order) { // 1. 第一优先级：按pos1排序 $aPos1Index = array_search($a['pos1'], $pos1Order); $bPos1Index = array_search($b['pos1'], $pos1Order); if ($aPos1Index !== $bPos1Index) { if ($aPos1Index === false) return 1; if ($bPos1Index === false) return -1; return $aPos1Index - $bPos1Index; } // 2. 第二优先级：按ss_name排序 $aNameIndex = array_search(strtolower($a['ss_name']), $ssNameOrder); $bNameIndex = array_search(strtolower($b['ss_name']), $ssNameOrder); if ($aNameIndex !== $bNameIndex) { if ($aNameIndex === false) return 1; if ($bNameIndex === false) return -1; return $aNameIndex - $bNameIndex; } // 3. 其他条件 foreach ( [ ['key' => 'pos4', 'order' => $pos4Order], ['key' => 'pos3', 'order' => $pos3Order], ['key' => 'pos2', 'order' => $pos2Order], ] as $rule ) { $aIndex = array_search($a[$rule['key']], $rule['order']); $bIndex = array_search($b[$rule['key']], $rule['order']); if ($aIndex !== $bIndex) { if ($aIndex === false) return 1; if ($bIndex === false) return -1; return $aIndex - $bIndex; } } // 4. 最后按ss_remark日期排序 $aDate = preg_match('/Date from:(\d{4}\.\d{2})/', $a['ss_remark'], $aMatches) ? $aMatches[1] : null; $bDate = preg_match('/Date from:(\d{4}\.\d{2})/', $b['ss_remark'], $bMatches) ? $bMatches[1] : null; if ($aDate !== null && $bDate !== null) { return strcmp($bDate, $aDate); //$bDate, $aDate 最早的排最前 } if ($aDate !== null) return -1; if ($bDate !== null) return 1; return 0; }); return $array; } public function mergeRemarksWithSymbols($remarks) { $valueMap = []; // 创建值到索引的映射，并过滤掉内容为空的项 foreach ($remarks as $index => $value) { $trimmed = trim((string)$value); if ($trimmed === '') { continue; // 跳过空内容 } if (!isset($valueMap[$trimmed])) { $valueMap[$trimmed] = []; } $valueMap[$trimmed][] = $index; } // 数字到符号的映射 $symbols = ['①', '②', '③', '④', '⑤', '⑥', '⑦', '⑧', '⑨', '⑩']; $result = []; foreach ($valueMap as $value => $indexes) { // 将数字索引转换为符号 $symbolIndexes = array_map(function ($index) use ($symbols) { return $symbols[$index] ?? (string)($index + 1); }, $indexes); if (count($symbolIndexes) > 1) { $mergedSymbols = implode('/', $symbolIndexes); $result[] = "$mergedSymbols: $value"; } else { $result[] = "{$symbolIndexes[0]}: $value"; } } // 按原始顺序排序（基于第一个出现的索引） usort($result, function ($a, $b) { preg_match('/^([①-⑩]+)/u', $a, $matchesA); preg_match('/^([①-⑩]+)/u', $b, $matchesB); // 将符号转换回数字比较 $symbolToNum = function ($symbol) { $symbols = ['①', '②', '③', '④', '⑤', '⑥', '⑦', '⑧', '⑨', '⑩']; $pos = array_search($symbol[0], $symbols); return $pos !== false ? $pos : (int)$symbol; }; $aNum = $symbolToNum($matchesA[1][0]); $bNum = $symbolToNum($matchesB[1][0]); return $aNum - $bNum; }); return $result; } //导出相同GSP型号的小车型列表 public function exportSameModelGspNo($headerData = []) { $sql = "SELECT p_id,p_cate,ss.*,CONCAT(ssc_model, ' ', ss_year_range) model,ppic_filebase ,files.filename,fileextname from catalog_ss ss JOIN pro ON p_outnum = ss.ssc_outnum LEFT OUTER JOIN pro_pic pic ON ppic_p_id = p_id AND ppic_type IN ('beautify','beautify_replay') AND ppic_innum like ppic_filebase LEFT OUTER JOIN files ON fileid = ppic_fileid WHERE !EXISTS(SELECT 1 FROM catalog_ss_ignore WHERE ssc_make=cssi_make AND ssc_model=cssi_model AND ssc_outnum = cssi_outnum) %s GROUP BY ssc_make,model,p_id ORDER BY ssc_make, model,ssc_outnum,ss_remark "; $where = ''; if (!empty($headerData)) { foreach ($headerData['models'] as $k => $v) { $headerData['models'][$k] = str_replace("'", "\'", $v); } $where = " AND ssc_make = '" . $headerData['make'] . "'"; $where .= " AND CONCAT(ssc_model, ' ', ss_year_range) in ('" . implode("','",$headerData['models']) . "')"; $sql = sprintf($sql, $where); } $sql = sprintf($sql, $where); $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); $data = []; $modelOutnums = []; foreach ($rows as $i => $row) { $model = $row['model']; $row = $this->getPos($row); $data[$row['ssc_make']][$model][] = $row; $modelOutnums[$row['ssc_make']][$model][] = trim($row['ssc_outnum']); } //分析构建:相同gsp型号的小车型数组 $sameOutnums = []; foreach ($modelOutnums as $make => $models) { foreach ($models as $model => $outnums) { sort($outnums); $md5 = md5(json_encode($outnums)); $sameOutnums[$make][$md5][] = $model; } } // 找出备注不同的(在相同gsp型号的小车型条件下) $diffRemarks = []; foreach ($sameOutnums as $make => $group) { foreach ($group as $gk => $models) { if (count($models) > 1) { $remarks = []; $hasDiff = false; foreach ($models as $i => $model) { $items = $data[$make][$model]; foreach ($items as $item) { if (isset($remarks[$make][$gk][$item['ssc_outnum']])) { if (!in_array($item['ss_remark'], $remarks[$make][$gk][$item['ssc_outnum']]['remarks'])) { $remarks[$make][$gk][$item['ssc_outnum']]['diff'] = true; $hasDiff = true; } } $remarks[$make][$gk][$item['ssc_outnum']]['remarks'][$i] = $item['ss_remark'] ?: ''; } } if ($hasDiff) { foreach ($remarks as $rMake => $rGroup) { foreach ($rGroup as $rGk => $outnumItems) { foreach ($outnumItems as $outnum => $outnumItem) { if ($outnumItem['diff']) { $outnumItem['remarksChange'] = $this->mergeRemarksWithSymbols($outnumItem['remarks']); $outnumItem['remarksChange'] = implode("
\n", $outnumItem['remarksChange']); } else { $outnumItem['remarksChange'] = $outnumItem['remarks'][0]; } $outnumItems[$outnum] = $outnumItem; } $rGroup[$rGk] = $outnumItems; } $remarks[$rMake] = $rGroup; } $diffRemarks[$make][$gk] = $remarks[$make][$gk]; } } } } return [$sameOutnums, $diffRemarks]; } public function getData($makeModels) { $makes = array_column($makeModels, 'make'); $makes = array_unique($makes); $models = array_column($makeModels, 'model'); $models = array_unique($models); foreach ($models as $k => $v) { $models[$k] = str_replace("\\\\","\\",$v); } $inMakesText = "'" . implode("','", $makes) . "'"; $inModelsText = "'" . implode("','", $models) . "'"; $sql = "SELECT pro.p_id, pro.p_cate, ss.*, CONCAT(ssc_model, ' ', ss_year_range) AS model, ppic_filebase, ppic_type, files.filename, fileextname FROM catalog_ss ss JOIN pro ON p_outnum = ss.ssc_outnum JOIN ( SELECT ssc_make, CONCAT(ssc_model, ' ', ss_year_range) AS model, p_id, SUBSTRING_INDEX(GROUP_CONCAT(ppic_type ORDER BY FIELD(ppic_type, 'beautify', 'beautify_replay')), ',', 1) AS best_ppic_type FROM catalog_ss ss JOIN pro ON p_outnum = ss.ssc_outnum LEFT JOIN pro_pic pic ON ppic_p_id = p_id AND ppic_type IN ('beautify','beautify_replay') AND ppic_innum LIKE ppic_filebase WHERE ssc_make IN (" . $inMakesText . ") AND CONCAT(ssc_model, ' ', ss_year_range) IN (" . $inModelsText . ") AND NOT EXISTS ( SELECT 1 FROM catalog_ss_ignore WHERE ssc_make=cssi_make AND ssc_model=cssi_model AND ssc_outnum = cssi_outnum ) GROUP BY ssc_make, model, p_id ) b ON ss.ssc_make = b.ssc_make AND CONCAT(ssc_model, ' ', ss_year_range) = b.model AND pro.p_id = b.p_id LEFT JOIN pro_pic pic ON ppic_p_id = pro.p_id AND ppic_type IN ('beautify','beautify_replay') AND ppic_type = b.best_ppic_type AND ppic_innum LIKE ppic_filebase LEFT JOIN files ON fileid = ppic_fileid GROUP BY ssc_make,model,p_id ORDER BY ss.ssc_make, model, ss.ssc_outnum, ss_remark "; $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); $data = []; foreach ($rows as $i => $row) { $model = $row['model']; $row = $this->getPos($row); $data[$row['ssc_make']][$model][] = $row; } // 大车型相同，gsp no 相同, 小车型不同的合并在一起 $newData = []; foreach ($data as $make => $models) { foreach ($models as $model => $items) { $uid = ''; $mergeModels = []; foreach ($items as $item) { $uid .= trim($item['ssc_make']) . '-' . trim($item['ssc_outnum']); // . '-' . $item['ss_remark']; $mergeModels[] = $item['model']; } $mergeModels = array_unique($mergeModels); $key = md5($uid); if (!isset($newData[$make][$key])) { $newData[$make][$key] = [ 'models' => [], 'items' => [], ]; } $newData[$make][$key]['items'] = $items; $newData[$make][$key]['models'] = array_merge($newData[$make][$key]['models'], $mergeModels); if ($model == 'A3 Allstreet (8YH) 2022.07-') { //dd($newData); } } } //dd($newData); $data = $newData; //dd($data); unset($newData); // 多个小车型合并，要固定取其中一个小车型的OE foreach ($data as $make => $list) { foreach ($list as $key => $value) { $models = $value['models']; if (count($models) > 1) { $firstModel = $models[0]; $firstModel = str_replace("'", "\'", $models[0]); foreach ($value['items'] as $i => $item) { $findSQL = "SELECT ssc_oe FROM catalog_ss WHERE ssc_outnum='" . $item['ssc_outnum'] . "' AND CONCAT(ssc_model, ' ', ss_year_range) = '". $firstModel ."'"; $findOE = $this->db->query($findSQL)->fetchColumn(); $data[$make][$key]['items'][$i]['ssc_oe'] = $findOE; } } } } foreach ($data as $make => $list) { foreach ($list as $key => $value) { $models = $value['models']; $count = count($models); $headers = []; if ($count == 1) { $headers[] = 1; } elseif ($count == 2) { $headers[] = 2; } elseif ($count > 2 && $count < 5) { $headers[] = 4; } elseif ($count > 4 && $count < 7) { $headers[] = 6; } elseif ($count > 6) { $headers[] = 9; } $data[$make][$key]['headers'] = $headers; $items = $data[$make][$key]['items']; //寻找同车型下重复号码记录 $newItems = []; foreach ($items as $k => $v) { $tsql = $sql = "SELECT pro.p_id, pro.p_cate, ss.*, CONCAT(ssc_model, ' ', ss_year_range) AS model, ppic_filebase, SUBSTRING_INDEX(GROUP_CONCAT(ppic_type ORDER BY FIELD(ppic_type, 'beautify', 'beautify_replay')), ',', 1) AS ppic_type, files.filename, fileextname FROM catalog_ss ss LEFT JOIN pro ON p_outnum = ss.ssc_outnum LEFT JOIN pro_pic pic ON ppic_p_id = pro.p_id AND ppic_type IN ('beautify','beautify_replay') AND ppic_innum LIKE ppic_filebase LEFT JOIN files ON fileid = ppic_fileid WHERE ssc_id <> {:ssc_id} AND ssc_make = '{:ssc_make}' AND ssc_model = '{:ssc_model}' AND ss_year_range = '{:ss_year_range}' AND ssc_outnum = '{:ssc_outnum}' GROUP BY ssc_id ORDER BY ss.ssc_make, model, ss.ssc_outnum, ss_remark "; $tsql = qgprintf($tsql, str_replace("'", "\'", $v)); $findRows = $this->db->query($tsql)->fetchAll(\PDO::FETCH_ASSOC); $newItems[] = $v; if ($findRows) { foreach ($findRows as $fk => $fv) { $newItems[] = $fv; } } } //dd($items); $data[$make][$key]['items'] = $this->sortItems($newItems); } } //dd($sameOutnums, $diffRemarks,$data); return $data; } public function hasMoreRemark($make, $model) { /* $result = false; if (!isset($this->sameOutnums[$make])) { return $result; } foreach ($this->sameOutnums[$make] as $gk => $models) { if (in_array($model, $models) && isset($this->diffRemarks[$make][$gk])) { $result = true; } } return $result; */ return !empty($this->diffRemarks); } public function findMoreRemark($make, $model, $outnum) { $result = ''; if (!isset($this->sameOutnums[$make])) { return ''; } foreach ($this->sameOutnums[$make] as $gk => $models) { if (in_array($model, $models) && isset($this->diffRemarks[$make][$gk])) { $finditem = $this->diffRemarks[$make][$gk][$outnum]; if ($finditem['diff']) { $result = str_replace('
', '', $finditem['remarksChange']); } } } return $result; } public function cmToInch($cm) { return $cm / 2.54; } public function pxToInch($px, $dpi = 96) { return $px / $dpi; } public function createExcel($regI, $makeModels) { $this->rowNum = 1; $this->activeLineHeight = 0; //当前图片高 $this->activePageStartRow = 1; //当前分页开始行 $this->activeImageRow = 0; //当前图片行 $this->activeImageEmptyRow = 0; $this->pagesHeight = []; //各分页高度 $this->cellColors = []; $this->pageRows = []; //已经计算过页高的行数 $this->isSetHeightRows = []; //已设置过的行高 $arr = []; foreach ($makeModels as $key => $v) { if (empty($v)) { unset($v[$key]); } else { list($make, $model) = explode('<->', $v); $make = str_replace("'", "\'", $make); $model = str_replace("'", "\'", $model); $arr[] = ['make' => $make, 'model' => $model]; } } $data = $this->getData($arr); //dump($data); $sheetIndex = 0; $spreadsheet = new Spreadsheet(); $workSheet = new Worksheet($spreadsheet, 'Sheet' . ($sheetIndex + 1)); $spreadsheet->getDefaultStyle()->getFont()->setName('Arial')->setSize(11); //Arial,Calibri $sheet = $spreadsheet->getActiveSheet(); $sheet->getPageSetup() ->setOrientation(PageSetup::ORIENTATION_PORTRAIT) //纵向 ->setPaperSize(PageSetup::PAPERSIZE_A4); //A4 $sheet->getPageSetup() ->setFitToPage(true) ->setFitToWidth(1) ->setFitToHeight(0); // 不强制分页高度 // 设置页边距（单位：英寸） //$sheet->getPageMargins()->setTop($this->cmToInch(0.5)); // 上边距 $sheet->getPageMargins()->setTop($this->cmToInch(0.4)); // 上边距 $sheet->getPageMargins()->setBottom($this->cmToInch(0.5)); // 下边距 $sheet->getPageMargins()->setLeft($this->cmToInch(0.3)); // 左边距 $sheet->getPageMargins()->setRight($this->cmToInch(0.3)); // 右边距 $sheet->getPageMargins()->setHeader($this->cmToInch(1.27)); // 页眉高度 $sheet->getPageMargins()->setFooter($this->cmToInch(1.27)); // 页脚高度 $this->A4Height = $this->A4Height - ( $sheet->getPageMargins()->getTop() + $sheet->getPageMargins()->getBottom() + $sheet->getPageMargins()->getHeader() + $sheet->getPageMargins()->getFooter() ) * 28.3465; $this->A4Height = $this->A4Height - 0.49; $sheet->getDefaultRowDimension()->setRowHeight(DEFAULT_ROW_HEIGHT); //设置默认行高 //设置列宽 $columnWidth = 10.30; //列宽 9.68 (10.39), 11.7 foreach (range('A', 'J') as $col) { $sheet->getColumnDimension($col)->setWidth($columnWidth); } $sheet->getColumnDimension(MODELS_REMARK_COL_JG)->setWidth(8); $sheet->getColumnDimension(MODELS_REMARK_COL)->setWidth(50); //$sheet->getStyle(MODELS_REMARK_COL.':'. MODELS_REMARK_COL)->getFont()->setSize(11); $sheet->getColumnDimension(MODELS_REMARK_COL)->setAutoSize(true); $sheet->getColumnDimension('M')->setWidth(50); $this->rowNum = 1; foreach ($data as $make => $list) { foreach ($list as $key => $group) { foreach ($group['headers'] as $headerTag) { $model = $group['items'][0]['ssc_model']; $yearRange = $group['items'][0]['ss_year_range']; $headerData = []; $headerData['make'] = $make; for ($i = 0; $i < count($group['models']); $i++) { $headerData['models'][] = $group['models'][$i]; } //相同号的车型，及它们不同的备注加上序号 $sameOutnums = []; $diffRemarks = []; $this->sameOutnums = []; $this->diffRemarks = []; list($sameOutnums, $diffRemarks) = $this->exportSameModelGspNo($headerData); $this->sameOutnums = $sameOutnums; $this->diffRemarks = $diffRemarks; //dump($headerData, $diffRemarks); $this->insertModelHeaderAll($headerTag, $sheet, 1, $this->rowNum, $headerData); $items = $this->fillItems($group['items']); if (strpos(trim($group['items'][0]['ssc_model']), '323 S V (BA)/FAMILIA') !== false) { // dd($items); } $this->drawItems($sheet, $items); //$this->insertItems($sheet, $group['items']); } //break; } } $highestRow = $sheet->getHighestRow(); $sheet->getPageSetup()->setPrintArea("A1:J{$highestRow}"); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $filename = $this->tmpDir . '/' . (str_pad($regI, 3, '0', STR_PAD_LEFT)) . '.xlsx'; if (is_file($filename)) @unlink($filename); $writer->save($filename); $spreadsheet->disconnectWorksheets(); foreach ($this->drawings as $k => $v) { unset($this->drawings[$k]); } foreach ($this->gdImages as $k => $v) { unset($this->gdImages[$k]); } $this->drawings = []; $this->gdImages = []; unset($spreadsheet); unset($workSheet); /* // 设置文件名 $filename = 'export_' . date('YmdHis') . '.xlsx'; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); header('Cache-Control: max-age=1'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header('Cache-Control: cache, must-revalidate'); header('Pragma: public'); $writer->save('php://output'); */ return $filename; } public function getPages() { //相同号的车型，及它们不同的备注 list($sameOutnums, $diffRemarks) = $this->exportSameModelGspNo(); $this->sameOutnums = $sameOutnums; $this->diffRemarks = $diffRemarks; //按make,model分页 500条 $sql = "SELECT ssc_make make,CONCAT(ssc_model, ' ', ss_year_range) model,COUNT(*) cnt FROM catalog_ss GROUP BY ssc_make, model ORDER BY ssc_make, ssc_model"; $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); $pages = []; $current_page = []; $current_total = 0; $skipKeys = []; foreach ($rows as $row) { $key = $row['make'] . '<->' . $row['model']; if (in_array($key, $skipKeys)) { continue; } if ($current_total + $row['cnt'] > 500) { //相同gspno的车型不断页 foreach ($sameOutnums as $make => $list) { if ($make == $row['make']) { foreach ($list as $tuid => $items) { if (in_array($row['model'], $items)) { foreach ($items as $model) { $tkey = $make . '<->' . $model; $current_page[] = $tkey; $skipKeys[] = $tkey; } } } } } // $current_page = array_unique($current_page); $pages[] = $current_page; $current_page = []; $current_total = 0; } else { $current_page[] = $key; } $current_total += $row['cnt']; } if (!empty($current_page)) { $pages[] = $current_page; } return $pages; } public function execute() { set_time_limit(3600 * 10); ini_set('memory_limit', \mvc::$cfg['export_memory_limit']); ignore_user_abort(true); $message = new \model\message(); $pages = $this->getPages(); $files = []; $context = ""; $listFilename = $this->tmpDir . '/list.txt'; try { //dump($pages); $i = 0; $pageCount = count($pages); foreach ($pages as $k => $makeModels) { $filename = $this->createExcel($k, $makeModels); $files[$k] = $filename; $context .= basename($filename) . "\n" . implode(', ', $makeModels) . "\n\n"; $msg = [ 'amsg_type' => '0', 'amsg_title' => '生成悬挂电子目录', 'amsg_body' => sprintf("%d / %d", ($i + 1), $pageCount), 'amsg_url' => '', 'amsg_urltitle' => '', 'amsg_file' => '', ]; $message->send([getOpuser()], $msg); if (getOpUser() == 'test.admin' && $_SERVER['HTTP_HOST'] == '127.0.0.1:8087') { ob_end_clean(); header('Content-Description: File Transfer'); header("Content-type:application/vnd.ms-excel"); header('Content-Disposition: attachment; filename=' . basename($filename)); header("Content-Length:" . filesize($filename)); error_reporting(0); readfile($filename); ob_end_flush(); exit(); } $i++; } //dd(1); file_put_contents($listFilename, $context); $zipfile = $this->tmpDir . "/SS-" . getOpuser() . '-' . date('Y-m-d-His') . ".zip"; $zip = new \ZipArchive(); if ($zip->open($zipfile, \ZIPARCHIVE::CREATE) !== true) { throw new \Exception('生成压缩文件' . $zipfile . '失败,请检查相关目录权限'); } $zip->addfile($listFilename, 'list.txt'); foreach ($files as $key => $filename) { $zip->addfile($filename, basename($filename)); } $zip->close(); $pdffileUrl = \mvc::$cfg['path']['cache'] . '/tmp_pdf_ss/' . basename($zipfile); echo ' 悬挂电子目录 '; $msg = [ 'amsg_type' => '0', 'amsg_title' => '生成悬挂电子目录', 'amsg_body' => '生成悬挂电子目录完成', 'amsg_url' => $pdffileUrl, 'amsg_urltitle' => basename($pdffileUrl), 'amsg_file' => $zipfile, ]; $message->send([getOpuser()], $msg); socketSendMsg([getOpuser()], $msg); /* ob_end_clean(); header('Content-Description: File Transfer'); header("Content-type:application/vnd.ms-excel"); header('Content-Disposition: attachment; filename=' . basename($zipfile)); header("Content-Length:" . filesize($zipfile)); error_reporting(0); readfile($zipfile); ob_end_flush();*/ } finally { //@unlink($zipfile); @unlink($listFilename); foreach ($files as $filename) { @unlink($filename); } } exit(); } public function fillItems($items) { foreach ($items as $k => $item) { //正式环境 $imgFile = \mvc::$cfg['dir']['files'] . '/' . substr($item['filename'], 0, strrpos($item['filename'], '.')) . '.' . $item['fileextname']; if (!file_exists($imgFile)) { //测试环境 $imgFile = $this->staticDir . '/test/' . $item['ssc_outnum'] . '.png'; } if (is_file($imgFile)) { $tmpFilename = $this->tmpDir . '/' . md5($imgFile) . '.png'; if (!file_exists($tmpFilename)) { $imgFile = $this->image->Cut2($imgFile, $tmpFilename, 400, 400); } $items[$k]['imgFile'] = $tmpFilename; $items[$k]['noimgFile'] = false; } else { $items[$k]['noimgFile'] = true; } $items[$k]['ss_name'] = trim($items[$k]['ss_name']); $items[$k]['pos_name'] = $this->getNameByPos($items[$k]); if (strpos(trim($item['ssc_model']), '323 S V (BA)/FAMILIA') !== false) { //dd($items); } } $items = $this->findFsAndLrItems($items); return $items; } //找父子配对与左右件 public function findFsAndLrItems($items) { $data = []; $outnums = array_unique(array_column($items, 'ssc_outnum')); $pushCenterOutnums = []; //已经装箱的中间产品型号 $pushCenterSscIds = []; //已经装箱的中间悬挂型号ID foreach ($items as $k => $item) { $make = $item['ssc_make']; $model = $item['ssc_model']; $name = $item['ss_name']; $ssc_id = $item['ssc_id']; $ssc_outnum = $item['ssc_outnum']; $pos1 = $item['pos1']; $pos2 = $item['pos2']; //转向机防尘罩修理包 if ($name == 'Steering Rack Bellow Kit') { //$data[$pos1][] = [$item]; $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } if ($item['ssc_outnum'] == '541147S') { // dd($data); } } //转向机防尘罩 if ($name == 'Steering Boot') { //大小车型相同时，Steering Rack Bellow Kit 与 Steering Boot 对应的GSP型号是通用关系时，只取 Steering Rack Bellow Kit 的数据 $sql = "SELECT t.*, pro1.p_outnum outnum1,pro2.p_outnum outnum2 FROM ( SELECT pe_p_id1 p_id1,pe_p_id2 p_id2 FROM pro_exchange UNION SELECT pemn_p_id1 p_id1,pemn_p_id2 p_id2 FROM pro_exchange_model_numidx ) t JOIN pro pro1 ON pro1.p_id = p_id1 JOIN pro pro2 ON pro2.p_id = p_id2 WHERE (pro1.p_outnum = '" . $item['ssc_outnum'] . "' OR pro2.p_outnum = '" . $item['ssc_outnum'] . "') "; $trows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); $hasSteeringRackBellowKit = false; foreach ($trows as $trow) { if ($trow['outnum1'] == $item['ssc_outnum']) { if (in_array($trow['outnum2'], $outnums)) { $hasSteeringRackBellowKit = true; break; } } elseif ($trow['outnum2'] == $item['ssc_outnum']) { if (in_array($trow['outnum1'], $outnums)) { $hasSteeringRackBellowKit = true; break; } } } if (!$hasSteeringRackBellowKit) { if ($pos2 == 'L/R' || $pos2 == '') { $data[$pos1][] = [ 'L/R' => $item ]; } elseif ($pos2 == 'L') { $rightItem = $this->findRight($items, $item, $skips); $data[$pos1][] = [ 'L' => $item, 'R' => $rightItem ]; } } } // 单拉杆, 拉杆球头 if ($name == 'Tie Rod End') { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds, 'Tie Rod Axle Joint'); foreach ($arr as $value) { $data[$pos1][] = $value; } } // 拉杆球头 (剩余的) if ($name == 'Tie Rod Axle Joint') { //if (!in_array($ssc_outnum, $pushCenterOutnums)) { if (!in_array($ssc_id, $pushCenterSscIds)) { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } } } // 拉杆总成 if ($name == 'Rod Assembly') { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } } //Track Control Arm 控制臂, 控制臂衬套 Control Arm Bush $controlArmArr = []; if ($name == 'Track Control Arm' || $name == 'Control Arm') { $controlArmArr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds, [ 'Ball Joint', 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', ]); } // 球头 Ball Joint (剩余的) if ($name == 'Ball Joint') { //if (!in_array($ssc_outnum, $pushCenterOutnums)) { if (!in_array($ssc_id, $pushCenterSscIds)) { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } } } //先填好剩余的球头，再填 控制臂 及组合 if ($controlArmArr) { foreach ($controlArmArr as $value) { $data[$pos1][] = $value; } } // 控制臂衬套 (剩余的) if (in_array($name, [ 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', ])) { //if (!in_array($ssc_outnum, $pushCenterOutnums)) { if (!in_array($ssc_id, $pushCenterSscIds)) { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } } } // 控制臂套件 if ($name == 'Suspension Repair Kit') { $data[$pos1][] = [$item]; } // 平衡杆 Rod/Strut,stabiliser,平衡/稳定杆衬套 Stabiliser Bushing, 平衡/稳定杆衬套修理包 Stabiliser Bushing Kit if ($name == 'Rod/Strut,stabiliser') { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds, [ 'Stabiliser Bushing', 'Stabiliser Bushing Kit', ]); foreach ($arr as $value) { $data[$pos1][] = $value; } } //平衡/稳定杆衬套, 平衡/稳定杆衬套修理包 (剩余的) if (($name === 'Stabiliser Bushing' || $name === 'Stabiliser Bushing Kit')) { //if (!in_array($ssc_outnum, $pushCenterOutnums)) { if (!in_array($ssc_id, $pushCenterSscIds)) { $arr = $this->findFsAndLrItemsSub($items, $item, $pushCenterOutnums, $pushCenterSscIds); foreach ($arr as $value) { $data[$pos1][] = $value; } } } $pushCenterOutnums = array_unique($pushCenterOutnums); $pushCenterSscIds = array_unique($pushCenterSscIds); } return $data; } public function findFsAndLrItemsSub($items, $item, &$pushCenterOutnums, &$pushCenterSscIds, $findNames = null) { $make = $item['ssc_make']; $model = $item['ssc_model']; $name = $item['ss_name']; $pos1 = $item['pos1']; $pos2 = $item['pos2']; if ($pos2 == 'R') { return []; } $data = []; if ($pos2 == 'L/R' || $pos2 == '') { $rightItems[] = $item; } elseif ($pos2 == 'L') { $rightItems = $this->findRight2($items, $item); } $centerArr = []; if ($findNames) { $centerItems = $this->findCenter($items, $item, $findNames); //找中间产品 if ($centerItems) { //找中间产品的左右配对 foreach ($centerItems as $centerItem) { if (in_array($centerItem['ss_name'],[ 'Stabiliser Bushing', 'Stabiliser Bushing Kit']) ) { if (in_array($centerItem['ss_id'], $this->skipIds)) { continue;//平衡杆衬套不重复出现 } $this->skipIds[$centerItem['ss_id']] = $centerItem['ss_id']; } //Inner And Outer 分拆 if ($centerItem['pos3'] == 'I/O') { if ($item['pos3'] == 'I') { $centerItem['pos_name'] = str_replace('Inner And Outer', 'Inner', $centerItem['pos_name']); } elseif ($item['pos3'] == 'O') { $centerItem['pos_name'] = str_replace('Inner And Outer', 'Outer', $centerItem['pos_name']); } } $centerRightItems = []; if ($centerItem['pos2'] == 'L/R' || $centerItem['pos2'] == '') { $centerRightItems[] = $centerItem; } elseif ($centerItem['pos2'] == 'L') { $centerRightItems = $this->findRight2($items, $centerItem); } $pushCenterOutnums[] = $centerItem['ssc_outnum']; $pushCenterSscIds[] = $centerItem['ssc_id']; foreach ($centerRightItems as $v) { $pushCenterOutnums[] = $v['ssc_outnum']; $pushCenterSscIds[] = $v['ssc_id']; //Inner And Outer 分拆 if ($v['pos3'] == 'I/O') { if ($item['pos3'] == 'I') { $v['pos_name'] = str_replace('Inner And Outer', 'Inner', $v['pos_name']); } elseif ($item['pos3'] == 'O') { $v['pos_name'] = str_replace('Inner And Outer', 'Outer', $v['pos_name']); } } $centerArr[] = [ 'L' => $centerItem, 'R' => $v, ]; } } } } foreach ($rightItems as $rightItem) { if (empty($centerArr)) { $data[] = [ 'L' => $item, 'R' => $rightItem ]; } else { foreach ($centerArr as $v) { $data[] = [ 'L' => $item, 'C' => $v, 'R' => $rightItem ]; } } } return $data; } public function drawItems($sheet, $items) { $insertFront = false; $insertRear = false; $HasInsertPart0 = false; //是否有 转向系之前的数据 $insertPart1 = false; //是否插入完了 转向系 foreach ($items as $pos1 => $rows) { if ($pos1 == 'F' && !$insertFront) { $insertFront = true; $this->insertFrontAxle($sheet, 1, $this->rowNum); } if ($pos1 == 'R' && !$insertRear) { $insertRear = true; if ($insertFront) { $this->insertJgAxle($sheet, 1, $this->rowNum); $this->insertRearAxle($sheet, 1, $this->rowNum); } else { $this->insertRearAxle($sheet, 1, $this->rowNum, 'noJg'); } } foreach ($rows as $row) { $startRow = $this->rowNum; $maxInsertRowsCount = 0; $isLR = false; $centerIsLR = false; $leftItem = $row['L'] ?: []; if (empty($leftItem)) { $leftItem = $row[0]; } $rightItem = $row['R'] ?: []; $centerItems = $row['C'] ?: []; if ($leftItem && $rightItem) { $isLR = $leftItem['ssc_outnum'] == $rightItem['ssc_outnum']; } if ($centerItems) { $centerLeftItem = $centerItems['L'] ?: []; $centerRightItem = $centerItems['R'] ?: []; $centerIsLR = $centerLeftItem['ssc_outnum'] == $centerRightItem['ssc_outnum']; } $name = $leftItem['ss_name']; //转向机防尘罩修理包 if ($name == 'Steering Rack Bellow Kit') { //include(__DIR__ . '/ss/draw/drawCenterOneLR.php'); include(__DIR__ . '/ss/draw/drawLR.php'); $HasInsertPart0 = true; } // 转向机防尘罩 if ($name == 'Steering Boot') { include(__DIR__ . '/ss/draw/drawLR.php'); } // 单拉杆, 拉杆球头 if ($name == 'Tie Rod End') { include(__DIR__ . '/ss/draw/drawCenterMore.php'); $HasInsertPart0 = true; } //拉杆球头 if ($name == 'Tie Rod Axle Joint') { include(__DIR__ . '/ss/draw/drawLR.php'); $HasInsertPart0 = true; } // 拉杆总成 if ($name == 'Rod Assembly') { include(__DIR__ . '/ss/draw/drawLR.php'); $HasInsertPart0 = true; } //球头 Ball Joint if ($name == 'Ball Joint') { if ($HasInsertPart0 === true && $insertPart1 === false) { $insertPart1 = true; $addRowCout = 3; // 转向系 前面多加几行 for ($i = $startRow; $i < ($startRow + $addRowCout); $i++) { $isBreak = $this->addRowNum($sheet, 'Empty'); if ($isBreak) { break; } else { $this->setBgColor($sheet, $i); } } } include(__DIR__ . '/ss/draw/drawLR.php'); } //Track Control Arm 控制臂, 控制臂衬套 Control Arm Bush if ($name == 'Track Control Arm' || $name == 'Control Arm') { if ($HasInsertPart0 === true && $insertPart1 === false) { $insertPart1 = true; $addRowCout = 3; // 转向系 前面多加几行 for ($i = $startRow; $i < ($startRow + $addRowCout); $i++) { $isBreak = $this->addRowNum($sheet, 'Empty'); if ($isBreak) { break; } else { $this->setBgColor($sheet, $i); } } } include(__DIR__ . '/ss/draw/drawCenterMore.php'); } // 控制臂衬套修理包 Control Arm Bush Kit if (in_array($name, [ 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', ])) { include(__DIR__ . '/ss/draw/drawLR.php'); } // 控制臂套件 if ($name == 'Suspension Repair Kit') { include(__DIR__ . '/ss/draw/drawOne.php'); } // 平衡杆 Rod/Strut,stabiliser,平衡/稳定杆衬套 Stabiliser Bushing, 平衡/稳定杆衬套修理包 Stabiliser Bushing Kit if ($name == 'Rod/Strut,stabiliser') { include(__DIR__ . '/ss/draw/drawCenterMore.php'); } //平衡/稳定杆衬套 Stabiliser Bushing, 平衡/稳定杆衬套修理包 Stabiliser Bushing Kit if (($name === 'Stabiliser Bushing' || $name === 'Stabiliser Bushing Kit')) { include(__DIR__ . '/ss/draw/drawLR.php'); } if ($maxInsertRowsCount > 0) { $regI = 0; for ($i = $startRow; $i < ($startRow + $maxInsertRowsCount); $i++) { $isBreak = $this->addRowNum($sheet, 'Image', $regI); $this->setBgColor($sheet, $i); $regI++; } $isBreak = $this->addRowNum($sheet, 'ImageEmpty'); //在型号块后再插入一行 $this->setBgColor($sheet, $this->rowNum); } } } //foreach $this->buPageHeight($sheet, $this->rowNum); ///补最后一页高度 } public function insertItems($sheet, $items) { $insertFront = false; $insertRear = false; $HasInsertPart0 = false; //是否有 转向系之前的数据 $insertPart1 = false; //是否插入完了 转向系 $skips = []; foreach ($items as $k => $item) { //正式环境 $imgFile = \mvc::$cfg['dir']['files'] . '/' . substr($item['filename'], 0, strrpos($item['filename'], '.')) . '.' . $item['fileextname']; if (!file_exists($imgFile)) { //测试环境 $imgFile = $this->staticDir . '/test/' . $item['ssc_outnum'] . '.png'; } if (is_file($imgFile)) { $tmpFilename = $this->tmpDir . '/' . md5($imgFile) . '.png'; if (!file_exists($tmpFilename)) { $imgFile = $this->image->Cut2($imgFile, $tmpFilename, 400, 400); } $items[$k]['imgFile'] = $tmpFilename; $items[$k]['noimgFile'] = false; } else { //unset($items[$k]); $items[$k]['noimgFile'] = true; } $items[$k]['ss_name'] = trim($items[$k]['ss_name']); } $outnums = array_unique(array_column($items, 'ssc_outnum')); foreach ($items as $k => $item) { $maxInsertRowsCount = 0; $currentKey = $k; if (in_array($currentKey, $skips)) { continue; } $name = $item['ss_name']; if ($item['pos1'] == 'F' && !$insertFront) { $insertFront = true; $this->insertFrontAxle($sheet, 1, $this->rowNum); } if ($item['pos1'] == 'R' && !$insertRear) { $insertRear = true; if ($insertFront) { $this->insertJgAxle($sheet, 1, $this->rowNum); $this->insertRearAxle($sheet, 1, $this->rowNum); } else { $this->insertRearAxle($sheet, 1, $this->rowNum, 'noJg'); } } $startRow = $this->rowNum; //转向机防尘罩修理包 if ($name == 'Steering Rack Bellow Kit') { include(__DIR__ . '/ss/SteeringRackBellowKit.php'); $HasInsertPart0 = true; } // 转向机防尘罩 if ($name == 'Steering Boot') { //大小车型相同时，Steering Rack Bellow Kit 与 Steering Boot 对应的GSP型号是通用关系时，只取 Steering Rack Bellow Kit 的数据 $sql = "SELECT t.*, pro1.p_outnum outnum1,pro2.p_outnum outnum2 FROM ( SELECT pe_p_id1 p_id1,pe_p_id2 p_id2 FROM pro_exchange UNION SELECT pemn_p_id1 p_id1,pemn_p_id2 p_id2 FROM pro_exchange_model_numidx ) t JOIN pro pro1 ON pro1.p_id = p_id1 JOIN pro pro2 ON pro2.p_id = p_id2 WHERE (pro1.p_outnum = '" . $item['ssc_outnum'] . "' OR pro2.p_outnum = '" . $item['ssc_outnum'] . "') "; $trows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); $hasSteeringRackBellowKit = false; foreach ($trows as $trow) { if ($trow['outnum1'] == $item['ssc_outnum']) { if (in_array($trow['outnum2'], $outnums)) { $hasSteeringRackBellowKit = true; break; } } elseif ($trow['outnum2'] == $item['ssc_outnum']) { if (in_array($trow['outnum1'], $outnums)) { $hasSteeringRackBellowKit = true; break; } } } if (!$hasSteeringRackBellowKit) { include(__DIR__ . '/ss/SteeringBoot.php'); } $HasInsertPart0 = true; } // 单拉杆, 拉杆球头 if ($name == 'Tie Rod End') { include(__DIR__ . '/ss/TieRodEnd.php'); $HasInsertPart0 = true; } //拉杆球头 if ($name == 'Tie Rod Axle Joint') { include(__DIR__ . '/ss/TieRodAxleJoint.php'); $HasInsertPart0 = true; } // 拉杆总成 if ($name == 'Rod Assembly') { include(__DIR__ . '/ss/RodAssembly.php'); $HasInsertPart0 = true; } //球头 Ball Joint if ($name == 'Ball Joint') { if ($HasInsertPart0 === true && $insertPart1 === false) { $insertPart1 = true; $addRowCout = 3; // 转向系 前面多加几行 for ($i = $startRow; $i < ($startRow + $addRowCout); $i++) { $isBreak = $this->addRowNum($sheet, 'Empty'); if ($isBreak) { break; } else { $this->setBgColor($sheet, $i); } } } include(__DIR__ . '/ss/BallJoint.php'); } //Track Control Arm 控制臂, 控制臂衬套 Control Arm Bush if ($name == 'Track Control Arm' || $name == 'Control Arm') { if ($HasInsertPart0 === true && $insertPart1 === false) { $insertPart1 = true; $addRowCout = 3; // 转向系 前面多加几行 for ($i = $startRow; $i < ($startRow + $addRowCout); $i++) { $isBreak = $this->addRowNum($sheet, 'Empty'); if ($isBreak) { break; } else { $this->setBgColor($sheet, $i); } } } include(__DIR__ . '/ss/ControlArm.php'); } // 控制臂衬套修理包 Control Arm Bush Kit if (in_array($name, [ 'Control Arm Bush', 'Control Arm Bushing', 'Control Arm Bush Kit', 'Control Arm Bushing Kit', ]) && !in_array($currentKey, $skips)) { include(__DIR__ . '/ss/ControlArmBushKit.php'); } // 控制臂套件 if ($name == 'Suspension Repair Kit') { include(__DIR__ . '/ss/SuspensionRepairKit.php'); } // 平衡杆 Rod/Strut,stabiliser,平衡/稳定杆衬套 Stabiliser Bushing, 平衡/稳定杆衬套修理包 Stabiliser Bushing Kit if ($name == 'Rod/Strut,stabiliser') { include(__DIR__ . '/ss/RodStrutstabiliser.php'); } //平衡/稳定杆衬套 Stabiliser Bushing, 平衡/稳定杆衬套修理包 Stabiliser Bushing Kit if (($name === 'Stabiliser Bushing' || $name === 'Stabiliser Bushing Kit') && !in_array($currentKey, $skips)) { include(__DIR__ . '/ss/StabiliserBushing.php'); } if ($maxInsertRowsCount > 0) { $regI = 0; for ($i = $startRow; $i < ($startRow + $maxInsertRowsCount); $i++) { $isBreak = $this->addRowNum($sheet, 'Image', $regI); $this->setBgColor($sheet, $i); $regI++; } $isBreak = $this->addRowNum($sheet, 'ImageEmpty'); //在型号块后再插入一行 $this->setBgColor($sheet, $this->rowNum); } } //foreach $this->buPageHeight($sheet, $this->rowNum); ///补最后一页高度 } public function findRight2($items, $item){ $data = []; if($item['ssc_lr']) { $lrArr = explode('/', trim($item['ssc_lr'])); $lrArr = array_unique($lrArr); foreach ($lrArr as $outnum) { if (empty(trim($outnum)) || $outnum == $item['ssc_outnum']) { //continue;//自己 , 如果包含有自己，那就是一个特殊的 左右通用 } $isFind = false; //先找位置完全对应的 foreach ($items as $ak => $v) { if ( $v['ssc_outnum'] == $outnum && $v['pos1'] === $item['pos1'] && ($v['pos3'] === $item['pos3'] || $v['pos3'] == 'I/O' || $item['pos3'] == 'I/O') && ($v['pos4'] === $item['pos4']) ) { $data[] = $v; $isFind = true; } } if (!$isFind) { //找不到位置完全对应的，忽略位置强制配对 foreach ($items as $ak => $v) { if ($v['ssc_outnum'] == $outnum) { $data[] = $v; } } } } } if (empty($data)) { // 找配对表 $findPairOutnums = []; $rows = $this->findPair($item['ssc_outnum']); foreach ($rows as $row) { if ($row['outnum1'] == $item['ssc_outnum']) { $findPairOutnums[] = $row['outnum2']; } else { $findPairOutnums[] = $row['outnum1']; } } if ($findPairOutnums) { foreach ($findPairOutnums as $findPairOutnum) { $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($item, $findPairOutnum) { return $v['pos2'] === 'R' && $v['ssc_outnum'] == $findPairOutnum; }, ARRAY_FILTER_USE_BOTH); if ($filteredItemsWithKeys) { foreach ($filteredItemsWithKeys as $key => $value) { $data[] = $value; } } } } if (empty($data)) { $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($item) { return $v['pos1'] === $item['pos1'] && $v['ss_name'] === $item['ss_name'] && $v['pos2'] === 'R' && ($v['pos3'] === $item['pos3'] || $v['pos3'] == 'I/O' || $item['pos3'] == 'I/O') && $v['pos4'] === $item['pos4'] && $v['ss_remark'] === $item['ss_remark'] && $v['ssc_outnum'] !== $item['ssc_outnum']; }, ARRAY_FILTER_USE_BOTH); if ($filteredItemsWithKeys) { foreach ($filteredItemsWithKeys as $key => $value) { $data[] = $value; } } else { // } } } return $data; } public function findCenter($items, $item, $findNames) { $data = []; //自定义父子 if ($item['ssc_fs']) { if (trim(strtolower($item['ssc_fs'])) == 'null') { return $data; // 为 null 时不找; } $fsArr = explode('/', trim($item['ssc_fs'])); $fsArr = array_unique($fsArr); foreach ($fsArr as $outnum) { if (empty(trim($outnum)) || $outnum == $item['ssc_outnum']) { continue; //自己 } $isFind = false; //先找位置完全对应的 foreach ($items as $ak => $v) { if ($v['ssc_outnum'] == $outnum && $v['pos1'] === $item['pos1'] && ($v['pos3'] === $item['pos3'] || $v['pos3'] == 'I/O' || $item['pos3'] == 'I/O') && ($v['pos4'] === $item['pos4']) ) { $data[] = $v; $isFind = true; } } if (!$isFind) { //找不到位置完全对应的，忽略位置强制配对 foreach ($items as $ak => $v) { if ($v['ssc_outnum'] == $outnum) { $data[] = $v; } } } } } //找产品库中的父子关系 if (empty($data)) { $sonOutnums = $this->findSonOutnums($item['ssc_outnum']); if ($sonOutnums) { $isFind = false; //先找位置完全对应的 foreach ($sonOutnums as $sonOutnum) { foreach ($items as $sonKey => $v) { if ($v['ssc_outnum'] == $sonOutnum && in_array($v['ss_name'], $findNames) && $v['pos1'] === $item['pos1'] && ($v['pos3'] === $item['pos3'] || $v['pos3'] == 'I/O' || $item['pos3'] == 'I/O') && ($v['pos4'] === $item['pos4']) ) { $data[] = $v; $isFind = true; } } } if (!$isFind) { //找不到位置完全对应的，忽略位置强制配对 foreach ($sonOutnums as $sonOutnum) { foreach ($items as $sonKey => $v) { if ($v['ssc_outnum'] == $sonOutnum && in_array($v['ss_name'], $findNames)) { $data[] = $v; } } } } } } if (empty($data)) { if (!is_array($findNames)) $findNames = [$findNames]; $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($items, $item, $findNames) { return isset($v['pos1']) && $v['pos1'] === $item['pos1'] && ($v['pos2'] == 'L' || $v['pos2'] == 'L/R' || $v['pos2'] == '') && ($v['pos3'] === $item['pos3'] || $v['pos3'] == 'I/O' || $item['pos3'] == 'I/O') && ($v['pos4'] === $item['pos4']) && isset($v['ss_name']) && in_array($v['ss_name'], $findNames) && isset($v['ssc_outnum']) && $v['ssc_outnum'] !== $item['ssc_outnum']; }, ARRAY_FILTER_USE_BOTH); foreach ($filteredItemsWithKeys as $key => $value) { $data[] = $value; if ($item['ss_name'] == 'Rod/Strut,stabiliser') { break; //平衡杆 只找一个 } } } return $data; } public function findRight($items, $item, &$skips) { if ($item['ssc_outnum'] == 'S080013') { //dd($item); } $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($item, $skips) { return $v['pos1'] === $item['pos1'] && $v['ss_name'] === $item['ss_name'] && $v['pos2'] === 'R' && $v['pos3'] === $item['pos3'] && $v['pos4'] === $item['pos4'] && $v['ss_remark'] === $item['ss_remark'] && !in_array($ak, $skips) && $v['ssc_outnum'] !== $item['ssc_outnum']; }, ARRAY_FILTER_USE_BOTH); if ($filteredItemsWithKeys) { foreach ($filteredItemsWithKeys as $key => $value) { $skips[] = $key; } return $value; } else { // 找配对表 $findPairOutnum = ''; $rows = $this->findPair($item['ssc_outnum']); foreach ($rows as $row) { if ($row['outnum1'] == $item['ssc_outnum']) { $findPairOutnum = $row['outnum2']; } else { $findPairOutnum = $row['outnum1']; } break; } if ($findPairOutnum) { $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($item, $skips, $findPairOutnum) { return $v['pos2'] === 'R' && !in_array($ak, $skips) && $v['ssc_outnum'] == $findPairOutnum; }, ARRAY_FILTER_USE_BOTH); if ($filteredItemsWithKeys) { foreach ($filteredItemsWithKeys as $key => $value) { $skips[] = $key; } return $value; } } return null; } } /** * 找配对表-记录 */ public function findPair($outnum) { $sql = sprintf("SELECT pair.*,pro1.p_outnum outnum1,pro2.p_outnum outnum2 from pro_pair pair JOIN pro pro1 ON pro1.p_id = pair.pp_p_id1 JOIN pro pro2 ON pro2.p_id = pair.pp_p_id2 JOIN pro_cate cate ON cate.id = pro1.p_cate WHERE (pro1.p_outnum = '%s' OR pro2.p_outnum = '%s') AND pro1.p_cate = pro2.p_cate", $outnum, $outnum); $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); return $rows; } /** * 找父零件-记录集 */ public function findFatherRows($outnum) { $sql = sprintf("SELECT fs.*,pro1.p_outnum father_outnum,pro2.p_outnum son_outnum,cate2.en_name son_name from pro_fatherson fs JOIN pro pro1 ON pro1.p_id = fs.pf_father_pid JOIN pro pro2 ON pro2.p_id = fs.pf_son_pid JOIN pro_cate cate2 ON cate2.id = pro2.p_cate WHERE pro2.p_outnum = '%s'", $outnum); $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); return $rows; } /** * 找子零件-记录集 */ public function findSonRows($outnum) { $sql = sprintf("SELECT fs.*,pro1.p_outnum father_outnum,pro2.p_outnum son_outnum,cate2.en_name son_name from pro_fatherson fs JOIN pro pro1 ON pro1.p_id = fs.pf_father_pid JOIN pro pro2 ON pro2.p_id = fs.pf_son_pid JOIN pro_cate cate2 ON cate2.id = pro2.p_cate WHERE pro1.p_outnum = '%s'", $outnum); $rows = $this->db->query($sql)->fetchAll(\PDO::FETCH_ASSOC); return $rows; } /** * 找父零件-gsp型号集 */ public function findFatherOutnums($outnum) { $rows = $this->findFatherRows($outnum); $data = []; foreach ($rows as $row) { $data[] = $row['father_outnum']; } return $data; } /** * 找子零件-gsp型号集 */ public function findSonOutnums($outnum) { $rows = $this->findSonRows($outnum); $data = []; foreach ($rows as $row) { $data[] = $row['son_outnum']; } return $data; } /** 找中间产品 */ public function findCenterNo($names, $items, $item, $rule, &$skips) { $filteredItemsWithKeys = array_filter($items, function ($v, $ak) use ($names, $item, $rule, $skips) { $result = isset($v['pos1']) && $v['pos1'] === $item['pos1'] && isset($v['ss_name']) && in_array($v['ss_name'], $names) && isset($v['ssc_outnum']) && $v['ssc_outnum'] !== $item['ssc_outnum'] && !in_array($ak, $skips); if (isset($rule['pos2'])) { $result = $result && $v['pos2'] == $rule['pos2']; } if (isset($rule['pos3'])) { $result = $result && $v['pos3'] == $rule['pos3']; } if (isset($rule['pos4'])) { $result = $result && $v['pos4'] == $rule['pos4']; } if (isset($rule['ss_remark_OR_empty'])) { $result = $result && ($v['ss_remark'] == $rule['ss_remark'] || $v['ss_remark'] == ''); } else if (isset($rules['ss_remark'])) { $result = $result && $v['ss_remark'] == $rule['ss_remark']; } return $result; }, ARRAY_FILTER_USE_BOTH); return $filteredItemsWithKeys; } public function setBgColor($sheet, $row, $color = 'F4F4F4', $force = false) { //$sheet->getStyle("A{$row}:J{$row}")->getFill()->setFillType(Fill::FILL_SOLID); //$sheet->getStyle("A{$row}:J{$row}")->getFill()->getStartColor()->setRGB($color); //$range = "A{$row}:K{$row}"; foreach (range('A', 'K') as $char) { $cell = $char . $row; $style = $sheet->getStyle($cell); if ( !$force //&& $style->getFill()->getFillType() == Fill::FILL_SOLID //&& $style->getFill()->getStartColor()->getARGB() != 'FFFFFFFF' && $sheet->getCell($cell)->getValue() != '' ) { //如果不是白色且不强制覆盖，则跳过; } else { $style->getFill() ->setFillType(Fill::FILL_SOLID) ->getStartColor() ->setRGB($color); } } } public function posAddToName($item) { $item['ss_name'] = $this->getNameByPos($item); return $item; } public function addRowNum($sheet, $type, $regI = '') { $result = false; // 是否有分页 if ($type == 'Image' && $regI == 0) { $this->activeImageRow = $this->rowNum; } $activePageHeight = $this->getActivePageHeight($sheet); if ($type == 'JgAxle') { $activePageHeight = $activePageHeight + 30; // 防止间隔自动分页时有隔断 } if ($activePageHeight > MAX_PAGE_HEIGHT) { if ($type == 'Image') { //超出一页 型号块 $tmpPageHeight = $this->getActivePageHeight($sheet, $this->activePageStartRow, $this->activeImageRow - 1); $this->buPageHeight($sheet, $this->activeImageRow - 1); //补上一页高度 //$this->setRowHeight($sheet, $this->activeImageRow - 1, MAX_PAGE_HEIGHT - $tmpPageHeight); $this->setBreak($sheet, $this->activeImageRow - 1, $type); } elseif ($type == 'ImageEmpty') { $this->activeImageEmptyRow = $this->rowNum; } elseif ($type != 'Header') { //超出一页 其他行 例间隔, 空行 $tmpPageHeight = $this->getActivePageHeight($sheet, $this->activePageStartRow, $this->rowNum - 1); $this->setRowHeight($sheet, $this->rowNum - 1, MAX_PAGE_HEIGHT - $tmpPageHeight); $this->setBreak($sheet, $this->rowNum - 1, $type); $this->activeImageEmptyRow = 0; } $result = true; } $this->rowNum++; return $result; //有没有分页 } //当前页面高度 public function getActivePageHeight($sheet, $beginRow = 0, $endRow = 0) { $result = 0; if ($beginRow == 0) $beginRow = $this->activePageStartRow; if ($endRow == 0) $endRow = $this->rowNum; for ($i = $beginRow; $i <= $endRow; $i++) { $h = $this->getRowHeight($sheet, $i); $result += $h; } return $result; } //分页 public function setBreak($sheet, $row, $type = '') { if ($type == 'Header') { if ($this->activeImageEmptyRow > 0) { $row = $row - 1; } $style = $sheet->getStyle('A' . $row); $fill = $style->getFill(); $bgColor = $fill->getStartColor()->getRGB(); if ($bgColor != 'F4F4F4' && $bgColor != 'F6AC2D' && $bgColor != 'D0CECE') { $row = $row - 1; } $row = $this->buPageHeight($sheet, $row); ///补上一页高度 } $this->activePageStartRow = $row + 1; $colChar = $this->int2Excel(1); $sheet->setBreak($colChar . $row, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); return $row; } public function buPageHeight($sheet, $row) { //上一页高度不足 $activePageHeight = $this->getActivePageHeight($sheet); if ($activePageHeight < MAX_PAGE_HEIGHT) { //if( MAX_PAGE_HEIGHT - $activePageHeight > 408) { //插入行 while (($activePageHeight + DEFAULT_ROW_HEIGHT) < MAX_PAGE_HEIGHT) { $this->addRowNum($sheet, 'Empty'); $this->setBgColor($sheet, $this->rowNum); $row = $row + 1; $activePageHeight = $this->getActivePageHeight($sheet, $this->activePageStartRow, $this->rowNum); } //} $activePageHeight = $this->getActivePageHeight($sheet); $this->setRowHeight($sheet, $this->rowNum - 1, MAX_PAGE_HEIGHT - $activePageHeight); } return $row; } public function getRowHeight($sheet, $row) { $rowHeight = $sheet->getRowDimension($row)->getRowHeight(); return $rowHeight > 0 ? $rowHeight : DEFAULT_ROW_HEIGHT; } public function setRowHeight($sheet, $row, $height) { if (!isset($this->isSetHeightRows[$row]) || $this->isSetHeightRows[$row] < $height) { $this->isSetHeightRows[$row] = $height; $sheet->getRowDimension($row)->setRowHeight($height); } } }