using System; using System.Collections.Generic; using System.Text; using System.Data; namespace DAL { public class ClsCg_ProInvoiceBill_K3 : DBUtility.ClsXt_BaseBill { public Model.ClsCg_ProInvoiceBillMain_K3 omodel = new Model.ClsCg_ProInvoiceBillMain_K3(); public List DetailColl = new List(); public ClsCg_ProInvoiceBill_K3() { base.MvarItemKeySub = "ICPurchaseEntry"; base.MvarItemKeySub2 = ""; base.MvarItemKeySub3 = ""; base.MvarItemKeySub4 = ""; base.MvarItemKey = "ICPurchase"; base.MvarReportTitle = "KD采购发票（普通）"; base.BillType = "76"; } #region 固定代码 ~ClsCg_ProInvoiceBill_K3() { DetailColl = null; } #endregion 自定义方法 //单据号是否重复 public bool IsExistBillNo(ref string sReturn, string sBillNo, DBUtility.ClsPub.Enum_BillStatus oBillStatus, Int64 lngBillKey) { try { string sSql = ""; if (oBillStatus == DBUtility.ClsPub.Enum_BillStatus.BillStatus_AddNew) { sSql = "Select FInterID from ICPurchase where FTranType='" + BillType + "' and FBillNo ='" + sBillNo + "'"; } else { sSql = "Select FInterID from ICPurchase where FTranType='" + BillType + "' and FBillNo ='" + sBillNo + "' and FInterID<>" + lngBillKey.ToString(); } DataSet Ds; Ds = oK3Cn.RunProcReturn(sSql, "ICPurchase"); if (Ds.Tables[0].Rows.Count != 0) { sReturn = "单号重复"; return true; } sReturn = "单号未重复"; return false; } catch (Exception e) { sReturn = e.Message; return false; } } //删除子表 public void DeleteBillSub(Int64 lngBillKey) { oK3Cn.RunProc("Delete From ICPurchaseEntry where FInterID=" + lngBillKey.ToString()); } //删除主表 public void DeleteBillMain(Int64 lngBillKey) { oK3Cn.RunProc("Delete From ICPurchase where FInterID=" + lngBillKey.ToString()); } //删除单据 public bool DeleteBill(Int64 lngBillKey, ref string sReturn) { DataSet Ds = new DataSet(); try { oK3Cn.BeginTran(); //删除关联 //DeleteRelation(ref sReturn, lngBillKey); //删除明细表 DeleteBillSub(lngBillKey); //删除主表 DeleteBillMain(lngBillKey); //删除关联 sReturn = "删除单据成功！"; oK3Cn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oK3Cn.RollBack(); return false; } } //修改单据 public override bool ModifyBill(Int64 lngBillKey, ref string sReturn) { try { oK3Cn.BeginTran(); //删除主表 DeleteBillMain(lngBillKey); //删除子表 DeleteBillSub(lngBillKey); // //更新主表 oK3Cn.RunProc("Insert Into ICPurchase " + "(FInterID,FBillNo,FBrNo,FTranType,FCancellation" + ",FStatus,FROB,Fdate" + ",FCheckDate,FBillerID,FMultiCheckDate1,FMultiCheckDate2" + ",FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6" + ",FSupplyID,FSettleDate,FInvStyle,FCussentAcctID" + ",FCurrencyID,FExchangeRate,FDeptID,FManagerID,FEmpID" + ",FBillerName,Fnote" + ") " + " values(" + lngBillKey.ToString() + ",'" + omodel.HBillNo + "','0'," + this.BillType + ",0" + ",0," + omodel.HRedBlueFlag.ToString() + ",'" + omodel.HDate.ToShortDateString() + "'" + ",Null,'" + omodel.HMaker + "',Null,Null" + ",Null,Null,Null,Null" + ", " + omodel.HSupplyID.ToString() + ",'" + omodel.HSettleDate.ToShortDateString() + "'," + omodel.HInvStyle.ToString() + "," + omodel.HCussentAcctID.ToString() + ", " + omodel.HCurrencyID.ToString() + "," + omodel.HExchangeRate.ToString() + "," + omodel.HDeptID.ToString() + "," + omodel.HManagerID.ToString() + "," + omodel.HEmpID.ToString() + ",'" + omodel.HBillerName + "','" + omodel.HRemark + "'" + ") "); //插入子表 foreach (Model.ClsCg_ProInvoiceBillSub_K3 oSub in DetailColl) { oK3Cn.RunProc("Insert into ICPurchaseEntry " + " (FInterID,FEntryID,FBrNo,Fnote" + ",FSourceBillNo,FSourceTranType,FSourceInterId,FSourceEntryID" + ",FItemID,FAuxPropID,FUnitID,Fauxqty,Fauxprice" + ",FSecCoefficient,FDiscountRate,FAuxPriceDiscount,Famount,FStdAmount" + ",FAmtDiscount,FStdAmtDiscount,FTaxRate,FTaxAmount,FStdTaxAmount" + ",FAmountMust,FNoMust,FDeductTax" + ") values(" + omodel.HInterID.ToString() + "," + oSub.HEntryID.ToString() + ",'0','" + oSub.HRemark + "'" + ",'" + oSub.HSourceBillNo + "','" + oSub.HSourceBillType + "'," + oSub.HSourceInterID.ToString() + "," + oSub.HSourceEntryID.ToString() + ", " + oSub.HMaterID.ToString() + "," + oSub.HPropertyID.ToString() + "," + oSub.HUnitID.ToString() + "," + oSub.Hauxqty.ToString() + "," + oSub.Hauxprice.ToString() + ", " + oSub.HSecCoefficient.ToString() + "," + oSub.HDiscountRate.ToString() + "," + oSub.HAuxPriceDiscount.ToString() + "," + oSub.Hamount.ToString() + "," + oSub.HStdAmount.ToString() + ", " + oSub.HAmtDiscount.ToString() + "," + oSub.HStdAmtDiscount.ToString() + "," + oSub.HTaxRate.ToString() + "," + oSub.HTaxAmount.ToString() + "," + oSub.HStdTaxAmount.ToString() + ", " + oSub.HAmountMust.ToString() + "," + oSub.HNoMust.ToString() + "," + oSub.HDeductTax.ToString() + ") "); } sReturn = "修改单据成功！"; oK3Cn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oK3Cn.RollBack(); throw (e); } } //新增单据 public override bool AddBill(ref string sReturn) { try { DataSet Ds; //得到mainid Ds = oK3Cn.RunProcReturn("declare @InterID int set @InterID=0 exec GetICMaxNum 'ICPurchase', @InterID output, 1, 16394 select ltrim(@InterID)", "GetICMaxNum"); omodel.HInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0][0]); //若MAINDI重复则重新获取 oK3Cn.BeginTran(); //主表 oK3Cn.RunProc("Insert Into ICPurchase " + "(FInterID,FBillNo,FBrNo,FTranType,FCancellation" + ",FStatus,FROB,Fdate" + ",FCheckDate,FBillerID,FMultiCheckDate1,FMultiCheckDate2" + ",FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FMultiCheckDate6" + ",FSupplyID,FSettleDate,FInvStyle,FCussentAcctID" + ",FCurrencyID,FExchangeRate,FDeptID,FManagerID,FEmpID" + ",FBillerName,Fnote" + ") " + " values(" + omodel.HInterID.ToString() + ",'" + omodel.HBillNo + "','0'," + this.BillType + ",0" + ",0," + omodel.HRedBlueFlag.ToString() + ",'" + omodel.HDate.ToShortDateString() + "'" + ",Null,'" + omodel.HMaker + "',Null,Null" + ",Null,Null,Null,Null" + ", " + omodel.HSupplyID.ToString() + ",'" + omodel.HSettleDate.ToShortDateString() + "'," + omodel.HInvStyle.ToString() + "," + omodel.HCussentAcctID.ToString() + ", " + omodel.HCurrencyID.ToString() + "," + omodel.HExchangeRate.ToString() + "," + omodel.HDeptID.ToString() + "," + omodel.HManagerID.ToString() + "," + omodel.HEmpID.ToString() + ",'" + omodel.HBillerName + "','" + omodel.HRemark + "'" + ") "); //插入子表 foreach (Model.ClsCg_ProInvoiceBillSub_K3 oSub in DetailColl) { oK3Cn.RunProc("Insert into ICPurchaseEntry " + " (FInterID,FEntryID,FBrNo,Fnote" + ",FSourceBillNo,FSourceTranType,FSourceInterId,FSourceEntryID" + ",FItemID,FAuxPropID,FUnitID,Fauxqty,Fauxprice" + ",FSecCoefficient,FDiscountRate,FAuxPriceDiscount,Famount,FStdAmount" + ",FAmtDiscount,FStdAmtDiscount,FTaxRate,FTaxAmount,FStdTaxAmount" + ",FAmountMust,FNoMust,FDeductTax" + ") values(" + omodel.HInterID.ToString() + "," + oSub.HEntryID.ToString() + ",'0','" + oSub.HRemark + "'" + ",'" + oSub.HSourceBillNo + "','" + oSub.HSourceBillType + "'," + oSub.HSourceInterID.ToString() + "," + oSub.HSourceEntryID.ToString() + ", " + oSub.HMaterID.ToString() + "," + oSub.HPropertyID.ToString() + "," + oSub.HUnitID.ToString() + "," + oSub.Hauxqty.ToString() + "," + oSub.Hauxprice.ToString() + ", " + oSub.HSecCoefficient.ToString() + "," + oSub.HDiscountRate.ToString() + "," + oSub.HAuxPriceDiscount.ToString() + "," + oSub.Hamount.ToString() + "," + oSub.HStdAmount.ToString() + ", " + oSub.HAmtDiscount.ToString() + "," + oSub.HStdAmtDiscount.ToString() + "," + oSub.HTaxRate.ToString() + "," + oSub.HTaxAmount.ToString() + "," + oSub.HStdTaxAmount.ToString() + ", " + oSub.HAmountMust.ToString() + "," + oSub.HNoMust.ToString() + "," + oSub.HDeductTax.ToString() + ") "); } sReturn = "新增单据成功！"; oK3Cn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oK3Cn.RollBack(); throw (e); } } //显示单据 public override bool ShowBill(Int64 lngBillKey, ref string sReturn) { try { //查询主表 DataSet Ds; Ds = oK3Cn.RunProcReturn("Select * from ICPurchase Where FInterID=" + lngBillKey.ToString(), "ICPurchase"); if (Ds.Tables[0].Rows.Count == 0) { sReturn = "单据未找到！"; return false; } //赋值 omodel.HInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FInterID"]); omodel.HBillNo = Ds.Tables[0].Rows[0]["FBillNo"].ToString().Trim(); omodel.HDate = Convert.ToDateTime(Ds.Tables[0].Rows[0]["Fdate"].ToString().Trim()); omodel.HMaker = Ds.Tables[0].Rows[0]["FBillerID"].ToString().Trim(); omodel.HMakeDate = Ds.Tables[0].Rows[0]["Fdate"].ToString().Trim(); omodel.HCheckDate = Ds.Tables[0].Rows[0]["FCheckDate"].ToString().Trim(); omodel.HChecker = Ds.Tables[0].Rows[0]["FCheckerID"].ToString().Trim(); omodel.HRemark = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[0]["Fnote"]); // omodel.HSupplyID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FSupplyID"]); //omodel.HPayCondition = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FPayCondition"]); omodel.HSettleDate = DBUtility.ClsPub.isDate(Ds.Tables[0].Rows[0]["FSettleDate"]); omodel.HInvStyle = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FInvStyle"]); omodel.HCussentAcctID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FCussentAcctID"]); omodel.HCurrencyID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FCurrencyID"]); omodel.HExchangeRate = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[0]["FExchangeRate"]); omodel.HDeptID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FDeptID"]); omodel.HManagerID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FManagerID"]); omodel.HEmpID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FEmpID"]); omodel.HBillerName = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[0]["FBillerName"]); omodel.HRedBlueFlag = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[0]["FROB"]); //循环 DataSet DsSub; DsSub = oK3Cn.RunProcReturn("Select * from ICPurchaseEntry Where FInterID=" + lngBillKey.ToString() + " order by HEntryID ", "ICPurchaseEntry"); DetailColl.Clear();//清空 for (int i = 0; i < DsSub.Tables[0].Rows.Count; i++) { Model.ClsCg_ProInvoiceBillSub_K3 oSub = new Model.ClsCg_ProInvoiceBillSub_K3(); oSub.HInterID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FInterID"].ToString()); oSub.HEntryID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FEntryID"].ToString()); oSub.HMaterID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FItemID"].ToString()); oSub.HPropertyID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FAuxPropID"].ToString()); oSub.HUnitID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FUnitID"].ToString()); oSub.Hauxqty = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["Fauxqty"].ToString()); oSub.Hauxprice = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["Fauxprice"].ToString()); oSub.HSecCoefficient = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FSecCoefficient"].ToString()); oSub.HDiscountRate = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FDiscountRate"].ToString()); oSub.HAuxPriceDiscount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FAuxPriceDiscount"].ToString()); oSub.Hamount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["Famount"].ToString()); oSub.HStdAmount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FStdAmount"].ToString()); oSub.HAmtDiscount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FAmtDiscount"].ToString()); oSub.HStdAmtDiscount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FStdAmtDiscount"].ToString()); oSub.HTaxRate = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FTaxRate"].ToString()); oSub.HTaxAmount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FTaxAmount"].ToString()); oSub.HStdTaxAmount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FStdTaxAmount"].ToString()); //oSub.HUnTaxamount = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FUnTaxamount"].ToString()); //oSub.HUnTaxamountFor = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FUnTaxamountFor"].ToString()); oSub.HAmountMust = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FAmountMust"].ToString()); oSub.HNoMust = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FNoMust"].ToString()); oSub.HDeductTax = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[i]["FDeductTax"].ToString()); oSub.HRemark = DsSub.Tables[0].Rows[i]["Fnote"].ToString().Trim(); // oSub.HSourceInterID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FSourceInterID"].ToString()); oSub.HSourceEntryID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[i]["FSourceEntryID"].ToString()); oSub.HSourceBillType = DsSub.Tables[0].Rows[i]["FSourceTranType"].ToString().Trim(); oSub.HSourceBillNo = DsSub.Tables[0].Rows[i]["FSourceBillNo"].ToString().Trim(); DetailColl.Add(oSub); } sReturn = "显示单据成功！"; return true; } catch (Exception e) { sReturn = e.Message; throw (e); } } } }