/beta/Inquiry/Rep_4col.asp
ASP | 234 lines | 210 code | 20 blank | 4 comment | 34 complexity | 8462eea5c5dda35a44724f9a28650c94 MD5 | raw file
1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><% 2'Order (2) 3PageTitle="����� ����" 4SubmenuItem=11 5if not Auth("C" , 8) then NotAllowdToViewThisPage() 6%> 7<!--#include file="top.asp" --> 8<!--#include File="../include_farsiDateHandling.asp"--> 9<!--#include File="../include_JS_InputMasks.asp"--> 10<!--#include File="../include_UtilFunctions.asp"--> 11<STYLE> 12 .GetCustTbl {font-family:tahoma; background-color: #DDDDDD; width:630; direction: RTL; } 13 .GetCustTbl td {padding:2; font-size: 9pt; height:25;} 14 .GetCustInp { font-family:tahoma; font-size: 9pt;} 15 .CusTableHeader {background-color: #33AACC; text-align: center; font-weight:bold;} 16 .CustContactTable {font-family:tahoma; width:100%; border:1 solid black; direction: RTL; background-color:#CCCCCC;} 17 .CustContactTable td {padding:5;} 18 .CustTable {font-family:tahoma; width:80%; border:1 solid black; direction: RTL; background-color:black;} 19 .CustTable td {padding:5;} 20 .CustTable a {text-decoration:none;color:#000088} 21 .CustTable a:hover {text-decoration:underline;} 22 .CusTD1 {background-color: #CCCC66; text-align: left; font-weight:bold;} 23 .CusTD2 {background-color: #DDDDDD; direction: LTR; text-align: right; font-size:9pt;} 24 .CusTD3 {background-color: #DDDDDD; direction: LTR; text-align: center; font-size:9pt;} 25 .CusTD4 {background-color: #CCCC66; direction: LTR; text-align: center; font-size:9pt;} 26 .CustTable4 {font-family:tahoma; direction: RTL; width:100%; height:100%; background-color:#C3DBEB;} 27</STYLE> 28<% 29if request("act")="show" then 30 31 'ON ERROR RESUME Next 32 FromDate = sqlSafe(request("FromDate")) 33 ToDate = sqlSafe(request("ToDate")) 34 Ord = request("Ord") 35 If Ord="" Then Ord = 0 36 37 'mySQL="SELECT account, accountTitle, sum(case arItems.[type] when 1 then amountOriginal else 0 end) as totalDebit, sum(case arItems.[type] when 2 then amountOriginal else 0 end) as totalCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id where voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' group by account,accountTitle" & order 38 39 40 select case Ord 41 case "1": 42 order="account" 43 case "-1": 44 order="account DESC" 45 case "2": 46 order="AccountTitle" 47 case "-2": 48 order="AccountTitle DESC" 49 case "3": 50 order="totalDebit" 51 case "-3": 52 order="totalDebit DESC" 53 case "4": 54 order="totalCredit" 55 case "-4": 56 order="totalCredit DESC" 57 Case "5": 58 order = "remainDebit" 59 Case "-5": 60 order = "remainDebit DESC" 61 case "6": 62 order="remainCredit" 63 case "-6": 64 order="remainCredit DESC" 65 case else: 66 order="account" 67 Ord=1 68 end select 69 70 71%> 72 <TABLE dir=rtl align=center width=640 cellspacing=2 cellpadding=2 style="border:2 solid #330066;"> 73 74<% 75 mySQL = "select sum(remDebit) as sumRemDebit, sum(remCredit) as sumRemCredit, sum(totalDebit + remDebit) as sumTotalDebit, sum(totalCredit + remCredit) as sumTotalCredit, sum(case when (totalDebit+remDebit)>(totalCredit+remCredit) then (totalDebit+remDebit)-(totalCredit+remCredit) else 0 end) as remainDebit, sum(case when (totalDebit+remDebit)<(totalCredit+remCredit) then (totalCredit+remCredit)-(totalDebit+remDebit) else 0 end) as remainCredit, sum(Items) as totalItems from (SELECT CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END AS remDebit, CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END AS remCredit, account, accountTitle, sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) as totalDebit, sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) as totalCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id LEFT OUTER JOIN (SELECT account as remAccount, ISNULL(SUM(AmountOriginal *(CONVERT(tinyint, IsCredit) - .5) * 2 ), 0) as amount from arItems WHERE voided = 0 AND EffectiveDate < N'"&FromDate&"' GROUP BY account) remTbl on remTbl.remAccount = arItems.account WHERE voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' GROUP BY account,accountTitle,remTbl.amount) drvTBL" 76'response.write mySQL 77 set rs=Conn.Execute (mySQL) 78 if rs.eof then 79%> <tr> 80 <td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td> 81 </tr> 82<% Else %> 83 <TR bgcolor="#CCCCEE" > 84 <TD colspan=2 rowspan=2 title=" <%=rs("totalItems")%> ���� "> 85 �� ����� <B><%=replace(FromDate,"/",".")%></B> �� ����� <B><%=replace(ToDate,"/",".")%></B><br> 86 �� ������ <B><%=FromTafsil%></B> �� ������ <B><%=ToTafsil%></B> 87 </TD> 88 <TD width=70 >���� ������ </TD> 89 <TD width=70 >���� �������� </TD> 90 <TD width=70 >����� ������ </TD> 91 <TD width=70 >����� �������� </TD> 92 </TR> 93 <TR bgcolor="#CCCCEE" > 94 <TD title='<%=Separate(rs("sumRemDebit"))%> ������� ����� ' width=70 ><%=Separate(rs("sumTotalDebit"))%></TD> 95 <TD title='<%=Separate(rs("sumRemCredit"))%> ��������� ����� ' width=70 ><%=Separate(rs("sumTotalCredit"))%></TD> 96 <TD width=70 ><%=Separate(rs("remainDebit"))%></TD> 97 <TD width=70 ><%=Separate(rs("remainCredit"))%></TD> 98 </TR> 99 <TR bgcolor="black" height="2"> 100 <TD colspan="6" style="padding:0;"></TD> 101 </TR> 102<% 103 rs.close 104 105 if ord<0 then 106 style="background-color: #33CC99;" 107 arrow="<br><span style='font-family:webdings'>6 6 6</span>" 108 else 109 style="background-color: #33CC99;" 110 arrow="<br><span style='font-family:webdings'>5 5 5</span>" 111 end if 112%> 113 <TR bgcolor="eeeeee" style="cursor:hand;" title="����� �����"> 114 <TD width=50 onclick='go2Page(1,1);' style="<%if abs(ord)=1 then response.write style%>">����� ���� <%if abs(ord)=1 then response.write arrow%></TD> 115 <TD width='*' onclick='go2Page(1,2);' style="<%if abs(ord)=2 then response.write style%>">����� ���� <%if abs(ord)=2 then response.write arrow%></TD> 116 <TD width=70 onclick='go2Page(1,-3);' style="<%if abs(ord)=3 then response.write style%>">���� ������ <%if abs(ord)=3 then response.write arrow%></TD> 117 <TD width=70 onclick='go2Page(1,-4);' style="<%if abs(ord)=4 then response.write style%>">���� �������� <%if abs(ord)=4 then response.write arrow%></TD> 118 <TD width=70 onclick='go2Page(1,-5);' style="<%if abs(ord)=5 then response.write style%>">����� ������ <%if abs(ord)=5 then response.write arrow%></TD> 119 <TD width=70 onclick='go2Page(1,-6);' style="<%if abs(ord)=6 then response.write style%>">����� �������� <%if abs(ord)=6 then response.write arrow%></TD> 120 </TR> 121 <TR bgcolor="eeeeee" > 122 <TD colspan=6 height=2 bgcolor=0></TD> 123 </TR> 124<% 125 SumCredit=0 126 SumDebit=0 127 SumCreditRemained=0 128 SumDebitRemained=0 129 tmpCounter=0 130 131 mySQL="SELECT CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END AS remDebit, CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END AS remCredit, account, accountTitle, sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END as totalDebit, sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END as totalCredit, case when (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END) > (sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) then (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END)-(sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) else 0 end as remainDebit, case when (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END) < (sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) then -(sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END)+(sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) else 0 end as remainCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id LEFT OUTER JOIN (SELECT account as remAccount, ISNULL(SUM(AmountOriginal *(CONVERT(tinyint, IsCredit) - .5) * 2 ), 0) as amount from arItems WHERE voided = 0 AND EffectiveDate < N'"&FromDate&"' GROUP BY account) remTbl on remTbl.remAccount = arItems.account WHERE voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' GROUP BY account,accountTitle,remTbl.amount ORDER BY " & order 132 133 Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL) 134 135 PageSize = 50 136 rs.PageSize = PageSize 137 138 rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3 139 rs.Open mySQL ,Conn,3 140 TotalPages = rs.PageCount 141 142 CurrentPage=1 143 144 if isnumeric(Request.QueryString("p")) then 145 pp=clng(Request.QueryString("p")) 146 if pp <= TotalPages AND pp > 0 then 147 CurrentPage = pp 148 end if 149 end if 150 151 if not rs.eof then 152 rs.AbsolutePage=CurrentPage 153 end if 154 155 if rs.eof then 156%> <tr> 157 <td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td> 158 </tr> 159<% else 160 Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage) 161 tmpCounter = tmpCounter + 1 162 if tmpCounter mod 2 = 1 then 163 tmpColor="#FFFFFF" 164 tmpColor2="#FFFFBB" 165 Else 166 tmpColor="#DDDDDD" 167 tmpColor2="#EEEEBB" 168 End if 169 %> 170 <TR bgcolor="<%=tmpColor%>" > 171 <TD dir=ltr align=right><A href="AccountReport.asp?act=show&reason=6&sys=AR&selectedCustomer=<%=rs("account")%>&startDate=<%=FromDate%>&endDate=<%=ToDate%>"><%=rs("account")%></A></TD> 172 <TD title=" <%=rs("Items")%> ���� "><%=rs("accountTitle")%></TD> 173 <TD title='<%=Separate(rs("remDebit"))%> ������� ����� ' dir=ltr align=right><span dir=ltr><%=Separate(rs("totalDebit"))%></span></TD> 174 <TD title='<%=Separate(rs("remCredit"))%> ��������� ����� ' dir=ltr align=right><span dir=ltr><%=Separate(rs("totalCredit"))%></span></TD> 175 <TD dir=ltr align=right><span dir=ltr><%=Separate(rs("remainDebit"))%></span></TD> 176 <TD dir=ltr align=right><span dir=ltr><%=Separate(rs("remainCredit"))%></span></TD> 177 </TR> 178 179 <% 180 rs.moveNext 181 Loop 182 183 if TotalPages > 1 then 184 pageCols=20 185%> 186 <TR bgcolor="eeeeee" > 187 <TD colspan=6 height=2 bgcolor=0></TD> 188 </TR> 189 <TR class="RepTableTitle"> 190 <TD bgcolor="#CCCCEE" height="30" colspan="6"> 191 <table width=100% cellspacing=0 style="cursor:hand;color:gray;"> 192 <tr> 193 <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>> 194 <b>���� <%=CurrentPage%> �� <%=TotalPages%></b> 195 <a href="javascript:go2Page(<%=CurrentPage+1%>,0);">���� ��� ></a> 196 </td> 197 </tr> 198 <tr> 199<% for i=1 to TotalPages 200 if i = CurrentPage then 201%> <td style="color:black;"><b>[<%=i%>]</b></td> 202<% else 203%> <td onclick="go2Page(<%=i%>,0);"><%=i%></td> 204<% end if 205 if i mod pageCols = 0 then response.write "</tr><tr>" 206 next 207 208%> </tr> 209 </table> 210 </TD> 211 </TR> 212<% end if 213%> 214 </TABLE> 215 <br> 216 217 <SCRIPT LANGUAGE="JavaScript"> 218 function go2Page(p,ord) { 219 if(ord==0){ 220 ord=<%=Ord%>; 221 } 222 else if(ord==<%=Ord%>){ 223 ord= 0-ord; 224 } 225 str = '?act=show&FromDate=' + escape('<%=FromDate%>') + '&ToDate=' + escape('<%=ToDate%>') + '&Ord=' + escape(ord) + '&p=' + escape(p); 226 window.location = str; 227 } 228 </SCRIPT> 229<% 230 End if 231End If 232End If 233%> 234<!--#include file="tah.asp" -->