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