/beta/Kid_Inv.asp
ASP | 100 lines | 82 code | 13 blank | 5 comment | 4 complexity | 7162cefcbb770bf152d80279b07d5671 MD5 | raw file
1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%> 2<%if request("act")="" then%> 3 <!--#include File="include_farsiDateHandling.asp"--> 4 <HTML> 5 <HEAD> 6 <meta http-equiv="Content-Type" content="text/html; charset=windows-1256"> 7 <meta http-equiv="Content-Language" content="fa"> 8 <style> 9 Table { font-family:tahoma; font-size: 9pt;} 10 </style> 11 <TITLE><%if request("act")="show" then response.write "����� ���� ������� �� ��� ���� ��� ���� �� ���� ��� ����� �� ����� "& replace(FromDate,"/",".") & " �� "& replace(ToDate,"/",".") else response.write "Kid Invoice Items Categories"%></TITLE> 12 </HEAD> 13 <BODY> 14 <!--#include File="include_JS_InputMasks.asp"--> 15 <FORM METHOD=POST ACTION="?act=show"> 16 <TABLE border=1 align=center dir="RTL"> 17 <TR> 18 <TD align=left>�� �����</TD> 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 </TR> 21 <TR> 22 <TD align=left>�� �����</TD> 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 </TR> 25 <TR> 26 <TD align=center colspan=2><INPUT style="font-family:tahoma;" TYPE="Submit" Value=" ����� " tabindex=6></TD> 27 </TR> 28 </FORM> 29 </TABLE> 30 <br> 31<% 32elseif request("act")="show" then 33 FromDate= request("FromDate") 34 ToDate= request("ToDate") 35 36' Response.ContentType = "application/msexcel" 37' Response.AddHeader "Content-Disposition","attachment; filename=Customers.txt" 38 39 Separator=chr(9) 40 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<% 43 44' conStr="DRIVER={SQL Server};SERVER=(local);DATABASE=sefareshat;UID=sefadmin; PWD=5tgb;" 45conStr = "Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sefadmin;Initial Catalog=sefareshat;Data Source=(local);PWD=5tgb;" 46 47 Set conn = Server.CreateObject("ADODB.Connection") 48 conn.open conStr 49 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 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 54 Set RS1 = conn.Execute(mySQL) 55 56 cols=20 57 Dim Col1(20) 58 Dim Col2(20) 59 for i=1 to cols 60 Col1(i)="" 61 Col2(i)="" 62 next 63 LastAcc=0 64 tmpCounter=0 65 66 Do While NOT RS1.EOF 'and tmpCounter < 10 67 tmpCounter=tmpCounter+1 68 AccID =RS1("AccID") 69 if AccID<>LastAcc AND LastAcc <> 0 then 70 'write last row 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<% 73 for i=1 to cols 74 Col1(i)="" 75 Col2(i)="" 76 next 77 end if 78 79 AccountTitle =RS1("AccountTitle") 80 Price =cdbl(RS1("Price")) 81 Discount =cdbl(RS1("Discount")) 82 Reverse =cdbl(RS1("Reverse")) 83 CatID =cint(RS1("CatID")) 84 CatName =RS1("CatName") 85 InvQtty =clng(RS1("InvQtty")) 86 TotalInvQtty =clng(RS1("TotalInvQtty")) 87 TotalReceivable =cdbl(RS1("TotalReceivable")) 88 TotalDiscount =cdbl(RS1("TotalDiscount")) 89 TotalReverse =cdbl(RS1("TotalReverse")) 90 91 Col1(CatID)=Price-Discount-Reverse 92 Col2(CatID)=InvQtty 93 94 LastAcc=AccID 95 RS1.moveNext 96 Loop 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<% 99end if 100%>