/// <summary> /// 更新生产任务汇报单和各明细信息 /// </summary> /// <param name="model"></param> /// <param name="modelProduct"></param> /// <param name="modelStaff"></param> /// <param name="modelMachine"></param> /// <param name="modelMeterial"></param> /// <returns></returns> public static bool UpdateManufactureReportInfo(ManufactureReportModel model, Hashtable ht, ManufactureReportProductModel modelProduct, ManufactureReportStaffModel modelStaff, ManufactureReportMachineModel modelMachine, ManufactureReportMeterialModel modelMeterial) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; try { bool succ = false; string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; LogInfoModel logModel = InitLogInfo(model.ReportNo, 0); logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; succ = ManufactureReportDBHelper.UpdateManufactureReportInfo(model, ht, modelProduct, modelStaff, modelMachine, modelMeterial, loginUserID); if (!succ) logModel.Remark = ConstUtil.LOG_PROCESS_FAILED; else logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS; LogDBHelper.InsertLog(logModel); return succ; } catch (Exception ex) { WriteSystemLog(userInfo, 0, ex); return false; } }
/// <summary> /// 确认生产任务汇报单 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool ConfirmMenufactureReport(ManufactureReportModel model,int EditType) { string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; //string loginUserID = "admin";//[待修改] return ManufactureReportDBHelper.ConfirmMenufactureReport(model, loginUserID, EditType); }
/// <summary> /// 物料明细 /// </summary> /// <returns>DataTable</returns> public static DataTable GetManufactureReportMeterial(ManufactureReportModel model) { try { return ManufactureReportDBHelper.GetManufactureReportMeterial(model); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 通过检索条件查询生产任务汇报单信息 /// </summary> /// <returns>DataTable</returns> public static DataTable GetManufactureReportListBycondition(ManufactureReportModel model, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return ManufactureReportDBHelper.GetManufactureReportListBycondition(model, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(ManufactureReportModel model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.ManufactureReport set "; foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","; cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim()); } int iLength = strSql.Length - 1; strSql = strSql.Substring(0, iLength); strSql += " where CompanyCD = @CompanyCD AND ReportNo = @ReportNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@ReportNo", model.ReportNo); cmd.CommandText = strSql; } catch (Exception) { } }
/// <summary> /// 生产任务汇报单插入 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertManufactureReport(ManufactureReportModel model, Hashtable htExtAttr, ManufactureReportProductModel modelProduct, ManufactureReportStaffModel modelStaff, ManufactureReportMachineModel modelMachine, ManufactureReportMeterialModel modelMeterial, string loginUserID, out string ID) { ArrayList listADD = new ArrayList(); bool result = false; //#region 传参 try { #region 生产任务汇报单添加SQL语句 StringBuilder sqlReport = new StringBuilder(); sqlReport.AppendLine("INSERT INTO officedba.ManufactureReport"); sqlReport.AppendLine(" (CompanyCD"); sqlReport.AppendLine(" ,ReportNo"); sqlReport.AppendLine(" ,Subject"); sqlReport.AppendLine(" ,TaskNo"); sqlReport.AppendLine(" ,DeptID"); sqlReport.AppendLine(" ,DailyDate"); if (model.PlanHRs>0) { sqlReport.AppendLine(" ,PlanHRs"); } if (model.RealHRs>0) { sqlReport.AppendLine(" ,RealHRs"); } if (model.PlanWorkTime>0) { sqlReport.AppendLine(" ,PlanWorkTime"); } if (model.AddWorkTime>0) { sqlReport.AppendLine(" ,AddWorkTime"); } if (model.StopWorkTime>0) { sqlReport.AppendLine(" ,StopWorkTime"); } if (model.RealWorktime>0) { sqlReport.AppendLine(" ,RealWorktime"); } if (model.MachineCount>0) { sqlReport.AppendLine(" ,MachineCount"); } if (model.OpenCount>0) { sqlReport.AppendLine(" ,OpenCount "); } if (model.OpenTime>0) { sqlReport.AppendLine(" ,OpenTime"); } if (model.OpenPercent>0) { sqlReport.AppendLine(" ,OpenPercent"); } if (model.LoadPercent>0) { sqlReport.AppendLine(" ,LoadPercent"); } if (model.UsePercent>0) { sqlReport.AppendLine(" ,UsePercent"); } if (model.StopCount>0) { sqlReport.AppendLine(" ,StopCount"); } if (model.StopTime>0) { sqlReport.AppendLine(" ,StopTime"); } sqlReport.AppendLine(" ,StopReason"); if (model.ProductionTotal>0) { sqlReport.AppendLine(" ,ProductionTotal"); } if (model.WorkTimeTotal>0) { sqlReport.AppendLine(" ,WorkTimeTotal"); } sqlReport.AppendLine(" ,Reporter"); sqlReport.AppendLine(" ,ReportDate"); if (model.TakeNum>0) { sqlReport.AppendLine(" ,TakeNum"); } if (model.UsedNum>0) { sqlReport.AppendLine(" ,UsedNum "); } if (model.NowNum>0) { sqlReport.AppendLine(" ,NowNum"); } sqlReport.AppendLine(" ,Remark"); sqlReport.AppendLine(" ,BillStatus"); sqlReport.AppendLine(" ,Creator"); sqlReport.AppendLine(" ,CreateDate"); sqlReport.AppendLine(" ,ModifiedDate"); sqlReport.AppendLine(" ,ModifiedUserID)"); sqlReport.AppendLine(" VALUES "); sqlReport.AppendLine(" (@CompanyCD "); sqlReport.AppendLine(" ,@ReportNo"); sqlReport.AppendLine(" ,@Subject"); sqlReport.AppendLine(" ,@TaskNo"); sqlReport.AppendLine(" ,@DeptID "); sqlReport.AppendLine(" ,@DailyDate "); if (model.PlanHRs>0) { sqlReport.AppendLine(" ,@PlanHRs"); } if (model.RealHRs>0) { sqlReport.AppendLine(" ,@RealHRs"); } if (model.PlanWorkTime>0) { sqlReport.AppendLine(" ,@PlanWorkTime"); } if (model.AddWorkTime>0) { sqlReport.AppendLine(" ,@AddWorkTime"); } if (model.StopWorkTime>0) { sqlReport.AppendLine(" ,@StopWorkTime"); } if (model.RealWorktime>0) { sqlReport.AppendLine(" ,@RealWorktime"); } if (model.MachineCount>0) { sqlReport.AppendLine(" ,@MachineCount"); } if (model.OpenCount>0) { sqlReport.AppendLine(" ,@OpenCount"); } if (model.OpenTime>0) { sqlReport.AppendLine(" ,@OpenTime"); } if (model.OpenPercent>0) { sqlReport.AppendLine(" ,@OpenPercent"); } if (model.LoadPercent>0) { sqlReport.AppendLine(" ,@LoadPercent"); } if (model.UsePercent>0) { sqlReport.AppendLine(" ,@UsePercent"); } if (model.StopCount>0) { sqlReport.AppendLine(" ,@StopCount"); } if (model.StopTime>0) { sqlReport.AppendLine(" ,@StopTime"); } sqlReport.AppendLine(" ,@StopReason"); if (model.ProductionTotal>0) { sqlReport.AppendLine(" ,@ProductionTotal"); } if (model.WorkTimeTotal>0) { sqlReport.AppendLine(" ,@WorkTimeTotal"); } sqlReport.AppendLine(" ,@Reporter"); sqlReport.AppendLine(" ,@ReportDate"); if (model.TakeNum>0) { sqlReport.AppendLine(" ,@TakeNum"); } if (model.UsedNum>0) { sqlReport.AppendLine(" ,@UsedNum"); } if (model.NowNum>0) { sqlReport.AppendLine(" ,@NowNum"); } sqlReport.AppendLine(" ,@Remark "); sqlReport.AppendLine(" ,1"); sqlReport.AppendLine(" ,@Creator"); sqlReport.AppendLine(" ,@CreateDate "); sqlReport.AppendLine(" ,getdate()"); sqlReport.AppendLine(" ,'"+loginUserID+"')"); sqlReport.AppendLine("set @ID=@@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlReport.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@DailyDate", model.DailyDate)); if (model.PlanHRs>0) { comm.Parameters.Add(SqlHelper.GetParameter("@PlanHRs", model.PlanHRs)); } if (model.RealHRs>0) { comm.Parameters.Add(SqlHelper.GetParameter("@RealHRs", model.RealHRs)); } if (model.PlanWorkTime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@PlanWorkTime", model.PlanWorkTime)); } if (model.AddWorkTime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@AddWorkTime", model.AddWorkTime)); } if (model.StopWorkTime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@StopWorkTime", model.StopWorkTime)); } if (model.RealWorktime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@RealWorkTime", model.RealWorktime)); } if (model.MachineCount>0) { comm.Parameters.Add(SqlHelper.GetParameter("@MachineCount", model.MachineCount)); } if (model.OpenCount>0) { comm.Parameters.Add(SqlHelper.GetParameter("@OpenCount", model.OpenCount)); } if (model.OpenTime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@OpenTime", model.OpenTime)); } if (model.OpenPercent>0) { comm.Parameters.Add(SqlHelper.GetParameter("@OpenPercent", model.OpenPercent)); } if (model.LoadPercent>0) { comm.Parameters.Add(SqlHelper.GetParameter("@LoadPercent", model.LoadPercent)); } if (model.UsePercent>0) { comm.Parameters.Add(SqlHelper.GetParameter("@UsePercent", model.UsePercent)); } if (model.StopCount>0) { comm.Parameters.Add(SqlHelper.GetParameter("@StopCount", model.StopCount)); } if (model.StopTime>0) { comm.Parameters.Add(SqlHelper.GetParameter("@StopTime", model.StopTime)); } comm.Parameters.Add(SqlHelper.GetParameter("@StopReason", model.StopReason)); if (model.ProductionTotal>0) { comm.Parameters.Add(SqlHelper.GetParameter("@ProductionTotal", model.ProductionTotal)); } if (model.WorkTimeTotal>0) { comm.Parameters.Add(SqlHelper.GetParameter("@WorkTimeTotal", model.WorkTimeTotal)); } comm.Parameters.Add(SqlHelper.GetParameter("@Reporter", model.Reporter)); comm.Parameters.Add(SqlHelper.GetParameter("@ReportDate", model.ReportDate)); if (model.TakeNum>0) { comm.Parameters.Add(SqlHelper.GetParameter("@TakeNum", model.TakeNum)); } if (model.UsedNum>0) { comm.Parameters.Add(SqlHelper.GetParameter("@UsedNum", model.UsedNum)); } if (model.NowNum>0) { comm.Parameters.Add(SqlHelper.GetParameter("@NowNum", model.NowNum)); } comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate)); comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); listADD.Add(comm); #endregion #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); #endregion #region 生产明细添加SQL语句 if (modelProduct.ProductID.Length > 0) { string[] detProductID = modelProduct.ProductID.Split(','); string[] detWorkTime = modelProduct.WorkTime.Split(','); string[] detFinishNum = modelProduct.FinishNum.Split(','); string[] detPassNum = modelProduct.PassNum.Split(','); string[] detPassPercent = modelProduct.PassPercent.Split(','); string[] detFromBillNo = modelProduct.FromBillNo.Split(','); string[] detFromBillID = modelProduct.FromBillID.Split(','); string[] detFromLineNo = modelProduct.FromLineNo.Split(','); for (int i = 0; i < detProductID.Length; i++) { StringBuilder sqlProduct = new StringBuilder(); sqlProduct.AppendLine("INSERT INTO officedba.ManufactureReportProduct"); sqlProduct.AppendLine(" (ReportNo "); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,ProductID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlProduct.AppendLine(" ,WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlProduct.AppendLine(" ,PassPercent"); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromBillNo"); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromBillID"); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromLineNo"); } } sqlProduct.AppendLine(" ,CompanyCD)"); sqlProduct.AppendLine(" VALUES"); sqlProduct.AppendLine(" (@ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@ProductID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@PassPercent"); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromBillNo"); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromBillID"); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromLineNo"); } } sqlProduct.AppendLine(" ,@CompanyCD)"); SqlCommand commProduct = new SqlCommand(); commProduct.CommandText = sqlProduct.ToString(); commProduct.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commProduct.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i].ToString())); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@WorkTime", detWorkTime[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", detFromBillNo[i].ToString())); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromBillID", detFromBillID[i].ToString())); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", detFromLineNo[i].ToString())); } } listADD.Add(commProduct); } } #endregion #region 人员明细添加SQL语句 if (modelStaff.StaffID.Length > 0) { string[] detStaffID = modelStaff.StaffID.Split(','); string[] detWorkTime = modelStaff.WorkTime.Split(','); string[] detFinishNum = modelStaff.FinishNum.Split(','); string[] detPassNum = modelStaff.PassNum.Split(','); string[] detPassPercent = modelStaff.PassPercent.Split(','); for (int i = 0; i < detStaffID.Length; i++) { StringBuilder sqlStaff = new StringBuilder(); sqlStaff.AppendLine("INSERT INTO officedba.ManufactureReportStaff "); sqlStaff.AppendLine(" (ReportNo"); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { sqlStaff.AppendLine(" ,StaffID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlStaff.AppendLine(" ,WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlStaff.AppendLine(" ,PassPercent"); } } sqlStaff.AppendLine(" ,CompanyCD)"); sqlStaff.AppendLine(" VALUES"); sqlStaff.AppendLine(" (@ReportNo"); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@StaffID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@PassPercent"); } } sqlStaff.AppendLine(" ,@CompanyCD)"); SqlCommand commStaff = new SqlCommand(); commStaff.CommandText = sqlStaff.ToString(); commStaff.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commStaff.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@StaffID", detStaffID[i].ToString())); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@WorkTime", detWorkTime[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } listADD.Add(commStaff); } } #endregion #region 设备明细添加SQL语句 if (modelMachine.MachineID.Length > 0 && modelMachine.MachineNo.Length>0 && modelMachine.MachineName.Length>0) { string[] detMachineID = modelMachine.MachineID.Split(','); string[] detMachineNo = modelMachine.MachineNo.Split(','); string[] detMachineName = modelMachine.MachineName.Split(','); string[] detUseHour = modelMachine.UseHour.Split(','); string[] detFinishNum = modelMachine.FinishNum.Split(','); string[] detPassNum = modelMachine.PassNum.Split(','); string[] detPassPercent = modelMachine.PassPercent.Split(','); for (int i = 0; i < detMachineID.Length; i++) { StringBuilder sqlMachine = new StringBuilder(); sqlMachine.AppendLine("INSERT INTO officedba.ManufactureReportMachine"); sqlMachine.AppendLine(" (ReportNo"); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineID "); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineNo "); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineName "); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { sqlMachine.AppendLine(" ,UseHour "); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,PassNum "); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlMachine.AppendLine(" ,PassPercent"); } } sqlMachine.AppendLine(" ,CompanyCD) "); sqlMachine.AppendLine(" VALUES "); sqlMachine.AppendLine(" (@ReportNo "); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineID "); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineNo "); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineName "); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@UseHour "); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@PassNum "); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@PassPercent"); } } sqlMachine.AppendLine(" ,@CompanyCD)"); SqlCommand commMachine = new SqlCommand(); commMachine.CommandText = sqlMachine.ToString(); commMachine.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commMachine.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineID", detMachineID[i].ToString())); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineNo", detMachineNo[i].ToString())); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineName", detMachineName[i].ToString())); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@UseHour", detUseHour[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } listADD.Add(commMachine); } } #endregion #region 物料明细添加SQL语句 if (modelMeterial.ProductID.Length > 0) { string[] detProductID = modelMeterial.ProductID.Split(','); string[] detTakeNum =modelMeterial.TakeNum.Split(','); string[] detBeforeNum =modelMeterial.BeforeNum.Split(','); string[] detUsedNum =modelMeterial.UsedNum.Split(','); string[] detBadNum = modelMeterial.BadNum.Split(','); string[] detNowNum = modelMeterial.NowNum.Split(','); for (int i = 0; i < detProductID.Length; i++) { StringBuilder sqlMeterial = new StringBuilder(); sqlMeterial.AppendLine("INSERT INTO officedba.ManufactureReportMeterial"); sqlMeterial.AppendLine(" (ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,ProductID"); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,TakeNum"); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,BeforeNum"); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,UsedNum"); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,BadNum"); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,NowNum"); } } sqlMeterial.AppendLine(" ,CompanyCD)"); sqlMeterial.AppendLine(" VALUES "); sqlMeterial.AppendLine(" (@ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@ProductID"); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@TakeNum "); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@BeforeNum "); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@UsedNum "); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@BadNum"); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@NowNum"); } } sqlMeterial.AppendLine(" ,@CompanyCD)"); SqlCommand commMeterial = new SqlCommand(); commMeterial.CommandText = sqlMeterial.ToString(); commMeterial.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commMeterial.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i].ToString())); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@TakeNum", detTakeNum[i].ToString())); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@BeforeNum", detBeforeNum[i].ToString())); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@UsedNum", detUsedNum[i].ToString())); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@BadNum", detBadNum[i].ToString())); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@NowNum", detNowNum[i].ToString())); } } listADD.Add(commMeterial); } } #endregion if (SqlHelper.ExecuteTransWithArrayList(listADD)) { ID = comm.Parameters["@ID"].Value.ToString(); result = true; } else { ID = "0"; } return result; } catch (Exception ex) { throw ex; } }
/// <summary> /// 通过检索条件查询生产任务汇报单信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetManufactureReportListBycondition(ManufactureReportModel model, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select * from ("); searchSql.AppendLine(" select a.ID,a.CompanyCD,a.ReportNo,a.Subject,a.TaskNo,a.BillStatus,"); searchSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatusText,"); searchSql.AppendLine(" a.DeptID,b.DeptName,isnull( CONVERT(CHAR(10), a.DailyDate, 23),'') as DailyDate,"); searchSql.AppendLine(" isnull(Convert(varchar,Convert(numeric(22,"+userInfo.SelPoint+"),a.ProductionTotal) ),'') as ProductionTotal,isnull(Convert(varchar,Convert(numeric(22,"+userInfo.SelPoint+"),a.WorkTimeTotal) ),'') as WorkTimeTotal,"); searchSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,"); searchSql.AppendLine(" a.Reporter,c.EmployeeName as ReporterReal,"); searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ReportDate, 23),'') as ReportDate,a.ModifiedDate "); searchSql.AppendLine(" from officedba.ManufactureReport a"); searchSql.AppendLine(" left join officedba.DeptInfo b on a.DeptID=b.ID"); searchSql.AppendLine(" left join officedba.EmployeeInfo c on c.ID=a.Reporter"); searchSql.AppendLine(") as info "); searchSql.AppendLine("where CompanyCD=@CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //单据编号 if (!string.IsNullOrEmpty(model.ReportNo)) { searchSql.AppendLine("and ReportNo like @ReportNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportNo", "%" + model.ReportNo + "%")); } //单据主题 if (!string.IsNullOrEmpty(model.Subject)) { searchSql.AppendLine(" and Subject like @Subject"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%")); } //负责人 if (model.Reporter > 0) { searchSql.AppendLine(" and Reporter=@Reporter "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Reporter", model.Reporter.ToString())); } //单据状态 if (model.BillStatus > 0) { searchSql.AppendLine(" and BillStatus=@BillStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus.ToString())); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 确认生产任务汇报单 /// </summary> /// <param name="model"></param> /// <param name="loginUserID"></param> /// <returns></returns> public static bool ConfirmMenufactureReport(ManufactureReportModel model, string loginUserID,int EditType) { //确认时更新对应生产任务单明细中的已生产数量 ArrayList listADD = new ArrayList(); if (EditType > 0) { #region 更新单据状态 StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.ManufactureReport SET"); sql.AppendLine(" BillStatus = 2,"); sql.AppendLine(" Confirmor = @Confirmor,"); sql.AppendLine(" ConfirmDate = getdate(),"); sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sql.AppendLine(" Where ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor)); listADD.Add(comm); #endregion #region 加写生产任务单明细里的已生产数量 DataTable dbDet = GetManufactureReportProduct(model); if (dbDet.Rows.Count > 0) { for (int i = 0; i < dbDet.Rows.Count; i++) { //FinishNum FromBillNo,FromBillID if (!string.IsNullOrEmpty(dbDet.Rows[i]["FinishNum"].ToString()) && !string.IsNullOrEmpty(dbDet.Rows[i]["FromBillNo"].ToString()) && !string.IsNullOrEmpty(dbDet.Rows[i]["FromBillID"].ToString())) { //update officedba.ManufactureTaskDetail set ProductedCount= isnull(ProductedCount,0)+ @ProductedCount where CompanyCD=@CompanyCD and TaskNo=@FromBillNo and ID=@FromBillID Decimal finishNum = Decimal.Parse(dbDet.Rows[i]["FinishNum"].ToString()); string fromBillNo = dbDet.Rows[i]["FromBillNo"].ToString(); int fromBillID = int.Parse(dbDet.Rows[i]["FromBillID"].ToString()); #region 更新已生产数量 StringBuilder sqlTask = new StringBuilder(); sqlTask.AppendLine("Update officedba.ManufactureTaskDetail Set ProductedCount= isnull(ProductedCount,0)+ @ProductedCount where CompanyCD=@CompanyCD and TaskNo=@FromBillNo and ID=@FromBillID"); SqlCommand commTask = new SqlCommand(); commTask.CommandText = sqlTask.ToString(); commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commTask.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", fromBillNo)); commTask.Parameters.Add(SqlHelper.GetParameter("@FromBillID", fromBillID)); commTask.Parameters.Add(SqlHelper.GetParameter("@ProductedCount", finishNum)); listADD.Add(commTask); #endregion } } } #endregion } else { #region 更新单据状态 StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.ManufactureReport SET"); sql.AppendLine(" BillStatus = 1,"); sql.AppendLine(" Confirmor = null,"); sql.AppendLine(" ConfirmDate = null,"); sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sql.AppendLine(" Where ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); listADD.Add(comm); #endregion #region 减写生产任务单明细里的已生产数量 DataTable dbDet = GetManufactureReportProduct(model); if (dbDet.Rows.Count > 0) { for (int i = 0; i < dbDet.Rows.Count; i++) { //FinishNum FromBillNo,FromBillID if (!string.IsNullOrEmpty(dbDet.Rows[i]["FinishNum"].ToString()) && !string.IsNullOrEmpty(dbDet.Rows[i]["FromBillNo"].ToString()) && !string.IsNullOrEmpty(dbDet.Rows[i]["FromBillID"].ToString())) { //update officedba.ManufactureTaskDetail set ProductedCount= isnull(ProductedCount,0)+ @ProductedCount where CompanyCD=@CompanyCD and TaskNo=@FromBillNo and ID=@FromBillID Decimal finishNum = Decimal.Parse(dbDet.Rows[i]["FinishNum"].ToString()); string fromBillNo = dbDet.Rows[i]["FromBillNo"].ToString(); int fromBillID = int.Parse(dbDet.Rows[i]["FromBillID"].ToString()); #region 更新已生产数量 StringBuilder sqlTask = new StringBuilder(); sqlTask.AppendLine("Update officedba.ManufactureTaskDetail Set ProductedCount= isnull(ProductedCount,0)-@ProductedCount where CompanyCD=@CompanyCD and TaskNo=@FromBillNo and ID=@FromBillID"); SqlCommand commTask = new SqlCommand(); commTask.CommandText = sqlTask.ToString(); commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commTask.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", fromBillNo)); commTask.Parameters.Add(SqlHelper.GetParameter("@FromBillID", fromBillID)); commTask.Parameters.Add(SqlHelper.GetParameter("@ProductedCount", finishNum)); listADD.Add(commTask); #endregion } } } #endregion } return SqlHelper.ExecuteTransWithArrayList(listADD); }
/// <summary> /// 物料使用明细 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetManufactureReportMeterial(ManufactureReportModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select a.ProductID,b.ProdNo,b.ProductName,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.TakeNum) as TakeNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.BeforeNum) as BeforeNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.UsedNum) as UsedNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.BadNum) as BadNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.NowNum) as NowNum"); infoSql.AppendLine("from officedba. ManufactureReportMeterial a"); infoSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); infoSql.AppendLine("where a.CompanyCD=@CompanyCD and a.ReportNo=(select top 1 ReportNo from officedba.ManufactureReport where ID=@ID)"); infoSql.AppendLine("and a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = infoSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 设备明细 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetManufactureReportMachine(ManufactureReportModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select MachineID,b.EquipmentNo as MachineNo,b.EquipmentName as MachineName,UseHour,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),UsePercent) as UsePercent,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),FinishNum) as FinishNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),PassNum) as PassNum,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),PassPercent) as PassPercent"); infoSql.AppendLine("from officedba.ManufactureReportMachine a "); infoSql.AppendLine("left join officedba.EquipmentInfo b on a.MachineID=b.ID"); infoSql.AppendLine("where a.CompanyCD=@CompanyCD and ReportNo=(select top 1 ReportNo from officedba.ManufactureReport where ID=@ID)"); infoSql.AppendLine("and a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = infoSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 生产明细 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetManufactureReportProduct(ManufactureReportModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select a.ReportNo,a.ProductID,Convert(numeric(14," + userInfo.SelPoint + "),a.WorkTime) as WorkTime,Convert(numeric(14," + userInfo.SelPoint + "),a.FinishNum) as FinishNum,a.FromBillNo,a.FromBillID,a.FromLineNo,"); infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.PassNum) as PassNum,Convert(numeric(14,"+userInfo.SelPoint+"),a.PassPercent) as PassPercent,"); infoSql.AppendLine(" b.ProdNo,b.ProductName,"); if (userInfo.IsMoreUnit) { infoSql.AppendLine(" (Convert(numeric(14," + userInfo.SelPoint + "),isnull(c.UsedUnitCount,0)) - Convert(numeric(14," + userInfo.SelPoint + "),isnull(c.ProductedCount,0))) as UnFinishNum"); } else { infoSql.AppendLine(" (Convert(numeric(14," + userInfo.SelPoint + "),isnull(c.ProductCount,0)) - Convert(numeric(14," + userInfo.SelPoint + "),isnull(c.ProductedCount,0))) as UnFinishNum"); } infoSql.AppendLine("from officedba. ManufactureReportProduct a"); infoSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); infoSql.AppendLine("left join officedba.ManufactureTaskDetail c on a.FromBillNo=c.TaskNo and a.FromBillID=c.ID"); infoSql.AppendLine("where a.CompanyCD=@CompanyCD and a.ReportNo=(select top 1 ReportNo from officedba.ManufactureReport where ID=@ID)"); infoSql.AppendLine("and a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = infoSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 生产任务汇报单详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetManufactureReport(ManufactureReportModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select a.CompanyCD,a.ID,a.ReportNo,a.Subject,a.TaskNo,a.Confirmor,e.EmployeeName as ConfirmorReal,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,"); infoSql.AppendLine(" a.DeptID,d.DeptName,isnull( CONVERT(CHAR(10), a.DailyDate, 23),'') as DailyDate,a.PlanHRs,a.RealHRs,"); infoSql.AppendLine(" Convert(numeric(12," + userInfo.SelPoint + "),a.PlanWorkTime) as PlanWorkTime,Convert(numeric(12," + userInfo.SelPoint + "),a.AddWorkTime) as AddWorkTime,Convert(numeric(12," + userInfo.SelPoint + "),a.StopWorkTime) as StopWorkTime,"); infoSql.AppendLine(" Convert(numeric(12," + userInfo.SelPoint + "),a.RealWorkTime) as RealWorkTime,a.MachineCount,a.OpenCount,"); infoSql.AppendLine(" a.OpenTime,Convert(numeric(12,"+userInfo.SelPoint+"),a.OpenPercent) as OpenPercent,Convert(numeric(12,"+userInfo.SelPoint+"),a.LoadPercent) as LoadPercent,Convert(numeric(12,"+userInfo.SelPoint+"),a.UsePercent) as UsePercent,"); infoSql.AppendLine(" a.StopCount,a.StopTime,a.StopReason,Convert(numeric(12," + userInfo.SelPoint + "),a.ProductionTotal) as ProductionTotal,Convert(numeric(12," + userInfo.SelPoint + "),a.WorkTimeTotal) as WorkTimeTotal,"); infoSql.AppendLine(" a.Reporter,c.EmployeeName as ReporterReal,"); infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ReportDate, 23),'') as ReportDate,Convert(numeric(12,"+userInfo.SelPoint+"),a.TakeNum) as TakeNum,Convert(numeric(12,"+userInfo.SelPoint+"),a.UsedNum) as UsedNum,Convert(numeric(12,"+userInfo.SelPoint+"),a.NowNum) as NowNum,a.Remark,a.BillStatus,"); infoSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '结单' end as strBillStatusText,"); infoSql.AppendLine(" a.Creator,b.EmployeeName as CreatorReal,"); infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,"); infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID"); infoSql.AppendLine("from officedba.ManufactureReport a "); infoSql.AppendLine("left join officedba.EmployeeInfo b on a.Creator=b.ID"); infoSql.AppendLine("left join officedba.EmployeeInfo c on a.Reporter=c.ID"); infoSql.AppendLine("left join officedba.DeptInfo d on a.DeptID=d.ID"); infoSql.AppendLine("left join officedba.EmployeeInfo e on a.Confirmor=e.ID"); infoSql.AppendLine("where a.ID=@ID"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = infoSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 修改主生产任务汇报单和各明细信息 /// </summary> /// <param name="model"></param> /// <param name="modelProduct"></param> /// <param name="modelStaff"></param> /// <param name="modelMachine"></param> /// <param name="modelMeterial"></param> /// <param name="loginUserID"></param> /// <param name="ID"></param> /// <returns></returns> public static bool UpdateManufactureReportInfo(ManufactureReportModel model, Hashtable htExtAttr, ManufactureReportProductModel modelProduct, ManufactureReportStaffModel modelStaff, ManufactureReportMachineModel modelMachine, ManufactureReportMeterialModel modelMeterial, string loginUserID) { //获取登陆用户ID ArrayList listADD = new ArrayList(); if (model.ID <= 0) { return false; } #region 生产任务单修改SQL语句 StringBuilder sqlReport = new StringBuilder(); sqlReport.AppendLine("UPDATE officedba.ManufactureReport "); sqlReport.AppendLine(" SET Subject = @Subject "); sqlReport.AppendLine(" ,TaskNo = @TaskNo "); sqlReport.AppendLine(" ,DeptID = @DeptID "); sqlReport.AppendLine(" ,DailyDate = @DailyDate "); sqlReport.AppendLine(" ,PlanHRs = @PlanHRs "); sqlReport.AppendLine(" ,RealHRs = @RealHRs "); sqlReport.AppendLine(" ,PlanWorkTime = @PlanWorkTime "); sqlReport.AppendLine(" ,AddWorkTime = @AddWorkTime "); sqlReport.AppendLine(" ,StopWorkTime = @StopWorkTime "); sqlReport.AppendLine(" ,RealWorktime = @RealWorktime "); sqlReport.AppendLine(" ,MachineCount = @MachineCount "); sqlReport.AppendLine(" ,OpenCount = @OpenCount "); sqlReport.AppendLine(" ,OpenTime = @OpenTime "); sqlReport.AppendLine(" ,OpenPercent = @OpenPercent "); sqlReport.AppendLine(" ,LoadPercent = @LoadPercent "); sqlReport.AppendLine(" ,UsePercent = @UsePercent "); sqlReport.AppendLine(" ,StopCount = @StopCount "); sqlReport.AppendLine(" ,StopTime = @StopTime "); sqlReport.AppendLine(" ,StopReason = @StopReason "); sqlReport.AppendLine(" ,ProductionTotal = @ProductionTotal "); sqlReport.AppendLine(" ,WorkTimeTotal = @WorkTimeTotal "); sqlReport.AppendLine(" ,Reporter = @Reporter "); sqlReport.AppendLine(" ,ReportDate = @ReportDate "); sqlReport.AppendLine(" ,TakeNum = @TakeNum "); sqlReport.AppendLine(" ,UsedNum = @UsedNum "); sqlReport.AppendLine(" ,NowNum = @NowNum "); sqlReport.AppendLine(" ,Remark = @Remark "); sqlReport.AppendLine(" ,ModifiedDate = getdate() "); sqlReport.AppendLine(" ,ModifiedUserID ='"+loginUserID+"' "); sqlReport.AppendLine(" WHERE CompanyCD=@CompanyCD and ID=@ID "); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlReport.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@DailyDate", model.DailyDate)); comm.Parameters.Add(SqlHelper.GetParameter("@PlanHRs", model.PlanHRs)); comm.Parameters.Add(SqlHelper.GetParameter("@RealHRs", model.RealHRs)); comm.Parameters.Add(SqlHelper.GetParameter("@PlanWorkTime", model.PlanWorkTime)); comm.Parameters.Add(SqlHelper.GetParameter("@AddWorkTime", model.AddWorkTime)); comm.Parameters.Add(SqlHelper.GetParameter("@StopWorkTime", model.StopWorkTime)); comm.Parameters.Add(SqlHelper.GetParameter("@RealWorktime", model.RealWorktime)); comm.Parameters.Add(SqlHelper.GetParameter("@MachineCount", model.MachineCount)); comm.Parameters.Add(SqlHelper.GetParameter("@OpenCount", model.OpenCount)); comm.Parameters.Add(SqlHelper.GetParameter("@OpenTime", model.OpenTime)); comm.Parameters.Add(SqlHelper.GetParameter("@OpenPercent", model.OpenPercent)); comm.Parameters.Add(SqlHelper.GetParameter("@LoadPercent", model.LoadPercent)); comm.Parameters.Add(SqlHelper.GetParameter("@UsePercent", model.UsePercent)); comm.Parameters.Add(SqlHelper.GetParameter("@StopCount", model.StopCount)); comm.Parameters.Add(SqlHelper.GetParameter("@StopTime", model.StopTime)); comm.Parameters.Add(SqlHelper.GetParameter("@StopReason", model.StopReason)); comm.Parameters.Add(SqlHelper.GetParameter("@ProductionTotal", model.ProductionTotal)); comm.Parameters.Add(SqlHelper.GetParameter("@WorkTimeTotal", model.WorkTimeTotal)); comm.Parameters.Add(SqlHelper.GetParameter("@Reporter", model.Reporter)); comm.Parameters.Add(SqlHelper.GetParameter("@ReportDate", model.ReportDate)); comm.Parameters.Add(SqlHelper.GetParameter("@TakeNum", model.TakeNum)); comm.Parameters.Add(SqlHelper.GetParameter("@UsedNum", model.UsedNum)); comm.Parameters.Add(SqlHelper.GetParameter("@NowNum", model.NowNum)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); listADD.Add(comm); #endregion #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); #endregion #region 生产明细处理 #region 删除生产明细 StringBuilder sqlProductDel = new StringBuilder(); sqlProductDel.AppendLine("delete from officedba.ManufactureReportProduct "); sqlProductDel.AppendLine("where CompanyCD=@CompanyCD"); sqlProductDel.AppendLine("and ReportNo=("); sqlProductDel.AppendLine(" select top 1 ReportNo from officedba.ManufactureReport where CompanyCD=@CompanyCD and ID=@ID"); sqlProductDel.AppendLine(" )"); SqlCommand commProductDel = new SqlCommand(); commProductDel.CommandText = sqlProductDel.ToString(); commProductDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commProductDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commProductDel); #endregion #region 生产明细添加SQL语句 if (modelProduct.ProductID.Length > 0) { string[] detProductID = modelProduct.ProductID.Split(','); string[] detWorkTime = modelProduct.WorkTime.Split(','); string[] detFinishNum = modelProduct.FinishNum.Split(','); string[] detPassNum = modelProduct.PassNum.Split(','); string[] detPassPercent = modelProduct.PassPercent.Split(','); string[] detFromBillNo = modelProduct.FromBillNo.Split(','); string[] detFromBillID = modelProduct.FromBillID.Split(','); string[] detFromLineNo = modelProduct.FromLineNo.Split(','); for (int i = 0; i < detProductID.Length; i++) { StringBuilder sqlProduct = new StringBuilder(); sqlProduct.AppendLine("INSERT INTO officedba.ManufactureReportProduct"); sqlProduct.AppendLine(" (ReportNo "); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,ProductID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlProduct.AppendLine(" ,WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlProduct.AppendLine(" ,PassPercent"); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromBillNo"); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromBillID"); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,FromLineNo"); } } sqlProduct.AppendLine(" ,CompanyCD)"); sqlProduct.AppendLine(" VALUES"); sqlProduct.AppendLine(" (@ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@ProductID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@PassPercent"); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromBillNo"); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromBillID"); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { sqlProduct.AppendLine(" ,@FromLineNo"); } } sqlProduct.AppendLine(" ,@CompanyCD)"); SqlCommand commProduct = new SqlCommand(); commProduct.CommandText = sqlProduct.ToString(); commProduct.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commProduct.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i].ToString())); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@WorkTime", detWorkTime[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } if (detFromBillNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillNo[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", detFromBillNo[i].ToString())); } } if (detFromBillID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromBillID[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromBillID", detFromBillID[i].ToString())); } } if (detFromLineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFromLineNo[i].ToString().Trim())) { commProduct.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", detFromLineNo[i].ToString())); } } listADD.Add(commProduct); } } #endregion #endregion #region 人员明细处理 #region 删除人员明细 StringBuilder sqlStaffDel = new StringBuilder(); sqlStaffDel.AppendLine("delete from officedba.ManufactureReportStaff "); sqlStaffDel.AppendLine("where CompanyCD=@CompanyCD"); sqlStaffDel.AppendLine("and ReportNo=("); sqlStaffDel.AppendLine(" select top 1 ReportNo from officedba.ManufactureReport where CompanyCD=@CompanyCD and ID=@ID"); sqlStaffDel.AppendLine(" )"); SqlCommand commStaffDel = new SqlCommand(); commStaffDel.CommandText = sqlStaffDel.ToString(); commStaffDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commStaffDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commStaffDel); #endregion #region 人员明细添加SQL语句 if (modelStaff.StaffID.Length > 0) { string[] detStaffID = modelStaff.StaffID.Split(','); string[] detWorkTime = modelStaff.WorkTime.Split(','); string[] detFinishNum = modelStaff.FinishNum.Split(','); string[] detPassNum = modelStaff.PassNum.Split(','); string[] detPassPercent = modelStaff.PassPercent.Split(','); for (int i = 0; i < detStaffID.Length; i++) { StringBuilder sqlStaff = new StringBuilder(); sqlStaff.AppendLine("INSERT INTO officedba.ManufactureReportStaff "); sqlStaff.AppendLine(" (ReportNo"); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { sqlStaff.AppendLine(" ,StaffID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlStaff.AppendLine(" ,WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,FinishNum"); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlStaff.AppendLine(" ,PassPercent"); } } sqlStaff.AppendLine(" ,CompanyCD)"); sqlStaff.AppendLine(" VALUES"); sqlStaff.AppendLine(" (@ReportNo"); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@StaffID"); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@WorkTime"); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@PassNum"); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlStaff.AppendLine(" ,@PassPercent"); } } sqlStaff.AppendLine(" ,@CompanyCD)"); SqlCommand commStaff = new SqlCommand(); commStaff.CommandText = sqlStaff.ToString(); commStaff.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commStaff.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detStaffID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detStaffID[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@StaffID", detStaffID[i].ToString())); } } if (detWorkTime[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detWorkTime[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@WorkTime", detWorkTime[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commStaff.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } listADD.Add(commStaff); } } #endregion #endregion #region 设备明细处理 #region 删除设备明细 StringBuilder sqlMachineDel = new StringBuilder(); sqlMachineDel.AppendLine("delete from officedba.ManufactureReportMachine "); sqlMachineDel.AppendLine("where CompanyCD=@CompanyCD"); sqlMachineDel.AppendLine("and ReportNo=("); sqlMachineDel.AppendLine(" select top 1 ReportNo from officedba.ManufactureReport where CompanyCD=@CompanyCD and ID=@ID"); sqlMachineDel.AppendLine(" )"); SqlCommand commMachineDel = new SqlCommand(); commMachineDel.CommandText = sqlMachineDel.ToString(); commMachineDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commMachineDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commMachineDel); #endregion #region 设备明细添加SQL语句 if (modelMachine.MachineID.Length > 0 && modelMachine.MachineNo.Length > 0 && modelMachine.MachineName.Length > 0) { string[] detMachineID = modelMachine.MachineID.Split(','); string[] detMachineNo = modelMachine.MachineNo.Split(','); string[] detMachineName = modelMachine.MachineName.Split(','); string[] detUseHour = modelMachine.UseHour.Split(','); string[] detFinishNum = modelMachine.FinishNum.Split(','); string[] detPassNum = modelMachine.PassNum.Split(','); string[] detPassPercent = modelMachine.PassPercent.Split(','); for (int i = 0; i < detMachineID.Length; i++) { StringBuilder sqlMachine = new StringBuilder(); sqlMachine.AppendLine("INSERT INTO officedba.ManufactureReportMachine"); sqlMachine.AppendLine(" (ReportNo"); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineID "); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineNo "); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { sqlMachine.AppendLine(" ,MachineName "); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { sqlMachine.AppendLine(" ,UseHour "); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,PassNum "); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlMachine.AppendLine(" ,PassPercent"); } } sqlMachine.AppendLine(" ,CompanyCD) "); sqlMachine.AppendLine(" VALUES "); sqlMachine.AppendLine(" (@ReportNo "); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineID "); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineNo "); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@MachineName "); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@UseHour "); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@FinishNum "); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@PassNum "); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { sqlMachine.AppendLine(" ,@PassPercent"); } } sqlMachine.AppendLine(" ,@CompanyCD)"); SqlCommand commMachine = new SqlCommand(); commMachine.CommandText = sqlMachine.ToString(); commMachine.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commMachine.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detMachineID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineID[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineID", detMachineID[i].ToString())); } } if (detMachineNo[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineNo[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineNo", detMachineNo[i].ToString())); } } if (detMachineName[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detMachineName[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@MachineName", detMachineName[i].ToString())); } } if (detUseHour[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUseHour[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@UseHour", detUseHour[i].ToString())); } } if (detFinishNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detFinishNum[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@FinishNum", detFinishNum[i].ToString())); } } if (detPassNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassNum[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@PassNum", detPassNum[i].ToString())); } } if (detPassPercent[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detPassPercent[i].ToString().Trim())) { commMachine.Parameters.Add(SqlHelper.GetParameter("@PassPercent", detPassPercent[i].ToString())); } } listADD.Add(commMachine); } } #endregion #endregion #region 物料明细处理 #region 删除物料明细 StringBuilder sqlMeterialDel = new StringBuilder(); sqlMeterialDel.AppendLine("delete from officedba.ManufactureReportMeterial "); sqlMeterialDel.AppendLine("where CompanyCD=@CompanyCD"); sqlMeterialDel.AppendLine("and ReportNo=("); sqlMeterialDel.AppendLine(" select top 1 ReportNo from officedba.ManufactureReport where CompanyCD=@CompanyCD and ID=@ID"); sqlMeterialDel.AppendLine(" )"); SqlCommand commMeterialDel = new SqlCommand(); commMeterialDel.CommandText = sqlMeterialDel.ToString(); commMeterialDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commMeterialDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commMeterialDel); #endregion #region 物料明细添加SQL语句 if (modelMeterial.ProductID.Length > 0) { string[] detProductID = modelMeterial.ProductID.Split(','); string[] detTakeNum = modelMeterial.TakeNum.Split(','); string[] detBeforeNum = modelMeterial.BeforeNum.Split(','); string[] detUsedNum = modelMeterial.UsedNum.Split(','); string[] detBadNum = modelMeterial.BadNum.Split(','); string[] detNowNum = modelMeterial.NowNum.Split(','); for (int i = 0; i < detProductID.Length; i++) { StringBuilder sqlMeterial = new StringBuilder(); sqlMeterial.AppendLine("INSERT INTO officedba.ManufactureReportMeterial"); sqlMeterial.AppendLine(" (ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,ProductID"); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,TakeNum"); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,BeforeNum"); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,UsedNum"); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,BadNum"); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,NowNum"); } } sqlMeterial.AppendLine(" ,CompanyCD)"); sqlMeterial.AppendLine(" VALUES "); sqlMeterial.AppendLine(" (@ReportNo"); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@ProductID"); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@TakeNum "); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@BeforeNum "); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@UsedNum "); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@BadNum"); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { sqlMeterial.AppendLine(" ,@NowNum"); } } sqlMeterial.AppendLine(" ,@CompanyCD)"); SqlCommand commMeterial = new SqlCommand(); commMeterial.CommandText = sqlMeterial.ToString(); commMeterial.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commMeterial.Parameters.Add(SqlHelper.GetParameter("@ReportNo", model.ReportNo)); if (detProductID[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detProductID[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i].ToString())); } } if (detTakeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detTakeNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@TakeNum", detTakeNum[i].ToString())); } } if (detBeforeNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBeforeNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@BeforeNum", detBeforeNum[i].ToString())); } } if (detUsedNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detUsedNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@UsedNum", detUsedNum[i].ToString())); } } if (detBadNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detBadNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@BadNum", detBadNum[i].ToString())); } } if (detNowNum[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(detNowNum[i].ToString().Trim())) { commMeterial.Parameters.Add(SqlHelper.GetParameter("@NowNum", detNowNum[i].ToString())); } } listADD.Add(commMeterial); } } #endregion #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); }