using System;

using System.IO;

using System.Xml;

using System.Text;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Net;

using System.Web;

using System.Web.SessionState;

using System.Globalization;

using Aras.IOM;

​

namespace gm_PAT_Lock_After_Specs_Complete_class

{

    class gm_PAT_Lock_After_Specs_Complete_class : Item

    {

        public gm_PAT_Lock_After_Specs_Complete_class() : base(null) { }

​

        public Item gm_PAT_Lock_After_Specs_Complete()

        {

            //NOTE: This is system defined by Innovator and will be available when below is pasted into Innovator.

            Aras.Server.Core.CallContext CCO = ((Aras.Server.Core.IOMConnection)serverConnection).CCO;

            Aras.Server.Core.IContextState RequestState = CCO.RequestState;

​

            // _________________Cut below this line to paste into Innovator________________________

​

​

​

​

            #region gm_PAT_Lock_After_Specs_Complete

​

            /* *****************************************************************************

            Method Name:            gm_PAT_Lock_After_Specs_Complete

            Client Name:            General Motors

            Created By/Company:     Pitamber Tiwari/GM 

            Creation Date:          2017-07-12

            Description:

                Promotes the PAT items associated with the current Work Order (in Investigate State) to Locked State when a spec completes the activity (Usually Investigate 10.1/10.2)

​

            Hooks:

                Type:       gm_Work Order, gm_Work Order gm_PATSrc

                Event:      on Activity close

                Called by:  <Parent functions>

​

            Inputs:

                <describe the inputs expected by the server method>

​

            Revisions:

                Rev Date        Modified By             Description

                2017-07-12      Pitamber Tiwari         Initial creation

                2017-09-27      Pitamber Tiwari         Display friendly message for Item Locked Exception

            ***************************************************************************** */

​

            //if (System.Diagnostics.Debugger.Launch())

            //    System.Diagnostics.Debugger.Break();

​

            string MethodName = "gm_PAT_Lock_After_Specs_Complete";

​

            Item result = this;     // default return value.

​

            try

            {

                // Only ARAS PLM can update the Lifecycle State

                grantIdentityPrivileges("Aras PLM");

​

                IDataAccessLayer dataAccessLayer = new DataAccessLayer(innovator);

                BusinessLogic businessLogic = new BusinessLogic(dataAccessLayer);

                businessLogic.LockPAT(this);

            }

            catch (System.ArgumentException argex)

            {

                // Hide the details and copy buffer as there is no meaningful info there

                result = innovator.newError("<style>#toggleInfo {display: none;} #copyBuffer {display: none;}</style>" + argex.Message);

            }

            catch (System.ApplicationException appex)

            {

                result = innovator.newError(string.Format("<b>ERROR in method {0}</b><br>{1}", MethodName, appex.Message));

            }

            catch (Aras.Server.Core.ItemIsLockedException ex)

            {

                result = innovator.newError(ex.Message);

                result.setErrorDetail(ex.InnerException.Message);

            }

            catch (System.Exception ex)

            {

                 if (ex.Message == "Aras.Server.Core.ItemIsLockedBySomeoneElseException")

               {

                   result = innovator.newError("The ECR is locked by someone else. Unlock the ECR to proceed further.");

               }

               else

               {

                result = innovator.newError(string.Format("<b>SYSTEM EXCEPTION in method {0}</b><br>{1}", MethodName, ex.Message));

               }

            }

            finally

            {

                // Revoke ARAS PLM Privileges

                revokeIdentityPrivileges();

            }

​

            return result;

        }

​

        #region Innovator

​

        private Innovator _innovator;

        public Innovator innovator

        {

            get

            {

                if (_innovator == null)

                    _innovator = this.getInnovator();

                return _innovator;

            }

        }

​

        #endregion

​

        #region BusinessLogic

​

        public class BusinessLogic

        {

            #region Private Fields

​

            // Define Constants that are going to be used instead of string in the method so that we have a centralized place for update

            private static class Constants

            {

                internal static class ItemTypeNames

                {

                    internal const string WorkflowTask = "Workflow Task";

                    internal const string WorkOrder = "gm_Work Order";

                    internal const string WorkOrderPAT = "gm_Work Order gm_PATSrc";

                    internal const string WorkOrderPPAT = "gm_Work Order PPPAT";

                }

​

                internal static class PropertyNames

                {

                    internal const string AssignmentId = "AssignmentId";

                    internal const string State = "state";

                    internal const string Item = "item";

                    internal const string IsAuto = "is_auto";

                    internal const string LockedById = "locked_by_id";

                    internal const string KeyedName = "keyed_name";

                    internal const string Type = "_type";

                }

​

                internal static class PropertyCondition

                {

