iZNW69Sv

1function getOrders( $adb, $ext = "", $start_date = "", $end_date = "" )
2	{
3		$query        = " SET SESSION group_concat_max_len = 1000000 ";
4		$set_session  = $adb->pquery( $query );
5		$result_array = array();
6
7		if ( $set_session )
8		{
9
10			$query = "CREATE TEMPORARY TABLE IF NOT EXISTS `deposit_total_stoma_tmp` 
11						(`billing_id` int not null,`deposit_id` int not null, `deposit_date` date, `deposit` int not null, 
12						INDEX `index1` (`billing_id`), INDEX `index2` (`deposit`))ENGINE = MEMORY;";
13			$adb->pquery( $query, array());
14			$query = "TRUNCATE TABLE `deposit_total_stoma_tmp`;";
15			$adb->pquery( $query, array());
16			$query = "INSERT INTO `deposit_total_stoma_tmp`
17						SELECT
18						vtiger_jorderdeposit.bill_no AS `billing_id`,
19						vtiger_jorderdeposit.jorderdepositid AS `deposit_id`,
20						vtiger_jorderdeposit.deposit_date AS `deposit_date`,
21						vtiger_jorderdeposit.deposit AS `deposit`
22						FROM vtiger_jorderdeposit
23						LEFT JOIN vtiger_crmentity AS crm_orderdeposit ON vtiger_jorderdeposit.jorderdepositid = crm_orderdeposit.crmid
24						WHERE crm_orderdeposit.deleted = 0
25						AND vtiger_jorderdeposit.deposit_date BETWEEN '$start_date' AND '$end_date';";
26
27			if($adb->pquery( $query, array())){
28
29				$query = "  SELECT
30							vtiger_jorder.jorderid AS `order_id`,
31							vtiger_jcustomer.customer_no AS `customer_cd`,
32							vtiger_jcustomer.lastname AS `customer_name`,
33							municipality.municipality_no AS `municipality_no`,
34							municipality.name AS `municipality`,
35							vtiger_jcustomer.personal_number AS `personal_number`,
36							vtiger_jorder.delivery_date AS `sort_date`,
37							IF( parent_order.parent_order_id is not null, parent_order.parent_order_no, vtiger_jorder.order_no ) AS `order_no`,
38							IF( parent_order.parent_order_id is not null, parent_order.parent_order_date, vtiger_jorder.order_date ) AS `order_date`,
39							vtiger_jorder.sum_expense AS `sum_expense`,
40							vtiger_jorder.delivery_date AS `delivery_date`,
41							IF( parent_order.parent_order_id is not null, parent_order.parent_order_sum_expense, vtiger_jorder.sum_expense )  AS `parent_order_sum_expense`,
42							IF( parent_order.parent_order_id is not null, vtiger_jorder.order_date, '' ) AS `children_order_date`,
43							IF( parent_order.parent_order_id is not null, vtiger_jorder.sum_expense - parent_order.parent_order_sum_expense, 0 ) AS `return_expense`,
44							vtiger_jorder.sum_expense AS `final_sum_expense`,
45							vtiger_jorder.sum_individual + vtiger_jorder.balance_used AS `sum_individual`,
46							vtiger_jorder.sum_municipality AS `sum_municipality`,
47							IF( parent_order.parent_order_id is not null, parent_order.parent_order_billing, order_billing_deposit.order_billing )  AS `customer_billing`,
48							GROUP_CONCAT( CONCAT( IF( order_billing_deposit.deposit < 0, '返金', IF( vtiger_jorder.jorder_payment = '0', '郵便振替', IF( vtiger_jorder.jorder_payment = '1', '銀行振り込み', IF( vtiger_jorder.jorder_payment = '2', '代引き', IF( vtiger_jorder.jorder_payment = '3', '公費負担', IF( vtiger_jorder.jorder_payment = '4', '集金', '' ) ) ) ) ) ), '|', order_billing_deposit.deposit_date, '|', order_billing_deposit.deposit ) ORDER BY order_billing_deposit.deposit_date, order_billing_deposit.deposit_id separator ',' ) AS `order_deposit`,
49							IF( parent_order.parent_order_id is not null, parent_order.parent_order_balance_used, IF( vtiger_jorder.balance_used > 0, CONCAT( '充当', '|', vtiger_jorder.order_date, '|', vtiger_jorder.balance_used ), 0 ) ) AS `balance_used`
50							FROM vtiger_jorder
51							LEFT JOIN vtiger_crmentity AS crm_order ON vtiger_jorder.jorderid = crm_order.crmid
52							LEFT JOIN (
53								SELECT
54								vtiger_jbill.order_no AS `order_id`,
55								CONCAT( vtiger_jbill.jbillid, '|', vtiger_jbill.billing_date, '|', vtiger_jbill.billing_payment ) AS `order_billing`,
56								order_deposit.deposit_id AS `deposit_id`,
57								order_deposit.deposit_date AS `deposit_date`,
58								order_deposit.deposit AS `deposit`,
59								vtiger_jbill.billing_date AS `billing_date`
60								FROM vtiger_jbill
61								LEFT JOIN vtiger_crmentity AS crm_bill ON vtiger_jbill.jbillid = crm_bill.crmid
62								LEFT JOIN `deposit_total_stoma_tmp` AS order_deposit ON vtiger_jbill.jbillid = order_deposit.billing_id
63								WHERE crm_bill.deleted = 0
64								AND vtiger_jbill.billing_date BETWEEN '$start_date' AND '$end_date'
65							) AS order_billing_deposit ON vtiger_jorder.jorderid = order_billing_deposit.order_id
66							LEFT JOIN (
67								SELECT
68								vtiger_jorder.jorderid AS `parent_order_id`,
69								vtiger_jorder.order_no AS `parent_order_no`,
70								vtiger_jorder.order_date AS `parent_order_date`,
71								ROUND( SUM( IF( commodity.tax_kbn = '1', ( vtiger_jorderdetail.unit_pice * vtiger_jorderdetail.quantity ) * ( ( vtiger_jorder.tax_rate / 100 ) + 1 ), vtiger_jorderdetail.unit_pice * vtiger_jorderdetail.quantity ) ) ) AS `parent_order_sum_expense`,
72								IF( vtiger_jorder.balance_used > 0, CONCAT( '充当', '|', vtiger_jorder.order_date, '|', vtiger_jorder.balance_used ), 0 ) AS `parent_order_balance_used`,
73								order_billing.order_billing AS `parent_order_billing`
74								FROM vtiger_jorder
75								LEFT JOIN vtiger_crmentity AS crm_order ON vtiger_jorder.jorderid = crm_order.crmid
76								LEFT JOIN (
77									SELECT
78									vtiger_jbill.order_no AS `order_id`,
79									CONCAT( vtiger_jbill.jbillid, '|', vtiger_jbill.billing_date, '|', vtiger_jbill.billing_payment ) AS `order_billing`
80									FROM vtiger_jbill
81									LEFT JOIN vtiger_crmentity AS crm_bill ON vtiger_jbill.jbillid = crm_bill.crmid
82									WHERE crm_bill.deleted = 0
83									GROUP BY vtiger_jbill.order_no
84								) AS order_billing ON vtiger_jorder.jorderid = order_billing.order_id
85								LEFT JOIN vtiger_jorderdetail ON vtiger_jorder.jorderid = vtiger_jorderdetail.order_no
86								LEFT JOIN vtiger_crmentity AS crm_orderdetail ON vtiger_jorderdetail.jorderdetailid = crm_orderdetail.crmid
87								LEFT JOIN (
88									SELECT
89									vtiger_jcommodity.jcommodityid AS `commodity_id`,
90									vtiger_jcommodity.tax_kbn AS `tax_kbn`
91									FROM vtiger_jcommodity
92									LEFT JOIN vtiger_crmentity AS crm_commodity ON vtiger_jcommodity.jcommodityid = crm_commodity.crmid
93									WHERE crm_commodity.deleted = 0
94								) AS commodity ON vtiger_jorderdetail.commodity_no = commodity.commodity_id
95								WHERE crm_order.deleted = 0
96								AND vtiger_jorder.jorder_cancel = 2
97								GROUP BY vtiger_jorder.jorderid
98							) AS parent_order ON vtiger_jorder.parent_order_id = parent_order.parent_order_id
99							LEFT JOIN vtiger_jcustomer ON vtiger_jorder.customer_no = vtiger_jcustomer.jcustomerid
100							LEFT JOIN vtiger_crmentity AS crm_customer ON vtiger_jcustomer.jcustomerid = crm_customer.crmid
101							LEFT JOIN (
102								SELECT
103								vtiger_jmunicipality.jmunicipalityid,
104								vtiger_jmunicipality.municipality_no,
105								vtiger_jmunicipality.name
106								FROM vtiger_jmunicipality
107								LEFT JOIN vtiger_crmentity AS crm_municipality ON vtiger_jmunicipality.jmunicipalityid = crm_municipality.crmid
108								WHERE crm_municipality.deleted = 0
109							) AS municipality ON vtiger_jcustomer.municipality = municipality.jmunicipalityid
110							WHERE crm_order.deleted = 0
111							AND crm_customer.deleted = 0
112							AND vtiger_jorder.jorder_cancel = 0
113							$ext
114							AND vtiger_jorder.delivery_date BETWEEN '$start_date' AND '$end_date'
115							GROUP BY vtiger_jorder.jorderid
116							ORDER BY vtiger_jorder.jorderid ";
117				print_r($query);
118				$result = $adb->pquery( $query, array() );
119				return sqlResultsToFormattedArray( $result );
120			} else {
121				return sqlResultsToFormattedArray(array());
122			}
123		}
124
125		return $result_array;
126	}