/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DatasourceResult <List <Product> > > GetByQuery(ElasticSearchQuery query)
{
var roles = _httpContextAccessor.HttpContext.GetRouteValue("roles")?.ToString().Split(",");
var userName = _httpContextAccessor.HttpContext.User.Claims.FirstOrDefault(s => s.Type == "userName")?.Value;
var companyId = _httpContextAccessor.HttpContext.Request?.Headers["CompanyId"].FirstOrDefault();
QueryContainer accessRightContainer = new QueryContainer();
if (!roles.Contains("COMPANY_DATA"))
{
accessRightContainer = Query <Product> .Term(t => t.CreatedBy, userName);
}
var listproducts = new DatasourceResult <List <Product> >
{
From = query.From,
Size = query.Size
};
var searchResponse = await _esClient.SearchAsync <Product>(s => s
.From(query.From)
.Size(query.Size)
.Sort(ss => ss.Field(query.Sort.Field, (SortOrder)query.Sort.SortOrder))
.Source(so => so
.Includes(i => i.Fields(query.Source.Includes.ToArray()))
.Excludes(e => e.Fields(query.Source.Excludes.ToArray())))
.Query(q => q
.Raw(JsonConvert.SerializeObject(query.Query)) && q.Term(t => t.CompanyId, companyId) && q.Term(t => t.IsDelete, false) && accessRightContainer)
);
listproducts.Total = searchResponse.Total;
listproducts.Data = searchResponse.Documents.ToList();
return(listproducts);
}
/// <summary>
/// Executes simple _search query and with sarch_type=count parameter and returns only Aggregations part of result
/// </summary>
/// <param name="filledQuery">Filled query</param>
public dynamic ExecuteAggregate(QueryBuilder filledQuery)
{
ElasticSearchQuery query = BuildQuery(filledQuery);
var result = _elasticSearchExecutor.ExecuteQuery <dynamic>(query, new GetParam("search_type", "count"));
return(result.Aggregations);
}
private static SelectMethod GetSelectMethod(ElasticSearchQuery compositeQuery)
{
var list = (from m in compositeQuery.Methods
where m.MethodType == QueryMethodType.Select
select(SelectMethod) m).ToList <SelectMethod>();
return(list.Count != 1 ? null : list[0]);
}
public ActionResult Search(ElasticSearchQuery searchQuery)
{
//
ElasticSearchService elasticService = new ElasticSearchService();
var result = elasticService
.Search(User.Identity.Name, searchQuery.RawSearch, 1, 20);
return(PartialView("_SearchResult", result));
}
private ElasticSearchQuery BuildQuery(QueryBuilder filledQuery)
{
var indexBuilder = new SmartIndexListBuilder(IndexDescriptors, filledQuery);
string queryJson = _querySerializer.BuildJsonQuery(filledQuery);
string[] queryInexes = indexBuilder.BuildLookupIndexes();
ElasticSearchQuery query = new ElasticSearchQuery(queryJson, queryInexes);
return(query);
}
public override IEnumerable <TElement> FindElements <TElement>(ElasticSearchQuery compositeQuery)
{
var searchResults = Execute(compositeQuery, typeof(TElement));
var list = (from m in compositeQuery.Methods
where m.MethodType == QueryMethodType.Select
select(SelectMethod) m).ToList <SelectMethod>();
var selectMethod = (list.Count() == 1) ? list[0] : null;
var results2 = new ElasticSearchResults <TElement>(_context, searchResults, selectMethod, compositeQuery.VirtualFieldProcessors);
return(results2.GetSearchResults());
}
public async Task <IActionResult> Export([FromBody] ElasticSearchQuery query, [FromQuery] string language)
{
var userId = User.Claims.FirstOrDefault(s => s.Type == "sub").Value;
var result = await _leadRepository.Export(query, language, userId);
var path = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot/Export", result);
var memory = new MemoryStream();
using (var stream = new FileStream(path, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, GetContentType(path), $"{Path.GetFileName(path)}_{DateTime.Now.ToString("yyyyMMddHHmmss")}"));
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DatasourceResult <List <Lead> > > GetByQuery(ElasticSearchQuery query)
{
var roles = _httpContextAccessor.HttpContext.GetRouteValue("roles")?.ToString().Split(",");
var teams = _httpContextAccessor.HttpContext.GetRouteValue("teams")?.ToString().Split(",");
var companyId = _httpContextAccessor.HttpContext.Request?.Headers["CompanyId"].FirstOrDefault();
var userId = _httpContextAccessor.HttpContext.User.Claims.FirstOrDefault(s => s.Type == "sub")?.Value;
QueryContainer accessRightContainer = new QueryContainer();
if (!roles.Contains("COMPANY_DATA"))
{
if (roles.Contains("TEAM_DATA"))
{
accessRightContainer = Query <Lead> .Terms(t => t.Field(f => f.TeamId).Terms(teams)) || Query <Lead> .Term(t => t.StaffInCharge, userId) || Query <Lead> .Term(t => t.SupportStaff, userId);
}
else
{
accessRightContainer = Query <Lead> .Term(t => t.StaffInCharge, userId) || Query <Lead> .Term(t => t.SupportStaff, userId);
}
}
var result = new DatasourceResult <List <Lead> >
{
From = query.From,
Size = query.Size
};
var searchResponse = await _esClient.SearchAsync <Lead>(s => s
.From(query.From)
.Size(query.Size)
.Sort(ss => ss.Field(query.Sort.Field, (SortOrder)query.Sort.SortOrder))
.Source(so => so
.Includes(i => i.Fields(query.Source.Includes.ToArray()))
.Excludes(e => e.Fields(query.Source.Excludes.ToArray())))
.Query(q => q
.Raw(JsonConvert.SerializeObject(query.Query)) && q.Term(t => t.CompanyId, companyId) && q.Term(t => t.IsDelete, false) && accessRightContainer)
);
result.Total = searchResponse.Total;
result.Data = searchResponse.Documents.ToList();
return(result);
}
public override TResult Execute <TResult>(ElasticSearchQuery query)
{
if (typeof(TResult).IsGenericType && (typeof(TResult).GetGenericTypeDefinition() == typeof(SearchResults <>)))
{
var resultType = typeof(TResult).GetGenericArguments()[0];
var results = Execute(query, resultType);
var searchResultsType = typeof(ElasticSearchResults <>).MakeGenericType(new[] { resultType });
var applyScalarMethods = GetType().GetMethod("ApplyScalarMethods", BindingFlags.NonPublic | BindingFlags.Instance).MakeGenericMethod(new[] { typeof(TResult), resultType });
var method = GetSelectMethod(query);
var searchResultsInstance = Activator.CreateInstance(searchResultsType, new object[] { _context, results, method, query.VirtualFieldProcessors });
return((TResult)applyScalarMethods.Invoke(this, new[] { query, searchResultsInstance }));
}
var searchResults = Execute(query, typeof(TResult));
var selectMethod = GetSelectMethod(query);
var processedResults = new ElasticSearchResults <TResult>(_context, searchResults, selectMethod, query.VirtualFieldProcessors);
return(ApplyScalarMethods <TResult, TResult>(query, processedResults));
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <DatasourceResult <List <Team> > > GetByQuery(ElasticSearchQuery query)
{
var companyId = _httpContextAccessor.HttpContext.Request?.Headers["CompanyId"].FirstOrDefault();
var listTeams = new DatasourceResult <List <Team> >
{
From = query.From,
Size = query.Size
};
var searchResponse = await _esClient.SearchAsync <Team>(s => s
.From(query.From)
.Size(query.Size)
.Sort(ss => ss.Field(query.Sort.Field, (SortOrder)query.Sort.SortOrder))
.Source(so => so
.Includes(i => i.Fields(query.Source.Includes.ToArray()))
.Excludes(e => e.Fields(query.Source.Excludes.ToArray())))
.Query(q => q
.Raw(JsonConvert.SerializeObject(query.Query)) && q.Term(t => t.CompanyId, companyId) && q.Term(t => t.IsDelete, false))
);
listTeams.Total = searchResponse.Total;
listTeams.Data = searchResponse.Documents.ToList();
return(listTeams);
}
internal ElasticSearchBooleanOperation(ElasticSearchQuery search)
: base(search)
{
_search = search;
}
public async Task <IActionResult> GetByQuery([FromBody] ElasticSearchQuery query)
{
var data = await _leadRepository.GetByQuery(query);
return(Ok(data));
}
/// <summary>
/// Executes query and returns original JSON response.
/// Usefull when custom deserializer is needed or deserialization is not required
/// </summary>
public string ExecuteQueryRaw(QueryBuilder filledQuery, params GetParam[] additionGetParams)
{
ElasticSearchQuery query = BuildQuery(filledQuery);
return(_elasticSearchExecutor.ExecuteQueryRaw(query, additionGetParams));
}
private TResult ApplyScalarMethods <TResult, TDocument>(ElasticSearchQuery query, ElasticSearchResults <TDocument> processedResults)
{
object scalarResult;
var method = query.Methods.First();
switch (method.MethodType)
{
case QueryMethodType.All:
scalarResult = true;
break;
case QueryMethodType.Any:
scalarResult = processedResults.Any();
break;
case QueryMethodType.Count:
scalarResult = processedResults.Count();
break;
case QueryMethodType.ElementAt:
if (!((ElementAtMethod)method).AllowDefaultValue)
{
scalarResult = processedResults.ElementAt(((ElementAtMethod)method).Index);
}
else
{
scalarResult = processedResults.ElementAtOrDefault(((ElementAtMethod)method).Index);
}
break;
case QueryMethodType.First:
if (!((FirstMethod)method).AllowDefaultValue)
{
scalarResult = processedResults.First();
}
else
{
scalarResult = processedResults.FirstOrDefault();
}
break;
case QueryMethodType.Last:
if (!((LastMethod)method).AllowDefaultValue)
{
scalarResult = processedResults.Last();
}
else
{
scalarResult = processedResults.LastOrDefault();
}
break;
case QueryMethodType.Single:
if (!((SingleMethod)method).AllowDefaultValue)
{
scalarResult = processedResults.Single();
}
else
{
scalarResult = processedResults.SingleOrDefault();
}
break;
case QueryMethodType.GetResults:
{
var searchHits = processedResults.GetSearchHits();
var results2 = FormatFacetResults(processedResults.GetFacets(), query.FacetQueries);
scalarResult = Activator.CreateInstance(typeof(TResult), new object[] { searchHits, processedResults.NumberFound, results2 });
break;
}
case QueryMethodType.GetFacets:
scalarResult = FormatFacetResults(processedResults.GetFacets(), query.FacetQueries);
break;
default:
throw new InvalidOperationException("Invalid query method");
}
return((TResult)System.Convert.ChangeType(scalarResult, typeof(TResult)));
}
/// <summary>
/// Executes simple _search query and fills results(_source) into provided TResultModel
/// </summary>
/// <param name="filledQuery">Filled query</param>
/// <param name="additionGetParams">Additional get parameters to customize ES request</param>
public SearchResult <TResultModel> ExecuteQuery <TResultModel>(QueryBuilder filledQuery, params GetParam[] additionGetParams)
{
ElasticSearchQuery query = BuildQuery(filledQuery);
return(_elasticSearchExecutor.ExecuteQuery <TResultModel>(query, additionGetParams));
}
internal IQueryResponse <Dictionary <string, object> > Execute(ElasticSearchQuery query, Type resultType)
{
var index = _context.Index as ElasticSearchIndex;
if (index == null)
{
return(new QueryResponse <Dictionary <string, object> >());
}
var descriptor = new SearchDescriptor <Dictionary <string, object> >();
descriptor.Query(query.Query);
//TODO: need to determine how to allow type specification, so that we don't have to hard-code AllTypes
//NEST generates the search query URL based on the descriptor type, so if we use Dictionary<string, object> as the descriptor type, the query URL is:
//http://[server]:9200/[indexname]/dictionary`2s/_search
//instead we want it to be:
//http://[server]:9200/[indexname]/_search
//when searching over all types. so basically, we want the default to AllTypes unless a specific type is used.
//In the context of Sitecore search, is it even possible to specify a type for the search? probably, but would need some sort of type mapping...
descriptor.AllTypes();
if (query.Filter != null)
{
descriptor.Filter(filterDescriptor => filterDescriptor.Query(q => query.Filter));
}
if (!Settings.DefaultLanguage.StartsWith(_cultureCode))
{
descriptor.Filter(f => f.Query(q => q.Term("_language", _cultureCode)));
}
var isResultsSizeSet = false;
if (query.Methods != null)
{
var fields = new List <string>();
var selectMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.Select
select(SelectMethod) m).ToList <SelectMethod>();
if (selectMethods.Any())
{
foreach (var method in selectMethods)
{
fields.AddRange(method.FieldNames.Select(fieldName => fieldName.ToLowerInvariant()));
}
if (!_context.SecurityOptions.HasFlag(SearchSecurityOptions.DisableSecurityCheck))
{
fields.Add("_uniqueid");
fields.Add("_datasource");
}
}
var getResultsMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.GetResults
select(GetResultsMethod) m).ToList <GetResultsMethod>();
if (getResultsMethods.Any())
{
if (fields.Count > 0)
{
fields.Add("score");
}
}
if (fields.Count > 0)
{
descriptor.Fields(fields.ToArray());
}
var orderByMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.OrderBy
select(OrderByMethod) m).ToList <OrderByMethod>();
if (orderByMethods.Any())
{
foreach (var method in orderByMethods)
{
var fieldName = method.Field;
switch (method.SortDirection)
{
case SortDirection.Ascending:
descriptor.SortAscending(fieldName);
break;
case SortDirection.Descending:
descriptor.SortDescending(fieldName);
break;
}
}
}
var skipMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.Skip
select(SkipMethod) m).ToList <SkipMethod>();
if (skipMethods.Any())
{
var num = skipMethods.Sum(skipMethod => skipMethod.Count);
descriptor.Skip(num);
}
var takeMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.Take
select(TakeMethod) m).ToList <TakeMethod>();
if (takeMethods.Any())
{
var num2 = takeMethods.Sum(takeMethod => takeMethod.Count);
descriptor.Size(num2); //Take is actually just an alias for Size in NEST, so just use Size instead.
isResultsSizeSet = true;
}
var countMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.Count
select(CountMethod) m).ToList <CountMethod>();
if (query.Methods.Count == 1 && countMethods.Any())
{
descriptor.Size(0); //TODO: is using Size appropriate here? and is "0" the proper value to send?
isResultsSizeSet = true;
}
var anyMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.Any
select(AnyMethod) m).ToList <AnyMethod>();
if (query.Methods.Count == 1 && anyMethods.Any())
{
descriptor.Size(0); //TODO: is using Size appropriate here? and is "0" the proper value to send?
isResultsSizeSet = true;
}
var getFacetsMethods = (from m in query.Methods
where m.MethodType == QueryMethodType.GetFacets
select(GetFacetsMethod) m).ToList <GetFacetsMethod>();
//TODO: implement facet querying
if ((query.FacetQueries.Count > 0) && (getFacetsMethods.Any() || getResultsMethods.Any()))
{
//foreach (var facetQuery in GetFacetsPipeline.Run(new GetFacetsArgs(null, query.FacetQueries, _context.Index.Configuration.VirtualFieldProcessors, _context.Index.FieldNameTranslator)).FacetQueries.ToHashSet())
//{
// if (!facetQuery.FieldNames.Any())
// continue;
// var nullable = facetQuery.MinimumResultCount;
// if (facetQuery.FieldNames.Count() == 1)
// {
// var fieldNameTranslator = FieldNameTranslator as ElasticSearchFieldNameTranslator;
// var indexFieldName = facetQuery.FieldNames.First();
// if (((fieldNameTranslator != null) && (indexFieldName == fieldNameTranslator.StripKnownExtensions(indexFieldName))) && (_context.Index.Configuration.FieldMap.GetFieldConfiguration(indexFieldName) == null))
// {
// indexFieldName = fieldNameTranslator.GetIndexFieldName(indexFieldName.Replace("__", "!").Replace("_", " ").Replace("!", "__"), true);
// }
// IElasticSearchFacetQuery[] queries = new IElasticSearchFacetQuery[1];
// ElasticSearchFacetFieldQuery query2 = new ElasticSearchFacetFieldQuery(indexFieldName)
// {
// MinCount = nullable
// };
// queries[0] = query2;
// options.AddFacets(queries);
// }
// if (facetQuery.FieldNames.Any())
// {
// IElasticSearchFacetQuery[] queryArray2 = new IElasticSearchFacetQuery[1];
// ElasticSearchFacetPivotQuery query3 = new ElasticSearchFacetPivotQuery
// {
// Fields = new[] { string.Join(",", facetQuery.FieldNames) },
// MinCount = nullable
// };
// queryArray2[0] = query3;
// options.AddFacets(queryArray2);
// }
//}
}
}
if (!isResultsSizeSet)
{
descriptor.Size(ContentSearchConfigurationSettings.SearchMaxResults);
}
//var blee = JsonConvert.SerializeObject(descriptor, Formatting.Indented);
var serializedDescriptor = index.Client.Serialize(descriptor);
SearchLog.Log.Info("Serialized Query - " + serializedDescriptor);
var response = index.Client.Search(descriptor);
if (!response.ConnectionStatus.Success)
{
SearchLog.Log.Error("Query exception - " + response.ConnectionStatus.Error.OriginalException);
}
return(response);
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="language"></param>
/// <param name="userId"></param>
/// <returns></returns>
public async Task <string> Export(ElasticSearchQuery query, string language, string userId)
{
query.From = 0;
query.Size = QueryConstant.MaxLimit;
var listLeads = await GetByQuery(query);
var leads = listLeads.Data;
var users = await _accountRepository.GetAll();
var commonData = await _commonDataRepository.GetAll();
var leadStatus = commonData.Where(w => w.DataType == CommonDataType.Status);
var definitionSources = commonData.Where(w => w.DataType == CommonDataType.Source);
var definitionChannels = commonData.Where(w => w.DataType == CommonDataType.Channel);
var listVocative = commonData.Where(w => w.DataType == CommonDataType.Vocative);
var listMaritalStatus = commonData.Where(w => w.DataType == CommonDataType.MaritalStatus);
var listRelationship = commonData.Where(w => w.DataType == CommonDataType.Relationship);
var listGender = commonData.Where(w => w.DataType == CommonDataType.Gender);
string excelName = $"lead_export_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
var path = Path.Combine(
Directory.GetCurrentDirectory(),
"wwwroot/Export", excelName);
FileInfo newFile = new FileInfo(path);
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Lead");
workSheet.TabColor = Color.Black;
workSheet.DefaultRowHeight = 12;
//Header of table
if (language == "vi")
{
workSheet.Row(1).Height = 20;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Cells[1, 1].Value = "Họ tên";
workSheet.Cells[1, 2].Value = "Điện thoại";
workSheet.Cells[1, 3].Value = "Email";
workSheet.Cells[1, 4].Value = "Social Id";
workSheet.Cells[1, 5].Value = "Người phụ trách";
workSheet.Cells[1, 6].Value = "Người hỗ trợ";
workSheet.Cells[1, 7].Value = "Mối quan hệ";
workSheet.Cells[1, 8].Value = "Xưng hô";
workSheet.Cells[1, 9].Value = "Giới tính";
workSheet.Cells[1, 10].Value = "Ngày sinh";
workSheet.Cells[1, 11].Value = "Tình trạng hôn nhân";
workSheet.Cells[1, 12].Value = "Trạng thái";
workSheet.Cells[1, 13].Value = "Sản phẩm quan tâm";
workSheet.Cells[1, 14].Value = "Ghi chú";
workSheet.Cells[1, 15].Value = "Nguồn";
workSheet.Cells[1, 16].Value = "Kênh";
workSheet.Cells[1, 17].Value = "Chiến dịch";
workSheet.Cells[1, 18].Value = "Địa chỉ";
workSheet.Cells[1, 19].Value = "Số CMND";
workSheet.Cells[1, 20].Value = "Ngày cấp";
workSheet.Cells[1, 21].Value = "Nơi cấp";
}
else
{
workSheet.Row(1).Height = 20;
workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(1).Style.Font.Bold = true;
workSheet.Cells[1, 1].Value = "Fullname";
workSheet.Cells[1, 2].Value = "Phone";
workSheet.Cells[1, 3].Value = "Email";
workSheet.Cells[1, 4].Value = "Social Id";
workSheet.Cells[1, 5].Value = "Staff in charge";
workSheet.Cells[1, 6].Value = "Support";
workSheet.Cells[1, 7].Value = "Relationship";
workSheet.Cells[1, 8].Value = "Title";
workSheet.Cells[1, 9].Value = "Gender";
workSheet.Cells[1, 10].Value = "Birthday";
workSheet.Cells[1, 11].Value = "Marriage";
workSheet.Cells[1, 12].Value = "Lead care status";
workSheet.Cells[1, 13].Value = "Product interesting";
workSheet.Cells[1, 14].Value = "Note";
workSheet.Cells[1, 15].Value = "Source";
workSheet.Cells[1, 16].Value = "Source channel";
workSheet.Cells[1, 17].Value = "Campaign";
workSheet.Cells[1, 18].Value = "Address";
workSheet.Cells[1, 19].Value = "Identify";
workSheet.Cells[1, 20].Value = "Date of issue";
workSheet.Cells[1, 21].Value = "Place of issue";
}
//Body of table
int recordIndex = 2;
foreach (var lead in leads)
{
var status = leadStatus.FirstOrDefault(f => f.Id == lead.Status);
var source = definitionSources.FirstOrDefault(f => f.Id == lead.Source);
var channel = definitionChannels.FirstOrDefault(f => f.Id == lead.Channel);
var staffInCharge = users.FirstOrDefault(f => f.Id == lead.StaffInCharge);
var supportStaff = string.Join(", ", users.Where(w => lead.SupportStaff.Contains(w.Id)).Distinct().Select(s => s.FirstName + " " + s.LastName).ToList());
var vocative = listVocative.FirstOrDefault(f => f.Id == lead.Vocative);
var maritalStatus = listMaritalStatus.FirstOrDefault(f => f.Id == lead.MaritalStatus);
var relationship = listRelationship.FirstOrDefault(f => f.Id == lead.Relationship);
var gender = listGender.FirstOrDefault(f => f.Id == lead.Gender);
workSheet.Cells[recordIndex, 1].Value = lead.FullName;
workSheet.Cells[recordIndex, 2].Value = lead.Phone;
workSheet.Cells[recordIndex, 3].Value = lead.Email;
workSheet.Cells[recordIndex, 4].Value = lead.SocialId;
workSheet.Cells[recordIndex, 5].Value = staffInCharge != null ? $"{staffInCharge.FirstName} {staffInCharge.LastName}" : string.Empty;
workSheet.Cells[recordIndex, 6].Value = supportStaff;
workSheet.Cells[recordIndex, 7].Value = relationship != null ? relationship.DataValue : string.Empty;
workSheet.Cells[recordIndex, 8].Value = vocative != null ? vocative.DataValue : string.Empty;
workSheet.Cells[recordIndex, 9].Value = gender != null ? gender.DataValue : string.Empty;
workSheet.Cells[recordIndex, 10].Value = lead.Birthday != null?lead.Birthday.Value.ToString("MM/dd/yyyy") : "";
workSheet.Cells[recordIndex, 11].Value = maritalStatus != null ? maritalStatus.DataValue : string.Empty;
workSheet.Cells[recordIndex, 12].Value = status != null ? status.DataValue : string.Empty;
workSheet.Cells[recordIndex, 13].Value = lead.Interest;
workSheet.Cells[recordIndex, 14].Value = lead.Note;
workSheet.Cells[recordIndex, 15].Value = source != null ? source.DataValue : string.Empty;
workSheet.Cells[recordIndex, 16].Value = channel != null ? channel.DataValue : string.Empty;
workSheet.Cells[recordIndex, 17].Value = lead.Campaign;
workSheet.Cells[recordIndex, 18].Value = lead.Address;
workSheet.Cells[recordIndex, 19].Value = lead.IdentityCard;
workSheet.Cells[recordIndex, 20].Value = lead.DateOfIssue != null?lead.DateOfIssue.Value.ToString("MM/dd/yyyy") : "";
workSheet.Cells[recordIndex, 21].Value = lead.PlaceOfIssue;
recordIndex++;
}
//auto fit
//for (int i = 1; i <= 24; i++)
//{
// workSheet.Column(i).AutoFit();
//}
//color header
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#1e88e5");
workSheet.Cells["A1:U1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells["A1:U1"].Style.Fill.BackgroundColor.SetColor(colFromHex);
workSheet.Cells["A1:U1"].Style.Font.Color.SetColor(Color.White);
//border
string modelRange = "A1:U" + (--recordIndex).ToString();
var modelTable = workSheet.Cells[modelRange];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
excel.SaveAs(newFile);
return(excelName);
}
public async Task <IActionResult> GetByQuery([FromBody] ElasticSearchQuery query)
{
var result = await _teamRepository.GetByQuery(query);
return(Ok(result));
}
/// <summary>
///
/// </summary>
/// <param name="data"></param>
/// <param name="param"></param>
/// <returns></returns>
public static string GetHashValueWithMoreParams(this ElasticSearchQuery data, dynamic[] param)
{
var hashValue = string.Join("", MD5.Create().ComputeHash(Encoding.ASCII.GetBytes(JsonConvert.SerializeObject(data) + JsonConvert.SerializeObject(param))).Select(s => s.ToString("x2")));
return(hashValue);
}
public async Task <IActionResult> GetByQuery([FromBody] ElasticSearchQuery data)
{
var result = await _activityHistoryLeadRepository.GetByQuery(data);
return(Ok(result));
}
