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