using ExportWcfService;
using Helpers.Log;
using log4net;
using Microsoft.AnalysisServices.AdomdClient;
using Microsoft.AnalysisServices;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Threading;
using System.IO;
using System.Web.Script.Serialization;

public partial class app_custom_screens_kpis_cube_api_cube_api : System.Web.UI.Page
{
    private static ILog logger = Logger.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.Name);

    private class Filter
    {
        public string Type { get; set; }
        public List<string> Values { get; set; }
        public string Name { get; set; }

        public Filter()
        {
            this.Type = "DEFAULT";
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        JavaScriptSerializer jss = new JavaScriptSerializer();
        string json = "";
        object results = null;

        string action = Request["action"];

        switch (action)
        {
            case "EXPORTEXCEL":
                try { 
                    ExcelExportParams exportParams = jss.Deserialize<ExcelExportParams>(Request["exportParams"]);

                    logger.DebugFormat("Debug getdataqquery: {0}", this.getDataQuery());
                    results = this.ExportToExcel(exportParams, this.getDataQuery());
                    
                    StreamReader sr = new StreamReader((Stream)results);
                    json = sr.ReadToEnd();
                }
                catch(Exception ex)
                {
                    logger.Error(ex);
                }
                

                break;
            case "FILTER":
                results = this.ExecuteCubeQuery(this.getFilterQuery());

                json = jss.Serialize(results);

                break;
            case "DATA":
            default:
                results = this.ExecuteCubeQuery(this.getDataQuery());

                json = jss.Serialize(results);

                break;
        }

        Response.Clear();
        Response.ContentType = "application/json; charset=utf-8";
        Response.Write(json);
        Response.End();

    }

    private object ExportToExcel(ExcelExportParams exportParams, string query)
    {
        String internalPath = "app\\custom-screens\\kpi-weekly\\cube-api\\";
        String path = AppDomain.CurrentDomain.BaseDirectory + internalPath + "cube\\cube.cub";

        exportParams.ConnectionString = string.Format("Provider=MSOLAP;Data Source={0}", path);
        exportParams.CubeDataSources = (new List<CubeDataSource> {
                                            new CubeDataSource {
                                                Name = "data",
                                                CubeFilePath = path,
                                                CubeQuery = query,
                                            },
                                        }).ToArray();

        ExportWcfServiceClient client = new ExportWcfServiceClient();

        return client.ExportToExcel(exportParams);
    }

    private object ExecuteCubeQuery(String query)
    {
        List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();
        String internalPath = "app\\custom-screens\\kpi-weekly\\cube-api\\";
        String path = AppDomain.CurrentDomain.BaseDirectory + internalPath;
        bool retry = false;
        bool failed = false;

        do
        {
            try
            {
                using (AdomdConnection conn = new AdomdConnection(string.Format("Provider=MSOLAP;Data Source={0}", path + "cube\\cube.cub")))
                {
                    conn.Open();

                    AdomdCommand cmd = new AdomdCommand();
                    cmd.Connection = conn;

                    /* XMLA query used previously in Management Studio */
                    logger.DebugFormat("Cube Query: [{0}].", query);
                    cmd.CommandText = query;

                    //Execute the command, retrieving an XmlReader.
                    //System.Xml.XmlReader reader = cmd.ExecuteXmlReader();
                    //Console.WriteLine(reader.ReadOuterXml());
                    //reader.Close(); 

                    /* ADOMD is just an ADO.NET provider and so 
                     * principles are the same
                     */
                    using (AdomdDataAdapter da = new AdomdDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();

                        da.Fill(ds);

                        if (ds != null)
                        {
                            int tn = 0;
                            for (int row = 0; row < ds.Tables[tn].Rows.Count; row++)
                            {
                                Dictionary<string, object> dRow = new Dictionary<string, object>();
                                for (int column = 0; column < ds.Tables[tn].Columns.Count; column++)
                                {
                                    dRow.Add(ds.Tables[tn].Columns[column].ToString(), ds.Tables[tn].Rows[row].ItemArray[column]);
                                }

                                results.Add(dRow);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //Since cube file could be deleted by a reset to factory, check if the file exists.
                //If the file does not exist, return an empty dataset. Else, return the error.
                if ((File.Exists(path + "cube\\cube.cub") == true)
                    && (string.Equals(
                            ex.Message,
                            "XML for Analysis parser: The CurrentCatalog XML/A property was not specified.",
                            StringComparison.OrdinalIgnoreCase) == false)
                )
                {
                    logger.Error("Failed when trying to read from cube.", ex);

                    if (!retry)
                    {
                        retry = true;
                        Thread.Sleep(1000);
                    }
                    else
                    {
                        retry = false;
                        failed = true;
                    }
                }
            }
        } while (retry);

        return (!failed)
                    ? (object)results
                    : new { Status = "FAILED", Message = "CUBE_BEING_PROCESSED" };
    }

    private string getDataQuery(string dbName = "MES")
    {
        JavaScriptSerializer jss = new JavaScriptSerializer();

        string query = @"";
        string groupByParam = Request["groupby"];
        string filterByParam = Request["filterBy"];
        int? rowsCount = (string.IsNullOrWhiteSpace(Request["rowsCount"]) == false)
                                ? Int32.Parse(Request["rowsCount"])
                                : (int?)null;
        int? rowsStartIndex = (string.IsNullOrWhiteSpace(Request["rowsStartIndex"]) == false)
                                    ? Int32.Parse(Request["rowsStartIndex"])
                                    : (int?)null;

        List<string> groupBy = (groupByParam != null) ? jss.Deserialize<List<string>>(groupByParam) : new List<string>();
        Dictionary<string, Filter> filterByList = (filterByParam != null) ? jss.Deserialize<Dictionary<string, Filter>>(filterByParam) : new Dictionary<string, Filter>();

        Dictionary<string, List<object>> filterBy = new Dictionary<string, List<object>>();
        foreach (KeyValuePair<string, Filter> kvp in filterByList)
        {
            string key = kvp.Key;
            Filter filter = kvp.Value;

            string type = filter.Type;
            List<string> values = filter.Values;

            List<object> qfs = new List<object>();
            if (type.ToUpper() == "DATE")
            {
                qfs.Add(new DateFilter() { StartDate = values[0], EndDate = values[1] });
            }
            else if (type.ToUpper() == "CUSTOM")
            {
                values.ForEach(p => qfs.Add(new CustomFilter() { Value = p, Name = filter.Name, }));
            }
            else
            {
                values.ForEach(p => qfs.Add(p));
            }

            filterBy.Add(key, qfs);
        }

        query = this.BuildMDXQuery(
            groupBy,
            filterBy,
            rowsStartIndex,
            rowsCount,
            dbName
        );

        return query;
    }

    private string getFilterQuery(string dbName = "MES")
    {
        string name = Request["name"];

        string query =
@"SELECT 
    [MEMBER_NAME], 
    [MEMBER_KEY],
    [MEMBER_UNIQUE_NAME], 
    [PARENT_UNIQUE_NAME],
	[LEVEL_UNIQUE_NAME]
FROM $system.mdschema_members
WHERE 
    [CUBE_NAME] = '${0}'
    AND 
    [DIMENSION_UNIQUE_NAME] = '[{1}]'
    AND
    [MEMBER_NAME] <> 'Unknown'
    AND
    [MEMBER_NAME] <> 'All'
    AND 
    [MEMBER_TYPE] = 1";

        query = String.Format(query, name, name);

        return query;
    }

    private abstract class QueryFilter
    {
        public object Value;
        public string Name;

        public string GetFilterClause(string name)
        {
            //return name + ".&[" + this.Value.ToString() + "]"; 
            return '{' + this.Value.ToString() + '}';
        }
    }

    private class CustomFilter : QueryFilter
    {

        public string GetFilterClause(string name)
        {
            return this.Value.ToString();
        }
    }

    private class DateFilter : QueryFilter
    {
        public String StartDate;
        public String EndDate;

        public string GetFilterClause(string name)
        {
            string datefilter;
            String StartDateFixed;
            String EndDateFixed;
            DateTime start = DateTime.Parse(StartDate);
            DateTime end = DateTime.Parse(EndDate);
            DateTime now = DateTime.Now;

            if (end > now)
            {
                end = now;
                EndDate = end.ToString("yyyy-MM-dd");
            }

            datefilter = @"ORDER(
                                    FILTER(
                                        {{ [Time].[{0}].MEMBERS }},
                                        [Time].[{0}].CURRENTMEMBER.MEMBER_VALUE >= CDATE('{1}')
		                                AND [Time].[{0}].CURRENTMEMBER.MEMBER_VALUE <= CDATE('{2}')
		                            ),
                                    [Time].[{0}].MEMBER_CAPTION,
                                    BDESC
                                )";

            //            datefilter = @"ORDER(
            //                ([Time].[{0}].&[{1}T00:00:00] : [Time].[{0}].&[{2}T00:00:00]) - [Time].[{0}].UNKNOWNMEMBER,
            //                [Time].[{0}].MEMBER_CAPTION,
            //                BDESC
            //            )";
            
            switch (name.ToUpper())
            {
                case "DATE":
                case "DAY":
                    StartDateFixed = StartDate;
                    EndDateFixed = EndDate;
                    //                    datefilter = @"ORDER(
                    //                        ([Time].[{0}].&[{1}T00:00:00] : [Time].[{0}].&[{2}T00:00:00]) - [Time].[{0}].UNKNOWNMEMBER,
                    //                        [Time].[{0}].MEMBER_CAPTION,
                    //                        BDESC
                    //                    )";
                    break;
                case "WEEK":
                    StartDateFixed = this.GetPreviousMonday(start);
                    EndDateFixed = this.GetPreviousSunday(end);
                    logger.DebugFormat("debug parameters: start: {0}, end: {1}, start previous mondat: {2}, end previous monday: {3}",start , end, StartDateFixed, EndDateFixed);
                    //                    datefilter = @"ORDER(
                    //                        ([Time].[{0}].&[{1}T00:00:00] : [Time].[{0}].&[{2}T00:00:00]) - [Time].[{0}].UNKNOWNMEMBER,
                    //                        [Time].[{0}].MEMBER_CAPTION,
                    //                        BDESC
                    //                    )";
                    break;
                case "MONTH":
                    StartDateFixed = this.GetPreviousMonth1st(start);
                    EndDateFixed = this.GetPreviousMonth1st(end);
                    //                    datefilter = @"ORDER(
                    //                        ([Time].[{0}].&[{1}T00:00:00] : [Time].[{0}].&[{2}T00:00:00]) - [Time].[{0}].UNKNOWNMEMBER,
                    //                        [Time].[{0}].MEMBER_CAPTION,
                    //                        BDESC
                    //                    )";
                    break;
                case "YEAR":
                    StartDateFixed = this.GetPreviousYear1st(start);
                    EndDateFixed = this.GetPreviousYear1st(end);
                    //                    datefilter = @"ORDER(
                    //                        ([Time].[{0}].&[{1}T00:00:00] : [Time].[{0}].&[{2}T00:00:00]) - [Time].[{0}].UNKNOWNMEMBER,
                    //                        [Time].[{0}].MEMBER_CAPTION,
                    //                        BDESC
                    //                    )";
                    break;
                default:
                    datefilter = "";
                    StartDateFixed = this.GetPreviousMonday(start);
                    EndDateFixed = this.GetPreviousSunday(end);
                    break;
            }

            logger.DebugFormat("test switch: {0}", name);
            return String.Format(datefilter, name, StartDateFixed, EndDateFixed);

            //            string datefilter = @"[Time].[Date].CURRENTMEMBER.MEMBER_VALUE >= '{0}'
            //		                        AND [Time].[Date].CURRENTMEMBER.MEMBER_VALUE <= '{1}'";

            //return String.Format(datefilter, StartDate, EndDate); 

            //string s = name + ".&[" + StartDate + "]";
            //string e = name + ".&[" + EndDate + "]";
            //return String.Join(":", new List<String>() { s, e }); 
        }

        private string GetPreviousMonday(DateTime dt)
        {
            int diff = dt.DayOfWeek - DayOfWeek.Monday;

            return dt.AddDays(-1 * 7).Date.ToString("yyyy-MM-dd");
        }

        private string GetPreviousSunday(DateTime dt)
        {
            int diff = dt.DayOfWeek - DayOfWeek.Sunday;
            if (diff < 0)
            {
                diff += 7;
            }

            return dt.AddDays(-1 * diff -7).Date.ToString("yyyy-MM-dd");
        }
        //private string GetNextMonday(DateTime dt)
        //{
        //    int diff = dt.DayOfWeek - DayOfWeek.Monday;
        //    if (diff < 0)
        //    {
        //        diff += 7;
        //    }

        //    return dt.AddDays(-1 * diff + 7).Date.ToString("yyyy-MM-dd");
        //}
        private string GetPreviousMonth1st(DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, 1).ToString("yyyy-MM-dd");
        }
        //private string GetNextMonth1st(DateTime dt)
        //{
        //    return new DateTime(dt.Year, dt.Month, 1).AddMonths(1).ToString("yyyy-MM-dd");
        //}
        private string GetPreviousYear1st(DateTime dt)
        {
            return new DateTime(dt.Year, 1, 1).ToString("yyyy-MM-dd");
        }
        //private string GetNextYear1st(DateTime dt)
        //{
        //    return new DateTime(dt.Year, 1, 1).AddYears(1).ToString("yyyy-MM-dd");
        //}
    }
    private string BuildMDXQuery(List<string> groupBy, Dictionary<string, List<object>> filterBy, string dbName = "MES")
    {
        return this.BuildMDXQuery(groupBy, filterBy, null, null, dbName);
    }

    
    private string BuildMDXQuery(List<string> groupBy, Dictionary<string, List<object>> filterBy, int? rowStartIndex, int? rowsCount, string dbName = "MES")
    {
        string columnsQuery = @"[Measures].AllMembers";
            string rowsQuery = @"{0}";
        //NOT (ISEMPTY([Measures].[CrewId]))
        //OR NOT (ISEMPTY([Measures].[EquipmentCode]))
        //OR NOT (ISEMPTY([Measures].[ProductTypeId]))";
        if ((rowsCount.HasValue == true) && (rowStartIndex.HasValue == true))
            rowsQuery = string.Format("SUBSET({0}, {1}, {2})", rowsQuery, rowStartIndex.Value, rowsCount.Value);
        
        List<Tuple<int, string, string>> groupByItems = new List<Tuple<int, string, string>>();
        List<Tuple<int, string, string>> filters = new List<Tuple<int, string, string>>();

        foreach (KeyValuePair<string, List<object>> kvpp in filterBy)
        {
            string key = kvpp.Key;
            foreach (object filterobj in kvpp.Value)
            {
                KeyValuePair<string, object> kvp = new KeyValuePair<string, object>(key, filterobj);
                if (kvp.Value is QueryFilter)
                {
                    if (kvp.Value is DateFilter)
                    {
                        var filter = ((DateFilter)kvp.Value);
                        logger.DebugFormat("Time Debug: {0}", filter);
                        if (groupBy.Contains("DATE") == true)
                        {
                            filters.Add(new Tuple<int, string, string>(1, filter.Name, filter.GetFilterClause("DATE")));
                            groupBy.Remove("DATE");
                        }
                        else if (groupBy.Contains("DAY") == true)
                        {
                            filters.Add(new Tuple<int, string, string>(1, filter.Name, filter.GetFilterClause("DATE")));
                            groupBy.Remove("DAY");
                        }
                        else if (groupBy.Contains("WEEK") == true)
                        {
                            filters.Add(new Tuple<int, string, string>(1, filter.Name, filter.GetFilterClause("WEEK")));
                            groupBy.Remove("WEEK");
                        }
                        else if (groupBy.Contains("MONTH") == true)
                        {
                            filters.Add(new Tuple<int, string, string>(1, filter.Name, filter.GetFilterClause("MONTH")));
                            groupBy.Remove("MONTH");
                        }
                        else if (groupBy.Contains("YEAR") == true)
                        {
                            filters.Add(new Tuple<int, string, string>(1, filter.Name, filter.GetFilterClause("YEAR")));
                            groupBy.Remove("YEAR");
                        }
                    }
                    //else if (kvp.Value is CustomFilter)
                    //{
                    //    var filter = ((CustomFilter)kvp.Value);
                    //    filters.Add(new Tuple<int, string>(1, filter.GetFilterClause(kvp.Key));
                    //    groupBy.Remove(filter.Name);
                    //}
                    else
                    {

                        var filter = ((QueryFilter)kvp.Value);
                        int order;

                        switch (filter.Name.ToUpper())
                        {
                            case "CREW":
                                order = 2;
                                groupBy.Remove("CREW");
                                break;
                            case "FURNACE":
                                order = 3;
                                groupBy.Remove("FURNACE");
                                break;
                            case "PRODUCTTYPE":
                                order = 4;
                                groupBy.Remove("PRODUCTTYPE");
                                break;
                            default:
                                order = 1;
                                break;
                        }

                        filters.Add(new Tuple<int, string, string>(order, filter.Name, filter.GetFilterClause(kvp.Key)));
                    }
                }
                //else
                //{
                //    filters.Add(kvp.Key + ".&[" + kvp.Value.ToString().Replace("[", String.Empty).Replace("]", String.Empty) + "]");
                //}
            }
        }

        foreach (string i in groupBy)
        {
            switch (i)
            {
                case "CREW":
                    groupByItems.Add(new Tuple<int, string, string>(2, "CREW", "ORDER(EXCEPT([Crews].AllMembers, [Crews].[Id].[Unknown]) , [Crews].[Id].MEMBER_UNIQUE_NAME, BASC)"));
                    break;
                default:
                    break;
            }
        }

        
        rowsQuery = String.Format(
            rowsQuery,
            String.Join(
                "\n*\n",
                groupByItems
                    .Concat(filters)
                    .OrderBy(i => i.Item1)
                    .Select(i => i.Item3)
            )
        );
        
        string baseQuery =
        @"SELECT
        {{ {0} }} ON COLUMNS,
        {1}        
        ON ROWS
        FROM [{2}]";
        //string query = String.Format(baseQuery, columnsQuery, rowsQuery, fromQuery, dbName);
        string query = String.Format(baseQuery, columnsQuery, rowsQuery, dbName);
        return query;
    }
}