/// <summary> /// 查询设备明细信息列表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTableBycondition(EquipMnetInfoModel equip_M,int pageIndex,int pageCount,string ord, ref int TotalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; string sql = "select a.ID,a.EquipmentNo,a.EquipmentIndex,a.CompanyCD,Convert(numeric(18,"+userInfo.SelPoint+"),[Money]) as moneys," + "a.EquipmentName,a.Norm,a.Precision,a.BuyDate,a.Provider,a.Type,a.Warranty," + "a.ExaminePeriod,a.CurrentUser,a.CurrentDepartment,isnull(a.Unit,'')Unit," + "a.EquipmentDetail," + "case FittingFlag when 0 then '无' when 1 then '有' else '其他' end FittingFlag, " + "case (a.Status) when 0 then '空闲' when 1 then '使用' when 3 then '维修' when 5 then '报废' end Status,b.CodeName," + "isnull(c.EmployeeName,'')EmployeeName,isnull(d.DeptName,'')DeptName " + " from officedba.EquipmentInfo a inner join officedba.CodeEquipmentType b on a.Type=b.ID " + "LEFT OUTER JOIN officedba.EmployeeInfo c " + "ON a.CurrentUser=c.ID and a.CompanyCD=c.CompanyCD " + "LEFT OUTER JOIN " + "officedba.DeptInfo d " + "ON a.CurrentDepartment=d.ID and a.CompanyCD=d.CompanyCD " +" where a.CompanyCD='"+equip_M.CompanyCD+"'"; if (equip_M.EquipmentNo != "") sql += " and EquipmentNo like '%"+equip_M.EquipmentNo+"%'"; if (equip_M.EquipmentName != "") sql += " and EquipmentName like '%"+equip_M.EquipmentName+"%'"; if (equip_M.EquipmentIndex != "") sql += " and EquipmentIndex like '%" + equip_M.EquipmentIndex + "%'"; if (equip_M.Provider != "") sql += " and Provider like '%"+equip_M.Provider+"%'"; if (equip_M.Type != 0) sql += " and [Type]=" + equip_M.Type + ""; if (equip_M.BuyDate.ToString() != "" && equip_M.BuyDate.ToString() != "1900-1-1 0:00:00") sql += " and BuyDate>='" + equip_M.BuyDate + "'"; if (equip_M.CurrentDepartment!= 0) sql += " and CurrentDepartment=" + equip_M.CurrentDepartment + ""; if (equip_M.CurrentUser!= 0) sql += " and CurrentUser="******""; if (equip_M.Status.ToString() != "") sql += " and Status='" + equip_M.Status + "'"; return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount); //return SqlHelper.ExecuteSql(sql); }
/// <summary> /// 查询设备明细信息列表(金额合计) /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTotalTableBycondition(EquipMnetInfoModel equip_M) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select Convert(numeric(18,"+userInfo.SelPoint+"),sum(money)) as TotalMoney "); searchSql.AppendLine(" from officedba.EquipmentInfo a inner join officedba.CodeEquipmentType b on a.Type=b.ID "); searchSql.AppendLine("LEFT OUTER JOIN officedba.EmployeeInfo c "); searchSql.AppendLine("ON a.CurrentUser=c.ID and a.CompanyCD=c.CompanyCD "); searchSql.AppendLine("LEFT OUTER JOIN "); searchSql.AppendLine("officedba.DeptInfo d "); searchSql.AppendLine("ON a.CurrentDepartment=d.ID and a.CompanyCD=d.CompanyCD "); searchSql.AppendLine(" where a.CompanyCD=@CompanyCD"); SqlCommand comm = new SqlCommand(); if (equip_M.EquipmentNo != "") { searchSql.AppendLine(" and EquipmentNo like @EquipmentNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentNo", "%" + equip_M.EquipmentNo + "%")); } if (equip_M.EquipmentName != "") { searchSql.AppendLine(" and EquipmentName like @EquipmentName"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentName", "%" + equip_M.EquipmentName + "%")); } if (equip_M.EquipmentIndex != "") { searchSql.AppendLine(" and EquipmentIndex like @EquipmentIndex"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentIndex", "%" + equip_M.EquipmentIndex + "%")); } if (equip_M.Provider != "") { searchSql.AppendLine(" and Provider like @Provider"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Provider", "%" + equip_M.Provider + "%")); } if (equip_M.Type != 0) { searchSql.AppendLine(" and [Type]=@types"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@types", equip_M.Type.ToString())); } if (equip_M.BuyDate.ToString() != "" && equip_M.BuyDate.ToString() != "1900-1-1 0:00:00") { searchSql.AppendLine(" and BuyDate>=@BuyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BuyDate", equip_M.BuyDate.ToString())); } if (equip_M.CurrentDepartment != 0) { searchSql.AppendLine(" and CurrentDepartment>=@CurrentDepartment"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentDepartment", equip_M.CurrentDepartment.ToString())); } if (equip_M.CurrentUser != 0) { searchSql.AppendLine(" and CurrentUser>=@CurrentUser"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentUser", equip_M.CurrentUser.ToString())); } if (equip_M.Status.ToString() != "") { searchSql.AppendLine(" and Status>=@Status"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", equip_M.Status)); } //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", equip_M.CompanyCD)); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 更新设备和配件操作 /// </summary> /// <param name="model">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool UpdateEquipMnetAndFitInfo(EquipMnetInfoModel EquipModel, string FitInfo) { try { StringBuilder UpdateEquipInfoSql = new StringBuilder(); UpdateEquipInfoSql.AppendLine("UPDATE officedba.EquipmentInfo "); UpdateEquipInfoSql.AppendLine("SET "); UpdateEquipInfoSql.AppendLine(" EquipmentIndex = '" + EquipModel.EquipmentIndex+ "', "); UpdateEquipInfoSql.AppendLine(" EquipmentName = '" + EquipModel .EquipmentName+ "', "); UpdateEquipInfoSql.AppendLine(" Norm = '" + EquipModel .Norm+ "', "); UpdateEquipInfoSql.AppendLine(" [Precision] = '" + EquipModel .Precision+ "', "); UpdateEquipInfoSql.AppendLine(" BuyDate = '" + EquipModel .BuyDate+ "', "); UpdateEquipInfoSql.AppendLine(" Provider = '" + EquipModel .Provider+ "', "); UpdateEquipInfoSql.AppendLine(" [Type] = " + EquipModel .Type+ ", "); UpdateEquipInfoSql.AppendLine(" Warranty = '" + EquipModel .Warranty+ "', "); UpdateEquipInfoSql.AppendLine(" ExaminePeriod = '" + EquipModel .ExaminePeriod+ "', "); UpdateEquipInfoSql.AppendLine(" CurrentUser = "******", "); UpdateEquipInfoSql.AppendLine(" CurrentDepartment = " + EquipModel .CurrentDepartment+ ", "); UpdateEquipInfoSql.AppendLine(" FittingFlag = '" + EquipModel .FittingFlag+ "', "); UpdateEquipInfoSql.AppendLine(" Unit=" + EquipModel.Unit + ", "); UpdateEquipInfoSql.AppendLine(" PYShort='" + EquipModel.PYShort + "', "); UpdateEquipInfoSql.AppendLine(" Money=" + EquipModel.Money + ", "); UpdateEquipInfoSql.AppendLine(" EquiFrom='" + EquipModel.EquiFrom + "', "); UpdateEquipInfoSql.AppendLine(" Attachment='" + EquipModel.Attachment + "', "); UpdateEquipInfoSql.AppendLine(" Status = '" + EquipModel.Status + "', "); UpdateEquipInfoSql.AppendLine(" ModifiedUserid = '" + EquipModel.ModifiedUserid + "', "); UpdateEquipInfoSql.AppendLine(" ModifiedDate = '" + EquipModel.ModifiedDate + "', "); UpdateEquipInfoSql.AppendLine(" EquipmentDetail = '" + EquipModel.EquipmentDetail + "' "); UpdateEquipInfoSql.AppendLine("WHERE "); UpdateEquipInfoSql.AppendLine(" EquipmentNo = '" + EquipModel.EquipmentNo + "' AND CompanyCD = '" + EquipModel .CompanyCD+ "' "); if (FitInfo != "") return UpdateAll(UpdateEquipInfoSql.ToString(), FitInfo, EquipModel.EquipmentNo, EquipModel.CompanyCD); else { SqlHelper.ExecuteTransSql(UpdateEquipInfoSql.ToString()); return SqlHelper.Result.OprateCount > 0 ? true : false; } } catch { return false; } }
/// <summary> /// 添加设备和配件操作 /// </summary> /// <param name="model">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool AddEquipMnetAndFitInfo(EquipMnetInfoModel EquipModel, string FitInfo) { try { ArrayList lstInsert = new ArrayList(); for (int i = 0; i < EquipModel.EquipmentCount; i++) { #region 参数设置 StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO officedba.EquipmentInfo"); sql.AppendLine(" (EquipmentNo "); sql.AppendLine(" ,EquipmentIndex "); sql.AppendLine(" ,CompanyCD "); sql.AppendLine(" ,EquipmentName "); sql.AppendLine(" ,Norm "); sql.AppendLine(" ,[Precision] "); sql.AppendLine(" ,BuyDate "); sql.AppendLine(" ,Provider "); sql.AppendLine(" ,[Type] "); sql.AppendLine(" ,Warranty "); sql.AppendLine(" ,ExaminePeriod "); sql.AppendLine(" ,CurrentUser "); sql.AppendLine(" ,CurrentDepartment "); sql.AppendLine(" ,FittingFlag "); sql.AppendLine(" ,Unit "); sql.AppendLine(" ,PYShort "); sql.AppendLine(" ,Money "); sql.AppendLine(" ,EquiFrom "); sql.AppendLine(" ,Attachment "); sql.AppendLine(" ,Status "); sql.AppendLine(" ,Creator "); sql.AppendLine(" ,CreateDate "); sql.AppendLine(" ,ModifiedUserid "); sql.AppendLine(" ,ModifiedDate "); sql.AppendLine(" ,EquipmentCount "); sql.AppendLine(" ,EquipmentDetail) "); sql.AppendLine("VALUES "); sql.AppendLine(" (@EquipmentNo "); sql.AppendLine(" ,@EquipmentIndex "); sql.AppendLine(" ,@CompanyCD "); sql.AppendLine(" ,@EquipmentName "); sql.AppendLine(" ,@Norm "); sql.AppendLine(" ,@Precision "); sql.AppendLine(" ,@BuyDate "); sql.AppendLine(" ,@Provider "); sql.AppendLine(" ,@Type"); sql.AppendLine(" ,@Warranty "); sql.AppendLine(" ,@ExaminePeriod "); sql.AppendLine(" ,@CurrentUser "); sql.AppendLine(" ,@CurrentDepartment "); sql.AppendLine(" ,@FittingFlag "); sql.AppendLine(" ,@Unit "); sql.AppendLine(" ,@PYShort "); sql.AppendLine(" ,@Money "); sql.AppendLine(" ,@EquiFrom "); sql.AppendLine(" ,@Attachment "); sql.AppendLine(" ,@Status "); sql.AppendLine(" ,@Creator "); sql.AppendLine(" ,@CreateDate "); sql.AppendLine(" ,@ModifiedUserid "); sql.AppendLine(" ,@ModifiedDate "); sql.AppendLine(" ,@EquipmentCount "); sql.AppendLine(" ,@EquipmentDetail) "); //设置参数 SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentNo", EquipModel.EquipmentNo.Split(',')[i])); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentIndex", EquipModel.EquipmentIndex)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", EquipModel.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentName", EquipModel.EquipmentName)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Norm", EquipModel.Norm)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Precision", EquipModel.Precision)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BuyDate", EquipModel.BuyDate.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Provider", EquipModel.Provider)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Type", EquipModel.Type.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Warranty", EquipModel.Warranty)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ExaminePeriod", EquipModel.ExaminePeriod)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentUser", EquipModel.CurrentUser.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentDepartment", EquipModel.CurrentDepartment.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FittingFlag", EquipModel.FittingFlag)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Unit", EquipModel.Unit.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", EquipModel.PYShort)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Money", EquipModel.Money.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquiFrom", EquipModel.EquiFrom)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", EquipModel.Attachment)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", EquipModel.Status)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentDetail", EquipModel.EquipmentDetail)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", EquipModel.Creator.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", EquipModel.CreateDate.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserid", EquipModel.ModifiedUserid)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentCount", EquipModel.EquipmentCount.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", EquipModel.ModifiedDate.ToString())); #endregion lstInsert.Add(comm);//数组加入插入基表的command } #region 明细 for (int i = 0; i < EquipModel.EquipmentCount; i++) { #region 参数设置 string[] strarray = null; string recorditems = ""; string[] inseritems = null; strarray = FitInfo.Split('|'); for (int j = 0; j < strarray.Length; j++) { StringBuilder fitsql = new StringBuilder(); recorditems = strarray[j]; inseritems = recorditems.Split(','); if (recorditems.Length != 0) { string fitname = inseritems[0].ToString();//配件名称 string fitdesc = inseritems[1].ToString();//描述 fitsql.AppendLine("INSERT INTO officedba.EquipmentFittings"); fitsql.AppendLine(" (CompanyCD "); fitsql.AppendLine(" ,EquipmentNo "); fitsql.AppendLine(" ,FittingName "); fitsql.AppendLine(" ,FittingDescription) "); fitsql.AppendLine("VALUES "); fitsql.AppendLine(" (@CompanyCD "); fitsql.AppendLine(" ,@EquipmentNo "); fitsql.AppendLine(" ,@FittingName "); fitsql.AppendLine(" ,@FittingDescription ) "); SqlCommand commdetail = new SqlCommand(); commdetail.CommandText = fitsql.ToString(); commdetail.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", EquipModel.CompanyCD)); commdetail.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentNo", EquipModel.EquipmentNo.Split(',')[i])); commdetail.Parameters.Add(SqlHelper.GetParameterFromString("@FittingName", inseritems[0].ToString())); commdetail.Parameters.Add(SqlHelper.GetParameterFromString("@FittingDescription", inseritems[1].ToString())); lstInsert.Add(commdetail);//数组加入插入基表的command } } #endregion } #endregion return SqlHelper.ExecuteTransWithArrayList(lstInsert); } catch { return false; } }
/// <summary> /// 设备修改操作(无配件) /// </summary> /// <param name="model">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool UpdateEquipMnetInfo(EquipMnetInfoModel EquipModel) { try { StringBuilder sql = new StringBuilder(); sql.AppendLine("UPDATE officedba.EquipmentInfo "); sql.AppendLine("SET "); sql.AppendLine(" EquipmentIndex = @EquipmentIndex, "); sql.AppendLine(" EquipmentName = @EquipmentName, "); sql.AppendLine(" Norm = @Norm, "); sql.AppendLine(" [Precision] = @Precision, "); sql.AppendLine(" BuyDate = @BuyDate, "); sql.AppendLine(" Provider = @Provider, "); sql.AppendLine(" [Type] = @Type, "); sql.AppendLine(" Warranty = @Warranty, "); sql.AppendLine(" ExaminePeriod = @ExaminePeriod, "); sql.AppendLine(" CurrentUser = @CurrentUser, "); sql.AppendLine(" CurrentDepartment = @CurrentDepartment, "); sql.AppendLine(" FittingFlag = @FittingFlag, "); sql.AppendLine(" Unit=@Unit, "); sql.AppendLine(" PYShort=@PYShort, "); sql.AppendLine(" Money=@Money, "); sql.AppendLine(" EquiFrom=@EquiFrom, "); sql.AppendLine(" Attachment=@Attachment, "); sql.AppendLine(" Status = @Status, "); sql.AppendLine(" ModifiedUserid = @ModifiedUserid, "); sql.AppendLine(" ModifiedDate = @ModifiedDate, "); sql.AppendLine(" EquipmentDetail = @EquipmentDetail "); sql.AppendLine("WHERE "); sql.AppendLine(" EquipmentNo = @EquipmentNo AND CompanyCD = @CompanyCD "); //设置参数 SqlParameter[] param; param = new SqlParameter[23]; param[0] = SqlHelper.GetParameter("@EquipmentIndex", EquipModel.EquipmentIndex); param[1] = SqlHelper.GetParameter("@EquipmentName", EquipModel.EquipmentName); param[2] = SqlHelper.GetParameter("@Norm", EquipModel.Norm); param[3] = SqlHelper.GetParameter("@Precision", EquipModel.Precision); param[4] = SqlHelper.GetParameter("@BuyDate", EquipModel.BuyDate); param[5] = SqlHelper.GetParameter("@Provider", EquipModel.Provider); param[6] = SqlHelper.GetParameter("@Type", EquipModel.Type); param[7] = SqlHelper.GetParameter("@Warranty", EquipModel.Warranty); param[8] = SqlHelper.GetParameter("@ExaminePeriod", EquipModel.ExaminePeriod); param[9] = SqlHelper.GetParameter("@CurrentUser", EquipModel.CurrentUser); param[10] = SqlHelper.GetParameter("@CurrentDepartment", EquipModel.CurrentDepartment); param[11] = SqlHelper.GetParameter("@FittingFlag", EquipModel.FittingFlag); param[12] = SqlHelper.GetParameter("@Unit", Convert.ToInt32(EquipModel.Unit)); param[13] = SqlHelper.GetParameter("@PYShort", EquipModel.PYShort); param[14] = SqlHelper.GetParameter("@Money", EquipModel.Money); param[15] = SqlHelper.GetParameter("@EquiFrom", EquipModel.EquiFrom); param[16] = SqlHelper.GetParameter("@Attachment", EquipModel.Attachment); param[17] = SqlHelper.GetParameter("@Status", EquipModel.Status); param[18] = SqlHelper.GetParameter("@EquipmentDetail", EquipModel.EquipmentDetail); param[19] = SqlHelper.GetParameter("@EquipmentNo", EquipModel.EquipmentNo); param[20] = SqlHelper.GetParameter("@CompanyCD", EquipModel.CompanyCD); param[21] = SqlHelper.GetParameter("@ModifiedUserid", EquipModel.ModifiedUserid); param[22] = SqlHelper.GetParameter("@ModifiedDate", EquipModel.ModifiedDate); string fitsql = "DELETE officedba.EquipmentFittings WHERE EquipmentNo = '" + EquipModel.EquipmentNo + "' AND CompanyCD='" + EquipModel.CompanyCD + "'"; SqlHelper.ExecuteTransSql(sql.ToString(), param); bool flag=SqlHelper.Result.OprateCount > 0 ? true : false; if (flag) { SqlHelper.ExecuteTransSql(fitsql.ToString()); return flag; } else return flag; } catch { return false; } }
/// <summary> /// 查询设备汇总信息列表(金额合计) /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTotalSumTableBycondition(EquipMnetInfoModel equip_M) { StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select sum(moneys) as moneys "); searchSql.AppendLine("from (select CompanyCD,isnull(m.TotalCount,'') TotalCount,isnull(m.Type,'') Type,isnull(m.status,'') Status,"); searchSql.AppendLine("isnull(m.CodeName,'') CodeName,isnull(m.WarningLimit,'') WarningLimit,"); searchSql.AppendLine("isnull(n.FreeCount,'') FreeCount,moneys "); searchSql.AppendLine(" from (select a.*,b.CodeName,b.WarningLimit from (select CompanyCD,count(*) as TotalCount ,[Type],SUM(MONEY) AS moneys,"); searchSql.AppendLine("case Status when 0 then '空闲' when 1 then '使用'"); searchSql.AppendLine(" when 3 then '维修'"); searchSql.AppendLine(" when 5 then '报废' end Status"); searchSql.AppendLine(" from officedba.EquipmentInfo a"); searchSql.AppendLine(" group by [Type],Status,CompanyCD) a inner join officedba.CodeEquipmentType b"); searchSql.AppendLine(" on a.[Type]=b.ID AND a.CompanyCD=b.CompanyCD) m"); searchSql.AppendLine(" left outer join "); searchSql.AppendLine("(select a.*,b.CodeName,b.WarningLimit from (select count(*) as FreeCount ,[Type],"); searchSql.AppendLine("case Status when 0 then '空闲' when 1 then '使用'"); searchSql.AppendLine(" when 3 then '维修'"); searchSql.AppendLine(" when 5 then '报废' end Status"); searchSql.AppendLine(" from officedba.EquipmentInfo a where 1=1 and a.status='0'"); searchSql.AppendLine(" group by [Type],Status) a inner join officedba.CodeEquipmentType b"); searchSql.AppendLine(" on a.[Type]=b.ID) n"); searchSql.AppendLine(" on m.Type=n.Type where m.CompanyCD=@CompanyCD) x "); searchSql.AppendLine(" where x.CompanyCD=@CompanyCD "); SqlCommand comm = new SqlCommand(); if (equip_M.Type != 0) { searchSql.AppendLine(" and x.Type=@Type"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Type", equip_M.Type.ToString())); } if (equip_M.Status != "") { searchSql.AppendLine(" and x.Status=@Status"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", equip_M.Status)); } //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", equip_M.CompanyCD)); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 设备添加操作(无配件) /// </summary> /// <param name="model">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool AddEquipMnetInfo(EquipMnetInfoModel EquipModel) { try { ArrayList lstInsert = new ArrayList(); for (int i = 0; i <EquipModel.EquipmentCount; i++) { #region 参数设置 StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO officedba.EquipmentInfo"); sql.AppendLine(" (EquipmentNo "); sql.AppendLine(" ,EquipmentIndex "); sql.AppendLine(" ,CompanyCD "); sql.AppendLine(" ,EquipmentName "); sql.AppendLine(" ,Norm "); sql.AppendLine(" ,[Precision] "); sql.AppendLine(" ,BuyDate "); sql.AppendLine(" ,Provider "); sql.AppendLine(" ,[Type] "); sql.AppendLine(" ,Warranty "); sql.AppendLine(" ,ExaminePeriod "); sql.AppendLine(" ,CurrentUser "); sql.AppendLine(" ,CurrentDepartment "); sql.AppendLine(" ,FittingFlag "); sql.AppendLine(" ,Unit "); sql.AppendLine(" ,PYShort "); sql.AppendLine(" ,Money "); sql.AppendLine(" ,EquiFrom "); sql.AppendLine(" ,Attachment "); sql.AppendLine(" ,Status "); sql.AppendLine(" ,Creator "); sql.AppendLine(" ,CreateDate "); sql.AppendLine(" ,ModifiedUserid "); sql.AppendLine(" ,ModifiedDate "); sql.AppendLine(" ,EquipmentCount "); sql.AppendLine(" ,EquipmentDetail) "); sql.AppendLine("VALUES "); sql.AppendLine(" (@EquipmentNo "); sql.AppendLine(" ,@EquipmentIndex "); sql.AppendLine(" ,@CompanyCD "); sql.AppendLine(" ,@EquipmentName "); sql.AppendLine(" ,@Norm "); sql.AppendLine(" ,@Precision "); sql.AppendLine(" ,@BuyDate "); sql.AppendLine(" ,@Provider "); sql.AppendLine(" ,@Type"); sql.AppendLine(" ,@Warranty "); sql.AppendLine(" ,@ExaminePeriod "); sql.AppendLine(" ,@CurrentUser "); sql.AppendLine(" ,@CurrentDepartment "); sql.AppendLine(" ,@FittingFlag "); sql.AppendLine(" ,@Unit "); sql.AppendLine(" ,@PYShort "); sql.AppendLine(" ,@Money "); sql.AppendLine(" ,@EquiFrom "); sql.AppendLine(" ,@Attachment "); sql.AppendLine(" ,@Status "); sql.AppendLine(" ,@Creator "); sql.AppendLine(" ,@CreateDate "); sql.AppendLine(" ,@ModifiedUserid "); sql.AppendLine(" ,@ModifiedDate "); sql.AppendLine(" ,@EquipmentCount "); sql.AppendLine(" ,@EquipmentDetail) "); //设置参数 SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentNo", EquipModel.EquipmentNo.Split(',')[i])); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentIndex", EquipModel.EquipmentIndex)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", EquipModel.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentName", EquipModel.EquipmentName)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Norm", EquipModel.Norm)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Precision", EquipModel.Precision)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BuyDate", EquipModel.BuyDate.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Provider", EquipModel.Provider)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Type", EquipModel.Type.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Warranty", EquipModel.Warranty)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ExaminePeriod", EquipModel.ExaminePeriod)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentUser", EquipModel.CurrentUser.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrentDepartment", EquipModel.CurrentDepartment.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FittingFlag", EquipModel.FittingFlag)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Unit", EquipModel.Unit.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", EquipModel.PYShort)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Money", EquipModel.Money.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquiFrom", EquipModel.EquiFrom)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", EquipModel.Attachment)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", EquipModel.Status)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentDetail", EquipModel.EquipmentDetail)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", EquipModel.Creator.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", EquipModel.CreateDate.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserid", EquipModel.ModifiedUserid)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EquipmentCount", EquipModel.EquipmentCount.ToString())); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", EquipModel.ModifiedDate.ToString())); #endregion lstInsert.Add(comm);//数组加入插入基表的command } return SqlHelper.ExecuteTransWithArrayList(lstInsert); } catch { return false; } }
/// <summary> /// 查询设备明细信息列表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentDetailInfoAll(EquipMnetInfoModel equip_M, string ord, string BuyEndDate) { string sql = "select a.ID,a.EquipmentNo,a.EquipmentIndex,a.CompanyCD," + "a.EquipmentName,a.Norm,a.Precision,a.BuyDate,a.Provider,a.Type,a.Warranty," +"a.ExaminePeriod,a.CurrentUser,a.CurrentDepartment,isnull(a.Unit,'')Unit,a.Money," +"a.EquipmentDetail," +"case FittingFlag when 0 then '无' when 1 then '有' else '其他' end FittingFlag, " +"case (a.Status) when 0 then '空闲' when 1 then '使用' when 3 then '维修' when 5 then '报废' end Status,b.CodeName," +"isnull(c.EmployeeName,'')EmployeeName,isnull(d.DeptName,'')DeptName " +"from officedba.EquipmentInfo a " +"left outer join officedba.CodeEquipmentType b on a.Type=b.ID " +"LEFT OUTER JOIN " +"officedba.EmployeeInfo c " +"ON a.CurrentUser=c.ID " +" LEFT OUTER JOIN " + "officedba.DeptInfo d on a.CurrentDepartment=d.ID " + " where a.CompanyCD='" + equip_M.CompanyCD + "'"; if (equip_M.EquipmentName != "") sql += " and EquipmentName like '%" + equip_M.EquipmentName + "%'"; if (equip_M.Type != 0) sql += " and [Type]=" + equip_M.Type + ""; if (equip_M.BuyDate.ToString() != "" && equip_M.BuyDate.ToString() != "1900-1-1 0:00:00") sql += " and BuyDate>='" + equip_M.BuyDate + "'"; if (BuyEndDate != "") sql += " and BuyDate<='" + BuyEndDate + "'"; if (equip_M.CurrentDepartment != 0) sql += " and CurrentDepartment=" + equip_M.CurrentDepartment + ""; if (equip_M.Status.ToString() != "") sql += " and Status='" + equip_M.Status + "'"; sql = sql + ord; return SqlHelper.ExecuteSql(sql); }
/// <summary> /// 查询设备汇总信息列表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTotalTableBycondition(EquipMnetInfoModel equip_M,string EquipPre,int EpLength,int pageIndex,int pageCount,string ord, ref int TotalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; EpLength = EpLength+1; string sql = "select sum(TotalCount)TotalCount,Status,CodeName,sum(FreeCount)FreeCount,sum(WarningLimit)WarningLimit,Type,Convert(numeric(20,"+userInfo.SelPoint+"),sum(moneys)) as moneys " + "from (select CompanyCD,isnull(m.TotalCount,'') TotalCount,isnull(m.Type,'') Type,isnull(m.status,'') Status," + "isnull(m.CodeName,'') CodeName,isnull(m.WarningLimit,'') WarningLimit," + "isnull(n.FreeCount,'') FreeCount,moneys " + " from (select a.*,b.CodeName,b.WarningLimit from (select CompanyCD,count(*) as TotalCount ,[Type],SUM(MONEY) AS moneys," + "case Status when 0 then '空闲' when 1 then '使用'" + " when 3 then '维修'" + " when 5 then '报废' end Status" + " from officedba.EquipmentInfo a" + " group by [Type],Status,CompanyCD) a inner join officedba.CodeEquipmentType b" + " on a.[Type]=b.ID AND a.CompanyCD=b.CompanyCD) m" + " left outer join " + "(select a.*,b.CodeName,b.WarningLimit from (select count(*) as FreeCount ,[Type]," + "case Status when 0 then '空闲' when 1 then '使用'" + " when 3 then '维修'" + " when 5 then '报废' end Status" + " from officedba.EquipmentInfo a where 1=1 and a.status='0'" + " group by [Type],Status) a inner join officedba.CodeEquipmentType b" + " on a.[Type]=b.ID) n" + " on m.Type=n.Type where m.CompanyCD='" + equip_M.CompanyCD + "') x " + " where x.CompanyCD='" + equip_M.CompanyCD + "' "; if (equip_M.Type != 0) sql += " and x.Type=" + equip_M.Type + ""; //if (EquipPre != "") // sql += " and x.EpPre='" + EquipPre + "'"; if (equip_M.Status != "") sql += " and x.Status='" + equip_M.Status + "'"; sql += " group by Status,CodeName,Type"; //return SqlHelper.ExecuteSql(sql); return SqlHelper.CreateSqlByPageExcuteSql(sql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount); }
/// <summary> /// 修改设备信息(无配件) /// </summary> /// <param name="EquipModel">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool UpdateEquipInfo(EquipMnetInfoModel EquipModel) { return EquipMentInfoDBHelper.UpdateEquipMnetInfo(EquipModel); }
/// <summary> /// 更新设备和配件 /// </summary> /// <param name="EquipModel">设备信息</param> /// <param name="FitInfo">配件信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool UpdateEquipAndFitInfo(EquipMnetInfoModel EquipModel, string FitInfo) { return EquipMentInfoDBHelper.UpdateEquipMnetAndFitInfo(EquipModel, FitInfo); }
/// <summary> /// 添加设备信息(无配件) /// </summary> /// <param name="EquipModel">设备信息</param> /// <returns>添加是否成功 false:失败,true:成功</returns> public static bool AddEquipInfo(EquipMnetInfoModel EquipModel) { return EquipMentInfoDBHelper.AddEquipMnetInfo(EquipModel); }
/// <summary> /// 查询设备明细信息报表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentDetailInfoAll(EquipMnetInfoModel equip_M, string ord, string BuyEndDate) { try { return EquipMentInfoDBHelper.GetEquipmentDetailInfoAll(equip_M, ord, BuyEndDate); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 查询设备明细信息报表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentReportInfo(EquipMnetInfoModel equip_M,string EndDate,int pageIndex,int pageCount,string ord, ref int TotalCount) { try { return EquipMentInfoDBHelper.GetEquipmentReportInfo(equip_M, EndDate, pageIndex, pageCount, ord, ref TotalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 查询设备汇总信息列表(金额合计) /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTotalSumTableBycondition(EquipMnetInfoModel equip_M) { try { return EquipMentInfoDBHelper.GetEquipmentTotalSumTableBycondition(equip_M); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 查询设备汇总信息列表 /// </summary> /// <returns>DataTable</returns> public static DataTable GetEquipmentTotalInfoBycondition(EquipMnetInfoModel equip_M,string EquipPre,int Eplength,int pageIndex,int pageCount,string ord, ref int TotalCount) { try { return EquipMentInfoDBHelper.GetEquipmentTotalTableBycondition(equip_M, EquipPre, Eplength, pageIndex, pageCount, ord, ref TotalCount); } catch (System.Exception ex) { throw ex; } }