public async Task <QueriesDataResponse> GetQueriesNReportsNameList(string categoryId)
{
CategoriesDal categoriesDal = new CategoriesDal();
ReportsDal reportsDal = new ReportsDal();
var responseData = new QueriesDataResponse();
List <QueryData> Querieslst = new List <QueryData>();
int categoryIdNum = 0;
int.TryParse(categoryId, out categoryIdNum);
var category = categoriesDal.GetCategoryById(categoryIdNum);
var queriesData = await HttpProvider.GetHttpRequest(StaticParams.TfsUrl, "Enterprise/_apis/wit/queries/Shared%20Queries/Release%20Dashboard/Reports/" + category.CategoryName + "?$depth=1");
QueriesDataRequest queriesDataObject = JsonConvert.DeserializeObject <QueriesDataRequest>(queriesData);
foreach (var item in queriesDataObject.children)
{
var queryData = new QueryData()
{
Id = item.id, Name = item.name
};
Querieslst.Add(queryData);
}
responseData.AllAvailableQueries = Querieslst.ToArray();
responseData.AllReports = reportsDal.GetReportsByCategoryId(categoryIdNum).ToArray();
return(responseData);
}
public ActionResult BalanceSheet_Group()
{
ReportsDal objDal = new ReportsDal();
List <TrialBalanceDto> lstRecords = objDal.GetAllTrialBalanceReport_Group(GroupInfo.GroupID);
RefMaster rm = _dbContext.RefMasters.ToList().Find(r => r.RefMasterCode.ToUpper() == "AHTYPE");
RefValueMaster rvmAssets = _dbContext.RefValueMasters.ToList().Find(f => f.RefMasterID == rm.RefMasterID && f.RefCode.ToUpper() == "ASSETS");
RefValueMaster rvmLiabilities = _dbContext.RefValueMasters.ToList().Find(f => f.RefMasterID == rm.RefMasterID && f.RefCode.ToUpper() == "LIABILITIES");
List <TrialBalanceDto> lstAssets = lstRecords.FindAll(l => l.AhType == rvmAssets.RefID);
List <TrialBalanceDto> lstLiabilities = lstRecords.FindAll(l => l.AhType == rvmLiabilities.RefID);
int RecordCount = lstRecords.Count > lstLiabilities.Count ? lstRecords.Count : lstLiabilities.Count;
ViewBag.RecordCount = RecordCount;
ViewBag.Assets = lstAssets;
ViewBag.Liabilities = lstLiabilities;
var Debit1 = lstAssets.Sum(l => l.Debit3);
var Credit1 = lstAssets.Sum(l => l.Credit3);
ViewBag.TotalAssetsBalance = Debit1 - Credit1;
var Debit2 = lstLiabilities.Sum(M => M.Debit3);
var Credit2 = lstLiabilities.Sum(M => M.Credit3);
ViewBag.TotalLiabilitiesbalance = Credit2 - Debit2;
return(View("BalanceSheet"));
}
public ActionResult IncomeAndExpenditure_Group()
{
ReportsDal objDal = new ReportsDal();
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 = lstRecords.Count > lstExpenditure.Count ? lstRecords.Count : lstExpenditure.Count;
ViewBag.RecordCount = RecordCount;
ViewBag.Incomes = lstIncomes;
ViewBag.Expenditure = lstExpenditure;
var Debit1 = lstIncomes.Sum(l => l.Debit3);
var Credit1 = lstIncomes.Sum(l => l.Credit3);
ViewBag.TotalIncomesBalance = Debit1 + Credit1;
var Debit2 = lstExpenditure.Sum(M => M.Debit3);
var Credit2 = lstExpenditure.Sum(M => M.Credit3);
ViewBag.TotalExpenditurebalance = Debit2 + Credit2;
return(View("IncomeAndExpenditure"));
}
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"));
}
private static ReportChartModel GetCart(string categoryName, string reportName, string chartName)
{
ReportChartModel currentChart = null;
CategoriesDal categoryDal = new CategoriesDal();
var categoryId = categoryDal.GetCategoryIdByName(categoryName);
if (categoryId > 0)
{
ReportsDal reportDal = new ReportsDal();
var reportId = reportDal.GetReportIdByNameAndCategoryId(categoryId, reportName);
if (reportId > 0)
{
ReportChartDal reportChart = new ReportChartDal();
currentChart = reportChart.GetChartByNameAndCartegoryReportIds(categoryId, reportId, chartName);
}
}
return(currentChart);
}
