/beta/AR/Rep_BInvoices.asp

http://github.com/khaneh/Orders · ASP · 385 lines · 331 code · 46 blank · 8 comment · 37 complexity · d9b27a0880c6eeee35850a1ca3f5c4dd 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. <%
  51. 'this query change by sam
  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 = 0) 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. <table class="CustTable" cellspacing='1' style='width:680;' align='center'>
  58. <tr>
  59. <td colspan="2" class="CusTableHeader" style="text-align:right;height:35;">ǘ (<%=pageTitle%>)</td>
  60. </tr>
  61. <tr>
  62. <td width="580px" class="CusTD3" style="text-align:left"> </td>
  63. <td width="100px" bgcolor="white" dir=LTR align=center><%=Separate(SumAmount)%></TD>
  64. </tr>
  65. </table>
  66. <br><br>
  67. <%
  68. end if
  69. RS1.close
  70. Set RS1= Nothing
  71. %>
  72. <br>
  73. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  74. <tr>
  75. <td colspan="8" class="CusTableHeader" style="text-align:right;height:35;">ǘ (<%=pageTitle%>)</td>
  76. </tr>
  77. <%
  78. 'this query change by sam
  79. 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 = 0) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) ORDER BY CONVERT(int, Invoices.Number)"
  80. Set RS1 = Server.CreateObject("ADODB.Recordset")
  81. PageSize = ResultsInPage
  82. RS1.PageSize = PageSize
  83. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  84. RS1.Open mySQL ,Conn,3
  85. TotalPages = RS1.PageCount
  86. CurrentPage=1
  87. if isnumeric(Request.QueryString("p")) then
  88. pp=clng(Request.QueryString("p"))
  89. if pp <= TotalPages AND pp > 0 then
  90. CurrentPage = pp
  91. end if
  92. end if
  93. if not RS1.eof then
  94. RS1.AbsolutePage=CurrentPage
  95. end if
  96. if RS1.eof then
  97. %> <tr>
  98. <td colspan="8" class="CusTD3" style='direction:RTL;'> .</td>
  99. </tr>
  100. <% else
  101. %> <tr>
  102. <td class="CusTD3">#</td>
  103. <td class="CusTD3" style='direction:RTL;'># </td>
  104. <td class="CusTD3"> </td>
  105. <td class="CusTD3"> </td>
  106. <td class="CusTD3"> </td>
  107. <td class="CusTD3"></td>
  108. <td class="CusTD3"></td>
  109. </tr>
  110. <% tmpCounter=(CurrentPage - 1) * PageSize
  111. LastNumber=RS1("Number")
  112. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  113. tmpCounter = tmpCounter + 1
  114. if tmpCounter mod 2 = 1 then
  115. tmpColor="#FFFFFF"
  116. tmpColor2="#FFFFBB"
  117. Else
  118. tmpColor="#DDDDDD"
  119. tmpColor2="#EEEEBB"
  120. End if
  121. Number= RS1("Number")
  122. if trim("" & Number) = "" then
  123. tmpColor3="BGcolor='#FFBBBB'"
  124. LastNumber=0
  125. elseif clng(Number) > clng(LastNumber+1) then
  126. tmpColor3="BGcolor='#FFBBBB'"
  127. LastNumber=Number
  128. else
  129. tmpColor3=""
  130. LastNumber=Number
  131. end if
  132. %>
  133. <TR bgcolor="<%=tmpColor%>" style="cursor: hand;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="window.open('../AR/AccountReport.asp?act=showInvoice&invoice=<%=RS1("ID")%>');">
  134. <TD style="height:30px;"><%=tmpCounter%></TD>
  135. <TD style="height:30px;"><%=RS1("ID")%></TD>
  136. <TD><%=RS1("AccountTitle")%>&nbsp;</TD>
  137. <TD><%=RS1("Customer")%>&nbsp;</TD>
  138. <TD dir="LTR" align='right'><%=RS1("IssuedDate")%>&nbsp;</TD>
  139. <TD><%=Separate(RS1("TotalReceivable"))%>&nbsp;</TD>
  140. <TD><%=Separate(RS1("RemainedAmount"))%>&nbsp;</TD>
  141. </TR>
  142. <% RS1.moveNext
  143. Loop
  144. if ToDate="9999/99/99" then ToDate=""
  145. if TotalPages > 1 then
  146. pageCols=20
  147. %>
  148. <TR class="RepTableTitle">
  149. <TD bgcolor='#33AACC' height="30" colspan="8">
  150. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  151. <tr>
  152. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  153. <b> <%=CurrentPage%> <%=TotalPages%></b>
  154. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  155. </td>
  156. </tr>
  157. <tr>
  158. <% for i=1 to TotalPages
  159. if i = CurrentPage then
  160. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  161. <% else
  162. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  163. <% end if
  164. if i mod pageCols = 0 then response.write "</tr><tr>"
  165. next
  166. %> </tr>
  167. </table>
  168. <SCRIPT LANGUAGE="JavaScript">
  169. <!--
  170. function go2Page(p) {
  171. window.location="?act=show&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>";
  172. }
  173. //-->
  174. </SCRIPT>
  175. </TD>
  176. </TR>
  177. <% end if
  178. end if
  179. RS1.close
  180. Set RS1 = Nothing
  181. %>
  182. </table>
  183. <br>
  184. <%
  185. elseif request("act")="showByDay" then
  186. ON ERROR RESUME NEXT
  187. ResultsInPage = cint(request("ResultsInPage"))
  188. FromDate = sqlSafe(request("FromDate"))
  189. ToDate = sqlSafe(request("ToDate"))
  190. if FromDate="" AND ToDate="" then
  191. pageTitle=" "
  192. elseif FromDate="" then
  193. pageTitle=" " & replace (ToDate,"/",".")
  194. elseif ToDate="" then
  195. pageTitle=" "& replace (FromDate,"/",".") & " "
  196. else
  197. pageTitle=" "& replace (FromDate,"/",".") & " " & replace (ToDate,"/",".")
  198. end if
  199. if ToDate = "" then ToDate = "9999/99/99"
  200. if Err.Number<>0 then
  201. Err.clear
  202. conn.close
  203. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  204. end if
  205. ON ERROR GOTO 0
  206. %>
  207. <br>
  208. <br>
  209. <%
  210. 'this query change by sam
  211. 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 = 0) AND (ARItems.GL = "& openGL & ") AND (ARItems.Type = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') "
  212. Set RS1=Conn.Execute(mySumSQL)
  213. if NOT RS1.eof then
  214. SumAmount=RS1("SumAmount")
  215. %>
  216. <table class="CustTable" cellspacing='1' style='width:680;' align='center'>
  217. <tr>
  218. <td colspan="2" class="CusTableHeader" style="text-align:right;height:35;">ǘ (<%=pageTitle%>)</td>
  219. </tr>
  220. <tr>
  221. <td width="580px" class="CusTD3" style="text-align:left"> </td>
  222. <td width="100px" bgcolor="white" dir=LTR align=center><%=Separate(SumAmount)%></TD>
  223. </tr>
  224. </table>
  225. <br><br>
  226. <%
  227. end if
  228. RS1.close
  229. Set RS1= Nothing
  230. %>
  231. <br>
  232. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  233. <tr>
  234. <td colspan="7" class="CusTableHeader" style="text-align:right;height:35;">ǘ (<%=pageTitle%>)</td>
  235. </tr>
  236. <%
  237. 'this query change by sam
  238. 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 = 0) GROUP BY Invoices.IssuedDate HAVING (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') ORDER BY Invoices.IssuedDate"
  239. 'response.write mySQL
  240. 'response.end
  241. Set RS1 = Server.CreateObject("ADODB.Recordset")
  242. PageSize = ResultsInPage
  243. RS1.PageSize = PageSize
  244. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  245. RS1.Open mySQL ,Conn,3
  246. TotalPages = RS1.PageCount
  247. CurrentPage=1
  248. if isnumeric(Request.QueryString("p")) then
  249. pp=clng(Request.QueryString("p"))
  250. if pp <= TotalPages AND pp > 0 then
  251. CurrentPage = pp
  252. end if
  253. end if
  254. if not RS1.eof then
  255. RS1.AbsolutePage=CurrentPage
  256. end if
  257. if RS1.eof then
  258. %> <tr>
  259. <td colspan="7" class="CusTD3" style='direction:RTL;'> .</td>
  260. </tr>
  261. <% else
  262. %> <tr>
  263. <td class="CusTD3">#</td>
  264. <td class="CusTD3"></td>
  265. <td class="CusTD3"> ǘ </td>
  266. <td class="CusTD3"> </td>
  267. <td class="CusTD3"> </td>
  268. </tr>
  269. <% tmpCounter=(CurrentPage - 1) * PageSize
  270. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  271. tmpCounter = tmpCounter + 1
  272. if tmpCounter mod 2 = 1 then
  273. tmpColor="#FFFFFF"
  274. tmpColor2="#FFFFBB"
  275. Else
  276. tmpColor="#DDDDDD"
  277. tmpColor2="#EEEEBB"
  278. End if
  279. %>
  280. <TR bgcolor="<%=tmpColor%>" style="cursor: hand;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="drill2date('<%=RS1("IssuedDate")%>');">
  281. <TD style="height:30px;"><%=tmpCounter%></TD>
  282. <TD dir="LTR" align='center'><%=RS1("IssuedDate")%>&nbsp;</TD>
  283. <TD align='center'><%=RS1("CNT_Invoices")%>&nbsp;</TD>
  284. <TD align='center'><%=RS1("CNT_Customers")%>&nbsp;</TD>
  285. <TD><%=Separate(RS1("SumPrice"))%>&nbsp;</TD>
  286. </TR>
  287. <% RS1.moveNext
  288. Loop
  289. if ToDate="9999/99/99" then ToDate=""
  290. if TotalPages > 1 then
  291. pageCols=20
  292. %>
  293. <TR class="RepTableTitle">
  294. <TD bgcolor='#33AACC' height="30" colspan="7">
  295. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  296. <tr>
  297. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  298. <b> <%=CurrentPage%> <%=TotalPages%></b>
  299. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  300. </td>
  301. </tr>
  302. <tr>
  303. <% for i=1 to TotalPages
  304. if i = CurrentPage then
  305. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  306. <% else
  307. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  308. <% end if
  309. if i mod pageCols = 0 then response.write "</tr><tr>"
  310. next
  311. %> </tr>
  312. </table>
  313. <SCRIPT LANGUAGE="JavaScript">
  314. <!--
  315. function go2Page(p) {
  316. window.location="?act=showByDay&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>";
  317. }
  318. //-->
  319. </SCRIPT>
  320. </TD>
  321. </TR>
  322. <% end if
  323. end if
  324. RS1.close
  325. Set RS1 = Nothing
  326. 'Rep_AInvoices.asp?act=show&ResultsInPage=50&p=2&FromDate=1384/01/01&ToDate=
  327. %>
  328. </table>
  329. <br>
  330. <SCRIPT LANGUAGE="JavaScript">
  331. <!--
  332. function drill2date(date) {
  333. window.open('?act=show&ResultsInPage=50&p=1&FromDate='+date+'&ToDate='+date);
  334. }
  335. //-->
  336. </SCRIPT>
  337. <%
  338. end if%>
  339. <!--#include file="tah.asp" -->