using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using CMS.PortalControls;
using System.Data;
using System.Data.OleDb;
using CMS.SiteProvider;
using CMS.IO;
using CMS.CMSHelper;
using CMS.GlobalHelper;
using System.Text;
using CMS.TreeEngine;
using UI = System.Web.UI.WebControls;
using CMS.SettingsProvider;
using CMS.DataEngine;
using CMS.EventLog;
using CMS.DocumentEngine;
using CMS.Helpers;
using CMS.CustomTables;
using CMS.Membership;

public partial class ERPApps_ERPWebParts_ExcelImport_ExcelImport : CMSAbstractWebPart
{
    #region "Variables"

    Dictionary<string, bool> existingVendors;

    DataSet mDataSet;
    protected DataSet Data
    {
        get
        {
            if (mDataSet == null && Session["excelImportData"] is DataSet)
            {
                mDataSet = Session["excelImportData"] as DataSet;
            }

            return mDataSet;
        }
        set
        {
            mDataSet = value;
            Session["excelImportData"] = mDataSet;
        }
    }

    #endregion

    #region "Setup"

    protected override void OnInit(EventArgs e)
    {
        base.OnInit(e);

        btnUpload.Click += new EventHandler(btnUpload_Click);
        btnImportSelected.Click += new EventHandler(btnImportSelected_Click);

        if (Data != null && !FileUpload.HasFile)
        {
            BindGridView();
        }
    }

    private void BindGridView()
    {
        if (Data != null)
        {
            gridView.DataSource = Data.Tables["Vendors"];
            gridView.DataBind();

            if (Data.Tables["Vendors"] != null && Data.Tables["Vendors"].Rows != null && Data.Tables["Vendors"].Rows.Count > 0)
            {
                plcData.Visible = true;
            }
        }
    }

