/// <summary> /// 领料单插入 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertTakeMaterial(TakeMaterialModel model, Hashtable ht, out string ID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; ID = "0"; try { bool succ = false; string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; LogInfoModel logModel = InitLogInfo(model.TakeNo, 0); logModel.Element = ConstUtil.LOG_PROCESS_INSERT; succ = TakeMaterialDBHelper.InsertTakeMaterial(model,ht, loginUserID, out ID); 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> /// <returns>DataTable</returns> public static DataTable GetTakeDetailInfo(TakeMaterialModel model) { try { return TakeMaterialDBHelper.GetTakeDetailInfo(model); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 确认或结单 /// </summary> /// <param name="model"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool ConfirmOrCompleteTakeMaterial(TakeMaterialModel model, int OperateType) { string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; //string loginUserID = "admin";//[待修改] return TakeMaterialDBHelper.ConfirmOrCompleteTakeMaterial(model, loginUserID, OperateType); }
/// <summary> /// 发料 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool SendTakeMaterial(TakeMaterialModel model,out string reason) { try { return TakeMaterialDBHelper.SendTakeMaterial(model,out reason); } catch (Exception ex) { throw ex; } }
/// <summary> /// 通过检索条件查询领料单信息 /// </summary> /// <returns>DataTable</returns> public static DataTable GetTakeMaterialListBycondition(TakeMaterialModel model, string TakeDateStart, string TakeDateEnd, int BillTypeFlag, int BillTypeCode, int FlowStatus, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return TakeMaterialDBHelper.GetTakeMaterialListBycondition(model, TakeDateStart, TakeDateEnd, BillTypeFlag, BillTypeCode, FlowStatus, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 发料(此处可能需要判断当前物品A所在的仓库A1在分仓存量表里是否存在,如果存在,再判断分仓存量表里的库存是否大于当前该物品的领料统计,如果小于返回提示) /// </summary> /// <param name="model"></param> public static bool SendTakeMaterial(TakeMaterialModel model,out string reason) { //判断当前物品在分仓存量表里是否存在,如果存在,再判断分仓存量表里的库存是否大于当前该物品的领料数量,如果大于则更新分仓存量表,如果小于则返回提示 //更新分仓库存量表 //更新领料表 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; try { ArrayList listADD = new ArrayList(); int tempTotalSucceed = 0; bool tempOperateResult = false; string tempReason = ""; string intTake = ""; DataTable dbTake = GetTakeInfo(model); if (dbTake.Rows.Count > 0) { intTake = dbTake.Rows[0]["ID"].ToString(); if (!string.IsNullOrEmpty(dbTake.Rows[0]["Handout"].ToString())) { reason = "已经发过料了"; } else { #region 业务处理 DataTable dtDetail = GetTakeDetailInfo(model); if (dtDetail.Rows.Count > 0) { #region 明细处理 for (int i = 0; i < dtDetail.Rows.Count; i++) { decimal TakeCount = decimal.Parse(dtDetail.Rows[i]["TakeCount"].ToString()); int ProductID = int.Parse(dtDetail.Rows[i]["ProductID"].ToString()); int StorageID = int.Parse(dtDetail.Rows[i]["StorageID"].ToString()); string BatchNo = dtDetail.Rows[i]["BatchNo"].ToString(); if (string.IsNullOrEmpty(BatchNo)) { BatchNo = ""; } string TakeNo =dtDetail.Rows[i]["TakeNo"].ToString(); decimal Price = decimal.Parse(dtDetail.Rows[i]["Price"].ToString()); decimal productCount = InStorageProductCount(model.CompanyCD, ProductID, StorageID,BatchNo);/*现有存量*/ #region 1.判断当前物品的领料数量是否大于分仓存量表中的库存量 if (TakeCount <= productCount) { #region 更新分仓存量表 StringBuilder sqlUpdate = new StringBuilder(); SqlCommand comUpdate = new SqlCommand(); sqlUpdate.AppendLine("update officedba.StorageProduct set ProductCount=isnull(ProductCount,0)-@TakeCount where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD and isnull(BatchNo,'')=@BatchNo"); comUpdate.CommandText = sqlUpdate.ToString(); comUpdate.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo)); comUpdate.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comUpdate.Parameters.Add(SqlHelper.GetParameter("@TakeCount", TakeCount)); comUpdate.Parameters.Add(SqlHelper.GetParameter("@StorageID", StorageID)); comUpdate.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID)); listADD.Add(comUpdate); #endregion tempTotalSucceed++; } else { if (productCount == decimal.Parse("-1")) { tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>"; } else { tempReason = tempReason + "第" + (i + 1) + "行中的领料数量大于该物品的库存量" + Decimal.Round(productCount, 2).ToString() + "<br>"; } } #endregion #region 2.写入数据到库存流水账表 StorageAccountModel modelSA = new StorageAccountModel(); modelSA.CompanyCD = model.CompanyCD; modelSA.BillType = ConstUtil.STORAGEACCOUNT_BILLTYPE_TAKE; /*单据类型*/ modelSA.ProductID = ProductID; /*物品ID*/ modelSA.StorageID = StorageID; /*仓库ID*/ modelSA.BatchNo = BatchNo; /*批次*/ modelSA.BillNo = TakeNo; /*单据编号*/ modelSA.HappenCount = TakeCount; /*操作数量*/ modelSA.Creator = userInfo.EmployeeID; /*操作人*/ modelSA.Price = Price; /*单价*/ modelSA.ProductCount = productCount-TakeCount; /*现有存量*/ modelSA.PageUrl = "Pages/Office/ProductionManager/TakeMaterial_Add.aspx?ModuleID=" + ConstUtil.MODULE_ID_TAKEMATERIAL_EDIT + "&intTakeID=" + intTake;/*页面URL*/ listADD.Add(StorageAccountDBHelper.InsertStorageAccountBySqlCommand(modelSA)); #endregion } #endregion if (dtDetail.Rows.Count > 0 && dtDetail.Rows.Count == tempTotalSucceed) { #region 更新领料表中的发料人和发料时间 if (!string.IsNullOrEmpty(model.TakeDate.ToShortDateString()) && !string.IsNullOrEmpty(model.Handout.ToString())) { //例:Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine("Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand comTake = new SqlCommand(); comTake.CommandText = sqlTake.ToString(); comTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comTake.Parameters.Add(SqlHelper.GetParameter("@Handout", model.Handout)); comTake.Parameters.Add(SqlHelper.GetParameter("@TakeDate", model.TakeDate)); listADD.Add(comTake); if (SqlHelper.ExecuteTransWithArrayList(listADD)) { tempOperateResult = true; } } #endregion } reason = tempReason; } else { reason = "领料单明细中没有对应的物品需要发料"; } #endregion } } else { reason = "没有找到相关记录"; } return tempOperateResult; } catch (Exception ex) { throw ex; } }
/// <summary> /// 通过检索条件查询领料单信息 /// </summary> /// <param name="model"></param> /// <param name="BillTypeFlag"></param> /// <param name="BillTypeCode"></param> /// <returns></returns> public static DataTable GetTakeMaterialListBycondition(TakeMaterialModel model, string TakeDateStart, string TakeDateEnd, int BillTypeFlag, int BillTypeCode, int FlowStatus, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select a.CompanyCD,a.ID,a.TakeNo,a.Subject,a.FromType,a.TaskID,isnull( f.TaskNo,'')as TaskNo,a.ModifiedDate,"); searchSql.AppendLine(" a.ProcessDeptID,isnull(b.DeptName,'')as ProcessDeptName,a.ManufactureType,"); searchSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,"); searchSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '生产任务单' end as strFromType,"); searchSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,"); searchSql.AppendLine(" isnull(a.Taker,'')as Taker,isnull(c.EmployeeName,'')as TakerReal,"); searchSql.AppendLine(" isnull(a.Handout,'')as Handout,isnull(d.EmployeeName,'')as HandoutReal,"); searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.TakeDate, 23),'') as TakeDate,a.BillStatus,p.ProjectName,a.ProjectID,"); 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(" case when e.FlowStatus=1 then '待审批' when e.FlowStatus=2 then '审批中' when e.FlowStatus=3 then '审批通过' when e.FlowStatus=4 then '审批不通过' when e.FlowStatus=5 then '撤消审批' end as strFlowStatus,"); searchSql.AppendLine(" isnull(e.FlowStatus,'0')as FlowStatus"); searchSql.AppendLine("from officedba.TakeMaterial a"); searchSql.AppendLine("left join officedba.Deptinfo b on a.ProcessDeptID=b.ID"); searchSql.AppendLine("left join officedba.EmployeeInfo c on a.Taker=c.ID"); searchSql.AppendLine("left join officedba.EmployeeInfo d on a.Handout=d.ID"); searchSql.AppendLine("LEFT JOIN officedba.ProjectInfo p on a.ProjectID=p.ID "); searchSql.AppendLine("LEFT JOIN officedba.FlowInstance e ON a.ID=e.BillID and e.BillTypeFlag=@BillTypeFlag and e.BillTypeCode=@BillTypeCode"); searchSql.AppendLine(" and e.ID=( "); searchSql.AppendLine(" select max(ID)"); searchSql.AppendLine(" from officedba.FlowInstance H"); searchSql.AppendLine(" where H.CompanyCD = A.CompanyCD"); searchSql.AppendLine(" and H.BillID = A.ID"); searchSql.AppendLine(" and H.BillTypeFlag =@BillTypeFlag"); searchSql.AppendLine(" and H.BillTypeCode =@BillTypeCode)"); searchSql.AppendLine("LEFT JOIN officedba.ManufactureTask f on a.TaskID=f.ID"); searchSql.AppendLine("where a.CompanyCD=@CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //BillTypeFlag comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", BillTypeFlag.ToString())); //BillTypeCode comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", BillTypeCode.ToString())); //单据编号 if (!string.IsNullOrEmpty(model.TakeNo)) { searchSql.AppendLine("and a.TakeNo like @TakeNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeNo", "%" + model.TakeNo + "%")); } //单据主题 if (!string.IsNullOrEmpty(model.Subject)) { searchSql.AppendLine(" and a.Subject like @Subject"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%")); } //源单类型 if (!string.IsNullOrEmpty(model.FromType)) { if (int.Parse(model.FromType) > -1) { searchSql.AppendLine(" and a.FromType=@FromType "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", model.FromType)); } } //生产任务单 if (model.TaskID > 0) { searchSql.AppendLine(" and a.TaskID=@TaskID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskID", model.TaskID.ToString())); } //生产部门 if (model.ProcessDeptID > 0) { searchSql.AppendLine(" and a.ProcessDeptID=@ProcessDeptID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProcessDeptID", model.ProcessDeptID.ToString())); } //加工类型 if (model.ManufactureType > 0) { searchSql.AppendLine(" and a.ManufactureType=@ManufactureType "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ManufactureType", model.ManufactureType.ToString())); } //领料人 if (model.Taker > 0) { searchSql.AppendLine(" and a.Taker=@Taker "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Taker", model.Taker.ToString())); } //领料单控件(取出发过料的所有领料单) if (model.Handout == -1) { searchSql.AppendLine(" and a.Handout is not null "); } //发料人 if (model.Handout > 0) { searchSql.AppendLine(" and a.Handout=@Handout "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Handout", model.Handout.ToString())); } //发料起始日期 if (!string.IsNullOrEmpty(TakeDateStart)) { searchSql.AppendLine(" and a.TakeDate>=@TakeDateStart "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeDateStart", TakeDateStart)); } //发料截止日期 if (!string.IsNullOrEmpty(TakeDateEnd)) { searchSql.AppendLine(" and a.TakeDate<=@TakeDateEnd "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeDateEnd", TakeDateEnd)); } //单据状态 if (!string.IsNullOrEmpty(model.BillStatus)) { if (int.Parse(model.BillStatus) > 0) { searchSql.AppendLine("and a.BillStatus=@BillStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus)); } } if (model.ProjectID > 0) { searchSql.AppendLine(" and a.ProjectID=@ProjectID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", model.ProjectID.ToString())); } //审批状态 if (FlowStatus > -1) { searchSql.AppendLine("and e.FlowStatus=@FlowStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", FlowStatus.ToString())); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and a.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="CompanyCD"></param> /// <param name="BillTypeFlag"></param> /// <param name="BillTypeCode"></param> /// <param name="BillID"></param> /// <param name="loginUserID"></param> /// <returns></returns> public static bool CancelConfirmOperate(TakeMaterialModel model, int BillTypeFlag, int BillTypeCode, string loginUserID) { ArrayList listADD = new ArrayList(); //#region 传参 try { #region 撤消审批流程 #region 撤消审批处理逻辑描述 //可参见撤消审批的存储过程[FlowApproval_Update],个别的判断去掉 //--1.往流程任务历史记录表(officedba.FlowTaskHistory)插1条处理记录, //--记录的步骤序号为0(表示返回到流程提交人环节),审批状态为撤销审批 //Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate) //Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate()) //--2.更新流程任务处理表(officedba.FlowTaskList)中的流程步骤序号为0(表示返回到流程提交人环节) //Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID //Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID //--3更新流程实例表(officedba.FlowInstance)中的流程状态为“撤销审批” //Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID //Where CompanyCD=@CompanyCD //and FlowNo=@tempFlowNo //and BillTypeFlag=@BillTypeFlag //and BillTypeCode=@BillTypeCode //and BillID=@BillID #endregion DataTable dtFlowInstance = Common.FlowDBHelper.GetFlowInstanceInfo(model.CompanyCD, BillTypeFlag, BillTypeCode, model.ID); if (dtFlowInstance.Rows.Count > 0) { //提交审批了的单据 string FlowInstanceID = dtFlowInstance.Rows[0]["FlowInstanceID"].ToString(); string FlowStatus = dtFlowInstance.Rows[0]["FlowStatus"].ToString(); string FlowNo = dtFlowInstance.Rows[0]["FlowNo"].ToString(); #region 往流程任务历史记录表 StringBuilder sqlHis = new StringBuilder(); sqlHis.AppendLine("Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)"); sqlHis.AppendLine("Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())"); SqlCommand commHis = new SqlCommand(); commHis.CommandText = sqlHis.ToString(); commHis.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID)); commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo)); commHis.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag)); commHis.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID)); commHis.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commHis); #endregion #region 更新流程任务处理表 StringBuilder sqlTask = new StringBuilder(); sqlTask.AppendLine("Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID"); sqlTask.AppendLine("Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID"); SqlCommand commTask = new SqlCommand(); commTask.CommandText = sqlTask.ToString(); commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commTask.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID)); commTask.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commTask); #endregion #region 更新流程实例表 StringBuilder sqlIns = new StringBuilder(); sqlIns.AppendLine("Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID "); sqlIns.AppendLine("Where CompanyCD=@CompanyCD "); sqlIns.AppendLine("and FlowNo=@tempFlowNo "); sqlIns.AppendLine("and BillTypeFlag=@BillTypeFlag "); sqlIns.AppendLine("and BillTypeCode=@BillTypeCode "); sqlIns.AppendLine("and BillID=@BillID"); SqlCommand commIns = new SqlCommand(); commIns.CommandText = sqlIns.ToString(); commIns.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commIns.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeCode", BillTypeCode)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID)); commIns.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commIns); #endregion } #endregion #region 处理自己的业务逻辑 StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine(" UPDATE officedba.TakeMaterial SET"); sqlTake.AppendLine(" Confirmor = null,"); sqlTake.AppendLine(" ConfirmDate = null,"); sqlTake.AppendLine(" ModifiedDate = getdate(),"); sqlTake.AppendLine(" BillStatus = 1,"); sqlTake.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sqlTake.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand commTake = new SqlCommand(); commTake.CommandText = sqlTake.ToString(); commTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commTake); #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); } catch (Exception ex) { throw ex; } }
/// <summary> /// 领料单详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetTakeInfo(TakeMaterialModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select * from ("); infoSql.AppendLine(" select a.ID,a.CompanyCD,a.TakeNo,a.Subject,a.FromType,a.TaskID,b.TaskNo,"); infoSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '生产任务单' end as strFromType,"); infoSql.AppendLine(" a.ProcessDeptID,j.DeptName as ProcessDeptName,a.ManufactureType,a.SaleID,c.EmployeeName as SaleReal,"); infoSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,"); infoSql.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,"); infoSql.AppendLine(" a.SaleDeptID,d.DeptName,Convert(numeric(22,"+userInfo.SelPoint+"),a.CountTotal) as CountTotal,a.Creator,e.EmployeeName as CreatorReal,"); infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,a.Taker,f.EmployeeName as TakerReal,"); infoSql.AppendLine(" a.Handout,g.EmployeeName as HandoutReal,isnull( CONVERT(CHAR(10), a.TakeDate, 23),'') as TakeDate,"); infoSql.AppendLine(" a.BillStatus,a.Confirmor,h.EmployeeName as ConfirmorReal,"); infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.Confirmdate, 23),'') as ConfirmDate,"); infoSql.AppendLine(" a.Closer,i.EmployeeName as CloserReal,"); infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,a.Remark,a.ProjectID,p.ProjectName,"); 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.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID"); infoSql.AppendLine(" from officedba.TakeMaterial a"); infoSql.AppendLine(" left join officedba.ManufactureTask b on a.TaskID=b.ID "); infoSql.AppendLine(" left join officedba.EmployeeInfo c on a.SaleID=c.ID"); infoSql.AppendLine(" left join officedba.DeptInfo d on a.SaleDeptID=d.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo e on a.Creator=e.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo f on a.Taker =f.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo g on a.Handout=g.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo h on a.Confirmor=h.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo i on a.Closer=i.ID"); infoSql.AppendLine(" left join officedba.DeptInfo j on a.ProcessDeptID=j.ID"); infoSql.AppendLine(" left join officedba.ProjectInfo p on a.ProjectID=p.ID"); infoSql.AppendLine(") as info"); infoSql.AppendLine("Where 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="reason"></param> /// <returns></returns> public static bool isCanTakeMaterial(TakeMaterialModel model, out string reason) { string tempReason = ""; bool isCan = true; #region 领料单明细QL语句 if (model.DetProductID.Length > 0) { string[] detProductID = model.DetProductID.Split(','); string[] detStorageID = model.DetStorageID.Split(','); string[] detTakeCount = model.DetTakeCount.Split(','); string[] detBatchNo = model.DetBatchNo.Split(','); for (int i = 0; i < detProductID.Length; i++) { decimal TakeCount = decimal.Parse(detTakeCount[i].ToString()); int ProductID = int.Parse(detProductID[i].ToString()); int StorageID = int.Parse(detStorageID[i].ToString()); string BatchNo = detBatchNo[i].ToString(); decimal productCount = InStorageProductCount(model.CompanyCD, ProductID, StorageID, BatchNo); if (TakeCount > productCount) { isCan = false; if (productCount == decimal.Parse("-1")) { tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>"; } else { tempReason = tempReason + "第" + (i + 1) + "行中的领料数量大于该物品的库存量" + Decimal.Round(productCount, 2).ToString() + "<br>"; } } } } #endregion reason = tempReason; return isCan; }
/// <summary> /// 领料单插入 /// </summary> /// <param name="model"></param> /// <param name="loginUserID"></param> /// <param name="ID"></param> /// <returns></returns> public static bool InsertTakeMaterial(TakeMaterialModel model, Hashtable htExtAttr, string loginUserID, out string ID) { ArrayList listADD = new ArrayList(); bool result = false; //#region 传参 try { #region 领料单添加SQL语句 StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine("INSERT INTO officedba.TakeMaterial "); sqlTake.AppendLine(" (CompanyCD "); sqlTake.AppendLine(" ,TakeNo "); sqlTake.AppendLine(" ,Subject "); sqlTake.AppendLine(" ,FromType "); sqlTake.AppendLine(" ,TaskID "); sqlTake.AppendLine(" ,ProcessDeptID "); sqlTake.AppendLine(" ,ManufactureType "); sqlTake.AppendLine(" ,SaleID "); sqlTake.AppendLine(" ,SaleDeptID "); sqlTake.AppendLine(" ,CountTotal "); sqlTake.AppendLine(" ,Creator "); sqlTake.AppendLine(" ,CreateDate "); sqlTake.AppendLine(" ,Taker "); sqlTake.AppendLine(" ,BillStatus "); sqlTake.AppendLine(" ,Remark "); sqlTake.AppendLine(" ,ProjectID "); sqlTake.AppendLine(" ,ModifiedDate "); sqlTake.AppendLine(" ,ModifiedUserID) "); sqlTake.AppendLine(" VALUES "); sqlTake.AppendLine(" (@CompanyCD "); sqlTake.AppendLine(" ,@TakeNo "); sqlTake.AppendLine(" ,@Subject "); sqlTake.AppendLine(" ,@FromType "); sqlTake.AppendLine(" ,@TaskID "); sqlTake.AppendLine(" ,@ProcessDeptID "); sqlTake.AppendLine(" ,@ManufactureType "); sqlTake.AppendLine(" ,@SaleID "); sqlTake.AppendLine(" ,@SaleDeptID "); sqlTake.AppendLine(" ,@CountTotal "); sqlTake.AppendLine(" ,@Creator "); sqlTake.AppendLine(" ,@CreateDate "); sqlTake.AppendLine(" ,@Taker "); sqlTake.AppendLine(" ,@BillStatus "); sqlTake.AppendLine(" ,@Remark "); sqlTake.AppendLine(" ,@ProjectID "); sqlTake.AppendLine(" ,getdate() "); sqlTake.AppendLine(" ,'" + loginUserID + "') "); sqlTake.AppendLine("set @ID=@@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlTake.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@TakeNo", model.TakeNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType)); comm.Parameters.Add(SqlHelper.GetParameter("@TaskID", model.TaskID.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDeptID", model.ProcessDeptID.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType)); comm.Parameters.Add(SqlHelper.GetParameter("@SaleID", model.SaleID.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@SaleDeptID", model.SaleDeptID.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate)); comm.Parameters.Add(SqlHelper.GetParameter("@Taker", model.Taker.ToString())); comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID)); comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); listADD.Add(comm); #endregion // 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); //领料单明细添加SQL语句 SaveDetail(model, listADD, loginUserID); 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> /// <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 UpdateTakeMaterialInfo(TakeMaterialModel model, Hashtable htExtAttr, string loginUserID) { //获取登陆用户ID ArrayList listADD = new ArrayList(); if (model.ID <= 0) { return false; } #region 领料单修改SQL语句 StringBuilder sqlEdit = new StringBuilder(); sqlEdit.AppendLine("UPDATE officedba.TakeMaterial "); sqlEdit.AppendLine(" SET CompanyCD = @CompanyCD "); sqlEdit.AppendLine(" ,Subject = @Subject "); sqlEdit.AppendLine(" ,FromType = @FromType "); sqlEdit.AppendLine(" ,TaskID = @TaskID "); sqlEdit.AppendLine(" ,ProcessDeptID = @ProcessDeptID "); sqlEdit.AppendLine(" ,ManufactureType = @ManufactureType "); sqlEdit.AppendLine(" ,SaleID = @SaleID "); sqlEdit.AppendLine(" ,SaleDeptID = @SaleDeptID "); sqlEdit.AppendLine(" ,CountTotal = @CountTotal "); sqlEdit.AppendLine(" ,Taker = @Taker "); sqlEdit.AppendLine(" ,Remark = @Remark "); sqlEdit.AppendLine(" ,ProjectID = @ProjectID "); if (int.Parse(model.BillStatus) == 2) { sqlEdit.AppendLine(" ,BillStatus = 3 "); } sqlEdit.AppendLine(" ,Confirmor=null "); sqlEdit.AppendLine(" ,ConfirmDate=null "); sqlEdit.AppendLine(" ,ModifiedDate = getdate() "); sqlEdit.AppendLine(" ,ModifiedUserID = '" + loginUserID + "' "); sqlEdit.AppendLine(" WHERE CompanyCD=@CompanyCD and ID=@ID "); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlEdit.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("@FromType", model.FromType)); comm.Parameters.Add(SqlHelper.GetParameter("@TaskID", model.TaskID)); comm.Parameters.Add(SqlHelper.GetParameter("@ProcessDeptID", model.ProcessDeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType)); comm.Parameters.Add(SqlHelper.GetParameter("@SaleID", model.SaleID)); comm.Parameters.Add(SqlHelper.GetParameter("@SaleDeptID", model.SaleDeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal)); comm.Parameters.Add(SqlHelper.GetParameter("@Taker", model.Taker)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID)); listADD.Add(comm); #endregion #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); #endregion #region 领料单明细处理 // 删除领料单明细 StringBuilder sqlProductDel = new StringBuilder(); sqlProductDel.AppendLine("delete from officedba.TakeMaterialDetail "); sqlProductDel.AppendLine("where CompanyCD=@CompanyCD"); sqlProductDel.AppendLine("and TakeNo=("); sqlProductDel.AppendLine(" select top 1 TakeNo from officedba.TakeMaterial 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); //领料单明细添加SQL语句 SaveDetail(model, listADD, loginUserID); #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); }
/// <summary> /// 保存明细信息 /// </summary> /// <param name="model"></param> /// <param name="list"></param> private static void SaveDetail(TakeMaterialModel model, ArrayList list, string loginUserID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; if (model.DetProductID.Length < 1) { return; } string[] detSortNo = model.DetSortNo.Split(','); string[] detProductID = model.DetProductID.Split(','); string[] detStorageID = model.DetStorageID.Split(','); string[] detTakeCount = model.DetTakeCount.Split(','); string[] detPrice = model.DetPrice.Split(','); string[] detTotalPrice = model.DetTotalPrice.Split(','); string[] detRemark = model.DetRemark.Split(','); string[] detFromType = model.DetFromType.Split(','); string[] detUnitID = model.DetUnitID.Split(','); string[] detUsedUnitID = model.DetUsedUnitID.Split(','); string[] detUsedUnitCount = model.DetUsedUnitCount.Split(','); string[] detExRate = model.DetExRate.Split(','); string[] detUsedPrice = model.DetUsedPrice.Split(','); string[] detBatchNo = model.DetBatchNo.Split(','); for (int i = 0; i < detSortNo.Length; i++) { StringBuilder sqlDet = new StringBuilder(); StringBuilder sqlValues = new StringBuilder(); SqlCommand commDet = new SqlCommand(); sqlDet.Append("INSERT INTO officedba.TakeMaterialDetail"); sqlDet.Append(" (CompanyCD,TakeNo,SortNo,ModifiedDate,ModifiedUserID,Remark"); sqlValues.AppendFormat(" VALUES(@CompanyCD,@TakeNo,@SortNo,getdate(),'{0}',@Remark", loginUserID); commDet.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commDet.Parameters.Add(SqlHelper.GetParameter("@TakeNo", model.TakeNo)); commDet.Parameters.Add(SqlHelper.GetParameter("@SortNo", detSortNo[i].ToString())); commDet.Parameters.Add(SqlHelper.GetParameter("@Remark", detRemark[i].ToString())); if (!string.IsNullOrEmpty(detProductID[i].Trim())) {// 产品ID sqlDet.Append(",ProductID"); sqlValues.Append(",@ProductID"); commDet.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i])); } if (!string.IsNullOrEmpty(detStorageID[i].Trim())) {// 仓库ID sqlDet.Append(",StorageID"); sqlValues.Append(",@StorageID"); commDet.Parameters.Add(SqlHelper.GetParameter("@StorageID", detStorageID[i])); } if (!string.IsNullOrEmpty(detPrice[i].Trim())) {//单价 sqlDet.Append(",Price"); sqlValues.Append(",@Price"); commDet.Parameters.Add(SqlHelper.GetParameter("@Price", detPrice[i])); } if (!string.IsNullOrEmpty(detTotalPrice[i].Trim())) {//金额 sqlDet.Append(",TotalPrice"); sqlValues.Append(",@TotalPrice"); commDet.Parameters.Add(SqlHelper.GetParameter("@TotalPrice", detTotalPrice[i])); } if (!string.IsNullOrEmpty(detFromType[i].Trim())) {// 源单类型 sqlDet.Append(",FromType"); sqlValues.Append(",@FromType"); commDet.Parameters.Add(SqlHelper.GetParameter("@FromType", detFromType[i])); } if (userInfo.IsMoreUnit) { if (!string.IsNullOrEmpty(detUnitID[i].Trim())) {//基本单位 sqlDet.Append(",UnitID"); sqlValues.Append(",@UnitID"); commDet.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i])); } if (!string.IsNullOrEmpty(detTakeCount[i].Trim())) {//基本数量 sqlDet.Append(",TakeCount"); sqlValues.Append(",@TakeCount"); commDet.Parameters.Add(SqlHelper.GetParameter("@TakeCount", detTakeCount[i])); } if (!string.IsNullOrEmpty(detUsedUnitID[i].Trim())) {//单位 sqlDet.Append(",UsedUnitID"); sqlValues.Append(",@UsedUnitID"); commDet.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", detUsedUnitID[i])); } if (!string.IsNullOrEmpty(detUsedUnitCount[i].Trim())) {//数量 sqlDet.Append(",UsedUnitCount"); sqlValues.Append(",@UsedUnitCount"); commDet.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", detUsedUnitCount[i])); } if (!string.IsNullOrEmpty(detExRate[i].Trim())) {//换算率 sqlDet.Append(",ExRate"); sqlValues.Append(",@ExRate"); commDet.Parameters.Add(SqlHelper.GetParameter("@ExRate", detExRate[i])); } if (!string.IsNullOrEmpty(detUsedPrice[i].Trim())) {//单价 sqlDet.Append(",UsedPrice"); sqlValues.Append(",@UsedPrice"); commDet.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", detUsedPrice[i])); } if (!string.IsNullOrEmpty(detBatchNo[i].Trim())) {//批次 sqlDet.Append(",BatchNo"); sqlValues.Append(",@BatchNo"); commDet.Parameters.Add(SqlHelper.GetParameter("@BatchNo", detBatchNo[i])); } } else { if (!string.IsNullOrEmpty(detUnitID[i].Trim())) {//单位 sqlDet.Append(",UnitID"); sqlValues.Append(",@UnitID"); commDet.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i])); } if (!string.IsNullOrEmpty(detTakeCount[i].Trim())) {//数量 sqlDet.Append(",TakeCount"); sqlValues.Append(",@TakeCount"); commDet.Parameters.Add(SqlHelper.GetParameter("@TakeCount", detTakeCount[i])); } } sqlDet.Append(")"); sqlValues.Append(")"); commDet.CommandText = sqlDet.ToString() + sqlValues.ToString(); list.Add(commDet); } }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(TakeMaterialModel model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.TakeMaterial 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 TakeNo = @TakeNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@TakeNo", model.TakeNo); cmd.CommandText = strSql; } catch (Exception) { } }
/// <summary> /// 取消确认 /// </summary> /// <param name="model"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool CancelConfirmTakeMaterial(TakeMaterialModel model, int BillTypeFlag, int BillTypeCode) { string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; return TakeMaterialDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID); }
/// <summary> /// 领料单明细详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetTakeDetailInfo(TakeMaterialModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder detSql = new StringBuilder(); detSql.AppendLine("select * from ("); detSql.AppendLine(" select a.CompanyCD,a.ID as DetailID,a.TakeNo,a.SortNo,a.ProductID,b.ProductName,b.ProdNo,a.BatchNo,isnull(b.IsBatchNo,0) as IsBatchNo,"); detSql.AppendLine(" b.UnitID,c.CodeName as UnitName,a.StorageID,d.StorageName,d.StorageNo,Convert(numeric(14,"+userInfo.SelPoint+"),a.TakeCount) as TakeCount,"); detSql.AppendLine(" Convert(numeric(14," + userInfo.SelPoint + "),a.Price) as Price,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedPrice) as UsedPrice,Convert(numeric(22," + userInfo.SelPoint + "),a.TotalPrice) as TotalPrice,"); detSql.AppendLine(" isnull(Convert(numeric(14,"+userInfo.SelPoint+"),BackCount),0) as BackCount,a.Remark,a.FromType,"); detSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '生产任务单' end as strFromType,a.UsedUnitID,e.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount "); detSql.AppendLine(" from officedba.TakeMaterialDetail a"); detSql.AppendLine(" left join officedba.ProductInfo b on a.ProductID=b.ID"); detSql.AppendLine(" left join officedba.CodeUnitType c on b.UnitID=c.Id"); detSql.AppendLine(" left join officedba.CodeUnitType e on a.UsedUnitID=e.ID"); detSql.AppendLine(" left join officedba.StorageInfo d on a.StorageID=d.ID"); detSql.AppendLine(" where TakeNo=(select top 1 TakeNo from officedba.TakeMaterial where ID=@ID)"); detSql.AppendLine(") as info"); detSql.AppendLine("where 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 = detSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 确认或结单 /// </summary> /// <param name="model"></param> /// <param name="loginUserID"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool ConfirmOrCompleteTakeMaterial(TakeMaterialModel model, string loginUserID, int OperateType) { ArrayList listADD = new ArrayList(); if (OperateType == 1) { StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine(" UPDATE officedba.TakeMaterial SET"); sqlTake.AppendLine(" Confirmor = @Confirmor,"); sqlTake.AppendLine(" ConfirmDate = @ConfirmDate,"); sqlTake.AppendLine(" ModifiedDate = getdate(),"); sqlTake.AppendLine(" BillStatus = 2,"); sqlTake.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sqlTake.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand commTake = new SqlCommand(); commTake.CommandText = sqlTake.ToString(); commTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commTake.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor)); commTake.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate)); commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commTake); } else if (OperateType == 2) { StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine(" UPDATE officedba.TakeMaterial SET"); sqlTake.AppendLine(" Closer = @Closer,"); sqlTake.AppendLine(" CloseDate = @CloseDate,"); sqlTake.AppendLine(" BillStatus = 4,"); sqlTake.AppendLine(" ModifiedDate = getdate(),"); sqlTake.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sqlTake.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand commTake = new SqlCommand(); commTake.CommandText = sqlTake.ToString(); commTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commTake.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer)); commTake.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate)); commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commTake); } else { StringBuilder sqlTake = new StringBuilder(); sqlTake.AppendLine(" update officedba.TakeMaterial set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,ModifiedUserID = '" + loginUserID + "'"); sqlTake.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID"); SqlCommand commTake = new SqlCommand(); commTake.CommandText = sqlTake.ToString(); commTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); commTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); listADD.Add(commTake); } return SqlHelper.ExecuteTransWithArrayList(listADD); }