/beta/Kid_Inv.asp

http://github.com/khaneh/Orders · ASP · 100 lines · 82 code · 13 blank · 5 comment · 4 complexity · 7162cefcbb770bf152d80279b07d5671 MD5 · raw file 

    

  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%>

    2. 

  2. <%if request("act")="" then%>

    3. 

  3. 	<!--#include File="include_farsiDateHandling.asp"-->

    4. 

  4. 	<HTML>

    5. 

  5. 	<HEAD>

    6. 

  6. 	<meta http-equiv="Content-Type" content="text/html; charset=windows-1256">

    7. 

  7. 	<meta http-equiv="Content-Language" content="fa">

    8. 

  8. 	<style>

    9. 

  9. 		Table { font-family:tahoma; font-size: 9pt;}

    10. 

  10. 	</style>

    11. 

  11. 	<TITLE><%if request("act")="show" then response.write "              "& replace(FromDate,"/",".") & "  "& replace(ToDate,"/",".") else response.write "Kid Invoice Items Categories"%></TITLE>

    12. 

  12. 	</HEAD>

    13. 

  13. 	<BODY>

    14. 

  14. 	<!--#include File="include_JS_InputMasks.asp"-->

    15. 

  15. 	<FORM METHOD=POST ACTION="?act=show">

    16. 

  16. 	<TABLE border=1 align=center dir="RTL">

    17. 

  17. 	<TR>

    18. 

  18. 		<TD align=left> </TD>

    19. 

  19. 		<TD><INPUT dir="LTR" class="GenInput" NAME="FromDate" tabIndex="3" TYPE="text" maxlength="10" size="10" value="<%=request("FromDate")%>" onBlur="acceptDate(this);" style="width:100px;"></TD>

    20. 

  20. 	</TR>

    21. 

  21. 	<TR>

    22. 

  22. 		<TD align=left> </TD>

    23. 

  23. 		<TD><INPUT dir="LTR" class="GenInput" NAME="ToDate" tabIndex="4" TYPE="text" maxlength="10" size="10" value="<%=request("ToDate")%>" onBlur="acceptDate(this)" style="width:100px;"></TD>

    24. 

  24. 	</TR>

    25. 

  25. 	<TR>

    26. 

  26. 		<TD align=center colspan=2><INPUT style="font-family:tahoma;" TYPE="Submit" Value="  " tabindex=6></TD>

    27. 

  27. 	</TR>

    28. 

  28. 	</FORM>

    29. 

  29. 	</TABLE>

    30. 

  30. 	<br>

    31. 

  31. <%

    32. 

  32. elseif request("act")="show" then

    33. 

  33. 	FromDate=	request("FromDate")

    34. 

  34. 	ToDate=		request("ToDate")

    35. 

  35. 

    36. 

  36. '	Response.ContentType = "application/msexcel"

    37. 

  37. '	Response.AddHeader "Content-Disposition","attachment; filename=Customers.txt"

    38. 

  38. 

    39. 

  39. 	Separator=chr(9)

    40. 

  40. 

    41. 

  41. 	%> <%=Separator%> <%=Separator%> 1 ( )<%=Separator%><%=Separator%> 2 ( )<%=Separator%><%=Separator%> 3 ()<%=Separator%><%=Separator%> 4 ()<%=Separator%><%=Separator%> 5 ( )<%=Separator%><%=Separator%> 6 ()<%=Separator%><%=Separator%> 7 ( )<%=Separator%><%=Separator%> 8 ()<%=Separator%><%=Separator%> 9 ( )<%=Separator%><%=Separator%> 10 ()<%=Separator%><%=Separator%> 11 (  )<%=Separator%><%=Separator%> 13 (   )<%=Separator%><%=Separator%>   <%=Separator%>   <%=Separator%>  <%=Separator%>  ѐ<%=vbCrLf%>

    42. 

  42. <%

    43. 

  43. 

    44. 

  44. '	conStr="DRIVER={SQL Server};SERVER=(local);DATABASE=sefareshat;UID=sefadmin; PWD=5tgb;"

    45. 

  45. conStr = "Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sefadmin;Initial Catalog=sefareshat;Data Source=(local);PWD=5tgb;"

    46. 

  46. 

    47. 

  47. 	Set conn = Server.CreateObject("ADODB.Connection")

    48. 

  48. 	conn.open conStr

    49. 

  49. 

    50. 

  50. 	'mySQL="SELECT Kid.AccID,Kid.AccountTitle,Kid.Price,Kid.Discount,Kid.Reverse,Kid.CatID,Kid.CatName,Kid.InvQtty,COUNT(*) AS TotalInvQtty, SUM(Invoices.TotalReceivable) AS TotalReceivable,SUM(Invoices.TotalDiscount) AS TotalDiscount,SUM(Invoices.TotalReverse)  AS TotalReverse FROM (SELECT Accounts.ID AS AccID,Accounts.AccountTitle,SUM(InvoiceLines.Price) AS Price,SUM(InvoiceLines.Discount) AS Discount, SUM(InvoiceLines.Reverse) AS Reverse,InvoiceItemCategories.ID AS CatID,InvoiceItemCategories.Name AS CatName,COUNT(*) AS InvQtty FROM InvoiceItemCategoryRelations INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceItemCategories ON InvoiceItemCategoryRelations.InvoiceItemCategory = InvoiceItemCategories.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID INNER JOIN Accounts ON Invoices.Customer = Accounts.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) GROUP BY Accounts.AccountTitle,InvoiceItemCategories.ID,InvoiceItemCategories.Name,Accounts.ID HAVING (InvoiceItemCategories.ID > 0)) Kid INNER JOIN Invoices ON Kid.AccID = Invoices.Customer GROUP BY Kid.AccID,Kid.AccountTitle,Kid.Price,Kid.Discount,Kid.Reverse,Kid.CatID,Kid.CatName,Kid.InvQtty,Invoices.Issued,Invoices.Voided HAVING (Invoices.Issued = 1) AND (Invoices.Voided = 0) ORDER BY Kid.AccID,Kid.CatID"

    51. 

  51. 

    52. 

  52. 	mySQL="SELECT Kid.AccID,Kid.AccountTitle,Kid.Price,Kid.Discount,Kid.Reverse,Kid.CatID,Kid.CatName,Kid.InvQtty,COUNT(*) AS TotalInvQtty, SUM(Invoices.TotalReceivable) AS TotalReceivable,SUM(Invoices.TotalDiscount) AS TotalDiscount,SUM(Invoices.TotalReverse)  AS TotalReverse FROM (SELECT Accounts.ID AS AccID,Accounts.AccountTitle,SUM(InvoiceLines.Price) AS Price,SUM(InvoiceLines.Discount) AS Discount, SUM(InvoiceLines.Reverse) AS Reverse,InvoiceItemCategories.ID AS CatID,InvoiceItemCategories.Name AS CatName,COUNT(DISTINCT Invoices.ID) AS InvQtty FROM InvoiceItemCategoryRelations INNER JOIN InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN InvoiceItemCategories ON InvoiceItemCategoryRelations.InvoiceItemCategory = InvoiceItemCategories.ID INNER JOIN InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN Invoices ON InvoiceLines.Invoice = Invoices.ID INNER JOIN Accounts ON Invoices.Customer = Accounts.ID WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') GROUP BY Accounts.AccountTitle,InvoiceItemCategories.ID,InvoiceItemCategories.Name,Accounts.ID HAVING (InvoiceItemCategories.ID > 0)) Kid INNER JOIN Invoices ON Kid.AccID = Invoices.Customer WHERE (Invoices.IssuedDate >= N'"& FromDate & "') AND (Invoices.IssuedDate <= N'"& ToDate & "') GROUP BY Kid.AccID,Kid.AccountTitle,Kid.Price,Kid.Discount,Kid.Reverse,Kid.CatID,Kid.CatName,Kid.InvQtty,Invoices.Issued,Invoices.Voided HAVING (Invoices.Issued = 1) AND (Invoices.Voided = 0) ORDER BY Kid.AccID,Kid.CatID"

    53. 

  53. 

    54. 

  54. 	Set RS1 = conn.Execute(mySQL)

    55. 

  55. 

    56. 

  56. 	cols=20

    57. 

  57. 	Dim Col1(20)

    58. 

  58. 	Dim Col2(20)

    59. 

  59. 	for i=1 to cols

    60. 

  60. 		Col1(i)=""

    61. 

  61. 		Col2(i)=""

    62. 

  62. 	next

    63. 

  63. 	LastAcc=0

    64. 

  64. 	tmpCounter=0

    65. 

  65. 

    66. 

  66. 	Do While NOT RS1.EOF 'and tmpCounter < 10

    67. 

  67. 		tmpCounter=tmpCounter+1

    68. 

  68. 		AccID			=RS1("AccID")

    69. 

  69. 		if AccID<>LastAcc AND LastAcc <> 0 then

    70. 

  70. 			'write last row

    71. 

  71. 			%><%=LastAcc%><%=Separator%><%=AccountTitle%><%=Separator%><%=Col1(1)%><%=Separator%><%=Col2(1)%><%=Separator%><%=Col1(2)%><%=Separator%><%=Col2(2)%><%=Separator%><%=Col1(3)%><%=Separator%><%=Col2(3)%><%=Separator%><%=Col1(4)%><%=Separator%><%=Col2(4)%><%=Separator%><%=Col1(5)%><%=Separator%><%=Col2(5)%><%=Separator%><%=Col1(6)%><%=Separator%><%=Col2(6)%><%=Separator%><%=Col1(7)%><%=Separator%><%=Col2(7)%><%=Separator%><%=Col1(8)%><%=Separator%><%=Col2(8)%><%=Separator%><%=Col1(9)%><%=Separator%><%=Col2(9)%><%=Separator%><%=Col1(10)%><%=Separator%><%=Col2(10)%><%=Separator%><%=Col1(11)%><%=Separator%><%=Col2(11)%><%=Separator%><%=Col1(13)%><%=Separator%><%=Col2(13)%><%=Separator%><%=TotalInvQtty%><%=Separator%><%=TotalReceivable%><%=Separator%><%=TotalDiscount%><%=Separator%><%=TotalReverse%><%=vbCrLf%>

    72. 

  72. <%

    73. 

  73. 			for i=1 to cols

    74. 

  74. 				Col1(i)=""

    75. 

  75. 				Col2(i)=""

    76. 

  76. 			next

    77. 

  77. 		end if

    78. 

  78. 

    79. 

  79. 		AccountTitle	=RS1("AccountTitle")

    80. 

  80. 		Price			=cdbl(RS1("Price"))

    81. 

  81. 		Discount		=cdbl(RS1("Discount"))

    82. 

  82. 		Reverse			=cdbl(RS1("Reverse"))

    83. 

  83. 		CatID			=cint(RS1("CatID"))

    84. 

  84. 		CatName			=RS1("CatName")

    85. 

  85. 		InvQtty			=clng(RS1("InvQtty"))

    86. 

  86. 		TotalInvQtty	=clng(RS1("TotalInvQtty"))

    87. 

  87. 		TotalReceivable	=cdbl(RS1("TotalReceivable"))

    88. 

  88. 		TotalDiscount	=cdbl(RS1("TotalDiscount"))

    89. 

  89. 		TotalReverse	=cdbl(RS1("TotalReverse"))

    90. 

  90. 

    91. 

  91. 		Col1(CatID)=Price-Discount-Reverse

    92. 

  92. 		Col2(CatID)=InvQtty

    93. 

  93. 

    94. 

  94. 		LastAcc=AccID

    95. 

  95. 		RS1.moveNext

    96. 

  96. 	Loop

    97. 

  97. 			%><%=LastAcc%><%=Separator%><%=AccountTitle%><%=Separator%><%=Col1(1)%><%=Separator%><%=Col2(1)%><%=Separator%><%=Col1(2)%><%=Separator%><%=Col2(2)%><%=Separator%><%=Col1(3)%><%=Separator%><%=Col2(3)%><%=Separator%><%=Col1(4)%><%=Separator%><%=Col2(4)%><%=Separator%><%=Col1(5)%><%=Separator%><%=Col2(5)%><%=Separator%><%=Col1(6)%><%=Separator%><%=Col2(6)%><%=Separator%><%=Col1(7)%><%=Separator%><%=Col2(7)%><%=Separator%><%=Col1(8)%><%=Separator%><%=Col2(8)%><%=Separator%><%=Col1(9)%><%=Separator%><%=Col2(9)%><%=Separator%><%=Col1(10)%><%=Separator%><%=Col2(10)%><%=Separator%><%=Col1(11)%><%=Separator%><%=Col2(11)%><%=Separator%><%=Col1(13)%><%=Separator%><%=Col2(13)%><%=Separator%><%=TotalInvQtty%><%=Separator%><%=TotalReceivable%><%=Separator%><%=TotalDiscount%><%=Separator%><%=TotalReverse%><%=vbCrLf%>

    98. 

  98. <%

    99. 

  99. end if

    100. 

  100. %>
    101.