namespace VECV_WebApi.Models.EscalationMatrix
{
    #region Namespaces

    using DBHelper;
    using LoggingHelper;
    using Npgsql;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using VECV_WebApi.Models.Authorization;

    #endregion

    #region Repository Class

    /// <summary>
    /// This class 
    /// 
    /// Escalation Matrix related methods
    /// </summary>
    public class EscalationMatrixRepository
    {
        #region Global Variables

        /// <summary>
        /// making object of LoggingUtility class available to this class
        /// </summary>
        LoggingUtility objLog = new LoggingUtility();

        /// <summary>
        /// making the data-log file path available to this class
        /// </summary>
        string path = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["PathLog"]);

        /// <summary>
        /// making error log file path available to this class
        /// </summary>
        string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);

        /// <summary>
        /// making the Database connection string available to this class
        /// </summary>
        private string _connStr;

        /// <summary>
        /// making Authentication Repository object available to this class
        /// </summary>
        AuthenticationRepository objAuthorization;

        #endregion

        #region Contructors

        /// <summary>
        /// Default constructor intialize connection string of tracking database 
        /// </summary>
        public EscalationMatrixRepository(string connString)
        {
            this._connStr = connString;
        }

        #endregion

        #region API Methods

        /// <summary>
        /// To Insert, 
        /// and get Escalation Matrix
        /// </summary>
        /// <param name="model">Represent object of EscalationMatrixModel class contain escalation matrix info</param>
        /// <returns>Status of record insertion, update, delete and read</returns>
        public EscalationMatrixModel InserUpdateGetEscalationMatrix(EscalationMatrixModel model)
        {
            EscalationMatrixModel objModel = new EscalationMatrixModel();
            try
            {
                DataSet ds = new DataSet();

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Check action, what we want to perform like insert,update or delete.
                    if (model.Action.Trim().ToUpper() == "INSERT" || model.Action.Trim().ToUpper() == "UPDATE" || model.Action.Trim().ToUpper() == "DELETE")
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[20];
                        nSqlParam[0] = new NpgsqlParameter("inid ", model.id);
                        nSqlParam[1] = new NpgsqlParameter("inmanager_name ", model.manager_name);
                        nSqlParam[2] = new NpgsqlParameter("inregion_name ", model.region_name);
                        nSqlParam[3] = new NpgsqlParameter("inorganization_id ", model.organization_id);
                        nSqlParam[4] = new NpgsqlParameter("incity ", model.city);
                        nSqlParam[5] = new NpgsqlParameter("instate ", model.state);
                        nSqlParam[6] = new NpgsqlParameter("indealer_name ", model.dealer_name_id);
                        nSqlParam[7] = new NpgsqlParameter("inmanager_level ", model.manager_level);
                        nSqlParam[8] = new NpgsqlParameter("inblind_spot ", model.blind_spot);
                        nSqlParam[9] = new NpgsqlParameter("inemails", model.emails);
                        nSqlParam[10] = new NpgsqlParameter("inphones ", model.phones);
                        nSqlParam[11] = new NpgsqlParameter("invan_type ", model.van_type);
                        nSqlParam[12] = new NpgsqlParameter("inalarm_age ", model.alarm_age);
                        nSqlParam[13] = new NpgsqlParameter("innotification_type ", model.notification_type);
                        nSqlParam[14] = new NpgsqlParameter("inalarm_age_sla_not_met ", model.alarm_age_sla_not_met);
                        nSqlParam[15] = new NpgsqlParameter("inalarm_age_ticket_not_closed ", model.alarm_age_ticket_not_closed);
                        nSqlParam[16] = new NpgsqlParameter("inalarm_age_van_not_live ", model.alarm_age_van_not_live);
                        nSqlParam[17] = new NpgsqlParameter("inaction ", model.Action);
                        nSqlParam[18] = new NpgsqlParameter("in_alarm_age_eicher_promise_48hrs ", model.alarm_age_eicher_promise_48hrs);
                        nSqlParam[19] = new NpgsqlParameter("inall_updation_bit", Convert.ToInt32(model.editEscalationAll));

                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_insert_or_update_escalation"], nSqlParam);

                        objModel.Status = ds.Tables[0].Rows[0]["sp_insert_or_update_escalation"].ToString();
                        if (objModel.Status == "2")
                        {
                            // if status return 2,it repersent to duplicate record.
                            objModel.Message = "Already Exists";
                        }
                        else
                        {
                            objModel.Message = "Saved Sucessfully";
                        }
                    }
                    else if (model.Action.Trim().ToUpper() == "GET")
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                        nSqlParam[0] = new NpgsqlParameter("inid ", model.id);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_escalation_matrix"], nSqlParam);

                        objModel.EscalationMatrixList = ds.Tables[0].AsEnumerable().Select(s => new EscalationMatrix
                        {
                            id = s.Field<int?>("_id"),
                            manager_name = s.Field<string>("_manager_name"),
                            region_name = s.Field<string>("_region_name"),
                            organization_id = s.Field<int?>("_organization_id"),
                            city = s.Field<string>("_city"),
                            state = s.Field<string>("_state"),
                            dealer_name_id = s.Field<string>("_dealer_name_id"),
                            dealer_name = s.Field<string>("_dealer_name"),
                            manager_level = s.Field<string>("_manager_level"),
                            blind_spot = s.Field<string>("_blind_spot"),
                            emails = s.Field<string>("_emails"),
                            phones = s.Field<string>("_phones"),
                            van_type = s.Field<string>("_van_type"),
                            alarm_age = s.Field<int?>("_alarm_age"),
                            notification_type = s.Field<string>("_notification_type"),
                            alarm_age_sla_not_met = s.Field<int?>("_alarm_age_sla_not_met"),
                            alarm_age_ticket_not_closed = s.Field<int?>("_alarm_age_ticket_not_closed"),
                            organization_name = s.Field<string>("_organization_name"),
                            alarm_age_van_not_live = s.Field<int?>("_alarm_age_van_not_live"),
                            alarm_age_eicher_promise_48hrs = s.Field<int?>("_alarm_age_eicher_promise_48hrs")

                        }).ToList();

                        objModel.Status = "1";
                    }
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();

                }
                return objModel;

            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString() + "  " + Ex.Message;
                objLog.ErrorLogFile("InserUpdateGetEscalationMatrix", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }

        public EscalationMatrixModel UpdateEscalationPhoneNUmber(EscalationPhoneNumber model)
        {
            EscalationMatrixModel objModel = new EscalationMatrixModel();
            try
            {
                DataSet ds = new DataSet();

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("inphones ", model.NewContactNumber);
                    nSqlParam[1] = new NpgsqlParameter("inold_phones ", model.OLdContactNumber);
                    nSqlParam[2] = new NpgsqlParameter("inmanager_name ", model.NewManagerName);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_insert_or_update_escalation"], nSqlParam);

                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();

                }

                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString() + "  " + Ex.Message;
                objLog.ErrorLogFile("UpdateEscalationPhoneNUmber", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }

        #region manage escalation get function with server side paging and filter

        /// <summary>
        /// To Insert, Update and get Escalation Matrix with server side paging and filter
        /// </summary>
        /// <param name="model">Represent object of EscalationMatrixModel class contain escalation matrix info</param>
        /// <returns>Status of record insertion, update, delete and read</returns>
        public EscalationMatrixModel GetEscalationMatrix(EscalationMatrixModel model)
        {
            EscalationMatrixModel objModel = new EscalationMatrixModel();
            try
            {
                DataSet ds = new DataSet();

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    if (model.Action.Trim().ToLower() == "count")
                    {
                        // Get closed ticket record count.

                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                        nSqlParam[0] = new NpgsqlParameter("inorganization_id ", model.organization_id);
                        //nSqlParam[1] = new NpgsqlParameter("inlimit", model.Limit);
                        //nSqlParam[2] = new NpgsqlParameter("inoffset", model.OffSet);
                        nSqlParam[1] = new NpgsqlParameter("incondition", model.Condition);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, "sp_get_escalation_matrix_count", nSqlParam);
                    }
                    else
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[4];
                        nSqlParam[0] = new NpgsqlParameter("inorganization_id ", model.organization_id);
                        nSqlParam[1] = new NpgsqlParameter("inlimit", model.Limit);
                        nSqlParam[2] = new NpgsqlParameter("inoffset", model.OffSet);
                        nSqlParam[3] = new NpgsqlParameter("incondition", model.Condition);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_escalation_matrix"], nSqlParam);
                    }

                    if (model.Action.Trim().ToLower() == "count")
                    {
                        objModel.TicketCount = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        objModel.EscalationMatrixList = ds.Tables[0].AsEnumerable().Select(s => new EscalationMatrix
                        {
                            id = s.Field<int?>("_id"),
                            manager_name = s.Field<string>("_manager_name"),
                            region_name = s.Field<string>("_region_name"),
                            organization_id = s.Field<int?>("_organization_id"),
                            city = s.Field<string>("_city"),
                            state = s.Field<string>("_state"),
                            dealer_name_id = s.Field<string>("_dealer_name_id"),
                            dealer_name = s.Field<string>("_dealer_name"),
                            manager_level = s.Field<string>("_manager_level"),
                            blind_spot = s.Field<string>("_blind_spot"),
                            emails = s.Field<string>("_emails"),
                            phones = s.Field<string>("_phones"),
                            van_type = s.Field<string>("_van_type"),
                            alarm_age = s.Field<int?>("_alarm_age"),
                            notification_type = s.Field<string>("_notification_type"),
                            alarm_age_sla_not_met = s.Field<int?>("_alarm_age_sla_not_met"),
                            alarm_age_ticket_not_closed = s.Field<int?>("_alarm_age_ticket_not_closed"),
                            organization_name = s.Field<string>("_organization_name"),
                            alarm_age_van_not_live = s.Field<int?>("_alarm_age_van_not_live"),
                            alarm_age_eicher_promise_48hrs = s.Field<int?>("_alarm_age_eicher_promise_48hrs")

                        }).ToList();

                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();

                }
                return objModel;

            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString() + "  " + Ex.Message;
                objLog.ErrorLogFile("GetEscalationMatrix", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }


        #endregion

        #endregion

    }
    #endregion
}