/// <summary> /// 获取客户账龄视图不同客户 /// </summary> /// <param name="CompanyCD">公司编码</param> /// <returns></returns> public static DataTable GetDistinctCustFromView(string CurryTypeID) { try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 return(FinanceReportDBHelper.GetDistinctCustFromView(CompanyCD, CurryTypeID)); } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取账簿中不同的客户--应收帐款 /// </summary> /// <param name="CurryType">币种</param> /// <param name="SubjectsCD">会计科目</param> /// <param name="CompanyCD">公司编码</param> /// <returns></returns> public static DataTable GetDistinctCusFromAcountBook(string CurryType, string SubjectsCD) { try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 return(FinanceReportDBHelper.GetDistinctCusFromAcountBook(CurryType, SubjectsCD, CompanyCD)); } catch (Exception ex) { throw ex; } }
/// <summary> /// 客户账龄分析表读取视图 /// </summary> /// <param name="CustID">客户主键</param> /// <param name="CurryTypeID">币种</param> /// <returns></returns> public static DataTable GetCusSalesAging(string CustID, string CurryTypeID) { try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 return(FinanceReportDBHelper.GetCusSalesAging(CustID, CurryTypeID, CompanyCD)); } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取某会计期间某科目某币种对应的详细信息 /// </summary> /// <param name="StartDate">开始日期</param> /// <param name="EndDate">结束日期</param> /// <param name="CurryType">币种</param> /// <param name="SubjectsCD">会计科目</param> /// <param name="CompanyCD">公司编码</param> /// <param name="SubjectsDetails">辅助核算主键</param> /// <param name="FormTBName">来源表</param> /// <param name="FileName">来源表字段名</param> /// <returns></returns> public static DataTable GetAccountBookInfo(string StartDate, string EndDate, string CurryType, string SubjectsCD, string SubjectsDetails, string FormTBName, string FileName) { try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 return(FinanceReportDBHelper.GetAccountBookInfo(StartDate, EndDate, CurryType, SubjectsCD, CompanyCD, SubjectsDetails, FormTBName, FileName)); } catch (Exception ex) { throw ex; } }
/// <summary> /// 销货成本明细表 /// </summary> /// <param name="Year">年</param> /// <param name="Month">月</param> /// <returns></returns> public static DataTable GetSellMonthIncomePrint(string Year, string Month, string CompanyCD, string ord) { return(FinanceReportDBHelper.GetSellMonthIncomePrint(Year, Month, CompanyCD, ord)); }
/// <summary> /// 销货收入月报表 /// </summary> /// <param name="Year">年</param> /// <param name="Month">月</param> /// <returns></returns> public static DataTable GetSellMonthIncome(string Year, string Month, string CompanyCD, int pageIndex, int pageCount, string ord, ref int TotalCount) { return(FinanceReportDBHelper.GetSellMonthIncome(Year, Month, CompanyCD, pageIndex, pageCount, ord, ref TotalCount)); }
/// <summary> /// 应收账款月报表 /// </summary> /// <param name="AccountDate">会计期间</param> /// <param name="CurryType">币种</param> /// <param name="SubjectsCD">会计科目</param> /// <param name="SubjectsDetails">辅助核算主键</param> /// <param name="FormTBName">来源表</param> /// <param name="FileName">来源表字段</param> /// <returns></returns> public static DataTable GetReceivableMothRepter(string AccountDate, string CurryType, string SubjectsCD, string SubjectsDetails, string FormTBName, string FileName) { try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 #region 对输入的资料日期进行处理开始 string startDate = string.Empty; string endDate = string.Empty; int MothString = Convert.ToInt32(AccountDate.Split('-')[1].ToString()); int yearString = Convert.ToInt32(AccountDate.Split('-')[0].ToString()); string daycount = string.Empty; if (MothString == 4 || MothString == 6 || MothString == 9 || MothString == 11) { daycount = "-30"; } else if (MothString == 2) { if (((yearString % 4 == 0 && yearString % 100 == 0)) || (yearString % 400 == 0)) { daycount = "-29"; } else { daycount = "-28"; } } else { daycount = "-31"; } startDate = AccountDate + "-01"; endDate = AccountDate + daycount; #endregion #region 若查询条件中带出具体客户,清空获取账簿中不同的客户记录,添加查询条件中的客户信息到SubjectsDetailsDT DataTable SubjectsDetailsDT = FinanceReportDBHelper.GetDistinctCusFromAcountBook(CurryType, SubjectsCD, CompanyCD);//获取账簿中不同的客户--应收帐款 /* 若查询条件中带出具体客户,清空获取账簿中不同的客户记录,添加查询条件中的客户信息到SubjectsDetailsDT 开始*/ if (SubjectsDetails.Trim().Length > 0) { SubjectsDetailsDT.Clear(); DataRow row = SubjectsDetailsDT.NewRow(); row["SubjectsDetails"] = SubjectsDetails; row["FormTBName"] = FormTBName; row["FileName"] = FileName; SubjectsDetailsDT.Rows.Add(row); } /* 若查询条件中带出具体客户,清空获取账簿中不同的客户记录,添加查询条件中的客户信息到SubjectsDetailsDT 结束*/ #endregion #region 定义数据源Table DataTable dt = new DataTable(); dt.Columns.Add("VoucherDate"); //日期 dt.Columns.Add("CusOrPro"); //客户 dt.Columns.Add("Abstract"); //摘要 dt.Columns.Add("BeginAmount"); //期初金额 dt.Columns.Add("ThisDebit"); //本期借方金额 dt.Columns.Add("ThisCredit"); //本期贷方金额 dt.Columns.Add("Direction"); //余额方向 dt.Columns.Add("EndAmount"); //期末余额 dt.Columns.Add("SourceCode"); //源单编码 dt.Columns.Add("Type"); //源单类别 dt.Columns.Add("SourceDate"); //源单日期 dt.Columns.Add("TotalAmount"); //总金额 dt.Columns.Add("BlendingAmount"); //回款金额 dt.Columns.Add("Scale"); //回款比例 dt.Columns.Add("ByOrder"); //排序 #endregion foreach (DataRow dr in SubjectsDetailsDT.Rows) { DataTable AccountBookInfo = GetAccountBookInfo(startDate, endDate, CurryType, SubjectsCD, dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString()); decimal BeginAmount = AcountBookDBHelper.GetSubjectsBeginDetailAmount(SubjectsCD, CurryType, CompanyCD, dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString()); foreach (DataRow drr in AccountBookInfo.Rows) { if (drr["FromTbale"].ToString().Trim().Length > 0 && (drr["FromTbale"].ToString().Equals("officedba.IncomeBill") || drr["FromTbale"].ToString().Equals("officedba.PayBill")))//根据凭证主表的值对应获取勾兑明细 { BlendingDetailsBus bus = new BlendingDetailsBus(); //根据凭证主表的来源表及来源表主键__获取对应的勾兑明细信息 Start DataTable BlendingSourceDT = bus.GetBlendingSoureByTB(drr["FromTbale"].ToString(), drr["FromValue"].ToString()); //根据凭证主表的来源表及来源表主键__获取对应的勾兑明细信息 End if (BlendingSourceDT.Rows.Count > 1)//判断是否含有勾兑明细 { for (int i = 0; i < BlendingSourceDT.Rows.Count; i++) { DataRow row = dt.NewRow(); if (i == 0) { row["VoucherDate"] = drr["VoucherDate"].ToString(); //日期 row["CusOrPro"] = VoucherDBHelper.GetAssistantName(dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString()); //客户 row["Abstract"] = drr["Abstract"].ToString(); //摘要 row["BeginAmount"] = BeginAmount.ToString("#,###0.#0"); //期初金额 if (CurryType.LastIndexOf(",") == -1) { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } else { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } decimal m = BeginAmount + Convert.ToDecimal(row["ThisDebit"].ToString()) - Convert.ToDecimal(row["ThisCredit"].ToString()); row["EndAmount"] = Math.Round(m, 2).ToString("#,###0.#0"); //期末余额 row["Direction"] = AcountBookDBHelper.DirectionSource(SubjectsCD, Convert.ToDecimal(row["EndAmount"].ToString())); //余额方向 row["SourceCode"] = BlendingSourceDT.Rows[i]["BillCD"].ToString(); row["Type"] = GetBillType(BlendingSourceDT.Rows[i]["BillingType"].ToString()); row["SourceDate"] = BlendingSourceDT.Rows[i]["CreateDate"].ToString(); row["TotalAmount"] = Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["TotalPrice"].ToString()), 2).ToString("#,###0.#0"); row["BlendingAmount"] = Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["BlendingAmount"].ToString()), 2).ToString("#,###0.#0"); row["Scale"] = Convert.ToString(Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["BlendingAmount"].ToString()) / Convert.ToDecimal(BlendingSourceDT.Rows[i]["TotalPrice"].ToString()) * 100, 2)) + "%"; row["ByOrder"] = "1"; } else { row["VoucherDate"] = ""; //日期 row["CusOrPro"] = ""; //客户 row["Abstract"] = ""; //摘要 row["BeginAmount"] = ""; //期初金额 row["ThisDebit"] = ""; //本期借方金额 row["ThisCredit"] = ""; //本期贷方金额 row["EndAmount"] = ""; //期末余额 row["Direction"] = ""; //余额方向 row["SourceCode"] = BlendingSourceDT.Rows[i]["BillCD"].ToString(); row["Type"] = GetBillType(BlendingSourceDT.Rows[i]["BillingType"].ToString()); row["SourceDate"] = BlendingSourceDT.Rows[i]["CreateDate"].ToString(); row["TotalAmount"] = Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["TotalPrice"].ToString()), 2).ToString("#,###0.#0"); row["BlendingAmount"] = Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["BlendingAmount"].ToString()), 2).ToString("#,###0.#0"); row["Scale"] = Convert.ToString(Math.Round(Convert.ToDecimal(BlendingSourceDT.Rows[i]["BlendingAmount"].ToString()) / Convert.ToDecimal(BlendingSourceDT.Rows[i]["TotalPrice"].ToString()) * 100, 2)) + "%"; row["ByOrder"] = "1"; } dt.Rows.Add(row); } } else { DataRow row = dt.NewRow(); row["VoucherDate"] = drr["VoucherDate"].ToString(); //日期 row["CusOrPro"] = VoucherDBHelper.GetAssistantName(dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString()); //客户 row["Abstract"] = drr["Abstract"].ToString(); //摘要 row["BeginAmount"] = BeginAmount.ToString("#,###0.#0"); //期初金额 if (CurryType.LastIndexOf(",") == -1) { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } else { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } decimal n = BeginAmount + Convert.ToDecimal(row["ThisDebit"].ToString()) - Convert.ToDecimal(row["ThisCredit"].ToString()); row["EndAmount"] = Math.Round(n, 2).ToString("#,###0.#0"); //期末余额 row["Direction"] = AcountBookDBHelper.DirectionSource(SubjectsCD, Convert.ToDecimal(row["EndAmount"].ToString())); //余额方向 row["SourceCode"] = ""; row["Type"] = ""; row["SourceDate"] = ""; row["TotalAmount"] = ""; row["BlendingAmount"] = ""; row["Scale"] = ""; row["ByOrder"] = "1"; dt.Rows.Add(row); } } else { DataRow row = dt.NewRow(); row["VoucherDate"] = drr["VoucherDate"].ToString(); //日期 row["CusOrPro"] = VoucherDBHelper.GetAssistantName(dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString()); //客户 row["Abstract"] = drr["Abstract"].ToString(); //摘要 row["BeginAmount"] = BeginAmount.ToString("#,###0.#0"); //期初金额 if (CurryType.LastIndexOf(",") == -1) { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ForeignThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } else { row["ThisDebit"] = Math.Round(Convert.ToDecimal(drr["ThisDebit"].ToString()), 2).ToString("#,###0.#0"); //本期借方金额 row["ThisCredit"] = Math.Round(Convert.ToDecimal(drr["ThisCredit"].ToString()), 2).ToString("#,###0.#0"); //本期贷方金额 } decimal p = BeginAmount + Convert.ToDecimal(row["ThisDebit"].ToString()) - Convert.ToDecimal(row["ThisCredit"].ToString()); row["EndAmount"] = Math.Round(p, 2).ToString("#,###0.#0"); //期末余额 row["Direction"] = AcountBookDBHelper.DirectionSource(SubjectsCD, Convert.ToDecimal(row["EndAmount"].ToString())); //余额方向 row["SourceCode"] = ""; row["Type"] = ""; row["SourceDate"] = ""; row["TotalAmount"] = ""; row["BlendingAmount"] = ""; row["Scale"] = ""; row["ByOrder"] = "1"; dt.Rows.Add(row); } } decimal ThisDebitSum = 0; decimal ThisCreditSum = 0; decimal TotalSum = 0; decimal BlendingSum = 0; decimal Amount = 0; foreach (DataRow rw in dt.Rows) { if (rw["EndAmount"].ToString().Trim().Length > 0) { ThisCreditSum += Convert.ToDecimal(rw["ThisCredit"].ToString()); ThisDebitSum += Convert.ToDecimal(rw["ThisDebit"].ToString()); Amount = BeginAmount + ThisDebitSum - ThisCreditSum; rw["EndAmount"] = Math.Abs(Amount); rw["Direction"] = AcountBookDBHelper.DirectionSource(SubjectsCD, Amount);//余额方向 } if (rw["SourceCode"].ToString().Trim().Equals("合计")) { TotalSum += Convert.ToDecimal(rw["TotalAmount"].ToString()); BlendingSum += Convert.ToDecimal(rw["BlendingAmount"].ToString()); } } DataRow rp = dt.NewRow(); rp["VoucherDate"] = ""; //日期 rp["CusOrPro"] = ""; //客户 rp["Abstract"] = "本月合计"; //摘要 rp["BeginAmount"] = ""; //期初金额 rp["ThisDebit"] = ThisDebitSum.ToString("#,###0.#0"); //本期借方金额 rp["ThisCredit"] = ThisCreditSum.ToString("#,###0.#0"); //本期贷方金额 rp["EndAmount"] = Amount.ToString("#,###0.#0"); //期末余额 rp["Direction"] = ""; //余额方向 rp["SourceCode"] = ""; rp["Type"] = ""; rp["SourceDate"] = ""; rp["TotalAmount"] = TotalSum.ToString("#,###0.#0"); rp["BlendingAmount"] = BlendingSum.ToString("#,###0.#0"); rp["ByOrder"] = "1"; if (TotalSum == 0) { rp["Scale"] = ""; } else { rp["Scale"] = Convert.ToString(Math.Round((BlendingSum / TotalSum) * 100, 2)) + "%"; } dt.Rows.Add(rp); } return(dt); } catch (Exception ex) { throw ex; } }