public void Should_Build_A_Traditional_Consolidated_Trial_Balance()
{
TrialBalanceCommand command = new TrialBalanceCommand();
command.AccountsChartUID = "b2328e67-3f2e-45b9-b1f6-93ef6292204e";
command.BalancesType = BalanceEngine.BalancesType.AllAccounts;
command.ConsolidateBalancesToTargetCurrency = false;
command.InitialPeriod.FromDate = TestingConstants.FromDate;
command.InitialPeriod.ToDate = TestingConstants.ToDate;
command.InitialPeriod.ExchangeRateDate = new DateTime(2021, 01, 15);
command.InitialPeriod.ExchangeRateTypeUID = "5923136d-8533-4975-81b9-c8ec3bf18dea";
command.InitialPeriod.ValuateToCurrrencyUID = "01";
//command.FinalPeriod.FromDate = new DateTime(2021, 02, 01);
//command.FinalPeriod.ToDate = new DateTime(2021, 02, 28);
//command.FinalPeriod.ExchangeRateDate = new DateTime(2021, 02, 15);
//command.FinalPeriod.ExchangeRateTypeUID = "5923136d-8533-4975-81b9-c8ec3bf18dea";
//command.FinalPeriod.ValuateToCurrrencyUID = "01";
command.ShowCascadeBalances = true;
command.TrialBalanceType = BalanceEngine.TrialBalanceType.BalanzaValorizadaComparativa;
command.FromAccount = "1101";
command.ToAccount = "1103";
command.Ledgers = new string[] { "2584a757-865c-2025-8025-fa633f200c49" };
TrialBalanceDto trialBalance = _usecases.BuildTrialBalance(command);
Assert.NotNull(trialBalance);
Assert.Equal(command, trialBalance.Command);
Assert.NotEmpty(trialBalance.Entries);
}
public ActionResult ExportsReports_Group()
{
ReportsDal objDal = new ReportsDal();
OrganizationService objIOrganizationService = new OrganizationService();
OrganizationDto organizationDto = objIOrganizationService.GetAll();
List <TrialBalanceDto> lstRecords = objDal.GetAllTrialBalanceReport_Group(GroupInfo.GroupID);
RefMaster rm = _dbContext.RefMasters.ToList().Find(r => r.RefMasterCode.ToUpper() == "AHTYPE");
RefValueMaster rvmIncomes = _dbContext.RefValueMasters.ToList().Find(f => f.RefMasterID == rm.RefMasterID && f.RefCode.ToUpper() == "INCOME");
RefValueMaster rvmExpenditure = _dbContext.RefValueMasters.ToList().Find(f => f.RefMasterID == rm.RefMasterID && f.RefCode.ToUpper() == "EXPENDITURE");
List <TrialBalanceDto> lstIncomes = lstRecords.FindAll(l => l.AhType == rvmIncomes.RefID);
List <TrialBalanceDto> lstExpenditure = lstRecords.FindAll(l => l.AhType == rvmExpenditure.RefID);
int recordCount = lstExpenditure.Count > lstIncomes.Count ? lstExpenditure.Count : lstIncomes.Count;
List <IncomeAndExpenditureModel> reportData = new List <IncomeAndExpenditureModel>();
for (int idx = 0; idx < recordCount; idx++)
{
TrialBalanceDto objAsset = lstIncomes.Count > idx ? lstIncomes[idx] : null;
TrialBalanceDto objLiability = lstExpenditure.Count > idx ? lstExpenditure[idx] : null;
IncomeAndExpenditureModel objIncomeAndExpenditureModel = new IncomeAndExpenditureModel();
objIncomeAndExpenditureModel.OrgName = organizationDto.OrgName;
objIncomeAndExpenditureModel.OrgAddress = organizationDto.Address;
if (objAsset != null)
{
objIncomeAndExpenditureModel.Income_AHCode = objAsset.Ahcode;
objIncomeAndExpenditureModel.Income_AHName = objAsset.Accounthaedname;
objIncomeAndExpenditureModel.Income_CrAmount = objAsset.Credit3;
objIncomeAndExpenditureModel.Income_DrAmount = objAsset.Debit3;
}
if (objLiability != null)
{
objIncomeAndExpenditureModel.Expenditure_AHCode = objLiability.Ahcode;
objIncomeAndExpenditureModel.Expenditure_AHName = objLiability.Accounthaedname;
objIncomeAndExpenditureModel.Expenditure_CrAmount = objLiability.Credit3;
objIncomeAndExpenditureModel.Expenditure_DrAmount = objLiability.Debit3;
}
reportData.Add(objIncomeAndExpenditureModel);
}
double incomeSum = reportData.Sum(l => l.Income_CrAmount - l.Income_DrAmount);
double expenditureSum = reportData.Sum(l => l.Expenditure_DrAmount - l.Expenditure_CrAmount);
reportData.ForEach(l => l.Expenditure_Sum = expenditureSum);
reportData.ForEach(l => l.Income_Sum = incomeSum);
ReportDocument rd = new ReportDocument();
rd.Load(Server.MapPath("~/Reports/FinancialRports/IncomeAndExpenditure.rpt"));
rd.SetDataSource(reportData);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/pdf", "IncomeAndExpenditure.pdf"));
}
public SingleObjectModel GetTrialBalance([FromBody] TrialBalanceCommand command)
{
base.RequireBody(command);
using (var usecases = TrialBalanceUseCases.UseCaseInteractor()) {
TrialBalanceDto trialBalance = usecases.BuildTrialBalance(command);
return(new SingleObjectModel(this.Request, trialBalance));
}
}
public SingleObjectModel GetExcelTrialBalance([FromBody] TrialBalanceCommand command)
{
base.RequireBody(command);
using (var usecases = TrialBalanceUseCases.UseCaseInteractor()) {
TrialBalanceDto trialBalance = usecases.BuildTrialBalance(command);
var excelExporter = new ExcelExporter();
ExcelFileDto excelFileDto = excelExporter.Export(trialBalance, command);
return(new SingleObjectModel(this.Request, excelFileDto));
}
}
public void Should_Build_A_Traditional_No_Consolidated_Trial_Balance()
{
TrialBalanceCommand command = new TrialBalanceCommand();
command.TrialBalanceType = BalanceEngine.TrialBalanceType.Balanza;
command.ShowCascadeBalances = true;
command.InitialPeriod.FromDate = TestingConstants.FromDate;
command.InitialPeriod.ToDate = TestingConstants.ToDate;
TrialBalanceDto trialBalance = _usecases.BuildTrialBalance(command);
Assert.NotNull(trialBalance);
Assert.Equal(command, trialBalance.Command);
Assert.NotEmpty(trialBalance.Entries);
}
public ExcelFileDto Export(TrialBalanceDto trialBalance, TrialBalanceCommand command)
{
Assertion.AssertObject(trialBalance, "trialBalance");
Assertion.AssertObject(command, "command");
var templateUID = $"TrialBalanceTemplate.{trialBalance.Command.TrialBalanceType}";
var templateConfig = ExcelTemplateConfig.Parse(templateUID);
var creator = new TrialBalanceExcelFileCreator(templateConfig);
ExcelFile excelFile = creator.CreateExcelFile(trialBalance);
return(ExcelFileMapper.Map(excelFile));
}
internal ExcelFile CreateExcelFile(TrialBalanceDto trialBalance)
{
Assertion.AssertObject(trialBalance, "trialBalance");
_excelFile = new ExcelFile(_templateConfig);
_excelFile.Open();
SetHeader(trialBalance.Command);
SetTable(trialBalance);
_excelFile.Save();
_excelFile.Close();
return(_excelFile);
}
public List <TrialBalanceDto> GetAllTrialBalanceReport(TrialBalanceTotalsDto objTotals = null)
{
if (objTotals == null)
{
objTotals = new TrialBalanceTotalsDto();
}
List <TrialBalanceDto> lstTrialBalance = new List <TrialBalanceDto>();
SqlCommand cmd = new SqlCommand("uspTrialBalanceReport", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrialBalanceDto obj = new TrialBalanceDto();
obj.Ahcode = dr["AHCODE"] == DBNull.Value ? string.Empty : dr["AHCODE"].ToString();
obj.Accounthaedname = dr["ACCOUNTHEADNAME"] == DBNull.Value ? string.Empty : Convert.ToString(dr["ACCOUNTHEADNAME"]);
obj.Debit1 = dr["OPENING_BALANCE_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_DEBIT"]);
obj.Credit1 = dr["OPENING_BALANCE_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_CREDIT"]);
obj.Debit2 = dr["TRAN_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_DEBIT"]);
obj.Credit2 = dr["TRAN_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_CREDIT"]);
obj.Debit3 = dr["CLOSING_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_DEBIT"]);
obj.Credit3 = dr["CLOSING_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_CREDIT"]);
lstTrialBalance.Add(obj);
}
if (dr.NextResult())
{
if (dr.Read())
{
objTotals.TotalRecords = dr["TOTAL_RECORDS"] == DBNull.Value ? default(int) : Convert.ToInt32(dr["TOTAL_RECORDS"]);
objTotals.OpeningBalDrSum = dr["OB_DR_Sum"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OB_DR_Sum"]);
objTotals.OpeningBalCrSum = dr["OB_CR_SUM"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OB_CR_SUM"]);
objTotals.TranBalDrSum = dr["TRAN_DR_SUM"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_DR_SUM"]);
objTotals.TranBalCrSum = dr["TRAN_CR_SUM"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_CR_SUM"]);
objTotals.ClosingBalDrSum = dr["CLOSING_DR_SUM"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_DR_SUM"]);
objTotals.ClosingBalCrSum = dr["CLOSING_CR_SUM"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_CR_SUM"]);
}
}
return(lstTrialBalance);
}
private void SetTable(TrialBalanceDto trialBalance)
{
switch (trialBalance.Command.TrialBalanceType)
{
case TrialBalanceType.AnaliticoDeCuentas:
FillOutAnaliticoDeCuentas(trialBalance.Entries.Select(x => (TwoColumnsTrialBalanceEntryDto)x));
return;
case TrialBalanceType.SaldosPorCuentaYMayor:
FillOutSaldosPorCuentayMayor(trialBalance.Entries.Select(x => (TrialBalanceEntryDto)x));
return;
case TrialBalanceType.Balanza:
case TrialBalanceType.BalanzaConAuxiliares:
case TrialBalanceType.Saldos:
case TrialBalanceType.SaldosPorAuxiliar:
case TrialBalanceType.SaldosPorCuenta:
FillOutBalanza(trialBalance.Entries.Select(x => (TrialBalanceEntryDto)x));
return;
default:
throw Assertion.AssertNoReachThisCode();
}
}
public List <TrialBalanceDto> GetAllTrialBalanceReport_Group(TrialBalanceTotalsDto objTotals = null)
{
OrganizationService objIOrganizationService = new OrganizationService();
OrganizationDto organizationDto = objIOrganizationService.GetAll();
int GroupID = GroupInfo != null ? GroupInfo.GroupID : 0;
if (objTotals == null)
{
objTotals = new TrialBalanceTotalsDto();
}
List <TrialBalanceDto> lstTrialBalance = new List <TrialBalanceDto>();
SqlCommand cmd = new SqlCommand("uspTrialBalanceReport", con);
cmd.Parameters.Add(new SqlParameter("GroupID", GroupID));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TrialBalanceDto obj = new TrialBalanceDto();
obj.OrganizationName = organizationDto.OrgName;
obj.OrganizationAddress = organizationDto.Address;
obj.Ahcode = dr["AHCODE"] == DBNull.Value ? string.Empty : dr["AHCODE"].ToString();
obj.AhType = dr["AHType"] == DBNull.Value ? default(Int32) : Convert.ToInt32(dr["AHType"]);
obj.Accounthaedname = dr["ACCOUNTHEADNAME"] == DBNull.Value ? string.Empty : Convert.ToString(dr["ACCOUNTHEADNAME"]);
obj.Debit1 = dr["OPENING_BALANCE_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_DEBIT"]);
obj.Credit1 = dr["OPENING_BALANCE_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_CREDIT"]);
obj.Debit2 = dr["TRAN_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_DEBIT"]);
obj.Credit2 = dr["TRAN_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_CREDIT"]);
obj.Debit3 = dr["CLOSING_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_DEBIT"]);
obj.Credit3 = dr["CLOSING_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_CREDIT"]);
lstTrialBalance.Add(obj);
}
if (dr.NextResult())
{
if (dr.Read())
{
if (Convert.ToInt32(dr["TOTAL_RECORDS"]) != 0)
{
objTotals.TotalRecords = Convert.ToInt32(dr["TOTAL_RECORDS"]);
}
if (dr["OB_DR_Sum"] != DBNull.Value)
{
objTotals.OpeningBalDrSum = Convert.ToDouble(dr["OB_DR_Sum"]);
}
if (dr["OB_CR_SUM"] != DBNull.Value)
{
objTotals.OpeningBalCrSum = Convert.ToDouble(dr["OB_CR_SUM"]);
}
if (dr["TRAN_DR_SUM"] != DBNull.Value)
{
objTotals.TranBalDrSum = Convert.ToDouble(dr["TRAN_DR_SUM"]);
}
if (dr["TRAN_CR_SUM"] != DBNull.Value)
{
objTotals.TranBalCrSum = Convert.ToDouble(dr["TRAN_CR_SUM"]);
}
if (dr["CLOSING_DR_SUM"] != DBNull.Value)
{
objTotals.ClosingBalDrSum = Convert.ToDouble(dr["CLOSING_DR_SUM"]);
}
if (dr["CLOSING_CR_SUM"] != DBNull.Value)
{
objTotals.ClosingBalCrSum = Convert.ToDouble(dr["CLOSING_CR_SUM"]);
}
}
}
return(lstTrialBalance);
}
public List <TrialBalanceDto> GetAllTrialBalanceReport_Group(int groupID, TrialBalanceTotalsDto objTotals = null)
{
if (objTotals == null)
{
objTotals = new TrialBalanceTotalsDto();
}
List <TrialBalanceDto> lstTrialBalance = new List <TrialBalanceDto>();
AdoHelper objado = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupID", groupID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader dr = objado.ExecDataReaderProc("uspTrialBalanceReport", parms);
while (dr.Read())
{
TrialBalanceDto obj = new TrialBalanceDto();
obj.Ahcode = dr["AHCODE"] == DBNull.Value ? string.Empty : dr["AHCODE"].ToString();
obj.AhType = dr["AHType"] == DBNull.Value ? default(Int32) : Convert.ToInt32(dr["AHType"]);
obj.Accounthaedname = dr["ACCOUNTHEADNAME"] == DBNull.Value ? string.Empty : Convert.ToString(dr["ACCOUNTHEADNAME"]);
obj.Debit1 = dr["OPENING_BALANCE_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_DEBIT"]);
obj.Credit1 = dr["OPENING_BALANCE_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["OPENING_BALANCE_CREDIT"]);
obj.Debit2 = dr["TRAN_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_DEBIT"]);
obj.Credit2 = dr["TRAN_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["TRAN_CREDIT"]);
obj.Debit3 = dr["CLOSING_DEBIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_DEBIT"]);
obj.Credit3 = dr["CLOSING_CREDIT"] == DBNull.Value ? default(double) : Convert.ToDouble(dr["CLOSING_CREDIT"]);
lstTrialBalance.Add(obj);
}
if (dr.NextResult())
{
if (dr.Read())
{
if (Convert.ToInt32(dr["TOTAL_RECORDS"]) != 0)
{
objTotals.TotalRecords = Convert.ToInt32(dr["TOTAL_RECORDS"]);
}
if (dr["OB_DR_Sum"] != DBNull.Value)
{
objTotals.OpeningBalDrSum = Convert.ToDouble(dr["OB_DR_Sum"]);
}
if (dr["OB_CR_SUM"] != DBNull.Value)
{
objTotals.OpeningBalCrSum = Convert.ToDouble(dr["OB_CR_SUM"]);
}
if (dr["TRAN_DR_SUM"] != DBNull.Value)
{
objTotals.TranBalDrSum = Convert.ToDouble(dr["TRAN_DR_SUM"]);
}
if (dr["TRAN_CR_SUM"] != DBNull.Value)
{
objTotals.TranBalCrSum = Convert.ToDouble(dr["TRAN_CR_SUM"]);
}
if (dr["CLOSING_DR_SUM"] != DBNull.Value)
{
objTotals.ClosingBalDrSum = Convert.ToDouble(dr["CLOSING_DR_SUM"]);
}
if (dr["CLOSING_CR_SUM"] != DBNull.Value)
{
objTotals.ClosingBalCrSum = Convert.ToDouble(dr["CLOSING_CR_SUM"]);
}
}
}
return(lstTrialBalance);
}