                    internal const string NotNull = "is not null";

                }

​

                internal static class Actions

                {

                    internal const string Get = "get";

                    internal const string PromoteItem = "promoteItem";

                }

​

                internal static class Attributes

                {

                    internal const string Select = "select";

                    internal const string IdList = "idlist";

                }

​

                internal static class WorkOrderStates

                {

                    internal const string Investigate = "Investigate";

                }

​

                internal static class PatStates

                {

                    internal const string Locked = "Locked";

                }

​

                internal static class EcrTypes

                {

                    internal const string BCR = "BOM Change Request Type";

                    internal const string BSD = "Build Site Direction Type";

                }

            }

​

            private readonly IDataAccessLayer dataAccessLayer;

            private Item workOrder = null;

            private readonly Innovator inn = null;

            #endregion

​

            public BusinessLogic(IDataAccessLayer dataAccessLayer)

            {

                this.dataAccessLayer = dataAccessLayer;

                this.inn = dataAccessLayer.getInnovator();

            }

​

            public void LockPAT(Item activity)

            {

​

                workOrder = GetWorkOrder(activity);

​

                // The work order must be in investigate state

                if (workOrder != null && workOrder.getProperty(Constants.PropertyNames.State).Equals(Constants.WorkOrderStates.Investigate, StringComparison.OrdinalIgnoreCase))

                {

                    //IM106527011 - Added this below code to validate the effective point code and the stock disposition combination

                    //Effective Point Code Validation against the current date

                    Item effPtVal = inn.newItem("Method", "gm_PIC EffectivePointValidation");

                    effPtVal.setProperty("source_id", workOrder.getID());

                    effPtVal = effPtVal.apply();

                    if (effPtVal.isError()) throw new ApplicationException(effPtVal.getErrorString());

​

                    //Effective Point code and Stock Disposition Code Combination Check

                    Item effPtValStk = inn.newItem("Method", "gm_EffecPt_StockDisp_Validation");

                    effPtValStk.setProperty("id", workOrder.getID());

                    effPtValStk = effPtValStk.apply();

                    if (effPtValStk.isError()) throw new ApplicationException(effPtValStk.getErrorString());

​

                    string type = workOrder.getProperty(Constants.PropertyNames.Type, "");

​

                    if (type.Equals(Constants.EcrTypes.BCR, StringComparison.OrdinalIgnoreCase) || type.Equals(Constants.EcrTypes.BSD, StringComparison.OrdinalIgnoreCase))

                    {

                        // Promote the PPAT. 

                        PromoteRelationship(Constants.ItemTypeNames.WorkOrderPPAT, Constants.PatStates.Locked);

                    }

                    else

                    {

                        // Promote the PAT

                        PromoteRelationship(Constants.ItemTypeNames.WorkOrderPAT, Constants.PatStates.Locked);

                    }

                }

            }

​

            /// <summary>

            /// Returns a work order associated with the given assignment in the activity.

            /// </summary>

            /// <param name="activity">Currnt activity on the workflow being voted on</param>

            /// <returns>Work Order if found; null otherwise</returns>

            private Item GetWorkOrder(Item activity)

            {

                // Workflow task and Assignment have the same id

                var workflowTask = dataAccessLayer.getItemById(Constants.ItemTypeNames.WorkflowTask, activity.getProperty(Constants.PropertyNames.AssignmentId));

​

                if (!workflowTask.isError() && !workflowTask.isEmpty())

                {

                    return dataAccessLayer.getItemById(Constants.ItemTypeNames.WorkOrder, workflowTask.getProperty(Constants.PropertyNames.Item));

                }

                return null;

            }

​

            /// <summary>

            /// Returns list of ids for the given RelationshipType that are not already in the specified state.

            /// </summary>

            /// <param name="relationshipTypeName">RelationshipType name</param>

            /// <param name="newState">Lifecycle state the relationshipTypes are being promoted to</param>

            /// <returns><see cref="List{string}"/> of ids to be promoted, if any; otherwise empty list</returns>

            private List<string> GetRelationshipTypeIds(string relationshipTypeName, string newState)

            {

                workOrder.fetchRelationships(relationshipTypeName, "id,state");

                var ecrRelationships = workOrder.getRelationships(relationshipTypeName);

                int count = ecrRelationships.getItemCount();

​

                List<string> relationshipIds = new List<string>(count);

​

                for (int i = 0; i < count; i++)

                {

                    var currentState = ecrRelationships.getItemByIndex(i).getProperty(Constants.PropertyNames.State, "");

                    // Only update if it's not already in the given state

                    if (!currentState.Equals(newState, StringComparison.OrdinalIgnoreCase))

                    {

                        relationshipIds.Add(ecrRelationships.getItemByIndex(i).getID());

                    }

                }

​

                return relationshipIds;

            }

​

