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); }