PageRenderTime 52ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 0ms

/beta/accounting/OtherReports.asp

http://github.com/khaneh/Orders
ASP | 1064 lines | 974 code | 57 blank | 33 comment | 65 complexity | 43802319e6b091a3b2a50d624293541a MD5 | raw file
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%><%
  2. 'Accounting (8)
  3. PageTitle="ساير گزارش ها"
  4. SubmenuItem=10
  5. if not Auth(8 , "E") 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. .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. .RepTR0 {background-color: #DDDDDD;}
  19. .RepTR1 {background-color: #FFFFFF;}
  20. .RepTableHeader {background-color: #BBBBFF; text-align: center; font-weight:bold;}
  21. .RepTDb {font-weight: bold;}
  22. </STYLE>
  23. <BR>
  24. <%
  25. '-----------------------------------------------------------------------------------------------------
  26. '----------------------------------------------------------------------------------------- Search Form
  27. '-----------------------------------------------------------------------------------------------------
  28. if request("act")="" then
  29. %>
  30. <TABLE class="RepTable" align=center cellspacing=10>
  31. <TR>
  32. <TD width="130">
  33. <FORM METHOD=POST ACTION="?act=MoeenRep">
  34. <table class="RepTable2" id="MoeenRep01">
  35. <tr>
  36. <th colspan="2">گزارش معين</td>
  37. </tr>
  38. <tr>
  39. <td align=left>معين</td>
  40. <td><INPUT TYPE="text" NAME="GLAccount" style="width:50px;" maxlength=5></td>
  41. </tr>
  42. <tr>
  43. <td align=left>از تاريخ</td>
  44. <td><INPUT TYPE="text" NAME="FromDate" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  45. </tr>
  46. <tr>
  47. <td align=left>تا تاريخ</td>
  48. <td><INPUT TYPE="text" NAME="ToDate" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  49. </tr>
  50. <tr>
  51. <td align=left>از تفصيل</td>
  52. <td><INPUT TYPE="text" NAME="FromTafsil" style="width:50px;" maxlength=6 value="0"></td>
  53. </tr>
  54. <tr>
  55. <td align=left>تا تفصيل</td>
  56. <td><INPUT TYPE="text" NAME="ToTafsil" style="width:50px;" maxlength=6 value="999999">
  57. <INPUT TYPE="hidden" NAME="GL" value="<%=OpenGL%>">
  58. </td>
  59. </tr>
  60. </table>
  61. <div align=center>
  62. <% ReportLogRow = PrepareReport ("Receipt.rpt", "Recept_ID", Receipt, "/beta/dialog_printManager.asp?act=Fin") %>
  63. <INPUT Class="GenButton" TYPE="submit" name="action" value=" نمايش ">
  64. <INPUT Class="GenButton" TYPE="submit" name="action" style="border:1 solid green;" value=" چاپ ">
  65. </div>
  66. </FORM>
  67. </TD>
  68. <TD width="130" align=center>
  69. &nbsp;
  70. <A HREF="OtherReports_Tmp4Zamani.asp" style='font-weight:bold;'>مانده حساب هاي مشتريان در سيستم جامع در پايان سال 82</A>
  71. </TD>
  72. </TR>
  73. <TR>
  74. <TD width="130">
  75. <%
  76. if Auth(8 , "G") then
  77. %>
  78. <FORM METHOD=POST ACTION="?act=cash">
  79. <table class="RepTable2">
  80. <tr>
  81. <th colspan="2">دشبورد خزانهداري</td>
  82. </tr>
  83. </table>
  84. <div align="center">
  85. <INPUT Class="GenButton" TYPE="submit" name="action" value=" نمايش ">
  86. </div>
  87. </form>
  88. <%end if%>
  89. </TD>
  90. <TD>
  91. <%
  92. if Auth(8,"G") then
  93. set rs=Conn.Execute ("select * from gls where id="&openGL)
  94. if not rs.eof then
  95. startDate=rs("startDate")
  96. if shamsiToday()>rs("endDate") then
  97. endDate=rs("endDate")
  98. else
  99. endDate=shamsiToday()
  100. end if
  101. end if
  102. %>
  103. <form method="post" action="?act=finState">
  104. <table class="RepTable2">
  105. <tr>
  106. <th colspan="2">صورت‌هاي مالي</th>
  107. <tr>
  108. <td align=left>از تاريخ</td>
  109. <td><INPUT TYPE="text" NAME="FromDate" value="<%=startDate%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  110. </tr>
  111. <tr>
  112. <td align=left>تا تاريخ</td>
  113. <td><INPUT TYPE="text" NAME="ToDate" value="<%=endDate%>" style="width:75px;direction:LTR;" maxlength=10 OnBlur="return acceptDate(this);"></td>
  114. </tr>
  115. <td colspan="2">
  116. <input name="rep" type="submit" value="سود و زيان">
  117. <input name="rep" type="submit" value="تراز نامه">
  118. <input name="rep" type="submit" value="گردش وجوه نقد">
  119. </td>
  120. </tr>
  121. </table>
  122. </form>
  123. <%
  124. end if
  125. %>
  126. </TD>
  127. </TR>
  128. <TR>
  129. <TD width="130">
  130. <%
  131. if Auth(8,"G") then
  132. %>
  133. <table class="RepTable2">
  134. <tr>
  135. <td colspan="2">گزارش تجزيه اسناد</td>
  136. </tr>
  137. <tr>
  138. <td><a href="otherReports.asp?act=sepPay">اسناد پرداختني</a></td>
  139. <td><a href="otherReports.asp?act=sepRec">اسناد دريافتني</a></td>
  140. </tr>
  141. </table>
  142. <%
  143. end if
  144. %>
  145. </td>
  146. <td></td>
  147. </TABLE>
  148. <%
  149. '-----------------------------------------------------------------------------------------------------
  150. '-----------------------------------------------------------------------------------------------------
  151. '-----------------------------------------------------------------------------------------------------
  152. elseif request("act")="sepPay" or request("act")="sepRec" then
  153. if request("act")="sepPay" then
  154. mySQL="select g42.*, case when g41.glAccount=41001 then N'تجاري' else N'غير تجاري' end as state from EffectiveGLRows as g42 inner join EffectiveGLRows as g41 on g41.glDoc=g42.glDoc and g41.sys=g42.sys and g41.link=g42.link where g42.GL=" & openGL & " and g42.glAccount between 42000 and 42999 and g41.glAccount between 41000 and 41999"
  155. else
  156. mySQL="select g17.*, case when g13.glAccount=13003 then N'تجاري' else N'غير تجاري' end as state from EffectiveGLRows as g17 inner join EffectiveGLRows as g13 on g17.glDoc=g13.glDoc and g17.sys=g13.sys and g17.link=g13.link where g17.GL=" & openGL & " and g17.glAccount between 17000 and 17999 and g17.ref1<>'' and not g13.glAccount between 17000 and 17999 "
  157. end if
  158. set rs=Conn.Execute(mySQL)
  159. %>
  160. <table class="RepTable">
  161. <tr class="RepTableHeader">
  162. <th>شماره سند</th>
  163. <th>تاريخ سند</th>
  164. <th>معين</th>
  165. <th>مبلغ</th>
  166. <th>شرح</th>
  167. <th>شماره چك</th>
  168. <th>تاريخ چك</th>
  169. <th>وضعيت</th>
  170. </tr>
  171. <%
  172. rowColor="RepTR0"
  173. while not rs.eof
  174. theLink = "ShowItem.asp?sys=" & rs("sys") & "&Item=" & rs("Link")
  175. if rowColor="RepTR0" then
  176. rowColor="RepTR1"
  177. else
  178. rowColor="RepTR0"
  179. end if
  180. %>
  181. <tr class="<%=rowColor%>">
  182. <td style="cursor:hand;" title="براي نمايش سند حسابداري مربوطه كليك كنيد." onclick="window.open('GLMemoDocShow.asp?id=<%=rs("glDoc")%>');"><%=rs("glDocID")%></td>
  183. <td><%=rs("glDocDate")%></td>
  184. <td><%=rs("glAccount")%></td>
  185. <td><%=Separate(rs("amount"))%></td>
  186. <td style="cursor:hand;" title="براي نمايش سند زير سيستم مربوطه كليك كنيد." onclick="window.open('<%=theLink%>');"><%=rs("description")%></td>
  187. <td style="cursor:hand;" title="براي نمايش چك مربوطه كليك كنيد." onclick="window.open('../bank/CheqBook.asp?act=findCheq&cheque=<%=rs("ref1")%>&amount=<%=rs("amount")%>');"><%=rs("ref1")%></td>
  188. <td><%=rs("ref2")%></td>
  189. <td><%=rs("state")%></td>
  190. </tr>
  191. <%
  192. rs.moveNext
  193. wend
  194. %>
  195. </table>
  196. <%
  197. '-----------------------------------------------------------------------------------------------------
  198. '-----------------------------------------------------------------------------------------------------
  199. '-----------------------------------------------------------------------------------------------------
  200. elseif request("act")="finState" then
  201. fromDate=request("fromDate")
  202. toDate=request("toDate")
  203. function getRem(v,t)
  204. if t="group" then
  205. mySQL="select isnull(sum(effectiveGlRows.Amount*2*(cast(effectiveGlRows.IsCredit as int)-.5)),0) as amount from effectiveGlRows inner join glAccounts on effectiveGlRows.glAccount=glAccounts.id and effectiveGlRows.gl=glAccounts.gl where effectiveGlRows.gl=" & OpenGL & " and glAccounts.GLGroup=" & v & " and effectiveGLRows.glDocDate between '" & fromDate & "' and '" & toDate & "'"
  206. elseif t="super" then
  207. mySQL="select isnull(sum(effectiveGlRows.Amount*2*(cast(effectiveGlRows.IsCredit as int)-.5)),0) as amount from effectiveGlRows inner join glAccounts on effectiveGlRows.glAccount=glAccounts.id and effectiveGlRows.gl=glAccounts.gl inner join GLAccountGroups on glAccounts.GLGroup=GLAccountGroups.ID and GLAccountGroups.gl=glAccounts.gl where effectiveGlRows.gl=" & OpenGL & " and GLAccountGroups.GLSuperGroup=" & v & " and effectiveGLRows.glDocDate between '" & fromDate & "' and '" & toDate & "'"
  208. end if
  209. set rs=Conn.Execute(mySQL)
  210. if not rs.eof then
  211. getRem = CDbl(rs("amount"))
  212. else
  213. getRem = "خطا"
  214. end if
  215. end function
  216. if request("rep")="سود و زيان" then
  217. sales = getRem(91000,"group")
  218. cogs = abs(getRem(30000,"super"))
  219. grossProfit = sales - cogs
  220. cgA = abs(getRem(62000,"group")+getRem(63000,"group")+getRem(68000,"group")+getRem(69000,"group"))
  221. rANDd = 0
  222. other = 0
  223. Interest = abs(getRem(61000,"group"))
  224. netProfit = grossProfit - cgA - rANDd - other - interest
  225. tax = 0
  226. Depreciation = 0
  227. EBITDA = netProfit + interest + tax + depreciation
  228. %>
  229. <table class="RepTable">
  230. <tr>
  231. <th>ترازنامه</th>
  232. <th>از <%=fromDate%> الي <%=toDate%></th>
  233. </tr>
  234. <tr>
  235. <td> فروش‌ خالص</td>
  236. <td><%=Separate(sales)%></td>
  237. </tr>
  238. <tr>
  239. <td> بهاي تمامشده كالاي فروش‌رفته</td>
  240. <td><%=Separate(cogs)%></td>
  241. </tr>
  242. <tr>
  243. <td>سود (زيان) ناخالص</td>
  244. <td><%=Separate(grossProfit)%></td>
  245. </tr>
  246. <tr>
  247. <td> فروش‌، اداري و عمومي</td>
  248. <td><%=Separate(cgA)%></td>
  249. </tr>
  250. <tr>
  251. <td>تحقيق و توسعه</td>
  252. <td><%=Separate(rANDd)%></td>
  253. </tr>
  254. <tr>
  255. <td>هزينه (درآمد) بهره</td>
  256. <td><%=Separate(interest)%></td>
  257. </tr>
  258. <tr>
  259. <td>ساير (عناوين اصلي ذكر شود)</td>
  260. <td><%=Separate(other)%></td>
  261. </tr>
  262. <tr>
  263. <td>سود (زيان) خالص‌</td>
  264. <td><%=Separate(netProfit)%></td>
  265. </tr>
  266. <tr>
  267. <td>هزينههاي بهره</td>
  268. <td><%=Separate(interest)%></td>
  269. </tr>
  270. <tr>
  271. <td>ماليات</td>
  272. <td><%=Separate(tax)%></td>
  273. </tr>
  274. <tr>
  275. <td>استهلاك</td>
  276. <td><%=Separate(depreciation)%></td>
  277. </tr>
  278. <tr>
  279. <td>ابيتدا</td>
  280. <td><%=Separate(EBITDA)%></td>
  281. </tr>
  282. </table>
  283. <%
  284. elseif request("rep")="تراز نامه" then
  285. 'mySQL="select sum(g42.amount) as amount from EffectiveGLRows as g42 inner join EffectiveGLRows as g41 on g41.glDoc=g42.glDoc and g41.sys=g42.sys and g41.link=g42.link where g42.GL=" & openGL & " and g42.glAccount between 42000 and 42999 and g41.glAccount between 41000 and 41999 and g42.isCredit=1 and g41.glAccount=41001"
  286. mySQL="select isnull(sum(amount),0) as amount from (select sum(amount)/count(id) as amount from EffectiveGLRows inner join (select g42.ref1,g42.ref2 from EffectiveGLRows as g42 inner join EffectiveGLRows as g41 on g41.glDoc=g42.glDoc and g41.sys=g42.sys and g41.link=g42.link where g42.GL=" & openGL & " and g42.glAccount between 42000 and 42999 and g41.glAccount between 41000 and 41999 and g42.isCredit=1 and g41.glAccount=41001 and g42.ref1<>'' and g42.glDocDate between '" & fromDate & "' and '" & toDate & "') as drv on EffectiveGLRows.Ref1=drv.ref1 and EffectiveGLRows.ref2=drv.ref2 where EffectiveGLRows.gl=" & openGL & " and glAccount between 42000 and 42999 and glDocDate between '" & fromDate & "' and '" & toDate & "' group by GLAccount, Tafsil, Amount,EffectiveGLRows.ref1,EffectiveGLRows.ref2 having count(EffectiveGLRows.ref1) % 2 =1) as d"
  287. rs=conn.Execute(mySQL)
  288. accountsPayableTej = cdbl(rs("amount"))
  289. 'mySQL="select sum(g42.amount) as amount from EffectiveGLRows as g42 inner join EffectiveGLRows as g41 on g41.glDoc=g42.glDoc and g41.sys=g42.sys and g41.link=g42.link where g42.GL=" & openGL & " and g42.glAccount between 42000 and 42999 and g41.glAccount between 41000 and 41999 and g42.isCredit=1 and g41.glAccount<>41001"
  290. mySQL="select isnull(sum(amount),0) as amount from (select sum(amount)/count(id) as amount from EffectiveGLRows inner join (select g42.ref1,g42.ref2 from EffectiveGLRows as g42 inner join EffectiveGLRows as g41 on g41.glDoc=g42.glDoc and g41.sys=g42.sys and g41.link=g42.link where g42.GL=" & openGL & " and g42.glAccount between 42000 and 42999 and g41.glAccount between 41000 and 41999 and g42.isCredit=1 and g41.glAccount<>41001 and g42.ref1<>'' and g42.glDocDate between '" & fromDate & "' and '" & toDate & "') as drv on EffectiveGLRows.Ref1=drv.ref1 and EffectiveGLRows.ref2=drv.ref2 where EffectiveGLRows.gl=" & openGL & " and glAccount between 42000 and 42999 and glDocDate between '" & fromDate & "' and '" & toDate & "' group by GLAccount, Tafsil, Amount,EffectiveGLRows.ref1,EffectiveGLRows.ref2 having count(EffectiveGLRows.ref1) % 2 =1) as d"
  291. rs=conn.Execute(mySQL)
  292. accountsPayableNonTej = cdbl(rs("amount"))
  293. 'mySQL="select sum(g17.amount) as amount from EffectiveGLRows as g17 inner join EffectiveGLRows as g13 on g17.glDoc=g13.glDoc and g17.sys=g13.sys and g17.link=g13.link where g17.GL=" & openGL & " and g17.glAccount between 17000 and 17999 and g17.ref1<>'' and not g13.glAccount between 17000 and 17999 and g17.isCredit=0 and g13.glAccount <> 13003"
  294. mySQL="select isnull(sum(amount),0) as amount from (select sum(amount)/count(id) as amount from EffectiveGLRows inner join (select g17.ref1,g17.ref2 from EffectiveGLRows as g17 inner join EffectiveGLRows as g13 on g17.glDoc=g13.glDoc and g17.sys=g13.sys and g17.link=g13.link where g17.GL=" & openGL & " and g17.glAccount between 17000 and 17999 and g17.ref1<>'' and not g13.glAccount between 17000 and 17999 and g17.isCredit=0 and g13.glAccount <> 13003 and g17.glDocDate between '" & fromDate & "' and '" & toDate & "') as drv on EffectiveGLRows.Ref1=drv.ref1 and EffectiveGLRows.ref2=drv.ref2 where EffectiveGLRows.gl=" & openGL & " and glAccount between 17000 and 17999 and glDocDate between '" & fromDate & "' and '" & toDate & "' group by GLAccount, Tafsil, Amount,EffectiveGLRows.ref1,EffectiveGLRows.ref2 having count(EffectiveGLRows.ref1) % 2 =1) as d"
  295. rs=conn.Execute(mySQL)
  296. accountsReceviableNonTej = cdbl(rs("amount"))
  297. 'mySQL="select sum(g17.amount) as amount from EffectiveGLRows as g17 inner join EffectiveGLRows as g13 on g17.glDoc=g13.glDoc and g17.sys=g13.sys and g17.link=g13.link where g17.GL=" & openGL & " and g17.glAccount between 17000 and 17999 and g17.ref1<>'' and not g13.glAccount=13003 and g17.isCredit=0"
  298. mySQL="select isnull(sum(amount),0) as amount from (select sum(amount)/count(id) as amount from EffectiveGLRows inner join (select g17.ref1,g17.ref2 from EffectiveGLRows as g17 inner join EffectiveGLRows as g13 on g17.glDoc=g13.glDoc and g17.sys=g13.sys and g17.link=g13.link where g17.GL=" & openGL & " and g17.glAccount between 17000 and 17999 and g17.ref1<>'' and not g13.glAccount=13003 and g17.isCredit=0 and g17.glDocDate between '" & fromDate & "' and '" & toDate & "') as drv on EffectiveGLRows.Ref1=drv.ref1 and EffectiveGLRows.ref2=drv.ref2 where EffectiveGLRows.gl=" & openGL & " and glAccount between 17000 and 17999 and glDocDate between '" & fromDate & "' and '" & toDate & "' group by GLAccount, Tafsil, Amount,EffectiveGLRows.ref1,EffectiveGLRows.ref2 having count(EffectiveGLRows.ref1) % 2 =1) as d"
  299. rs=conn.Execute(mySQL)
  300. accountsReceviableTej = cdbl(rs("amount"))
  301. cash=abs(getRem(11000,"group")) + abs(getRem(12000,"group"))
  302. 'accountsReceviable = abs(getRem(13000,"group")) + abs(getRem(15000,"group")) + abs(getRem(17000,"group")) + abs(getRem(18000,"group"))
  303. inventory = abs(getRem(14000,"group"))
  304. orders = abs(getRem(16000,"group"))
  305. plant = abs(getRem(26000,"group"))-abs(getRem(26100,"group"))
  306. properties = abs(getRem(27000,"group"))-abs(getRem(27100,"group"))
  307. otherAsset = abs(getRem(29000,"group"))
  308. totalAsset = cash + accountsReceviableTej + accountsReceviableNonTej + inventory + orders + plant + properties + otherAsset
  309. 'accountsPayable = abs(getRem(41000,"group"))+abs(getRem(42000,"group"))
  310. bankDebit = abs(getRem(46000,"group"))
  311. personsDebit = abs(getRem(44000,"group"))
  312. otherDebit = abs(getRem(43000,"group")) + abs(getRem(45000,"group")) + abs(getRem(47000,"group")) + abs(getRem(49000,"group")) + abs(getRem(71000,"group"))
  313. totalDebit = accountsPayableTej + accountsPayableNonTej + bankDebit + personsDebit + otherDebit
  314. equity = abs(getRem(50100,"group"))
  315. retainedEarnings = getRem(51000,"group") + getRem(52000,"group") + getRem(53000,"group") + getRem(54000,"group") + getRem(55000,"group") + getRem(59000,"group")
  316. periodEarnings = 0
  317. totalLiabilities = totalDebit + equity + retainedEarnings
  318. %>
  319. <table class="RepTable">
  320. <tr>
  321. <th>ترازنامه</th>
  322. <th><%=toDate%></th>
  323. </tr>
  324. <tr>
  325. <td>موجودي نقد</td>
  326. <td><%=Separate(cash)%></td>
  327. </tr>
  328. <tr>
  329. <td>حسابها و اسناد دريافتني تجاري</td>
  330. <td><%=Separate(accountsReceviableTej)%></td>
  331. </tr>
  332. <tr>
  333. <td>حسابها و اسناد دريافتني غير تجاري</td>
  334. <td><%=Separate(accountsReceviableNonTej)%></td>
  335. </tr>
  336. <tr>
  337. <td>موجودي انبار مواد اوليه و كالاي ساخته شده</td>
  338. <td><%=Separate(inventory)%></td>
  339. </tr>
  340. <tr>
  341. <td>سفارشات و پيش پرداختها</td>
  342. <td><%=Separate(orders)%></td>
  343. </tr>
  344. <tr>
  345. <td>داراييهاي ثابت‌ مشهود</td>
  346. <td><%=Separate(plant)%></td>
  347. </tr>
  348. <tr>
  349. <td>داراييهاي نا مشهود</td>
  350. <td><%=Separate(properties)%></td>
  351. </tr>
  352. <tr>
  353. <td>حصه سنوات آتي وام بانكي</td>
  354. <td><%=Separate(otherAsset)%></td>
  355. </tr>
  356. <tr>
  357. <td class="RepTDb">جمع‌ داراييها</td>
  358. <td><%=Separate(totalAsset)%></td>
  359. </tr>
  360. <tr>
  361. <td>حسابها و اسناد پرداختني تجاري</td>
  362. <td><%=Separate(accountsPayableTej)%></td>
  363. </tr>
  364. <tr>
  365. <td>حسابها و اسناد پرداختني غير تجاري</td>
  366. <td><%=Separate(accountsPayableNonTej)%></td>
  367. </tr>
  368. <tr>
  369. <td>تسهيلات‌ مالي بانكي</td>
  370. <td><%=Separate(bankDebit)%></td>
  371. </tr>
  372. <tr>
  373. <td>تسهيلات مالي اشخاص</td>
  374. <td><%=Separate(personsDebit)%></td>
  375. </tr>
  376. <tr>
  377. <td>ساير بدهيها (عناوين اصلي ذكر شود(</td>
  378. <td><%=Separate(otherDebit)%></td>
  379. </tr>
  380. <tr>
  381. <td class="RepTDb">جمع‌ بدهيها</td>
  382. <td><%=Separate(totalDebit)%></td>
  383. </tr>
  384. <tr>
  385. <td>سرمايه</td>
  386. <td><%=Separate(equity)%></td>
  387. </tr>
  388. <tr>
  389. <td>انباشته ( سود (زيان</td>
  390. <td><%=Separate(retainedEarnings)%></td>
  391. </tr>
  392. <tr>
  393. <td>سود و زيان دوره جاري</td>
  394. <td></td>
  395. </tr>
  396. <tr>
  397. <td class="RepTDb">جمع‌ بدهيها و حقوق‌ صاحبان سهام</td>
  398. <td><%=Separate(totalLiabilities)%></td>
  399. </tr>
  400. </table>
  401. <%
  402. elseif request("rep")="گردش وجوه نقد" then
  403. response.write "هنوز ساخته نشده"
  404. end if
  405. '-----------------------------------------------------------------------------------------------------
  406. '-----------------------------------------------------------------------------------------------------
  407. '-----------------------------------------------------------------------------------------------------
  408. elseif request("act")="cash" then
  409. myMonth = replace(request("month"),"-","/")
  410. dim fmonth(12)
  411. fmonth(0)="فروردين"
  412. fmonth(1)="ارديبهشت"
  413. fmonth(2)="خرداد"
  414. fmonth(3)="تير"
  415. fmonth(4)="مرداد"
  416. fmonth(5)="شهريور"
  417. fmonth(6)="مهر"
  418. fmonth(7)="آبان"
  419. fmonth(8)="آذر"
  420. fmonth(9)="دي"
  421. fmonth(10)="بهمن"
  422. fmonth(11)="اسفند"
  423. '----------------------------------------------------------------------------------------
  424. function echoTD(num,sys)
  425. result="<td style=""direction: ltr;text-align: right;color:"
  426. if num<0 then
  427. result = result & "red"
  428. else
  429. reslt = result & "black"
  430. end if
  431. result = result & ";"">"
  432. select case sys
  433. case "ar" ' ----------- AR Items
  434. result = result & "<a target=”_blank” href=""../AR/rep_dailySale.asp?input_date_start="
  435. if EffectiveDate<>"old" then
  436. result = result & Server.URLEncode(EffectiveDate&"01")&"&input_date_end=" & Server.URLEncode(EffectiveDate&"31")&"&fullyApplied=on"" >"
  437. else
  438. result = result & Server.URLEncode("0000/00/00")&"&input_date_end=" & Server.URLEncode("1388/01/01") &"&fullyApplied=on"">"
  439. end if
  440. case "ap" '------------ AP Items
  441. result = result & "<a target=”_blank” href=""../AP/report.asp?fromDate="
  442. if EffectiveDate<>"old" then
  443. if myMonth<>"" then
  444. result = result & Server.URLEncode(EffectiveDate)&"&toDate=" & Server.URLEncode(EffectiveDate)
  445. else
  446. result = result & Server.URLEncode(EffectiveDate&"01")&"&toDate=" & Server.URLEncode(EffectiveDate&"31")
  447. end if
  448. else
  449. result = result & Server.URLEncode(shamsiDate(DateAdd("m",-10,Date)))&"&toDate=" & Server.URLEncode(mid(shamsiDate(DateAdd("m",-6,Date)),1,8)) & "31"
  450. end if
  451. result = result & "&submit=" & Server.URLEncode("مشاهده") & """>"
  452. case "ar-full"
  453. result = result & "<a target=”_blank” href=""../AR/rep_dailySale.asp?input_date_start="
  454. if EffectiveDate<>"old" then
  455. result = result & Server.URLEncode(EffectiveDate&"01")&"&input_date_end=" & Server.URLEncode(EffectiveDate&"31")&"&fullyApplied="" >"
  456. else
  457. result = result & Server.URLEncode("0000/00/00")&"&input_date_end=" & Server.URLEncode("1388/01/01") &"&fullyApplied="">"
  458. end if
  459. case "ap-full"
  460. result = result & "<a target=”_blank” href=""../AP/report.asp?fromDate="
  461. if EffectiveDate<>"old" then
  462. if myMonth<>"" then
  463. result = result & Server.URLEncode(EffectiveDate)&"&toDate=" & Server.URLEncode(EffectiveDate)
  464. else
  465. result = result & Server.URLEncode(EffectiveDate&"01")&"&toDate=" & Server.URLEncode(EffectiveDate&"31")
  466. end if
  467. else
  468. result = result & Server.URLEncode(shamsiDate(DateAdd("m",-10,Date)))&"&toDate=" & Server.URLEncode(mid(shamsiDate(DateAdd("m",-6,Date)),1,8)) & "31"
  469. end if
  470. result = result & "&submit=" & Server.URLEncode("مشاهده") & """>"
  471. case "cash"
  472. if myMonth<>"" then
  473. result = result & "<a target=”_blank” href=""AccountInfo.asp?act=account&id=11000&fromDate=" & Server.URLEncode(ref2) & "&DateTo=" & Server.URLEncode(ref2) & """>"
  474. else
  475. result = result & "<a target=”_blank” href=""AccountInfo.asp?act=account&id=11000&fromDate=" & Server.URLEncode(ref2&"01") & "&DateTo=" & Server.URLEncode(ref2&"31") & """>"
  476. end if
  477. case "bnk"
  478. if myMonth<>"" then
  479. result = result & "<a target=”_blank” href=""AccountInfo.asp?act=account&id=12000&fromDate=" & Server.URLEncode(ref2) & "&DateTo=" & Server.URLEncode(ref2) & """>"
  480. else
  481. result = result & "<a target=”_blank” href=""AccountInfo.asp?act=account&id=12000&fromDate=" & Server.URLEncode(ref2&"01") & "&DateTo=" & Server.URLEncode(ref2&"31") & """>"
  482. end if
  483. case else
  484. if mid(sys,1,4)="bank" then
  485. if myMonth<>"" then
  486. result = result & "<a target=”_blank” href=""../bank/CheqBook.asp?act=showBook&GLAccount=" & mid(sys,5,5) & "&FromDate="
  487. result = result & Server.URLEncode(Ref2)&"&ToDate=" & Server.URLEncode(Ref2)&"&ShowRemained=&displayMode=0"">"
  488. else
  489. result = result & "<a target=”_blank” href=""../bank/CheqBook.asp?act=showBook&GLAccount=" & mid(sys,5,5) & "&FromDate="
  490. result = result & Server.URLEncode(Ref2&"01")&"&ToDate=" & Server.URLEncode(Ref2&"31")&"&ShowRemained=&displayMode=0"">"
  491. end if
  492. end if
  493. end select
  494. result = result & Separate(num)
  495. if sys="ar" or sys="ar-full" or sys="ap" or sys="ap-full" or sys="cash" or sys="bnk" then result = result & "</a>"
  496. result = result & "</td>"
  497. echoTD = result
  498. end function
  499. today = shamsiToday()
  500. if myMonth<>"" then
  501. mySQL="select * from (select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17003 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17003 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17004 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17004 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17016 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17016 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42001 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42001 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42002 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42002 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42004 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42004 group by Ref2,glAccount union select sum(amount) as amount,ref2,glAccount from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42011 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42011 group by Ref2,glAccount union select sum(Amount) as Amount, GLDocDate collate SQL_Latin1_General_CP1_CI_AS as ref2,11000 as GLAccount from EffectiveGLRows where gl=" & OpenGL & " and SUBSTRING(GLDocDate,1,8)='" & myMonth & "' and isCredit=0 and glAccount in (11004,11005,11006,11007) group by GLDocDate union select sum(2*(.5-cast(isCredit as int))*amount) as amount, d.ref2 collate SQL_Latin1_General_CP1_CI_AS as ref2,12000 as GLAccount from EffectiveGLRows inner join (select GLDocDate as ref2 from EffectiveGLRows where GL=" & OpenGL & " and glAccount between 12000 and 12999 and SUBSTRING(GLDocDate,1,8)='" & myMonth & "' group by GLDocDate) d on GLDocDate<=d.ref2 where GL=" & OpenGL & " and glAccount between 12000 and 12999 group by d.ref2 union select isnull(d17.amount,0)-isnull(d42.amount,0) as Amount,isnull(d42.ref2,d17.ref2) as ref2, 1742 as glAccount from (select sum(amount) as amount,ref2 from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount in (42001,42002,42004,42011) and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' and Ref2>'" & today & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42 group by Ref2)as d42 full outer join (select sum(amount) as amount,ref2 from (select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount in (17003,17004,17016) and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' and Ref2>'" & today & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17 group by Ref2) as d17 on d42.ref2=d17.ref2) dev order by Ref2,GLAccount"
  502. '"select * from (select Amount,Ref2,ref1,GLAccount,max(description) as description from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17003 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) union select Amount,Ref2,ref1,GLAccount,max(description) as description from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17004 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) union select Amount,Ref2,ref1,GLAccount,max(description) as description from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42001 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) union select Amount,Ref2,ref1,GLAccount,max(description) as description from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42004 and Ref1<>'' and SUBSTRING(Ref2,1,8)='" & myMonth & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) dev order by Ref2,GLAccount"
  503. else
  504. mySQL="select * from (select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17003 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17003 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17004 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17004 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=17016 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv17016 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42001 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42001 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42002 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42002 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42004 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42004 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2,GLAccount from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount=42011 and Ref1<>'' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42011 group by SUBSTRING(Ref2,1,8),GLAccount union select sum(Amount) as Amount, SUBSTRING(GLDocDate,1,8) collate SQL_Latin1_General_CP1_CI_AS as ref2,11000 as GLAccount from EffectiveGLRows where gl=" & OpenGL & " and isCredit=0 and glAccount in (11004,11005,11006,11007) group by SUBSTRING(GLDocDate,1,8) union select sum(2*(.5-cast(isCredit as int))*amount) as amount, d.ref2,12000 as GLAccount from EffectiveGLRows inner join (select SUBSTRING(GLDocDate,1,8) collate SQL_Latin1_General_CP1_CI_AS as ref2 from EffectiveGLRows where GL=" & OpenGL & " and glAccount between 12000 and 12999 group by SUBSTRING(GLDocDate,1,8)) d on GLDocDate<d.ref2+'32' where GL=" & OpenGL & " and glAccount between 12000 and 12999 group by d.ref2 union select isnull(d17.amount,0)-isnull(d42.amount,0) as Amount,isnull(d42.ref2,d17.ref2) as ref2, 1742 as glAccount from (select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2 from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount in (42001,42002,42004,42011) and Ref1<>'' and Ref2>'" & today & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42011 group by SUBSTRING(Ref2,1,8)) as d42 full outer join (select sum(Amount) as Amount, SUBSTRING(Ref2,1,8) as Ref2 from ( select Amount,Ref2,GLAccount from EffectiveGLRows where GL=" & OpenGL & " and GLAccount in (17003,17004,17016) and Ref1<>'' and Ref2>'" & today & "' group by GLAccount, Tafsil, Amount, Ref1, Ref2, GL HAVING (COUNT(Ref1) % 2 = 1) ) drv42011 group by SUBSTRING(Ref2,1,8)) as d17 on d42.ref2=d17.ref2) drv order by Ref2,GLAccount"
  505. end if
  506. 'response.write mySQL
  507. set rs=Conn.Execute(mySQL)
  508. amount12000=0
  509. amount1742=0
  510. 'Conn.Close
  511. 'response.redirect "?errMsg=" & Server.URLEncode("خطاي عجيب! چيزي پيدا نشد")
  512. sub setValue()
  513. select case rs("GLAccount")
  514. case "17003":
  515. amount17003 = CDbl(rs("Amount"))
  516. case "17004":
  517. amount17004 = CDbl(rs("Amount"))
  518. case "17016":
  519. amount17016 = CDbl(rs("Amount"))
  520. case "42001":
  521. amount42001 = CDbl(rs("Amount"))
  522. case "42002":
  523. amount42002 = CDbl(rs("Amount"))
  524. case "42004":
  525. amount42004 = CDbl(rs("Amount"))
  526. case "42011":
  527. amount42011 = CDbl(rs("Amount"))
  528. case "11000":
  529. amount11000 = CDbl(rs("Amount"))
  530. case "12000":
  531. amount12000 = CDbl(rs("Amount"))
  532. case "1742":
  533. amount1742 = amount1742 + CDbl(rs("Amount"))
  534. end select
  535. rs.MoveNext
  536. if not rs.eof then
  537. if ref2=rs("Ref2") then
  538. ref2=rs("Ref2")
  539. call setValue()
  540. end if
  541. end if
  542. end sub
  543. If not rs.EOF then
  544. %>
  545. <table>
  546. <tr class="RepTableHeader">
  547. <td rowspan="2" colspan="2">&nbsp;</td>
  548. <td colspan="3">در جريان وصول</td>
  549. <td rowspan="2">جمع در جريان</td>
  550. <td colspan="4">اسناد پرداختني</td>
  551. <td rowspan="2">جمع اسناد پرداختني</td>
  552. <td rowspan="2">تراز چك</td>
  553. <td rowspan="2">صندوق</td>
  554. <td rowspan="2">بانك روز</td>
  555. <td rowspan="2">بانك پيش‌بيني</td>
  556. </tr>
  557. <tr class="RepTableHeader">
  558. <td>17003</td>
  559. <td>17004</td>
  560. <td>17016</td>
  561. <td>42001</td>
  562. <td>42002</td>
  563. <td>42004</td>
  564. <td>42011</td>
  565. </tr>
  566. <%
  567. rowColor="RepTR0"
  568. do while not rs.eof
  569. ref2=rs("Ref2")
  570. if rowColor="RepTR0" then
  571. rowColor="RepTR1"
  572. else
  573. rowColor="RepTR0"
  574. end if
  575. %>
  576. <tr class="<%=rowColor%>">
  577. <%
  578. yyyy=mid(ref2,1,4)
  579. if myMonth<>"" then
  580. mm=ref2
  581. else
  582. mm=fmonth(cint(mid(ref2,6,2))-1)
  583. end if
  584. amount17003=0
  585. amount17004=0
  586. amount17016=0
  587. amount42001=0
  588. amount42002=0
  589. amount42004=0
  590. amount42011=0
  591. amount11000=0
  592. call setValue()
  593. %>
  594. <td title="<%=ref2%>"><%=yyyy%></td>
  595. <%if myMonth<>"" then%>
  596. <td><%=mm%></td>
  597. <%else%>
  598. <td title='براي مشاهده جزئيات كليك كنيد'><a href="otherReports.asp?act=cash&month=<%=replace(mid(ref2,1,8),"/","-")%>"><%=mm%></a></td>
  599. <%
  600. end if
  601. response.write echoTD(amount17003,"bank17003")
  602. response.write echoTD(amount17004,"bank17004")
  603. response.write echoTD(amount17016,"bank17016")
  604. response.write echoTD(amount17003+amount17004+amount17016,"")
  605. response.write echoTD(amount42001,"bank42001")
  606. response.write echoTD(amount42002,"bank42002")
  607. response.write echoTD(amount42004,"bank42004")
  608. response.write echoTD(amount42011,"bank42011")
  609. response.write echoTD(amount42001+amount42002+amount42004+amount42011,"")
  610. response.write echoTD(amount17003+amount17004+amount17016-(amount42001+amount42002+amount42004+amount42011),"")
  611. response.write echoTD(amount11000,"cash")
  612. response.write echoTD(amount12000+amount1742,"bnk")
  613. if amount1742<>0 then
  614. response.write echoTD(amount12000+amount1742,"")
  615. else
  616. response.write echoTD(amount12000+amount17003+amount17004+amount17016-(amount42001+amount42002+amount42004+amount42011),"")
  617. end if
  618. %>
  619. </tr>
  620. <%
  621. loop
  622. %>
  623. </table>
  624. <%
  625. end if
  626. if myMonth<>"" then
  627. mySQL="select * from (select RemainedAmount, EffectiveDate, 'ar' as sys from ARItems where FullyApplied=0 and Voided=0 and ARItems.Type=1 and SUBSTRING(EffectiveDate,1,8)='" & myMonth & "' UNION select RemainedAmount, Vouchers.EffectiveDate, 'ap' as sys from APItems inner join Vouchers on apItems.link=Vouchers.id where FullyApplied=0 and apItems.Voided=0 and APItems.Type=6 and SUBSTRING(Vouchers.EffectiveDate,1,8)='" & myMonth & "' UNION select amountOriginal as RemainedAmount, EffectiveDate, 'ar-full' as sys from ARItems where Voided=0 and ARItems.Type=1 and SUBSTRING(EffectiveDate,1,8)='" & myMonth & "' UNION select amountOriginal as RemainedAmount, EffectiveDate, 'ap-full' as sys from APItems where Voided=0 and APItems.Type=6 and SUBSTRING(EffectiveDate,1,8)='" & myMonth & "' ) dev order by EffectiveDate,sys"
  628. else
  629. mySQL="select * from (select sum(RemainedAmount) as RemainedAmount, SUBSTRING(EffectiveDate,1,8) as EffectiveDate, 'ar' as sys from ARItems where FullyApplied=0 and Voided=0 and ARItems.Type=1 and EffectiveDate>='1388/01/01' group by SUBSTRING(EffectiveDate,1,8) UNION select sum(RemainedAmount) as RemainedAmount, SUBSTRING(Vouchers.EffectiveDate,1,8) as EffectiveDate, 'ap' as sys from APItems inner join Vouchers on apItems.link=Vouchers.id where FullyApplied=0 and apItems.Voided=0 and APItems.Type=6 and Vouchers.EffectiveDate>='1388/01/01' group by SUBSTRING(Vouchers.EffectiveDate,1,8) UNION select sum(amountOriginal) as RemainedAmount, SUBSTRING(EffectiveDate,1,8) as EffectiveDate, 'ar-full' as sys from ARItems where Voided=0 and ARItems.Type=1 and EffectiveDate>='1388/01/01' group by SUBSTRING(EffectiveDate,1,8) UNION select sum(amountOriginal) as RemainedAmount, SUBSTRING(EffectiveDate,1,8) as EffectiveDate, 'ap-full' as sys from APItems where Voided=0 and APItems.Type=6 and EffectiveDate>='1388/01/01' group by SUBSTRING(EffectiveDate,1,8)) dev order by EffectiveDate,sys"
  630. end if
  631. mySQLold="select sum(RemainedAmount) as RemainedAmount,'old' as EffectiveDate,'ar' as sys from ARItems where FullyApplied=0 and Voided=0 and ARItems.Type=1 and EffectiveDate<'1388/01/01' UNION select sum(RemainedAmount) as RemainedAmount, 'old' as EffectiveDate,'ap' as sys from APItems inner join Vouchers on apItems.link=Vouchers.id where FullyApplied=0 and apItems.Voided=0 and APItems.Type=6 and Vouchers.EffectiveDate<'1388/01/01' UNION select sum(amountOriginal) as RemainedAmount,'old' as EffectiveDate,'ar-full' as sys from ARItems where Voided=0 and ARItems.Type=1 and EffectiveDate<'1388/01/01' UNION select sum(amountOriginal) as RemainedAmount, 'old' as EffectiveDate,'ap-full' as sys from APItems where Voided=0 and APItems.Type=6 and EffectiveDate<'1388/01/01'"
  632. set rs=Conn.Execute(mySQL)
  633. set rsOLD=conn.Execute(mySQLold)
  634. 'Conn.Close
  635. 'response.redirect "?errMsg=" & Server.URLEncode("خطاي عجيب! چيزي پيدا نشد")
  636. sub setAXValue(RSs)
  637. select case RSs("sys")
  638. case "ap"
  639. apRemainedAmount = CDbl(RSs("RemainedAmount"))
  640. case "ar"
  641. arRemainedAmount = CDbl(RSs("RemainedAmount"))
  642. case "ap-full"
  643. apTotal = CDbl(RSs("RemainedAmount"))
  644. case "ar-full"
  645. arTotal = CDbl(RSs("RemainedAmount"))
  646. end select
  647. if not RSs.EOF then RSs.MoveNext
  648. if not RSs.eof then
  649. if EffectiveDate = RSs("EffectiveDate") then
  650. ref2 = RSs("EffectiveDate")
  651. call setAXValue(RSs)
  652. end if
  653. end if
  654. end sub
  655. If not rs.EOF and not rsOLD.EOF then
  656. %>
  657. <table>
  658. <tr class="RepTableHeader">
  659. <td colspan="2">&nbsp;</td>
  660. <td>مانده فروش</td>
  661. <td>كل فروش</td>
  662. <td>درصد مانده</td>
  663. <td>مانده خريد</td>
  664. <td>كل خريد</td>
  665. <td>درصد مانده</td>
  666. </tr>
  667. <%
  668. rowColor="RepTR0"
  669. if myMonth="" then
  670. do while not rsOLD.eof
  671. EffectiveDate=rsOLD("EffectiveDate")
  672. %>
  673. <tr class="<%=rowColor%>">
  674. <%
  675. arRemainedAmount=0
  676. apRemainedAmount=0
  677. arTotal=0
  678. apTotal=0
  679. call setAXValue(rsOLD)
  680. %>
  681. <td colspan="2">مانده قديمي</td>
  682. <%
  683. response.write echoTD(arRemainedAmount, "ar")
  684. response.write echoTD(arTotal, "ar-full")
  685. response.write echoTD(round(arRemainedAmount/arTotal*100), "")
  686. response.write echoTD(apRemainedAmount, "ap")
  687. response.write echoTD(apTotal, "ap-full")
  688. response.write echoTD(round(apRemainedAmount/apTotal*100), "")
  689. %>
  690. </tr>
  691. <%
  692. loop
  693. end if
  694. arRemainedAmountSum=0
  695. apRemainedAmountSum=0
  696. arTotalAmountSum=0
  697. apTotalAmountSum=0
  698. do while not rs.eof
  699. EffectiveDate=rs("EffectiveDate")
  700. if rowColor="RepTR0" then
  701. rowColor="RepTR1"
  702. else
  703. rowColor="RepTR0"
  704. end if
  705. %>
  706. <tr class="<%=rowColor%>">
  707. <%
  708. yyyy=mid(EffectiveDate,1,4)
  709. if myMonth<>"" then
  710. mm= mid(EffectiveDate,6,5)
  711. else
  712. mm=fmonth(cint(mid(EffectiveDate,6,2))-1)
  713. end if
  714. arRemainedAmount=0
  715. apRemainedAmount=0
  716. call setAXValue(rs)
  717. arRemainedAmountSum=arRemainedAmountSum+arRemainedAmount
  718. apRemainedAmountSum=apRemainedAmountSum+apRemainedAmount
  719. arTotalAmountSum=arTotalAmountSum+arTotal
  720. apTotalAmountSum=apTotalAmountSum+apTotal
  721. %>
  722. <td title="<%=EffectiveDate%>"><%=yyyy%></td>
  723. <%if myMonth<>"" then%>
  724. <td><%=mm%></td>
  725. <%else%>
  726. <td title='براي مشاهده جزئيات كليك كنيد'><a href="otherReports.asp?act=cash&month=<%=replace(mid(effectiveDate,1,8),"/","-")%>"><%=mm%></a></td>
  727. <%
  728. end if
  729. response.write echoTD(arRemainedAmount, "ar")
  730. response.write echoTD(arTotal, "ar-full")
  731. if arTotal>0 then
  732. response.write echoTD(round(arRemainedAmount/arTotal*100), "")
  733. else
  734. response.write echoTD(0,"")
  735. end if
  736. response.write echoTD(apRemainedAmount, "ap")
  737. response.write echoTD(apTotal, "ap-full")
  738. if apTotal>0 then
  739. response.write echoTD(round(apRemainedAmount/apTotal*100), "")
  740. else
  741. response.write echoTD(0,"")
  742. end if
  743. %>
  744. </tr>
  745. <%
  746. loop
  747. if rowColor="RepTR0" then
  748. rowColor="RepTR1"
  749. else
  750. rowColor="RepTR0"
  751. end if
  752. %>
  753. <tr class="<%=rowColor%>">
  754. <td colspan="2">جمع بدون ماندهها</td>
  755. <td><%=Separate(arRemainedAmountSum)%></td>
  756. <td><%=Separate(arTotalAmountSum)%></td>
  757. <td><%if arTotalAmountSum>0 then response.write Round(arRemainedAmountSum/arTotalAmountSum*100)%></td>
  758. <td><%=Separate(apRemainedAmountSum)%></td>
  759. <td><%=Separate(apTotalAmountSum)%></td>
  760. <td><%if apTotalAmountSum>0 then response.write Round(apRemainedAmountSum/apTotalAmountSum*100)%></td>
  761. </tr>
  762. </table>
  763. <%
  764. end if
  765. '-----------------------------------------------------------------------------------------------------
  766. '-----------------------------------------------------------------------------------------------------
  767. '-----------------------------------------------------------------------------------------------------
  768. elseif request("act")="MoeenRep" then
  769. ON ERROR RESUME NEXT
  770. GLAccount = clng(request("GLAccount"))
  771. FromDate = sqlSafe(request("FromDate"))
  772. ToDate = sqlSafe(request("ToDate"))
  773. if FromDate = "" then FromDate = fiscalYear & "/01/01"
  774. if ToDate = "" then ToDate = shamsiToday()
  775. FromTafsil = clng(request("FromTafsil"))
  776. ToTafsil = clng(request("ToTafsil"))
  777. if Err.Number<>0 then
  778. Err.clear
  779. conn.close
  780. response.redirect "?errMsg=" & Server.URLEncode("خطا!")
  781. end if
  782. ON ERROR GOTO 0
  783. '-----------------------------------------------------------------------------------------------------
  784. if request("action")=" چاپ " then
  785. '---- It's Print ( Crystal Reports )
  786. %>
  787. <BR>
  788. <BR>
  789. <CENTER>
  790. <% ReportLogRow = PrepareReport ("MoeenRep01.rpt", "GLAccountGLFromDateToDateFromTafsilToTafsil", GLAccount & "" & OpenGL & "" & FromDate & "" & ToDate & "" & FromTafsil& "" & ToTafsil, "/beta/dialog_printManager.asp?act=Fin") %>
  791. <INPUT TYPE="button" value=" چاپ " Class="GenButton" style="border:1 solid blue;" onclick="printThisReport(this,<%=ReportLogRow%>);">
  792. </CENTER>
  793. <BR><iframe name=f1 id=f1 src="/CRReports/?Id=<%=ReportLogRow%>" align=center style="width:750; height:410; border-style: none" border=0 FRAMEBORDER=0 scrollbars=no ></iframe>
  794. <%
  795. else
  796. '---- It's Not Print
  797. Ord=request("Ord")
  798. select case Ord
  799. case "1":
  800. order="Tafsil"
  801. case "-1":
  802. order="Tafsil DESC"
  803. case "2":
  804. order="AccountTitle"
  805. case "-2":
  806. order="AccountTitle DESC"
  807. case "3":
  808. order="totalDebit"
  809. case "-3":
  810. order="totalDebit DESC"
  811. case "4":
  812. order="totalCredit"
  813. case "-4":
  814. order="totalCredit DESC"
  815. case "5","-6":
  816. order="(SUM(GLRows.IsCredit * GLRows.Amount) - SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount))) DESC"
  817. case "6","-5":
  818. order="SUM(GLRows.IsCredit * GLRows.Amount) - SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount))"
  819. case else:
  820. order="Tafsil"
  821. Ord=1
  822. end select
  823. mySQL="SELECT GLAccounts.ID, GLAccounts.Name AS AccountName, GLAccountGroups.ID AS GroupID, GLAccountGroups.Name AS GroupName, GLAccountSuperGroups.ID AS SuperGroupID, GLAccountSuperGroups.Name AS SuperGroupName, GLs.ID AS GLID, GLs.Name AS GLname FROM GLs INNER JOIN GLAccountSuperGroups ON GLs.ID = GLAccountSuperGroups.GL INNER JOIN GLAccountGroups ON GLs.ID = GLAccountGroups.GL AND GLAccountSuperGroups.ID = GLAccountGroups.GLSuperGroup INNER JOIN GLAccounts ON GLs.ID = GLAccounts.GL AND GLAccountGroups.ID = GLAccounts.GLGroup WHERE (GLs.ID = "& OpenGL & ") AND (GLAccounts.ID = "& GLAccount & ")"
  824. set rsGLNames=Conn.Execute (mySQL)
  825. If rsGLNames.EOF then
  826. Conn.Close
  827. response.redirect "?errMsg=" & Server.URLEncode("حساب مورد نظر [" & GLAccount & "] در سال مالي جاري (" & OpenGL & ") وجود ندارد ")
  828. End If
  829. AccountInfoParams = "&DateFrom=" & FromDate & "&DateTo=" & ToDate
  830. %>
  831. <SCRIPT LANGUAGE="JavaScript">
  832. <!--
  833. function showAcc(acc){
  834. window.open('tafsili.asp?accountID='+acc+'&FromDate=<%=FromDate%>&ToDate=<%=ToDate%>&moeenFrom=0&moeenTo=99999&act=Show');
  835. }
  836. //-->
  837. </SCRIPT>
  838. <TABLE dir=rtl align=center width=640 cellspacing=2 cellpadding=2 style="border:2 solid #330066;">
  839. <TR bgcolor="#CCCCEE" height="30">
  840. <TD colspan=7>
  841. <A HREF="AccountInfo.asp?OpenGL=<%=rsGLNames("GLID")&AccountInfoParams%>" Target="_blank"><%=rsGLNames("GLname")%></A>
  842. > <A HREF="AccountInfo.asp?act=groups&id=<%=rsGLNames("SuperGroupID")&AccountInfoParams%>" Target="_blank"><%=rsGLNames("SuperGroupName")%></A>
  843. > <A HREF="AccountInfo.asp?act=account&id=<%=rsGLNames("GroupID")&AccountInfoParams%>" Target="_blank"><%=rsGLNames("GroupName")%></A>
  844. > <%=rsGLNames("AccountName")%>
  845. [<%=GLAccount%>]
  846. </TD>
  847. </TR>
  848. <%
  849. rsGLNames.close
  850. Set rsGLNames = Nothing
  851. 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"
  852. set rs=Conn.Execute (mySQL)
  853. %>
  854. <TR bgcolor="#CCCCEE" >
  855. <TD colspan=2 rowspan=2>
  856. از تاريخ <B><%=replace(FromDate,"/",".")%></B> تا تاريخ <B><%=replace(ToDate,"/",".")%></B><br>
  857. از تفصيلي <B><%=FromTafsil%></B> تا تفصيلي <B><%=ToTafsil%></B>
  858. </TD>
  859. <TD width=70 >گردش بدهكار </TD>
  860. <TD width=70 >گردش بستانكار </TD>
  861. <TD width=70 >مانده بدهكار </TD>
  862. <TD width=70 >مانده بستانكار </TD>
  863. </TR>
  864. <TR bgcolor="#CCCCEE" >
  865. <TD width=70 ><%=Separate(rs("SumDeb"))%></TD>
  866. <TD width=70 ><%=Separate(rs("SumCred"))%></TD>
  867. <TD width=70 ><%=Separate(rs("SumFlowDeb"))%></TD>
  868. <TD width=70 ><%=Separate(rs("SumFlowCred"))%></TD>
  869. </TR>
  870. <TR bgcolor="black" height="2">
  871. <TD colspan="6" style="padding:0;"></TD>
  872. </TR>
  873. <%
  874. rs.close
  875. if ord<0 then
  876. style="background-color: #33CC99;"
  877. arrow="<br><span style='font-family:webdings'>6 6 6</span>"
  878. else
  879. style="background-color: #33CC99;"
  880. arrow="<br><span style='font-family:webdings'>5 5 5</span>"
  881. end if
  882. %>
  883. <TR bgcolor="eeeeee" style="cursor:hand;" title="ترتيب نمايش">
  884. <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>
  885. <TD width='*' onclick='go2Page(1,2);' style="<%if abs(ord)=2 then response.write style%>">عنوان حساب <%if abs(ord)=2 then response.write arrow%></TD>
  886. <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>
  887. <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>
  888. <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>
  889. <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>
  890. </TR>
  891. <TR bgcolor="eeeeee" >
  892. <TD colspan=6 height=2 bgcolor=0></TD>
  893. </TR>
  894. <%
  895. SumCredit=0
  896. SumDebit=0
  897. SumCreditRemained=0
  898. SumDebitRemained=0
  899. tmpCounter=0
  900. 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
  901. Set rs=Server.CreateObject("ADODB.Recordset")'Conn.Execute(mySQL)
  902. PageSize = 50
  903. rs.PageSize = PageSize
  904. rs.CursorLocation=3 'in ADOVBS_INC adUseClient=3
  905. rs.Open mySQL ,Conn,3
  906. TotalPages = rs.PageCount
  907. CurrentPage=1
  908. if isnumeric(Request.QueryString("p")) then
  909. pp=clng(Request.QueryString("p"))
  910. if pp <= TotalPages AND pp > 0 then
  911. CurrentPage = pp
  912. end if
  913. end if
  914. if not rs.eof then
  915. rs.AbsolutePage=CurrentPage
  916. end if
  917. if rs.eof then
  918. %> <tr>
  919. <td bgcolor="#BBBBBB" height="30" colspan="7" align=center><b>هيچ .</b></td>
  920. </tr>
  921. <% else
  922. Do While NOT rs.eof AND (rs.AbsolutePage = CurrentPage)
  923. tmpCounter = tmpCounter + 1
  924. if tmpCounter mod 2 = 1 then
  925. tmpColor="#FFFFFF"
  926. tmpColor2="#FFFFBB"
  927. Else
  928. tmpColor="#DDDDDD"
  929. tmpColor2="#EEEEBB"
  930. End if
  931. totalDebit = cdbl(rs("totalDebit"))
  932. totalCredit = cdbl(rs("totalCredit"))
  933. if totalCredit > totalDebit then
  934. creditRemained = totalCredit - totalDebit
  935. debitRemained = 0
  936. else
  937. creditRemained = 0
  938. debitRemained = totalDebit - totalCredit
  939. end if
  940. SumCredit = SumCredit + totalCredit
  941. SumDebit = SumDebit + totalDebit
  942. SumCreditRemained = SumCreditRemained + creditRemained
  943. SumDebitRemained = SumDebitRemained + debitRemained
  944. %>
  945. <TR bgcolor="<%=tmpColor%>" >
  946. <TD dir=ltr align=right><A HREF="javascript:showAcc(<%=rs("Tafsil")%>);"><%=rs("Tafsil")%></A></TD>
  947. <TD><%=rs("AccountTitle")%></TD>
  948. <TD dir=ltr align=right><span dir=ltr><%=Separate(totalDebit)%></span></TD>
  949. <TD dir=ltr align=right><span dir=ltr><%=Separate(totalCredit)%></span></TD>
  950. <TD dir=ltr align=right><span dir=ltr><%=Separate(debitRemained)%></span></TD>
  951. <TD dir=ltr align=right><span dir=ltr><%=Separate(creditRemained)%></span></TD>
  952. </TR>
  953. <%
  954. rs.moveNext
  955. Loop
  956. if TotalPages > 1 then
  957. pageCols=20
  958. %>
  959. <TR bgcolor="eeeeee" >
  960. <TD colspan=6 height=2 bgcolor=0></TD>
  961. </TR>
  962. <TR class="RepTableTitle">
  963. <TD bgcolor="#CCCCEE" height="30" colspan="6">
  964. <table width=100% cellspacing=0 style="cursor:hand;color:gray;">
  965. <tr>
  966. <td style="height:25;border-bottom:1 solid black;" colspan=<%=pagecols%>>
  967. <b>صفحه <%=CurrentPage%> از <%=TotalPages%></b>
  968. &nbsp;&nbsp;<a href="javascript:go2Page(<%=CurrentPage+1%>,0);">صفحه بعد &gt;</a>
  969. </td>
  970. </tr>
  971. <tr>
  972. <% for i=1 to TotalPages
  973. if i = CurrentPage then
  974. %> <td style="color:black;"><b>[<%=i%>]</b></td>
  975. <% else
  976. %> <td onclick="go2Page(<%=i%>,0);"><%=i%></td>
  977. <% end if
  978. if i mod pageCols = 0 then response.write "</tr><tr>"
  979. next
  980. %> </tr>
  981. </table>
  982. </TD>
  983. </TR>
  984. <% end if
  985. %>
  986. </TABLE><br>
  987. <SCRIPT LANGUAGE="JavaScript">
  988. <!--
  989. function go2Page(p,ord) {
  990. if(ord==0){
  991. ord=<%=Ord%>;
  992. }
  993. else if(ord==<%=Ord%>){
  994. ord= 0-ord;
  995. }
  996. 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(' ')
  997. window.location=str;
  998. }
  999. //-->
  1000. </SCRIPT>
  1001. <%
  1002. end if
  1003. end if
  1004. '-----------------------------------------------------------------------------------------------------
  1005. end if
  1006. %>
  1007. <!--#include file="tah.asp" -->