            /// <summary>

            /// Promotes the supplied RelationshipTypes to the specified state

            /// </summary>

            /// <remarks>

            /// Only promotes the item if the current state is different from the specified state.

            /// </remarks>

            private void PromoteRelationship(string relationship, string newState)

            {

                // Get the ids of relationship that are not in the current state

                var idsToPromote = GetRelationshipTypeIds(relationship, newState);

​

                // Promote together

                if (idsToPromote.Any())

                {

                    Item itemsToPromote = dataAccessLayer.newItem(relationship, Constants.Actions.Get);

                    itemsToPromote.setAttribute(Constants.Attributes.IdList, string.Join(",", idsToPromote));

                    itemsToPromote.setProperty(Constants.PropertyNames.State, newState);

​

                    // Items that are locked cannot be promoted. OOTB feature. Throws an exception trying to promote items that are locked.

                    try

                    {

                        var results = itemsToPromote.apply(Constants.Actions.PromoteItem);

                        if (results.isError())

                        {

                            throw new  Aras.Server.Core.InnovatorServerException(results.getErrorDetail());

                        }

                    }

                    catch (Aras.Server.Core.ItemIsLockedException)

                    {

                        GetAllKeyedNames(relationship, idsToPromote);

                    }

                }

            }

            //Sonar qube issues

            private void GetAllKeyedNames(string relationship, List<string> idsToPromote)

            {

                // Get all the keyed names for the ids

                Item itemsFailedToPromote = dataAccessLayer.newItem(relationship, Constants.Actions.Get);

                itemsFailedToPromote.setAttribute(Constants.Attributes.IdList, string.Join(",", idsToPromote));

                itemsFailedToPromote.setPropertyCondition(Constants.PropertyNames.LockedById, Constants.PropertyCondition.NotNull);

                itemsFailedToPromote.setAttribute(Constants.Attributes.Select, "id, keyed_name,locked_by_id");

                itemsFailedToPromote = itemsFailedToPromote.apply();

​

                if (itemsFailedToPromote.isError())

                {

                    throw new  Aras.Server.Core.InnovatorServerException(

                        "Failed trying to Promote locked items. " + itemsFailedToPromote.getErrorDetail());

                }

​

                List<string> lockedItems = new List<string>();

                for (var i = 0; i < itemsFailedToPromote.getItemCount(); i++)

                {

                    var itm = itemsFailedToPromote.getItemByIndex(i);

                    //Check lock status added to fix the defect 107383

                    if (itm.getProperty("locked_by_id") == null) continue;

                    var lockedItem = itm.getProperty(Constants.PropertyNames.KeyedName, "");

                    if (!string.IsNullOrWhiteSpace(lockedItem))

                    {

                        lockedItems.Add("\"" + lockedItem + "\"");

                    }

                }

​

                string itemOrItems = lockedItems.Count != 1 ? " items" : " item";

                string isOrAre = lockedItems.Count != 1 ? " are" : " is";

​

                // For developer purpose

                var detailException =

                    new Exception("Locked Item Type: " + relationship + ". Locked Ids: " + string.Join(", ", idsToPromote) + ".");

​

                throw new Aras.Server.Core.ItemIsLockedException(

                    "<b>" + string.Join("</b>, <b>", lockedItems) + "</b>" + itemOrItems + isOrAre +

                    " locked. Your vote cannot be completed while the" + itemOrItems + isOrAre + " locked. Unlock the" +

                    itemOrItems + " to continue voting.", detailException);

            }

        }

​

        #endregion

​

        #region IDataAccessLayer

​

        public interface IDataAccessLayer

        {

​

            Innovator getInnovator();

​

            Item newItem(string itemTypeName, string action);

​

            Item apply(Item item);

​

            Item getItemById(string itemTypeName, string id);

​

            Item fetchRelationships(Item item, string relationshipTypeName);

​

        }

​

        #endregion

​

        #region DataAccessLayer

​

        public class DataAccessLayer : IDataAccessLayer

        {

            private readonly Innovator innovator = null;

​

            public DataAccessLayer(Innovator innovator)

            {

                this.innovator = innovator;

            }

​

            public Innovator getInnovator()

            {

                return this.innovator;

            }

​

            public Item newItem(string itemTypeName, string action)

            {

                return innovator.newItem(itemTypeName, action);

            }

​

            public Item getItemById(string itemTypeName, string id)

            {

                return innovator.getItemById(itemTypeName, id);

            }

​

            public Item apply(Item item)

            {

                return item.apply();

            }

​

            public Item fetchRelationships(Item item, string relationshipTypeName)

