2qc4AkRE

1DROP TEMPORARY TABLE IF EXISTS finaltable /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
2# time took:0.00097799301147461;
3DROP TABLE IF EXISTS tmp_non_replicate_05d9c41169d438,tmp_non_replicate_15d9c41169d453,tmp_non_replicate_25d9c41169d466,tmp_non_replicate_35d9c41169d479,tmp_non_replicate_45d9c41169d489,tmp_non_replicate_55d9c41169d497 /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
4# time took:0.0027329921722412;
5CREATE TEMPORARY TABLE tmp_non_replicate_35d9c41169d479 (INDEX(ent_hd_employee), INDEX(lt_id)) SELECT ent_hd_id, ent_hd_employee, emp_id, emp_code, emp_firstname, emp_lastname, emp_deptid, emp_project, concat( emp_code , ' : ', emp_lastname , ' ' ,emp_firstname) AS employee_name, emp_status, IF(emp_status = 0, 'All Employee', IF(emp_status = 1, 'Without Resign Employee', IF(emp_status = 2, 'Resign Employee', ''))) AS emp_status_name, dept_id, dept_code, dept_deptname AS department_name, concat(dept_code,' : ',dept_deptname) AS department, project_id, project_code, IFNULL(project_projecttitle, 'Unassigned Project') AS project_name, IF(SUBSTRING(CAST(ent_dt_lvmax AS DECIMAL(16,2)), -2) < 50, FLOOR(ent_dt_lvmax), FLOOR(ent_dt_lvmax) + 0.50) AS ent_dt_lvmax, ent_dt_leavebringforward, IF(SUBSTRING(CAST(ent_dt_lvmax AS DECIMAL(16,2)), -2) < 50, FLOOR(ent_dt_lvmax), FLOOR(ent_dt_lvmax) + 0.50) AS opening, lt_id, lt_code ,lt_name FROM thp_leave_entitlement_header INNER JOIN thp_leave_entitlement_detail ON ent_hd_dt_id = ent_hd_id INNER JOIN thp_leave_type ON lt_id = ent_dt_leavetypeid INNER JOIN thp_employee ON ent_hd_employee=emp_id INNER JOIN thp_department ON dept_id = emp_deptid LEFT JOIN thp_project ON project_id = emp_project WHERE emp_levelno > 0 AND ent_dt_from >= concat(date_format('2019-10-02','%Y'), '-01-01') AND ent_dt_to <= concat(date_format('2019-10-31','%Y'), '-12-31') and (emp_code >= '0000001') and (emp_code <= 'C01') and (dept_code >= 'Company' or dept_code is null) and (dept_code <= 'Company' or dept_code is null) and (project_code >= 'D' or project_code is null) and (lt_code >= 'al' or lt_code is null) and (lt_code <= 'PL' or lt_code is null) and (CASE WHEN (0 = 1) THEN emp_dateresigned IS NULL WHEN (0=2) THEN emp_dateresigned IS NOT NULL ELSE 1 END) GROUP BY ent_hd_employee, lt_id /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
6# time took:0.09247088432312;
7/*opening_balance*/ CREATE TEMPORARY TABLE tmp_non_replicate_45d9c41169d489 (INDEX(lv_empid), INDEX(lv_type)) SELECT lv_id, lv_empid, lv_type, lv_date_from, lv_date_to, DATE_FORMAT(lv_date_from, '%Y - %m') AS yearmonth, SUM(IF(lv_status = 'A', IF(lv_half_day = 1, '0.5', datediff(lv_date_to,lv_date_from) + 1),0)) AS total, SUM(IF(lv_status = 'R', IF(lv_half_day = 1, '0.5', datediff(lv_date_to,lv_date_from) + 1), 0)) AS reject, SUM(IF(lv_status = 'A', IF(lv_half_day = 1, '0.5', datediff(lv_date_to,lv_date_from) + 1), 0)) AS approved, SUM(IF(lv_status = 'P', IF(lv_half_day = 1, '0.5', datediff(lv_date_to,lv_date_from) + 1), 0)) AS pending FROM thp_leave INNER JOIN thp_employee ON lv_empid=emp_id INNER JOIN thp_department ON dept_id = emp_deptid INNER JOIN thp_leave_type ON lt_id = lv_type LEFT JOIN thp_project ON project_id = emp_project WHERE emp_levelno > 0 AND lv_date_from >= '2019-10-02' AND lv_date_to <= '2019-10-31' and (emp_code >= '0000001') and (emp_code <= 'C01') and (dept_code >= 'Company' or dept_code is null) and (dept_code <= 'Company' or dept_code is null) and (project_code >= 'D' or project_code is null) and (lt_code >= 'al' or lt_code is null) and (lt_code <= 'PL' or lt_code is null) and (CASE WHEN (0 = 1) THEN emp_dateresigned IS NULL WHEN (0=2) THEN emp_dateresigned IS NOT NULL ELSE 1 END) GROUP BY lv_empid, lv_type /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
8# time took:0.025390148162842;
9CREATE TEMPORARY TABLE tmp_non_replicate_25d9c41169d466 (INDEX(lv_adjust_employee),INDEX(lv_adjust_leave_type)) SELECT lv_adjust_id, lv_adjust_employee, lv_adjust_type, lv_adjust_leave_type, lv_adjust_total, lv_adjust_date_effective, DATE_FORMAT(lv_adjust_date_effective, '%Y - %m') AS yearmonth, SUM(IF(lv_adjust_type = 0, - lv_adjust_total, lv_adjust_total)) AS total_adjust FROM thp_leave_adjustment INNER JOIN thp_employee ON lv_adjust_employee=emp_id INNER JOIN thp_department ON dept_id = emp_deptid INNER JOIN thp_leave_type ON lt_id = lv_adjust_leave_type LEFT JOIN thp_project ON project_id = emp_project WHERE emp_levelno > 0 AND lv_adjust_date_effective BETWEEN '2019-10-02' AND '2019-10-31' and (emp_code >= '0000001') and (emp_code <= 'C01') and (dept_code >= 'Company' or dept_code is null) and (dept_code <= 'Company' or dept_code is null) and (project_code >= 'D' or project_code is null) and (lt_code >= 'al' or lt_code is null) and (lt_code <= 'PL' or lt_code is null) and (CASE WHEN (0 = 1) THEN emp_dateresigned IS NULL WHEN (0=2) THEN emp_dateresigned IS NOT NULL ELSE 1 END) GROUP BY lv_adjust_employee, lv_adjust_leave_type /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
10# time took:0.019536972045898;
11DROP TABLE IF EXISTS tbl_opening_5d9c41169e202 /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
12# time took:0.00071811676025391;
13CREATE TEMPORARY TABLE tbl_opening_5d9c41169e202 (INDEX(bf_emp_id), INDEX(bf_lt_id)) SELECT ent_hd_employee as bf_emp_id, lt_id as bf_lt_id, ent_dt_lvmax - IFNULL(total, 0) + IFNULL(total_adjust, 0) as bf_balance FROM tmp_non_replicate_35d9c41169d479 LEFT JOIN tbl_leave_taken_bf_5d9c41169e1fc ON lv_empid = ent_hd_employee AND lv_type = lt_id LEFT JOIN tbl_leave_adjust_bf_5d9c41169e200 ON lv_empid = lv_adjust_employee AND lv_type = lv_adjust_leave_type /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
14# time took:0.038897037506104;
15UPDATE tmp_non_replicate_35d9c41169d479 INNER JOIN tbl_opening_5d9c41169e202 ON ent_hd_employee=bf_emp_id and lt_id=bf_lt_id SET opening = bf_balance /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
16# time took:0.0011258125305176;
17CREATE TEMPORARY TABLE tmp_non_replicate_15d9c41169d453 SELECT tmp_non_replicate_35d9c41169d479.*, CONCAT(tmp_non_replicate_35d9c41169d479.lt_code,':', tmp_non_replicate_35d9c41169d479.lt_name) as leave_code, #tmp_non_replicate_35d9c41169d479.lt_code AS leave_code, tmp_non_replicate_35d9c41169d479.lt_name AS leave_type, tmp_non_replicate_35d9c41169d479.ent_dt_lvmax AS total_entitle2, tmp_non_replicate_35d9c41169d479.opening AS total_opening2, tmp_non_replicate_35d9c41169d479.opening - IFNULL(tmp_non_replicate_45d9c41169d489.total, 0) AS balance_before_adjust2, tmp_non_replicate_25d9c41169d466.lv_adjust_date_effective, IFNULL(tmp_non_replicate_25d9c41169d466.total_adjust, 0) AS total_adjust2, IFNULL(tmp_non_replicate_45d9c41169d489.total, 0) AS leave_taken2, tmp_non_replicate_35d9c41169d479.opening - IFNULL(tmp_non_replicate_45d9c41169d489.total, 0) + IFNULL(tmp_non_replicate_25d9c41169d466.total_adjust, 0) AS balance_after_adjust2, IFNULL(tmp_non_replicate_45d9c41169d489.approved,0) AS total_approved2, IFNULL(tmp_non_replicate_45d9c41169d489.pending,0) AS total_pending2, IFNULL(tmp_non_replicate_45d9c41169d489.reject,0) AS total_reject2 FROM tmp_non_replicate_35d9c41169d479 LEFT JOIN tmp_non_replicate_45d9c41169d489 ON lv_empid = ent_hd_employee AND lv_type = lt_id LEFT JOIN tmp_non_replicate_25d9c41169d466 ON ent_hd_employee = lv_adjust_employee AND lt_id = lv_adjust_leave_type /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
18# time took:0.044935941696167;
19CREATE TEMPORARY TABLE tmp_non_replicate_05d9c41169d438 SELECT tmp_non_replicate_15d9c41169d453.*, ROUND(SUM(IFNULL(total_entitle2,0)),2) as total_entitle, ROUND(SUM(IFNULL(total_opening2,0)),2) as total_opening, ROUND(SUM(IFNULL(leave_taken2,0)),2) as leave_taken, ROUND(SUM(IFNULL(total_approved2,0)),2) as total_approved, ROUND(SUM(IFNULL(total_pending2,0)),2) as total_pending, ROUND(SUM(IFNULL(total_reject2,0)),2) as total_reject, ROUND(SUM(IFNULL(balance_before_adjust2,0)),2) as balance_before_adjust, ROUND(SUM(IFNULL(total_adjust2,0)),2) as total_adjust, ROUND(SUM(IFNULL(balance_after_adjust2,0)),2) as total_balance, concat(ifnull(employee_name,'-')) as Group10 FROM tmp_non_replicate_15d9c41169d453 GROUP BY Group10 WITH ROLLUP /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
20# time took:0.032831192016602;
21CREATE TEMPORARY TABLE finaltable SELECT Group10, total_entitle,total_opening,leave_taken,total_approved,total_pending,total_reject,balance_before_adjust,total_adjust,total_balance FROM tmp_non_replicate_05d9c41169d438 /*u1=/ver5.14.02/modules/xeersoft_dynamic/xeersoft_inv_report_dt.php=nll000=qrID99=rightFrame2*/;
22# time took:0.028088808059692;
23select