using System; using System.Collections.Generic; using System.Text; using System.Data; namespace DAL { public class ClsWL_YayBillMain : DBUtility.ClsXt_BaseBill { public Model.ClsWL_YayBillMainBillMain omodel = new Model.ClsWL_YayBillMainBillMain(); public List DetailColl = new List(); string SafeString(string value, string defaultValue = "") => string.IsNullOrEmpty(value) ? defaultValue : value; // 辅助方法：安全转换为整数，处理null、空字符串和非数字 int SafeInt(string value, int defaultValue = 0) => int.TryParse(value, out int result) ? result : defaultValue; // 辅助方法：安全转换为小数 decimal SafeDecimal(string value, decimal defaultValue = 0) => decimal.TryParse(value, out decimal result) ? result : defaultValue; // 辅助方法：安全转换为日期 string SafeDateTime(string dateStr, string defaultValue) { if (string.IsNullOrEmpty(dateStr) || dateStr == "\"\"") return defaultValue; if (DateTime.TryParse(dateStr, out DateTime result) && result != DateTime.MinValue) return "'" + result.ToString("yyyy-MM-dd HH:mm:ss") + "'"; return defaultValue; } public ClsWL_YayBillMain() { base.MvarItemKeySub = "WL_YayBillMain"; base.MvarItemKeySub2 = ""; base.MvarItemKeySub3 = ""; base.MvarItemKeySub4 = ""; base.MvarItemKey = "WL_YayBillMain"; base.MvarReportTitle = "出运单"; base.BillType = "3321"; base.HBillSubType = "3321"; } #region 固定代码 ~ClsWL_YayBillMain() { DetailColl = null; } #endregion 自定义方法 //修改单据 public override bool ModifyBill(Int64 lngBillKey, ref string sReturn) { try { // oCn.BeginTran(); //保存前控制========================================= string HBillNote = ""; DataSet ds = oCn.RunProcReturn("Exec h_p_Gy_QCVerificationBill_BeforeSaveCtrl " + omodel.HInterID.ToString() + ", '" + omodel.HBillNo + "','" + HBillNote + "',2 ", "h_p_Gy_QCVerificationBill_BeforeSaveCtrl"); if (ds == null) { sReturn = "保存前判断失败！"; return false; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { sReturn = "保存失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); return false; } //========================================================= //更新主表 oCn.RunProc("UpDate WL_YayBillMain set " + " HBillNo='" + omodel.HBillNo + "'" + //固定赋值=============== ",HDate='" + omodel.HDate + "'" + ",HYear='" + omodel.HYear.ToString() + "'" + ",HPeriod='" + omodel.HPeriod.ToString() + "'" + ",HRemark='" + omodel.HRemark + "'" + ",HUpDater='" + DBUtility.ClsPub.CurUserName + "'" + ",HUpDateDate=getdate()" + //======================================== ",HChecker='" + omodel.HChecker.ToString()+ "'" + ",HMainSourceBillType='" + omodel.HMainSourceBillType + "'" + ",HMainSourceBillNo='" + omodel.HMainSourceBillNo + "'" + ",HMainSourceInterID='" + omodel.HMainSourceInterID.ToString() + "'" + ",HMainSourceEntryID='" + omodel.HMainSourceEntryID.ToString() + "'" + ",HCusID='" + omodel.HCusID.ToString() + "'" + ",HBeginAddr='" + omodel.HBeginAddr.ToString() + "'" + ",HEndAddr='" + omodel.HEndAddr.ToString() + "'" + ",HPickAddr='" + omodel.HPickAddr.ToString() + "'" + ",HArriverAddr='" + omodel.HArriverAddr.ToString() + "'" + ",HTransType='" + omodel.HTransType.ToString() + "'" + ",HCarTypeID='" + omodel.HCarTypeID.ToString() + "'" + ",HCarTypeMaxVolume='" + omodel.HCarTypeMaxVolume.ToString() + "'" + ",HTotalVolume='" + omodel.HTotalVolume.ToString() + "'" + ",HLoadingRate='" + omodel.HLoadingRate.ToString() + "'" + ",HContractTransportInterID='" + omodel.HContractTransportInterID.ToString() + "'" + ",HContractTransportEntryID='" + omodel.HContractTransportEntryID.ToString() + "'" + ",HContractTransportBillNo='" + omodel.HContractTransportBillNo.ToString() + "'" + ",HCarrierID='" + omodel.HCarrierID.ToString() + "'" + ",HDistance='" + omodel.HDistance.ToString() + "'" + ",HTransportTimes='" + omodel.HTransportTimes.ToString() + "'" + ",HTransportTyep='" + omodel.HTransportTyep.ToString() + "'" + ",HSSID='" + omodel.HSSID.ToString() + "'" + ",HCurID='" + omodel.HCurID.ToString() + "'" + ",HMoney='" + omodel.HMoney.ToString() + "'" + ",HDateForRequestedPick='" + omodel.HDateForRequestedPick.ToString() + "'" + ",HDataForRequestedArrived='" + omodel.HDataForRequestedArrived.ToString() + "'" + ",HCarryStatus='" + omodel.HCarryStatus.ToString() + "'" + ",HDate_CarrierSure='" + omodel.HDate_CarrierSure.ToString() + "'" + ",HDate_AllocationDriver='" + omodel.HDate_AllocationDriver.ToString() + "'" + ",HCarID='" + omodel.HCarID.ToString() + "'" + ",HDriverID='" + omodel.HDriverID.ToString() + "'" + ",HSurer_Arrive='" + omodel.HSurer_Arrive.ToString() + "'" + ",HDate_Arrive='" + omodel.HDate_Arrive.ToString() + "'" + ",HSurer_Pick='" + omodel.HSurer_Pick.ToString() + "'" + ",HDate_Pick='" + omodel.HDate_Pick.ToString() + "'" + ",HSurer_Trans='" + omodel.HSurer_Trans.ToString() + "'" + ",HDate_Trans='" + omodel.HDate_Trans.ToString() + "'" + ",HSurer_Receive='" + omodel.HSurer_Receive.ToString() + "'" + ",HDate_Receive='" + omodel.HDate_Receive.ToString() + "'" + ",HSurer_Accounting='" + omodel.HSurer_Accounting.ToString() + "'" + ",HDate_Accounting='" + omodel.HDate_Accounting.ToString() + "'" + " where HInterID=" + lngBillKey.ToString()); //删除关联 DeleteRelation(ref sReturn, lngBillKey); oCn.RunProc("delete from WL_YayBillSub_Material where HInterID=" + lngBillKey); //插入子表 omodel.HInterID = lngBillKey; string sql = ""; foreach (Model.ClsWL_YayBillMainBillSub oSub in DetailColl) { sql = "Insert into WL_YayBillSub_Material " + "(HInterID,HBillNo_bak,HEntryID,HCloseMan,HEntryCloseDate,HCloseType,HRemark" + ",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HMaterID,HUnitID,HMouldID,HMouldLength,HMouldHeight,HMouldWidth" + ",HMouldFoldHeight,HVolume,HQty,HSNP,HScatteredVolume,HMouldQty) values(" + SafeInt(omodel.HInterID.ToString(), 0) + ",'" // HInterID int + SafeString(omodel.HBillNo.ToString()) + "'," // HBillNo_bak varchar(50) + (oSub.HEntryID) + ",'" // HEntryID int + SafeString(oSub.HCloseMan?.ToString()) + "','" // HCloseMan varchar(20) + (oSub.HEntryCloseDate == null ? DateTime.Now : oSub.HEntryCloseDate) + "'," // HEntryCloseDate datetime + (oSub.HCloseType ? "1" : "0") + ",'" // HCloseType bit + SafeString(oSub.HRemark?.ToString() ?? "") + "'," // HRemark varchar(200) + (oSub.HSourceInterID == null ? 0 : oSub.HSourceInterID) + "," // HSourceInterID int + (oSub.HSourceEntryID == null ? 0 : oSub.HSourceEntryID) + ",'" // HSourceEntryID int + SafeString(oSub.HSourceBillNo?.ToString(), "") + "','" // HSourceBillNo varchar(50) + SafeString(oSub.HSourceBillType?.ToString(), "") + "'," // HSourceBillType varchar(10) + (oSub.HRelationQty == null ? 0 : oSub.HRelationQty) + "," // HRelationQty dec(18,8) + (oSub.HRelationMoney) + "," // HRelationMoney dec(18,8) + (oSub.HMaterID) + "," // HMaterID int + (oSub.HUnitID.ToString() == "" ? 0 : oSub.HUnitID) + "," // HUnitID int + (oSub.HMouldID) + "," // HMouldID int + (oSub.HMouldLength.ToString() == "" ? 0 : oSub.HMouldLength) + "," // HMouldLength dec(18,8) + (oSub.HMouldHeight.ToString() == "" ? 0 : oSub.HMouldHeight) + "," // HMouldHeight dec(18,8) + (oSub.HMouldWidth.ToString() == "" ? 0 : oSub.HMouldWidth) + "," // HMouldWidth dec(18,8) + (oSub.HMouldFoldHeight.ToString() == "" ? 0 : oSub.HMouldFoldHeight) + "," // HMouldFoldHeight dec(18,8) + (oSub.HVolume.ToString() == "" ? 0 : oSub.HVolume) + "," // HVolume dec(18,8) + (oSub.HQty.ToString() == "" ? 0 : oSub.HQty) + "," // HQty dec(18,8) + (oSub.HSNP.ToString() == "" ? 0 : oSub.HSNP) + "," // HSNP dec(18,8) + (oSub.HScatteredVolume) + "," // HSNP dec(18,8) + (oSub.HMouldQty.ToString() == "" ? 0 : oSub.HMouldQty) + ")"; // HMouldQty dec(18,8) oCn.RunProc(sql); } //=========================保存后控制 DataSet ds2 = oCn.RunProcReturn("Exec h_p_Gy_QCVerificationBill_AfterSaveCtrl " + omodel.HInterID.ToString() + ", '" + omodel.HBillNo + "',2 ", "h_p_Gy_QCVerificationBill_AfterSaveCtrl"); if (ds2 == null) { sReturn = "保存后控制判断失败！"; oCn.RollBack(); return false; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { sReturn = "保存失败2！" + DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBackRemark"]); oCn.RollBack(); return false; } //============================ sReturn = "修改单据成功！"; oCn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); throw (e); } } //新增单据 public override bool AddBill(ref string sReturn) { try { //得到mainid omodel.HInterID = DBUtility.ClsPub.CreateBillID(BillType, ref DBUtility.ClsPub.sExeReturnInfo); //保存前控制========================================= string HBillNote = ""; DataSet ds = oCn.RunProcReturn("Exec h_p_Gy_QCVerificationBill_BeforeSaveCtrl " + omodel.HInterID.ToString() + ", '" + omodel.HBillNo + "','" + HBillNote + "',1 ", "h_p_Gy_QCVerificationBill_BeforeSaveCtrl"); if (ds == null) { sReturn = "保存前判断失败！"; return false; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { sReturn = "保存失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); return false; } //========================================================= oCn.BeginTran(); // 获取当前日期时间，用于空日期字段的默认值 // 获取当前日期时间 string currentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); // 辅助方法：安全转换为字符串，处理null和空字符串 string sql = "Insert Into WL_YayBillMain " + "(HInterID,HYear,HPeriod,HBillType,HBillSubType,HDate,HBillNo,HBillStatus," + "HCheckItemNowID,HCheckItemNextID,HCheckFlowID,HRemark,HBacker,HBackDate," + "HBackRemark,HChecker,HCheckDate,HMaker,HMakeDate,HUpDater,HUpDateDate," + "HCloseMan,HCloseDate,HCloseType,HDeleteMan,HDeleteDate," + "HMainSourceBillType,HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo," + "HPrintQty,HOrgID,HCusID,HBeginAddr,HEndAddr,HPickAddr," + "HArriverAddr,HTransType,HCarTypeID,HCarTypeMaxVolume,HTotalVolume," + "HLoadingRate,HContractTransportInterID,HContractTransportEntryID," + "HContractTransportBillNo,HCarrierID,HDistance,HTransportTimes," + "HTransportTyep,HSSID,HCurID,HMoney,HDateForRequestedPick," + "HDataForRequestedArrived,HCarryStatus,HDate_CarrierSure," + "HDate_AllocationDriver,HCarID,HDriverID,HSurer_Arrive,HDate_Arrive," + "HSurer_Pick,HDate_Pick,HSurer_Trans,HDate_Trans,HSurer_Receive," + "HDate_Receive,HSurer_Accounting,HDate_Accounting) " + "values(" + SafeInt(omodel.HInterID.ToString(), 0) + "," + // HInterID "0,0," + // HYear, HPeriod - 需要从HDate计算，这里先给0 "'" + SafeString(this.BillType) + "'," + "'" + SafeString(this.HBillSubType) + "'," + SafeDateTime(omodel.HDate.ToString(), "'" + currentDateTime + "'") + "," + "'" + SafeString(omodel.HBillNo) + "'," + SafeInt(omodel.HBillStatus.ToString(), 1) + "," + // HBillStatus SafeInt(omodel.HCheckItemNowID.ToString(), 0) + "," + // HCheckItemNowID SafeInt(omodel.HCheckItemNextID.ToString(), 0) + "," + // HCheckItemNextID SafeInt(omodel.HCheckFlowID.ToString(), 0) + "," + // HCheckFlowID "'" + SafeString(omodel.HRemark) + "'," + "'" + SafeString(omodel.HBacker) + "'," + SafeDateTime(omodel.HBackDate?.ToString(), "NULL") + "," + "'" + SafeString(omodel.HBackRemark) + "'," + "'" + SafeString(omodel.HChecker) + "'," + SafeDateTime(omodel.HCheckDate?.ToString(), "NULL") + "," + "'" + SafeString(omodel.HMaker) + "',getdate()," + // HMaker, HMakeDate "'" + SafeString(omodel.HUpDater) + "'," + SafeDateTime(omodel.HUpDateDate?.ToString(), "NULL") + "," + "'" + SafeString(omodel.HCloseMan) + "'," + SafeDateTime(omodel.HCloseDate?.ToString(), "NULL") + "," + (omodel.HCloseType.ToString() == "1" ? "1" : "0") + "," + // HCloseType "'" + SafeString(omodel.HDeleteMan) + "'," + SafeDateTime(omodel.HDeleteDate?.ToString(), "NULL") + "," + "'" + SafeString(omodel.HMainSourceBillType) + "'," + SafeInt(omodel.HMainSourceInterID.ToString(), 0) + "," + // HMainSourceInterID SafeInt(omodel.HMainSourceEntryID.ToString(), 0) + "," + // HMainSourceEntryID "'" + SafeString(omodel.HMainSourceBillNo) + "'," + SafeInt(omodel.HPrintQty.ToString(), 0) + "," + // HPrintQty SafeInt(omodel.HOrgID.ToString(), 0) + "," + // HOrgID SafeInt(omodel.HCusID?.ToString(), 0) + "," + // HCusID SafeInt(omodel.HBeginAddr?.ToString(), 0) + "," + // HBeginAddr SafeInt(omodel.HEndAddr?.ToString(), 0) + "," + // HEndAddr "'" + SafeString(omodel.HPickAddr) + "'," + "'" + SafeString(omodel.HArriverAddr) + "'," + "'" + SafeString(omodel.HTransType) + "'," + SafeInt(omodel.HCarTypeID?.ToString(), 0) + "," + // HCarTypeID SafeDecimal(omodel.HCarTypeMaxVolume?.ToString(), 0) + "," + // HCarTypeMaxVolume SafeDecimal(omodel.HTotalVolume?.ToString(), 0) + "," + // HTotalVolume SafeDecimal(omodel.HLoadingRate?.ToString(), 0) + "," + // HLoadingRate SafeInt(omodel.HContractTransportInterID?.ToString(), 0) + "," + // HContractTransportInterID SafeInt(omodel.HContractTransportEntryID?.ToString(), 0) + "," + // HContractTransportEntryID "'" + SafeString(omodel.HContractTransportBillNo) + "'," + SafeInt(omodel.HCarrierID?.ToString(), 0) + "," + // HCarrierID SafeDecimal(omodel.HDistance?.ToString(), 0) + "," + // HDistance SafeDecimal(omodel.HTransportTimes?.ToString(), 0) + "," + // HTransportTimes "'" + SafeString(omodel.HTransportTyep) + "'," + SafeInt(omodel.HSSID?.ToString(), 0) + "," + // HSSID SafeInt(omodel.HCurID?.ToString(), 0) + "," + // HCurID SafeDecimal(omodel.HMoney?.ToString(), 0) + "," + // HMoney SafeDateTime(omodel.HDateForRequestedPick?.ToString(), "'" + currentDateTime + "'") + "," + SafeDateTime(omodel.HDataForRequestedArrived?.ToString(), "'" + currentDateTime + "'") + "," + SafeInt(omodel.HCarryStatus?.ToString(), 1) + "," + // HCarryStatus SafeDateTime(omodel.HDate_CarrierSure?.ToString(), "'" + currentDateTime + "'") + "," + SafeDateTime(omodel.HDate_AllocationDriver?.ToString(), "'" + currentDateTime + "'") + "," + SafeInt(omodel.HCarID?.ToString(), 0) + "," + // HCarID SafeInt(omodel.HDriverID?.ToString(), 0) + "," + // HDriverID "'" + SafeString(omodel.HSurer_Arrive) + "'," + SafeDateTime(omodel.HDate_Arrive?.ToString(), "'" + currentDateTime + "'") + "," + "'" + SafeString(omodel.HSurer_Pick) + "'," + SafeDateTime(omodel.HDate_Pick?.ToString(), "'" + currentDateTime + "'") + "," + "'" + SafeString(omodel.HSurer_Trans) + "'," + SafeDateTime(omodel.HDate_Trans?.ToString(), "'" + currentDateTime + "'") + "," + "'" + SafeString(omodel.HSurer_Receive) + "'," + SafeDateTime(omodel.HDate_Receive?.ToString(), "'" + currentDateTime + "'") + "," + "'" + SafeString(omodel.HSurer_Accounting) + "'," + SafeDateTime(omodel.HDate_Accounting?.ToString(), "'" + currentDateTime + "'") + ")"; oCn.RunProc(sql); //主表 //插入子表 foreach (Model.ClsWL_YayBillMainBillSub oSub in DetailColl) { sql = "Insert into WL_YayBillSub_Material " + "(HInterID,HBillNo_bak,HEntryID,HCloseMan,HEntryCloseDate,HCloseType,HRemark" + ",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HMaterID,HUnitID,HMouldID,HMouldLength,HMouldHeight,HMouldWidth" + ",HMouldFoldHeight,HVolume,HQty,HSNP,HScatteredVolume,HMouldQty) values(" + SafeInt(omodel.HInterID.ToString(), 0) + ",'" // HInterID int + SafeString(omodel.HBillNo.ToString()) + "'," // HBillNo_bak varchar(50) + (oSub.HEntryID) + ",'" // HEntryID int + SafeString(oSub.HCloseMan?.ToString()) + "','" // HCloseMan varchar(20) + (oSub.HEntryCloseDate == null ? DateTime.Now : oSub.HEntryCloseDate) + "'," // HEntryCloseDate datetime + (oSub.HCloseType ? "1" : "0") + ",'" // HCloseType bit + SafeString(oSub.HRemark?.ToString() ?? "") + "'," // HRemark varchar(200) + (oSub.HSourceInterID == null ? 0 : oSub.HSourceInterID) + "," // HSourceInterID int + (oSub.HSourceEntryID == null ? 0 : oSub.HSourceEntryID) + ",'" // HSourceEntryID int + SafeString(oSub.HSourceBillNo?.ToString(), "") + "','" // HSourceBillNo varchar(50) + SafeString(oSub.HSourceBillType?.ToString(), "") + "'," // HSourceBillType varchar(10) + (oSub.HRelationQty == null ? 0 : oSub.HRelationQty) + "," // HRelationQty dec(18,8) + (oSub.HRelationMoney) + "," // HRelationMoney dec(18,8) + (oSub.HMaterID) + "," // HMaterID int + (oSub.HUnitID.ToString() == "" ? 0 : oSub.HUnitID) + "," // HUnitID int + (oSub.HMouldID) + "," // HMouldID int + (oSub.HMouldLength.ToString() == "" ? 0 : oSub.HMouldLength) + "," // HMouldLength dec(18,8) + (oSub.HMouldHeight.ToString() == "" ? 0 : oSub.HMouldHeight) + "," // HMouldHeight dec(18,8) + (oSub.HMouldWidth.ToString() == "" ? 0 : oSub.HMouldWidth) + "," // HMouldWidth dec(18,8) + (oSub.HMouldFoldHeight.ToString() == "" ? 0 : oSub.HMouldFoldHeight) + "," // HMouldFoldHeight dec(18,8) + (oSub.HVolume.ToString() == "" ? 0 : oSub.HVolume) + "," // HVolume dec(18,8) + (oSub.HQty.ToString() == "" ? 0 : oSub.HQty) + "," // HQty dec(18,8) + (oSub.HSNP.ToString() == "" ? 0 : oSub.HSNP) + "," // HSNP dec(18,8) + (oSub.HScatteredVolume) + "," // HSNP dec(18,8) + (oSub.HMouldQty.ToString() == "" ? 0 : oSub.HMouldQty) + ")"; // HMouldQty dec(18,8) oCn.RunProc(sql); } //=========================保存后控制 DataSet ds2 = oCn.RunProcReturn("Exec h_p_Gy_QCVerificationBill_AfterSaveCtrl " + omodel.HInterID.ToString() + ", '" + omodel.HBillNo + "',1", "h_p_Gy_QCVerificationBill_AfterSaveCtrl"); if (ds2 == null) { sReturn = "保存后控制判断失败！"; oCn.RollBack(); return false; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { sReturn = "保存失败2！" + DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBackRemark"]); oCn.RollBack(); return false; } //============================ sReturn = "新增单据成功！"; oCn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); throw (e); } } } }