public ActionResult ReportProfitLoss(DateTime startDate, DateTime endDate)
{
var company = _companyService.GetQueryable().FirstOrDefault();
var cashSalesReturnPayables = _payableService.GetQueryable().Where(x => x.IsCompleted &&
x.CompletionDate.Value >= startDate && x.CompletionDate.Value <= endDate && x.PayableSource == Core.Constants.Constant.PayableSource.CashSalesReturn);
decimal totalSalesReturnPayable = 0;
foreach (var payable in cashSalesReturnPayables)
{
totalSalesReturnPayable += (payable.Amount - payable.AllowanceAmount);
}
var paymentRequestPayables = _payableService.GetQueryable().Where(x => x.IsCompleted &&
x.CompletionDate.Value >= startDate && x.CompletionDate.Value <= endDate && x.PayableSource == Core.Constants.Constant.PayableSource.PaymentRequest);
decimal totalPaymentRequestPayable = 0;
foreach (var payable in cashSalesReturnPayables)
{
totalPaymentRequestPayable += (payable.Amount - payable.AllowanceAmount);
}
var cashSalesInvoices = _cashSalesInvoiceService.GetQueryable().Where(x => x.IsPaid &&
x.ConfirmationDate.Value >= startDate && x.ConfirmationDate.Value <= endDate).ToList();
decimal totalCashSales = 0;
decimal totalCoGS = 0;
foreach (var cashSales in cashSalesInvoices)
{
totalCashSales += (cashSales.Total - cashSales.Allowance);
totalCoGS += cashSales.CoGS;
}
List <ModelProfitLoss> query = new List <ModelProfitLoss>();
var profitloss = new ModelProfitLoss
{
StartDate = startDate.Date,
EndDate = endDate.Date,
TotalSales = totalCashSales,
TotalCoGS = totalCoGS,
TotalSalesReturn = totalSalesReturnPayable,
TotalExpense = totalPaymentRequestPayable,
CompanyName = company.Name,
CompanyAddress = company.Address,
CompanyContactNo = company.ContactNo,
};
query.Add(profitloss);
var rd = new ReportDocument();
//Loading Report
rd.Load(Server.MapPath("~/") + "Reports/General/ProfitLoss.rpt");
// Setting report data source
rd.SetDataSource(query);
var stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
return(File(stream, "application/pdf"));
}
public dynamic GetList(string _search, long nd, int rows, int?page, string sidx, string sord, string filters = "")
{
// Construct where statement
string strWhere = GeneralFunction.ConstructWhere(filters);
string filter = null;
GeneralFunction.ConstructWhereInLinq(strWhere, out filter);
if (filter == "")
{
filter = "true";
}
// Get Data
var q = _cashSalesInvoiceService.GetQueryable().Include("CashBank").Include("Warehouse");
var query = (from model in q
select new
{
model.Id,
model.Code,
model.Description,
model.Discount,
model.Tax,
model.Allowance,
model.AmountPaid,
model.Total,
model.CoGS,
profitloss = (model.IsConfirmed ? (Nullable <decimal>)((model.Total * (100 - model.Tax) / 100) - model.CoGS) : null),
model.IsConfirmed,
model.ConfirmationDate,
model.CashBankId,
cashbank = model.CashBank.Name,
model.IsBank,
model.IsPaid,
model.IsFullPayment,
model.WarehouseId,
warehouse = model.Warehouse.Name,
model.SalesDate,
model.DueDate,
model.CreatedAt,
model.UpdatedAt,
}).Where(filter).OrderBy(sidx + " " + sord); //.ToList();
var list = query.AsEnumerable();
var pageIndex = Convert.ToInt32(page) - 1;
var pageSize = rows;
var totalRecords = query.Count();
var totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
// default last page
if (totalPages > 0)
{
if (!page.HasValue)
{
pageIndex = totalPages - 1;
page = totalPages;
}
}
list = list.Skip(pageIndex * pageSize).Take(pageSize);
return(Json(new
{
total = totalPages,
page = page,
records = totalRecords,
rows = (
from model in list
select new
{
id = model.Id,
cell = new object[] {
model.Id,
model.Code,
model.Description,
model.Discount,
model.Tax,
model.Allowance,
model.AmountPaid,
model.Total,
model.CoGS,
model.profitloss,
model.IsConfirmed,
model.ConfirmationDate,
model.CashBankId,
model.cashbank,
model.IsBank,
model.IsPaid,
model.IsFullPayment,
model.WarehouseId,
model.warehouse,
model.SalesDate,
model.DueDate,
model.CreatedAt,
model.UpdatedAt,
}
}).ToArray()
}, JsonRequestBehavior.AllowGet));
}