PageRenderTime 22ms CodeModel.GetById 14ms app.highlight 2ms RepoModel.GetById 2ms app.codeStats 0ms

/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<%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%>