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

/beta/AR/Rep_4col.asp

http://github.com/khaneh/Orders
ASP | 234 lines | 210 code | 20 blank | 4 comment | 34 complexity | 8462eea5c5dda35a44724f9a28650c94 MD5 | raw file
  1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2'Order (2)
  3PageTitle="����� ����"
  4SubmenuItem=11
  5if not Auth("C" , 8) then NotAllowdToViewThisPage()
  6%>
  7<!--#include file="top.asp" -->
  8<!--#include File="../include_farsiDateHandling.asp"-->
  9<!--#include File="../include_JS_InputMasks.asp"-->
 10<!--#include File="../include_UtilFunctions.asp"-->
 11<STYLE>
 12	.GetCustTbl {font-family:tahoma; background-color: #DDDDDD; width:630; direction: RTL; }
 13	.GetCustTbl td {padding:2; font-size: 9pt; height:25;}
 14	.GetCustInp { font-family:tahoma; font-size: 9pt;}
 15	.CusTableHeader {background-color: #33AACC; text-align: center; font-weight:bold;}
 16	.CustContactTable {font-family:tahoma; width:100%; border:1 solid black; direction: RTL; background-color:#CCCCCC;}
 17	.CustContactTable td {padding:5;}
 18	.CustTable {font-family:tahoma; width:80%; border:1 solid black; direction: RTL; background-color:black;}
 19	.CustTable td {padding:5;}
 20	.CustTable a {text-decoration:none;color:#000088}
 21	.CustTable a:hover {text-decoration:underline;}
 22	.CusTD1 {background-color: #CCCC66; text-align: left; font-weight:bold;}
 23	.CusTD2 {background-color: #DDDDDD; direction: LTR; text-align: right; font-size:9pt;}
 24	.CusTD3 {background-color: #DDDDDD; direction: LTR; text-align: center; font-size:9pt;}
 25	.CusTD4 {background-color: #CCCC66; direction: LTR; text-align: center; font-size:9pt;}
 26	.CustTable4 {font-family:tahoma; direction: RTL; width:100%; height:100%; background-color:#C3DBEB;}
 27</STYLE>
 28<%
 29if request("act")="show" then
 30
 31	'ON ERROR RESUME Next
 32	FromDate =		sqlSafe(request("FromDate"))
 33	ToDate = 		sqlSafe(request("ToDate"))
 34	Ord =			request("Ord")
 35	If Ord="" Then Ord = 0
 36
 37	'mySQL="SELECT account, accountTitle, sum(case arItems.[type] when 1 then amountOriginal else 0 end) as totalDebit, sum(case arItems.[type] when 2 then amountOriginal else 0 end) as totalCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id where voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' group by account,accountTitle" & order
 38
 39
 40	select case Ord
 41	case "1":
 42		order="account"
 43	case "-1":
 44		order="account DESC"
 45	case "2":
 46		order="AccountTitle"
 47	case "-2":
 48		order="AccountTitle DESC"
 49	case "3":
 50		order="totalDebit"
 51	case "-3":
 52		order="totalDebit DESC"
 53	case "4":
 54		order="totalCredit"
 55	case "-4":
 56		order="totalCredit DESC"
 57	Case "5":
 58		order = "remainDebit"
 59	Case "-5":
 60		order = "remainDebit DESC"
 61	case "6":
 62		order="remainCredit"
 63	case "-6":
 64		order="remainCredit DESC"
 65	case else:
 66		order="account"
 67		Ord=1
 68	end select
 69
 70	
 71%>
 72	<TABLE dir=rtl align=center width=640 cellspacing=2 cellpadding=2 style="border:2 solid #330066;">
 73	
 74<%
 75	mySQL = "select sum(remDebit) as sumRemDebit, sum(remCredit) as sumRemCredit, sum(totalDebit + remDebit) as sumTotalDebit, sum(totalCredit + remCredit) as sumTotalCredit, sum(case when (totalDebit+remDebit)>(totalCredit+remCredit) then (totalDebit+remDebit)-(totalCredit+remCredit) else 0 end) as remainDebit, sum(case when (totalDebit+remDebit)<(totalCredit+remCredit) then (totalCredit+remCredit)-(totalDebit+remDebit) else 0 end) as remainCredit, sum(Items) as totalItems from (SELECT CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END AS remDebit, CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END AS remCredit, account, accountTitle, sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) as totalDebit, sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) as totalCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id LEFT OUTER JOIN (SELECT account as remAccount, ISNULL(SUM(AmountOriginal *(CONVERT(tinyint, IsCredit) - .5) * 2 ), 0) as amount from arItems WHERE voided = 0 AND EffectiveDate < N'"&FromDate&"' GROUP BY account) remTbl on remTbl.remAccount = arItems.account WHERE voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' GROUP BY account,accountTitle,remTbl.amount) drvTBL"
 76'response.write mySQL
 77	set rs=Conn.Execute (mySQL)
 78	if rs.eof then
 79%>	<tr>
 80		<td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td>
 81	</tr>
 82<%	Else %>
 83	<TR bgcolor="#CCCCEE" >
 84		<TD colspan=2 rowspan=2 title=" <%=rs("totalItems")%> ���� ">
 85			�� ����� <B><%=replace(FromDate,"/",".")%></B> �� ����� <B><%=replace(ToDate,"/",".")%></B><br>
 86			�� ������ <B><%=FromTafsil%></B> �� ������ <B><%=ToTafsil%></B>
 87		</TD>
 88		<TD width=70 >���� ������		</TD>
 89		<TD width=70 >���� ��������		</TD>
 90		<TD width=70 >����� ������		</TD>
 91		<TD width=70 >����� ��������	</TD>
 92	</TR>
 93	<TR bgcolor="#CCCCEE" >
 94		<TD title='<%=Separate(rs("sumRemDebit"))%> ������� ����� ' width=70 ><%=Separate(rs("sumTotalDebit"))%></TD>
 95		<TD title='<%=Separate(rs("sumRemCredit"))%> ��������� ����� ' width=70 ><%=Separate(rs("sumTotalCredit"))%></TD>
 96		<TD width=70 ><%=Separate(rs("remainDebit"))%></TD>
 97		<TD width=70 ><%=Separate(rs("remainCredit"))%></TD>
 98	</TR>
 99	<TR bgcolor="black" height="2">
100		<TD colspan="6" style="padding:0;"></TD>
101	</TR>
102<%
103	rs.close
104
105	if ord<0 then
106		style="background-color: #33CC99;"
107		arrow="<br><span style='font-family:webdings'>6 6 6</span>"
108	else
109		style="background-color: #33CC99;"
110		arrow="<br><span style='font-family:webdings'>5 5 5</span>"
111	end if
112%>
113	<TR bgcolor="eeeeee" style="cursor:hand;" title="����� �����">
114		<TD width=50  onclick='go2Page(1,1);' style="<%if abs(ord)=1 then response.write style%>">����� ����			<%if abs(ord)=1 then response.write arrow%></TD>
115		<TD width='*' onclick='go2Page(1,2);' style="<%if abs(ord)=2 then response.write style%>">����� ����		<%if abs(ord)=2 then response.write arrow%></TD>
116		<TD width=70 onclick='go2Page(1,-3);' style="<%if abs(ord)=3 then response.write style%>">���� ������		<%if abs(ord)=3 then response.write arrow%></TD>
117		<TD width=70 onclick='go2Page(1,-4);' style="<%if abs(ord)=4 then response.write style%>">���� ��������		<%if abs(ord)=4 then response.write arrow%></TD>
118		<TD width=70 onclick='go2Page(1,-5);' style="<%if abs(ord)=5 then response.write style%>">����� ������		<%if abs(ord)=5 then response.write arrow%></TD>
119		<TD width=70 onclick='go2Page(1,-6);' style="<%if abs(ord)=6 then response.write style%>">����� ��������	<%if abs(ord)=6 then response.write arrow%></TD>
120	</TR>
121	<TR bgcolor="eeeeee" >
122		<TD colspan=6 height=2 bgcolor=0></TD>
123	</TR>
124<%		
125	SumCredit=0
126	SumDebit=0
127	SumCreditRemained=0
128	SumDebitRemained=0
129	tmpCounter=0
130
131	mySQL="SELECT CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END AS remDebit, CASE WHEN remTbl.amount > 0 THEN remTbl.amount ELSE 0 END AS remCredit, account, accountTitle, sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END as totalDebit, sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN  remTbl.amount > 0 THEN remTbl.amount ELSE 0 END as totalCredit, case when (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END) > (sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN  remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) then (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END)-(sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN  remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) else 0 end as remainDebit, case when (sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END) < (sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN  remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) then -(sum(case arItems.IsCredit when 0 then amountOriginal else 0 end) + CASE WHEN remTbl.amount > 0 THEN 0 ELSE - remTbl.amount END)+(sum(case arItems.IsCredit when 1 then amountOriginal else 0 end) + CASE WHEN  remTbl.amount > 0 THEN remTbl.amount ELSE 0 END) else 0 end as remainCredit, count(account) as Items FROM arItems inner join accounts on arItems.account = accounts.id LEFT OUTER JOIN (SELECT account as remAccount, ISNULL(SUM(AmountOriginal *(CONVERT(tinyint, IsCredit) - .5) * 2 ), 0) as amount from arItems WHERE voided = 0 AND EffectiveDate < N'"&FromDate&"' GROUP BY account) remTbl on remTbl.remAccount = arItems.account WHERE voided = 0 and effectiveDate between N'" & FromDate & "' and N'" & ToDate & "' GROUP BY account,accountTitle,remTbl.amount ORDER BY " & order
132	
133	Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL)
134
135	PageSize = 50
136	rs.PageSize = PageSize
137
138	rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3
139	rs.Open mySQL ,Conn,3
140	TotalPages = rs.PageCount
141
142	CurrentPage=1
143
144	if isnumeric(Request.QueryString("p")) then
145		pp=clng(Request.QueryString("p"))
146		if pp <= TotalPages AND pp > 0 then
147			CurrentPage = pp
148		end if
149	end if
150
151	if not rs.eof then
152		rs.AbsolutePage=CurrentPage
153	end if
154
155	if rs.eof then
156%>		<tr>
157			<td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td>
158		</tr>
159<%	else
160		Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage)
161			tmpCounter = tmpCounter + 1
162			if tmpCounter mod 2 = 1 then
163				tmpColor="#FFFFFF"
164				tmpColor2="#FFFFBB"
165			Else
166				tmpColor="#DDDDDD"
167				tmpColor2="#EEEEBB"
168			End if 
169	%>
170			<TR bgcolor="<%=tmpColor%>" >
171				<TD dir=ltr align=right><A href="AccountReport.asp?act=show&reason=6&sys=AR&selectedCustomer=<%=rs("account")%>&startDate=<%=FromDate%>&endDate=<%=ToDate%>"><%=rs("account")%></A></TD>
172				<TD title=" <%=rs("Items")%> ���� "><%=rs("accountTitle")%></TD>
173				<TD title='<%=Separate(rs("remDebit"))%> ������� ����� ' dir=ltr align=right><span dir=ltr><%=Separate(rs("totalDebit"))%></span></TD>
174				<TD title='<%=Separate(rs("remCredit"))%> ��������� ����� ' dir=ltr align=right><span dir=ltr><%=Separate(rs("totalCredit"))%></span></TD>
175				<TD dir=ltr align=right><span dir=ltr><%=Separate(rs("remainDebit"))%></span></TD>
176				<TD dir=ltr align=right><span dir=ltr><%=Separate(rs("remainCredit"))%></span></TD>
177			</TR>
178			  
179	<% 
180		rs.moveNext
181		Loop
182
183		if TotalPages > 1 then
184			pageCols=20
185%>			
186			<TR bgcolor="eeeeee" >
187				<TD colspan=6 height=2 bgcolor=0></TD>
188			</TR>
189			<TR class="RepTableTitle">
190				<TD bgcolor="#CCCCEE" height="30" colspan="6">
191				<table width=100% cellspacing=0 style="cursor:hand;color:gray;">
192				<tr>
193					<td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
194						<b>���� <%=CurrentPage%> �� <%=TotalPages%></b>
195						&nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>,0);">���� ��� &gt;</a>
196					</td>
197				</tr>
198				<tr>
199<%				for i=1 to TotalPages 
200					if i = CurrentPage then 
201%>						<td style="color:black;"><b>[<%=i%>]</b></td>
202<%					else
203%>						<td onclick="go2Page(<%=i%>,0);"><%=i%></td>
204<%					end if 
205					if i mod pageCols = 0 then response.write "</tr><tr>" 
206				next 
207
208%>				</tr>
209				</table>
210				</TD>
211			</TR>
212<%		end if
213%>
214		</TABLE>
215		<br>
216
217		<SCRIPT LANGUAGE="JavaScript">
218		function go2Page(p,ord) {
219			if(ord==0){
220				ord=<%=Ord%>;
221			}
222			else if(ord==<%=Ord%>){
223				ord= 0-ord;
224			}
225			str = '?act=show&FromDate=' + escape('<%=FromDate%>') + '&ToDate=' + escape('<%=ToDate%>') + '&Ord=' + escape(ord) + '&p=' + escape(p);  
226			window.location = str;
227		}
228		</SCRIPT>
229<%	
230	End if
231End If
232End If 
233%>
234<!--#include file="tah.asp" -->