    void btnImportSelected_Click(object sender, EventArgs e)
    {
        DataTable table = (DataTable) gridView.DataSource;

        GeneralConnection cn = ConnectionHelper.GetConnection();

        DataSet dsIndustries = CustomTableItemProvider.GetItems("ERP.ListIndustrySector", null, null);
        DataSet dsProcesses = CustomTableItemProvider.GetItems("ERP.ListProcessGroup", null, null);
        DataSet dsPlatforms = CustomTableItemProvider.GetItems("ERP.ListPlatformType", null, null);
        DataSet dsCertifications = CustomTableItemProvider.GetItems("ERP.ListCertification", null, null);
        DataSet dsSolutionTypes = CustomTableItemProvider.GetItems("ERP.ListSolutionType", null, null);

        Dictionary<string, int> availableIndustries = new Dictionary<string, int>();
        Dictionary<string, int> availableProcesses = new Dictionary<string, int>();
        Dictionary<string, int> availablePlatforms = new Dictionary<string, int>();
        Dictionary<string, int> availableCertifications = new Dictionary<string, int>();
        Dictionary<string, int> availableSolutionTypes = new Dictionary<string, int>();

        foreach (DataRow row in dsIndustries.Tables[0].Rows)
        {
            availableIndustries.Add(ValidationHelper.GetString(row["ItemName"], "").Trim(), ValidationHelper.GetInteger(row["ItemID"], 0));
        }

        foreach (DataRow row in dsProcesses.Tables[0].Rows)
        {
            availableProcesses.Add(ValidationHelper.GetString(row["ItemName"], "").Trim(), ValidationHelper.GetInteger(row["ItemID"], 0));
        }

        foreach (DataRow row in dsPlatforms.Tables[0].Rows)
        {
            availablePlatforms.Add(ValidationHelper.GetString(row["ItemName"], "").Trim(), ValidationHelper.GetInteger(row["ItemID"], 0));
        }

        foreach (DataRow row in dsCertifications.Tables[0].Rows)
        {
            availableCertifications.Add(ValidationHelper.GetString(row["ItemName"], "").Trim().ToLowerInvariant(), ValidationHelper.GetInteger(row["ItemID"], 0));
        }

        foreach (DataRow row in dsSolutionTypes.Tables[0].Rows)
        {
            availableSolutionTypes.Add(ValidationHelper.GetString(row["ItemName"], "").Trim().ToLowerInvariant(), ValidationHelper.GetInteger(row["ItemID"], 0));
        }

        StringBuilder logInfo = new StringBuilder();
        StringBuilder logError = new StringBuilder();

        foreach(UI.GridViewRow item in gridView.Rows) {
            UI.CheckBox DiscontinuedCheckBox = (UI.CheckBox)item.FindControl("chckImport");
            bool shouldImport = DiscontinuedCheckBox.Checked;
            DataRow row = table.Rows[item.RowIndex];
            row["ShouldImport"] = shouldImport;

            try
            {
                if (shouldImport)
                {
                    // create vendor
                    TreeNode vendorNode = TreeNode.New(ERPConfig.DOCTYPE_VENDOR);
                    vendorNode.SetValue("VendorName", row[0]);
                    vendorNode.NodeName = row[0].ToString();
                    vendorNode.DocumentName = row[0].ToString();
                    vendorNode.SetValue("VendorAddressStreet", ValidationHelper.GetString(row[1], ""));
                    vendorNode.SetValue("VendorAddressCity", ValidationHelper.GetString(row[3], ""));
                    vendorNode.SetValue("VendorAddressPostCode", ValidationHelper.GetString(row[2], ""));
                    vendorNode.SetValue("VendorAddressCountry", ValidationHelper.GetString(row[4], ""));
                    vendorNode.SetValue("VendorContactEmail", ValidationHelper.GetString(row[6], ""));
                    vendorNode.SetValue("VendorWebsiteURL", ValidationHelper.GetString(row[7], ""));
                    vendorNode.SetValue("VendorContactPerson", "");

                    vendorNode.Insert(TreeHelper.SelectSingleNode("/vendors"));

                    List<ImportApplication> apps = row["Apps"] as List<ImportApplication>;
                    if (apps != null)
                    {
                        foreach (ImportApplication ia in apps)
                        {
                            string shortDescription = ia.Description.Length > 300 ? ia.Description.Substring(0, 290) + "..." : ia.Description;

                            // create application
                            TreeNode appNode = TreeNode.New(ERPConfig.DOCTYPE_APPLICATION);
                            appNode.SetValue("AppName", ia.Name);
                            appNode.DocumentName = ia.Name;
                            appNode.NodeName = ia.Name;
                            appNode.SetValue("AppShortDescription", shortDescription);
                            appNode.SetValue("AppDescription", ia.Description);
                            appNode.SetValue("AppVendorID", vendorNode.GetValue("VendorID"));
                            appNode.SetValue("AppIsPublished", "1");

                            appNode.SetValue("AppDateAdded", DateTime.Now);
                            appNode.SetValue("AppDateUpdated", DateTime.Now);
                            appNode.SetValue("AppFirstPublishedDate", DateTime.Now);

                            appNode.SetValue("AppShowCommercialProfile", "0");
                            appNode.SetValue("AppMonthPageviews", "0");

                            if (availableCertifications.ContainsKey(ia.Certification.Trim().ToLowerInvariant()))
                            {
                                appNode.SetValue("AppCertifications", availableCertifications[ia.Certification.Trim().ToLowerInvariant()]);
                            }

                            if (availableSolutionTypes.ContainsKey(ia.SolutionType.Trim().ToLowerInvariant()))
                            {
                                appNode.SetValue("AppSolutionType", availableSolutionTypes[ia.SolutionType.Trim().ToLowerInvariant()]);
                            }

                            appNode.Insert(vendorNode);

                            CreateRelations(cn, ia.GetIndustries(), availableIndustries, appNode, "@IndustrySectorID", "ERP.JoinApplicationListIndustrySector.insert", "AppIndustrySectors");
                            CreateRelations(cn, ia.GetProcesses(), availableProcesses, appNode, "@ProcessGroupID", "ERP.JoinApplicationListProcessGroup.insert", "AppProcessGroups");
                            CreateRelations(cn, ia.GetPlatforms(), availablePlatforms, appNode, "@PlatformTypeID", "ERP.JoinApplicationListPlatformType.insert", "AppPlatformTypes");

                            appNode.Update();
                        }
                    }

                    logInfo.Append(vendorNode.GetValue("VendorName"), ", ");
                }
            }
            catch (Exception ex)
            {
                EventLogProvider.LogException("IMPORT", "Run", ex);
                logError.Append(row[0], ", ");
            }
        }

        if (logInfo.Length > 0)
        {
            lblInfo.Text = "<h2>Following vendors have been imported</h2>" + logInfo.ToString().TrimEnd(new char[] { ' ', ',' });
            lblInfo.Visible = true;
        }
        if (logError.Length > 0)
        {
            lblError.Text = "<h2>Following vendors could not be imported</h2>" + logError.ToString().TrimEnd(new char[] { ' ', ',' });
            lblError.Visible = true;
        }

        plcData.Visible = false;
    }

