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

using Dapper;

using Fab2ApprovalSystem.Misc;
using Fab2ApprovalSystem.Models;
using Fab2ApprovalSystem.ViewModels;

namespace Fab2ApprovalSystem.DMO;

public class LotTravelerDMO {

    private readonly IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING);
    private readonly WorkflowDMO wfDMO = new();

    /// <returns></returns>
    /// 
    internal LTWorkRequest InsertWorkRequest(LTWorkRequest workRequest) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", value: workRequest.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@OriginatorID", workRequest.OriginatorID);

        db.Execute("LTInsertWorkRequest", parameters, commandType: CommandType.StoredProcedure);
        workRequest.ID = parameters.Get<int>("@ID");

        return workRequest;
    }

    internal LTWorkRequest GetLTWorkRequestItem(int workRequestID, out int isITAR, int userID) {
        isITAR = 2;
        LTWorkRequest workRequestItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", value: workRequestID);
        parameters.Add("@UserID", userID);
        parameters.Add("@IsITAR", value: isITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("LTGetWorkRequestItem", parameters, commandType: CommandType.StoredProcedure)) {
            workRequestItem = multipleResultItems.Read<LTWorkRequest>().SingleOrDefault();

            List<int> departments = multipleResultItems.Read<int>().ToList();
            if (workRequestItem != null && departments != null) {
                workRequestItem.DepartmentIDs.AddRange(departments);
            }

            List<int> modules = multipleResultItems.Read<int>().ToList();
            if (workRequestItem != null && modules != null) {
                if (modules.Count > 0)
                    workRequestItem.ModuleIDs.AddRange(modules);
            }

            isITAR = parameters.Get<int>("@IsITAR");

        }

        return workRequestItem;
    }

    internal WorkRequestPdf GetLTWorkRequestItemPDF(int workRequestID) {
        WorkRequestPdf workRequestItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", value: workRequestID);

        using (var multipleResultItems = db.QueryMultiple("LTGetWorkRequestItemPDF", parameters, commandType: CommandType.StoredProcedure)) {
            workRequestItem = multipleResultItems.Read<WorkRequestPdf>().SingleOrDefault();

            List<string> departments = multipleResultItems.Read<string>().ToList();
            workRequestItem.AffectedDepartments = string.Join(", ", departments);

            List<string> modules = multipleResultItems.Read<string>().ToList();
            workRequestItem.AffectedModules = string.Join(", ", modules);

            List<LTMaterial> materials = multipleResultItems.Read<LTMaterial>().ToList();
            if (workRequestItem != null && materials != null) {
                if (materials.Count > 0)
                    workRequestItem.LTMaterial.AddRange(materials);
            }

            List<LTHoldStep> holdsteps = multipleResultItems.Read<LTHoldStep>().ToList();
            if (workRequestItem != null && holdsteps != null) {
                if (holdsteps.Count > 0)
                    workRequestItem.LTHoldStep.AddRange(holdsteps);
            }
        }

        return workRequestItem;
    }

    internal LTWorkRequest GetLTWorkRequestItemForRead(int workRequestID, out int isITAR, int userID) {
        isITAR = 2;
        LTWorkRequest workRequestItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", value: workRequestID);
        parameters.Add("@UserID", userID);
        parameters.Add("@IsITAR", value: isITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("LTGetWorkRequestItemForRead", parameters, commandType: CommandType.StoredProcedure)) {
            workRequestItem = multipleResultItems.Read<LTWorkRequest>().SingleOrDefault();

            List<int> departments = multipleResultItems.Read<int>().ToList();
            if (workRequestItem != null && departments != null) {
                workRequestItem.DepartmentIDs.AddRange(departments);
            }

            List<int> modules = multipleResultItems.Read<int>().ToList();
            if (workRequestItem != null && modules != null) {
                if (modules.Count > 0)
                    workRequestItem.ModuleIDs.AddRange(modules);
            }

            isITAR = parameters.Get<int>("@IsITAR");

        }

        return workRequestItem;
    }

    internal int UpdateWorkRequest(LTWorkRequest data, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        using (TransactionScope transanction = new()) {
            try {
                parameters.Add("@LTWorkRequestID", data.ID);
                parameters.Add("@Title", data.Title);
                parameters.Add("@LotType", data.LotType);
                parameters.Add("@EngLotStartRequired", data.EngLotStartRequired);
                parameters.Add("@LotTypeConversion", data.LotTypeConversion);
                parameters.Add("@QualLot", data.QualLot);
                parameters.Add("@PurposeOfRequest", data.PurposeOfRequest);
                parameters.Add("@PlannedLotStartDate", data.PlannedLotStartDate);
                parameters.Add("@PlannedScheduledCloseDate", data.PlannedScheduledCloseDate);
                parameters.Add("@ChargeDepartment", data.ChargeDepartment);
                parameters.Add("@AllocationToUse", data.AllocationToUse);
                parameters.Add("@PredictedCyleTime", data.PredictedCyleTime);
                parameters.Add("@DeptChargedForRawWafers", data.ChargeDepartment);
                parameters.Add("@TotalQty", data.TotalQty);
                parameters.Add("@WIPArea", data.WIPArea);
                parameters.Add("@LotStartDate", data.LotStartDate);
                parameters.Add("@LotNumber", data.LotNumber);
                parameters.Add("@WIPPartNumber", data.WIPPartNumber);
                parameters.Add("@PartDescription", data.PartDescription);
                parameters.Add("@DefaultEPISiliconPartNumber", data.DefaultEPISiliconPartNumber);
                parameters.Add("@RecordSiliconLotInSPNTapeTagTotrav", data.RecordSiliconLotInSPNTapeTagTotrav);
                parameters.Add("@SiliconLotNoCassette1", data.SiliconLotNoCassette1);
                parameters.Add("@ProcessFlow", data.ProcessFlow);
                parameters.Add("@EmployeeID", data.EmployeeID);
                parameters.Add("@VerbComment", data.VerbComment);
                parameters.Add("@SiliconOnHand", data.SiliconOnHand);
                parameters.Add("@SiliconExpectedDueDate", data.SiliconExpectedDueDate);
                parameters.Add("@SiliconComments", data.SiliconComments);
                parameters.Add("@ReticleOnHand", data.ReticleOnHand);
                parameters.Add("@ReticleExpectedDueDate", data.ReticleExpectedDueDate);
                parameters.Add("@ReticleComments", data.ReticleComments);
                parameters.Add("@SPNTransferOnHand", data.SPNTransferOnHand);
                parameters.Add("@SPNTransferExpectedDueDate", data.SPNTransferExpectedDueDate);
                parameters.Add("@SPNTransferComments", data.SPNTransferComments);
                parameters.Add("@ProbeCardOnHand", data.ProbeCardOnHand);
                parameters.Add("@ProbeCardExpectedDueDate", data.ProbeCardExpectedDueDate);
                parameters.Add("@ProbeCardComments", data.ProbeCardComments);
                parameters.Add("@ProbeRecipeOnHand", data.ProbeRecipeOnHand);
                parameters.Add("@ProbeRecipeExpectedDueDate", data.ProbeRecipeExpectedDueDate);
                parameters.Add("@ProbeRecipeComments", data.ProbeRecipeComments);
                parameters.Add("@ProcessChangeDetailsOnHand", data.ProcessChangeDetailsOnHand);
                parameters.Add("@ProcessChangeDetailsDueDate", data.ProcessChangeDetailsDueDate);
                parameters.Add("@LotStartCheckListComments", data.LotStartCheckListComments);
                parameters.Add("@PCRBNumber", data.PCRBNumber);
                parameters.Add("@LastUpdateBy", userID);
                parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);
                db.Execute("LTUpdateWorkRequest", parameters, commandType: CommandType.StoredProcedure);

                result = parameters.Get<int>("@Result");

                if (result == -1) {
                    throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
                }

                parameters = new DynamicParameters();
                parameters.Add("@WorkRequestID", data.ID);
                db.Execute("LTDeleteAffectedDepartments", parameters, commandType: CommandType.StoredProcedure);
                List<int> wrDepIDs = data.DepartmentIDs;
                if (wrDepIDs != null) {
                    foreach (int depId in wrDepIDs) {
                        parameters = new DynamicParameters();
                        parameters.Add("@WorkRequestID", data.ID);
                        parameters.Add("@DepartmentID", depId);
                        db.Execute("LTInsertAffectedDepartments", parameters, commandType: CommandType.StoredProcedure);
                    }
                }

                parameters = new DynamicParameters();
                parameters.Add("@WorkRequestID", data.ID);
                db.Execute("LTDeleteAffectedModules", parameters, commandType: CommandType.StoredProcedure);

                List<int> wrModuleIDs = data.ModuleIDs;
                if (wrModuleIDs != null) {
                    foreach (int moduleID in wrModuleIDs) {
                        parameters = new DynamicParameters();
                        parameters.Add("@WorkRequestID", data.ID);
                        parameters.Add("@ModuleID", moduleID);
                        db.Execute("LTInsertAffectedModules", parameters, commandType: CommandType.StoredProcedure);
                    }
                }
                transanction.Complete();
            } catch (Exception ex) {
                transanction.Dispose();
                throw new Exception(ex.Message + " " + ex.InnerException);
            }

        }

        // have to do it outside of the Transaction scope because the OPENQUERY to TEMIRWAP019 LINKED server does not work 
        parameters = new DynamicParameters();
        parameters.Add("@WorkRequestID", data.ID);
        db.Execute("LTUpdateIsITAR", parameters, commandType: CommandType.StoredProcedure);

        return result;
    }

    internal LTWorkRequestAttachment GetWorkRequestAttachDetail(int wrAttachmentID) {
        var wrAttach = db.Query<LTWorkRequestAttachment>("SELECT ID, FileName ,DocType, Comments FROM LTWorkRequestAttachment WHERE ID = @wrAttachmentID ", new { wrAttachmentID = wrAttachmentID }, commandType: CommandType.Text).Single();
        return wrAttach;
    }

    internal IEnumerable<LTMaterialViewModel> GetMaterialDetails(int workRequestID) {
        DynamicParameters parameters = new();
        parameters.Add("@workRequestID", value: workRequestID);

        List<LTMaterialViewModel> data = db.Query<LTMaterialViewModel>("LTGetMaterialDetails", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    /// <summary>
    /// Update via the edit screen
    /// </summary>

    internal void UpdateMaterialDetail(LTMaterialViewModel model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", model.ID);
        parameters.Add("@EPISiliconPartNumber", model.@EPISiliconPartNumber);
        parameters.Add("@Quantity", model.Quantity);
        parameters.Add("@Source", model.Source);
        parameters.Add("@Supplier", model.Supplier);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

        db.Execute("LTUpdateMaterialDetail", parameters, commandType: CommandType.StoredProcedure);
        result = parameters.Get<int>("@Result");
        if (result == -1) {
            throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
        }
    }

    /// <summary>
    /// Update via the revision screen
    /// </summary>

    internal void UpdateMaterialDetail(LTMaterial model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", model.ID);
        parameters.Add("@EPISiliconPartNumber", model.@EPISiliconPartNumber);
        parameters.Add("@Quantity", model.Quantity);
        parameters.Add("@Source", model.Source);
        parameters.Add("@Supplier", model.Supplier);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        result = parameters.Get<int>("@Result");
        if (result == -1) {
            throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
        }
    }

    internal void UpdateMaterialDetailRevision(LTMaterial model, int previousMaterialID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", model.ID);
        parameters.Add("@EPISiliconPartNumber", model.EPISiliconPartNumber);
        parameters.Add("@Quantity", model.Quantity);
        parameters.Add("@Source", model.Source);
        parameters.Add("@Supplier", model.Supplier);
        parameters.Add("@PreviousMaterialID", previousMaterialID);

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

    internal void InsertMaterialDetail(LTMaterialViewModel model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", model.ID, direction: ParameterDirection.InputOutput);
        parameters.Add("@EPISiliconPartNumber", model.@EPISiliconPartNumber);
        parameters.Add("@Quantity", model.Quantity);
        parameters.Add("@Source", model.Source);
        parameters.Add("@Supplier", model.Supplier);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        int id = parameters.Get<int>("@LTMaterialID");
        model.ID = id;

        result = parameters.Get<int>("@Result");
        if (result == -1) {
            throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
        }
    }

    /// <summary>
    /// For Revisioing the data
    /// </summary>

    internal void InsertMaterialDetail(LTMaterial model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", model.ID, direction: ParameterDirection.InputOutput);
        parameters.Add("@EPISiliconPartNumber", model.@EPISiliconPartNumber);
        parameters.Add("@Quantity", model.Quantity);
        parameters.Add("@Source", model.Source);
        parameters.Add("@Supplier", model.Supplier);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        int id = parameters.Get<int>("@LTMaterialID");
        model.ID = id;

        result = parameters.Get<int>("@Result");
        if (result == -1) {
            throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
        }
    }

    internal void DeleteMaterialDetail(int LTMaterialID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTMaterialID", LTMaterialID);
        db.Execute("LTDeleteMaterial", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteMaterialDetailRevision(int materialID) {
        DynamicParameters parameters = new();
        parameters.Add("@MaterialID", materialID);
        db.Execute("LTDeleteMaterialRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal List<BaseFlowLocation> GetBaseFlowLocations(string processFlow) {
        List<BaseFlowLocation> baseFlowLocations = db.Query<BaseFlowLocation>("SELECT DISTINCT LOC as Location FROM vSPNPDB_FLOWLOCS WHERE PROCESS = @ProcessFlow ORDER BY LOC ", new { ProcessFlow = processFlow }, commandType: CommandType.Text).ToList();
        return baseFlowLocations;
    }

    internal LTMaterial GetMaterialRecord(int materialID) {
        var material = db.Query<LTMaterial>("SELECT * FROM LTMaterial WHERE ID = @materialID ", new { materialID }, commandType: CommandType.Text).Single();
        return material;
    }

    internal List<BaseFlowOperations> GetBaseFlowOperations(string processFlow, string loc) {
        List<BaseFlowOperations> baseFlowOperations = db.Query<BaseFlowOperations>(
            "SELECT DISTINCT " +
            "  LTRIM(RTRIM(OPER)) + '-' + LTRIM(RTRIM(SEQ))  as Operation, " +
            "  LTRIM(RTRIM(OPER)) as OperCode, " +
            "  LTRIM(RTRIM(SEQ)) as SeqCode, " +
            "  LTRIM(RTRIM([OPDESC])) + ' (' + LTRIM(RTRIM([AREA])) + ')'  AS 'OperationDesc' " +
            "FROM vSPNPDB_FLOWLOCS " +
            "WHERE PROCESS = @ProcessFlow AND LOC = @LOC " +
            "ORDER BY Operation ",
            new { ProcessFlow = processFlow, LOC = loc }, commandType: CommandType.Text).ToList();
        return baseFlowOperations;
    }

    internal List<PartNumberAttrib> GetPartNumbers() {
        List<PartNumberAttrib> baseFlowOperations = db.Query<PartNumberAttrib>("SELECT DISTINCT  TOP 100 [MP_PART_NUMBER] AS PartNumber,  MP_DESCRIPTION +  ' ~ ' + MP_PART_NUMBER AS SiliconAndDesc FROM vFAB2SPN_MP_RECORD ORDER BY MP_PART_NUMBER ", commandType: CommandType.Text).ToList();
        return baseFlowOperations;
    }

    internal IEnumerable<LTHoldStep> GetHoldSteps(int workRequestID) {
        DynamicParameters parameters = new();
        parameters.Add("@workRequestID", value: workRequestID);

        List<LTHoldStep> data = db.Query<LTHoldStep>("LTGetHoldSteps", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal void InsertHoldStep(LTHoldStep model) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", model.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@BaseFlow", model.BaseFlow);
        parameters.Add("@BaseFlowLocation", model.Location);
        parameters.Add("@BaseFlowOperationSeq", model.OperSequence);
        parameters.Add("@BaseFlowOperation", model.Operation);
        parameters.Add("@OperationDescription", model.OperationDescription);
        parameters.Add("@ChangeInstruction", model.ChangeInstructions);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", model.UpdatedBy);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);
        db.Execute("LTInsertHoldStep", parameters, commandType: CommandType.StoredProcedure);

        int id = parameters.Get<int>("@LTHoldStepID");
        model.ID = id;

        result = parameters.Get<int>("@Result");

    }

    internal void UpdateHoldStep(LTHoldStep model) {
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", model.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@BaseFlow", model.BaseFlow);
        parameters.Add("@BaseFlowOperationSeq", model.OperSequence);
        parameters.Add("@BaseFlowLocation", model.Location);
        parameters.Add("@BaseFlowOperation", model.Operation);
        parameters.Add("@OperationDescription", model.OperationDescription);
        parameters.Add("@ChangeInstruction", model.ChangeInstructions);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", model.UpdatedBy);

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

    }

    internal void InsertHoldStepRevision(LTHoldStep model) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", model.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@BaseFlow", model.BaseFlow);
        parameters.Add("@BaseFlowLocation", model.Location);
        parameters.Add("@BaseFlowOperationSeq", model.OperSequence);
        parameters.Add("@BaseFlowOperation", model.Operation);
        parameters.Add("@OperationDescription", model.OperationDescription);
        parameters.Add("@ChangeInstruction", model.ChangeInstructions);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", model.UpdatedBy);

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

        int id = parameters.Get<int>("@LTHoldStepID");
        model.ID = id;
    }

    internal void UpdateHoldStepRevision(LTHoldStep model) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", model.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@BaseFlowLocation", model.Location);
        parameters.Add("@BaseFlowOperationSeq", model.OperSequence);
        parameters.Add("@BaseFlowOperation", model.Operation);
        parameters.Add("@OperationDescription", model.OperationDescription);
        parameters.Add("@ChangeInstruction", model.ChangeInstructions);
        parameters.Add("@LTWorkRequestID", model.LTWorkRequestID);
        parameters.Add("@UpdatedBy", model.UpdatedBy);

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

    internal void DeleteHoldStep(int holdStepID, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", holdStepID);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        result = parameters.Get<int>("@Result");
        if (result == -1) {
            throw new Exception("In order to do the UPDATE or SUBMIT operation the record needs to exclusively locked by you.\nThe record was unlocked by the system due to inactivity for more than 30 minutes, hence the update was not successful");
        }
    }

    internal void DeleteHoldStepRevision(int holdStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", holdStepID);
        db.Execute("LTDeleteHoldStepRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteLotTravHoldStep(int lotTravHoldStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", lotTravHoldStepID);
        db.Execute("LTDeleteLotTravHoldStep", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteLotTravHoldStepRevision(int lotTravHoldStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", lotTravHoldStepID);
        db.Execute("LTDeleteLotTravHoldStepRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<LTWorkRequestAttachment> GetHoldStepAttachemnts(int holdStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", holdStepID);
        List<LTWorkRequestAttachment> data = db.Query<LTWorkRequestAttachment>("LTGetHoldStepAttachments", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal IEnumerable<LTWorkRequestAttachment> GetLotTravHoldStepAttachemnts(int ltHoldStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", ltHoldStepID);
        List<LTWorkRequestAttachment> data = db.Query<LTWorkRequestAttachment>("LTGetLotTravHoldStepAttachments", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal LTHoldStep GetHoldStep(int holdStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", value: holdStepID);

        var data = db.Query<LTHoldStep>("LTGetHoldStep", parameters, commandType: CommandType.StoredProcedure).SingleOrDefault();
        return data;
    }

    internal LTLotTravelerHoldSteps GetLotTravHoldStep(int ltHoldStepID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", value: ltHoldStepID);

        var data = db.Query<LTLotTravelerHoldSteps>("LTGetLotTravHoldStep", parameters, commandType: CommandType.StoredProcedure).SingleOrDefault();
        return data;
    }

    internal void InsertLotHoldStepAttachment(LTWorkRequestAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", attachment.LTHoldStepID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@Comments", attachment.Comments);
        db.Execute("LTInsertHoldStepAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertLotHoldStepAttachmentRevision(LTWorkRequestAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@HoldStepID", attachment.LTHoldStepID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@Comments", attachment.Comments);
        db.Execute("LTInsertHoldStepAttachmentRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertWorkRequestAttachment(LTWorkRequestAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", attachment.WorkRequestID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@Comments", attachment.Comments);
        db.Execute("LTInsertWorkRequestAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertWorkRequestAttachmentRevision(LTWorkRequestAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", attachment.WorkRequestID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@Comments", attachment.Comments);
        db.Execute("LTInsertWorkRequestAttachmentRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal int GetRevisedWrkReqIDFromHoldStepID(int prevHoldStepID) {
        int workRequestID = db.Query<int>("SELECT LTWorkRequestID FROM LTHoldStep WHERE PreviousHoldStepID = @PrevHoldStepID", new { PrevHoldStepID = prevHoldStepID }, commandType: CommandType.Text).Single();

        return workRequestID;
    }

    internal int GetRevisedWrkReqIDFromPreviousWrkReqID(int prevWorkRequestID) {
        int workRequestID = db.Query<int>("SELECT ID FROM LTWorkRequest WHERE PreviousWorkRequestID = @prevWorkRequestID", new { prevWorkRequestID = prevWorkRequestID }, commandType: CommandType.Text).Single();

        return workRequestID;
    }

    internal IEnumerable<LTWorkRequestAttachment> GetWorkRequestAttachments(int workRequestID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        List<LTWorkRequestAttachment> data = db.Query<LTWorkRequestAttachment>("LTGetWorkRequestAttachments", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal void UpdateWorkRequestAttachment(LTWorkRequestAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestAttachmentID", attachment.ID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@Comments", attachment.Comments);

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

    internal void UpdateWorkRequestAttachmentRevision(LTWorkRequestAttachment attachment, int previousWorkRequestAttachmentID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestAttachmentID", attachment.ID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@Comments", attachment.Comments);
        parameters.Add("@PreviousWorkRequestAttachmentID", previousWorkRequestAttachmentID);

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

    internal void DeleteWorkRequestAttachment(int workRequestAttachmentID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestAttachmentID", workRequestAttachmentID);
        db.Execute("LTDeleteWorkRequestAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteWorkRequestAttachmentRevision(int workRequestAttachmentID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestAttachmentID", workRequestAttachmentID);
        db.Execute("LTDeleteWorkRequestAttachmentRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    public int SubmitDocument(int workRequestID, int userID, int documentType, out int allowedITAR) {
        allowedITAR = 1;
        string subRoles = "";
        subRoles = wfDMO.GetSubRoleItems(workRequestID, documentType);

        // bubble the error 
        int appoverCount = 0;
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        parameters.Add("@UserID", userID);
        parameters.Add("@DocumentTypeID", documentType);
        parameters.Add("@SubRoleCategoriesClause", subRoles);
        parameters.Add("@AppoverCount", appoverCount, dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("@AllowedITAR", allowedITAR, dbType: DbType.Int32, direction: ParameterDirection.Output);
        db.Execute("LTSubmitForApproval", parameters, commandType: CommandType.StoredProcedure);

        appoverCount = parameters.Get<int>("@AppoverCount");
        allowedITAR = parameters.Get<int>("@AllowedITAR");

        return appoverCount;
    }

    /// <summary>
    /// Release lock on all the documents locked by the current user
    /// </summary>

    public void ReleaseLockOnDocument(int userID, int workRequestID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        parameters.Add("@UserID", userID);
        db.Execute("LTReleaseLockOnDocuments", parameters, commandType: CommandType.StoredProcedure);
    }

    public void ReleaseLockOnLotTravelerUpdateDoc(int userID, int ltLotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@UserID", userID);
        db.Execute("LTReleaseLockOnLTUpdateDoc", parameters, commandType: CommandType.StoredProcedure);
    }

    public List<string> GetRejectionOrginatorEmailList(int workRequestID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        List<string> emailList = db.Query<string>("LTGetRejectionOrginatorEmailList", parameters, commandType: CommandType.StoredProcedure).ToList();
        return emailList;
    }

    public int CreateWorkRequestRevision(LTWorkRequest data, int userID) {
        int newWorkRequestID = -1;
        DynamicParameters parameters = new();
        parameters.Add("@UserID", userID);
        parameters.Add("@ChangeComments", data.RevisionComments);
        parameters.Add("@CurrentStep", data.CurrentStep);
        parameters.Add("@LTWorkRequestID", data.ID);
        parameters.Add("@Title", data.Title);
        parameters.Add("@LotType", data.LotType);
        parameters.Add("@EngLotStartRequired", data.EngLotStartRequired);
        parameters.Add("@LotTypeConversion", data.LotTypeConversion);
        parameters.Add("@QualLot", data.QualLot);
        parameters.Add("@PurposeOfRequest", data.PurposeOfRequest);
        parameters.Add("@PlannedLotStartDate", data.PlannedLotStartDate);
        parameters.Add("@PlannedScheduledCloseDate", data.PlannedScheduledCloseDate);
        parameters.Add("@ChargeDepartment", data.ChargeDepartment);
        parameters.Add("@AllocationToUse", data.AllocationToUse);
        parameters.Add("@PredictedCyleTime", data.PredictedCyleTime);
        parameters.Add("@DeptChargedForRawWafers", data.ChargeDepartment);
        parameters.Add("@StartQty", data.StartQty);
        parameters.Add("@TotalQty", data.TotalQty);
        parameters.Add("@WIPArea", data.WIPArea);
        parameters.Add("@LotStartDate", data.LotStartDate);
        parameters.Add("@LotNumber", data.LotNumber);
        parameters.Add("@WIPPartNumber", data.WIPPartNumber);
        parameters.Add("@PartDescription", data.PartDescription);
        parameters.Add("@DefaultEPISiliconPartNumber", data.DefaultEPISiliconPartNumber);
        parameters.Add("@ProcessFlow", data.ProcessFlow);
        parameters.Add("@EmployeeID", data.EmployeeID);
        parameters.Add("@VerbComment", data.VerbComment);
        parameters.Add("@RecordSiliconLotInSPNTapeTagTotrav", data.RecordSiliconLotInSPNTapeTagTotrav);
        parameters.Add("@SiliconLotNoCassette1", data.SiliconLotNoCassette1);
        parameters.Add("@SiliconOnHand", data.SiliconOnHand);
        parameters.Add("@SiliconExpectedDueDate", data.SiliconExpectedDueDate);
        parameters.Add("@SiliconComments", data.SiliconComments);
        parameters.Add("@ReticleOnHand", data.ReticleOnHand);
        parameters.Add("@ReticleExpectedDueDate", data.ReticleExpectedDueDate);
        parameters.Add("@ReticleComments", data.ReticleComments);
        parameters.Add("@SPNTransferOnHand", data.SPNTransferOnHand);
        parameters.Add("@SPNTransferExpectedDueDate", data.SPNTransferExpectedDueDate);
        parameters.Add("@SPNTransferComments", data.SPNTransferComments);
        parameters.Add("@ProbeCardOnHand", data.ProbeCardOnHand);
        parameters.Add("@ProbeCardExpectedDueDate", data.ProbeCardExpectedDueDate);
        parameters.Add("@ProbeCardComments", data.ProbeCardComments);
        parameters.Add("@ProbeRecipeOnHand", data.ProbeRecipeOnHand);
        parameters.Add("@ProbeRecipeExpectedDueDate", data.ProbeRecipeExpectedDueDate);
        parameters.Add("@ProbeRecipeComments", data.ProbeRecipeComments);
        parameters.Add("@LotStartCheckListComments", data.LotStartCheckListComments);
        parameters.Add("@ProcessChangeDetailsOnHand", data.ProcessChangeDetailsOnHand);
        parameters.Add("@ProcessChangeDetailsDueDate", data.ProcessChangeDetailsDueDate);
        parameters.Add("@PCRBNumber", data.PCRBNumber);
        parameters.Add("@LotStartCheckListComments", data.LotStartCheckListComments);
        parameters.Add("@NewWorkRequestID", data.ID, direction: ParameterDirection.InputOutput);
        db.Execute("LTCreateWorkRequestRevision", parameters, commandType: CommandType.StoredProcedure);
        newWorkRequestID = parameters.Get<int>("@NewWorkRequestID");

        return newWorkRequestID;
    }

    public List<Revision> GetWorkReqRevisions(int swrNumber) {
        List<Revision> revisions = db.Query<Revision>("SELECT ID, Revision AS RevisionNumber FROM LTWorkRequest WHERE SWRNumber = @SWRNUmber ORDER BY Revision ", new { SWRNUmber = swrNumber }, commandType: CommandType.Text).ToList();
        return revisions;
    }

    public List<Revision> GetLotTravRevisions(int ltLotID) {
        List<Revision> revisions = db.Query<Revision>("SELECT ID, RevisionNumber FROM LTLotTravelerRevisionAttrib WHERE LTLotID = @ltLotID ORDER BY RevisionNumber ", new { ltLotID = ltLotID }, commandType: CommandType.Text).ToList();
        return revisions;
    }

    public void InsertLot(LTLot lot) {
        MiscDMO.GetLTLotInformation(lot);

        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", lot.WorkRequestID);
        parameters.Add("@LotNumber", lot.LotNumber);
        parameters.Add("@WIPPartNumber", lot.WIPPartNumber);
        parameters.Add("@WaferQty", lot.WaferQty);
        parameters.Add("@PartDescription", lot.PartDescription);
        parameters.Add("@Process", lot.Process);
        parameters.Add("@Location", lot.Location);
        parameters.Add("@Operation", lot.Operation);
        parameters.Add("@LotStatus", lot.LotStatus);
        parameters.Add("@UploadedBy", lot.LotUploadedBy);

        parameters.Add("@WRWithExistingLot", dbType: DbType.Int32, direction: ParameterDirection.Output);

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

        if (parameters.Get<int>("@WRWithExistingLot") != 0) {
            lot.WRWithExistingLot = parameters.Get<int>("@WRWithExistingLot");
        }

    }

    public IEnumerable<LTLot> GetLotList(int workRequestID) {
        StringBuilder sql = new();
        sql.Append("SELECT L.*,    U.FirstName + ' ' + U.LastName AS UploadedByName,CASE WHEN TravelerClosed = 0 THEN 'Open' ELSE 'Closed' END AS TravelerStatus ");
        sql.Append("FROM LTLot L LEFT JOIN Users U ON L.LotUploadedBy = U.UserID ");
        sql.Append("WHERE WorkRequestID = @workRequestID ORDER BY LotNumber  ");
        List<LTLot> lots = db.Query<LTLot>(sql.ToString(), new { @workRequestID = workRequestID }, commandType: CommandType.Text).ToList();
        return lots;
    }

    public IEnumerable<LTLot> GetLotListBasedOnSWRNumber(int swrNumber) {
        StringBuilder sql = new();
        sql.Append("SELECT L.*,    U.FirstName + ' ' + U.LastName AS UploadedByName,CASE WHEN TravelerClosed = 0 THEN 'Open' ELSE 'Closed' END AS TravelerStatus ");
        sql.Append("FROM LTWorkRequest WR INNER JOIN  LTLot L ON WR.ID = L.WorkRequestID  AND WR.IsCurrentRevision = 1 ");
        sql.Append("LEFT JOIN Users U ON L.LotUploadedBy = U.UserID ");
        sql.Append("WHERE WR.SWRNumber = @SWRNumber ORDER BY LotNumber  ");
        List<LTLot> lots = db.Query<LTLot>(sql.ToString(), new { @SWRNumber = swrNumber }, commandType: CommandType.Text).ToList();
        return lots;
    }

    public IEnumerable<LotWithTraveler> GetLotsWithTraveler(int workRequestID) {
        List<LotWithTraveler> lotList = db.Query<LotWithTraveler>("SELECT ID AS LotID, LotNumber FROM LTLot WHERE WorkRequestID = @workRequestID AND IsTravelerGenerated =  1 ORDER BY LotNumber ", new { workRequestID = workRequestID }, commandType: CommandType.Text).ToList();
        return lotList;
    }

    public void CreateTraveler(int ltLotID, int workRequestID, int UserID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@WorkRequestID", workRequestID);
        parameters.Add("@UserID", UserID);
        db.Execute("LTCreateLotTraveler", parameters, commandType: CommandType.StoredProcedure);
    }

    public LTLotTravelerHeaderViewModel GetLotTravelerHeaderForUpdate(int ltLotID, int UserID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@UserID", UserID);
        var data = db.Query<LTLotTravelerHeaderViewModel>("LTGetLotTravelerHeaderForUpdate", parameters, commandType: CommandType.StoredProcedure).Single();
        return data;
    }

    public LTLotTravelerHeaderViewModel GetLotTravelerHeaderForReadOnly(int ltLotID, int revisionNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@RevisionNumber", revisionNumber);
        var data = db.Query<LTLotTravelerHeaderViewModel>("LTGetLotTravelerHeaderForReadOnly", parameters, commandType: CommandType.StoredProcedure).Single();
        return data;
    }

    public IEnumerable<LTLotTravelerHoldSteps> GetLotTravHoldSteps(int ltLotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        List<LTLotTravelerHoldSteps> data = db.Query<LTLotTravelerHoldSteps>("LTGetLotTravelerHoldSteps", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<LTLotTravelerHoldSteps> GetLotTravHoldStepsPending(int ltLotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        List<LTLotTravelerHoldSteps> data = db.Query<LTLotTravelerHoldSteps>("LTGetLotTravelerHoldStepsPending", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<LTLotTravelerHoldSteps> GetLotTravHoldStepsCompleted(int ltLotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        List<LTLotTravelerHoldSteps> data = db.Query<LTLotTravelerHoldSteps>("LTGetLotTravelerHoldStepsCompleted", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<LTLotTravelerHoldSteps> GetLotTravelerHolStepsByRevision(int ltLotID, int revisionNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@RevisionNumber", revisionNumber);
        List<LTLotTravelerHoldSteps> data = db.Query<LTLotTravelerHoldSteps>("LTGetLotTravelerHoldStepsByRevision", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public int CreateLotTravelerRevision(LTLotTravelerHoldSteps model, int userID) {
        DynamicParameters parameters = new();
        parameters.Add("@CurrLotTravelerRevisionID", model.LotTravelerRevisionID);
        parameters.Add("@LTLotID", model.LTLotID);
        parameters.Add("@NewUpdatedBy", userID);
        parameters.Add("@ChangeComments", model.RevisionComments);
        parameters.Add("@NewLotTravelerRevisionID", model.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

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

        int newLotTravRevisionID = parameters.Get<int>("@NewLotTravelerRevisionID");
        // model.ID = id;

        return newLotTravRevisionID;
    }

    public int CanAddLocationOperation(LTLotTravelerHoldSteps model) {
        int result = 0;

        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", model.LTLotID);
        parameters.Add("@Location", model.Location);
        parameters.Add("@OperSequence", model.OperSequence);
        parameters.Add("@Operation", model.Operation);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

        db.Execute("LTCanAddLocationOperation", parameters, commandType: CommandType.StoredProcedure);
        result = parameters.Get<int>("@Result");
        return result;
    }

    public void RestoreLotTravToPrevRevision(int prevLotTravRevID, int newLotTravRevID) {
        DynamicParameters parameters = new();
        parameters.Add("@PrevLotTravelerRevisionID", prevLotTravRevID);
        parameters.Add("@NewLotTravelerRevisionID", newLotTravRevID);

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

    public int UpdateRevisedLotTravelerHoldStep(LTLotTravelerHoldSteps model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", model.ID);
        parameters.Add("@NewLocation", model.Location);
        parameters.Add("@NewOperSequence", model.OperSequence);
        parameters.Add("@NewOperation", model.Operation);
        parameters.Add("@NewOperationDescription", model.OperationDescription);
        parameters.Add("@NewChangeInstructions", model.ChangeInstructions);
        parameters.Add("@NewUpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

        db.Execute("LTUpdateRevisedLotTravelerHoldStep", parameters, commandType: CommandType.StoredProcedure);
        result = parameters.Get<int>("@Result");
        return result;
    }

    public int UpdateLotTravelerHoldStep(LTLotTravelerHoldSteps model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", model.ID);
        parameters.Add("@NewLocation", model.Location);
        parameters.Add("@NewOperSequence", model.OperSequence);
        parameters.Add("@NewOperation", model.Operation);
        parameters.Add("@NewOperationDescription", model.OperationDescription);
        parameters.Add("@NewChangeInstructions", model.ChangeInstructions);
        parameters.Add("@NewUpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        result = parameters.Get<int>("@Result");
        return result;
    }

    public int InsertLotTravelerHoldStep(LTLotTravelerHoldSteps model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LTLotTravHoldStepID", model.ID);
        parameters.Add("@LTLotID", model.LTLotID);
        parameters.Add("@Location", model.Location);
        parameters.Add("@OperSequence", model.OperSequence);
        parameters.Add("@Operation", model.Operation);
        parameters.Add("@OperationDescription", model.OperationDescription);
        parameters.Add("@ChangeInstructions", model.ChangeInstructions);
        parameters.Add("@UpdatedBy", userID);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

        db.Execute("LTInsertLotTravelerHoldStep", parameters, commandType: CommandType.StoredProcedure);
        result = parameters.Get<int>("@Result");

        return result;
    }

    public void DeleteLot(int ltLotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        db.Execute("LTDeleteLot", parameters, commandType: CommandType.StoredProcedure);
    }

    public void UpdateLotTravlerExecution(int lotTravHoldStepID, string taskComments, bool CompletedFlag, int userID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotTravHoldStepID", lotTravHoldStepID);
        parameters.Add("@TaskComments", taskComments);
        parameters.Add("@CompletedFlag", CompletedFlag);
        parameters.Add("@UserID", userID);

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

    public LotTravelerPdf GetLotTravlerPdf(int ltLotID, int revisionNumber) {
        LotTravelerPdf data = new();

        DynamicParameters parameters = new();
        parameters.Add("@LTLotID", ltLotID);
        parameters.Add("@RevisionNumber", revisionNumber);

        using (var multipleResultItems = db.QueryMultiple("LTGetLotTravelerForPDF", parameters, commandType: CommandType.StoredProcedure)) {
            data = multipleResultItems.Read<LotTravelerPdf>().SingleOrDefault();

            List<LTLotTravelerHoldSteps> ltTaskList = multipleResultItems.Read<LTLotTravelerHoldSteps>().ToList();
            if (data != null && ltTaskList != null) {
                if (ltTaskList.Count > 0)
                    data.Tasklist.AddRange(ltTaskList);
            }

        }

        return data;
    }

    public IEnumerable<ApprovalLogHistory> GetWorkReqApprovalLogHistory(int swrNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@SWRNumber", swrNumber);
        List<ApprovalLogHistory> data = db.Query<ApprovalLogHistory>("LTGetWorkRequestApprovalLogHistory", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<RevisionHistory> GetWorkReqRevisionHistory(int swrNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@SWRNumber", swrNumber);
        List<RevisionHistory> data = db.Query<RevisionHistory>("LTGetWorkRequestRevHistory", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<RevisionHistory> GetLotTravelerRevisionHistory(int lotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotID", lotID);
        List<RevisionHistory> data = db.Query<RevisionHistory>("LTGetLotTravelerRevHistory", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal void InsertLotTravLotHoldStepAttachment(LTLotTravAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", attachment.LTLotTravHoldStepID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@LotTravelerRevisionID", attachment.LotTravelerRevisionID);
        db.Execute("LTInsertLotTravHoldStepAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertLotTravLotHoldStepAttachmentRevision(LTLotTravAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@LTHoldStepID", attachment.LTLotTravHoldStepID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);
        parameters.Add("@DocType", attachment.DocType);
        parameters.Add("@UploadedByID", attachment.UploadedByID);
        parameters.Add("@LotTravelerRevisionID", attachment.LotTravelerRevisionID);
        db.Execute("LTInsertLotTravHoldStepAttachmentRevision", parameters, commandType: CommandType.StoredProcedure);
    }

    internal int IsWorkRequestDocLockedByUser(int workRequestID, int userID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        parameters.Add("@UserID", userID);
        var data = db.Query<int>("LTIsWorkRequestDocLockedByUser", parameters, commandType: CommandType.StoredProcedure).Single();

        return data;
    }

    internal int CloseTraveler(int ltLotID, int userID, string reason) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@LtLotID", ltLotID);
        parameters.Add("@UserID", userID);
        parameters.Add("@Reason", reason);
        parameters.Add("@Result", result, direction: ParameterDirection.InputOutput);

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

        result = parameters.Get<int>("@Result");

        return result;
    }

    internal string GetFileName(string fileGUID, int typeOfDoc) {
        DynamicParameters parameters = new();
        parameters.Add("@FileGUID", fileGUID);
        parameters.Add("@TypeOfDoc", typeOfDoc);
        var fileName = db.Query<string>("LTGetFileName", parameters, commandType: CommandType.StoredProcedure).Single();
        return fileName;
    }

    public void ReassignOriginator(int workRequestID, int newOriginatorID, string comments, int userID) {
        DynamicParameters parameters = new();
        parameters.Add("@WorkRequestID", workRequestID);
        parameters.Add("@NewOriginatorID", newOriginatorID);
        parameters.Add("@Comments", comments);
        parameters.Add("@UserID", userID);

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

}