public CustomDataResult <VendorPaidDue> PaidDues(CustomDataRequest request)
{
return(Context.Vendor.Where(v => v.VendorDue > 0).OrderBy(v => v.VendorCompanyName).Select(v => new VendorPaidDue
{
VendorID = v.VendorID,
VendorCompanyName = v.VendorCompanyName,
TotalAmount = v.TotalAmount,
VendorDue = v.VendorDue,
VendorPaid = v.VendorPaid
}).ToDataResultCustom(request));
}
public CustomDataResult <SupplierPaidDue> PaidDues(CustomDataRequest request)
{
return(Context.Supplier.Where(v => v.SupplierDue > 0).OrderBy(v => v.SupplierCompanyName).Select(v => new SupplierPaidDue
{
SupplierID = v.SupplierID,
SupplierCompanyName = v.SupplierCompanyName,
TotalAmount = v.TotalAmount,
SupplierDue = v.SupplierDue,
SupplierPaid = v.SupplierPaid
}).ToDataResultCustom(request));
}
public CustomDataResult <PaymentReceiptList> DateToDate(CustomDataRequest request, DateTime?sDateTime, DateTime?eDateTime)
{
var sD = sDateTime ?? new DateTime(DateTime.Now.Year, 1, 1);
var eD = eDateTime ?? new DateTime(DateTime.Now.Year, 12, 31);
var sell = Context.SellingPaymentReceipt.Include(s => s.Vendor).Select(s => new PaymentReceiptList
{
ReceiptID = s.ReceiptID,
Date = s.Paid_Date,
Vendor = s.Vendor.VendorCompanyName,
Receipt = s.ReceiptSN,
Amount = s.PaidAmount
}).Where(e => e.Date <= eD && e.Date >= sD).OrderBy(e => e.Date);
return(sell.ToDataResultCustom(request));
}
public CustomDataResult <SellingRecord> SellDateToDate(CustomDataRequest request, DateTime?sDateTime, DateTime?eDateTime)
{
var sD = sDateTime ?? new DateTime(DateTime.Now.Year, 1, 1);
var eD = eDateTime ?? new DateTime(DateTime.Now.Year, 12, 31);
var sell = Context.Selling.Include(s => s.Vendor).Select(s => new SellingRecord
{
SellingID = s.SellingID,
VendorID = s.VendorID,
VendorCompanyName = s.Vendor.VendorCompanyName,
SellingSN = s.SellingSN,
SellingAmount = s.SellingTotalPrice - s.SellingDiscountAmount.GetValueOrDefault(),
SellingPaidAmount = s.SellingPaidAmount.GetValueOrDefault(),
SellingDueAmount = s.SellingDueAmount.GetValueOrDefault(),
SellingDiscountAmount = s.SellingDiscountAmount.GetValueOrDefault(),
SellingDate = s.SellingDate
}).Where(e => e.SellingDate <= eD && e.SellingDate >= sD).OrderBy(e => e.SellingDate);
return(sell.ToDataResultCustom(request));
}
public CustomDataResult <PurchaseRecord> SellDateToDate(CustomDataRequest request, DateTime?sDateTime, DateTime?eDateTime)
{
var sD = sDateTime ?? new DateTime(DateTime.Now.Year, 1, 1);
var eD = eDateTime ?? new DateTime(DateTime.Now.Year, 12, 31);
var sell = Context.Purchase.Include(s => s.Supplier).Select(s => new PurchaseRecord
{
PurchaseID = s.PurchaseID,
SupplierID = s.SupplierID,
SupplierCompanyName = s.Supplier.SupplierCompanyName,
PurchaseSN = s.PurchaseSN,
PurchaseAmount = s.PurchaseTotalPrice - s.PurchaseDiscountAmount.GetValueOrDefault(),
PurchasePaidAmount = s.PurchasePaidAmount.GetValueOrDefault(),
PurchaseDueAmount = s.PurchaseDueAmount.GetValueOrDefault(),
PurchaseDiscountAmount = s.PurchaseDiscountAmount.GetValueOrDefault(),
PurchaseDate = s.PurchaseDate
}).Where(e => e.PurchaseDate <= eD && e.PurchaseDate >= sD).OrderBy(e => e.PurchaseDate);
return(sell.ToDataResultCustom(request));
}
public CustomDataResult <IncomeVM> IncomeDateToDate(CustomDataRequest request, DateTime?sDateTime,
DateTime?eDateTime)
{
var sD = sDateTime ?? new DateTime(DateTime.Now.Year, 1, 1);
var eD = eDateTime ?? new DateTime(DateTime.Now.Year, 12, 31);
var income = Context.SellingPaymentRecord.Include(r => r.Selling).ThenInclude(s => s.Vendor)
.Include(r => r.Registration)
.Where(r => r.SellingPaid_Date <= eD && r.SellingPaid_Date >= sD).Select(r => new IncomeVM
{
SellingID = r.SellingID,
VendorID = r.Selling.VendorID,
VendorCompanyName = r.Selling.Vendor.VendorCompanyName,
SellingSN = r.Selling.SellingSN,
SellingPaidAmount = r.SellingPaidAmount,
Payment_Situation = r.Payment_Situation,
Description = r.Description,
SellingPaid_Date = r.SellingPaid_Date,
ReceivedBy = r.Registration.Name
});
return(income.ToDataResultCustom(request));
}
public CustomDataResult <PurchaseIncomeVM> IncomeDateToDate(CustomDataRequest request, DateTime?sDateTime,
DateTime?eDateTime)
{
var sD = sDateTime ?? new DateTime(DateTime.Now.Year, 1, 1);
var eD = eDateTime ?? new DateTime(DateTime.Now.Year, 12, 31);
var income = Context.PurchasePaymentRecord.Include(r => r.Purchase).ThenInclude(s => s.Supplier)
.Include(r => r.Registration)
.Where(r => r.PurchasePaid_Date <= eD && r.PurchasePaid_Date >= sD).Select(r => new PurchaseIncomeVM
{
PurchaseID = r.PurchaseID,
SupplierID = r.Purchase.SupplierID,
SupplierCompanyName = r.Purchase.Supplier.SupplierCompanyName,
PurchaseSN = r.Purchase.PurchaseSN,
PurchasePaidAmount = r.PurchasePaidAmount,
Payment_Situation = r.Payment_Situation,
Description = r.Description,
PurchasePaid_Date = r.PurchasePaid_Date,
ReceivedBy = r.Registration.Name
});
return(income.ToDataResultCustom(request));
}
public static CustomDataResult <T> ToDataResultCustom <T>(this IQueryable <T> query, CustomDataRequest request) where T : class
{
var result = new CustomDataResult <T>
{
draw = request.draw
};
try
{
result.recordsTotal = result.recordsFiltered = query.Count();
foreach (var item in request.filters)
{
var exp = GetExpression <T>((Operand)item.Operand, item.Field, item.Value);
if (exp != null)
{
query = query.Where(exp);
}
}
if (!string.IsNullOrEmpty(request.search?.value))
{
Expression <Func <T, bool> > exp = null;
var listExp = new List <FilterDefinition>();
foreach (var item in request.columns.Where(a => a.searchable))
{
ParameterExpression param = Expression.Parameter(typeof(T), "t");
MemberExpression member = Expression.Property(param, item.data);
var operand = member.Type == typeof(string) ? Operand.Contains : Operand.Equal;
listExp.Add(new FilterDefinition {
Operand = operand, Field = item.data, Value = request.search.value
});
}
exp = ExpressionBuilder.GetExpression <T>(listExp);
if (exp != null)
{
query = query.Where(exp);
}
}
if (!string.IsNullOrEmpty(request.search?.value) || request.filters.Any())
{
result.recordsFiltered = query.Count();
}
//------GrandTotal of request column---------------------------------------
if (!string.IsNullOrEmpty(request.GrandTotalProperty))
{
result.GrandTotal = query.SumCreate(request.GrandTotalProperty);
}
if (request.draw > 0)
{
if (!request.order.Any())
{
query = query.OrderBy(request.columns[0].data);
}
else
{
foreach (var item in request.order)
{
if (item.dir == "asc")
{
query = query.OrderBy(request.columns[item.column].data);
}
else
{
query = query.OrderByDescending(request.columns[item.column].data);
}
}
}
query = query.Skip(request.start).Take(request.length);
}
result.data = query.ToList();
return(result);
}
catch
{
result.data = new List <T>();
return(result);
}
}