jdescopingtool/OLD/DataModel/Process/LotFinderDB.ProfitCenter.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using DataModel.Models;

namespace DataModel.Process
{
    /// <summary>
    /// Profit center tracking functions for LotFinderDB interface
    /// </summary>
    public partial class LotFinderDB
    {
        /// <summary>
        /// Query to find profit centers with matching code or description
        /// </summary>
        private const string SQL_SEARCH_PROFIT_CENTERS = @"
            SELECT TOP 25
                   pc.Code,
                   pc.Description,
                   pc.LastUpdateDT
            FROM   dbo.ProfitCenter AS pc
            WHERE  pc.Code LIKE '%' + @filter + '%' OR
                   pc.Description LIKE '%' + @filter + '%'
            ORDER BY pc.Code";

        /// <summary>
        /// Finds profit centers with matching code or description
        /// </summary>
        /// <param name="filter">Search filter</param>
        /// <returns>Profit centers with matching code or description</returns>
        public static List<ProfitCenter> SearchProfitCenters(string filter)
        {
            List<ProfitCenter> results = new List<ProfitCenter>();

            try
            {
                using (SqlConnection connection = GetConnection())
                {
                    results.AddRange(connection.Query<ProfitCenter>(SQL_SEARCH_PROFIT_CENTERS, new { filter }));
                }
            }
            catch (Exception error)
            {
                //Log and forward exception
                logger.Error("SearchProfitCenters: failed to search for profit centers '{0}': {1}.", filter, error.Message);
                throw new Exception($"Failed to search for profit centers '{filter}' from LotFinderDB.", error);
            }

            return results;
        }

        /// <summary>
        /// Query to find profit centers with matching profit center codes
        /// </summary>
        private const string SQL_LOOKUP_PROFIT_CENTERS = @"
             SELECT pc.Code,
                    pc.Description,
                    pc.LastUpdateDT
             FROM   dbo.ProfitCenter AS pc INNER JOIN
                    @profitCenterCodes AS pc2 ON (pc.Code = pc2.Code)
             ORDER BY pc.Code";

        /// <summary>
        /// Finds profit centers with matching profit center codes
        /// </summary>
        /// <param name="profitCenterCodes">Profit center codes to match</param>
        /// <returns>List of matching profit centers</returns>
        public static List<ProfitCenter> LookupProfitCenters(List<string> profitCenterCodes)
        {
            List<ProfitCenter> results = new List<ProfitCenter>();

            try
            {
                //Create search filter parameter
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("Code", typeof(string));
                foreach (string profitCenter in profitCenterCodes)
                {
                    dataTable.Rows.Add(profitCenter);
                }

                using (SqlConnection connection = GetConnection())
                {
                    results = connection.Query<ProfitCenter>(SQL_LOOKUP_PROFIT_CENTERS, new { profitCenterCodes = dataTable.AsTableValuedParameter("ProfitCenterFilterParameter") }).ToList();
                }
            }
            catch (Exception error)
            {
                //Log and forward exception
                logger.Error("LookupProfitCenters: failed to find matching profit centers: {0}.", error.Message);
                throw new Exception("LookupProfitCenters: failed to find matching profit centers in LotFinderDB.", error);
            }

            return results;
        }
    }
}