$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);
$output = "";
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;
}
$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'])) {
$brno = str_pad($dt[$i]['branch_code'], 5, "0", STR_PAD_LEFT);