    private static void CreateRelations(GeneralConnection cn, List<string> values, Dictionary<string, int> options, TreeNode appNode, string queryParameterName, string queryName, string appProperty)
    {
        // industries
        List<int> toInsert = new List<int>();
        string stringValue = "|";
        foreach (string value in values)
        {
            if (options.ContainsKey(value))
            {
                toInsert.Add(options[value]);
            }
            else if (value.Equals("ALL"))
            {
                foreach (int option in options.Values)
                {
                    toInsert.Add(option);
                }
            }

            foreach (int valueToInsert in toInsert)
            {
                QueryDataParameters insertParameters = new QueryDataParameters();
                insertParameters.Add("@AppID", appNode.GetValue("AppID"));
                insertParameters.Add(queryParameterName, valueToInsert);
                insertParameters.Add("@ItemGUID", Guid.NewGuid());

                stringValue += valueToInsert + "|";
                
                cn.ExecuteNonQuery(new QueryParameters(QueryInfoProvider.GetQueryInfo(queryName), insertParameters,null));
            }
        }

        if (!stringValue.Equals("|"))
        {
            appNode.SetValue(appProperty, stringValue + "|");
        }
    }

    void btnUpload_Click(object sender, EventArgs e)
    {
        if (!FileUpload.HasFile) {
            lblInfo.Visible = true;
            lblInfo.Text = "Please select file to upload.";

            return;
        }

        if (!IsValidFileExtension(FileUpload.FileName)) {
            lblInfo.Visible = true;
            lblInfo.Text = "Only .xls files are allowed.";

            return;
        }

        DataSet ds = RunUpload();
        Data = ds;

        BindGridView();
    }

    private DataSet RunUpload()
    {
        // copy file to temp folder
        string path = TempFileInfoProvider.TemporaryFilesFolderPath + Guid.NewGuid() + ".xls";
        FileUpload.SaveAs(MapPath(path));
        string filesource = MapPath(path);

        // load data from XLS and init DataSet
        DataSet ds = new DataSet();
        try
        {
            ProcessInputFile(filesource, ds);
        }
        catch (Exception e)
        {
            EventLogProvider.LogException("TOOLARY", "XLSIMPORT", e);
            lblError.Text = "Input file can not be processed. Is it in correct format? Contact pux.";
            lblError.Visible = true;

            plcData.Visible = false;
        }

        // delete temp file
        File.Delete(filesource);

        return ds;
    }

    #endregion

    #region "Import helper methods"

    protected void ProcessInputFile(string file, DataSet data)
    {
        string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + file + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";
        //string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=\"Excel 12.0;HDR=NO;\"";

        var conn = new OleDbConnection(connstr);
        var cmdImport = new OleDbCommand("SELECT * FROM [ERP_apps_import_sheet$]", conn);
        var daImport = new OleDbDataAdapter(cmdImport);

        // create DataTable with vendors
        DataTable vendorsTable = new DataTable("Vendors");
        daImport.Fill(vendorsTable);

        // remove header included in XLS file
        vendorsTable.Rows[0].Delete();
        vendorsTable.Rows[1].Delete();

        vendorsTable.AcceptChanges();

        // parse vendors and apps
        Dictionary<string, List<ImportApplication>> vendorsApps = new Dictionary<string, List<ImportApplication>>();
        foreach (DataRow row in vendorsTable.Rows)
        {
            string vendorKey = row[0].ToString();
            if (DataHelper.IsEmpty(vendorKey))
            {
                row.Delete();
                continue;
            }

            bool isNewRow = !vendorsApps.ContainsKey(vendorKey);

            if (isNewRow)
            {
                vendorsApps.Add(vendorKey, new List<ImportApplication>());
            }

            vendorsApps[vendorKey].Add(ParseApplication(row));

            if (!isNewRow)
            {
                row.Delete();
            }
        }
        vendorsTable.AcceptChanges();

        // load vendors into datatable and link them with applications
        LoadVendors(vendorsTable, vendorsApps);

        data.Tables.Add(vendorsTable);
    }