            {

                return item.fetchRelationships(relationshipTypeName);

            }

        }

​

        #endregion

​

        #region "Identity Privileges"

        /// <summary>

        /// This section is to allow the current method to assume the privileges of a specific identity.

        /// For instance calling: grantIdentityPrivileges("Aras PLM"); will allow this method all of the

        /// rights of the Aras PLM identity.

        /// The revokeIdentityPrivileges() method will removed the privileges added for this method. It

        /// must be called from before the method exits. Most likely in the Finally of the main method code.

        /// If different identity privileges must be granted a second time within the same method, first use

        /// revokeIdentityPrivileges() to remove the first privileges before granting new ones.

        /// </summary>

​

        private string AssignedIdentity;

​

        /// <summary>

        /// If required, call this helper function to run this method as another Identity. 

        /// Ensure that you call the revokeIdentityPrivileges helper function in the 

        /// finally block to revoke the Identity privilege when this method completes.

        /// </summary>

        public void grantIdentityPrivileges(string assignedIdent)

        {

            if (!string.IsNullOrEmpty(AssignedIdentity))

            {

                revokeIdentityPrivileges();

            }

​

            Aras.Server.Security.Identity secIdentity = Aras.Server.Security.Identity.GetByName(assignedIdent);

            if (Aras.Server.Security.Permissions.GrantIdentity(secIdentity))

            {

                AssignedIdentity = assignedIdent;

            }

        }

​

        /// <summary>

        /// If you granted security privileges, then you need to ensure that you revoke the privilege.

        /// </summary>

        public void revokeIdentityPrivileges()

        {

            if (AssignedIdentity != null)

            {

                Aras.Server.Security.Permissions.RevokeIdentity(Aras.Server.Security.Identity.GetByName(AssignedIdentity));

                AssignedIdentity = null;

            }

        }

​

        #endregion

​

        public void EndOfMethod()

        {

            //ARAS auto-appends an end curly brace }, so this EndOfMethod method allows us to not have to choose which method has the missing closing brace

            #endregion

// __________Cut Above this line and paste into Innovator Method editor __________

            // This method must not be closed when it is pasted into Innovator!

        }

    }

}

​

​

using System;

using System.IO;

using System.Xml;

using System.Text;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Net;

using System.Web;

using System.Web.SessionState;

using System.Globalization;

using System.Web.ModelBinding;

using Aras.IOM;

using Aras.Net;

​

​

namespace gm_Query_PPAT_onSearch_class

{

    class gm_Query_PPAT_onSearch_class : Item

    {

        public gm_Query_PPAT_onSearch_class() : base(null) { }

​

        public Item gm_Query_PPAT_onSearch()

        {

            //NOTE: This is system defined by Innovator and will be available when below is pasted into Innovator.

            Aras.Server.Core.CallContext CCO = ((Aras.Server.Core.IOMConnection)serverConnection).CCO;

            Aras.Server.Core.IContextState RequestState = CCO.RequestState;

​

            // _________________Cut below this line to paste into Innovator________________________

​

​

​

            #region gm_Query_PPAT_onSearch

​

            /* *****************************************************************************

            Method Name:            gm_Query_PPAT_onSearch

            Client Name:            General Motors

            Created By/Company:     Pitamber Tiwari/GM 

            Description:

                    Fetches the data for the query when the Search Button is clicked.

            ***************************************************************************** */

            if(System.Diagnostics.Debugger.Launch())

               System.Diagnostics.Debugger.Break();

​

            const string methodName = "gm_Query_PPAT_onSearch";

            Item result = this;     // default return value.

​

            try

            {

                IDataAccessLayer dataAccessLayer = new DataAccessLayer(innovator);

                BusinessLogic businessLogic = new BusinessLogic(dataAccessLayer);

                result = businessLogic.Search(result);

​

            }

            catch (Aras.Server.Core.InnovatorServerException ex)

            {

                result = innovator.newError(string.Format("<b>SYSTEM EXCEPTION in method {0}</b><br>{1}", methodName, ex.Message));

            }

            catch (System.ArgumentException argex)

            {

                // Hide the details and copy buffer as there is no meaningful info there

                result = innovator.newError("<style>#toggleInfo {display: none;} #copyBuffer {display: none;}</style>" + argex.Message);

            }

​

​

            return result;

        }

​

        #region Innovator

​

        private Innovator _innovator;

        public Innovator innovator

        {

            get

            {

                if (_innovator == null)

                    _innovator = this.getInnovator();

                return _innovator;

            }

        }

​

        #endregion

​

        #region BusinessLogic

​

        public class BusinessLogic

        {

            #region Private Fields

​

            private readonly IDataAccessLayer _dataAccessLayer;

​

