using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Infragistics.Excel;
using DCS.Contract.BLL;
using System.Collections;
using System.IO;

namespace DCS.Contract.Reports
{
    public static class CostPricing
    {
        public static void createPriceSheets(BLL.Contract contract, String filePath)
        {
            App _app = (App)System.Windows.Application.Current;
            List<Plugin.IPluginCostBasis> costBasisPlugins = new List<DCS.Contract.Plugin.IPluginCostBasis>();

            //Get cost basis plugins
            foreach (Plugin.Plugin p in _app.PluginsCost)
            {
                Plugin.IPluginCostBasis pc = (Plugin.IPluginCostBasis)p.CreateInstance();
                costBasisPlugins.Add(pc);
            }

            Workbook wb = new Workbook(WorkbookFormat.Excel97To2003);

            //flatten costs into one collection
            ContractTaskCostAmtCollection allCosts = new ContractTaskCostAmtCollection();

            foreach (ContractTask t in contract.Tasks)
                foreach (ContractTaskCostAmt c in t.Costs)
                    allCosts.Add(c);

            //List<int> contractYears = new List<int>();
            //for (int y = contract.PoPStart.Value.Year; y <= contract.PoPEnd.Value.Year; y++)
            //    contractYears.Add(y);

            //Header Row indexes
            int headerRow = 3;
            int fringeHdrRow = 2;
            int siteHdrRow = 1;
            int yearHdrRow = 0;

            int currPeriodNum = 1;
            int currCellIdx = 0;

            foreach (Region r in contract.Regions)
            {
                int baseRateColIdx = 0;
                Worksheet ws = wb.Worksheets.Add(r.Name);

                //Create Header
                ws.Rows[headerRow].Cells[0].Value = String.Format("{0}\r\nLabor Category", contract.Agency.Name);
                ws.Rows[headerRow].Cells[1].Value = "DCS\r\nLabor Category";
                currCellIdx = 2;

                //Past Performances Header
                Dictionary<string, int> ContractNameToColMap = new Dictionary<string, int>();

                foreach (ContractTaskCostAmt c in allCosts)
                {
                    if (c.Region == r)
                    {
                        if (c.Task.PastPerformances == null)
                        {
                            c.Task.PastPerformances = Plugins.Accounting.GetPastPerformances(c.Task.ID);
                        }

                        foreach (object p in c.Task.PastPerformances)
                        {
                            Plugin.IPastPerformanceView pv = p as Plugin.IPastPerformanceView;

                            if (pv != null)
                            {
                                string pastContractName = pv.getPastPerformanceName();

                                if (!string.IsNullOrEmpty(pastContractName) && !ContractNameToColMap.ContainsKey(pastContractName))
                                {
                                    ContractNameToColMap.Add(pastContractName, currCellIdx);
                                    ws.Rows[headerRow].Cells[currCellIdx].Value = pastContractName;
                                    currCellIdx++;
                                }

                            }
                        }
                    }
                }
                //TODO: Web Labor Cat
                Dictionary<string, int> costBasisPluginColDic = new Dictionary<string, int>();

                foreach (var p in costBasisPlugins)
                {
                    List<String> cats = p.getCategories(contract.ID, r.ID);

                    foreach (string c in cats)
                    {
                        if (!costBasisPluginColDic.ContainsKey(c))
                        {
                            //add category and save index value
                            ws.Rows[headerRow].Cells[currCellIdx].Value = c;
                            costBasisPluginColDic.Add(c, currCellIdx);
                            currCellIdx++;
                        }
                    }
                }


                baseRateColIdx = currCellIdx;
                //currCellIdx++;

                int yearStartCellIdx = currCellIdx;
                String PrevYrEsc = string.Empty;
                String PrevYrBaseRateOffSite = string.Empty;
                String PrevYrBaseRateOnsite = string.Empty;
                String PrevYrEscDollarsOnsite = string.Empty;
                String PrevYrEscDollarsOffsite = string.Empty;

                foreach (ContractPeriod p in contract.Periods)
                {
                    currPeriodNum = contract.Periods.IndexOf(p) + 1;

                    if (r.Site != BLL.Common.Enum.SiteType.OffSite)
                    {
                        WorksheetMergedCellsRegion onsiteHdr = ws.MergedCellsRegions.Add(siteHdrRow, currCellIdx, siteHdrRow, currCellIdx + 6);
                        onsiteHdr.Value = "On-Site";
                        onsiteHdr.CellFormat.Alignment = HorizontalCellAlignment.Center;
                        onsiteHdr.CellFormat.FillPatternForegroundColor = System.Drawing.Color.AntiqueWhite;
                        onsiteHdr.CellFormat.FillPattern = FillPatternStyle.Solid;
                        onsiteHdr.CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        onsiteHdr.CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        onsiteHdr.CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        onsiteHdr.CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Base Rate";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                        currCellIdx++;

                        var rate = (from t in contract.TaskBilling
                                    where t.Period.Equals(p)
                                    select t).FirstOrDefault();

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Fringe / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Fringe";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Overhead / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Overhead";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.GeneralAdmin / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "G&A";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Fee / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Yellow;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Fee";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Escalation / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Yellow;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Escalation";
                        currCellIdx++;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Billing Rate";
                        ws.Rows[headerRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        currCellIdx++;
                    }

                    if (r.Site != BLL.Common.Enum.SiteType.OnSite)
                    {
                        WorksheetMergedCellsRegion onsiteHdr = ws.MergedCellsRegions.Add(siteHdrRow, currCellIdx, siteHdrRow, currCellIdx + 6);
                        onsiteHdr.Value = "Off-Site";
                        onsiteHdr.CellFormat.Alignment = HorizontalCellAlignment.Center;
                        onsiteHdr.CellFormat.FillPatternForegroundColor = System.Drawing.Color.Beige;
                        onsiteHdr.CellFormat.FillPattern = FillPatternStyle.Solid;
                        onsiteHdr.CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        onsiteHdr.CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        onsiteHdr.CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        onsiteHdr.CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Base Rate";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                        currCellIdx++;

                        var rate = (from t in r.OffSiteBillings
                                    where t.Period.Equals(p)
                                    select t).FirstOrDefault();

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Fringe / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Fringe";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Overhead / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Overhead";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.GeneralAdmin / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Orange;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "G&A";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Fee / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Yellow;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Fee";
                        currCellIdx++;

                        ws.Rows[fringeHdrRow].Cells[currCellIdx].Value = rate.Escalation / 100;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FormatString = "0.00%";
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Yellow;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Escalation";
                        currCellIdx++;

                        ws.Rows[headerRow].Cells[currCellIdx].Value = "Billing Rate";
                        ws.Rows[headerRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        ws.Rows[fringeHdrRow].Cells[currCellIdx].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                        currCellIdx++;
                    }

                    //Setup Column Header Format
                    for (int i = 0; i < currCellIdx; i++)
                    {
                        ws.Rows[headerRow].Cells[i].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
                        ws.Rows[headerRow].Cells[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        ws.Rows[headerRow].Cells[i].CellFormat.FillPatternForegroundColor = System.Drawing.Color.LightSkyBlue;
                        ws.Rows[headerRow].Cells[i].CellFormat.FillPattern = FillPatternStyle.Solid;
                        ws.Rows[headerRow].Cells[i].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[headerRow].Cells[i].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;

                        ws.Rows[headerRow].Cells[i].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;
                        ws.Rows[headerRow].Cells[i].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                        if (ws.Rows[headerRow].Cells[i].Value.ToString() != "Billing Rate")
                            ws.Rows[headerRow].Cells[i].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                    }

                    ws.Rows[headerRow].Cells[baseRateColIdx].CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                    ws.Rows[headerRow].Height = 255 * 3;  //default hieght of excel row is 12.75 points which equates to 255 twips

                    //Year Header
                    WorksheetMergedCellsRegion yearMergedCells = ws.MergedCellsRegions.Add(yearHdrRow, yearStartCellIdx, yearHdrRow, currCellIdx - 1);
                    yearMergedCells.Value = string.Format("{0}: {1} - {2}", p.PeriodName, p.DateStart.ToString("d"), p.DateEnd.ToString("d"));
                    yearMergedCells.CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                    yearMergedCells.CellFormat.FillPattern = FillPatternStyle.Solid;
                    yearMergedCells.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                    yearMergedCells.CellFormat.Alignment = HorizontalCellAlignment.Center;
                    yearMergedCells.CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                    yearMergedCells.CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;

                    int fringeCol = yearStartCellIdx;

                    //setup start index for next year
                    yearStartCellIdx = currCellIdx;

                    //data
                    int currDataRow = headerRow + 1;

                    int currDataCol;


                    #region Create Cost View
                    var costs = from c in allCosts
                                where c.Region == r && c.Period.Equals(p)
                                select c;
                    List<Views.LaborCostView> lcvs = new List<DCS.Contract.Views.LaborCostView>();

                    foreach (ContractTaskCostAmt cost in costs)
                    {
                        Views.LaborCostView view = new DCS.Contract.Views.LaborCostView();
                        view.AgentLabCat = cost.Task.LaborCategory.Title;
                        view.taskID = cost.Task.ID;

                        if (lcvs.Contains(view))
                        {
                            view = lcvs[lcvs.IndexOf(view)];
                        }
                        else
                        {
                            view.BaseRate = cost.Cost;

                            if (cost.Task.LaborCategory.ContractorLaborCat != null)
                                view.ContractorLabCat = cost.Task.LaborCategory.ContractorLaborCat.Title;

                            lcvs.Add(view);
                        }

                        if (cost.Onsite)
                        {
                            view.OnsiteEscalation = cost.EscalationDollars;
                            view.OnsiteFringe = cost.FringeDollars;
                            view.OnsiteOverhead = cost.OverheadDollars;
                            view.OnsiteGA = cost.GeneralAdminDollars;
                            view.OnsiteFee = cost.FeeDollars;
                            view.OnsiteBillRate = cost.Bill;
                        }
                        else
                        {
                            view.OffsiteEscalation = cost.EscalationDollars;
                            view.OffsiteFringe = cost.FringeDollars;
                            view.OffsiteOverhead = cost.OverheadDollars;
                            view.OffsiteGA = cost.GeneralAdminDollars;
                            view.OffsiteFee = cost.FeeDollars;
                            view.OffsiteBillRate = cost.Bill;
                        }
                    }

                    //done creating view now iterate through and create xls data rows
                    lcvs.Sort();
                    #endregion

                    //Formulas
                    String currBaseRateOnSiteAddr;
                    String currBaseRateOffSiteAddr;

                    String FormulaBaseRate = string.Empty;
                    String FormulaEsc = string.Empty;
                    String FormulaFringe = string.Empty;
                    String FormulaOH = string.Empty;
                    String FormulaGA = string.Empty;
                    String FormulaFee = string.Empty;
                                        
                    foreach (Views.LaborCostView view in lcvs)
                    {
                        currBaseRateOnSiteAddr = ws.Rows[currDataRow].Cells[baseRateColIdx].ToString(CellReferenceMode.A1, false);

                        //set currDataCol based on year number
                        if (currPeriodNum == 1)
                            currDataCol = 0;
                        else
                            currDataCol = fringeCol;

                        //if first year write labor cat names
                        if (currDataCol == 0)
                        {
                            ws.Rows[currDataRow].Cells[currDataCol].Value = view.AgentLabCat;
                            currDataCol++;

                            ws.Rows[currDataRow].Cells[currDataCol].Value = view.ContractorLabCat;
                            currDataCol++;

                            //TODO: Past Performances Labor Categories

                            //Cost Basis Plugins
                            foreach (var cp in costBasisPlugins)
                            {
                                Dictionary<String, String> pluginDic = cp.getLaborNames(view.taskID, r.ID);

                                foreach (String pluginKey in pluginDic.Keys)
                                {
                                    if (costBasisPluginColDic.ContainsKey(pluginKey))
                                    {
                                        ws.Rows[currDataRow].Cells[costBasisPluginColDic[pluginKey]].Value = pluginDic[pluginKey];
                                    }
                                }

                            }

                            currDataCol = baseRateColIdx;

                            ws.Rows[currDataRow].Cells[currDataCol].Value = view.BaseRate;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                            PrevYrBaseRateOnsite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                            FormulaEsc = string.Format("({0} * {1})", currBaseRateOnSiteAddr, ws.Rows[fringeHdrRow].Cells[currDataCol + 5].ToString(CellReferenceMode.A1, false));
                            currDataCol++;

                            PrevYrEsc = "0";
                        }
                        if (PrevYrEsc != "0")
                        {
                            FormulaBaseRate = string.Format("{0} + {1}", PrevYrBaseRateOnsite, PrevYrEscDollarsOnsite);

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaBaseRate);
                            PrevYrBaseRateOnsite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                            currBaseRateOnSiteAddr = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                            FormulaEsc = string.Format("({0} * {1})", currBaseRateOnSiteAddr, ws.Rows[fringeHdrRow].Cells[currDataCol + 5].ToString(CellReferenceMode.A1, false));

                            currDataCol++;
                        }

                        if (view.OnsiteFringe.HasValue)
                        {

                            FormulaFringe = string.Format("{0} * {1}", currBaseRateOnSiteAddr,
                                ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaFringe);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteFringe.Value;

                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }

                        if (view.OnsiteOverhead.HasValue)
                        {
                            FormulaOH = string.Format("({0} + {1}) * {2}", currBaseRateOnSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaOH);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteOverhead.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }

                        if (view.OnsiteGA.HasValue)
                        {
                            FormulaGA = string.Format("({0} + {1} + {2}) * {3}", currBaseRateOnSiteAddr,
                                 ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false),
                                 ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                 ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaGA);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteGA.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }

                        if (view.OnsiteFee.HasValue)
                        {
                            FormulaFee = string.Format("({0} + {1} + {2} + {3}) * {4}", currBaseRateOnSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 3].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaFee);
                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteFee.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }
                        if (view.OnsiteEscalation.HasValue)
                        {
                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaEsc);
                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteEscalation.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            PrevYrEscDollarsOnsite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                            currDataCol++;
                        }

                        if (view.OnsiteBillRate.HasValue)
                        {
                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula(string.Format("={0} + {1} + {2} + {3} + {4}",
                                currBaseRateOnSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 5].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 4].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 3].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false)));

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OnsiteBillRate.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }

                        currBaseRateOffSiteAddr = string.Empty;

                        if (view.OffsiteFringe.HasValue)
                        {
                            if (PrevYrEsc == "0")
                            {
                                ws.Rows[currDataRow].Cells[currDataCol].Value = view.BaseRate;
                                ws.Rows[currDataRow].Cells[currDataCol].CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium;
                                PrevYrBaseRateOffSite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                                currBaseRateOffSiteAddr = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                                FormulaEsc = string.Format("({0} * {1})", currBaseRateOffSiteAddr, ws.Rows[fringeHdrRow].Cells[currDataCol + 5].ToString(CellReferenceMode.A1, false));
                                currDataCol++;
                            }
                            else
                            {
                                FormulaBaseRate = string.Format("{0} + {1}", PrevYrBaseRateOffSite, PrevYrEscDollarsOffsite);

                                ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaBaseRate);
                                PrevYrBaseRateOffSite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                                currBaseRateOffSiteAddr = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                                FormulaEsc = string.Format("({0} * {1})", currBaseRateOffSiteAddr, ws.Rows[fringeHdrRow].Cells[currDataCol + 5].ToString(CellReferenceMode.A1, false));

                                currDataCol++;
                            }
                       
                            FormulaFringe = string.Format("({0}) * {1}", currBaseRateOffSiteAddr,
                               ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaFringe);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteFringe.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }

                        if (view.OffsiteOverhead.HasValue)
                        {
                            FormulaOH = string.Format("({0} + {1}) * {2}", currBaseRateOffSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaOH);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteOverhead.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }
                        if (view.OffsiteGA.HasValue)
                        {
                            FormulaGA = string.Format("({0} + {1} + {2}) * {3}", currBaseRateOffSiteAddr,
                                 ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false),
                                 ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                 ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaGA);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteGA.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }
                        if (view.OffsiteFee.HasValue)
                        {
                            FormulaFee = string.Format("({0} + {1} + {2} + {3}) * {4}", currBaseRateOffSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 3].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 1].ToString(CellReferenceMode.A1, false),
                                ws.Rows[fringeHdrRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false));

                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaFee);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteFee.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            currDataCol++;
                        }
                        if (view.OffsiteEscalation.HasValue)
                        {
                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula("=" + FormulaEsc);

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteEscalation.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                            PrevYrEscDollarsOffsite = ws.Rows[currDataRow].Cells[currDataCol].ToString(CellReferenceMode.A1, false);
                            currDataCol++;
                        }
                        if (view.OffsiteBillRate.HasValue)
                        {
                            ws.Rows[currDataRow].Cells[currDataCol].ApplyFormula(string.Format("={0} + {1} + {2} + {3} + {4}",
                                currBaseRateOffSiteAddr,
                                ws.Rows[currDataRow].Cells[currDataCol - 5].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 4].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 3].ToString(CellReferenceMode.A1, false),
                                ws.Rows[currDataRow].Cells[currDataCol - 2].ToString(CellReferenceMode.A1, false)));

                            //ws.Rows[currDataRow].Cells[currDataCol].Value = view.OffsiteBillRate.Value;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPatternForegroundColor = getColorForYear(currPeriodNum);
                            ws.Rows[currDataRow].Cells[currDataCol].CellFormat.FillPattern = FillPatternStyle.Solid;
                        }
                        //currDataCol++;

                        WorksheetRegion costCells = ws.GetRegion(string.Format("R{0}C{1}:R{0}C{2}", currDataRow + 1, baseRateColIdx, currDataCol + 1), CellReferenceMode.R1C1);
                        foreach (var c in costCells)
                            c.CellFormat.FormatString = "\"$\"#,##0.00";

                        currDataRow++;
                    }
                    PrevYrEsc = FormulaEsc;

                }
                //Setup Freeze Rows and Columns
                ws.DisplayOptions.PanesAreFrozen = true;
                ws.DisplayOptions.FrozenPaneSettings.FrozenRows = headerRow + 1;
                ws.DisplayOptions.FrozenPaneSettings.FrozenColumns = 2;
            }//end of foreach region

            save(wb, filePath);
        }

        private static void save(Workbook wb, String filePath)
        {
            try
            {
                wb.Save(filePath);
            }
            catch (IOException exIO)
            {
                if (exIO.Message == string.Format("The process cannot access the file '{0}' because it is being used by another process.", filePath))
                {
                    System.Windows.Forms.DialogResult result = System.Windows.Forms.MessageBox.Show("File is open.  Please close file and try again.", Common.Constants.AppName,
                                            System.Windows.Forms.MessageBoxButtons.RetryCancel, System.Windows.Forms.MessageBoxIcon.Error,
                                            System.Windows.Forms.MessageBoxDefaultButton.Button1);

                    if (result == System.Windows.Forms.DialogResult.Retry)
                        save(wb, filePath);
                }
                else
                    throw;

            }
            catch (Exception)
            {
                throw;
            }
        }

        private static System.Drawing.Color getColorForYear(int yearNumOfContract)
        {
            if (yearNumOfContract > 5)
                yearNumOfContract %= 5;

            switch (yearNumOfContract)
            {
                case 1:
                    return System.Drawing.Color.LightBlue;
                case 2:
                    return System.Drawing.Color.LightGray;
                case 3:
                    return System.Drawing.Color.LightSteelBlue;
                case 4:
                    return System.Drawing.Color.Gainsboro;
                case 5:
                    return System.Drawing.Color.AliceBlue;
                default:
                    return System.Drawing.Color.LightBlue;
            }

        }
    }
}