/// <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 void Should_Build_QueryFromQueryStoreWithParameters() { _store.AddParam(QueryType.QueryCity, "0001"); _store.AddParam(QueryType.QueryLanguage, "0001"); var value = _store.BuildQuery(); Assert.AreEqual(value, CityLanguageQuery); }
private IQueryStore CreateQueryStore(OFICINAS office) { IQueryStore store = _queryStoreFactory.GetQueryStore(); store.AddParam(QueryType.QueryCity, office.CP); store.AddParam(QueryType.QueryOfficeZone, office.ZONAOFI); return(store); }
/// <summary> /// Create a local query store. /// </summary> /// <param name="company">Company data transfer object used to be inserted in the query store.</param> /// <returns>Return an interface of the query store.</returns> private IQueryStore CreateQueryStore(CompanyDto company) { IQueryStore store = _queryStoreFactory.GetQueryStore(); store.AddParam(QueryType.QueryCity, company.City.Code); store.AddParam(QueryType.QueryCountry, company.Country.Code); store.AddParam(QueryType.QueryProvince, company.Province.Code); store.AddParam(QueryType.QueryOffice, company.Code); return(store); }
/// <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 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); }
/// <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); }
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); }
private IQueryStore AddParams(IQueryStore auxQueryStore, VehicleDto resultQuery) { auxQueryStore.AddParam(QueryType.QueryVehicleModelWithCount, resultQuery.MAR, resultQuery.MO1, resultQuery.MO2); auxQueryStore.AddParamCount(QueryType.QueryBrandByVehicle, resultQuery.MAR, resultQuery.CODIINT); auxQueryStore.AddParam(QueryType.QueryVehiclePhoto, resultQuery.CODIINT); auxQueryStore.AddParamCount(QueryType.QueryVehicleActivity, resultQuery.ACTIVIDAD); auxQueryStore.AddParamCount(QueryType.QueryVehicleOwner, resultQuery.PROPIE); auxQueryStore.AddParamCount(QueryType.QueryAgentByVehicle, resultQuery.AGENTE_VEHI); auxQueryStore.AddParam(QueryType.QueryVehicleMaintenance, resultQuery.CODIINT); auxQueryStore.AddParamCount(QueryType.QueryVehicleColor, resultQuery.COLOR); auxQueryStore.AddParamCount(QueryType.QueryVehicleGroup, resultQuery.GRUPO); auxQueryStore.AddParamCount(QueryType.QueryPaymentForm, resultQuery.FORPAGOCO); auxQueryStore.AddParamCount(QueryType.QuerySupplierSummaryById, resultQuery.PROVEEDOR); auxQueryStore.AddParamCount(QueryType.QueryClientSummaryExtById, resultQuery.COMPRADOR); auxQueryStore.AddParamCount(QueryType.QueryReseller, resultQuery.VENDEDOR_VTA); auxQueryStore.AddParamCount(QueryType.QueryCity, resultQuery.POBLA_IMP); auxQueryStore.AddParamCount(QueryType.QueryOfficeZone, resultQuery.ZONA_IMP); return(auxQueryStore); }
/// <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); } }
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); }
/// <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); }
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> /// 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); }
private IQueryStore CreateQueryStore(ClientDto clientPoco) { IQueryStore store = _queryStoreFactory.GetQueryStore(); store.AddParam(QueryType.QueryCity, clientPoco.CP); store.AddParam(QueryType.QueryClientType, clientPoco.TIPOCLI); store.AddParam(QueryType.QueryMarket, clientPoco.MERCADO); store.AddParam(QueryType.QueryZone, clientPoco.ZONA); store.AddParam(QueryType.QueryLanguage, ValueToString(clientPoco.IDIOMA)); store.AddParam(QueryType.QueryCreditCard, clientPoco.TARTI); store.AddParam(QueryType.QueryChannel, clientPoco.CANAL); store.AddParam(QueryType.QueryCompany, clientPoco.SUBLICEN); store.AddParam(QueryType.QueryOffice, clientPoco.OFICINA); store.AddParam(QueryType.QueryRentingUse, ValueToString(clientPoco.USO_ALQUILER)); store.AddParam(QueryType.QueryActivity, clientPoco.SECTOR); store.AddParam(QueryType.QueryClientSummary, clientPoco.CLIENTEFAC); store.AddParam(QueryType.QueryClientContacts, clientPoco.NUMERO_CLI); store.AddParam(QueryType.QueryPaymentForm, ValueToString(clientPoco.FPAGO)); return(store); }
/* 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); }
/// <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> /// 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); }