            #endregion

​

            public BusinessLogic(IDataAccessLayer dataAccessLayer)

            {

                _dataAccessLayer = dataAccessLayer;

            }

​

            public Item Search(Item searchItem)

            {

                var queryPpat = new QueryPpatPoco(searchItem);

                // PPAT Query View uses program code instead of program name, so get the code

                queryPpat.PreProdProgramCode = GetProgramCode(queryPpat.PreProdProgramName);

​

                var sqlParameters = new List<System.Data.SqlClient.SqlParameter>();

                var query = queryPpat.GenerateQuery(sqlParameters);

                var result = _dataAccessLayer.GetResult(query, sqlParameters, queryPpat.PreProdProgramName, queryPpat.HardwareStage);

​

                if (result.isError())

                {

                    throw new ArgumentException(result.getErrorDetail());

                }

​

                return result;

            }

​

            /// <summary>

            /// Returns the program code for the given program name

            /// </summary>

            public string GetProgramCode(string programName)

            {

                var query = "select _PREPROD_PROD_PGM_CD from GM_PREPROD_PROD_PGM_CD where _PREPROD_PROD_PGM_NM = '" +

                            programName + "'";

                var result = _dataAccessLayer.ApplySql(query);

                if (result.isError())

                    throw new ArgumentException("\"Pre - Production Program\" is not valid.");

                return result.getItemByIndex(0).getProperty("_preprod_prod_pgm_cd");

            }

        }

​

        public class QueryPpatPoco

        {

            internal static class PropertyNames

            {

                internal const string VehicleBuildQuery = "_vehicle_build_query";

                internal const string GpsBuildQuery = "_gps_build_query";

                internal const string PreProdProgramName = "_pre_prod_program_name";

                internal const string HardwareStage = "_hardware_stage";

                internal const string UpcPrefix = "_upc_prefix";

                internal const string Upc = "_upc";

                internal const string Fna = "_fna";

                internal const string FnaDescription = "_fna_desc";

                internal const string PartNumber = "_part_numbers";

                internal const string Pls = "_pls";

                internal const string Dls = "_dls";

                internal const string EngineerCodes = "_engr_codes";

                internal const string EngineerLastNames = "_engr_last_name";

            }

​

            private const string Keyedname = "keyed_name";

            public bool VehicleBuildQuery { get; set; }

            public bool GpsBuildQuery { get; set; }

            public string PreProdProgramName { get; set; }

            public string PreProdProgramCode { get; set; }

            public string HardwareStage { get; set; }

            public string UpcPrefix { get; set; }

            public string Upc { get; set; }

            public string Fna { get; set; }

            public string FnaDescription { get; set; }

            public string PartNumber { get; set; }

            public string Pls { get; set; }

            public string Dls { get; set; }

            public List<string> EngineerCodes { get; set; }

            public List<string> EngineerLastNames { get; set; }

​

            public QueryPpatPoco(Item queryItem)

            {

                VehicleBuildQuery = Trim(queryItem.getProperty(PropertyNames.VehicleBuildQuery, "0")).Equals("1", StringComparison.OrdinalIgnoreCase);

                GpsBuildQuery = Trim(queryItem.getProperty(PropertyNames.GpsBuildQuery, "0")).Equals("1", StringComparison.OrdinalIgnoreCase);

                PreProdProgramName = Trim(queryItem.getProperty(PropertyNames.PreProdProgramName));

                HardwareStage = Trim(queryItem.getProperty(PropertyNames.HardwareStage));

                UpcPrefix = Trim(queryItem.getPropertyAttribute(PropertyNames.UpcPrefix, Keyedname));

                Upc = Trim(queryItem.getPropertyAttribute(PropertyNames.Upc, Keyedname));

                Fna = Trim(queryItem.getPropertyAttribute(PropertyNames.Fna, Keyedname));

                FnaDescription = Trim(queryItem.getProperty(PropertyNames.FnaDescription));

                PartNumber = Trim(queryItem.getPropertyAttribute(PropertyNames.PartNumber, Keyedname));

                Pls = Trim(queryItem.getProperty(PropertyNames.Pls));

                Dls = Trim(queryItem.getProperty(PropertyNames.Dls));

                EngineerCodes = Trim(queryItem.getProperty(PropertyNames.EngineerCodes, "")).Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();

                EngineerLastNames = Trim(queryItem.getProperty(PropertyNames.EngineerLastNames, "")).Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();

​

                SanityCheck();

            }

​

            private void SanityCheck()

