public MessageModel add(BOMMainModel obj)
{
List <string> sqlList = new List <string>();
string num = "(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'bommain')";
string sqlMain = "insert into bommain (materiel,quantity,jump,isActive,num) " +
" values(@materiel, @quantity, @jump,@isActive, concat('BOM',LPAD(" + num + ",9,'0')));";
sqlList.Add(sqlMain);
string sqlDetail = "insert into bomdetail (parent,materiel,quantity,remark,children) values ";
//string sqlMateriel = "update materiel set bom = 1 where id = @materiel";
if (obj.isActive)
{
string sqlOtherBOM = "update bommain set isActive = 0 where materiel = @materiel and id != last_insert_id()";
sqlList.Add(sqlOtherBOM);
string sqlUpdateDetail = "update bomdetail dtl inner join bommain main on dtl.parent = main.id set dtl.children = last_insert_id() where dtl.materiel = @materiel and main.isActive = 1";
sqlList.Add(sqlUpdateDetail);
}
string children;
for (int i = 0; i < obj.list.Count; i++)
{
if (i != 0)
{
sqlDetail += ",";
}
children = "(select id from bommain where materiel = '" + obj.list[i].materiel + "' and isActive = 1 limit 1)";
sqlDetail += " (last_insert_id(),'" + obj.list[i].materiel + "','" + obj.list[i].quantity + "' ,'" + obj.list[i].remark + "'," + children + ")";
}
sqlList.Add(sqlDetail);
//sqlList.Add(sqlMateriel);
Dictionary <string, object> paras = new Dictionary <string, object>();
paras.Add("@materiel", obj.materiel);
paras.Add("@quantity", obj.quantity);
paras.Add("@jump", obj.jump);
paras.Add("@isActive", obj.isActive);
bool result = h.ExcuteTransaction(sqlList, paras);
MessageModel msg = new MessageModel();
if (result == true)
{
msg = new MessageModel(0, "新建成功");
}
else
{
msg = new MessageModel(10005, "新建失败");
}
return(msg);
}
public MessageModel set(BOMMainModel obj)
{
MessageModel msg = new MessageModel();
List <int> list_del = new List <int>();
for (int i = 0; i < obj.list.Count; i++)
{
list_del.Add(obj.list[i].materiel);
}
string oldsql = "SELECT dtl.id,dtl.materiel, dtl.remark,dtl.quantity FROM bomdetail dtl inner join bommain main on dtl.parent = main.id WHERE dtl.parent = '" + obj.id + "'";
DataTable dtold = h.ExecuteQuery(oldsql, CommandType.Text);
Dictionary <int, BOMDetailModel> map_old = new Dictionary <int, BOMDetailModel>();
List <string> sqlList = new List <string>();
Dictionary <string, object> paras = new Dictionary <string, object>();
paras.Add("@id", obj.id);
paras.Add("@quantity", obj.quantity);
paras.Add("@jump", obj.jump);
var sqlnew = "";
var sqlupdate = "";
var sqldel = "";
var children = "";
var sqlUpMain = "update bommain set quantity = @quantity , jump = @jump where id = @id";
sqlList.Add(sqlUpMain);
for (int i = 0; i < dtold.Rows.Count; i++)
{
var materiel = Convert.ToInt32(dtold.Rows[i]["materiel"]);
map_old.Add(materiel, new BOMDetailModel(dtold.Rows[i]));
if (!list_del.Contains(materiel))
{
if (sqldel == "")
{
sqldel = "delete from bomdetail where parent = '" + obj.id + "' AND materiel in (";
}
else
{
sqldel += ",";
}
sqldel += " '" + materiel + "' ";
}
}
if (sqldel != "")
{
sqldel += ")";
sqlList.Add(sqldel);
}
for (int i = 0; i < obj.list.Count; i++)
{
if (map_old.ContainsKey(obj.list[i].materiel))
{
var remark = map_old[obj.list[i].materiel].remark;
var quantity = map_old[obj.list[i].materiel].quantity;
var id = map_old[obj.list[i].materiel].id;
if (obj.list[i].remark != remark || obj.list[i].quantity != quantity)
{
sqlupdate = "";
if (obj.list[i].remark != remark)
{
sqlupdate += " update bomdetail set remark = '" + obj.list[i].remark + "'";
}
if (obj.list[i].quantity != quantity)
{
if (sqlupdate == "")
{
sqlupdate += "update bomdetail set quantity = '" + obj.list[i].quantity + "'";
}
else
{
sqlupdate += ",quantity = '" + obj.list[i].quantity + "'";
}
}
sqlupdate += " where materiel = '" + obj.list[i].materiel + "' AND parent = '" + obj.id + "'";
sqlList.Add(sqlupdate);
}
}
else
{
if (sqlnew == "")
{
sqlnew += "insert into bomdetail (parent,materiel,quantity,remark,children) values ";
}
else
{
sqlnew += ",";
}
children = "(select id from bommain where materiel = '" + obj.list[i].materiel + "' and isActive = 1 limit 1)";
sqlnew += " ('" + obj.id + "','" + obj.list[i].materiel + "','" + obj.list[i].quantity + "' ,'" + obj.list[i].remark + "'," + children + ")";
}
}
if (sqlnew != "")
{
sqlList.Add(sqlnew);
}
bool result = h.ExcuteTransaction(sqlList, paras);
if (result == true)
{
msg = new MessageModel(0, "保存成功");
}
else
{
msg = new MessageModel(10005, "保存失败");
}
return(msg);
}
