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 Hownet.Model.Measure GetModel(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,Name,IsUse,Sn,MeasureTypeID,IsEnd,Conversion from Measure ");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
Hownet.Model.Measure model = new Hownet.Model.Measure();
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]["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.Sn = ds.Tables[0].Rows[0]["Sn"].ToString();
if (ds.Tables[0].Rows[0]["MeasureTypeID"] != null && ds.Tables[0].Rows[0]["MeasureTypeID"].ToString() != "")
{
model.MeasureTypeID = int.Parse(ds.Tables[0].Rows[0]["MeasureTypeID"].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]["Conversion"] != null && ds.Tables[0].Rows[0]["Conversion"].ToString() != "")
{
model.Conversion = decimal.Parse(ds.Tables[0].Rows[0]["Conversion"].ToString());
}
model.A = 1;
return(model);
}
else
{
return(null);
}
}
public int AddNew(string TypeName, string NewName)
{
int id = 0;
if (TypeName == "Measure")
{
Hownet.BLL.Measure bllM = new Measure();
Hownet.Model.Measure modM = new Hownet.Model.Measure();
modM.Name = NewName;
modM.Sn = "";
id = bllM.Add(modM);
}
return(id);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Hownet.Model.Measure model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Measure set ");
strSql.Append("Name=@Name,");
strSql.Append("IsUse=@IsUse,");
strSql.Append("Sn=@Sn,");
strSql.Append("MeasureTypeID=@MeasureTypeID,");
strSql.Append("IsEnd=@IsEnd,");
strSql.Append("Conversion=@Conversion");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@Name", SqlDbType.NVarChar, 50),
new SqlParameter("@IsUse", SqlDbType.Bit, 1),
new SqlParameter("@Sn", SqlDbType.NVarChar, 50),
new SqlParameter("@MeasureTypeID", SqlDbType.Int, 4),
new SqlParameter("@IsEnd", SqlDbType.TinyInt, 1),
new SqlParameter("@Conversion", SqlDbType.Decimal, 9),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.Name;
parameters[1].Value = model.IsUse;
parameters[2].Value = model.Sn;
parameters[3].Value = model.MeasureTypeID;
parameters[4].Value = model.IsEnd;
parameters[5].Value = model.Conversion;
parameters[6].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Hownet.Model.Measure model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Measure(");
strSql.Append("Name,IsUse,Sn,MeasureTypeID,IsEnd,Conversion)");
strSql.Append(" values (");
strSql.Append("@Name,@IsUse,@Sn,@MeasureTypeID,@IsEnd,@Conversion)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Name", SqlDbType.NVarChar, 50),
new SqlParameter("@IsUse", SqlDbType.Bit, 1),
new SqlParameter("@Sn", SqlDbType.NVarChar, 50),
new SqlParameter("@MeasureTypeID", SqlDbType.Int, 4),
new SqlParameter("@IsEnd", SqlDbType.TinyInt, 1),
new SqlParameter("@Conversion", SqlDbType.Decimal, 9)
};
parameters[0].Value = model.Name;
parameters[1].Value = model.IsUse;
parameters[2].Value = model.Sn;
parameters[3].Value = model.MeasureTypeID;
parameters[4].Value = model.IsEnd;
parameters[5].Value = model.Conversion;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}