/beta/accounting/OtherReports_Tmp4Zamani.asp
ASP | 252 lines | 214 code | 27 blank | 11 comment | 18 complexity | bbe7c9e24ad7259b558cc18a42207900 MD5 | raw file
1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><% 2'Accounting (8) 3PageTitle="���� ����� ��" 4SubmenuItem=10 5if not Auth(8 , "E") then NotAllowdToViewThisPage() 6 7%> 8<!--#include file="top.asp" --> 9<!--#include File="../include_farsiDateHandling.asp"--> 10<!--#include File="../include_JS_InputMasks.asp"--> 11<STYLE> 12 .RepTable {font-family:tahoma; font-size:9pt; direction: RTL; } 13 .RepTable td {border:1pt solid white;vertical-align:top;} 14 .RepTable a {text-decoration:none; color:#222288;} 15 .RepTable a:hover {text-decoration:underline;} 16 .RepTable2 th {font-size:9pt; background-color:#666699;height:25px;} 17 .RepTable2 input {font-family:tahoma; font-size:9pt; border:1 solid black;} 18</STYLE> 19<BR> 20<% 21'----------------------------------------------------------------------------------------------------- 22'----------------------------------------------------------------------------------------- Search Form 23'----------------------------------------------------------------------------------------------------- 24if request("act")="MoeenRep" OR request("act")="" then 25 26 Ord=request("Ord") 27 28 select case Ord 29 case "1": 30 order="Tafsil" 31 case "-1": 32 order="Tafsil DESC" 33 case "2": 34 order="AccountTitle" 35 case "-2": 36 order="AccountTitle DESC" 37 case "3": 38 order="totalDebit" 39 case "-3": 40 order="totalDebit DESC" 41 case "4": 42 order="totalCredit" 43 case "-4": 44 order="totalCredit DESC" 45 case "5","-6": 46 order="ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) - ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0) DESC" 47 case "6","-5": 48 order="ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) - ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0)" 49 case else: 50 order="Tafsil" 51 Ord=1 52 end select 53 54%> 55 <SCRIPT LANGUAGE="JavaScript"> 56 <!-- 57 function showAcc(acc){ 58 window.open('tafsili.asp?accountID='+acc+'&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&moeenFrom=0&moeenTo=99999&act=Show'); 59 } 60 //--> 61 </SCRIPT> 62 <TABLE dir=rtl align=center width=640 cellspacing=2 cellpadding=2 style="border:2 solid #330066;"> 63 <TR bgcolor="#EEEECC" height="30"> 64 <TD colspan=7> 65 ����� ���� ��� ������� �� ����� ���� �� ����� ��� 82 66 </TD> 67 </TR> 68<% 69 70 'mySQL = "SELECT SUM(SumCred) AS SumCred, SUM(SumDeb) AS SumDeb, SUM(Flow * (Sgn + 1) / 2) AS sumFlowCred, SUM(Flow * (Sgn - 1) / 2) AS sumFlowdeb FROM (SELECT SUM(IsCredit * Amount) AS SumCred, SUM(- ((IsCredit - 1) * Amount)) AS SumDeb, SUM(IsCredit * Amount) - SUM(- ((IsCredit - 1) * Amount)) AS Flow, SIGN(SUM(IsCredit * Amount) - SUM(- ((IsCredit - 1) * Amount))) AS Sgn FROM EffectiveGLRows WHERE (GLAccount = "& GLAccount & ") AND (GL = "& OpenGL & ") AND (ISNULL(Tafsil, 0) >= "& FromTafsil & ") AND (ISNULL(Tafsil, 0) <= "& ToTafsil & ") AND (GLDocDate >= N'"& FromDate & "') AND (GLDocDate <= N'"& ToDate & "') GROUP BY Tafsil) FlowTbl" 71 mySQL="SELECT SUM(totalCredit) AS sumCred, SUM(totalDebit) AS sumDeb, SUM(Flow * (Sgn + 1) / 2) AS sumFlowCred, SUM(Flow * (Sgn - 1) / 2) AS sumFlowdeb FROM (SELECT Account AS Tafsil, ISNULL(SUM(AmountOriginal * IsCredit), 0) AS totalCredit, ISNULL(SUM(AmountOriginal * (1 - IsCredit)), 0) AS totalDebit, ISNULL(SUM(AmountOriginal * (CONVERT(tinyint, IsCredit) - .5) * 2), 0) AS Flow, ISNULL(SIGN(SUM(AmountOriginal * (CONVERT(tinyint, IsCredit) - .5) * 2)), 0) AS Sgn FROM ARItems WHERE (Voided = 0) AND (EffectiveDate < '1383/01/01') GROUP BY Account) FlowTbl" 72 73 set rs=Conn.Execute (mySQL) 74 75%> 76 <TR bgcolor="#EEEECC" > 77 <TD colspan=2 rowspan=2> 78 ��� 79 </TD> 80 <TD width=70 >���� ������ </TD> 81 <TD width=70 >���� �������� </TD> 82 <TD width=70 >����� ������ </TD> 83 <TD width=70 >����� �������� </TD> 84 </TR> 85 <TR bgcolor="#EEEECC" > 86 <TD width=70 ><%=Separate(rs("SumDeb"))%></TD> 87 <TD width=70 ><%=Separate(rs("SumCred"))%></TD> 88 <TD width=70 ><%=Separate(rs("SumFlowDeb"))%></TD> 89 <TD width=70 ><%=Separate(rs("SumFlowCred"))%></TD> 90 </TR> 91 <TR bgcolor="black" height="2"> 92 <TD colspan="6" style="padding:0;"></TD> 93 </TR> 94<% 95 rs.close 96 97 if ord<0 then 98 style="background-color: #33CC99;" 99 arrow="<br><span style='font-family:webdings'>6 6 6</span>" 100 else 101 style="background-color: #33CC99;" 102 arrow="<br><span style='font-family:webdings'>5 5 5</span>" 103 end if 104%> 105 <TR bgcolor="eeeeee" style="cursor:hand;" title="����� �����"> 106 <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> 107 <TD width='*' onclick='go2Page(1,2);' style="<%if abs(ord)=2 then response.write style%>">����� ���� <%if abs(ord)=2 then response.write arrow%></TD> 108 <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> 109 <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> 110 <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> 111 <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> 112 </TR> 113 <TR bgcolor="eeeeee" > 114 <TD colspan=6 height=2 bgcolor=0></TD> 115 </TR> 116<% 117 SumCredit=0 118 SumDebit=0 119 SumCreditRemained=0 120 SumDebitRemained=0 121 tmpCounter=0 122 123 'mySQL="SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit, Accounts.AccountTitle AS AccountTitle FROM (SELECT ID AS GLDoc, GLDocDate FROM GLDocs WHERE (GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocDate >= N'"& FromDate & "') AND (GLDocDate <= N'"& ToDate & "') AND (GL = "& openGL & " ) AND (IsRemoved = 0) AND (deleted = 0)) EffectiveGLDocs INNER JOIN GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc INNER JOIN Accounts ON GLRows.Tafsil = Accounts.ID WHERE (GLRows.GLAccount = "& GLAccount & ") AND (GLRows.deleted = 0) GROUP BY GLRows.Tafsil, Accounts.AccountTitle HAVING (ISNULL(GLRows.Tafsil, 0) >= "& FromTafsil & ") AND (ISNULL(GLRows.Tafsil, 0) <= "& ToTafsil & ") ORDER BY " & order 124 mySQL="SELECT ARItems.Account AS Tafsil, ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) AS totalCredit, ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0) AS totalDebit, Accounts.AccountTitle FROM ARItems INNER JOIN Accounts ON ARItems.Account = Accounts.ID WHERE (ARItems.Voided = 0) AND (ARItems.EffectiveDate < '1383/01/01') GROUP BY ARItems.Account, Accounts.AccountTitle ORDER BY "& order 125 126' 127' The Differences: 128' mySQL="SELECT FromHan.Tafsil, FromHan.totalCredit AS HanCredit, FromHan.totalDebit AS HanDebit, DRV.totalCredit AS sysCred, DRV.totalDebit AS sysDeb FROM (SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit FROM (SELECT ID AS GLDoc, GLDocDate FROM GLDocs WHERE (GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/01/01') AND (GLDocDate <= N'1383/01/01') AND (GL = 83) AND (IsRemoved = 0) AND (deleted = 0)) EffectiveGLDocs INNER JOIN GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc WHERE (GLRows.deleted = 0) AND (LEFT(GLRows.GLAccount, 2) = 13) AND (GLRows.Tafsil IS NOT NULL) GROUP BY GLRows.Tafsil) FromHan INNER JOIN (SELECT Account AS Tafsil, ISNULL(SUM(AmountOriginal * IsCredit), 0) AS totalCredit, ISNULL(SUM(AmountOriginal * (1 - IsCredit)), 0) AS totalDebit FROM ARItems WHERE (Voided = 0) AND (EffectiveDate < '1383/01/01') GROUP BY Account) DRV ON FromHan.Tafsil = DRV.Tafsil AND FromHan.totalCredit - FromHan.totalDebit <> DRV.totalCredit - DRV.totalDebit" 129' 130' 131 Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL) 132 133 PageSize = 25 134 rs.PageSize = PageSize 135 136 rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3 137 rs.Open mySQL ,Conn,3 138 TotalPages = rs.PageCount 139 140 CurrentPage=1 141 142 if isnumeric(Request.QueryString("p")) then 143 pp=clng(Request.QueryString("p")) 144 if pp <= TotalPages AND pp > 0 then 145 CurrentPage = pp 146 end if 147 end if 148 149 if not rs.eof then 150 rs.AbsolutePage=CurrentPage 151 end if 152 153 if rs.eof then 154%> <tr> 155 <td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td> 156 </tr> 157<% else 158 Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage) 159 tmpCounter = tmpCounter + 1 160 if tmpCounter mod 2 = 1 then 161 tmpColor="#FFFFFF" 162 tmpColor2="#FFFFBB" 163 Else 164 tmpColor="#DDDDDD" 165 tmpColor2="#EEEEBB" 166 End if 167 168 totalDebit = cdbl(rs("totalDebit")) 169 totalCredit = cdbl(rs("totalCredit")) 170 171 if totalCredit > totalDebit then 172 creditRemained = totalCredit - totalDebit 173 debitRemained = 0 174 else 175 creditRemained = 0 176 debitRemained = totalDebit - totalCredit 177 end if 178 179 SumCredit = SumCredit + totalCredit 180 SumDebit = SumDebit + totalDebit 181 182 SumCreditRemained = SumCreditRemained + creditRemained 183 SumDebitRemained = SumDebitRemained + debitRemained 184 185 %> 186 <TR bgcolor="<%=tmpColor%>" > 187 <TD dir=ltr align=right><A HREF="javascript:showAcc(<%=rs("Tafsil")%>);"><%=rs("Tafsil")%></A></TD> 188 <TD><%=rs("AccountTitle")%></TD> 189 <TD dir=ltr align=right><span dir=ltr><%=Separate(totalDebit)%></span></TD> 190 <TD dir=ltr align=right><span dir=ltr><%=Separate(totalCredit)%></span></TD> 191 <TD dir=ltr align=right><span dir=ltr><%=Separate(debitRemained)%></span></TD> 192 <TD dir=ltr align=right><span dir=ltr><%=Separate(creditRemained)%></span></TD> 193 </TR> 194 195 <% 196 rs.moveNext 197 Loop 198 199 if TotalPages > 1 then 200 pageCols=20 201%> 202 <TR bgcolor="eeeeee" > 203 <TD colspan=6 height=2 bgcolor=0></TD> 204 </TR> 205 <TR class="RepTableTitle"> 206 <TD bgcolor="#CCCCEE" height="30" colspan="6"> 207 <table width=100% cellspacing=0 style="cursor:hand;color:gray;"> 208 <tr> 209 <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>> 210 <b>���� <%=CurrentPage%> �� <%=TotalPages%></b> 211 <a href="javascript:go2Page(<%=CurrentPage+1%>,0);">���� ��� ></a> 212 </td> 213 </tr> 214 <tr> 215<% for i=1 to TotalPages 216 if i = CurrentPage then 217%> <td style="color:black;"><b>[<%=i%>]</b></td> 218<% else 219%> <td onclick="go2Page(<%=i%>,0);"><%=i%></td> 220<% end if 221 if i mod pageCols = 0 then response.write "</tr><tr>" 222 next 223 224%> </tr> 225 </table> 226 </TD> 227 </TR> 228<% end if 229%> 230 </TABLE><br> 231 <SCRIPT LANGUAGE="JavaScript"> 232 <!-- 233 function go2Page(p,ord) { 234 if(ord==0){ 235 ord=<%=Ord%>; 236 } 237 else if(ord==<%=Ord%>){ 238 ord= 0-ord; 239 } 240 str='?act=MoeenRep&GLAccount='+escape('<%=GLAccount%>')+'&FromDate='+escape('<%=FromDate%>')+'&ToDate='+escape('<%=ToDate%>')+'&FromTafsil='+escape('<%=FromTafsil%>')+'&ToTafsil='+escape('<%=ToTafsil%>')+'&Ord='+escape(ord)+'&p='+escape(p) //+'& ='+escape(' ')+'& ='+escape(' ')+'& ='+escape(' ') 241 window.location=str; 242 } 243 //--> 244 </SCRIPT> 245<% 246 end if 247end if 248 249 250%> 251 252<!--#include file="tah.asp" -->