function LoanReport()
{
$BranchID = $_POST["BranchID"];
$blockID = $_POST["BlockID"];
$StartDate = $_POST["StartDate"];
$EndDate = $_POST["EndDate"];
$TafsiliID = $_POST["TafsiliID"];
$where = "";
$param = array($_SESSION["accounting"]["CycleID"], $blockID);
if ($BranchID != "") {
$where .= " AND BranchID =?";
$param[] = $BranchID;
}
if ($TafsiliID != "") {
$where .= " AND (TafsiliID=? or TafsiliID2=?)";
$param[] = $TafsiliID;
$param[] = $TafsiliID;
}
if ($StartDate != "") {
$where .= " AND DocDate >=?";
$param[] = DateModules::shamsi_to_miladi($StartDate, "-");
}
if ($EndDate != "") {
$where .= " AND DocDate <=?";
$param[] = DateModules::shamsi_to_miladi($EndDate, "-");
}
$dt = PdoDataAccess::runquery("\r\n\t\tselect cc.*,concat_ws(' - ',b1.BlockDesc,b2.BlockDesc,b3.BlockDesc) CostDesc,\r\n\t\t\t\tsum(CreditorAmount - DebtorAmount ) remain\r\n\t\tfrom ACC_DocItems di\r\n\t\t\tjoin ACC_docs using(DociD)\r\n\t\t\tjoin ACC_CostCodes cc using(CostID)\r\n\t\t\tjoin ACC_blocks b1 on(cc.level1=b1.BlockID)\r\n\t\t\tjoin ACC_blocks b2 on(cc.level2=b2.BlockID)\r\n\t\t\tleft join ACC_blocks b3 on(cc.level3=b3.BlockID)\r\n\t\t\t\r\n\t\twhere CycleID=? AND cc.level2 = ? " . $where . "\r\n\t\t\r\n\t\tgroup by di.CostID", $param);
//print_r(ExceptionHandler::PopAllExceptions());
function moneyRender($row, $val)
{
$BranchID = $_POST["BranchID"];
$StartDate = $_POST["StartDate"];
$EndDate = $_POST["EndDate"];
$TafsiliID = $_POST["TafsiliID"];
$params = "IncludeRaw=true&show=true";
$params .= $BranchID != "" ? "&BranchID=" . $BranchID : "";
$params .= $TafsiliID != "" ? "&TafsiliID=" . $TafsiliID : "";
$params .= $row["level1"] != "" ? "&level1=" . $row["level1"] : "";
$params .= $row["level2"] != "" ? "&level2=" . $row["level2"] : "";
$params .= $row["level3"] != "" ? "&level3=" . $row["level3"] : "";
$params .= $StartDate != "" ? "&fromDate=" . $StartDate : "";
$params .= $EndDate != "" ? "&toDate=" . $EndDate : "";
return "<a target=_blank href='../accounting/report/flow.php?" . $params . "'>" . number_format($val) . "</a>";
}
$rpg = new ReportGenerator();
$rpg->mysql_resource = $dt;
$col = $rpg->addColumn("کد حساب", "CostDesc");
$col = $rpg->addColumn("مانده حساب", "remain", "moneyRender");
$rpg->generateReport();
die;
}
function GetWorkTimeExcel(){
$param = array();
MakeWhere($where, $param);
$rpt = new ReportGenerator();
$rpt->mysql_resource = PrepareData();
$rpt->addColumn("شماره شناسایی", "staff_id");
$rpt->addColumn("نام خانوادگي", "plname");
$rpt->addColumn("نام", "pfname");
$rpt->addColumn("واحد اصلی محل خدمت", "unit_title");
$rpt->addColumn("واحد فرعی محل خدمت", "sub_unit_title");
$rpt->addColumn("کارکرد", "work_time");
$rpt->excel = true;
$rpt->generateReport();
die();
}
$col->align = "center";
$col->EnableSummary();
$rpg->mysql_resource = $dataTable;
$percentGovermental = array(0, 0);
foreach ($dataTable as $row) {
if ($row["IsGovermental"] == "YES") {
$percentGovermental[0] += round($row["shareCount"] * 100 / $TotalShare, 2);
} else {
$percentGovermental[1] += round($row["shareCount"] * 100 / $TotalShare, 2);
}
}
$percentGovermental = "<table width=100% style=font-weight:bold;font-family:tahoma;font-size:13px><tr>\r\n\t<td align=center>جمع درصد سهم بخش دولتی : " . $percentGovermental[0] . " %</td>\r\n\t<td align=center>جمع درصد سهم بخش خصوصی : " . $percentGovermental[1] . " %</td></tr></table>";
if (isset($_REQUEST["print"])) {
echo '<meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>';
echo "<body dir=rtl>";
$rpg->generateReport();
echo "<br>" . $percentGovermental . "<br>";
echo "</body>";
die;
}
?>
<script>
ShareReport.prototype = {
TabID : '<?php
echo $_REQUEST["ExtTabID"];
?>
',
address_prefix : "<?php
echo $js_prefix_address;
?>
",
$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"];
?>
',
address_prefix : "<?php
echo $js_prefix_address;
?>
",
get : function(elementID){
function GetChangesExcel(){
$param = array();
$where = "";
$param[":year"] = $_POST["pay_year"];
$param[":month"] = $_POST["pay_month"];
$param[":pt"] = $_POST["PayType"];
$pre_year = $_POST["pay_year"] ;
$pre_month = $_POST["pay_month"] - 1 ;
if($pre_month == 0){
$pre_year--;
$pre_month = 12 ;
}
$param[":preyear"] = $pre_year;
$param[":premonth"] = $pre_month;
MakeWhere($where, $param);
$dataTable = PdoDataAccess::runquery_fetchMode("
select
'' date,
concat(plname,' ',pfname) name,
s.staff_id ,
bi.Title emp_mode_desc,
pit1.get_value val1,
if( pit2.param1 - pit1.param1>0 , pit2.param1 - pit1.param1 , 0 ) normal_insure_inc,
if( pit2.param8 - pit1.param8>0 , pit2.param8 - pit1.param8 , 0 ) normal2_insure_inc,
if( pit2.param2 - pit1.param2>0 , pit2.param2 - pit1.param2 , 0 ) first_surplus_insure_inc,
if( pit2.param3 - pit1.param3>0 , pit2.param3 - pit1.param3 , 0 ) second_surplus_insure_inc,
if( pit2.param1 - pit1.param1>0 , pit2.param1 - pit1.param1 , 0 ) +
if(pit2.param2 - pit1.param2>0 , pit2.param2 - pit1.param2 , 0 ) +
if(pit2.param3 - pit1.param3>0 , pit2.param3 - pit1.param3 , 0 ) insure_inc_count,
bi2.Title emp_mode_desc2,
pit2.get_value val2,
if( pit2.param1 - pit1.param1>0 , 0 , pit1.param1 - pit2.param1 ) normal_insure_dec,
if( pit2.param8 - pit1.param8>0 , 0 , pit1.param8 - pit2.param8 ) normal2_insure_dec,
if( pit2.param2 - pit1.param2>0 , 0 , pit1.param2 - pit2.param2 ) first_surplus_insure_dec,
if( pit2.param3 - pit1.param3>0 , 0 , pit1.param3 - pit2.param3 ) second_surplus_insure_dec,
if( pit2.param1 - pit1.param1>0 , 0 , pit1.param1 - pit2.param1 ) +
if( pit2.param2 - pit1.param2>0 , 0 , pit1.param2 - pit2.param2 ) +
if( pit2.param3 - pit1.param3>0 , 0 , pit1.param3 - pit2.param3 ) insure_dec_count
from staff s
INNER JOIN persons per ON (per.personID = s.personID)
LEFT OUTER JOIN payments p1
ON p1.pay_year = :preyear AND p1.pay_month = :premonth AND p1.payment_type = :pt AND s.staff_id = p1.staff_id
LEFT OUTER JOIN payments p2
ON p2.pay_year = :year AND p2.pay_month = :month AND p2.payment_type = :pt AND s.staff_id = p2.staff_id
LEFT OUTER JOIN writs w1
ON (w1.writ_id = p1.writ_id AND w1.writ_ver = p1.writ_ver AND w1.staff_id = p1.staff_id )
LEFT OUTER JOIN writs w2
ON (w2.writ_id = p2.writ_id AND w2.writ_ver = p2.writ_ver AND w2.staff_id = p2.staff_id )
LEFT OUTER JOIN payment_items pit1
ON ((p1.staff_id = pit1.staff_id) AND (p1.pay_year = pit1.pay_year) AND
(p1.pay_month = pit1.pay_month) AND (p1.payment_type = pit1.payment_type))
AND (pit1.salary_item_type_id IN (143,38) )
LEFT OUTER JOIN payment_items pit2
ON ((p2.staff_id = pit2.staff_id) AND (p2.pay_year = pit2.pay_year) AND
(p2.pay_month = pit2.pay_month) AND (p2.payment_type = pit2.payment_type))
AND (pit2.salary_item_type_id IN (143,38) )
left join Basic_Info bi on(bi.TypeID=4 AND bi.InfoID=w1.emp_mode)
left join Basic_Info bi2 on(bi2.TypeID=4 AND bi2.InfoID=w2.emp_mode)
where (pit1.get_value > 0 OR pit2.get_value >0 ) AND
(pit1.param1 != pit2.param1 OR
pit1.param8 != pit2.param8 OR
pit1.param2 != pit2.param2 OR
pit1.param3 != pit2.param3 OR
w1.emp_mode != w2.emp_mode ) $where ", $param);
//echo PdoDataAccess::GetLatestQueryString();die();
$rpt = new ReportGenerator();
$rpt->mysql_resource = $dataTable;
$rpt->addColumn("تاريخ", "date");
$rpt->addColumn('نام و نام خانوادگي', "name");
$rpt->addColumn("كد اصلي بيمه شده", "staff_id");
$rpt->addColumn("وضعيت استخدامي قبل", "emp_mode_desc");
$rpt->addColumn("مبلغ قبلي", "val1");
$rpt->addColumn("افزايش تبعي1", "normal_insure_inc");
$rpt->addColumn("افزايش تبعي 1 جديد", "normal2_insure_inc");
$rpt->addColumn("افزايش تبعي2", "first_surplus_insure_inc");
$rpt->addColumn("افزايش تبعي3", "second_surplus_insure_inc");
$rpt->addColumn("جمع افزايش", "insure_inc_count");
$rpt->addColumn("وضعيت استخدامي جديد", "emp_mode_desc2");
$rpt->addColumn("'مبلغ جديد", "val2");
$rpt->addColumn("كاهش تبعي1", "normal_insure_dec");
$rpt->addColumn("كاهش تبعي 1 جدید", "normal2_insure_dec");
$rpt->addColumn("كاهش تبعي2", "first_surplus_insure_dec");
$rpt->addColumn("كاهش تبعي3", "second_surplus_insure_dec");
$rpt->addColumn("جمع كاهش", "insure_dec_count");
$rpt->excel = true;
$rpt->generateReport();
die();
}
die;
}
if (!empty($_REQUEST["NTC_EXCEL"])) {
$data = GetDelayedInstallments(true);
$rpt = new ReportGenerator();
$rpt->rowNumber = false;
$rpt->excel = true;
$rpt->mysql_resource = $data;
$rpt->addColumn("PID", "LoanPersonID");
$rpt->addColumn("کد وام", "RequestID");
$rpt->addColumn("وام گیرنده", "LoanPersonName");
$rpt->addColumn("سررسید", "InstallmentDate");
$rpt->addColumn("تاخیر", "ForfeitDays");
$rpt->addColumn("مبلغ قسط", "InstallmentAmount");
$rpt->addColumn("قابل پرداخت", "remainder");
$rpt->generateReport();
die;
}
$dg = new sadaf_datagrid("dg", $js_prefix_address . "../request/request.data.php?task=GetDelayedInstallments", "grid_div");
$dg->addColumn("", "PartID", "", true);
$col = $dg->addColumn("کد وام", "RequestID", "");
$col->width = 50;
$col = $dg->addColumn("وام گیرنده", "LoanPersonName");
$col = $dg->addColumn("سررسید", "InstallmentDate", GridColumn::ColumnType_date);
$col->width = 80;
$col = $dg->addColumn("تاخیر", "ForfeitDays", GridColumn::ColumnType_date);
$col->width = 80;
$col = $dg->addColumn("مبلغ قسط", "InstallmentAmount", GridColumn::ColumnType_money);
$col->width = 150;
$col = $dg->addColumn("قابل پرداخت", "remainder", GridColumn::ColumnType_money);
$col->width = 100;
function ShowReport()
{
$query = "SELECT c.*,concat_ws(' ',p1.fname,p1.lname,p1.CompanyName) fullname,\r\n\t\t\tconcat_ws(' ',p2.fname,p2.lname,p2.CompanyName)fullname2,bf.InfoDesc ContractTypeDesc \r\n\t\tFROM CNT_contracts c \r\n\t\t\tjoin BaseInfo bf on(bf.TypeID=18 AND bf.InfoID=ContractType)\r\n\t\t\tleft join CNT_ContractItems ci using(ContractID)\r\n\t\t\tleft join BSC_persons p1 on(c.PersonID=p1.PersonID)\r\n\t\t\tleft join BSC_persons p2 on(c.PersonID2=p2.PersonID)\r\n\t\twhere 1=1\r\n\t";
$params = array();
$index = 0;
$itemsWhere = "1=0 ";
$keys = array_keys($_POST);
foreach ($keys as $key) {
if (empty($_POST[$key])) {
continue;
}
switch ($key) {
case "TemplateID":
case "ContractType":
case "PersonID":
case "PersonID2":
$query .= " AND c." . $key . "=:p" . $index;
$params[":p" . $index++] = $_POST[$key];
break;
case "FromContractAmount":
case "FromStartDate":
case "FromEndDate":
$query .= " AND c." . $key . " <= :p" . $index;
$params[":p" . $index++] = $_POST[$key];
break;
case "ToContractAmount":
case "ToStartDate":
case "ToEndDate":
$query .= " AND c." . $key . " >= :p" . $index;
$params[":p" . $index++] = $_POST[$key];
break;
case "description":
$query .= " AND c." . $key . " like :p" . $index;
$params[":p" . $index++] = "%" . $_POST[$key] . "%";
break;
default:
if (strpos($key, "TplItem_") !== false) {
$key = substr($key, 8);
$itemsWhere .= " OR if(ci.TemplateItemID=" . $key . ",ItemValue like :p" . $index . ",1=0)";
$params[":p" . $index++] = "%" . $_POST["TplItem_" . $key] . "%";
}
}
}
$data = PdoDataAccess::runquery($query . " AND ({$itemsWhere})" . " group by c.ContractID", $params);
//echo PdoDataAccess::GetLatestQueryString();
$rpg = new ReportGenerator();
$rpg->excel = !empty($_POST["excel"]);
$rpg->mysql_resource = $data;
function dateRender($row, $val)
{
return DateModules::miladi_to_shamsi($val);
}
function moneyRender($row, $value)
{
return number_format($value);
}
function ContractRender($row, $value)
{
return "<a target=blank href=../contract/PrintContract.php?ContractID=" . $value . ">" . $value . "</a>";
}
$rpg->addColumn("شماره قرارداد", "ContractID", "ContractRender");
$rpg->addColumn("تاریخ شروع", "StartDate", "dateRender");
$rpg->addColumn("تاریخ پایان", "EndDate", "dateRender");
$rpg->addColumn("نوع قرارداد", "ContractTypeDesc");
$rpg->addColumn("طرف اول قرارداد", "fullname");
$rpg->addColumn("طرف دوم قرارداد", "fullname2");
$rpg->addColumn("مبلغ قرارداد", "ContractAmount", "moneyRender");
if (!$rpg->excel) {
echo '<META http-equiv=Content-Type content="text/html; charset=UTF-8" ><body dir="rtl">';
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</td>\r\n\t\t\t\t<td width='200px' align='center' style='font-family:tahoma;font-size:11px'>تاریخ تهیه گزارش : " . DateModules::shNow() . "<br>";
echo "</td></tr></table>";
}
$rpg->generateReport();
die;
die;
}
?>
</b></td>
<td></td>
</tr>
</table>
<? if(count($temp) >0) {
$rpt->generateReport(); ?>
<div style="width: 100%; padding-top: 20px; padding-bottom: 20px; height: 120px;">
<div class="footer"> <br>جمع کل : <?php
echo number_format($SumAmount);
?>
ریال
<div style="float:left;width: 250px">نام و نام خانوادگی :<br>امضاء</div><br><br><br>
</div>
</div>
<?} if(count($dt) > 0){
$rpt2->generateReport(); ?>
<div style="width: 100%; padding-top: 20px; padding-bottom: 20px; height: 120px;">
<div class="footer">
<br>خواهشمند است وجه چک های فوق را پس از وصول به حساب <?php
echo SoftwareName;
?>
منظور دارید.
<br><div style="float:left;width: 250px">نام و نام خانوادگی :<br>امضاء</div><br><br><br>
</div>
</div>
<?} if(count($temp) == 0 && count($dt) == 0){?>
<center>فاقد مدارک</center>
<?}?>
</body>
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();
}
$col = $rpg2->addColumn("اصل قسط", "InstallmentID","pureRender", $PartObj);
function pureRemainRender(&$row, $value, $param, $prevRow){
if(!$prevRow)
$row["pureAmount"] = $param;
else
$row["pureAmount"] = $prevRow["EndingBalance"];
$row["EndingBalance"] = $row["pureAmount"] - ($row["InstallmentAmount"] - $row["profit"]);
return number_format($row["pureAmount"]);
}
$col = $rpg2->addColumn("مانده اصل وام", "InstallmentID","pureRemainRender",$PartObj->PartAmount);
ob_start();
$rpg2->generateReport();
$report2 = ob_get_clean();
//..........................................................
$LastPayedInstallment = 0;
foreach($rpg->mysql_resource as $row)
{
if($row["remainder"]*1 == 0 && isset($row["InstallmentID"]))
$LastPayedInstallment = $row["InstallmentID"];
}
if($LastPayedInstallment == 0)
$EndingAmount = $rpg2->mysql_resource[0]["pureAmount"];
else
{
for($i=0; $i < count($rpg2->mysql_resource);$i++)
{
