/beta/reports/Salse_by_customer_category.old.asp
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>