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 } }