public ActionResult Index()
{
CertificateListModel model = new CertificateListModel();
//Get the entry points to be loaded in the screen
IList<EntryPoint> query = (from entryPoints in db.EntryPoints where entryPoints.IsDeleted == false select entryPoints).ToList();
model.EntryPoints = new SelectList(query, "EntryPointId", "Name");
//Search the certificates in the page 1
model.SearchCertificateList(1);
return View(model);
}
public void TestSearchCertificatesWithNoResults()
{
CertificateController controller = new CertificateController();
CertificateListModel model = new CertificateListModel();
model.CertificateNumber = "NUMBER0000001";
ActionResult result = controller.SearchCertificateGrid(model, 1);
Assert.IsNotNull(result);
Assert.IsNotNull(model);
Assert.AreEqual(model.Certificates.Page, 1);
Assert.AreEqual(model.Certificates.TotalCount, 0);
Assert.AreEqual(model.Certificates.NumberOfPages, 0);
Assert.AreEqual(model.Certificates.Collection.Count, 0);
}
public void TestSearchCertificatesSpecificPage()
{
CertificateController controller = new CertificateController();
CertificateListModel model = new CertificateListModel();
ActionResult result = controller.SearchCertificateGrid(model,2);
Assert.IsNotNull(result);
Assert.IsNotNull(model);
if (model.Certificates.TotalCount > model.Certificates.PageSize)
{
Assert.AreEqual(model.Certificates.Page, 2);
}
else
{
Assert.AreEqual(model.Certificates.Page, 1);
}
}
public void TestSearchCertificatesWithSpecificFilters()
{
CertificateController controller = new CertificateController();
CertificateListModel model = new CertificateListModel();
//Test with certificate number == 1
model.CertificateNumber = "1";
ActionResult result = controller.SearchCertificateGrid(model, 1);
if (model.Certificates.Collection.Count > 0)
{
int countResults = model.Certificates.Collection.Where(item1 => !item1.Certificate.Sequential.Contains("1")).ToList().Count;
Assert.AreEqual(countResults, 0);
}
//Test with certificate status different to 1,2,3
model = new CertificateListModel();
model.CertificateStatusSelected = "1,2,3";
result = controller.SearchCertificateGrid(model, 1);
if (model.Certificates.Collection.Count > 0)
{
int countResults = model.Certificates.Collection.Where(item1 => item1.Certificate.CertificateStatusId != CertificateStatusEnum.Cancelled && item1.Certificate.CertificateStatusId != CertificateStatusEnum.Conform && item1.Certificate.CertificateStatusId != CertificateStatusEnum.NonConform).ToList().Count;
Assert.AreEqual(countResults, 0);
}
//Test with entry point == 1
model = new CertificateListModel();
model.EntryPointSelected = 1;
result = controller.SearchCertificateGrid(model, 1);
if (model.Certificates.Collection.Count > 0)
{
int countResults = model.Certificates.Collection.Where(item1 => item1.Certificate.EntryPointId !=1).ToList().Count;
Assert.AreEqual(countResults, 0);
}
//Test with issuance date in a range
model = new CertificateListModel();
//model.IssuanceDateFrom = new DateTime(2012, 1, 1);
//model.IssuanceDateTo = new DateTime(2012, 1, 31);
result = controller.SearchCertificateGrid(model, 1);
if (model.Certificates.Collection.Count > 0)
{
int countResults = model.Certificates.Collection.Where(item1 => item1.Certificate.IssuanceDate < new DateTime(2012, 1, 1) || item1.Certificate.IssuanceDate > new DateTime(2012, 1, 31)).ToList().Count;
Assert.AreEqual(countResults, 0);
}
}
public void TestVerifySearchOrderedBySequentialNumber()
{
CertificateController controller = new CertificateController();
CertificateListModel model = new CertificateListModel();
ActionResult result = controller.SearchCertificateGrid(model, 1);
Assert.IsNotNull(result);
Assert.IsNotNull(model);
IList<int> sequentials = new List<int>();
int onlyNumber = 0;
int itemFound = 0;
foreach (var itemCollection in model.Certificates.Collection)
{
//Get the number from the string
onlyNumber = Convert.ToInt32(String.Concat(itemCollection.Certificate.Sequential.Where(Char.IsDigit)));
//Verify if there is a previous number less than the current one then ERROR
itemFound = sequentials.FirstOrDefault(item => item < onlyNumber);
Assert.AreEqual(itemFound, 0);
sequentials.Add(onlyNumber);
}
}
public void TestSearchDetailFirstCertificateReturnedInTheSearch()
{
CertificateController controller = new CertificateController();
CertificateListModel model = new CertificateListModel();
ActionResult result = controller.SearchCertificateGrid(model, 1);
var firstCertificate = model.Certificates.Collection.FirstOrDefault();
if (firstCertificate != null)
{
ActionResult resultDetail = controller.ViewCertificateDetail(firstCertificate.Certificate.CertificateId);
CertificateDetailModel modelDetail = (CertificateDetailModel)((System.Web.Mvc.ViewResultBase)(resultDetail)).Model;
//Compare the certificate number displayed in the grid with the one obtained when the user access to the detail screen of the certificate
Assert.AreEqual(firstCertificate.Certificate.Sequential, modelDetail.Certificate.Sequential);
//Compare the certificate status displayed in the grid with the one obtained when the user access to the detail screen of the certificate
Assert.AreEqual(firstCertificate.Certificate.CertificateStatusId, modelDetail.Certificate.CertificateStatusId);
//Compare the entry point displayed in the grid with the one obtained when the user access to the detail screen of the certificate
Assert.AreEqual(firstCertificate.Certificate.EntryPointId, modelDetail.Certificate.EntryPointId);
if (modelDetail.Documents.TotalCount > 0)
{
Assert.AreEqual(modelDetail.Documents.Page, 1);
Assert.AreNotEqual(modelDetail.Documents.Collection.Count, 0);
}
}
}
/// <summary>
/// Generate an excel file with the information of certificates
/// </summary>
/// <param name="dataSource">The list of certificates</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, 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 = VocStyleSheet();
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;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; 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 < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
List<CertificateDocument> dataSource = model.Certificates.Collection;
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the title
UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);
//set min date and max date in header
Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
string minDate, maxDate;
//get dates
if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
{
minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
}
else
{
minDate = model.IssuanceDateFrom;
maxDate = model.IssuanceDateTo;
}
//write both dates
UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);
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 = Resources.Common.CertificateList, 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 ms;
}
public static IQueryable<CertificateDocument> CreateCertificateFilters(this IQueryable<CertificateDocument> query, CertificateListModel certificateListModel)
{
IQueryable<CertificateDocument> filters = query;
//Filter Certificate Number
if (!string.IsNullOrEmpty(certificateListModel.CertificateNumber))
filters = filters.Where(y => y.Certificate.Sequential.Contains(certificateListModel.CertificateNumber));
//Filter Certificate Issuance date from
if (!string.IsNullOrEmpty(certificateListModel.IssuanceDateFrom))
{
DateTime f2;
bool success = DateTime.TryParse(certificateListModel.IssuanceDateFrom, new System.Globalization.CultureInfo("fr-FR"), System.Globalization.DateTimeStyles.None, out f2);
if (success)
{
filters = filters.Where(z => z.Certificate.IssuanceDate >= f2);
}
}
//Filter Certificate Issuance date to
if (!string.IsNullOrEmpty(certificateListModel.IssuanceDateTo))
{
DateTime f2;
bool success = DateTime.TryParse(certificateListModel.IssuanceDateTo, new System.Globalization.CultureInfo("fr-FR"), System.Globalization.DateTimeStyles.None, out f2);
if (success)
{
f2 = f2.AddDays(1);
filters = filters.Where(z => z.Certificate.IssuanceDate < f2);
}
}
//Filter Certificate Status
if (!string.IsNullOrEmpty(certificateListModel.CertificateStatusSelected))
{
List<CertificateStatusEnum> statusx = new List<CertificateStatusEnum>();
certificateListModel.CertificateStatusSelected.Split(',').ToList().ForEach(x =>
{
statusx.Add((CertificateStatusEnum)Convert.ToInt32(x));
});
filters = filters.Where(y => statusx.Contains(y.Certificate.CertificateStatusId));
}
//Filter Entry Point
if (certificateListModel.EntryPointSelected.HasValue)
filters = filters.Where(y => y.Certificate.EntryPointId == certificateListModel.EntryPointSelected.Value);
return filters;
}
public PartialViewResult SearchCertificateGrid(CertificateListModel model, int? selectedPage)
{
model.SearchCertificateList(selectedPage.HasValue?selectedPage.Value:1);
return PartialView("_CertificateGrid", model);
}
public void SearchCertificateExport(CertificateListModel model, int? selectedPage)
{
model.SearchCertificateList(1, true);
MemoryStream report = ExcelManagement.GenerateCertificateReport(model, Server.MapPath("~/Images/Logo-Voc-Iraq.png"));
report.Position = 0;
string currentDateTime = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
MicrosoftExcelStreamResult result = new MicrosoftExcelStreamResult(report, "CertificateReport" + currentDateTime + ".xlsx");
Session.Add("CertificateReport", result);
}
