foreach ($list as $key => $val) {
$orderId = $val['ORDER_ID'];
//订单业绩归属人
$orderPerform = $OrderQueryMod->getOrderPerform($orderId);
//订单产品信息
$productInfo = M('order_detail_info')->where(array('ORDER_ID' => $orderId))->select();
//订单类型：1 正常订单 2 扩容续费 3 压货 4 支付码订单
switch ($val['ORDER_TYPE']) {
case 1:
$ordertype = '正常订单';
break;
case 2:
$ordertype = '扩容续费';
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]['销售部门'] = D('config_system')->where(array('CONFIG_KEY' => 'SALES_OFF', 'CONFIG_VALUE' => $salesOffId))->getField('CONFIG_DESCRIPTIO');
;
$data[$j]['订单号'] = $orderId;
$data[$j]['订单标识码'] = $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]['返点前业绩'] = round($performBfRebate, 2);
$data[$j]['返点比例'] = $val['REBATE_PERCENT'];
$data[$j]['是否特价审批单'] = $val['IS_BARGAIN_PRICE'] == 0 ? '非特价' : '特价';
$data[$j]['结算方式'] = 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('config_dept_info')->where(array('DEPT_ID' => $deptId))->getField('DEPT_NAME');
$data[$j]['业绩归属人'] = $v['SALESMAN_NAME'];
$data[$j]['业绩分配比例'] = $v['PERCENT'];
$saler_info = $OrderQueryMod->GetOrderSaler($val['APPLY_SALER_ID']);
$second_saler_info = $OrderQueryMod->GetOrderSaler($val['SECONDARY_SALER_ID']);
$data[$j]['一级经销商SAP主数据编号'] = empty($saler_info['SAP_UID']) ? '无' : $saler_info['SAP_UID'];
$data[$j]['经销商全称'] = $saler_info['REAL_NAME'];
$data[$j]['二级经销商SAP主数据编号'] = empty($second_saler_info['SAP_UID']) ? '无' : $second_saler_info['SAP_UID'];
$data[$j]['二级经销商'] = $second_saler_info['REAL_NAME'];
$consignerTime = $val['SAP_CONSIGNER_TIME'];
$data[$j]['发货日期'] = empty($consignerTime) ? '无' : 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]['项目类型'] = 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]['区域'] = $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]['详细地址'] = $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]['客户行业'] = $customIndustryName;
$data[$j]['CRM客户编号'] = empty($finalUser['CRM_CUSTOM_ID']) ? '无' : $finalUser['CRM_CUSTOM_ID'];
$data[$j]['CRM项目编号'] = empty($val['CRM_PROJECT_ID']) ? '无' : $val['CRM_PROJECT_ID'];
$data[$j]['采购联系人'] = $finalUser['PURCHASER'];
$data[$j]['采购联系人电话'] = empty($finalUser['PURCHASER_PHONE']) ? '无' : $finalUser['PURCHASER_PHONE'];
$data[$j]['采购联系人邮箱'] = $finalUser['PURCHASER_EMAIL'];
$data[$j]['IT部/信息部负责人'] = $finalUser['TECHNICIAN'];
$data[$j]['IT部/信息部负责人电话'] = empty($finalUser['TECHNICIAN_PHONE']) ? '无' : $finalUser['TECHNICIAN_PHONE'];
$data[$j]['IT部/信息部负责人邮箱'] = $finalUser['TECHNICIAN_EMAIL'];
$data[$j]['WPS+账号联系人'] = $finalUser['WPS_CONTACTOR'];
$data[$j]['WPS+账号联系人电话'] = empty($finalUser['WPS_CONTACTOR_PHONE']) ? '无' : $finalUser['WPS_CONTACTOR_PHONE'];
$data[$j]['WPS+账号联系人邮箱'] = $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+账号'] = $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]['是否赠送服务'] = '否';
} else {
$data[$j]['是否赠送服务'] = $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]['产品类'] = 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", "中国", $serialTypeStr);
$serialTypeStr = str_replace("england", "英国", $serialTypeStr);
$serialTypeStr = str_replace("japan", "日本", $serialTypeStr);
$serialTypeStr = str_replace("chn", "中文", $serialTypeStr);
$serialTypeStr = str_replace("enu", "英文", $serialTypeStr);
$serialTypeStr = str_replace("jpn", "日文", $serialTypeStr);
$data[$j]['序列号类型'] = rtrim($serialTypeStr, '|');
} else {
$data[$j]['序列号类型'] = '无';
}
if ($productCategory == 1) {
$authorizedType = $vv['AUTHORIZED_TYPE'];
$data[$j]['授权类型'] = M('config_system')->where(array('CONFIG_KEY' => 'AUTHORIZED_TYPE', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
} else {
$authorizedType = $vv['SPEC'];
$data[$j]['授权类型'] = M('config_system')->where(array('CONFIG_KEY' => 'PRODUCT_SPECS', 'CONFIG_VALUE' => $authorizedType))->getField('CONFIG_DESCRIPTIO');
}
$data[$j]['授权范围'] = $vv['AUTHORIZED_RANGE'];
$data[$j]['介质数量'] = $vv['TANN_COUNT'];
$data[$j]['授权数量'] = $vv['AMOUNT'];
$data[$j]['单价'] = $vv['PRICE'] / 100;
$data[$j]['到期后授权数量'] = $vv['EXPIRE_AUTHORIZED_AMOUNT'];
$data[$j]['服务年限'] = empty($vv['SERVICE_LENGTH']) ? 0 : $vv['SERVICE_LENGTH'];
$data[$j]['服务截止时间'] = 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]['授权年限'] = empty($vv['AUTHORIZED_YEARS']) ? 0 : $vv['AUTHORIZED_YEARS'];
if ($vv['AUTHORIZED_YEARS'] == '永久') {
$data[$j]['授权截止时间'] = '永久';
} else if ($vv['AUTHORIZED_YEARS'] == '随设备') {
$data[$j]['授权截止时间'] = date('Y-m-d', strtotime('+5 years 10 days', strtotime($val['APPLY_TIME'])));
} else {
$data[$j]['授权截止时间'] = 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]['订方性质'] = $isFinal == 1 ? '最终用户' : '经销商';
$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单号'] = '';
} else {
$data[$j]['SAP单号'] = empty($val['SAP_SALES_ORDER_ID']) ? '' : (int) $val['SAP_SALES_ORDER_ID'];
}
$data[$j]['合同编号'] = empty($val['CONTRACT_CODE']) ? '无' : $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]['订单规范检查备注'] = empty($description) ? '' : $description;
$data[$j]['订单审核备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 3, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['序列号设置备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 14, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['打印授权书审核备注'] = D('order_audit_log')->where(array('ORDER_ID' => $orderId, 'AUDIT_TYPE' => 15, 'LOG_STATE' => 1))->getField('DESCRIPTION');
$data[$j]['报备审核备注'] = 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]['发票收件地址'] = $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]['发票收件地址'] = $provinceName . $cityName . $val['INVOICE_OWNER_ADDRESS'];
}
$data[$j]['发票收件人'] = $val['INVOICE_OWNER'];
$data[$j]['发票收件人电话'] = empty($val['INVOICE_OWNER_PHONE']) ? '无' : $val['INVOICE_OWNER_PHONE'];
$j++;
}
}
}
$list大约有4000左右条数据 打印日志到超时大约有20M的sql日志 每条sql执行时间都在0.00几秒 有什么办法或者思路优化呢？
3 回答
慕容森
TA贡献1853条经验 获得超18个赞
4000次的循环本身并不大，如果循环里仅仅是对内存的操作其实很快就应该完成，但是你在循环里做了很多次数据库操作，这应该就是造成性能问题的根本原因。尽管每条sql执行都很快，但是你忽略了每次执行所带来的网络io开销时间。我才想4000次的循环里如此多的数据库操作足以是你的脚本超时了，当你所提到超时时，我认为你的php运行在fast cgi模式下。那么你有两种方法来解决
1，将sql操作合并，一次或几次在循环之外一口气得到所有的数据，再在循环中进行分门别类。我相信这样做会立竿见影的提升效率。
2， 如果这个操作不是及时性的，那么可以尝试放在cli模式下运行，你不用修改代码，尽管效率同样低，但cli模式下脚本不会超时。
另外如果你所获得到数据总量很大，那么还要考虑php本身为脚本所分配的最大可用内存，如果这个值低于你获取的数据所需要的内存，那么即便在cli模式脚本还是得崩。这个配置好像是在php.ini里一个叫max_memory_size定义的，名字可能不准确，我记不太清了
青春有我
TA贡献1784条经验 获得超8个赞
我一般查两次数据库，将人的信息和订单的信息全部取出（需要的部分哈），然后用PHP对两个数组进行重新运算、组合。一般很快就完成了。而不必要为了每一条数据去查一次数据库。我看到上面为了获取部门还查了一次数据库，感觉很不必要。在项目中一些常用的数据小的可以用配置文件，大的可以用缓存。
梵蒂冈之花
TA贡献1900条经验 获得超5个赞
sql查询往往是最慢的IO操作，每次sql操作哪怕只是0.001秒，4000循环，每个循环里面有10几个sql操作，这就40秒了。
1.所以尽量不要在循环里面做sql操作，可以考虑合并查询请求
$orderPerform = $OrderQueryMod->getOrderPerform($orderId);可以在循环外批量查询出来
$orderIdArr = array_column($list,'ORDER_ID');
$orderPerformArr = $OrderQueryMod->getOrderPerformArr($orderIdArr);循环里面直接从数组里面取值
2.一些热点数据，尤其是更新还比较少的数据，可以写入到缓存
- 3 回答
- 0 关注
- 987 浏览
添加回答
举报
0/150
提交
取消