            {

                // Make sure either Vehicle or GPS is checked

                if (!VehicleBuildQuery && !GpsBuildQuery)

                    throw new ArgumentException("Either \"Vehicle Build Query\" or \"GPS Build Query\" must be selected.");

​

                // 1. The user must select an existing combination of "Pre-Production Program" and "Hardware Stage" as minimum criteria to perform a query.

                if (string.IsNullOrWhiteSpace(PreProdProgramName) || string.IsNullOrWhiteSpace(HardwareStage))

                    throw new ArgumentException(" \"Pre - Production Program\" and \"Hardware Stage\" must be selected.");

​

                // Must have at-least one other property specified

                if (string.IsNullOrWhiteSpace(PartNumber) && string.IsNullOrWhiteSpace(UpcPrefix) && string.IsNullOrWhiteSpace(Upc) && string.IsNullOrWhiteSpace(Fna)

                     && string.IsNullOrWhiteSpace(FnaDescription) && (EngineerCodes == null || !EngineerCodes.Any()) && (EngineerLastNames == null || !EngineerLastNames.Any()))

                {

                    throw new ArgumentException("You must select a \"Pre-Production Program and Hardware Stage\" and a minimum of one other criteria in order to perform a search.");

                }

​

                // Make sure the engineering codes are max 6 count with 4 chars each

                if (EngineerCodes != null && (EngineerCodes.Count > 6 || EngineerCodes.Any(s => s.Length != 4)))

                {

                    throw new ArgumentException("Engineer Code(s) must be 4 characters each separated by commas; maximum of 6 Engineer Codes allowed.");

                }

​

                // Make sure engineer names length is no greater than 30

                if (EngineerLastNames != null && EngineerLastNames.Any() && EngineerLastNames.Select(s => s.Length).Sum() > 30)

                {

                    throw new ArgumentException("Engineer Last Name(s) must be 30 or fewer characters.");

                }

            }

​

            public string Trim(string input)

            {

                return input != null ? input.Trim() : null;

            }

​

            public string GenerateQuery(List<System.Data.SqlClient.SqlParameter> sqlParameters)

            {

                var searchQuery = string.Empty;

​

                // 1c. If the "Vehicle Build Query" was selected and the "GPS Build Query" was NOT selected on the query selection screen,

                // then the results shall be based on the Experimental BOM data.

                if (VehicleBuildQuery)

                {

                    searchQuery = new VehicleSearchQuery(this).GetQuery(sqlParameters);

                }

​

                if (!GpsBuildQuery) return searchQuery;

​

                // 1e. If the "Vehicle Build Query" was selected and the "GPS Build Query" was also selected on the query selection screen,

                // then the results shall be based on both the Experimental BOM data and the Experimental Component data.

                if (VehicleBuildQuery)

                    searchQuery += " union ";

​

                // 1d. If the "Vehicle Build Query" was NOT selected and the "GPS Build Query" was selected on the query selection screen,

                // then the results shall be based on the Experimental Component data.

                searchQuery += new GpsSearchQuery(this).GetQuery(sqlParameters);

​

                return searchQuery;

            }

        }

​

        public abstract class BaseSearchQuery

        {

            protected string PartNumberColName;

            protected string DlsColName;

            protected string PlsColName;

            protected string UpcPrefixColName;

            protected string UpcColName;

            protected string FnaColName;

            protected string FnaDescriptionColName;

            protected string EngineerCodesColName;

            protected string EngineerLastNamesColName;

            protected QueryPpatPoco SearchItem;

​

            protected static class ParameterNames

            {

                internal const string PreProdProgam = "@preprodpgram";

                internal const string HardwareStage = "@hardwarestage";

                internal const string PartNumber = "@partnumber";

                internal const string Dls = "@dls";

                internal const string Pls = "@pls";

                internal const string UpcPrefix = "@upcprefix";

                internal const string Upc = "@upc";

                internal const string Fna = "@fna";

                internal const string FnaDescription = "@fnadesc";

                internal const string EngineerCodes = "@engineercodes";

                internal const string EngineerLastNames = "@engineerlastname";

            }

​

            protected bool IsVehicle = false;

​

            protected BaseSearchQuery(QueryPpatPoco ppatSearch)

            {

                SearchItem = ppatSearch;

            }

​

            public abstract void SetColumnNames();

​

            public abstract string BaseQuery();

​

           private static void AddToSqlParameter(List<System.Data.SqlClient.SqlParameter> sqlParameters, string parameterName, string value)

            {

                var sqlParameter = new System.Data.SqlClient.SqlParameter(parameterName, value);

                if (string.IsNullOrWhiteSpace(value))

                {

                    sqlParameter.Value = DBNull.Value;

                }

​

                sqlParameters.Add(sqlParameter);

            }

​

​

            public string GetQuery(List<System.Data.SqlClient.SqlParameter> sqlParameters)

