/// <summary>
/// Get the needed data for writing the excel file
/// </summary>
/// <param name="collection">The collection sent by the form</param>
/// <param name="businessApplicationId">Id of business application</param>
/// <param name="serviceOrderReportId">Id of service order</param>
/// <returns>DynamicDataGrid</returns>
private DynamicDataGrid GetModelExportExcel(FormCollection collection, Guid businessApplicationId, Guid serviceOrderReportId)
{
if (collection.ToFilledDictionary().Keys.Contains("InspectionReportId"))
collection.Remove("InspectionReportId");
if (collection.ToFilledDictionary().Keys.Contains("selectedInspectionReports"))
collection.Remove("selectedInspectionReports");
List<string> fielsdWithLike = new List<string>();
foreach (string name in collection.AllKeys.Where(name => name.Contains("IsLike")))
{
fielsdWithLike.Add(name.Replace("IsLike", ""));
collection.Remove(name);
}
//set the parameters for search
ParameterSearchInspectionReport parameters = new ParameterSearchInspectionReport
{
BusinessApplicationId = businessApplicationId,
Collection = collection,
InspectionReportName = _inspectionReportId,
ServiceOrderId = serviceOrderReportId,
UserName = UserName,
RolesForUser = Roles.GetRolesForUser(UserName).ToList(),
PageSize = 0,
SelectedPage = 0,
IsClient = User.IsInRole("Client"),
IsExport = true,
Captions = _captions,
FieldsWithLike = fielsdWithLike
};
//get the data
return InspectionReportBusiness.SearchInspectionReportList(parameters);
}
/// <summary>
/// Get the information of all inspection reports in the service order
/// </summary>
/// <returns>ExportInspectionReportsModel</returns>
public static ExportInspectionReportsModel SearchInspectionReportsByServiceOrder(ParameterSearchAllInspectionReport parameters)
{
ExportInspectionReportsModel result = new ExportInspectionReportsModel();
using (VestalisEntities context = new VestalisEntities())
{
//Get the list of inspection reports by service order
var inspectionReports = (from inspectionRep in context.InspectionReports
join serviceOrd in context.ServiceOrders on inspectionRep.ServiceOrderId equals serviceOrd.ServiceOrderId
where inspectionRep.IsDeleted == false && serviceOrd.IsDeleted == false &&
serviceOrd.ServiceOrderId == parameters.ServiceOrderId && parameters.SelectedReports.Contains(inspectionRep.FormName)
orderby inspectionRep.FormOrder
select new
{
inspectionRep.FormName,
inspectionRep.FormOrder
}).ToList();
bool isClient = Roles.IsUserInRole(parameters.UserName, "Client");
List<string> rolesForUser = Roles.GetRolesForUser(parameters.UserName).ToList();
foreach (var item in inspectionReports)
{
DynamicDataGrid gridColumns = InspectionReportBusiness.GetInspectionReportGridDefinition(parameters.BusinessApplicationId, isClient, item.FormName);
//set the parameters
ParameterSearchInspectionReport parameterSearchInspectionReport = new ParameterSearchInspectionReport
{
BusinessApplicationId = parameters.BusinessApplicationId,
Collection = new FormCollection(),
InspectionReportName = item.FormName,
ServiceOrderId = parameters.ServiceOrderId,
UserName = parameters.UserName,
RolesForUser = rolesForUser,
PageSize = 0,
SelectedPage = 0,
IsClient = isClient,
IsExport = true,
Captions = gridColumns.Captions
};
//get the information of the current inspection report
DynamicDataGrid model = InspectionReportBusiness.SearchInspectionReportList(parameterSearchInspectionReport);
result.InspectionReports.Add(item.FormName, model);
}
result.ServiceOrderData = GetServiceOrderForm(parameters.BusinessApplicationId, parameters.ServiceOrderId);
if (parameters.IsSelectedServiceOrder)
{
result.IsSelectedServiceOrder = true;
result.ServiceOrderSheetName = parameters.ServiceOrderReportName;
}
}
return result;
}
/// <summary>
/// Search a new inspection Report
/// </summary>
/// <param name="page">Page</param>
/// <returns>PartialViewResult</returns>
public PartialViewResult SearchInspectionReportPaginated(int? page)
{
Guid businessApplicationId = new Guid(Convert.ToString(Session["BusinessAplicationId"]));
Guid serviceOrderReportId = new Guid(Session["serviceOrderReportId"].ToString());
if (_collection.ToFilledDictionary().Keys.Contains("InspectionReportId"))
_collection.Remove("InspectionReportId");
List<string> fielsdWithLike = new List<string>();
foreach (string name in _collection.AllKeys.Where(name => name.Contains("IsLike")))
{
fielsdWithLike.Add(name.Replace("IsLike", ""));
}
ParameterSearchInspectionReport parameters = new ParameterSearchInspectionReport
{
BusinessApplicationId = businessApplicationId,
Collection = _collection,
InspectionReportName = _inspectionReportId,
ServiceOrderId = serviceOrderReportId,
UserName = UserName,
RolesForUser = Roles.GetRolesForUser(UserName).ToList(),
PageSize = Cotecna.Vestalis.Web.Properties.Settings.Default.PageSize,
SelectedPage = page == null ? 1 : page.Value,
IsClient = User.IsInRole("Client"),
FieldsWithLike = fielsdWithLike
};
DynamicDataGrid model = InspectionReportBusiness.SearchInspectionReportList(parameters);
model.Captions = _captions;
model.UserLevel = InspectionReportBusiness.GetRoleLevel(Roles.GetRolesForUser(UserName), businessApplicationId);
return PartialView("_InspectionReportGrid", model);
}
/// <summary>
/// Get the list of inspection reports
/// </summary>
/// <param name="parameters">Filter data to execute the search</param>
/// <returns>DynamicDataGrid</returns>
public static DynamicDataGrid SearchInspectionReportList(ParameterSearchInspectionReport parameters)
{
DynamicDataGrid dynamicDataGrid = new DynamicDataGrid();
DynamicDataRow dataRow = null;
PaginatedList<FormValue> resultQuery = null;
string fieldName = string.Empty;
string fieldValue = string.Empty;
int fieldType = 0;
List<PropertyInfo> filterProperties = null;
List<string> gridFields = new List<string>();
Dictionary<string, DynamicDataRowValue> temRowValues = null;
List<FormValue> inspectionReportItemQuery = null;
List<ApprovalItem> approvalItems = null;
List<Guid?> inspectionReportItemIds = null;
//obtain the definition of the form
Form inspectionReportForm = DynamicFormEngine.GetFormDefinition(parameters.BusinessApplicationId, FormType.InspectionReport, parameters.InspectionReportName, parameters.IsClient);
//obtain the id of the selected inspection report
Guid inspectionReportId = GetInspectionReportByName(parameters.InspectionReportName, parameters.ServiceOrderId).InspectionReportId;
using (VestalisEntities context = new VestalisEntities())
{
//get the query
resultQuery = GetInspectionReportQuery(context, inspectionReportId, parameters, ref inspectionReportItemIds);
//set data of pagination
dynamicDataGrid.Page = parameters.SelectedPage;
dynamicDataGrid.NumberOfPages = resultQuery.NumberOfPages;
dynamicDataGrid.TotalNumberOfItemsWithoutPagination = resultQuery.TotalCount;
dynamicDataGrid.PageSize = parameters.PageSize;
//Get approval items for the inspection report and the roles of the logged user
approvalItems = GetApprovalItemsGivenIds(context, inspectionReportItemIds, parameters.RolesForUser);
//set flags for show buttons
dynamicDataGrid.CanPublishAll = approvalItems.Any(data => data.CanPublish.GetValueOrDefault() && data.ApprovalStatus == (int)ApprovalStatus.Ready);
dynamicDataGrid.CanValidateAll = approvalItems.Any(data => !data.CanPublish.GetValueOrDefault() && data.ApprovalStatus == (int)ApprovalStatus.Ready);
inspectionReportItemQuery = resultQuery.Collection;
var groupInspectionReports = (from formValue in inspectionReportItemQuery
group formValue by new { formValue.InspectionReportItemId }
into rows
select new
{
serviceOrderId = rows.Key,
rows
});
if (inspectionReportForm != null && (inspectionReportItemQuery != null && inspectionReportItemQuery.Count > 0))
{
//get the fields that can be showed in the grid
foreach (var section in inspectionReportForm.Sections)
{
if (parameters.IsClient)
{
gridFields.AddRange(
section.FormElements.Where(element => element.IsDataGridVisible && element.IsVisibleClient).Select(
element => element.Identifier));
}
else
{
gridFields.AddRange(
section.FormElements.Where(element => element.IsDataGridVisible).Select(
element => element.Identifier));
}
}
var formValueProperties = new List<string>();
formValueProperties.Add("FieldType");
formValueProperties.Add("FieldName");
//Get the fields definition for the business application
Fields fieldBusinessApplication = CacheHandler.Get(String.Format("Field{0}", parameters.BusinessApplicationId),
() => DynamicFormEngine.GetFields(parameters.BusinessApplicationId));
foreach (var group in groupInspectionReports)
{
dataRow = new DynamicDataRow();
temRowValues = new Dictionary<string, DynamicDataRowValue>();
gridFields.ForEach(field =>
{
Field currentField = fieldBusinessApplication.Items.FirstOrDefault(x => x.FieldName == field);
if (currentField is FieldsPictureField)
{
if (!parameters.IsExport)
{
temRowValues.Add(field, new DynamicDataRowValue() { FieldType = (int)FieldType.PictureField });
temRowValues[field].Pictures = PictureDocumentBusiness.SearchPictureGridInspectionReport(parameters.ServiceOrderId, group.serviceOrderId.InspectionReportItemId.Value);
}
else
{
var captionPic = parameters.Captions.FirstOrDefault(caption => caption.FieldName == currentField.FieldName);
parameters.Captions.Remove(captionPic);
}
}
else if (currentField is FieldsStatusField)
{
temRowValues.Add(field, new DynamicDataRowValue() { FieldType = (int)FieldType.StatusField });
}
else
{
temRowValues.Add(field, new DynamicDataRowValue() { FieldType = 2 });
}
});
foreach (var data in group.rows)
{
filterProperties = data.GetType().GetProperties().Where(property => formValueProperties.Contains(property.Name)).ToList();
//Get the field name
if (filterProperties.FirstOrDefault(item => item.Name == "FieldName") != null)
{
fieldName = data.GetPropertyValue<object>("FieldName").ToString();
}
//Get the field type and field value
if (filterProperties.FirstOrDefault(item => item.Name == "FieldType") != null)
{
string tempValue = data.GetPropertyValue<object>("FieldType").ToString();
if (!string.IsNullOrEmpty(tempValue))
{
fieldType = int.Parse(tempValue);
//Get the value as a string according the type
fieldValue = GetFieldValue(data, fieldType, fieldValue);
}
}
//if all values are filled,the system continues for create the result
if ((!string.IsNullOrEmpty(fieldName) && gridFields.Any(field => field == fieldName))
&& !string.IsNullOrEmpty(fieldValue) && fieldType > 0)
{
temRowValues[fieldName].FieldValue = fieldValue;
temRowValues[fieldName].FieldType = fieldType;
}
fieldName = string.Empty;
fieldValue = string.Empty;
fieldType = 0;
}
dataRow.FieldValues.Clear();
temRowValues.Values.ToList().ForEach(value =>
{
dataRow.FieldValues.Add(new DynamicDataRowValue { FieldType = value.FieldType, FieldValue = value.FieldValue, Pictures = value.Pictures });
});
//get the identifier of row
Guid inspectionReportItemId = group.serviceOrderId.InspectionReportItemId.Value;
dataRow.RowIdentifier = inspectionReportItemId;
SetApprovalItems(dataRow, approvalItems, inspectionReportItemId);
//add the row to the dinamic grid
dynamicDataGrid.DataRows.Add(dataRow);
}
}
}
SetStatusInspectionReport(dynamicDataGrid);
dynamicDataGrid.Captions = parameters.Captions;
return dynamicDataGrid;
}
/// <summary>
/// This method get dinamically the result according of the filters
/// </summary>
/// <param name="context">Database context</param>
/// <param name="inspectionReportId">if of inspection report item</param>
/// <param name="parameters">Parameters</param>
/// <returns></returns>
private static List<Guid?> GetInspectionReportsFiltered(VestalisEntities context, Guid inspectionReportId, ParameterSearchInspectionReport parameters)
{
List<Guid?> result = new List<Guid?>();
string query1 = string.Empty;
//filter FormCollection object to get a dictionary only with the key that have a value
Dictionary<string, string> formCollectionFiltered = parameters.Collection.ToFilledDictionary();
//get the types of the filters
var queryServiceOrder = (from formValue in context.FormValues
join inspectionReportItem in context.InspectionReportItems on formValue.InspectionReportItemId equals inspectionReportItem.InspectionReportItemId
where formCollectionFiltered.Keys.Contains(formValue.FieldName) && inspectionReportItem.InspectionReportId == inspectionReportId
select new { FieldName = formValue.FieldName, TypeField = formValue.FieldType }).Distinct();
//this query filter the result by inspection report
if (parameters.RolesForUser.Contains("Client"))
{
query1 = "select VALUE DISTINCT inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" +
" inner join VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" +
" inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" +
" inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" +
" where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" +
" and ApprovalItem.IsDeleted = false and ApprovalItem.ApprovalStatus = 3 and ApprovalItem.CanPublish = true" +
" and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'";
}
else
{
string roles = string.Empty;
foreach (string item in parameters.RolesForUser)
{
if (item == parameters.RolesForUser.Last())
roles += "ApprovalItem.RoleName = '" + item + "'";
else
roles += "ApprovalItem.RoleName = '" + item + "' or ";
}
query1 = "select VALUE inspectionReportItem.InspectionReportItemId from VestalisEntities.InspectionReportItems as inspectionReportItem" +
" inner join VestalisEntities.ApprovalItems as approvalItem on inspectionReportItem.InspectionReportItemId = approvalItem.InspectionReportItemId" +
" inner join VestalisEntities.InspectionReports as inspectionReport on inspectionReportItem.InspectionReportId = inspectionReport.InspectionReportId" +
" inner join VestalisEntities.ServiceOrders as serviceOrder on inspectionReport.ServiceOrderId = serviceOrder.ServiceOrderId" +
" where inspectionReportItem.IsDeleted = false AND inspectionReportItem.InspectionReportId = GUID '" + inspectionReportId.ToString() + "'" +
" and ApprovalItem.IsDeleted = false and (" + roles + ") and (ApprovalItem.ApprovalStatus = 3 or ApprovalItem.ApprovalStatus = 2)" +
" and serviceOrder.BusinessApplicationId = GUID '" + parameters.BusinessApplicationId.ToString() + "'";
}
ObjectQuery<Guid> query1Result = new ObjectQuery<Guid>(query1, context);
ObjectQuery<Guid> tempQuery = null;
//for each result of filters, the systems perform a query to filter the corresponding value, but this iteration is not valid when in the filters exist a date range
foreach (var item in queryServiceOrder)
{
string fieldName = item.FieldName;
switch (item.TypeField)
{
case (int)FieldType.Boolean:
tempQuery = GetBooleanQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
break;
case (int)FieldType.Catalogue:
tempQuery = GetCatalogueQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
break;
case (int)FieldType.Decimal:
tempQuery = GetDecimalQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
break;
case (int)FieldType.Integer:
tempQuery = GetIntegerQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
break;
case (int)FieldType.MultipleTextLine:
tempQuery = GetMultiLineQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
break;
case (int)FieldType.RegularExpressionText:
tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
break;
case (int)FieldType.SingleTextLine:
tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FieldsWithLike.Contains(fieldName));
break;
case (int)FieldType.Time:
tempQuery = GetTimeQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result);
break;
default:
break;
}
}
//if in the filter exist a date range, the system will perform the query for filter the results
if (formCollectionFiltered.Keys.Any(key => key.EndsWith("to") || key.EndsWith("from")))
{
tempQuery = GetDateRangeQuery(context, formCollectionFiltered, tempQuery, query1Result);
}
//retreive the results of the dynamic query
if (tempQuery != null)
{
var tempResult = tempQuery.ToList();
if (tempResult.Count > 0)
result = tempResult.Cast<Guid?>().ToList();
}
return result;
}
/// <summary>
/// Get the inspection report items result of the query
/// </summary>
/// <param name="parameters">Parameters</param>
/// <param name="context">Database context</param>
/// <param name="inspectionReportId">Id of inpection repot</param>
/// <param name="result">Result</param>
/// <param name="currentIndex">Current index</param>
/// <param name="inspectionReportItemIds">List of inspection report item ids</param>
private static void GetInspectionReportQueryParameters(ParameterSearchInspectionReport parameters, VestalisEntities context, Guid inspectionReportId, PaginatedList<FormValue> result, int currentIndex, ref List<Guid?> inspectionReportItemIds)
{
List<Guid?> inspectionReportItemIdsTemp = new List<Guid?>();
if (parameters.RolesForUser.Contains("Client"))
{
inspectionReportItemIdsTemp = (from inspectionReportItem in context.InspectionReportItems
join inspectionReport in context.InspectionReports on inspectionReportItem.InspectionReportId equals inspectionReport.InspectionReportId
join appItem in context.ApprovalItems on inspectionReportItem.InspectionReportItemId equals appItem.InspectionReportItemId
where inspectionReportItem.IsDeleted == false && inspectionReportItem.InspectionReportId == inspectionReportId
&& inspectionReport.ServiceOrderId == parameters.ServiceOrderId && inspectionReport.IsDeleted == false
&& appItem.IsDeleted == false
&& appItem.ApprovalStatus == (int)ApprovalStatus.Completed && appItem.CanPublish == true
orderby inspectionReportItem.CreationDate descending
select inspectionReportItem.InspectionReportItemId).AsEnumerable().Distinct().Cast<Guid?>().ToList();
}
else
{
inspectionReportItemIdsTemp = (from inspectionReportItem in context.InspectionReportItems
join inspectionReport in context.InspectionReports on inspectionReportItem.InspectionReportId equals inspectionReport.InspectionReportId
join appItem in context.ApprovalItems on inspectionReportItem.InspectionReportItemId equals appItem.InspectionReportItemId
where inspectionReportItem.IsDeleted == false && inspectionReportItem.InspectionReportId == inspectionReportId
&& inspectionReport.ServiceOrderId == parameters.ServiceOrderId && inspectionReport.IsDeleted == false
&& appItem.IsDeleted == false && parameters.RolesForUser.Contains(appItem.RoleName)
&& (appItem.ApprovalStatus == (int)ApprovalStatus.Completed || appItem.ApprovalStatus == (int)ApprovalStatus.Ready)
orderby inspectionReportItem.CreationDate descending
select inspectionReportItem.InspectionReportItemId).AsEnumerable().Cast<Guid?>().ToList();
}
inspectionReportItemIds = inspectionReportItemIdsTemp;
result.TotalCount = inspectionReportItemIdsTemp.Count;
if (!parameters.IsExport)
inspectionReportItemIdsTemp = inspectionReportItemIdsTemp.Skip(currentIndex).Take(parameters.PageSize).ToList();
result.Collection = (from formValue in context.FormValues
join inspectionReportItem in context.InspectionReportItems on formValue.InspectionReportItemId equals inspectionReportItem.InspectionReportItemId
where inspectionReportItemIdsTemp.Contains(formValue.InspectionReportItemId) && formValue.IsDeleted == false
orderby inspectionReportItem.CreationDate descending
select formValue).ToList();
}
/// <summary>
/// Get the query of inspection reports
/// </summary>
/// <param name="context">Database context</param>
/// <param name="inspectionReportId">Id of inspectionReport</param>
/// <param name="parameters">Parametes</param>
/// <param name="inspectionReportItemIds">List of inspection report id</param>
/// <returns>List of FormValue</returns>
private static PaginatedList<FormValue> GetInspectionReportQuery(VestalisEntities context, Guid inspectionReportId, ParameterSearchInspectionReport parameters, ref List<Guid?> inspectionReportItemIds)
{
PaginatedList<FormValue> result = new PaginatedList<FormValue>();
int currentIndex = (parameters.SelectedPage - 1) * parameters.PageSize;
List<Guid?> inspectionReportItemIdsTemp = new List<Guid?>();
if (parameters.Collection.ToFilledDictionary().Count > 0)
{
//get the data from database
inspectionReportItemIdsTemp = GetInspectionReportsFiltered(context, inspectionReportId, parameters);
inspectionReportItemIds = inspectionReportItemIdsTemp;
result.TotalCount = inspectionReportItemIdsTemp.Count;
if (!parameters.IsExport)
inspectionReportItemIdsTemp = inspectionReportItemIdsTemp.Skip(currentIndex).Take(parameters.PageSize).ToList();
//get the data from database
result.Collection = (from formValue in context.FormValues
join inspectionReportItem in context.InspectionReportItems on formValue.InspectionReportItemId equals inspectionReportItem.InspectionReportItemId
where inspectionReportItemIdsTemp.Contains(formValue.InspectionReportItemId) && formValue.IsDeleted == false
orderby inspectionReportItem.CreationDate descending
select formValue).ToList();
}
else
{
//get the data from database
GetInspectionReportQueryParameters(parameters, context, inspectionReportId, result, currentIndex, ref inspectionReportItemIds);
}
result.NumberOfPages = (int)Math.Ceiling((double)result.TotalCount / (double)parameters.PageSize);
return result;
}
