foreach ($list as $key => $val) {
$orderId = $val['ORDER_ID'];
//訂單業(yè)績(jī)歸屬人
$orderPerform = $OrderQueryMod->getOrderPerform($orderId);
//訂單產(chǎn)品信息
$productInfo = M('order_detail_info')->where(array('ORDER_ID' => $orderId))->select();
//訂單類型:1 正常訂單 2 擴(kuò)容續(xù)費(fèi) 3 壓貨 4 支付碼訂單
switch ($val['ORDER_TYPE']) {
case 1:
$ordertype = '正常訂單';
break;
case 2:
$ordertype = '擴(kuò)容續(xù)費(fèi)';
break;
case 3:
$ordertype = '壓貨訂單';
break;
case 4:
$ordertype = '支付碼訂單';
break;
}
foreach ($orderPerform as $k => $v) {
$num = 0;
foreach ($productInfo as $kk => $vv) {
$num++;
$salesOffId = M('consignment_info')->where(array('ORDER_ID' => $orderId))->getField('SALES_OFF');
$data[$j]['銷售部門(mén)'] = D('config_system')->where(array('CONFIG_KEY' => 'SALES_OFF', 'CONFIG_VALUE' => $salesOffId))->getField('CONFIG_DESCRIPTIO');
;
$data[$j]['訂單號(hào)'] = $orderId;
$data[$j]['訂單標(biāo)識(shí)碼'] = $orderId . '-' . $num;
$data[$j]['訂單金額'] = $vv['INCOME'] / 100;
$data[$j]['訂單類型'] = $ordertype;
$receivedMoney = ($vv['INCOME'] / 100) * (($val['RECEIVED_MONEY'] / 100) / ($val['TOTAL_MONEY'] / 100));
$data[$j]['到款金額'] = round($receivedMoney, 2);
$data[$j]['成本金額'] = $vv['COST'] / 100;
$performBfRebate = ($vv['INCOME'] / 100) * ($val['PERFORM_BF_REBATE'] / ($val['TOTAL_MONEY'] / 100));
$data[$j]['返點(diǎn)前業(yè)績(jī)'] = round($performBfRebate, 2);
$data[$j]['返點(diǎn)比例'] = $val['REBATE_PERCENT'];
$data[$j]['是否特價(jià)審批單'] = $val['IS_BARGAIN_PRICE'] == 0 ? '非特價(jià)' : '特價(jià)';
$data[$j]['結(jié)算方式'] = M('config_system')->where(array('CONFIG_KEY' => 'SETTLEMENT_TYPE', 'CONFIG_VALUE' => $val['PAY_TYPE']))->getField('CONFIG_DESCRIPTIO');
$salesmanId = $val['SALESMAN_ID'];
$deptId = $v['DEPT_ID'];
$data[$j]['部門(mén)'] = M('config_dept_info')->where(array('DEPT_ID' => $deptId))->getField('DEPT_NAME');
$data[$j]['業(yè)績(jī)歸屬人'] = $v['SALESMAN_NAME'];
$data[$j]['業(yè)績(jī)分配比例'] = $v['PERCENT'];
$saler_info = $OrderQueryMod->GetOrderSaler($val['APPLY_SALER_ID']);
$second_saler_info = $OrderQueryMod->GetOrderSaler($val['SECONDARY_SALER_ID']);
$data[$j]['一級(jí)經(jīng)銷商SAP主數(shù)據(jù)編號(hào)'] = empty($saler_info['SAP_UID']) ? '無(wú)' : $saler_info['SAP_UID'];
$data[$j]['經(jīng)銷商全稱'] = $saler_info['REAL_NAME'];
$data[$j]['二級(jí)經(jīng)銷商SAP主數(shù)據(jù)編號(hào)'] = empty($second_saler_info['SAP_UID']) ? '無(wú)' : $second_saler_info['SAP_UID'];
$data[$j]['二級(jí)經(jīng)銷商'] = $second_saler_info['REAL_NAME'];
$consignerTime = $val['SAP_CONSIGNER_TIME'];
$data[$j]['發(fā)貨日期'] = empty($consignerTime) ? '無(wú)' : date('Y-m-d', strtotime($consignerTime));
if (empty($consignerTime)) {
$data[$j]['季度'] = '';
$data[$j]['周起止'] = '';
} else {
$consignerTimeMonths = date('m', strtotime($consignerTime));
if (in_array($consignerTimeMonths, array('01', '02', '03'))) {
$data[$j]['季度'] = 1;
}
if (in_array($consignerTimeMonths, array('04', '05', '06'))) {
$data[$j]['季度'] = 2;
}
if (in_array($consignerTimeMonths, array('07', '08', '09'))) {
$data[$j]['季度'] = 3;
}
if (in_array($consignerTimeMonths, array('10', '11', '12'))) {
$data[$j]['季度'] = 4;
}
$lastday = date('Y-m-d', strtotime("$consignerTime Thursday"));
$firstday = date('Y-m-d', strtotime("$lastday -6 days"));
$data[$j]['周起止'] = date('m.d', strtotime($firstday)) . '-' . date('m.d', strtotime($lastday));
}
$finalUser = $OrderQueryMod->GetOrderFinaluser($val['FINAL_USER_ID']);
$data[$j]['最終用戶名稱'] = $finalUser['FINAL_USER_NAME'];
$data[$j]['項(xiàng)目類型'] = D('config_system')->where(array('CONFIG_KEY' => 'FINAL_USER_TYPE', 'CONFIG_VALUE' => $finalUser['FINAL_USER_TYPE']))->getField('CONFIG_DESCRIPTIO');;
$provinceId = $finalUser['FINALE_USER_PROVINCE'];
$areaId = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('AREA_ID');
$areaName = M('config_area_info')->where("AREA_ID = $areaId")->getField('AREA_NAME');
$data[$j]['區(qū)域'] = $areaName;
$provinceName = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('PROVINCE_NAME');
$data[$j]['省'] = $provinceName;
$cityId = $finalUser['FINALE_USER_CITY'];
$cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
$data[$j]['市'] = $cityName;
$data[$j]['詳細(xì)地址'] = $finalUser['FINAL_USER_ADDRESS'];
$customType = $finalUser['CUSTOM_TYPE'];
$customTypeName = M('customer_type_info')->where("CUSTOMER_TYPE = $customType")->getField('CUSTOMER_TYPE_NAME');
$data[$j]['客戶類型'] = $customTypeName;
$customIndustry = $finalUser['CUSTOMER_INDUSTRY'];
$customIndustryName = M('customer_industry_info')->where("CUSTOMER_INDUSTRY_ID = $customIndustry")->getField('CUSTOMER_INDUSTRY_NAME');
$data[$j]['客戶行業(yè)'] = $customIndustryName;
$data[$j]['CRM客戶編號(hào)'] = empty($finalUser['CRM_CUSTOM_ID']) ? '無(wú)' : $finalUser['CRM_CUSTOM_ID'];
$data[$j]['CRM項(xiàng)目編號(hào)'] = empty($val['CRM_PROJECT_ID']) ? '無(wú)' : $val['CRM_PROJECT_ID'];
$data[$j]['采購(gòu)聯(lián)系人'] = $finalUser['PURCHASER'];
$data[$j]['采購(gòu)聯(lián)系人電話'] = empty($finalUser['PURCHASER_PHONE']) ? '無(wú)' : $finalUser['PURCHASER_PHONE'];
$data[$j]['采購(gòu)聯(lián)系人郵箱'] = $finalUser['PURCHASER_EMAIL'];
$data[$j]['IT部/信息部負(fù)責(zé)人'] = $finalUser['TECHNICIAN'];
$data[$j]['IT部/信息部負(fù)責(zé)人電話'] = empty($finalUser['TECHNICIAN_PHONE']) ? '無(wú)' : $finalUser['TECHNICIAN_PHONE'];
$data[$j]['IT部/信息部負(fù)責(zé)人郵箱'] = $finalUser['TECHNICIAN_EMAIL'];
$data[$j]['WPS+賬號(hào)聯(lián)系人'] = $finalUser['WPS_CONTACTOR'];
$data[$j]['WPS+賬號(hào)聯(lián)系人電話'] = empty($finalUser['WPS_CONTACTOR_PHONE']) ? '無(wú)' : $finalUser['WPS_CONTACTOR_PHONE'];
$data[$j]['WPS+賬號(hào)聯(lián)系人郵箱'] = $finalUser['WPS_CONTACTOR_EMAIL'];
$wpsAccount = M('order_inferior_user')->where(array('ORDER_ID' => $orderId, 'WPS_ACCOUNT_ID' => array('neq', '')))->getField('WPS_ACCOUNT_ID', true);
$wpsAccountstr = empty($wpsAccount) ? '' : implode(',', $wpsAccount);
$data[$j]['WPS+賬號(hào)'] = $wpsAccountstr;
$data[$j]['支付碼'] = M('order_grant_code')->where(array('ORDER_ID' => $orderId))->order('ID DESC')->getField('GRANT_CODE');
if ($vv['PRODUCT_ID'] == 60 || $vv['PRODUCT_ID'] == 61 || $vv['PRODUCT_ID'] == 109 || $vv['PRODUCT_ID'] == 110) {
$data[$j]['是否贈(zèng)送服務(wù)'] = '否';
} else {
$data[$j]['是否贈(zèng)送服務(wù)'] = $vv['SERVICE_TYPE'] == '' || $vv['SERVICE_TYPE'] == -1 ? '否' : '是';
}
$productId = $vv['PRODUCT_ID'];
$productCategory = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->getField('PRODUCT_CATEGORY');
$data[$j]['產(chǎn)品類'] = M('config_system')->where(array('CONFIG_KEY' => 'PRODUCT_CATEGORY', 'CONFIG_VALUE' => $productCategory))->getField('CONFIG_DESCRIPTIO');
$data[$j]['物料描述'] = M('config_product_info')->where(array('PRODUCT_ID' => $productId))->getField('PRODUCT_DETAIL_NAME');
$productType = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->getField('PRODUCT_TYPE');
if ($productCategory == 1 && $productType == 1 || $productType == 3) {
$serialIds = M('serial_number')->where(array('ORDER_ID' => $orderId, 'SERIAL_NUMBER' => array('neq', '')))->getField('SERIAL_ID', true);
$serialTypeStr = '';
foreach ($serialIds as $serialId) {
$serialTypeInfo = M('serial_number')->where(array('SERIAL_ID' => $serialId))->field('USE_AREA, LANGUAGE_TYPE')->find();
$serialTypeStr .= implode(',', $serialTypeInfo) . '|';
}
$serialTypeStr = str_replace("china", "中國(guó)", $serialTypeStr);
$serialTypeStr = str_replace("england", "英國(guó)", $serialTypeStr);
$serialTypeStr = str_replace("japan", "日本", $serialTypeStr);
$serialTypeStr = str_replace("chn", "中文", $serialTypeStr);
$serialTypeStr = str_replace("enu", "英文", $serialTypeStr);
$serialTypeStr = str_replace("jpn", "日文", $serialTypeStr);
$data[$j]['序列號(hào)類型'] = rtrim($serialTypeStr, '|');
} else {
$data[$j]['序列號(hào)類型'] = '無(wú)';
}
if ($productCategory == 1) {
$authorizedType = $vv['AUTHORIZED_TYPE'];
$data[$j]['授權(quán)類型'] = M('config_system')->where(array('CONFIG_KEY' => 'AUTHORIZED_TYPE', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
} else {
$authorizedType = $vv['SPEC'];
$data[$j]['授權(quán)類型'] = M('config_system')->where(array('CONFIG_KEY' => 'PRODUCT_SPECS', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
}
$data[$j]['授權(quán)范圍'] = $vv['AUTHORIZED_RANGE'];
$data[$j]['介質(zhì)數(shù)量'] = $vv['TANN_COUNT'];
$data[$j]['授權(quán)數(shù)量'] = $vv['AMOUNT'];
$data[$j]['單價(jià)'] = $vv['PRICE'] / 100;
$data[$j]['到期后授權(quán)數(shù)量'] = $vv['EXPIRE_AUTHORIZED_AMOUNT'];
$data[$j]['服務(wù)年限'] = empty($vv['SERVICE_LENGTH']) ? 0 : $vv['SERVICE_LENGTH'];
$data[$j]['服務(wù)截止時(shí)間'] = empty($vv['SERVICE_LENGTH']) ? date('Y-m-d', strtotime('+0 years 10 days', strtotime($val['APPLY_TIME']))) : date('Y-m-d', strtotime('+' . $vv['SERVICE_LENGTH'] . ' years 10 days', strtotime($val['APPLY_TIME'])));
$data[$j]['授權(quán)年限'] = empty($vv['AUTHORIZED_YEARS']) ? 0 : $vv['AUTHORIZED_YEARS'];
if ($vv['AUTHORIZED_YEARS'] == '永久') {
$data[$j]['授權(quán)截止時(shí)間'] = '永久';
} else if ($vv['AUTHORIZED_YEARS'] == '隨設(shè)備') {
$data[$j]['授權(quán)截止時(shí)間'] = date('Y-m-d', strtotime('+5 years 10 days', strtotime($val['APPLY_TIME'])));
} else {
$data[$j]['授權(quán)截止時(shí)間'] = empty($vv['AUTHORIZED_YEARS']) ? date('Y-m-d', strtotime('+0 years 10 days', strtotime($val['APPLY_TIME']))) : date('Y-m-d', strtotime('+' . $vv['AUTHORIZED_YEARS'] . ' years 10 days', strtotime($val['APPLY_TIME'])));
}
$isFinal = M('saler_info')->where(array('SALER_ID' => $val['APPLY_SALER_ID']))->getField('IS_FINAL');
$data[$j]['訂方性質(zhì)'] = $isFinal == 1 ? '最終用戶' : '經(jīng)銷商';
$data[$j]['供貨方'] = M('order_info')->where(array('ORDER_ID' => $orderId))->getField('SALES_ORG');
$wuliao = M('config_product_info')->where(array('PRODUCT_ID' => $vv['PRODUCT_ID']))->find();
if (empty($wuliao['TAP']) && empty($wuliao['TANN']) && empty($wuliao['TAN'])) {
$data[$j]['SAP單號(hào)'] = '';
} else {
$data[$j]['SAP單號(hào)'] = empty($val['SAP_SALES_ORDER_ID']) ? '' : (int) $val['SAP_SALES_ORDER_ID'];
}
$data[$j]['合同編號(hào)'] = empty($val['CONTRACT_CODE']) ? '無(wú)' : $val['CONTRACT_CODE'];
$data[$j]['下單備注'] = $val['DESCRIPTION'];
$description = D('order_audit_log')->where(array('AUDIT_TYPE' => 1, 'LOG_STATE' => 1, 'ORDER_ID' => $orderId))->getField('DESCRIPTION');
$data[$j]['訂單規(guī)范檢查備注'] = empty($description) ? '' : $description;
$data[$j]['訂單審核備注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 3, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['序列號(hào)設(shè)置備注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 14, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['打印授權(quán)書(shū)審核備注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 15, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['報(bào)備審核備注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 2, 'LOG_STATE' => 1))->getField('DESCRIPTION');
if ($val['INVOICE_OWNER_PROVINCE'] == $val['INVOICE_OWNER_CITY']) {
$cityId = $val['INVOICE_OWNER_CITY'];
$cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
$data[$j]['發(fā)票收件地址'] = $cityName . $val['INVOICE_OWNER_ADDRESS'];
} else {
$provinceId = $val['INVOICE_OWNER_PROVINCE'];
$provinceName = M('config_province_info')->where("PROVINCE_ID = $provinceId")->getField('PROVINCE_NAME');
$cityId = $val['INVOICE_OWNER_CITY'];
$cityName = M('config_city_info')->where("CITY_ID = $cityId")->getField('CITY_NAME');
$data[$j]['發(fā)票收件地址'] = $provinceName . $cityName . $val['INVOICE_OWNER_ADDRESS'];
}
$data[$j]['發(fā)票收件人'] = $val['INVOICE_OWNER'];
$data[$j]['發(fā)票收件人電話'] = empty($val['INVOICE_OWNER_PHONE']) ? '無(wú)' : $val['INVOICE_OWNER_PHONE'];
$j++;
}
}
}
$list大約有4000左右條數(shù)據(jù) 打印日志到超時(shí)大約有20M的sql日志 每條sql執(zhí)行時(shí)間都在0.00幾秒 有什么辦法或者思路優(yōu)化呢?
4000次的循環(huán)本身并不大,如果循環(huán)里僅僅是對(duì)內(nèi)存的操作其實(shí)很快就應(yīng)該完成,但是你在循環(huán)里做了很多次數(shù)據(jù)庫(kù)操作,這應(yīng)該就是造成性能問(wèn)題的根本原因。盡管每條sql執(zhí)行都很快,但是你忽略了每次執(zhí)行所帶來(lái)的網(wǎng)絡(luò)io開(kāi)銷時(shí)間。我才想4000次的循環(huán)里如此多的數(shù)據(jù)庫(kù)操作足以是你的腳本超時(shí)了,當(dāng)你所提到超時(shí)時(shí),我認(rèn)為你的php運(yùn)行在fast cgi模式下。那么你有兩種方法來(lái)解決
1,將sql操作合并,一次或幾次在循環(huán)之外一口氣得到所有的數(shù)據(jù),再在循環(huán)中進(jìn)行分門(mén)別類。我相信這樣做會(huì)立竿見(jiàn)影的提升效率。
2, 如果這個(gè)操作不是及時(shí)性的,那么可以嘗試放在cli模式下運(yùn)行,你不用修改代碼,盡管效率同樣低,但cli模式下腳本不會(huì)超時(shí)。
另外如果你所獲得到數(shù)據(jù)總量很大,那么還要考慮php本身為腳本所分配的最大可用內(nèi)存,如果這個(gè)值低于你獲取的數(shù)據(jù)所需要的內(nèi)存,那么即便在cli模式腳本還是得崩。這個(gè)配置好像是在php.ini里一個(gè)叫max_memory_size定義的,名字可能不準(zhǔn)確,我記不太清了
sql查詢往往是最慢的IO操作,每次sql操作哪怕只是0.001秒,4000循環(huán),每個(gè)循環(huán)里面有10幾個(gè)sql操作,這就40秒了。
1.所以盡量不要在循環(huán)里面做sql操作,可以考慮合并查詢請(qǐng)求
$orderPerform = $OrderQueryMod->getOrderPerform($orderId);
可以在循環(huán)外批量查詢出來(lái)
$orderIdArr = array_column($list,'ORDER_ID');
$orderPerformArr = $OrderQueryMod->getOrderPerformArr($orderIdArr);
循環(huán)里面直接從數(shù)組里面取值
2.一些熱點(diǎn)數(shù)據(jù),尤其是更新還比較少的數(shù)據(jù),可以寫(xiě)入到緩存
北大青鳥(niǎo)APTECH成立于1999年。依托北京大學(xué)優(yōu)質(zhì)雄厚的教育資源和背景,秉承“教育改變生活”的發(fā)展理念,致力于培養(yǎng)中國(guó)IT技能型緊缺人才,是大數(shù)據(jù)專業(yè)的國(guó)家
北大青鳥(niǎo)中博軟件學(xué)院創(chuàng)立于2003年,作為華東區(qū)著名互聯(lián)網(wǎng)學(xué)院和江蘇省首批服務(wù)外包人才培訓(xùn)基地,中博成功培育了近30000名軟件工程師走向高薪崗位,合作企業(yè)超4
中公教育集團(tuán)創(chuàng)建于1999年,經(jīng)過(guò)二十年潛心發(fā)展,已由一家北大畢業(yè)生自主創(chuàng)業(yè)的信息技術(shù)與教育服務(wù)機(jī)構(gòu),發(fā)展為教育服務(wù)業(yè)的綜合性企業(yè)集團(tuán),成為集合面授教學(xué)培訓(xùn)、網(wǎng)
達(dá)內(nèi)教育集團(tuán)成立于2002年,是一家由留學(xué)海歸創(chuàng)辦的高端職業(yè)教育培訓(xùn)機(jī)構(gòu),是中國(guó)一站式人才培養(yǎng)平臺(tái)、一站式人才輸送平臺(tái)。2014年4月3日在美國(guó)成功上市,融資1
曾工作于聯(lián)想擔(dān)任系統(tǒng)開(kāi)發(fā)工程師,曾在博彥科技股份有限公司擔(dān)任項(xiàng)目經(jīng)理從事移動(dòng)互聯(lián)網(wǎng)管理及研發(fā)工作,曾創(chuàng)辦藍(lán)懿科技有限責(zé)任公司從事總經(jīng)理職務(wù)負(fù)責(zé)iOS教學(xué)及管理工作。
浪潮集團(tuán)項(xiàng)目經(jīng)理。精通Java與.NET 技術(shù), 熟練的跨平臺(tái)面向?qū)ο箝_(kāi)發(fā)經(jīng)驗(yàn),技術(shù)功底深厚。 授課風(fēng)格 授課風(fēng)格清新自然、條理清晰、主次分明、重點(diǎn)難點(diǎn)突出、引人入勝。
精通HTML5和CSS3;Javascript及主流js庫(kù),具有快速界面開(kāi)發(fā)的能力,對(duì)瀏覽器兼容性、前端性能優(yōu)化等有深入理解。精通網(wǎng)頁(yè)制作和網(wǎng)頁(yè)游戲開(kāi)發(fā)。
具有10 年的Java 企業(yè)應(yīng)用開(kāi)發(fā)經(jīng)驗(yàn)。曾經(jīng)歷任德國(guó)Software AG 技術(shù)顧問(wèn),美國(guó)Dachieve 系統(tǒng)架構(gòu)師,美國(guó)AngelEngineers Inc. 系統(tǒng)架構(gòu)師。