public CompanyExpenseDatatable GetCompanyExpenseForTable(CompanyExpenseSearchParams searchInfo)
{
var companyExpense = from a in _companyExpenseRepository.GetAllQueryable()
join c in _expenseRepository.GetAllQueryable() on new { a.ExpenseC }
equals new { c.ExpenseC } into t1
from c in t1.DefaultIfEmpty()
join e in _supplierRepository.GetAllQueryable() on new { a.SupplierMainC, a.SupplierSubC }
equals new { e.SupplierMainC, e.SupplierSubC } into t2
from e in t2.DefaultIfEmpty()
join t in _employeeRepository.GetAllQueryable() on a.EntryClerkC equals t.EmployeeC into t3
from t in t3.DefaultIfEmpty()
join q in _employeeRepository.GetAllQueryable() on a.EmployeeC equals q.EmployeeC into t4
from q in t4.DefaultIfEmpty()
where (((searchInfo.ParamSearch.InvoiceDEnd == null || a.InvoiceD <= searchInfo.ParamSearch.InvoiceDEnd) &&
(searchInfo.ParamSearch.InvoiceDStart == null || a.InvoiceD >= searchInfo.ParamSearch.InvoiceDStart)) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.EmployeeC) || a.EmployeeC == searchInfo.ParamSearch.EmployeeC) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.ExpenseC) || a.ExpenseC == searchInfo.ParamSearch.ExpenseC) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.SupplierMainC) || (a.SupplierMainC == searchInfo.ParamSearch.SupplierMainC && a.SupplierSubC == searchInfo.ParamSearch.SupplierSubC)))
select new CompanyExpenseViewModel()
{
Id = a.Id,
InvoiceD = a.InvoiceD,
ExpenseC = a.ExpenseC,
ExpenseN = c.ExpenseN,
EmployeeC = a.EmployeeC,
EmployeeN = q != null ? (q.EmployeeLastN + " " + q.EmployeeFirstN) : "",
EntryClerkC = a.EntryClerkC,
EntryClerkN = t != null ? (t.EmployeeLastN + " " + t.EmployeeFirstN) : "",
PaymentMethodI = a.PaymentMethodI,
SupplierMainC = a.SupplierMainC,
SupplierSubC = a.SupplierSubC,
SupplierN = e.SupplierN,
Quantity = a.Quantity,
UnitPrice = a.UnitPrice,
Total = a.Total,
Tax = a.Tax,
Description = a.Description,
};
// sorting (done with the System.Linq.Dynamic library available on NuGet)
var companyExpenseOrdered = companyExpense.OrderBy(searchInfo.SortBy + (searchInfo.Reverse ? " descending" : ""));
// paging
var companyExpensePaging = companyExpenseOrdered.Skip((searchInfo.Page - 1) * searchInfo.ItemsPerPage).Take(searchInfo.ItemsPerPage).ToList();
var datatable = new CompanyExpenseDatatable()
{
Data = companyExpensePaging,
Total = companyExpenseOrdered.Count()
};
return(datatable);
}
public TruckExpenseDatatables GetTruckExpensesForTable(TruckExpenseSearchParams searchInfo)
{
var truckExpense = (from a in _truckExpenseRepository.GetAllQueryable()
join b in _truckRepository.GetAllQueryable() on a.Code
equals b.TruckC into t1
join f in _trailerRepository.GetAllQueryable() on a.Code
equals f.TrailerC into t6
from f in t6.DefaultIfEmpty()
from b in t1.DefaultIfEmpty()
join c in _expenseRepository.GetAllQueryable() on new { a.ExpenseC }
equals new { c.ExpenseC } into t2
from c in t2.DefaultIfEmpty()
join d in _driverRepository.GetAllQueryable() on new { a.DriverC }
equals new { d.DriverC } into t3
from d in t3.DefaultIfEmpty()
join e in _supplierRepository.GetAllQueryable() on new { a.SupplierMainC, a.SupplierSubC }
equals new { e.SupplierMainC, e.SupplierSubC } into t4
from e in t4.DefaultIfEmpty()
join t in _employeeRepository.GetAllQueryable() on a.EntryClerkC equals t.EmployeeC into t5
from t in t5.DefaultIfEmpty()
where (((searchInfo.ParamSearch.InvoiceDEnd == null || a.InvoiceD <= searchInfo.ParamSearch.InvoiceDEnd) &&
(searchInfo.ParamSearch.InvoiceDStart == null || a.InvoiceD >= searchInfo.ParamSearch.InvoiceDStart)) &
((searchInfo.ParamSearch.TransportDEnd == null || a.TransportD <= searchInfo.ParamSearch.TransportDEnd) &&
(searchInfo.ParamSearch.TransportDStart == null || a.TransportD >= searchInfo.ParamSearch.TransportDStart)) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.ExpenseC) || a.ExpenseC == searchInfo.ParamSearch.ExpenseC) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.SupplierMainC) || (a.SupplierMainC == searchInfo.ParamSearch.SupplierMainC && a.SupplierSubC == searchInfo.ParamSearch.SupplierSubC)) &
( //searchInfo.ParamSearch.ObjectI != "C" &&
( //((String.IsNullOrEmpty(searchInfo.ParamSearch.Code) || a.Code == searchInfo.ParamSearch.Code)) ||
(searchInfo.ParamSearch.ObjectI == "1" && searchInfo.ParamSearch.ObjectI == a.ObjectI && (String.IsNullOrEmpty(searchInfo.ParamSearch.Code) || searchInfo.ParamSearch.Code == f.TrailerC)) ||
(searchInfo.ParamSearch.ObjectI == "0" && searchInfo.ParamSearch.ObjectI == a.ObjectI && (String.IsNullOrEmpty(searchInfo.ParamSearch.Code) || searchInfo.ParamSearch.Code == b.TruckC)))))
select new TruckExpenseViewModel()
{
Id = a.Id,
InvoiceD = a.InvoiceD,
TransportD = a.TransportD,
ExpenseC = a.ExpenseC,
ExpenseN = c.ExpenseN,
EntryClerkC = a.EntryClerkC,
EntryClerkN = t != null ? (t.EmployeeLastN + " " + t.EmployeeFirstN) : "",
DriverC = a.DriverC,
DriverN = d != null ? d.LastN + " " + d.FirstN : "",
PaymentMethodI = a.PaymentMethodI,
SupplierMainC = a.SupplierMainC,
SupplierSubC = a.SupplierSubC,
SupplierN = e.SupplierN,
Quantity = a.Quantity,
UnitPrice = a.UnitPrice,
Total = a.Total + a.Tax,
Tax = a.Tax,
Description = a.Description,
ObjectNo = a.ObjectI == "0" ? b.RegisteredNo : f.TrailerNo,
ObjectI = a.ObjectI,
ExpenseI = "T",
}).ToList();
var companyExpense = (from a in _companyExpenseRepository.GetAllQueryable()
join c in _expenseRepository.GetAllQueryable() on new { a.ExpenseC }
equals new { c.ExpenseC } into t1
from c in t1.DefaultIfEmpty()
join e in _supplierRepository.GetAllQueryable() on new { a.SupplierMainC, a.SupplierSubC }
equals new { e.SupplierMainC, e.SupplierSubC } into t2
from e in t2.DefaultIfEmpty()
join t in _employeeRepository.GetAllQueryable() on a.EntryClerkC equals t.EmployeeC into t3
from t in t3.DefaultIfEmpty()
join q in _employeeRepository.GetAllQueryable() on a.EmployeeC equals q.EmployeeC into t4
from q in t4.DefaultIfEmpty()
where (((searchInfo.ParamSearch.InvoiceDEnd == null || a.InvoiceD <= searchInfo.ParamSearch.InvoiceDEnd) &&
(searchInfo.ParamSearch.InvoiceDStart == null || a.InvoiceD >= searchInfo.ParamSearch.InvoiceDStart)) &
//(String.IsNullOrEmpty(searchInfo.ParamSearch.EmployeeC) || a.EmployeeC == searchInfo.ParamSearch.EmployeeC) &
(String.IsNullOrEmpty(searchInfo.ParamSearch.ExpenseC) || a.ExpenseC == searchInfo.ParamSearch.ExpenseC) &
(searchInfo.ParamSearch.ObjectI == "C") &
(String.IsNullOrEmpty(searchInfo.ParamSearch.SupplierMainC) || (a.SupplierMainC == searchInfo.ParamSearch.SupplierMainC && a.SupplierSubC == searchInfo.ParamSearch.SupplierSubC)))
select new TruckExpenseViewModel()
{
Id = a.Id,
InvoiceD = a.InvoiceD,
ExpenseC = a.ExpenseC,
ExpenseN = c.ExpenseN,
//DriverC = a.EmployeeC,
//DriverN = q != null ? (q.EmployeeLastN + " " + q.EmployeeFirstN) : "",
EntryClerkC = a.EntryClerkC,
EntryClerkN = t != null ? (t.EmployeeLastN + " " + t.EmployeeFirstN) : "",
PaymentMethodI = a.PaymentMethodI,
SupplierMainC = a.SupplierMainC,
SupplierSubC = a.SupplierSubC,
SupplierN = e.SupplierN,
Quantity = a.Quantity,
UnitPrice = a.UnitPrice,
Total = a.Total + a.Tax,
Tax = a.Tax,
Description = a.Description,
ExpenseI = "C",
}).ToList();
var combined = truckExpense.Concat(companyExpense);
// sorting (done with the System.Linq.Dynamic library available on NuGet)
var truckExpenseOrdered = combined.OrderBy(searchInfo.SortBy + (searchInfo.Reverse ? " descending" : ""));
// paging
var truckExpensePaging = truckExpenseOrdered.Skip((searchInfo.Page - 1) * searchInfo.ItemsPerPage).Take(searchInfo.ItemsPerPage).ToList();
var datatable = new TruckExpenseDatatables()
{
Data = truckExpensePaging,
Total = truckExpenseOrdered.Count()
};
return(datatable);
}
