public DataSet PrintLable(int TaskID, int TicketID, int GroupBy, int ColorID, int SizeID, int OneAmount) { Hownet.DAL.ProductTaskMain dalPTM = new ProductTaskMain(); Hownet.Model.ProductTaskMain modPTM = dalPTM.GetModel(TaskID); Hownet.DAL.Materiel dalMat = new Materiel(); Hownet.Model.Materiel modMat = dalMat.GetModel(modPTM.MaterielID); Hownet.DAL.Measure dalMea = new Measure(); Hownet.Model.Measure modMea = dalMea.GetModel(modMat.MeasureID); string mea = string.Empty; if (modMea != null) { mea = modMea.Name; } StringBuilder strSql = new StringBuilder(); //strSql.Append("SELECT WorkTicketIDCard.IDCardNo, '第' + CAST(WorkTicket.BoxNum AS nvarchar) + '箱,' AS BoxNum, WorkTicket.BoxNum as BN,"); //strSql.Append(" Color.Name AS ColorName, Size.Name AS SizeName, '第' + CAST(WorkTicketIDCard.GroupBy AS nvarchar) "); //strSql.Append(" + '组' AS GroupBy, Materiel.Name AS MaterielName, CAST(WorkTicket.Amount AS nvarchar) + '件' AS Amount "); //strSql.Append(" ,(Select Name from Materiel where (id=workticket.BrandID)) as BrandName,WorkTicketIDCard.GroupBy as GB "); //strSql.Append(" FROM Color INNER JOIN "); //strSql.Append(" WorkTicket ON Color.ID = WorkTicket.ColorID INNER JOIN Size ON WorkTicket.SizeID = Size.ID INNER JOIN "); //strSql.Append(" WorkTicketIDCard ON WorkTicket.ID = WorkTicketIDCard.TicketID INNER JOIN ProductTaskMain ON WorkTicketIDCard.TaskID "); //strSql.Append(" = ProductTaskMain.ID INNER JOIN Materiel ON ProductTaskMain.MaterielID = Materiel.ID "); strSql.Append("SELECT (CAST(WorkTicketIDCard.TicketID AS nvarchar)+cast( WorkTicketIDCard.GroupBy as nvarchar)) as QR, WorkTicketIDCard.IDCardNo, '第' + CAST(WorkTicket.BoxNum AS nvarchar) + '箱,' AS BoxNum,WorkTicket.BoxNum as BN, ProductTaskMain.BedNO, "); strSql.Append(" WorkTicket.BoxNum AS BN, Color.Name AS ColorName, Size.Name AS SizeName, '第' + CAST(WorkTicketIDCard.GroupBy "); strSql.Append("AS nvarchar) + '组' AS GroupBy, Materiel_1.Name AS MaterielName, CAST(WorkTicket.Amount AS nvarchar) + '" + mea + "' AS Amount,WorkTicketIDCard.GroupBy as CaiPian, "); strSql.Append("(SELECT Name FROM Materiel WHERE (ID = ProductTaskMain.BrandID)) AS BrandName, WorkTicketIDCard.GroupBy AS GB, "); strSql.Append(" Color_1.Name AS ColorOneName, Color_2.Name AS ColorTwoName FROM Color INNER JOIN WorkTicket ON Color.ID = "); strSql.Append(" WorkTicket.ColorID INNER JOIN Size ON WorkTicket.SizeID = Size.ID INNER JOIN WorkTicketIDCard ON WorkTicket.ID = "); strSql.Append(" WorkTicketIDCard.TicketID INNER JOIN ProductTaskMain ON WorkTicketIDCard.TaskID = ProductTaskMain.ID INNER JOIN "); strSql.Append(" Materiel AS Materiel_1 ON ProductTaskMain.MaterielID = Materiel_1.ID LEFT OUTER JOIN Color AS Color_1 ON "); strSql.Append(" WorkTicket.ColorOneID = Color_1.ID LEFT OUTER JOIN Color AS Color_2 ON WorkTicket.ColorTwoID = Color_2.ID"); strSql.Append(" WHERE (WorkTicketIDCard.TaskID = @TaskID) And (WorkTicket.OneAmount=@OneAmount)"); if (TicketID > 0) { strSql.Append(" And (WorkTicketIDCard.TicketID = @TicketID) "); } if (GroupBy > 0) { strSql.Append(" AND (WorkTicketIDCard.GroupBy = @GroupBy) "); } if (ColorID > 0) { strSql.Append(" And (WorkTicket.ColorID=@ColorID) "); } if (SizeID > 0) { strSql.Append(" And (WorkTicket.SizeID=@SizeID) "); } strSql.Append(" ORDER BY WorkTicketIDCard.GroupBy, WorkTicket.BoxNum"); SqlParameter[] sps = { new SqlParameter("@TicketID", TicketID), new SqlParameter("@GroupBy", GroupBy), new SqlParameter("@ColorID", ColorID), new SqlParameter("@TaskID", TaskID), new SqlParameter("@SizeID", SizeID), new SqlParameter("@OneAmount", OneAmount) }; return(DbHelperSQL.Query(strSql.ToString(), sps)); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Hownet.Model.Materiel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into Materiel("); strSql.Append("Name,TypeID,MeasureID,Sn,Remark,AttributeID,SecondMeasureID,Conversion,IsEnd,IsUse,Image,Designers,SelectSpec,TiaoMaH,ChengBengJ,LingShouJia,YiJiDaiLiJia,ErJiDaiLiJia,SanJiDaiLiJia)"); strSql.Append(" values ("); strSql.Append("@Name,@TypeID,@MeasureID,@Sn,@Remark,@AttributeID,@SecondMeasureID,@Conversion,@IsEnd,@IsUse,@Image,@Designers,@SelectSpec,@TiaoMaH,@ChengBengJ,@LingShouJia,@YiJiDaiLiJia,@ErJiDaiLiJia,@SanJiDaiLiJia)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@Name", SqlDbType.NVarChar, 50), new SqlParameter("@TypeID", SqlDbType.Int, 4), new SqlParameter("@MeasureID", SqlDbType.Int, 4), new SqlParameter("@Sn", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 255), new SqlParameter("@AttributeID", SqlDbType.Int, 4), new SqlParameter("@SecondMeasureID", SqlDbType.Int, 4), new SqlParameter("@Conversion", SqlDbType.Decimal, 9), new SqlParameter("@IsEnd", SqlDbType.TinyInt, 1), new SqlParameter("@IsUse", SqlDbType.Bit, 1), new SqlParameter("@Image", SqlDbType.NVarChar, 50), new SqlParameter("@Designers", SqlDbType.Int, 4), new SqlParameter("@SelectSpec", SqlDbType.Int, 4), new SqlParameter("@TiaoMaH", SqlDbType.NVarChar, 50), new SqlParameter("@ChengBengJ", SqlDbType.Decimal, 9), new SqlParameter("@LingShouJia", SqlDbType.Decimal, 9), new SqlParameter("@YiJiDaiLiJia", SqlDbType.Decimal, 9), new SqlParameter("@ErJiDaiLiJia", SqlDbType.Decimal, 9), new SqlParameter("@SanJiDaiLiJia", SqlDbType.Decimal, 9) }; parameters[0].Value = model.Name; parameters[1].Value = model.TypeID; parameters[2].Value = model.MeasureID; parameters[3].Value = model.Sn; parameters[4].Value = model.Remark; parameters[5].Value = model.AttributeID; parameters[6].Value = model.SecondMeasureID; parameters[7].Value = model.Conversion; parameters[8].Value = model.IsEnd; parameters[9].Value = model.IsUse; parameters[10].Value = model.Image; parameters[11].Value = model.Designers; parameters[12].Value = model.SelectSpec; parameters[13].Value = model.TiaoMaH; parameters[14].Value = model.ChengBengJ; parameters[15].Value = model.LingShouJia; parameters[16].Value = model.YiJiDaiLiJia; parameters[17].Value = model.ErJiDaiLiJia; parameters[18].Value = model.SanJiDaiLiJia; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 得到一个对象实体 /// </summary> public Hownet.Model.Materiel GetModel(int ID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 ID,Name,TypeID,MeasureID,Sn,Remark,AttributeID,SecondMeasureID,Conversion,IsEnd,IsUse,Image,Designers,SelectSpec,TiaoMaH,ChengBengJ,LingShouJia,YiJiDaiLiJia,ErJiDaiLiJia,SanJiDaiLiJia from Materiel "); strSql.Append(" where ID=@ID"); SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int, 4) }; parameters[0].Value = ID; Hownet.Model.Materiel model = new Hownet.Model.Materiel(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "") { model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString()); } model.Name = ds.Tables[0].Rows[0]["Name"].ToString(); if (ds.Tables[0].Rows[0]["TypeID"] != null && ds.Tables[0].Rows[0]["TypeID"].ToString() != "") { model.TypeID = int.Parse(ds.Tables[0].Rows[0]["TypeID"].ToString()); } if (ds.Tables[0].Rows[0]["MeasureID"] != null && ds.Tables[0].Rows[0]["MeasureID"].ToString() != "") { model.MeasureID = int.Parse(ds.Tables[0].Rows[0]["MeasureID"].ToString()); } model.Sn = ds.Tables[0].Rows[0]["Sn"].ToString(); model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString(); if (ds.Tables[0].Rows[0]["AttributeID"] != null && ds.Tables[0].Rows[0]["AttributeID"].ToString() != "") { model.AttributeID = int.Parse(ds.Tables[0].Rows[0]["AttributeID"].ToString()); } if (ds.Tables[0].Rows[0]["SecondMeasureID"] != null && ds.Tables[0].Rows[0]["SecondMeasureID"].ToString() != "") { model.SecondMeasureID = int.Parse(ds.Tables[0].Rows[0]["SecondMeasureID"].ToString()); } if (ds.Tables[0].Rows[0]["Conversion"] != null && ds.Tables[0].Rows[0]["Conversion"].ToString() != "") { model.Conversion = decimal.Parse(ds.Tables[0].Rows[0]["Conversion"].ToString()); } if (ds.Tables[0].Rows[0]["IsEnd"] != null && ds.Tables[0].Rows[0]["IsEnd"].ToString() != "") { model.IsEnd = int.Parse(ds.Tables[0].Rows[0]["IsEnd"].ToString()); } if (ds.Tables[0].Rows[0]["IsUse"] != null && ds.Tables[0].Rows[0]["IsUse"].ToString() != "") { if ((ds.Tables[0].Rows[0]["IsUse"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsUse"].ToString().ToLower() == "true")) { model.IsUse = true; } else { model.IsUse = false; } } model.Image = ds.Tables[0].Rows[0]["Image"].ToString(); if (ds.Tables[0].Rows[0]["Designers"] != null && ds.Tables[0].Rows[0]["Designers"].ToString() != "") { model.Designers = int.Parse(ds.Tables[0].Rows[0]["Designers"].ToString()); } if (ds.Tables[0].Rows[0]["SelectSpec"] != null && ds.Tables[0].Rows[0]["SelectSpec"].ToString() != "") { model.SelectSpec = int.Parse(ds.Tables[0].Rows[0]["SelectSpec"].ToString()); } model.TiaoMaH = ds.Tables[0].Rows[0]["TiaoMaH"].ToString(); if (ds.Tables[0].Rows[0]["ChengBengJ"] != null && ds.Tables[0].Rows[0]["ChengBengJ"].ToString() != "") { model.ChengBengJ = decimal.Parse(ds.Tables[0].Rows[0]["ChengBengJ"].ToString()); } if (ds.Tables[0].Rows[0]["LingShouJia"] != null && ds.Tables[0].Rows[0]["LingShouJia"].ToString() != "") { model.LingShouJia = decimal.Parse(ds.Tables[0].Rows[0]["LingShouJia"].ToString()); } if (ds.Tables[0].Rows[0]["YiJiDaiLiJia"] != null && ds.Tables[0].Rows[0]["YiJiDaiLiJia"].ToString() != "") { model.YiJiDaiLiJia = decimal.Parse(ds.Tables[0].Rows[0]["YiJiDaiLiJia"].ToString()); } if (ds.Tables[0].Rows[0]["ErJiDaiLiJia"] != null && ds.Tables[0].Rows[0]["ErJiDaiLiJia"].ToString() != "") { model.ErJiDaiLiJia = decimal.Parse(ds.Tables[0].Rows[0]["ErJiDaiLiJia"].ToString()); } if (ds.Tables[0].Rows[0]["SanJiDaiLiJia"] != null && ds.Tables[0].Rows[0]["SanJiDaiLiJia"].ToString() != "") { model.SanJiDaiLiJia = decimal.Parse(ds.Tables[0].Rows[0]["SanJiDaiLiJia"].ToString()); } model.A = 1; return(model); } else { return(null); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Hownet.Model.Materiel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Materiel set "); strSql.Append("Name=@Name,"); strSql.Append("TypeID=@TypeID,"); strSql.Append("MeasureID=@MeasureID,"); strSql.Append("Sn=@Sn,"); strSql.Append("Remark=@Remark,"); strSql.Append("AttributeID=@AttributeID,"); strSql.Append("SecondMeasureID=@SecondMeasureID,"); strSql.Append("Conversion=@Conversion,"); strSql.Append("IsEnd=@IsEnd,"); strSql.Append("IsUse=@IsUse,"); strSql.Append("Image=@Image,"); strSql.Append("Designers=@Designers,"); strSql.Append("SelectSpec=@SelectSpec,"); strSql.Append("TiaoMaH=@TiaoMaH,"); strSql.Append("ChengBengJ=@ChengBengJ,"); strSql.Append("LingShouJia=@LingShouJia,"); strSql.Append("YiJiDaiLiJia=@YiJiDaiLiJia,"); strSql.Append("ErJiDaiLiJia=@ErJiDaiLiJia,"); strSql.Append("SanJiDaiLiJia=@SanJiDaiLiJia"); strSql.Append(" where ID=@ID"); SqlParameter[] parameters = { new SqlParameter("@Name", SqlDbType.NVarChar, 50), new SqlParameter("@TypeID", SqlDbType.Int, 4), new SqlParameter("@MeasureID", SqlDbType.Int, 4), new SqlParameter("@Sn", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 255), new SqlParameter("@AttributeID", SqlDbType.Int, 4), new SqlParameter("@SecondMeasureID", SqlDbType.Int, 4), new SqlParameter("@Conversion", SqlDbType.Decimal, 9), new SqlParameter("@IsEnd", SqlDbType.TinyInt, 1), new SqlParameter("@IsUse", SqlDbType.Bit, 1), new SqlParameter("@Image", SqlDbType.NVarChar, 50), new SqlParameter("@Designers", SqlDbType.Int, 4), new SqlParameter("@SelectSpec", SqlDbType.Int, 4), new SqlParameter("@TiaoMaH", SqlDbType.NVarChar, 50), new SqlParameter("@ChengBengJ", SqlDbType.Decimal, 9), new SqlParameter("@LingShouJia", SqlDbType.Decimal, 9), new SqlParameter("@YiJiDaiLiJia", SqlDbType.Decimal, 9), new SqlParameter("@ErJiDaiLiJia", SqlDbType.Decimal, 9), new SqlParameter("@SanJiDaiLiJia", SqlDbType.Decimal, 9), new SqlParameter("@ID", SqlDbType.Int, 4) }; parameters[0].Value = model.Name; parameters[1].Value = model.TypeID; parameters[2].Value = model.MeasureID; parameters[3].Value = model.Sn; parameters[4].Value = model.Remark; parameters[5].Value = model.AttributeID; parameters[6].Value = model.SecondMeasureID; parameters[7].Value = model.Conversion; parameters[8].Value = model.IsEnd; parameters[9].Value = model.IsUse; parameters[10].Value = model.Image; parameters[11].Value = model.Designers; parameters[12].Value = model.SelectSpec; parameters[13].Value = model.TiaoMaH; parameters[14].Value = model.ChengBengJ; parameters[15].Value = model.LingShouJia; parameters[16].Value = model.YiJiDaiLiJia; parameters[17].Value = model.ErJiDaiLiJia; parameters[18].Value = model.SanJiDaiLiJia; parameters[19].Value = model.ID; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }