PHP PdoDataAccess::GetLatestQueryString方法代码示例

 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>		
<?
}

//.........这里部分代码省略.........
         }
         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;
 }

//.........这里部分代码省略.........
				
		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 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();
}