PageRenderTime 27ms CodeModel.GetById 19ms app.highlight 5ms RepoModel.GetById 1ms app.codeStats 0ms

/beta/accounting/OtherReports_Tmp4Zamani.asp

http://github.com/khaneh/Orders
ASP | 252 lines | 214 code | 27 blank | 11 comment | 18 complexity | bbe7c9e24ad7259b558cc18a42207900 MD5 | raw file
  1<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2'Accounting (8)
  3PageTitle="���� ����� ��"
  4SubmenuItem=10
  5if not Auth(8 , "E") then NotAllowdToViewThisPage()
  6
  7%>
  8<!--#include file="top.asp" -->
  9<!--#include File="../include_farsiDateHandling.asp"-->
 10<!--#include File="../include_JS_InputMasks.asp"-->
 11<STYLE>
 12	.RepTable {font-family:tahoma; font-size:9pt; direction: RTL; }
 13	.RepTable td {border:1pt solid white;vertical-align:top;}
 14	.RepTable a {text-decoration:none; color:#222288;}
 15	.RepTable a:hover {text-decoration:underline;}
 16	.RepTable2 th {font-size:9pt; background-color:#666699;height:25px;}
 17	.RepTable2 input {font-family:tahoma; font-size:9pt; border:1 solid black;}
 18</STYLE>
 19<BR>
 20<%
 21'-----------------------------------------------------------------------------------------------------
 22'----------------------------------------------------------------------------------------- Search Form
 23'-----------------------------------------------------------------------------------------------------
 24if request("act")="MoeenRep" OR request("act")="" then
 25
 26	Ord=request("Ord")
 27
 28	select case Ord
 29	case "1":
 30		order="Tafsil"
 31	case "-1":
 32		order="Tafsil DESC"
 33	case "2":
 34		order="AccountTitle"
 35	case "-2":
 36		order="AccountTitle DESC"
 37	case "3":
 38		order="totalDebit"
 39	case "-3":
 40		order="totalDebit DESC"
 41	case "4":
 42		order="totalCredit"
 43	case "-4":
 44		order="totalCredit DESC"
 45	case "5","-6":
 46		order="ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) - ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0) DESC"
 47	case "6","-5":
 48		order="ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) - ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0)"
 49	case else:
 50		order="Tafsil"
 51		Ord=1
 52	end select
 53
 54%>
 55	<SCRIPT LANGUAGE="JavaScript">
 56	<!--
 57	function showAcc(acc){
 58		window.open('tafsili.asp?accountID='+acc+'&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&moeenFrom=0&moeenTo=99999&act=Show');
 59	}
 60	//-->
 61	</SCRIPT>
 62	<TABLE dir=rtl align=center width=640 cellspacing=2 cellpadding=2 style="border:2 solid #330066;">
 63	<TR bgcolor="#EEEECC" height="30">
 64		<TD colspan=7>
 65		����� ���� ��� ������� �� ����� ���� �� ����� ��� 82	
 66		</TD>
 67	</TR>
 68<%
 69
 70	'mySQL = "SELECT SUM(SumCred) AS SumCred, SUM(SumDeb) AS SumDeb, SUM(Flow * (Sgn + 1) / 2) AS sumFlowCred, SUM(Flow * (Sgn - 1) / 2) AS sumFlowdeb FROM (SELECT SUM(IsCredit * Amount) AS SumCred, SUM(- ((IsCredit - 1) * Amount)) AS SumDeb, SUM(IsCredit * Amount) - SUM(- ((IsCredit - 1) * Amount)) AS Flow, SIGN(SUM(IsCredit * Amount) - SUM(- ((IsCredit - 1) * Amount))) AS Sgn FROM EffectiveGLRows WHERE (GLAccount = "& GLAccount & ") AND (GL = "& OpenGL & ") AND (ISNULL(Tafsil, 0) >= "& FromTafsil & ") AND (ISNULL(Tafsil, 0) <= "& ToTafsil & ") AND (GLDocDate >= N'"& FromDate & "') AND (GLDocDate <= N'"& ToDate & "') GROUP BY Tafsil) FlowTbl"
 71	mySQL="SELECT SUM(totalCredit) AS sumCred, SUM(totalDebit) AS sumDeb, SUM(Flow * (Sgn + 1) / 2) AS sumFlowCred, SUM(Flow * (Sgn - 1) / 2) AS sumFlowdeb FROM (SELECT Account AS Tafsil, ISNULL(SUM(AmountOriginal * IsCredit), 0) AS totalCredit, ISNULL(SUM(AmountOriginal * (1 - IsCredit)), 0) AS totalDebit, ISNULL(SUM(AmountOriginal * (CONVERT(tinyint, IsCredit) - .5) * 2), 0) AS Flow, ISNULL(SIGN(SUM(AmountOriginal * (CONVERT(tinyint, IsCredit) - .5) * 2)), 0) AS Sgn FROM ARItems WHERE (Voided = 0) AND (EffectiveDate < '1383/01/01') GROUP BY Account) FlowTbl"
 72
 73	set rs=Conn.Execute (mySQL)
 74
 75%>
 76	<TR bgcolor="#EEEECC" >
 77		<TD colspan=2 rowspan=2>
 78		���
 79		</TD>
 80		<TD width=70 >���� ������		</TD>
 81		<TD width=70 >���� ��������		</TD>
 82		<TD width=70 >����� ������		</TD>
 83		<TD width=70 >����� ��������	</TD>
 84	</TR>
 85	<TR bgcolor="#EEEECC" >
 86		<TD width=70 ><%=Separate(rs("SumDeb"))%></TD>
 87		<TD width=70 ><%=Separate(rs("SumCred"))%></TD>
 88		<TD width=70 ><%=Separate(rs("SumFlowDeb"))%></TD>
 89		<TD width=70 ><%=Separate(rs("SumFlowCred"))%></TD>
 90	</TR>
 91	<TR bgcolor="black" height="2">
 92		<TD colspan="6" style="padding:0;"></TD>
 93	</TR>
 94<%
 95	rs.close
 96
 97	if ord<0 then
 98		style="background-color: #33CC99;"
 99		arrow="<br><span style='font-family:webdings'>6 6 6</span>"
100	else
101		style="background-color: #33CC99;"
102		arrow="<br><span style='font-family:webdings'>5 5 5</span>"
103	end if
104%>
105	<TR bgcolor="eeeeee" style="cursor:hand;" title="����� �����">
106		<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>
107		<TD width='*' onclick='go2Page(1,2);' style="<%if abs(ord)=2 then response.write style%>">����� ����		<%if abs(ord)=2 then response.write arrow%></TD>
108		<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>
109		<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>
110		<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>
111		<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>
112	</TR>
113	<TR bgcolor="eeeeee" >
114		<TD colspan=6 height=2 bgcolor=0></TD>
115	</TR>
116<%		
117	SumCredit=0
118	SumDebit=0
119	SumCreditRemained=0
120	SumDebitRemained=0
121	tmpCounter=0
122
123	'mySQL="SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit, Accounts.AccountTitle AS AccountTitle FROM (SELECT ID AS GLDoc, GLDocDate FROM GLDocs WHERE (GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocDate >= N'"& FromDate & "') AND (GLDocDate <= N'"& ToDate & "') AND (GL = "& openGL & " ) AND (IsRemoved = 0) AND (deleted = 0)) EffectiveGLDocs INNER JOIN GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc INNER JOIN Accounts ON GLRows.Tafsil = Accounts.ID WHERE (GLRows.GLAccount = "& GLAccount & ") AND (GLRows.deleted = 0) GROUP BY GLRows.Tafsil, Accounts.AccountTitle HAVING (ISNULL(GLRows.Tafsil, 0) >= "& FromTafsil & ") AND (ISNULL(GLRows.Tafsil, 0) <= "& ToTafsil & ") ORDER BY " & order
124	mySQL="SELECT ARItems.Account AS Tafsil, ISNULL(SUM(ARItems.AmountOriginal * ARItems.IsCredit), 0) AS totalCredit, ISNULL(SUM(ARItems.AmountOriginal * (1 - ARItems.IsCredit)), 0) AS totalDebit, Accounts.AccountTitle FROM ARItems INNER JOIN Accounts ON ARItems.Account = Accounts.ID WHERE (ARItems.Voided = 0) AND (ARItems.EffectiveDate < '1383/01/01') GROUP BY ARItems.Account, Accounts.AccountTitle ORDER BY "& order
125
126'
127' The Differences:
128' mySQL="SELECT FromHan.Tafsil, FromHan.totalCredit AS HanCredit, FromHan.totalDebit AS HanDebit, DRV.totalCredit AS sysCred, DRV.totalDebit AS sysDeb FROM (SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit FROM (SELECT ID AS GLDoc, GLDocDate FROM GLDocs WHERE (GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/01/01') AND (GLDocDate <= N'1383/01/01') AND (GL = 83) AND (IsRemoved = 0) AND (deleted = 0)) EffectiveGLDocs INNER JOIN GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc WHERE (GLRows.deleted = 0) AND (LEFT(GLRows.GLAccount, 2) = 13) AND (GLRows.Tafsil IS NOT NULL) GROUP BY GLRows.Tafsil) FromHan INNER JOIN (SELECT Account AS Tafsil, ISNULL(SUM(AmountOriginal * IsCredit), 0) AS totalCredit, ISNULL(SUM(AmountOriginal * (1 - IsCredit)), 0) AS totalDebit FROM ARItems WHERE (Voided = 0) AND (EffectiveDate < '1383/01/01') GROUP BY Account) DRV ON FromHan.Tafsil = DRV.Tafsil AND FromHan.totalCredit - FromHan.totalDebit <> DRV.totalCredit - DRV.totalDebit"
129'
130'
131	Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL)
132
133	PageSize = 25
134	rs.PageSize = PageSize
135
136	rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3
137	rs.Open mySQL ,Conn,3
138	TotalPages = rs.PageCount
139
140	CurrentPage=1
141
142	if isnumeric(Request.QueryString("p")) then
143		pp=clng(Request.QueryString("p"))
144		if pp <= TotalPages AND pp > 0 then
145			CurrentPage = pp
146		end if
147	end if
148
149	if not rs.eof then
150		rs.AbsolutePage=CurrentPage
151	end if
152
153	if rs.eof then
154%>		<tr>
155			<td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>��� .</b></td>
156		</tr>
157<%	else
158		Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage)
159			tmpCounter = tmpCounter + 1
160			if tmpCounter mod 2 = 1 then
161				tmpColor="#FFFFFF"
162				tmpColor2="#FFFFBB"
163			Else
164				tmpColor="#DDDDDD"
165				tmpColor2="#EEEEBB"
166			End if 
167
168			totalDebit =	cdbl(rs("totalDebit"))
169			totalCredit =	cdbl(rs("totalCredit"))
170
171			if totalCredit > totalDebit then
172				creditRemained =	totalCredit - totalDebit
173				debitRemained =	0
174			else
175				creditRemained =	0
176				debitRemained =	totalDebit - totalCredit
177			end if
178
179			SumCredit = SumCredit + totalCredit 
180			SumDebit =	SumDebit + totalDebit
181
182			SumCreditRemained = SumCreditRemained + creditRemained
183			SumDebitRemained =	SumDebitRemained + debitRemained
184
185	%>
186			<TR bgcolor="<%=tmpColor%>" >
187				<TD dir=ltr align=right><A HREF="javascript:showAcc(<%=rs("Tafsil")%>);"><%=rs("Tafsil")%></A></TD>
188				<TD><%=rs("AccountTitle")%></TD>
189				<TD dir=ltr align=right><span dir=ltr><%=Separate(totalDebit)%></span></TD>
190				<TD dir=ltr align=right><span dir=ltr><%=Separate(totalCredit)%></span></TD>
191				<TD dir=ltr align=right><span dir=ltr><%=Separate(debitRemained)%></span></TD>
192				<TD dir=ltr align=right><span dir=ltr><%=Separate(creditRemained)%></span></TD>
193			</TR>
194			 
195	<% 
196		rs.moveNext
197		Loop
198
199		if TotalPages > 1 then
200			pageCols=20
201%>			
202			<TR bgcolor="eeeeee" >
203				<TD colspan=6 height=2 bgcolor=0></TD>
204			</TR>
205			<TR class="RepTableTitle">
206				<TD bgcolor="#CCCCEE" height="30" colspan="6">
207				<table width=100% cellspacing=0 style="cursor:hand;color:gray;">
208				<tr>
209					<td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
210						<b>���� <%=CurrentPage%> �� <%=TotalPages%></b>
211						&nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>,0);">���� ��� &gt;</a>
212					</td>
213				</tr>
214				<tr>
215<%				for i=1 to TotalPages 
216					if i = CurrentPage then 
217%>						<td style="color:black;"><b>[<%=i%>]</b></td>
218<%					else
219%>						<td onclick="go2Page(<%=i%>,0);"><%=i%></td>
220<%					end if 
221					if i mod pageCols = 0 then response.write "</tr><tr>" 
222				next 
223
224%>				</tr>
225				</table>
226				</TD>
227			</TR>
228<%		end if
229%>
230		</TABLE><br>
231		<SCRIPT LANGUAGE="JavaScript">
232		<!--
233		function go2Page(p,ord) {
234			if(ord==0){
235				ord=<%=Ord%>;
236			}
237			else if(ord==<%=Ord%>){
238				ord= 0-ord;
239			}
240			str='?act=MoeenRep&GLAccount='+escape('<%=GLAccount%>')+'&FromDate='+escape('<%=FromDate%>')+'&ToDate='+escape('<%=ToDate%>')+'&FromTafsil='+escape('<%=FromTafsil%>')+'&ToTafsil='+escape('<%=ToTafsil%>')+'&Ord='+escape(ord)+'&p='+escape(p) //+'& ='+escape(' ')+'& ='+escape(' ')+'& ='+escape(' ')
241			window.location=str;
242		}
243		//-->
244		</SCRIPT>
245<%
246	end if
247end if
248
249
250%>
251
252<!--#include file="tah.asp" -->