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)"); }