public static SqlCommand UpdatePrimary(OfficeThingsPurchaseApplyModel PurchaseApplyM) { SqlCommand comm = new SqlCommand(); #region SQL文 StringBuilder sql = new StringBuilder(); sql.AppendLine(" update officedba.OfficeThingsPurchaseApply set "); sql.AppendLine("CompanyCD = @CompanyCD, "); sql.AppendLine("ApplyNo = @ApplyNo, "); sql.AppendLine("Subject = @Subject,"); sql.AppendLine("ApplyUserID = @ApplyUserID,"); sql.AppendLine("ApplyDeptID = @ApplyDeptID, "); sql.AppendLine("ApplyDate = @ApplyDate,"); sql.AppendLine("Address = @Address, "); sql.AppendLine("CountTotal = @CountTotal, "); sql.AppendLine("Remark = @Remark, "); sql.AppendLine("BillStatus = @BillStatus, "); sql.AppendLine("ModifiedDate = getDate() , "); sql.AppendLine("ModifiedUserID = @ModifiedUserID "); sql.AppendLine(" WHERE ID=@ID "); #endregion #region 传参 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyNo", PurchaseApplyM.ApplyNo)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseApplyM.CompanyCD )); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", PurchaseApplyM.Subject)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID", PurchaseApplyM.ApplyUserID.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Address", PurchaseApplyM.Address.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseApplyM.CountTotal.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", PurchaseApplyM.ApplyDate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", PurchaseApplyM.Remark.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseApplyM.ModifiedUserID.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", PurchaseApplyM.id.ToString())); #endregion comm.CommandText = sql.ToString(); return comm; }
public static DataTable SelectPrimary(OfficeThingsPurchaseApplyModel PurchaseApplyM, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { SqlCommand comm = new SqlCommand(); StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT A.ID"); sql.AppendLine(" , isnull(A.CompanyCD ,'') AS CompanyCD "); sql.AppendLine(" , isnull(A.ApplyNo ,'') AS ApplyNo "); sql.AppendLine(" , isnull(A.Subject ,'') AS Title "); sql.AppendLine(" , isnull(A.ApplyUserID ,0) AS ApplyUserID "); sql.AppendLine(" , isnull(C.EmployeeName,'') AS ApplyUserName "); sql.AppendLine(" , isnull(A.ApplyDeptID ,0) AS ApplyDeptID "); sql.AppendLine(" , isnull(D.DeptName ,'') AS ApplyDeptName "); sql.AppendLine(" , CONVERT(varchar(100), A.ApplyDate, 23) AS ApplyDate "); sql.AppendLine(" , isnull(A.BillStatus ,'') AS BillStatus "); sql.AppendLine(" , case A.BillStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'"); sql.AppendLine(" when '4' then '手工结单' when '5' then '自动结单' end AS BillStatusName "); sql.AppendLine(" ,isnull(A.ModifiedDate,'') AS ModifiedDate"); sql.AppendLine(" FROM officedba.OfficeThingsPurchaseApply AS A "); sql.AppendLine(" INNER JOIN officedba.EmployeeInfo AS C ON A.ApplyUserID = C.ID "); sql.AppendLine(" INNER JOIN officedba.DeptInfo AS D ON A.ApplyDeptID = D.ID "); sql.AppendLine("WHERE A.CompanyCD = @CompanyCD"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD)); if (PurchaseApplyM.ApplyNo != "" && PurchaseApplyM.ApplyNo != null) { sql.AppendLine(" AND A.ApplyNo like '%'+ @ApplyNo +'%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyNo", PurchaseApplyM.ApplyNo)); } if (PurchaseApplyM.Subject != "" && PurchaseApplyM.Subject != null) { sql.AppendLine(" AND A.Subject like '%'+ @Title +'%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseApplyM.Subject)); } if (PurchaseApplyM.ApplyUserID != "" && PurchaseApplyM.ApplyUserID != null) { sql.AppendLine(" AND A.ApplyUserID = @ApplyUserID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID", PurchaseApplyM.ApplyUserID)); } if (PurchaseApplyM.ApplyDeptID != "" && PurchaseApplyM.ApplyDeptID != null) { sql.AppendLine(" AND A.ApplyDeptID = @ApplyDeptID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID)); } if (PurchaseApplyM.StartApplyDate != "") { sql.AppendLine(" AND A.ApplyDate >= @StartApplyDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartApplyDate", PurchaseApplyM.StartApplyDate)); } if (PurchaseApplyM.EndApplyDate != "") { sql.AppendLine(" AND A.ApplyDate <= @EndApplyDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndApplyDate", PurchaseApplyM.EndApplyDate)); } if (PurchaseApplyM.BillStatus != "0") { sql.AppendLine(" AND A.BillStatus = @BillStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus)); } comm.CommandText = sql.ToString(); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); //return SqlHelper.ExecuteSearch(comm); }
public static SqlCommand InsertPrimary(OfficeThingsPurchaseApplyModel PurchaseApplyM) { SqlCommand comm = new SqlCommand(); #region SQL文 StringBuilder sql = new StringBuilder(); sql.AppendLine(" INSERT INTO officedba.OfficeThingsPurchaseApply ( "); sql.AppendLine("CompanyCD ,"); sql.AppendLine("ApplyNo ,"); sql.AppendLine("Subject ,"); sql.AppendLine("ApplyUserID ,"); sql.AppendLine("ApplyDeptID ,"); sql.AppendLine("ApplyDate ,"); sql.AppendLine("Address ,"); sql.AppendLine("CountTotal ,"); sql.AppendLine("Remark ,"); sql.AppendLine("BillStatus ,"); sql.AppendLine("Creator ,"); sql.AppendLine("CreateDate ,"); sql.AppendLine("ModifiedDate ,"); sql.AppendLine("ModifiedUserID ) VALUES "); sql.AppendLine(" ( "); sql.AppendLine(" @CompanyCD,"); sql.AppendLine(" @ApplyNo,"); sql.AppendLine(" @Subject,"); sql.AppendLine(" @ApplyUserID,"); sql.AppendLine(" @ApplyDeptID,"); sql.AppendLine(" @ApplyDate,"); sql.AppendLine(" @Address,"); sql.AppendLine(" @CountTotal,"); sql.AppendLine(" @Remark,"); sql.AppendLine(" @BillStatus,"); sql.AppendLine(" @Creator,"); sql.AppendLine(" getdate(),"); sql.AppendLine(" getdate(),"); sql.AppendLine(" @ModifiedUserID)"); sql.AppendLine("set @IndexID = @@IDENTITY"); #endregion #region 传参 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseApplyM.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyNo", PurchaseApplyM.ApplyNo)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", PurchaseApplyM.Subject)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID", PurchaseApplyM.ApplyUserID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Address", PurchaseApplyM.Address)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseApplyM.CountTotal)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", PurchaseApplyM.ApplyDate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", PurchaseApplyM.Remark.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", PurchaseApplyM.Creator.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseApplyM.ModifiedUserID)); SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int); IndexID.Direction = ParameterDirection.Output; comm.Parameters.Add(IndexID); #endregion comm.CommandText = sql.ToString(); return comm; }
public static DataTable SelectPurchaseApply(OfficeThingsPurchaseApplyModel PurchaseApplyM, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { DataTable dt = OfficeThingsPurchaseApplyDBHelper.SelectPrimary(PurchaseApplyM, pageIndex, pageCount, OrderBy, ref totalCount); return dt; } catch (Exception ex) { throw ex; } }
public static bool InsertPurchaseApply(OfficeThingsPurchaseApplyModel PurchaseApplyM, List<OfficeThingsPurchaseApplyDetailModel> PurchaseApplyDetailSourceMList , out int IndexIDentity) { ArrayList lstAdd = new ArrayList(); //插入主表 SqlCommand AddPri = OfficeThingsPurchaseApplyDBHelper.InsertPrimary(PurchaseApplyM); lstAdd.Add(AddPri); string ApplyNo = PurchaseApplyM.ApplyNo; foreach (OfficeThingsPurchaseApplyDetailModel PurchaseApplyDetailSourceM in PurchaseApplyDetailSourceMList) { SqlCommand AddDtlS = OfficeThingsPurchaseApplyDBHelper.InsertDtlS(PurchaseApplyDetailSourceM, ApplyNo); lstAdd.Add(AddDtlS); } //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //定义返回变量 bool isSucc = false; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ //执行插入操作 try { isSucc = SqlHelper.ExecuteTransWithArrayList(lstAdd); } catch (Exception ex) { //输出日志 WriteSystemLog(userInfo, ex); } //定义变量 string remark; //成功时 if (isSucc) { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; IndexIDentity = int.Parse(((SqlCommand)AddPri).Parameters["@IndexID"].Value.ToString()); } else { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; IndexIDentity = 0; } LogInfoModel logModel = InitLogInfo(ApplyNo); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return isSucc; }
/// <summary> /// 修改采购申请 /// </summary> /// <param name="PurchaseApplyM">主表</param> /// <param name="str">明细来源</param> /// <param name="start"></param> /// <param name="end">从start--end需要更新,end以后要插入</param> /// <param name="str2">明细信息</param> /// <param name="ApplyNo"></param> /// <returns></returns> public static bool UpdatePurchaseApply(OfficeThingsPurchaseApplyModel PurchaseApplyM, List<OfficeThingsPurchaseApplyDetailModel> PurchaseApplyDetailSourceMList ) { try { ArrayList lstUpdate = new ArrayList(); //更新主表 SqlCommand UpdatePri = OfficeThingsPurchaseApplyDBHelper.UpdatePrimary(PurchaseApplyM); lstUpdate.Add(UpdatePri); string ApplyNo = PurchaseApplyM.ApplyNo; //删除原来明细来源 SqlCommand DelDtlS = OfficeThingsPurchaseApplyDBHelper.DeleteDtlS(ApplyNo); lstUpdate.Add(DelDtlS); //插入现在的明细来源 foreach (OfficeThingsPurchaseApplyDetailModel PurchaseApplyDetailSourceM in PurchaseApplyDetailSourceMList) { SqlCommand AddDtlS = OfficeThingsPurchaseApplyDBHelper.InsertDtlS(PurchaseApplyDetailSourceM, ApplyNo); lstUpdate.Add(AddDtlS); } ////删除原来明细 //SqlCommand DelDtl = OfficeThingsPurchaseApplyDBHelper.DeleteDtl(ApplyNo); //lstUpdate.Add(DelDtl); //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //定义返回变量 bool isSucc = false; /* * 定义日志内容变量 * 增删改相关的日志,需要输出操作日志,该类型日志插入到数据库 * 其他的 如出现异常时,需要输出系统日志,该类型日志保存到日志文件 */ //执行插入操作 try { isSucc = SqlHelper.ExecuteTransWithArrayList(lstUpdate); } catch (Exception ex) { //输出日志 WriteSystemLog(userInfo, ex); } //定义变量 string remark; //成功时 if (isSucc) { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } else { //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } LogInfoModel logModel = InitLogInfo(ApplyNo); //涉及关键元素 这个需要根据每个页面具体设置,本页面暂时设置为空 logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //设置操作成功标识 logModel.Remark = remark; //登陆日志 LogDBHelper.InsertLog(logModel); return isSucc; } catch (Exception ex) { throw ex; } }
public static DataTable SelectPurchaseApply(OfficeThingsPurchaseApplyModel PurchaseApplyM, string OrderBy) { try { DataTable dt = OfficeThingsPurchaseApplyDBHelper.SelectPrimary(PurchaseApplyM, OrderBy); return dt; } catch (Exception ex) { throw ex; } }