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

        string action = Request["action"];

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

                results = this.ExportToExcel(exportParams, this.getDataQuery());

                StreamReader sr = new StreamReader((Stream)results);

                json = sr.ReadToEnd();

                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\\modules\\oee\\oee-report\\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\\modules\\oee\\oee-report\\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 */
                    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.
                //Do the same also for some specific excpetions that are raised when cube is processed with no
                //production items (for example after a reset to factory of production and configuration data).
                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 = "OEE")
    {
        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 = "OEE")
    {
        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_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(
                ([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.GetPreviousSunday(start);
                    EndDateFixed = this.GetPreviousSunday(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 "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 = StartDate;
                    EndDateFixed = EndDate;
                    break;
            }

            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 GetPreviousSunday(DateTime dt)
        {
            int diff = dt.DayOfWeek - DayOfWeek.Sunday;
            if (diff < 0)
            {
                diff += 7;
            }

            return dt.AddDays(-1 * diff).Date.ToString("yyyy-MM-dd");
        }
        //private string GetNextSunday(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 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 = "OEE")
    {
        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 = "OEE")
    {
        string columnsQuery = @"[Measures].AllMembers";
        string rowsQuery = 
            @"{0},
			NOT (ISEMPTY([Measures].[Quality]))
				OR NOT (ISEMPTY([Measures].[Performance]))
				OR NOT (ISEMPTY([Measures].[OEE]))
				OR NOT (ISEMPTY([Measures].[Availability]))";

        if ((rowsCount.HasValue == true) && (rowStartIndex.HasValue == true))
            rowsQuery = string.Format("SUBSET(FILTER({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);
                        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 "AREA":
                                order = 2;
                                groupBy.Remove("AREA");
                                break;
                            case "LINE":
                                order = 3;
                                groupBy.Remove("LINE");
                                break;
                            case "MACHINE":
                                order = 4;
                                groupBy.Remove("MACHINE");
                                break;
                            case "PRODUCT":
                                order = 5;
                                groupBy.Remove("PRODUCT");
                                break;
                            case "SHIFT":
                                order = 6;
                                groupBy.Remove("SHIFT");
                                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 "AREA":
                    if ((groupByItems.Any(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase)) == false)
                        && (groupByItems.Any(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase)) == false))
                    {
                        string query2;

                        query2 = "ORDER([Production Items].[Parent Id].[Level 02].ALLMEMBERS, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)";

                        groupByItems.Add(new Tuple<int, string, string>(2, "AREA", query2));
                    }
                    break;
                case "DATE":
                case "DAY":
                    groupByItems.Add(new Tuple<int, string, string>(1, "DATE", "ORDER([Time].[Date].Children - [Time].[Date].&[Unknown], [Time].[Date].MEMBER_CAPTION, BDESC)"));
                    break;
                case "LINE":
                    if ((groupByItems.Any(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase)) == false)
                        && (groupByItems.Any(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase)) == false))
                    {
                        string query2;

                        ////if (filters.Any(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase)) == true)
                        ////{
                        ////    var filter = filters
                        ////                .First(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase));

                        ////    query2 = string.Format(
                        ////        "ORDER([Production Items].[Parent Id].[Level 02].ALLMEMBERS - {0}, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)",
                        ////        filter.Item3
                        ////    );

                        ////    filters.Remove(filter);
                        ////}
                        ////if (filters.Any(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase)) == true)
                        ////{
                        ////    var filter = filters
                        ////                .First(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase));

                        ////    query2 = string.Format(
                        ////        "ORDER([Production Items].[Parent Id].[Level 02].ALLMEMBERS - {0}, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)",
                        ////        filter.Item3
                        ////    );
                        ////    filters.Remove(filter);
                        ////}
                        ////else
                        ////{
                        //    query2 = "ORDER([Production Items].[Parent Id].[Level 02].ALLMEMBERS, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)";
                        ////}

                        if (filters.Any(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase)) == true)
                        {
                            var filter = filters
                                        .First(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase));

                            query2 = string.Format(
                                "ORDER(DESCENDANTS({0}, 1), [Production Items].[Parent Id].MEMBER_CAPTION, BASC)",
                                filter.Item3
                            );
                            filters.Remove(filter);
                        }
                        else
                        {
                            query2 = "ORDER([Production Items].[Parent Id].[Level 03].ALLMEMBERS, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)";
                        }

                        groupByItems.Add(new Tuple<int, string, string>(2, "LINE", query2));
                    }
                    break;
                case "MACHINE":
                    if ((groupByItems.Any(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase)) == false)
                        && (groupByItems.Any(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase)) == false))
                    {
                        string query2;

                        if (filters.Any(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase)) == true)
                        {
                            var filter = filters
                                        .First(j => string.Equals(j.Item2, "MACHINE", StringComparison.OrdinalIgnoreCase));

                            query2 = string.Format(
                                "{0}",
                                filter.Item3
                            );

                            filters.Remove(filter);
                        }
                        else if (filters.Any(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase)) == true)
                        {
                            var filter = filters
                                        .First(j => string.Equals(j.Item2, "LINE", StringComparison.OrdinalIgnoreCase));

                            query2 = string.Format(
                                "ORDER(DESCENDANTS({0}, 1), [Production Items].[Parent Id].MEMBER_CAPTION, BASC)",
                                filter.Item3
                            );
                            filters.Remove(filter);
                        }
                        else if (filters.Any(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase)) == true)
                        {
                            var filter = filters
                                        .First(j => string.Equals(j.Item2, "AREA", StringComparison.OrdinalIgnoreCase));

                            query2 = string.Format(
                                "ORDER(DESCENDANTS({0}, 2), [Production Items].[Parent Id].MEMBER_CAPTION, BASC)",
                                filter.Item3
                            );
                            filters.Remove(filter);
                        }
                        else
                        {
                            query2 = "ORDER([Production Items].[Parent Id].[Level 04].ALLMEMBERS, [Production Items].[Parent Id].MEMBER_CAPTION, BASC)";
                        }

                        groupByItems.Add(new Tuple<int, string, string>(2, "MACHINE", query2));
                    }
                    break;
                case "MONTH":
                    groupByItems.Add(new Tuple<int, string, string>(1, "MONTH", "ORDER([Time].[Month].Children - [Time].[Month].&[Unknown], [Time].[Month].MEMBER_CAPTION, BDESC)"));
                    break;                
                case "PRODUCT":
                    groupByItems.Add(new Tuple<int, string, string>(3, "PRODUCT", "ORDER([Products].Children, [Products].MEMBER_CAPTION, BASC)"));
                    break;
                case "SHIFT":
                    groupByItems.Add(new Tuple<int, string, string>(4, "SHIFT", "ORDER([Shifts].Children, [Shifts].MEMBER_CAPTION, BASC)"));
                    break;
                case "WEEK":
                    groupByItems.Add(new Tuple<int, string, string>(1, "WEEK", "ORDER([Time].[Week].Children - [Time].[Week].&[Unknown], [Time].[Week].MEMBER_CAPTION, BDESC)"));
                    break;
                case "YEAR":
                    groupByItems.Add(new Tuple<int, string, string>(1, "YEAR", "ORDER([Time].[Year].Children - [Time].[Year].&[Unknown], [Time].[Year].MEMBER_CAPTION, BDESC)"));
                    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; 
    }
}