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 = "01/" . $pay_month . "/" . $pay_year; $start_month_date = DateModules::shamsi_to_miladi($s_date, '-'); $worker_month_day = 30; $actual_month_day = DateModules::DaysOfMonth($pay_year, $pay_month); if ($payment_type) { $payment_type_where = ' AND pi.payment_type = ' . $payment_type; } //................................... PdoDataAccess::runquery("DROP TABLE IF EXISTS temp_insure_include_sum "); PdoDataAccess::runquery(" DROP TABLE IF EXISTS temp_limit_staff "); $w1 = ""; if ($pay_year >= 1393 && $pay_month > 2) { $w1 = " OR si.end_date ='0000-00-00'"; } PdoDataAccess::runquery(" CREATE 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 \r\n\t\t\t\t\t\t\t\t\t\t (si.end_date IS NULL {$w1} 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 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 in (" . HR_WORKER . " , " . HR_CONTRACT . ") 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)"); }
function ProcessArrearPayment() { $payArrCalcObj = new manage_arrear_pay_calculation(); $payArrCalcObj->__YEAR = $_POST["pay_year"]; $payArrCalcObj->__MONTH = 12; $start_date = $_POST["pay_year"] . "/01/01"; $end_date = $_POST["pay_year"] . "/12/" . DateModules::DaysOfMonth($payArrCalcObj->__YEAR, 12); $payArrCalcObj->__CALC_NORMALIZE_TAX = isset($_POST['tax_normalize']) ? "1" : "0"; $payArrCalcObj->__START_NORMALIZE_TAX_MONTH = 1; $payArrCalcObj->__START_NORMALIZE_TAX_YEAR = $_POST["pay_year"]; $payArrCalcObj->__BACKPAY_BEGIN_FROM = 1; $payArrCalcObj->month_start = DateModules::shamsi_to_miladi($start_date); $payArrCalcObj->month_end = DateModules::shamsi_to_miladi($end_date); $payArrCalcObj->__MSG = $_POST["message"]; // <editor-fold defaultstate="collapsed" desc="Create Where" > $where = "1=1"; $whereParam = array(); if (!empty($_POST["person_type"])) { $where .= " AND p.person_type=:ptype"; $whereParam[":ptype"] = $_POST["person_type"]; } if (!empty($_POST["from_staff_id"])) { $where .= " AND s.staff_id >= :fsid"; $whereParam[":fsid"] = $_POST["from_staff_id"]; } if (!empty($_POST["to_staff_id"])) { $where .= " AND s.staff_id <= :tsid"; $whereParam[":tsid"] = $_POST["to_staff_id"]; } if (!empty($_POST["from_cost_center_id"])) { $where .= " AND w.cost_center_id >= :fccid"; $whereParam[":fccid"] = $_POST["from_cost_center_id"]; } if (!empty($_POST["to_cost_center_id"])) { $where .= " AND w.cost_center_id <= :tccid"; $whereParam[":tccid"] = $_POST["to_cost_center_id"]; } if (!isset($_POST["ouid"])) { $result = QueryHelper::MK_org_units($_POST["ouid"], true); $where .= " AND " . $result["where"]; $whereParams = array_merge($whereParam, $result["param"]); } // </editor-fold> $payArrCalcObj->__WHERE = $where; $payArrCalcObj->__WHEREPARAM = $whereParam; $res = $payArrCalcObj->run_back(); if (!$res) { echo Response::createObjectiveResponse(false, ExceptionHandler::popExceptionDescription()); die; } else { echo Response::createObjectiveResponse(true, $payArrCalcObj->success_counter . "_" . $payArrCalcObj->fail_counter); die; } }
private function init() { parent::runquery('TRUNCATE delete_payment_staff;'); parent::runquery('DROP TABLE IF EXISTS temp_cancel_limit_staff;'); parent::runquery('CREATE TABLE temp_cancel_limit_staff AS SELECT DISTINCT s.staff_id , s.PersonID FROM staff s INNER JOIN writs w ON(s.last_writ_id = w.writ_id AND s.last_writ_ver = w.writ_ver AND s.staff_id = w.staff_id ) LEFT OUTER JOIN payment_items pit ON(s.staff_id = pit.staff_id AND ' . $this->where_clause . ') WHERE ' . $this->staff_where . ' AND ' . '((pit.staff_id IS NOT NULL AND ' . $this->where_clause . ') OR ' . '(' . $this->writ_where . '))'); parent::runquery('ALTER TABLE temp_cancel_limit_staff ADD INDEX(staff_id);'); $this->end_date = DateModules::shamsi_to_miladi($this->year . '/' . $this->month . '/' . DateModules::DaysOfMonth($this->year, $this->month)); $this->start_date = DateModules::shamsi_to_miladi($this->year . '/' . $this->month . '/01'); $this->success_count = array(); $this->unsuccess_count = 0; }
function onCalcField(&$rec) { if(empty($rec['work_sheet'])) { $DT = PdoDataAccess::runquery('SELECT MAX(DISTINCT pai.param4) work_days, s.person_type FROM payment_items pai INNER JOIN salary_item_types sit ON (pai.salary_item_type_id = sit.salary_item_type_id) INNER JOIN staff s ON (pai.staff_id = s.staff_id) WHERE pai.pay_year = '.$_POST['pay_year'].' AND pai.pay_month = '.$_POST['pay_month'].' AND pai.payment_type = '.$_POST['PayType'].' AND pai.staff_id = '.$rec['staff_id'].' AND sit.compute_place = '.SALARY_ITEM_COMPUTE_PLACE_WRIT.' GROUP BY s.staff_id') ; if( $DT[0]['work_days'] > 1 ) $DT[0]['work_days'] = 1 ; $work_days = $DT[0]['work_days']; $person_type = $DT[0]['person_type']; $work_days *= DateModules::DaysOfMonth($rec['pay_month'],$rec['pay_year']); $rec['work_sheet'] = $work_days; } if(!empty($rec['work_sheet'])) $rec['work_sheet'] = round($rec['work_sheet']) ; if ($rec['person_type'] == HR_WORKER) { $rec['daily_fee'] = $rec['monthly_fee'] / $rec['work_sheet']; } else { $rec['daily_fee'] = $rec['monthly_fee'] / DateModules::DaysOfMonth($rec['pay_month'],$rec['pay_year']); } $rec['monthly_premium'] = $rec['monthly_insure_include'] - $rec['monthly_fee']; $rec['other_gets'] = $rec['gets'] - $rec['worker_insure_include']; return true; }
function process_tax_normalize($staffID, $PayVal, $PayYear, $PayMonth) { /*اين فرد مشمول ماليات نمي باشد*/ if ($PayMonth > 6 && $PayMonth < 11) { $SPayMonth = 7; $EPayMonth = 10; } elseif ($PayMonth > 10) { $SPayMonth = 11; $EPayMonth = 12; } elseif ($PayMonth >= 1 && $PayMonth < 4) { $SPayMonth = 1; $EPayMonth = 3; } elseif ($PayMonth > 3 && $PayMonth < 7) { $SPayMonth = 4; $EPayMonth = 6; } $SDate = $PayYear . "/" . $SPayMonth . "/01"; if ($EPayMonth < 7) { $endDay = "31"; } elseif ($EPayMonth > 6 && $EPayMonth < 12) { $endDay = "30 "; } elseif ($EPayMonth == 12) { $endDay = "29"; } $EDate = $PayYear . "/" . $EPayMonth . "/" . $endDay; $EDate = DateModules::shamsi_to_miladi($EDate); $SDate = DateModules::shamsi_to_miladi($SDate); $qry = " select tax_include\n\t\t\t\t\tfrom staff_include_history\n\t\t\t\t\t\twhere staff_id = " . $staffID . " and start_date <= '" . $EDate . "' AND\n\t\t\t\t\t\t\t (end_date IS NULL OR end_date = '0000-00-00' OR\n\t\t\t\t\t\t\t end_date >= '" . $EDate . "' OR end_date > '" . $SDate . "' ) "; $res = PdoDataAccess::runquery($qry); if ($res[0]['tax_include'] == 0) { return; } //.......................................... $qry = "\n\t\t\tSELECT\n\t\t\t\t pit.staff_id staff_id,\n\t\t\t\t\tSUM(pit2.get_value + if( pit3.get_value IS NULL , 0 , pit3.get_value) + \n\t\t\t\t\t (pit2.diff_get_value * pit2.diff_value_coef) + \n\t\t\t\t\t\tif(pit3.diff_get_value is null , 0 , (pit3.diff_get_value * pit3.diff_value_coef)) ) sum_tax,\n\t\t\t\t\tSUM(pit.param1 + pit.diff_param1) sum_tax_include\n\t\t\t\t\t\n\t\t\tFROM payment_items pit\t\t\t\t\t\t\t\n\t\t\t\t\t LEFT OUTER JOIN payment_items pit2\n\t\t\t\t\t\t\tON(pit2.staff_id = pit.staff_id AND \n\t\t\t\t\t\t\t pit2.pay_year = pit.pay_year AND\n\t\t\t\t\t\t\t pit2.pay_month = pit.pay_month AND\n\t\t\t\t\t\t\t pit2.payment_type != 3 AND\n\t\t\t\t\t\t\t pit2.salary_item_type_id = pit.salary_item_type_id)\n\t\t\t\t\t\t\t \n\t\t\t\t\t LEFT OUTER JOIN payment_items pit3\n\t\t\t\t\t\t\tON(pit3.staff_id = pit.staff_id AND \n\t\t\t\t\t\t\t pit3.pay_year = pit.pay_year AND\n\t\t\t\t\t\t\t pit3.pay_month = pit.pay_month AND\n\t\t\t\t\t\t\t pit3.payment_type = 3 AND if( pit3.pay_year = 1393 , pit3.pay_month > 1 , (1=1) ) AND\n\t\t\t\t\t\t\t pit3.salary_item_type_id = pit.salary_item_type_id)\n\n\t\t\tWHERE pit.pay_year >= " . $PayYear . " AND\n\t\t\t\t pit.pay_month >= " . $SPayMonth . " AND pit.pay_month <= " . $EPayMonth . " AND\n\t\t\t\t pit.salary_item_type_id IN(" . SIT_PROFESSOR_TAX . ",\n\t\t\t\t\t\t\t\t\t\t\t " . SIT_STAFF_TAX . ",\n\t\t\t\t\t\t\t\t\t\t\t " . SIT_WORKER_TAX . ",\n\t\t\t\t\t\t\t\t\t\t\t " . SIT5_STAFF_TAX . ") AND\n\t \t\t pit.staff_id = " . $staffID; $taxRes = PdoDataAccess::runquery($qry); //......................................................................... $qry2 = "\n\t\t\t\tSELECT sth.staff_id,\n\t\t\t\t\t\tsth.start_date,\n\t\t\t\t\t\tsth.end_date,\n\t\t\t\t\t\tsth.tax_table_type_id,\n\t\t\t\t\t\tsth.payed_tax_value\n\n\t\t\t\tFROM staff_tax_history sth \n\n\t\t\t\tWHERE end_date IS NULL OR end_date = '0000-00-00' OR end_date > '" . $SDate . "' AND\n\t\t\t\t\t start_date < '" . $EDate . "' AND sth.staff_id = " . $staffID . " \n\t\t\t\tORDER BY sth.staff_id,sth.start_date\t\t\t\n\t\t\t\t\n\t\t\t\t"; $taxHisRes = PdoDataAccess::runquery($qry2); $tax_table_type_id = $taxHisRes[0]['tax_table_type_id']; exe_taxtable_sql($PayYear, $PayMonth, $taxTable); //......................................................................... /* تعدیل مالیات با توجه به بازه مربوط به آن ترم در نظر گرفته می شود */ $year_avg_tax_include = ($taxRes[0]['sum_tax_include'] + $PayVal + $taxHisRes[0]['payed_tax_value']) / ($EPayMonth - $SPayMonth + 1); $sum_normalized_tax = $tax_table_type_id = 0; //متغيري جهت نگهداري ماليات تعديل شده براي cur_staff در تمام طول سال reset($this->tax_tables); for ($m = $SPayMonth; $m <= $EPayMonth; $m++) { $begin_month_date = DateModules::shamsi_to_miladi($this->__YEAR . "/" . $m . "/1"); $end_month_date = DateModules::shamsi_to_miladi($this->__YEAR . "/" . $m . "/" . DateModules::DaysOfMonth($this->__YEAR, $m)); for ($j = 0; $j < count($taxHisRes); $j++) { if ($taxHisRes[$j]['end_date'] != null && $taxHisRes[$j]['end_date'] != '0000-00-00' && DateModules::CompareDate($taxHisRes[$j]['end_date'], $begin_month_date) == -1) { continue; } if (DateModules::CompareDate($taxHisRes[$j]['start_date'], $end_month_date) == 1) { break; } $tax_table_type_id = $taxHisRes[$j]['tax_table_type_id']; break; } if (!isset($tax_table_type_id) || $tax_table_type_id == NULL) { continue; } if (!key_exists($tax_table_type_id, $taxTable)) { return; } foreach ($taxTable[$tax_table_type_id] as $tax_table_row) { $pay_mid_month_date = DateModules::shamsi_to_miladi($this->__YEAR . "/" . $m . "/15"); if (DateModules::CompareDate($pay_mid_month_date, $tax_table_row['from_date']) != -1 && DateModules::CompareDate($pay_mid_month_date, $tax_table_row['to_date']) != 1) { if ($year_avg_tax_include >= $tax_table_row['from_value'] && $year_avg_tax_include <= $tax_table_row['to_value']) { $sum_normalized_tax += ($year_avg_tax_include - $tax_table_row['from_value']) * $tax_table_row['coeficient']; } else { if ($year_avg_tax_include > $tax_table_row['to_value']) { $sum_normalized_tax += ($tax_table_row['to_value'] - $tax_table_row['from_value']) * $tax_table_row['coeficient']; } } } } } $normalized_tax = $sum_normalized_tax - $taxRes[0]['sum_tax']; if ($normalized_tax < 0) { $normalized_tax = 0; } //انتصاب ماليات تعديل شده به payment_items $PaymentItems = array('get_value' => $normalized_tax, 'param1' => $PayVal, 'param2' => $sum_normalized_tax, 'param3' => $taxRes[0]['sum_tax'] + $normalized_tax, 'param5' => $tax_table_type_id); return $PaymentItems; }
function GetAllHolidays() { $where = ""; $whereParam = array(); if (!empty($_REQUEST["Year"])) { $year = $_REQUEST["Year"]; $StartDate = DateModules::shamsi_to_miladi($year . "-01-01", "-"); $EndDate = DateModules::shamsi_to_miladi($year . "-12-" . DateModules::DaysOfMonth($year, 12), "-"); $where .= " AND TheDate between ? AND ?"; $whereParam[] = $StartDate; $whereParam[] = $EndDate; } $temp = ATN_holidays::Get($where . dataReader::makeOrder(), $whereParam); echo dataReader::getJsonData($temp->fetchAll(), $temp->rowCount(), $_GET["callback"]); die; }
public function run_back() { //در اين تابع فرض براين است که سال مالي با سال شمسي مطابقت دارد $this->empty_corrective_tables(); $this->last_month = $this->__MONTH; $this->last_month_end = $this->month_end; $this->first_month_start = $this->month_start; $this->backpay_recurrence = 0; //محاسبه حقوق ماههاي قبلي for ($i = $this->__BACKPAY_BEGIN_FROM; $i <= $this->last_month; $i++) { $this->backpay_recurrence++; //$this->backpay = true; // $this->backpay = false; $this->month_start = DateModules::shamsi_to_miladi($this->__YEAR . "/" . $i . "/01"); $this->month_end = DateModules::shamsi_to_miladi($this->__YEAR . "/" . $i . "/" . DateModules::DaysOfMonth($this->__YEAR, $i)); $this->__MONTH = $i; $this->__MONTH_LENGTH = DateModules::DaysOfMonth($this->__YEAR, $i); if (!$this->run()) { return false; } } //............................................................................... /* //محاسبه حقوق همين ماه $this->backpay_recurrence++; $this->backpay = false; $this->month_start = DateModules::shamsi_to_miladi($this->__YEAR."/".$this->last_month."/01") ; $this->month_end = DateModules::shamsi_to_miladi($this->__YEAR."/".$this->last_month."/".DateModules::DaysOfMonth($this->__YEAR,$this->last_month)) ; $this->__MONTH = $this->last_month; $this->__MONTH_LENGTH = DateModules::DaysOfMonth($this->__YEAR,$this->last_month); $this->run();*/ return true; }
static function GetRemainder($subtract_type = "", $subtract_id = "", $PersonID = "", $justCreateTempTable = false, $last_month = "", $Year = "") { $where = ""; $param = array(); $stypes = "'LOAN','FIX_FRACTION','FIX_BENEFIT'"; if ($PersonID != "") { $where .= " AND s.PersonID=:pid"; $param[":pid"] = $PersonID; } if ($subtract_id != "") { $where .= " AND s.subtract_id=:subid"; $param[":subid"] = $subtract_id; } if ($subtract_type != "") { $where .= " AND s.subtract_type=:stype"; $param[":stype"] = $subtract_type; /*switch ($subtract_type) { case SUBTRACT_TYPE_LOAN : $stypes = "'LOAN'"; break; case SUBTRACT_TYPE_FIX_FRACTION : $stypes = "'FIX_FRACTION'"; break; case SUBTRACT_TYPE_FIX_BENEFIT : $stypes = "'FIX_BENEFIT'"; break; }*/ } /*if($where == "" && $justCreateTempTable == true ) { $JoinClause = " limit_staff ls inner join payment_items p on ls.staff_id = p.staff_id inner join person_subtracts s on(p.param2=s.subtract_id) " ; $JoinClause2 = "" ; } else { */ $JoinClause = " payment_items p join person_subtracts s on(p.param2=s.subtract_id) \n\t\t\t\t\t\t\t\t\t\t\t inner join staff st\n on p.staff_id = st.staff_id \n\t\t\t\t\t\t\t\t\t\t inner join persons per \n\t\t\t\t\t\t\t\t\t\t\t\t on st.personid = per.personid and st.person_type = per.person_type "; $JoinClause2 = " "; /*}*/ //parent::runquery("drop table if exists tmp_SubtractReceiptSummary"); //parent::runquery("drop table if exists tmp_SubtractRemainders"); parent::runquery('TRUNCATE tmp_SubtractReceiptSummary'); parent::runquery('TRUNCATE tmp_SubtractRemainders'); if ($last_month > 0) { $LM = (int) $last_month - 1; $dayNO = DateModules::DaysOfMonth($Year, $LM); $edt = DateModules::shamsi_to_miladi($Year . "/" . $LM . "/" . $dayNO); $WhrMonth = " AND if(p.pay_year = " . $Year . " , p.pay_month < " . $last_month . " , (1=1)) AND \n\t\t\t\t\t\t\t\t( s.end_date IS NULL or s.end_date ='0000-00-00' or s.end_date > '" . $edt . "' ) "; } else { $WhrMonth = " "; } parent::runquery("/*create table tmp_SubtractReceiptSummary as */\n\t\t\t\t\t\t insert into tmp_SubtractReceiptSummary \n\t\t\t\t\t\t\tselect s.*,sum(get_value) receipt \n\t\t\t\t\n\t\t\t\t\t\t\tfrom {$JoinClause}\n\t\t\t\t\n\t\t\t\t\t\t\twhere param1 in({$stypes}) {$WhrMonth} AND s.IsFinished=0 AND st.person_type in (1,2,3,5) {$where}\n\t\t\t\t\t\t\tgroup by s.subtract_id", $param); /* echo PdoDataAccess::GetLatestQueryString() ; die() ;*/ // parent::runquery("ALTER TABLE tmp_SubtractReceiptSummary ADD INDEX Index_1(subtract_id)"); $query = "\n\t\t\t\tselect subtract_id,first_value-ifnull(sum(receipt),0)-ifnull(sum(flow),0) remainder, ifnull(sum(receipt),0) receipt\n\t\t\t\tfrom\n\t\t\t\t\t(\n\t\t\t\t\tselect subtract_id,first_value,0 receipt,0 flow\n\t\t\t\t\tfrom person_subtracts s {$JoinClause2}\n\t\t\t\t\twhere IsFinished=0 and s.subtract_type = 1 {$where}\n\n\t\t\t\t\tunion All\n\n\t\t\t\t\tselect sf.subtract_id,0,0 receipt,sum(cast(flow_coaf as Decimal(2))*amount) flow \n\t\t\t\t\tfrom person_subtract_flows sf join person_subtracts s on(sf.subtract_id=s.subtract_id AND IsFinished=0 AND s.subtract_type = 1 ) {$JoinClause2}\n\t\t\t\t\twhere flow_type=3 {$where}\n\t\t\t\t\tgroup by sf.subtract_id\n\n\t\t\t\t\tunion All\n\n\t\t\t\t\tselect subtract_id,0,receipt,0 from tmp_SubtractReceiptSummary s where 1=1 {$where}\n\n\t\t\t\t\t)t\n\t\t\t\tgroup by subtract_id"; /*if($_SESSION['UserID'] == 'jafarkhani' || $_SESSION['UserID'] == 'bmahdipour' ) { echo $query ; die() ; }*/ if ($justCreateTempTable) { parent::runquery("/*create table tmp_SubtractRemainders as*/ insert into tmp_SubtractRemainders " . $query, $param); /*echo "**************".PdoDataAccess::GetLatestQueryString() ; die();*/ //parent::runquery("ALTER TABLE tmp_SubtractRemainders ADD INDEX Index_1(subtract_id)"); return; } $dt = parent::runquery($query, $param); if ($subtract_id != "") { return $dt[0]["remainder"]; } return $dt; }
function ShowReport($admin){ if($_POST["FromDate"] == "") { $StartDate = DateModules::shamsi_to_miladi($_POST["year"] . "-" . $_POST["month"] . "-01", "-"); $EndDate = DateModules::shamsi_to_miladi($_POST["year"] . "-" . $_POST["month"] ."-" . DateModules::DaysOfMonth($_POST["year"] ,$_POST["month"]), "-"); } else { $StartDate = DateModules::shamsi_to_miladi($_POST["FromDate"], "-"); $EndDate = DateModules::shamsi_to_miladi($_POST["ToDate"], "-"); } $holidays = ATN_holidays::Get(" AND TheDate between ? AND ? order by TheDate", array($StartDate, $EndDate)); $holidayRecord = $holidays->fetch(); $PersonID = $_SESSION["USER"]["PersonID"]; $PersonID = !empty($_POST["PersonID"]) ? $_POST["PersonID"] : $PersonID; $query = "select * from ( select 'normal' RecordType,'' ReqType, TrafficDate,TrafficTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_traffic t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND TrafficDate between FromDate AND ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.IsActive='YES' AND t.PersonID=:p AND TrafficDate>= :sd AND TrafficDate <= :ed union All select 'start' RecordType,t.ReqType, t.FromDate,StartTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_requests t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND t.FromDate between ps.FromDate AND ps.ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.PersonID=:p AND t.ToDate is null AND ReqStatus=2 AND t.FromDate>= :sd union all select 'end' RecordType,t.ReqType, t.FromDate,EndTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_requests t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND t.FromDate between ps.FromDate AND ps.ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.PersonID=:p AND t.ToDate is null AND ReqStatus=2 AND t.FromDate>= :sd AND EndTime is not null )t2 order by TrafficDate,TrafficTime"; $dt = PdoDataAccess::runquery($query, array(":p" => $PersonID, ":sd" => $StartDate, ":ed" => $EndDate)); /*if($_SESSION["USER"]["UserName"] == "admin") { print_r(ExceptionHandler::PopAllExceptions()); echo PdoDataAccess::GetLatestQueryString(); }*/ //print_r($dt); //........................ create days array .................. $index = 0; $returnArr = array(); while($StartDate <= $EndDate) { if($index < count($dt) && $StartDate == $dt[$index]["TrafficDate"]) { while($index < count($dt) && $StartDate == $dt[$index]["TrafficDate"]) $returnArr[] = $dt[$index++]; $StartDate = DateModules::AddToGDate($StartDate, 1); continue; } $shiftRecord = ATN_PersonShifts::GetShiftOfDate($PersonID, $StartDate); $returnArr[] = array( "RecordType" => "normal", "TrafficID" => "", "TrafficDate" => $StartDate , "ShiftTitle" => $shiftRecord["ShiftTitle"], "FromTime" => $shiftRecord["FromTime"], "ToTime" => $shiftRecord["ToTime"], "ExceptFromTime" => $shiftRecord["ExceptFromTime"], "ExceptToTime" => $shiftRecord["ExceptToTime"], "TrafficTime" => ""); $StartDate = DateModules::AddToGDate($StartDate, 1); } //------------ holidays ------------------ for($i=0; $i<count($returnArr); $i++) { $holiday = false; $holidayTitle = "تعطیل"; if(FridayIsHoliday && DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") == "5") $holiday = true; if(ThursdayIsHoliday && DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") == "4") $holiday = true; if($holidayRecord && $holidayRecord["TheDate"] == $returnArr[$i]["TrafficDate"]) { $holidayTitle .= $holidayRecord["details"] != "" ? "(" . $holidayRecord["details"] . ")" : ""; $holiday = true; $holidayRecord = $holidays->fetch(); } $returnArr[$i]["holiday"] = $holiday; $returnArr[$i]["holidayTitle"] = $holidayTitle; } //........................................................... function ShowTime($arr){ if($arr[0] == "00" && $arr[1] == "00") return ""; return $arr[0] . ":" . $arr[1]; } $returnStr = ""; $SUM = array( "absence" => 0, "attend"=> 0, "firstAbsence" => 0, "lastAbsence" => 0, "extra" => 0, "Off" => 0, "mission" => 0, "DailyOff_1" => 0, "DailyOff_2" => 0, "DailyOff_3" => 0, "DailyMission" => 0, "DailyAbsence" => 0 ); for($i=0; $i < count($returnArr); $i++) { if(!$returnArr[$i]["holiday"]) { //........... Daily off and mission ................... $requests = PdoDataAccess::runquery(" select t.*, InfoDesc OffTypeDesc from ATN_requests t left join BaseInfo on(TypeID=20 AND InfoID=OffType) where ReqStatus=2 AND PersonID=:p AND FromDate <= :td AND if(ToDate is not null, ToDate >= :td, 1=1) order by ToDate desc,StartTime asc ", array( ":p" => $PersonID, ":td" => $returnArr[$i]["TrafficDate"] )); if(count($requests) > 0) { if($requests[0]["ReqType"] == "DayOFF") { $returnStr .= "<td>" . DateModules::$JWeekDays[ DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") ] . "</td> <td>" . DateModules::miladi_to_shamsi($returnArr[$i]["TrafficDate"]) . "</td> <td colspan=8> مرخصی " . $requests[0]["OffTypeDesc"] . "<td></tr>"; $SUM["DailyOff_" . $requests[0]["OffType"] ]++; $currentDay = $returnArr[$i]["TrafficDate"]; while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) $i++; $i--; continue; } if($requests[0]["ReqType"] == "DayMISSION") { $returnStr .= "<td>" . DateModules::$JWeekDays[ DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") ] . "</td> <td>" . DateModules::miladi_to_shamsi($returnArr[$i]["TrafficDate"]) . "</td> <td colspan=8> ماموریت " . $requests[0]["MissionSubject"] . "<td></tr>"; $SUM["DailyMission"]++; $currentDay = $returnArr[$i]["TrafficDate"]; while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) $i++; $i--; continue; } } } //.................................................... if( DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "l") == "Thursday") { $returnArr[$i]["FromTime"] = $returnArr[$i]["ExceptFromTime"]; $returnArr[$i]["ToTime"] = $returnArr[$i]["ExceptToTime"]; } //.................................................... $returnStr .= "<tr> <td>" . DateModules::$JWeekDays[ DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") ] . "</td>"; if($admin) $returnStr .= "<td><a class=link onclick=TraceTrafficObj.TrafficList('" . $returnArr[$i]["TrafficDate"] . "')>" . DateModules::miladi_to_shamsi($returnArr[$i]["TrafficDate"]) . "</a></td>"; else $returnStr .= "<td>" . DateModules::miladi_to_shamsi($returnArr[$i]["TrafficDate"]) . "</td>"; $returnStr .= "<td>" . ($returnArr[$i]["holiday"] ? $returnArr[$i]["holidayTitle"] : $returnArr[$i]["ShiftTitle"]) . "</td> <td>"; $firstAbsence = 0; $Off = 0; $mission = 0; $index = 1; $totalAttend = 0; $currentDay = $returnArr[$i]["TrafficDate"]; $startOff = 0; $endOff = 0; $extra = 0; if($returnArr[$i]["TrafficTime"] != "") { if(strtotime($returnArr[$i]["TrafficTime"]) > strtotime($returnArr[$i]["FromTime"])) $firstAbsence = strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i]["FromTime"]); else { if(strtotime($returnArr[$i+1]["TrafficTime"]) < strtotime($returnArr[$i]["FromTime"])) $extra += strtotime($returnArr[$i+1]["TrafficTime"]) - strtotime($returnArr[$i]["TrafficTime"]); else $extra += strtotime($returnArr[$i]["FromTime"]) - strtotime($returnArr[$i]["TrafficTime"]); } } while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) { //.................................................... if( DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "l") == "Thursday") { $returnArr[$i]["FromTime"] = $returnArr[$i]["ExceptFromTime"]; $returnArr[$i]["ToTime"] = $returnArr[$i]["ExceptToTime"]; } //.................................................... $returnStr .= substr($returnArr[$i]["TrafficTime"],0,5); $returnStr .= $index % 2 == 0 ? "<br>" : " - "; if($index % 2 == 0) { $totalAttend += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); if(strtotime($returnArr[$i]["TrafficTime"]) > strtotime($returnArr[$i]["ToTime"])) { if(strtotime($returnArr[$i-1]["TrafficTime"]) > strtotime($returnArr[$i]["ToTime"])) $extra += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); else $extra += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["ToTime"]); } } if($returnArr[$i]["RecordType"] != "normal") { if($i>0 && $returnArr[$i-1]["TrafficDate"] == $currentDay && $returnArr[$i]["RecordType"] == "start") { if($i == 0 || $returnArr[$i-1]["TrafficDate"] != $currentDay) $startDiff = 0; else $startDiff = strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); if($startDiff > Valid_Traffic_diff) $startOff = strtotime($returnArr[$i]["TrafficTime"]) - Valid_Traffic_diff; else $startOff = strtotime($returnArr[$i-1]["TrafficTime"]); } if( ($i==0 || $returnArr[$i-1]["TrafficDate"] != $currentDay) && $returnArr[$i]["RecordType"] == "start") { $startOff = strtotime($returnArr[$i]["TrafficTime"]); } if($returnArr[$i]["RecordType"] == "end") { if($i == count($returnArr)-1 || $returnArr[$i+1]["TrafficDate"] != $currentDay) $endDiff = 0; else $endDiff = strtotime($returnArr[$i+1]["TrafficTime"]) - strtotime($returnArr[$i]["TrafficTime"]); if($endDiff > Valid_Traffic_diff) $endOff = strtotime($returnArr[$i]["TrafficTime"]) - Valid_Traffic_diff; else $endOff = strtotime($returnArr[$i]["TrafficTime"]); if($returnArr[$i]["ReqType"] == "OFF") $Off += $endOff - $startOff; else $mission += $endOff - $startOff; } } $index++; $i++; } $i--; $lastAbsence = 0; if($returnArr[$i]["TrafficTime"] != "" && strtotime($returnArr[$i]["TrafficTime"]) < strtotime($returnArr[$i]["ToTime"])) $lastAbsence = strtotime($returnArr[$i]["ToTime"]) - strtotime($returnArr[$i]["TrafficTime"]); $ShiftDuration = strtotime($returnArr[$i]["ToTime"]) - strtotime($returnArr[$i]["FromTime"]); //$extra = ($totalAttend > $ShiftDuration) ? $totalAttend - $ShiftDuration : 0; $Absence = $totalAttend < $ShiftDuration ? $ShiftDuration - $totalAttend : 0; if($returnArr[$i]["holiday"]) { $extra = $totalAttend; $lastAbsence = 0; $firstAbsence = 0; $Absence = 0; $Off = 0; } if($Absence == $ShiftDuration) $SUM["DailyAbsence"]++; $SUM["absence"] += $Absence; $SUM["attend"] += $totalAttend; $SUM["firstAbsence"] += $firstAbsence; $SUM["lastAbsence"] += $lastAbsence; $SUM["extra"] += $extra; $SUM["Off"] += $Off; $SUM["mission"] += $mission; $totalAttend = TimeModules::SecondsToTime($totalAttend); $firstAbsence = TimeModules::SecondsToTime($firstAbsence); $lastAbsence = TimeModules::SecondsToTime($lastAbsence); $Absence = TimeModules::SecondsToTime($Absence); $extra = TimeModules::SecondsToTime($extra); $Off = TimeModules::SecondsToTime($Off); $mission = TimeModules::SecondsToTime($mission); $returnStr .= "</td><td class=attend>" . ShowTime($totalAttend) . "</td> <td class=extra>" . ShowTime($extra) . "</td> <td class=off>" . ShowTime($Off) . "</td> <td class=mission>" . ShowTime($mission) . "</td> <td class=sub>" . ShowTime($firstAbsence) . "</td> <td class=sub>" . ShowTime($lastAbsence) . "</td> <td class=sub>" . ShowTime($Absence) . "</td> </tr>"; } ?> <style> .reportTbl td {padding:4px;} .reportTbl th {padding:4px;text-align: center; background-color: #efefef; font-weight: bold} .reportTbl .attend { text-align:center} .reportTbl .extra { background-color: #D0F7E2; text-align:center} .reportTbl .off { background-color: #D7BAFF; text-align:center} .reportTbl .mission { text-align:center} .reportTbl .sub { background-color: #FFcfdd; text-align:center} .reportTbl .footer { background-color: #eee; text-align:center; line-height: 18px} </style> <table class="reportTbl" width="100%" border="1"> <tr class="blueText"> <th>روز</th> <th>تاریخ</th> <th>شیفت</th> <th style=width:70px>ورود/خروج</th> <th>حضور</th> <th class="extra">اضافه کار</th> <th class="off" >مرخصی</th> <th>ماموریت</th> <th class=sub>تاخیر</th> <th class=sub>تعجیل</th> <th class=sub>غیبت</th> </tr> <?= $returnStr ?> <tr class="footer"> <? $SUM["absence"] = TimeModules::SecondsToTime($SUM["absence"]); $SUM["attend"] = TimeModules::SecondsToTime($SUM["attend"] ); $SUM["firstAbsence"] = TimeModules::SecondsToTime($SUM["firstAbsence"]); $SUM["lastAbsence"] = TimeModules::SecondsToTime($SUM["lastAbsence"]); $SUM["extra"] = TimeModules::SecondsToTime($SUM["extra"]); $SUM["Off"] = TimeModules::SecondsToTime($SUM["Off"]); $SUM["mission"] = TimeModules::SecondsToTime($SUM["mission"]); ?> <td colspan="4"></td> <td><?= ShowTime($SUM["attend"]) ?></td> <td><?= ShowTime($SUM["extra"]) ?></td> <td><?= ShowTime($SUM["Off"]) ?></td> <td><?= ShowTime($SUM["mission"]) ?></td> <td><?= ShowTime($SUM["firstAbsence"]) ?></td> <td><?= ShowTime($SUM["lastAbsence"]) ?></td> <td><?= ShowTime($SUM["absence"]) ?></td> </tr> <tr class="footer"> <td colspan="4">مجموع عملکرد</td> <td colspan="3"> مجموع مرخصی استعلاجی : <?= $SUM["DailyOff_1"] ?><br> مجموع مرخصی استحقاقی : <?= $SUM["DailyOff_2"] ?><br> مجموع مرخصی بدون حقوق : <?= $SUM["DailyOff_3"] ?><br> </td> <td colspan="4"> مجموع ماموریت روزانه : <?= $SUM["DailyMission"] ?><br> مجموع غیبت روزانه : <?= $SUM["DailyAbsence"]?><br> </td> </tr> </table> <? die(); }
function ShowReport(){ if($_POST["FromDate"] == "") { $OrigStartDate = DateModules::shamsi_to_miladi($_POST["year"] . "-" . $_POST["month"] . "-01", "-"); $OrigEndDate = DateModules::shamsi_to_miladi($_POST["year"] . "-" . $_POST["month"] ."-" . DateModules::DaysOfMonth($_POST["year"] ,$_POST["month"]), "-"); } else { $OrigStartDate = DateModules::shamsi_to_miladi($_POST["FromDate"], "-"); $OrigEndDate = DateModules::shamsi_to_miladi($_POST["ToDate"], "-"); } $holidays = ATN_holidays::Get(" AND TheDate between ? AND ? order by TheDate", array($OrigStartDate, $OrigEndDate)); $holidayIndex = 0; $holidays = $holidays->fetchAll(); $where = ""; $param = array(); if(!empty($_POST["PersonID"])) { $where .= " AND PersonID = ?"; $param[] = $_POST["PersonID"]; } $PersonsDT = PdoDataAccess::runquery("select PersonID, concat(fname,' ',lname) fullname from BSC_persons where IsStaff='YES' " . $where, $param); $returnStr = ""; foreach($PersonsDT as $personRecord) { $holidayIndex = 0; $holidayRecord = $holidayIndex < count($holidays) ? $holidays[$holidayIndex++] : null; $PersonID = $personRecord["PersonID"]; $SUM = array( "absence" => 0, "attend"=> 0, "firstAbsence" => 0, "lastAbsence" => 0, "extra" => 0, "Off" => 0, "mission" => 0, "DailyOff_1" => 0, "DailyOff_2" => 0, "DailyOff_3" => 0, "DailyMission" => 0, "DailyAbsence" => 0 ); $StartDate = $OrigStartDate; $EndDate = $OrigEndDate; $query = "select * from ( select 'normal' RecordType,'' ReqType, TrafficDate,TrafficTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_traffic t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND TrafficDate between FromDate AND ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.IsActive='YES' AND t.PersonID=:p AND TrafficDate>= :sd AND TrafficDate <= :ed union All select 'start' RecordType,t.ReqType, t.FromDate,StartTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_requests t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND t.FromDate between ps.FromDate AND ps.ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.PersonID=:p AND t.ToDate is null AND ReqStatus=2 AND t.FromDate>= :sd union all select 'end' RecordType,t.ReqType, t.FromDate,EndTime,s.ShiftTitle,s.FromTime,s.ToTime ,ExceptFromTime,ExceptToTime from ATN_requests t left join ATN_PersonShifts ps on(ps.IsActive='YES' AND t.PersonID=ps.PersonID AND t.FromDate between ps.FromDate AND ps.ToDate) left join ATN_shifts s on(ps.ShiftID=s.ShiftID) where t.PersonID=:p AND t.ToDate is null AND ReqStatus=2 AND t.FromDate>= :sd AND EndTime is not null )t2 order by TrafficDate,TrafficTime"; $dt = PdoDataAccess::runquery($query, array(":p" => $PersonID, ":sd" => $StartDate, ":ed" => $EndDate)); //........................ create days array .................. $index = 0; $returnArr = array(); while($StartDate <= $EndDate) { if($index < count($dt) && $StartDate == $dt[$index]["TrafficDate"]) { while($index < count($dt) && $StartDate == $dt[$index]["TrafficDate"]) $returnArr[] = $dt[$index++]; $StartDate = DateModules::AddToGDate($StartDate, 1); continue; } $shiftRecord = ATN_PersonShifts::GetShiftOfDate($PersonID, $StartDate); $returnArr[] = array( "RecordType" => "normal", "TrafficID" => "", "TrafficDate" => $StartDate , "ShiftTitle" => $shiftRecord["ShiftTitle"], "FromTime" => $shiftRecord["FromTime"], "ToTime" => $shiftRecord["ToTime"], "ExceptFromTime" => $shiftRecord["ExceptFromTime"], "ExceptToTime" => $shiftRecord["ExceptToTime"], "TrafficTime" => ""); $StartDate = DateModules::AddToGDate($StartDate, 1); } //------------ holidays ------------------ for($i=0; $i<count($returnArr); $i++) { $holiday = false; if(FridayIsHoliday && DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") == "5") $holiday = true; if(ThursdayIsHoliday && DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "N") == "4") $holiday = true; if($holidayRecord && $holidayRecord["TheDate"] == $returnArr[$i]["TrafficDate"]) { $holiday = true; $holidayRecord = $holidayIndex < count($holidays) ? $holidays[$holidayIndex++] : null; } $returnArr[$i]["holiday"] = $holiday; } //........................................................... for($i=0; $i < count($returnArr); $i++) { if(!$returnArr[$i]["holiday"]) { //........... Daily off and mission ................... $requests = PdoDataAccess::runquery(" select t.*, InfoDesc OffTypeDesc from ATN_requests t left join BaseInfo on(TypeID=20 AND InfoID=OffType) where ReqStatus=2 AND PersonID=:p AND FromDate <= :td AND if(ToDate is not null, ToDate >= :td, 1=1) order by ToDate desc,StartTime asc ", array( ":p" => $PersonID, ":td" => $returnArr[$i]["TrafficDate"] )); if(count($requests) > 0) { if($requests[0]["ReqType"] == "DayOFF") { $SUM["DailyOff_" . $requests[0]["OffType"] ]++; $currentDay = $returnArr[$i]["TrafficDate"]; while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) $i++; $i--; continue; } if($requests[0]["ReqType"] == "DayMISSION") { $SUM["DailyMission"]++; $currentDay = $returnArr[$i]["TrafficDate"]; while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) $i++; $i--; continue; } } } //.................................................... if( DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "l") == "Thursday") { $returnArr[$i]["FromTime"] = $returnArr[$i]["ExceptFromTime"]; $returnArr[$i]["ToTime"] = $returnArr[$i]["ExceptToTime"]; } //.................................................... $firstAbsence = 0; $Off = 0; $mission = 0; $index = 1; $totalAttend = 0; if($returnArr[$i]["TrafficTime"] != "" && strtotime($returnArr[$i]["TrafficTime"]) > strtotime($returnArr[$i]["FromTime"])) $firstAbsence = strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i]["FromTime"]); $currentDay = $returnArr[$i]["TrafficDate"]; $startOff = 0; $endOff = 0; $extra = 0; while($i < count($returnArr) && $currentDay == $returnArr[$i]["TrafficDate"]) { //.................................................... if( DateModules::GetWeekDay($returnArr[$i]["TrafficDate"], "l") == "Thursday") { $returnArr[$i]["FromTime"] = $returnArr[$i]["ExceptFromTime"]; $returnArr[$i]["ToTime"] = $returnArr[$i]["ExceptToTime"]; } //.................................................... if($index % 2 == 0) { $totalAttend += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); if(strtotime($returnArr[$i]["TrafficTime"]) > strtotime($returnArr[$i]["ToTime"])) { if(strtotime($returnArr[$i-1]["TrafficTime"]) > strtotime($returnArr[$i]["ToTime"])) $extra += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); else $extra += strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["ToTime"]); } } if($returnArr[$i]["RecordType"] != "normal") { if($i>0 && $returnArr[$i-1]["TrafficDate"] == $currentDay && $returnArr[$i]["RecordType"] == "start") { if($i == 0 || $returnArr[$i-1]["TrafficDate"] != $currentDay) $startDiff = 0; else $startDiff = strtotime($returnArr[$i]["TrafficTime"]) - strtotime($returnArr[$i-1]["TrafficTime"]); if($startDiff > Valid_Traffic_diff) $startOff = strtotime($returnArr[$i]["TrafficTime"]) - Valid_Traffic_diff; else $startOff = strtotime($returnArr[$i-1]["TrafficTime"]); } if( ($i==0 || $returnArr[$i-1]["TrafficDate"] != $currentDay) && $returnArr[$i]["RecordType"] == "start") { $startOff = strtotime($returnArr[$i]["TrafficTime"]); } if($returnArr[$i]["RecordType"] == "end") { if($i == count($returnArr)-1 || $returnArr[$i+1]["TrafficDate"] != $currentDay) $endDiff = 0; else $endDiff = strtotime($returnArr[$i+1]["TrafficTime"]) - strtotime($returnArr[$i]["TrafficTime"]); if($endDiff > Valid_Traffic_diff) $endOff = strtotime($returnArr[$i]["TrafficTime"]) - Valid_Traffic_diff; else $endOff = strtotime($returnArr[$i]["TrafficTime"]); if($returnArr[$i]["ReqType"] == "OFF") $Off += $endOff - $startOff; else $mission += $endOff - $startOff; } } $index++; $i++; } $i--; $lastAbsence = 0; if($returnArr[$i]["TrafficTime"] != "" && strtotime($returnArr[$i]["TrafficTime"]) < strtotime($returnArr[$i]["ToTime"])) $lastAbsence = strtotime($returnArr[$i]["ToTime"]) - strtotime($returnArr[$i]["TrafficTime"]); $ShiftDuration = strtotime($returnArr[$i]["ToTime"]) - strtotime($returnArr[$i]["FromTime"]); $Absence = $totalAttend < $ShiftDuration ? $ShiftDuration - $totalAttend : 0; if($returnArr[$i]["holiday"]) { $extra = $totalAttend + $mission; $lastAbsence = 0; $firstAbsence = 0; $Absence = 0; $Off = 0; } if($Absence == $ShiftDuration) { $SUM["DailyAbsence"]++; $Absence = 0; } $SUM["absence"] += $Absence; $SUM["attend"] += $totalAttend; $SUM["firstAbsence"] += $firstAbsence; $SUM["lastAbsence"] += $lastAbsence; $SUM["extra"] += $extra; $SUM["Off"] += $Off; $SUM["mission"] += $mission; } $SUM["absence"] = TimeModules::SecondsToTime($SUM["absence"]); $SUM["attend"] = TimeModules::SecondsToTime($SUM["attend"] ); $SUM["firstAbsence"] = TimeModules::SecondsToTime($SUM["firstAbsence"]); $SUM["lastAbsence"] = TimeModules::SecondsToTime($SUM["lastAbsence"]); $SUM["extra"] = TimeModules::SecondsToTime($SUM["extra"]); $SUM["Off"] = TimeModules::SecondsToTime($SUM["Off"]); $SUM["mission"] = TimeModules::SecondsToTime($SUM["mission"]); $returnStr .= "<tr> <td>" . $personRecord["fullname"] . "</td> <td>" . ShowTime($SUM["attend"]) . "</td> <td>" . ShowTime($SUM["extra"]) . "</td> <td>" . ShowTime($SUM["Off"]) . "</td> <td>" . ShowTime($SUM["mission"]) . "</td> <td>" . ShowTime($SUM["firstAbsence"]) . "</td> <td>" . ShowTime($SUM["lastAbsence"]) . "</td> <td>" . ShowTime($SUM["absence"]) . "</td> <td>" . $SUM["DailyOff_1"] . "</td> <td>" . $SUM["DailyOff_2"] . "</td> <td>" . $SUM["DailyOff_3"] . "</td> <td>" . $SUM["DailyMission"] . "</td> <td>" . $SUM["DailyAbsence"] . "</td> </tr>"; } ?> <META http-equiv=Content-Type content="text/html; charset=UTF-8" ><body dir="rtl"> <link rel="stylesheet" type="text/css" href="/generalUI/fonts/fonts.css" /></head> <style> .reportTbl {border-collapse:collapse} .reportTbl td {padding:4px;font-family: nazanin; font-size:14px;} .reportTbl th {font-family: nazanin; font-size:14px;padding:4px;text-align: center; background-color: #efefef; font-weight: bold} .reportTbl .attend { text-align:center} .reportTbl .extra { background-color: #D0F7E2; text-align:center} .reportTbl .off { background-color: #D7BAFF; text-align:center} .reportTbl .mission { text-align:center} .reportTbl .sub { background-color: #FFcfdd; text-align:center} .reportTbl .footer { background-color: #eee; text-align:center; line-height: 18px} </style> <table style='border:2px groove #9BB1CD;border-collapse:collapse;width:100%'> <tr> <td width=60px><img src='/framework/icons/logo.jpg' style='width:120px'></td> <td align='center' style='height:100px;vertical-align:middle;font-family:b titr;font-size:15px'> گزارش خلاصه کارکرد پرسنل <br>از تاریخ <?= DateModules::miladi_to_shamsi($OrigStartDate) ?> تا تاریخ <?= DateModules::miladi_to_shamsi($OrigEndDate) ?> </td> <td width='200px' align='center' style='font-family:tahoma;font-size:11px'>تاریخ تهیه گزارش : <?= DateModules::shNow() ?> </td> </tr> </table> <table class="reportTbl" width="100%" border="1"> <tr class="blueText"> <th>نام و نام خانوادگی</th> <th>حضور</th> <th>اضافه کار</th> <th>مرخصی</th> <th>ماموریت</th> <th>تاخیر</th> <th>تعجیل</th> <th>غیبت</th> <th>مرخصی استعلاجی</th> <th>مرخصی استحقاقی</th> <th>مرخصی بدون حقوق</th> <th>ماموریت روزانه</th> <th>غیبت روزانه</th> </tr> <?= $returnStr ?> </table> <? die(); }