/beta/accounting/balance.asp
ASP | 269 lines | 222 code | 29 blank | 18 comment | 17 complexity | bf62c29edaa58e43b7eceba0c2866565 MD5 | raw file
1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><% 2'Accounting (8) 3PageTitle= "����" 4SubmenuItem=4 5if not Auth(8 , 4) then NotAllowdToViewThisPage() 6 7%> 8<!--#include file="top.asp" --> 9<!--#include File="../include_farsiDateHandling.asp"--> 10 11<% 12 13 14'----------------------------------------------------------------------------------------------------- 15'-------------------------------------------------------------------------------- List GL Super Groups 16'----------------------------------------------------------------------------------------------------- 17if request("act")="" then 18%><BR><BR> 19<TABLE dir=rtl align=center width=600 border=0> 20<TR> 21 <TD align=right><A HREF="AccountInfo.asp">���� �� </A>> ���� ������� 22 </TD> 23</TR> 24<TR bgcolor="eeeeee" > 25 <TD colspan=2 align=center>���� ���� ������� 26 </TD> 27</TR> 28<TR> 29 <TD bgcolor="#FFFFFF" width=50%><!----Right----> 30 <TABLE width=70% align=center> 31 <% 32 mySQL="SELECT GLAccountSuperGroups.Name, ISNULL(DERIVEDTBL.total, 0) AS total, GLAccountSuperGroups.ID, GLAccountSuperGroups.Type FROM (SELECT SUM(DERIVEDTBL.Total) AS total, GLAccountGroups.GLSuperGroup FROM (SELECT SUM(DERIVEDTBL.total) AS Total, GLAccounts.GLGroup FROM (SELECT SUM(-1*(CONVERT(tinyint, IsCredit) - .5) * 2 * Amount) AS total, GLAccount FROM GLDocs inner join GLRows on GLDocs.id=GLRows.GLDoc where ((GLDocs.IsTemporary=1 or GLDocs.IsChecked=1 or GLDocs.IsFinalized=1) and GLDocs.deleted=0) GROUP BY GLAccount, GL HAVING (GL = "& OpenGL&")) DERIVEDTBL INNER JOIN GLAccounts ON DERIVEDTBL.GLAccount = GLAccounts.ID WHERE (GLAccounts.GL = "& OpenGL&") GROUP BY GLAccounts.GLGroup) DERIVEDTBL INNER JOIN GLAccountGroups ON DERIVEDTBL.GLGroup = GLAccountGroups.ID WHERE (GLAccountGroups.GL = "& OpenGL&") GROUP BY GLAccountGroups.GLSuperGroup) DERIVEDTBL RIGHT OUTER JOIN GLAccountSuperGroups ON DERIVEDTBL.GLSuperGroup = GLAccountSuperGroups.ID WHERE (GLAccountSuperGroups.GL = "& OpenGL&" and GLAccountSuperGroups.Type=1) " 33' mySQL="SELECT GLAccountSuperGroups.Name,SUM((CONVERT(tinyint, GLRows.IsCredit) - .5) * 2 * GLRows.Amount) AS total, GLAccountSuperGroups.ID FROM GLAccountGroups INNER JOIN GLAccounts ON GLAccountGroups.ID = GLAccounts.GLGroup INNER JOIN GLDocs INNER JOIN GLRows ON GLDocs.ID = GLRows.GLDoc ON GLAccounts.ID = GLRows.GLAccount RIGHT OUTER JOIN GLAccountSuperGroups ON GLAccountGroups.GLSuperGroup = GLAccountSuperGroups.ID WHERE (GLDocs.IsTemporary = 1) AND (GLDocs.deleted = 0) AND (GLDocs.IsRemoved = 0) OR (GLDocs.deleted = 0) AND (GLDocs.IsChecked = 1) AND (GLDocs.IsRemoved = 0) OR (GLDocs.deleted = 0) AND (GLDocs.IsFinalized = 1) AND (GLDocs.IsRemoved = 0) GROUP BY GLDocs.GL, GLAccountSuperGroups.ID,GLAccountSuperGroups.Name HAVING (GLDocs.GL = 82)" 34 35' response.write "<br>" & mySQL 36' response.end 37 set RSS=Conn.Execute (mySQL) 38 39 Do while not RSS.eof 40 %> 41 <TR> 42 <TD><A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS("ID")%>"><%=RSS("name")%></a></TD> 43 <TD><span dir=ltr><%=RSS("total")%></span><br></TD> 44 </TR> 45 <% 46 totalRight = totalRight + cdbl(RSS("total")) 47 RSS.moveNext 48 Loop 49 %> 50 </TABLE> 51 </TD> 52 53 <TD bgcolor="#DDDDDD" width=50%><!----left----> 54 <TABLE width=70% align=center> 55 <% 56 mySQL="SELECT GLAccountSuperGroups.Name, ISNULL(DERIVEDTBL.total, 0) AS total, GLAccountSuperGroups.ID, GLAccountSuperGroups.Type FROM (SELECT SUM(DERIVEDTBL.Total) AS total, GLAccountGroups.GLSuperGroup FROM (SELECT SUM(DERIVEDTBL.total) AS Total, GLAccounts.GLGroup FROM (SELECT SUM((CONVERT(tinyint, IsCredit) - .5) * 2 * Amount) AS total, GLAccount FROM GLDocs inner join GLRows on GLDocs.id=GLRows.GLDoc where ((GLDocs.IsTemporary=1 or GLDocs.IsChecked=1 or GLDocs.IsFinalized=1) and GLDocs.deleted=0) GROUP BY GLAccount, GL HAVING (GL = "& OpenGL&")) DERIVEDTBL INNER JOIN GLAccounts ON DERIVEDTBL.GLAccount = GLAccounts.ID WHERE (GLAccounts.GL = "& OpenGL&") GROUP BY GLAccounts.GLGroup) DERIVEDTBL INNER JOIN GLAccountGroups ON DERIVEDTBL.GLGroup = GLAccountGroups.ID WHERE (GLAccountGroups.GL = "& OpenGL&") GROUP BY GLAccountGroups.GLSuperGroup) DERIVEDTBL RIGHT OUTER JOIN GLAccountSuperGroups ON DERIVEDTBL.GLSuperGroup = GLAccountSuperGroups.ID WHERE (GLAccountSuperGroups.GL = "& OpenGL&" and GLAccountSuperGroups.Type=2) " 57' response.write "<br>" & mySQL 58' response.end 59 60 set RSS=Conn.Execute (mySQL) 61 62 Do while not RSS.eof 63 %> 64 <TR> 65 <TD><A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS("ID")%>"><%=RSS("name")%></a></TD> 66 <TD><span dir=ltr><%=RSS("total")%></span><br></TD> 67 </TR> 68 <% 69 totalLeft = totalLeft + cdbl(RSS("total")) 70 RSS.moveNext 71 Loop 72 %> 73 <TR> 74 <TD><!A HREF="balance.asp?act=groups&SuperGroupID=">������</a></TD> 75 <TD><span dir=ltr>???</span><br></TD> 76 </TR> 77 <% 78 totalLeft = totalLeft + 0 79 %> 80 </TABLE> 81 </TD> 82</TR> 83<TR bgcolor="eeeeee" > 84 <TD align=center><span dir=ltr><%=totalRight%></span> 85 </TD> 86 <TD align=center><span dir=ltr><%=totalLeft%></span> 87 </TD> 88</TR> 89</TABLE> 90 91<BR><BR><BR> 92<% if not totalRight=totalLeft then %> 93<FONT COLOR="RED"><H3><CENTER>���� �� ���� ����</CENTER></H3></FONT> 94<% else %> 95<H3><CENTER>���� �� ���� ��� </CENTER></H3> 96<% end if %> 97<% 98response.end 99 100 101'----------------------------------------------------------------------------------------------------- 102'------------------------------------------------------------------ List GL Groups under a super group 103'----------------------------------------------------------------------------------------------------- 104elseif request("act")="groups" then 105SuperGroupID = request("SuperGroupID") 106 107set RSS=Conn.Execute ("SELECT SUM(ISNULL(DERIVEDTBL.Total, 0)) AS total, GLAccountGroups.GLSuperGroup, SUM(DERIVEDTBL.accountsCount) AS accountsCount, GLAccountGroups.Name, GLAccountGroups.ID FROM (SELECT SUM(DERIVEDTBL.total) AS Total, GLAccounts.GLGroup, COUNT(GLAccounts.ID) AS accountsCount FROM (SELECT SUM((CONVERT(tinyint, IsCredit) - .5) * 2 * Amount) AS total, GLAccount FROM GLDocs inner join GLRows on GLDocs.id=GLRows.GLDoc where ((GLDocs.IsTemporary=1 or GLDocs.IsChecked=1 or GLDocs.IsFinalized=1) and GLDocs.deleted=0) GROUP BY GLAccount, GL HAVING (GL = "& OpenGL & ")) DERIVEDTBL RIGHT OUTER JOIN GLAccounts ON DERIVEDTBL.GLAccount = GLAccounts.ID WHERE (GLAccounts.GL = "& OpenGL & ") GROUP BY GLAccounts.GLGroup) DERIVEDTBL INNER JOIN GLAccountGroups ON DERIVEDTBL.GLGroup = GLAccountGroups.ID WHERE (GLAccountGroups.GL = "& OpenGL & ") AND (GLAccountGroups.GLSuperGroup = "& SuperGroupID & ") GROUP BY GLAccountGroups.GLSuperGroup, GLAccountGroups.Name, GLAccountGroups.ID ") 108 109%><BR><BR> 110<TABLE dir=rtl align=center width=600> 111<TR > 112 <TD colspan=4> 113 <% 114 set RSS2=Conn.Execute ("SELECT *, GLs.ID AS GLID ,GLs.Name AS GLname FROM GLs INNER JOIN GLAccountSuperGroups ON GLs.ID = GLAccountSuperGroups.GL WHERE (GLAccountSuperGroups.GL = "& OpenGL & ") AND (GLAccountSuperGroups.ID = "& SuperGroupID & ")") 115 %><A HREF="AccountInfo.asp">���� �� </A>> <A HREF="balance.asp?OpenGL=<%=RSS2("GLID")%>">���� �������</A> > <%=RSS2("name")%> 116 <BR><hR> 117 </TD> 118</TR> 119<TR bgcolor="eeeeee" > 120 <TD><!A HREF="default.asp?s=1"><SMALL>��</SMALL></A></TD> 121 <TD><!A HREF="default.asp?s=2"><SMALL>��� ����</SMALL></A></TD> 122 <TD><!A HREF="default.asp?s=4"><SMALL>����� ������� �����</SMALL></A></TD> 123 <TD><!A HREF="default.asp?s=4"><SMALL>��� ������ (����)</SMALL></A></TD> 124</TR> 125<% 126tmpCounter=0 127Do while not RSS.eof 128 tmpCounter = tmpCounter + 1 129 if tmpCounter mod 2 = 1 then 130 tmpColor="#FFFFFF" 131 tmpColor2="#FFFFBB" 132 Else 133 tmpColor="#DDDDDD" 134 tmpColor2="#EEEEBB" 135 End if 136 137%> 138<TR bgcolor="<%=tmpColor%>" > 139 <TD><A HREF="balance.asp?act=account&GroupID=<%=RSS("id")%>"><%=RSS("id")%></A></TD> 140 <TD><A HREF="balance.asp?act=account&GroupID=<%=RSS("id")%>"><%=RSS("Name")%></A></TD> 141 <TD><%=RSS("accountsCount")%></TD> 142 <TD><span dir=ltr><%=RSS("total")%></span></TD> 143</TR> 144 145<% 146RSS.moveNext 147Loop 148%> 149</TABLE><br> 150<% 151 152'----------------------------------------------------------------------------------------------------- 153'---------------------------------------------------------------------- List GL Accounts under a group 154'----------------------------------------------------------------------------------------------------- 155elseif request("act")="account" then 156GroupID = request("GroupID") 157 158set RSS=Conn.Execute ("SELECT GLs.Name AS GLName, GLs.ID AS GLID, GLAccountSuperGroups.ID AS SuperGroupID, GLAccountSuperGroups.Name AS SuperGroupName, GLAccountGroups.ID AS GroupID, GLAccountGroups.Name AS GroupName FROM GLAccountGroups INNER JOIN GLAccountSuperGroups ON GLAccountGroups.GLSuperGroup = GLAccountSuperGroups.ID INNER JOIN GLs ON GLAccountSuperGroups.GL = GLs.ID WHERE (GLAccountSuperGroups.GL = "& OpenGL & ") AND (GLAccountGroups.GL = "& OpenGL & ") AND (GLAccountGroups.ID = "& GroupID & ")") 159%><BR><BR> 160<TABLE dir=rtl align=center width=600> 161<TR > 162 <TD colspan=5> 163 <% 164 set RSS2=Conn.Execute ("SELECT GLAccounts.Name, DERIVEDTBL.totalDebit AS totalDebit, DERIVEDTBL.totalCredit AS totalCredit, GLAccounts.ID FROM (SELECT SUM(CONVERT(tinyint, IsCredit) * Amount) AS totalCredit, SUM((CONVERT(tinyint, IsCredit) - 1) * (- 1) * Amount) AS totalDebit, GLAccount FROM GLDocs inner join GLRows on GLDocs.id=GLRows.GLDoc where ((GLDocs.IsTemporary=1 or GLDocs.IsChecked=1 or GLDocs.IsFinalized=1) and GLDocs.deleted=0) GROUP BY GLAccount, GL HAVING (GL = "& OpenGL & ")) DERIVEDTBL RIGHT OUTER JOIN GLAccounts ON DERIVEDTBL.GLAccount = GLAccounts.ID WHERE (GLAccounts.GL = "& OpenGL & ") AND (GLAccounts.GLGroup = "& GroupID & ")") 165 166 %><A HREF="AccountInfo.asp">���� �� </A>> <A HREF="balance.asp?OpenGL=<%=RSS("GLID")%>">���� �������</A> > <A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS("SuperGroupID")%>"><%=RSS("SuperGroupName")%></a> > <%=RSS("GroupName")%> 167 <BR><hR> 168 </TD> 169</TR> 170<TR bgcolor="eeeeee" > 171 <TD><!A HREF="default.asp?s=1"><SMALL>��</SMALL></A></TD> 172 <TD><!A HREF="default.asp?s=2"><SMALL>����� ����</SMALL></A></TD> 173 <TD><!A HREF="default.asp?s=2"><SMALL>��� ������</SMALL></A></TD> 174 <TD><!A HREF="default.asp?s=2"><SMALL>��� ��������</SMALL></A></TD> 175</TR> 176<% 177tmpCounter=0 178Do while not RSS2.eof 179 tmpCounter = tmpCounter + 1 180 if tmpCounter mod 2 = 1 then 181 tmpColor="#FFFFFF" 182 tmpColor2="#FFFFBB" 183 Else 184 tmpColor="#DDDDDD" 185 tmpColor2="#EEEEBB" 186 End if 187 188%> 189<TR bgcolor="<%=tmpColor%>" > 190 <TD><A HREF="balance.asp?act=accountRows&accountID=<%=RSS2("id")%>"><%=RSS2("id")%></A></TD> 191 <TD><A HREF="balance.asp?act=accountRows&accountID=<%=RSS2("id")%>"><%=RSS2("Name")%></A></TD> 192 <TD><span dir=ltr><%=RSS2("totalDebit")%></span></TD> 193 <TD><span dir=ltr><%=RSS2("totalCredit")%></span></TD> 194</TR> 195 196<% 197RSS2.moveNext 198Loop 199%> 200</TABLE><br> 201<% 202 203'----------------------------------------------------------------------------------------------------- 204'-------------------------------------------------------------------------- List Rows of a GL Accounts 205'----------------------------------------------------------------------------------------------------- 206elseif request("act")="accountRows" then 207account = request("accountID") 208 209set RSS=Conn.Execute ("SELECT * FROM GLDocs inner join GLRows on GLDocs.id=GLRows.GLDoc where ((GLDocs.IsTemporary=1 or GLDocs.IsChecked=1 or GLDocs.IsFinalized=1) and GLDocs.deleted=0) AND (GL = "& OpenGL & ") AND (GLaccount = "& account & ")") 210%><BR><BR> 211<TABLE dir=rtl align=center width=600> 212<TR > 213 <TD colspan=6> 214 <% 215 set RSS2=Conn.Execute ("SELECT *, GLAccountGroups.ID AS GroupID, GLAccountGroups.Name AS GroupName, GLAccountSuperGroups.ID AS SuperGroupID, GLAccountSuperGroups.Name AS SuperGroupName, GLs.ID AS GLID, GLs.Name AS GLname, GLAccounts.Name AS name FROM GLs INNER JOIN GLAccountSuperGroups ON GLs.ID = GLAccountSuperGroups.GL INNER JOIN GLAccountGroups ON GLs.ID = GLAccountGroups.GL AND GLAccountSuperGroups.ID = GLAccountGroups.GLSuperGroup INNER JOIN GLAccounts ON GLs.ID = GLAccounts.GL AND GLAccountGroups.ID = GLAccounts.GLGroup WHERE (GLAccounts.GL = "& OpenGL & ") AND (GLAccounts.ID = "& account & ")") 216 217 %><A HREF="AccountInfo.asp">���� �� </A>> <A HREF="balance.asp?OpenGL=<%=RSS2("GLID")%>">���� �������</A> > <A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS2("SuperGroupID")%>"><%=RSS2("SuperGroupName")%></a> > <A HREF="balance.asp?act=account&GroupID=<%=RSS2("GroupID")%>"><%=RSS2("GroupName")%></a> > <%=RSS2("Name")%> 218 <BR><hR> 219 </TD> 220</TR> 221<TR bgcolor="eeeeee" > 222 <TD><!A HREF="default.asp?s=1"><SMALL>��</SMALL></A></TD> 223 <TD><!A HREF="default.asp?s=2"><SMALL>���</SMALL></A></TD> 224 <TD><!A HREF="default.asp?s=2"><SMALL>����� ���</SMALL></A></TD> 225 <TD><!A HREF="default.asp?s=2"><SMALL>����� ���</SMALL></A></TD> 226 <TD><!A HREF="default.asp?s=5"><SMALL>������</SMALL></A></TD> 227 <TD><!A HREF="default.asp?s=5"><SMALL>��������</SMALL></A></TD> 228</TR> 229<% 230tmpCounter=0 231Do while not RSS.eof 232 tmpCounter = tmpCounter + 1 233 if tmpCounter mod 2 = 1 then 234 tmpColor="#FFFFFF" 235 tmpColor2="#FFFFBB" 236 Else 237 tmpColor="#DDDDDD" 238 tmpColor2="#EEEEBB" 239 End if 240 241 debit = "" 242 credit = "" 243 if RSS("IsCredit") then 244 credit = RSS("Amount") 245 else 246 debit = RSS("Amount") 247 end if 248%> 249<TR bgcolor="<%=tmpColor%>" > 250 <TD><!A HREF="balance.asp?act=accountRows&accountID=<%=RSS("id")%>"><%=RSS("id")%></A></TD> 251 <TD><!A HREF="balance.asp?act=accountRows&accountID=<%=RSS("id")%>"><%=RSS("Description")%></A></TD> 252 <TD><A HREF="javascript:void(0);" onclick="window.open('GLMemoDocShow.asp?id=<%=RSS("GLDoc")%>',null,'menubars=no')"><%=RSS("GLDocID")%></A></TD> 253 <TD><%=RSS("GLDocDate")%></TD> 254 <TD><%=debit%></TD> 255 <TD><%=credit%></TD> 256</TR> 257 258<% 259RSS.moveNext 260Loop 261%> 262</TABLE><br> 263<% 264end if 265 266 267%> 268 269<!--#include file="tah.asp" -->