    private ImportApplication ParseApplication(DataRow row)
    {
        ImportApplication ia = new ImportApplication();
        ia.Name = ValidationHelper.GetString(row[8], "Application");
        ia.Description = ValidationHelper.GetString(row[14], "");
        ia.AddPlatform(row[9].ToString());
        ia.AddIndustry(row[10].ToString());
        ia.AddProcess(row[11].ToString());

        ia.SolutionType = row[12].ToString();
        ia.Certification = row[13].ToString();

        return ia;
    }

    private void LoadVendors(DataTable tableVendors, Dictionary<string, List<ImportApplication>> apps)
    {
        // add "Should import" info
        tableVendors.Columns.Add("ShouldImport", typeof(bool));
        tableVendors.Columns.Add("Apps", typeof(List<ImportApplication>));

        string vendorName;
        foreach (DataRow row in tableVendors.Rows)
        {
            vendorName = row["F1"].ToString();
            row["ShouldImport"] = !ExistsVendor(vendorName);
            row["Apps"] = (apps.ContainsKey(vendorName) ? apps[vendorName] : null);
        }
    }

    #region "Old code - previous import structure"

    /// <summary>
    /// Fills data set by data from excel file
    /// </summary>        
    //protected void ProcessInputFile(string file, DataSet data)
    //{
    //    string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + file + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";
    //    //string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=\"Excel 12.0;HDR=NO;\"";

    //    var conn = new OleDbConnection(connstr);
    //    var cmdVendors = new OleDbCommand("SELECT * FROM [Vendors$]", conn);
    //    var daVendors = new OleDbDataAdapter(cmdVendors);

    //    // create DataTable with vendors
    //    DataTable tableVendors = new DataTable("Vendors");
    //    daVendors.Fill(tableVendors);
    //    data.Tables.Add(tableVendors);

    //    // remove header included in XLS file
    //    tableVendors.Rows[0].Delete();
    //    tableVendors.AcceptChanges();

    //    //cmd = new OleDbCommand("SELECT * FROM [Applications$A3:AA10000]", conn);
    //    var cmdApplications = new OleDbCommand("SELECT * FROM [Applications$]", conn);
    //    var daApplications = new OleDbDataAdapter(cmdApplications);

    //    DataTable applications = new DataTable();
    //    daApplications.Fill(applications);

    //    // load vendors into datatable and link them with applications
    //    LoadVendors(tableVendors, LoadApplications(applications));
    //}

    //private void LoadVendors(DataTable tableVendors, Dictionary<string, List<ImportApplication>> apps)
    //{
    //    // add "Should import" info
    //    tableVendors.Columns.Add("ShouldImport", typeof(bool));
    //    tableVendors.Columns.Add("Apps", typeof(List<ImportApplication>));

    //    string vendorName;
    //    foreach (DataRow row in tableVendors.Rows)
    //    {
    //        vendorName = row["F1"].ToString();
    //        row["ShouldImport"] = !ExistsVendor(vendorName);
    //        row["Apps"] = (apps.ContainsKey(vendorName) ? apps[vendorName] : null);
    //    }
    //}

    //private Dictionary<string, List<ImportApplication>> LoadApplications(DataTable tmp)
    //{
    //    int i = -1;
    //    int j = -1;

    //    Dictionary<string, List<ImportApplication>> apps = new Dictionary<string, List<ImportApplication>>();

    //    string vendorName;
    //    ImportApplication application = null;

    //    Dictionary<int, string> industries = new Dictionary<int, string>();
    //    Dictionary<int, string> processes = new Dictionary<int, string>();
    //    Dictionary<int, string> platforms = new Dictionary<int, string>();

    //    foreach (DataRow row in tmp.Rows)
    //    {
    //        i++;
    //        if (i < 4)
    //        {
    //            // first four rows are header
    //            switch (i) {
    //                case 1:
    //                    LoadList(row, industries);
    //                    break;
    //                case 2:
    //                    LoadList(row, processes);
    //                    break;
    //                case 3:
    //                    LoadList(row, platforms);
    //                    break;
    //                default:
    //                    break;
    //            } 

    //            continue;
    //        }

