public virtual IEnumerable <Module> GetModules(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var builder = new SqlBuilder();
var search = builder.AddTemplate(SqlTemplate.ModuleSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
builder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
builder.OrderBy(s.ToExpression());
}
}
else
{
builder.OrderBy("Module.DisplayOrder");
}
IEnumerable <Module> modules = null;
using (var connection = _dapperContext.GetOpenConnection())
{
modules = connection.Query <Module>(search.RawSql, search.Parameters);
}
return(modules);
}
public virtual PagedResult <Issue> GetIssues(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.IssueSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Issue.IssueDate DESC");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.IssueSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var issues = connection.Query <Issue, Site, Store, Issue>(search.RawSql,
(issue, site, store) => { issue.Site = site; issue.Store = store; return(issue); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Issue>(issues, totalCount));
}
}
public virtual PagedResult <Visual> GetVisuals(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.VisualSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.VisualSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var visuals = connection.Query <Visual>(search.RawSql, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Visual>(visuals, totalCount));
}
}
public virtual PagedResult <ServiceRequest> GetServiceRequests(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.ServiceRequestSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("ServiceRequest.Number");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.ServiceRequestSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var serviceRequests = connection.Query <ServiceRequest, Asset, Location, Site, Assignment, ServiceRequest>(search.RawSql,
(serviceRequest, asset, location, site, assignment) => { serviceRequest.Asset = asset; serviceRequest.Location = location; serviceRequest.Site = site; serviceRequest.Assignment = assignment; return(serviceRequest); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <ServiceRequest>(serviceRequests, totalCount));
}
}
public virtual PagedResult <PreventiveMaintenance> GetPreventiveMaintenances(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.PreventiveMaintenanceSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("PreventiveMaintenance.Number");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.PreventiveMaintenanceSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var preventiveMaintenances = connection.Query <PreventiveMaintenance, ValueItem, Asset, Location, Site, PreventiveMaintenance>(search.RawSql,
(preventiveMaintenance, valueItem, asset, location, site) => { preventiveMaintenance.Site = site; preventiveMaintenance.PMStatus = valueItem; preventiveMaintenance.Asset = asset; preventiveMaintenance.Location = location; return(preventiveMaintenance); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <PreventiveMaintenance>(preventiveMaintenances, totalCount));
}
}
public virtual PagedResult <PurchaseOrder> GetPurchaseOrders(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.PurchaseOrderSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("PurchaseOrder.Number");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.PurchaseOrderSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var purchaseOrders = connection.Query <PurchaseOrder, Company, ValueItem, Site, Assignment, PurchaseOrder>(search.RawSql,
(purchaseOrder, company, valueItem, site, assignment) => { purchaseOrder.Vendor = company; purchaseOrder.PaymentTerm = valueItem; purchaseOrder.Site = site; purchaseOrder.Assignment = assignment; return(purchaseOrder); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <PurchaseOrder>(purchaseOrders, totalCount));
}
}
public virtual PagedResult <Item> GetItems(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.ItemSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Item.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.ItemSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var items = connection.Query <Item, ItemGroup, UnitOfMeasure, Item>(search.RawSql,
(item, itemgroup, unitofmeasure) => { item.ItemGroup = itemgroup; item.UnitOfMeasure = unitofmeasure; return(item); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Item>(items, totalCount));
}
}
public virtual PagedResult <TenantLease> GetTenantLeases(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.TenantLeaseSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("TenantLease.Number");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.TenantLeaseSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var tenantLeases = connection.Query <TenantLease, Site, Tenant, Property, Assignment, TenantLease>(search.RawSql,
(tenantLease, site, tenant, property, assignment) => { tenantLease.Site = site; tenantLease.Tenant = tenant; tenantLease.Property = property; tenantLease.Assignment = assignment; return(tenantLease); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <TenantLease>(tenantLeases, totalCount));
}
}
public virtual PagedResult <Asset> GetAssets(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.AssetSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Asset.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.AssetSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var assets = connection.Query <Asset, ValueItem, ValueItem, ValueItem, Location, Site, Asset>(search.RawSql,
(asset, assetType, assetStatus, assetCategory, location, site) => { asset.AssetType = assetType; asset.AssetStatus = assetStatus; asset.AssetCategory = assetCategory; asset.Location = location; asset.Site = site; return(asset); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Asset>(assets, totalCount));
}
}
public virtual PagedResult <Location> GetLocations(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.LocationSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Location.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.LocationSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var locations = connection.Query <Location, ValueItem, ValueItem, Site, Location>(search.RawSql,
(location, locationType, locationStatus, site) => { location.LocationType = locationType; location.LocationStatus = locationStatus; location.Site = site; return(location); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Location>(locations, totalCount));
}
}
public virtual PagedResult <Transfer> GetTransfers(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.TransferSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Transfer.TransferDate DESC");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.TransferSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var transfers = connection.Query <Transfer, Site, Site, Store, Store, Transfer>(search.RawSql,
(transfer, fromsite, tosite, fromstore, tostore) => { transfer.FromSite = fromsite; transfer.ToSite = tosite; transfer.FromStore = fromstore; transfer.ToStore = tostore; return(transfer); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Transfer>(transfers, totalCount));
}
}
public virtual PagedResult <Technician> GetTechnicians(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.TechnicianSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Technician.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.TechnicianSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var technicians = connection.Query <Technician, User, Calendar, Shift, Craft, Technician> (search.RawSql,
(technician, user, calendar, shift, craft) => { technician.User = user; technician.Calendar = calendar; technician.Shift = shift; technician.Craft = craft; return(technician); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Technician>(technicians, totalCount));
}
}
public virtual PagedResult <Property> GetProperties(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.PropertySearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Property.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.PropertySearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var properties = connection.Query <Property, Site, Location, Property>(search.RawSql, (property, site, location) => { property.Site = site; property.Location = location; return(property); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Property>(properties, totalCount));
}
}
public virtual PagedResult <UnitConversion> GetUnitConversions(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var builder = new SqlBuilder();
var search = builder.AddTemplate(SqlTemplate.UnitConversionSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
builder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
builder.OrderBy(s.ToExpression());
}
}
else
{
builder.OrderBy("FromUnitOfMeasure.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.UnitConversionSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var unitConversions = connection.Query <UnitConversion, UnitOfMeasure, UnitOfMeasure, UnitConversion>(search.RawSql, (unitConversion, fromUnitOfMeasure, toUnitOfMeasure) => { unitConversion.FromUnitOfMeasure = fromUnitOfMeasure; unitConversion.ToUnitOfMeasure = toUnitOfMeasure; return(unitConversion); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <UnitConversion>(unitConversions, totalCount));
}
}
