public int GetTotalRowsClients(Connections connections, ClientSearch clientSearch) { QueryWarehouseClient _query = new QueryWarehouseClient(); int totalRows = 0; string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CrateQueryStringCountRowsClients(clientSearch); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command = _query.EmbedParameters(command, clientSearch); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { totalRows = Convert.ToInt32(reader[0]); } reader.Close(); return(totalRows); } catch (Exception ex) { connection.Close(); Console.WriteLine(ex.ToString()); return(0); } } }
public static Clients SearchCompany(String bin, int baseType) { AppJournal.Write("1C Transport", "Search company", true); ChooseBaseType(baseType); var clientSearch = new ClientSearch(); clientSearch.IIN = bin; try { responseData = baseConn.readClient(clientSearch); } catch (Exception) { } if (responseData.RequestSuccess) { return(responseData.ResponseObject as Clients); } else { AppJournal.Write("1C Transport", "Search company error :" + responseData.ErrorMsg, true); return(null); } }
/// <summary> /// Искать клиентов /// </summary> /// <param name="model"></param> /// <returns></returns> public Task <GetListResult <ClientModel> > GetClients(ClientSearch model) { var criterias = GetCriterias(model); var clientQuery = Query <Client>().BuildQuery(criterias) .OrderByDescending(x => x.CreatedOn); return(EFCoreExtensions.GetAsync(model, clientQuery, ClientSelectExpression)); }
internal static IEnumerable <SearchQueryCriteria <Client> > GetCriterias(ClientSearch model) { yield return(model.Q.MapString(str => new SearchQueryCriteria <Client>(x => x.Email.Contains(str) || x.PhoneNumber.Contains(str) || x.Name.Contains(str)))); yield return(model.Deactivated.MapNullable(b => new SearchQueryCriteria <Client>(x => x.DeActivated == b))); yield return(model.RegistrationDate.GetSearchCriteriaFromGenericRange <Client, DateTime>(x => x.CreatedOn)); if (model.SearchSex) { yield return(new SearchQueryCriteria <Client>(x => x.Sex == model.Sex)); } }
public void Delete(ClientSearch request) { var matches = Get(request) as List <Client>; if (true != matches?.Any()) { throw new HttpError(HttpStatusCode.NotFound, "No matches for request"); } matches.ForEach(match => { Delete(match); }); }
public SqlCommand EmbedParameters(SqlCommand command, ClientSearch clientSearch) { if (clientSearch.FirstName != "") { command.Parameters.AddWithValue("@entFirstName", clientSearch.FirstName + "%"); } if (clientSearch.LastName != "") { command.Parameters.AddWithValue("@entLastName", clientSearch.LastName + "%"); } if (clientSearch.MemberID != "") { command.Parameters.AddWithValue("@clientOtherID", clientSearch.MemberID + "%"); } if (clientSearch.AdmissionType != "") { command.Parameters.AddWithValue("@adTypeID", clientSearch.AdmissionType + "%"); } if (clientSearch.UpdatedTo != null) { command.Parameters.Add("@updatedTo", SqlDbType.Date).Value = ((DateTime)(clientSearch.UpdatedTo)).Date; } if (clientSearch.UpdatedFrom != null) { command.Parameters.Add("@updatedFrom", SqlDbType.Date).Value = ((DateTime)(clientSearch.UpdatedFrom)).Date; } if (clientSearch.FTPFileName != "") { command.Parameters.AddWithValue("@ftpFileName", clientSearch.FTPFileName + "%"); } if (clientSearch.Jurisdiction != "") { command.Parameters.AddWithValue("@jurisdictionID", clientSearch.Jurisdiction); } return(command); }
public IActionResult GetSearchClients([FromBody] JObject data) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } Connections connections = data["connection"].ToObject <Connections>(); ClientSearch clientSearch = data["clientSearch"].ToObject <ClientSearch>(); Paging paging = data["paging"].ToObject <Paging>(); var clientResults = _service.GetSearchClients(connections, clientSearch, paging); int totalRows = _service.GetTotalRowsClients(connections, clientSearch); if (clientResults != null) { return(Ok(new { clientResults, totalRows })); } return(BadRequest(new { message = "Error get results!" })); }
public List <ClientResult> GetSearchClients(Connections connections, ClientSearch clientSearch, Paging paging) { QueryWarehouseClient _query = new QueryWarehouseClient(); string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CrateQueryStringClient(clientSearch, paging); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command = _query.EmbedParameters(command, clientSearch); try { List <ClientResult> results = new List <ClientResult>(); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ClientResult clientResult = new ClientResult(); clientResult.SetValuesFromReader(reader); results.Add(clientResult); } reader.Close(); connection.Close(); return(results); } catch (Exception ex) { Console.WriteLine(ex.ToString()); return(null); } finally { connection.Close(); } } }
public string CrateQueryStringClient(ClientSearch clientSearch, Paging paging) { string selectString = "SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS RowNum, jurisdictionID, ftpFileName, entFirstName, entMiddleInitial, entLastName, adTypeID, clientOtherID, clientCustomID, diag10Code, rowcreated, rowupdated, IsProcessed FROM dbo.DataImport_Client "; string filterString = "WHERE "; bool isNoFilter = true; if (clientSearch.FirstName != "") { isNoFilter = false; filterString += "entFirstName like @entFirstName "; } if (clientSearch.LastName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "entLastName like @entLastName "; } if (clientSearch.MemberID != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "clientOtherID like @clientOtherID "; } if (clientSearch.AdmissionType != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "adTypeID like @adTypeID "; } if (clientSearch.UpdatedFrom != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) >= @updatedFrom "; } if (clientSearch.UpdatedTo != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) <= @updatedTo "; } if (clientSearch.FTPFileName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "ftpFileName like @ftpFileName "; } if (clientSearch.Jurisdiction != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "jurisdictionID = @jurisdictionID "; } int indexRowStart = paging.PageSize * (paging.PageNumber - 1) + 1; int indexRowEnd = indexRowStart + paging.PageSize - 1; string pagingString = $"WHERE RowNum >= ${indexRowStart} AND RowNum <= ${indexRowEnd}"; string queryString = isNoFilter ? selectString + ") AS PAGING " + pagingString : selectString + filterString + ") AS PAGING " + pagingString; return(queryString); }
public string CrateQueryStringCountRowsClients(ClientSearch clientSearch) { string selectString = "SELECT jurisdictionID, ftpFileName, entFirstName, entMiddleInitial, entLastName, adTypeID, clientOtherID, clientCustomID, diag10Code, rowcreated, rowupdated, IsProcessed FROM dbo.DataImport_Client "; string filterString = "WHERE "; bool isNoFilter = true; if (clientSearch.FirstName != "") { isNoFilter = false; filterString += "entFirstName like @entFirstName "; } if (clientSearch.LastName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "entLastName like @entLastName "; } if (clientSearch.MemberID != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "clientOtherID like @clientOtherID "; } if (clientSearch.AdmissionType != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "adTypeID like @adTypeID "; } if (clientSearch.UpdatedFrom != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) >= @updatedFrom "; } if (clientSearch.UpdatedTo != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) <= @updatedTo "; } if (clientSearch.FTPFileName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "ftpFileName like @ftpFileName "; } if (clientSearch.Jurisdiction != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "jurisdictionID = @jurisdictionID "; } string queryString = isNoFilter ? selectString : selectString + filterString; string countString = "SELECT COUNT(*) FROM (" + queryString + ") as CO"; return(countString); }
private IQueryable <DocEntityClient> _ExecSearch(ClientSearch request, DocQuery query) { request = InitSearch <Client, ClientSearch>(request); IQueryable <DocEntityClient> entities = null; query.Run(session => { entities = query.SelectAll <DocEntityClient>(); if (!DocTools.IsNullOrEmpty(request.FullTextSearch)) { var fts = new ClientFullTextSearch(request); entities = GetFullTextSearch <DocEntityClient, ClientFullTextSearch>(fts, entities); } if (null != request.Ids && request.Ids.Any()) { entities = entities.Where(en => en.Id.In(request.Ids)); } if (!DocTools.IsNullOrEmpty(request.Updated)) { entities = entities.Where(e => null != e.Updated && e.Updated.Value.Date == request.Updated.Value.Date); } if (!DocTools.IsNullOrEmpty(request.UpdatedBefore)) { entities = entities.Where(e => null != e.Updated && e.Updated <= request.UpdatedBefore); } if (!DocTools.IsNullOrEmpty(request.UpdatedAfter)) { entities = entities.Where(e => null != e.Updated && e.Updated >= request.UpdatedAfter); } if (!DocTools.IsNullOrEmpty(request.Created)) { entities = entities.Where(e => null != e.Created && e.Created.Value.Date == request.Created.Value.Date); } if (!DocTools.IsNullOrEmpty(request.CreatedBefore)) { entities = entities.Where(e => null != e.Created && e.Created <= request.CreatedBefore); } if (!DocTools.IsNullOrEmpty(request.CreatedAfter)) { entities = entities.Where(e => null != e.Created && e.Created >= request.CreatedAfter); } if (true == request.Archived?.Any() && currentUser.HasProperty(DocConstantModelName.CLIENT, nameof(Reference.Archived), DocConstantPermission.VIEW)) { entities = entities.Where(en => en.Archived.In(request.Archived)); } else { entities = entities.Where(en => !en.Archived); } if (true == request.Locked?.Any()) { entities = entities.Where(en => en.Locked.In(request.Locked)); } if (!DocTools.IsNullOrEmpty(request.DefaultLocale) && !DocTools.IsNullOrEmpty(request.DefaultLocale.Id)) { entities = entities.Where(en => en.DefaultLocale.Id == request.DefaultLocale.Id); } if (true == request.DefaultLocaleIds?.Any()) { entities = entities.Where(en => en.DefaultLocale.Id.In(request.DefaultLocaleIds)); } if (true == request.DivisionsIds?.Any()) { entities = entities.Where(en => en.Divisions.Any(r => r.Id.In(request.DivisionsIds))); } if (true == request.DocumentSetsIds?.Any()) { entities = entities.Where(en => en.DocumentSets.Any(r => r.Id.In(request.DocumentSetsIds))); } if (!DocTools.IsNullOrEmpty(request.Name)) { entities = entities.Where(en => en.Name.Contains(request.Name)); } if (!DocTools.IsNullOrEmpty(request.Names)) { entities = entities.Where(en => en.Name.In(request.Names)); } if (true == request.ProjectsIds?.Any()) { entities = entities.Where(en => en.Projects.Any(r => r.Id.In(request.ProjectsIds))); } if (!DocTools.IsNullOrEmpty(request.Role) && !DocTools.IsNullOrEmpty(request.Role.Id)) { entities = entities.Where(en => en.Role.Id == request.Role.Id); } if (true == request.RoleIds?.Any()) { entities = entities.Where(en => en.Role.Id.In(request.RoleIds)); } if (!DocTools.IsNullOrEmpty(request.SalesforceAccountId)) { entities = entities.Where(en => en.SalesforceAccountId.Contains(request.SalesforceAccountId)); } if (!DocTools.IsNullOrEmpty(request.SalesforceAccountIds)) { entities = entities.Where(en => en.SalesforceAccountId.In(request.SalesforceAccountIds)); } if (true == request.ScopesIds?.Any()) { entities = entities.Where(en => en.Scopes.Any(r => r.Id.In(request.ScopesIds))); } entities = ApplyFilters <DocEntityClient, ClientSearch>(request, entities); if (request.Skip > 0) { entities = entities.Skip(request.Skip.Value); } if (request.Take > 0) { entities = entities.Take(request.Take.Value); } if (true == request?.OrderBy?.Any()) { entities = entities.OrderBy(request.OrderBy); } if (true == request?.OrderByDesc?.Any()) { entities = entities.OrderByDescending(request.OrderByDesc); } }); return(entities); }
public object Get(ClientSearch request) => GetSearchResultWithCache <Client, DocEntityClient, ClientSearch>(DocConstantModelName.CLIENT, request, _ExecSearch);
public object Post(ClientSearch request) => Get(request);