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