/beta/cashReg/CashRegAdminReport.asp

http://github.com/khaneh/Orders · ASP · 559 lines · 514 code · 37 blank · 8 comment · 58 complexity · e2efd9a76d7e2099be5db75530773661 MD5 · raw file

  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2. 'CashRegister (9)
  3. PageTitle=" с "
  4. SubmenuItem=6
  5. if not Auth(9 , 6) then NotAllowdToViewThisPage()
  6. %>
  7. <!--#include file="top.asp" -->
  8. <!--#include File="../include_farsiDateHandling.asp"-->
  9. <!--#include File="../include_JS_InputMasks.asp"-->
  10. <STYLE>
  11. .CClosTable {font-family:tahoma; font-size:9pt; direction: RTL; border:2 solid #330099;}
  12. .CClosTable td {padding:5;border:1pt solid gray;}
  13. .CClosTable a {text-decoration:none; color:#222288;}
  14. .CClosTable a:hover {text-decoration:underline;}
  15. .CClosTableTitle {background-color: #CCCCFF; text-align: center; font-weight:bold; height:50;}
  16. .CClosTableHeader {background-color: #BBBBBB; text-align: center; font-weight:bold;}
  17. .CClosTableFooter {background-color: #BBBBBB; direction: LTR; }
  18. .CClosTR1 {background-color: #DDDDDD;}
  19. .CClosTR2 {background-color: #FFFFFF;}
  20. .CClosTR3 {background-color: #EEEEFF;}
  21. .RepTable {font-family:tahoma; font-size:9pt; direction: RTL; }
  22. .RepTable td {padding:5;border:1pt solid gray;}
  23. .RepTable a {text-decoration:none; color:#222288;}
  24. .RepTable a:hover {text-decoration:underline;}
  25. .RepTableTitle {background-color: #CCCCFF; text-align: center; font-weight:bold; height:50;}
  26. .RepTableHeader {background-color: #BBBBBB; text-align: center; font-weight:bold;}
  27. .RepTableFooter {background-color: #BBBBBB; direction: LTR; }
  28. .RepTR1 {background-color: #DDDDDD;}
  29. .RepTR2 {background-color: #FFFFFF;}
  30. .GenInput { font-family:tahoma; font-size: 9pt;}
  31. .GenButton { font-family:tahoma; font-size: 9pt; border: 1px solid black; }
  32. </STYLE>
  33. <%
  34. function Link2Trace(OrderNo)
  35. Link2Trace="<A HREF='../order/TraceOrder.asp?act=show&order="& OrderNo & "' target='_balnk'>"& OrderNo & "</A>"
  36. end function
  37. if request("act")="showCashRegReport" AND request("CashRegID")<>"" then
  38. CashRegID=request("CashRegID")
  39. if not isnumeric(CashRegID) then
  40. response.write "<br>"
  41. call showAlert (" ",CONST_MSG_ERROR)
  42. response.end
  43. end if
  44. mySQL="SELECT Bankers.Name AS BankerName, CashRegisters.*, Users.RealName FROM CashRegisters INNER JOIN Users ON CashRegisters.Cashier = Users.ID INNER JOIN Bankers ON CashRegisters.Banker = Bankers.ID WHERE (CashRegisters.ID='"& CashRegID & "')"
  45. Set RS1= conn.Execute(mySQL)
  46. if RS1.eof then
  47. response.write "<br>"
  48. call showAlert (" ",CONST_MSG_ERROR)
  49. response.end
  50. else
  51. BankerName=RS1("BankerName")
  52. CashRegName=RS1("NameDate")
  53. CashierName=RS1("RealName")
  54. OpenningDate=RS1("OpenDate")
  55. OpenningAmount=cdbl(RS1("OpeningAmount"))
  56. totalCashAmountA=Separate(RS1("cashAmountA")) ' SAM
  57. totalCashAmountB=Separate(RS1("cashAmountB")) ' SAM
  58. totalChequeAmount=Separate(RS1("chequeAmount"))
  59. totalChequeQtty=RS1("chequeQtty")
  60. TotalAmount=Separate(cdbl(RS1("cashAmountA")) + cdbl(RS1("cashAmountB")) + cdbl(RS1("chequeAmount")))
  61. Set RS1=nothing
  62. end if
  63. mySQL="SELECT CashRegisterLines.*, ISNULL(Receipts.CashAmount,0) AS CashAmount, ISNULL(Receipts.ChequeAmount,0) AS ChequeAmount, Receipts.ChequeQtty, Payments.CashAmount AS PayAmount, Accounts_1.AccountTitle AS PayAccTitle, Accounts_2.AccountTitle AS RcpAccTitle, Accounts_1.ID AS PayAccID, Accounts_2.ID AS RcpAccID FROM CashRegisterLines LEFT OUTER JOIN Accounts Accounts_1 INNER JOIN Payments ON Accounts_1.ID = Payments.Account ON CashRegisterLines.Link = Payments.ID LEFT OUTER JOIN Accounts Accounts_2 INNER JOIN Receipts ON Accounts_2.ID = Receipts.Customer ON CashRegisterLines.Link = Receipts.ID WHERE (CashRegisterLines.CashReg = '"& CashRegID & "') ORDER BY CashRegisterLines.ID"
  64. Set RS1 = conn.execute(mySQL)
  65. Remained = 0
  66. Totalcredit = 0
  67. TotalDebit = 0
  68. tempCounter = 0
  69. if Not (RS1.EOF AND OpenningAmount=0) then
  70. %>
  71. <br>
  72. <TABLE class="RepTable" width='100%' align='center'>
  73. <TR>
  74. <TD class="RepTableTitle" colspan=9 dir='rtl' align='center'><br><B> <span dir='LTR'><%=CashRegName%></span> - <%=CashierName%></B><BR>(: <%=BankerName%>)<br>
  75. </TD>
  76. </TR>
  77. <TR class="RepTableHeader">
  78. <TD>#</TD>
  79. <TD width="50px"></TD>
  80. <TD width="10px"></TD>
  81. <TD width="60px"></TD>
  82. <td width="30px">/</td>
  83. <TD></TD>
  84. <TD width="60px"></TD>
  85. <TD width="60px"></TD>
  86. <TD width="60px"></TD>
  87. </TR>
  88. <%
  89. if OpenningAmount<>0 then
  90. Remained = OpenningAmount
  91. Credit = OpenningAmount
  92. Totalcredit = OpenningAmount
  93. tempCounter = 0
  94. %> <TR class='<%if tempCounter MOD 2 = 0 then response.write "RepTR1" else response.write "RepTR2"%>'>
  95. <td><%=tempCounter%></td>
  96. <td dir='LTR'><%=CashRegName%></td>
  97. <td colspan='3'> </td>
  98. <td dir='LTR' align='right'><%=Separate(Debit)%>&nbsp;</td>
  99. <td dir='LTR' align='right'><%=Separate(Credit)%>&nbsp;</td>
  100. <td dir='LTR' align='right'><%=Separate(Remained)%>&nbsp;</td>
  101. </TR>
  102. <% end if
  103. While Not (RS1.EOF)
  104. tempCounter=tempCounter+1
  105. if RS1("Type")=1 then
  106. sourceLink="../AR/AccountReport.asp?act=showReceipt&receipt="& RS1("Link")
  107. ' voidLink="Void.asp?act=voidReceipt&receipt="& RS1("Link")
  108. Description = " "
  109. myAND=""
  110. CashAmount=cdbl(RS1("CashAmount"))
  111. ChequeAmount=cdbl(RS1("ChequeAmount"))
  112. if CashAmount<>0 then
  113. Description=Description & " "
  114. myAND=" "
  115. end if
  116. if RS1("ChequeQtty")<>0 then
  117. Description=Description & myAND & RS1("ChequeQtty") & " "
  118. end if
  119. AccID=RS1("RcpAccID")
  120. AccTitle=RS1("RcpAccTitle")
  121. Credit= CashAmount + ChequeAmount
  122. Debit=0
  123. elseif RS1("Type")=2 then
  124. sourceLink="../AR/AccountReport.asp?act=showPayment&payment="& RS1("Link")
  125. ' voidLink="Void.asp?act=voidPayment&payment="& RS1("Link")
  126. Description = " "
  127. AccID=RS1("PayAccID")
  128. AccTitle=RS1("PayAccTitle")
  129. Credit=0
  130. Debit=RS1("PayAmount")
  131. else
  132. sourceLink="javascript:void(0);"
  133. ' voidLink="javascript:void(0);"
  134. Description = ""
  135. AccountInfo=""
  136. Credit=0
  137. Debit=0
  138. end if
  139. TotalDebit = TotalDebit + CLng(Debit)
  140. Totalcredit = Totalcredit + CLng(Credit)
  141. Remained = Remained + CLng(Credit) - CLng(Debit)
  142. %> <TR class='<%if tempCounter MOD 2 = 0 then response.write "RepTR1" else response.write "RepTR2"%>'>
  143. <td><%=tempCounter%></td>
  144. <td dir='LTR'><%=RS1("Date")%></td>
  145. <td dir='LTR'><%=RS1("Time")%></td>
  146. <td><A HREF='<%=sourceLink%>' target='_blank'><%=Description%></A></td>
  147. <td align="center"><%
  148. if CBool(rs1("isA")) then
  149. response.write("<b></b>")
  150. else
  151. response.write("")
  152. end if
  153. %></td>
  154. <td><A HREF='../AR/AccountReport.asp?act=show&selectedCustomer=<%=AccID%>' target='_blank'><%=AccTitle%></A></td>
  155. <td dir='LTR' align='right'><%=Separate(Debit)%></td>
  156. <td dir='LTR' align='right'><%=Separate(Credit) %></td>
  157. <td dir='LTR' align='right'>
  158. <% if RS1("Voided")=TRUE then
  159. %> <div style="position:absolute;width:650;"><hr style="color:red;"></div>
  160. <% response.write "xx xx xx" '& Separate(remained) & "xx"
  161. TotalDebit = TotalDebit - CLng(Debit)
  162. Totalcredit = Totalcredit - CLng(Credit)
  163. Remained = Remained - CLng(Credit) + CLng(Debit)
  164. else
  165. response.write Separate(remained)
  166. end if
  167. %> </td>
  168. </TR>
  169. <%
  170. RS1.MoveNext
  171. Wend
  172. if Remained>=0 then
  173. remainedColor="green"
  174. else
  175. remainedColor="red"
  176. end if
  177. %>
  178. <TR>
  179. <TD class="RepTableFooter" colspan='6'>&nbsp;&nbsp; : </td>
  180. <TD class="RepTableFooter" align='right'><%=Separate(totaldebit)%></td>
  181. <TD class="RepTableFooter" align='right'><%=Separate(totalcredit)%></td>
  182. <TD class="RepTableFooter" align='right'><FONT COLOR='<%=remainedColor%>'><%=Separate(Remained)%></FONT></td>
  183. </TR>
  184. </TABLE>
  185. <br>
  186. <TABLE class="CClosTable" width='90%' align='center' style="page-break-before:always;">
  187. <%
  188. mySQL="SELECT ReceivedCheques.ChequeNo, ReceivedCheques.ChequeDate, ReceivedCheques.Description, ReceivedCheques.BankOfOrigin, receivedCheques.Amount, CashRegisterLines.* FROM ReceivedCheques INNER JOIN Receipts ON ReceivedCheques.Receipt = Receipts.ID INNER JOIN CashRegisterLines ON Receipts.ID = CashRegisterLines.Link WHERE (CashRegisterLines.CashReg = '"& CashRegID & "') AND (CashRegisterLines.Type = 1) ORDER BY CashRegisterLines.[Date], CashRegisterLines.[Time]"
  189. Set RS1 = conn.execute(mySQL)
  190. Remained = OpenningAmount
  191. Credit = OpenningAmount
  192. Totalcredit = OpenningAmount
  193. TotalDebit = 0
  194. tempCounter = 0
  195. %> <TR class='CClosTR3'>
  196. <td colspan='7' align='center'> <span dir='LTR'><%=CashRegName%></span> (: <%=CashierName%> : <%=BankerName%>)</TD>
  197. </TR>
  198. <TR>
  199. <TD colspan='6' class="CClosTableFooter">: </td>
  200. <TD class="CClosTableFooter" align='right'><span id='cashAmount'><%=Separate(cdbl(totalCashAmountA) + Cdbl(totalCashAmountB))%></span></TD>
  201. </TR>
  202. <TR>
  203. <TD colspan='6' class="CClosTableFooter" style='direction:RTL;text-align=left;'> (<span id='chequeQtty'><%=totalChequeQtty%></span> ) :</td>
  204. <TD class="CClosTableFooter" align='right'><%=totalChequeAmount%></TD>
  205. </TR>
  206. <TR>
  207. <TD colspan='6' class="CClosTableFooter">: </td>
  208. <TD class="CClosTableFooter" align='right'><%=TotalAmount%></td>
  209. </TR>
  210. <TR class='CClosTR3'>
  211. <td colspan='7' align='right'> :</td>
  212. </TR>
  213. <TR class="CClosTableHeader">
  214. <TD width="10px">#</TD>
  215. <TD width="100px"> </TD>
  216. <TD width="50px"></TD>
  217. <TD width="50px"></TD>
  218. <TD width="70px"> </TD>
  219. <TD width="220px"></TD>
  220. <TD width="70px"></TD>
  221. </TR>
  222. <%
  223. While Not (RS1.EOF)
  224. tempCounter=tempCounter+1
  225. sourceLink="../AR/AccountReport.asp?act=showReceipt&receipt="& RS1("Link")
  226. Description = " "
  227. myAND=""
  228. Amount=Separate(RS1("Amount"))
  229. if RS1("Voided") then
  230. tempCounter=tempCounter-1%>
  231. <TR class='CClosTR4'>
  232. <td>-</td>
  233. <td dir='LTR'><A HREF='<%=sourceLink%>' target='_blank'><%=RS1("Date") & " @ " & RS1("Time")%></A></td>
  234. <td><%=RS1("ChequeNo")%></td>
  235. <td><%=RS1("BankOfOrigin")%></td>
  236. <td dir='LTR'><%=RS1("ChequeDate")%></td>
  237. <td><%=RS1("Description")%></td>
  238. <td dir='LTR'>
  239. <div style="position:absolute;width:600;"><hr style="color:red;"></div><div align='right'><%=Amount%></div>
  240. <% else%>
  241. <TR class='<%if tempCounter MOD 2 = 0 then response.write "CClosTR1" else response.write "CClosTR2"%>'>
  242. <td><%=tempCounter%></td>
  243. <td dir='LTR'><A HREF='<%=sourceLink%>' target='_blank'><%=RS1("Date") & " @ " & RS1("Time")%></A></td>
  244. <td><%=RS1("ChequeNo")%></td>
  245. <td><%=RS1("BankOfOrigin")%></td>
  246. <td dir='LTR'><%=RS1("ChequeDate")%></td>
  247. <td><%=RS1("Description")%></td>
  248. <td dir='LTR' align='right'>
  249. <%=Amount%>
  250. <% end if
  251. %> </td>
  252. </TR>
  253. <%
  254. RS1.MoveNext
  255. Wend
  256. %>
  257. </TABLE>
  258. <br>
  259. <% end if
  260. else
  261. %>
  262. <BR><BR>
  263. <TABLE class="RepTable" align='center'>
  264. <TR class="RepTableTitle">
  265. <TD colspan=<% if Auth(9,9) then response.write "11" else response.write "6" end if%>> </TD>
  266. </TR>
  267. <%
  268. Banker = request("Banker")
  269. connector = ""
  270. Criteria = ""
  271. if isnumeric(Banker) then Banker = cint(Banker) else Banker = 0
  272. if Banker <> 0 then
  273. Criteria = Criteria & connector & "(CashRegisters.Banker = " & Banker & ")"
  274. connector = " AND "
  275. end if
  276. Cashier = request("Cashier")
  277. if isnumeric(Cashier) then Cashier = cint(Cashier) else Cashier = 0
  278. if Cashier <> 0 then
  279. Criteria = Criteria & connector & "(CashRegisters.Cashier = " & Cashier & ")"
  280. connector = " AND "
  281. end if
  282. openDateStart = sqlSafe(request("openDateStart"))
  283. openDateEnd = sqlSafe(request("openDateEnd"))
  284. if openDateStart <> "" then
  285. Criteria = Criteria & connector & "(CashRegisters.openDate >= '" & openDateStart & "')"
  286. connector = " AND "
  287. end if
  288. if openDateEnd <> "" then
  289. Criteria = Criteria & connector & "(CashRegisters.openDate <= '" & openDateEnd & "')"
  290. connector = " AND "
  291. end if
  292. closeDateStart= sqlSafe(request("closeDateStart"))
  293. closeDateEnd = sqlSafe(request("closeDateEnd"))
  294. if closeDateStart <> "" then
  295. Criteria = Criteria & connector & "(CashRegisters.closeDate >= '" & closeDateStart & "')"
  296. connector = " AND "
  297. end if
  298. if closeDateEnd <> "" then
  299. Criteria = Criteria & connector & "(CashRegisters.closeDate <= '" & closeDateEnd & "')"
  300. connector = " AND "
  301. end if
  302. hasTheDate = sqlSafe(request("hasTheDate"))
  303. if hasTheDate <> "" then
  304. Criteria = Criteria & connector & "(CashRegisters.openDate <= '" & hasTheDate & "') AND (CashRegisters.closeDate >= '" & hasTheDate & "')"
  305. connector = " AND "
  306. end if
  307. if Criteria <> "" then Criteria = " WHERE " & Criteria
  308. Sort = request("Sort")
  309. if isnumeric(Sort) then Sort = cint(Sort) else Sort = 0
  310. OrderBy=""
  311. Select Case Sort
  312. Case 0:
  313. OrderBy="ORDER BY CashRegisters.IsOpen DESC, CashRegisters.OpenDate DESC"
  314. Case 1:
  315. OrderBy="ORDER BY CashRegisters.NameDate DESC"
  316. Case 2:
  317. OrderBy="ORDER BY CashierName"
  318. Case 3:
  319. OrderBy="ORDER BY BankerName"
  320. Case 4:
  321. OrderBy="ORDER BY CashRegisters.OpenDate DESC"
  322. Case 5:
  323. OrderBy="ORDER BY CashRegisters.CloseDate DESC"
  324. End Select
  325. PageLink = "Banker=" & Banker & "&Cashier=" & Cashier & "&openDateStart=" & openDateStart & "&openDateEnd=" & openDateEnd & "&closeDateStart=" & closeDateStart & "&closeDateEnd=" & closeDateEnd & "&hasTheDate=" & hasTheDate & "&Sort=" & Sort
  326. %>
  327. <FORM METHOD=POST ACTION="?">
  328. <TR class="RepTableTitle">
  329. <TD colspan=<% if Auth(9,9) then response.write "11" else response.write "6" end if%>><table border=0 width=100%>
  330. <tr>
  331. <td align='left'>:</td>
  332. <td colspan=2><SELECT NAME="Banker" class="GenInput">
  333. <option Value=""> -- -- </option>
  334. <% mySQL="SELECT * FROM Bankers WHERE (IsBankAccount <> 1)"
  335. Set RS1 = conn.Execute(mySQL)
  336. do while not (RS1.eof)
  337. if cint(RS1("ID")) = Banker then selectedText="selected" else selectedText=""
  338. %> <option Value="<%=RS1("ID")%>" <%=selectedText%>><%=RS1("Name")%></option>
  339. <%
  340. RS1.movenext
  341. loop
  342. RS1.close
  343. %> </SELECT>
  344. </td>
  345. <td align='left'>:</td>
  346. <td colspan=2><SELECT NAME="Cashier" class="GenInput">
  347. <option Value=""> -- -- </option>
  348. <% mySQL="SELECT * FROM Users WHERE Display=1 ORDER BY RealName"
  349. Set RS1 = conn.Execute(mySQL)
  350. do while not (RS1.eof)
  351. if cint(RS1("ID")) = Cashier then selectedText="selected" else selectedText=""
  352. %> <option Value="<%=RS1("ID")%>" <%=selectedText%>><%=RS1("RealName")%></option>
  353. <%
  354. RS1.movenext
  355. loop
  356. RS1.close
  357. %> </SELECT>
  358. </td>
  359. </tr>
  360. <tr>
  361. <td align='left'> :</td>
  362. <td>:<INPUT TYPE="text" NAME="openDateStart" value="<%=openDateStart%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  363. <td>:<INPUT TYPE="text" NAME="openDateEnd" value="<%=openDateEnd%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  364. <td> :</td>
  365. <td colspan=2><SELECT NAME="Sort" class="GenInput">
  366. <option Value="0" <%if Sort = 0 then response.write "selected"%>> </option>
  367. <option Value="1" <%if Sort = 1 then response.write "selected"%>> </option>
  368. <option Value="2" <%if Sort = 2 then response.write "selected"%>> </option>
  369. <option Value="3" <%if Sort = 3 then response.write "selected"%>> </option>
  370. <option Value="4" <%if Sort = 4 then response.write "selected"%>> </option>
  371. <option Value="5" <%if Sort = 5 then response.write "selected"%>> </option>
  372. </SELECT>
  373. </td>
  374. </tr>
  375. <tr>
  376. <td align='left'> :</td>
  377. <td>:<INPUT TYPE="text" NAME="closeDateStart" value="<%=closeDateStart%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  378. <td>:<INPUT TYPE="text" NAME="closeDateEnd" value="<%=closeDateEnd%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  379. <td align='left'> :</td>
  380. <td>
  381. <INPUT TYPE="text" NAME="hasTheDate" value="<%=hasTheDate%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);">
  382. </td>
  383. <td align=center>
  384. <INPUT TYPE="submit" class="GenButton" value="">
  385. </td>
  386. </tr>
  387. </table>
  388. </TD>
  389. </TR>
  390. </FORM>
  391. <FORM METHOD=POST ACTION="?act=showCashRegReport">
  392. <%
  393. if Auth(9,9) then
  394. mySQL="SELECT CashRegisters.ID, MAX(Users.RealName) AS CashierName, MAX(Bankers.Name) AS BankerName, CashRegisters.NameDate, CashRegisters.CloseDate, isnull(SUM(case when CashRegisterLines.isA=1 and CashRegisterLines.Type=1 then ISNULL(Receipts.CashAmount,0) end),0) AS sumCashAmountA, isnull(SUM(case when CashRegisterLines.isA=0 and CashRegisterLines.Type=1 then ISNULL(Receipts.CashAmount,0) end),0) AS sumCashAmountB, SUM(ISNULL(case when CashRegisterLines.Type=1 then Receipts.ChequeAmount end,0)) AS sumChequeAmount, SUM(case when CashRegisterLines.isA=1 and CashRegisterLines.Type=2 then ISNULL(Payments.CashAmount,0) end) AS sumPayAmountA, SUM(case when CashRegisterLines.isA=0 and CashRegisterLines.Type=2 then ISNULL(Payments.CashAmount,0) end) AS sumPayAmountB FROM CashRegisters INNER JOIN Users ON CashRegisters.Cashier = Users.ID INNER JOIN Bankers ON CashRegisters.Banker = Bankers.ID LEFT OUTER JOIN CashRegisterLines ON CashRegisters.ID = CashRegisterLines.CashReg and CashRegisterLines.voided=0 LEFT OUTER JOIN Payments ON CashRegisterLines.Link = Payments.ID LEFT OUTER JOIN Receipts ON CashRegisterLines.Link = Receipts.ID " &Criteria& " GROUP BY CashRegisters.ID,CashRegisters.NameDate, CashRegisters.CloseDate, CashRegisters.IsOpen, CashRegisters.OpenDate, CashRegisters.Banker, CashRegisters.Cashier "& OrderBy
  395. else
  396. mySQL="SELECT CashRegisters.ID, Users.RealName AS CashierName, Bankers.Name AS BankerName, CashRegisters.NameDate, CashRegisters.CloseDate FROM CashRegisters INNER JOIN Users ON CashRegisters.Cashier = Users.ID INNER JOIN Bankers ON CashRegisters.Banker = Bankers.ID " & Criteria & " " & OrderBy
  397. end if
  398. '-------------------Add by Sam
  399. if Auth(9,9) then
  400. mySQLsum = "SELECT SUM(case when CashRegisterLines.isA=1 and CashRegisterLines.Type=1 then ISNULL(Receipts.CashAmount,0) end) AS sumCashAmountA, SUM(case when CashRegisterLines.isA=0 and CashRegisterLines.Type=1 then ISNULL(Receipts.CashAmount,0) end) AS sumCashAmountB, SUM(ISNULL(case when CashRegisterLines.Type=1 then Receipts.ChequeAmount end,0)) AS sumChequeAmount, SUM(case when CashRegisterLines.isA=1 and CashRegisterLines.Type=2 then ISNULL(Payments.CashAmount,0) end) AS sumPayAmountA, SUM(case when CashRegisterLines.isA=0 and CashRegisterLines.Type=2 then ISNULL(Payments.CashAmount,0) end) AS sumPayAmountB FROM CashRegisters INNER JOIN Users ON CashRegisters.Cashier = Users.ID INNER JOIN Bankers ON CashRegisters.Banker = Bankers.ID LEFT OUTER JOIN CashRegisterLines ON CashRegisters.ID = CashRegisterLines.CashReg and CashRegisterLines.voided=0 LEFT OUTER JOIN Payments ON CashRegisterLines.Link = Payments.ID LEFT OUTER JOIN Receipts ON CashRegisterLines.Link = Receipts.ID " &Criteria
  401. Set rsSUM = Conn.Execute(mySQLsum)
  402. end if
  403. Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL)
  404. PageSize = 20
  405. rs.PageSize = PageSize
  406. rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  407. rs.Open mySQL ,Conn,3
  408. TotalPages = rs.PageCount
  409. CurrentPage=1
  410. if isnumeric(Request.QueryString("p")) then
  411. pp=clng(Request.QueryString("p"))
  412. if pp <= TotalPages AND pp > 0 then
  413. CurrentPage = pp
  414. end if
  415. end if
  416. if not rs.eof then
  417. rs.AbsolutePage=CurrentPage
  418. end if
  419. if rs.eof then
  420. %> <tr>
  421. <td colspan=<% if Auth(9,9) then response.write "11" else response.write "7" end if%> class="CusTD3" style='direction:RTL;'> .</td>
  422. </tr>
  423. <% else
  424. %> <tr class="RepTableHeader">
  425. <td width="20px">#</td>
  426. <td width="10px"><input disabled type="radio"></td>
  427. <td> </td>
  428. <td> </td>
  429. <td> </td>
  430. <td> </td>
  431. <%
  432. '-------------------Add by Sam
  433. if Auth(9,9) then
  434. %>
  435. <td> </td>
  436. <td> </td>
  437. <td> </td>
  438. <td> </td>
  439. <td> </td>
  440. <%
  441. end if
  442. %>
  443. </tr>
  444. <%
  445. '-------------------Add by Sam
  446. if (Auth(9,9)) then
  447. if NOT rsSUM.eof then
  448. %>
  449. <TR class='RepTR1'>
  450. <TD colspan=6 align="center" style="font-weight: bold;"> </TD>
  451. <%
  452. if Auth(9,9) then
  453. %>
  454. <td><%=Separate(rsSUM("sumCashAmountA"))%></td>
  455. <td><%=Separate(rsSUM("sumCashAmountB"))%></td>
  456. <td><%=Separate(rsSUM("sumChequeAmount"))%></td>
  457. <td><%=Separate(rsSUM("sumPayAmountA"))%></td>
  458. <td><%=Separate(rsSUM("sumPayAmountB"))%></td>
  459. <%
  460. end if
  461. %>
  462. </TR>
  463. <% end if
  464. end if
  465. tempCounter=(CurrentPage - 1) * PageSize
  466. Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage)
  467. tempCounter = tempCounter+1
  468. CloseDate=rs("CloseDate")
  469. if isnull(CloseDate) then
  470. CloseDate="<FONT COLOR='Red'> </FONT>"
  471. end if
  472. %>
  473. <TR class='<%if tempCounter MOD 2 = 0 then response.write "RepTR1" else response.write "RepTR2"%>'>
  474. <TD><%=tempCounter%></TD>
  475. <TD><INPUT TYPE="radio" NAME="CashRegID" Value='<%=rs("ID")%>'></TD>
  476. <TD dir='LTR'><%=rs("NameDate")%></TD>
  477. <TD><%=rs("CashierName")%></TD>
  478. <TD><%=rs("BankerName")%></TD>
  479. <TD dir='LTR'><%=CloseDate%></TD>
  480. <%
  481. '-------------------Add by Sam
  482. if Auth(9,9) then
  483. %>
  484. <td><%=Separate(rs("sumCashAmountA"))%></td>
  485. <td><%=Separate(rs("sumCashAmountB"))%></td>
  486. <td><%=Separate(rs("sumChequeAmount"))%></td>
  487. <td><%=Separate(rs("sumPayAmountA"))%></td>
  488. <td><%=Separate(rs("sumPayAmountB"))%></td>
  489. <%
  490. end if
  491. %>
  492. </TR>
  493. <%
  494. rs.MoveNext
  495. Loop
  496. %> <tr class="RepTableTitle">
  497. <td align='right' colspan=<% if Auth(9,9) then response.write "11" else response.write "6" end if%>>
  498. <%
  499. if TotalPages > 1 then
  500. response.write " &nbsp;"
  501. for i=1 to TotalPages
  502. if i = CurrentPage then
  503. %> [<%=i%>]&nbsp;
  504. <% else
  505. %> <A HREF="?<%=PageLink%>&p=<%=i%>"><%=i%></A>&nbsp;
  506. <% end if
  507. if i mod 16 = 0 then response.write "<br>"
  508. next
  509. end if
  510. end if
  511. %> <BR>
  512. <P align=center><INPUT TYPE="submit" Value="" class="GenButton"></P>
  513. </td>
  514. </tr>
  515. </TABLE>
  516. </FORM>
  517. <%
  518. end if
  519. conn.Close
  520. %>
  521. </font>
  522. <!--#include file="tah.asp" -->