jdescopingtool/OLD/WorkerService/Process/LotFinderDBExt.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Dapper;
using DataModel.Helpers;
using DataModel.Models;
using DataModel.Process;
using WorkerService.Helpers;
using WorkerService.Models.Reporting;

namespace WorkerService.Process
{
    /// <summary>
    /// Worker service-specific functionality for LotFinderDB interface
    /// </summary>
    public class LotFinderDBExt : LotFinderDB
    {
        /// <summary>
        ///  Query to get next queued search
        /// </summary>
        private const string SQL_GET_NEXT_SEARCH = @"
		  SELECT  TOP 1 s.ID,
				s.UserName,
				s.Name,
				s.Status,
				s.SubmitDT,
				s.StartDT,
				s.EndDT,
				s.Criteria AS CriteriaJSON
		  FROM    dbo.Search s
		  WHERE   s.Status = 1
		  ORDER BY s.SubmitDT";

        /// <summary>
        /// Gets next queued search
        /// </summary>
        /// <returns>Next queue search</returns>
        public static Search GetNextSearch()
        {
            Search nextSearch = null;

            try
            {
                using (SqlConnection connection = GetConnection())
                {
                    nextSearch = connection.QueryFirstOrDefault<Search>(SQL_GET_NEXT_SEARCH);

                    if (nextSearch != null && !string.IsNullOrEmpty(nextSearch.CriteriaJSON))
                    {
                        nextSearch.Criteria = JsonHelpers.FromJSON<SearchCriteria>(nextSearch.CriteriaJSON);
                    }
                }
            }
            catch (Exception error)
            {
                //Log but do not forward error
                logger.Error("GetNextSearch: failed to get next queued search: {0}.", error.Message);
            }

            return nextSearch;
        }

        /// <summary>
        /// Resets the status of partially completed searches
        /// </summary>
        public static void ResetPartialSearches()
        {
            try
            {
                using (SqlConnection connection = GetConnection())
                {
                    connection.Execute("ResetPartialSearches", commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception error)
            {
                //Log but do not forward error
                logger.Error("ResetPartialSearches: failed to reset partial searches: {0}.", error.Message);
            }
        }

        /// <summary>
        /// Updates the status of the search to 'Start'
        /// </summary>
        /// <param name="search">Search to start</param>
        public static void StartSearch(Search search)
        {
            try
            {
                search.Status = SearchStatus.Started;
                search.StartDT = DateTime.Now;

                using (SqlConnection connection = GetConnection())
                {
                    connection.Execute("StartSearch", new { p_SearchID = search.ID }, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception error)
            {
                //Log but do not forward error
                logger.Error("StartSearch: failed to mark search as started: {0}.", error.Message);
            }
        }

        /// <summary>
        /// Update the status of the search to 'Complete' and stores the results
        /// </summary>
        /// <param name="search">Search to complete</param>
        /// <param name="wasSuccessful">Whether or not the search was successful</param>
        public static void CompleteSearch(Search search, bool wasSuccessful)
        {
            try
            {
                search.Status = wasSuccessful ? SearchStatus.Ended : SearchStatus.Error;

                using (SqlConnection connection = GetConnection())
                {
                    connection.Execute("CompleteSearch", new { p_SearchID = search.ID, p_WasSuccessful = wasSuccessful, p_Results = search.Results }, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception error)
            {
                //Log but do not forward error
                logger.Error("CompleteSearch: failed to mark search as completed: {0}.", error.Message);
            }
        }

        /// <summary>
        /// Performs search and extracts results into given model
        /// </summary>
        /// <param name="searchModel">Search parameter & result model</param>
        public static void Search(SearchModel searchModel)
        {
            try
            {
                //Get configured timeout
                int queryTimeout = 600;
                try
                {
                    string queryTimeoutStr = ConfigurationManager.AppSettings["querytimeout"];
                    queryTimeout = int.Parse(queryTimeoutStr);
                }
                catch
                {
                    //Ignore
                }

                //Generate query to execute
                string query = searchModel.GetQuery();
                File.WriteAllText($"search_{searchModel.ID}.sql", query);

                searchModel.StartDT = DateTime.Now;
                using (SqlConnection connection = GetConnection())
                {
                    var results = connection.QueryMultiple(query, new
                    {
                        p_MinimumDT = searchModel.MinimumDT,
                        p_MaximumDT = searchModel.MaximumDT,
                        p_WorkOrderFilter = searchModel.CreateWorkOrderFilterParameter(),
                        p_ItemNumberFilter = searchModel.CreateItemNumberFilterParameter(),
                        p_ProfitCenterFilter = searchModel.CreateProfitCenterFilterParameter(),
                        p_WorkCenterFilter = searchModel.CreateWorkCenterFilterParameter(),
                        p_ComponentLotFilter = searchModel.CreateComponentLotFilterParameter(),
                        p_OperatorFilter = searchModel.CreateOperatorFilterParameter(),
                        p_ItemOperationMisFilter = searchModel.CreateItemOperationMisFilterParameter(),
                        p_ExtractMisData = searchModel.ExtractMisData
                    }, commandTimeout: queryTimeout);

                    //Parse search results
                    searchModel.Results.AddRange(results.Read<SearchResult>());

                    //Parse MIS data if extracted
                    if (searchModel.ExtractMisData)
                    {
                        searchModel.MisResults.AddRange(results.Read<MisSearchResult>());
                        searchModel.MisNonMatchResults.AddRange(results.Read<MisNonMatchSearchResult>());
                    }
                }
                searchModel.EndDT = DateTime.Now;
            }
            catch (Exception error)
            {
                //Log but do not forward error
                logger.Error("Search: failed to perform search: {0}.", error.Message);
                throw;
            }
        }
    }
}