/beta/Inquiry/Rep_InvoiceItems.asp

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