public void GenerateReportDinamicallyTest()
{
bool isClient = true;
Guid businessApplicationId = new Guid("BFE0C1B1-B4B7-4342-BEE7-ED6CC097B6F5");
string userName = "******";
ParameterSearchServicerOrder parameters = new ParameterSearchServicerOrder
{
FormCollection = new System.Web.Mvc.FormCollection(),
BusinessApplicationId = businessApplicationId,
RolesForUser = Roles.GetRolesForUser(userName).ToList(),
Page = 0,
PageSize = 0,
IsExport = true,
IsClient = isClient
};
DynamicDataGrid def = ServiceOrderBusiness.GetServiceOrderGridDefinition(businessApplicationId, isClient);
var model = ServiceOrderBusiness.SearchOrderList(parameters);
model.Captions = def.Captions;
model.BusinessApplicationName = def.BusinessApplicationName;
model.FormName = def.FormName;
string path = @"D:\NewTfs\Vestalis3TPMain\Cotecna.Vestalis.Web\Cotecna.Vestalis.Web\Templates\demo.xlsx";
if (File.Exists(path))
File.Delete(path);
MemoryStream result = ExcelBusiness.GenerateReportDinamically(model, "");
result.Position = 0;
File.WriteAllBytes(path, result.ToArray());
}
/// <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>
/// This method get dinamically the result according of the filters
/// </summary>
/// <param name="context">Database context</param>
/// <param name="parameters">Parameters</param>
/// <returns></returns>
private static List<Guid?> GetServiceOrdersFiltered(VestalisEntities context, ParameterSearchServicerOrder parameters)
{
List<Guid?> result = new List<Guid?>();
//filter FormCollection object to get a dictionary only with the key that have a value
Dictionary<string, string> formCollectionFiltered = parameters.FormCollection.ToFilledDictionary();
//get the types of the filters
var queryServiceOrder = context.FormValues.Where(data => formCollectionFiltered.Keys.Contains(data.FieldName))
.Select(data => new { FieldName = data.FieldName, TypeField = data.FieldType }).Distinct();
//this query filter the result by business application
string query1 = "select VALUE ServiceOrder.ServiceOrderId from VestalisEntities.ServiceOrders as ServiceOrder where ServiceOrder.IsDeleted = false 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.FielsdWithLike.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.FielsdWithLike.Contains(fieldName));
break;
case (int)FieldType.RegularExpressionText:
tempQuery = GetRegularExpressionQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.Contains(fieldName));
break;
case (int)FieldType.SingleTextLine:
tempQuery = GetSingleTextQuery(context, formCollectionFiltered, fieldName, tempQuery, query1Result, parameters.FielsdWithLike.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.Any(keyPair => keyPair.Key.EndsWith("from")) && formCollectionFiltered.Any(keyPair => keyPair.Key.EndsWith("to")))
{
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 list of service orders
/// </summary>
/// <param name="context">Vestalis context</param>
/// <param name="parameters">Parameters</param>
/// <returns>List of FormValue</returns>
private static PaginatedList<FormValue> GetServiceOrderQuery(VestalisEntities context, ParameterSearchServicerOrder parameters)
{
PaginatedList<FormValue> result = new PaginatedList<FormValue>();
int currentIndex = (parameters.Page - 1) * parameters.PageSize;
List<Guid?> serviceOrderIds = null;
if (parameters.FormCollection.ToFilledDictionary().Count > 0)
{
//get the data from database
serviceOrderIds = GetServiceOrdersFiltered(context, parameters);
result.TotalCount = serviceOrderIds.Count;
if (!parameters.IsExport)
serviceOrderIds = serviceOrderIds.Skip(currentIndex).Take(parameters.PageSize).ToList();
//get the data from database
result.Collection = (from formValue in context.FormValues
join serviceOrders in context.ServiceOrders on formValue.ServiceOrderId equals serviceOrders.ServiceOrderId
where serviceOrderIds.Contains(formValue.ServiceOrderId) && formValue.IsDeleted == false
orderby serviceOrders.CreationDate descending
select formValue).ToList();
}
else
{
//get the data from database
serviceOrderIds = (from serviceOrder in context.ServiceOrders
where serviceOrder.BusinessApplicationId == parameters.BusinessApplicationId
&& serviceOrder.IsDeleted == false
orderby serviceOrder.CreationDate descending
select serviceOrder.ServiceOrderId).AsEnumerable().Cast<Guid?>().ToList();
result.TotalCount = serviceOrderIds.Count;
if (!parameters.IsExport)
serviceOrderIds = serviceOrderIds.Skip(currentIndex).Take(parameters.PageSize).ToList();
//get the data from database
result.Collection = (from formValue in context.FormValues
join serviceOrders in context.ServiceOrders on formValue.ServiceOrderId equals serviceOrders.ServiceOrderId
where serviceOrderIds.Contains(formValue.ServiceOrderId) && formValue.IsDeleted == false
orderby serviceOrders.CreationDate descending
select formValue).ToList();
}
result.NumberOfPages = (int)Math.Ceiling((double)result.TotalCount / (double)parameters.PageSize);
return result;
}
/// <summary>
/// Get the data needed for export to excel
/// </summary>
/// <param name="isClient">Is client</param>
/// <param name="collection">Form collection</param>
/// <param name="businessApplicationId">Id of business application</param>
/// <returns>DynamicDataGrid</returns>
private DynamicDataGrid GetModelExcelExport(bool isClient, FormCollection collection, Guid businessApplicationId)
{
if (isClient)
{
Guid? parameter = AuthorizationBusiness.GetClientIdByBusinnessApplication(businessApplicationId, UserName);
collection.Add("Client", parameter.GetValueOrDefault().ToString());
}
//get the data
ParameterSearchServicerOrder parameters = new ParameterSearchServicerOrder
{
FormCollection = collection,
BusinessApplicationId = businessApplicationId,
RolesForUser = Roles.GetRolesForUser(UserName).ToList(),
Page = 0,
PageSize = 0,
IsExport = true,
IsClient = isClient
};
return ServiceOrderBusiness.SearchOrderList(parameters);
}
public PartialViewResult SearchOrderPaginated(int? page)
{
Guid businessApplicationId = new Guid(Convert.ToString(Session["BusinessAplicationId"]));
int pageSize = Cotecna.Vestalis.Web.Properties.Settings.Default.PageSize;
int currentPage = page == null ? 1 : page.Value;
List<string> fielsdWithLike = new List<string>();
foreach (string name in _formCollection.AllKeys.Where(name => name.Contains("IsLike")))
{
fielsdWithLike.Add(name.Replace("IsLike", ""));
}
ParameterSearchServicerOrder parameters = new ParameterSearchServicerOrder
{
FormCollection = _formCollection,
BusinessApplicationId = businessApplicationId,
RolesForUser = Roles.GetRolesForUser(UserName).ToList(),
Page = currentPage,
PageSize = pageSize,
IsExport = false,
IsClient = User.IsInRole("Client"),
FielsdWithLike = fielsdWithLike
};
DynamicDataGrid model = ServiceOrderBusiness.SearchOrderList(parameters);
model.Captions = _captions;
return PartialView("_ServiceOrderGrid", model);
}
