/// <summary>
/// Load asynchronous values.
/// </summary>
/// <param name="code">Code to be used</param>
/// <returns>An office data transfer object</returns>
public async Task <OfficeDtos> LoadValueAsync(string code)
{
OfficeDtos officeDtos = new OfficeDtos();
using (IDbConnection connection = _executor.OpenNewDbConnection())
{
var value = await connection.GetAsync <OFICINAS>(code);
officeDtos = _mapper.Map <OFICINAS, OfficeDtos>(value);
if (value != null)
{
IQueryStore queryStore = _queryStoreFactory.GetQueryStore();
queryStore.AddParam(QueryType.QueryCurrency);
queryStore.AddParam(QueryType.HolidaysByOffice, value.CODIGO);
var queryHolidays = queryStore.BuildQuery();
var reader = await connection.QueryMultipleAsync(queryHolidays);
var offices = reader.Read <CURRENCIES>();
if (!reader.IsConsumed)
{
var holidaysByOffice = reader.Read <FESTIVOS_OFICINA>();
officeDtos.HolidayDates = _mapper.Map <IEnumerable <FESTIVOS_OFICINA>, IEnumerable <HolidayDto> >(holidaysByOffice);
}
}
}
return(officeDtos);
}
public async Task <IEnumerable <ContractSummaryDto> > GetPagedSummaryDoAsync(int offset, int pageSize)
{
IQueryStore store = QueryStoreFactory.GetQueryStore();
var list = new List <string>();
list.Add(pageSize.ToString());
list.Add(offset.ToString());
var query = store.BuildQuery(QueryType.QueryContractSummaryPaged, list);
var result = new List <ContractSummaryDto>();
using (var conn = SqlExecutor.OpenNewDbConnection())
{
if (conn == null)
{
return(result);
}
var resultValues = await conn?.QueryAsync <ContractSummaryDto>(query);
if (resultValues != null)
{
return(resultValues);
}
}
// safe default in case of error.
return(new List <ContractSummaryDto>());
}
/// <summary>
/// Retrieve the list of all commission agents and convert them in a data transfer object list.
/// </summary>
/// <returns>The list of commission agents</returns>
public async Task <IEnumerable <CommissionAgentSummaryDto> > GetCommissionAgentSummaryDo()
{
IEnumerable <CommissionAgentSummaryDto> summary = new ObservableCollection <CommissionAgentSummaryDto>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryCommissionAgentSummary);
var query = store.BuildQuery();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
try
{
using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
summary = await connection.QueryAsync <CommissionAgentSummaryDto>(query).ConfigureAwait(false);
}
} catch (System.Exception ex)
{
throw new DataLayerException("CommissionAgentLoadException", ex);
}
finally
{
connection.Close();
}
}
return(summary);
}
public void Should_Build_QueryFromQueryStoreWithParameters()
{
_store.AddParam(QueryType.QueryCity, "0001");
_store.AddParam(QueryType.QueryLanguage, "0001");
var value = _store.BuildQuery();
Assert.AreEqual(value, CityLanguageQuery);
}
/// <summary>
/// Composability BuildQuery
/// </summary>
/// <returns>return a new query.</returns>
public override string BuildQuery()
{
var value = _builder.ToString();
var currentString = string.Empty;
var store = _store.BuildQuery();
return(store);
}
/// <summary>
/// This saves the holiday in the office.
/// </summary>
/// <param name="currentOffice">Current office to be saved.</param>
/// <param name="holidayDto">List of vacation for the current office.</param>
/// <returns>return a task for saving the holidays</returns>
private async Task <bool> SaveHolidayOfficeAsync(IDbConnection connection, OFICINAS currentOffice, IEnumerable <HolidayDto> holidayDto)
{
Contract.Requires(connection != null, "Connection is not null");
Contract.Requires(currentOffice != null, "Current office is not null");
Contract.Requires(holidayDto != null, "HolidayDto is not null");
if (currentOffice.CODIGO == null)
{
throw new ArgumentNullException("Office code is null");
}
if (holidayDto.Count() == 0)
{
return(true);
}
IEnumerable <FESTIVOS_OFICINA> holidayOffice = _mapper.Map <IEnumerable <HolidayDto>, IEnumerable <FESTIVOS_OFICINA> >(holidayDto);
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.HolidaysByOffice, currentOffice.CODIGO);
var query = store.BuildQuery();
bool saved = false;
// First i want fetch the current festivo oficina.
// we shall insert or merge.
try
{
var currentHolidays = await connection.QueryAsync <FESTIVOS_OFICINA>(query);
var festivosOficinas = currentHolidays as FESTIVOS_OFICINA[] ?? currentHolidays.ToArray();
if (!festivosOficinas.Any())
{
saved = await connection.InsertAsync(holidayOffice).ConfigureAwait(false) > 0;
}
else
{
// divide what to be inserted from what we should update.
var holidayValues = DistintSelect(currentHolidays, holidayOffice);
var value = holidayValues.Item2.ToList();
if (holidayValues.Item2.Any())
{
saved = await connection.InsertAsync(holidayValues.Item2).ConfigureAwait(false) > 0;
}
if (holidayValues.Item1.Any())
{
saved = saved && await connection.UpdateAsync(holidayValues.Item1).ConfigureAwait(false);
}
}
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new DataLayerException(e.Message, e);
}
return(saved);
}
/// <summary>
/// Load an entity from clients and maps the entity to a data transfer object.
/// </summary>
/// <param name="conn">Connection to be used</param>
/// <param name="code">Code of the entity</param>
/// <returns>A data transfer object to be loaded.</returns>
private async Task <ClientDto> LoadEntity(IDbConnection conn, string code)
{
IQueryStore clientPocoQueryStore = _queryStoreFactory.GetQueryStore();
clientPocoQueryStore.AddParam(QueryType.QueryClient1, code);
clientPocoQueryStore.AddParam(QueryType.QueryClient2, code);
var query = clientPocoQueryStore.BuildQuery();
var pocoReader = await conn.QueryMultipleAsync(query).ConfigureAwait(false);
var clients1 = pocoReader.Read <CLIENTES1>().FirstOrDefault();
var clients2 = pocoReader.Read <CLIENTES2>().FirstOrDefault();
var outClient = new ClientDto
{
Helper = new HelperBase(),
IsValid = (clients1 != null) && (clients2 != null)
};
var outType = outClient.GetType();
if (clients1 != null)
{
foreach (var propertyInfo in clients1.GetType().GetProperties())
{
var name = propertyInfo.Name;
var prop = outType.GetProperty(name);
if (prop != null)
{
// ok we have set the property
var v = propertyInfo.GetValue(clients1);
if (v != null)
{
prop.SetValue(outClient, v);
}
}
}
}
if (clients2 != null)
{
foreach (var propertyInfo in clients2.GetType().GetProperties())
{
var name = propertyInfo.Name;
var prop = outType.GetProperty(name);
if (prop != null)
{
// ok we have set the property
var v = propertyInfo.GetValue(clients2);
if (v != null)
{
prop.SetValue(outClient, v);
}
}
}
}
return(outClient);
}
/// <summary>
/// Retrieve the invoice summary in asynchronous way.
/// </summary>
/// <returns>A collection of invoices.</returns>
public async Task <IEnumerable <InvoiceSummaryValueDto> > GetInvoiceSummaryAsync()
{
using (IDbConnection db = _sqlExecutor.OpenNewDbConnection())
{
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryInvoiceSummaryExtended);
var query = store.BuildQuery();
var invoice = await db.QueryAsync <InvoiceSummaryValueDto>(query);
return(invoice);
}
}
/// <summary>
/// Load a single client value
/// </summary>
/// <param name="code">Code to load</param>
/// <returns></returns>
public async Task <bool> LoadValue(string code)
{
Contract.Assert(!string.IsNullOrEmpty(code), "Client code shall be not empty");
IDbConnection conn = null;
bool returnValue = false;
if (_sqlExecutor.Open())
{
conn = _sqlExecutor.Connection;
}
if (conn != null)
{
// here we shall load the entity and the helpers.
_currentPoco = await LoadEntity(conn, code);
// we load the helpers.
if (_currentPoco != null)
{
SqlMapper.GridReader reader = null;
IQueryStore store = CreateQueryStore(_currentPoco);
string multipleQuery = store.BuildQuery();
if (!string.IsNullOrEmpty(multipleQuery))
{
reader = await conn.QueryMultipleAsync(multipleQuery);
SetDataTransferObject(reader, _currentPoco);
string delega = string.Format(_queryDelegations, DefaultDelegation, _currentPoco.NUMERO_CLI);
var delegations = await conn.QueryAsync <CliDelegaPoco, PROVINCIA, CliDelegaPoco>(delega,
(branch, prov) =>
{
branch.PROV = prov;
return(branch);
}, splitOn : "SIGLAS");
if (delegations != null)
{
branchesDto =
_mapper.Map <IEnumerable <CliDelegaPoco>, IEnumerable <BranchesDto> >(delegations);
}
}
}
if (_currentPoco != null)
{
returnValue = true;
Valid = true;
}
}
return(returnValue);
}
/// <summary>
/// Get a paged version of the summary.
/// </summary>
/// <param name="pageSize">Page dimension.</param>
/// <param name="offset">Offset</param>
/// <returns></returns>
public async Task <IEnumerable <VehicleSummaryDto> > GetVehiclesAgentSummary(int pageSize, int offset)
{
IEnumerable <VehicleSummaryDto> summary = new List <VehicleSummaryDto>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryVehicleSummary);
var query = store.BuildQuery();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
summary = await connection.QueryAsync <VehicleSummaryDto>(query).ConfigureAwait(false);
}
return(summary);
}
/// <summary>
/// Load at most N async values.
/// </summary>
/// <param name="n">Number of values from the current position to be loaded</param>
/// <param name="back">offset, it can be positive or negative</param>
/// <returns></returns>
public async Task <IEnumerable <CompanyDto> > LoadValueAtMostAsync(int n, int back = 0)
{
IEnumerable <CompanyDto> companyDto = new List <CompanyDto>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
store.AddParamRange(QueryType.QueryPagedCompany, _currentPos, n);
_currentPos += n + back;
var query = store.BuildQuery();
var value = await connection.QueryAsync <SUBLICEN>(query);
}
return(companyDto);
}
public async Task <IEnumerable <VehicleSummaryViewObject> > GetSummaryAllAsync()
{
IEnumerable <VehicleSummaryViewObject> summary = new List <VehicleSummaryViewObject>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryVehicleSummary);
var query = store.BuildQuery();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
summary = await connection.QueryAsync <VehicleSummaryViewObject>(query).ConfigureAwait(false);
}
return(summary);
}
/// <summary>
/// This gives all the offices from the company.
/// </summary>
/// <param name="companyCode">Code of the company</param>
/// <returns>Returns the collection of offices.</returns>
public async Task <IEnumerable <OfficeSummaryDto> > GetCompanyOffices(string companyCode)
{
IEnumerable <OfficeSummaryDto> summaryCollection = new List <OfficeSummaryDto>();
IQueryStore qs = _queryStoreFactory.GetQueryStore();
qs.AddParam(QueryType.QueryOfficeSummaryByCompany, companyCode);
var query = qs.BuildQuery();
using (IDbConnection conn = _sqlExecutor.OpenNewDbConnection())
{
summaryCollection = await conn.QueryAsync <OfficeSummaryDto>(query).ConfigureAwait(false);
}
return(summaryCollection);
}
public async Task <IEnumerable <OfficeDtos> > LoadValueAtMostAsync(int n, int back = 0)
{
IEnumerable <OfficeDtos> officeDtos = new List <OfficeDtos>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
using (IDbConnection connection = _executor.OpenNewDbConnection())
{
store.AddParamRange(QueryType.QueryPagedCompany, _currentPos, n);
_currentPos += n + back;
var query = store.BuildQuery();
var value = await connection.QueryAsync <OFICINAS>(query);
officeDtos = _mapper.Map <IEnumerable <OFICINAS>, IEnumerable <OfficeDtos> >(value);
}
return(officeDtos);
}
/// <summary>
/// It load at most n entities. It retains the state in order to support paging.
/// </summary>
/// <param name="n">Number of state.</param>
/// <returns>It returns a list of n client data transfer objects</returns>
public async Task <IEnumerable <ClientDto> > LoadValueAtMostAsync(int n, int back)
{
IEnumerable <ClientDto> dtoCollection = new List <ClientDto>();
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParamRange(QueryType.QueryPagedClient, _currentQueryPos, n);
var query = store.BuildQuery();
_currentQueryPos = _currentQueryPos + n - back;
using (IDbConnection conn = _sqlExecutor.OpenNewDbConnection())
{
var currentPoco = await conn.QueryAsync <ClientPoco>(query);
dtoCollection = _mapper.Map <IEnumerable <ClientPoco>, IEnumerable <ClientDto> >(currentPoco);
}
return(dtoCollection);
}
public void Should_Build_QueryFromQueryStoreWithNullParameters2()
{
CompanyDto dto = new CompanyDto
{
CP = null,
PROVINCIA = null,
Code = "282998"
};
IQueryStore store = _storeFactory.GetQueryStore();
store.Clear();
store.AddParam(QueryType.QueryCity, dto.CP);
store.AddParam(QueryType.QueryProvince, dto.PROVINCIA);
store.AddParam(QueryType.QueryCompanyOffices,
dto.Code);
var q = store.BuildQuery();
Assert.AreEqual(q, Query3);
}
public void Should_Build_QueryFromQueryStoreWithNullParameters()
{
CompanyViewObject viewObject = new CompanyViewObject
{
CP = "1892829",
PROVINCIA = null,
Code = "282998"
};
IQueryStore store = _storeFactory.GetQueryStore();
store.Clear();
store.AddParam(QueryType.QueryCity, viewObject.CP);
store.AddParam(QueryType.QueryProvince, viewObject.PROVINCIA);
store.AddParam(QueryType.QueryCompanyOffices,
viewObject.Code);
var q = store.BuildQuery();
Assert.AreEqual(q, Query2);
}
public async Task <IEnumerable <OfficeViewObject> > LoadValueAtMostAsync(int n, int back = 0)
{
IEnumerable <OfficeViewObject> officeDtos;
IQueryStore store = _queryStoreFactory.GetQueryStore();
if (_currentPos <= 0)
{
_currentPos = 1;
}
using (IDbConnection connection = _executor.OpenNewDbConnection())
{
store.AddParam <int, long>(QueryType.QueryOfficePaged, n, _currentPos);
_currentPos += n - back;
var query = store.BuildQuery();
var value = await connection.QueryAsync <OFICINAS>(query);
officeDtos = _mapper.Map <IEnumerable <OFICINAS>, IEnumerable <OfficeViewObject> >(value);
}
return(officeDtos);
}
/// <summary>
/// This saves the holiday in the office.
/// </summary>
/// <param name="currentOffice">Current office to be saved.</param>
/// <param name="holidayDto">List of vacation for the current office.</param>
/// <returns>return a task for saving the holidays</returns>
private async Task SaveHolidayOfficeAsync(IDbConnection connection, OFICINAS currentOffice, IEnumerable <HolidayDto> holidayDto)
{
Contract.Requires(connection != null, "Connection is not null");
Contract.Requires(currentOffice != null, "Current office is not null");
Contract.Requires(holidayDto != null, "HolidayDto is not null");
IEnumerable <FESTIVOS_OFICINA> holidayOffice = _mapper.Map <IEnumerable <HolidayDto>, IEnumerable <FESTIVOS_OFICINA> >(holidayDto);
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.HolidaysByOffice, currentOffice.CODIGO);
var query = store.BuildQuery();
bool saved = false;
// First i want fetch the current festivo oficina.
// we shall insert or merge.
try
{
IEnumerable <FESTIVOS_OFICINA> currentHolidays = await connection.QueryAsync <FESTIVOS_OFICINA>(query);
if (currentHolidays.Count <FESTIVOS_OFICINA>() == 0)
{
connection.BulkInsert(holidayOffice);
}
else
{
// FIXME : check for concurrent optimistic lock.
var holidaysToBeInserted = holidayOffice.Except(currentHolidays);
connection.BulkInsert <FESTIVOS_OFICINA>(holidaysToBeInserted);
var holidaysToBeUpdated = holidayOffice.Intersect(currentHolidays);
connection.BulkUpdate <FESTIVOS_OFICINA>(holidaysToBeUpdated);
}
saved = true;
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new DataLayerException(e.Message, e);
}
Contract.Ensures(saved);
}
/// <summary>
/// Load just a company with a code primary key value
/// </summary>
/// <param name="code">Identifier of the company</param>
/// <returns>The company data transfer object.</returns>
public async Task <CompanyDto> LoadValueAsync(string code)
{
CompanyDto companyDto = new CompanyDto();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
var value = await connection.GetAsync <SUBLICEN>(code);
companyDto = _mapper.Map <SUBLICEN, CompanyDto>(value);
if (value != null)
{
SqlMapper.GridReader reader = null;
IQueryStore store = CreateQueryStore(companyDto);
string multipleQuery = store.BuildQuery();
reader = await connection.QueryMultipleAsync(multipleQuery);
// set the helpers and maps the offices that belong to a company.
SetDataTransferObject(reader, value, ref companyDto);
}
}
return(companyDto);
}
/// <summary>
/// This is a safe contract to be used.
/// </summary>
/// <returns>A list of contracts.</returns>
public async Task <IEnumerable <ContractSummaryDto> > GetContractSummaryAsync()
{
IQueryStore store = QueryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryContractSummaryBasic);
var query = store.BuildQuery();
using (var conn = SqlExecutor.OpenNewDbConnection())
{
if (conn == null)
{
return(new List <ContractSummaryDto>());
}
var resultValues = await conn?.QueryAsync <ContractSummaryDto>(query);
if (resultValues != null)
{
return(resultValues);
}
}
// safe default in case of error.
return(new List <ContractSummaryDto>());
}
/// <summary>
///Get the list of company summary.
/// </summary>
/// <returns>Return the list of company summary</returns>
public async Task <IEnumerable <CompanySummaryDto> > GetAsyncAllCompanySummary()
{
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QueryCompanySummary);
var query = store.BuildQuery();
IEnumerable <CompanySummaryDto> companySummaryDto = new ObservableCollection <CompanySummaryDto>();
using (IDbConnection conn = sqlExecutor.OpenNewDbConnection())
{
try
{
companySummaryDto = await conn.QueryAsync <CompanySummaryDto>(query);
} catch (System.Exception ex)
{
throw new DataAccessLayerException("GetAsyncAllCompanySummary exception", ex);
}
finally
{
conn.Close();
}
}
return(companySummaryDto);
}
/// <summary>
/// Load single value. It loads a commission agent.
/// </summary>
/// <param name="commissionDictionary">Dictionary of the selected fields</param>
/// <param name="commissionId"></param>
/// <returns></returns>
public async Task <bool> LoadValue(IDictionary <string, string> commissionDictionary, string commissionId)
{
// in the commission query already i have a commission id.
bool preCondition = commissionDictionary.ContainsKey(Comisio);
Contract.Requires(preCondition, "The dictionary used is not correct");
Contract.Requires(!string.IsNullOrEmpty(commissionId), "The commission is is not ok");
logger.Info("Load Agent for ID" + commissionId);
string commisionQueryFields = commissionDictionary[Comisio];
string tipoComiFields = GetFields(Tipocomi, commissionDictionary, DefaultTicomiFields);
string branchesField = GetFields(Branches, commissionDictionary, DefaultDelegation);
// this collect all kind of objects as result of the query.
bool isOpen = false;
if (_dbConnection == null)
{
logger.Debug("Opening Connection to DB");
isOpen = _executor.Open();
}
// now between the two
// TODO: all this is pretty slow. Just use query store and load it.
IQueryStore store = _queryStoreFactory.GetQueryStore();
if (isOpen)
{
try
{
_dbConnection = _executor.Connection;
string commisionQuery = string.Format(_queryComi, commisionQueryFields, commissionId);
logger.Debug("Executing query " + commisionQuery);
_commissionAgents = await _dbConnection.QueryAsync <COMISIO>(commisionQuery);
_currentComisio = _commissionAgents.FirstOrDefault();
if (_currentComisio != null)
{
if (!string.IsNullOrEmpty(_currentComisio.PROVINCIA))
{
string provinceQuery = string.Format(_queryProvincia, _currentComisio.PROVINCIA);
_provinces = await _dbConnection.QueryAsync <PROVINCIA>(provinceQuery).ConfigureAwait(false);
ProvinceDto = _mapper.Map <IEnumerable <PROVINCIA>, IEnumerable <ProvinciaDto> >(_provinces);
}
store.AddParam(QueryType.QueryBrokerContacts, _currentComisio.NUM_COMI);
var queryContactos = store.BuildQuery();
_contactos = await _dbConnection
.QueryAsync <ContactsComiPoco>(queryContactos);
ContactsDto = _mapper.Map <IEnumerable <ContactsComiPoco>, IEnumerable <ContactsDto> >(_contactos);
string queryTipoComi = string.Format(_queryTipoComi, tipoComiFields, _currentComisio.TIPOCOMI);
logger.Debug("Query commission agent kind: " + queryTipoComi);
_tipoComis = await _dbConnection.QueryAsync <TIPOCOMI>(queryTipoComi).ConfigureAwait(false);
if (!string.IsNullOrEmpty(_currentComisio.NACIOPER))
{
string queryPais = string.Format(_queryPais, _currentComisio.NACIOPER);
logger.Debug("Query commission agent kind: " + queryPais);
_nacioPer = await _dbConnection.QueryAsync <Country>(queryPais);
CountryDto = _mapper.Map <IEnumerable <Country>, IEnumerable <CountryDto> >(_nacioPer);
}
string delega = string.Format(_queryComiDelega, branchesField, _currentComisio.NUM_COMI);
_delegations = await _dbConnection.QueryAsync <ComiDelegaPoco, PROVINCIA, ComiDelegaPoco>(delega,
(branch, prov) =>
{
branch.PROV = prov;
return(branch);
}, splitOn : "SIGLAS");
BranchesDto = _mapper.Map <IEnumerable <ComiDelegaPoco>, IEnumerable <BranchesDto> >(_delegations);
store.Clear();
store.AddParam(QueryType.QueryBrokerVisit, _currentComisio.NUM_COMI);
var visitas = store.BuildQuery();
_visitasComis = await _dbConnection.QueryAsync <VisitasComiPoco>(visitas);
VisitsDto = _mapper.Map <IEnumerable <VisitasComiPoco>, IEnumerable <VisitsDto> >(_visitasComis);
var visitType = await _dbConnection.GetAsyncAll <TIPOVISITAS>();
VisitTypeDto = _mapper.Map <IEnumerable <TIPOVISITAS>, IEnumerable <VisitTypeDto> >(visitType);
logger.Debug("Executing AuxQueries.");
await BuildAuxQueryMultiple(_currentComisio, _dbConnection);
}
}
catch (System.Exception e)
{
logger.Info("Cannot open value" + e.Message);
return(false);
}
finally
{
_executor.Close();
logger.Debug("Connection close with success.");
}
}
else
{
logger.Debug("Current commisionist is null.");
return(false);
}
_valid = true;
return(true);
}
/// <summary>
/// Load a single client value
/// </summary>
/// <param name="code">Code to load</param>
/// <returns></returns>
public async Task <bool> LoadValue(string code)
{
Contract.Assert(!string.IsNullOrEmpty(code), "Client code shall be not empty");
IDbConnection conn = null;
bool returnValue = false;
if (_sqlExecutor.Open())
{
conn = _sqlExecutor.Connection;
}
if (conn != null)
{
// here we shall load the entity and the helpers.
_currentPoco = await LoadEntity(conn, code);
// we load the helpers.
if (_currentPoco != null)
{
IQueryStore store = CreateQueryStore(_currentPoco);
string multipleQuery = store.BuildQuery();
if (!string.IsNullOrEmpty(multipleQuery))
{
var reader = await conn.QueryMultipleAsync(multipleQuery).ConfigureAwait(false);
if (_currentPoco.Helper != null)
{
try
{
_currentPoco.Helper.CityDto = WrappedSelectedDto <POBLACIONES, CityDto>(_currentPoco.CP, _mapper, reader);
_currentPoco.Helper.ProvinciaDto = WrappedSelectedDto <PROVINCIA, ProvinciaDto>(_currentPoco.PROVINCIA, _mapper, reader);
_currentPoco.Helper.CountryDto = WrappedSelectedDto <Country, CountryDto>(_currentPoco.NACIODOMI, _mapper, reader);
_currentPoco.Helper.ClientTypeDto = WrappedSelectedDto <TIPOCLI, ClientTypeDto>(_currentPoco.TIPOCLI, _mapper, reader);
_currentPoco.Helper.ClientMarketDto = WrappedSelectedDto <MERCADO, MercadoDto>(_currentPoco.MERCADO, _mapper, reader);
_currentPoco.Helper.ZoneDto = WrappedSelectedDto <ZONAS, ClientZoneDto>(_currentPoco.ZONA, _mapper, reader);
_currentPoco.Helper.LanguageDto = WrappedSelectedDto <IDIOMAS, LanguageDto>(_currentPoco.IDIOMA, _mapper, reader);
_currentPoco.Helper.CreditCardType = WrappedSelectedDto <TARCREDI, CreditCardDto>(_currentPoco.TARTI, _mapper, reader);
_currentPoco.Helper.ChannelDto = WrappedSelectedDto <CANAL, ChannelDto>(_currentPoco.CANAL, _mapper, reader);
_currentPoco.Helper.CompanyDto = WrappedSelectedDto <SUBLICEN, CompanyDto>(_currentPoco.SUBLICEN, _mapper, reader);
_currentPoco.Helper.OfficeDto = WrappedSelectedDto <OFICINAS, OfficeDtos>(_currentPoco.OFICINA, _mapper, reader);
_currentPoco.Helper.ClientPaymentForm = WrappedSelectedDto <FORMAS, PaymentFormDto>(_currentPoco.FPAGO, _mapper, reader);
_currentPoco.Helper.ResellerDto = WrappedSelectedDto <VENDEDOR, ResellerDto>(_currentPoco.VENDEDOR, _mapper, reader);
_currentPoco.Helper.ActivityDto = WrappedSelectedDto <ACTIVI, ActividadDto>(_currentPoco.SECTOR, _mapper, reader);
_currentPoco.Helper.OrigenDto = WrappedSelectedDto <ORIGEN, OrigenDto>(_currentPoco.ORIGEN, _mapper, reader);
_currentPoco.Helper.BusinessDto = WrappedSelectedDto <NEGOCIO, BusinessDto>(_currentPoco.NEGOCIO, _mapper, reader);
_currentPoco.Helper.InvoiceBlock = WrappedSelectedDto <BLOQUEFAC, InvoiceBlockDto>(_currentPoco.BLOQUEFAC, _mapper, reader);
_currentPoco.Helper.BudgetKeyDto = WrappedSelectedDto <CLAVEPTO, BudgetKeyDto>(_currentPoco.CLAVEPTO, _mapper, reader);
_currentPoco.Helper.DriversDto = WrappedSelectedDto <ClientSummaryDto, ClientSummaryDto>(_currentPoco.CLIENTEFAC, _mapper, reader);
_currentPoco.Helper.BrokerDto = WrappedSelectedDto <CommissionAgentSummaryDto, CommissionAgentSummaryDto>(_currentPoco.COMISIO, _mapper, reader);
_currentPoco.ContactsDto = WrappedSelectedDto <CliContactsPoco, ContactsDto>(_currentPoco.NUMERO_CLI, _mapper, reader);
_currentPoco.BranchesDto = WrappedSelectedDto <CliContactsPoco, BranchesDto>(_currentPoco.NUMERO_CLI, _mapper, reader);
_currentPoco.Helper.RentUsageDto = WrappedSelectedDto <USO_ALQUILER, RentingUseDto>(_currentPoco.USO_ALQUILER, _mapper, reader);
}
catch (System.Exception ex)
{
// this is an antipatter log and throw but i need the log.
logger.Error(ex.Message);
throw new DataLayerException(ex.Message, ex);
}
}
}
}
if (_currentPoco == null)
{
return(returnValue);
}
returnValue = true;
Valid = true;
}
return(returnValue);
}
/// <summary>
/// This load asynchronously a vehicle.
/// </summary>
/// <param name="fields">Fields to be loaded.</param>
/// <param name="codeVehicle">Vehicle code to be loaded.</param>
/// <returns></returns>
public async Task <bool> LoadValue(IDictionary <string, string> fields, string codeVehicle)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Contract.Requires(!string.IsNullOrEmpty(fields["VEHICULO1"]));
Contract.Requires(!string.IsNullOrEmpty(fields["VEHICULO2"]));
Contract.Requires(_vehicleMapper != null);
QueryStoreFactory storeFactory = new QueryStoreFactory();
IQueryStore store = storeFactory.GetQueryStore();
store.AddParam(QueryType.QueryVehicle, codeVehicle);
string vehicleQuery = store.BuildQuery();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
try
{
var queryResult = await connection.QueryAsync <VehiclePoco>(vehicleQuery);
_vehicleValue = queryResult.FirstOrDefault(c => c.CODIINT == codeVehicle);
if (_vehicleValue == null)
{
return(false);
}
/*
* See if for the lookup tables. we shall try to use multiple query,
*/
var query = string.Format(BrandByVehicle, _vehicleValue.CODIINT);
var brand = await connection.QueryAsync <MARCAS>(query);
BrandDtos = _vehicleMapper.Map <IEnumerable <MARCAS>, IEnumerable <BrandVehicleDto> >(brand);
var queryPicture = string.Format(PhotoByValue, _vehicleValue.CODIINT);
_pictureResult = await connection.QueryAsync <PICTURES>(queryPicture);
PictureDtos = _vehicleMapper.Map <IEnumerable <PICTURES>, IEnumerable <PictureDto> >(_pictureResult);
var queryActivi = string.Format(ActividadByVehicle, _vehicleValue.ACTIVIDAD);
var actividad = await connection.QueryAsync <ACTIVEHI>(queryActivi);
ActividadDtos = _vehicleMapper.Map <IEnumerable <ACTIVEHI>, IEnumerable <ActividadDto> >(actividad);
// this is the owner. Just in this case i sue the dto.
string queryOwner = string.Format(OwnersByVehicle, _vehicleValue.PROPIE);
AssistQueryOwner = queryOwner;
OwnerDtos = await connection.QueryAsync <OwnerDto>(queryOwner);
VehicleAgentQuery = string.Format(AgentByVehicule, _vehicleValue.AGENTE);
AgentDtos = await connection.QueryAsync <AgentDto>(VehicleAgentQuery);
var maintananceQuery = string.Format(MaintenanceQuery, _vehicleValue.CODIINT);
MaintenanceDtos = await connection.QueryAsync <MaintainanceDto>(maintananceQuery);
var queryVehicle = CraftModelQuery(_vehicleValue);
var colors = CraftColorQuery(_vehicleValue.COLOR);
var cl = await connection.QueryAsync <COLORFL>(colors);
ColorDtos = _vehicleMapper.Map <IEnumerable <COLORFL>, IEnumerable <ColorDto> >(cl);
var models = await connection.QueryAsync <MODELO>(queryVehicle);
var q = CraftVehicleGroup(_vehicleValue);
var grupos = await connection.QueryAsync <GRUPOS>(q);
VehicleGroupDtos = _vehicleMapper.Map <IEnumerable <GRUPOS>, IEnumerable <VehicleGroupDto> >(grupos);
ModelDtos = _vehicleMapper.Map <IEnumerable <MODELO>, IEnumerable <ModelVehicleDto> >(models);
Valid = true;
}
catch (System.Exception e)
{
string message = "Vehicle Loading error: " + e.Message;
throw new DataLayerExecutionException(message);
}
}
stopwatch.Stop();
// around 100 ms/ 90 ms just to load the vehicle.
long value = stopwatch.ElapsedMilliseconds;
return(true);
}
/* TODO: Refactor this like the client loader. Too many queries. It is slow.
* Too many resposabiltities for this function
* in the crud there is a
*/
public async Task <bool> LoadValue(IDictionary <string, string> fields, string code)
{
var currentQuery = string.Format(SupplierQuery, code);
Valid = false;
MonthsDtos = fillMonths();
using (IDbConnection connection = _sqlExecutor.OpenNewDbConnection())
{
try
{
var queryResult = await connection.QueryAsync <SupplierPoco>(currentQuery).ConfigureAwait(false);
_supplierValue = queryResult.FirstOrDefault(c => c.NUM_PROVEE == code);
if (_supplierValue == null)
{
return(false);
}
Value = _supplierMapper.Map <SupplierPoco, SupplierViewObject>(_supplierValue);
// now we look for aux tables.
if (_supplierValue.TIPO.HasValue)
{
var supplierType = await connection.GetAsyncAll <TIPOPROVE>();
Type = _supplierMapper.Map <IEnumerable <TIPOPROVE>, IEnumerable <SupplierTypeViewObject> >(supplierType);
}
IEnumerable <CU1> accounts = await connection.QueryAsync <CU1>(AccountSelect);
AccountDtos = _supplierMapper.Map <IEnumerable <CU1>, IEnumerable <AccountViewObject> >(accounts);
var provincia = await connection.QueryAsync <PROVINCIA>(ProvinciaSelectAll);
// await BuildAndExecute<PROVINCIA>(connection, ProvinciaSelect, _supplierValue.PROV);
if (provincia != null)
{
ProvinceDtos =
_supplierMapper.Map <IEnumerable <PROVINCIA>, IEnumerable <ProvinceViewObject> >(provincia);
}
var pais = await connection.QueryAsync <Country>(PaisSelectAll);
if (pais != null)
{
CountryDtos = _supplierMapper.Map <IEnumerable <Country>, IEnumerable <CountryViewObject> >(pais);
}
var banks = await BuildAndExecute <BANCO>(connection, BankSelect, _supplierValue.BANCO);
if (banks != null)
{
BanksDtos = _supplierMapper.Map <IEnumerable <BANCO>, IEnumerable <BanksViewObject> >(banks);
if (_supplierValue.VIA.HasValue)
{
var vias = await BuildAndExecute <VIAS, byte>(connection, ViaSelect,
_supplierValue.VIA.Value);
ViasDtos = _supplierMapper.Map <IEnumerable <VIAS>, IEnumerable <ViaViewObject> >(vias);
}
}
QueryStoreFactory queryStoreFactory = new QueryStoreFactory();
IQueryStore store = queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.QuerySuppliersBranches);
var qs = store.BuildQuery();
var branches = await BuildAndExecute <ProDelegaPoco>(connection, qs,
_supplierValue.NUM_PROVEE);
var tmp = _supplierMapper.Map <IEnumerable <ProDelegaPoco>, IEnumerable <BranchesViewObject> >(branches);
BranchesDto = tmp;
var contacts =
await BuildAndExecute <ProContactos>(connection, GenericSql.ContactsQuery,
_supplierValue.NUM_PROVEE);
ContactsDto = _supplierMapper.Map <IEnumerable <ProContactos>, IEnumerable <ContactsViewObject> >(contacts);
//var months = await connection.QueryAsync<MESES>(MonthsSelect);
//MonthsDtos = _supplierMapper.Map<IEnumerable<MESES>, IEnumerable<MonthsViewObject>>(months);
var languages = await connection.QueryAsync <IDIOMAS>(LanguageSelect);
LanguageDtos = _supplierMapper.Map <IEnumerable <IDIOMAS>, IEnumerable <LanguageViewObject> >(languages);
var paymentForms = await connection.QueryAsync <FORMAS>(PaymentFormSelect);
PaymentDtos = _supplierMapper.Map <IEnumerable <FORMAS>, IEnumerable <PaymentFormViewObject> >(paymentForms);
var currency = await connection.QueryAsync <DIVISAS>(CurrencySelect);
CurrencyDtos = _supplierMapper.Map <IEnumerable <DIVISAS>, IEnumerable <CurrencyViewObject> >(currency);
// poblacion mapping
var globalMapper = MapperField.GetMapper();
if (_supplierValue.FORMA_ENVIO.HasValue)
{
var deliveringForm = await BuildAndExecute <FORMAS_PEDENT>(connection,
GenericSql.DeliveringFromQuery,
_supplierValue?.FORMA_ENVIO.ToString());
var mapper = MapperField.GetMapper();
DeliveringFormDto = globalMapper.Map <IEnumerable <FORMAS_PEDENT>, IEnumerable <DeliveringFormViewObject> >(deliveringForm);
}
var cityMapper = _supplierMapper;
var cityQuery = string.Format(_queryCity);
var cities = await connection.QueryAsync <POBLACIONES>(cityQuery);
var mappedCityDtos = cityMapper.Map <IEnumerable <POBLACIONES>, IEnumerable <CityViewObject> >(cities);
CityDtos = new ObservableCollection <CityViewObject>(mappedCityDtos);
// office mapping
string query = string.Format(OfficeSelect, _supplierValue.OFICINA);
var office = await connection.QueryAsync <OFICINAS>(query);
var mappedOffices = _supplierMapper.Map <IEnumerable <OFICINAS>, IEnumerable <OfficeViewObject> >(office);
OfficeDtos = new ObservableCollection <OfficeViewObject>(mappedOffices);
query = string.Format(CompanySelect, _supplierValue.SUBLICEN);
var company = await connection.QueryAsync <SUBLICEN>(query);
CompanyDtos = _supplierMapper.Map <IEnumerable <SUBLICEN>, IEnumerable <CompanyViewObject> >(company);
Value = _supplierMapper.Map <SupplierPoco, SupplierViewObject>(_supplierValue);
Valid = true;
}
catch (System.Exception e)
{
throw new DataLayerException("Cannot load supplier " + e.Message, e);
}
}
return(Valid);
}
