public function getInvoiceOrderItemsList($customer_id, $currency, $key = null)
{
$data = array();
$sql = $this->select()->setIntegrityCheck(false)->from(array('t1' => $this->_name), array('order_tax_id' => 'tax_id', 'order_tax_name' => 'tax_name', 'order_tax_rate' => 'tax_rate', 'order_price_tax' => 'price_tax', 'order_sales_id' => 'sales_id', 'order_customer_id' => 'customer_id', 'order_date', 'order_number' => 'number', 'order_remark' => 'remark', 'order_currency' => 'currency', 'order_currency_rate' => 'currency_rate', '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_sale_order_items'), "t1.id = t8.order_id", array('id', 'order_id', 'request_date', 'code', 'name', 'description', 'remark', 'price', 'qty', 'unit'))->joinLeft(array('t9' => $this->_dbprefix . 'bpartner'), "t1.customer_id = t9.id", array('order_customer_code' => 'code', 'order_customer_name' => new Zend_Db_Expr("case when t9.cname != '' then t9.cname else t9.ename end")))->joinLeft(array('t10' => $this->_dbprefix . 'bpartner_contact'), "t1.customer_contact_id = t10.id", array('order_customer_contact' => new Zend_Db_Expr("concat(t10.name, ' [', t10.tel, ']')")))->where("t1.customer_id = " . $customer_id . " and t1.currency = '" . $currency . "' and t1.state = 2 and t1.active = 1");
if ($key) {
$sql->where("t1.number like '%" . $key . "%' \n or t1.remark like '%" . $key . "%' \n or t3.cname like '%" . $key . "%' \n or t3.ename like '%" . $key . "%' \n or t7.name like '%" . $key . "%' \n or t8.code like '%" . $key . "%' \n or t8.name like '%" . $key . "%' \n or t8.description like '%" . $key . "%'");
}
/* echo $sql;
exit; */
$data = $this->fetchAll($sql)->toArray();
$items_invoice = new Erp_Model_Sale_Invoiceitems();
$items_receive = new Erp_Model_Purchse_Receiveitemsorder();
// 交货
$sales = new Erp_Model_Sale_Sales();
for ($i = 0; $i < count($data); $i++) {
// 获取已开票数量
$data[$i]['qty_invoice'] = $items_invoice->getQty($data[$i]['id']);
$data[$i]['qty_left'] = $data[$i]['qty'] - $data[$i]['qty_invoice'];
$data[$i]['qty_invoice'] = 0;
$salesData = $sales->getData($data[$i]['order_sales_id']);
$data[$i]['order_sales_name'] = isset($salesData['cname']) ? $salesData['cname'] : '';
// 交货数量
$data[$i]['qty_send'] = 0 - $items_receive->getQty('sale_order', $data[$i]['id']);
// 交货（负数）
$data[$i]['description'] = str_replace("'", "\\'", $data[$i]['description']);
}
return $data;
}
public function getOrderItemsList($key = null)
{
$sql = $this->select()->setIntegrityCheck(false)->from(array('t1' => $this->_name), array('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('id', 'order_id', 'request_date', 'code', 'name', 'description', 'remark', 'supplier_code', 'supplier_codename', 'supplier_description', 'warehouse_code', 'price', 'qty', 'unit', 'project_info'))->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, ']')")))->where("t1.receive_flag = 0 and t8.code != '' and t1.state = 2 and t1.active = 1");
if ($key) {
$sql->where("t1.number like '%" . $key . "%' or t1.remark like '%" . $key . "%' or t3.cname like '%" . $key . "%' or t3.ename like '%" . $key . "%' or t7.name like '%" . $key . "%' or t8.code like '%" . $key . "%' or t8.name like '%" . $key . "%' or t8.description like '%" . $key . "%'");
}
/* echo $sql;
exit; */
$data = $this->fetchAll($sql)->toArray();
$items_receive = new Erp_Model_Purchse_Receiveitemsorder();
$buyer = new Erp_Model_Purchse_Buyer();
for ($i = 0; $i < count($data); $i++) {
// 获取已下单数量
$data[$i]['qty_receive'] = $items_receive->getQty('order', $data[$i]['id']);
$data[$i]['qty_left'] = $data[$i]['qty'] - $data[$i]['qty_receive'];
$data[$i]['qty_receive'] = 0;
$buyerData = $buyer->getData($data[$i]['order_buyer_id']);
$data[$i]['order_buyer_name'] = isset($buyerData['cname']) ? $buyerData['cname'] : '';
$data[$i]['description'] = str_replace("'", "\\'", $data[$i]['description']);
}
return $data;
}
public function getData($order_id)
{
$result = array();
$data = $this->fetchAll("order_id = " . $order_id)->toArray();
$item = new Erp_Model_Purchse_Receiveitemsorder();
$reqItem = new Erp_Model_Purchse_Orderitemsreq();
foreach ($data as $d) {
$active = $d['active'] == 1 ? true : false;
$qty_receive = $item->getQty('order', $d['id']);
$itemReqInfo = $reqItem->getOrderItemReqInfo($d['id']);
$req_item_ids = array();
$req_qtys = array();
foreach ($itemReqInfo as $info) {
array_push($req_item_ids, $info['req_item_id']);
array_push($req_qtys, $info['qty']);
}
$items_req_item_id = implode(',', $req_item_ids);
$items_req_qty = implode(',', $req_qtys);
array_push($result, array('items_id' => $d['id'], 'items_order_id' => $d['order_id'], 'items_req_number' => $d['req_number'], 'items_active' => $active, 'items_code' => $d['code'], 'items_name' => $d['name'], 'items_description' => $d['description'], 'items_supplier_code' => $d['supplier_code'], 'items_supplier_codename' => $d['supplier_codename'], 'items_supplier_description' => $d['supplier_description'], 'items_warehouse_code' => $d['warehouse_code'], 'items_qty' => $d['qty'], 'items_qty_receive' => $qty_receive, 'items_unit' => $d['unit'], 'items_price' => $d['price'], 'items_total' => $d['total'], 'items_request_date' => $d['request_date'], 'items_dept_id' => $d['dept_id'], 'items_project_info' => $d['project_info'], 'items_req_item_id' => $items_req_item_id, 'items_req_qty' => $items_req_qty, 'items_remark' => $d['remark']));
}
return $result;
}
