public static function get_arrear_document($staff_id, $pay_year, $pay_month) { for ($t = $_POST['from_pay_year']; $t < $_POST['to_pay_year'] + 1; $t++) { $retCoef = 1; $qry = " select max(arrear_ver) MV\r\n\t\t\t\t\tfrom corrective_payment_writs \r\n\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year = " . $t . "\r\n\t\t "; $MaxVer = PdoDataAccess::runquery($qry); if (count($MaxVer) == 0 || $MaxVer[0]['MV'] == 0) { continue; } $qry = " select cpw.staff_id , cpw.writ_id , cpw.writ_ver , cpw.arrear_ver , w.execute_date, w.send_letter_no , w.salary_pay_proc\r\n\t\t\r\n\t\t\t\t from corrective_payment_writs cpw inner join writs w \r\n\t\t\t on cpw.staff_id = w.staff_id and \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tcpw.writ_id = w.writ_id and \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tcpw.writ_ver = w.writ_ver \r\n\r\n\t\t\t where cpw.pay_year = " . $t . " and cpw.staff_id = " . $SID . " and arrear_ver = " . $MaxVer[0]['MV'] . " and cpw.pay_month = 12 \r\n\t\t\t order by w.execute_date "; $res1 = PdoDataAccess::runquery($qry); //............................. پیدا کردن آخرین ماهی که فیش حقوقی در آن محاسبه شده است ................ $qry = " select max(pay_month) PM from payment_writs where staff_id = " . $SID . " AND pay_year= " . $t; $MaxMonth = PdoDataAccess::runquery($qry); if ($MaxMonth[0]['PM'] > 0 && $MaxMonth[0]['PM'] < 7) { $LastDate = $t . "/" . $MaxMonth[0]['PM'] . "/31"; } else { if ($MaxMonth[0]['PM'] > 6 && $MaxMonth[0]['PM'] < 12) { $LastDate = $t . "/" . $MaxMonth[0]['PM'] . "/30"; } else { if ($MaxMonth[0]['PM'] == 12) { $LastDate = $t . "/" . $MaxMonth[0]['PM'] . "/29"; } else { $LastDate = '0000-00-00'; } } } $LAST_PAY_DATE = date('Y-m-d', strtotime(DateModules::shamsi_to_miladi($LastDate) . "+1 days")); $LAST_PAY_DATE = DateModules::miladi_to_shamsi($LAST_PAY_DATE); if ($res1[count($res1) - 1]['salary_pay_proc'] == 1) { $LastDate = DateModules::miladi_to_shamsi($res1[count($res1) - 1]['execute_date']); $arr = preg_split('/\\//', $LastDate); $NewDate = date('Y-m-d', strtotime(DateModules::shamsi_to_miladi($LastDate) . "-1 days")); $LastDate = DateModules::miladi_to_shamsi($NewDate); $arr = preg_split('/\\//', $LastDate); } $LD = array(); //................................................................................................. for ($i = 0; $i < count($res1); $i++) { if ($LastDate != '0000-00-00' && DateModules::CompareDate($res1[$i]['execute_date'], str_replace("/", "-", DateModules::shamsi_to_miladi($LastDate))) >= 0) { $LD[$t]['lastDate'] = $LastDate; break; } $CDate = DateModules::miladi_to_shamsi($res1[$i]['execute_date']); $arr2 = preg_split('/\\//', $CDate); if (intval($arr2[1]) <= intval($MaxMonth[0]['PM'])) { $qry = " select t.staff_id,max_execute_date ,SUBSTRING(max_execute_date,1,10) execute_date ,\r\n\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING(max_execute_date,11),'.',1) writ_id,\r\n\t\t\t\t\t\tSUBSTRING_INDEX(max_execute_date,'.',-1) writ_ver\r\n\r\n\t\t\t\t\tfrom (\r\n\t\t\t\t\t\t\tselect w.staff_id,max( CONCAT(w.execute_date,w.writ_id,'.',w.writ_ver) ) max_execute_date\r\n\r\n\t\t\t\t\t\t\tfrom payment_writs cpw inner join writs w\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton cpw.staff_id = w.staff_id and\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcpw.writ_id = w.writ_id and\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcpw.writ_ver = w.writ_ver\r\n\t\t\t\t\t\t\twhere cpw.pay_year = " . $t . " and cpw.staff_id = " . $SID . " and\r\n\t\t\t\t\t\t\t /*cpw.pay_month = 12 and*/ execute_date <= '" . $res1[$i]['execute_date'] . "'\r\n\r\n\t\t\t\t\t\t\tgroup by w.staff_id\r\n\t\t\t\t\t\t) t\r\n\t\t\t\t\t\t\t"; // پرس و جو بالا هم بایستی union شود با ورژن های قبلی پرداخت تا احکام آنها هم دیده شود . $res2 = PdoDataAccess::runquery($qry); if (count($res2) == 0) { $res2[0]['execute_date'] = '0000-00-00'; $res2[0]['writ_id'] = 0; $res2[0]['writ_ver'] = 0; } } else { $res2[0]['execute_date'] = '0000-00-00'; $res2[0]['writ_id'] = 0; $res2[0]['writ_ver'] = 0; } /* PdoDataAccess::runquery(" insert compare_arrear_writs (staff_id ,current_execute_date , current_writ_id , current_writ_ver, prev_execute_date , prev_writ_id , prev_writ_ver , arrear_ver , pay_year ) values (" . $res1[$i]['staff_id'] . ",'" . $res1[$i]['execute_date'] . "'," . $res1[$i]['writ_id'] . "," . $res1[$i]['writ_ver'] . ",'" . $res2[0]['execute_date'] . "'," . $res2[0]['writ_id'] . "," . $res2[0]['writ_ver'] . "," . $res1[$i]['arrear_ver'] . "," . $t . " ) "); */ if ($res1[$i]['writ_id'] == $res2[0]['writ_id'] && $res1[$i]['writ_ver'] == $res2[0]['writ_ver']) { continue; } $writNo++; } //..................اگر برج 12 در دیون محاسبه می شود ولی در قبلی نبوده است بایستی به جدول compare افزوده شود.............................. /* if ($res1[(count($res1) - 1)]['salary_pay_proc'] != 1 && $MaxMonth[0]['PM'] < 12) { $qry = " select staff_id , execute_date , writ_id , writ_ver from writs where staff_id = " . $SID . " and salary_pay_proc = 1 and execute_date >= '" . DateModules::shamsi_to_miladi($t . '/01/01') . "' and execute_date <= '" . DateModules::shamsi_to_miladi($t . '/12/29') . "' and issue_date >= '" . $res1[$i - 1]['execute_date'] . "' and state = 3 "; $res3 = PdoDataAccess::runquery($qry); PdoDataAccess::runquery(" insert compare_arrear_writs ( staff_id ,current_execute_date , current_writ_id , current_writ_ver, prev_execute_date , prev_writ_id , prev_writ_ver , arrear_ver , pay_year ) values (" . $SID . ",'" . $res1[$i - 1]['execute_date'] . "'," . $res1[$i - 1]['writ_id'] . "," . $res1[$i - 1]['writ_ver'] . ",'" . $res3[0]['execute_date'] . "'," . $res3[0]['writ_id'] . "," . $res3[0]['writ_ver'] . "," . $res1[$i - 1]['arrear_ver'] . "," . $t . " ) "); } */ //....................we need this part...................... $res = PdoDataAccess::runquery(" select * \r\n\t\t\t\t\t\t\t\t\t\tfrom compare_arrear_writs \r\n\t\t\t\t\t\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year =" . $t . " and arrear_ver = " . $MaxVer[0]['MV']); for ($i = 0; $i < count($res); $i++) { $writsWhereClause .= ' (wsi.writ_id=' . $res[$i]['current_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['current_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ') OR (wsi.writ_id=' . $res[$i]['prev_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['prev_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ' ) OR '; $PrewritsWhereClause .= ' (wsi.writ_id=' . $res[$i]['prev_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['prev_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ' ) OR '; $CurrwritsWhereClause .= ' (wsi.writ_id=' . $res[$i]['current_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['current_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ') OR '; } //............................................................. } $writsWhereClause = substr($writsWhereClause, 0, strlen($writsWhereClause) - 4); $PrewritsWhereClause = substr($PrewritsWhereClause, 0, strlen($PrewritsWhereClause) - 4); $CurrwritsWhereClause = substr($CurrwritsWhereClause, 0, strlen($CurrwritsWhereClause) - 4); $ResITM2 = PdoDataAccess::runquery(" select distinct wsi.salary_item_type_id , sit.print_title ,sit.month_length_effect\r\n\t\t\t\t\t\t\t\t\t\t\t from writ_salary_items wsi \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tinner join salary_item_types sit \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton wsi.salary_item_type_id = sit.salary_item_type_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\twhere " . $writsWhereClause); //.............................................................................. $ResITM = array(); $diffVal = 0; for ($j = 0; $j < count($ResITM2); $j++) { $res6 = PdoDataAccess::runquery(" select wsi.salary_item_type_id , sit.print_title , sum(wsi.value) CurrVal \r\n\t\t\t\t\t\t\t\t\t\t\t from writ_salary_items wsi \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tinner join salary_item_types sit \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton wsi.salary_item_type_id = sit.salary_item_type_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\twhere wsi.must_pay = 1 and (" . $CurrwritsWhereClause . ") and wsi.salary_item_type_id =" . $ResITM2[$j]['salary_item_type_id']); $res7 = PdoDataAccess::runquery(" select wsi.salary_item_type_id , sit.print_title , sum(wsi.value) PreVal \r\n\t\t\t\t\t\t\t\t\t\t\t from writ_salary_items wsi \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tinner join salary_item_types sit \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton wsi.salary_item_type_id = sit.salary_item_type_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\twhere wsi.must_pay = 1 and (" . $PrewritsWhereClause . ") and wsi.salary_item_type_id =" . $ResITM2[$j]['salary_item_type_id']); $diffVal = $res6[0]['CurrVal'] - $res7[0]['PreVal']; if ($diffVal != 0) { $diffVal = 0; $ResITM[] = array('salary_item_type_id' => $ResITM2[$j]['salary_item_type_id'], 'print_title' => $ResITM2[$j]['print_title'], 'month_length_effect' => $ResITM2[$j]['month_length_effect']); } else { //.................................... چنانچه قلمی در حکم فعلی حذف شده باشد................. for ($i = 0; $i < count($res); $i++) { $res6 = PdoDataAccess::runquery(' select wsi.salary_item_type_id , sit.print_title , sum(wsi.value) CurrVal from writ_salary_items wsi inner join salary_item_types sit on wsi.salary_item_type_id = sit.salary_item_type_id where wsi.must_pay = 1 and (wsi.writ_id=' . $res[$i]['prev_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['prev_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ' ) and wsi.salary_item_type_id =' . $ResITM2[$j]['salary_item_type_id']); $res7 = PdoDataAccess::runquery(' select wsi.salary_item_type_id , sit.print_title , sum(wsi.value) PreVal from writ_salary_items wsi inner join salary_item_types sit on wsi.salary_item_type_id = sit.salary_item_type_id where wsi.must_pay = 1 and (wsi.writ_id=' . $res[$i]['current_writ_id'] . ' AND wsi.writ_ver=' . $res[$i]['current_writ_ver'] . ' AND wsi.staff_id=' . $res[$i]['staff_id'] . ') and wsi.salary_item_type_id =' . $ResITM2[$j]['salary_item_type_id']); if (count($res6) != count($res7) || $res6[0]['CurrVal'] != $res7[0]['PreVal']) { if (!in_array($ResITM2[$j]['salary_item_type_id'], $ResITM)) { $ResITM[] = array('salary_item_type_id' => $ResITM2[$j]['salary_item_type_id'], 'print_title' => $ResITM2[$j]['print_title'], 'month_length_effect' => $ResITM2[$j]['month_length_effect']); } } } } } //.......................................................................... $TotalsumDiff = $TotalMainVal = $TotalRowTax = $TotalRowIns = $TotalRowRet = $TotalPay = 0; for ($t = $_POST['from_pay_year']; $t < $_POST['to_pay_year'] + 1; $t++) { $retCoef = 1; $qry = " select max(arrear_ver) MV\r\n\t\t\t\t\tfrom corrective_payment_writs \r\n\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year = " . $t . "\r\n\t\t "; $MaxVer = PdoDataAccess::runquery($qry); if (count($MaxVer) == 0 || $MaxVer[0]['MV'] == 0) { continue; } $res = PdoDataAccess::runquery(" select * \r\n\t\t\t\t\t\t\t\t\t\tfrom compare_arrear_writs \r\n\t\t\t\t\t\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year =" . $t . " and arrear_ver = " . $MaxVer[0]['MV']); $prior_execute_date = $current_execute_date = $prior_writ_type = $current_writ_type = ''; $current_writ_items = $prior_writ_items = array(); $sdate = ""; for ($i = 0; $i < count($res); $i++) { if ($res[$i]['current_writ_id'] == $res[$i]['prev_writ_id'] && $res[$i]['current_writ_ver'] == $res[$i]['prev_writ_ver']) { continue; } if (!empty($LD[$t]['lastDate'])) { $toDate = $LD[$t]['lastDate']; } else { if ($LastDate == '0000-00-00') { $toDate = $t . "/12/30"; } else { $toDate = DateModules::CompareDate(str_replace("/", "-", DateModules::shamsi_to_miladi($t . "/12/30")), str_replace("/", "-", DateModules::shamsi_to_miladi($LastDate))) >= 0 ? $LastDate : $t . "/12/30"; } } /* if ($sdate != "" && $sdate > '0000-00-00') $Row .= "<td>" . $sdate . "</td>"; else $Row .= "<td>" .DateModules::miladi_to_shamsi($res[$i]['current_execute_date']). "</td>"; */ if ($res[$i]['current_execute_date'] == $res[$i + 1]['current_execute_date']) { //$Row .= "<td>" . DateModules::AddToJDate($toDate, 1) . "</td>"; $sdate = DateModules::AddToJDate($toDate, 1); } /*else if ($sdate != "" && $sdate > '0000-00-00' && empty($res[$i + 1]['current_execute_date'])) $Row .= "<td>" . $t . "/12/29" . "</td>"; elseif($res[$i + 1]['current_execute_date'] !='0000-00-00' && DateModules::CompareDate(DateModules::miladi_to_shamsi($res[$i + 1]['current_execute_date']),$LAST_PAY_DATE) == 1 ) { $Row .= "<td>" . $LAST_PAY_DATE . "</td>"; } else $Row .= "<td>" . ( empty($res[$i + 1]['current_execute_date']) ? $toDate : DateModules::miladi_to_shamsi($res[$i + 1]['current_execute_date']) ) . "</td>"; */ if (!empty($LD[$t]['lastDate'])) { $toDate2 = DateModules::shamsi_to_miladi(DateModules::AddToJDate($LD[$t]['lastDate'], 1)); } else { if ($LastDate == '0000-00-00') { $toDate2 = DateModules::shamsi_to_miladi($t + 1 . "/01/01"); } else { $toDate2 = DateModules::CompareDate(str_replace("/", "-", DateModules::shamsi_to_miladi($t . "/12/30")), str_replace("/", "-", DateModules::shamsi_to_miladi($LastDate))) >= 0 ? DateModules::AddToJDate($LastDate, 1) : DateModules::shamsi_to_miladi($t + 1 . "/01/01"); } } $endDate = empty($res[$i + 1]['current_execute_date']) ? $toDate2 : $res[$i + 1]['current_execute_date']; if ($res[$i]['current_execute_date'] == $res[$i + 1]['current_execute_date']) { $endDate = $toDate2; } if ($sdate != "" && $sdate > '0000-00-00' && empty($res[$i + 1]['current_execute_date'])) { $endDate = $t + 1 . "/1/01"; if ($resPerInfo[0]['person_type'] == 3) { $DayNo = round(DateModules::GDateMinusGDate(DateModules::shamsi_to_miladi($endDate), DateModules::shamsi_to_miladi($sdate))); $DayNo2 = calculate_duration($sdate, $endDate); } else { $DayNo = calculate_duration($sdate, $endDate); } } else { if ($resPerInfo[0]['person_type'] == 3) { $DayNo = round(DateModules::GDateMinusGDate(DateModules::shamsi_to_miladi($endDate), $res[$i]['current_execute_date'])); $DayNo2 = calculate_duration($res[$i]['current_execute_date'], $endDate); } else { $DayNo = calculate_duration($res[$i]['current_execute_date'], $endDate); } } if ($res[$i + 1]['current_execute_date'] != '0000-00-00' && DateModules::CompareDate(DateModules::miladi_to_shamsi($res[$i + 1]['current_execute_date']), $LAST_PAY_DATE) == 1) { $DayNo = calculate_duration($res[$i]['current_execute_date'], DateModules::shamsi_to_miladi($LAST_PAY_DATE)); } // $Row .= "<td>" . $DayNo . "</td>"; $sumDiff = $MainVal = 0; $RetInc = $TaxInc = $InsInc = 0; for ($j = 0; $j < count($ResITM); $j++) { $val1 = get_writSalaryItem_value($res[$i]["current_writ_id"], $res[$i]["current_writ_ver"], $res[$i]["staff_id"], $ResITM[$j]["salary_item_type_id"]); $val2 = get_writSalaryItem_value($res[$i]["prev_writ_id"], $res[$i]["prev_writ_ver"], $res[$i]["staff_id"], $ResITM[$j]["salary_item_type_id"]); $RetRes = PdoDataAccess::runquery(" select retired_include , tax_include , insure_include \r\n from salary_item_types where salary_item_type_id = " . $ResITM[$j]["salary_item_type_id"]); // $Row .= "<td>" . CurrencyModulesclass::toCurrency($val1 - $val2) . (($val1 - $val2) < 0 ? "- " : "" ) . "</td>"; $sumDiff += $val1 - $val2; if (empty($Itm[$j]['sumVal'])) { $Itm[$j]['sumVal'] = 0; $Itm[$j]['sumCol'] = 0; } $Itm[$j]['sumVal'] += $val1 - $val2; if ($ResITM[$j]["month_length_effect"] == 0 && $resPerInfo[0]['person_type'] == 3) { $Itm[$j]['sumCol'] += round(($val1 - $val2) * $DayNo2 / 30); $MainVal += round(($val1 - $val2) * $DayNo2 / 30); } elseif ($resPerInfo[0]['person_type'] == 5 && $_SESSION['UserID'] == 'jafarkhani') { // echo $endDate . "---<br>" ; // echo DateModules::miladi_to_shamsi($res[$i]['current_execute_date'])."***<br>" ; die(); $arr3 = preg_split('/\\//', DateModules::miladi_to_shamsi($res[$i]['current_execute_date'])); $arr4 = preg_split('/\\//', DateModules::miladi_to_shamsi($endDate)); if ($arr4[1] * 1 == 1 && $arr4[0] == $arr3[0] + 1) { $EP = 12; } else { $EP = $arr4[1] * 1; } //......................... $std = $res[$i]['current_execute_date']; $stMonth = $arr3[1] * 1; if ($stMonth < 7) { $endt = $arr3[0] . "/" . $arr3[1] . "/31"; } elseif (6 < $stMonth && $stMonth < 12) { $endt = $arr3[0] . "/" . $arr3[1] . "/30"; } elseif ($stMonth == 12) { $endt = $arr3[0] . "/" . $arr3[1] . "/29"; } for ($k = $arr3[1] * 1; $k < $EP + 1; $k++) { if (DateModules::CompareDate(DateModules::miladi_to_shamsi($endt), DateModules::miladi_to_shamsi($endDate)) < 1) { $DYNo = round(DateModules::GDateMinusGDate(DateModules::shamsi_to_miladi($endt), $std)) + 1; if ($stMonth < 7) { $mNO = 31; } elseif ($stMonth > 6 && $stMonth < 12) { $mNO = 30; } elseif ($stMonth == 12) { $mNO = 29; } $Itm[$j]['sumCol'] += round(($val1 - $val2) * $DYNo / $mNO); $MainVal += round(($val1 - $val2) * $DYNo / $mNO); $TaxInc += round(($val1 - $val2) * $DYNo / $mNO); //........................ $std = DateModules::shamsi_to_miladi($arr3[0] . "/" . ($k + 1) . "/01"); $stMonth = $k + 1; //$arr3[1] + 1; if ($stMonth < 7) { $endt = $arr3[0] . "/" . $stMonth . "/31"; } elseif (6 < $stMonth && $stMonth < 12) { $endt = $arr3[0] . "/" . $stMonth . "/30"; } elseif ($stMonth == 12) { $endt = $arr3[0] . "/" . $stMonth . "/29"; } } else { //echo "****"; // die(); $endt = $endDate; $DYNo = round(DateModules::GDateMinusGDate(DateModules::shamsi_to_miladi($endt), $std)); if ($stMonth < 7) { $mNO = 31; } elseif ($stMonth > 6 && $stMonth < 12) { $mNO = 30; } elseif ($stMonth == 12) { $mNO = 29; } $Itm[$j]['sumCol'] += round(($val1 - $val2) * $DYNo / $mNO); $MainVal += round(($val1 - $val2) * $DYNo / $mNO); $TaxInc += round(($val1 - $val2) * $DYNo / $mNO); break; } } } else { $Itm[$j]['sumCol'] += round(($val1 - $val2) * $DayNo / 30); $MainVal += round(($val1 - $val2) * $DayNo / 30); } //............................... if ($RetRes[0]['retired_include'] == 1) { $RetInc += $val1 - $val2; } if ($RetRes[0]['tax_include'] == 1 && $resPerInfo[0]['person_type'] != 5) { if ($ResITM[$j]["month_length_effect"] == 0 && $resPerInfo[0]['person_type'] == 3) { $TaxInc += round(($val1 - $val2) * $DayNo2 / 30); } else { $TaxInc += round(($val1 - $val2) * $DayNo / 30); } } if ($RetRes[0]['insure_include'] == 1) { $InsInc += $val1 - $val2; } } //$MainVal = round(($sumDiff) * $DayNo / 30); //....................مالیات........... $qry = " select count(*) cn \r\n\t\t\t\t\tfrom Arrear_payment_items \r\n\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year = " . $t . " and pay_month = 12 and salary_item_type_id in (146,147,148,747)"; $res4 = PdoDataAccess::runquery($qry); if ($res4[0]['cn'] > 0) { //......................................... $qry = " select count(*) cnp\r\n from payments \r\n where staff_id = " . $SID . " and pay_year = " . $t; $resE = PdoDataAccess::runquery($qry); if ($resE[0]['cnp'] == 0) { $sdatetax = DateModules::shamsi_to_miladi($t . "/12/29"); $qry = " SELECT tti.from_value\r\n FROM staff_tax_history sth\r\n inner join tax_tables tt on sth.tax_table_type_id = tt.tax_table_type_id\r\n inner join tax_table_items tti on tti.tax_table_id = tt.tax_table_id\r\n\r\n WHERE sth.staff_id = " . $SID . " and \r\n sth.start_date < '" . DateModules::Now() . "' and\r\n ( sth.end_date = '0000-00-00' or sth.end_date is null or \r\n sth.end_date > '" . DateModules::Now() . "' ) and\r\n tt.from_date < '" . $sdatetax . "' and\r\n ( tt.to_date = '0000-00-00' or tt.to_date is null or tt.to_date >= '" . $sdatetax . "' ) and coeficient = 0.1 "; $resFval = PdoDataAccess::runquery($qry); $TaxInc -= $resFval[0]['from_value']; } //......................................... $RowTax = $TaxInc * 0.1; //round(($TaxInc * $DayNo / 30) * 0.1); } else { $RowTax = 0; } //.................................. $qry = " select count(*) cn \r\n\t\t\t\t\tfrom Arrear_payment_items \r\n\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year = " . $t . " and pay_month = 12 and salary_item_type_id in (149 , 150)"; $res3 = PdoDataAccess::runquery($qry); if ($res3[0]['cn'] > 0) { $RowRet = round($RetInc * $DayNo / 30 * 0.09); } else { $RowRet = 0; } //.......................بیمه تامین اجتماعی........... $qry = " select count(*) cn \r\n\t\t\t\t\tfrom Arrear_payment_items \r\n\t\t\t\t\t\twhere staff_id = " . $SID . " and pay_year = " . $t . " and pay_month = 12 and salary_item_type_id in (144,145,744,9920)"; $res5 = PdoDataAccess::runquery($qry); if ($res5[0]['cn'] > 0) { $RowInsure = round($InsInc * $DayNo / 30 * 0.07000000000000001); } else { $RowInsure = 0; } //..................................... if ($resPerInfo[0]['last_retired_pay'] != NULL && $resPerInfo[0]['last_retired_pay'] != '0000-00-00' && DateModules::CompareDate($resPerInfo[0]['last_retired_pay'], DateModules::shamsi_to_miladi($t . "/01/01")) == -1) { $RowRet = $retCoef = 0; } $TotalsumDiff += $sumDiff; $TotalMainVal += $MainVal; $TotalRowTax += $RowTax; $TotalRowRet += $RowRet; $TotalRowIns += $RowInsure; $TotalPay += $MainVal - ($RowTax + $RowRet + $RowInsure); /* $Row .= "<td>" . ( ($sumDiff < 0 ) ? CurrencyModulesclass::toCurrency($sumDiff) . "-" : CurrencyModulesclass::toCurrency($sumDiff)) . "</td> <td>" . (($MainVal < 0 ) ? CurrencyModulesclass::toCurrency($MainVal) . "-" : CurrencyModulesclass::toCurrency($MainVal)) . "</td><td>" . (($RowTax < 0 ) ? CurrencyModulesclass::toCurrency($RowTax) . "-" : CurrencyModulesclass::toCurrency($RowTax)) . "</td><td>" . (($RowRet < 0 ) ? CurrencyModulesclass::toCurrency($RowRet) . "-" : CurrencyModulesclass::toCurrency($RowRet)) . "</td><td>" . (($RowInsure < 0 ) ? CurrencyModulesclass::toCurrency($RowInsure) . "-" : CurrencyModulesclass::toCurrency($RowInsure)) . "</td><td>" . ((($MainVal - ($RowTax + $RowRet + $RowInsure )) < 0 ) ? CurrencyModulesclass::toCurrency(($MainVal - ($RowTax + $RowRet + $RowInsure))) . "-" : CurrencyModulesclass::toCurrency(($MainVal - ($RowTax + $RowRet + $RowInsure)))) . "</td></tr>"; */ } // $Row .= "</tr>"; } }
function ShowReport(){ /* $qry = " select cpw.staff_id , cpw.writ_id , cpw.writ_ver , cpw.arrear_ver , w.execute_date from corrective_payment_writs cpw inner join writs w on cpw.staff_id = w.staff_id and cpw.writ_id = w.writ_id and cpw.writ_ver = w.writ_ver where cpw.pay_year = 1392 and cpw.staff_id = 471479 and cpw.pay_month = 12 " ; $res1 = PdoDataAccess::runquery($qry) ; $qry = " select cpw.staff_id , cpw.writ_id , cpw.writ_ver , w.execute_date from hrms.payment_writs cpw inner join writs w on cpw.staff_id = w.staff_id and cpw.writ_id = w.writ_id and cpw.writ_ver = w.writ_ver where cpw.pay_year = 1392 and cpw.staff_id = 471479 and cpw.pay_month = 12 " ; // پرس و جو بالا هم بایستی union شود با ورژن های قبلی پرداخت تا احکام آنها هم دیده شود . $res2 = PdoDataAccess::runquery($qry) ; for($i=0; $i<count($res1);$i++) { for($j=0;$j<count($res2);$j++) { if( $res1[$i]['execute_date'] == $res2[$j]['execute_date'] && ($res1[$i]['writ_id'] != $res2[$j]['writ_id'] || $res1[$i]['writ_ver'] != $res2[$j]['writ_ver'] ) ) { PdoDataAccess::runquery(" insert compare_arrear_writs (staff_id ,current_execute_date , current_writ_id , current_writ_ver, prev_execute_date , prev_writ_id , prev_writ_ver , arrear_ver , pay_year ) values (".$res1[$i]['staff_id'].",'".$res1[$i]['execute_date']."',".$res1[$i]['writ_id'].",". $res1[$i]['writ_ver'].",'".$res2[$j]['execute_date']."',".$res2[$j]['writ_id'].",". $res2[$j]['writ_ver'].",".$res1[$i]['arrear_ver'].",".$_POST['year']." ) "); } elseif($res1[$i]['execute_date'] > $res2[$j]['execute_date']) { PdoDataAccess::runquery(" insert compare_arrear_writs (staff_id ,current_execute_date , current_writ_id , current_writ_ver, prev_execute_date , prev_writ_id , prev_writ_ver , arrear_ver , pay_year ) values (".$res1[$i]['staff_id'].",'".$res1[$i]['execute_date']."',".$res1[$i]['writ_id'].",". $res1[$i]['writ_ver'].",'".$res2[$j]['execute_date']."',".$res2[$j]['writ_id'].",". $res2[$j]['writ_ver'].",".$res1[$i]['arrear_ver'].",".$_POST['year']." ) "); } elseif ( $res1[$i]['execute_date'] < $res2[$j]['execute_date'] ) { break ; } } } */ $res = PdoDataAccess::runquery(" select * from compare_arrear_writs where staff_id = ".$_POST['staff_id']." and pay_year =".$_POST['pay_year']); $writsWhereClause = "" ; for($i=0;$i<count($res);$i++) { $writsWhereClause.='(wsi.writ_id='.$res[$i]['current_writ_id'].' AND wsi.writ_ver='.$res[$i]['current_writ_ver'].' AND wsi.staff_id='.$res[$i]['staff_id'].') OR (wsi.writ_id='.$res[$i]['prev_writ_id'].' AND wsi.writ_ver='.$res[$i]['prev_writ_ver'].' AND wsi.staff_id='.$res[$i]['staff_id'].' ) OR '; } $writsWhereClause = substr($writsWhereClause,0,strlen($writsWhereClause) - 4); $ResITM = PdoDataAccess::runquery(" select distinct wsi.salary_item_type_id , sit.print_title from writ_salary_items wsi inner join salary_item_types sit on wsi.salary_item_type_id = sit.salary_item_type_id where ".$writsWhereClause); echo '<META http-equiv=Content-Type content="text/html; charset=UTF-8" ><body dir="rtl"><center>'; echo "<center><table style='border:2px groove #9BB1CD;border-collapse:collapse;width:80%'> <tr height=200px> <td align='center' style='font-family:b titr;font-size:15px'> مقایسه احکام پرداختی سال ".$_POST['pay_year']." </td> </tr> </table></center>"; echo '<table class="reportGenerator" style="text-align: center ;width:80%!important" cellpadding="4" cellspacing="0"> '; $prior_execute_date = $current_execute_date = $prior_writ_type = $current_writ_type = '' ; $current_writ_items = $prior_writ_items = array(); $width = round(400/count($res)); for($i=0;$i<count($res);$i++) { $current_writ_type .= "<td class='money' width=".$width."px>" . get_WrtitTypeTitle($res[$i]["current_writ_id"], $res[$i]["current_writ_ver"] , $res[$i]["staff_id"])."</td>"; $prior_writ_type .= "<td class='money' >" .get_WrtitTypeTitle($res[$i]["prev_writ_id"], $res[$i]["prev_writ_ver"] , $res[$i]["staff_id"]). "</td>"; $current_execute_date .= "<td class='money' >" . DateModules::miladi_to_shamsi($res[$i]['current_execute_date']) . "</td>"; $prior_execute_date .= "<td class='money' >" . DateModules::miladi_to_shamsi($res[$i]['prev_execute_date']) . "</td>"; for($j=0; $j < count($ResITM); $j++) { if(!isset($current_writ_items[$j])) { $current_writ_items[$j] = ""; $prior_writ_items[$j] = ""; } $val = get_writSalaryItem_value($res[$i]["current_writ_id"], $res[$i]["current_writ_ver"] , $res[$i]["staff_id"], $ResITM[$j]["salary_item_type_id"]); $current_writ_items[$j] .= "<td class='money'>" . ($val == 0 ? "-" : CurrencyModulesclass::toCurrency($val)) . "</td>"; $val = get_writSalaryItem_value($res[$i]["prev_writ_id"], $res[$i]["prev_writ_ver"] , $res[$i]["staff_id"], $ResITM[$j]["salary_item_type_id"]); $prior_writ_items[$j] .= "<td class='money'>" . ($val == 0 ? "-" : CurrencyModulesclass::toCurrency($val)) . "</td>"; } } echo "<tr> <td style='width:80px' rowspan=2>عنوان حکم </td> <td style='width:20px'>قبلي</td> " . $prior_writ_type . " </tr> <tr> <td>فعلی</td> " . $current_writ_type . " </tr>"; echo "<tr> <td style='width:80px' rowspan=2>تاريخ اجراي حکم</td> <td style='width:20px'>قبلي</td> " . $prior_execute_date . " </tr> <tr> <td>فعلی</td> " . $current_execute_date . " </tr>"; for($j=0; $j<count($ResITM); $j++) { echo " <tr> <td style='width:130px' rowspan=2>" . $ResITM[$j]["print_title"] . "</td> <td>قبلي</td> " . $prior_writ_items[$j] . " </tr> <tr> <td>فعلی</td> " . $current_writ_items[$j] . " </tr>"; } echo "</table>" ; die(); }