/// <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; }