PageRenderTime 24ms CodeModel.GetById 6ms app.highlight 7ms RepoModel.GetById 1ms app.codeStats 0ms

/beta/reports/Salse_by_customer_category.old.asp

http://github.com/khaneh/Orders
ASP | 280 lines | 253 code | 19 blank | 8 comment | 14 complexity | e4176a850d1e81e95374358a6275c611 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	reportTitle = "����� ���� ������� �� ��� ���� ��� ���� �� ���� ��� �����"
 36	%>
 37	<html>
 38	<head>
 39	<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1256">
 40	<title><%=reportTitle%></title>
 41	</head>
 42	<body>
 43	<%
 44	function Separate(inputTxt)
 45
 46	if not isnumeric(inputTxt) or "" & inputTxt="" then 
 47		Separate=inputTxt
 48	else
 49		myMinus=""
 50		input=inputTxt
 51		t=instr(input, ".")
 52		if t>0 then 
 53			expPart = mid(input, t+1, 2)
 54			input = left(input, t-1)
 55		end if
 56		if left(input,1)="-" then
 57			myMinus="-"
 58			input=right(input,len(input)-1)
 59		end if
 60		if len(input) > 3 then
 61			tmpr=right(input ,3)
 62			tmpl=left(input , len(input) - 3 )
 63			result = tmpr
 64			while len(tmpl) > 3
 65				tmpr=right(tmpl,3)
 66				result = tmpr & "," & result 
 67				tmpl=left(tmpl , len(tmpl) - 3 )
 68			wend
 69			if len(tmpl) > 0 then
 70				result = tmpl & "," & result
 71			end if 
 72		else
 73			result = input
 74		end if 
 75		if t>0 then 
 76			result = result & "." & expPart
 77		end if
 78
 79		Separate=myMinus & result
 80	end if
 81	end function
 82
 83'---------------------
 84
 85'	conStr="DRIVER={SQL Server};SERVER=(local);DATABASE=sefareshat;UID=sefadmin; PWD=5tgb;"
 86conStr = "Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sefadmin;Initial Catalog=sefareshat;Data Source=(local);PWD=5tgb;"
 87
 88	Set conn = Server.CreateObject("ADODB.Connection")
 89	conn.open conStr
 90
 91	'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"
 92
 93	'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"
 94	' coment by Alix - 84-10-10 (add CSRname column to query)
 95
 96	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, Kid.CSRname 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, Users.RealName AS CSRname 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 INNER JOIN Users ON Accounts.CSR = Users.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, Users.RealName 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, Kid.CSRname HAVING (Invoices.Issued = 1) AND (Invoices.Voided = 0) ORDER BY Kid.AccID, Kid.CatID"
 97
 98'------------------------------------------------------------------------------------------------------------------------------------
 99
