/// <summary>
/// 导BOM
/// </summary>
public void ImportCuprum(List <EACT_CUPRUM> CupRumList, string creator, string mouldInteriorID, bool isImportEman, string emanWebPath, List <EACT_CUPRUM_EXP> cuprumEXPs = null)
{
using (var conn = DAL.GetConn())
{
conn.Open();
var _tran = conn.BeginTransaction();
try
{
if (CupRumList.Count > 0)
{
if (isImportEman && string.IsNullOrEmpty(emanWebPath))
{ //导入Eman
Eman.ImportEman(conn, _tran, CupRumList, mouldInteriorID, creator);
}
string ids = string.Join(",", Enumerable.Select(CupRumList, u => string.Format("'{0}'", u.CUPRUMSN + u.STEELMODELSN)).ToArray());
//TODO 先判断待导入的电极在库中是否已经存在，如果存在则先删除再插入
//清除符合条件的预装表数据
var delete_cuprum_assembly_sql = string.Format("delete from EACT_cuprum_assembly where CUPRUMID in(select cuprumid from EACT_cuprum where CUPRUMSN+STEELMODELSN in({0}))", ids.TrimEnd(','));
//清除符合条件的电极表数据
var delete_cuprum_sql = string.Format("delete from EACT_cuprum where CUPRUMSN+STEELMODELSN in({0})", ids.TrimEnd(','));
//插入钢件并返回插入的钢件ID
var select_mould_sql = string.Format("select mouldid from EACT_mould where sn='{0}'", CupRumList[0].STEELMODELSN);
var insert_mould_sql = string.Format("insert into EACT_mould(SN,DESIGNER,DESIGNERTIME) output inserted.mouldid values('{0}','{1}',getdate())", CupRumList[0].STEELMODELSN, creator);
//conn.Execute(delete_cuprum_assembly_sql, null, _tran);
//conn.Execute(delete_cuprum_sql, null, _tran);
var sel_cuprumList_sql = string.Format("select CUPRUMID,CUPRUMSN,STEELMODELSN from EACT_cuprum where CUPRUMSN+STEELMODELSN in({0})", ids.TrimEnd(','));
var updateCuprumList = conn.Query <EACT_CUPRUM>(sel_cuprumList_sql, null, _tran);
object mouldId = conn.ExecuteScalar(select_mould_sql, null, _tran, null, null);
if (mouldId == null)
{
mouldId = conn.ExecuteScalar(insert_mould_sql, null, _tran, null, null).ToString();
}
if (mouldId != null)
{
foreach (var item in CupRumList)
{
string cuprumId = string.Empty;
item.LOGINID = creator;
var updateCuprum = updateCuprumList.FirstOrDefault(u => u.CUPRUMSN == item.CUPRUMSN && u.STEELMODELSN == item.STEELMODELSN);
if (updateCuprum != null)
{
cuprumId = updateCuprum.CUPRUMID.ToString();
var update_cuprum_sql = string.Empty;
update_cuprum_sql = "update EACT_cuprum set ";
update_cuprum_sql += "CUPRUMNAME=@CUPRUMNAME,CUPRUMSN=@CUPRUMSN,FRIENUM=@FRIENUM,VDI=@VDI,STRUFF=@STRUFF,STRUFFTYPE=@STRUFFTYPE,STYLIST=@STYLIST,DATEOFDELIVERY=@DATEOFDELIVERY,";
update_cuprum_sql += "OPENSTRUFF=@OPENSTRUFF,DISCHARGING=@DISCHARGING,SHAPE=@SHAPE,ROCK=@ROCK,PROCDIRECTION=@PROCDIRECTION,RMF=@RMF,OFFSETX=@OFFSETX,OFFSETY=@OFFSETY,X=@X,";
update_cuprum_sql += "Y=@Y,Z=@Z,C=@C,STEEL=@STEEL,SUBSTRATECQUADRANT=@SUBSTRATECQUADRANT,STEELMODELSN=@STEELMODELSN,STEELMODULESN=@STEELMODULESN,ASSEMBLYEXP=@ASSEMBLYEXP,PARTFILENAME=@PARTFILENAME,HEADPULLUPH=@HEADPULLUPH,STRETCHH=@STRETCHH,STRUFFGROUPL=@STRUFFGROUPL,UNIT=@UNIT,PROCESSNUM=@PROCESSNUM,REGION=@REGION,CAPSET=@CAPSET,STEELZ=@STEELZ,LOGINID=@LOGINID,CUPRUMCOUNT=@CUPRUMCOUNT,STRUFFCODE=@STRUFFCODE,ROCKSHAPE=@ROCKSHAPE,SPEC=@SPEC";
update_cuprum_sql += " where CUPRUMSN+STEELMODELSN = '{0}'";
update_cuprum_sql = string.Format(update_cuprum_sql, item.CUPRUMSN + item.STEELMODELSN);
item.STYLIST = creator;
conn.Execute(update_cuprum_sql, item, _tran, null, null);
}
else
{
//插入电极表并返回插入的电极ID
var insert_cuprum_sql = string.Empty;
insert_cuprum_sql = "insert into EACT_cuprum(mouldid,cuprumname,cuprumsn,frienum,vdi,struff,strufftype,STYLIST,";
insert_cuprum_sql += "Dateofdelivery,openstruff,discharging,Shape,rock,Procdirection,Rmf,offsetx,offsety,";
insert_cuprum_sql += "x,y,z,c,steel,Substratecquadrant,STEELMODELSN,STEELMODULESN,Assemblyexp,PARTFILENAME,HEADPULLUPH,STRETCHH,STRUFFGROUPL,UNIT";
insert_cuprum_sql += ",PROCESSNUM,REGION,CAPSET,STEELZ,LOGINID,CUPRUMCOUNT,STRUFFCODE,ROCKSHAPE,SPEC";
insert_cuprum_sql += ") output inserted.cuprumid values({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',";
insert_cuprum_sql += "'{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16},";
insert_cuprum_sql += "'{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}',{34},'{35}',{36},'{37}','{38}',@SPEC)";
insert_cuprum_sql = string.Format(insert_cuprum_sql,
mouldId, item.CUPRUMNAME, item.CUPRUMSN, item.FRIENUM, item.VDI, item.STRUFF, item.STRUFFTYPE
, creator, item.DATEOFDELIVERY, item.OPENSTRUFF, item.DISCHARGING, item.SHAPE, item.ROCK
, item.PROCDIRECTION, item.RMF, DecimalConvert(item.OFFSETX)
, DecimalConvert(item.OFFSETY), item.X, item.Y, item.Z, item.C, item.STEEL, item.SUBSTRATECQUADRANT
, item.STEELMODELSN, item.STEELMODULESN, item.ASSEMBLYEXP, item.PARTFILENAME, item.HEADPULLUPH, item.STRETCHH
, item.STRUFFGROUPL, item.UNIT, item.PROCESSNUM, item.REGION, item.CAPSET, item.STEELZ, item.LOGINID
, item.CUPRUMCOUNT, item.STRUFFCODE, item.ROCKSHAPE
);
cuprumId = conn.ExecuteScalar(insert_cuprum_sql, item, _tran, null, null).ToString();
}
//共用电极
if (cuprumEXPs != null)
{
var shareElec = new DataAccess.Model.EACT_CUPRUM_EXP();
shareElec.CUPRUMID = decimal.Parse(cuprumId);
shareElec.PARTNO = item.STEELMODULESN;
shareElec.MODELNO = item.STEELMODELSN;
shareElec.X = item.X;
shareElec.Y = item.Y;
shareElec.Z = item.Z;
shareElec.C = item.C;
shareElec.CUPRUMNAME = item.CUPRUMNAME;
shareElec.CUPRUMSN = item.CUPRUMSN;
cuprumEXPs.Add(shareElec);
}
//插入物料表(如果匹配记录不存在则新增一条)
var select_cuprum_struff_sql = string.Format("select struffid from EACT_cuprum_struff where STRUFFNAME='{0}'", item.STRUFF);
object struffId = conn.ExecuteScalar(select_cuprum_struff_sql, null, _tran, null, null);
if (struffId == null)
{
var insert_cuprum_struff_sql = string.Format("insert into EACT_cuprum_struff output inserted.struffid select '{0}' where not exists(select * from EACT_cuprum_struff where STRUFFNAME='{0}')", item.STRUFF);
struffId = conn.ExecuteScalar(insert_cuprum_struff_sql, null, _tran, null, null).ToString();
}
//插入物料规格(如果匹配记录不存在则新增一条)
var select_cuprum_spec_sql = string.Format("select specid from EACT_cuprum_spec where specl={0} and specw={1} and spech={2}", item.EDMCONDITIONSN.ToLower().Split('x')[0], item.EDMCONDITIONSN.ToLower().Split('x')[1], item.EDMCONDITIONSN.ToLower().Split('x')[2]);
object specId = conn.ExecuteScalar(select_cuprum_spec_sql, null, _tran, null, null);
if (specId == null)
{
var insert_cuprum_spec_sql = "insert into EACT_cuprum_spec(specnname,specexp,specl,specw,spech) output inserted.specid ";
insert_cuprum_spec_sql += " select '{0}*{1}*{2}','{0}*{1}*{2}',{0},{1},{2} ";
insert_cuprum_spec_sql += " where not exists(select * from EACT_cuprum_spec where specl={0} and specw={1} and spech={2})";
insert_cuprum_spec_sql = string.Format(insert_cuprum_spec_sql, item.EDMCONDITIONSN.ToLower().Split('x')[0], item.EDMCONDITIONSN.ToLower().Split('x')[1], item.EDMCONDITIONSN.ToLower().Split('x')[2]);
specId = conn.ExecuteScalar(insert_cuprum_spec_sql, null, _tran, null, null).ToString();
}
//匹配夹具（根据长宽的取值区间，取第一个符合条件的；如果找不到匹配项，默认取第一条记录）
string chuckId = null;
if (!string.IsNullOrEmpty(item.CHUCK))
{
var newSelect_chuck_type_sql = string.Format("select top 1 chucktypeid from EACT_chuck_type where TypeName='{0}'", item.CHUCK);
var sValue = conn.ExecuteScalar(newSelect_chuck_type_sql, null, _tran, null, null);
chuckId = sValue == null ? null : sValue.ToString();
}
if (string.IsNullOrEmpty(chuckId))
{
var select_chuck_type_sql = "select top 1 chucktypeid from";
select_chuck_type_sql += "(select chucktypeid from EACT_chuck_type where (specmaxw>={0} and specminw<={0}) and (specmaxl>={1} and specminl<={1})";
select_chuck_type_sql += " union all ";
select_chuck_type_sql += "select min(chucktypeid) from EACT_chuck_type) t";
select_chuck_type_sql = string.Format(select_chuck_type_sql, item.EDMCONDITIONSN.ToLower().Split('x')[0], item.EDMCONDITIONSN.ToLower().Split('x')[1]);
chuckId = conn.ExecuteScalar(select_chuck_type_sql, null, _tran, null, null).ToString();
}
if (updateCuprum == null)
{
//插入电极预装表
var insert_cuprum_assembly_sql = "insert into EACT_cuprum_assembly(cuprumid,specid,struffid,chucktypeid,RECORDTIME)";
insert_cuprum_assembly_sql += " values(" + cuprumId + "," + specId + "," + struffId + "," + chuckId + ",getdate())";
conn.Execute(insert_cuprum_assembly_sql, null, _tran);
}
else
{
var update_cuprum_assembly_sql = "update EACT_cuprum_assembly set specid=@specId,struffid=@struffId,chucktypeid=@chuckId,RECORDTIME=getdate() where cuprumId=@cuprumId";
conn.Execute(update_cuprum_assembly_sql, new { cuprumId = updateCuprum.CUPRUMID, specId = specId, struffId = struffId, chuckId = chuckId, RECORDTIME = DateTime.Now }, _tran);
}
}
}
}
//删除
if (cuprumEXPs != null)
{
if (cuprumEXPs.Count > 0)
{
conn.Execute(DeleteCuprumEx(cuprumEXPs), null, _tran, null, null);
}
cuprumEXPs.ForEach(u =>
{
conn.Execute(InsertCuprumEx(u), u, _tran, null, null);
});
}
_tran.Commit();
}
catch (Exception ex)
{
_tran.Rollback();
throw ex;
}
if (isImportEman && !string.IsNullOrEmpty(emanWebPath))
{ //导入Eman
Eman.ImportEman(mouldInteriorID, emanWebPath);
}
}
}
public void ImportCuprum(List <EACT_CUPRUM> CupRumList, string creator, string mouldInteriorID, bool isImportEman, string emanWebPath, List <EACT_CUPRUM_EXP> cuprumEXPs = null)
{
using (var conn = DAL.GetConn())
{
conn.Open();
var _tran = conn.BeginTransaction();
try
{
//导入Eman
if (isImportEman)
{
Eman.ImportEman(conn, _tran, CupRumList, mouldInteriorID, creator);
}
if (CupRumList.Count > 0)
{
string ids = string.Join(",", Enumerable.Select(CupRumList, u => string.Format("'{0}'", u.CUPRUMSN)).ToArray());
//TODO 先判断待导入的电极在库中是否已经存在，如果存在则先删除再插入
//插入模号并返回插入的模号ID
var eact_mould = new DataAccess.ModelV2.EACT_MOULD();
eact_mould.M_SN = CupRumList[0].STEELMODELSN;
eact_mould.M_UPDATEUSE = creator;
eact_mould.M_UPDATETIME = DateTime.Now;
var select_mould_sql = string.Format("select M_ID from EACT_MOULD where M_SN=@M_SN");
var insert_mould_sql = CreateEACT_MOULDInsertSql();
object mouldId = conn.ExecuteScalar(select_mould_sql, eact_mould, _tran, null, null);
if (mouldId == null)
{
mouldId = conn.ExecuteScalar(insert_mould_sql, eact_mould, _tran, null, null).ToString();
}
//插入钢件并返回插入的钢件ID
object steelID = null;
if (mouldId != null)
{
var eact_steel = new DataAccess.ModelV2.EACT_STEEL();
eact_steel.M_ID = double.Parse(mouldId.ToString());
eact_steel.ST_SN = CupRumList[0].STEELMODULESN;
eact_steel.ST_UPDATETIME = DateTime.Now;
eact_steel.ST_UPDATEUSE = creator;
var select_st_c_sql = string.Format("select * from EACT_STEEL where M_ID=@M_ID and ST_SN=@ST_SN");
steelID = conn.ExecuteScalar(select_st_c_sql, eact_steel, _tran, null, null);
if (steelID == null)
{
steelID = conn.ExecuteScalar(CreateEACT_STEELInsertSql(), eact_steel, _tran, null, null).ToString();
}
}
if (steelID != null)
{
foreach (var item in CupRumList.GroupBy(u => u.PARTFILENAME).ToDictionary(group => group.Key, group => group.ToList()))
{
var cuprum = item.Value.FirstOrDefault();
if (cuprum != null)
{
//TODO 插入电极表
var eact_cuprum = new DataAccess.ModelV2.EACT_CUPRUM();
eact_cuprum.C_INTIME = DateTime.Now;
eact_cuprum.C_PRTNAME = item.Key;
eact_cuprum.C_SN = item.Key;
string cuprumId = conn.ExecuteScalar(CreateEACT_CUPRUMInsertSql(), eact_cuprum, _tran, null, null).ToString();
//TODO 插入钢件关联电极
var eact_steel_cuprum = new DataAccess.ModelV2.EACT_STEEL_CUPRUM();
eact_steel_cuprum.C_ID = double.Parse(cuprumId);
eact_steel_cuprum.ST_ID = double.Parse(steelID.ToString());
string stcID = conn.ExecuteScalar(CreateEACT_STEEL_CUPRUMInsertSql(), eact_steel_cuprum, _tran, null, null).ToString();
//TODO 插入跑位信息
var eact_c_pos = new DataAccess.ModelV2.EACT_CUPRUM_POS();
eact_c_pos.ST_ID = double.Parse(steelID.ToString());
eact_c_pos.STC_ID = double.Parse(stcID);
eact_c_pos.CP_ID = double.Parse(cuprumId);
conn.Execute(CreateEACT_CUPRUM_POSInsertSql(), eact_c_pos, _tran);
//TODO 插入投产信息
foreach (var c in item.Value)
{
var eact_c_make = new DataAccess.ModelV2.EACT_CUPRUM_MAKE();
conn.Execute(CreateEACT_CUPRUM_MAKEInsertSql(), eact_c_make, _tran);
}
}
}
}
}
_tran.Commit();
}
catch (Exception ex)
{
_tran.Rollback();
throw ex;
}
}
}