            {

                var query = new StringBuilder();

                var parameterPrefix = IsVehicle ? "Vehicle" : "Gps";

​

                // Add the base query

                query.Append(BaseQuery());

​

                // Add PreProd and Hardware parameters

                AddToSqlParameter(sqlParameters, ParameterNames.PreProdProgam + parameterPrefix, SearchItem.PreProdProgramCode);

                AddToSqlParameter(sqlParameters, ParameterNames.HardwareStage + parameterPrefix, SearchItem.HardwareStage);

                AddToSqlParameter(sqlParameters, ParameterNames.PartNumber + parameterPrefix, SearchItem.PartNumber);

                AddToSqlParameter(sqlParameters, ParameterNames.Dls + parameterPrefix, SearchItem.Dls);

                AddToSqlParameter(sqlParameters, ParameterNames.Pls + parameterPrefix, SearchItem.Pls);

                AddToSqlParameter(sqlParameters, ParameterNames.Upc + parameterPrefix, SearchItem.Upc);

                AddToSqlParameter(sqlParameters, ParameterNames.Fna + parameterPrefix, SearchItem.Fna);

                AddToSqlParameter(sqlParameters, ParameterNames.FnaDescription + parameterPrefix, SearchItem.FnaDescription);

                AddToSqlParameter(sqlParameters, ParameterNames.UpcPrefix + parameterPrefix, SearchItem.UpcPrefix);

                AddToSqlParameter(sqlParameters, ParameterNames.EngineerCodes + parameterPrefix, string.Join(",", SearchItem.EngineerCodes));

                AddToSqlParameter(sqlParameters, ParameterNames.EngineerLastNames + parameterPrefix, string.Join(",", SearchItem.EngineerLastNames));

​

                return query.ToString();

            }

        }

​

        public class GpsSearchQuery : BaseSearchQuery

        {

            public GpsSearchQuery(QueryPpatPoco ppatSearch) : base(ppatSearch)

            {

                SetColumnNames();

            }

​

            public sealed override void SetColumnNames()

            {

                PartNumberColName = "a01.CPNT_part_no";

                DlsColName = "a01.CPNT_part_dls";

                PlsColName = "a01.CPNT_part_pls";

                UpcPrefixColName = "";

                UpcColName = "a01.CPNT_upc_cde";

                FnaColName = "a01.CPNT_fna_Cde";

                FnaDescriptionColName = "a01.fna_desc";

                EngineerCodesColName = "a01.ENGR_CD_LIST";

                EngineerLastNamesColName = "a01.ENGR_NM_LIST";

            }

​

            public override string BaseQuery()

            {

                // fn_ECM_PreProd_BOM_VW (Pre-prod Program, hardware stage, part number, dls,  pls, upc, fna, fna desc, upc prefix, engr code, engr name )

                return string.Format(@"select * from Fn_ECM_PreProd_COMP_VW ( {0}Gps, {1}Gps, {2}Gps, {3}Gps, {4}Gps, {5}Gps, {6}Gps, {7}Gps, {8}Gps, {9}Gps, {10}Gps)",

                    ParameterNames.PreProdProgam,

                    ParameterNames.HardwareStage,

                    ParameterNames.PartNumber,

                    ParameterNames.Dls,

                    ParameterNames.Pls,

                    ParameterNames.Upc,

                    ParameterNames.Fna,

                    ParameterNames.FnaDescription,

                    ParameterNames.UpcPrefix,

                    ParameterNames.EngineerCodes,

                    ParameterNames.EngineerLastNames);

            }

        }

​

        public class VehicleSearchQuery : BaseSearchQuery

        {

            public VehicleSearchQuery(QueryPpatPoco ppatSearch) : base(ppatSearch)

            {

                SetColumnNames();

                IsVehicle = true;

            }

​

            public sealed override void SetColumnNames()

            {

                PartNumberColName = "a01.part_no";

                DlsColName = "a01.part_dls";

                PlsColName = "a01.part_pls";

                UpcPrefixColName = "a01.upc_Prefx";

                UpcColName = "a01.upc_cde";

                FnaColName = "a01.fna_Cde";

                FnaDescriptionColName = "a01.fna_desc";

                EngineerCodesColName = "a01.ENGR_CD_LIST";

                EngineerLastNamesColName = "a01.ENGR_NM_LIST";

            }

​

            public override string BaseQuery()

