if (isset($_POST['PT_1']) && $_POST['PT_1'] == 1) { $WherePT .= $WherePT != "" ? " ,1 " : "1 "; } if (isset($_POST['PT_2']) && $_POST['PT_2'] == 1) { $WherePT .= $WherePT != "" ? " ,2 " : "2 "; } if (isset($_POST['PT_3']) && $_POST['PT_3'] == 1) { $WherePT .= $WherePT != "" ? " ,3 " : "3 "; } if (isset($_POST['PT_5']) && $_POST['PT_5'] == 1) { $WherePT .= $WherePT != "" ? " ,5 " : "5"; } $pament_type = $_POST['PayType']; $staffID = isset($_POST['staff_id']) && $_POST['staff_id'] > 0 ? $_POST['staff_id'] : " "; if ($_POST['PayType'] != 2) { manage_salary_utils::simulate_tax($_POST['pay_year'], $_POST['pay_month'], $pament_type); manage_salary_utils::simulate_bime($_POST['pay_year'], $_POST['pay_month'], $pament_type); $value = getSalaryItemValue(); $treasure_items = SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED . ',' . SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ',744,' . SIT1_BASE_SALARY . ',34,' . SIT2_BASE_SALARY . ',' . SIT2_BASE_SALARY . ',' . SIT_STAFF_ANNUAL_INC . ',36,' . SIT_WORKER_ANNUAL_INC . ',' . SIT_STAFF_JOB_EXTRA . ',' . SIT_PROFESSOR_ADAPTION_DIFFERENCE . ',' . SIT_STAFF_EQUALITY_DIFFERENCE . ',57,' . SIT_STAFF_HARD_WORK_EXTRA . ',49,' . SIT_PROFESSOR_BAD_WEATHER_EXTRA . ',46,' . SIT_STAFF_BAD_WEATHER_EXTRA . ',' . SIT_STAFF_FACILITIES_VITIOSITY_EXTRA . ',' . SIT_STAFF_DUTY_LOCATION_EXTRA . ',' . SIT_STAFF_SHIFT_EXTRA . ',' . SIT_WORKER_SHIFT_EXTRA . ',55,' . SIT_EMPLOYEE_SPECIAL_EXTRA . ',' . SIT_PROFESSOR_PARTICULAR_EXTRA . ',' . SIT_STAFF_DOMINANT_JOB_EXTRA . ',' . SIT_STAFF_CHILD_RIGHT . ',50,' . SIT_PROFESSOR_CHILD_RIGHT . ',' . SIT_WORKER_CHILD_RIGHT . ',' . SIT_PROFESSOR_CHILDREN_RIGHT . ',51,' . SIT_STAFF_CHILDREN_RIGHT . ',' . SIT_STAFF_ABSOPPTION_EXTRA . ',' . SIT_PROFESSOR_ABSOPPTION_EXTRA . ',' . SIT_EMPLOYEE_SEVEN_PERCENT_ABSORB_EXTRA . ',' . SIT_STAFF_HEIAT_OMANA_SPECIAL_EXTRA . ',' . SIT_STAFF_MIN_PAY . ',45,' . SIT_STAFF_ADJUST_EXTRA . ',' . SIT_STAFF_ABSORB_EXTRA_8_9 . ',' . SIT_STAFF_DEPRIVED_REGIONS_ABSOPPTION_EXTRA . ',54,' . SIT_PROFESSOR_SPECIAL_EXTRA . ',' . '507' . ',' . '9944' . ',' . '9969' . ',56,47,' . SIT_PROFESSOR_DEVOTION_EXTRA . ',' . SIT_STAFF_ADAPTION_DIFFERENCE . ',' . SIT_STAFF_WORK_WITH_RAY_EXTRA . ',' . SIT_PROFESSOR_WORK_WITH_RAY_EXTRA . ',' . SIT_PROFESSOR_MANAGMENT_EXTRA . ',35,' . SIT_PROFESSOR_DEVOTION_EXTRA . ',' . SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED . ',' . SIT_RETURN_INSURE_AND_RETIRED_WOUNDED_PERSONS . ',' . RETURN_FIRST_MONTH_MOGHARARY . ',' . SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_TAX . ',' . SIT_STAFF_TAX . ',' . SIT_WORKER_TAX . ',747,' . SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED . ',' . SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_REMEDY_SERVICES_INSURE . ',' . PROFESSOR_FIRST_MONTH_MOGHARARY . ',' . STAFF_FIRST_MONTH_MOGHARARY . ',10264 , 10364 , 10265 ,10365, 10266 ,10366 , 10267 , 10367, 10327,10368 , 10328 , 10369 , 10329 ,10370, 10330 ,10371 , 10331 ,10372, 10332 ,10377,10373 , 10333 ,10374, 10334,10375 , 10335 ,10376'; $insure_and_tax = SIT_PROFESSOR_TAX . ',' . SIT_STAFF_TAX . ',' . SIT_WORKER_TAX . ',' . SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE; $other_subtracts = " pai.salary_item_type_id NOT IN ({$treasure_items}) AND sit.effect_type = 2 AND sit.credit_topic = " . CREDIT_TOPIC_1; $other_payments = " pai.salary_item_type_id NOT IN ({$treasure_items}) AND sit.effect_type = 1 AND sit.credit_topic = " . CREDIT_TOPIC_1; $query = " SELECT \n\t\t\t\t\t\t'115500' item_1 ,\n\t\t\t\t\t\tprs.national_code item_2 ,\n\t\t\t\t\t\ts.staff_id item_3,\n\t\t\t\t\t\tprs.birth_date item_4,\n\t\t\t\t\t\tw.onduty_year item_5,\n\t\t\t\t\t\tprs.pfname item_6,\n\t\t\t\t\t\tprs.plname item_7,\n\t\t\t\t\t\tCASE prs.sex WHEN 1 THEN 2 ELSE 4 END item_8,\n\t\t\t\t\t\tCASE prs.marital_status WHEN 1 THEN 1 ELSE 3 END item_9,\n\t\t\t\t\t\tw.children_count item_10,\n\t\t\t\t\t\tCASE WHEN w.emp_state = 1 OR w.emp_state = 2 OR w.emp_state = 10 THEN 6 ELSE 5 END item_11 ,\n\t\t\t\t\t\tw.education_level item_12,\n\t\t\t\t\t\tCASE WHEN SUM(CASE WHEN pai.salary_item_type_id NOT IN (" . SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED . ") THEN 0 ELSE 1 END)>0 THEN 7 ELSE 8 END item_13 ,\n\t\t\t\t\t\tCASE WHEN SUM(CASE WHEN pai.salary_item_type_id NOT IN (" . SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ",744) THEN 0 ELSE 1 END)>0 THEN 2 ELSE 1 END item_14 ,\n\t\t\t\t\t\t" . get_salary_items_select(SIT1_BASE_SALARY . ',' . SIT2_BASE_SALARY . ',34,10264, 10364,' . SIT2_BASE_SALARY) . " item_15 ," . get_salary_items_select(SIT_STAFF_ANNUAL_INC . ',36,' . SIT_WORKER_ANNUAL_INC) . " item_16," . get_salary_items_select(SIT_STAFF_JOB_EXTRA . ',10267, 10367, 10367') . " item_17 , " . get_salary_items_select(SIT_PROFESSOR_ADAPTION_DIFFERENCE . ',56,10335,10376') . " item_18 ," . get_salary_items_select(SIT_STAFF_EQUALITY_DIFFERENCE . ',57') . " item_19," . get_salary_items_select(SIT_STAFF_HARD_WORK_EXTRA . ',49,10328,10369') . " item_20," . get_salary_items_select(SIT_PROFESSOR_BAD_WEATHER_EXTRA . ',46,10333,10374,' . SIT_STAFF_BAD_WEATHER_EXTRA) . " item_21," . get_salary_items_select(SIT_STAFF_FACILITIES_VITIOSITY_EXTRA) . " item_22," . get_salary_items_select(SIT_STAFF_DUTY_LOCATION_EXTRA) . " item_23," . get_salary_items_select(SIT_STAFF_SHIFT_EXTRA . ',' . SIT_WORKER_SHIFT_EXTRA . ',' . '55,10331,10372') . " item_24," . get_salary_items_select(SIT_EMPLOYEE_SPECIAL_EXTRA . ',10265,10365,' . SIT_PROFESSOR_PARTICULAR_EXTRA) . " item_25," . get_salary_items_select(SIT_STAFF_DOMINANT_JOB_EXTRA . ',48') . " item_26," . get_salary_items_select(SIT_STAFF_CHILD_RIGHT . ',50,10330,10371,' . SIT_PROFESSOR_CHILD_RIGHT . ',' . SIT_WORKER_CHILD_RIGHT) . " item_27," . get_salary_items_select(SIT_PROFESSOR_CHILDREN_RIGHT . ',51,10329,10370,' . SIT_STAFF_CHILDREN_RIGHT) . " item_28," . get_salary_items_select(SIT_STAFF_ABSOPPTION_EXTRA . ',' . SIT_PROFESSOR_ABSOPPTION_EXTRA . ',' . SIT_EMPLOYEE_SEVEN_PERCENT_ABSORB_EXTRA . ',47,10266,10366,' . SIT_PROFESSOR_DEVOTION_EXTRA) . " item_29 ," . get_salary_items_select(SIT_STAFF_MIN_PAY . ',45,10327,10368') . " item_30 ," . get_salary_items_select(SIT_STAFF_ADJUST_EXTRA . ',' . SIT_STAFF_ABSORB_EXTRA_8_9) . " item_31 ," . "0 item_32 ," . get_salary_items_select(SIT_STAFF_DEPRIVED_REGIONS_ABSOPPTION_EXTRA . ',54') . " item_33 ," . get_salary_items_select(SIT_PROFESSOR_SPECIAL_EXTRA . ',284') . " item_34 ," . "0 item_35 ," . get_salary_items_select_insure(SIT_STAFF_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_REMEDY_SERVICES_INSURE) . " item_36 ," . "0 item_37 ," . get_salary_items_select(SIT_STAFF_ADAPTION_DIFFERENCE) . " item_38 ," . get_salary_items_select(SIT_STAFF_WORK_WITH_RAY_EXTRA . ',10334,10375,' . SIT_PROFESSOR_WORK_WITH_RAY_EXTRA) . " item_39 ," . "0 item_40 ," . "0 item_41 ," . get_salary_items_select(SIT_PROFESSOR_EXCLUDE_MANAGEMENT_EXTRA . ',35,' . SIT_STAFF_EXCLUDE_MANAGEMENT_EXTRA . ',10332,10377,10373,' . SIT_PROFESSOR_MANAGMENT_EXTRA) . " item_42 ," . get_salary_items_select(SIT_RETURN_INSURE_AND_RETIRED_WOUNDED_PERSONS . ',' . RETURN_FIRST_MONTH_MOGHARARY) . " item_43 ," . get_salary_item_gov_share(SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED) . " item_44 ," . get_insure_gov_share(SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ',744') . " item_45 ," . get_salary_items_select_insure_dolat(SIT_STAFF_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_REMEDY_SERVICES_INSURE) . " item_46 ," . "0 item_47 ," . "SUM(CASE WHEN " . $other_payments . " THEN 1*" . $value . " ELSE 0 END) item_48 ," . get_tax(SIT_PROFESSOR_TAX . ',' . SIT_STAFF_TAX . ',' . SIT_WORKER_TAX . ',747', 1) . " item_49 ," . get_salary_items_select(SIT_PROFESSOR_RETIRED . ',' . SIT_STAFF_RETIRED, -1) . " item_50 ," . get_insure_staff_share(SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE . ',' . SIT_STAFF_COLLECTIVE_SECURITY_INSURE . ',' . SIT_WORKER_COLLECTIVE_SECURITY_INSURE . ',744') . " item_51 ," . get_salary_items_select(SIT_STAFF_REMEDY_SERVICES_INSURE . ',' . SIT_PROFESSOR_REMEDY_SERVICES_INSURE, -1) . " item_52 ," . "0 item_53 ," . get_salary_items_select(PROFESSOR_FIRST_MONTH_MOGHARARY . ',' . STAFF_FIRST_MONTH_MOGHARARY . ',' . EMPLOYEE_FIRST_MONTH_MOGHARARY_DEBT, -1) . " item_54 ," . "SUM(CASE WHEN " . $other_subtracts . " THEN -1*" . $value . " ELSE 0 END) item_55 ,\n\t\t\t\t\t\t0 item_56 ,\n\t\t\t\t\t\ts.account_no item_57 ,\n\t\t\t\t\t\tb.name item_58 ,\n\t\t\t\t\t\tb.name item_59 ,\n\t\t\t\t\t\tb.branch_code item_60\n\t\t\t\t\tFROM\n\t\t\t\t\t\tpayments p\n\t\t\t\t\t\tINNER JOIN payment_items pai\n\t\t\t\t\t\t\tON(p.pay_year = pai.pay_year AND p.pay_month = pai.pay_month AND p.staff_id = pai.staff_id AND p.payment_type = pai.payment_type)\n\t\t\t\t\t\tINNER JOIN salary_item_types sit\n\t\t\t\t\t\t\tON(pai.salary_item_type_id = sit.salary_item_type_id)\n\t\t\t\t\t\tINNER JOIN cost_centers c\n\t\t\t\t\t\t\tON(pai.cost_center_id = c.cost_center_id)\n\t\t\t\t\t\tINNER JOIN staff s\n\t\t\t\t\t\t\tON(p.staff_id = s.staff_id)\n\t\t\t\t\t\tINNER JOIN persons prs\n\t\t\t\t\t\t\tON(s.PersonID = prs.PersonID)\n\t\t\t\t\t\tLEFT JOIN writs w\n\t\t\t\t\t\t\tON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id )\n\t\t\t\t\t\tINNER JOIN banks b\n\t\t\t\t\t\t\tON(p.bank_id = b.bank_id)\n\t\t\t\t\t\t\t\n\t\t\t\t\t\tLEFT OUTER JOIN temp_tax_include_sum tts\n\t\t\t\t\t\t\tON (tts.staff_id = pai.staff_id AND pai.salary_item_type_id IN(146,147,148,747))\n\t\t\t\t\t\t\t\n\t\t\t\t\t\tLEFT OUTER JOIN temp_insure_include_sum tis\n\t\t\t\t\t\t\tON (tis.staff_id = pai.staff_id AND pai.salary_item_type_id IN(9920,145,144,744))\n\t\t\t\t\t\n\t\t\t\t\tWHERE pai.pay_year = " . $_POST['pay_year'] . " AND\n\t\t\t\t\t\t pai.pay_month = " . $_POST['pay_month'] . " AND\n\t\t\t\t\t\t pai.payment_type = " . $pament_type; $query .= $WhereCost != "" ? " AND pai.cost_center_id in (" . $WhereCost . ") " : " "; $query .= $WhereEmpstate != "" ? " AND w.emp_state in (" . $WhereEmpstate . ") " : " "; $query .= $WherePT != "" ? " AND s.person_type in (" . $WherePT . ") " : " "; $query .= $staffID != " " ? " AND s.staff_id in (" . $staffID . ") " : " "; $query .= " GROUP BY '115500' ,\n\t\t\t\t\t\tprs.national_code ,\n\t\t\t\t\t\ts.staff_id ,\n\t\t\t\t\t\tprs.birth_date ,\n\t\t\t\t\t\tw.onduty_year ,\n\t\t\t\t\t\tprs.pfname,\n\t\t\t\t\t\tprs.plname,\n\t\t\t\t\t\tprs.sex ,\n\t\t\t\t\t\tprs.marital_status ,\n\t\t\t\t\t\tw.children_count,\n\t\t\t\t\t\tw.emp_mode ,\n\t\t\t\t\t\tw.education_level,\n\t\t\t\t\t\ts.account_no,\n\t\t\t\t\t\tb.name,\n\t\t\t\t\t\tb.branch_code"; //echo $query ; die() ; $res = PdoDataAccess::runquery($query);
function PrepareData(&$person_count,$type){ global $GroupCostCenter; global $GroupPersonType; global $where; global $param; $payment_type = $_POST["PayType"] ; $pay_year = $_POST["pay_year"]; $pay_month = $_POST["pay_month"]; $where = ""; $param = array( ":year" => $pay_year, ":month" => $pay_month, ":ptype" => $payment_type ); makeWhere(); $group = makeGroup(); $month_start = DateModules::shamsi_to_miladi($pay_year . "-" . $pay_month . "-1"); if($type == 'TSummary') { manage_salary_utils::simulate_tax($pay_year, $pay_month, $payment_type); manage_salary_utils::simulate_bime($pay_year, $pay_month, $payment_type); $MainRows = PdoDataAccess::runquery(" SELECT pi.salary_item_type_id, sit.full_title sit_title, sit.effect_type, max(pi.pay_year) pay_year, max(pi.pay_month) pay_month, sum(pay_value) pay_sum, sum(diff_pay_value * diff_value_coef) diff_pay_sum, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.value WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.value ELSE get_value END) get_sum, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_get_value * diff_value_coef END) diff_get_sum, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.param2 WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.param2 ELSE pi.param2 END) param2, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_param2 * diff_param2_coef END) diff_param2, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.param3 WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.param3 ELSE pi.param3 END) param3, sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_param3 * diff_param3_coef END) diff_param3, sum(CASE WHEN (s.last_retired_pay IS NOT NULL AND s.last_retired_pay < '$month_start') THEN pi.param3 END) retired_for_org_up30, sum(CASE WHEN (s.last_retired_pay IS NOT NULL AND s.last_retired_pay < '$month_start') THEN diff_param3 * diff_param3_coef END) diff_retired_for_org_up30, sum(CASE WHEN (s.last_retired_pay IS NULL OR s.last_retired_pay >= '$month_start') THEN pi.param3 END) retired_for_org_dn30, sum(CASE WHEN (s.last_retired_pay IS NULL OR s.last_retired_pay >= '$month_start') THEN diff_param3 * diff_param3_coef END) diff_retired_for_org_dn30, sum(pi.param7) param7, sum(pi.diff_param7 * pi.diff_value_coef) diff_param7, max(s.person_type) person_type, max(pi.cost_center_id) cost_center_id, max(cc.title) cost_center_title, max(ou.ptitle) unit_title, max(s.person_type) person_type, max(w.ouid) ouid FROM payment_items pi INNER JOIN payments p ON(pi.pay_year = p.pay_year AND pi.pay_month = p.pay_month AND pi.staff_id = p.staff_id AND pi.payment_type = p.payment_type AND pi.pay_year = :year AND pi.pay_month = :month) INNER JOIN writs w ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ") LEFT OUTER JOIN org_new_units ou ON(w.ouid = ou.ouid) LEFT OUTER JOIN cost_centers cc ON (pi.cost_center_id = cc.cost_center_id) LEFT OUTER JOIN salary_item_types sit ON (sit.salary_item_type_id = pi.salary_item_type_id AND sit.credit_topic = 1) LEFT OUTER JOIN staff s ON (pi.staff_id = s.staff_id) LEFT OUTER JOIN temp_tax_include_sum tts ON (tts.staff_id = pi.staff_id AND pi.salary_item_type_id IN(146,147,148,747)) LEFT OUTER JOIN temp_insure_include_sum tis ON (tis.staff_id = pi.staff_id AND pi.salary_item_type_id IN(9920,145,144,744)) WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where GROUP BY pi.salary_item_type_id,sit.effect_type,sit.full_title $group ORDER BY pi.pay_year,pi.pay_month" . $group . ",pi.salary_item_type_id", $param); //echo PdoDataAccess::GetLatestQueryString(); } elseif($type == 'Summary') { $MainRows = PdoDataAccess::runquery(" SELECT pi.salary_item_type_id, sit.full_title sit_title, sit.effect_type, max(pi.pay_year) pay_year, max(pi.pay_month) pay_month, sum(pay_value) pay_sum, sum(diff_pay_value * diff_value_coef) diff_pay_sum, sum(get_value) get_sum, sum(diff_get_value * diff_value_coef) diff_get_sum, sum(param2) param2, sum(diff_param2 * diff_param2_coef) diff_param2, sum(param3) param3, sum(diff_param3 * diff_param3_coef) diff_param3, sum(CASE WHEN (s.last_retired_pay IS NOT NULL AND s.last_retired_pay < '".$month_start."' ) THEN param3 END) retired_for_org_up30, sum(CASE WHEN (s.last_retired_pay IS NOT NULL AND s.last_retired_pay < '".$month_start."' ) THEN diff_param3 * diff_param3_coef END) diff_retired_for_org_up30, sum(CASE WHEN (s.last_retired_pay IS NULL OR s.last_retired_pay >= '".$month_start."' ) THEN param3 END) retired_for_org_dn30, sum(CASE WHEN (s.last_retired_pay IS NULL OR s.last_retired_pay >= '".$month_start."' ) THEN diff_param3 * diff_param3_coef END) diff_retired_for_org_dn30, sum(pi.param7) param7, sum(pi.diff_param7 * pi.diff_value_coef) diff_param7, max(s.person_type) person_type, max(pi.cost_center_id) cost_center_id, max(cc.title) cost_center_title, max(ou.ptitle) unit_title, max(s.person_type) person_type, max(w.ouid) ouid FROM payment_items pi INNER JOIN payments p ON(pi.pay_year = p.pay_year AND pi.pay_month = p.pay_month AND pi.staff_id = p.staff_id AND pi.payment_type = p.payment_type ) LEFT JOIN writs w ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ") LEFT OUTER JOIN org_new_units ou ON(w.ouid = ou.ouid) LEFT OUTER JOIN cost_centers cc ON (pi.cost_center_id = cc.cost_center_id) LEFT OUTER JOIN salary_item_types sit ON (sit.salary_item_type_id = pi.salary_item_type_id) LEFT OUTER JOIN staff s ON (pi.staff_id = s.staff_id) WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where GROUP BY pi.salary_item_type_id,sit.effect_type,sit.full_title $group ORDER BY pi.pay_year,pi.pay_month" . $group . ",pi.salary_item_type_id", $param ); } global $MainQuery; $MainQuery = PdoDataAccess::GetLatestQueryString(); //............... counting persons number ................. $dt = PdoDataAccess::runquery(" select COUNT(DISTINCT pi.staff_id) person_count $group from payment_items pi INNER JOIN payments p ON(pi.pay_year = p.pay_year AND pi.pay_month = p.pay_month AND pi.staff_id = p.staff_id AND pi.payment_type = p.payment_type) LEFT JOIN writs w ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ") LEFT OUTER JOIN staff s ON pi.staff_id=s.staff_id WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where " . ($group != "" ? " group by " . substr($group,1) : "") , $param); //echo PdoDataAccess::GetLatestQueryString(); $person_count = array(); foreach($dt as $row) { $group = ""; $group = $GroupCostCenter ? $row["cost_center_id"] . "_" : "ALL_"; $group .= $GroupPersonType ? $row["person_type"] : "ALL"; $person_count[$group] = $row['person_count']; } return $MainRows; }
$output = $sum . $output; $file = "TRANS.DAT"; $filename = "../../../HRProcess/" . $file; $fp = fopen($filename, 'w+'); fwrite($fp, $output); fclose($fp); header('Content-disposition: filename="' . $file . '"'); header('Content-type: application/file'); header('Pragma: no-cache'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); echo file_get_contents("../../../HRProcess/" . $file); die; } elseif ($_POST['RepFormat'] == 1) { manage_salary_utils::simulate_tax($_POST['pay_year'], $_POST['pay_month'], $_POST['PayType']); manage_salary_utils::simulate_bime($_POST['pay_year'], $_POST['pay_month'], $_POST['PayType']); $query = " select pa.account_no,\n\t\t\t\t\t\t pa.pay_year,\n\t\t\t\t\t\t pa.pay_month,\n\t\t\t\t\t\t ba.branch_code,\n\t\t\t\t\t\t sum(pai.pay_value + pai.diff_pay_value * pai.diff_value_coef -\n\t\t\t\t\t\t CASE WHEN pai.salary_item_type_id IN(146,147,148) THEN tts.value WHEN pai.salary_item_type_id IN(9920,145,144) THEN tis.value\n\t\t\t\t\t\t ELSE (pai.get_value + pai.diff_get_value * pai.diff_value_coef) END) amount\n\n\t\t\t\t\tfrom payment_items pai\n\t\t\t\t\t\t\t INNER JOIN salary_item_types sit\n\t\t\t\t\t\t\t\tON pai.salary_item_type_id = sit.salary_item_type_id AND sit.credit_topic = " . CREDIT_TOPIC_1 . "\n\t\t\t\t\t\t\t INNER JOIN payments pa\n\t\t\t\t\t\t\t\t ON (pai.staff_id = pa.staff_id AND\n\t\t\t\t\t\t\t\t\t\tpai.pay_year = pa.pay_year AND\n\t\t\t\t\t\t\t\t\t\tpai.pay_month = pa.pay_month AND\n\t\t\t\t\t\t\t\t\t\tpai.payment_type = pa.payment_type)\n\t\t\t\t\t\t\t INNER JOIN banks ba\n\t\t\t\t\t\t\t\t ON (ba.bank_id = pa.bank_id) \n\t\t\t\t\t\t\t LEFT OUTER JOIN temp_tax_include_sum tts\n\t\t\t\t\t\t\t\t ON (tts.staff_id = pai.staff_id AND pai.salary_item_type_id IN(146,147,148))\n\t\t\t\t\t\t\t LEFT OUTER JOIN temp_insure_include_sum tis\n\t\t\t\t\t\t\t\t ON (tis.staff_id = pai.staff_id AND pai.salary_item_type_id IN(9920,145,144))\n\t\t\t\t\t\t\t INNER JOIN staff s \n\t\t\t\t\t\t\t\t ON pai.staff_id = s.staff_id\n\t\t\t\t\t\t\t LEFT JOIN writs w\n\t\t\t\t\t\t\t\t ON s.last_writ_id = w.writ_id AND s.last_writ_ver = w.writ_ver\n\n\t\t\t\t\twhere pai.pay_year = " . $_POST['pay_year'] . " AND\n\t\t\t\t\t\t pai.pay_month = " . $_POST['pay_month'] . " AND\n\t\t\t\t\t\t pai.payment_type = " . $_POST['PayType']; $query .= $staffID > 0 ? " AND pai.staff_id=" . $staffID : ""; $query .= $WhereCost != "" ? " AND pai.cost_center_id in (" . $WhereCost . ") " : ""; $query .= $WhereEmpstate != "" ? " AND w.emp_state in (" . $WhereEmpstate . ") " : ""; $query .= $WherePT != "" ? " AND s.person_type in (" . $WherePT . ") " : ""; $query .= $WhereBT != "" ? " AND pa.bank_id in (" . $WhereBT . ") " : ""; $query .= "\tgroup by pa.account_no,\n\t\t\t\t\t\t\t pa.pay_year,\n\t\t\t\t\t\t\t pa.pay_month,\n\t\t\t\t\t\t\t ba.branch_code "; $dt = PdoDataAccess::runquery($query); $year = $_POST['pay_year'] - 1300; $list_date = $year . str_pad($_POST['pay_month'], 2, "0", STR_PAD_LEFT) . '29'; $output = ""; $sum = 0; for ($i = 0; $i < count($dt); $i++) { if (!empty($dt[$i]['branch_code'])) {