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);
