/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. 

  2. 'Accounting (8)

    3. 

  3. PageTitle= ""

    4. 

  4. SubmenuItem=4

    5. 

  5. if not Auth(8 , 4) then NotAllowdToViewThisPage()

    6. 

  6. 

    7. 

  7. %>

    8. 

  8. <!--#include file="top.asp" -->

    9. 

  9. <!--#include File="../include_farsiDateHandling.asp"-->

    10. 

  10. 

    11. 

  11. <%

    12. 

  12. 

    13. 

  13. 

    14. 

  14. '-----------------------------------------------------------------------------------------------------

    15. 

  15. '-------------------------------------------------------------------------------- List GL Super Groups

    16. 

  16. '-----------------------------------------------------------------------------------------------------

    17. 

  17. if request("act")="" then

    18. 

  18. %><BR><BR>

    19. 

  19. <TABLE dir=rtl align=center width=600 border=0>

    20. 

  20. <TR>

    21. 

  21. 	<TD   align=right><A HREF="AccountInfo.asp">  </A>>  

    22. 

  22. 	</TD>

    23. 

  23. </TR>

    24. 

  24. <TR  bgcolor="eeeeee" >

    25. 

  25. 	<TD colspan=2 align=center>  

    26. 

  26. 	</TD>

    27. 

  27. </TR>

    28. 

  28. <TR>

    29. 

  29. 	<TD bgcolor="#FFFFFF" width=50%><!----Right---->

    30. 

  30. 	<TABLE width=70% align=center>

    31. 

  31. 	<%

    32. 

  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. 

  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. 

  34. 

    35. 

  35. '	response.write "<br>" & mySQL

    36. 

  36. '	response.end

    37. 

  37. 	set RSS=Conn.Execute (mySQL)

    38. 

  38. 

    39. 

  39. 	Do while not RSS.eof

    40. 

  40. 		%>

    41. 

  41. 		<TR>

    42. 

  42. 		<TD><A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS("ID")%>"><%=RSS("name")%></a></TD>

    43. 

  43. 		<TD><span dir=ltr><%=RSS("total")%></span><br></TD>

    44. 

  44. 		</TR>

    45. 

  45. 		<%

    46. 

  46. 		totalRight = totalRight + cdbl(RSS("total"))

    47. 

  47. 		RSS.moveNext

    48. 

  48. 	Loop

    49. 

  49. 	%>	

    50. 

  50. 	</TABLE>

    51. 

  51. 	</TD>

    52. 

  52. 

    53. 

  53. 	<TD bgcolor="#DDDDDD" width=50%><!----left---->

    54. 

  54. 	<TABLE width=70% align=center>

    55. 

  55. 	<%

    56. 

  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. 

  57. '	response.write "<br>" & mySQL

    58. 

  58. '	response.end

    59. 

  59. 

    60. 

  60. 	set RSS=Conn.Execute (mySQL)

    61. 

  61. 

    62. 

  62. 	Do while not RSS.eof

    63. 

  63. 		%>

    64. 

  64. 		<TR>

    65. 

  65. 		<TD><A HREF="balance.asp?act=groups&SuperGroupID=<%=RSS("ID")%>"><%=RSS("name")%></a></TD>

    66. 

  66. 		<TD><span dir=ltr><%=RSS("total")%></span><br></TD>

    67. 

  67. 		</TR>

    68. 

  68. 		<%

    69. 

  69. 		totalLeft = totalLeft + cdbl(RSS("total"))

    70. 

  70. 		RSS.moveNext

    71. 

  71. 	Loop

    72. 

  72. 	%>	

    73. 

  73. 		<TR>

    74. 

  74. 		<TD><!A HREF="balance.asp?act=groups&SuperGroupID="></a></TD>

    75. 

  75. 		<TD><span dir=ltr>???</span><br></TD>

    76. 

  76. 		</TR>

    77. 

  77. 		<%

    78. 

  78. 		totalLeft = totalLeft + 0

    79. 

  79. 		%>

    80. 

  80. 	</TABLE>

    81. 

  81. 	</TD>

    82. 

  82. </TR>

    83. 

  83. <TR  bgcolor="eeeeee" >

    84. 

  84. 	<TD align=center><span dir=ltr><%=totalRight%></span>

    85. 

  85. 	</TD>

    86. 

  86. 	<TD align=center><span dir=ltr><%=totalLeft%></span>

    87. 

  87. 	</TD>

    88. 

  88. </TR>

    89. 

  89. </TABLE>

    90. 

  90. 

    91. 

  91. <BR><BR><BR>

    92. 

  92. <% if not totalRight=totalLeft then %>

    93. 

  93. <FONT COLOR="RED"><H3><CENTER>   </CENTER></H3></FONT>

    94. 

  94. <% else %>

    95. 

  95. <H3><CENTER>    </CENTER></H3>

    96. 

  96. <% end if  %>

    97. 

  97. <% 

    98. 

  98. response.end

    99. 

  99. 

    100. 

  100. 

    101. 

  101. '-----------------------------------------------------------------------------------------------------

    102. 

  102. '------------------------------------------------------------------ List GL Groups under a super group

    103. 

  103. '-----------------------------------------------------------------------------------------------------

    104. 

  104. elseif request("act")="groups" then

    105. 

  105. SuperGroupID = request("SuperGroupID")

    106. 

  106. 

    107. 

  107. set 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. 

  108. 

    109. 

  109. %><BR><BR>

    110. 

  110. <TABLE dir=rtl align=center width=600>

    111. 

  111. <TR >

    112. 

  112. 	<TD colspan=4>

    113. 

  113. 		<%

    114. 

  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. 

  115. 		%><A HREF="AccountInfo.asp">  </A>> <A HREF="balance.asp?OpenGL=<%=RSS2("GLID")%>"> </A> > <%=RSS2("name")%>

    116. 

  116. 		<BR><hR>

    117. 

  117. 	</TD>

    118. 

  118. </TR>

    119. 

  119. <TR bgcolor="eeeeee" >

    120. 

  120. 	<TD><!A HREF="default.asp?s=1"><SMALL></SMALL></A></TD>

    121. 

  121. 	<TD><!A HREF="default.asp?s=2"><SMALL>  </SMALL></A></TD>

    122. 

  122. 	<TD><!A HREF="default.asp?s=4"><SMALL>  </SMALL></A></TD>

    123. 

  123. 	<TD><!A HREF="default.asp?s=4"><SMALL>  ()</SMALL></A></TD>

    124. 

  124. </TR>

    125. 

  125. <%

    126. 

  126. tmpCounter=0

    127. 

  127. Do while not RSS.eof

    128. 

  128. 	tmpCounter = tmpCounter + 1

    129. 

  129. 	if tmpCounter mod 2 = 1 then

    130. 

  130. 		tmpColor="#FFFFFF"

    131. 

  131. 		tmpColor2="#FFFFBB"

    132. 

  132. 	Else

    133. 

  133. 		tmpColor="#DDDDDD"

    134. 

  134. 		tmpColor2="#EEEEBB"

    135. 

  135. 	End if 

    136. 

  136. 

    137. 

  137. %>

    138. 

  138. <TR bgcolor="<%=tmpColor%>" >

    139. 

  139. 	<TD><A HREF="balance.asp?act=account&GroupID=<%=RSS("id")%>"><%=RSS("id")%></A></TD>

    140. 

  140. 	<TD><A HREF="balance.asp?act=account&GroupID=<%=RSS("id")%>"><%=RSS("Name")%></A></TD>

    141. 

  141. 	<TD><%=RSS("accountsCount")%></TD>

    142. 

  142. 	<TD><span dir=ltr><%=RSS("total")%></span></TD>

    143. 

  143. </TR>

    144. 

  144. 	  

    145. 

  145. <% 

    146. 

  146. RSS.moveNext

    147. 

  147. Loop

    148. 

  148. %>

    149. 

  149. </TABLE><br>

    150. 

  150. <%

    151. 

  151. 

    152. 

  152. '-----------------------------------------------------------------------------------------------------

    153. 

  153. '---------------------------------------------------------------------- List GL Accounts under a group

    154. 

  154. '-----------------------------------------------------------------------------------------------------

    155. 

  155. elseif request("act")="account" then

    156. 

  156. GroupID = request("GroupID")

    157. 

  157. 

    158. 

  158. set 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. 

  159. %><BR><BR>

    160. 

  160. <TABLE dir=rtl align=center width=600>

    161. 

  161. <TR >

    162. 

  162. 	<TD colspan=5>

    163. 

  163. 		<%

    164. 

  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. 

  165. 		

    166. 

  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. 

  167. 		<BR><hR>

    168. 

  168. 	</TD>

    169. 

  169. </TR>

    170. 

  170. <TR bgcolor="eeeeee" >

    171. 

  171. 	<TD><!A HREF="default.asp?s=1"><SMALL></SMALL></A></TD>

    172. 

  172. 	<TD><!A HREF="default.asp?s=2"><SMALL> </SMALL></A></TD>

    173. 

  173. 	<TD><!A HREF="default.asp?s=2"><SMALL> </SMALL></A></TD>

    174. 

  174. 	<TD><!A HREF="default.asp?s=2"><SMALL> </SMALL></A></TD>

    175. 

  175. </TR>

    176. 

  176. <%

    177. 

  177. tmpCounter=0

    178. 

  178. Do while not RSS2.eof

    179. 

  179. 	tmpCounter = tmpCounter + 1

    180. 

  180. 	if tmpCounter mod 2 = 1 then

    181. 

  181. 		tmpColor="#FFFFFF"

    182. 

  182. 		tmpColor2="#FFFFBB"

    183. 

  183. 	Else

    184. 

  184. 		tmpColor="#DDDDDD"

    185. 

  185. 		tmpColor2="#EEEEBB"

    186. 

  186. 	End if 

    187. 

  187. 

    188. 

  188. %>

    189. 

  189. <TR bgcolor="<%=tmpColor%>" >

    190. 

  190. 	<TD><A HREF="balance.asp?act=accountRows&accountID=<%=RSS2("id")%>"><%=RSS2("id")%></A></TD>

    191. 

  191. 	<TD><A HREF="balance.asp?act=accountRows&accountID=<%=RSS2("id")%>"><%=RSS2("Name")%></A></TD>

    192. 

  192. 	<TD><span dir=ltr><%=RSS2("totalDebit")%></span></TD>

    193. 

  193. 	<TD><span dir=ltr><%=RSS2("totalCredit")%></span></TD>

    194. 

  194. </TR>

    195. 

  195. 	  

    196. 

  196. <% 

    197. 

  197. RSS2.moveNext

    198. 

  198. Loop

    199. 

  199. %>

    200. 

  200. </TABLE><br>

    201. 

  201. <%

    202. 

  202. 

    203. 

  203. '-----------------------------------------------------------------------------------------------------

    204. 

  204. '-------------------------------------------------------------------------- List Rows of a GL Accounts 

    205. 

  205. '-----------------------------------------------------------------------------------------------------

    206. 

  206. elseif request("act")="accountRows" then

    207. 

  207. account = request("accountID")

    208. 

  208. 

    209. 

  209. set 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. 

  210. %><BR><BR>

    211. 

  211. <TABLE dir=rtl align=center width=600>

    212. 

  212. <TR >

    213. 

  213. 	<TD colspan=6>

    214. 

  214. 		<%

    215. 

  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. 

  216. 		

    217. 

  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. 

  218. 		<BR><hR>

    219. 

  219. 	</TD>

    220. 

  220. </TR>

    221. 

  221. <TR bgcolor="eeeeee" >

    222. 

  222. 	<TD><!A HREF="default.asp?s=1"><SMALL></SMALL></A></TD>

    223. 

  223. 	<TD><!A HREF="default.asp?s=2"><SMALL></SMALL></A></TD>

    224. 

  224. 	<TD><!A HREF="default.asp?s=2"><SMALL> </SMALL></A></TD>

    225. 

  225. 	<TD><!A HREF="default.asp?s=2"><SMALL> </SMALL></A></TD>

    226. 

  226. 	<TD><!A HREF="default.asp?s=5"><SMALL></SMALL></A></TD>

    227. 

  227. 	<TD><!A HREF="default.asp?s=5"><SMALL></SMALL></A></TD>

    228. 

  228. </TR>

    229. 

  229. <%

    230. 

  230. tmpCounter=0

    231. 

  231. Do while not RSS.eof

    232. 

  232. 	tmpCounter = tmpCounter + 1

    233. 

  233. 	if tmpCounter mod 2 = 1 then

    234. 

  234. 		tmpColor="#FFFFFF"

    235. 

  235. 		tmpColor2="#FFFFBB"

    236. 

  236. 	Else

    237. 

  237. 		tmpColor="#DDDDDD"

    238. 

  238. 		tmpColor2="#EEEEBB"

    239. 

  239. 	End if 

    240. 

  240. 	

    241. 

  241. 	debit = ""

    242. 

  242. 	credit = ""

    243. 

  243. 	if RSS("IsCredit") then

    244. 

  244. 		credit = RSS("Amount")

    245. 

  245. 	else

    246. 

  246. 		debit = RSS("Amount")

    247. 

  247. 	end if

    248. 

  248. %>

    249. 

  249. <TR bgcolor="<%=tmpColor%>" >

    250. 

  250. 	<TD><!A HREF="balance.asp?act=accountRows&accountID=<%=RSS("id")%>"><%=RSS("id")%></A></TD>

    251. 

  251. 	<TD><!A HREF="balance.asp?act=accountRows&accountID=<%=RSS("id")%>"><%=RSS("Description")%></A></TD>

    252. 

  252. 	<TD><A HREF="javascript:void(0);" onclick="window.open('GLMemoDocShow.asp?id=<%=RSS("GLDoc")%>',null,'menubars=no')"><%=RSS("GLDocID")%></A></TD>

    253. 

  253. 	<TD><%=RSS("GLDocDate")%></TD>

    254. 

  254. 	<TD><%=debit%></TD>

    255. 

  255. 	<TD><%=credit%></TD>

    256. 

  256. </TR>

    257. 

  257. 	  

    258. 

  258. <% 

    259. 

  259. RSS.moveNext

    260. 

  260. Loop

    261. 

  261. %>

    262. 

  262. </TABLE><br>

    263. 

  263. <%

    264. 

  264. end if

    265. 

  265. 

    266. 

  266. 

    267. 

  267. %>

    268. 

  268. 

    269. 

  269. <!--#include file="tah.asp" -->
    270.