public async Task <(IEnumerable <CostCenterModel> CostCenterModelList, int totalCount)> PrepareCostCenterListModel(CostCenterListModel model, int pageIndex, int pageSize)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", model.ClientId),
new SqlParameter("@intSort", model.SearchSortBy)
};
var costcenters = await _dbContext.Set <Ship2Billable>().FromSqlRaw("exec Ship2Billable @intClientID,@intSort", pr).ToListAsync();
int totalCount = costcenters.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
costcenters = costcenters.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
///Add from store procedure response to model
var costcenterListModel = new List <CostCenterModel>();
foreach (var p in costcenters)
{
costcenterListModel.Add(new CostCenterModel
{
ShipID = p.ShipID,
SName = p.SName,
CompanyName = p.CompanyName,
Address = p.Address
});
}
return(costcenterListModel, totalCount);
}
public async Task <(IEnumerable <VendorPricePerf> vendorPricePerfListModel, int totalCount)> PreparePriceCompilanceReportListModel(PriceCompilancelistModel model, int pageIndex, int pageSize)
{
try
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", (int)_workcontext.CurrentCustomer.ClientId),
new SqlParameter("@DateStart", (string.IsNullOrEmpty(model.DateStart)) ? DBNull.Value.ToString() : model.DateStart),
new SqlParameter("@DateEnd", (string.IsNullOrEmpty(model.DateEnd)) ? DBNull.Value.ToString() : model.DateEnd),
new SqlParameter("@strVendor", model.strVendor),
new SqlParameter("@strShip2", model.strShip2)
};
var query = await _dbContext.Set <VendorPricePerf>().FromSqlRaw("exec [dbo].[VendorPricePerf] @intClientID,@strVendor,@strShip2,@DateStart,@DateEnd", pr).ToListAsync();
int totalCount = query.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
query = query.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
return(query.ToList(), totalCount);
}
catch (Exception ex)
{
}
return(null, 0);
}
public async Task <(IEnumerable <ProductSearchModel> productsearchlistmodel, int totalCount)> PrepareProductListModel(ProductSearchListModel model, int pageIndex = 0, int pageSize = 2147483647)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientId", model.ClientId),
new SqlParameter("@txtSS", model.SearchProductByName)
};
var products = await _dbContext.Set <ItemSearch>().FromSqlRaw("exec ItemSearch @intClientId,@txtSS", pr).ToListAsync();
int totalCount = products.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
products = products.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
///Add from store procedure response to model
var productsearchlistmodel = new List <ProductSearchModel>();
foreach (var p in products)
{
productsearchlistmodel.Add(new ProductSearchModel
{
VItemId = p.VItemID,
Item = p.Vitem,
Mfg = p.MfgNo,
Description = p.VDescription,
Unit = p.UOM,
Pkg = p.Pkg,
Price = p.SellingPrice,
Vendor = p.VName
});
}
return(productsearchlistmodel, totalCount);
}
public async Task <T> DeleteAsync(T entity)
{
_dbContext.Set <T>().Remove(entity);
await _dbContext.SaveChangesAsync();
return(entity);
}
public async Task <IList <OItem_GL> > SelectGl(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@Session_ClientID", clientId)
};
return(await _dbContext.Set <OItem_GL>().FromSqlRaw("exec OItem_GL @Session_ClientID", pr).ToListAsync());
}
public async Task <IList <Ship2_Usage> > GetCostCenterList(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId)
};
var query = await _dbContext.Set <Ship2_Usage>().FromSqlRaw("exec Ship2_Usage @intClientID", pr).ToListAsync();
return(query);
}
public async Task <IList <NotReceived> > CheckClientAlert
(int LogonId, int ClientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intLogonID", LogonId),
new SqlParameter("@intClientID", ClientId)
};
return(await _contextDb.Set <NotReceived>().FromSqlRaw("exec NotReceived @intLogonID,@intClientID", pr).ToListAsync());
}
public async Task <IList <repInvValue_Location> > GetReportByLocation(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId)
};
var locations = await _dbContext.Set <repInvValue_Location>().FromSqlRaw("exec [dbo].[repInvValue-Location] @intClientID", pr).ToListAsync();
return(locations);
}
public async Task<(IEnumerable<ServiceContractModel> ServiceContractModelList, int totalCount)> PrepareServiceContractListModel(ServiceContractListModel model, int pageIndex, int pageSize)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID",model.ClientId),
new SqlParameter("@txtSort", model.SearchBySort)
};
var contracts = await _dbContext.Set<SCLIST>().FromSqlRaw("exec SCLIST @intClientID,@txtSort", pr).ToListAsync();
int totalCount = contracts.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
contracts = contracts.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
///Add from store procedure response to model
var contractListModel = new List<ServiceContractModel>();
foreach (var p in contracts)
{
contractListModel.Add(new ServiceContractModel
{
Scid = p.SCId,
ClientId = p.ClientID,
Scvendor = p.SCVendor,
ScendDate = p.SCEndDate,
ScnotifyDate = p.SCNotifyDate,
ShortDescription = p.ShortDescription,
Category = p.Category
});
}
return (contractListModel, totalCount);
}
public async Task <IList <DivisionSelect3> > SelectDivisionByClientId(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId)
};
return(await _contextDb.Set <DivisionSelect3>().FromSqlRaw("exec DivisionSelect3 @intClientID", pr).ToListAsync());
}
public async Task <IList <Ship2_ShipStorageAll> > GetAllShipTo(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId)
};
return(await _dbContext.Set <Ship2_ShipStorageAll>().FromSqlRaw("exec Ship2_ShipStorageAll @intClientID", pr).ToListAsync());
}
public async Task <IList <EmployeeCountShip> > GetEmployeeCountShip(int clientId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId)
};
var result = await _dbContext.Set <EmployeeCountShip>().FromSqlRaw("exec EmployeeCountShip @intClientID", pr).ToListAsync();
return(result);
}
public async Task <IList <Ship2_Ship> > SelectCostCenter(int clientId,
int userType, int logonId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId),
new SqlParameter("@intUserType", userType),
new SqlParameter("@intLogonID", logonId)
};
return(await _contextDb.Set <Ship2_Ship>().FromSqlRaw("exec Ship2_Ship @intClientID,@intUserType,@intLogonID", pr).ToListAsync());
}
public async Task <MaxOrderAmountModel> GetMaxPOAmountList(int clientId, string ShipId)
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", clientId),
new SqlParameter("@strShipID", ShipId)
};
var query = (_dbContext.Set <BudgetCostCenter1>().FromSqlRaw("exec BudgetCostCenter1 @intClientID,@strShipID", pr)).FirstOrDefault();
var model = _mapper.Map <MaxOrderAmountModel>(query);
return(model);
}
public async Task <(IEnumerable <AssetModel> AssetModelList, int totalCount)> PrepareAssetListModel(AssetListModel model, int pageIndex, int pageSize)
{
try
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", (int)_workContext.CurrentCustomer.ClientId),
new SqlParameter("@DateBegin", (string.IsNullOrEmpty(model.SearchByStartDate)) ? DBNull.Value.ToString() : model.SearchByStartDate),
new SqlParameter("@DateEnd", (string.IsNullOrEmpty(model.SearchByEndDate)) ? DBNull.Value.ToString() : model.SearchByEndDate),
new SqlParameter("@intSearchFor", model.SearchFor),
new SqlParameter("@txtSearchFor", (string.IsNullOrEmpty(model.SearchTerm)) ? DBNull.Value.ToString() : model.SearchTerm),
new SqlParameter("@intSort", model.SortBy),
new SqlParameter("@DateType", model.SearchByDate),
new SqlParameter("@txtCategory", model.SearchByCategory)
};
var assetList = await _dbContext.Set <AssetExport>().FromSqlRaw("exec AssetExport @intClientID,@DateBegin,@DateEnd,@intSearchFor,@txtSearchFor,@intSort,@DateType,@txtCategory", pr).ToListAsync();
var assetListModel = _mapper.Map <List <AssetModel> >(assetList);
int totalCount = assetListModel.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
assetListModel = assetListModel.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
return(assetListModel, totalCount);
}
catch (Exception ex)
{
}
return(null, 0);
}
public async Task <(IEnumerable <SOPreview> ServiceRequestList, int totalCount)> PrepareServiceRequestListModel(ServiceRequestListModel model, int pageIndex, int pageSize)
{
try
{
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", (int)_workContext.CurrentCustomer.ClientId),
new SqlParameter("@intSort", model.intSort)
};
var serviceRequestListModel = await _dbContext.Set <SOPreview>().FromSqlRaw("exec SOPreview @intClientID,@intSort", pr).ToListAsync();
int totalCount = serviceRequestListModel.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
serviceRequestListModel = serviceRequestListModel.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
return(serviceRequestListModel, totalCount);
}
catch (Exception ex)
{
}
return(null, 0);
}
public async Task <(IEnumerable <ApproveOrderModel> processOrderListModel, int totalCount)> PrepareApproveOrderListModel(ApproveOrderListModel model, int pageIndex, int pageSize)
{
try
{
var usrType = (int)_workContext.CurrentCustomer.UserType;
int sortBy = 0;
//As per client VB Code
switch (model.intSort)
{
case 1: //Sort by Cost Center
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 1;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 10;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 30;
}
else if (usrType == 15)
{
sortBy = 20;
}
break;
case 2: //sort by date
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 2;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 11;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 31;
}
else if (usrType == 15)
{
sortBy = 21;
}
break;
case 3: //sort by item #
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 3;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 12;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 32;
}
else if (usrType == 15)
{
sortBy = 22;
}
break;
case 4: //sort by Vendor
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 4;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 13;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 33;
}
else if (usrType == 15)
{
sortBy = 23;
}
break;
case 5: //orders Sent to Central Purchasing Sorted by Cost Center"
sortBy = 40;
break;
case 6: //Orders Sent to Central Purchasing Sorted by Date
sortBy = 41;
break;
case 7: //all transfered orders sorted by date
sortBy = 42;
break;
case 8: //Orders Sent to Central Purchasing Sorted by Vendor
sortBy = 43;
break;
case 9: //sort by quantity
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 5;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 14;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 34;
}
else if (usrType == 15)
{
sortBy = 24;
}
break;
case 10: //sort by total expense
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 6;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 15;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 35;
}
else if (usrType == 15)
{
sortBy = 25;
}
break;
case 11: //sort by total expense
if (usrType == 1 || usrType == 3 || usrType == 4)
{
sortBy = 6;
}
else if (usrType == 2 || usrType == 6 || usrType == 8)
{
sortBy = 16;
}
else if (usrType == 7 || usrType == 10)
{
sortBy = 36;
}
else if (usrType == 15)
{
sortBy = 26;
}
break;
}
SqlParameter[] pr = new SqlParameter[]
{
new SqlParameter("@intClientID", (int)_workContext.CurrentCustomer.ClientId),
new SqlParameter("@intLogonID", (int)_workContext.CurrentCustomer.LogonId),
new SqlParameter("@intSort", sortBy)
};
var orders = await _dbContext.Set <Orders>().FromSqlRaw("exec Preview @intClientID,@intLogonID,@intSort", pr).ToListAsync();
var approveOrderlist = new List <ApproveOrderModel>();
foreach (var order in orders)
{
approveOrderlist.Add(new ApproveOrderModel
{
OrdersId = order.OrdersId,
VitemId = order.VitemId,
Oitem = order.Oitem,
Odescription = order.Odescription,
Oqty = order.Oqty,
DeptResp = order.DeptResp,
OshipId = order.OshipId,
OshipName = order.OshipName,
Osid = order.Osid,
OsellingPrice = order.OsellingPrice,
Total = ((int)order.Oqty * Convert.ToDecimal(order.OsellingPrice)),
OvendorId = order.OvendorId,
OvendorName = order.OvendorName,
Ogl = order.Ogl,
Ouom = order.Ouom
});
}
int totalCount = approveOrderlist.Count;
int pageOffSet = (Convert.ToInt32(pageIndex) - 1) * 10;
approveOrderlist = approveOrderlist.Skip(pageOffSet).Take(Convert.ToInt32(pageSize)).ToList();
return(approveOrderlist, totalCount);
}
catch (Exception ex)
{
}
return(null, 0);
}
