using framework_business;
using framework_library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace scheduler
{
    class Program
    {

        private static StreamWriter _LogWriter;
        private static string LogPath = String.Empty;
        private static string LogFile = String.Empty;


        static void Main(string[] args)
        {
            if (args != null && args.Length >0) 
            {
                if (args[0].Contains("googlesheetimport"))
                {
                    var googleSheetImports = new GoogleSheetImports();
                    googleSheetImports.ExecuteGoogleSheetImportMethods(args);
                    LogWriter.DeleteOldLog("GoogleSheetImport", -14);
                    return;
                }
            }
            else
            {
                HandlePaletteScheduler();
            }

        }

        private static void HandlePaletteScheduler()
        {
            try
            {
                //setup culture and settigns
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(ConfigurationManager.AppSettings["Locale"], true);
                System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = ConfigurationManager.AppSettings["ShortDateFormat"];
                System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
                nfi.CurrencySymbol = ConfigurationManager.AppSettings["CurrencySymbol"];
                nfi.CurrencyPositivePattern = 2;
                nfi.CurrencyNegativePattern = 9;
                nfi.CurrencyDecimalDigits = 2;
                nfi.CurrencyDecimalSeparator = ConfigurationManager.AppSettings["DecimalSeparator"];
                nfi.CurrencyGroupSeparator = ConfigurationManager.AppSettings["GroupSeparator"];
                System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat = nfi;

                if (!Directory.Exists(Application.StartupPath + "\\logs\\"))
                    Directory.CreateDirectory(Application.StartupPath + "\\logs\\");

                LogPath = Application.StartupPath + "\\logs\\";

                // Delete files older than 14 days in folder
                LogWriter.DeleteOldLog("", -14);

                LogFile = "Scheduler_Log" + DateTime.Now.ToString("yyyyMMdd HH-mm-ss") + ".txt";

                _LogWriter = new StreamWriter(LogPath + LogFile);

                LogStatus("Starting of Palette scheduler...");
                //handle instance creation
                //TO DO

                //handle campaigns
                //if (ConfigurationManager.AppSettings["campaignHour"] != null)
                //{
                //    int thisHour = DateTime.Now.Hour;
                //    int configHour = Convert.ToInt32(ConfigurationManager.AppSettings["campaignHour"].ToString());
                //    if (thisHour == configHour)
                //    {
                LogStatus("campaign process started... ");
                HandleCampaigns();
                //    }
                //}

                //handle loyalty
                if (ConfigurationManager.AppSettings["loyaltyEmailHour"] != null)
                {
                    int thisHour = DateTime.Now.Hour;
                    int thisMinute = DateTime.Now.Minute;
                    int configHour = Convert.ToInt32(ConfigurationManager.AppSettings["loyaltyEmailHour"].ToString());
                    if (thisHour == configHour && thisMinute < 5)
                    {
                        LogStatus("loyalty process started... ");
                        HandleLoyalty();
                    }
                }

                //handle transaction lock
                LogStatus("transaction lock check started... ");
                HandleTransactionLock();

                //handle mediswitch queued responses
                LogStatus("mediswitch queued responses process started... ");
                HandleMediswitch();

                //CVH 2019-06-05 Surface Data Import
                //handle surface data import
                LogStatus("surface data import process started... ");
                HandleSurfaceDataImport();
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
            finally
            {
                LogStatus("Palette scheduler complete.. ");
                _LogWriter.Close();
            }
        }

        /// <summary>
        /// handle transaction lock
        /// </summary>
        private static void HandleTransactionLock()
        {
            try
            {
                DataTable databases = GetPaletteDatabases();
                //enumerate eacgh palette database
                foreach (DataRow dbrow in databases.Rows)
                {
                    string dbName = dbrow["name"].ToString();
                    oSetup _setup = new oSetup();
                    foreach (oSetup setup in utils.ConvertDataTableToList(GetSetup(dbName), typeof(oSetup)))
                    {
                        _setup = setup;
                        break;
                    }
                    if (_setup.GetType().GetProperty("isTransactionLock") != null
                        && _setup.isTransactionLock
                        && _setup.GetType().GetProperty("transactionLockDay") != null
                        && _setup.transactionLockDay == DateTime.Now.Day)
                    {
                        LogStatus("processing transaction lock for " + dbName + ".. ");
                        TransactionLock transactionLock = new TransactionLock(dbName);
                        LogStatus(transactionLock.LockTransactions());
                    }
                }
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
        }

        /// <summary>
        /// handle mediswitch queued responces
        /// </summary>
        private static void HandleMediswitch()
        {
            try
            {
                DataTable databases = GetPaletteDatabases();
                //enumerate eacgh palette database
                foreach (DataRow dbrow in databases.Rows)
                {
                    string dbName = dbrow["name"].ToString();
                    oSetup _setup = new oSetup();
                    foreach (oSetup setup in utils.ConvertDataTableToList(GetSetup(dbName), typeof(oSetup)))
                    {
                        _setup = setup;
                        break;
                    }
                    if (_setup.GetType().GetProperty("isMediswitch") != null && _setup.isMediswitch)
                    {
                        LogStatus("processing mediswitch queued responses for " + dbName + ".. ");

                        //CVH 2018-02-19 Add additional logging, doesn't seem to be processing delayed responses. 
                        MediSwitch mediswitch = new MediSwitch(dbName);
                        if (mediswitch.isOnline())
                        {
                            LogStatus("mediswitch is online.");
                            mediswitch.ProcessDelayed(_LogWriter);
                            LogStatus("mediswitch processing complete.");
                        }
                        else
                        {
                            LogStatus("mediswitch is NOT online.");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
        }

        /// <summary>
        /// handle loyalty emails
        /// </summary>
        private static void HandleLoyalty()
        {
            try
            {
                DataTable databases = GetPaletteDatabases();
                //enumerate each palette database
                foreach (DataRow dbrow in databases.Rows)
                {
                    string dbName = dbrow["name"].ToString();
                    oSetup _setup = new oSetup();
                    foreach (oSetup setup in utils.ConvertDataTableToList(GetSetup(dbName), typeof(oSetup)))
                    {
                        _setup = setup;
                        break;
                    }
                    if (_setup.GetType().GetProperty("isLoyalty") != null && _setup.isLoyalty &&
                        _setup.GetType().GetProperty("loyaltyEmailRecipients") != null && !string.IsNullOrEmpty(_setup.loyaltyEmailRecipients))
                    {
                        LogStatus("processing loyalty email for " + dbName + ".. ");
                        Loyalty loyalty = new Loyalty(dbName);
                        LogStatus(loyalty.SendLoyaltyMembers(_setup.loyaltyEmailRecipients));
                    }
                }
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
        }

        /// <summary>
        /// handle surface data import
        /// </summary>
        private static void HandleSurfaceDataImport()
        {
            try
            {
                DataTable databases = GetPaletteDatabases();
                //enumerate eacgh palette database
                foreach (DataRow dbrow in databases.Rows)
                {
                    try
                    {
                        string dbName = dbrow["name"].ToString();

                        //TEMPORARY REMOVE FOR LIVE
                        //if (dbName != "Framework_CAPI-1_Demo")
                        //    continue;

                        oSetup _setup = new oSetup();
                        foreach (oSetup setup in utils.ConvertDataTableToList(GetSetup(dbName), typeof(oSetup)))
                        {
                            _setup = setup;
                            break;
                        }

                        LogStatus("processing surface data import for " + dbName + ".. ");

                        if (!Directory.Exists(Application.StartupPath + "\\SurfaceDataImport\\" + _setup.code))
                            continue;

                        SurfaceDataImport import = new SurfaceDataImport(dbName, _setup);
                        LogStatus(import.Process(Application.StartupPath + "\\SurfaceDataImport\\" + _setup.code));
                    }
                    catch (Exception ex)
                    {
                        LogStatus(ex.Message);
                    }
                }
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
        }

        /// <summary>
        /// Method to handle campaigns for each palette intance
        /// </summary>
        public static void HandleCampaigns()
        {
            try
            {
                DataTable databases = GetPaletteDatabases();
                //enumerate each palette database
                foreach (DataRow dbrow in databases.Rows)
                {
                    string dbName = dbrow["name"].ToString();

                    oSetup _setup = new oSetup();
                    foreach (oSetup setup in utils.ConvertDataTableToList(GetSetup(dbName), typeof(oSetup)))
                    {
                        _setup = setup;
                        break;
                    }
                    string webPath = String.Empty;
                    if (_setup.securePath != string.Empty)
                        webPath = _setup.securePath;
                    else
                        webPath = "http://" + _setup.code + ".mypalette.co.za";

                    LogStatus("processing campaigns for " + dbName + ".. ");
                    Campaigns campaigns = new Campaigns(dbName, _setup, webPath);
                    //process campaigns
                    campaigns.Process();

                    //DataTable campaignTable = GetCampaigns(dbName);
                    ////enumerate each campaign
                    //foreach (oCampaign campaign in utils.ConvertDataTableToList(campaignTable, typeof(oCampaign)))
                    //{
                    //    bool useCampaign = false;
                    //    if (campaign.nextSend.Date < DateTime.Now.AddDays(1).Date)
                    //    {
                    //        if (campaign.isRecurring && campaign.lastSent.Date < DateTime.Now.Date)
                    //            useCampaign = true;
                    //        else if (campaign.lastSent.Year < 2016)//not recurrign but never sent before so lets send it
                    //        {
                    //            useCampaign = true;
                    //        }
                    //    }
                    //    if (useCampaign)
                    //    {
                    //        LogStatus("processing campaign " + campaign.campaignTitle + ".. ");

                    //        switch (campaign.campaignType)
                    //        {
                    //            #region subscriber communications
                    //            case 0://subscriber communication

                    //                switch (campaign.messageType)
                    //                {
                    //                    case 1://email
                    //                        foreach (oSubscriber sub in utils.ConvertDataTableToList(GetSubcribers(dbName), typeof(oSubscriber)))
                    //                        {
                    //                            LogStatus("Sending email to " + sub.name + "...");
                    //                            oEmail email = new oEmail();
                    //                            email.fromAddress = _setup.communicationEmail;
                    //                            email.toAddress = sub.email;
                    //                            email.Body = campaign.campaignBody;
                    //                            email.Subject = campaign.campaignTitle;
                    //                            email.Body = email.Body.Replace("{name}", sub.name);
                    //                            email.Body = email.Body.Replace("src=\"/images", "src=\"" + webPath + "/images");
                    //                            email.Body = email.Body.Replace("{WebAddress}", webPath);

                    //                            oCampaignMessage msg = new oCampaignMessage();
                    //                            msg.campaignId = campaign.recId;
                    //                            msg.message = email.Body;
                    //                            msg.messageType = 1;
                    //                            msg.mobile = "";
                    //                            msg.dateSent = DateTime.Now;
                    //                            if (communication.SendAnEmail(email))
                    //                            {
                    //                                msg.status = 1;
                    //                                msg.statusCode = 0;
                    //                                msg.statusDisplay = "email sent OK";
                    //                            }
                    //                            else
                    //                            {
                    //                                msg.status = -1;
                    //                                msg.statusCode = -1;
                    //                                msg.statusDisplay = "failed to send the email";
                    //                            }
                    //                            LogStatus(msg.statusDisplay);
                    //                            SaveCampaignMessage(dbName, msg);
                    //                        }
                    //                        break;
                    //                    case 2://sms

                    //                        foreach (oSubscriberSMS sub in utils.ConvertDataTableToList(GetSubcribersSMS(dbName), typeof(oSubscriberSMS)))
                    //                        {
                    //                            LogStatus("Sending sms to " + sub.name + "...");

                    //                            string SMSMessage = campaign.campaignBody;

                    //                            SMSMessage = SMSMessage.Replace("{name}", sub.name);

                    //                            oCampaignMessage msg = new oCampaignMessage();
                    //                            msg.campaignId = campaign.recId;
                    //                            msg.message = SMSMessage;
                    //                            msg.messageType = 2;
                    //                            msg.mobile = sub.mobile;
                    //                            msg.dateSent = DateTime.Now;
                    //                            msg.status = 1;

                    //                            long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), sub.mobile, SMSMessage);

                    //                            if (smsResult >= 0)
                    //                            {
                    //                                msg.statusCode = 0;
                    //                                msg.statusDisplay = "sms sent OK";
                    //                            }
                    //                            else
                    //                            {
                    //                                msg.statusCode = Convert.ToInt32(smsResult);
                    //                                msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
                    //                            }

                    //                            LogStatus(msg.statusDisplay);
                    //                            SaveCampaignMessage(dbName, msg);
                    //                        }
                    //                        break;
                    //                }


                    //                break;
                    //            #endregion
                    //            #region appointment reminders
                    //            case 1://appointemnt reminder
                    //                foreach (oCalendarEvent calEvnt in utils.ConvertDataTableToList(GetCalendarEvents(dbName), typeof(oCalendarEvent)))
                    //                {
                    //                    //get attendees for this event
                    //                    foreach (oUser usr in utils.ConvertDataTableToList(GetUsersForCalendar(calEvnt.calendarId, calEvnt.users, dbName, _setup), typeof(oUser)))
                    //                    {
                    //                        oCampaignMessage msg = new oCampaignMessage();
                    //                        LogStatus("Sending event reminder to " + usr.name + "...");
                    //                        switch (campaign.messageType)
                    //                        {
                    //                            case 1://email                                                  

                    //                                oEmail email = new oEmail();
                    //                                email.fromAddress = _setup.communicationEmail;
                    //                                email.toAddress = usr.email;
                    //                                email.Body = campaign.campaignBody;
                    //                                email.Subject = campaign.campaignTitle;
                    //                                email.Body = email.Body.Replace("{name}", usr.name);
                    //                                email.Body = email.Body.Replace("src=\"/images", "src=\"" + webPath + "/images");
                    //                                email.Body = email.Body.Replace("{WebAddress}", webPath);

                    //                                msg.campaignId = campaign.recId;
                    //                                msg.message = email.Body;
                    //                                msg.messageType = 1;
                    //                                msg.mobile = "";
                    //                                msg.dateSent = DateTime.Now;
                    //                                if (communication.SendAnEmail(email))
                    //                                {
                    //                                    msg.status = 1;
                    //                                    msg.statusCode = 0;
                    //                                    msg.statusDisplay = "email sent OK";
                    //                                }
                    //                                else
                    //                                {
                    //                                    msg.status = -1;
                    //                                    msg.statusCode = -1;
                    //                                    msg.statusDisplay = "failed to send the email";
                    //                                }
                    //                                LogStatus(msg.statusDisplay);
                    //                                SaveCampaignMessage(dbName, msg);

                    //                                break;
                    //                            case 2://sms

                    //                                string SMSMessage = campaign.campaignBody;

                    //                                SMSMessage = SMSMessage.Replace("{name}", usr.name);
                    //                                SMSMessage = SMSMessage.Replace("{startDate}", calEvnt.start.ToString("dd/MM/yyyy"));
                    //                                SMSMessage = SMSMessage.Replace("{startTime}", calEvnt.start.ToString("HH:mmtt"));
                    //                                SMSMessage = SMSMessage.Replace("{customer}", _setup.customer);
                    //                                msg.campaignId = campaign.recId;
                    //                                msg.message = SMSMessage;
                    //                                msg.messageType = 2;
                    //                                msg.mobile = usr.tel;
                    //                                msg.dateSent = DateTime.Now;
                    //                                msg.status = 1;

                    //                                long smsResult = xSendEasy.SendSMS(handler.ReturnConfigAppSetting("smsU"), handler.ReturnConfigAppSetting("smsP"), usr.tel, SMSMessage);

                    //                                if (smsResult >= 0)
                    //                                {
                    //                                    msg.statusCode = 0;
                    //                                    msg.statusDisplay = "sms sent OK";

                    //                                }
                    //                                else
                    //                                {
                    //                                    msg.statusCode = Convert.ToInt32(smsResult);
                    //                                    msg.statusDisplay = xSendEasy.ReturnErrorDetail(msg.statusCode);
                    //                                }

                    //                                LogStatus(msg.statusDisplay);
                    //                                SaveCampaignMessage(dbName, msg);

                    //                                break;
                    //                        }
                    //                    }

                    //                }
                    //                break;
                    //                #endregion
                    //        }
                    //        //update campaign after send to all recipients
                    //        campaign.dateUpdated = DateTime.Now;
                    //        campaign.lastSent = DateTime.Now;
                    //        if (campaign.isRecurring)
                    //        {
                    //            switch (campaign.interval)
                    //            {
                    //                case 1://daily
                    //                    campaign.nextSend = DateTime.Now.AddDays(1);
                    //                    break;
                    //                case 2://monthly
                    //                    campaign.nextSend = DateTime.Now.AddMonths(1);
                    //                    break;
                    //            }
                    //        }
                    //        //Update Campaign
                    //        if (UpdateCampaign(dbName, campaign))
                    //        {
                    //            LogStatus("campaign " + campaign.campaignTitle + " has been successfully processed.");
                    //        }
                    //    }
                    //}
                }
            }
            catch (Exception ex)
            {
                LogStatus(ex.Message);
            }
        }

        /// <summary>
        /// Datatable of Setup
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSetup(string dbName)
        {
            LogStatus("get setup " + dbName);
            DataTable result = new DataTable();
            SqlCommand command = new SqlCommand();
            try
            {
                string query = "SELECT * FROM pal_Setup ";
                command = CreateCommand(dbName, query);

                DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

                if (ds.Tables.Count > 0)
                    result = ds.Tables[0];
            }
            catch (Exception ex)
            {
                LogStatus("get setup error: " + ex.Message);

                Console.WriteLine(ex.Message);
            }
            finally
            {
                dataTier.CommandMethods.CompleteSQLCommand(ref command);
            }

            return result;
        }

        ///// <summary>
        ///// Datatable of Campigns for a database
        ///// </summary>
        ///// <returns></returns>
        //public static DataTable GetCampaigns(string dbName)
        //{
        //    DataTable result = new DataTable();
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        string query = "SELECT * FROm pal_Campaign WHERE isActive = '1' AND (isRecurring = '1' OR YEAR(lastSent) < 2015) ";
        //        command = CreateCommand(dbName, query);

        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //        if (ds.Tables.Count > 0)
        //            result = ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Datatable of Subscribers for Email
        /// </summary>
        /// <returns></returns>
        //public static DataTable GetSubcribers(string dbName)
        //{
        //    DataTable result = new DataTable();
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        string query = "SELECT * FROm pal_Subscriber WHERE isActive = '1' ";
        //        command = CreateCommand(dbName, query);

        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //        if (ds.Tables.Count > 0)
        //            result = ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Datatable of Subscribers for SMS
        /// </summary>
        /// <returns></returns>
        //public static DataTable GetSubcribersSMS(string dbName)
        //{
        //    DataTable result = new DataTable();
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        string query = "SELECT * FROm pal_SubscriberSMS WHERE isActive = '1' ";
        //        command = CreateCommand(dbName, query);

        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //        if (ds.Tables.Count > 0)
        //            result = ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Datatable of Calendar Events
        /// </summary>
        /// <returns></returns>
        //public static DataTable GetCalendarEvents(string dbName)
        //{
        //    DataTable result = new DataTable();
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        int eventTypeID = 0;
        //        string q = "Select recId FROM pal_CalendarEventType Where name LIKE '%Appointment%' ";
        //        command = CreateCommand(dbName, q);
        //        eventTypeID = dataTier.ReturnMethods.ReturnIntValue(ref command);

        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);

        //        string query = "SELECT * FROm pal_CalendarEvent WHERE calendarEventTypeId = " + eventTypeID + " AND (start > '" + utils.fixDate(DateTime.Now.AddDays(1).Date) + "' AND start < '" + utils.fixDate(DateTime.Now.AddDays(2).Date) + "') ";
        //        command = CreateCommand(dbName, query);

        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //        if (ds.Tables.Count > 0)
        //            result = ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Datatable of Users
        /// </summary>
        /// <returns></returns>
        //public static DataTable GetUsers(string users)
        //{
        //    DataTable result = new DataTable();
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        string query = "SELECT * FROm pal_User WHERE recId In (" + users + ") ";
        //        command = CreateCommand("Framework_EVOL-1", query);
        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //        if (ds.Tables.Count > 0)
        //            result = ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}


        /// <summary>
        /// Datatable of Users
        /// </summary>
        /// <returns></returns>
        //public static DataTable GetUsersForCalendar(int calendarId, string userIds, string dbName, oSetup _setup)
        //{

        //    DataTable result = new DataTable();

        //    SqlCommand command = new SqlCommand();
        //    try
        //    {
        //        //get module for this calendar
        //        string modQ = "Select * FROM pal_CalendarEventModule WHERE calendarId = " + calendarId + " ";
        //        command = CreateCommand(dbName, modQ);
        //        DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);

        //        foreach (DataRow row in ds.Tables[0].Rows)
        //        {
        //            if (row["moduleId"].ToString() == "3")
        //            {
        //                result = GetUsers(userIds);
        //            }
        //            else if (row["moduleId"].ToString() == "10")
        //            {
        //                command = CreateCommand(dbName, "");
        //                command.CommandType = CommandType.StoredProcedure;
        //                command.CommandText = "sp_GetSurfaceCalendarUsersByIDs";
        //                command.Parameters.Add(new SqlParameter("@SurfaceID", row["entityId"].ToString()));
        //                command.Parameters.Add(new SqlParameter("@NameField", row["entityNameField"].ToString()));
        //                command.Parameters.Add(new SqlParameter("@SurnameField", row["entitySurnameField"].ToString()));
        //                command.Parameters.Add(new SqlParameter("@EmailField", row["entityEmailField"].ToString()));
        //                command.Parameters.Add(new SqlParameter("@MobileField", row["entityMobileField"].ToString()));
        //                command.Parameters.Add(new SqlParameter("@ItemIDs", userIds));

        //                DataSet data = dataTier.ReturnMethods.ReturnDataSet(ref command);

        //                command.Parameters.Clear();
        //                ArrayList usrList = new ArrayList();
        //                if (data.Tables.Count > 0)
        //                {
        //                    foreach (DataRow uRow in data.Tables[0].Rows)
        //                    {
        //                        oUser usr = new oUser();
        //                        usr.customerCode = _setup.code;
        //                        usr.recId = int.Parse(uRow[0].ToString());
        //                        usr.name = uRow[3].ToString();
        //                        usr.surname = uRow[4].ToString();
        //                        usr.email = uRow[5].ToString();
        //                        usr.tel = uRow[6].ToString();
        //                        usrList.Add(usr);
        //                    }

        //                }
        //                result = utils.ConvertListToDataTable(usrList);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Datatable of Databases
        /// </summary>
        /// <returns></returns>
        public static DataTable GetPaletteDatabases()
        {
            DataTable result = new DataTable();
            SqlCommand command = new SqlCommand();
            try
            {
                string query = "SELECT name FROM master.dbo.sysdatabases Where name LIKE 'Framework_%'";
                command = CreateCommand("master", query);

                DataSet ds = dataTier.ReturnMethods.ReturnDataSet(ref command);

                if (ds.Tables.Count > 0)
                    result = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                dataTier.CommandMethods.CompleteSQLCommand(ref command);
            }

            return result;
        }

        /// <summary>
        /// Create a SQL Commane for a database name
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string dbName, string query)
        {
            string conn = handler.ReturnConfigAppSetting("conn");
            conn = conn.Replace("{catalog}", dbName);
            SqlConnection con = new SqlConnection(conn);
            con.Open();
            SqlCommand result = new SqlCommand(query, con);
            return result;
        }

        /// <summary>
        /// Save Campaign Message to database
        /// </summary>
        /// <param name="_msg"></param>
        /// <returns></returns>
        //public static bool SaveCampaignMessage(string dbName, oCampaignMessage _msg)
        //{
        //    bool result = false;
        //    string query = String.Empty;
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {

        //        if (_msg.recId > 0)
        //        {
        //            query = "UPDATE [dbo].[pal_CampaignMessage] " +
        //                       "SET [campaignId] = @campaignId " +
        //                       ",[message] = @message " +
        //                       ",[messageType] = @messageType " +
        //                       ",[email] = @email " +
        //                       ",[mobile] = @mobile " +
        //                       ",[dateSent] = @dateSent " +
        //                       ",[status] = @status " +
        //                       ",[statusCode] = @statusCode " +
        //                       ",[statusDisplay] = @statusDisplay " +
        //                        "WHERE [recId] = " + _msg.recId.ToString();
        //        }
        //        else
        //        {
        //            query = "INSERT INTO [dbo].[pal_CampaignMessage] " +
        //                       "([campaignId] " +
        //                       ",[message] " +
        //                       ",[messageType] " +
        //                       ",[email] " +
        //                       ",[mobile] " +
        //                       ",[dateSent] " +
        //                       ",[status] " +
        //                       ",[statusCode] " +
        //                       ",[statusDisplay]) " +
        //                 "VALUES " +
        //                       "(@campaignId " +
        //                       ",@message " +
        //                       ",@messageType " +
        //                       ",@email " +
        //                       ",@mobile " +
        //                       ",@dateSent " +
        //                       ",@status " +
        //                       ",@statusCode " +
        //                       ",@statusDisplay)  ";
        //        }


        //        //create command
        //        command = CreateCommand(dbName, query);
        //        command.Parameters.Add(new SqlParameter("@campaignId", _msg.campaignId));
        //        command.Parameters.Add(new SqlParameter("@message", utils.formatSqlString(_msg.message)));
        //        command.Parameters.Add(new SqlParameter("@messageType", _msg.messageType));
        //        command.Parameters.Add(new SqlParameter("@email", _msg.email));
        //        command.Parameters.Add(new SqlParameter("@mobile", _msg.mobile));
        //        command.Parameters.Add(new SqlParameter("@dateSent", utils.fixDate(_msg.dateSent)));
        //        command.Parameters.Add(new SqlParameter("@status", _msg.status));
        //        command.Parameters.Add(new SqlParameter("@statusCode", _msg.statusCode));
        //        command.Parameters.Add(new SqlParameter("@statusDisplay", utils.formatSqlString(_msg.statusDisplay)));
        //        //save campaign message
        //        result = dataTier.SavingMethods.UpdateRecord(ref command);
        //        command.Parameters.Clear();

        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Update a Campaign
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="_campaign"></param>
        /// <returns></returns>
        //public static bool UpdateCampaign(string dbName, oCampaign _campaign)
        //{
        //    bool result = false;
        //    string query = String.Empty;
        //    SqlCommand command = new SqlCommand();
        //    try
        //    {

        //        if (_campaign.recId > 0)
        //        {
        //            query = "UPDATE [dbo].[pal_Campaign] " +
        //                       "SET [campaignTitle] = @campaignTitle " +
        //                       ",[campaignBody] = @campaignBody " +
        //                       ",[campaignType] = @campaignType " +
        //                       ",[isRecurring] = @isRecurring " +
        //                       ",[interval] = @interval " +
        //                       ",[messageType] = @messageType " +
        //                       ",[lastSent] = @lastSent " +
        //                       ",[nextSend] = @nextSend " +
        //                       ",[dateUpdated] = @dateUpdated " +
        //                        "WHERE [recId] = " + _campaign.recId.ToString();

        //            //create command
        //            command = CreateCommand(dbName, query);
        //            command.Parameters.Add(new SqlParameter("@campaignTitle", _campaign.campaignTitle));
        //            command.Parameters.Add(new SqlParameter("@campaignBody", utils.formatSqlString(_campaign.campaignBody)));
        //            command.Parameters.Add(new SqlParameter("@campaignType", _campaign.campaignType));
        //            command.Parameters.Add(new SqlParameter("@isRecurring", _campaign.isRecurring));
        //            command.Parameters.Add(new SqlParameter("@interval", _campaign.interval));
        //            command.Parameters.Add(new SqlParameter("@messageType", _campaign.messageType));
        //            command.Parameters.Add(new SqlParameter("@lastSent", utils.fixDate(_campaign.lastSent)));
        //            command.Parameters.Add(new SqlParameter("@nextSend", utils.fixDate(_campaign.nextSend)));
        //            command.Parameters.Add(new SqlParameter("@dateUpdated", _campaign.dateUpdated));
        //            //save campaign
        //            result = dataTier.SavingMethods.UpdateRecord(ref command);
        //            command.Parameters.Clear();
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        dataTier.CommandMethods.CompleteSQLCommand(ref command);
        //    }

        //    return result;
        //}

        /// <summary>
        /// Log the status
        /// </summary>
        /// <param name="message"></param>
        public static void LogStatus(string message)
        {
            _LogWriter.Write(message + Environment.NewLine);
            Console.WriteLine(message + Environment.NewLine);
        }
    }
}