PageRenderTime 23ms CodeModel.GetById 14ms app.highlight 5ms RepoModel.GetById 2ms app.codeStats 0ms

/beta/accounting/balance.asp

http://github.com/khaneh/Orders
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" -->