Skip to content

Instantly share code, notes, and snippets.

@tdchien
Created October 23, 2017 07:40
Show Gist options
  • Save tdchien/fe24e5699dcae878f23f49c4069e3ce2 to your computer and use it in GitHub Desktop.
Save tdchien/fe24e5699dcae878f23f49c4069e3ce2 to your computer and use it in GitHub Desktop.
PDO Binding param.php
<?php
$dbh = null;
try {
$dbh = new PDO('mysql:host=localhost;dbname=webcms', 'webcms', 'webcms');
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
// prepare and bind
$sql = "SELECT e.id,
tc.name AS customer_name,
CASE
WHEN e.estimate_date IS NOT NULL THEN e.estimate_date
ELSE e.create_date
END AS sort_date,
to_char ( CASE
WHEN e.estimate_date IS NOT NULL THEN e.estimate_date
ELSE e.create_date
END, 'YYYY/MM/DD' ) AS e_create_date,
array_to_string(ARRAY
(SELECT t_estimate_detail.category_id
FROM t_estimate_detail
LEFT JOIN m_dms_goods ON m_dms_goods.id = t_estimate_detail.category_id
WHERE t_estimate_detail.estimate_id = e.id
GROUP BY category_id, m_dms_goods.name), ',') AS category_id,
array_to_string(ARRAY
(SELECT m_dms_goods.name
FROM t_estimate_detail
LEFT JOIN m_dms_goods ON m_dms_goods.id = t_estimate_detail.category_id
WHERE t_estimate_detail.estimate_id = e.id
GROUP BY category_id, m_dms_goods.name), '/') AS category_name,
e.total,
e.car_num_num,
e.car_type,
CASE
WHEN (so.estimate_id) IS NULL THEN '1_estimate'
WHEN (so.estimate_id) IS NOT NULL THEN '1_ins_estimate'
ELSE '1_estimate'
END AS proc_type,
-2 AS work_staff,
-2 AS wcheck_staff,
'' AS work_staff_name,
'' AS wcheck_staff_name,
st1.name AS receipt_staff_name,
e.receipt_staff,
-2 AS sign_flg,
e.customer_id,
so.estimate_id AS shaken_id,
ro.estimate_id AS repair_id,
sw.workflow_status AS insp_workflow_status,
rw.workflow_status AS repair_workflow_status,
e.car_id,
e.id AS estimate__id
FROM t_estimate_detail d,
m_dms_goods g,
t_estimate e
LEFT OUTER JOIN t_customer tc ON tc.id = e.customer_id
LEFT OUTER JOIN m_shop_staff st1 ON st1.shop_id = :param_shop_id
AND st1.id = e.receipt_staff
LEFT OUTER JOIN
(SELECT CASE
WHEN so.tenken_id IS NOT NULL THEN NULL
ELSE so.estimate_id
END AS estimate_id
FROM t_shaken_estimate so,
t_estimate e
WHERE so.estimate_id = e.id
AND so.delete_flg = 0) so ON so.estimate_id = e.id
LEFT OUTER JOIN
(SELECT ro.estimate_id
FROM t_repair_estimate ro,
t_estimate e
WHERE ro.estimate_id = e.id
AND ro.delete_flg = 0) ro ON ro.estimate_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN rw.estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
rw.estimate_id
FROM t_repair_workflow rw,
t_estimate e
WHERE rw.estimate_id = e.id
AND rw.customer_id = e.customer_id
AND rw.shop_id = 1
AND rw.cust_car_id = e.car_id
AND rw.delete_flg = 0 ) rw ON rw.estimate_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN sw.estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
sw.estimate_id
FROM t_shaken_workflow sw,
t_estimate e
WHERE sw.estimate_id = e.id
AND sw.customer_id = e.customer_id
AND sw.shop_id = 1
AND sw.cust_car_id = e.car_id
AND sw.delete_flg = 0
ORDER BY sw.create_date DESC
LIMIT 1
OFFSET 0) sw ON sw.estimate_id = e.id,
(SELECT DISTINCT d2.estimate_id
FROM t_estimate e2,
t_estimate_detail d2
WHERE e2.company_id = :param_company_id
AND e2.shop_id = :param_shop_id
AND d2.category_id IN (1,
9,
2,
8,
6,
5,
10,
7,
11,
12)
AND e2.id = d2.estimate_id
AND ((e2.estimate_date IS NULL
AND e2.create_date BETWEEN :param_start_date AND :param_end_date )
OR (e2.estimate_date IS NOT NULL
AND e2.estimate_date BETWEEN :param_start_date AND :param_end_date )) ) t1
WHERE e.company_id = :param_company_id
AND e.shop_id = :param_shop_id
AND e.id NOT IN
(SELECT estimate_id
FROM t_order
WHERE estimate_id = e.id)
AND e.id = d.estimate_id
AND g.id = d.category_id
AND t1.estimate_id = e.id
AND e.delete_flg = 0
AND tc.delete_flg = 0
AND d.disable_flg = 0
AND ((e.estimate_date IS NULL
AND e.create_date BETWEEN :param_start_date AND :param_end_date )
OR (e.estimate_date IS NOT NULL
AND e.estimate_date BETWEEN :param_start_date AND :param_end_date ))
GROUP BY e.id,
customer_name,
e.estimate_date,
e.create_date,
sort_date,
e_create_date,
e.total,
e.car_num_num,
e.car_type,
proc_type,
work_staff,
wcheck_staff,
work_staff_name,
wcheck_staff_name,
receipt_staff_name,
receipt_staff,
sign_flg,
e.customer_id,
shaken_id,
repair_id,
e.car_id,
estimate__id,
insp_workflow_status,
repair_workflow_status
UNION ALL
SELECT e.id,
tc.name AS customer_name,
e.sch_date AS sort_date,
to_char(e.sch_date,'YYYY/MM/DD') AS e_create_date,
array_to_string(ARRAY
(SELECT t_order_detail.category_id
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), ',') AS category_id,
array_to_string(ARRAY
(SELECT m_dms_goods.name
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), '/') AS category_name,
e.total,
e.car_num_num,
e.car_type,
CASE
WHEN (so.order_id) IS NULL THEN '2_order'
WHEN (so.order_id) IS NOT NULL THEN '3_order'
ELSE '2_order'
END AS proc_type,
e.work_staff,
e.wcheck_staff,
st1.name AS work_staff_name,
st2.name AS wcheck_staff_name,
st3.name AS receipt_staff_name,
e.receipt_staff,
e.sign_flg AS sign_flg,
e.customer_id,
so.order_id AS shaken_id,
ro.order_id AS repair_id,
2 AS insp_workflow_status,
rw.workflow_status AS repair_workflow_status,
e.car_id,
e.estimate_id AS estimate__id
FROM t_order_detail d,
m_dms_goods g,
t_order e
LEFT OUTER JOIN m_shop_staff st1 ON st1.shop_id = :param_shop_id
AND st1.id = e.work_staff
LEFT OUTER JOIN m_shop_staff st2 ON st2.shop_id = :param_shop_id
AND st2.id = e.wcheck_staff
LEFT OUTER JOIN m_shop_staff st3 ON st3.shop_id = :param_shop_id
AND st3.id = e.receipt_staff
LEFT OUTER JOIN t_customer tc ON tc.id = e.customer_id
LEFT OUTER JOIN
(SELECT so.order_id
FROM t_shaken_order so,
t_order e
WHERE so.order_id = e.id
AND so.delete_flg = 0
AND so.delete_flg = 0
ORDER BY so.create_date DESC
LIMIT 1
OFFSET 0) so ON so.order_id = e.id
LEFT OUTER JOIN
(SELECT ro.order_id
FROM t_repair_order ro,
t_order e
WHERE ro.order_id = e.id
AND ro.delete_flg = 0
AND ro.delete_flg = 0
ORDER BY ro.create_date DESC
LIMIT 1
OFFSET 0) ro ON ro.order_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN rw.checkreport_date IS NOT NULL THEN 3
WHEN rw.order_date IS NOT NULL THEN 2
WHEN rw.estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
rw.order_id
FROM t_repair_workflow rw,
t_order e
WHERE rw.order_id = e.id
AND rw.customer_id = e.customer_id
AND rw.shop_id = 1
AND rw.cust_car_id = e.car_id
AND rw.delete_flg = 0
LIMIT 1) rw ON rw.order_id = e.id,
(SELECT DISTINCT d2.order_id
FROM t_order e2,
t_order_detail d2
WHERE e2.company_id = :param_company_id
AND e2.shop_id = :param_shop_id
AND d2.category_id IN (1,
9,
2,
8,
6,
5,
10,
7,
11,
12)
AND e2.id = d2.order_id
AND e2.sch_date BETWEEN :param_start_date AND :param_end_date ) t1
WHERE e.company_id = :param_company_id
AND e.shop_id = :param_shop_id
AND e.id = d.order_id
AND g.id = d.category_id
AND t1.order_id = e.id
AND e.delete_flg = 0
AND tc.delete_flg = 0
AND e.sch_date BETWEEN :param_start_date AND :param_end_date
GROUP BY e.id,
customer_name,
sort_date,
e_create_date,
e.total,
e.car_num_num,
e.car_type,
proc_type,
work_staff,
wcheck_staff,
work_staff_name,
wcheck_staff_name,
receipt_staff_name,
e.receipt_staff,
sign_flg,
e.customer_id,
shaken_id,
repair_id,
e.car_id,
estimate__id,
insp_workflow_status,
repair_workflow_status
UNION ALL
SELECT e.id,
tc.name AS customer_name,
e.sch_date AS sort_date,
to_char(e.sch_date,'YYYY/MM/DD') AS e_create_date,
array_to_string(ARRAY
(SELECT t_order_detail.category_id
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), ',') AS category_id,
array_to_string(ARRAY
(SELECT m_dms_goods.name
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), '/') AS category_name,
e.total,
e.car_num_num,
e.car_type,
'5_account' AS proc_type,
e.work_staff,
e.wcheck_staff,
st1.name AS work_staff_name,
st2.name AS wcheck_staff_name,
st3.name AS receipt_staff_name,
e.receipt_staff,
e.sign_flg AS sign_flg,
e.customer_id,
so.order_id AS shaken_id,
ro.order_id AS repair_id,
3 AS insp_workflow_status,
rw.workflow_status AS repair_workflow_status,
e.car_id,
e.estimate_id AS estimate__id
FROM t_order_detail d,
m_dms_goods g,
t_order e
LEFT OUTER JOIN m_shop_staff st1 ON st1.shop_id = :param_shop_id
AND st1.id = e.work_staff
LEFT OUTER JOIN m_shop_staff st2 ON st2.shop_id = :param_shop_id
AND st2.id = e.wcheck_staff
LEFT OUTER JOIN m_shop_staff st3 ON st3.shop_id = :param_shop_id
AND st3.id = e.receipt_staff
LEFT OUTER JOIN t_customer tc ON tc.id = e.customer_id
LEFT OUTER JOIN
(SELECT so.order_id
FROM t_shaken_order so,
t_order e
WHERE so.order_id = e.id
AND so.delete_flg = 0
AND so.delete_flg = 0
ORDER BY so.create_date DESC
LIMIT 1
OFFSET 0) so ON so.order_id = e.id
LEFT OUTER JOIN
(SELECT ro.order_id
FROM t_repair_order ro,
t_order e
WHERE ro.order_id = e.id
AND ro.delete_flg = 0
AND ro.delete_flg = 0
ORDER BY ro.create_date DESC
LIMIT 1
OFFSET 0) ro ON ro.order_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN checkreport_date IS NOT NULL THEN 3
WHEN order_date IS NOT NULL THEN 2
WHEN estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
rw.order_id
FROM t_repair_workflow rw,
t_order e
WHERE rw.order_id = e.id
AND rw.customer_id = e.customer_id
AND rw.shop_id = 1
AND rw.cust_car_id = e.car_id
AND rw.delete_flg = 0
LIMIT 1) rw ON rw.order_id = e.id,
(SELECT DISTINCT d2.order_id
FROM t_order e2,
t_order_detail d2
WHERE e2.company_id = :param_company_id
AND e2.shop_id = :param_shop_id
AND d2.category_id IN (1,
9,
2,
8,
6,
5,
10,
7,
11,
12)
AND e2.id = d2.order_id
AND e2.sch_date BETWEEN :param_start_date AND :param_end_date ) t1,
t_shaken_workflow t2
WHERE e.company_id = :param_company_id
AND e.shop_id = :param_shop_id
AND e.id = d.order_id
AND g.id = d.category_id
AND t1.order_id = e.id
AND e.delete_flg = 0
AND tc.delete_flg = 0
AND e.sch_date BETWEEN :param_start_date AND :param_end_date
AND t2.order_id = e.id
AND t2.accounts_date IS NOT NULL
GROUP BY e.id,
customer_name,
sort_date,
e_create_date,
e.total,
e.car_num_num,
e.car_type,
proc_type,
work_staff,
wcheck_staff,
work_staff_name,
wcheck_staff_name,
receipt_staff_name,
e.receipt_staff,
sign_flg,
e.customer_id,
shaken_id,
repair_id,
e.car_id,
estimate__id,
insp_workflow_status,
repair_workflow_status
UNION ALL
SELECT e.id,
tc.name AS customer_name,
e.sch_date AS sort_date,
to_char(e.sch_date,'YYYY/MM/DD') AS e_create_date,
array_to_string(ARRAY
(SELECT t_order_detail.category_id
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), ',') AS category_id,
array_to_string(ARRAY
(SELECT m_dms_goods.name
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), '/') AS category_name,
e.total,
e.car_num_num,
e.car_type,
'2_deposit' AS proc_type,
e.work_staff,
e.wcheck_staff,
st1.name AS work_staff_name,
st2.name AS wcheck_staff_name,
st3.name AS receipt_staff_name,
e.receipt_staff,
e.sign_flg AS sign_flg,
e.customer_id,
so.order_id AS shaken_id,
ro.order_id AS repair_id,
4 AS insp_workflow_status,
rw.workflow_status AS repair_workflow_status,
e.car_id,
e.estimate_id AS estimate__id
FROM t_order_detail d,
m_dms_goods g,
t_order e
LEFT OUTER JOIN m_shop_staff st1 ON st1.shop_id = :param_shop_id
AND st1.id = e.work_staff
LEFT OUTER JOIN m_shop_staff st2 ON st2.shop_id = :param_shop_id
AND st2.id = e.wcheck_staff
LEFT OUTER JOIN m_shop_staff st3 ON st3.shop_id = :param_shop_id
AND st3.id = e.receipt_staff
LEFT OUTER JOIN t_customer tc ON tc.id = e.customer_id
LEFT OUTER JOIN
(SELECT so.order_id
FROM t_shaken_order so,
t_order e
WHERE so.order_id = e.id
AND so.delete_flg = 0
AND so.delete_flg = 0
ORDER BY so.create_date DESC
LIMIT 1
OFFSET 0) so ON so.order_id = e.id
LEFT OUTER JOIN
(SELECT ro.order_id
FROM t_repair_order ro,
t_order e
WHERE ro.order_id = e.id
AND ro.delete_flg = 0
AND ro.delete_flg = 0
ORDER BY ro.create_date DESC
LIMIT 1
OFFSET 0) ro ON ro.order_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN rw.checkreport_date IS NOT NULL THEN 3
WHEN rw.order_date IS NOT NULL THEN 2
WHEN rw.estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
rw.order_id
FROM t_repair_workflow rw,
t_order e
WHERE rw.order_id = e.id
AND rw.customer_id = e.customer_id
AND rw.shop_id = 1
AND rw.cust_car_id = e.car_id
AND rw.delete_flg = 0
LIMIT 1) rw ON rw.order_id = e.id,
(SELECT DISTINCT d2.order_id
FROM t_order e2,
t_order_detail d2
WHERE e2.company_id = :param_company_id
AND e2.shop_id = :param_shop_id
AND d2.category_id IN (1,
9,
2,
8,
6,
5,
10,
7,
11,
12)
AND e2.id = d2.order_id
AND e2.sch_date BETWEEN :param_start_date AND :param_end_date ) t1,
t_shaken_workflow t2
WHERE e.company_id = :param_company_id
AND e.shop_id = :param_shop_id
AND e.id = d.order_id
AND g.id = d.category_id
AND t1.order_id = e.id
AND e.delete_flg = 0
AND tc.delete_flg = 0
AND e.sch_date BETWEEN :param_start_date AND :param_end_date
AND t2.order_id = e.id
AND t2.deposit_date IS NOT NULL
GROUP BY e.id,
customer_name,
sort_date,
e_create_date,
e.total,
e.car_num_num,
e.car_type,
proc_type,
work_staff,
wcheck_staff,
work_staff_name,
wcheck_staff_name,
receipt_staff_name,
e.receipt_staff,
sign_flg,
e.customer_id,
shaken_id,
repair_id,
e.car_id,
estimate__id,
insp_workflow_status,
repair_workflow_status
UNION ALL
SELECT e.id,
tc.name AS customer_name,
e.sch_date AS sort_date,
to_char(e.sch_date,'YYYY/MM/DD') AS e_create_date,
array_to_string(ARRAY
(SELECT t_order_detail.category_id
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), ',') AS category_id,
array_to_string(ARRAY
(SELECT m_dms_goods.name
FROM t_order_detail
INNER JOIN m_dms_goods ON m_dms_goods.id = t_order_detail.category_id
WHERE t_order_detail.order_id = e.id
GROUP BY category_id, m_dms_goods.name), '/') AS category_name,
e.total,
e.car_num_num,
e.car_type,
'4_estimate_inspection' AS proc_type,
e.work_staff,
e.wcheck_staff,
st1.name AS work_staff_name,
st2.name AS wcheck_staff_name,
st3.name AS receipt_staff_name,
e.receipt_staff,
e.sign_flg AS sign_flg,
e.customer_id,
so.order_id AS shaken_id,
ro.order_id AS repair_id,
5 AS insp_workflow_status,
rw.workflow_status AS repair_workflow_status,
e.car_id,
e.estimate_id AS estimate__id
FROM t_order_detail d,
m_dms_goods g,
t_order e
LEFT OUTER JOIN m_shop_staff st1 ON st1.shop_id = :param_shop_id
AND st1.id = e.work_staff
LEFT OUTER JOIN m_shop_staff st2 ON st2.shop_id = :param_shop_id
AND st2.id = e.wcheck_staff
LEFT OUTER JOIN m_shop_staff st3 ON st3.shop_id = :param_shop_id
AND st3.id = e.receipt_staff
LEFT OUTER JOIN t_customer tc ON tc.id = e.customer_id
LEFT OUTER JOIN
(SELECT so.order_id
FROM t_shaken_order so,
t_order e
WHERE so.order_id = e.id
AND so.delete_flg = 0
AND so.delete_flg = 0
ORDER BY so.create_date DESC
LIMIT 1
OFFSET 0) so ON so.order_id = e.id
LEFT OUTER JOIN
(SELECT ro.order_id
FROM t_repair_order ro,
t_order e
WHERE ro.order_id = e.id
AND ro.delete_flg = 0
AND ro.delete_flg = 0
ORDER BY ro.create_date DESC
LIMIT 1
OFFSET 0) ro ON ro.order_id = e.id
LEFT OUTER JOIN
(SELECT CASE
WHEN rw.checkreport_date IS NOT NULL THEN 3
WHEN rw.order_date IS NOT NULL THEN 2
WHEN rw.estimate_date IS NOT NULL THEN 1
ELSE 0
END AS workflow_status,
rw.order_id
FROM t_repair_workflow rw,
t_order e
WHERE rw.order_id = e.id
AND rw.customer_id = e.customer_id
AND rw.shop_id = 1
AND rw.cust_car_id = e.car_id
AND rw.delete_flg = 0
LIMIT 1) rw ON rw.order_id = e.id,
(SELECT DISTINCT d2.order_id
FROM t_order e2,
t_order_detail d2
WHERE e2.delete_flg = 0
AND e2.company_id = :param_company_id
AND e2.shop_id = :param_shop_id
AND d2.category_id IN (1,
9,
2,
8,
6,
5,
10,
7,
11,
12)
AND e2.id = d2.order_id
AND e2.sch_date BETWEEN :param_start_date AND :param_end_date ) t1,
t_shaken_workflow t2
WHERE e.company_id = :param_company_id
AND e.shop_id = :param_shop_id
AND e.id = d.order_id
AND g.id = d.category_id
AND t1.order_id = e.id
AND e.delete_flg = 0
AND tc.delete_flg = 0
AND e.sch_date BETWEEN :param_start_date AND :param_end_date
AND t2.order_id = e.id
AND t2.final_estimate_date IS NOT NULL
AND t2.final_estimate_date >= '2016-10-07'
GROUP BY e.id,
customer_name,
sort_date,
e_create_date,
e.total,
e.car_num_num,
e.car_type,
proc_type,
work_staff,
wcheck_staff,
work_staff_name,
wcheck_staff_name,
receipt_staff_name,
e.receipt_staff,
sign_flg,
e.customer_id,
shaken_id,
repair_id,
e.car_id,
estimate__id,
insp_workflow_status,
repair_workflow_status
ORDER BY proc_type DESC,
sort_date DESC";
// Data for bind param
$startDate = '2017-03-02 10:00:00';
$endDate = '2017-03-02 10:00:00';
$companyId = '1';
$shopId = '1';
// Prepare statement
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':param_company_id', $startDate, PDO::PARAM_INT);
$stmt->bindParam(':param_shop_id', $shopId, PDO::PARAM_INT);
$stmt->bindParam(':param_start_date ', $startDate, PDO::PARAM_STR);
$stmt->bindParam(':param_end_date', $endDate, PDO::PARAM_STR);
$stmt->execute();
var_dump($stmt);
die;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment