public static function simulate_bime($pay_year, $pay_month, $payment_type = NULL)
{
$e_date = "31-" . $pay_month . "-" . $pay_year;
$end_month_date = DateModules::shamsi_to_miladi($e_date);
$s_date = "1-" . $pay_month . "-" . $pay_year;
$start_month_date = DateModules::shamsi_to_miladi($s_date);
$worker_month_day = 30;
$actual_month_day = DateModules::get_month_dayno($pay_year, $pay_month);
//...................................
PdoDataAccess::runquery("DROP TABLE IF EXISTS temp_insure_include_sum ");
PdoDataAccess::runquery(" DROP TABLE IF EXISTS temp_limit_staff ");
PdoDataAccess::runquery(" CREATE TEMPORARY TABLE temp_limit_staff AS\r\n\t\t\t\t\t\t\t\tSELECT DISTINCT s.staff_id , s.person_type\r\n\t\t\t\t\t\t\t\tFROM staff s\r\n\t\t\t\t\t\t\t\t\tINNER JOIN staff_include_history si\r\n\t\t\t\t\t\t\t\t\t\tON (s.staff_id = si.staff_id AND si.start_date <= ('{$end_month_date}') AND (si.end_date IS NULL OR si.end_date >= ('{$end_month_date}'))) \r\n\t\t\t\t\t\t\t\tWHERE si.insure_include = 1 ");
PdoDataAccess::runquery("ALTER TABLE temp_limit_staff ADD INDEX (staff_id)");
PdoDataAccess::runquery(" CREATE TEMPORARY table temp_insure_include_sum AS\r\n\t\t\t\t\t\t\t\tSELECT s.staff_id ,\r\n\t\t\t\t\t\t\t\t\t\t0 value,\r\n\t\t\t\t\t\t\t\t\t\tsum(pi.pay_value) + CASE WHEN sum(pi.diff_pay_value * pi.diff_value_coef) < 0 THEN 0 ELSE sum(pi.diff_pay_value * pi.diff_value_coef) END param1 ,\r\n\t\t\t\t\t\t\t\t\t\t0 param2 ,\r\n\t\t\t\t\t\t\t\t\t\t0 param3 , \r\n\t\t\t\t\t\t\t\t\t\tCASE WHEN s.person_type = " . HR_WORKER . " THEN {$worker_month_day} ELSE {$actual_month_day} END month_days\r\n\t\t\t\t\t\t\t\tFROM payment_items pi\r\n\t\t\t\t\t\t\t\tINNER JOIN salary_item_types sit\r\n\t\t\t\t\t\t\t\t\tON (pi.salary_item_type_id = sit.salary_item_type_id AND \r\n\t\t\t\t\t\t\t\t\t\tsit.credit_topic = " . CREDIT_TOPIC_1 . " AND \r\n\t\t\t\t\t\t\t\t\t\tsit.insure_include = 1)\r\n\t\t\t\t\t\t\t\tINNER JOIN temp_limit_staff s\r\n\t\t\t\t\t\t\t\t\tON (s.staff_id = pi.staff_id)\r\n\t\t\t\t\t\t\t\tWHERE pi.pay_year = ({$pay_year}) AND \r\n\t\t\t\t\t\t\t\t\tpi.pay_month = ({$pay_month}) AND \r\n\t\t\t\t\t\t\t\t\tpi.payment_type = 1\r\n\t\t\t\t\t\t\t\t\t{$payment_type_where}\r\n\t\t\t\t\t\t\t\tGROUP BY\r\n\t\t\t\t\t\t\t\tstaff_id; ");
PdoDataAccess::runquery("ALTER TABLE temp_insure_include_sum ADD INDEX (staff_id)");
$max_daily_salary_insure_include = manage_salary_params::get_salaryParam_value("", 100, SPT_MAX_DAILY_SALARY_INSURE_INCLUDE, $start_month_date);
PdoDataAccess::runquery(" UPDATE temp_insure_include_sum\r\n\t\t\t\t\t\t\t SET param1 = {$max_daily_salary_insure_include} * {$worker_month_day} \r\n\t\t\t\t\t\t\t WHERE param1 > {$max_daily_salary_insure_include} * {$worker_month_day} ");
//استخراج درصد بیمه بیکاری سهم کارفرما
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_UNEMPLOYMENT_INSURANCE_VALUE);
$unemployment_insurance_value = $res[0]['value'];
//استخراج درصد بیمه سهم شخص
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_SOCIAL_SUPPLY_INSURE_PERSON_VALUE);
$person_value = $res[0]['value'];
//استخراج درصد بیمه سهم کارفرما
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_SOCIAL_SUPPLY_INSURE_EMPLOYER_VALUE);
$employmer_value = $res[0]['value'];
PdoDataAccess::runquery(" UPDATE temp_insure_include_sum\r\n\t\t\t\t\t\t\t\tSET value = param1 * {$person_value} ,\r\n\t\t\t\t\t\t\t\t\tparam2 = param1 * {$employmer_value} ,\r\n\t\t\t\t\t\t\t\t\tparam3 = param1 * {$unemployment_insurance_value} \r\n\t\t\t\t\t\t\t\tWHERE (1=1)");
}
