using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using Dapper;

using Fab2ApprovalSystem.Misc;
using Fab2ApprovalSystem.Models;

namespace Fab2ApprovalSystem.DMO;

public class WorkflowDMO {
    private readonly IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING);

    public string GetSubRoleItems(int issueID, int docType) {
        List<string> subRoleItems = new();
        StringBuilder sqlString = new();
        string resultInClause = "";

        // LOTDISPSITION
        if (GlobalVars.DocumentType.LotDisposition == (GlobalVars.DocumentType)docType) {
            resultInClause = GetLotDispositionSubRoleItems(issueID);
        }
        // MRB
        else if (GlobalVars.DocumentType.MRB == (GlobalVars.DocumentType)docType) {
            subRoleItems.Add("'MRBApprover'"); // TODO remove Hard coding
            resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        } else if (GlobalVars.DocumentType.MRB == (GlobalVars.DocumentType)docType) {
            subRoleItems.Add("'QA_Admin'"); // TODO remove Hard coding
            resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        } else if (GlobalVars.DocumentType.ECN == (GlobalVars.DocumentType)docType || GlobalVars.DocumentType.TECNCancelledExpired == (GlobalVars.DocumentType)docType) {
            // TODO remove Hard coding
            resultInClause = GetECNSubRoleItems(issueID, docType);
        } else if (GlobalVars.DocumentType.EECN == (GlobalVars.DocumentType)docType) {
            resultInClause = GetECNSubRoleItems(issueID, docType);
        } else if (GlobalVars.DocumentType.LotTraveler == (GlobalVars.DocumentType)docType) {
            resultInClause = GetLotTravelerSubRoleItems(issueID);
        } else if (GlobalVars.DocumentType.CorrectiveAction == (GlobalVars.DocumentType)docType) {
            resultInClause = GetCorrectiveActionSubRoleItems(issueID);
        } else if (GlobalVars.DocumentType.CorrectiveActionSection == (GlobalVars.DocumentType)docType) {
            resultInClause = GetCorrectiveActionSectionSubRoleItems(issueID);
        } else if (GlobalVars.DocumentType.PartsRequest == (GlobalVars.DocumentType)docType) {
            resultInClause = GetSubRolesForPartsRequestNextStep(issueID);
        }

        return resultInClause;
    }

    private string GetCorrectiveActionSubRoleItems(int caNo) {
        List<string> subRoleItems = new();
        StringBuilder sqlString = new();
        subRoleItems.Add("'Integration'");

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }

    private string GetCorrectiveActionSectionSubRoleItems(int caNo) {
        List<string> subRoleItems = new();
        StringBuilder sqlString = new();
        subRoleItems.Add("'8DQA'");

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }

    /// <summary>
    /// Get the Lot Disposition Document WorkFlow Sub Roles
    /// </summary>

    private string GetLotDispositionSubRoleItems(int issueID) {
        List<string> subRoleItems = new();
        int recordCount = 0;
        StringBuilder sqlString = new();

        // Check if "Disposition by OCAP" is checked
        sqlString.Append("SELECT COUNT(*) FROM LotDisposition WHERE (DispositionByOCAP = 1) AND IssueID = @IssueID ");
        recordCount = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single();

        if (recordCount > 0) {
            // if "Disposition by OCAP" is checked only the get roles for the "Execution" level only                 

            // For Step 3
            // Get the Location Role
            sqlString = new StringBuilder();
            sqlString.Append("SELECT Location FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE LD.IssueID = @IssueID ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
            string tempLocation = "";
            foreach (var dataRow in dataRows) {
                tempLocation = dataRow.Location.ToString();
                if (tempLocation == "QDB" || tempLocation == "EDB")
                    subRoleItems.Add("'" + tempLocation + "'");
                else if (tempLocation == "6300" || tempLocation == "6400" || tempLocation == "6600")
                    subRoleItems.Add("'Probe'");
                else
                    subRoleItems.Add("'Fab'");
            }

        } else {
            // NOT "Disposition by OCAP"

            // For Step 1
            // Get the "Issue"
            sqlString = new StringBuilder();
            sqlString.Append("SELECT Issue FROM LotDisposition LD INNER JOIN ResponsibilityIssue R ON LD.ResponsibilityIssueID = R.ResponsibilityIssueID WHERE LD.IssueID = @IssueID ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.Issue.ToString() + "'");
            }

            #region YE Role

            // ==================================================================YE ROLE===============================================================================================
            // For Step 1

            // check if there any IG Medical products
            sqlString = new StringBuilder();
            sqlString.Append("SELECT COUNT(*) FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE LD.IssueID = @IssueID AND (L.Description LIKE 'IG,%' AND L.Description LIKE '%MA,%')");
            int recordCountForIG_MA = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single();

            // Get list of the YE "Product-Generation"
            sqlString = new StringBuilder();
            sqlString.Append("SELECT DISTINCT SubRoleCategoryItem, LTRIM(RTRIM(L.ProductFamily)) + '-' + ISNULL(LTRIM(RTRIM(CONVERT(varchar(10), L.Gen))), 'NULL') AS LotFamilyGen, ");
            sqlString.Append("CASE WHEN L.LotStatusOptionID = 2 OR (ISNULL(S.ScrapCount, 0) > 0 AND ISNULL(S.ReleaseCount, 0) = 0) THEN 1 ELSE 0 END AS LotIsFullyScrapped, ");
            sqlString.Append("L.Location ");
            sqlString.Append("FROM Lot L ");
            sqlString.Append("LEFT OUTER JOIN vFamilyGenYESubRole ON FamilyGen = LTRIM(RTRIM(L.ProductFamily)) + @FamilySuffix + '-' + LTRIM(RTRIM(CONVERT(varchar(10), L.Gen))) ");
            sqlString.Append("LEFT OUTER JOIN ScrapLot S on S.LotNo = L.LotNumber AND S.IssueID = L.IssueID ");
            sqlString.Append("WHERE L.IssueID = @IssueID ");

            dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID, FamilySuffix = (recordCountForIG_MA > 0 ? "_MA" : "") }).ToList();
            foreach (var dataRow in dataRows) {
                if (dataRow.LotIsFullyScrapped == 1) {   // if lot is fully scrapped
                    int location = 0;
                    if (!int.TryParse(dataRow.Location, out location))
                        location = 9999;
                    if (location < 6300) // only add YE if location is >= 6300
                        continue;
                }
                if (dataRow.SubRoleCategoryItem == null)
                    throw new Exception("Missing YE for family/gen: " + dataRow.LotFamilyGen);

                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }

            #endregion

            #region PE Role

            // ==================================================================PE ROLE===============================================================================================
            // For Step 1

            // check if all wafers with in the lots for the Dispo are "SCRAP"                
            sqlString = new StringBuilder();
            sqlString.Append("SELECT COUNT(*) FROM [ScrapLot] S ");
            sqlString.Append("INNER JOIN Lot L ON S.LotNo = L.LotNumber ");
            sqlString.Append("WHERE L.IssueID = @IssueID AND (L.LotStatusOptionID <> 2 OR S.ReleaseCount > 0)");
            bool allLotsAreScrap = (db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single() == 0);

            // If PE Required OR MRB Required (Could be combined with the MA OR RH rule)
            // But not if all lots are scrap
            sqlString = new StringBuilder();
            sqlString.Append("SELECT COUNT(*) FROM LotDisposition LD INNER JOIN Lot L  ON LD.IssueID = L.IssueID ");
            sqlString.Append("WHERE (PERequired = 1 OR Location = 'QDB' OR Location = 'EDB' ) AND LD.IssueID = @IssueID ");
            recordCount = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single();
            if ((recordCount > 0) && (allLotsAreScrap == false)) {
                // check if there any IG Medical products
                if (recordCountForIG_MA > 0) {
                    sqlString = new StringBuilder();
                    sqlString.Append("SELECT 'PE_' + LTRIM(RTRIM(L.ProductFamily)) + '_MA-' + LTRIM(RTRIM(CONVERT(NCHAR(10),L.Gen))) AS ProdGen ");
                    sqlString.Append("FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE LD.IssueID = @IssueID ");
                    dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
                    foreach (var dataRow in dataRows) {
                        subRoleItems.Add("'" + dataRow.ProdGen.ToString() + "'");
                    }
                } else {
                    // Get list of the PE "Product-Generation"
                    sqlString = new StringBuilder();
                    sqlString.Append("SELECT 'PE_' + LTRIM(RTRIM(L.ProductFamily)) + '-' + LTRIM(RTRIM(CONVERT(NCHAR(10),L.Gen))) AS ProdGen ");
                    sqlString.Append("FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE LD.IssueID = @IssueID ");
                    dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
                    foreach (var dataRow in dataRows) {
                        subRoleItems.Add("'" + dataRow.ProdGen.ToString() + "'");
                    }
                }
            }

            #endregion

            // For Step 1 and Step 2
            // check to see if the Lot has any M_Suffix
            sqlString = new StringBuilder();
            sqlString.Append("SELECT COUNT(*) FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE (L.LotStatusOptionID = 4) AND LD.IssueID = @IssueID ");
            recordCount = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'M_Suffix'");
            }

            // get wafer scrap counts

            sqlString = new StringBuilder();
            sqlString.Append("SELECT ISNULL(SUM(ScrapCount), 0) AS ScrapCount FROM Lot L ");
            sqlString.Append("INNER JOIN ScrapLot S ON S.IssueID = L.IssueID AND S.LotNo = L.LotNumber ");
            sqlString.Append("WHERE L.IssueID = @IssueID AND L.Location < '6300'");
            int scrapCountLocationBelow6300 = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).SingleOrDefault();

            if (scrapCountLocationBelow6300 > 10) {
                // For Step 2
                if (!subRoleItems.Contains("'Engineering'"))
                    subRoleItems.Add("'Engineering'");
            }

            sqlString = new StringBuilder();
            sqlString.Append("SELECT ISNULL(SUM(ScrapCount), 0) AS ScrapCount FROM Lot L ");
            sqlString.Append("INNER JOIN ScrapLot S ON S.IssueID = L.IssueID AND S.LotNo = L.LotNumber ");
            sqlString.Append("WHERE L.IssueID = @IssueID AND L.Location >= '6300'");
            int scrapCountLocation6300AndGreater = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).SingleOrDefault();

            if (scrapCountLocation6300AndGreater > 10) {
                // For Step 2
                if (!subRoleItems.Contains("'Integration / YE'"))
                    subRoleItems.Add("'Integration / YE'");
            }

            /* ===== Remove the code for Director Approval Loop - Changed per Dixie's request 08/06/2018
             */

            sqlString = new StringBuilder();

            // 09/01/2020 - 
            // Per Hans, do not include directors if PE required is checked

            // 09/01/2020
            // Per Hans, do not include directors if PE required is checked
            sqlString.Append("SELECT COUNT(*) FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID ");
            sqlString.Append("WHERE (Location = 'QDB' OR Location = 'EDB' ) ");
            sqlString.Append("AND LD.IssueID = @IssueID ");

            recordCount = db.Query<int>(sqlString.ToString(), new { IssueID = issueID }).Single();
            if (recordCount > 0) {
                // For Step 2
                // get all the departments, except the 'Facilities' and 'Die bank' dept
                sqlString = new StringBuilder();
                sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'Department' AND SubRoleCategoryItem <> 'Facilities' AND SubRoleCategoryItem <> 'Die Bank' ");
                dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
                foreach (var dataRow in dataRows) {
                    subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
                }
            }

            // For Step 3
            // Get the Location Role
            sqlString = new StringBuilder();
            sqlString.Append("SELECT Location FROM LotDisposition LD INNER JOIN Lot L ON LD.IssueID = L.IssueID WHERE LD.IssueID = @IssueID ");
            dataRows = db.Query<dynamic>(sqlString.ToString(), new { IssueID = issueID }).ToList();
            string tempLocation = "";
            foreach (var dataRow in dataRows) {
                tempLocation = dataRow.Location.ToString();
                if (tempLocation.ToUpper() == "QDB" || tempLocation.ToUpper() == "EDB")
                    subRoleItems.Add("'" + tempLocation + "'");
                else if (tempLocation == "6300" || tempLocation == "6400" || tempLocation == "6600")
                    subRoleItems.Add("'Probe'");
                else
                    subRoleItems.Add("'Fab'");
            }
        }

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }

    private string GetECNSubRoleItems(int ecnNumber, int documentType) {
        StringBuilder qryString = new();
        List<string> subRoleItems = new();

        int recordCount = 0;
        // EMERGENCY TECNS ==============================================================================================           
        if (GlobalVars.DocumentType.EECN == (GlobalVars.DocumentType)documentType) {
            qryString.Clear();
            qryString.Append("SELECT DISTINCT SRC.SubRoleCategoryItem	FROM DocumentType D ");
            qryString.Append("INNER JOIN Workflows W ON D.DocumentTypeID = W.DocumentTypeID ");
            qryString.Append("INNER JOIN  WorkFlowSteps WS ON W.WorkflowID = WS.WorkflowID ");
            qryString.Append("INNER JOIN Role R ON WS.RoleID = R.RoleID ");
            qryString.Append("INNER JOIN SubRole SR ON R.RoleID = SR.RoleID ");
            qryString.Append("INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID ");
            qryString.Append("WHERE D.DocumentTypeID = @DocumentType");
            List<dynamic> dataRows = db.Query<dynamic>(qryString.ToString(), new { DocumentType = documentType }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        } else {
            subRoleItems.Add("'QA Pre Approver'");
            // get the affected Department: Removed by JRO per Jeanne
            qryString.Clear();
            qryString.Append("SELECT SubRoleCategoryItem FROM SubRoleCategory S INNER JOIN ECNAffectedDepartment AD ON S.SubRoleCategoryID = AD.DepartmentID WHERE AD.ECNNumber= @ECNNumber");
            List<dynamic> dataRows = db.Query<dynamic>(qryString.ToString(), new { ECNNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }

            // Add Directors if # of Lots Affected is > 10

            qryString.Clear();
            // Impact / Change Sub Roles =======================================================================================================================

            // Get the "Environment" Impact SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND ImpactOnEnvironment = 2 ");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'Environment'");
            }

            // Get the "Capacity" Impact SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (ImpactOnCapacity = 1 )");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'Capacity'");
            }

            // Get the "MaterialConsumptionChangeRequired" Impact SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (MaterialConsumptionChangeRequired = 1 )");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'MaterialConsumption'");
            }

            // Get the "NewPartFlow" Impact SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (NewPartFlowRequired = 1 )");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'NewPartFlow'");
                subRoleItems.Add("'SPN-Execution'");
                subRoleItems.Add("'Metrology Change'");
                subRoleItems.Add("'SPC'");
            }

            subRoleItems.Add("'Document Control Admin'");
            subRoleItems.Add("'Training Notification'");

            // Execution level========================================================================================================
            // Get the "SPN" SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (SPNChangeRequired = 1) ");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
            }

            // Get the "Metrology Change" SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (MetrologyChangeRequired = 1) ");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'Metrology Change'");
            }

            // Get the "SPCChange" SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (SPCChangeRequired = 1) ");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'SPC'");
            }

            // Get the "FIChange" SubRole
            qryString.Clear();
            qryString.Append("SELECT COUNT(*) FROM ECN WHERE ECNNumber= @ECNNumber AND (FIChangeRequired = 1) ");
            recordCount = db.Query<int>(qryString.ToString(), new { ECNNumber = ecnNumber }).Single();
            if (recordCount > 0) {
                subRoleItems.Add("'FI-Execution'");
            }
        }

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }

    private string GetLotTravelerSubRoleItems(int workRequestID) {
        List<string> subRoleItems = new();
        StringBuilder qryString = new();

        // get the affected Modules
        qryString.Clear();
        qryString.Append("SELECT ModuleName FROM Module M INNER JOIN LTAffectedModule AM ON M.ModuleID = AM.ModuleID WHERE AM.LTWorkRequestID = @WorkRequestID");
        List<dynamic> dataRows = db.Query<dynamic>(qryString.ToString(), new { WorkRequestID = workRequestID }).ToList();
        foreach (var dataRow in dataRows) {
            subRoleItems.Add("'" + dataRow.ModuleName.ToString() + "'");
        }

        // get the affected Department
        qryString.Clear();
        qryString.Append("SELECT SubRoleCategoryItem FROM SubRoleCategory S INNER JOIN LTAffectedDepartment AD ON S.SubRoleCategoryID = AD.DepartmentID WHERE AD.LTWorkRequestID= @WorkRequestID");
        dataRows = db.Query<dynamic>(qryString.ToString(), new { WorkRequestID = workRequestID }).ToList();
        foreach (var dataRow in dataRows) {
            subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
        }

        qryString.Clear();
        qryString.Append("SELECT * FROM LTWorkRequest WHERE ID = @WorkRequestID");
        dataRows = db.Query<dynamic>(qryString.ToString(), new { WorkRequestID = workRequestID }).ToList();
        var engLotStartReq = -1;
        var lotTypeConversion = -1;
        foreach (dynamic dataRow in dataRows) {
            engLotStartReq = dataRow.EngLotStartRequired;
            lotTypeConversion = dataRow.LotTypeConversion;
        }

        if (engLotStartReq == 1) {
            if (!subRoleItems.Contains("Operations")) // need to remove the hard coding
            {
                subRoleItems.Add("'Operations'");
            }
            if (!subRoleItems.Contains("Engineering")) // need to remove the hard coding
            {
                subRoleItems.Add("'Engineering'");
            }

            if (!subRoleItems.Contains("Integration / YE")) // need to remove the hard coding
            {
                subRoleItems.Add("'Integration / YE'");
            }

        }
        if (lotTypeConversion == 1) {
            if (!subRoleItems.Contains("Operations")) // need to remove the hard coding
            {
                subRoleItems.Add("'Operations'");
            }
        }

        if (engLotStartReq == 1 || lotTypeConversion == 1) {
            // END OF DEpartments= ===========================================================
            qryString.Clear();

            qryString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'FabPlanner' ");
            dataRows = db.Query<dynamic>(qryString.ToString(), new { WorkRequestID = workRequestID }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        }

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }
    /// <summary>
    /// Returns a boolean to indicate whethere it is was the last step in the approval 
    /// </summary>

    public bool Approve(AppSettings appSettings, int issueID, byte step, string comments, out bool lastStep, int userID, int documentType, int workFlowNumber) {
        bool lastApproverInCurrentStep = false;
        try {
            string subRolesInClause = GetSubRoleItems(issueID, documentType);

            lastStep = false;

            DynamicParameters parameters = new();
            parameters.Add("@IssueID", issueID);
            parameters.Add("@CurrentStep", step);
            parameters.Add("@ItemStatus", (int)GlobalVars.ApprovalOption.Approved);
            parameters.Add("@UserID", userID);
            parameters.Add("@Comments", comments);
            parameters.Add("@SubRoleCategoriesClause", subRolesInClause);
            parameters.Add("@DocumentTypeID", documentType);
            parameters.Add("@LastStep", lastStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);
            parameters.Add("@LastApproverInCurrentStep", lastApproverInCurrentStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);
            parameters.Add("@WorkFlowNumber", workFlowNumber);

            db.Execute("UpdateApproval", parameters, commandType: CommandType.StoredProcedure);

            lastStep = parameters.Get<bool>("@LastStep");
            lastApproverInCurrentStep = parameters.Get<bool>("@LastApproverInCurrentStep");
        } catch (Exception e) {
            string detailedException = "";
            try {
                detailedException = e.InnerException.ToString();
            } catch {
                detailedException = e.Message;
            }
            string exceptionString = e.Message.ToString().Trim().Length > 500 ? "Issue=" + issueID.ToString() + " Step:" + step + " " + e.Message.ToString().Substring(0, 250) : e.Message.ToString();
            Functions.WriteEvent(appSettings, "User ID:" + userID + "\r\n " + "ApproveDMO\r\n" + issueID.ToString() + "\r\n" + e.InnerException, System.Diagnostics.EventLogEntryType.Error);
            EventLogDMO.Add(new WinEventLog() { IssueID = issueID, UserID = userID.ToString(), DocumentType = "Lot Disposition", OperationType = "Error", Comments = "ApproveDMO - " + exceptionString });
            throw new Exception(e.Message);
        }

        return lastApproverInCurrentStep;
    }

    public bool Reject(int issueID, byte step, string comments, int userID, int docType) {
        bool lastStep = false;
        bool lastApproverInCurrentStep = false;

        DynamicParameters parameters = new();
        parameters.Add("@IssueID", issueID);
        parameters.Add("@CurrentStep", step);
        parameters.Add("@ItemStatus", (int)GlobalVars.ApprovalOption.Denied);
        parameters.Add("@UserID", userID);
        parameters.Add("@Comments", comments);
        parameters.Add("@DocumentTypeID", docType);
        parameters.Add("@SubRoleCategoriesClause", string.Empty);
        parameters.Add("@LastStep", lastStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);
        parameters.Add("@LastApproverInCurrentStep", lastApproverInCurrentStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);

        db.Execute("UpdateApproval", parameters, commandType: CommandType.StoredProcedure);

        lastStep = parameters.Get<bool>("@LastStep");
        lastApproverInCurrentStep = parameters.Get<bool>("@LastApproverInCurrentStep");

        return lastApproverInCurrentStep;
    }

    public bool Recall(int issueID, byte step, string comments, int userID, int docType) {
        bool lastStep = false;
        bool lastApproverInCurrentStep = false;

        DynamicParameters parameters = new();
        parameters.Add("@IssueID", issueID);
        parameters.Add("@CurrentStep", step);
        parameters.Add("@ItemStatus", (int)GlobalVars.ApprovalOption.Recalled);
        parameters.Add("@UserID", userID);
        parameters.Add("@Comments", comments);
        parameters.Add("@DocumentTypeID", docType);
        parameters.Add("@SubRoleCategoriesClause", string.Empty);
        parameters.Add("@LastStep", lastStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);
        parameters.Add("@LastApproverInCurrentStep", lastApproverInCurrentStep, dbType: DbType.Boolean, direction: ParameterDirection.Output);

        db.Execute("UpdateApproval", parameters, commandType: CommandType.StoredProcedure);

        lastStep = parameters.Get<bool>("@LastStep");
        lastApproverInCurrentStep = parameters.Get<bool>("@LastApproverInCurrentStep");

        return lastApproverInCurrentStep;
    }

    public void RejectTECNExtension(int ecnNumber, byte step, string comments, int userID, int docType) {
        DynamicParameters parameters = new();
        parameters.Add("@ECNNumber", ecnNumber);
        parameters.Add("@CurrentStep", step);
        parameters.Add("@UserID", userID);
        parameters.Add("@DocumentTypeID", docType);

        db.Execute("ECN_TECNExtensionRejection", parameters, commandType: CommandType.StoredProcedure);
    }

    public string ReAssignApproval(int issueID, int assignedFromUser, int assignedToUser, byte step, int docType) {
        string email = "";
        string emailIDArray = "";

        DynamicParameters parameters = new();
        email = "";
        parameters = new DynamicParameters();
        parameters.Add("@IssueID", issueID);
        parameters.Add("@Step", step);
        parameters.Add("@AssignedFromUser", assignedFromUser);
        parameters.Add("@AssignedToUser", assignedToUser);
        parameters.Add("@DocumentTypeID", docType);
        parameters.Add("@Email", email, DbType.String, direction: ParameterDirection.InputOutput);

        db.Execute("ReAssignApproval", parameters, commandType: CommandType.StoredProcedure);
        emailIDArray += parameters.Get<string>("@Email");

        return emailIDArray;
    }

    public string DelegateDocumentApproval(int issueID, int delegateFromUser, int delegateToUser) {
        string email = "";
        string emailID = "";

        DynamicParameters parameters = new();
        email = "";
        parameters = new DynamicParameters();
        parameters.Add("@IssueID", issueID);
        parameters.Add("@DelegateFromUser", delegateFromUser);
        parameters.Add("@DelegateToUser", delegateToUser);
        parameters.Add("@Email", email, DbType.String, direction: ParameterDirection.InputOutput);

        db.Execute("DelegateApproval", parameters, commandType: CommandType.StoredProcedure);
        emailID = parameters.Get<string>("@Email");

        return emailID;
    }

    public string AddAdditionalApproval(int issueID, string userIDs, byte step, int documentType) {
        string email = "";
        string emailIDArray = "";

        string[] arrayOfUsers = userIDs.Split(new char[] { '~' });

        DynamicParameters parameters = new();
        for (int i = 0; i < arrayOfUsers.Length; i++) {
            email = "";
            parameters = new DynamicParameters();
            parameters.Add("@IssueID", issueID);
            parameters.Add("@Step", step);
            parameters.Add("@UserID", arrayOfUsers[i]);
            parameters.Add("@DocumentTypeID", documentType);
            parameters.Add("@Email", email, DbType.String, direction: ParameterDirection.InputOutput);

            db.Execute("InsertdAdditionalApproval", parameters, commandType: CommandType.StoredProcedure);
            emailIDArray = emailIDArray + parameters.Get<string>("@Email") + "~";
        }

        return emailIDArray;
    }

    public string AddEECNApproval(int ecnNumber, byte step, int documentType, string engUserIDs, string opUserIDs) {
        string email = "";
        string emailIDArray = "";

        string[] arrayOfUsers = engUserIDs.Split(new char[] { '~' });

        // Engineering SubRole
        DynamicParameters parameters = new();
        for (int i = 0; i < arrayOfUsers.Length; i++) {
            email = "";
            parameters = new DynamicParameters();
            parameters.Add("@ECNNumber", ecnNumber);
            parameters.Add("@Step", step);
            parameters.Add("@UserID", arrayOfUsers[i]);
            parameters.Add("@DocumentTypeID", documentType);
            parameters.Add("@SubRoleCategoryName", "Engineering");
            parameters.Add("@Email", email, DbType.String, direction: ParameterDirection.InputOutput);

            db.Execute("ECNInsertEmergencyECNApprovers", parameters, commandType: CommandType.StoredProcedure);
            emailIDArray = emailIDArray + parameters.Get<string>("@Email") + "~";
        }

        // Operations SubRole
        arrayOfUsers = opUserIDs.Split(new char[] { '~' });
        parameters = new DynamicParameters();
        for (int i = 0; i < arrayOfUsers.Length; i++) {
            email = "";
            parameters = new DynamicParameters();
            parameters.Add("@ECNNumber", ecnNumber);
            parameters.Add("@Step", step);
            parameters.Add("@UserID", arrayOfUsers[i]);
            parameters.Add("@DocumentTypeID", documentType);
            parameters.Add("@SubRoleCategoryName", "Operations");
            parameters.Add("@Email", email, DbType.String, direction: ParameterDirection.InputOutput);

            db.Execute("ECNInsertEmergencyECNApprovers", parameters, commandType: CommandType.StoredProcedure);
            emailIDArray = emailIDArray + parameters.Get<string>("@Email") + "~";
        }

        return emailIDArray;
    }

    public string GetApproversForCancelled_ExpiredTECNDocs(int ecnNumber) {
        List<string> subRoleItems = new();
        StringBuilder sqlString = new();
        sqlString.Append(
            "SELECT TestProgramChangeRequired, SPCChangeRequired, NewPartFlowRequired, SPNChangeRequired, FIChangeRequired FROM ECN WHERE ECNNumber = @ecNumber ");

        var data = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).Single();

        if (data.TestProgramChangeRequired == 1) {
            sqlString.Clear();
            sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'TestProgram' ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        }
        if (data.SPCChangeRequired == 1) {
            sqlString.Clear();
            sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'SPC' ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }

        }
        if (data.NewPartFlowRequired == 1) {
            sqlString.Clear();
            sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'NewPartFlow' ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        }
        if (data.SPNChangeRequired == 1) {
            sqlString.Clear();
            sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'SPN-Execution' ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        }
        if (data.FIChangeRequired == 1) {
            sqlString.Clear();
            sqlString.Append("SELECT SubRoleCategoryItem FROM [SubRoleCategory] WHERE SubRoleCategory = 'FI-Execution' ");
            List<dynamic> dataRows = db.Query<dynamic>(sqlString.ToString(), new { ecNumber = ecnNumber }).ToList();
            foreach (var dataRow in dataRows) {
                subRoleItems.Add("'" + dataRow.SubRoleCategoryItem.ToString() + "'");
            }
        }

        string resultInClause = "(" + string.Join(", ", subRoleItems.Distinct().ToArray()) + ")";
        return resultInClause;
    }

    public string GetSubRolesForPartsRequestNextStep(int prNumber) {
        string subRoles = db.Query<string>("SELECT dbo.fnPartsRequestGetSubRolesForNextStep(@PRNumber)",
                new { PRNumber = prNumber },
                commandType: CommandType.Text).First();
        subRoles = "('" + subRoles + "')";
        return subRoles;
    }

    public WorkflowSteps GetWorkflowStep(int docTypeID, int wfNumber, int stepNumber) {
        return db.Query<WorkflowSteps>(
            "SELECT s.* FROM WorkflowSteps s INNER JOIN Workflows w ON w.WorkflowID = s.WorkflowID " +
            "WHERE w.DocumentTypeID = @docTypeID AND w.WorkFlowNumber = @wfNumber AND s.WorkflowStepNumber = @stepNumber",
            param: new { docTypeID = docTypeID, wfNumber = wfNumber, stepNumber = stepNumber },
            commandType: CommandType.Text).FirstOrDefault();
    }

}