/// <summary>
/// Get multi line query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="fieldName">Name of the field</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <param name="isLikeSearch">Is or not a search with like statement</param>
/// <returns>Object query of Guid</returns>
private static ObjectQuery<Guid> GetMultiLineQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch)
{
string multilineValue = formCollectionFiltered[fieldName];
string queryMultiText = string.Empty;
if (isLikeSearch)
{
queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue like '%"
+ multilineValue + "%' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
}
else
{
queryMultiText = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue= '"
+ multilineValue + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
}
ObjectQuery<Guid> objQueryMultiText = new ObjectQuery<Guid>(queryMultiText, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryMultiText);
else
tempQuery = tempQuery.Intersect(objQueryMultiText);
return tempQuery;
}
/// <summary>
/// Get integer query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="fieldName">Name of the field</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <returns>Object query of Guid</returns>s
private static ObjectQuery<Guid> GetIntegerQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
{
int intValue = int.Parse(formCollectionFiltered[fieldName]);
string queryInteger = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.IntegerValue= "
+ intValue.ToString() + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
ObjectQuery<Guid> objQueryInteger = new ObjectQuery<Guid>(queryInteger, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryInteger);
else
tempQuery = tempQuery.Intersect(objQueryInteger);
return tempQuery;
}
/// <summary>
/// Get date range query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <returns>Object query of Guid</returns>
private static ObjectQuery<Guid> GetDateRangeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
{
string[] OrderDatefromValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("from")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries);
string[] OrderDateToValue = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Value.Split(new char[] { '/', '-' }, StringSplitOptions.RemoveEmptyEntries);
string fieldName = formCollectionFiltered.First(keyPair => keyPair.Key.EndsWith("to")).Key;
fieldName = fieldName.Remove(fieldName.Length - 2, 2);
DateTime OrderDatefrom = new DateTime(int.Parse(OrderDatefromValue[2]), int.Parse(OrderDatefromValue[1]), int.Parse(OrderDatefromValue[0]));
DateTime OrderDateTo = new DateTime(int.Parse(OrderDateToValue[2]), int.Parse(OrderDateToValue[1]), int.Parse(OrderDateToValue[0]));
OrderDatefrom = OrderDatefrom.Date;
OrderDateTo = OrderDateTo.Date.AddDays(1).AddSeconds(-1);
string queryDate = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false and FormValue.DateValue >= DATETIME '"
+ OrderDatefrom.ToString("yyyy-MM-dd HH:mm:ss.fffffff") + "' and FormValue.DateValue <= DATETIME '" + OrderDateTo.ToString("yyyy-MM-dd HH:mm:ss.fffffff")
+ "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
ObjectQuery<Guid> objQueryDate = new ObjectQuery<Guid>(queryDate, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryDate);
else
tempQuery = tempQuery.Intersect(objQueryDate);
return tempQuery;
}
/// <summary>
/// Get catalogue query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="fieldName">Name of the field</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <param name="isLikeSearch">Is or not a search with like statement</param>
/// <returns>Object query of Guid</returns>
private static ObjectQuery<Guid> GetCatalogueQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result, bool isLikeSearch)
{
Guid guidValue = Guid.Empty;
string likeValue = string.Empty;
string queryCatalogue = string.Empty;
if (isLikeSearch)
{
likeValue = formCollectionFiltered[fieldName];
queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue "
+ " inner join VestalisEntities.CatalogueValues as CatalogueValue on FormValue.CatalogueValueId = CatalogueValue.CatalogueValueId"
+ " where FormValue.IsDeleted = false"
+ " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL"
+ " and CatalogueValue.CatalogueValueData like '%" + likeValue + "%' and CatalogueValue.IsDeleted = false";
}
else
{
guidValue = new Guid(formCollectionFiltered[fieldName]);
queryCatalogue = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.CatalogueValueId =Guid '"
+ guidValue.ToString() + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
}
ObjectQuery<Guid> objQueryGuid = new ObjectQuery<Guid>(queryCatalogue, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryGuid);
else
tempQuery = tempQuery.Intersect(objQueryGuid);
return tempQuery;
}
/// <summary>
/// Get time query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="fieldName">Name of the field</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <returns>Object query of Guid</returns>
private static ObjectQuery<Guid> GetTimeQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
{
string timeValue = formCollectionFiltered[fieldName];
string queryTime = "select VALUE FormValue.ServiceOrderId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.TextValue= '"
+ timeValue + "' and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NULL";
ObjectQuery<Guid> objQueryTime = new ObjectQuery<Guid>(queryTime, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryTime);
else
tempQuery = tempQuery.Intersect(objQueryTime);
return tempQuery;
}
/// <summary>
/// Get decimal query
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="formCollectionFiltered">Form collection</param>
/// <param name="fieldName">Name of the field</param>
/// <param name="tempQuery">Temporal query</param>
/// <param name="query1Result">First query</param>
/// <returns>Object query of Guid</returns>
private static ObjectQuery<Guid> GetDecimalQuery(VestalisEntities context, Dictionary<string, string> formCollectionFiltered, string fieldName, ObjectQuery<Guid> tempQuery, ObjectQuery<Guid> query1Result)
{
decimal decimalValue = decimal.Parse(formCollectionFiltered[fieldName]);
string queryDecimal = "select VALUE FormValue.InspectionReportItemId from VestalisEntities.FormValues as FormValue where FormValue.IsDeleted = false AND FormValue.DecimalValue= "
+ decimalValue.ToString() + " and FormValue.FieldName='" + fieldName + "' AND FormValue.InspectionReportItemId IS NOT NULL";
ObjectQuery<Guid> objQueryDecimal = new ObjectQuery<Guid>(queryDecimal, context);
if (tempQuery == null)
tempQuery = query1Result.Intersect(objQueryDecimal);
else
tempQuery = tempQuery.Intersect(objQueryDecimal);
return tempQuery;
}