using Exceptionless.Json;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Net;

using System.Text;

using System.Threading.Tasks;

using Xcd.ERP.API.Common;

using Xcd.ERP.API.Helper;

using Xcd.ERP.API.Modules.Common;

​

namespace Xcd.ERP.API.Modules.AccountsReceivable.ARCommissionPlans

{

    public class ARCommissionModel

    {

        private ARCommissionQueryBuilder queryBuilder;

        private ARCommissionMapper mapper;

​

        /// <summary>

        /// Constructor of the ARClasses model.

        /// </summary>

        public ARCommissionModel()

        {

            queryBuilder = new ARCommissionQueryBuilder();

            mapper = new ARCommissionMapper();

        }

​

        #region CommissionPlan

​

        /// <summary>

        ///  Get all ARCommission Plans data.

        /// </summary>

        /// <param name="offset">Offset for the page.</param>

        /// <param name="limit">Limit for the page.</param>

        /// <param name="options">Choose either dropdown or getAll.</param>

        /// <returns>Returns the collection of the items based on the given.</returns>

        internal async Task<Dictionary<int, List<ARCommissionEntity>>> Get(BasicGetEntity basicGetEntity)

        {

            var count = 0;

            var plans = new List<ARCommissionEntity>();

            var resultSet = new Dictionary<int, List<ARCommissionEntity>>();

​

​

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

​

                var sb = new StringBuilder(queryBuilder.getARCommissionPlanQuery());

                sb.AppendLine(ColunmSorting.SortMethod(basicGetEntity, "commission-plans", "PlanCode"));

​

                var command = new SqlCommand(sb.ToString(), con);

                command.Parameters.AddWithValue("@offset", basicGetEntity.offset);

                command.Parameters.AddWithValue("@limit", (basicGetEntity.limit > 0) ? basicGetEntity.limit : 10);

​

                using (var reader = await command.ExecuteReaderAsync())

                {

                    while (reader.Read())

                    {

                        count = Utility.MapInt(reader, "RowsCount").Value;

                        plans.Add(mapper.MapARCommissionPlanReader(reader));

                    }

                    resultSet.Add(count, plans);

                    return resultSet;

                }

            }

        }

​

        /// <summary>

        /// Create new ARCommission Plans data.

        /// </summary>

        /// <param name="arClasses">AR Classes object</param>

        /// <returns>Returns the response message.</returns>

        internal async Task<Guid> Save(ARCommissionEntity plans)

        {

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

​

                plans.CommPlanId = Guid.NewGuid();

                var command = new SqlCommand(queryBuilder.saveARCommissionPlanQuery(), con);

                mapper.SqlQueryParameter(command, plans);

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception(CommonMessages.NotCreated))));

                }

                return plans.CommPlanId;

            }

        }

​

        /// <summary>

        /// Modify existing ARCommission Plans data.

        /// </summary>

        /// <param name="plans">ARClasses object</param>

        /// <returns>Returns the response message.</returns>

        internal async Task Update(ARCommissionEntity plans)

        {

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

                var command = new SqlCommand(queryBuilder.updateARCommissionPlanQuery(), con);

​

                mapper.SqlQueryParameter(command, plans);

​

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception(CommonMessages.NoDataChange))));

                }

​

            }

        }

​

        /// <summary>

        /// 

        /// </summary>

        /// <param name="id"></param>

        /// <returns></returns>

        internal async Task Delete(Guid id)

        {

            string pkTable = "AR_COMM_PLAN";

            string skipTables = "AR_COMM_RATES";

            var validate = new ValidationModel();

            await validate.ValidatePkTables(id, pkTable, skipTables);

​

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

​

                var command = new SqlCommand(queryBuilder.deleteARCommissionPlanQuery(), con);

                command.Parameters.AddWithValue("@CommPlanId", Utility.GetSQLParameterValue(id));

​

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception("Not able to delete the records"))));

                }

            }

        }

        #endregion

​

        #region CommissionRate

​

        /// <summary>

        ///  Get all ARCommission Rate data.

        /// </summary>

        /// <param name="offset">Offset for the page.</param>

        /// <param name="limit">Limit for the page.</param>

        /// <returns>Returns the collection of the items based on the given.</returns>

        internal async Task<Dictionary<int, List<ARCommRates>>> GetRate(BasicGetEntity basicGetEntity, Guid id)

        {

            var count = 0;

            var rate = new List<ARCommRates>();

            var resultSet = new Dictionary<int, List<ARCommRates>>();

​

​

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

​

                var sb = new StringBuilder(queryBuilder.getARCommissionRateQuery());

                sb.AppendLine(ColunmSorting.SortMethod(basicGetEntity, "commission-rate", "Bracket", false));

​

                var command = new SqlCommand(sb.ToString(), con);

                command.Parameters.AddWithValue("@offset", basicGetEntity.offset);

                command.Parameters.AddWithValue("@limit", (basicGetEntity.limit > 0) ? basicGetEntity.limit : 10);

                command.Parameters.AddWithValue("@CommPlanId", id);

​

                using (var reader = await command.ExecuteReaderAsync())

                {

                    while (reader.Read())

                    {

                        count = Utility.MapInt(reader, "RowsCount").Value;

                        rate.Add(mapper.MapARCommissionRateReader(reader));

                    }

                    resultSet.Add(count, rate);

                    return resultSet;

                }

            }

        }

​

        /// <summary>

        /// Create new ARCommission Rate data.

        /// </summary>

        /// <param name="arClasses">AR Classes object</param>

        /// <returns>Returns the response message.</returns>

        internal async Task<Guid> SaveRate(ARCommRates rate)

        {

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

​

                rate.BracketId = Guid.NewGuid();

                var command = new SqlCommand(queryBuilder.saveARCommissionRateQuery(), con);

                mapper.SqlQueryParameter(command, rate);

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception(CommonMessages.NotCreated))));

                }

                return rate.BracketId;

            }

        }

​

        /// <summary>

        /// Modify existing ARCommission Rate data.

        /// </summary>

        /// <param name="rate">Comm Rate object</param>

        /// <returns>Returns the response message.</returns>

        internal async Task UpdateRate(ARCommRates rate)

        {

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

                var command = new SqlCommand(queryBuilder.updateARCommissionRateQuery(), con);

​

                mapper.SqlQueryParameter(command, rate);

​

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception(CommonMessages.NoDataChange))));

                }

​

            }

        }

​

        /// <summary>

        /// 

        /// </summary>

        /// <param name="id"></param>

        /// <param name="bracketId"></param>

        /// <returns></returns>

        internal async Task DeleteRate(Guid id, Guid bracketId)

        {

            using (var con = SQLHelper.GetConnection())

            {

                con.Open();

                var command = new SqlCommand(queryBuilder.deleteARCommissionRateQuery(), con);

                command.Parameters.AddWithValue("@CommPlanId", Utility.GetSQLParameterValue(id));

                command.Parameters.AddWithValue("@BracketId", Utility.GetSQLParameterValue(bracketId));

​

                if (await command.ExecuteNonQueryAsync() < 1)

                {

                    throw new Exception(HttpStatusCode.NotFound.ToString(), new Exception(JsonConvert.SerializeObject(new Exception("Not able to delete the records"))));

                }

            }

        }

        #endregion

    }

}