/beta/AR/Rep_InvoiceItems.asp

http://github.com/khaneh/Orders · ASP · 646 lines · 572 code · 66 blank · 8 comment · 55 complexity · 880d41bfdb246d02c72eef1e7b7e711d MD5 · raw file

  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2. 'Order (2)
  3. PageTitle=" "
  4. SubmenuItem=11
  5. if not Auth("C" , 3) then NotAllowdToViewThisPage()
  6. %>
  7. <!--#include file="top.asp" -->
  8. <!--#include File="../include_farsiDateHandling.asp"-->
  9. <!--#include File="../include_JS_InputMasks.asp"-->
  10. <STYLE>
  11. .CusTableHeader {background-color: #33AACC; text-align: center; font-weight:bold;}
  12. .CustTable {font-family:tahoma; width:80%; border:1 solid black; direction: RTL; background-color:black;}
  13. .CustTable td {padding:5;}
  14. .CustTable a {text-decoration:none;color:#000088}
  15. .CustTable a:hover {text-decoration:underline;}
  16. .CusTD1 {background-color: #CCCC66; text-align: left; font-weight:bold;}
  17. .CusTD2 {background-color: #DDDDDD; direction: LTR; text-align: right; font-size:9pt;}
  18. .CusTD3 {background-color: #DDDDDD; direction: LTR; text-align: center; font-size:9pt;}
  19. .CusTD4 {background-color: #CCCC66; direction: LTR; text-align: center; font-size:9pt;}
  20. .RepTable {font-family:tahoma; font-size:9pt; direction: RTL; }
  21. .RepTable th {font-size:9pt; padding:5px; background-color:#0080C0;height:25px;}
  22. .RepTable td {height:25px;}
  23. .RepTable input {font-family:tahoma; font-size:9pt; border:1 solid black;}
  24. .RepTable select {font-family:tahoma; font-size:9pt; border:1 solid black;}
  25. </STYLE>
  26. <%
  27. if request("act")="show" then
  28. ON ERROR RESUME NEXT
  29. Category = cint(request("Category"))
  30. if auth("C","B") then Category = 8
  31. isA = clng(request("isA"))
  32. FromDate = sqlSafe(request("FromDate"))
  33. ToDate = sqlSafe(request("ToDate"))
  34. ResultsInPage = cint(request("ResultsInPage"))
  35. if FromDate="" AND ToDate="" then
  36. pageTitle=" "
  37. elseif FromDate="" then
  38. pageTitle=" " & replace (ToDate,"/",".")
  39. elseif ToDate="" then
  40. pageTitle=" "& replace (FromDate,"/",".") & " "
  41. else
  42. pageTitle=" "& replace (FromDate,"/",".") & " " & replace (ToDate,"/",".")
  43. end if
  44. if ToDate = "" then ToDate = "9999/99/99"
  45. SalesAction = request("SalesAction")
  46. SalesPerson = request("SalesPerson")
  47. if SalesAction="" then
  48. SalesAction = 2 '
  49. else
  50. SalesAction = cint(SalesAction)
  51. end if
  52. if SalesPerson="" then
  53. SalesPerson = 0 '
  54. else
  55. SalesPerson = cint(SalesPerson)
  56. end if
  57. if Err.Number<>0 then
  58. Err.clear
  59. conn.close
  60. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  61. end if
  62. ON ERROR GOTO 0
  63. %>
  64. <br>
  65. <FORM METHOD=POST ACTION="?act=show">
  66. <table class="RepTable" id="AInvoices" width="300" align=center>
  67. <tr>
  68. <th colspan="4"> </td>
  69. </tr>
  70. <tr>
  71. <td align=left>&nbsp;</td>
  72. <td><INPUT TYPE="text" NAME="FromDate" style="width:75px;direction:LTR;" maxlength=10 OnBlur="acceptDate(this);" Value="<%=FromDate%>"></td>
  73. <td align=left>&nbsp;</td>
  74. <td><INPUT TYPE="text" NAME="ToDate" style="width:75px;direction:LTR;" maxlength=10 OnBlur="acceptDate(this);" <%if ToDate<>"9999/99/99" then response.write "Value='" & ToDate & "'" %>></td>
  75. </tr>
  76. <tr>
  77. <td align=left></td>
  78. <td align=left>
  79. <SELECT NAME="Category" style="width:150px;">
  80. <option value="">--- ---</option>
  81. <option value="0" <%if Category=0 then response.write "selected " : GroupName="[ ] "%>></option>
  82. <%
  83. mySQL = "SELECT * FROM InvoiceItemCategories"
  84. Set RS1 = Conn.Execute(mySQL)
  85. Do Until RS1.eof
  86. %>
  87. <option value="<%=RS1("ID")%>" <%if RS1("ID")=Category then response.write "selected " : GroupName="[" & RS1("Name") & "] "%>><%=RS1("Name")%></option>
  88. <%
  89. RS1.moveNext
  90. Loop
  91. RS1.close
  92. Set RS1 = Nothing
  93. otherCriteria=""
  94. if isA = 1 then
  95. isAName = " "
  96. otherCriteria = " AND (isA=1) "
  97. elseif isA = 2 then
  98. isAName = " "
  99. otherCriteria = " AND (isA=0) "
  100. end if
  101. if Auth("C" , 6) then '
  102. if SalesPerson <> 0 then
  103. select case SalesAction
  104. case 0:
  105. otherCriteria = otherCriteria & " AND ((Invoices.CreatedBy=" & SalesPerson & ") OR (Invoices.ApprovedBy=" & SalesPerson & ") OR (Invoices.IssuedBy=" & SalesPerson & "))"
  106. case 1:
  107. otherCriteria = otherCriteria & " AND (Invoices.CreatedBy=" & SalesPerson & ")"
  108. case 2:
  109. otherCriteria = otherCriteria & " AND (Invoices.ApprovedBy=" & SalesPerson & ")"
  110. case 3:
  111. otherCriteria = otherCriteria & " AND (Invoices.IssuedBy=" & SalesPerson & ")"
  112. end select
  113. end if
  114. end if
  115. if Category = 0 then
  116. GroupName= "()"
  117. ' S A M ------------------------------- THIS FILE HAS BEEN CHANGED BY SAM
  118. mySQL_Sum="SELECT Count(*) AS CNT, SUM(A4Qtty * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS A4Qtty, SUM(SumAppQtty * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumAppQtty, SUM(SumPrice * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumPrice, SUM(SumDiscount * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumDiscount, SUM(SumReverse * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumReverse, SUM(SumVat * - (2 * CONVERT(tinyint, IsReverse) -1)) AS SumVat FROM (SELECT InvoiceItems.ID, InvoiceItems.Name, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat, Invoices.IsReverse FROM InvoiceItems INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') " & otherCriteria & " GROUP BY Invoices.IsReverse, InvoiceItems.ID, InvoiceItems.Name) DRV"
  119. 'mySQL="SELECT InvoiceItems.ID, InvoiceItems.Name, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat, Invoices.IsReverse, MAX(InvoiceItemCategories.Name) as CategoryName FROM InvoiceItemCategories INNER JOIN InvoiceItemCategoryRelations ON InvoiceItemCategories.ID = InvoiceItemCategoryRelations.InvoiceItemCategory INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') " & otherCriteria & " GROUP BY Invoices.IsReverse, InvoiceItems.ID, InvoiceItems.Name ORDER BY InvoiceItems.ID, Invoices.IsReverse"
  120. 'mySQL="SELECT InvoiceItemCategories.ID, InvoiceItemCategories.Name, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat, InvoiceItemCategories.Name as CategoryName FROM InvoiceItemCategories INNER JOIN InvoiceItemCategoryRelations ON InvoiceItemCategories.ID = InvoiceItemCategoryRelations.InvoiceItemCategory INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate BETWEEN N'"& FromDate & "' AND N'"& ToDate & "') " & otherCriteria & " GROUP BY InvoiceItemCategories.ID, InvoiceItemCategories.Name ORDER BY InvoiceItemCategories.ID "
  121. mySQL="SELECT InvoiceItemCategories.ID, InvoiceItemCategories.Name, ISNULL(drv.A4Qtty,0) as A4Qtty, ISNULL(drv.SumAppQtty,0) as SumAppQtty, ISNULL(drv.SumPrice,0) as SumPrice, ISNULL(drv.SumDiscount,0) as SumDiscount, ISNULL(drv.SumReverse,0) as SumReverse, ISNULL(drv.SumVat,0) as SumVat, ISNULL(drv.CategoryName,'-') as CategoryName FROM InvoiceItemCategories LEFT OUTER JOIN (SELECT InvoiceItemCategories.ID, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat, MAX(InvoiceItemCategories.Name) as CategoryName FROM InvoiceItemCategories INNER JOIN InvoiceItemCategoryRelations ON InvoiceItemCategories.ID = InvoiceItemCategoryRelations.InvoiceItemCategory INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate BETWEEN N'"& FromDate & "' AND N'"& ToDate & "') " & otherCriteria & " GROUP BY InvoiceItemCategories.ID) drv ON InvoiceItemCategories.ID = drv.ID ORDER BY InvoiceItemCategories.ID"
  122. else
  123. mySQL_Sum="SELECT Count(*) AS CNT, SUM(A4Qtty * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS A4Qtty, SUM(SumAppQtty * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumAppQtty, SUM(SumPrice * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumPrice, SUM(SumDiscount * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumDiscount, SUM(SumReverse * - (2 * CONVERT(tinyint, IsReverse) - 1)) AS SumReverse, SUM(SumVat * -(2 * CONVERT(tinyint, IsReverse) - 1)) AS SumVat FROM (SELECT InvoiceItems.ID, InvoiceItems.Name, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat, Invoices.IsReverse FROM InvoiceItemCategories INNER JOIN InvoiceItemCategoryRelations ON InvoiceItemCategories.ID = InvoiceItemCategoryRelations.InvoiceItemCategory INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (InvoiceItemCategories.ID = "& Category & ") AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') " & otherCriteria & " GROUP BY Invoices.IsReverse, InvoiceItems.ID, InvoiceItems.Name) DRV"
  124. mySQL="SELECT InvoiceItems.ID, InvoiceItems.Name, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(CONVERT(bigint,InvoiceLines.Price)) AS SumPrice, SUM(InvoiceLines.Discount) AS SumDiscount, SUM(InvoiceLines.Reverse) AS SumReverse, SUM(InvoiceLines.Vat) AS SumVat , Invoices.IsReverse, MAX(InvoiceItemCategories.Name) as CategoryName FROM InvoiceItemCategories INNER JOIN InvoiceItemCategoryRelations ON InvoiceItemCategories.ID = InvoiceItemCategoryRelations.InvoiceItemCategory INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (InvoiceItemCategories.ID = "& Category & ") AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') " & otherCriteria & " GROUP BY Invoices.IsReverse, InvoiceItems.ID, InvoiceItems.Name ORDER BY InvoiceItems.ID,Invoices.IsReverse"
  125. end if
  126. %>
  127. </SELECT>
  128. </td>
  129. <td align=left></td>
  130. <td><INPUT TYPE="text" NAME="ResultsInPage" style="width:75px;direction:LTR;text-align:right;" maxlength=5 value="<%=ResultsInPage%>"></td>
  131. </tr>
  132. <tr>
  133. <td align=left></td>
  134. <td align=left>
  135. <SELECT NAME="isA" style="width:150px;">
  136. <option value="0" <%if isA=0 then response.write "selected " %>> </option>
  137. <option value="1" <%if isA=1 then response.write "selected " %>> </option>
  138. <option value="2" <%if isA=2 then response.write "selected " %>> </option>
  139. </SELECT>
  140. </td>
  141. </tr>
  142. <%
  143. 'response.write mySQL
  144. 'response.end
  145. if true Or Auth("C" , 6) then '
  146. %>
  147. <tr>
  148. <td align=left>CSR</td>
  149. <td align=left>
  150. <SELECT NAME="SalesAction" style="width:100px;">
  151. <option value="1" <%if SalesAction=1 then response.write "selected " %>> </option>
  152. <option value="2" <%if SalesAction=2 then response.write "selected " %>> </option>
  153. <option value="3" <%if SalesAction=3 then response.write "selected " %>> </option>
  154. <option value="0" <%if SalesAction=0 then response.write "selected " %>> </option>
  155. </SELECT>
  156. </td>
  157. <td align=left>
  158. <SELECT NAME="SalesPerson" style="width:100px;">
  159. <option value="0" <%if SalesPerson=0 then response.write "selected " %>> </option>
  160. <%
  161. set RS=Conn.Execute ("SELECT * FROM Users WHERE ID<>0 ORDER BY RealName")
  162. Do while not RS.eof
  163. %>
  164. <option value="<%=RS("ID")%>" <%if SalesPerson=RS("ID") then response.write "selected " %>><%=RS("RealName")%></option>
  165. <%
  166. RS.moveNext
  167. Loop
  168. RS.close
  169. %>
  170. </SELECT>
  171. </td>
  172. </tr>
  173. <%
  174. end if
  175. %>
  176. <tr>
  177. <td colspan=4 align=center><INPUT Class="GenButton" style="border:1 solid black;" TYPE="submit" value=" "></td>
  178. </tr>
  179. </table>
  180. </FORM>
  181. <br>
  182. <%
  183. 'response.write mySQL_Sum
  184. Set RS1=Conn.Execute(mySQL_Sum)
  185. if NOT RS1.eof then
  186. CNT = cdbl(RS1("CNT"))
  187. if CNT >0 then
  188. A4Qtty = cdbl(RS1("A4Qtty"))
  189. SumAppQtty = cdbl(RS1("SumAppQtty"))
  190. SumPrice = cdbl(RS1("SumPrice"))
  191. SumDiscount = cdbl(RS1("SumDiscount"))
  192. SumReverse = cdbl(RS1("SumReverse"))
  193. SumVat = cdbl(RS1("SumVat"))
  194. else
  195. A4Qtty = 0
  196. SumAppQtty = 0
  197. SumPrice = 0
  198. SumDiscount = 0
  199. SumReverse = 0
  200. SumVat = 0
  201. end if
  202. SumSales = SumPrice - SumDiscount - SumReverse '+ SumVat
  203. %>
  204. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  205. <tr>
  206. <td colspan="8" class="CusTableHeader" style="text-align:right;height:35;"> <%=isAName %> &nbsp; <%=GroupName%> (<%=pageTitle%>)</td>
  207. </tr>
  208. <tr>
  209. <td class="CusTD3">#</td>
  210. <td class="CusTD3" style="direction:RTL;"> A4</td>
  211. <td class="CusTD3"> </td>
  212. <td class="CusTD3"> </td>
  213. <td class="CusTD3"> </td>
  214. <td class="CusTD3"> ѐ</td>
  215. <td class="CusTD3"> </td>
  216. <td class="CusTD3"> </td>
  217. </tr>
  218. <tr bgcolor="white">
  219. <TD dir=LTR align=right><%=Separate(CNT)%></TD>
  220. <TD dir=LTR align=right><%=Separate(A4Qtty)%></TD>
  221. <TD dir=LTR align=right><%=Separate(SumAppQtty)%></TD>
  222. <TD dir=LTR align=right><%=Separate(SumPrice)%></TD>
  223. <TD dir=LTR align=right><%=Separate(SumDiscount)%></TD>
  224. <TD dir=LTR align=right><%=Separate(SumReverse)%></TD>
  225. <TD dir=LTR align=right><%=Separate(SumVat)%></TD>
  226. <TD dir=LTR align=right><%=Separate(SumSales)%></TD>
  227. </tr>
  228. </table>
  229. <br><br>
  230. <%
  231. end if
  232. RS1.close
  233. %>
  234. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  235. <tr>
  236. <td colspan="10" class="CusTableHeader" style="text-align:right;height:35;"> <%=isAName %>&nbsp;<%=GroupName%> (<%=pageTitle%>)</td>
  237. </tr>
  238. <%
  239. Set RS1 = Server.CreateObject("ADODB.Recordset")
  240. PageSize = ResultsInPage
  241. RS1.PageSize = PageSize
  242. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  243. RS1.Open mySQL ,Conn,3
  244. TotalPages = RS1.PageCount
  245. CurrentPage=1
  246. if isnumeric(Request.QueryString("p")) then
  247. pp=clng(Request.QueryString("p"))
  248. if pp <= TotalPages AND pp > 0 then
  249. CurrentPage = pp
  250. end if
  251. end if
  252. if not RS1.eof then
  253. RS1.AbsolutePage=CurrentPage
  254. end if
  255. if RS1.eof then
  256. %> <tr>
  257. <td colspan="10" class="CusTD3" style='direction:RTL;'> .</td>
  258. </tr>
  259. <% else
  260. %> <tr>
  261. <td class="CusTD3">#</td>
  262. <td class="CusTD3" style='direction:RTL;'> </td>
  263. <td class="CusTD3" style="direction:RTL;"> A4</td>
  264. <td class="CusTD3"> </td>
  265. <td class="CusTD3"> </td>
  266. <td class="CusTD3"> </td>
  267. <td class="CusTD3"> ѐ</td>
  268. <td class="CusTD3"> </td>
  269. <td class="CusTD3"> </td>
  270. <td class="CusTD3"></td>
  271. </tr>
  272. <SCRIPT LANGUAGE="JavaScript">
  273. <!--
  274. function drill(item) {
  275. <%if Category=0 then
  276. response.write "window.open('?act=show&category='+item+'&FromDate="& FromDate & "&ToDate=" & ToDate & "&isA=" & isA & "&SalesAction=" & SalesAction & "&SalesPerson=" & SalesPerson & "&ResultsInPage="&ResultsInPage&"');"
  277. else
  278. response.write "window.open('?act=showItemDetails&Item='+item+'&FromDate="& FromDate & "&ToDate=" & ToDate & "&isA=" & isA & "&SalesAction=" & SalesAction & "&SalesPerson=" & SalesPerson & "');"
  279. end if
  280. %>
  281. }
  282. function drillGroup(cat) {
  283. window.location('../AR/Rep_InvoiceItems.asp?act=show&category='+cat+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&isA=<%=isA%>&SalesAction=<%=SalesAction%>&SalesPerson=<%=SalesPerson%>");
  284. }
  285. //-->
  286. </SCRIPT>
  287. <% tmpCounter=(CurrentPage - 1) * PageSize
  288. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  289. tmpCounter = tmpCounter + 1
  290. if tmpCounter mod 2 = 1 then
  291. tmpColor="#FFFFFF"
  292. tmpColor2="#FFFFBB"
  293. Else
  294. tmpColor="#DDDDDD"
  295. tmpColor2="#EEEEBB"
  296. End if
  297. if Category=0 then
  298. A4Qtty = cdbl(RS1("A4Qtty"))
  299. SumAppQtty = cdbl(RS1("SumAppQtty"))
  300. SumPrice = cdbl(RS1("SumPrice"))
  301. SumDiscount = cdbl(RS1("SumDiscount"))
  302. SumReverse = cdbl(RS1("SumReverse"))
  303. SumVat = cdbl(RS1("SumVat"))
  304. else
  305. if RS1("IsReverse") then
  306. A4Qtty = -cdbl(RS1("A4Qtty"))
  307. SumAppQtty = -cdbl(RS1("SumAppQtty"))
  308. SumPrice = -cdbl(RS1("SumPrice"))
  309. SumDiscount = -cdbl(RS1("SumDiscount"))
  310. SumReverse = -cdbl(RS1("SumReverse"))
  311. SumVat = -cdbl(RS1("SumVat"))
  312. tmpColor="#FF9966"
  313. else
  314. A4Qtty = cdbl(RS1("A4Qtty"))
  315. SumAppQtty = cdbl(RS1("SumAppQtty"))
  316. SumPrice = cdbl(RS1("SumPrice"))
  317. SumDiscount = cdbl(RS1("SumDiscount"))
  318. SumReverse = cdbl(RS1("SumReverse"))
  319. SumVat = cdbl(RS1("SumVat"))
  320. end if
  321. end if
  322. SumSales = SumPrice - SumDiscount - SumReverse '+ SumVat
  323. %>
  324. <TR bgcolor="<%=tmpColor%>">
  325. <TD><%=tmpCounter%></TD>
  326. <TD style="cursor: hand; height:30px;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="drill('<%=RS1("ID")%>');"><%=RS1("Name")%></TD>
  327. <TD dir=LTR align=right><%=Separate(A4Qtty)%></TD>
  328. <TD dir=LTR align=right><%=Separate(SumAppQtty)%></TD>
  329. <TD dir=LTR align=right><%=Separate(SumPrice)%></TD>
  330. <TD dir=LTR align=right><%=Separate(SumDiscount)%></TD>
  331. <TD dir=LTR align=right><a href="rep_reversSales.asp?act=show&fromDate=<%=escape(fromDate)%>&toDate=<%=escape(toDate)%>&<%if Category=0 then response.write "cat" else response.write "item"%>=<%=rs1("id")%>"><%=Separate(SumReverse)%></a></TD>
  332. <TD dir=LTR align=right><%=Separate(SumVat)%></TD>
  333. <TD dir=LTR align=right><%=Separate(SumSales)%></TD>
  334. <td dir=LTR align=center><%=RS1("CategoryName")%></td>
  335. </TR>
  336. <% RS1.moveNext
  337. Loop
  338. if ToDate="9999/99/99" then ToDate=""
  339. if TotalPages > 1 then
  340. pageCols=20
  341. %>
  342. <TR>
  343. <TD bgcolor='#33AACC' height="30" colspan="8">
  344. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  345. <tr>
  346. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  347. <b> <%=CurrentPage%> <%=TotalPages%></b>
  348. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  349. </td>
  350. </tr>
  351. <tr>
  352. <% for i=1 to TotalPages
  353. if i = CurrentPage then
  354. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  355. <% else
  356. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  357. <% end if
  358. if i mod pageCols = 0 then response.write "</tr><tr>"
  359. next
  360. %> </tr>
  361. </table>
  362. <SCRIPT LANGUAGE="JavaScript">
  363. <!--
  364. function go2Page(p) {
  365. window.location="?act=show&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&Category=<%=Category%>";
  366. }
  367. //-->
  368. </SCRIPT>
  369. </TD>
  370. </TR>
  371. <% end if
  372. end if
  373. RS1.close
  374. Set RS1 = Nothing
  375. %>
  376. </table>
  377. <br>
  378. <%
  379. elseif request("act")="showItemDetails" then
  380. ON ERROR RESUME NEXT
  381. Item = clng(request("Item"))
  382. FromDate = sqlSafe(request("FromDate"))
  383. ToDate = sqlSafe(request("ToDate"))
  384. isA = cint(request("isA"))
  385. ResultsInPage = 50
  386. if ToDate = "" then ToDate = "9999/99/99"
  387. if FromDate="" AND ToDate="9999/99/99" then
  388. pageTitle=" "
  389. elseif FromDate="" then
  390. pageTitle=" " & replace (ToDate,"/",".")
  391. elseif ToDate="9999/99/99" then
  392. pageTitle=" "& replace (FromDate,"/",".") & " "
  393. else
  394. pageTitle=" "& replace (FromDate,"/",".") & " " & replace (ToDate,"/",".")
  395. end if
  396. SalesAction = request("SalesAction")
  397. SalesPerson = request("SalesPerson")
  398. if SalesAction="" then
  399. SalesAction = 2 '
  400. else
  401. SalesAction = cint(SalesAction)
  402. end if
  403. if SalesPerson="" then
  404. SalesPerson = 0 '
  405. else
  406. SalesPerson = cint(SalesPerson)
  407. end if
  408. if Err.Number<>0 then
  409. Err.clear
  410. conn.close
  411. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  412. end if
  413. ON ERROR GOTO 0
  414. mySQL= "SELECT Name FROM InvoiceItems WHERE (ID = "& Item & ")"
  415. Set RS1 = Conn.Execute(mySQL)
  416. if RS1.eof then
  417. conn.close
  418. response.redirect "OtherReports.asp?errMsg=" & Server.URLEncode(" .")
  419. else
  420. ItemName=RS1("Name")
  421. end if
  422. RS1.close
  423. Set RS1 = Nothing
  424. otherCriteria = ""
  425. if isA = 1 then
  426. isAName = " "
  427. otherCriteria = " AND (Invoices.isA=1) "
  428. elseif isA = 2 then
  429. isAName = " "
  430. otherCriteria = " AND (Invoices.isA=0) "
  431. end if
  432. if Auth("C" , 6) then '
  433. if SalesPerson <> 0 then
  434. select case SalesAction
  435. case 0:
  436. otherCriteria = otherCriteria & " AND ((Invoices.CreatedBy=" & SalesPerson & ") OR (Invoices.ApprovedBy=" & SalesPerson & ") OR (Invoices.IssuedBy=" & SalesPerson & "))"
  437. case 1:
  438. otherCriteria = otherCriteria & " AND (Invoices.CreatedBy=" & SalesPerson & ")"
  439. case 2:
  440. otherCriteria = otherCriteria & " AND (Invoices.ApprovedBy=" & SalesPerson & ")"
  441. case 3:
  442. otherCriteria = otherCriteria & " AND (Invoices.IssuedBy=" & SalesPerson & ")"
  443. end select
  444. end if
  445. end if
  446. %>
  447. <br>
  448. <table class="CustTable" cellspacing='1' style='width:90%;' align='center'>
  449. <tr>
  450. <td colspan="10" class="CusTableHeader" style="text-align:right;height:35;"> <%=isAName%> '<%=ItemName%>' (<%=pageTitle%>)</td>
  451. </tr>
  452. <%
  453. mySQL="SELECT Invoices.ID, Invoices.IssuedDate, Invoices.TotalReceivable, Invoices.IsReverse, Accounts.AccountTitle, SUM(InvoiceLines.Qtty * InvoiceLines.Sets * ROUND(InvoiceLines.Length * InvoiceLines.Width / 630 + .49, 0)) AS A4Qtty, SUM(InvoiceLines.AppQtty) AS SumAppQtty, SUM(InvoiceLines.Price - InvoiceLines.Discount - InvoiceLines.Reverse) AS SumReceivable, dbo.isInvoiceHasPaper(Invoices.ID) AS isPaper, dbo.isInvoiceHasHavale(Invoices.ID) AS isHavale FROM Invoices INNER JOIN InvoiceLines ON Invoices.ID = InvoiceLines.Invoice INNER JOIN Accounts ON Invoices.Customer = Accounts.ID WHERE (InvoiceLines.Item = "& Item & ") AND (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'" & FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') " & otherCriteria & " GROUP BY Accounts.AccountTitle, Invoices.ID, Invoices.TotalReceivable, Invoices.IsReverse, Invoices.IssuedDate, Invoices.IssuedDate ORDER BY Invoices.IssuedDate DESC"
  454. 'response.write mySQL
  455. Set RS1 = Server.CreateObject("ADODB.Recordset")
  456. PageSize = ResultsInPage
  457. RS1.PageSize = PageSize
  458. RS1.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  459. RS1.Open mySQL ,Conn,3
  460. TotalPages = RS1.PageCount
  461. CurrentPage=1
  462. if isnumeric(Request.QueryString("p")) then
  463. pp=clng(Request.QueryString("p"))
  464. if pp <= TotalPages AND pp > 0 then
  465. CurrentPage = pp
  466. end if
  467. end if
  468. if not RS1.eof then
  469. RS1.AbsolutePage=CurrentPage
  470. end if
  471. if RS1.eof then
  472. %> <tr>
  473. <td colspan="10" class="CusTD3" style='direction:RTL;'> .</td>
  474. </tr>
  475. <% else
  476. %> <tr>
  477. <td class="CusTD3">#</td>
  478. <td class="CusTD3" style="direction:RTL;"># </td>
  479. <td class="CusTD3"> </td>
  480. <td class="CusTD3"> </td>
  481. <td class="CusTD3" style="direction:RTL;"> A4</td>
  482. <td class="CusTD3"> </td>
  483. <td class="CusTD3"> </td>
  484. <td class="CusTD3"> </td>
  485. <td class="CusTD3" width="5px">I</td>
  486. <td class="CusTD3" width="5px">II</td>
  487. </tr>
  488. <SCRIPT LANGUAGE="JavaScript">
  489. <!--
  490. function drill(inv) {
  491. window.open('../AR/AccountReport.asp?act=showInvoice&invoice='+inv);
  492. }
  493. //-->
  494. </SCRIPT>
  495. <% tmpCounter=(CurrentPage - 1) * PageSize
  496. Do while NOT RS1.eof AND (RS1.AbsolutePage = CurrentPage)
  497. tmpCounter = tmpCounter + 1
  498. if tmpCounter mod 2 = 1 then
  499. tmpColor="#FFFFFF"
  500. tmpColor2="#FFFFBB"
  501. Else
  502. tmpColor="#DDDDDD"
  503. tmpColor2="#EEEEBB"
  504. End if
  505. if RS1("IsReverse") then
  506. A4Qtty = -cdbl(RS1("A4Qtty"))
  507. SumAppQtty = -cdbl(RS1("SumAppQtty"))
  508. SumReceivable = -cdbl(RS1("SumReceivable"))
  509. TotalReceivable = -cdbl(RS1("TotalReceivable"))
  510. tmpColor="#FF9966"
  511. else
  512. A4Qtty = cdbl(RS1("A4Qtty"))
  513. SumAppQtty = cdbl(RS1("SumAppQtty"))
  514. SumReceivable = cdbl(RS1("SumReceivable"))
  515. TotalReceivable = cdbl(RS1("TotalReceivable"))
  516. end if
  517. %>
  518. <TR bgcolor="<%=tmpColor%>" style="cursor: hand; height:30px;" onMouseOver="this.style.backgroundColor='<%=tmpColor2%>'" onMouseOut="this.style.backgroundColor='<%=tmpColor%>'" onclick="drill('<%=RS1("ID")%>');">
  519. <TD><%=tmpCounter%></TD>
  520. <TD><%=RS1("ID")%></TD>
  521. <TD dir=LTR align=right><%=RS1("IssuedDate")%></TD>
  522. <TD><%=RS1("AccountTitle")%></TD>
  523. <TD dir=LTR align=right><%=Separate(A4Qtty)%></TD>
  524. <TD dir=LTR align=right><%=Separate(SumAppQtty)%></TD>
  525. <TD dir=LTR align=right><%=Separate(SumReceivable)%></TD>
  526. <TD dir=LTR align=right><%=Separate(TotalReceivable)%></TD>
  527. <td dir="ltr" align="center"><%if cbool(RS1("isPaper")) then response.write("*")%></td>
  528. <td dir="ltr" align="center"><%if cbool(RS1("isHavale")) then response.write("*")%></td>
  529. </TR>
  530. <% RS1.moveNext
  531. Loop
  532. if ToDate="9999/99/99" then ToDate=""
  533. if TotalPages > 1 then
  534. pageCols=20
  535. %>
  536. <TR class="RepTableTitle">
  537. <TD bgcolor='#33AACC' height="30" colspan="10">
  538. <table width=100% cellspacing=0 style="cursor:hand;color:#444444">
  539. <tr>
  540. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  541. <b> <%=CurrentPage%> <%=TotalPages%></b>
  542. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>);"> &gt;</a>
  543. </td>
  544. </tr>
  545. <tr>
  546. <% for i=1 to TotalPages
  547. if i = CurrentPage then
  548. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  549. <% else
  550. %> <td onclick="go2Page(<%=i%>);"><%=i%></td>
  551. <% end if
  552. if i mod pageCols = 0 then response.write "</tr><tr>"
  553. next
  554. %> </tr>
  555. </table>
  556. <div>
  557. I:
  558. </div>
  559. <div>
  560. II:
  561. </div>
  562. <SCRIPT LANGUAGE="JavaScript">
  563. <!--
  564. function go2Page(p) {
  565. window.location="?act=showItemDetails&Item=<%=Item%>&ResultsInPage=<%=ResultsInPage%>&p="+p+"&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&isA=<%=isA%>&SalesAction=<%=SalesAction%>&SalesPerson=<%=SalesPerson%>";
  566. }
  567. //-->
  568. </SCRIPT>
  569. </TD>
  570. </TR>
  571. <% end if
  572. end if
  573. RS1.close
  574. Set RS1 = Nothing
  575. %>
  576. </table>
  577. <br>
  578. <%
  579. end if%>
  580. <!--#include file="tah.asp" -->