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