/// <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;
}
}
