public function getReqStatistics($condition = array()) { $data = array(); $sql = $this->select()->setIntegrityCheck(false)->from(array('t1' => $this->_name), array('req_active' => 'active', 'req_state' => new Zend_Db_Expr("case when t1.state = 0 then '审核中' when t1.state = 1 then '拒绝' else '批准' end"), 'req_number' => 'number', 'req_dept_id' => 'dept_id', 'req_release_time' => 'release_time', 'req_create_time' => 'create_time', 'req_remark' => 'remark', 'req_reason' => 'reason', 'req_type_id' => 'type_id'))->joinLeft(array('t2' => $this->_dbprefix . 'user'), "t1.create_user = t2.id", array())->joinLeft(array('t3' => $this->_dbprefix . 'employee'), "t2.employee_id = t3.id", array('req_creater' => 'cname'))->joinLeft(array('t4' => $this->_dbprefix . 'user'), "t1.update_user = t4.id", array())->joinLeft(array('t5' => $this->_dbprefix . 'employee'), "t4.employee_id = t5.id", array('req_updater' => 'cname'))->joinLeft(array('t6' => $this->_dbprefix . 'employee_dept'), "t1.dept_id = t6.id", array('req_dept' => 'name'))->joinLeft(array('t7' => $this->_dbprefix . 'erp_pur_type'), "t1.type_id = t7.id", array('req_type' => 'name'))->joinLeft(array('t8' => $this->_dbprefix . 'erp_pur_req_items'), "t1.id = t8.req_id")->joinLeft(array('t9' => $this->_dbprefix . 'employee_dept'), "t8.dept_id = t9.id", array('dept' => 'name'))->joinLeft(array('t10' => $this->_dbprefix . 'erp_pur_order_items_req'), "t10.req_item_id = t8.id and t10.active = 1", array('req_item_id', 'order_item_ids' => new Zend_Db_Expr("group_concat(t10.order_item_id)"), 'qty_order' => new Zend_Db_Expr("sum(t10.qty)")))->joinLeft(array('t11' => $this->_dbprefix . 'erp_pur_order_items'), "t10.order_item_id = t11.id", array('order_item_id' => 'id', 'delivery_date', 'delivery_date_remark'))->joinLeft(array('t14' => $this->_dbprefix . 'erp_pur_order'), "t11.order_id = t14.id", array('order_number' => new Zend_Db_Expr("group_concat(t14.number)")))->joinLeft(array('t12' => $this->_dbprefix . 'user'), "t1.apply_user = t12.id", array())->joinLeft(array('t13' => $this->_dbprefix . 'employee'), "t12.employee_id = t13.id", array('req_applier' => 'cname'))->group("t8.id")->order(array('t1.number desc', 't1.create_time desc')); // 状态 if ($condition['state'] != null) { if ($condition['state'] == 3) { $sql->where("t1.active = 0"); } else { $sql->where("t1.state = " . $condition['state']); } } // 申请人 if ($condition['applier']) { $sql->where("t3.cname like '%" . $condition['applier'] . "%' or t3.ename like '%" . $condition['applier'] . "%'"); } // 日前从 if ($condition['date_from']) { $sql->where("t1.create_time >= '" . $condition['date_from'] . " 00:00:00'"); } // 日期至 if ($condition['date_to']) { $sql->where("t1.create_time <= '" . $condition['date_to'] . " 23:59:59'"); } // 采购类别 if ($condition['type']) { $type = json_decode($condition['type']); if (count($type)) { $type_con = "t1.type_id = " . $type[0]; for ($i = 1; $i < count($type); $i++) { $type_con .= " or t1.type_id = " . $type[$i]; } $sql->where($type_con); } } // 需求部门 if ($condition['dept']) { $dept = json_decode($condition['dept']); if (count($dept)) { $dept_con = "t1.dept_id = " . $dept[0]; for ($i = 1; $i < count($dept); $i++) { $dept_con .= " or t1.dept_id = " . $dept[$i]; } $sql->where($dept_con); } } if ($condition['key']) { $sql->where("t1.number like '%" . $condition['key'] . "%' or t1.remark like '%" . $condition['key'] . "%' or t1.reason like '%" . $condition['key'] . "%' or t3.cname like '%" . $condition['key'] . "%' or t3.ename like '%" . $condition['key'] . "%' or t7.name like '%" . $condition['key'] . "%' or t8.code like '%" . $condition['key'] . "%' or t8.name like '%" . $condition['key'] . "%' or t8.description like '%" . $condition['key'] . "%'"); } $total = $this->fetchAll($sql)->count(); if ($condition['option'] != 'csv') { $sql->limitPage($condition['page'], $condition['limit']); } $data = $this->fetchAll($sql)->toArray(); $items_req = new Erp_Model_Purchse_Orderitemsreq(); $order = new Erp_Model_Purchse_Order(); $operateModel = new Application_Model_Log_Operate(); $receiveModel = new Erp_Model_Purchse_Reqitemsreceived(); for ($i = 0; $i < count($data); $i++) { $data[$i]['delivery_reply_log'] = ''; // 入库日志 $data[$i]['in_stock_qty'] = 0; $data[$i]['in_stock_info'] = ''; if ($data[$i]['req_item_id']) { $in_stock_info = $receiveModel->getReceivedInfo('req', $data[$i]['req_item_id']); $data[$i]['in_stock_qty'] = $in_stock_info['qty']; $data[$i]['in_stock_info'] = implode(',', $in_stock_info['info']); } // 交期回复日志 if ($data[$i]['delivery_date'] != '' || $data[$i]['delivery_date_remark'] != '') { $logInfo = array(); $log = $operateModel->getLogByOperateAndTargetId('采购交期回复', $data[$i]['order_item_id']); foreach ($log as $l) { $content = Zend_Json::decode($l['content']); $logText = $content['time'] . ' [' . $content['delivery_date'] . '] [' . $content['delivery_date_remark'] . '] ' . $content['user']; array_push($logInfo, $logText); } $data[$i]['delivery_reply_log'] = implode(',', $logInfo); } // 获取采购订单数量(合并下单的申请分拆显示) $data[$i]['req_info'] = ''; $data[$i]['receive_info'] = ''; $data[$i]['receive_qty'] = 0; $data[$i]['order_info'] = ''; if ($data[$i]['order_item_ids'] != '') { $item_ids = explode(',', $data[$i]['order_item_ids']); $req_item_data = $order->getOrderQty($item_ids, $data[$i]['req_item_id']); $orderInfoArr = array(); $receiveInfoArr = array(); //echo '<pre>';print_r($req_item_data);exit; foreach ($req_item_data as $req_info) { foreach ($req_info['receive_data'] as $r) { array_push($receiveInfoArr, $r['number'] . ' [' . $r['qty'] . '] [' . $r['time'] . ']'); $data[$i]['receive_qty'] += $r['qty']; } array_push($orderInfoArr, $req_info['number'] . ' [' . $req_info['qty'] . '] [' . $req_info['time'] . ']'); } $data[$i]['order_info'] = implode(',', $orderInfoArr); $data[$i]['receive_info'] = implode(',', $receiveInfoArr); } $data[$i]['qty_left'] = $data[$i]['qty'] - $data[$i]['qty_order']; if ($data[$i]['req_active'] == 0) { $data[$i]['req_state'] = '取消'; } } //echo '<pre>';print_r($data);exit; if ($condition['option'] == 'csv') { $data_csv = array(); $title = array('cnt' => '#', 'active' => '启用', 'req_number' => '申请单号', 'req_type' => '类别', 'req_dept' => '申请部门', 'dept' => '需求部门', 'code' => '物料号', 'qty' => '申请数量', 'qty_order' => '下单数量', 'order_info' => '采购订单', 'receive_qty' => '到货数量', 'receive_info' => '到货信息', 'in_stock_qty' => '入库数量', 'in_stock_info' => '入库信息', 'qty_left' => '未下单数量', 'date_req' => '需求日期', 'delivery_date' => '预计交期', 'delivery_date_remark' => '交期备注', 'delivery_reply_log' => '交期回复日志', 'name' => '名称', 'description' => '描述', 'model' => '型号', 'order_req_num' => '订货产品出库申请号', 'customer_address' => '客户收件人地址简码', 'customer_aggrement' => '客户合同号', 'remark' => '备注', 'req_creater' => '制单人', 'req_applier' => '申请人', 'req_reason' => '申请事由', 'project_info' => '项目信息', 'req_remark' => '申请备注', 'req_create_date' => '申请日期', 'req_create_time' => '申请时间', 'req_release_date' => '批准日期', 'req_release_time' => '批准时间'); array_push($data_csv, $title); $i = 0; foreach ($data as $d) { $i++; $info = array('cnt' => $i, 'active' => $d['active'] == 1 ? '是' : '否', 'req_number' => $d['req_number'], 'req_type' => $d['req_type'], 'req_dept' => $d['req_dept'], 'dept' => $d['dept'], 'code' => $d['code'], 'qty' => $d['qty'], 'qty_order' => $d['qty_order'], 'order_info' => $d['order_info'], 'receive_qty' => $d['receive_qty'], 'receive_info' => $d['receive_info'], 'in_stock_qty' => $d['in_stock_qty'], 'in_stock_info' => $d['in_stock_info'], 'qty_left' => $d['qty_left'], 'date_req' => $d['date_req'], 'delivery_date' => $d['delivery_date'], 'delivery_date_remark' => $d['delivery_date_remark'], 'delivery_reply_log' => $d['delivery_reply_log'], 'name' => $d['name'], 'description' => $d['description'], 'model' => $d['model'], 'order_req_num' => isset($d['order_req_num']) ? $d['order_req_num'] : '', 'customer_address' => $d['customer_address'], 'customer_aggrement' => $d['customer_aggrement'], 'remark' => $d['remark'], 'req_creater' => $d['req_creater'], 'req_applier' => $d['req_applier'], 'req_reason' => $d['req_reason'], 'project_info' => $d['project_info'], 'req_remark' => $d['req_remark'], 'req_create_date' => $d['req_create_time'] != '' ? date('Y-m-d', strtotime($d['req_create_time'])) : '', 'req_create_time' => $d['req_create_time'] != '' ? date('H:i:s', strtotime($d['req_create_time'])) : '', 'req_release_date' => $d['req_release_time'] != '' ? date('Y-m-d', strtotime($d['req_release_time'])) : '', 'req_release_time' => $d['req_release_time'] != '' ? date('H:i:s', strtotime($d['req_release_time'])) : ''); array_push($data_csv, $info); } return $data_csv; } return array('total' => $total, 'rows' => $data); }
public function getOrderStatistics($condition = array()) { $sql = $this->select()->setIntegrityCheck(false)->from(array('t1' => $this->_name), array('order_price_tax' => 'price_tax', 'order_state' => new Zend_Db_Expr("case when t1.state = 0 then '审核中' when t1.state = 1 then '拒绝' else '批准' end"), 'order_create_time' => 'create_time', 'order_release_time' => 'release_time', 'order_buyer_id' => 'buyer_id', 'order_supplier_id' => 'supplier_id', 'order_date', 'order_number' => 'number', 'order_remark' => 'remark', 'order_currency' => 'currency', 'order_type_id' => 'type_id'))->joinLeft(array('t2' => $this->_dbprefix . 'user'), "t1.create_user = t2.id", array())->joinLeft(array('t3' => $this->_dbprefix . 'employee'), "t2.employee_id = t3.id", array('creater' => 'cname'))->joinLeft(array('t4' => $this->_dbprefix . 'user'), "t1.update_user = t4.id", array())->joinLeft(array('t5' => $this->_dbprefix . 'employee'), "t4.employee_id = t5.id", array('updater' => 'cname'))->joinLeft(array('t7' => $this->_dbprefix . 'erp_pur_type'), "t1.type_id = t7.id", array('order_type_name' => 'name'))->joinLeft(array('t8' => $this->_dbprefix . 'erp_pur_order_items'), "t1.id = t8.order_id", array('delivery_date', 'delivery_date_remark', 'active', 'id', 'order_id', 'request_date', 'code', 'name', 'description', 'remark', 'supplier_code', 'supplier_codename', 'supplier_description', 'warehouse_code', 'price', 'qty', 'unit', 'project_info', 'req_number'))->joinLeft(array('t9' => $this->_dbprefix . 'bpartner'), "t1.supplier_id = t9.id", array('order_supplier_code' => 'code', 'order_supplier_name' => new Zend_Db_Expr("case when t9.cname != '' then t9.cname else t9.ename end")))->joinLeft(array('t10' => $this->_dbprefix . 'bpartner_contact'), "t1.supplier_contact_id = t10.id", array('order_supplier_contact' => new Zend_Db_Expr("concat(t10.name, ' [', t10.tel, ']')")))->joinLeft(array('t11' => $this->_dbprefix . 'erp_pur_order_items_req'), "t11.order_item_id = t8.id", array('req_item_ids' => new Zend_Db_Expr("group_concat(t11.req_item_id)")))->joinLeft(array('t12' => $this->_dbprefix . 'erp_pur_req_items'), "t12.id = t11.req_item_id", array('qty_req' => new Zend_Db_Expr('sum(t12.qty)'), 'order_req_num', 'customer_address', 'customer_aggrement'))->where("t1.active = 1")->group("t8.id")->order(array('t1.number desc', 't1.create_time desc')); // 状态 if ($condition['state'] != null) { $sql->where("t1.state = " . $condition['state']); } // 日前从 if ($condition['date_from']) { $sql->where("t1.create_time >= '" . $condition['date_from'] . " 00:00:00'"); } // 日期至 if ($condition['date_to']) { $sql->where("t1.create_time <= '" . $condition['date_to'] . " 23:59:59'"); } // 采购类别 if ($condition['type']) { $type = json_decode($condition['type']); if (count($type)) { $type_con = "t1.type_id = " . $type[0]; for ($i = 1; $i < count($type); $i++) { $type_con .= " or t1.type_id = " . $type[$i]; } $sql->where($type_con); } } // 需求部门 if ($condition['dept']) { $dept = json_decode($condition['dept']); if (count($dept)) { $dept_con = "t8.dept_id = " . $dept[0]; for ($i = 1; $i < count($dept); $i++) { $dept_con .= " or t8.dept_id = " . $dept[$i]; } $sql->where($dept_con); } } // 采购员 if ($condition['buyer']) { $buyer = json_decode($condition['buyer']); if (count($buyer)) { $buyer_con = "t1.buyer_id = " . $buyer[0]; for ($i = 1; $i < count($buyer); $i++) { $buyer_con .= " or t1.buyer_id = " . $buyer[$i]; } $sql->where($buyer_con); } } if ($condition['key']) { $sql->where("t9.code like '%" . $condition['key'] . "%' or t9.cname like '%" . $condition['key'] . "%' or t9.ename like '%" . $condition['key'] . "%' or t3.cname like '%" . $condition['key'] . "%' or t3.ename like '%" . $condition['key'] . "%' or t1.number like '%" . $condition['key'] . "%' or t1.remark like '%" . $condition['key'] . "%' or t3.cname like '%" . $condition['key'] . "%' or t3.ename like '%" . $condition['key'] . "%' or t7.name like '%" . $condition['key'] . "%' or t8.code like '%" . $condition['key'] . "%' or t8.name like '%" . $condition['key'] . "%' or t8.description like '%" . $condition['key'] . "%'"); } $total = $this->fetchAll($sql)->count(); /* echo $sql; exit; */ if ($condition['option'] != 'csv') { $sql->limitPage($condition['page'], $condition['limit']); } $data = $this->fetchAll($sql)->toArray(); //echo '<pre>';print_r($data);exit; $items_receive = new Erp_Model_Purchse_Receiveitemsorder(); $items_invoice = new Erp_Model_Purchse_Invoiceitems(); $buyer = new Erp_Model_Purchse_Buyer(); $req = new Erp_Model_Purchse_Req(); $operateModel = new Application_Model_Log_Operate(); $receiveModel = new Erp_Model_Purchse_Reqitemsreceived(); for ($i = 0; $i < count($data); $i++) { // 入库日志 $in_stock_info = $receiveModel->getReceivedInfo('order', $data[$i]['id']); $data[$i]['in_stock_qty'] = $in_stock_info['qty']; $data[$i]['in_stock_info'] = implode(',', $in_stock_info['info']); // 交期回复日志 if ($data[$i]['delivery_date'] != '' || $data[$i]['delivery_date_remark'] != '') { $logInfo = array(); $log = $operateModel->getLogByOperateAndTargetId('采购交期回复', $data[$i]['id']); foreach ($log as $l) { $content = Zend_Json::decode($l['content']); $logText = $content['time'] . ' [' . $content['delivery_date'] . '] [' . $content['delivery_date_remark'] . '] ' . $content['user']; array_push($logInfo, $logText); } $data[$i]['delivery_reply_log'] = implode(',', $logInfo); } // 获取采购申请数量(合并下单的申请分拆显示) $data[$i]['req_info'] = ''; if ($data[$i]['req_item_ids'] != '') { $item_ids = explode(',', $data[$i]['req_item_ids']); $req_item_data = $req->getReqQty($item_ids); $reqInfoArr = array(); foreach ($req_item_data as $req_info) { array_push($reqInfoArr, $req_info['number'] . ' [' . $req_info['qty'] . '] [' . $req_info['time'] . ']'); } $data[$i]['req_info'] = implode(',', $reqInfoArr); } $data[$i]['order_supplier'] = $data[$i]['order_supplier_code'] . $data[$i]['order_supplier_name']; $data[$i]['qty_receive'] = 0; $data[$i]['receive_info'] = ''; if ($data[$i]['id']) { if ($data[$i]['code'] != '') { // 已收货物料:从采购收货 $receiveData = $items_receive->getOrderItemReceivedQty($data[$i]['id']); $receiveInfoArr = array(); foreach ($receiveData as $r) { $data[$i]['qty_receive'] += $r['qty']; array_push($receiveInfoArr, $r['number'] . ' [' . $r['qty'] . '] [' . $r['time'] . ']'); } $data[$i]['receive_info'] = implode(',', $receiveInfoArr); } else { // 已收货非物料:从采购发票 $data[$i]['qty_receive'] = $items_invoice->getQty($data[$i]['id'], 1); } } $data[$i]['qty_left'] = $data[$i]['qty'] - $data[$i]['qty_receive']; $buyerData = $buyer->getData($data[$i]['order_buyer_id']); $data[$i]['order_buyer_name'] = isset($buyerData['cname']) ? $buyerData['cname'] : ''; } if ($condition['option'] == 'csv') { $data_csv = array(); $showPrice = false; if (Application_Model_User::checkPermissionByRoleName('系统管理员') || Application_Model_User::checkPermissionByRoleName('采购人员') || Application_Model_User::checkPermissionByRoleName('财务人员')) { $showPrice = true; } if ($showPrice) { $title = array('cnt' => '#', 'active' => '启用', 'order_number' => '订单号', 'order_state' => '订单状态', 'order_date' => '订单日期', 'order_buyer_name' => '采购员', 'order_type_name' => '类别', 'order_supplier_code' => '供应商代码', 'order_supplier_name' => '供应商名称', 'code' => '物料号', 'price' => '价格', 'order_price_tax' => '含税', 'qty' => '订单数量', 'qty_req' => '申请数量', 'qty_receive' => '到货数量', 'receive_info' => '到货信息', 'in_stock_qty' => '入库数量', 'in_stock_info' => '入库信息', 'qty_left' => '未到货数量', 'request_date' => '需求交期', 'delivery_date' => '预计交期', 'delivery_date_remark' => '交期备注', 'order_req_num' => '订货产品出库申请号', 'customer_address' => '客户收件人地址简码', 'customer_aggrement' => '客户合同号', 'name' => '名称', 'description' => '描述', 'req_info' => '申购单号', 'remark' => '备注', 'order_create_date' => '下单日期', 'order_create_time' => '下单时间', 'order_release_date' => '批准日期', 'order_release_time' => '批准时间'); } else { $title = array('cnt' => '#', 'active' => '启用', 'order_number' => '订单号', 'order_state' => '订单状态', 'order_date' => '订单日期', 'order_buyer_name' => '采购员', 'order_type_name' => '类别', 'order_supplier_code' => '供应商代码', 'order_supplier_name' => '供应商名称', 'code' => '物料号', 'qty' => '订单数量', 'qty_req' => '申请数量', 'qty_receive' => '到货数量', 'receive_info' => '到货信息', 'in_stock_qty' => '入库数量', 'in_stock_info' => '入库信息', 'qty_left' => '未到货数量', 'request_date' => '需求交期', 'delivery_date' => '预计交期', 'delivery_date_remark' => '交期备注', 'order_req_num' => '订货产品出库申请号', 'customer_address' => '客户收件人地址简码', 'customer_aggrement' => '客户合同号', 'name' => '名称', 'description' => '描述', 'req_info' => '申购单号', 'remark' => '备注', 'order_create_date' => '下单日期', 'order_create_time' => '下单时间', 'order_release_date' => '批准日期', 'order_release_time' => '批准时间'); } array_push($data_csv, $title); $i = 0; foreach ($data as $d) { $i++; if ($showPrice) { $info = array('cnt' => $i, 'active' => $d['active'] == 1 ? '是' : '否', 'order_number' => $d['order_number'], 'order_state' => $d['order_state'], 'order_date' => $d['order_date'], 'order_buyer_name' => $d['order_buyer_name'], 'order_type_name' => $d['order_type_name'], 'order_supplier_code' => $d['order_supplier_code'], 'order_supplier_name' => $d['order_supplier_name'], 'code' => $d['code'], 'price' => $d['price'], 'order_price_tax' => $d['order_price_tax'] == 1 ? '是' : '否', 'qty' => $d['qty'], 'qty_req' => $d['qty_req'], 'qty_receive' => $d['qty_receive'], 'receive_info' => $d['receive_info'], 'in_stock_qty' => $d['in_stock_qty'], 'in_stock_info' => $d['in_stock_info'], 'qty_left' => $d['qty_left'], 'request_date' => $d['request_date'], 'delivery_date' => $d['delivery_date'], 'delivery_date_remark' => $d['delivery_date_remark'], 'order_req_num' => $d['order_req_num'], 'customer_address' => $d['customer_address'], 'customer_aggrement' => $d['customer_aggrement'], 'name' => $d['name'], 'description' => $d['description'], 'req_info' => $d['req_info'], 'remark' => $d['remark'], 'order_create_date' => $d['order_create_time'] != '' ? date('Y-m-d', strtotime($d['order_create_time'])) : '', 'order_create_time' => $d['order_create_time'] != '' ? date('H:i:s', strtotime($d['order_create_time'])) : '', 'order_release_date' => $d['order_release_time'] != '' ? date('Y-m-d', strtotime($d['order_release_time'])) : '', 'order_release_time' => $d['order_release_time'] != '' ? date('H:i:s', strtotime($d['order_release_time'])) : ''); } else { $info = array('cnt' => $i, 'active' => $d['active'] == 1 ? '是' : '否', 'order_number' => $d['order_number'], 'order_state' => $d['order_state'], 'order_date' => $d['order_date'], 'order_buyer_name' => $d['order_buyer_name'], 'order_type_name' => $d['order_type_name'], 'order_supplier_code' => $d['order_supplier_code'], 'order_supplier_name' => $d['order_supplier_name'], 'code' => $d['code'], 'qty' => $d['qty'], 'qty_req' => $d['qty_req'], 'qty_receive' => $d['qty_receive'], 'receive_info' => $d['receive_info'], 'in_stock_qty' => $d['in_stock_qty'], 'in_stock_qty' => $d['in_stock_info'], 'qty_left' => $d['qty_left'], 'request_date' => $d['request_date'], 'delivery_date' => $d['delivery_date'], 'delivery_date_remark' => $d['delivery_date_remark'], 'order_req_num' => $d['order_req_num'], 'customer_address' => $d['customer_address'], 'customer_aggrement' => $d['customer_aggrement'], 'name' => $d['name'], 'description' => $d['description'], 'req_info' => $d['req_info'], 'remark' => $d['remark'], 'order_create_date' => $d['order_create_time'] != '' ? date('Y-m-d', strtotime($d['order_create_time'])) : '', 'order_create_time' => $d['order_create_time'] != '' ? date('H:i:s', strtotime($d['order_create_time'])) : '', 'order_release_date' => $d['order_release_time'] != '' ? date('Y-m-d', strtotime($d['order_release_time'])) : '', 'order_release_time' => $d['order_release_time'] != '' ? date('H:i:s', strtotime($d['order_release_time'])) : ''); } array_push($data_csv, $info); } return $data_csv; } return array('total' => $total, 'rows' => $data); }