public static List <mg_BOMModel> QueryBOMForStepEditing(string part_id)
{
List <mg_BOMModel> list = null;
string joinSre = (!string.IsNullOrEmpty(part_id) && part_id != "0") ? @" inner join [mg_part_bom_rel] pbrel on pbrel.bom_id=bom.bom_id and pbrel.part_id=" + part_id : "";
string sql = @"
SELECT bom.[bom_id]
,[bom_PN]
,bom_desc
FROM [mg_BOM] bom
" + joinSre + @"
order by bom_PN,bom_desc";
DataTable dt = SqlHelper.GetDataDataTable(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql, null);
if (DataHelper.HasData(dt))
{
list = new List <mg_BOMModel>();
foreach (DataRow row in dt.Rows)
{
mg_BOMModel model = new mg_BOMModel();
model.bom_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_id"));
model.bom_PN = DataHelper.GetCellDataToStr(row, "bom_PN") + " | " + DataHelper.GetCellDataToStr(row, "bom_desc");
list.Add(model);
}
}
return(list);
}
void SaveBOM()
{
string bom_id = Request.Params["bom_id"];
string bom_PN = Request.Params["bom_PN"];
string bom_customerPN = Request.Params["bom_customerPN"];
string bom_isCustomerPN = Request.Params["bom_isCustomerPN"];
string bom_picture = Request.Params["bom_picture"];
string bom_weight = Request.Params["bom_weight"];
string bom_leve = Request.Params["bom_leve"];
string bom_colorid = Request.Params["bom_colorid"];
string bom_materialid = Request.Params["bom_materialid"];
string bom_categoryid = Request.Params["bom_categoryid"];
string bom_prop = Request.Params["bom_prop"];
string bom_storeid = Request.Params["bom_storeid"];
string bom_suppllerid = Request.Params["bom_suppllerid"];
string bom_profile = Request.Params["bom_profile"];
string bom_descCH = Request.Params["bom_descCH"];
string bom_desc = Request.Params["bom_desc"];
string partIDs = Request.Params["partIDs"];
string barcode_start = Request.Params["barcode_start"];
string barcode_number = Request.Params["barcode_number"];
mg_BOMModel model = new mg_BOMModel();
model.bom_id = NumericParse.StringToInt(bom_id);
model.bom_PN = bom_PN;
model.bom_customerPN = bom_customerPN;
model.bom_isCustomerPN = NumericParse.StringToInt(bom_isCustomerPN);
model.bom_picture = bom_picture;
model.bom_weight = NumericParse.StringToInt(bom_weight);
model.bom_leve = NumericParse.StringToInt(bom_leve);
model.bom_colorid = NumericParse.StringToInt(bom_colorid);
model.bom_materialid = NumericParse.StringToInt(bom_materialid);
model.bom_categoryid = NumericParse.StringToInt(bom_categoryid);
model.bom_storeid = NumericParse.StringToInt(bom_storeid);
// model.bom_prop = NumericParse.StringToInt(bom_prop);
model.bom_suppllerid = NumericParse.StringToInt(bom_suppllerid);
model.bom_profile = bom_profile;
model.bom_descCH = bom_descCH;
model.bom_desc = bom_desc;
model.partIDs = partIDs;
model.barcode_start = NumericParse.StringToInt(barcode_start);
model.barcode_number = NumericParse.StringToInt(barcode_number);
string json = mg_BomBLL.SaveBOM(model);
Response.Write(json);
Response.End();
}
public static List <mg_BOMModel> QueryListForFirstPage(string pagesize, out string total)
{
total = "0";
List <mg_BOMModel> list = null;
string sql1 = @"select count([bom_id]) total from [mg_BOM];";
string sql2 = @"
with data as
(
select p.part_id,p.part_no,pbrel.bom_id from mg_part_bom_rel pbrel left join mg_part p on pbrel.part_id=p.part_id
)
SELECT top " + pagesize + @" [bom_id]
,[bom_PN]
,[bom_customerPN]
,[bom_isCustomerPN]
,case [bom_isCustomerPN]
when 1 then '是'
else '否'
end bom_isCustomerPNName
,[bom_leve]
,l.prop_name [bom_leveName]
,[bom_materialid]
,m.prop_name [bom_material]
,[bom_suppllerid]
,ms.SupplierName [bom_suppller]
,[bom_categoryid]
,ca.prop_name [bom_category]
,[bom_storeid]
,st.prop_name [bom_storeName]
,[bom_colorid]
,mm.Prop_type [bom_proptype]
,mm.prop_name [bom_propname]
,mm.prop_id [bom_propid]
,co.prop_name [bom_colorname]
,[bom_profile]
,[bom_weight]
,[bom_desc]
,[bom_descCH]
,[bom_picture]
,STUFF((SELECT ','+cast (part_id as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partIDs
,STUFF((SELECT ','+cast (part_no as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partNOs
,b.barcode_start
,b.barcode_number
FROM [mg_BOM] b
left join mg_Property m on b.bom_materialid=m.prop_id
left join mg_Property s on b.bom_suppllerid=s.prop_id
left join mg_Property ca on b.bom_categoryid=ca.prop_id
left join mg_Property co on b.bom_colorid=co.prop_id
left join Mat_SupplierList ms on b.bom_suppllerid=ms.SupplierID
left join Mg_MaterialProperty mm on ms.WareHouseID=mm.prop_id
left join mg_Property l on b.bom_leve=l.prop_id
left join mg_Property st on b.bom_storeid=st.prop_id
order by b.bom_id desc;
";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
DataTable dt2 = ds.Tables["data"];
list = new List <mg_BOMModel>();
foreach (DataRow row in dt2.Rows)
{
mg_BOMModel model = new mg_BOMModel();
model.bom_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_id"));
model.bom_isCustomerPN = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_isCustomerPN"));
model.bom_colorid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_colorid"));
model.bom_materialid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_materialid"));
model.bom_categoryid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_categoryid"));
model.bom_suppllerid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_suppllerid"));
model.bom_leve = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_leve"));
model.bom_weight = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_weight"));
model.partIDs = DataHelper.GetCellDataToStr(row, "partIDs");
model.bom_picture = DataHelper.GetCellDataToStr(row, "bom_picture");
model.bom_PN = DataHelper.GetCellDataToStr(row, "bom_PN");
model.bom_customerPN = DataHelper.GetCellDataToStr(row, "bom_customerPN");
model.bom_isCustomerPNName = DataHelper.GetCellDataToStr(row, "bom_isCustomerPNName");
model.bom_colorname = DataHelper.GetCellDataToStr(row, "bom_colorname");
model.bom_material = DataHelper.GetCellDataToStr(row, "bom_material");
model.bom_category = DataHelper.GetCellDataToStr(row, "bom_category");
model.bom_suppller = DataHelper.GetCellDataToStr(row, "bom_suppller");
model.bom_profile = DataHelper.GetCellDataToStr(row, "bom_profile");
model.bom_desc = DataHelper.GetCellDataToStr(row, "bom_desc");
model.bom_descCH = DataHelper.GetCellDataToStr(row, "bom_descCH");
model.partNOs = DataHelper.GetCellDataToStr(row, "partNOs");
model.bom_leveName = DataHelper.GetCellDataToStr(row, "bom_leveName");
model.bom_storeid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_storeid"));
model.bom_storeName = DataHelper.GetCellDataToStr(row, "bom_storeName");
model.barcode_start = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_start"));
model.barcode_number = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "barcode_number"));
model.bom_propid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "bom_propid"));
model.bom_propname = DataHelper.GetCellDataToStr(row, "bom_propname");
list.Add(model);
}
}
return(list);
}
public static bool AddBOM(mg_BOMModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"declare @i int;
SELECT @i=max([bom_id]) FROM [mg_BOM];
if @i is null
begin
set @i=1
end
else
begin
set @i=@i+1
end;
");
strSql.Append("insert into mg_BOM(");
strSql.Append("bom_id,bom_PN,bom_customerPN,bom_isCustomerPN,bom_leve,bom_materialid,bom_suppllerid,bom_categoryid,bom_colorid,bom_profile,bom_weight,bom_desc,bom_descCH,bom_picture,bom_storeid,barcode_start,barcode_number)");
strSql.Append(" values (");
strSql.Append("@i,@bom_PN,@bom_customerPN,@bom_isCustomerPN,@bom_leve,@bom_materialid,@bom_suppllerid,@bom_categoryid,@bom_colorid,@bom_profile,@bom_weight,@bom_desc,@bom_descCH,@bom_picture,@bom_storeid,@barcode_start,@barcode_number);");
SqlParameter[] parameters =
{
new SqlParameter("@bom_PN", SqlDbType.NVarChar),
new SqlParameter("@bom_customerPN", SqlDbType.NVarChar),
new SqlParameter("@bom_isCustomerPN", SqlDbType.Int),
new SqlParameter("@bom_leve", SqlDbType.Int),
new SqlParameter("@bom_materialid", SqlDbType.Int),
new SqlParameter("@bom_suppllerid", SqlDbType.Int),
new SqlParameter("@bom_categoryid", SqlDbType.Int),
new SqlParameter("@bom_colorid", SqlDbType.Int),
new SqlParameter("@bom_profile", SqlDbType.NVarChar),
new SqlParameter("@bom_weight", SqlDbType.Int),
new SqlParameter("@bom_desc", SqlDbType.NVarChar),
new SqlParameter("@bom_descCH", SqlDbType.VarChar),
new SqlParameter("@bom_picture", SqlDbType.NVarChar),
new SqlParameter("@bom_storeid", SqlDbType.Int),
new SqlParameter("@barcode_start", SqlDbType.Int),
new SqlParameter("@barcode_number", SqlDbType.Int)
};
parameters[0].Value = model.bom_PN;
parameters[1].Value = model.bom_customerPN;
parameters[2].Value = model.bom_isCustomerPN;
parameters[3].Value = model.bom_leve;
parameters[4].Value = model.bom_materialid;
parameters[5].Value = model.bom_suppllerid;
parameters[6].Value = model.bom_categoryid;
parameters[7].Value = model.bom_colorid;
parameters[8].Value = model.bom_profile;
parameters[9].Value = model.bom_weight;
parameters[10].Value = model.bom_desc;
parameters[11].Value = model.bom_descCH;
parameters[12].Value = model.bom_picture;
parameters[13].Value = model.bom_storeid;
parameters[14].Value = model.barcode_start;
parameters[15].Value = model.barcode_number;
if (!string.IsNullOrEmpty(model.partIDs))
{
string[] idArr = model.partIDs.Split(',');
foreach (string id in idArr)
{
strSql.Append("INSERT INTO [mg_part_bom_rel] ([part_id],[bom_id]) VALUES (" + id + ",@i);");
}
}
List <SqlParameter> list = new List <SqlParameter>();
list.AddRange(parameters);
return(SqlHelper.ExecuteSqlTran(SqlHelper.SqlConnString, strSql.ToString(), list));
}
/*
*
* 姜任鹏
*
*/
public static bool UpdateBOM(mg_BOMModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update mg_BOM set ");
strSql.Append("bom_PN=@bom_PN,");
strSql.Append("bom_customerPN=@bom_customerPN,");
strSql.Append("bom_isCustomerPN=@bom_isCustomerPN,");
strSql.Append("bom_leve=@bom_leve,");
strSql.Append("bom_materialid=@bom_materialid,");
strSql.Append("bom_suppllerid=@bom_suppllerid,");
strSql.Append("bom_categoryid=@bom_categoryid,");
strSql.Append("bom_colorid=@bom_colorid,");
strSql.Append("bom_profile=@bom_profile,");
strSql.Append("bom_weight=@bom_weight,");
strSql.Append("bom_desc=@bom_desc,");
strSql.Append("bom_descCH=@bom_descCH,");
strSql.Append("bom_picture=@bom_picture,");
strSql.Append("bom_storeid=@bom_storeid,");
strSql.Append("barcode_start =@barcode_start ,");
strSql.Append("barcode_number =@barcode_number ");
strSql.Append(" where bom_id=@bom_id; ");
if (!string.IsNullOrEmpty(model.partIDs))
{
strSql.Append("delete from [mg_part_bom_rel] where bom_id=@bom_id ;");
string[] idArr = model.partIDs.Split(',');
foreach (string id in idArr)
{
strSql.Append("INSERT INTO [mg_part_bom_rel] ([part_id],[bom_id]) VALUES (" + id + ",@bom_id);");
}
}
SqlParameter[] parameters =
{
new SqlParameter("@bom_id", SqlDbType.Int),
new SqlParameter("@bom_PN", SqlDbType.NVarChar),
new SqlParameter("@bom_customerPN", SqlDbType.NVarChar),
new SqlParameter("@bom_isCustomerPN", SqlDbType.Int),
new SqlParameter("@bom_leve", SqlDbType.Int),
new SqlParameter("@bom_materialid", SqlDbType.Int),
new SqlParameter("@bom_suppllerid", SqlDbType.Int),
new SqlParameter("@bom_categoryid", SqlDbType.Int),
new SqlParameter("@bom_colorid", SqlDbType.Int),
new SqlParameter("@bom_profile", SqlDbType.NVarChar),
new SqlParameter("@bom_weight", SqlDbType.Int),
new SqlParameter("@bom_desc", SqlDbType.NVarChar),
new SqlParameter("@bom_descCH", SqlDbType.VarChar),
new SqlParameter("@bom_picture", SqlDbType.NVarChar),
new SqlParameter("@bom_storeid", SqlDbType.Int),
new SqlParameter("@barcode_start", SqlDbType.Int),
new SqlParameter("@barcode_number", SqlDbType.Int)
};
parameters[0].Value = model.bom_id;
parameters[1].Value = model.bom_PN;
parameters[2].Value = model.bom_customerPN;
parameters[3].Value = model.bom_isCustomerPN;
parameters[4].Value = model.bom_leve;
parameters[5].Value = model.bom_materialid;
parameters[6].Value = model.bom_suppllerid;
parameters[7].Value = model.bom_categoryid;
parameters[8].Value = model.bom_colorid;
parameters[9].Value = model.bom_profile;
parameters[10].Value = model.bom_weight;
parameters[11].Value = model.bom_desc;
parameters[12].Value = model.bom_descCH;
parameters[13].Value = model.bom_picture;
parameters[14].Value = model.bom_storeid;
parameters[15].Value = model.barcode_start;
parameters[16].Value = model.barcode_number;
List <SqlParameter> list = new List <SqlParameter>();
list.AddRange(parameters);
return(SqlHelper.ExecuteSqlTran(SqlHelper.SqlConnString, strSql.ToString(), list));
}
private static string AddBOM(mg_BOMModel model)
{
bool flag = mg_BomDAL.AddBOM(model);
return(flag ? "true" : "false");
}
/*
*
* 姜任鹏
*
*/
public static string SaveBOM(mg_BOMModel model)
{
return(model.bom_id == 0 ? AddBOM(model) : UpdateBOM(model));
}
