/// <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;
}
}
