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