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