            {

                // Fn_ECM_PreProd_COMP_VW    (Pre-prod Program, hardware stage, part number, dls,  pls, upc, fna, fna desc, upc prefix, engr code, engr name )

                return string.Format(@"select * from fn_ECM_PreProd_BOM_VW ( {0}Vehicle, {1}Vehicle, {2}Vehicle, {3}Vehicle, {4}Vehicle, {5}Vehicle, {6}Vehicle, {7}Vehicle, {8}Vehicle, {9}Vehicle, {10}Vehicle)",

                    ParameterNames.PreProdProgam,

                    ParameterNames.HardwareStage,

                    ParameterNames.PartNumber,

                    ParameterNames.Dls,

                    ParameterNames.Pls,

                    ParameterNames.Upc,

                    ParameterNames.Fna,

                    ParameterNames.FnaDescription,

                    ParameterNames.UpcPrefix,

                    ParameterNames.EngineerCodes,

                    ParameterNames.EngineerLastNames);

            }

        }

​

        #endregion

​

        #region IDataAccessLayer

​

        public interface IDataAccessLayer

        {

            Item NewItem(string itemTypeName, string action);

​

            Item GetResult(string sql, List<System.Data.SqlClient.SqlParameter> sqlParameters, string preprodProgramName, string hardwareStage);

​

            Item ApplySql(string sql);

        }

​

        #endregion

​

        #region DataAccessLayer

​

        public class DataAccessLayer : IDataAccessLayer

        {

            private readonly Innovator _innovator;

            private const string QueryPpatResult = "gm_Query PPAT Result";

            private const string QueryPpatSearch = "gm_Query PPAT";

            private readonly Dictionary<string, string> _propertyMap = new Dictionary<string, string>()

            {

                {"_asm_stn","ASM_STN"},

                {"_current_part_number","PART_Number"},

                {"_current_part_number_dls","DLS"},

                {"_current_part_number_pls","PLS"},

                {"_upc_prefix","UPC_Prefix"},

                {"_upc_cde","UPC"},

                {"_fna_cde","FNA"},

                {"_fna_desc","fna_desc"},

                {"_part_usage_qty","QTY"},

                {"_indent_level","Indent_Level"},

                {"_sequence_number","SEQ_Number"},

                {"_engineer_code","Engr_Code_LIST"},

                {"_prod_yr","ModelYear"},

                {"_prod_line_cde","Product"},

                {"_dynamic_torque_code","Torque_D"},

                {"_static_torque_cde","Torque_S"},

                {"_permanent_notes","PermNotes"},

                {"_part_fstnr_ind_cde","FastenerInd"},

                {"_fna_mdf_desc","FNM"},

                {"_bld_blk_phase","BlockPhase"},

                {"_calc","CALC"},

                {"_control_number","ControlNumber"},

                {"_hand_cde","HandCode"},

                {"_sys_bom_item_no","item"},

                {"_less_finish_indicator","lessFinish"},

                {"_usage_st_cde","Status"},

                {"_mdl_cde","ModelCode"},

                {"_no_of_units_qty","NOU"},

                {"_unit_msre_cde","UOM"},

                {"_div_cde","EngrRelDiv"},

                {"_vpps_cde","VPPS"}

            };

​

            public DataAccessLayer(Innovator innovator)

            {

                _innovator = innovator;

            }

​

            public Item NewItem(string itemTypeName, string action)

            {

                return _innovator.newItem(itemTypeName, action);

            }

​

            public Item GetResult(string sql, List<System.Data.SqlClient.SqlParameter> sqlParameters, string preprodProgramName, string hardwareStage)

            {

                var querySearch = _innovator.newItem(QueryPpatSearch);

​

                using (var conn = new System.Data.SqlClient.SqlConnection())

                {

                    conn.ConnectionString = GetConnectionString();

                    conn.Open();

                    using (var command = new System.Data.SqlClient.SqlCommand())

                    {

                        command.Connection = conn;

                        command.Parameters.AddRange(sqlParameters.ToArray());

                        command.CommandText = sql;

                        command.CommandTimeout = 0;

                        var query = sql;

                        foreach (System.Data.SqlClient.SqlParameter commandParameter in command.Parameters)

                        {

                            query = query.Replace(commandParameter.ParameterName, "'" + commandParameter.Value + "'");

                        }

​

                        if(System.Diagnostics.Debugger.Launch())

                            System.Diagnostics.Debugger.Break();

​

                        using (var reader = command.ExecuteReader())

                        {

                            while (reader.Read())

                            {

                                // Read the item and put into AML

                                querySearch.addRelationship(GetItem(reader, preprodProgramName, hardwareStage));

                            }

                        } // reader disposed

                    } // command disposed

                } // connection disposed

​

                return querySearch;

            }

​

            public Item ApplySql(string sql)

            {

                return _innovator.applySQL(sql);

            }

​

            /// <summary>

            /// Returns Torque Notes, Perm Notes from the perm notes received from Embassy

            /// </summary>

            private List<string> GetPermanentAndTorqueNotes(string permNotes)

            {