/// <summary> /// 获取不相同的科目编码的顶级科目编码 /// </summary> /// <returns></returns> public static string GetDistinctSubjectsCD() { string str = string.Empty; try { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; if (!string.IsNullOrEmpty(CompanyCD)) { DataTable dt = SubjectsBeginDetailsDBHelper.GetDistinctSubjectCD(CompanyCD); foreach (DataRow dr in dt.Rows) { string PresubjectsCD = VoucherDBHelper.GetSubjectsPerCD(dr["SubjectsCD"].ToString()).ToString().Split(',')[0].ToString(); if (!str.Contains(PresubjectsCD)) { str += PresubjectsCD + ","; } } str = str.TrimEnd(new char[] { ',' }); str = str.Replace("'", ""); } return(str); } catch (Exception ex) { throw ex; } }
/// <summary> /// 业务单反确认时--已登记凭证且已登帐的业务单--反登帐-->删除自动生成的凭证-->更新业务单登记凭证状态 /// </summary> /// <param name="FromTBInfo">业务单表及主键,格式为(表名(带上架构),主键)</param> /// <param name="returnV">returnValue = "0";//未自动生成凭证,不做处理,returnValue = "1";//反登帐成功,returnValue = "2";//反登帐失败,returnValue = "3";//删除对应凭证并更新原始业务单凭证登记状态成功,returnValue = "4";//删除对应凭证并更新原始业务单凭证登记状态失败</param> /// <returns></returns> public static bool AntiConfirmVoucher(string FromTBInfo, out string returnV) { bool rev = true; string[] TBinfo = FromTBInfo.Split(','); string returnValue = "0";//未自动生成凭证 StringBuilder SelectBill = new StringBuilder();//获取业务单生成的凭证的主键 SelectBill.AppendLine("Select AttestBillID from {0} "); SelectBill.AppendLine(" where ID=@ID "); string selectBillSQL = string.Format(SelectBill.ToString(), TBinfo[0].ToString()); SqlParameter[] parms = { new SqlParameter("@ID", TBinfo[1].ToString()) }; object obj = SqlHelper.ExecuteScalar(selectBillSQL, parms); if (obj != null) { string objValue = Convert.ToString(obj); if (!string.IsNullOrEmpty(objValue)) { DataTable rowDt = VoucherDBHelper.GetVoucherInfo(Convert.ToInt32(obj)); if (rowDt != null && rowDt.Rows.Count > 0) { if (VoucherDBHelper.AntiAccount(objValue)) //反登帐成功 { returnValue = "1"; //反登帐成功 if (VoucherDBHelper.DeleteAttestBillInfo(objValue)) //删除凭证并更新业务单登记凭证状态 { returnValue = "3"; //删除对应凭证并更新原始业务单凭证登记状态成功 } else { returnValue = "4";//删除对应凭证并更新原始业务单凭证登记状态失败 rev = false; } } else { returnValue = "2";//反登帐失败 rev = false; } } } } returnV = returnValue; return(rev); }
private decimal GetAmount(int FormulaID, string sql, string Line, string CurrencyTypeID) { string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;//公司编码 if (Convert.ToInt32(Line) == 10 || Convert.ToInt32(Line) == 13 || Convert.ToInt32(Line) == 15) { return(0); } decimal YInitAmount = 0; //年初始值 decimal CurrentAmount = 0; //本期数 decimal TotalAmount = 0; //总金额 decimal nev = 0; try { DataTable dt = ProfitFormulaDBHelper.GetInstance().GetProfitFormulaDetails(CompanyCD, FormulaID); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow rows in dt.Rows) { YInitAmount = AcountBookDBHelper.GetBeginCurryTypeAmount(rows["SubjectsCD"].ToString(), CurrencyTypeID, CompanyCD); CurrentAmount = VoucherDBHelper.GetBeginAmount(sql, rows["SubjectsCD"].ToString(), rows["Direction"].ToString()); TotalAmount = YInitAmount + CurrentAmount; if (rows["Operator"].ToString().Trim() == "+") { nev = nev + TotalAmount; } else if (rows["Operator"].ToString().Trim() == "-") { nev = nev - TotalAmount; } } } } catch (Exception ex) { throw ex; } return(nev); }
/// <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 CompanyCD, string SubjectsDetails, string FormTBName, string FileName) { try { string SubjectsCDList = VoucherDBHelper.GetSubjectsNextCD(SubjectsCD); string sql = string.Empty; if (CurryType.LastIndexOf(",") == -1) { sql = string.Format(@"select a.Abstract,a.VoucherDate,a.CurrencyTypeID,a.ExchangeRate,a.OriginalAmount,a.ForeignBeginAmount,a.ForeignThisDebit,a.ForeignThisCredit,a.ForeignEndAmount,a.BeginAmount,a.ThisDebit,a.ThisCredit,a.EndAmount,b.FromTbale,b.FromValue from officedba.AcountBook a left outer join officedba.AttestBill b on a.AttestBillID=b.ID where a.VoucherDate>='{0}' and a.VoucherDate<='{1}' and a.CurrencyTypeID='{2}' and a.SubjectsCD in ( {3} ) and a.CompanyCD='{4}' and b.CompanyCD='{5}' and a.SubjectsDetails='{6}' and a.FormTBName='{7}' and a.FileName='{8}'", StartDate, EndDate, CurryType, SubjectsCDList, CompanyCD, CompanyCD, SubjectsDetails, FormTBName, FileName); } else { sql = string.Format(@"select a.Abstract,a.VoucherDate,a.CurrencyTypeID,a.ExchangeRate,a.OriginalAmount,a.ForeignBeginAmount,a.ForeignThisDebit,a.ForeignThisCredit,a.ForeignEndAmount,a.BeginAmount,a.ThisDebit,a.ThisCredit,a.EndAmount,b.FromTbale,b.FromValue from officedba.AcountBook a left outer join officedba.AttestBill b on a.AttestBillID=b.ID where a.VoucherDate>='{0}' and a.VoucherDate<='{1}' and a.SubjectsCD in ( {2} ) and a.CompanyCD='{3}' and b.CompanyCD='{4}' and a.SubjectsDetails='{5}' and a.FormTBName='{6}' and a.FileName='{7}'", StartDate, EndDate, SubjectsCDList, CompanyCD, CompanyCD, SubjectsDetails, FormTBName, FileName); } return(SqlHelper.ExecuteSql(sql)); } 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, string CompanyCD) { try { string SubjectsCDList = VoucherDBHelper.GetSubjectsNextCD(SubjectsCD); string sql = string.Empty; if (CurryType.LastIndexOf(",") == -1) { sql = string.Format(@"select distinct [SubjectsDetails],[FormTBName],[FileName] from officedba.AcountBook where CurrencyTypeID='{0}' and SubjectsCD in ( " + SubjectsCDList + " ) and CompanyCD='{1}' and SubjectsDetails<>'' and SubjectsDetails is not null ", CurryType, CompanyCD); } else { sql = string.Format(@"select distinct [SubjectsDetails],[FormTBName],[FileName] from officedba.AcountBook where SubjectsCD in ( " + SubjectsCDList + " ) and CompanyCD='{0}' and SubjectsDetails<>'' and SubjectsDetails is not null", CompanyCD); } return(SqlHelper.ExecuteSql(sql)); } catch (Exception ex) { throw ex; } }
public DataTable GetProfitProcessInfo(string DuringDate, string TypeID) { DataTable Profitdt = null; //定义利润表对象 #region 变量的定义 string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; //公司编码 string CurrentstartDate = string.Empty; //本期开始时间 string CurrentendDate = string.Empty; //本期结束时间 string BeforestartDate = string.Empty; //上期开始时间 string BeforeendDate = string.Empty; //上期结束时间 string CurrencyTypeID = string.Empty; //币种ID string DayEnd = string.Empty; //月末数 string _DayEnd = string.Empty; //月末数 decimal CurrBusinessProfit = 0; //本年营业利润 decimal BeforeBusinessProfit = 0; //上一年营业利润 #endregion try { if (TypeID == "0")//显示本年累计数、上年累计数 { //对输入的资料日期进行处理开始 int MothString = Convert.ToInt32(DuringDate.Split('-')[1].ToString()); int yearString = Convert.ToInt32(DuringDate.Split('-')[0].ToString()); int Year = Convert.ToInt32(DuringDate.Split('-')[0].ToString()); Year = Year - 1; if (MothString == 4 || MothString == 6 || MothString == 9 || MothString == 11) { DayEnd = "-30"; _DayEnd = "-30"; } else if (MothString == 2) { if (((yearString % 4 == 0 && yearString % 100 == 0)) || (yearString % 400 == 0)) { DayEnd = "-29"; } else { DayEnd = "-28"; } if (((Year % 4 == 0 && Year % 100 == 0)) || (Year % 400 == 0)) { _DayEnd = "-29"; } else { _DayEnd = "-28"; } } else { DayEnd = "-31"; _DayEnd = "-31"; } CurrentstartDate = yearString.ToString() + "-01-01"; CurrentendDate = DuringDate + DayEnd; BeforestartDate = Year.ToString() + "-01-01"; if (MothString.ToString().Length == 1) { BeforeendDate = Year.ToString() + "-" + "0" + MothString.ToString() + _DayEnd; } else { BeforeendDate = Year.ToString() + "-" + MothString.ToString() + _DayEnd; } //获取币种表币种ID DataTable Currencydt = CurrTypeSettingDBHelper.GetCurrTypeByCompanyCD(CompanyCD); if (Currencydt != null && Currencydt.Rows.Count > 0) { foreach (DataRow rows in Currencydt.Rows) { CurrencyTypeID += rows["ID"].ToString() + ","; } CurrencyTypeID = CurrencyTypeID.TrimEnd(new char[] { ',' });//所有币种ID集 } //获取公式项目信息 DataTable ForumlaItemDt = ProfitFormulaDBHelper.GetInstance().GetProfitFormulaInfo(); /* * Add by Moshenlin 去除统计期末处理损益结转生成的凭证登帐的账簿信息 */ string CurrNotIDS = VoucherDBHelper.GetProfitandLossAttestIDS(CurrentstartDate, CurrentendDate, CompanyCD); string BeforeNotIDS = VoucherDBHelper.GetProfitandLossAttestIDS(BeforestartDate, BeforeendDate, CompanyCD); string CQueryStr = string.Empty; string BQueryStr = string.Empty; if (CurrNotIDS.Trim().Length > 0) { CQueryStr = " and AttestBillID not in ( " + CurrNotIDS + " ) "; } if (BeforeNotIDS.Trim().Length > 0) { BQueryStr = " and AttestBillID not in ( " + BeforeNotIDS + " ) "; } //执行本期数SQL语句 string CurrSql = " VoucherDate>='" + CurrentstartDate + "' and VoucherDate<='" + CurrentendDate + "' and CompanyCD='" + CompanyCD + "' " + CQueryStr + " "; //执行上期数SQL语句 string BeforeSql = " VoucherDate>='" + BeforestartDate + "' and VoucherDate<='" + BeforeendDate + "' and CompanyCD='" + CompanyCD + "' " + BQueryStr + " "; if (ForumlaItemDt != null && ForumlaItemDt.Rows.Count > 0) { Profitdt = new DataTable();//构造利润表对象 Profitdt.Columns.Add("itemName"); Profitdt.Columns.Add("line"); Profitdt.Columns.Add("currMoney"); Profitdt.Columns.Add("agoMoney"); foreach (DataRow rows in ForumlaItemDt.Rows) { DataRow Newrows = Profitdt.NewRow(); Newrows["itemName"] = rows["Name"].ToString(); //项目名称 Newrows["line"] = rows["Line"].ToString(); //行号 if (Convert.ToInt32(rows["Line"]) != 10 && Convert.ToInt32(rows["Line"]) != 13 && Convert.ToInt32(rows["Line"]) != 15) { //获取本期数 Newrows["currMoney"] = Math.Round(GetAmount(Convert.ToInt32(rows["ID"]), CurrSql, rows["Line"].ToString(), CurrencyTypeID), 2).ToString("#,###0.#0"); //获取上期数 Newrows["agoMoney"] = Math.Round(GetAmount(Convert.ToInt32(rows["ID"]), BeforeSql, rows["Line"].ToString(), CurrencyTypeID), 2).ToString("#,###0.#0"); } //营业利润 if (Convert.ToInt32(rows["Line"]) == 10) { if (Profitdt != null && Profitdt.Rows.Count > 0) { for (int i = 0; i < Profitdt.Rows.Count; i++) { if (i == 0) { //获取当年营业利润 CurrBusinessProfit = Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //获取上一年营业利润 BeforeBusinessProfit = Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } else { if (i <= 6) { //减:当年营业成本 CurrBusinessProfit = CurrBusinessProfit - Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //减:上一年营业成本 BeforeBusinessProfit = BeforeBusinessProfit - Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } else { if (i == 7) { //加:当年 投资收益 CurrBusinessProfit = CurrBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //加:当年 投资收益 BeforeBusinessProfit = BeforeBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } if (i == 8) { //加: 投资收益 CurrBusinessProfit = CurrBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //加:上一年 投资收益 BeforeBusinessProfit = BeforeBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } } } } //当年本营业利润 Newrows["currMoney"] = Math.Round(CurrBusinessProfit, 2).ToString("#,###0.#0"); //上一年本营业利润 Newrows["agoMoney"] = Math.Round(BeforeBusinessProfit, 2).ToString("#,###0.#0"); } }//利润总额 else if (Convert.ToInt32(rows["Line"]) == 13) { if (Profitdt != null && Profitdt.Rows.Count > 0) { //当年利润总额 Newrows["currMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[9]["currMoney"]) + Convert.ToDecimal(Profitdt.Rows[10]["currMoney"]) - Convert.ToDecimal(Profitdt.Rows[11]["currMoney"]), 2).ToString("#,###0.#0"); //上一年利润总额 Newrows["agoMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[9]["agoMoney"]) + Convert.ToDecimal(Profitdt.Rows[10]["agoMoney"]) - Convert.ToDecimal(Profitdt.Rows[11]["agoMoney"]), 2).ToString("#,###0.#0"); } }//净利润 else if (Convert.ToInt32(rows["Line"]) == 15) { //当年净利润 Newrows["currMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[12]["currMoney"]) - Convert.ToDecimal(Profitdt.Rows[13]["currMoney"]), 2).ToString("#,###0.#0"); //上一年净利润 Newrows["agoMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[12]["agoMoney"]) - Convert.ToDecimal(Profitdt.Rows[13]["agoMoney"]), 2).ToString("#,###0.#0"); } Profitdt.Rows.Add(Newrows); } } } else if (TypeID == "1")//显示本期数、本年累计数 { //对输入的资料日期进行处理开始 int MothString = Convert.ToInt32(DuringDate.Split('-')[1].ToString()); int yearString = Convert.ToInt32(DuringDate.Split('-')[0].ToString()); if (MothString == 4 || MothString == 6 || MothString == 9 || MothString == 11) { DayEnd = "-30"; } else if (MothString == 2) { if (((yearString % 4 == 0 && yearString % 100 == 0)) || (yearString % 400 == 0)) { DayEnd = "-29"; } else { DayEnd = "-28"; } } else { DayEnd = "-31"; } if (MothString.ToString().Length == 1) { CurrentstartDate = yearString.ToString() + "-0" + MothString + "-01"; CurrentendDate = yearString.ToString() + "-0" + MothString + DayEnd; BeforeendDate = yearString.ToString() + "-0" + MothString + DayEnd; } else { CurrentstartDate = yearString.ToString() + MothString + "-01"; CurrentendDate = yearString.ToString() + MothString + DayEnd; BeforeendDate = yearString.ToString() + MothString + DayEnd; } BeforestartDate = yearString.ToString() + "-01-01"; //获取币种表币种ID DataTable Currencydt = CurrTypeSettingDBHelper.GetCurrTypeByCompanyCD(CompanyCD); if (Currencydt != null && Currencydt.Rows.Count > 0) { foreach (DataRow rows in Currencydt.Rows) { CurrencyTypeID += rows["ID"].ToString() + ","; } CurrencyTypeID = CurrencyTypeID.TrimEnd(new char[] { ',' });//所有币种ID集 } //获取公式项目信息 DataTable ForumlaItemDt = ProfitFormulaDBHelper.GetInstance().GetProfitFormulaInfo(); /* * Add by Moshenlin 去除统计期末处理损益结转生成的凭证登帐的账簿信息 */ string CurrNotIDS = VoucherDBHelper.GetProfitandLossAttestIDS(CurrentstartDate, CurrentendDate, CompanyCD); string BeforeNotIDS = VoucherDBHelper.GetProfitandLossAttestIDS(BeforestartDate, BeforeendDate, CompanyCD); string CQueryStr = string.Empty; string BQueryStr = string.Empty; if (CurrNotIDS.Trim().Length > 0) { CQueryStr = " and AttestBillID not in ( " + CurrNotIDS + " ) "; } if (BeforeNotIDS.Trim().Length > 0) { BQueryStr = " and AttestBillID not in ( " + BeforeNotIDS + " ) "; } //执行本期数SQL语句 string CurrSql = " VoucherDate>='" + CurrentstartDate + "' and VoucherDate<='" + CurrentendDate + "' and CompanyCD='" + CompanyCD + "' " + CQueryStr + " "; //执行本年累计数SQL语句 string BeforeSql = " VoucherDate>='" + BeforestartDate + "' and VoucherDate<='" + BeforeendDate + "' and CompanyCD='" + CompanyCD + "' " + BQueryStr + " "; if (ForumlaItemDt != null && ForumlaItemDt.Rows.Count > 0) { Profitdt = new DataTable();//构造利润表对象 Profitdt.Columns.Add("itemName"); Profitdt.Columns.Add("line"); Profitdt.Columns.Add("currMoney"); Profitdt.Columns.Add("agoMoney"); foreach (DataRow rows in ForumlaItemDt.Rows) { DataRow Newrows = Profitdt.NewRow(); Newrows["itemName"] = rows["Name"].ToString(); //项目名称 Newrows["line"] = rows["Line"].ToString(); //行号 if (Convert.ToInt32(rows["Line"]) != 10 && Convert.ToInt32(rows["Line"]) != 13 && Convert.ToInt32(rows["Line"]) != 15) { //获取本期数 Newrows["currMoney"] = Math.Round(GetAmount(Convert.ToInt32(rows["ID"]), CurrSql, rows["Line"].ToString(), CurrencyTypeID), 2).ToString("#,###0.#0"); //获取上期数 Newrows["agoMoney"] = Math.Round(GetAmount(Convert.ToInt32(rows["ID"]), BeforeSql, rows["Line"].ToString(), CurrencyTypeID), 2).ToString("#,###0.#0"); } //营业利润 if (Convert.ToInt32(rows["Line"]) == 10) { if (Profitdt != null && Profitdt.Rows.Count > 0) { for (int i = 0; i < Profitdt.Rows.Count; i++) { if (i == 0) { //获取当年营业利润 CurrBusinessProfit = Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //获取上一年营业利润 BeforeBusinessProfit = Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } else { if (i <= 6) { //减:当年营业成本 CurrBusinessProfit = CurrBusinessProfit - Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //减:上一年营业成本 BeforeBusinessProfit = BeforeBusinessProfit - Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } else { if (i == 7) { //加:当年 投资收益 CurrBusinessProfit = CurrBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //加:当年 投资收益 BeforeBusinessProfit = BeforeBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } if (i == 8) { //加: 投资收益 CurrBusinessProfit = CurrBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["currMoney"]); //加:上一年 投资收益 BeforeBusinessProfit = BeforeBusinessProfit + Convert.ToDecimal(Profitdt.Rows[i]["agoMoney"]); } } } } //当年本营业利润 Newrows["currMoney"] = Math.Round(CurrBusinessProfit, 2).ToString("#,###0.#0"); //上一年本营业利润 Newrows["agoMoney"] = Math.Round(BeforeBusinessProfit, 2).ToString("#,###0.#0"); } }//利润总额 else if (Convert.ToInt32(rows["Line"]) == 13) { if (Profitdt != null && Profitdt.Rows.Count > 0) { //当年利润总额 Newrows["currMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[9]["currMoney"]) + Convert.ToDecimal(Profitdt.Rows[10]["currMoney"]) - Convert.ToDecimal(Profitdt.Rows[11]["currMoney"]), 2).ToString("#,###0.#0"); //上一年利润总额 Newrows["agoMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[9]["agoMoney"]) + Convert.ToDecimal(Profitdt.Rows[10]["agoMoney"]) - Convert.ToDecimal(Profitdt.Rows[11]["agoMoney"]), 2).ToString("#,###0.#0"); } }//净利润 else if (Convert.ToInt32(rows["Line"]) == 15) { //当年净利润 Newrows["currMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[12]["currMoney"]) - Convert.ToDecimal(Profitdt.Rows[13]["currMoney"]), 2).ToString("#,###0.#0"); //上一年净利润 Newrows["agoMoney"] = Math.Round(Convert.ToDecimal(Profitdt.Rows[12]["agoMoney"]) - Convert.ToDecimal(Profitdt.Rows[13]["agoMoney"]), 2).ToString("#,###0.#0"); } Profitdt.Rows.Add(Newrows); } } } } catch (Exception ex) { throw ex; } return(Profitdt); }
/// <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; } }
public static bool CountFixAssetDepreciation(string ItemID, string PeriodNum, ref string getAttestNo, ref int AttestID) { //获取当前操作用户企业编码 bool result = false;//执行返回结果 try { int PeriodID = 0; string CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; DataTable dt = FixAssetInfoDBHelper.GetAssetInfoByCompanyCD(CompanyCD, PeriodNum); if (dt != null && dt.Rows.Count > 0) { #region 固定资产计提折旧计算 //计算方法 string CountMethod = string.Empty; //预计使用年限 int EstimateUseYear; //预计净残值率 decimal EstiResiValue; //原值 decimal OriginalValue; //月折旧率 decimal MonthDeprRate; //期末净值 decimal EndNetValue; //本期净值 decimal CurrentNetValue; //本期减值准备 decimal CurrValueRe; //月折旧额 decimal MonthDeprPrice = 0; //累计月折旧额 decimal CountTotalDeprPrice = 0; ArrayList DeprDetailList = new ArrayList(); ArrayList DeprPeriodList = new ArrayList(); //计提前固定资产实体数组 ArrayList FixDeprAfterList = new ArrayList(); //定义固定资产计提明细实体 FixAssetDeprDetailModel DeprDetailmodel = null; //资产计提 FixAssetPeriodDeprModel DeprPeriodmodel = null; //资产计提前实体 FixAssetDeprAfterModel FixDeprAftermodel = null; foreach (DataRow rows in dt.Rows) { //读取期末净值 EndNetValue = Convert.ToDecimal(rows["EndNetValue"]); //读取计算方法 CountMethod = rows["CountMethod"].ToString(); //读取预计净残值率 EstiResiValue = Convert.ToDecimal(rows["EstiResiValue"]); //读取原价 OriginalValue = Convert.ToDecimal(rows["OriginalValue"]); //读取本期减值准备 CurrValueRe = Convert.ToDecimal(rows["CurrValueRe"]); //读取月折旧率 MonthDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]); //实例固定资产计提明细实体 DeprDetailmodel = new FixAssetDeprDetailModel(); #region 固定资产计提明细赋值 //给固定资产计提明细赋值 DeprDetailmodel.CompanyCD = CompanyCD; DeprDetailmodel.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; //固定资产名称 DeprDetailmodel.FixName = rows["FixName"].ToString(); //固定资产编号 DeprDetailmodel.FixNo = rows["FixNo"].ToString(); //资产类别 DeprDetailmodel.FixType = Convert.ToInt32(rows["FixType"]); //资产数量 DeprDetailmodel.Number = Convert.ToInt32(rows["FixNumber"]); //资产原值 DeprDetailmodel.OriginalValue = Convert.ToDecimal(rows["OriginalValue"]); //使用日期 DeprDetailmodel.UsedDate = Convert.ToDateTime(rows["UseDate"]); //使用年限 DeprDetailmodel.UsedYears = Convert.ToDecimal(rows["UsedYear"]); //预计使用年限 DeprDetailmodel.EstimateUse = Convert.ToDecimal(rows["EstimateUse"]); //计算预计净残值 decimal Yjcz = OriginalValue * (EstiResiValue / 100); #endregion //判断期末净值大于计净残值则进行折旧处理 if (EndNetValue > Yjcz) { //年限平均法 if (CountMethod == ConstUtil.ASSETCOUNT_METHOD_NXPJF_CODE) { //读取期初减值准备 decimal ReduValueRe = Convert.ToDecimal(rows["ReduValueRe"]); //读取预计使用年限 EstimateUseYear = Convert.ToInt32(rows["EstimateUse"]); //MonthDeprPrice = Math.Round(MonthDeprRate * OriginalValue, 2);//计算月折旧额 //读取月折旧额 MonthDeprPrice = Convert.ToDecimal(rows["AmorDeprM"]); //计算本期期末净值 CurrentNetValue = EndNetValue - MonthDeprPrice - CurrValueRe; //本期期末净值 DeprDetailmodel.EndNetValue = CurrentNetValue; //月折旧额 DeprDetailmodel.MDeprPrice = MonthDeprPrice; //计算累计折旧额 CountTotalDeprPrice = Convert.ToDecimal(rows["TotalDeprPrice"]) + MonthDeprPrice; DeprDetailmodel.TotalDeprPrice = CountTotalDeprPrice; //累计减值 DeprDetailmodel.TotalImpairment = (ReduValueRe + CurrValueRe); //计算月折旧额 //如果本期净值大于净残值则更新 if (CurrentNetValue > Yjcz) { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Math.Round(CurrentNetValue, 2); DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = 0; DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = CountTotalDeprPrice; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } else { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]); FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Yjcz; DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = 0; DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = Convert.ToDecimal(rows["EndNetValue"]) - Yjcz; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } } //工作量法 else if (CountMethod == ConstUtil.ASSETCOUNT_METHOD_GZLF_CODE) { //读取月折旧额 MonthDeprPrice = Convert.ToDecimal(rows["AmorDeprM"]); //计算本期期末净值 CurrentNetValue = EndNetValue - MonthDeprPrice - CurrValueRe; //计算累计折旧额 CountTotalDeprPrice = Convert.ToDecimal(rows["TotalDeprPrice"]) + MonthDeprPrice; //如果本期净值大于原净值则不更新 if (CurrentNetValue > Yjcz) { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Math.Round(CurrentNetValue, 2); DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = 0; DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = CountTotalDeprPrice; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } else { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Yjcz; DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = 0; DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = Convert.ToDecimal(rows["EndNetValue"]) - Yjcz; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } }//年限总和法 else if (CountMethod == ConstUtil.ASSETCOUNT_METHOD_NXZHF_CODE) { //年折旧率 decimal YearDeprRate = 0; //读取预计使用年限 EstimateUseYear = Convert.ToInt32(rows["EstimateUse"]); //读取已使用年限 int UsedYears = Convert.ToInt32(rows["UsedYear"]); //计算年折旧率 YearDeprRate = Convert.ToDecimal((EstimateUseYear - UsedYears)) / (Convert.ToDecimal(EstimateUseYear) * (Convert.ToDecimal(EstimateUseYear + 1)) / 2) * (100 / 100); //计算月折旧率 MonthDeprRate = YearDeprRate / 12; //计算月折旧额 MonthDeprPrice = (OriginalValue - (OriginalValue * (EstiResiValue / 100))) * MonthDeprRate; //计算本期期末净值 CurrentNetValue = EndNetValue - MonthDeprPrice - CurrValueRe; //计算累计折旧额 CountTotalDeprPrice = Convert.ToDecimal(rows["TotalDeprPrice"]) + MonthDeprPrice; if (CurrentNetValue > Yjcz) { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Math.Round(CurrentNetValue, 2); DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = Math.Round(MonthDeprRate, 8); DeprPeriodmodel.AmorDeprM = Math.Round(MonthDeprPrice, 2); DeprPeriodmodel.TotalDeprPrice = CountTotalDeprPrice; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } else { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Yjcz; DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = Math.Round(MonthDeprRate, 8); DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = Convert.ToDecimal(rows["EndNetValue"]) - Yjcz; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } }//双倍余额递减法 else if (CountMethod == ConstUtil.ASSETCOUNT_METHOD_SBYETJS_CODE) { //读取预计使用年限 EstimateUseYear = Convert.ToInt32(rows["EstimateUse"]); //年折旧率 decimal YearDeprRate = 0; //计算年折旧率 YearDeprRate = 2 / Convert.ToDecimal(EstimateUseYear) * (100 / 100); //计算月折旧率 MonthDeprRate = YearDeprRate / 12; //计算月折旧额 MonthDeprPrice = EndNetValue * MonthDeprRate; //计算本期期末净值 CurrentNetValue = EndNetValue - MonthDeprPrice - CurrValueRe; //计算累计折旧额 CountTotalDeprPrice = Convert.ToDecimal(rows["TotalDeprPrice"]) + MonthDeprPrice; if (CurrentNetValue > Yjcz) { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Math.Round(CurrentNetValue, 2); DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = Math.Round(MonthDeprRate, 8); DeprPeriodmodel.AmorDeprM = Math.Round(MonthDeprPrice, 2); DeprPeriodmodel.TotalDeprPrice = CountTotalDeprPrice; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } else { #region 添加固定资产折旧前信息实体 FixDeprAftermodel = new FixAssetDeprAfterModel(); FixDeprAftermodel.AmorDeprM = rows["AmorDeprM"] != DBNull.Value ? Convert.ToDecimal(rows["AmorDeprM"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.AmorDeprRate = Convert.ToDecimal(rows["AmorDeprRate"]);; FixDeprAftermodel.CompanyCD = CompanyCD; FixDeprAftermodel.EndNetValue = rows["EndNetValue"] != DBNull.Value ? Convert.ToDecimal(rows["EndNetValue"]) : Convert.ToDecimal(DBNull.Value); FixDeprAftermodel.FixNo = rows["FixNo"].ToString(); FixDeprAftermodel.PeriodNum = Convert.ToInt32(PeriodNum); FixDeprAftermodel.TotalDeprPrice = rows["TotalDeprPrice"] != DBNull.Value ? Convert.ToDecimal(rows["TotalDeprPrice"]) : Convert.ToDecimal(DBNull.Value); FixDeprAfterList.Add(FixDeprAftermodel); #endregion DeprPeriodmodel = new FixAssetPeriodDeprModel(); DeprPeriodmodel.CompanyCD = CompanyCD; DeprPeriodmodel.EndNetValue = Yjcz; DeprPeriodmodel.FixNo = rows["FixNo"].ToString(); DeprPeriodmodel.AmorDeprRate = Math.Round(MonthDeprRate, 8); DeprPeriodmodel.AmorDeprM = MonthDeprPrice; DeprPeriodmodel.TotalDeprPrice = Convert.ToDecimal(rows["EndNetValue"]) - Yjcz; DeprDetailList.Add(DeprDetailmodel); DeprPeriodList.Add(DeprPeriodmodel); } } } } #endregion //折旧固定资产明细 result = FixAssetInfoDBHelper.UpdateEndFixAssetInfo(DeprDetailList, DeprPeriodList, CompanyCD, PeriodNum, ItemID, ref PeriodID, FixDeprAfterList); } if (result) { #region 凭证单据明细实体 //凭证单据实体 AttestBillModel Attestmodel = new AttestBillModel(); //凭证明细实体定义 AttestBillDetailsModel Detailmodel = null; #endregion #region 凭证单据实体赋值 Attestmodel.CompanyCD = CompanyCD; Attestmodel.AttestNo = "记-" + VoucherDBHelper.GetMaxAttestNo(CompanyCD, DateTime.Now.ToString()); getAttestNo = Attestmodel.AttestNo; Attestmodel.AttestName = "记账凭证"; Attestmodel.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; Attestmodel.status = 0; Attestmodel.FromTbale = "EndItemProcessedRecord,FixWithInfo,FixPeriodDeprDetails"; Attestmodel.FromValue = PeriodID.ToString() + "," + PeriodNum.ToString(); Attestmodel.VoucherDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")); #endregion int ID; ArrayList modelList = new ArrayList();//定义数组存储实体对象 //添加凭证单据信息 result = FixAssetInfoDBHelper.BuildDepreAttestBill(Attestmodel, out ID, ItemID, PeriodNum); AttestID = ID; if (result && ID > 0) { //获取固定资产科目汇总金额信息 DataTable AttestDetail = FixAssetInfoDBHelper.GetBuildAttestInfo(CompanyCD); //获取折旧凭证摘要ID string AbstractID; AbstractID = ConstUtil.SUMMARY_ZCJZ_NAME; #endregion //获取本币币种ID int MasterCurrencyID = Convert.ToInt32(CurrTypeSettingDBHelper.GetMasterCurrency(CompanyCD).Rows[0]["ID"]); if (AttestDetail != null && AttestDetail.Rows.Count > 0) { foreach (DataRow drows in AttestDetail.Rows) { #region 凭证明细实体赋值 Detailmodel = new AttestBillDetailsModel(); Detailmodel.AttestBillID = ID; Detailmodel.Abstract = AbstractID; // 摘要 Detailmodel.SubjectsCD = drows["AccuDeprSubjeCD"].ToString(); //累计折旧科目 贷 Detailmodel.CreditAmount = Convert.ToDecimal(drows["CountSum"]); //贷方金额 Detailmodel.DebitAmount = 0; Detailmodel.OriginalAmount = Convert.ToDecimal(drows["CountSum"]); //外币金额 Detailmodel.CurrencyTypeID = MasterCurrencyID; //本币ID Detailmodel.ExchangeRate = 1; //本币汇率 modelList.Add(Detailmodel); Detailmodel = new AttestBillDetailsModel(); Detailmodel.AttestBillID = ID; Detailmodel.Abstract = AbstractID; // 摘要 Detailmodel.CreditAmount = 0; Detailmodel.SubjectsCD = drows["DeprCostSubjeCD"].ToString(); //折旧费用科目 借 Detailmodel.DebitAmount = Convert.ToDecimal(drows["CountSum"]); //借方金额 Detailmodel.OriginalAmount = Convert.ToDecimal(drows["CountSum"]); //外币金额 Detailmodel.CurrencyTypeID = MasterCurrencyID; //本币ID Detailmodel.ExchangeRate = 1; //本币汇率 modelList.Add(Detailmodel); #endregion } //凭证自动登帐及自动审核 result = FixAssetInfoDBHelper.BuildDepreDetailInfo(modelList); if (result) { VoucherBus.SetStatus(ID.ToString(), "1", "status", 0); VoucherBus.InsertAccount(AttestID); } } } } return(result); } catch (Exception ex) { throw ex; } }
/// <summary> /// 业务单确认时自动生成凭证--自动登帐--启用自动生成凭证时生成凭证后自动回写源单登记凭证状态 /// </summary> /// <param name="TemplateType">模板类别(1.采购订单,2. 销售订单,3. 委托代销单,4. 销售退货单,5. 采购入库,6. 其他出库单,7. 销售出库单,8. 其他入库单,9.收款单,10.付款单)</param> /// <param name="CompanyCD">公司编号</param> /// <param name="IsVoucher">是否自动生成凭证 0 不生成 1 启用自动生成</param> /// <param name="IsApply">凭证是否自动登帐 0 不登帐 1 启用自动登帐</param> /// <param name="BillAmount">业务单含税金额合计</param> /// <param name="FromTBInfo">来源表信息,格式来源表名+,+来源表主键(业务单主表名称(带上架构),自动生成凭证的业务单主键)必填</param> /// <param name="CurrencyInfo">业务单币种信息,格式为(币种ID,汇率)必填,若业务单无币种汇率,则默认传本位币及汇率</param> /// <param name="ProOrCustID">科目辅助核算ID(默认为业务单中的供应商或客户主键)</param> /// <param name="returnV">返回提示信息</param> /// <returns></returns> public static bool AutoVoucherInsert(int TemplateType, string CompanyCD, string IsVoucher, string IsApply, decimal BillAmount, string FromTBInfo, string CurrencyInfo, int ProOrCustID, out string returnV) { bool rev = true; string returnValue = string.Empty; int BillID = 0; //生成凭证的凭证主键 if (IsVoucher == "1") //自动生成凭证 { /*判断某业务单是否配置生成凭证模板--start*/ StringBuilder SelectTemplateSQL = new StringBuilder(); SelectTemplateSQL.AppendLine("select Abstract,TemNo from officedba.VoucherTemplate "); SelectTemplateSQL.AppendLine(" where CompanyCD=@CompanyCD and TemType=@TemType and UsedStatus='1' "); SqlCommand comm = new SqlCommand(); comm.CommandText = SelectTemplateSQL.ToString(); comm.Parameters.AddWithValue("@CompanyCD", SqlDbType.VarChar).Value = CompanyCD; comm.Parameters.AddWithValue("@TemType", SqlDbType.Int).Value = TemplateType; DataTable dt = SqlHelper.ExecuteSearch(comm); /*判断某业务单是否配置生成凭证模板--End*/ if (dt != null && dt.Rows.Count > 0) { /*获取业务单对应的凭证模板明细--start*/ StringBuilder TemplateDetailSQL = new StringBuilder(); TemplateDetailSQL.AppendLine(" select SubjectsNo,Direction,Scale "); TemplateDetailSQL.AppendLine(" from officedba.VoucherTemplateDetail "); TemplateDetailSQL.AppendLine(" where CompanyCD=@CompanyCD "); TemplateDetailSQL.AppendLine(" and TemNo=@TemNo"); SqlCommand cmd = new SqlCommand(); cmd.CommandText = TemplateDetailSQL.ToString(); cmd.Parameters.AddWithValue("@CompanyCD", SqlDbType.VarChar).Value = CompanyCD; cmd.Parameters.AddWithValue("@TemNo", SqlDbType.VarChar).Value = dt.Rows[0]["TemNo"].ToString(); DataTable tempdt = SqlHelper.ExecuteSearch(cmd); /*获取业务单对应的凭证模板明细--End*/ if (tempdt != null && tempdt.Rows.Count > 0) { AttestBillModel Model = new AttestBillModel(); //凭证主表实例 ArrayList DetailList = new ArrayList(); //凭证明细数组 Model.Attachment = 1; //附件数 Model.AttestName = "记账凭证"; //凭证名称 Model.VoucherDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")); //凭证日期 Model.AttestNo = "记-" + VoucherDBHelper.GetMaxAttestNo(CompanyCD, DateTime.Now.ToString("yyyy-MM-dd")); //凭证号 Model.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; //制单人 Model.CreateDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")); //制单日期 Model.FromTbale = FromTBInfo.Split(',')[0].ToString(); //来源表名 Model.FromValue = FromTBInfo.Split(',')[1].ToString(); //来源表主键 Model.CompanyCD = CompanyCD; Model.FromName = ""; foreach (DataRow row in tempdt.Rows) //根据凭证模板构建凭证明细数组 { AttestBillDetailsModel DetailModel = new AttestBillDetailsModel(); //凭证明细表实例 DetailModel.Abstract = dt.Rows[0]["Abstract"].ToString(); //摘要 DetailModel.CurrencyTypeID = int.Parse(CurrencyInfo.Split(',')[0].ToString()); //币种 DetailModel.ExchangeRate = Convert.ToDecimal(CurrencyInfo.Split(',')[1].ToString()); //汇率 DetailModel.SubjectsCD = row["SubjectsNo"].ToString(); //科目编号 decimal orgAmount = BillAmount * Convert.ToDecimal(row["Scale"].ToString()) / 100; DetailModel.OriginalAmount = orgAmount; //原币金额 if (row["Direction"].ToString() == "0") { DetailModel.DebitAmount = orgAmount * Convert.ToDecimal(CurrencyInfo.Split(',')[1].ToString()); //借方金额 DetailModel.CreditAmount = 0; //贷方金额 } else { DetailModel.DebitAmount = 0; //借方金额 DetailModel.CreditAmount = orgAmount * Convert.ToDecimal(CurrencyInfo.Split(',')[1].ToString()); //贷方金额 } string Auciliary = VoucherDBHelper.GetSubjectsAuciliaryCD(row["SubjectsNo"].ToString(), CompanyCD); DetailModel.SubjectsDetails = ""; DetailModel.FormTBName = ""; DetailModel.FileName = ""; if (Auciliary == "供应商" || Auciliary == "客户") { DetailModel.SubjectsDetails = ProOrCustID.ToString();//辅助核算 if (Auciliary == "供应商") { DetailModel.FormTBName = "officedba.ProviderInfo"; DetailModel.FileName = "CustName"; } else { DetailModel.FormTBName = "officedba.CustInfo"; DetailModel.FileName = "CustName"; } } DetailList.Add(DetailModel); } if (VoucherDBHelper.InsertIntoAttestBill(Model, DetailList, out BillID, "0"))//自动生成凭证并根据IsApply判断是否登帐 --生成成功 { /*更新原始业务单登记凭证状态 start*/ StringBuilder UpdateSourceTB = new StringBuilder(); UpdateSourceTB.AppendLine("update {0} set IsAccount=@IsAccount,AccountDate=@AccountDate, "); UpdateSourceTB.AppendLine("Accountor=@Accountor , AttestBillID=@AttestBillID "); UpdateSourceTB.AppendLine(" where ID=@ID "); string UpdateSourceTBSQL = string.Format(UpdateSourceTB.ToString(), FromTBInfo.Split(',')[0].ToString()); SqlParameter[] parms = { new SqlParameter("@IsAccount", "1"), new SqlParameter("@AccountDate", DateTime.Now.ToString("yyyy-MM-dd")), new SqlParameter("@Accountor", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString()), new SqlParameter("@AttestBillID", BillID.ToString()), new SqlParameter("@ID", FromTBInfo.Split(',')[1].ToString()) }; if (SqlHelper.ExecuteTransSql(UpdateSourceTBSQL, parms) > 0)//更新业务单成功 { // } else { returnValue = " 警告:自动生成凭证失败!"; rev = false; //删除凭证操作开始 VoucherDBHelper.DeleteAttestBillInfo(BillID.ToString()); //删除凭证操作结束 } /*更新原始业务单登记凭证状态 End*/ if (IsApply == "1")//自动生成凭证是否自动登帐 --否 { if (!VoucherDBHelper.InsertAccount(BillID)) { returnValue = " 警告:自动生成凭证失败!"; rev = false; //删除凭证操作开始 VoucherDBHelper.DeleteAttestBillInfo(BillID.ToString()); //删除凭证操作结束 /*更新原始业务单登记凭证状态 start*/ StringBuilder AntiUpdateSourceTB = new StringBuilder(); AntiUpdateSourceTB.AppendLine("update {0} set IsAccount=@IsAccount,AccountDate=@AccountDate, "); AntiUpdateSourceTB.AppendLine("Accountor=@Accountor , AttestBillID=@AttestBillID "); AntiUpdateSourceTB.AppendLine(" where ID=@ID "); string AntiUpdateSourceTBSQL = string.Format(UpdateSourceTB.ToString(), FromTBInfo.Split(',')[0].ToString()); SqlParameter[] Antiparms = { new SqlParameter("@IsAccount", "0"), new SqlParameter("@AccountDate", DateTime.Now.ToString("yyyy-MM-dd")), new SqlParameter("@Accountor", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString()), new SqlParameter("@AttestBillID", BillID.ToString()), new SqlParameter("@ID", FromTBInfo.Split(',')[1].ToString()) }; SqlHelper.ExecuteTransSql(AntiUpdateSourceTBSQL, Antiparms);//更新业务单成功 /*更新原始业务单登记凭证状态 End*/ } } } else { returnValue = " 警告:自动生成凭证失败!"; rev = false; } } } else { //未设置模板 returnValue = "警告:自动生成凭证失败,请在“财务管理-初始设置”中设置或启用对应的凭证模板!"; rev = false; } } returnV = returnValue; return(rev); }
public static DataTable SearchSumStandardCurrency(string CompanyCD, string SubjectTypeID) { //DataSet ds = new DataSet(); //StringBuilder sql = new StringBuilder(); //sql.AppendLine("select case when a.Dirt='0' then '借'"); //sql.AppendLine(" when a.Dirt='1' then '贷' end as Dirt,"); //sql.AppendLine(" sum (isnull (a.YTotalDebit,0)) as YTotalDebit,"); //sql.AppendLine(" sum (isnull(a.YTotalLenders,0))"); //sql.AppendLine(" as YTotalLenders, sum(isnull(a.OriginalCurrency,0)) as OriginalCurrency,"); //sql.AppendLine(" sum(isnull (a.StandardCurrency,0)) as StandardCurrency,"); //sql.AppendLine(" a.SubjectsCD+'-'+ b.SubjectsName as SubjectsName"); //sql.AppendLine(",sum(isnull(a.BeginMoney,0)) as BeginMoney ,sum(isnull(a.SumOriginalCurrency,0)) as SumOriginalCurrency,"); //sql.AppendLine("sum(isnull(a.YTotalDebitY,0)) as YTotalDebitY,sum(isnull(a.YTotalLendersY,0)) as YTotalLendersY"); //sql.AppendLine(" from officedba.SubjectsBeginDetails as a"); //sql.AppendLine(" left join officedba.AccountSubjects as b"); //sql.AppendLine(" on a.SubjectsCD=b.SubjectsCD and a.CompanyCD=b.CompanyCD "); //sql.AppendLine(" where a.CompanyCD=@CompanyCD "); ////StringBuilder sqlcount = new StringBuilder(); ////sqlcount.AppendLine("select sum (isnull (a.YTotalDebit,0)) as YTotalDebit,"); ////sqlcount.AppendLine("sum (isnull(a.YTotalLenders,0)) as YTotalLenders, "); ////sqlcount.AppendLine("sum(isnull (a.StandardCurrency,0)) as StandardCurrency"); ////sqlcount.AppendLine(",sum (isnull(a.BeginMoney,0)) as BeginMoney ,sum (isnull(a.SumOriginalCurrency,0)) as SumOriginalCurrency, "); ////sqlcount.AppendLine("sum(isnull(a.YTotalDebitY,0)) as YTotalDebitY,sum(isnull(a.YTotalLendersY,0)) as YTotalLendersY"); ////sqlcount.AppendLine("from officedba.SubjectsBeginDetails as a"); ////sqlcount.AppendLine(" left join officedba.AccountSubjects as b"); ////sqlcount.AppendLine("on a.SubjectsCD=b.SubjectsCD and a.CompanyCD=b.CompanyCD "); ////sqlcount.AppendLine(" where a.CompanyCD=@CompanyCD "); ////定义查询的命令 //SqlCommand comm = new SqlCommand(); ////SqlCommand cmd = new SqlCommand(); ////添加公司代码参数 //comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD)); ////cmd.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD)); ////币种类别 //if (!string.IsNullOrEmpty(SubjectTypeID)) //{ // sql.AppendLine(" AND left(a.SubjectsCD,1)=@SubjectTypeID "); // comm.Parameters.Add(SqlHelper.GetParameterFromString("@SubjectTypeID", SubjectTypeID)); // //sqlcount.AppendLine(" AND left(a.SubjectsCD,1)=@SubjectTypeID "); // //cmd.Parameters.Add(SqlHelper.GetParameterFromString("@SubjectTypeID", SubjectTypeID)); //} //sql.AppendLine(" group by a.SubjectsCD,a.Dirt,b.SubjectsName"); ////指定命令的SQL文 //comm.CommandText = sql.ToString(); ////执行查询 //cmd.CommandText = sqlcount.ToString(); //DataTable dt= SqlHelper.ExecuteSearch(comm); //DataTable CountDt = SqlHelper.ExecuteSearch(cmd); //ds.Tables.Add(dt); //ds.Tables.Add(CountDt); //return ds; StringBuilder sql = new StringBuilder(); sql.AppendLine("select a.ID,a.SubjectsCD, "); sql.AppendLine("case when a.Dirt='0' then '借'"); sql.AppendLine("when a.Dirt='1' then '贷' end as Dirt,"); sql.AppendLine("isnull (a.YTotalDebit,0) as YTotalDebit,isnull(a.YTotalLenders,0)"); sql.AppendLine(" as YTotalLenders,"); sql.AppendLine("isnull(a.OriginalCurrency,0) as OriginalCurrency ,"); sql.AppendLine("isnull (a.StandardCurrency,0) as StandardCurrency"); sql.AppendLine(",isnull (a.YTotalDebitY,0) as YTotalDebitY, "); sql.AppendLine("isnull (a.YTotalLendersY,0) as YTotalLendersY,"); sql.AppendLine("isnull (a.SubjectsDetails,'') as SubjectsDetails, "); sql.AppendLine("isnull (a.FormTBName,'') as FormTBName, "); sql.AppendLine("isnull (a.FileName,'') as FileName,case when a.ID is not null then '1' end as ByOrder "); sql.AppendLine(",isnull(a.BeginMoney,0) as BeginMoney,isnull(a.SumOriginalCurrency,0) as SumOriginalCurrency from officedba.SubjectsBeginDetails as a"); sql.AppendLine("left join officedba.AccountSubjects as b on a.SubjectsCD=b.SubjectsCD and a.CompanyCD=b.CompanyCD"); sql.AppendLine("where a.CompanyCD=@CompanyCD "); //StringBuilder sqlcount = new StringBuilder(); //sqlcount.AppendLine("select sum(isnull (a.YTotalDebit,0)) as YTotalDebit,sum (isnull(a.YTotalLenders,0))"); //sqlcount.AppendLine(" as YTotalLenders,"); //sqlcount.AppendLine("sum(isnull(a.OriginalCurrency,0)) as OriginalCurrency ,"); //sqlcount.AppendLine("sum(isnull (a.StandardCurrency,0)) as StandardCurrency"); //sqlcount.AppendLine(",sum(isnull (a.YTotalDebitY,0)) as YTotalDebitY, "); //sqlcount.AppendLine("sum(isnull (a.YTotalLendersY,0)) as YTotalLendersY"); //sqlcount.AppendLine(",sum(isnull(a.BeginMoney,0)) as BeginMoney,sum (isnull(a.SumOriginalCurrency,0)) as "); //sqlcount.AppendLine("SumOriginalCurrency from officedba.SubjectsBeginDetails as a"); //sqlcount.AppendLine("left join officedba.AccountSubjects as b on a.SubjectsCD=b.SubjectsCD and a.CompanyCD=b.CompanyCD "); //sqlcount.AppendLine("where a.CompanyCD=@CompanyCD"); //定义查询的命令 SqlCommand comm = new SqlCommand(); //SqlCommand comm1 = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD)); //comm1.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD)); //科目类别 if (!string.IsNullOrEmpty(SubjectTypeID)) { sql.AppendLine(" AND left(a.SubjectsCD,1)=@SubjectsType "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@SubjectsType", SubjectTypeID)); //sqlcount.AppendLine(" AND left(a.SubjectsCD,1)=@SubjectsType"); //comm1.Parameters.Add(SqlHelper.GetParameterFromString("@SubjectsType", SubjectsType)); } sql.AppendLine(" order by a.SubjectsCD asc "); //指定命令的SQL文 comm.CommandText = sql.ToString(); //执行查询 DataTable dt = SqlHelper.ExecuteSearch(comm); foreach (DataRow dr in dt.Rows) { dr["SubjectsCD"] = dr["SubjectsCD"].ToString() + "-" + VoucherDBHelper.GetSubJectsName(dr["SubjectsCD"].ToString(), dr["SubjectsDetails"].ToString(), dr["FormTBName"].ToString(), dr["FileName"].ToString(), CompanyCD); } return(dt); }