/// <summary>
/// Get an instance of InspectionReportModel class
/// </summary>
public InspectionReportModel()
{
Links = new List<string>();
GridColumns = new DynamicDataGrid();
ServiceOrderHeader = new List<Field>();
FormDefinition = new Form();
OrderIdentifier = new Field();
PictureModel = new PictureReportModel();
}
/// <summary>
/// Get the list of service orders
/// </summary>
/// <param name="parameters">Parameters</param>
/// <returns>DynamicDataGrid</returns>
public static DynamicDataGrid SearchOrderList(ParameterSearchServicerOrder parameters)
{
DynamicDataGrid dynamicDataGrid = new DynamicDataGrid();
DynamicDataRow dataRow = null;
string fieldName = string.Empty;
string fieldValue = string.Empty;
int fieldType = 0;
List<PropertyInfo> filterProperties = null;
List<string> gridFields = new List<string>();
bool isOk = false;
Dictionary<string, DynamicDataRowValue> temRowValues = null;
List<FormValue> serviceOrderQuery = null;
PaginatedList<FormValue> resultQuery = null;
List<ApprovalItem> approvalItems = null;
//obtain the definition of the form
Form serviceOrder = CacheHandler.Get(String.Format("Form{0}", parameters.BusinessApplicationId),
() =>
DynamicFormEngine.GetFormDefinition(parameters.BusinessApplicationId,
FormType.ServiceOrder, false));
using (VestalisEntities context = new VestalisEntities())
{
//Get the values from data base
resultQuery = GetServiceOrderQuery(context, parameters);
serviceOrderQuery = resultQuery.Collection;
dynamicDataGrid.Page = parameters.Page;
dynamicDataGrid.NumberOfPages = resultQuery.NumberOfPages;
dynamicDataGrid.TotalNumberOfItemsWithoutPagination = resultQuery.TotalCount;
dynamicDataGrid.PageSize = parameters.PageSize;
//agroup the result by service order
var groupServices = (from item in serviceOrderQuery
group item by new { item.ServiceOrderId }
into rows
select new
{
serviceOrderId = rows.Key,
rows
});
//verify if the result have data in the query
if (serviceOrder != null && (serviceOrderQuery != null && serviceOrderQuery.Count > 0))
{
//get the fields that can be showed in the grid
foreach (var section in serviceOrder.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");
//verify the result of the query
foreach (var group in groupServices)
{
dataRow = new DynamicDataRow();
temRowValues = new Dictionary<string, DynamicDataRowValue>();
gridFields.ForEach(field =>
{
temRowValues.Add(field, new DynamicDataRowValue() { FieldType = 2});
});
foreach (var data in group.rows)
{
filterProperties =
data.GetType().GetProperties().Where(property => formValueProperties.Contains(property.Name)).ToList();
foreach (var property in filterProperties)
{
//obtain the values of the necesary properties for create the result
if (property.Name == "FieldName")
{
fieldName = data.GetPropertyValue<object>(property.Name).ToString();
}
else if (property.Name == "FieldType")
{
string tempValue = data.GetPropertyValue<object>(property.Name).ToString();
if (!string.IsNullOrEmpty(tempValue))
{
fieldType = int.Parse(tempValue);
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)
{
//if in the result the system founds a catalogue id,the system retrieves the value of catalogue
fieldValue = GetFinalFieldValue(temRowValues, fieldName, fieldType, fieldValue);
isOk = true;
}
if (isOk)
{
isOk = false;
dataRow.FieldValues.Clear();
temRowValues.Values.ToList().ForEach(value =>
{
dataRow.FieldValues.Add(new DynamicDataRowValue { FieldType = value.FieldType, FieldValue = value.FieldValue });
});
break;
}
}
fieldName = string.Empty;
fieldValue = string.Empty;
fieldType = 0;
}
//get the identifier of row
dataRow.RowIdentifier = group.serviceOrderId.ServiceOrderId;
approvalItems = GetApprovalItemsOfServiceOrder(dataRow.RowIdentifier.Value, parameters.RolesForUser, context);
dataRow.CanPublish = approvalItems.Any(data => data.CanPublish.GetValueOrDefault());
dataRow.CanValidate = approvalItems.Any(data => !data.CanPublish.GetValueOrDefault());
if (parameters.IsClient)
{
//Get the first report that has data published to the client
dataRow.FirstInspectionReportClient = (
from inspectionReport in context.InspectionReports
where
inspectionReport.ServiceOrderId ==
dataRow.RowIdentifier.Value
&& inspectionReport.IsDeleted == false &&
inspectionReport.IsClientVisible == true &&
inspectionReport.InspectionReportItems.Count(
item =>
item.IsDeleted == false &&
item.PublicationDate.HasValue) > 0
orderby inspectionReport.FormOrder
select
inspectionReport.FormName).FirstOrDefault();
//Get the number of pictures related to the service order
int countPictures =
context.Pictures.Count(
item =>
item.IsDeleted == false && item.ServiceOrderId == dataRow.RowIdentifier.Value);
//Get the number of documents related to the service order
int countDocuments =
context.Documents.Count(
item =>
item.IsDeleted == false && item.ServiceOrderId == dataRow.RowIdentifier.Value);
dataRow.HasPicturesClient = countPictures > 0
? true
: false;
dataRow.HasDocumentsClient = countDocuments > 0
? true
: false;
}
//add the row to the dinamic grid
dynamicDataGrid.DataRows.Add(dataRow);
}
}
}
return dynamicDataGrid;
}
/// <summary>
/// Get the definition to create the search service order screen
/// </summary>
/// <param name="businessApplicationId">Business application identifier</param>
/// <param name="clientVisible">Is visible or not for the client</param>
/// <returns>Values to build dynamically the search service order screen</returns>
public static DynamicDataGrid GetServiceOrderGridDefinition(Guid businessApplicationId, bool clientVisible)
{
DynamicDataGrid dynamicGrid = new DynamicDataGrid();
//Get the xml form definition of the service order given for the business application
Form serviceOrder = CacheHandler.Get(String.Format("Form{0}", businessApplicationId),
() =>
DynamicFormEngine.GetFormDefinition(businessApplicationId,
FormType.ServiceOrder, clientVisible));
//Get the fields definition for the business application
Fields fieldBusinessApplication = CacheHandler.Get(String.Format("Field{0}", businessApplicationId),
() =>
DynamicFormEngine.GetFields(businessApplicationId));
IList<Field> filters = new List<Field>();
if (serviceOrder != null)
{
//For each section
foreach (var section in serviceOrder.Sections)
//For each element of the section
foreach (var element in section.FormElements)
{
//Verify attributte "IsDataGridVisible" to set it as grid column
if (element.IsDataGridVisible && (!clientVisible || (clientVisible && element.IsVisibleClient)))
{
Field field = fieldBusinessApplication.Items.Single(x => x.FieldName == element.Identifier);
dynamicGrid.Captions.Add(new DynamicCaptionGrid
{
Caption = (string.IsNullOrEmpty(field.CaptionGrid) ? field.Caption : field.CaptionGrid),
FieldName = element.Identifier,
Width = string.IsNullOrEmpty(field.Width) ? 0: int.Parse(field.Width),
ExcelColumnWidth = string.IsNullOrEmpty(field.ExcelColumnWidth) ? 0: int.Parse(field.ExcelColumnWidth)
});
}
//Verify attributte "IsFilterVisible" to set it as search filter in the grid
if (element.IsFilterVisible && (!clientVisible || (clientVisible && element.IsVisibleClient)))
{
Field field = fieldBusinessApplication.Items.Single(x => x.FieldName == element.Identifier);
field.InitFieldType(businessApplicationId);
field.FieldValue = String.Empty;
filters.Add(field);
}
}
}
dynamicGrid.BusinessApplicationName = AuthorizationBusiness.GetBusinessApplicationById(businessApplicationId).BusinessApplicationName;
dynamicGrid.Filters = filters;
dynamicGrid.FormName = serviceOrder.Name;
dynamicGrid.CaptionBreadcrumbs = serviceOrder.CaptionBreadcrumbs;
dynamicGrid.CaptionTitle = serviceOrder.CaptionTitle;
return dynamicGrid;
}
/// <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>
/// Set the current status for each inspection report item
/// </summary>
/// <param name="dataGrid">DataGrid with the result of the search</param>
private static void SetStatusInspectionReport(DynamicDataGrid dataGrid)
{
//check all rows in the result
foreach (DynamicDataRow row in dataGrid.DataRows)
{
//take all fields with the type StatusField
foreach (DynamicDataRowValue value in row.FieldValues.Where(field => field.FieldType ==(int)FieldType.StatusField))
{
if (row.ApprovalStatus == (int)ApprovalStatus.Ready)
{
//set UnPublish or UnValidated status
if (row.CanPublish && !row.CanValidate)
{
value.FieldValue = LanguageResource.Unpublished;
}
else if (!row.CanPublish && row.CanValidate)
{
value.FieldValue = LanguageResource.Unvalidated;
}
}
else if (row.IsReadOnly && row.CanPublish)
{
//Set published status
value.FieldValue = LanguageResource.Published;
}
else if (row.ApprovalStatus == (int)ApprovalStatus.Completed && row.CurrentCompletedLevel >= 1 && !row.IsPublished)
{
//Set validated status
value.FieldValue = LanguageResource.Validated;
}
else if (row.ApprovalStatus == (int)ApprovalStatus.Completed && row.CurrentCompletedLevel > 1 && row.IsPublished)
{
//Set published status
value.FieldValue = LanguageResource.Published;
}
}
}
}
/// <summary>
/// Generate an excel report dinamically
/// </summary>
/// <param name="model">Data source</param>
public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
{
MemoryStream report = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = model.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", model.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", model.FormName, currentRowTitle,5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < model.Captions.Count; i++)
{
var caption = model.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var item in model.DataRows)
{
int numberOfColumnsData = item.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the data information
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = item.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return report;
}
