private static string BuildFieldsFilter(SearchParams.SpFieldsStructHidden spFieldsStructHidden)
{
if (string.IsNullOrWhiteSpace(spFieldsStructHidden.Value))
{
return(string.Empty);
}
string fieldsFilter;
switch (spFieldsStructHidden.FieldType)
{
case SPFieldType.DateTime:
string[] dateTimes = spFieldsStructHidden.Value.Split(new[] { '#' }, StringSplitOptions.None);
DateTime?dateTimeFrom = null;
DateTime?dateTimeTo = null;
CultureInfo ruRU = new CultureInfo("ru-RU");
if (!string.IsNullOrWhiteSpace(dateTimes[0]))
{
DateTime tFrom;
if (DateTime.TryParseExact(dateTimes[0], "dd.MM.yyyy H:mm", ruRU, DateTimeStyles.None, out tFrom))
{
dateTimeFrom = tFrom;
}
else if (DateTime.TryParseExact(dateTimes[0], "dd.MM.yyyy", ruRU, DateTimeStyles.None, out tFrom))
{
dateTimeFrom = new DateTime(tFrom.Year, tFrom.Month, tFrom.Day, 0, 0, 1);
}
}
if (!string.IsNullOrWhiteSpace(dateTimes[1]))
{
DateTime tTo;
if (DateTime.TryParseExact(dateTimes[1], "dd.MM.yyyy H:mm", ruRU, DateTimeStyles.None, out tTo))
{
dateTimeTo = tTo;
}
else if (DateTime.TryParseExact(dateTimes[1], "dd.MM.yyyy", ruRU, DateTimeStyles.None, out tTo))
{
dateTimeTo = new DateTime(tTo.Year, tTo.Month, tTo.Day, 23, 59, 59);
}
}
if (!dateTimeFrom.HasValue && !dateTimeTo.HasValue)
{
return(string.Empty);
}
if (dateTimeFrom.HasValue && dateTimeTo.HasValue)
{
fieldsFilter = string.Format("(AD.[{0}] >= '{1}' AND AD.[{0}] <= '{2}')",
spFieldsStructHidden.InternalName, dateTimeFrom.Value.ToString(SqlDatetimeFormat), dateTimeTo.Value.ToString(SqlDatetimeFormat));
}
else if (dateTimeFrom.HasValue)
{
fieldsFilter = string.Format("(AD.[{0}] >= '{1}')", spFieldsStructHidden.InternalName, dateTimeFrom.Value.ToString(SqlDatetimeFormat));
}
else
{
fieldsFilter = string.Format("(AD.[{0}] <= '{1}')", spFieldsStructHidden.InternalName, dateTimeTo.Value.ToString(SqlDatetimeFormat));
}
break;
default:
fieldsFilter = "(AD.[" + spFieldsStructHidden.InternalName + "] like '%" + spFieldsStructHidden.Value + "%')";
break;
}
return(fieldsFilter);
}
public StatusValuePair <List <ArchiveEntities> > Search(SearchParams searchParams)
{
List <string> barcodes = searchParams.Barcodes.ToList();
bool? isArchiveElement = searchParams.IsArchiveElement;
List <int> userGroupIds = searchParams.UserGroupIds;
List <SearchParams.SpFieldsStructHidden> fieldsStruct = searchParams.FieldsStruct;
// когда всё пусто
if ((!searchParams.Barcodes.Any() || searchParams.Barcodes.All(string.IsNullOrWhiteSpace)) &&
!searchParams.IsArchiveElement.HasValue &&
searchParams.FieldsStruct.Count == 0)
{
return(new StatusValuePair <List <ArchiveEntities> >(null, ErrorCode.UnknownError));
}
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat(
"SELECT A.* FROM ArchiveEntities AS A left join ArchiveDocsContentType as AD on A.{0}=AD.{0} WHERE ",
Constants.BarCodeFieldName);
// Permissions
string permissionsFilter = string.Empty;
if (userGroupIds.Count > 1)
{
foreach (int uId in userGroupIds)
{
if (permissionsFilter.EndsWith(")"))
{
permissionsFilter += " OR ";
}
permissionsFilter += "([UserGroupId] = " + uId + ")";
}
}
else
{
permissionsFilter = "[UserGroupId] = " + userGroupIds.FirstOrDefault();
}
sqlQuery.AppendFormat(" A.[{0}] IN (SELECT [{0}] FROM [{1}] WHERE ({2})) ",
Constants.BarCodeFieldName,
Constants.PermissionsDataTableName,
permissionsFilter);
// End Permissions
// IsArchiveElement And IsUpdated
if (isArchiveElement.HasValue)
{
sqlQuery.AppendFormat(" AND (A.[IsArchiveElement] = {0}) ", isArchiveElement.Value ? 1 : 0);
}
sqlQuery.Append(" AND (A.[IsUpdated] = 1 OR AD.[IsUpdated] = 1) ");
// End IsArchiveElement And IsUpdated
// BarCodes
string barcodeFilter = string.Empty;
if (barcodes.Count() > 1)
{
foreach (string barcode in barcodes)
{
if (barcodeFilter.EndsWith(")"))
{
barcodeFilter += " OR ";
}
barcodeFilter += "(A.[" + Constants.BarCodeFieldName + "] = '" + barcode + "')";
}
}
else if (barcodes.Any())
{
barcodeFilter = "A.[" + Constants.BarCodeFieldName + "] = '" + barcodes.FirstOrDefault() + "'";
}
// End BarCodes
// Field Filters
string fieldsFilter = string.Empty;
if (!barcodes.Any())
{
if (fieldsStruct.Count() > 1)
{
foreach (SearchParams.SpFieldsStructHidden fieldFilter in fieldsStruct)
{
string buildFieldsFilter = BuildFieldsFilter(fieldFilter);
if (string.IsNullOrWhiteSpace(buildFieldsFilter))
{
continue;
}
if (fieldsFilter.EndsWith(")"))
{
fieldsFilter += " AND ";
}
fieldsFilter += buildFieldsFilter;
}
}
else if (fieldsStruct.Any())
{
SearchParams.SpFieldsStructHidden spFieldsStructHidden = fieldsStruct.First();
if (!string.IsNullOrWhiteSpace(spFieldsStructHidden.InternalName) &&
!string.IsNullOrWhiteSpace(spFieldsStructHidden.Value))
{
fieldsFilter = BuildFieldsFilter(spFieldsStructHidden);
}
}
}
// End Field Filters
if (!string.IsNullOrWhiteSpace(barcodeFilter) && !string.IsNullOrWhiteSpace(fieldsFilter))
{
sqlQuery.AppendFormat(" AND ({0} OR {1}) ", barcodeFilter, fieldsFilter);
}
else if (!string.IsNullOrWhiteSpace(barcodeFilter))
{
sqlQuery.AppendFormat(" AND ({0}) ", barcodeFilter);
}
else if (!string.IsNullOrWhiteSpace(fieldsFilter))
{
sqlQuery.AppendFormat(" AND ({0})", fieldsFilter);
}
string sql = sqlQuery.ToString();
StatusValuePair <List <ArchiveEntities> > entitieses = StoredProcedureList <ArchiveEntities>(sql);
//entitieses.Value = entitieses.Value.Select(archiveItem=>archiveItem);
List <ArchiveEntities> global = entitieses.Value.Select(archiveEntity =>
{
archiveEntity.Barcode = Barcodes.FormatBarcode(archiveEntity.Barcode);
return(archiveEntity);
}).ToList();
if (entitieses.HasValue && entitieses.ErrorCode == ErrorCode.NoError)
{
return(entitieses.Value);
}
return(new StatusValuePair <List <ArchiveEntities> >(null, ErrorCode.UnknownError));
}
