using PharmaStoreReport.Common.Interface;
using PharmaStoreReport.NLogConfig.Logging;
using PharmaStoreReport.Services.Interface;
using PharmaStoreReport.SqlHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace PharmaStoreReport.Services
{
    public class AmsScriptFigureService : IAmsScriptFigureService
    {
        #region ScriptSheet
        /// <summary>
        /// Member initionalized
        /// </summary>
        private readonly IConnections _connections;
        private readonly ICaluclations _caluclations;
        private readonly IUtility _utility;
        private readonly ILog _log;
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connections"></param>
        /// <param name="caluclations"></param>
        /// <param name="utility"></param>
        /// <param name="log"></param>
        public AmsScriptFigureService(IConnections connections, ICaluclations caluclations, IUtility utility, LogNLog log)
        {
            this._connections = connections;
            this._caluclations = caluclations;
            this._utility = utility;
            this._log = log;
        }
        /// <summary>
        /// GetScriptSheet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public DataTable GetScriptSheet(string fileName)
        {
            DataTable dt = new DataTable();
            try
            {
                dt = _connections.GetSheetData(fileName, ".xlsx", "Scripts");
            }
            catch (Exception ex)
            {
                _log.LogException("GetScriptSheet", "GetScriptSheet", ex);
            }
            return dt;
        }
        /// <summary>
        /// GetCWHScriptFigureData
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="healthNotes"></param>
        /// <returns></returns>
        public DataTable GetCWHScriptFigureData(string fileName, DataTable healthNotes)
        {
            DataTable dt = new DataTable();
            try
            {
                DataTable AllFilteredData = new DataTable();
                DataTable cwhScript = new DataTable();
                DataTable ScriptFigureSheet = new DataTable();
                ScriptFigureSheet = GetScriptSheet(fileName);
                if (ScriptFigureSheet != null && ScriptFigureSheet.Rows.Count > 0)
                {
                    cwhScript = ScriptFigureSheet.Select("[Scripts Actual] LIKE 'Chemist Warehouse%'").CopyToDataTable();
                    DataTable cwhFilterData = _utility.RemoveBrandHeaderFromDT("Chemist Warehouse", cwhScript);
                    //changing Pharmacy Names in datatables
                    var filteredRows = from q in cwhFilterData.AsEnumerable()
                                       where q.Field<string>("Pharmacy Name") == "Chemist Warehouse Adelaide Street Brisbane"
                                       select q;
                    if (filteredRows != null)
                    {
                        for (int rowIndex = 0; rowIndex < cwhFilterData.Rows.Count; rowIndex++)
                        {
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Brisbane Adelaide Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Adelaide Street Brisbane";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Altona Gate Shopping Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Altona Gate S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Ascot Vale Union Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Ascot Vale Union Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Ascot Vale Union Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Ascot Vale Union Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Balmain Darling Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Balmain";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Port Macquarie - Bay Park Plaza").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Bay Park Plaza Port Macquarie";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Belmont").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Belmont D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Birkenhead Point S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Birkenhead";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Box Hill Whitehorse").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Whitehorse Box Hill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Boxhill").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Box Hill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Campbellfield DC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Campbellfield D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Carnegie - Koornang Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Carnegie - Koornang Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Chadstone SC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Chadstone S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Geelong Corio").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Corio D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Geelong Corio Central S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Corio Central S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Cranbourne North - The Avenue").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Cranbourne North";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Cranbourne North - Eve Central").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Cranbourne North - Eve Central Shopping Centre";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Cranbourne Springhill SC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Cranbourne Springhill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Derrimut Distribution Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Derrimut D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Eltham Village SC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Eltham Village S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Epping Pacific").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Epping Pacific S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Armadale Forrest Road GP Clinic").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Forrest Road Armadale";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Fortitude Valley").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Fortitude Valley Emporium";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Glenquarie S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Glenquarie";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Chadstone DC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Chadstone D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Harbourtown").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Harbour Town";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Essendon Mt Alexander Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Essendon Mt Alexander Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Perth Hay Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Hay Street Perth";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Sydney Haymarket").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Haymarket";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Hurstville Forest Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Hurstville Forest Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Ipswich Centre Plaza").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Ipswich central";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Lake Macquarie Fair").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Lake Macquarie";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Racecourse").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Launceston Racecourse";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Logan").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Logan Central";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Melbourne - Manchester Unity").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Manchester Unity Building";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Northlakes - Mango Hill").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Mango Hill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Maribyrnong").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Maribyrnong D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Melbourne Paramount Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Melbourne Paramount";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Sydney MidCity").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Mid City Sydney";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Mildura DC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Mildura D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Brisbane Upper Queen Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Upper Queen St Brisbane";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Morley").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Morley Coventry Village";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Munno Para").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Munno Para";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Newtown NSW").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Newtown (NSW)";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Newtown TAS").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Newtown (TAS)";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Northlakes - Westfield East").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse North Lakes East";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Northlakes - Westfield West").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse North Lakes West";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Northlakes - Home Co.").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Northlakes Home Co";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Northland Homemaker Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Northland Homemaker Centre DC";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Nowra Junction Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Nowra Junction St";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Oakleigh Links S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Oakleigh The Links";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Pakenham Main Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Pakenham Main St";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Parabanks SC Salisbury").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Parabanks S/C Salisbury";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Penrith Distribution Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Penrith D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Pimpama City SC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Pimpama City S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Polaris").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Polaris Shopping Centre";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Port Central S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Port Central SC";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Redcliffe").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Redcliff";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Richmond - Swan Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Richmond - Swan St";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Smithfield").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Smithfield";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse St Kilda").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse St. Kilda";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Summerhill Village").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Summerhill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Sunbury Gap Rd").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Sunbury Gap Road";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Sydney - George Street").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Sydney George Street";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Sydney Pitt St").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Sydney Pitt Street";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Tarneit Gardens S/C").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Tarneit Gardens";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Victoria Gardens Shopping Centre").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Victoria Gardens S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Wangaratta DC").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Wangaratta D.C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Fountain Gate Westfield").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Westfield Fountain Gate";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Hornsby Westfield").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Westfield Hornsby";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Knox Westfield").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Westfield Knox";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Box Hill Whitehorse").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Whitehorse Box Hill";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Whitford").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Whitford City";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Wollongong Central").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "Chemist Warehouse Wollongong Central S/C";
                            }
                            if (cwhFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chemist Warehouse Gladstone Home").ToLower())
                            {
                                cwhFilterData.Rows[rowIndex][0] = "CWH Home Gladstone";
                            }
                        }
                    }
                    DataTable dtCWHMerged = getLinq(healthNotes, cwhFilterData);
                    DataTable dtScpirtMerged = getDataLinq(cwhFilterData, healthNotes);
                    DataTable dtheathorderby = dtScpirtMerged.AsEnumerable().OrderBy(x => x.Field<string>("Scripts Actual")).CopyToDataTable();
                    DataTable dtcwhFilterData = dtCWHMerged.AsEnumerable().OrderBy(x => x.Field<string>("Pharmacy Name")).CopyToDataTable();
                    dt = _caluclations.CalculateScriptFigure(dtCWHMerged, dtScpirtMerged);
                }
            }
            catch (Exception ex)
            {
                _log.LogException("CalculateScriptFigure", "CalculateScriptFigure", ex);
            }
            return dt;
        }
        /// <summary>
        /// GetMYCScriptFigureData
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="healthNotes"></param>
        /// <returns></returns>
        public DataTable GetMYCScriptFigureData(string fileName, DataTable healthNotes)
        {
            DataTable dtScpirtMerged = new DataTable();
            try
            {
                DataTable mycScript = new DataTable();
                DataTable ScriptFigureSheet = new DataTable();
                ScriptFigureSheet = GetScriptSheet(fileName);
                if (ScriptFigureSheet != null && ScriptFigureSheet.Rows.Count > 0)
                {
                    mycScript = ScriptFigureSheet.Select("[Scripts Actual] LIKE 'My Chemist%'").CopyToDataTable();
                    DataTable mycFilterData = _utility.RemoveBrandHeaderFromDT("My Chemist", ScriptFigureSheet);
                    for (int rowIndex = 0; rowIndex < mycFilterData.Rows.Count; rowIndex++)
                    {
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Midvalley").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Midvalley SC";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Castle Towers").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Castle Towers Shopping Centre";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Chadstone Health & Beauty").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Chadstone H & B";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Epping NSW").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Epping";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Moonee Ponds Banias").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Moonee Ponds";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Northland Health & Beauty").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Northland H & B";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Market Square").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Market Sq Geelong";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Doncaster - Dontown").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Dontown";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Broadway").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Broadway Reservoir";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Northland Gance").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Northland";
                        }
                        if (mycFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("My Chemist Elizabeth St").ToLower())
                        {
                            mycFilterData.Rows[rowIndex][0] = "My Chemist Elizabeth Street";
                        }
                    }
                    DataTable dtMycMerged = getLinq(healthNotes, mycFilterData);
                    dtScpirtMerged = getDataLinq(mycFilterData, healthNotes);
                    dtScpirtMerged = _caluclations.CalculateScriptFigure(dtMycMerged, dtScpirtMerged);
                }
            }
            catch (Exception ex)
            {
                _log.LogException("CalculateScriptFigure", "CalculateScriptFigure", ex);
            }
            return dtScpirtMerged;
        }
        /// <summary>
        /// GetOtherScriptFigureData
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="healthNotes"></param>
        /// <returns></returns>
        public DataTable GetOtherScriptFigureData(string fileName, DataTable healthNotes)
        {
            DataTable dtScpirtMerged = new DataTable();
            try
            {
                DataTable OtherScript = new DataTable();
                DataTable ScriptFigureSheet = new DataTable();
                ScriptFigureSheet = GetScriptSheet(fileName);
                if (ScriptFigureSheet != null && ScriptFigureSheet.Rows.Count > 0)
                {
                    DataTable otherFilterData = _utility.RemoveAllBrandHeaderFromDT("Chemist Warehouse%", ScriptFigureSheet);
                    otherFilterData = _utility.RemoveAllBrandHeaderFromDT("My Chemist%", ScriptFigureSheet);
                    otherFilterData = _utility.RemoveAllBrandHeaderFromDT("Beauty Spot%", ScriptFigureSheet);
                    var filteredRows = from q in healthNotes.AsEnumerable()
                                       where q.Field<string>("Pharmacy Name") == "Brookvale Chemist (CWH)"
                                       select q;
                    if (filteredRows != null)
                    {
                        for (int rowIndex = 0; rowIndex < healthNotes.Rows.Count; rowIndex++)
                        {
                            if (healthNotes.Rows[rowIndex][0].ToString() == "Brookvale Chemist (CWH)")
                            {
                                healthNotes.Rows[rowIndex][0] = "Brookvale Chemist";
                            }
                        }
                    }
                    for (int rowIndex = 0; rowIndex < otherFilterData.Rows.Count; rowIndex++)
                    {
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Churchil Avenue Pharmacy & Post Office").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Churchill Avenue Pharmacy";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Helen Street Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Helen St Pharmacy";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Sydney Olympic Park Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Sydney Olympic Park Pharmacy (CWH)";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Mitchell Street Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Mitchel St Pharmacy";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Chetwynd Rd Neighbourhood Shop & Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Chetwynd Road Neighbourhood Shop & Pharmacy";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Waterloo Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Waterloo Pharmacy (CWH)";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Wasley Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Wasley Pharmacy (CWH)";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Discount Chemist Langwarrin").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Discount Centre Langwarrin (CWH)";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Healthline Pharmacy York Street").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Healthline Pharmacy York St";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Heidelberg Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Heidelberg Pharmacy (CWH)";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Munster Street Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Munster Street Pharmacy";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Lower Plenty Pharmacy").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Lower Plenty";
                        }
                        if (otherFilterData.Rows[rowIndex][0].ToString().Trim().ToLower() == Convert.ToString("Taree Manning Street Chemist").ToLower())
                        {
                            otherFilterData.Rows[rowIndex][0] = "Taree Manning St Chemist";
                        }
                    }
                    foreach (var item in _utility.GetStates())
                    {
                        otherFilterData = _utility.RemoveAllBrandHeaderFromDT(item, ScriptFigureSheet);
                    }
                    otherFilterData = _utility.RemoveBrandHeaderFromDT("Generic", ScriptFigureSheet);
                    DataTable dtOtherMerged = getLinq(healthNotes, otherFilterData);
                    dtScpirtMerged = getDataLinq(otherFilterData, healthNotes);
                    dtScpirtMerged = _caluclations.CalculateScriptFigure(dtOtherMerged, dtScpirtMerged);
                }
            }
            catch (Exception ex)
            {
                _log.LogException("GetOtherScriptFigureData", "GetOtherScriptFigureData", ex);
            }
            return dtScpirtMerged;
        }
        /// <summary>
        /// Get Linq data
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataTable getLinq(DataTable dt1, DataTable dt2)
        {
            DataTable diffdat = new DataTable();
            diffdat.Columns.Add("Pharmacy Name", typeof(string));
            diffdat.Columns.Add("State", typeof(string));
            diffdat.Columns.Add("Sms", typeof(string));
            IEnumerable<string> idsInDataTableA = dt1.AsEnumerable().Select(row => (string)row["Pharmacy Name"].ToString().ToLower().Trim());
            IEnumerable<string> idsInDataTableB = dt2.AsEnumerable().Select(row => (string)row["Scripts Actual"].ToString().ToLower().Trim());
            IEnumerable<string> difference = idsInDataTableA.Except(idsInDataTableB);
            foreach (var i in difference)
            {
                try
                {
                    DataRow r = diffdat.Rows.Add();
                    var data = dt1.AsEnumerable().Where(y => y.Field<string>("Pharmacy Name").ToLower().Trim() == i.ToString().ToLower().Trim()).FirstOrDefault();
                    r.SetField(0, data.ItemArray[0]);
                    r.SetField(1, data.ItemArray[1]);
                    r.SetField(2, data.ItemArray[2]);
                }
                catch (Exception ex)
                {
                    _log.LogException("GetData", "GetData", ex);
                }
            }
            if (dt1.Rows.Count > 0 && dt2.Rows.Count > 0)
            {
                var CWHdata = (from a in dt1.AsEnumerable()
                               join b in dt2.AsEnumerable()
                               on a["Pharmacy Name"].ToString().ToLower().Trim() equals b["Scripts Actual"].ToString().ToLower().Trim()
                               into g
                               where g.Count() > 0
                               orderby a.Field<string>("Pharmacy Name")
                               select a);
                if (CWHdata.Any())
                {
                    DataTable dt = CWHdata.CopyToDataTable();
                    dt.Merge(diffdat);
                    dt.AcceptChanges();
                    return dt;
                }
            }
            return new DataTable();
        }
        /// <summary>
        /// Get data by linq query
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public DataTable getDataLinq(DataTable dt1, DataTable dt2)
        {
            DataTable diffdat = new DataTable();
            diffdat.Columns.Add("Scripts Actual", typeof(string));
            diffdat.Columns.Add("NoName", typeof(Double));
            diffdat.Columns.Add("F3", typeof(Double));
            IEnumerable<string> idsInDataTableA = dt2.AsEnumerable().Select(row => (string)row["Pharmacy Name"].ToString().ToLower().Trim());
            IEnumerable<string> idsInDataTableB = dt1.AsEnumerable().Select(row => (string)row["Scripts Actual"].ToString().ToLower().Trim());
            IEnumerable<string> difference = idsInDataTableA.Except(idsInDataTableB);
            foreach (var i in difference)
            {
                try
                {
                    DataRow r = diffdat.Rows.Add();
                    var data = dt2.AsEnumerable().Where(y => y.Field<string>("Pharmacy Name").ToLower().Trim() == i.ToString().ToLower().Trim()).FirstOrDefault();
                    r.SetField(0, data.ItemArray[0]);
                    r.SetField(1, 0);
                    r.SetField(2, 0);
                }
                catch (Exception ex)
                {
                    _log.LogException("GetSheetNameFromConfig", "GetSheetNameFromConfig", ex);
                }
            }
            if (dt1.Rows.Count > 0 && dt2.Rows.Count > 0)
            {
                var dataScpirt = (from a in dt1.AsEnumerable()
                                  join b in dt2.AsEnumerable()
                                  on a["Scripts Actual"].ToString().ToLower().Trim() equals b["Pharmacy Name"].ToString().ToLower().Trim()
                                  into g
                                  where g.Count() > 0
                                  orderby a.Field<string>("Scripts Actual")
                                  select a);

                if (dataScpirt.Any())
                {
                    DataTable dt = dataScpirt.CopyToDataTable();
                    dt.Merge(diffdat);
                    dt.AcceptChanges();
                    return dt;
                }
            }
            return new DataTable();
        }
        /// <summary>
        /// Get Stript Figure Data
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public DataTable GetScriptFigureDta(string fileName)
        {
            return _connections.GetSheetData(fileName, ".xlsx", "Scripts");
        }
        #endregion
    }
}