| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429 |
- /*******************************************************************************
- * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
- * 类的信息：
- * 1.程序名称：PCModuleLogicDAL.cs
- * 2.功能描述：生产配置数据库访问类（插入、修改、删除）
- * 编辑履历：
- * 作者 日期 版本 修改内容
- * 陈冰 2014/09/3 1.00 新建
- *******************************************************************************/
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using Dongke.IBOSS.PRD.Basics.BaseResources;
- using Dongke.IBOSS.PRD.Basics.DataAccess;
- using Dongke.IBOSS.PRD.Basics.Library;
- using Dongke.IBOSS.PRD.Service.DataModels;
- using Dongke.IBOSS.PRD.Service.SAPHegiiDataService;
- using Dongke.IBOSS.PRD.WCF.DataModels;
- using Dongke.IBOSS.PRD.WCF.DataModels.PCModule;
- using Newtonsoft.Json;
- using Newtonsoft.Json.Linq;
- using Oracle.ManagedDataAccess.Client;
- namespace Dongke.IBOSS.PRD.Service.PCModuleLogic
- {
- /// <summary>
- /// 生产配置数据库访问类（插入、修改、删除）
- /// </summary>
- public partial class PCModuleLogicDAL
- {
- #region 生产线管理
- /// <summary>
- /// 保存生产线
- /// </summary>
- /// <param name="productionLine">生产线实体</param>
- /// <param name="sUserInfo">用户基本信息</param>
- /// <returns></returns>
- public static int SaveProductionLine(ProductionLineEntity productionLine, SUserInfo sUserInfo)
- {
- int returnRows = 0;
- IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- try
- {
- #region 新建生产线
- if (productionLine.ProductionLineID == 0)
- {
- #region 判断编码是否重复
- string sqlText = " SELECT ProductionLineID "
- + " FROM TP_PC_ProductionLine "
- + " WHERE "
- + " AccountID=:accountID "
- + " AND ProductionLineCode=:productionLineCode";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- };
- string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras);
- if (!string.IsNullOrEmpty(returnCode))
- {
- return Constant.RETURN_IS_EXIST;
- }
- #endregion
- #region 查询新ID
- sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.nextval FROM dual";
- productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- #endregion
- #region 插入生产线
- sqlText = "INSERT INTO TP_PC_ProductionLine (ProductionLineID,"
- + "ProductionLineCode,"
- + "ProductionLineName,"
- + "FlowXML,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID)"
- + " VALUES(:ProductionLineID,"
- + ":productionLineCode,"
- + ":productionLineName,"
- + ":flowXML,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":ProductionLineID",productionLine.ProductionLineID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- new OracleParameter(":productionLineName",productionLine.ProductionLineName),
- new OracleParameter(":flowXML", System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML)),
- new OracleParameter(":remarks",productionLine.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- };
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- //#region 查询新ID
- //sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.currval FROM dual";
- //productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- //#endregion
- #region 插入工序
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual";
- procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- sqlText = "INSERT INTO TP_PC_Procedure ("
- + "ProcedureID,"
- + "NodeNo,"
- + "ProductionLineID,"
- + "ProcedureCode,"
- + "ProcedureName,"
- + "P_Name,"
- + "ProcedureModel,"
- + "ModelType,"
- + "NodeType,"
- + "MustFlag,"
- + "CollectType,"
- + "PieceType,"
- + "IsSpecialRework,"
- + "IsSemireWork,"
- + "OrganizationID,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID)"
- + " VALUES("
- + ":procedureID,"
- + ":nodeNo,"
- + ":productionLineID,"
- + ":procedureCode,"
- + ":procedureName,"
- + ":pName,"
- + ":procedureModel,"
- + ":ModelType,"
- + ":NodeType,"
- + ":mustFlag,"
- + ":collectType,"
- + ":pieceType,"
- + ":isSpecialRework,"
- + ":IsSemireWork,"
- + ":organizationID,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureCode",procedure.ProcedureCode),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":procedureModel",procedure.ProcedureModel),
- new OracleParameter(":ModelType",procedure.ModelType),
- new OracleParameter(":NodeType",procedure.NodeType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":IsSemireWork",procedure.IsSemireWork),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- };
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- }
- #endregion
- #region 编辑生产线
- else
- {
- #region 校验时间戳
- // todo
- string sql = "SELECT OPTimeStamp FROM TP_PC_ProductionLine"
- + " WHERE ProductionLineID = " + productionLine.ProductionLineID + " and OPTimeStamp = :OPTimeStamp";
- OracleParameter[] parmetersSql = new OracleParameter[]
- {
- new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, productionLine.OPTimeStamp, ParameterDirection.Input),
- };
- DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql);
- if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0)
- {
- oracleTrConn.Commit();
- oracleTrConn.Disconnect();
- return Constant.RETURN_IS_DATACHANGED;
- }
- #endregion
- #region 判断编码是否重复
- string sqlText = " SELECT ProductionLineID "
- + " FROM TP_PC_ProductionLine "
- + " WHERE "
- + " AccountID=:accountID "
- + " AND ProductionLineCode=:productionLineCode"
- + " AND ProductionLineID <> :productionLineID";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- };
- string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras);
- if (!string.IsNullOrEmpty(returnCode))
- {
- return Constant.RETURN_IS_EXIST;
- }
- #endregion
- #region 编辑生产线
- sqlText = "UPDATE TP_PC_ProductionLine SET "
- + "ProductionLineName=:productionLineName,"
- + "FlowXML=:flowXML,"
- + "Remarks=:remarks,"
- + "UpdateTime=sysdate, "
- + "UpdateUserID=:updateUserID "
- + " WHERE ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineName",productionLine.ProductionLineName),
- new OracleParameter(":flowXML", OracleDbType.Blob,System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML),ParameterDirection.Input),
- new OracleParameter(":remarks",productionLine.Remarks),
- new OracleParameter(":updateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
- new OracleParameter(":productionLineID", OracleDbType.Int32, productionLine.ProductionLineID,ParameterDirection.Input)
- };
- //foreach (OracleParameter item in paras)
- //{
- // if (item.Value + "" == "")
- // {
- // item.Value = System.DBNull.Value;
- // }
- //}
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 编辑工序
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- if (procedure.EditingAddFlag)
- {
- #region 编辑时新添加的节点
- sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual";
- procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- sqlText = "INSERT INTO TP_PC_Procedure ("
- + "ProcedureID,"
- + "NodeNo,"
- + "ProductionLineID,"
- + "ProcedureCode,"
- + "ProcedureName,"
- + "P_Name,"
- + "ProcedureModel,"
- + "ModelType,"
- + "NodeType,"
- + "MustFlag,"
- + "CollectType,"
- + "PieceType,"
- + "IsSpecialRework,"
- + "IsSemireWork,"
- + "OrganizationID,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID)"
- + " VALUES("
- + ":procedureID,"
- + ":nodeNo,"
- + ":productionLineID,"
- + ":procedureCode,"
- + ":procedureName,"
- + ":pName,"
- + ":procedureModel,"
- + ":ModelType,"
- + ":NodeType,"
- + ":mustFlag,"
- + ":collectType,"
- + ":pieceType,"
- + ":isSpecialRework,"
- + ":isSemireWork,"
- + ":organizationID,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureCode",procedure.ProcedureCode),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":procedureModel",procedure.ProcedureModel),
- new OracleParameter(":ModelType",procedure.ModelType),
- new OracleParameter(":NodeType",procedure.NodeType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- };
- #endregion
- }
- else
- {
- #region 编辑
- sqlText = "UPDATE TP_PC_Procedure SET "
- + " ProcedureName=:procedureName,"
- + " P_Name=:pName,"
- + " ProcedureCode=:ProcedureCode,"
- + " ModelType=:modelType,"
- + " MustFlag=:mustFlag,"
- + " CollectType=:collectType,"
- + " OrganizationID=:organizationID,"
- + " NodeType=:nodeType,"
- + " PieceType=:pieceType,"
- + " IsSpecialRework=:isSpecialRework,"
- + " IsSemireWork=:isSemireWork,"
- + " NodeNo=:nodeNo,"
- + " Remarks=:remarks,"
- + " updateUserID=:updateUserID,"
- + " UpdateTime=sysdate"
- + " WHERE NodeNo=:nodeNo "
- + " AND ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":ProcedureCode",procedure.ProcedureCode),
- new OracleParameter(":modelType",procedure.ModelType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":nodeType",procedure.NodeType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- };
- #endregion
- }
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 先删除生产工序流程明细 然后再插入
- sqlText = "DELETE TP_PC_ProcedureFlow WHERE ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应产品 然后再插入
- sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应工号 然后再插入
- sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应缺陷 然后再插入
- sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除缺陷对应工序工种表 然后再插入
- sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- }
- #endregion
- #region 插入生产工序属性等信息
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- string sqlText;
- OracleParameter[] paras;
- #region 插入生产工序流程明细
- if (procedure.ProcedureFlowDetailList != null)
- {
- foreach (ProcedureFlowEntity flowDetail in procedure.ProcedureFlowDetailList)
- {
- ProcedureEntity procedureWhere = productionLine.ProcedureList.Where(p => p.NodeNo == flowDetail.ArriveNodeNo).SingleOrDefault();
- // 没有找到对应节点的ID
- if (procedureWhere == null)
- {
- return Constant.INT_IS_ZERO;
- }
- else
- {
- flowDetail.ArriveProcedureID = procedureWhere.ProcedureID;
- }
- sqlText = "INSERT INTO TP_PC_ProcedureFlow ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "FlowFlag,"
- + "ArriveProcedureID,"
- + "ArriveNodeNo"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":flowFlag,"
- + ":arriveProcedureID,"
- + ":arriveNodeNo"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":flowFlag",flowDetail.FlowFlag),
- new OracleParameter(":arriveProcedureID",flowDetail.ArriveProcedureID),
- new OracleParameter(":arriveNodeNo",flowDetail.ArriveNodeNo),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- }
- #endregion
- #region 插入生产工序对应产品表
- foreach (DataRow row in procedure.ProcedureGoodsTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureGoods ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "GOODSID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":goodsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应工号表
- foreach (DataRow row in procedure.ProcedureUserTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureUser ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "UserID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":userID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应缺陷表
- foreach (DataRow row in procedure.ProcedureDefectTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_ProcedureDefect ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":defectID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入缺陷对应工序工种表
- foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_DefectProcedureJobs ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "JobsID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":DefectID,"
- + ":jobsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- }
- #endregion
- oracleTrConn.Commit();
- }
- catch (Exception ex)
- {
- oracleTrConn.Rollback();
- throw ex;
- }
- finally
- {
- // 释放资源
- if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
- {
- oracleTrConn.Disconnect();
- }
- }
- return returnRows;
- }
- /// <summary>
- /// 停用生产线
- /// </summary>
- /// <param name="lineID">生产线ID</param>
- /// <param name="sUserInfo">用户基本信息</param>
- /// <returns>int</returns>
- /// <remarks>
- /// 陈冰 2014.09.15 新建
- /// </remarks>
- public static int StopProductionLine(int lineID, int flag, SUserInfo sUserInfo)
- {
- int returnRows = 0;
- IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- try
- {
- // 更新总表
- // 更新明细表
- if (flag == 0)
- {
- string sqlText = "UPDATE tp_pc_productionline SET valueflag=0,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":productionLineID",lineID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- sqlText = "UPDATE TP_PC_Procedure SET valueflagback=valueflag, valueflag=0,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- else
- {
- string sqlText = "UPDATE tp_pc_productionline SET valueflag=1,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":productionLineID",lineID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- sqlText = "UPDATE TP_PC_Procedure SET valueflag=valueflagback, UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- oracleTrConn.Commit();
- }
- catch (Exception ex)
- {
- oracleTrConn.Rollback();
- throw ex;
- }
- finally
- {
- // 释放资源
- if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
- {
- oracleTrConn.Disconnect();
- }
- }
- return returnRows;
- }
- #endregion
- #region 班次配置
- /// <summary>
- /// 保存班次配置信息
- /// </summary>
- /// <param name="setEntity">班次配置实体</param>
- /// <param name="dtClassesSetting">班次配置明细table</param>
- /// <param name="sUserInfo">用户基本信息</param>
- /// <returns>>0 保存成功 else 失败</returns>
- /// <remarks>
- /// 作者 日期 内容
- /// 冯雪 2014-9-24 新建
- /// </remarks>
- public static int SaveClassesSetting(ClassesSettingEntity setEntity, DataTable dtClassesSetting, SUserInfo sUserInfo)
- {
- int returnRows = 0;
- IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- try
- {
- oracleTrConn.Connect();
- int classesSettingID = 0;
- if (setEntity.ClassesSettingID == 0)
- {
- // 查询新插入的生产数据ID
- string sqlText = "SELECT SEQ_PC_ClassesSetting_ID.NEXTVAL FROM dual";
- classesSettingID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- // 插入班次配置表
- string sqlString1 = " INSERT INTO TP_PC_ClassesSetting "
- + " (ClassesSettingID,AccountDate,UserID,UserCode, "
- + " Remarks,AccountID,CreateTime,CreateUserID,UpdateUserID) "
- + " VALUES (:ClassesSettingID,:AccountDate,:userID,:userCode, "
- + " :remarks,:accountID,sysdate,:createUserID,:updateUserID)";
- OracleParameter[] parmeters1 = new OracleParameter[]
- {
- new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
- new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
- new OracleParameter(":userID",OracleDbType.Int32,setEntity.UserID,ParameterDirection.Input),
- new OracleParameter(":userCode",OracleDbType.Varchar2,setEntity.UserCode,ParameterDirection.Input),
- new OracleParameter(":AccountDate",OracleDbType.Date,setEntity.AccountDate,ParameterDirection.Input),
- new OracleParameter(":remarks",OracleDbType.Varchar2,setEntity.Remarks,ParameterDirection.Input),
- new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
- new OracleParameter(":createUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
- new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
- };
- returnRows = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1);
- }
- else
- {
- classesSettingID = setEntity.ClassesSettingID;
- // 删除以前的班次
- string sqlDelete = "DELETE FROM TP_PC_ClassesDetail WHERE ClassesSettingID=:ClassesSettingID";
- OracleParameter[] parmeters1 = new OracleParameter[]
- {
- new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
- };
- returnRows = oracleTrConn.ExecuteNonQuery(sqlDelete, parmeters1);
- }
- #region 保存班次配置明细信息
- foreach (DataRow dataRow in dtClassesSetting.Rows)
- {
- if (dataRow["UJobsId"].ToString() == "" && dataRow["StaffCode"].ToString() == "")
- {
- continue;
- }
- if (dataRow.RowState == DataRowState.Deleted)
- {
- continue;
- }
- string sqlInsertString = "INSERT INTO TP_PC_ClassesDetail "
- + " (ClassesSettingID,AccountDate,userid,usercode,ujobsid,staffid,sjobsid,staffstatus,"
- + " remarks,accountid,createtime,createuserid,UpdateUserID,UJobsNum) "
- + "VALUES (:settingID,:AccountDate,:userid,:usercode,:ujobsid,:staffid,:sjobsid,:staffstatus,"
- + " :remarks,:accountid,sysdate,:createuserid,:updateUserID,:uJobsNum)";
- int UJobsNum = 1;//默认一个
- if (dtClassesSetting.Select("ujobsid=" + dataRow["UJobsId"]).Length > 0)
- {
- UJobsNum = dtClassesSetting.Select("ujobsid=" + dataRow["UJobsId"]).Length;
- }
- OracleParameter[] oracleParameter = new OracleParameter[]
- {
- new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
- new OracleParameter(":userid",OracleDbType.Int32,dataRow["userid"].ToString(),ParameterDirection.Input),
- new OracleParameter(":usercode",OracleDbType.Varchar2,dataRow["usercode"].ToString(),ParameterDirection.Input),
- new OracleParameter(":ujobsid",OracleDbType.Int32,dataRow["UJobsId"].ToString(),ParameterDirection.Input),
- new OracleParameter(":staffid",OracleDbType.Int32,dataRow["staffid"].ToString(),ParameterDirection.Input),
- new OracleParameter(":sjobsid",OracleDbType.Int32,dataRow["Jobs"].ToString(),ParameterDirection.Input),
- new OracleParameter(":staffstatus",OracleDbType.Int32,dataRow["staffstatus"].ToString(),ParameterDirection.Input),
- new OracleParameter(":remarks",OracleDbType.Varchar2,dataRow["remarks"].ToString(),ParameterDirection.Input),
- new OracleParameter(":AccountDate",OracleDbType.Date,setEntity.AccountDate,ParameterDirection.Input),
- new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
- new OracleParameter(":createuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
- new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
- new OracleParameter(":uJobsNum",OracleDbType.Decimal,UJobsNum,ParameterDirection.Input)
- };
- returnRows = oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter);
- }
- #endregion
- oracleTrConn.Commit();
- oracleTrConn.Disconnect();
- return returnRows;
- }
- catch (Exception ex)
- {
- oracleTrConn.Rollback();
- oracleTrConn.Disconnect();
- throw ex;
- }
- finally
- {
- // 释放资源
- if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
- {
- oracleTrConn.Disconnect();
- }
- }
- }
- #endregion
- /// <summary>
- /// 保存生产线 xuwei modify 2019-11-22
- /// </summary>
- /// <param name="productionLine">生产线实体</param>
- /// <param name="sUserInfo">用户基本信息</param>
- /// <returns></returns>
- public static int SaveProductionLine2(ProductionLineEntity productionLine, SUserInfo sUserInfo)
- {
- int returnRows = 0;
- IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- try
- {
- #region 验证工序个数
- if (DataManager.LicDataSet != null)
- {
- int licCount = Convert.ToInt32(DataManager.LicDataSet.Tables["Info"].Rows[0]["PFNum"]);
- if (licCount > -1)
- {
- int pNum = Convert.ToInt32(oracleTrConn.GetSqlResultToObj("select count(*) from tp_pc_procedure p where p.valueflag = '1' and p.productionlineid <> " + productionLine.ProductionLineID));
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- if(procedure.ProcedureState != 2)
- {
- pNum++;
- }
- }
- if (pNum > licCount)
- {
- oracleTrConn.Rollback();
- return -10;
- }
- }
- }
- #endregion
- #region 新建生产线
- if (productionLine.ProductionLineID == 0)
- {
- #region 判断编码是否重复
- string sqlText = " SELECT ProductionLineID "
- + " FROM TP_PC_ProductionLine "
- + " WHERE "
- + " AccountID=:accountID "
- + " AND ProductionLineCode=:productionLineCode";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- };
- string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras);
- if (!string.IsNullOrEmpty(returnCode))
- {
- return Constant.RETURN_IS_EXIST;
- }
- #endregion
- #region 查询新ID
- sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.nextval FROM dual";
- productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- #endregion
- #region 插入生产线
- sqlText = "INSERT INTO TP_PC_ProductionLine (ProductionLineID,"
- + "ProductionLineCode,"
- + "ProductionLineName,"
- + "FlowXML,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID)"
- + " VALUES(:ProductionLineID,"
- + ":productionLineCode,"
- + ":productionLineName,"
- + ":flowXML,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":ProductionLineID",productionLine.ProductionLineID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- new OracleParameter(":productionLineName",productionLine.ProductionLineName),
- new OracleParameter(":flowXML", System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML)),
- new OracleParameter(":remarks",productionLine.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- };
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- //#region 查询新ID
- //sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.currval FROM dual";
- //productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- //#endregion
- #region 插入工序
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual";
- procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- sqlText = "INSERT INTO TP_PC_Procedure ("
- + "ProcedureID,"
- + "NodeNo,"
- + "ProductionLineID,"
- + "ProcedureCode,"
- + "ProcedureName,"
- + "P_Name,"
- + "ProcedureModel,"
- + "ModelType,"
- + "NodeType,"
- + "MustFlag,"
- + "CollectType,"
- + "PieceType,"
- + "IsSpecialRework,"
- + "IsSemireWork,"
- + "OrganizationID,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID,misspriority,displayno,UNDOFLAG,DeliverType,BarCodePrintCopies,BarCodeFlag)"
- + " VALUES("
- + ":procedureID,"
- + ":nodeNo,"
- + ":productionLineID,"
- + ":procedureCode,"
- + ":procedureName,"
- + ":pName,"
- + ":procedureModel,"
- + ":ModelType,"
- + ":NodeType,"
- + ":mustFlag,"
- + ":collectType,"
- + ":pieceType,"
- + ":isSpecialRework,"
- + ":isSemireWork,"
- + ":organizationID,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID,:misspriority,:displayno,:UNDOFLAG,:DeliverType,:BarCodePrintCopies,:BarCodeFlag"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureCode",procedure.ProcedureCode),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":procedureModel",procedure.ProcedureModel),
- new OracleParameter(":ModelType",procedure.ModelType),
- new OracleParameter(":NodeType",procedure.NodeType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":misspriority",procedure.MissPriority),
- new OracleParameter(":displayno",procedure.DisplayNo),
- new OracleParameter(":UNDOFLAG",procedure.UnDo),
- new OracleParameter(":DeliverType",procedure.DeliverType),
- new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies),
- new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag),
- };
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- }
- #endregion
- #region 编辑生产线
- else
- {
- #region 校验时间戳
- // todo
- string sql = "SELECT OPTimeStamp FROM TP_PC_ProductionLine"
- + " WHERE ProductionLineID = " + productionLine.ProductionLineID + " and OPTimeStamp = :OPTimeStamp";
- OracleParameter[] parmetersSql = new OracleParameter[]
- {
- new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, productionLine.OPTimeStamp, ParameterDirection.Input),
- };
- DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql);
- if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0)
- {
- oracleTrConn.Commit();
- oracleTrConn.Disconnect();
- return Constant.RETURN_IS_DATACHANGED;
- }
- #endregion
- #region 判断编码是否重复
- string sqlText = " SELECT ProductionLineID "
- + " FROM TP_PC_ProductionLine "
- + " WHERE "
- + " AccountID=:accountID "
- + " AND ProductionLineCode=:productionLineCode"
- + " AND ProductionLineID <> :productionLineID";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":productionLineCode",productionLine.ProductionLineCode),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- };
- string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras);
- if (!string.IsNullOrEmpty(returnCode))
- {
- return Constant.RETURN_IS_EXIST;
- }
- #endregion
- #region 编辑生产线
- sqlText = "UPDATE TP_PC_ProductionLine SET "
- + "ProductionLineName=:productionLineName,"
- + "FlowXML=:flowXML,"
- + "Remarks=:remarks,"
- + "UpdateTime=sysdate, "
- + "UpdateUserID=:updateUserID "
- + " WHERE ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineName",productionLine.ProductionLineName),
- new OracleParameter(":flowXML", OracleDbType.Blob,System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML),ParameterDirection.Input),
- new OracleParameter(":remarks",productionLine.Remarks),
- new OracleParameter(":updateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
- new OracleParameter(":productionLineID", OracleDbType.Int32, productionLine.ProductionLineID,ParameterDirection.Input)
- };
- //foreach (OracleParameter item in paras)
- //{
- // if (item.Value + "" == "")
- // {
- // item.Value = System.DBNull.Value;
- // }
- //}
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 编辑工序
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- //if (procedure.EditingAddFlag)
- if (procedure.ProcedureState == 1)
- {
- #region 编辑时新添加的节点
- sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual";
- procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText));
- sqlText = "INSERT INTO TP_PC_Procedure ("
- + "ProcedureID,"
- + "NodeNo,"
- + "ProductionLineID,"
- + "ProcedureCode,"
- + "ProcedureName,"
- + "P_Name,"
- + "ProcedureModel,"
- + "ModelType,"
- + "NodeType,"
- + "MustFlag,"
- + "CollectType,"
- + "PieceType,"
- + "IsSpecialRework,"
- + "IsSemireWork,"
- + "OrganizationID,"
- + "Remarks,"
- + "AccountID,"
- + "CreateUserID,"
- + "UpdateUserID,misspriority,displayno,UNDOFLAG,DeliverType,BarCodePrintCopies,BarCodeFlag)"
- + " VALUES("
- + ":procedureID,"
- + ":nodeNo,"
- + ":productionLineID,"
- + ":procedureCode,"
- + ":procedureName,"
- + ":pName,"
- + ":procedureModel,"
- + ":ModelType,"
- + ":NodeType,"
- + ":mustFlag,"
- + ":collectType,"
- + ":pieceType,"
- + ":isSpecialRework,"
- + ":isSemireWork,"
- + ":organizationID,"
- + ":remarks,"
- + ":accountID,"
- + ":createUserID,"
- + ":updateUserID,:misspriority,:displayno,:UNDOFLAG,:DeliverType,:BarCodePrintCopies,:BarCodeFlag"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureCode",procedure.ProcedureCode),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":procedureModel",procedure.ProcedureModel),
- new OracleParameter(":ModelType",procedure.ModelType),
- new OracleParameter(":NodeType",procedure.NodeType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":accountID",sUserInfo.AccountID),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":misspriority",procedure.MissPriority),
- new OracleParameter(":displayno",procedure.DisplayNo),
- new OracleParameter(":UNDOFLAG",procedure.UnDo),
- new OracleParameter(":DeliverType",procedure.DeliverType),
- new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies),
- new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag),
- };
- #endregion
- }
- else if (procedure.ProcedureState == 2) //后添加的,删除后标用节点
- {
- #region 编辑
- sqlText = "update TP_PC_Procedure set valueflag=0 "
- + " WHERE ProcedureID=:procedureID "
- + " AND ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- };
- #endregion
- }
- else
- {
- #region 编辑
- sqlText = "UPDATE TP_PC_Procedure SET "
- + " ProcedureName=:procedureName,"
- + " P_Name=:pName,"
- + " ProcedureCode=:ProcedureCode,"
- + " ModelType=:modelType,"
- + " MustFlag=:mustFlag,"
- + " CollectType=:collectType,"
- + " OrganizationID=:organizationID,"
- + " NodeType=:nodeType,"
- + " PieceType=:pieceType,"
- + " IsSpecialRework=:isSpecialRework,"
- + " IsSemireWork=:isSemireWork,"
- + " PrintType=:printType," //xuwei add 2019-11-20
- + " IsGlazeChange=:IsGlazeChange," //xuwei add 2020-01-02
- + " NodeNo=:nodeNo,"
- + " Remarks=:remarks,"
- + " updateUserID=:updateUserID,"
- + " UpdateTime=sysdate,"
- + " misspriority=:misspriority,"
- + " displayno=:displayno,"
- + " UNDOFLAG=:UNDOFLAG,"
- + " DeliverType=:DeliverType,"
- + " BarCodePrintCopies=:BarCodePrintCopies,"
- + " BarCodeFlag=:BarCodeFlag"
- + " WHERE NodeNo=:nodeNo "
- + " AND ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":pName",procedure.PName),
- new OracleParameter(":ProcedureCode",procedure.ProcedureCode),
- new OracleParameter(":modelType",procedure.ModelType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":nodeType",procedure.NodeType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),
- new OracleParameter(":printType",procedure.PrintType),//xuwei add 2019-11-20
- new OracleParameter(":IsGlazeChange",procedure.IsGlazeChange),//xuwei add 2020-01-02
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":misspriority",procedure.MissPriority),
- new OracleParameter(":displayno",procedure.DisplayNo),
- new OracleParameter(":UNDOFLAG",procedure.UnDo),
- new OracleParameter(":DeliverType",procedure.DeliverType),
- new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies),
- new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag),
- };
- #endregion
- }
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 先删除生产工序流程明细 然后再插入
- sqlText = "DELETE TP_PC_ProcedureFlow WHERE ProductionLineID=:productionLineID";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应产品 然后再插入
- sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应工号 然后再插入
- sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应缺陷 然后再插入
- sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除缺陷对应工序工种表 然后再插入
- sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应窑炉 然后再插入
- sqlText = "DELETE TP_PC_ProcedureKiln WHERE ProductionLineID=:productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除半检产品等级 然后再插入 xuwei add 2019-12-12
- sqlText = "DELETE TP_PC_PROCEDUREGOODSLEVEL WHERE PRODUCTIONLINEID = :productionLineID";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- }
- #endregion
- #region 插入生产工序属性等信息
- foreach (ProcedureEntity procedure in productionLine.ProcedureList)
- {
- string sqlText;
- OracleParameter[] paras;
- #region 插入生产工序流程明细
- if (procedure.ProcedureFlowDetailList != null)
- {
- foreach (ProcedureFlowEntity flowDetail in procedure.ProcedureFlowDetailList)
- {
- ProcedureEntity procedureWhere = productionLine.ProcedureList.Where(p => p.NodeNo == flowDetail.ArriveNodeNo).SingleOrDefault();
- // 没有找到对应节点的ID
- if (procedureWhere == null)
- {
- return Constant.INT_IS_ZERO;
- }
- else
- {
- flowDetail.ArriveProcedureID = procedureWhere.ProcedureID;
- }
- sqlText = "INSERT INTO TP_PC_ProcedureFlow ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "FlowFlag,"
- + "ArriveProcedureID,"
- + "ArriveNodeNo"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":flowFlag,"
- + ":arriveProcedureID,"
- + ":arriveNodeNo"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":flowFlag",flowDetail.FlowFlag),
- new OracleParameter(":arriveProcedureID",flowDetail.ArriveProcedureID),
- new OracleParameter(":arriveNodeNo",flowDetail.ArriveNodeNo),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- }
- #endregion
- #region 插入生产工序对应产品表
- foreach (DataRow row in procedure.ProcedureGoodsTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureGoods ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "GOODSID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":goodsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应工号表
- foreach (DataRow row in procedure.ProcedureUserTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureUser ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "UserID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":userID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应缺陷表
- foreach (DataRow row in procedure.ProcedureDefectTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_ProcedureDefect ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":defectID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入缺陷对应工序工种表
- foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_DefectProcedureJobs ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "JobsID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":DefectID,"
- + ":jobsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应窑炉
- if (procedure.ProcedureKilnTable != null)
- {
- foreach (DataRow row in procedure.ProcedureKilnTable.Rows)
- {
- sqlText = "INSERT INTO tp_pc_procedurekiln ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "KilnID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":kilnID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",productionLine.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":kilnID",OracleDbType.Int32,row["kilnID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- }
- #endregion
- #region 插入生产工序对应半检产品等级 xuwei add 2019-12-12
- if (!string.IsNullOrEmpty(procedure.SemiGoodsLevel))
- {
- string[] ids = procedure.SemiGoodsLevel.Split(',');
- string sqlStr = @"
- BEGIN
- ";
- for (int i = 0; i < ids.Length; i++)
- {
- sqlStr += $@"
- INSERT INTO TP_PC_PROCEDUREGOODSLEVEL
- ( PRODUCTIONLINEID, PROCEDUREID, GOODSLEVELTYPEID)
- VALUES
- (:PRODUCTIONLINEID, :PROCEDUREID, {ids[i]});
- ";
- }
- sqlStr += "END;";
- oracleTrConn.ExecuteNonQuery(
- sqlStr,
- new OracleParameter[]
- {
- new OracleParameter(":PRODUCTIONLINEID",productionLine.ProductionLineID),
- new OracleParameter(":PROCEDUREID",procedure.ProcedureID)
- }
- );
- }
- #endregion
- }
- #endregion
- oracleTrConn.Commit();
- }
- catch (Exception ex)
- {
- oracleTrConn.Rollback();
- throw ex;
- }
- finally
- {
- // 释放资源
- if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
- {
- oracleTrConn.Disconnect();
- }
- }
- return returnRows;
- }
- /// <summary>
- /// 保存工序节点
- /// </summary>
- /// <param name="procedureEntity">工序实体</param>
- /// <param name="sUserInfo">用户基本信息</param>
- /// <returns></returns>
- public static int SaveProcedureInfo(ProcedureEntity procedure, SUserInfo sUserInfo)
- {
- int returnRows = 0;
- IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- try
- {
- #region 校验时间戳
- // todo
- string sql = "SELECT OPTimeStamp FROM TP_PC_Procedure"
- + " WHERE ProcedureID = " + procedure.ProcedureID + " and OPTimeStamp = :OPTimeStamp and productionlineid=:productionlineid and nodeno=:nodeno";
- OracleParameter[] parmetersSql = new OracleParameter[]
- {
- new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, procedure.OPTimeStamp, ParameterDirection.Input),
- new OracleParameter(":productionlineid", OracleDbType.Int32, procedure.ProductionLineID, ParameterDirection.Input),
- new OracleParameter(":nodeno", OracleDbType.Int32, procedure.NodeNo, ParameterDirection.Input),
- };
- DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql);
- if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0)
- {
- oracleTrConn.Commit();
- oracleTrConn.Disconnect();
- return Constant.RETURN_IS_DATACHANGED;
- }
- #endregion
- string sqlText = "";
- #region 更新工序信息
- sqlText = "UPDATE TP_PC_Procedure SET "
- + " ProcedureName=:procedureName,"
- + " P_Name=:PName,"
- + " ProcedureCode=:ProcedureCode,"
- + " CollectType=:collectType,"
- + " OrganizationID=:organizationID,"
- + " NodeType=:nodeType,"
- + " PieceType=:pieceType,"
- + " IsSpecialRework=:isSpecialRework,"
- + " IsSemireWork=:isSemireWork," //xuwei add 2019-10-14
- + " PrintType=:printType," //xuwei add 2019-11-20
- + " IsGlazeChange=:IsGlazeChange," //xuwei add 2019-11-20
- + " NodeNo=:nodeNo,"
- + " Remarks=:remarks,"
- + " updateUserID=:updateUserID,"
- + " UpdateTime=sysdate,"
- + " misspriority=:misspriority,"
- + " displayno=:displayno,"
- + " UNDOFLAG=:UNDOFLAG,"
- + " DeliverType=:DeliverType,"
- + " MustFlag=:mustFlag,"
- + " BarCodePrintCopies=:BarCodePrintCopies,"
- + " FINISHEDCHECKPROCEDUREID=:FINISHEDCHECKPROCEDUREID,"
- + " BarCodeFlag=:BarCodeFlag"
- + " WHERE NodeNo=:nodeNo "
- + " AND ProductionLineID=:productionLineID";
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureName",procedure.ProcedureName),
- new OracleParameter(":PName",procedure.PName),
- new OracleParameter(":ProcedureCode",procedure.ProcedureCode),
- new OracleParameter(":collectType",procedure.CollectType),
- new OracleParameter(":organizationID",procedure.OrganizationID),
- new OracleParameter(":nodeType",procedure.NodeType),
- new OracleParameter(":pieceType",procedure.PieceType),
- new OracleParameter(":isSpecialRework",procedure.IsSpecialRework),
- new OracleParameter(":isSemireWork",procedure.IsSemireWork),//xuwei add 2019-10-14
- new OracleParameter(":printType",procedure.PrintType),//xuwei add 2019-11-20
- new OracleParameter(":IsGlazeChange",procedure.IsGlazeChange),//xuwei add 2020-01-02
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":remarks",procedure.Remarks),
- new OracleParameter(":updateUserID",sUserInfo.UserID),
- new OracleParameter(":misspriority",procedure.MissPriority),
- new OracleParameter(":displayno",procedure.DisplayNo),
- new OracleParameter(":UNDOFLAG",procedure.UnDo),
- new OracleParameter(":DeliverType",procedure.DeliverType),
- new OracleParameter(":mustFlag",procedure.MustFlag),
- new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies),
- new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag),
- new OracleParameter(":FINISHEDCHECKPROCEDUREID",procedure.FinishedCheckProcedureID),//xiacm add 2022-10-12
- };
- foreach (OracleParameter item in paras)
- {
- if (item.Value + "" == "")
- {
- item.Value = System.DBNull.Value;
- }
- //成检工序id xiacm 2022-10-12
- if (item.ParameterName== ":FINISHEDCHECKPROCEDUREID"&& Convert.ToInt32( item.Value)==-1)
- {
- item.Value = System.DBNull.Value;
- }
- }
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":nodeno",procedure.NodeNo),
- new OracleParameter(":procedureid",procedure.ProcedureID),
- };
- #region 删除对应产品 然后再插入
- sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应工号 然后再插入
- sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应缺陷 然后再插入
- sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除缺陷对应工序工种表 然后再插入
- sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除对应窑炉 然后再插入
- sqlText = "DELETE TP_PC_ProcedureKiln WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- #endregion
- #region 删除半检产品等级 然后再插入 xuwei add 2019-12-12
- sqlText = "DELETE TP_PC_PROCEDUREGOODSLEVEL WHERE PRODUCTIONLINEID = :productionLineID and ProcedureID=:procedureid";
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureid",procedure.ProcedureID),
- });
- #endregion
- #region 插入生产工序对应产品表
- foreach (DataRow row in procedure.ProcedureGoodsTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureGoods ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "GOODSID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":goodsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应工号表
- foreach (DataRow row in procedure.ProcedureUserTable.Rows)
- {
- sqlText = "INSERT INTO TP_PC_ProcedureUser ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "UserID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":userID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应缺陷表
- foreach (DataRow row in procedure.ProcedureDefectTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_ProcedureDefect ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":defectID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入缺陷对应工序工种表
- foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows)
- {
- if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value)
- {
- continue;
- }
- sqlText = "INSERT INTO TP_PC_DefectProcedureJobs ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "DefectID,"
- + "JobsID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":DefectID,"
- + ":jobsID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input),
- new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应窑炉
- foreach (DataRow row in procedure.ProcedureKilnTable.Rows)
- {
- sqlText = "INSERT INTO tp_pc_procedurekiln ("
- + "ProductionLineID,"
- + "ProcedureID,"
- + "NodeNo,"
- + "KilnID,"
- + "CreateUserID"
- + ")"
- + " VALUES("
- + ":productionLineID,"
- + ":procedureID,"
- + ":nodeNo,"
- + ":kilnID,"
- + ":createUserID"
- + ")";
- paras = new OracleParameter[]
- {
- new OracleParameter(":productionLineID",procedure.ProductionLineID),
- new OracleParameter(":procedureID",procedure.ProcedureID),
- new OracleParameter(":nodeNo",procedure.NodeNo),
- new OracleParameter(":kilnID",OracleDbType.Int32,row["kilnID"],ParameterDirection.Input),
- new OracleParameter(":createUserID",sUserInfo.UserID),
- };
- returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras);
- }
- #endregion
- #region 插入生产工序对应半检产品等级 xuwei add 2019-12-12
- if (!string.IsNullOrEmpty(procedure.SemiGoodsLevel))
- {
- string[] ids = procedure.SemiGoodsLevel.Split(',');
- string sqlStr = @"
- BEGIN
- ";
- for (int i = 0; i < ids.Length; i++)
- {
- sqlStr += $@"
- INSERT INTO TP_PC_PROCEDUREGOODSLEVEL
- ( PRODUCTIONLINEID, PROCEDUREID, GOODSLEVELTYPEID)
- VALUES
- (:PRODUCTIONLINEID, :PROCEDUREID, {ids[i]});
- ";
- }
- sqlStr += "END;";
- oracleTrConn.ExecuteNonQuery(
- sqlStr,
- new OracleParameter[]
- {
- new OracleParameter(":PRODUCTIONLINEID",procedure.ProductionLineID),
- new OracleParameter(":PROCEDUREID",procedure.ProcedureID)
- }
- );
- }
- #endregion
- oracleTrConn.Commit();
- }
- catch (Exception ex)
- {
- oracleTrConn.Rollback();
- throw ex;
- }
- finally
- {
- // 释放资源
- if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
- {
- oracleTrConn.Disconnect();
- }
- }
- return returnRows;
- }
- /// <summary>
- /// 注浆变更
- /// </summary>
- /// <param name="user">登录用户信息</param>
- /// <param name="barcode">原条码</param>
- /// <param name="newBarcode">新条码</param>
- /// <param name="remarks">备注</param>
- /// <returns>操作结果</returns>
- public static ServiceResultEntity SetFPM2105Data(SUserInfo user, int? groutingLineID, int? groutingDailyID, string groutingDailyDetailIDs, DateTime groutingDate, string groutingUserCode, int? goodsID)
- {
- IDBTransaction tran = null;
- try
- {
- ServiceResultEntity sre = new ServiceResultEntity();
- tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- string sql = @"
- SELECT T.BARCODE,
- T.MATERIALCODE AS OLDMATNR
- FROM TP_PM_GROUTINGDAILYDETAIL T
- WHERE T.BARCODE IS NOT NULL
- AND T.GROUTINGDAILYID = :GROUTINGDAILYID
- AND (:GROUTINGDAILYDETAILIDS IS NULL OR :GROUTINGDAILYDETAILIDS = '' OR
- INSTR(',' || :GROUTINGDAILYDETAILIDS || ',', ',' || T.GROUTINGDAILYDETAILID || ',') > 0) ";
- DataTable dtMatnr = tran.GetSqlResultToDt(sql, new OracleParameter[]
- {
- new OracleParameter(":GROUTINGDAILYID", OracleDbType.Int32, groutingDailyID, ParameterDirection.Input),
- new OracleParameter(":GROUTINGDAILYDETAILIDS", OracleDbType.Varchar2,groutingDailyDetailIDs, ParameterDirection.Input)
- });
- OracleParameter[] paras = new OracleParameter[]
- {
- new OracleParameter("in_AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
- new OracleParameter("in_UserID", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
- new OracleParameter("in_GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input),
- new OracleParameter("in_GroutingDailyID", OracleDbType.Int32, groutingDailyID, ParameterDirection.Input),
- new OracleParameter("in_GroutingDailyDetailIDs", OracleDbType.Varchar2,groutingDailyDetailIDs, ParameterDirection.Input),
- new OracleParameter("in_GroutingDate", OracleDbType.Date, groutingDate, ParameterDirection.Input),
- new OracleParameter("in_GroutingUserCode", OracleDbType.Varchar2, groutingUserCode, ParameterDirection.Input),
- new OracleParameter("in_GoodsID", OracleDbType.Int32, goodsID, ParameterDirection.Input),
- new OracleParameter("out_Result", OracleDbType.Varchar2, 100, null, ParameterDirection.Output),
- new OracleParameter("out_Message", OracleDbType.Varchar2, 100, null, ParameterDirection.Output),
- };
- DataSet ds = tran.ExecStoredProcedure("PRO_PM_ReplacedGroutingInfo", paras);
- string out_Result = paras[8].Value.ToString();
- if (out_Result != "0")
- {
- sre.Status = Constant.ServiceResultStatus.Other;
- switch (out_Result)
- {
- case "1.0":
- sre.OtherStatus = 1;
- sre.Message = "当前用户没有此成型线的操作权限";
- break;
- case "1.1":
- sre.OtherStatus = 2;
- sre.Message = "此注浆日报中的条码已交坯不能修改成型工号。";
- break;
- case "1.2":
- sre.OtherStatus = 3;
- sre.Message = "工号[" + groutingUserCode + "]不存在或不是生产工号";
- break;
- case "1.3":
- sre.OtherStatus = 7;
- sre.Message = "工号[" + groutingUserCode + "]在" + groutingDate.Date + "没有班次配置。";
- break;
- case "2.1":
- sre.OtherStatus = 4;
- sre.Message = "此产品不存在。";
- break;
- default:
- break;
- }
- }
- if (dtMatnr.Rows.Count > 0)
- {
- foreach (DataRow row in dtMatnr.Rows)
- {
- // 切换物料，切换已装组件
- string returnMessage = string.Empty;
- int returnRows = SetMatnrIdnrk(tran, row["OLDMATNR"] + "", row["BARCODE"] + "", user, out returnMessage);
- if (returnRows < 0)
- {
- sre.Status = Constant.ServiceResultStatus.Other;
- sre.OtherStatus = returnRows;
- sre.Message = returnMessage;
- return sre;
- }
- }
- }
- tran.Commit();
- return sre;
- }
- catch (Exception ex)
- {
- if (tran != null &&
- tran.ConnState == ConnectionState.Open)
- {
- tran.Rollback();
- }
- throw ex;
- }
- finally
- {
- if (tran != null &&
- tran.ConnState == ConnectionState.Open)
- {
- tran.Disconnect();
- }
- }
- }
- /// <summary>
- /// 注浆变更
- /// </summary>
- /// <param name="user">登录用户信息</param>
- /// <param name="barcode">原条码</param>
- /// <param name="newBarcode">新条码</param>
- /// <param name="remarks">备注</param>
- /// <returns>操作结果</returns>
- public static ServiceResultEntity SetFPM2105CancelBarCodeData(SUserInfo user, string BarCodes)
- {
- IDBTransaction tran = null;
- try
- {
- ServiceResultEntity sre = new ServiceResultEntity();
- tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
- string[] subBarCode = BarCodes.Split(',');
- string sql = "";
- string returnValue = "";
- bool isError = false;
- for (int i = 0; i < subBarCode.Length; i++)
- {
- if (subBarCode[i] != "")
- {
- //sql = "select DELIVERFLAG from TP_PM_GroutingDailyDetail where barcode='" + subBarCode[i] + "'";
- sql = "select BEGINNINGFLAG from TP_PM_GroutingDailyDetail where barcode='" + subBarCode[i] + "'";
- returnValue = tran.GetSqlResultToStr(sql);
- if (returnValue == "1")
- {
- sre.Status = Constant.ServiceResultStatus.Other;
- //sre.Message = "条码[" + subBarCode[i] + "]已经交坯，不能取消绑定";
- sre.Message = "条码[" + subBarCode[i] + "]已经在产，不能取消绑定";
- isError = true;
- break;
- }
- sql = "update TP_PM_GroutingDailyDetail set barcode='' where barcode='" + subBarCode[i] + "'";
- tran.ExecuteNonQuery(sql);
- sql = "delete from TP_PM_UsedBarCode where barcode='" + subBarCode[i] + "'";
- //sql = "update TP_PM_UsedBarCode set barcode='" + subBarCode[i] + "@" + user.UserCode + "@"
- // + DateTime.Now.ToString("yyyyMMddHHmmss") + "' where barcode='" + subBarCode[i] + "'";
- tran.ExecuteNonQuery(sql);
- }
- }
- if (isError) //有错误
- {
- tran.Rollback();
- }
- else
- {
- tran.Commit();
- }
- return sre;
- }
- catch (Exception ex)
- {
- if (tran != null &&
- tran.ConnState == ConnectionState.Open)
- {
- tran.Rollback();
- }
- throw ex;
- }
- finally
- {
- if (tran != null &&
- tran.ConnState == ConnectionState.Open)
- {
- tran.Disconnect();
- }
- }
- }
- /// <summary>
- /// 设置物料编码组件
- /// </summary>
- /// <returns></returns>
- private static int SetMatnrIdnrk(IDBTransaction oracleTrConn, string oldMatnr, string barcode, SUserInfo sUserInfo, out string message)
- {
- int returnRows = 0;
- message = "";
- try
- {
- string sql = "";
- sql = @"
- SELECT GDD.MATERIALCODE AS MATNR,
- DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS ZSCMS,
- CASE
- WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
- AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
- 1
- WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
- AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
- 2
- WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN
- 3
- ELSE
- 9
- END AS WORKSHOP
- FROM TP_PM_GROUTINGDAILYDETAIL GDD
- INNER JOIN TP_MST_GOODS G
- ON G.GOODSID = GDD.GOODSID
- INNER JOIN TP_MST_GOODSTYPE GT
- ON GT.GOODSTYPEID = G.GOODSTYPEID
- WHERE GDD.BARCODE = :BARCODE ";
- DataTable dtMatnr = oracleTrConn.GetSqlResultToDt(sql, new OracleParameter[]
- {
- new OracleParameter(":barcode", OracleDbType.Varchar2, barcode, ParameterDirection.Input)
- });
- // 如果物料编码不一致
- if (!oldMatnr.Equals(dtMatnr.Rows[0]["MATNR"]))
- {
- sql = @"
- SELECT IDNRK,
- MEINS,
- MENGE,
- IDNRKNAME,
- IDNRKONLYCODE,
- CHARG,
- LGORT
- FROM TP_PM_BARCODEIDNRKREL
- WHERE VALUEFLAG = '1'
- AND BARCODE = :BARCODE ";
- DataTable dtIdnrks = oracleTrConn.GetSqlResultToDt(sql, new OracleParameter[]
- {
- new OracleParameter(":BARCODE", OracleDbType.Varchar2, barcode, ParameterDirection.Input)
- });
- // 如果装了配件了
- if (dtIdnrks.Rows.Count > 0)
- {
- string datuv = System.DateTime.Now.Date.ToString("yyyyMMdd");
- Hashtable pars = new Hashtable();
- pars.Add("MATNR", dtMatnr.Rows[0]["MATNR"]); // 物料
- pars.Add("WERKS", "5000"); // 工厂
- pars.Add("ZSCS", "T"); // 生产工艺
- pars.Add("ZSCCJ", dtMatnr.Rows[0]["WORKSHOP"]); // 生产车间
- pars.Add("ZSCMS", dtMatnr.Rows[0]["ZSCMS"]); // 生产模式
- pars.Add("ZJDNU", "60"); // 节点
- pars.Add("DATUV", datuv); // 查询日期
- pars.Add("EMENG", 1); // 需求数量
- Hashtable item = new Hashtable();
- item.Add("item", pars);
- Hashtable body = new Hashtable();
- body.Add("T_INPUT", item);
- string postString = JsonConvert.SerializeObject(body);
- INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
- string url039 = ini.ReadIniData("SAP_NEW_INFO", "Url039");
- //string url039 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM039";
- string result = string.Empty;
- try
- {
- result = SAPDataLogic.PostData(url039, postString, "POST");
- }
- catch (Exception ex)
- {
- message = "获取SAP库存接口异常：\n" + ex.Message;
- return -4;
- }
- JObject returnObj = JsonConvert.DeserializeObject<JObject>(result);
- JObject output = returnObj["T_OUTPUT"] as JObject;
- JArray array = output["item"] as JArray;
- DataTable dtIdnrk = Utility.ConvertToDataTable(array);
- if ("E".Equals(dtIdnrk.Rows[0]["ZTYPE"] + ""))
- {
- message = "获取SAP库存失败";
- return -5;
- }
- dtIdnrk.DefaultView.RowFilter = "MAKTX NOT LIKE '%半成品%'";
- dtIdnrk = dtIdnrk.DefaultView.ToTable();
- // 获取已走过的装配工序
- string procedureidlist = oracleTrConn.GetSqlResultToStr(@"
- SELECT LISTAGG(PROCEDUREID, ',') WITHIN GROUP(ORDER BY PROCEDUREID) AS PROCEDUREIDLIST
- FROM TP_PM_PRODUCTIONDATA
- WHERE VALUEFLAG = '1'
- AND MODELTYPE = '-5'
- AND BARCODE = :BARCODE ",
- new OracleParameter[] { new OracleParameter("BARCODE", barcode) }
- );
- procedureidlist = "," + procedureidlist + ",";
- // 过滤当前工序需要的组件
- DataTable dtIdnrkType = oracleTrConn.GetSqlResultToDt(@"
- SELECT IDNRKTYPE
- FROM TP_PC_PROCEDUREIDNRKTYPE
- WHERE INSTR(:PROCEDUREIDLIST , ',' || PROCEDUREID || ',') > 0",
- new OracleParameter[] { new OracleParameter("PROCEDUREIDLIST", procedureidlist) }
- );
- dtIdnrk.DefaultView.RowFilter = "MAKTX NOT LIKE '%半成品%'";
- dtIdnrk = dtIdnrk.DefaultView.ToTable();
- // 如果包含其它，就不判断了
- if (dtIdnrkType.Select("IDNRKTYPE = '其它'").Length == 0)
- {
- string fifter = "";
- foreach (DataRow row in dtIdnrkType.Rows)
- {
- fifter += " MAKTX LIKE '%" + row["IDNRKTYPE"] + "%' OR";
- }
- // 如果没有条件，也不判断了
- if (fifter.Length > 0)
- {
- fifter = fifter.Substring(0, fifter.Length - 2);
- dtIdnrk.DefaultView.RowFilter = fifter;
- dtIdnrk = dtIdnrk.DefaultView.ToTable();
- }
- }
- // 过滤掉已安装的组件
- object idnrklist = oracleTrConn.GetSqlResultToStr(@"
- SELECT LISTAGG(IDNRK, ''',''') WITHIN GROUP(ORDER BY IDNRK) AS IDNRKLIST
- FROM TP_PM_BARCODEIDNRKREL
- WHERE VALUEFLAG = '1'
- AND BARCODE = :BARCODE ",
- new OracleParameter[] { new OracleParameter("BARCODE", barcode) }
- );
- // 可以保留的组件
- DataTable dtCanSaveIdnrk = dtIdnrk.Copy();
- dtCanSaveIdnrk.DefaultView.RowFilter = "IDNRK IN ('" + idnrklist + "')";
- dtCanSaveIdnrk = dtCanSaveIdnrk.DefaultView.ToTable();
- if (idnrklist != null)
- {
- dtIdnrk.DefaultView.RowFilter = "IDNRK NOT IN ('" + idnrklist + "')";
- dtIdnrk = dtIdnrk.DefaultView.ToTable();
- }
- if (dtIdnrk.Rows.Count == 0)
- {
- }
- // 加上需要的列
- dtIdnrk.Columns.Add("LGORT", typeof(string));
- dtIdnrk.Columns.Add("CHARG", typeof(string));
- dtIdnrk.Columns.Add("IDNRKONLYCODE", typeof(string));
- // 提取包材物料编码
- List<string> matnrs = new List<string>();
- foreach (DataRow row in dtIdnrk.Rows)
- {
- if (!matnrs.Contains(row["IDNRK"] + ""))
- {
- matnrs.Add(row["IDNRK"] + "");
- }
- }
- string ZMSG = string.Empty;
- // 查线边仓库存
- DataTable dtSapInventory = SapApi.ZMM_WMS016("5000", matnrs, "", out ZMSG);
- dtSapInventory.DefaultView.RowFilter = "LGORT IN('2420','2430','2440','2450','2460','2470','2480','2490')";
- dtSapInventory = dtSapInventory.DefaultView.ToTable();
- DataRow[] rows = null;
- if (dtSapInventory != null && dtSapInventory.Rows.Count > 0)
- {
- // 改名
- dtSapInventory.Columns["MATNR"].ColumnName = "IDNRK";
- // 判断是否缺库存
- List<string> notEnoughIdnrks = new List<string>();
- foreach (string idnrk in matnrs)
- {
- rows = dtSapInventory.Select("IDNRK = '" + idnrk + "'"); ;
- if (rows.Length == 0)
- {
- notEnoughIdnrks.Add(idnrk);
- }
- }
- if (notEnoughIdnrks.Count > 0)
- {
- message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", notEnoughIdnrks.ToArray());
- return -6;
- }
- // 库存数量要改为数字类型
- dtSapInventory.Columns.Add("BALANCE", typeof(decimal));
- decimal balance = 0;
- foreach (DataRow row in dtSapInventory.Rows)
- {
- decimal.TryParse(row["KYKC"] + "", out balance);
- row["BALANCE"] = balance;
- }
- }
- else
- {
- message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", matnrs.ToArray());
- return -6;
- }
- dtSapInventory.DefaultView.RowFilter = "BALANCE <> 0";
- dtSapInventory = dtSapInventory.DefaultView.ToTable();
- dtSapInventory.DefaultView.Sort = "LGORT, CHARG";
- dtSapInventory = dtSapInventory.DefaultView.ToTable();
- DateTime now = DateTime.Now;
- foreach (DataRow row in dtIdnrk.Rows)
- {
- rows = dtSapInventory.Select("IDNRK = '" + row["IDNRK"] + "' AND BALANCE >= " + row["MENGE"]);
- if (rows.Length > 0)
- {
- row["LGORT"] = rows[0]["LGORT"];
- row["CHARG"] = rows[0]["CHARG"];
- row["IDNRKONLYCODE"] = "";
- }
- else
- {
- message = row["IDNRK"] + ":库存不足";
- return -6;
- }
- }
- dtIdnrk.TableName = "Idnrk";
- dtCanSaveIdnrk.TableName = "CanSaveIdnrk";
- string canSaveIdnrks = ",";
- if (dtCanSaveIdnrk != null && dtCanSaveIdnrk.Rows.Count > 0)
- {
- foreach (DataRow row in dtCanSaveIdnrk.Rows)
- {
- canSaveIdnrks += row["IDNRK"] + ",";
- }
- }
- string delSql = @"
- DELETE FROM TP_PM_BARCODEIDNRKREL
- WHERE BARCODE = :BARCODE
- AND INSTR(:CANSAVEIDNRKS, ','|| IDNRK ||',') = 0 ";
- returnRows += oracleTrConn.ExecuteNonQuery(delSql, new OracleParameter[]
- {
- new OracleParameter(":BARCODE", barcode),
- new OracleParameter(":CANSAVEIDNRKS", canSaveIdnrks)
- });
- if (dtIdnrk != null && dtIdnrk.Rows.Count > 0)
- {
- string barcodeidnrkrel = @"
- INSERT INTO TP_PM_BARCODEIDNRKREL
- (PROCEDUREID,
- BARCODE,
- MATNR,
- IDNRK,
- MEINS,
- MENGE,
- IDNRKNAME,
- IDNRKONLYCODE,
- CHARG,
- LGORT,
- ACCOUNTID,
- CREATEUSERID,
- UPDATEUSERID)
- VALUES
- (:PROCEDUREID,
- :BARCODE,
- :MATNR,
- :IDNRK,
- :MEINS,
- :MENGE,
- :IDNRKNAME,
- :IDNRKONLYCODE,
- :CHARG,
- :LGORT,
- :ACCOUNTID,
- :USERID,
- :USERID) ";
- foreach (DataRow row in dtIdnrk.Rows)
- {
- returnRows += oracleTrConn.ExecuteNonQuery(barcodeidnrkrel,
- new OracleParameter[]
- {
- new OracleParameter(":PROCEDUREID", -1),
- new OracleParameter(":BARCODE", barcode),
- new OracleParameter(":MATNR", row["MATNR"]),
- new OracleParameter(":IDNRK", row["IDNRK"]),
- new OracleParameter(":MEINS", row["MEINS"]),
- new OracleParameter(":MENGE", Convert.ToDecimal( row["MENGE"])),
- new OracleParameter(":IDNRKNAME", row["MAKTX"]),
- new OracleParameter(":IDNRKONLYCODE", row["IDNRKONLYCODE"]),
- new OracleParameter(":CHARG", row["CHARG"]),
- new OracleParameter(":LGORT", row["LGORT"]),
- new OracleParameter(":ACCOUNTID", sUserInfo.AccountID),
- new OracleParameter(":USERID", sUserInfo.UserID)
- });
- }
- }
- }
- }
- }
- catch (Exception ex)
- {
- message = "接口异常：\n" + ex.Message;
- return -4;
- }
- return returnRows;
- }
- }
- }
|
