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