    //        switch (i % 4)
    //        {
    //            case 0:
    //                // new vendor every 4th row
    //                vendorName = row["F1"].ToString();
    //                if (!apps.ContainsKey(vendorName))
    //                {
    //                    // new vendor
    //                    apps.Add(vendorName, new List<ImportApplication>());
    //                }

    //                application = new ImportApplication();
    //                application.Name = row["F2"].ToString();
    //                application.Description = row["F3"].ToString();
    //                apps[vendorName].Add(application);

    //                break;

    //            case 1:
    //                // industries
    //                j = -1;
    //                foreach (object value in row.ItemArray)
    //                {
    //                    j++;
    //                    if (DataHelper.IsEmpty(value) || !industries.ContainsKey(j))
    //                        continue;

    //                    application.AddIndustry(industries[j]);
    //                }
    //                break;

    //            case 2:
    //                // processes
    //                j = -1;
    //                foreach (object value in row.ItemArray)
    //                {
    //                    j++;
    //                    if (DataHelper.IsEmpty(value) || !processes.ContainsKey(j))
    //                        continue;

    //                    application.AddProcess(processes[j]);
    //                }
    //                break;

    //            default:
    //                // platforms
    //                j = -1;
    //                foreach (object value in row.ItemArray)
    //                {
    //                    j++;
    //                    if (DataHelper.IsEmpty(value) || !platforms.ContainsKey(j))
    //                        continue;

    //                    application.AddPlatform(platforms[j]);
    //                }
    //                break;
    //        }
    //    }

    //    return apps;
    //}

    //private void LoadList(DataRow row, Dictionary<int, string> list)
    //{
    //    int i = -1;

    //    foreach (object item in row.ItemArray)
    //    {
    //        i++;

    //        if (DataHelper.IsEmpty(item))
    //            continue;

    //        list[i] = item.ToString();
    //    }
    //}

#endregion

    protected bool IsValidFileExtension(string fileName)
    {
        string fileExtension = CMS.IO.Path.GetExtension(fileName).TrimStart('.').ToLower();

        List<string> allowedExtensions = new List<string>() { "xls" };

        return allowedExtensions.Contains(fileExtension);
    }

    protected string GetVendorAppsLabel(object apps)
    {
        if (apps == null || !(apps is List<ImportApplication>))
            return null;

        StringBuilder sb = new StringBuilder();
        foreach (ImportApplication app in apps as List<ImportApplication>)
        {
            sb.Append("<strong>");
            sb.Append(HTMLHelper.HTMLEncode(app.Name));
            sb.Append("</strong><br />");
            sb.Append("industries: ");
            sb.Append(app.GetIndustries().Join(", "));

            sb.Append("; processes: ");
            sb.Append(app.GetProcesses().Join(", "));

            sb.Append("; platforms: ");
            sb.Append(app.GetPlatforms().Join(", "));

            sb.Append("<br />");
        }

        return sb.ToString();
    }

    protected bool ExistsVendor(string name)
    {
        if (existingVendors == null)
        {
            LoadExistingVendors();
        }

        return existingVendors.ContainsKey(name.ToLower());
    }

    private void LoadExistingVendors()
    {
        existingVendors = new Dictionary<string, bool>();

        var nodes = TreeHelper.SelectNodes("/%", false, ERPConfig.DOCTYPE_VENDOR);
        if (nodes == null)
            return;

        foreach (TreeNode node in nodes)
        {
            string key = node.GetValue("VendorName").ToString().ToLower();

            if (!existingVendors.ContainsKey(key))
            {
                existingVendors.Add(key, true);
            }
        }
    }

    #endregion

    [Serializable]
    class ImportApplication
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public string SolutionType { get; set; }
        public string Certification { get; set; }

        protected List<string> industries = new List<string>();
        protected List<string> processes = new List<string>();
        protected List<string> platforms = new List<string>();

        public void AddIndustry(string value)
        {
            industries.Add(value);
        }

        public void AddProcess(string value)
        {
            processes.Add(value);
        }

        public void AddPlatform(string value)
        {
            platforms.Add(value);
        }

        public List<string> GetIndustries()
        {
            return industries;
        }

        public List<string> GetProcesses()
        {
            return processes;
        }
        public List<string> GetPlatforms()
        {
            return platforms;
        }
    }
}