public function execute($pdo = null)
{
//------------------- fill data members --------------------
$this->DataAuditID = PDONULL;
$this->PersonID = isset($_SESSION["USER"]["PersonID"]) ? $_SESSION["USER"]["PersonID"] : "";
$this->SystemID = SYSTEMID;
$this->PageName = isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : $_SERVER['SCRIPT_FILENAME'];
$this->IPAddress = $_SESSION['LIPAddress'];
$this->ActionTime = PDONOW;
$this->QueryString = PdoDataAccess::GetLatestQueryString();
//----------------------------------------------------------
PdoDataAccess::insert("DataAudit", $this, $pdo);
}
function writ_print_list($transcript_no, $last_writ_flag)
{
$staff_onclause = $last_writ_flag ? "s.staff_id=w.staff_id AND s.last_writ_id=w.writ_id AND s.last_writ_ver=w.writ_ver" : "s.staff_id=w.staff_id";
$title = $transcript_no == "all" ? 'wts.title transcripts_title,wts.transcript_id , tbl1.mtid,' : "";
$writ_transcripts_join = $transcript_no == "all" ? '
LEFT OUTER JOIN HRM_writ_transcripts wts ON(w.person_type = wts.person_type AND w.emp_state = wts.emp_state)
LEFT OUTER JOIN (select max(transcript_id) mtid ,person_type , emp_state
from HRM_writ_transcripts group by person_type , emp_state) tbl1
ON(tbl1.person_type = w.person_type AND tbl1.emp_state = w.emp_state)' : "";
$order = $transcript_no == "all" ? ' wts.transcript_id ASC' : "";
$query = " select\n\t\t\t\ts.staff_id,\n\t\t\t\tw.writ_id,\n\t\t\t\tw.writ_ver,\n\t\t\t\tw.staff_id,\n\t\t\t\tw.person_type,\n\t\t\t\tw.onduty_year,\n\t\t\t\tw.onduty_month,\n\t\t\t\tw.onduty_day,\n\t\t\t\tw.writ_type_id,\n\t\t\t\tw.emp_state,\n\t\t\t\tw.emp_mode,\n\t\t\t\tw.ref_letter_date,\n\t\t\t\tw.ref_letter_no,\n\t\t\t\tw.send_letter_no,\n\t\t\t\tw.contract_start_date,\n\t\t\t\tw.contract_end_date,\n\t\t\t\tw.corrective,\n\t\t\t\tw.issue_date,\n\t\t\t\tw.execute_date ,\n\t\t\t\twst.time_limited,\n\t\t\t\tp.sex sex ,w.marital_status , \n\t\t\t\tw.notes,w.family_responsible , \n\t\t\t\tw.pay_date pay_date,\n w.MissionPlace , \n\t\t\t\tmsts.Title marital_status_title,\n\t\t\t\tedulv.Title education_level_title,\n\n\t\t\t\t(w.cur_group-jf.start_group)+1 old_grup,\n\t\t\t\t(w.cur_group - 4) new_grup,\n\t\t\t\tw.cur_group ,\n\t\t\t\tw.description description,\n\t\t\t\tw.included_children_count,\n\t\t\t\tw.children_count,\n\t\t\t\tmiltype.Title as military,miltype.InfoID as militaryID , \n\t\t\t\tw.job_id,\n\t\t\t\tsinclv.Title snc_level,\n\t\t\t\tw.base,\n\t\t\t\tw.ouid,\n\t\t\t\tempstt.Title emp_st,\n\t\t\t\tworktime.Title worktime,\n\t\t\t\tp.sex gnd,\n\t\t\t\tp.pfname ps_pfname,\n\t\t\t\tp.birth_place ps_birth_place,\n\t\t\t\tp.issue_place,\n\t\t\t\tp.plname ps_plname,\n\t\t\t\tp.idcard_no ps_idcard_no,\n\t\t\t\tp.father_name ps_father_name,\n\t\t\t\tp.birth_date ps_birth_date,\n\t\t\t\tp.national_code,\n\t\t\t\tp.military_from_date,\n\t\t\t\tp.military_to_date,\n\t\t\t\tp.military_type,\n\t\t\t\tp.military_duration_day, \n\t\t\t\tp.military_duration ,\n\t\t\t\to.ptitle o_ptitle,\n\t\t\t\to.ouid o_ouid,\n\t\t\t\tparentu.ouid ou_ouid ,\n\t\t\t\tparentu.ptitle ou_ptitle ,\n\t\t\t\to.org_unit_type org_sub_unit_type ,\n\t\t\t\to.ptitle os_ptitle ,\n\t\t\t\tpunit.ouid po_ouid,\n\t\t\t\tpunit.ptitle po_ptitle,\n\t\t\t\tpsubunit.ptitle pos_ptitle ,\n\t\t\t\tpsubunit.ouid pos_ouid ,\n\t\t\t\tj.title j_title,\n\t\t\t\tj.job_group,\n\t\t\t\tsf.ptitle sf_title,\n\t\t\t\tsbs.ptitle sbs_title,\n\t\t\t\twst.print_title wst_title,\n\t\t\t\tpo.title p_title,\n\t\t\t\tpo.post_no p_post_no,\n\t\t\t\tpo.post_id ,\n\t\t\t\tpo.ouid pouid ,\n\t\t\t\tposttype.Title post_type,\n\t\t\t\tcw.ptitle cw_ptitle,\n\t\t\t\tcb.ptitle cb_ptitle,\n\t\t\t\tci.ptitle ci_ptitle,\t\t\n\t\t\t\tsi.ptitle si_ptitle,\n\t\t\t\tsw.ptitle sw_ptitle,\n\t\t\t\tjf.title jf_title,\n\t\t\t\tjsc.title jsc_title,\n\t\t\t\tjc.title jc_title,\n\t\t\t\ts.personel_no,\n\t\t\t\ts.work_start_date,\n\t\t\t\tc.ptitle country_title,\n\t\t\t\tsbs.ptitle sbs_title,\n\t\t\t\tsf.ptitle sf_ptitle,\n\t\t\t\tsf.sfid , s.unitCode ,\n\t\t\t\tj.job_id ,\n\t\t\t\tj.title j_title,\n\t\t\t\twt.title writ_type,\n\t\t\t\t" . $title . "\n\t\t\t\tp.PersonID ,\n\t\t\t\tw.writ_signature_post_title,\n\t\t\t\tw.writ_signature_post_owner ,w.grade ,\n\t\t\t\thistory_only,\n\t\t\t\twst.req_staff_signature, \n\t\t\t\twsi.param8 E_base, \n\t\t\t\twsi.param5 ComputeGrade, \n\t\t\t\tba.SBase S_base, \n\t\t\t\tba.IsarValue I_base ,\n\t\t\t\tba.TashvighiValue T_base ";
$query .= " from staff s\n\t\t\t INNER JOIN HRM_writs w ON (" . $staff_onclause . ")\n\n\t\t\t LEFT OUTER JOIN HRM_writ_subtypes wst\n\t\t\t\t ON ((w.person_type = wst.person_type) AND (w.writ_type_id = wst.writ_type_id) AND (w.writ_subtype_id=wst.writ_subtype_id))\n\t\t\t LEFT OUTER JOIN HRM_writ_types wt\n\t\t\t\t ON ((w.person_type = wt.person_type) AND (w.writ_type_id = wt.writ_type_id))\n\t\t\t LEFT OUTER JOIN HRM_person_educations pe1\n\t\t\t\t ON ((w.education_level = pe1.education_level) AND (w.sfid = pe1.sfid) AND (w.sbid = pe1.sbid) AND (pe1.PersonID = s.PersonID))\n\t\t\t LEFT OUTER JOIN HRM_countries c ON (pe1.country_id = c.country_id)\n\t\t\t LEFT OUTER JOIN HRM_universities u ON ((pe1.university_id = u.university_id) AND (pe1.country_id = u.country_id))\n\t\t\t LEFT OUTER JOIN HRM_study_fields sf ON ((w.sfid = sf.sfid))\n\t\t\t LEFT OUTER JOIN HRM_study_branchs sbs ON ((w.sfid = sbs.sfid)AND(w.sbid=sbs.sbid))\n\t\t\t LEFT OUTER JOIN HRM_persons p ON (s.PersonID = p.PersonID)\n\t\t\t LEFT OUTER JOIN HRM_cities cw ON ((w.work_city_id = cw.city_id) AND (w.work_state_id = cw.state_id))\n\t\t\t " . $writ_transcripts_join . "\n\t\t\t LEFT OUTER JOIN HRM_states sw ON (cw.state_id=sw.state_id)\n\t\t\t LEFT OUTER JOIN HRM_cities cb ON ((p.birth_city_id = cb.city_id) AND (p.birth_state_id = cb.state_id))\n\t\t\t LEFT OUTER JOIN HRM_cities ci ON ((p.issue_city_id=ci.city_id) AND (p.issue_state_id = ci.state_id))\n\t\t\t LEFT OUTER JOIN HRM_states si ON (ci.state_id=si.state_id)\n\t\t\t LEFT OUTER JOIN HRM_position po ON (w.post_id = po.post_id)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units psubunit ON (psubunit.ouid = po.ouid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units punit ON (punit.ouid = psubunit.parent_ouid)\n\t\t\t LEFT OUTER JOIN HRM_job_fields jf ON (po.jfid = jf.jfid)\n\t\t\t LEFT OUTER JOIN HRM_job_subcategory jsc ON ((jf.jsid = jsc.jsid) AND (jf.jcid=jsc.jcid))\n\t\t\t LEFT OUTER JOIN HRM_job_category jc ON (jsc.jcid = jc.jcid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units o ON (w.ouid = o.ouid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units parentu ON (parentu.ouid = o.parent_ouid)\n\t\t\t LEFT OUTER JOIN HRM_jobs j ON (w.job_id = j.job_id) \n\t\t\t \n\t\t\t LEFT OUTER JOIN HRM_writ_salary_items wsi ON w.writ_id = wsi.writ_id AND \n\t\t\t\t\t\t\t\tw.writ_ver = wsi.writ_ver AND w.staff_id = wsi.staff_id AND wsi.salary_item_type_id = 10364 \n\t\t\t\t\t\t\t\t\n\t\t\t LEFT OUTER JOIN (select PersonID , sum(if(ba.BaseType in (6,2,20,21,22,23,24,25,26,27) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) TashvighiValue ,\n\t\t\t\t\t\t\t\t\t\tsum(if(ba.BaseType in (3,4,5,7) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) IsarValue ,\n\t\t\t\t\t\t\t\t\t\tsum(if(ba.BaseType in (1 ) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) SBase\n\n\t\t\t\t\t\t\t\t\tfrom bases ba\n\n\t\t\t\t\t\t\t group by PersonID) ba \n\t\t\t\t\t\t\t ON ba.PersonID = s.PersonID " . QueryHelper::makeBasicInfoJoin(15, "msts", "w.marital_status") . QueryHelper::makeBasicInfoJoin(6, "edulv", "w.education_level") . QueryHelper::makeBasicInfoJoin(10, "miltype", "p.military_type") . QueryHelper::makeBasicInfoJoin(8, "sinclv", "w.science_level") . QueryHelper::makeBasicInfoJoin(3, "empstt", "w.emp_state") . QueryHelper::makeBasicInfoJoin(14, "worktime", "w.worktime_type") . QueryHelper::makeBasicInfoJoin(27, "posttype", "po.post_type");
$where = "1=1";
$whereParam = array();
require_once '../data/writ.data.php';
echo "****";
die;
MakeAdvanceSearchWhere($where, $whereParam);
if (!empty($_REQUEST["writ_id"]) && !empty($_REQUEST["writ_ver"]) && !empty($_REQUEST["staff_id"])) {
$where .= " AND w.writ_id = :wid AND w.writ_ver=:wver AND w.staff_id=:stid";
$whereParam[":wid"] = $_REQUEST["writ_id"];
$whereParam[":wver"] = $_REQUEST["writ_ver"];
$whereParam[":stid"] = $_REQUEST["staff_id"];
} else {
if ($last_writ_flag == 1) {
$where .= " AND w.writ_id = s.last_writ_id AND w.writ_ver=s.last_writ_ver";
}
}
if (!empty($_REQUEST["ouid"])) {
$return = QueryHelper::MK_org_units($_REQUEST["ouid"]);
$where .= " AND " . $return["where"];
$whereParam = array_merge($whereParam, $return["param"]);
}
if ($transcript_no != 1 && $transcript_no != 2) {
$transcript_no = 2;
}
//--------------------------------------------------------------------------
if ($_REQUEST['transcript_no'] == "all") {
$where .= " AND wts.transcript_id not in (117 , 123 , 127 , 44 , 9 , 114 ) ";
}
$query .= " where " . $where;
$query .= " order by p.plname , p.pfname ";
$dt = PdoDataAccess::runquery_fetchMode($query, $whereParam);
echo PdoDataAccess::GetLatestQueryString();
die;
return $dt;
}
LEFT JOIN Basic_Info bi
ON bi.typeid = 8 and bi.InfoID = w.science_level
WHERE (w.history_only = 0 AND s.person_type=1) AND $where
GROUP BY w.staff_id, p.plname, p.pfname
HAVING COUNT(DISTINCT(w.base)) = 1
ORDER BY full_path,plname,pfname " ;
$res = PdoDataAccess::runquery($query) ;
echo PdoDataAccess::GetLatestQueryString() ; die() ;
?>
<html dir='rtl'>
<head>
<style>
.reportGenerator {border-collapse: collapse;border: 1px solid black;font-family: tahoma;font-size: 8pt;
text-align: center;width: 50%;padding: 2px;}
.reportGenerator .header {color: white;font-weight: bold;background-color:#3F5F96}
.reportGenerator td {border: 1px solid #555555;height: 20px;}
</style>
<title>گزارش افرادی که ترفیع نگرفته اند </title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<link rel=stylesheet href="/HumanResources/css/writ.css">
</head>
$row["Sum"] = $BeforeAmount + $row["CreditorAmount"] - $row["DebtorAmount"];
} else {
$row["Sum"] = $prevRow["Sum"] + $row["CreditorAmount"] - $row["DebtorAmount"];
}
return "<div style=direction:ltr>" . number_format($row["Sum"]) . "</div>";
}
$col = $rpg->addColumn("مانده حساب", "CreditorAmount", "TotalRemainRender", $BeforeAmount);
//$col->EnableSummary(true);
$rpg->mysql_resource = $dataTable;
$rpg->page_size = 12;
$rpg->paging = true;
if (!$rpg->excel) {
BeginReport();
//if($_SESSION["USER"]["UserName"] == "admin")
// echo PdoDataAccess::GetLatestQueryString ();
echo "<div style=display:none>" . PdoDataAccess::GetLatestQueryString() . "</div>";
echo "<table style='border:2px groove #9BB1CD;border-collapse:collapse;width:100%'><tr>\r\n\t\t\t\t<td width=60px><img src='/framework/icons/logo.jpg' style='width:120px'></td>\r\n\t\t\t\t<td align='center' style='height:100px;vertical-align:middle;font-family:b titr;font-size:15px'>\r\n\t\t\t\t\tگزارش گردش حساب ها \r\n\t\t\t\t\t <br> " . $_SESSION["accounting"]["BranchName"] . "<br>" . "دوره سال " . $_SESSION["accounting"]["CycleID"] . "</td>\r\n\t\t\t\t<td width='200px' align='center' style='font-family:tahoma;font-size:11px'>تاریخ تهیه گزارش : " . DateModules::shNow() . "<br>";
if (!empty($_POST["fromDate"])) {
echo "<br>گزارش از تاریخ : " . $_POST["fromDate"] . ($_POST["toDate"] != "" ? " - " . $_POST["toDate"] : "");
}
echo "</td></tr></table>";
}
echo $BeforeRemaindar;
$rpg->generateReport();
die;
}
?>
<script>
AccReport_flow.prototype = {
TabID : '<?php
echo $_REQUEST["ExtTabID"];
private function compute_salary_item5_37($writ_rec) {
//param1 : وضعيت تاهل
$query = " SELECT title FROM Basic_Info where typeid = 15 and infoid = " . $writ_rec['marital_status'];
$res = parent::runquery($query);
$this->param1 = $res[0]['title'];
$value = manage_salary_params::get_salaryParam_value("", $writ_rec['person_type'].",101", SPT_HOME_EXTRA, $writ_rec['execute_date'], $writ_rec['marital_status']);
if (!(0 < $value)) { echo PdoDataAccess::GetLatestQueryString(); die();
parent::PushException(UNKNOWN_HOME_EXTRA);
return false;
}
return $value;
}
function copyDoc()
{
PdoDataAccess::runquery("insert into ACC_DocItems\n\t\tSELECT :dst,ItemID,kolID,moinID,TafsiliID,TafsiliID2,DebtorAmount,CreditorAmount,details,locked FROM ACC_DocItems where DocID=:src", array(":src" => $_POST["src_DocID"], ":dst" => $_POST["dst_DocID"]));
echo PdoDataAccess::GetLatestQueryString();
print_r(ExceptionHandler::PopAllExceptions());
echo PdoDataAccess::AffectedRows();
echo Response::createObjectiveResponse(true, "");
die;
}
private static function get_writ_edit_state($Gexecute_date = "", $person_type = "", $writ_id = "", $writ_ver = "", $staff_id = "")
{
$stage1_end_date = OPEN_WRIT_WITHOUT_CALC_END_DATE;
$stage2_end_date = OPEN_WRIT_WITH_CALC_END_DATE;
if ($Gexecute_date == "") {
if ($writ_ver == "") {
$query = "SELECT MAX(writ_ver) writ_ver\n\t\t\t\t\t FROM HRM_writs\n\t\t\t\t\t WHERE writ_id = :wid AND staff_id = :stid";
$temp = PdoDataAccess::runquery($query, array(":wid" => $writ_id, ":stid" => $staff_id));
$writ_ver = $temp[0][0];
}
$query = "SELECT execute_date,person_type\n\t\t\t\t\tFROM HRM_writs\n\t\t\t\t\tWHERE writ_id = :wid AND writ_ver = :wver AND staff_id = :stid";
$temp = PdoDataAccess::runquery($query, array(":wid" => $writ_id, ":wver" => $writ_ver, ":stid" => $staff_id));
echo PdoDataAccess::GetLatestQueryString();
die;
$Gexecute_date = $temp[0]["execute_date"];
$person_type = $temp[0]["person_type"];
}
if ($person_type == "") {
//echo " نوع فرد مشخص نشده است .";
return null;
}
if ($person_type == HR_PROFESSOR && DateModules::CompareDate($Gexecute_date, $stage1_end_date) >= 0 || $person_type == HR_WORKER && DateModules::CompareDate($Gexecute_date, $stage1_end_date) >= 0 || $person_type == HR_EMPLOYEE && DateModules::CompareDate($Gexecute_date, $stage1_end_date) >= 0 || $person_type == HR_CONTRACT && DateModules::CompareDate($Gexecute_date, $stage1_end_date) >= 0 || $person_type == HR_RETIRED) {
$is_auto_writ = true;
} else {
$is_auto_writ = false;
}
if ($person_type == HR_PROFESSOR && DateModules::CompareDate($Gexecute_date, $stage2_end_date) >= 0 || $person_type == HR_WORKER && DateModules::CompareDate($Gexecute_date, $stage2_end_date) >= 0 || $person_type == HR_EMPLOYEE && DateModules::CompareDate($Gexecute_date, $stage2_end_date) >= 0 || $person_type == HR_CONTRACT && DateModules::CompareDate($Gexecute_date, $stage2_end_date) >= 0) {
$is_new_writ = true;
} else {
$is_new_writ = false;
}
if (!$is_auto_writ && !$is_new_writ) {
return 0;
}
if (!$is_auto_writ && $is_new_writ) {
return 1;
}
if ($is_auto_writ && !$is_new_writ) {
return 2;
}
if ($is_auto_writ && $is_new_writ) {
return 3;
}
}
function ShowReport(){
global $tableQuery;
$rpt = new ReportGenerator();
$rpt->mysql_resource = PrepareData();
?>
<html>
<head>
<META http-equiv=Content-Type content="text/html; charset=UTF-8" >
</head>
<body dir=rtl>
<div style="display:none" >
<? echo $tableQuery . "\n----------------------------------------------------\n";?>
<? echo PdoDataAccess::GetLatestQueryString();?>
</div>
<?
function moneyRender($row,$value){
return number_format($value, 0, '.', ',');
}
function pure_paymentRender($row){
return moneyRender(null,$row["pure_payment"]) . "<br>" . moneyRender(null,$row["diff_pure_payment"]);
}
function continus_paymentRender($row){
return moneyRender(null,$row["continus_payment"]) . "<br>" . moneyRender(null,$row["diff_continus_payment"]);
}
function totalRender($row){
return moneyRender(null, $row["normal_insure"] + $row["normal2_insure"] + $row["first_surplus_insure"] + $row["second_surplus_insure"]);
}
function insure_valueRender($row){
return moneyRender(null,$row["insure_value"]) . "<br>" . moneyRender(null,$row["diff_insure_value"]);
}
function org_insure_valueRender($row){
return moneyRender(null,$row["org_insure_value"]) . "<br>" . moneyRender(null,$row["diff_org_insure_value"]);
}
$rpt->addColumn("شماره<br> شناسایی", "staff_id");
$rpt->addColumn("نام خانوادگي", "plname");
$rpt->addColumn("نام", "pfname");
$rpt->addColumn("خالص دريافتي <br>تفاوت", "pure_payment", "pure_paymentRender");
$rpt->addColumn("مستمر<br>تفاوت", "continus_payment", "continus_paymentRender");
$rpt->addColumn("کل", "normal_insure", "totalRender");
$rpt->addColumn("مازاد2", "second_surplus_insure");
$rpt->addColumn("مازاد1", "first_surplus_insure");
$rpt->addColumn("معمولي2", "normal2_insure");
$rpt->addColumn("معمولي", "normal_insure");
$rpt->addColumn("مبلغ بيمه<br>تفاوت", "insure_value", "insure_valueRender");
$rpt->addColumn("سهم سازمان<br>تفاوت", "org_insure_value", "org_insure_valueRender");
$rpt->header_alignment = "center";
$rpt->headerContent = "
<table width=100% border=0 style='font-family:b nazanin;'>
<tr>
<td width=120px><img src='/HumanResources/img/fum_symbol.jpg'></td>
<td align=center style='font-weight:bold'>گزارش کارمندان استفاده کننده از بيمه</td>
<td width=120px>
شماره :
<br>
تاریخ :
" . DateModules::shNow() . "
</td>
</tr>
<tr bgcolor='#BDD3EF' >
<td colspan=3 style='border: 1px solid black;font-weight:bold;color:#15428B;padding-right:5px'>
" . DateModules::GetMonthName($_POST["pay_month"]) . " ماه " . $_POST["pay_year"] . "
</td>
</tr>
</table>";
$rpt->page_size = 30;
$rpt->paging = true;
$rpt->generateReport();
die();
?>
</body>
</html>
<?
}
private function save_to_DataBase()
{
//نوشتن آرايه paymnet_items در فايل
ob_start();
$pure_pay = 0;
//متغيري جهت نگهداري خالص پرداختي
reset($this->payment_items);
foreach ($this->payment_items as $pay_row) {
if ($pay_row['pay_value'] == 0 && $pay_row['get_value'] == 0 && (!empty($pay_row['diff_pay_value']) && $pay_row['diff_pay_value'] == 0) && (!empty($pay_row['diff_get_value']) && $pay_row['diff_get_value'] == 0) && $pay_row['salary_item_type_id'] != SIT_PROFESSOR_RETIRED && $pay_row['salary_item_type_id'] != SIT_STAFF_RETIRED) {
continue;
}
$pay_row['payment_type'] = 1;
if (empty($pay_row['pay_value'])) {
$pay_row['pay_value'] = 0;
}
if (empty($pay_row['get_value'])) {
$pay_row['get_value'] = 0;
}
if (empty($pay_row['param1'])) {
$pay_row['param1'] = 0;
}
if (empty($pay_row['param2'])) {
$pay_row['param2'] = 0;
}
if (empty($pay_row['param3'])) {
$pay_row['param3'] = 0;
}
if (empty($pay_row['param4'])) {
$pay_row['param4'] = 0;
}
if (empty($pay_row['param5'])) {
$pay_row['param5'] = 0;
}
if (empty($pay_row['param6'])) {
$pay_row['param6'] = 0;
}
if (empty($pay_row['param7'])) {
$pay_row['param7'] = 0;
}
if (empty($pay_row['param8'])) {
$pay_row['param8'] = 0;
}
if (empty($pay_row['param9'])) {
$pay_row['param9'] = 0;
}
if (empty($pay_row['diff_param1'])) {
$pay_row['diff_param1'] = 0;
}
if (empty($pay_row['diff_param2'])) {
$pay_row['diff_param2'] = 0;
}
if (empty($pay_row['diff_param3'])) {
$pay_row['diff_param3'] = 0;
}
if (empty($pay_row['diff_param4'])) {
$pay_row['diff_param4'] = 0;
}
if (empty($pay_row['diff_param5'])) {
$pay_row['diff_param5'] = 0;
}
if (empty($pay_row['diff_param6'])) {
$pay_row['diff_param6'] = 0;
}
if (empty($pay_row['diff_param7'])) {
$pay_row['diff_param7'] = 0;
}
if (empty($pay_row['diff_param8'])) {
$pay_row['diff_param8'] = 0;
}
if (empty($pay_row['diff_param9'])) {
$pay_row['diff_param9'] = 0;
}
if (empty($pay_row['diff_get_value'])) {
$pay_row['diff_get_value'] = 0;
}
if (empty($pay_row['diff_pay_value'])) {
$pay_row['diff_pay_value'] = 0;
}
if (!isset($pay_row['diff_value_coef'])) {
$pay_row['diff_value_coef'] = 1;
}
echo '(' . $pay_row['diff_get_value'] . ',' . $pay_row['diff_pay_value'] . ',' . $pay_row['pay_year'] . ',' . $pay_row['pay_month'] . ',' . $pay_row['staff_id'] . ',' . $pay_row['salary_item_type_id'] . ',' . $pay_row['pay_value'] . ',' . $pay_row['get_value'] . ',' . $pay_row['param1'] . ',' . $pay_row['param2'] . ',' . $pay_row['param3'] . ',' . $pay_row['param4'] . ',' . $pay_row['param5'] . ',' . $pay_row['param6'] . ',' . $pay_row['param7'] . ',' . $pay_row['param8'] . ',' . $pay_row['param9'] . ',' . $pay_row['diff_param1'] . ',' . $pay_row['diff_param2'] . ',' . $pay_row['diff_param3'] . ',' . $pay_row['diff_param4'] . ',' . $pay_row['diff_param5'] . ',' . $pay_row['diff_param6'] . ',' . $pay_row['diff_param7'] . ',' . $pay_row['diff_param8'] . ',' . $pay_row['diff_param9'] . ',' . $pay_row['cost_center_id'] . ',' . $pay_row['payment_type'] . ',' . $pay_row['diff_value_coef'] . '),';
echo chr(10);
$pure_pay += $pay_row['pay_value'] + $pay_row['diff_pay_value'] * $pay_row['diff_value_coef'] - $pay_row['get_value'] - $pay_row['diff_get_value'] * $pay_row['diff_value_coef'];
}
/*خطا : حقوق فرد منفي شده است لذا ساير قسمتها براي او انجام نمي شود*/
if ($pure_pay < 0 && !$this->backpay) {
if (!$this->__CALC_NEGATIVE_FICHE) {
$this->log('FAIL', 'حقوق اين شخص به مبلغ ' . CurrencyModulesclass::toCurrency($pure_pay * -1, 'CURRENCY') . ' منفي شده است.');
ob_clean();
return;
} else {
$this->log('FAIL', 'حقوق اين شخص به مبلغ ' . CurrencyModulesclass::toCurrency($pure_pay * -1, 'CURRENCY') . ' منفي شده است.(فيش اين فرد از بخش چاپ فيش در دسترس است، لطفا پس از انجام كنترلهاي لازم فيشهاي منفي را ابطال كنيد)');
}
}
$file_line = str_replace(',,', ',\\N,', ob_get_clean());
//براي اصلاح مقادير null
$file_line = str_replace(',,', ',\\N,', $file_line);
//براي اصلاح مقادير null
$pdo = parent::getPdoObject();
$pdo->beginTransaction();
//if($this->backpay) //در صورتي که محاسبه backpay صورت مي گيرد نيازي به نوشتن ساير فايلها نيست
// return ;
if (!$this->backpay) {
//نوشتن آرايه staff_writs در فايل payment_writs
reset($this->staff_writs);
//$writ_row = '';
if ($this->ExtraWork == 1 && $this->last_month == $this->__MONTH) {
$ptype = 3;
} else {
$ptype = NORMAL;
}
foreach ($this->staff_writs[$this->cur_staff_id] as $writ) {
parent::runquery(" insert into hrmstotal.payment_writs (writ_id,writ_ver,staff_id,pay_year,pay_month,payment_type) values \n\t\t\t\t\t\t\t(" . $writ['writ_id'] . "," . $writ['writ_ver'] . "," . $this->cur_staff_id . "," . $this->__YEAR . "," . $this->last_month . "," . $ptype . ")", array(), $pdo);
if (parent::AffectedRows() == 0) {
$this->log('FAIL', 'خطا در افزودن اطلاعات به جدول احکام مورد استفاده در ماه جاری ');
$pdo->rollBack();
ob_clean();
return;
}
/*$writ_row .= $writ['writ_id'] . ',' .
$writ['writ_ver'] . ',' .
$this->cur_staff_id . ',' .
$this->__YEAR . ',' .
$this->last_month . ',' .
NORMAL . ',' .
$this->__MSG.chr(10);*/
}
//fwrite($this->payment_writs_file_h,$writ_row);
//نوشتن payment در فايل
$payment_row = $this->cur_staff_id . ',' . $this->__YEAR . ',' . $this->__MONTH . ',' . $writ['writ_id'] . ',' . $writ['writ_ver'] . ",'" . $this->month_start . "','" . $this->month_end . "'," . $ptype . ',' . $this->__MSG . ',' . $this->staffRow['bank_id'] . ',' . $this->staffRow['account_no'] . ',' . PAYMENT_STATE_NORMAL . ",'" . DateModules::NowDateTime() . "'";
$file_line2 = str_replace(',,', ',\\N,', $payment_row);
//براي اصلاح مقادير null
$file_line2 = str_replace(',,', ',\\N,', $file_line2);
//براي اصلاح مقادير null
parent::runquery(" insert into payments (staff_id,pay_year,pay_month,writ_id,writ_ver,start_date,end_date,payment_type,message,\n\t\t\t\t\t\t bank_id,account_no,state ,calc_date ) value (" . $file_line2 . ") ", array(), $pdo);
if (parent::AffectedRows() == 0) {
echo PdoDataAccess::GetLatestQueryString();
die;
$this->log('FAIL', 'خطا در افزودن اطلاعات به جدول پرداختها ');
$pdo->rollBack();
ob_clean();
return;
}
}
if ($this->backpay) {
$tblName = "back_payment_items";
} else {
$tblName = "payment_items";
}
$file_line = substr($file_line, 0, strlen($file_line) - 2);
if (strlen($file_line) > 0) {
parent::runquery("insert into " . $tblName . " (diff_get_value, diff_pay_value, pay_year, pay_month, staff_id,\n salary_item_type_id, pay_value, get_value, param1, param2, param3,param4, param5, param6, param7, param8, param9,\n diff_param1,diff_param2,diff_param3,diff_param4,diff_param5,diff_param6,diff_param7,diff_param8,diff_param9,\n cost_center_id, payment_type, diff_value_coef ) values " . $file_line . " ", array(), $pdo);
} else {
$pdo->rollBack();
ob_clean();
return;
}
if (parent::AffectedRows() == 0) {
$this->log('FAIL', 'خطا در افزودن اطلاعات به جدول اقلام حقوقی');
$pdo->rollBack();
ob_clean();
return;
}
$this->log('SUCCESS', $pure_pay);
$pdo->commit();
return true;
}
function PrepareData(&$person_count,$type){
global $GroupCostCenter;
global $GroupPersonType;
global $where;
global $param;
$payment_type = $_POST["PayType"] ;
$pay_year = $_POST["pay_year"];
$pay_month = $_POST["pay_month"];
$where = "";
$param = array(
":year" => $pay_year,
":month" => $pay_month,
":ptype" => $payment_type
);
makeWhere();
$group = makeGroup();
$month_start = DateModules::shamsi_to_miladi($pay_year . "-" . $pay_month . "-1");
if($type == 'TSummary') {
manage_salary_utils::simulate_tax($pay_year, $pay_month, $payment_type);
manage_salary_utils::simulate_bime($pay_year, $pay_month, $payment_type);
$MainRows = PdoDataAccess::runquery("
SELECT pi.salary_item_type_id,
sit.full_title sit_title,
sit.effect_type,
max(pi.pay_year) pay_year,
max(pi.pay_month) pay_month,
sum(pay_value) pay_sum,
sum(diff_pay_value * diff_value_coef) diff_pay_sum,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.value WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.value ELSE get_value END) get_sum,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_get_value * diff_value_coef END) diff_get_sum,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.param2 WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.param2 ELSE pi.param2 END) param2,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_param2 * diff_param2_coef END) diff_param2,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747) THEN tts.param3 WHEN pi.salary_item_type_id IN(9920,145,144) THEN tis.param3 ELSE pi.param3 END) param3,
sum(CASE WHEN pi.salary_item_type_id IN(146,147,148,747,9920,145,144,744) THEN 0 ELSE diff_param3 * diff_param3_coef END) diff_param3,
sum(CASE
WHEN (s.last_retired_pay IS NOT NULL
AND s.last_retired_pay < '$month_start') THEN pi.param3
END) retired_for_org_up30,
sum(CASE
WHEN (s.last_retired_pay IS NOT NULL
AND s.last_retired_pay < '$month_start') THEN diff_param3 * diff_param3_coef
END) diff_retired_for_org_up30,
sum(CASE
WHEN (s.last_retired_pay IS NULL
OR s.last_retired_pay >= '$month_start') THEN pi.param3
END) retired_for_org_dn30,
sum(CASE
WHEN (s.last_retired_pay IS NULL
OR s.last_retired_pay >= '$month_start') THEN diff_param3 * diff_param3_coef
END) diff_retired_for_org_dn30,
sum(pi.param7) param7,
sum(pi.diff_param7 * pi.diff_value_coef) diff_param7,
max(s.person_type) person_type,
max(pi.cost_center_id) cost_center_id,
max(cc.title) cost_center_title,
max(ou.ptitle) unit_title,
max(s.person_type) person_type,
max(w.ouid) ouid
FROM payment_items pi
INNER JOIN payments p
ON(pi.pay_year = p.pay_year AND pi.pay_month = p.pay_month AND pi.staff_id = p.staff_id AND pi.payment_type = p.payment_type
AND pi.pay_year = :year AND pi.pay_month = :month)
INNER JOIN writs w
ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ")
LEFT OUTER JOIN org_new_units ou
ON(w.ouid = ou.ouid)
LEFT OUTER JOIN cost_centers cc
ON (pi.cost_center_id = cc.cost_center_id)
LEFT OUTER JOIN salary_item_types sit
ON (sit.salary_item_type_id = pi.salary_item_type_id AND sit.credit_topic = 1)
LEFT OUTER JOIN staff s
ON (pi.staff_id = s.staff_id)
LEFT OUTER JOIN temp_tax_include_sum tts
ON (tts.staff_id = pi.staff_id AND pi.salary_item_type_id IN(146,147,148,747))
LEFT OUTER JOIN temp_insure_include_sum tis
ON (tis.staff_id = pi.staff_id AND pi.salary_item_type_id IN(9920,145,144,744))
WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where
GROUP BY pi.salary_item_type_id,sit.effect_type,sit.full_title $group
ORDER BY pi.pay_year,pi.pay_month" . $group . ",pi.salary_item_type_id", $param);
//echo PdoDataAccess::GetLatestQueryString();
}
elseif($type == 'Summary') {
$MainRows = PdoDataAccess::runquery(" SELECT pi.salary_item_type_id,
sit.full_title sit_title,
sit.effect_type,
max(pi.pay_year) pay_year,
max(pi.pay_month) pay_month,
sum(pay_value) pay_sum,
sum(diff_pay_value * diff_value_coef) diff_pay_sum,
sum(get_value) get_sum,
sum(diff_get_value * diff_value_coef) diff_get_sum,
sum(param2) param2,
sum(diff_param2 * diff_param2_coef) diff_param2,
sum(param3) param3,
sum(diff_param3 * diff_param3_coef) diff_param3,
sum(CASE
WHEN (s.last_retired_pay IS NOT NULL
AND s.last_retired_pay < '".$month_start."' ) THEN param3
END) retired_for_org_up30,
sum(CASE
WHEN (s.last_retired_pay IS NOT NULL
AND s.last_retired_pay < '".$month_start."' ) THEN diff_param3 * diff_param3_coef
END) diff_retired_for_org_up30,
sum(CASE
WHEN (s.last_retired_pay IS NULL
OR s.last_retired_pay >= '".$month_start."' ) THEN param3
END) retired_for_org_dn30,
sum(CASE
WHEN (s.last_retired_pay IS NULL
OR s.last_retired_pay >= '".$month_start."' ) THEN diff_param3 * diff_param3_coef
END) diff_retired_for_org_dn30,
sum(pi.param7) param7,
sum(pi.diff_param7 * pi.diff_value_coef) diff_param7,
max(s.person_type) person_type,
max(pi.cost_center_id) cost_center_id,
max(cc.title) cost_center_title,
max(ou.ptitle) unit_title,
max(s.person_type) person_type,
max(w.ouid) ouid
FROM payment_items pi
INNER JOIN payments p
ON(pi.pay_year = p.pay_year AND
pi.pay_month = p.pay_month AND
pi.staff_id = p.staff_id AND
pi.payment_type = p.payment_type )
LEFT JOIN writs w
ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ")
LEFT OUTER JOIN org_new_units ou
ON(w.ouid = ou.ouid)
LEFT OUTER JOIN cost_centers cc
ON (pi.cost_center_id = cc.cost_center_id)
LEFT OUTER JOIN salary_item_types sit
ON (sit.salary_item_type_id = pi.salary_item_type_id)
LEFT OUTER JOIN staff s
ON (pi.staff_id = s.staff_id)
WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where
GROUP BY pi.salary_item_type_id,sit.effect_type,sit.full_title $group
ORDER BY pi.pay_year,pi.pay_month" . $group . ",pi.salary_item_type_id", $param );
}
global $MainQuery;
$MainQuery = PdoDataAccess::GetLatestQueryString();
//............... counting persons number .................
$dt = PdoDataAccess::runquery("
select COUNT(DISTINCT pi.staff_id) person_count $group
from payment_items pi
INNER JOIN payments p
ON(pi.pay_year = p.pay_year AND pi.pay_month = p.pay_month AND pi.staff_id = p.staff_id AND pi.payment_type = p.payment_type)
LEFT JOIN writs w
ON(p.writ_id = w.writ_id AND p.writ_ver = w.writ_ver AND p.staff_id = w.staff_id AND w.state=" . WRIT_SALARY . ")
LEFT OUTER JOIN staff s
ON pi.staff_id=s.staff_id
WHERE pi.payment_type = :ptype AND pi.pay_year=:year AND pi.pay_month=:month $where " .
($group != "" ? " group by " . substr($group,1) : "") , $param);
//echo PdoDataAccess::GetLatestQueryString();
$person_count = array();
foreach($dt as $row)
{
$group = "";
$group = $GroupCostCenter ? $row["cost_center_id"] . "_" : "ALL_";
$group .= $GroupPersonType ? $row["person_type"] : "ALL";
$person_count[$group] = $row['person_count'];
}
return $MainRows;
}
if (strpos($key, "to") === 0) {
$where .= " AND " . $prefix . substr($key, 2) . " <= :{$key}";
} else {
$where .= " AND " . $prefix . $key . " = :{$key}";
}
}
$whereParam[":{$key}"] = $value;
}
}
//.....................................
$where = "";
$whereParam = array();
MakeWhere($where, $whereParam);
$query = "select r.*,l.*,p.*,\r\n\t\t\t\tconcat_ws(' ',p1.fname,p1.lname,p1.CompanyName) ReqFullname,\r\n\t\t\t\tconcat_ws(' ',p2.fname,p2.lname,p2.CompanyName) LoanFullname,\r\n\t\t\t\tbi.InfoDesc StatusDesc,\r\n\t\t\t\tBranchName,\r\n\t\t\t\tTotalPayAmount,\r\n\t\t\t\tTotalInstallmentAmount\r\n\t\t\t\t\r\n\t\t\tfrom LON_requests r\r\n\t\t\tjoin LON_ReqParts p on(r.RequestID=p.RequestID AND p.IsHistory='NO')\r\n\t\t\tleft join LON_loans l using(LoanID)\r\n\t\t\tjoin BSC_branches using(BranchID)\r\n\t\t\tleft join BaseInfo bi on(bi.TypeID=5 AND bi.InfoID=StatusID)\r\n\t\t\tleft join BSC_persons p1 on(p1.PersonID=r.ReqPersonID)\r\n\t\t\tleft join BSC_persons p2 on(p2.PersonID=r.LoanPersonID)\r\n\t\t\tleft join (\r\n\t\t\t\tselect RequestID,sum(PayAmount) TotalPayAmount from LON_BackPays\r\n\t\t\t\tgroup by RequestID\t\t\t\r\n\t\t\t)t1 on(r.RequestID=t1.RequestID)\r\n\t\t\tleft join (\r\n\t\t\t\tselect RequestID,sum(InstallmentAmount) TotalInstallmentAmount \r\n\t\t\t\tfrom LON_installments\r\n\t\t\t\tgroup by RequestID\t\t\t\r\n\t\t\t)t2 on(r.RequestID=t2.RequestID)\r\n\t\t\twhere 1=1 " . $where . " \r\n\t\t\t\r\n\t\t\tgroup by r.RequestID\r\n\t\t\torder by " . $_POST["OrderBy"] . " " . $_POST["OrderByDirection"];
$dataTable = PdoDataAccess::runquery($query, $whereParam);
$query = PdoDataAccess::GetLatestQueryString();
for ($i = 0; $i < count($dataTable); $i++) {
$dt = array();
$returnArr = ComputePayments($dataTable[$i]["RequestID"], $dt);
$dataTable[$i]["remainder"] = count($returnArr) > 0 ? $returnArr[count($returnArr) - 1]["TotalRemainder"] : 0;
}
$rpg = new ReportGenerator();
$rpg->excel = !empty($_POST["excel"]);
$rpg->mysql_resource = $dataTable;
function endedRender($row, $value)
{
return $value == "YES" ? "خاتمه" : "جاری";
}
$rpg->addColumn("شماره وام", "RequestID");
$rpg->addColumn("نوع وام", "LoanDesc");
$rpg->addColumn("معرفی کننده", "ReqFullname");
function showReport(){
$levelsDescArr = array(
"l1" => "کل",
"l2" => "معین",
"l3" => "جزء معین",
"l4" => "گروه تفصیلی",
"l5" => "گروه تفصیلی2",
"l6" => "تفصیلی",
"l7" => "تفصیلی2"
);
global $level;
$level = empty($_REQUEST["level"]) ? "l1" : $_REQUEST["level"];
$rpg = new ReportGenerator();
$whereParam = array();
$where = "";
$group = "cc.level1";
$select = "select
sum(di.DebtorAmount) bdAmount,
sum(di.CreditorAmount) bsAmount,
b1.BlockDesc level1Desc,
b2.BlockDesc level2Desc,
b3.BlockDesc level3Desc,
b.InfoDesc TafsiliTypeDesc,
t.TafsiliDesc TafsiliDesc,
bi2.InfoDesc TafsiliTypeDesc2,
t2.TafsiliDesc TafsiliDesc2,
cc.level1,cc.level2,cc.level3,di.TafsiliType,di.TafsiliType2,di.TafsiliID,di.TafsiliID2,
t.StartCycleDebtor,
t.StartCycleCreditor
";
$from = " from ACC_DocItems di
join ACC_docs d using(DocID)
join ACC_CostCodes cc using(CostID)
join ACC_blocks b1 on(level1=b1.BlockID)
left join ACC_blocks b2 on(level2=b2.BlockID)
left join ACC_blocks b3 on(level3=b3.BlockID)
left join BaseInfo b on(TypeID=2 AND di.TafsiliType=InfoID)
left join ACC_tafsilis t using(TafsiliID)
left join BaseInfo bi2 on(bi2.TypeID=2 AND di.TafsiliType2=bi2.InfoID)
left join ACC_tafsilis t2 on(t2.TafsiliID=di.TafsiliID2)
left join (
select CycleID,CostID,sum(DebtorAmount) StartCycleDebtor, sum(CreditorAmount) StartCycleCreditor
from ACC_DocItems join ACC_docs using(DocID)
where DocType=1
group by CostID
)t on(d.CycleID=t.CycleID AND di.CostID=t.CostID)
";
if($level >= "l1")
$rpg->addColumn("کل", "level1Desc",$level =="l1" ? "levelRender" : "");
if($level >= "l2")
$rpg->addColumn("معین", "level2Desc", $level =="l2" ? "levelRender" : "");
if($level >= "l3")
$rpg->addColumn("جزء معین", "level3Desc", $level =="l3" ? "levelRender" : "");
if($level == "l4" || $level == "l6")
$rpg->addColumn("گروه تفصیلی", "TafsiliTypeDesc", $level =="l4" ? "levelRender" : "");
if($level == "l4" || $level == "l7")
$rpg->addColumn("گروه تفصیلی2", "TafsiliTypeDesc2", $level == "l4" ? "levelRender2" : "");
if($level == "l6")
$rpg->addColumn("تفصیلی", "TafsiliDesc", "showDocs");
if($level == "l7")
$rpg->addColumn("تفصیلی2", "TafsiliDesc2", "showDocs2");
switch($level)
{
case "l2" : $group .= ",cc.level2"; break;
case "l3" : $group .= ",cc.level3"; break;
case "l4" : $group .= ",di.TafsiliType"; break;
case "l5" : $group .= ",di.TafsiliType2"; break;
case "l6" : $group .= ",di.TafsiliID"; break;
case "l7" : $group .= ",di.TafsiliID2"; break;
}
function levelRender($row, $value){
global $level;
if($value == "")
$value = "-----";
return "<a onclick=changeLevel('" . $level . "','".
($level >= "l1" ? $row["level1"] : "") . "','" .
($level >= "l2" ? $row["level2"] : "") . "','" .
($level >= "l3" ? $row["level3"] : ""). "','" .
($level == "l4" ? $row["TafsiliType"] : ""). "','" .
($level == "l5" ? $row["TafsiliType2"] : ""). "') "."
href='javascript:void(0);'>" . $value . "</a>";
}
function levelRender2($row, $value){
global $level;
return "<a onclick=changeLevel('l5','".
($level >= "l1" ? $row["level1"] : "") . "','" .
($level >= "l2" ? $row["level2"] : "") . "','" .
($level >= "l3" ? $row["level3"] : ""). "','" .
($level == "l4" ? $row["TafsiliType"] : ""). "','" .
($level == "l5" ? $row["TafsiliType2"] : ""). "') "."
href='javascript:void(0);'>" . $value . "</a>";
}
function showDocs($row, $value){
if($value == "")
$value = "-----";
return "<a onclick=\"window.open('flow.php?show=true&taraz=true".
"&level1=" . $row["level1"] .
"&level2=" . $row["level2"] .
"&level3=" . $row["level3"] .
"&TafsiliType=" . $row["TafsiliType"] .
"&TafsiliID=" . $row["TafsiliID"] .
(!empty($_REQUEST["fromDate"]) ? "&fromDate=" . $_REQUEST["fromDate"] : "") .
(!empty($_REQUEST["toDate"]) ? "&toDate=" . $_REQUEST["toDate"] : "") .
(!empty($_REQUEST["fromLocalNo"]) ? "&fromLocalNo=" . $_REQUEST["fromLocalNo"] : "") .
(!empty($_REQUEST["toLocalNo"]) ? "&toLocalNo=" . $_REQUEST["toLocalNo"] : "") .
(!empty($_REQUEST["BranchID"]) ? "&BranchID=" . $_REQUEST["BranchID"] : "") .
(!empty($_REQUEST["IncludeRaw"]) ? "&IncludeRaw=1" : "") .
"');\" href=javascript:void(0)>" . $value . "</a>";
}
function showDocs2($row, $value){
if($value == "")
$value = "-----";
return "<a onclick=\"window.open('flow.php?taraz=true&show=true&taraz=true".
"&level1=" . $row["level1"] .
"&level2=" . $row["level2"] .
"&level3=" . $row["level3"] .
"&TafsiliID2=" . $row["TafsiliID2"] .
(!empty($_POST["fromDate"]) ? "&fromDate=" . $_POST["fromDate"] : "") .
(!empty($_POST["toDate"]) ? "&toDate=" . $_POST["toDate"] : "") .
(!empty($_REQUEST["BranchID"]) ? "&BranchID=" . $_REQUEST["BranchID"] : "") .
(!empty($_REQUEST["IncludeRaw"]) ? "&IncludeRaw=1" : "") .
"');\" href=javascript:void(0)>" . $value . "</a>";
}
function MakeWhere(&$where, &$whereParam){
if(!isset($_REQUEST["IncludeRaw"]))
{
$where .= " AND d.DocStatus != 'RAW'";
}
if(isset($_REQUEST["level1"]))
{
if($_REQUEST["level1"] == "")
$where .= " AND cc.level1 is null";
else
{
$where .= " AND cc.level1=:l1";
$whereParam[":l1"] = $_REQUEST["level1"];
}
}
if(isset($_REQUEST["level2"]))
{
if($_REQUEST["level2"] == "")
$where .= " AND cc.level2 is null";
else
{
$where .= " AND cc.level2=:l2";
$whereParam[":l2"] = $_REQUEST["level2"];
}
}
if(isset($_REQUEST["level3"]))
{
if($_REQUEST["level3"] == "")
$where .= " AND cc.level3 is null";
else
{
$where .= " AND cc.level3=:l3";
$whereParam[":l3"] = $_REQUEST["level3"];
}
}
if(isset($_REQUEST["tafsiligroup"]))
{
if($_REQUEST["tafsiligroup"] == "")
$where .= " AND di.TafsiliType is null";
else
{
$where .= " AND di.TafsiliType=:tt";
$whereParam[":tt"] = $_REQUEST["tafsiligroup"];
}
}
//..............................................
if(!empty($_POST["level1s"]))
{
$level1s = preg_replace("/[^0-9,]+/", "", $_POST["level1s"]);
$where .= " AND cc.level1 in( " . $level1s . ")";
}
//----------------------------------------------
if(!empty($_POST["level2s"]))
{
$level2s = preg_replace("/[^0-9,]+/", "", $_POST["level2s"]);
$level2s = substr($level2s, 0, strlen($level2s)-1);
$where .= " AND cc.level2 in( " . $level2s . ")";
}
//----------------------------------------------
if(!empty($_POST["level3s"]))
{
$level3s = preg_replace("/[^0-9,]+/", "", $_POST["level3s"]);
$level3s = substr($level3s, 0, strlen($level3s)-1);
$where .= " AND cc.level3 in( " . $level3s . ")";
}
//----------------------------------------------
if(!empty($_POST["TafsiliGroup"]))
{
$where .= " AND (di.TafsiliType=:tt or di.TafsiliType2=:tt)";
$whereParam[":tt"] = $_POST["TafsiliGroup"];
}
/*if(!empty($_POST["TafsiliGroup2"]))
{
$where .= " AND di.TafsiliType2=:tt2";
$whereParam[":tt2"] = $_POST["TafsiliGroup2"];
}*/
if(!empty($_POST["TafsiliID"]))
{
$where .= " AND (di.TafsiliID=:tid or di.TafsiliID2=:tid)";
$whereParam[":tid"] = $_POST["TafsiliID"];
}
/*if(!empty($_POST["TafsiliID2"]))
{
$where .= " AND di.TafsiliID2=:tid2";
$whereParam[":tid2"] = $_POST["TafsiliID2"];
}*/
if(!empty($_REQUEST["fromLocalNo"]))
{
$where .= " AND d.LocalNo >= :lo1 ";
$whereParam[":lo1"] = $_REQUEST["fromLocalNo"];
}
if(!empty($_REQUEST["toLocalNo"]))
{
$where .= " AND d.LocalNo <= :lo2 ";
$whereParam[":lo2"] = $_REQUEST["toLocalNo"];
}
if(!empty($_POST["fromDate"]))
{
$where .= " AND d.DocDate >= :q1 ";
$whereParam[":q1"] = DateModules::shamsi_to_miladi($_POST["fromDate"], "-");
}
if(!empty($_POST["toDate"]))
{
$where .= " AND d.DocDate <= :q2 ";
$whereParam[":q2"] = DateModules::shamsi_to_miladi($_POST["toDate"], "-");
}
}
$where = "";
$whereParam = array();
if(!empty($_POST["BranchID"]))
{
$where .= " AND d.BranchID=:b";
$whereParam[":b"] = $_POST["BranchID"];
}
MakeWhere($where, $whereParam);
$query = $select . $from . " where
d.CycleID=" . $_SESSION["accounting"]["CycleID"] . $where;
$query .= $group != "" ? " group by " . $group : "";
$query .= " order by b1.BlockCode,b2.BlockCode,b3.BlockCode";
$dataTable = PdoDataAccess::runquery($query, $whereParam);
//..........................................................................
function dateRender($row, $val){
return DateModules::miladi_to_shamsi($val);
}
function moneyRender($row, $val) {
return number_format($val, 0, '.', ',');
}
function bdremainRender($row){
$v = $row["bdAmount"] - $row["bsAmount"];
return $v < 0 ? 0 : number_format($v);
}
function bsremainRender($row){
$v = $row["bsAmount"] - $row["bdAmount"];
return $v < 0 ? 0 : number_format($v);
}
$col = $rpg->addColumn("بدهکار", "StartCycleDebtor", "moneyRender");
$col->GroupHeader = "حساب ابتدای دوره";
$col->EnableSummary(true);
$col = $rpg->addColumn("بستانکار", "StartCycleCreditor", "moneyRender");
$col->GroupHeader = "حساب ابتدای دوره";
$col->EnableSummary(true);
$col = $rpg->addColumn("بدهکار", "bdAmount" , "moneyRender");
$col->GroupHeader = "گردش طی دوره";
$col->EnableSummary();
$col = $rpg->addColumn("بستانکار", "bsAmount", "moneyRender");
$col->GroupHeader = "گردش طی دوره";
$col->EnableSummary();
$col = $rpg->addColumn("مانده بدهکار", "bdAmount", "bdremainRender");
$col->GroupHeader = "مانده پایان دوره";
$col->EnableSummary(true);
$col = $rpg->addColumn("مانده بستانکار", "bsAmount", "bsremainRender");
$col->GroupHeader = "مانده پایان دوره";
$col->EnableSummary(true);
if(!$rpg->excel)
{
BeginReport();
echo "<div style=display:none>" . PdoDataAccess::GetLatestQueryString() . "</div>";
echo "<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'>
تراز دفتر
".$levelsDescArr[$level]." <br> ".
$_SESSION["accounting"]["BranchName"]. "<br>" . "دوره سال " .
$_SESSION["accounting"]["CycleID"] .
"</td>
<td width='200px' align='center' style='font-family:tahoma;font-size:11px'>تاریخ تهیه گزارش : "
. DateModules::shNow() . "<br>";
if(!empty($_POST["fromDate"]))
{
echo "<br>گزارش از تاریخ : " . $_POST["fromDate"] . ($_POST["toDate"] != "" ? " - " . $_POST["toDate"] : "");
}
echo "</td></tr></table>";
}
$rpg->mysql_resource = $dataTable;
$rpg->generateReport();
?>
<script>
function changeLevel(curlevel,level1,level2,level3,TafsiliGroup,TafsiliID)
{
nextLevel = (curlevel.substring(1)*1);
nextLevel = (nextLevel == 4 || nextLevel == 5) ? nextLevel+2 : nextLevel+1
var form = document.getElementById("subForm");
form.action = "taraz.php?show=true&level=" + "l" + nextLevel;
if(curlevel >= "l1")
form.action += "&level1=" + level1;
if(curlevel >= "l2")
form.action += "&level2=" + level2;
if(curlevel >= "l3")
form.action += "&level3=" + level3;
if(curlevel >= "l4")
form.action += "&tafsiligroup=" + TafsiliGroup;
if(curlevel >= "l5")
form.action += "&TafsiliID=" + TafsiliID;
form.submit();
return;
}
</script>
<form id="subForm" method="POST" target="_blank">
<input type="hidden" name="fromDate" value="<?= !empty($_REQUEST["fromDate"]) ? $_REQUEST["fromDate"] : "" ?>">
<input type="hidden" name="toDate" value="<?= !empty($_REQUEST["toDate"]) ? $_REQUEST["toDate"] : "" ?>">
<input type="hidden" name="fromLocalNo" value="<?= !empty($_REQUEST["fromLocalNo"]) ? $_REQUEST["fromLocalNo"] : "" ?>">
<input type="hidden" name="toLocalNo" value="<?= !empty($_REQUEST["toLocalNo"]) ? $_REQUEST["toLocalNo"] : "" ?>">
<input type="hidden" name="IncludeRaw" value="<?= !empty($_REQUEST["IncludeRaw"]) ? $_REQUEST["IncludeRaw"] : "" ?>">
<input type="hidden" name="BranchID" value="<?= !empty($_REQUEST["BranchID"]) ? $_REQUEST["BranchID"] : "" ?>">
<input type="hidden" name="level1s" id="level1s" value="<?= $_POST["level1s"] ?>">
<input type="hidden" name="level2s" id="level2s" value="<?= $_POST["level2s"] ?>">
<input type="hidden" name="level3s" id="level3s" value="<?= $_POST["level3s"] ?>">
</form>
<?
die();
}
