/beta/AR/Rep_AInvoices.asp

http://github.com/khaneh/Orders · ASP · 395 lines · 347 code · 44 blank · 4 comment · 38 complexity · c8ddba09e60c4ef283b8ae9627b09615 MD5 · raw file

  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2. 'Order (2)
  3. PageTitle=" "
  4. SubmenuItem=11
  5. if not Auth("C" , 4) then NotAllowdToViewThisPage()
  6. %>
  7. <!--#include file="top.asp" -->
  8. <STYLE>
  9. .GetCustTbl {font-family:tahoma; background-color: #DDDDDD; width:630; direction: RTL; }
  10. .GetCustTbl td {padding:2; font-size: 9pt; height:25;}
  11. .GetCustInp { font-family:tahoma; font-size: 9pt;}
  12. .CusTableHeader {background-color: #33AACC; text-align: center; font-weight:bold;}
  13. .CustContactTable {font-family:tahoma; width:100%; border:1 solid black; direction: RTL; background-color:#CCCCCC;}
  14. .CustContactTable td {padding:5;}
  15. .CustTable {font-family:tahoma; width:80%; border:1 solid black; direction: RTL; background-color:black;}
  16. .CustTable td {padding:5;}
  17. .CustTable a {text-decoration:none;color:#000088}
  18. .CustTable a:hover {text-decoration:underline;}
  19. .CusTD1 {background-color: #CCCC66; text-align: left; font-weight:bold;}
  20. .CusTD2 {background-color: #DDDDDD; direction: LTR; text-align: right; font-size:9pt;}
  21. .CusTD3 {background-color: #DDDDDD; direction: LTR; text-align: center; font-size:9pt;}
  22. .CusTD4 {background-color: #CCCC66; direction: LTR; text-align: center; font-size:9pt;}
  23. .CustTable4 {font-family:tahoma; direction: RTL; width:100%; height:100%; background-color:#C3DBEB;}
  24. </STYLE>
  25. <%
  26. if request("act")="show" then
  27. ON ERROR RESUME NEXT
  28. ResultsInPage = cint(request("ResultsInPage"))
  29. FromDate = sqlSafe(request("FromDate"))
  30. ToDate = sqlSafe(request("ToDate"))
  31. if FromDate="" AND ToDate="" then
  32. pageTitle=" "
  33. elseif FromDate="" then
  34. pageTitle=" " & replace (ToDate,"/",".")
  35. elseif ToDate="" then
  36. pageTitle=" "& replace (FromDate,"/",".") & " "
  37. else
  38. pageTitle=" "& replace (FromDate,"/",".") & " " & replace (ToDate,"/",".")
  39. end if
  40. if ToDate = "" then ToDate = "9999/99/99"
  41. if Err.Number<>0 then
  42. Err.clear
  43. conn.close
  44. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  45. end if
  46. ON ERROR GOTO 0
  47. %>
  48. <br>
  49. <br>
  50. <table class="CustTable" cellspacing='1' style='width:680;' align='center'>
  51. <%
  52. mySumSQL="SELECT SUM(CONVERT(bigint, Invoices.TotalReceivable)) AS SumAmount FROM Invoices INNER JOIN ARItems ON Invoices.ID = ARItems.Link WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IsA = 1) AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') "
  53. Set RS1=Conn.Execute(mySumSQL)
  54. if NOT RS1.eof then
  55. SumAmount=RS1("SumAmount")
  56. %>
  57. <tr>
  58. <td colspan="2" class="CusTableHeader" style="text-align:right;height:35;"> (<%=pageTitle%>)</td>
  59. </tr>
  60. <tr>
  61. <td width="580px" class="CusTD3" style="text-align:left"> </td>
  62. <td width="100px" bgcolor="white" dir=LTR align=center><%=Separate(SumAmount)%></TD>
  63. </tr>
  64. <br><br>
  65. <%
  66. end if
  67. RS1.close
  68. Set RS1= Nothing
  69. mySumSQL="SELECT SUM(CONVERT(bigint, ARItems.RemainedAmount)) AS SumRemainedAmount FROM Invoices INNER JOIN ARItems ON Invoices.ID = ARItems.Link WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IsA = 1) AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') "
  70. Set RS1=Conn.Execute(mySumSQL)
  71. if NOT RS1.eof then
  72. SumRemainedAmount = RS1("SumRemainedAmount")
  73. %>
  74. <tr>
  75. <td width="580px" class="CusTD3" style="text-align:left"> </td>
  76. <td width="100px" bgcolor="white" dir=LTR align=center><%=Separate(SumRemainedAmount)%></TD>
  77. </tr>
  78. <br><br>
  79. <%
  80. end if
  81. RS1.close
  82. Set RS1= Nothing
  83. %>
  84. </table>
  85. <br>
  86. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  87. <tr>
  88. <td colspan="8" class="CusTableHeader" style="text-align:right;height:35;"> (<%=pageTitle%>)</td>
  89. </tr>
  90. <%
  91. mySQL="SELECT Invoices.ID, Invoices.IssuedDate, Invoices.Number, Invoices.TotalReceivable, Invoices.Customer, Accounts.AccountTitle, ARItems.RemainedAmount FROM Invoices INNER JOIN ARItems ON Invoices.ID = ARItems.Link INNER JOIN Accounts ON Invoices.Customer = Accounts.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IsA = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) ORDER BY CONVERT(int, Invoices.Number)"
  92. Set RS1 = Server.CreateObject("ADODB.Recordset")
  93. PageSize = ResultsInPage
  94. RS1.PageSize = PageSize
  95. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  96. RS1.Open mySQL ,Conn,3
  97. TotalPages = RS1.PageCount
  98. CurrentPage=1
  99. if isnumeric(Request.QueryString("p")) then
  100. pp=clng(Request.QueryString("p"))
  101. if pp <= TotalPages AND pp > 0 then
  102. CurrentPage = pp
  103. end if
  104. end if
  105. if not RS1.eof then
  106. RS1.AbsolutePage=CurrentPage
  107. end if
  108. if RS1.eof then
  109. %> <tr>
  110. <td colspan="8" class="CusTD3" style='direction:RTL;'> .</td>
  111. </tr>
  112. <% else
  113. %> <tr>
  114. <td class="CusTD3">#</td>
  115. <td class="CusTD3" style='direction:RTL;'># </td>
  116. <td class="CusTD3"> </td>
  117. <td class="CusTD3"> </td>
  118. <td class="CusTD3"> </td>
  119. <td class="CusTD3"> </td>
  120. <td class="CusTD3"></td>
  121. <td class="CusTD3"></td>
  122. </tr>
  123. <% tmpCounter=(CurrentPage - 1) * PageSize
  124. LastNumber=RS1("Number")
  125. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  126. tmpCounter = tmpCounter + 1
  127. if tmpCounter mod 2 = 1 then
  128. tmpColor="#FFFFFF"
  129. tmpColor2="#FFFFBB"
  130. Else
  131. tmpColor="#DDDDDD"
  132. tmpColor2="#EEEEBB"
  133. End if
  134. Number= RS1("Number")
  135. if trim("" & Number) = "" then
  136. tmpColor3="BGcolor='#FFBBBB'"
  137. LastNumber=0
  138. elseif clng(Number) > clng(LastNumber+1) then
  139. tmpColor3="BGcolor='#FFBBBB'"
  140. LastNumber=Number
  141. else
  142. tmpColor3=""
  143. LastNumber=Number
  144. end if
  145. %>
  146. <TR bgcolor="<%=tmpColor%>" style="cursor: pointer;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="window.open('../AR/AccountReport.asp?act=showInvoice&invoice=<%=RS1("ID")%>');">
  147. <TD style="height:30px;"><%=tmpCounter%></TD>
  148. <TD style="height:30px;"><%=RS1("ID")%></TD>
  149. <TD><%=RS1("AccountTitle")%>&nbsp;</TD>
  150. <TD><%=RS1("Customer")%>&nbsp;</TD>
  151. <TD dir="LTR" align='right' <%=tmpColor3%> ><%=Number%>&nbsp;</TD>
  152. <TD dir="LTR" align='right'><%=RS1("IssuedDate")%>&nbsp;</TD>
  153. <TD><%=Separate(RS1("TotalReceivable"))%>&nbsp;</TD>
  154. <TD><%=Separate(RS1("RemainedAmount"))%>&nbsp;</TD>
  155. </TR>
  156. <% RS1.moveNext
  157. Loop
  158. if ToDate="9999/99/99" then ToDate=""
  159. if TotalPages > 1 then
  160. pageCols=20
  161. %>
  162. <TR class="RepTableTitle">
  163. <TD bgcolor='#33AACC' height="30" colspan="8">
  164. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  165. <tr>
  166. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  167. <b> <%=CurrentPage%> <%=TotalPages%></b>
  168. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  169. </td>
  170. </tr>
  171. <tr>
  172. <% for i=1 to TotalPages
  173. if i = CurrentPage then
  174. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  175. <% else
  176. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  177. <% end if
  178. if i mod pageCols = 0 then response.write "</tr><tr>"
  179. next
  180. %> </tr>
  181. </table>
  182. <SCRIPT LANGUAGE="JavaScript">
  183. <!--
  184. function go2Page(p) {
  185. window.location="?act=show&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>";
  186. }
  187. //-->
  188. </SCRIPT>
  189. </TD>
  190. </TR>
  191. <% end if
  192. end if
  193. RS1.close
  194. Set RS1 = Nothing
  195. %>
  196. </table>
  197. <br>
  198. <%
  199. elseif request("act")="showByDay" then
  200. ON ERROR RESUME NEXT
  201. ResultsInPage = cint(request("ResultsInPage"))
  202. FromDate = sqlSafe(request("FromDate"))
  203. ToDate = sqlSafe(request("ToDate"))
  204. if FromDate="" AND ToDate="" then
  205. pageTitle=" "
  206. elseif FromDate="" then
  207. pageTitle=" " & replace (ToDate,"/",".")
  208. elseif ToDate="" then
  209. pageTitle=" "& replace (FromDate,"/",".") & " "
  210. else
  211. pageTitle=" "& replace (FromDate,"/",".") & " " & replace (ToDate,"/",".")
  212. end if
  213. if ToDate = "" then ToDate = "9999/99/99"
  214. if Err.Number<>0 then
  215. Err.clear
  216. conn.close
  217. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  218. end if
  219. ON ERROR GOTO 0
  220. %>
  221. <br>
  222. <br>
  223. <%
  224. mySumSQL="SELECT SUM(CONVERT(bigint, Invoices.TotalReceivable)) AS SumAmount FROM Invoices INNER JOIN ARItems ON Invoices.ID = ARItems.Link WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IsA = 1) AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') "
  225. Set RS1=Conn.Execute(mySumSQL)
  226. if NOT RS1.eof then
  227. SumAmount=RS1("SumAmount")
  228. %>
  229. <table class="CustTable" cellspacing='1' style='width:680;' align='center'>
  230. <tr>
  231. <td colspan="2" class="CusTableHeader" style="text-align:right;height:35;"> (<%=pageTitle%>)</td>
  232. </tr>
  233. <tr>
  234. <td width="580px" class="CusTD3" style="text-align:left"> </td>
  235. <td width="100px" bgcolor="white" dir=LTR align=center><%=Separate(SumAmount)%></TD>
  236. </tr>
  237. </table>
  238. <br><br>
  239. <%
  240. end if
  241. RS1.close
  242. Set RS1= Nothing
  243. %>
  244. <br>
  245. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  246. <tr>
  247. <td colspan="7" class="CusTableHeader" style="text-align:right;height:35;"> (<%=pageTitle%>)</td>
  248. </tr>
  249. <%
  250. mySQL="SELECT Invoices.IssuedDate, COUNT(Invoices.ID) AS CNT_Invoices, SUM(Invoices.TotalReceivable) AS SumPrice, COUNT(DISTINCT Invoices.Customer) AS CNT_Customers FROM Invoices INNER JOIN ARItems ON Invoices.ID = ARItems.Link WHERE (ARItems.GL = '"& openGL & "') AND (ARItems.Type = 1) AND (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IsA = 1) GROUP BY Invoices.IssuedDate HAVING (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') ORDER BY Invoices.IssuedDate"
  251. 'response.write mySQL
  252. 'response.end
  253. Set RS1 = Server.CreateObject("ADODB.Recordset")
  254. PageSize = ResultsInPage
  255. RS1.PageSize = PageSize
  256. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  257. RS1.Open mySQL ,Conn,3
  258. TotalPages = RS1.PageCount
  259. CurrentPage=1
  260. if isnumeric(Request.QueryString("p")) then
  261. pp=clng(Request.QueryString("p"))
  262. if pp <= TotalPages AND pp > 0 then
  263. CurrentPage = pp
  264. end if
  265. end if
  266. if not RS1.eof then
  267. RS1.AbsolutePage=CurrentPage
  268. end if
  269. if RS1.eof then
  270. %> <tr>
  271. <td colspan="7" class="CusTD3" style='direction:RTL;'> .</td>
  272. </tr>
  273. <% else
  274. %> <tr>
  275. <td class="CusTD3">#</td>
  276. <td class="CusTD3"></td>
  277. <td class="CusTD3"> </td>
  278. <td class="CusTD3"> </td>
  279. <td class="CusTD3"> </td>
  280. </tr>
  281. <% tmpCounter=(CurrentPage - 1) * PageSize
  282. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  283. tmpCounter = tmpCounter + 1
  284. if tmpCounter mod 2 = 1 then
  285. tmpColor="#FFFFFF"
  286. tmpColor2="#FFFFBB"
  287. Else
  288. tmpColor="#DDDDDD"
  289. tmpColor2="#EEEEBB"
  290. End if
  291. %>
  292. <TR bgcolor="<%=tmpColor%>" style="cursor: pointer;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="drill2date('<%=RS1("IssuedDate")%>');">
  293. <TD style="height:30px;"><%=tmpCounter%></TD>
  294. <TD dir="LTR" align='center'><%=RS1("IssuedDate")%>&nbsp;</TD>
  295. <TD align='center'><%=RS1("CNT_Invoices")%>&nbsp;</TD>
  296. <TD align='center'><%=RS1("CNT_Customers")%>&nbsp;</TD>
  297. <TD><%=Separate(RS1("SumPrice"))%>&nbsp;</TD>
  298. </TR>
  299. <% RS1.moveNext
  300. Loop
  301. if ToDate="9999/99/99" then ToDate=""
  302. if TotalPages > 1 then
  303. pageCols=20
  304. %>
  305. <TR class="RepTableTitle">
  306. <TD bgcolor='#33AACC' height="30" colspan="7">
  307. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  308. <tr>
  309. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  310. <b> <%=CurrentPage%> <%=TotalPages%></b>
  311. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  312. </td>
  313. </tr>
  314. <tr>
  315. <% for i=1 to TotalPages
  316. if i = CurrentPage then
  317. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  318. <% else
  319. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  320. <% end if
  321. if i mod pageCols = 0 then response.write "</tr><tr>"
  322. next
  323. %> </tr>
  324. </table>
  325. <SCRIPT LANGUAGE="JavaScript">
  326. <!--
  327. function go2Page(p) {
  328. window.location="?act=showByDay&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>";
  329. }
  330. //-->
  331. </SCRIPT>
  332. </TD>
  333. </TR>
  334. <% end if
  335. end if
  336. RS1.close
  337. Set RS1 = Nothing
  338. 'Rep_AInvoices.asp?act=show&ResultsInPage=50&p=2&FromDate=1384/01/01&ToDate=
  339. %>
  340. </table>
  341. <br>
  342. <SCRIPT LANGUAGE="JavaScript">
  343. <!--
  344. function drill2date(date) {
  345. window.open('?act=show&ResultsInPage=50&p=1&FromDate='+date+'&ToDate='+date);
  346. }
  347. //-->
  348. </SCRIPT>
  349. <%
  350. end if%>
  351. <!--#include file="tah.asp" -->