using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Transactions; using Dapper; using Fab2ApprovalSystem.Misc; using Fab2ApprovalSystem.Models; using Fab2ApprovalSystem.ViewModels; namespace Fab2ApprovalSystem.DMO; public class LotDispositionDMO { private readonly WorkflowDMO wfDMO = new(); private readonly IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING); public IEnumerable GetTaskList(int userID) { // eventually, the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List lotDispostions = db.Query("GetTaskListByUser", parameters, commandType: CommandType.StoredProcedure).ToList(); return lotDispostions; } public IEnumerable GetMyOpenActionItems(int userID) { DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List lotDispostions = db.Query("GetOpenActionItemsByUser", parameters, commandType: CommandType.StoredProcedure).ToList(); return lotDispostions; } public IEnumerable GetLotDispositions() { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items List lotDispostions = db.Query("GetLotDispositions", null, commandType: CommandType.StoredProcedure).ToList(); return lotDispostions; } public IEnumerable GetDocuments() { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items List lotDispostions = db.Query("GetDocuments", null, commandType: CommandType.StoredProcedure).ToList(); return lotDispostions; } public IEnumerable GetWorkRequests() { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items List workReqs = db.Query("LTGetWorkRequests", null, commandType: CommandType.StoredProcedure).ToList(); return workReqs; } public IEnumerable GetAuditList(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List al = db.Query("_8DGetAuditList", parameters, commandType: CommandType.StoredProcedure).ToList(); return al; } public IEnumerable GetCorrectiveActionList(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List cal = db.Query("_8DGetCorrectiveActionList", parameters, commandType: CommandType.StoredProcedure).ToList(); return cal; } public IEnumerable GetChangeControls(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List cc = db.Query("CCGetChangeControlList", parameters, commandType: CommandType.StoredProcedure).ToList(); return cc; } public IEnumerable GetMRBList(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List cc = db.Query("MRBGetMRBList", null, commandType: CommandType.StoredProcedure).ToList(); return cc; } public IEnumerable GetECNList(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List cc = db.Query("ECNGetECNList", null, commandType: CommandType.StoredProcedure).ToList(); return cc; } public IEnumerable GetLotDispositionList(int userID) { // later on the View Model will refer to a generic task list instead of the just Lot Disposition Items DynamicParameters parameters = new(); parameters.Add("@UserID", userID); List cc = db.Query("GetLotDispositionList", null, commandType: CommandType.StoredProcedure).ToList(); return cc; } public LotDisposition GetLotDispositionItem(int issueID, out int isITAR, int userID) { /* IsITAR 0 = false 1 = true 2 = not required */ isITAR = 2; LotDisposition issueItem = new(); DynamicParameters parameters = new(); parameters.Add("@IssueID", value: issueID); parameters.Add("@UserID", userID); parameters.Add("@IsITAR", value: isITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput); using (var multipleResultItems = db.QueryMultiple("GetLotDispositionItem", parameters, commandType: CommandType.StoredProcedure)) { issueItem = multipleResultItems.Read().SingleOrDefault(); List departments = multipleResultItems.Read().ToList(); if (issueItem != null && departments != null) { issueItem.DepartmentIDs.AddRange(departments); } isITAR = parameters.Get("@IsITAR"); } return issueItem; } public LotDisposition GetLotDispositionItemForRead(int issueID, out int isITAR, int userID) { /* IsITAR 0 = false 1 = true 2 = not required */ isITAR = 2; LotDisposition issueItem = new(); DynamicParameters parameters = new(); parameters.Add("@IssueID", value: issueID); parameters.Add("@UserID", userID); parameters.Add("@IsITAR", value: isITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput); using (var multipleResultItems = db.QueryMultiple("GetLotDispositionItemForRead", parameters, commandType: CommandType.StoredProcedure)) { issueItem = multipleResultItems.Read().SingleOrDefault(); List departments = multipleResultItems.Read().ToList(); if (issueItem != null && departments != null) { issueItem.DepartmentIDs.AddRange(departments); } isITAR = parameters.Get("@IsITAR"); } return issueItem; } public int GetRHLotCount(int issueID) { StringBuilder query = new("SELECT COUNT(*) FROM dbo.fnGetLot_RH(@IssueID) "); return db.Query(query.ToString(), new { IssueID = issueID }).SingleOrDefault(); } public List GetDepartments() { List departments = db.Query("GetLotDispositionDepartments", null, commandType: CommandType.StoredProcedure).ToList(); return departments; } #region LotDisposition public LotDisposition InsertLotDisposition(LotDisposition lotDispo) { DynamicParameters parameters = new(); using (TransactionScope transanction = new()) { // Lot Disposition parameters = new DynamicParameters(); parameters.Add("@IssueID", value: lotDispo.IssueID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput); parameters.Add("@Title", lotDispo.Title); parameters.Add("@IssueDescription", lotDispo.IssueDescription); parameters.Add("@PERequired", lotDispo.PERequired); parameters.Add("@ResponsibilityID", lotDispo.ResponsibilityID); parameters.Add("@IssueDate", lotDispo.IssueDate); parameters.Add("@OriginatorID", lotDispo.OriginatorID); parameters.Add("@ReasonForDisposition", lotDispo.ReasonForDisposition); parameters.Add("@ResponsibilityIssueID", lotDispo.ResponsibilityIssueID); parameters.Add("@SPNScrapCode", lotDispo.SPNScrapCode); parameters.Add("@CurrentStep", lotDispo.CurrentStep); db.Execute("InsertLotDisposition", parameters, commandType: CommandType.StoredProcedure); lotDispo.IssueID = parameters.Get("@IssueID"); // Lot Update // LotDispoDepartment List lotDispDepIDs = lotDispo.DepartmentIDs; if (lotDispDepIDs != null) { foreach (int depIds in lotDispDepIDs) { parameters = new DynamicParameters(); parameters.Add("@IssueID", depIds); parameters.Add("@DepartMentID", lotDispo.IssueID); db.Execute("InsertLotDispoDepartment", parameters, commandType: CommandType.StoredProcedure); } } transanction.Complete(); return lotDispo; } } public ScrapLot GetLotStausDetail(int issueID, string lotNumber) => db.Query("SELECT L.WaferCount, S.* FROM ScrapLot S INNER JOIN Lot L ON S.IssueID = L.IssueID AND S.LotNo = L.LotNumber WHERE S.IssueID = @IssueID AND LotNo = @lotNumber", new { IssueID = issueID, lotNumber = lotNumber }).SingleOrDefault(); public void UpdateLotDisposition(LotDisposition lotDispo) { DynamicParameters parameters = new(); using (TransactionScope transanction = new()) { // Lot Disposition parameters = new DynamicParameters(); parameters.Add("@IssueID", lotDispo.IssueID); parameters.Add("@Title", lotDispo.Title); parameters.Add("@IssueDescription", lotDispo.IssueDescription); parameters.Add("@PERequired", lotDispo.PERequired); parameters.Add("@MRBRequired", lotDispo.MRBRequired); parameters.Add("@DispositionByOCAP", lotDispo.DispositionByOCAP); parameters.Add("@CANo", lotDispo.CANo); parameters.Add("@ResponsibilityID", lotDispo.ResponsibilityID); parameters.Add("@IssueDate", lotDispo.IssueDate); parameters.Add("@ReasonForDisposition", lotDispo.ReasonForDisposition); parameters.Add("@ResponsibilityIssueID", lotDispo.ResponsibilityIssueID); parameters.Add("@SPNScrapCode", lotDispo.SPNScrapCode); db.Execute("UpdateLotDisposition", parameters, commandType: CommandType.StoredProcedure); List lotDispDepIDs = lotDispo.DepartmentIDs; if (lotDispDepIDs != null) { parameters = new DynamicParameters(); parameters.Add("@IssueID", lotDispo.IssueID); db.Execute("DeleteLotDispositionDepartment", parameters, commandType: CommandType.StoredProcedure); foreach (int depIds in lotDispDepIDs) { parameters = new DynamicParameters(); parameters.Add("@IssueID", lotDispo.IssueID); parameters.Add("@DepartmentID", depIds); db.Execute("InsertLotDispositionDepartment", parameters, commandType: CommandType.StoredProcedure); } } transanction.Complete(); } } #endregion #region LotDispoitio Lot public int InsertLot(Lot lot, bool getLotInfo) { if (getLotInfo) { MiscDMO.GetLotInformation(lot); } DynamicParameters parameters = new(); parameters.Add("@LotID", value: lot.LotID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput); parameters.Add("@LotNumber", lot.LotNumber); parameters.Add("@DieLotNumber", lot.DieLotNumber); parameters.Add("@IssueID", lot.IssueID); parameters.Add("@Description", lot.Description); parameters.Add("@NewPartNo", lot.NewPartNo); parameters.Add("@WipPartNo", lot.WipPartNo); parameters.Add("@DiePartNo", lot.DiePartNo); parameters.Add("@ProductFamily", lot.ProductFamily); parameters.Add("@Gen", lot.Gen); parameters.Add("@Channel", lot.Channel); parameters.Add("@Hexsize", lot.Hexsize); parameters.Add("@Voltage", lot.Voltage); parameters.Add("@WaferCount", lot.WaferCount); parameters.Add("@DieCount", lot.DieCount); parameters.Add("@Location", lot.Location); parameters.Add("@TotalCost", lot.TotalCost); parameters.Add("@LotStatusOptionID", lot.LotStatusOption.LotStatusOptionID); parameters.Add("@QualityCode", lot.QualityCode); parameters.Add("@OpenIssueWithExistingLot", dbType: DbType.Int32, direction: ParameterDirection.Output); db.Execute("InsertLotDispositionLot", parameters, commandType: CommandType.StoredProcedure); int lotID = parameters.Get("@LotID"); if (parameters.Get("@OpenIssueWithExistingLot") != 0) { lot.OpenIssueWithExistingLots = parameters.Get("@OpenIssueWithExistingLot"); } return lotID; } public IEnumerable GetMRBsFromSPN(string lotNumber) { DynamicParameters parameters = new(); parameters.Add("@LotNo", lotNumber); List mrbList = db.Query("GetMRBsFromSPN", parameters, commandType: CommandType.StoredProcedure).ToList(); return mrbList; } public void InsertChildLot_NotInTheMRB(string lotNumber) { DynamicParameters parameters = new(); parameters.Add("@LotNo", lotNumber); db.Execute("InsertChildLot_NotInTheMRB_LD", parameters, commandType: CommandType.StoredProcedure); } public void DeleteCADocument(int CANo, int userID, string caTypeString) { DynamicParameters parameters = new(); parameters.Add("@UserID", userID); parameters.Add("@CANo", CANo); parameters.Add("@CAType", caTypeString); db.Execute("_8DDeleteCADocument", parameters, commandType: CommandType.StoredProcedure); } public void DeleteLotDispoLot(int lotID) { DynamicParameters parameters = new(); parameters.Add("@LotID", lotID); db.Execute("DeleteLotDispositionLot", parameters, commandType: CommandType.StoredProcedure); } public void DeleteAllLotDispoLot(int issueID) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); db.Execute("DeleteLotDispositionAllLots", parameters, commandType: CommandType.StoredProcedure); } public void UpdateLotDispoLot(Lot lot) { DynamicParameters parameters = new(); parameters.Add("@LotID", lot.LotID); parameters.Add("@LotNumber", lot.LotNumber); parameters.Add("@DieLotNumber", lot.DieLotNumber); parameters.Add("@IssueID", lot.IssueID); parameters.Add("@Description", lot.Description); parameters.Add("@NewPartNo", lot.NewPartNo); parameters.Add("@WipPartNo", lot.WipPartNo); parameters.Add("@DiePartNo", lot.DiePartNo); parameters.Add("@ProductFamily", lot.ProductFamily); parameters.Add("@Gen", lot.Gen); parameters.Add("@Channel", lot.Channel); parameters.Add("@Hexsize", lot.Hexsize); parameters.Add("@Voltage", lot.Voltage); parameters.Add("@WaferCount", lot.WaferCount); parameters.Add("@DieCount", lot.DieCount); parameters.Add("@Location", lot.Location); parameters.Add("@TotalCost", lot.TotalCost); parameters.Add("@LotStatusOptionID", lot.LotStatusOption.LotStatusOptionID); db.Execute("UpdateLotDispositionLot", parameters, commandType: CommandType.StoredProcedure); // Update the Scrap Lot table ===================================================================== ScrapLot sl = new(); sl.IssueID = lot.IssueID; sl.LotNo = lot.LotNumber; sl.WaferCount = lot.WaferCount; foreach (PropertyInfo pi in sl.GetType().GetProperties()) { if (pi.Name.ToLower().StartsWith("lot") && pi.Name.ToLower().EndsWith("state")) { if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.Release) pi.SetValue(sl, (byte)1, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.Scrap) pi.SetValue(sl, (byte)2, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.NotAvailable) pi.SetValue(sl, (byte)3, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.M_Suffix) pi.SetValue(sl, (byte)4, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.Select_Wafers) pi.SetValue(sl, (byte)5, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.CloseToQDB) pi.SetValue(sl, (byte)6, null); else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.SplitOffHold) pi.SetValue(sl, (byte)7, null); } } // if the Lot Status is M_Suffix, the whole lot is automatically in a Release state per Hans if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.Release || lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.M_Suffix || lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.CloseToQDB) { sl.ScrapCount = 0; sl.ReleaseCount = lot.WaferCount > 0 ? lot.WaferCount : lot.DieCount; lot.ScrapCount = 0; lot.ReleaseCount = sl.ReleaseCount; } else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.Scrap) { sl.ScrapCount = lot.WaferCount > 0 ? lot.WaferCount : lot.DieCount; sl.ReleaseCount = 0; lot.ScrapCount = lot.WaferCount > 0 ? lot.WaferCount : lot.DieCount; lot.ReleaseCount = 0; } else if (lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.SplitOffHold || lot.LotStatusOption.LotStatusOptionID == (int)GlobalVars.LotStatusOption.NotAvailable) { sl.ScrapCount = 0; sl.ReleaseCount = 0; lot.ScrapCount = 0; lot.ReleaseCount = 0; } UpdateLotScrapReleaseStatus(sl); } #endregion public IEnumerable GetLotStatusOptions() { List lotStatusOption = db.Query("SELECT * FROM LotStatusOption").ToList(); return lotStatusOption; } public IEnumerable GetLotDispositionLots(int issueID) { // NOTE: Any new fields that needs to be added to select list , needs to be referenced prior to the "LS.LotStatusOptionID , LS.LotStatusOption" fields db.Open(); DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID, DbType.Int32); List data = db.Query ("GetLotDispositionLots", (lot, lotstatusoption) => { lot.LotStatusOption = lotstatusoption; return lot; }, param: parameters, commandType: CommandType.StoredProcedure, splitOn: "LotStatusOptionID").ToList(); return data; } public IEnumerable GetLotDispoAttachments(int issueID) { List attachments = db.Query("SELECT A.AttachmentID, A.IssueID, A.FileName, A.UserID, CONVERT(VARCHAR(10), A.UploadDate, 101) AS UploadDate, U.FirstName + ' ' + U.LastName AS FullName FROM Attachment A INNER JOIN Users U ON A.UserID = U.UserID WHERE IssueID = @IssueID ", new { IssueID = issueID }).ToList(); return attachments; } public void DeleteLotDispoAttachment(int attachmentID) { DynamicParameters parameters = new(); parameters.Add("@AttachmentID", attachmentID); db.Execute("DeleteLotDispositionAttachment", parameters, commandType: CommandType.StoredProcedure); } public void InsertLotDispositionAttachment(Attachment attach) { DynamicParameters parameters = new(); parameters.Add("@IssueID", attach.IssueID); parameters.Add("@UserID", attach.UserID); parameters.Add("@FileName", attach.FileName); db.Execute("InsertLotDispositionAttachment", parameters, commandType: CommandType.StoredProcedure); } public IEnumerable SearchLots(string searchText) { // string sql = "SELECT WP_LOT_NO AS LotNumber FROM SPNLot WHERE WP_LOT_NO LIKE '%" + searchText + "%' OR DieLotNumber LIKE '%" + searchText + "%'"; string sql = "SELECT WP_LOT_NO AS LotNumber FROM SPNLot WHERE WP_LOT_NO LIKE '%" + searchText + "%' "; List lotList = db.Query(sql).ToList(); return lotList; } public IEnumerable GetUserList() { StringBuilder sql = new(); sql.Append("SELECT FirstName + ' ' + LastName AS OriginatorName, U.UserID AS OriginatorID, FirstName "); sql.Append("FROM Users U "); sql.Append("ORDER BY FirstName"); return db.Query(sql.ToString()).ToList(); } public List GetResponsibilityList() { List departments = db.Query("SELECT ResponsibilityID, ResponsibilityName FROM Responsibility ORDER BY ResponsibilityName ", null, commandType: CommandType.Text).ToList(); return departments; } public List GetResponsibilityIssueList(int responsibilityID) { List respIssue = db.Query("SELECT ResponsibilityIssueID, Issue FROM ResponsibilityIssue WHERE ResponsibilityID = @ResponsibilityID ORDER BY Issue ", new { @ResponsibilityID = responsibilityID }, commandType: CommandType.Text).ToList(); return respIssue; } public void UpdateLotScrapReleaseStatus(ScrapLot scrap) { StringBuilder qryInsert = new(); qryInsert.Append("DELETE FROM ScrapLot WHERE LotNo = @LotNo AND IssueID = @IssueID"); qryInsert.Append(" INSERT INTO ScrapLot(LotNo,IssueID,ScrapCount,ReleaseCount, Lot1State,Lot2State,Lot3State,Lot4State,Lot5State,Lot6State,Lot7State,Lot8State,Lot9State,Lot10State, "); qryInsert.Append(" Lot11State,Lot12State,Lot13State,Lot14State,Lot15State,Lot16State,Lot17State,Lot18State,Lot19State,Lot20State, "); qryInsert.Append(" Lot21State,Lot22State,Lot23State,Lot24State,Lot25State,Lot26State,Lot27State,Lot28State,Lot29State,Lot30State, "); qryInsert.Append(" Lot31State,Lot32State,Lot33State,Lot34State,Lot35State,Lot36State,Lot37State,Lot38State,Lot39State,Lot40State, "); qryInsert.Append(" Lot41State,Lot42State,Lot43State,Lot44State,Lot45State,Lot46State,Lot47State,Lot48State,Lot49State,Lot50State) "); qryInsert.Append(" VALUES(@LotNo,@IssueID,@ScrapCount,@ReleaseCount,@Lot1State,@Lot2State,@Lot3State,@Lot4State,@Lot5State,@Lot6State,@Lot7State,@Lot8State,@Lot9State,@Lot10State,"); qryInsert.Append(" @Lot11State,@Lot12State,@Lot13State,@Lot14State,@Lot15State,@Lot16State,@Lot17State,@Lot18State,@Lot19State,@Lot20State,"); qryInsert.Append(" @Lot21State,@Lot22State,@Lot23State,@Lot24State,@Lot25State,@Lot26State,@Lot27State,@Lot28State,@Lot29State,@Lot30State,"); qryInsert.Append(" @Lot31State,@Lot32State,@Lot33State,@Lot34State,@Lot35State,@Lot36State,@Lot37State,@Lot38State,@Lot39State,@Lot40State,"); qryInsert.Append(" @Lot41State,@Lot42State,@Lot43State,@Lot44State,@Lot45State,@Lot46State,@Lot47State,@Lot48State,@Lot49State,@Lot50State)"); DynamicParameters parameters = new(); parameters.Add("@LotNo", scrap.LotNo); parameters.Add("@IssueID", scrap.IssueID); parameters.Add("@ScrapCount", scrap.ScrapCount); parameters.Add("@ReleaseCount", scrap.ReleaseCount); parameters.Add("@Lot1State", scrap.Lot1State); parameters.Add("@Lot2State", scrap.Lot2State); parameters.Add("@Lot3State", scrap.Lot3State); parameters.Add("@Lot4State", scrap.Lot4State); parameters.Add("@Lot5State", scrap.Lot5State); parameters.Add("@Lot6State", scrap.Lot6State); parameters.Add("@Lot7State", scrap.Lot7State); parameters.Add("@Lot8State", scrap.Lot8State); parameters.Add("@Lot9State", scrap.Lot9State); parameters.Add("@Lot10State", scrap.Lot10State); parameters.Add("@Lot11State", scrap.Lot11State); parameters.Add("@Lot12State", scrap.Lot12State); parameters.Add("@Lot13State", scrap.Lot13State); parameters.Add("@Lot14State", scrap.Lot14State); parameters.Add("@Lot15State", scrap.Lot15State); parameters.Add("@Lot16State", scrap.Lot16State); parameters.Add("@Lot17State", scrap.Lot17State); parameters.Add("@Lot18State", scrap.Lot18State); parameters.Add("@Lot19State", scrap.Lot19State); parameters.Add("@Lot20State", scrap.Lot20State); parameters.Add("@Lot21State", scrap.Lot21State); parameters.Add("@Lot22State", scrap.Lot22State); parameters.Add("@Lot23State", scrap.Lot23State); parameters.Add("@Lot24State", scrap.Lot24State); parameters.Add("@Lot25State", scrap.Lot25State); parameters.Add("@Lot26State", scrap.Lot26State); parameters.Add("@Lot27State", scrap.Lot27State); parameters.Add("@Lot28State", scrap.Lot28State); parameters.Add("@Lot29State", scrap.Lot29State); parameters.Add("@Lot30State", scrap.Lot30State); parameters.Add("@Lot31State", scrap.Lot31State); parameters.Add("@Lot32State", scrap.Lot32State); parameters.Add("@Lot33State", scrap.Lot33State); parameters.Add("@Lot34State", scrap.Lot34State); parameters.Add("@Lot35State", scrap.Lot35State); parameters.Add("@Lot36State", scrap.Lot36State); parameters.Add("@Lot37State", scrap.Lot37State); parameters.Add("@Lot38State", scrap.Lot38State); parameters.Add("@Lot39State", scrap.Lot39State); parameters.Add("@Lot40State", scrap.Lot40State); parameters.Add("@Lot41State", scrap.Lot41State); parameters.Add("@Lot42State", scrap.Lot42State); parameters.Add("@Lot43State", scrap.Lot43State); parameters.Add("@Lot44State", scrap.Lot44State); parameters.Add("@Lot45State", scrap.Lot45State); parameters.Add("@Lot46State", scrap.Lot46State); parameters.Add("@Lot47State", scrap.Lot47State); parameters.Add("@Lot48State", scrap.Lot48State); parameters.Add("@Lot49State", scrap.Lot49State); parameters.Add("@Lot50State", scrap.Lot50State); db.Execute(qryInsert.ToString(), parameters, commandType: CommandType.Text); } public void UpdateLotStatus(ScrapLot lotStatus) { // if all the wafers in a lot is either "RELEASE" or "SCRAP" from the "SELECT WAFERS SCREEN" set the Lot Status Option accodingly for the particula lot int tempLotStatus; if ((lotStatus.ReleaseCount == lotStatus.WaferCount || lotStatus.ScrapCount == lotStatus.WaferCount) && (lotStatus.ReleaseCount > 0 || lotStatus.WaferCount > 0)) { // set the LotStatus appropriately because the entire lot either is set to "RELEASE" or "SCRAP"' tempLotStatus = lotStatus.ReleaseCount == lotStatus.WaferCount ? (int)GlobalVars.LotStatusOption.Release : (int)GlobalVars.LotStatusOption.Scrap; // Close to QDB is count as "Release" if ((lotStatus.CloseToQDBCount == lotStatus.WaferCount) && (lotStatus.CloseToQDBCount > 0 || lotStatus.WaferCount > 0)) { tempLotStatus = (int)GlobalVars.LotStatusOption.CloseToQDB; } } else if ((lotStatus.SplitOfHoldCount == lotStatus.WaferCount) && (lotStatus.SplitOfHoldCount > 0 || lotStatus.WaferCount > 0)) { tempLotStatus = (int)GlobalVars.LotStatusOption.SplitOffHold; } else { tempLotStatus = (int)GlobalVars.LotStatusOption.Select_Wafers; } DynamicParameters parameters = new(); parameters.Add("@LotNo", lotStatus.LotNo); parameters.Add("@IssueID", lotStatus.IssueID); parameters.Add("@LotStatus", tempLotStatus); db.Execute("UpdateLotStatus", parameters, commandType: CommandType.StoredProcedure); } /// /// Update the Status of all the lots for a given Lot disposition document /// public void UpdateLotStatusAll(ScrapLot scrap, int lotStatus) { DynamicParameters parameters = new(); parameters.Add("@IssueID", scrap.IssueID); parameters.Add("@LotStatus", lotStatus); parameters.Add("@ScrapCount", 0); parameters.Add("@Lot1State", scrap.Lot1State); parameters.Add("@Lot2State", scrap.Lot2State); parameters.Add("@Lot3State", scrap.Lot3State); parameters.Add("@Lot4State", scrap.Lot4State); parameters.Add("@Lot5State", scrap.Lot5State); parameters.Add("@Lot6State", scrap.Lot6State); parameters.Add("@Lot7State", scrap.Lot7State); parameters.Add("@Lot8State", scrap.Lot8State); parameters.Add("@Lot9State", scrap.Lot9State); parameters.Add("@Lot10State", scrap.Lot10State); parameters.Add("@Lot11State", scrap.Lot11State); parameters.Add("@Lot12State", scrap.Lot12State); parameters.Add("@Lot13State", scrap.Lot13State); parameters.Add("@Lot14State", scrap.Lot14State); parameters.Add("@Lot15State", scrap.Lot15State); parameters.Add("@Lot16State", scrap.Lot16State); parameters.Add("@Lot17State", scrap.Lot17State); parameters.Add("@Lot18State", scrap.Lot18State); parameters.Add("@Lot19State", scrap.Lot19State); parameters.Add("@Lot20State", scrap.Lot20State); parameters.Add("@Lot21State", scrap.Lot21State); parameters.Add("@Lot22State", scrap.Lot22State); parameters.Add("@Lot23State", scrap.Lot23State); parameters.Add("@Lot24State", scrap.Lot24State); parameters.Add("@Lot25State", scrap.Lot25State); parameters.Add("@Lot26State", scrap.Lot26State); parameters.Add("@Lot27State", scrap.Lot27State); parameters.Add("@Lot28State", scrap.Lot28State); parameters.Add("@Lot29State", scrap.Lot29State); parameters.Add("@Lot30State", scrap.Lot30State); parameters.Add("@Lot31State", scrap.Lot31State); parameters.Add("@Lot32State", scrap.Lot32State); parameters.Add("@Lot33State", scrap.Lot33State); parameters.Add("@Lot34State", scrap.Lot34State); parameters.Add("@Lot35State", scrap.Lot35State); parameters.Add("@Lot36State", scrap.Lot36State); parameters.Add("@Lot37State", scrap.Lot37State); parameters.Add("@Lot38State", scrap.Lot38State); parameters.Add("@Lot39State", scrap.Lot39State); parameters.Add("@Lot40State", scrap.Lot40State); parameters.Add("@Lot41State", scrap.Lot41State); parameters.Add("@Lot42State", scrap.Lot42State); parameters.Add("@Lot43State", scrap.Lot43State); parameters.Add("@Lot44State", scrap.Lot44State); parameters.Add("@Lot45State", scrap.Lot45State); parameters.Add("@Lot46State", scrap.Lot46State); parameters.Add("@Lot47State", scrap.Lot47State); parameters.Add("@Lot48State", scrap.Lot48State); parameters.Add("@Lot49State", scrap.Lot49State); parameters.Add("@Lot50State", scrap.Lot50State); db.Execute("UpdateScrapLotAll", parameters, commandType: CommandType.StoredProcedure); } public LotDispositionLotSummaryViewModel GetLotDispositionLotSummary(int issueID) { StringBuilder sql = new(); sql.Append("SELECT L.WaferCount AS 'TotalWaferCount', L.DieCount AS 'TotalDieCount', L.TotalCost AS 'TotalCost', S.ScrapCount AS 'ScrapWaferCount', S.ReleaseCount AS 'ReleaseWaferCount', L.LotStatusOptionID AS 'LotStatusOption' "); sql.Append("FROM ScrapLot S INNER JOIN Lot L ON S.IssueID = L.IssueID AND S.LotNo = L.LotNumber "); sql.Append("WHERE S.IssueID = @issueID"); List LotDispositionLotList = db.Query(sql.ToString(), new { issueID = issueID }).ToList(); int lotCount = 0; int totalWaferScrapCount = 0; int totalDieScrapCount = 0; double totalScrapCost = 0; int totalWaferReleaseCount = 0; int totalDieReleaseCount = 0; double totalReleaseCost = 0; int releaseWaferCount = 0; double releaseCost = 0; int releaseDieCount = 0; int scrapWaferCount = 0; double scrapCost = 0; int scrapDieCount = 0; foreach (LotDispositionLotSummaryViewModel ld in LotDispositionLotList) { releaseWaferCount = 0; releaseCost = 0; releaseDieCount = 0; scrapWaferCount = 0; scrapCost = 0; scrapDieCount = 0; // Wafer=================================================================== if (ld.ScrapWaferCount > 0 && ld.TotalWaferCount > 0 && ld.TotalCost > 0) { scrapWaferCount = ld.ScrapWaferCount; double perWaferCost = ld.TotalCost / ld.TotalWaferCount; scrapCost = scrapWaferCount * perWaferCost; } else if (ld.ScrapWaferCount == 0) { scrapWaferCount = ld.ScrapWaferCount; scrapCost = 0; } if (ld.ReleaseWaferCount > 0 && ld.TotalWaferCount > 0 && ld.TotalCost > 0) { releaseWaferCount = ld.ReleaseWaferCount; double perWaferCost = ld.TotalCost / ld.TotalWaferCount; releaseCost = releaseWaferCount * perWaferCost; } else if (ld.ReleaseWaferCount == 0) { releaseWaferCount = ld.ReleaseWaferCount; releaseCost = 0; } // Die =================================================================== if ( (ld.LotStatusOption == (int)GlobalVars.LotStatusOption.Release || ld.LotStatusOption == (int)GlobalVars.LotStatusOption.M_Suffix || ld.LotStatusOption == (int)GlobalVars.LotStatusOption.CloseToQDB) && ld.TotalDieCount > 0) { releaseDieCount = ld.TotalDieCount;// releaseCost = ld.TotalCost; } if (ld.LotStatusOption == (int)GlobalVars.LotStatusOption.Scrap && ld.TotalDieCount > 0) { scrapDieCount = ld.TotalDieCount;// scrapCost = ld.TotalCost; } // Summarize lotCount++; totalWaferScrapCount += scrapWaferCount; totalWaferReleaseCount += releaseWaferCount; totalDieScrapCount += scrapDieCount; totalDieReleaseCount += releaseDieCount; totalScrapCost += scrapCost; totalReleaseCost += releaseCost; } return new LotDispositionLotSummaryViewModel() { LotCount = lotCount, ReleaseCost = string.Format("{0:C}", totalReleaseCost), ReleaseWaferCount = totalWaferReleaseCount, ReleaseDieCount = totalDieReleaseCount, ScrapCost = string.Format("{0:C}", totalScrapCost), ScrapWaferCount = totalWaferScrapCount, ScrapDieCount = totalDieScrapCount }; } public int SubmitDocument(int issueID, bool peRequired, bool mrbRequired, int userID) { string subRoles = wfDMO.GetSubRoleItems(issueID, (int)GlobalVars.DocumentType.LotDisposition); // bubble the error int appoverCount = 0; DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); parameters.Add("@UserID", userID); parameters.Add("@PERequired", peRequired); parameters.Add("@MRBRequired", mrbRequired); parameters.Add("@DocumentTypeID", 1); // Lot Dispostion parameters.Add("@SubRoleCategoriesClause", subRoles); parameters.Add("@AppoverCount", appoverCount, dbType: DbType.Int32, direction: ParameterDirection.Output); db.Execute("SubmitForApproval_LotDisposition", parameters, commandType: CommandType.StoredProcedure); appoverCount = parameters.Get("@AppoverCount"); return appoverCount; } public List GetRejectionOrginatorEmailList(int issueID) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); List emailList = db.Query("GetRejectionOrginatorEmailList", parameters, commandType: CommandType.StoredProcedure).ToList(); return emailList; } public void UpdateReasonForDisposition(int issueID, string reasonForDisposition) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); parameters.Add("@ReasonForDisposition", reasonForDisposition); db.Execute("UpdateReasonForDisposition", parameters, commandType: CommandType.StoredProcedure); } public void InsertComments(int issueID, string comments, int commentedBy) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); parameters.Add("@Comments", comments); parameters.Add("@CommentedBy", commentedBy); db.Execute("InsertComments", parameters, commandType: CommandType.StoredProcedure); } internal void DeleteLotDisposition(int issueID) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); db.Execute("DeleteLotDisposition", parameters, commandType: CommandType.StoredProcedure); } internal IEnumerable GetComments(int issueID) { IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING); return db.Query("GetComments", new { @IssueID = issueID }, commandType: CommandType.StoredProcedure).ToList(); } public void ReleaseLockOnDocument(int userID, int issueID) { DynamicParameters parameters = new(); parameters.Add("@IssueID", issueID); parameters.Add("@UserID", userID); db.Execute("ReleaseLockOnLotDispoDoc", parameters, commandType: CommandType.StoredProcedure); } public string GetFileName(string attachmentID) { DynamicParameters parameters = new(); parameters.Add("@AttachmentID", attachmentID); var fileName = db.Query("GetFileName", parameters, commandType: CommandType.StoredProcedure).Single(); return fileName; } }