100	Set RS1 = conn.Execute(mySQL)
101
102	cols=20
103	Dim Col1(20)
104	Dim Col2(20)
105	for i=1 to cols
106		Col1(i)=""
107		Col2(i)=""
108	next
109	LastAcc=0
110	tmpCounter=0
111
112	%>
113	<style>
114		.resTable1 { Font-family:tahoma; Font-Size:9pt; Background-color:#336699; border: 1 solid #336699;}
115		.resTable1 th {Background-color:#6699CC;}
116		.resRow1 {Background-color:#F8F8FF;}
117		.resRow0 {Background-color:#CCCCDD;}
118	</style>
119
120	<CENTER><H3><%=reportTitle%></H3></CENTER>
121	<table class="resTable1" Cellspacing="1" Cellpadding="2" align=center>
122		<th>����� ����</th>
123		<th>����� ����</th>
124		<th>���� 1 (����� ������)</th>
125		<th>�����</th>
126		<th>���� 2 (���� ����)</th>
127		<th>�����</th>
128		<th>���� 3 (����)</th>
129		<th>�����</th>
130		<th>���� 4 (�������)</th>
131		<th>�����</th>
132		<th>���� 5 (���� �����)</th>
133		<th>�����</th>
134		<th>���� 6 (�������)</th>
135		<th>�����</th>
136		<th>���� 7 (������ ������)</th>
137		<th>�����</th>
138		<th>���� 8 (�����)</th>
139		<th>�����</th>
140		<th>���� 9 (�� ��)</th>
141		<th>�����</th>
142		<th>���� 10 (����)</th>
143		<th>�����</th>
144		<th>���� 11 (���� ����� )</th>
145		<th>�����</th>
146		<th>���� 13 (������� ���� � ����)</th>
147		<th>�����</th>
148		<th>���� 14 (������ ������ �������)</th>
149		<th>�����</th>
150		<th>����� �� ������ ��</th>
151		<th>��� �� ���� ������</th>
152		<th>��� �� �����</th>
153		<th>��� �� �ѐ��</th>
154		<th>����� ������</th>
155
156		</tr>
157	<%
158		if RS1.EOF then
159	%>
160		<tr>
161			<td width="100%" class="resRow1" colspan="33" align="center">
162				����� ���� ���
163			</td>
164		</tr>
165	<%
166		else
167			Do While NOT RS1.EOF 'and tmpCounter < 10	
168				AccID			=RS1("AccID")
169				if AccID<>LastAcc AND LastAcc <> 0 then
170					'write last row
171					tmpCounter=tmpCounter+1
172				%>
173				<tr class="resRow<%=tmpCounter mod 2%>">
174					<td><%=LastAcc%></td>
175					<td><%=Server.HTMLEncode(AccountTitle)%></td>
176					<td><%=Separate(Col1(1))%></td>
177					<td><%=Separate(Col2(1))%></td>
178					<td><%=Separate(Col1(2))%></td>
179					<td><%=Separate(Col2(2))%></td>
180					<td><%=Separate(Col1(3))%></td>
181					<td><%=Separate(Col2(3))%></td>
182					<td><%=Separate(Col1(4))%></td>
183					<td><%=Separate(Col2(4))%></td>
184					<td><%=Separate(Col1(5))%></td>
185					<td><%=Separate(Col2(5))%></td>
186					<td><%=Separate(Col1(6))%></td>
187					<td><%=Separate(Col2(6))%></td>
188					<td><%=Separate(Col1(7))%></td>
189					<td><%=Separate(Col2(7))%></td>
190					<td><%=Separate(Col1(8))%></td>
191					<td><%=Separate(Col2(8))%></td>
192					<td><%=Separate(Col1(9))%></td>
193					<td><%=Separate(Col2(9))%></td>
194					<td><%=Separate(Col1(10))%></td>
195					<td><%=Separate(Col2(10))%></td>
196					<td><%=Separate(Col1(11))%></td>
197					<td><%=Separate(Col2(11))%></td>
198					<td><%=Separate(Col1(13))%></td>
199					<td><%=Separate(Col2(13))%></td>
200					<td><%=Separate(Col1(14))%></td>
201					<td><%=Separate(Col2(14))%></td>
202					<td><%=Separate(TotalInvQtty)%></td>
203					<td><%=Separate(TotalReceivable)%></td>
204					<td><%=Separate(TotalDiscount)%></td>
205					<td><%=Separate(TotalReverse)%></td>
206					<td><%=Server.HTMLEncode(CSRname)%></td>	
207				</tr>
208		<%
209					for i=1 to cols
210						Col1(i)=""
211						Col2(i)=""
212					next
213				end if
214
215				AccountTitle	=RS1("AccountTitle")
216				Price			=cdbl(RS1("Price"))
217				Discount		=cdbl(RS1("Discount"))
218				Reverse			=cdbl(RS1("Reverse"))
219				CatID			=cint(RS1("CatID"))
220				CatName			=RS1("CatName")
221				InvQtty			=clng(RS1("InvQtty"))
222				TotalInvQtty	=clng(RS1("TotalInvQtty"))
223				TotalReceivable	=cdbl(RS1("TotalReceivable"))
224				TotalDiscount	=cdbl(RS1("TotalDiscount"))
225				TotalReverse	=cdbl(RS1("TotalReverse"))
226				CSRname			=RS1("CSRname")
227
228				Col1(CatID)=Price-Discount-Reverse
229				Col2(CatID)=InvQtty
230
231				LastAcc=AccID
232				RS1.moveNext
233			Loop
234			tmpCounter=tmpCounter+1
235			%>
236				<tr class="resRow<%=tmpCounter mod 2%>">
237					<td><%=LastAcc%></td>
238					<td><%=Server.HTMLEncode(AccountTitle)%></td>
239					<td><%=Separate(Col1(1))%></td>
240					<td><%=Separate(Col2(1))%></td>
241					<td><%=Separate(Col1(2))%></td>
242					<td><%=Separate(Col2(2))%></td>
243					<td><%=Separate(Col1(3))%></td>
244					<td><%=Separate(Col2(3))%></td>
245					<td><%=Separate(Col1(4))%></td>
246					<td><%=Separate(Col2(4))%></td>
247					<td><%=Separate(Col1(5))%></td>
248					<td><%=Separate(Col2(5))%></td>
249					<td><%=Separate(Col1(6))%></td>
250					<td><%=Separate(Col2(6))%></td>
251					<td><%=Separate(Col1(7))%></td>
252					<td><%=Separate(Col2(7))%></td>
253					<td><%=Separate(Col1(8))%></td>
254					<td><%=Separate(Col2(8))%></td>
255					<td><%=Separate(Col1(9))%></td>
256					<td><%=Separate(Col2(9))%></td>
257					<td><%=Separate(Col1(10))%></td>
258					<td><%=Separate(Col2(10))%></td>
259					<td><%=Separate(Col1(11))%></td>
260					<td><%=Separate(Col2(11))%></td>
261					<td><%=Separate(Col1(13))%></td>
262					<td><%=Separate(Col2(13))%></td>
263					<td><%=Separate(Col1(14))%></td>
264					<td><%=Separate(Col2(14))%></td>
265					<td><%=Separate(TotalInvQtty)%></td>
266					<td><%=Separate(TotalReceivable)%></td>
267					<td><%=Separate(TotalDiscount)%></td>
268					<td><%=Separate(TotalReverse)%></td>
269					<td><%=Server.HTMLEncode(CSRname)%></td>
270				</tr>
271	</table>
272<%
273
274end if
275
276'------------------------------------------------------------------------------------------------------------------------------------
277end if
278%>
279</body>
280</html>