using Exceptionless.Json;
using System.Collections.Generic;
using System.Data.SqlClient;
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;
public ARCommissionModel()
queryBuilder = new ARCommissionQueryBuilder();
mapper = new ARCommissionMapper();
internal async Task<Dictionary<int, List<ARCommissionEntity>>> Get(BasicGetEntity basicGetEntity)
var plans = new List<ARCommissionEntity>();
var resultSet = new Dictionary<int, List<ARCommissionEntity>>();
using (var con = SQLHelper.GetConnection())
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())
count = Utility.MapInt(reader, "RowsCount").Value;
plans.Add(mapper.MapARCommissionPlanReader(reader));
resultSet.Add(count, plans);
internal async Task<Guid> Save(ARCommissionEntity plans)
using (var con = SQLHelper.GetConnection())
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))));
internal async Task Update(ARCommissionEntity plans)
using (var con = SQLHelper.GetConnection())
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))));
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())
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"))));
internal async Task<Dictionary<int, List<ARCommRates>>> GetRate(BasicGetEntity basicGetEntity, Guid id)
var rate = new List<ARCommRates>();
var resultSet = new Dictionary<int, List<ARCommRates>>();
using (var con = SQLHelper.GetConnection())
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())
count = Utility.MapInt(reader, "RowsCount").Value;
rate.Add(mapper.MapARCommissionRateReader(reader));
resultSet.Add(count, rate);
internal async Task<Guid> SaveRate(ARCommRates rate)
using (var con = SQLHelper.GetConnection())
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))));
internal async Task UpdateRate(ARCommRates rate)
using (var con = SQLHelper.GetConnection())
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))));
internal async Task DeleteRate(Guid id, Guid bracketId)
using (var con = SQLHelper.GetConnection())
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"))));