public async Task <ObservableCollection <CompositBDO> > GetAllNBTScoresAsync(IntakeYearsBDO period)
{
var AllScores = new ObservableCollection <CompositBDO>();
using (var context = new NBT_ProductionEntities())
{
var scores = await context.Composits.Where(x => x.DOT >= period.yearStart && x.DOT <= period.yearEnd).Select(m => m).ToListAsync();
if (scores != null)
{
// isloaded = true;
foreach (var score in scores)
{
var NBTScore = new CompositBDO();
CompositDALToCompositBDO(NBTScore, score);
AllScores.Add(NBTScore);
}
}
else
{
string msg = "No such intake year " + period.Year;
log.Error(msg);
}
}
return(AllScores);
}
static void IntakeYearDalToIntakeYearBDO(IntakeYear intakeDAL, IntakeYearsBDO intakeBDO)
{
intakeBDO.Year = intakeDAL.Year;
intakeBDO.yearID = intakeDAL.yearID;
intakeBDO.yearStart = intakeDAL.yearStart;
intakeBDO.yearEnd = intakeDAL.yearEnd;
intakeBDO.DateModified = intakeDAL.DateModified;
}
public List <IntakeYearsBDO> GetAllIntakeYears()
{
var periods = new List <IntakeYearsBDO>();
using (var context = new NBT_ProductionEntities())
{
var period = context.IntakeYears.ToList();
foreach (var yr in period)
{
var yrBDO = new IntakeYearsBDO();
IntakeYearDalToIntakeYearBDO(yr, yrBDO);
periods.Add(yrBDO);
}
}
return(periods);
}
private async void GetComposite()
{
DataVisible = false;
IsLoaded = true;
DateTime myDate = Convert.ToDateTime("2016/04/16");
Composite = new ObservableCollection <CompositBDO>();
if (_selectedPeriod.Year == 2017)
{
var temp = await _cservice.GetAllNBTScoresAsync(_selectedPeriod);
var temp1 = temp.Where(x => (x.VenueCode != 40101) && (x.DOT != myDate)).Select(x => x).ToList();
Composite = new ObservableCollection <CompositBDO>(temp1);
}
else if (_selectedPeriod.Year == 2016)
{
Composite = await _cservice.GetAllNBTScoresAsync(_selectedPeriod);
var MyPer = new IntakeYearsBDO();
MyPer = _selectedPeriod;
MyPer.yearStart = myDate;
MyPer.yearEnd = myDate;
var temp = await _cservice.GetAllNBTScoresAsync(MyPer);
foreach (var item in temp)
{
Composite.Add(item);
}
}
else
{
Composite = await _cservice.GetAllNBTScoresAsync(_selectedPeriod);
}
DataVisible = true;
IsLoaded = false;
}
public Task <bool> SaveLargeExcelAsync(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
{
throw new NotImplementedException();
}
public async Task <bool> SaveLargeExcel2Async(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
{
AllScores = composite;
// string path = Path.GetDirectoryName(myfile);
bool isgen = false;
string n = " n = " + AllScores.Count() + ".xlsx";
string filename = intake.Year.ToString();
filename += " intake cycle Logistics Composite ";
// Get the date today
DateTime dt = new DateTime();
dt = DateTime.Now;
string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");
filename = filename + myDay + n;
filename = Path.Combine(folder, filename);
await LoadNBTVenuesAsync(); // loads all venues into memory
//generate a collection of Logistics objects
await CompositToLogisticsCompositeAsync();
composite.Clear();
AllScores.Clear();
// column heading for excel
string[] ColumnHeadings = new[]
{
"Test Session ID", "NBT Reference", "Surname", "First Name",
"Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
"Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
"Venue", "Gender", "AL Score",
"AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
"AQL TEST LANGUAGE", "MATHS TEST LANGUAGE", "Test Type", "Venue Province",
};
// Size of spreadsheet
var data = new[]
{
new {
SheetName = "Logistics Composite",
NumberCols = ColumnHeadings.Count(),
Numberrows = LComposite.Count(),
},
};
// generate the workbook and spreadsheets
WorkbookDfn wb = new WorkbookDfn
{
Worksheets = data
.Select(d => new WorksheetDfn
{
Name = d.SheetName,
ColumnHeadings = Enumerable.Range(0, d.NumberCols)
.Select(c => new CellDfn
{
Value = ColumnHeadings[c],
Bold = true,
}),
Rows = Enumerable.Range(0, d.Numberrows)
.Select(r => new RowDfn
{
Cells = Enumerable.Range(0, d.NumberCols)
.Select(c =>
new CellDfn
{
Value =
c == 0 ? LComposite.ElementAt(r).SessionId :
c == 1 ? LComposite.ElementAt(r).NBT :
c == 2 ? LComposite.ElementAt(r).Surname :
c == 3 ? LComposite.ElementAt(r).Name :
c == 4 ? LComposite.ElementAt(r).MiddleInitials :
c == 5 ? LComposite.ElementAt(r).SouthAfricanID :
c == 6 ? LComposite.ElementAt(r).Passport :
c == 7 ? LComposite.ElementAt(r).Birth :
c == 8 ? LComposite.ElementAt(r).WroteAL :
c == 9 ? LComposite.ElementAt(r).WroteQL :
c == 10 ? LComposite.ElementAt(r).WroteMat :
c == 11 ? LComposite.ElementAt(r).StudentNo :
c == 12 ? LComposite.ElementAt(r).Faculty :
c == 13 ? LComposite.ElementAt(r).Programme :
c == 14 ? LComposite.ElementAt(r).TestDate :
c == 15 ? LComposite.ElementAt(r).Venue :
c == 16 ? LComposite.ElementAt(r).Sex :
c == 17 ? LComposite.ElementAt(r).ALScore :
c == 18 ? LComposite.ElementAt(r).ALLevel :
c == 19 ? LComposite.ElementAt(r).QLScore :
c == 20 ? LComposite.ElementAt(r).QLLevel :
c == 21 ? LComposite.ElementAt(r).MatScore :
c == 22 ? LComposite.ElementAt(r).MatLevel :
c == 23 ? LComposite.ElementAt(r).AQLLanguage :
c == 24 ? LComposite.ElementAt(r).MatLanguage :
c == 25 ? LComposite.ElementAt(r).TestType :
c == 26 ? LComposite.ElementAt(r).VenueProvince : "",
HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
c == 8 ? HorizontalCellAlignment.Center :
c == 9 ? HorizontalCellAlignment.Center :
c == 10 ? HorizontalCellAlignment.Center :
c == 16 ? HorizontalCellAlignment.Center :
c == 17 ? HorizontalCellAlignment.Center :
c == 19 ? HorizontalCellAlignment.Center :
c == 21 ? HorizontalCellAlignment.Center :
c == 23 ? HorizontalCellAlignment.Center :
c == 24 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
// FormatCode = c == 5 ? "0000000000000" :"",
})
}),
})
};
// stream write to excel file
SpreadsheetWriter.Write(filename, wb);
// SpreadsheetWriter.Write()
isgen = true;
LComposite.Clear();
LComposite = null;
return(isgen);
}
// Open document for saving excel data
public async Task <bool> SaveLargeExcelAsync(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
{
AllScores = composite;
// string path = Path.GetDirectoryName(folder);
bool isgen = false;
string n = " n = " + AllScores.Count() + ".xlsx";
string filename = intake.Year.ToString();
filename += " intake cycle Composite ";
// Get the date today
DateTime dt = new DateTime();
dt = DateTime.Now;
string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");
filename = filename + myDay + n;
filename = Path.Combine(folder, filename);
// make a copy of the template
//File.Copy("intake cycle Composite.xlsx", filename, true);
//using (SpreadsheetDocument SpDoc = SpreadsheetDocument.Open(filename, true))
//{
await CompositToFullCompositeAsync();
composite.Clear();
AllScores.Clear();
// column heading for excel
string[] ColumnHeadings = new[] {
"Ref No", "Barcode", "Last Name", "First_Name", "INITIALS", "ID NUMBER",
"ID_Foreign", "Date of Birth", "ID Type", "Citizenship", "Classification",
"Gender 1", "Faculty 1", "DATE", "Test Centre Code", "Venue Name",
"Home Lang", "GR12 Language", "AQL LANG", "AQL CODE", "MAT LANG", "MAT CODE",
"Faculty 2", "Faculty 3", "Test Session ID", "NBT Reference", "Surname", "First Name",
"Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
"Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
"Venue", "Gender", "Street and Number", "Street Name", "Suburb", "City/Town", "Province/Region",
"Postal Code", "e-mail Address", "Landline Number", "Mobile Number", "AL Score",
"AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
"AQL TEST LANGUAGE", "MATHS TEST LANGUAGE",
};
// Size of spreadsheet
var data = new[]
{
new {
SheetName = "Composite",
NumberCols = ColumnHeadings.Count(),
Numberrows = FComposite.Count(),
},
};
// generate the workbook and spreadsheets
WorkbookDfn workbook = new WorkbookDfn
{
Worksheets = data
.Select(d => new WorksheetDfn
{
Name = d.SheetName,
ColumnHeadings = Enumerable.Range(0, d.NumberCols)
.Select(c => new CellDfn
{
Value = ColumnHeadings[c],
Bold = true,
}),
Rows = Enumerable.Range(0, d.Numberrows)
.Select(r => new RowDfn
{
Cells = Enumerable.Range(0, d.NumberCols)
.Select(c =>
new CellDfn
{
Value = c == 0 ? FComposite.ElementAt(r).RefNo :
c == 1 ? FComposite.ElementAt(r).Barcode :
c == 2 ? FComposite.ElementAt(r).LastName :
c == 3 ? FComposite.ElementAt(r).FirstName :
c == 4 ? FComposite.ElementAt(r).Initials :
c == 5 ? FComposite.ElementAt(r).SAID :
c == 6 ? FComposite.ElementAt(r).FID :
c == 7 ? FComposite.ElementAt(r).DOB :
c == 8 ? FComposite.ElementAt(r).IDType :
c == 9 ? FComposite.ElementAt(r).Citizenship :
c == 10 ? FComposite.ElementAt(r).Classification :
c == 11 ? FComposite.ElementAt(r).Gender :
c == 12 ? FComposite.ElementAt(r).Faculty1 :
c == 13 ? FComposite.ElementAt(r).DOT :
c == 14 ? FComposite.ElementAt(r).VenueCode :
c == 15 ? FComposite.ElementAt(r).VenueName :
c == 16 ? FComposite.ElementAt(r).HomeLanguage :
c == 17 ? FComposite.ElementAt(r).SchLanguage :
c == 18 ? FComposite.ElementAt(r).AQLLang :
c == 19 ? FComposite.ElementAt(r).AQLCode :
c == 20 ? FComposite.ElementAt(r).MATLang :
c == 21 ? FComposite.ElementAt(r).MATCode :
c == 22 ? FComposite.ElementAt(r).Faculty2 :
c == 23 ? FComposite.ElementAt(r).Faculty3 :
c == 24 ? FComposite.ElementAt(r).SessionId :
c == 25 ? FComposite.ElementAt(r).NBT :
c == 26 ? FComposite.ElementAt(r).Surname :
c == 27 ? FComposite.ElementAt(r).Name :
c == 28 ? FComposite.ElementAt(r).MiddleInitials :
c == 29 ? FComposite.ElementAt(r).SouthAfricanID :
c == 30 ? FComposite.ElementAt(r).Passport :
c == 31 ? FComposite.ElementAt(r).Birth :
c == 32 ? FComposite.ElementAt(r).WroteAL :
c == 33 ? FComposite.ElementAt(r).WroteQL :
c == 34 ? FComposite.ElementAt(r).WroteMat :
c == 35 ? FComposite.ElementAt(r).StudentNo :
c == 36 ? FComposite.ElementAt(r).Faculty :
c == 37 ? FComposite.ElementAt(r).Programme :
c == 38 ? FComposite.ElementAt(r).TestDate :
c == 39 ? FComposite.ElementAt(r).Venue :
c == 40 ? FComposite.ElementAt(r).Sex :
c == 41 ? FComposite.ElementAt(r).Street1 :
c == 42 ? FComposite.ElementAt(r).Street2 :
c == 43 ? FComposite.ElementAt(r).Suburb :
c == 44 ? FComposite.ElementAt(r).City :
c == 45 ? FComposite.ElementAt(r).Province :
c == 46 ? FComposite.ElementAt(r).Postal :
c == 47 ? FComposite.ElementAt(r).Email :
c == 48 ? FComposite.ElementAt(r).Landline :
c == 49 ? FComposite.ElementAt(r).Mobile :
c == 50 ? FComposite.ElementAt(r).ALScore :
c == 51 ? FComposite.ElementAt(r).ALLevel :
c == 52 ? FComposite.ElementAt(r).QLScore :
c == 53 ? FComposite.ElementAt(r).QLLevel :
c == 54 ? FComposite.ElementAt(r).MatScore :
c == 55 ? FComposite.ElementAt(r).MatLevel :
c == 56 ? FComposite.ElementAt(r).AQLLanguage :
c == 57 ? FComposite.ElementAt(r).MatLanguage : "",
CellDataType =
c == 7 ? CellDataType.String :
c == 2 ? CellDataType.String :
c == 3 ? CellDataType.String : CellDataType.String,
HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
c == 33 ? HorizontalCellAlignment.Center :
c == 34 ? HorizontalCellAlignment.Center :
c == 32 ? HorizontalCellAlignment.Center :
c == 40 ? HorizontalCellAlignment.Center :
c == 18 ? HorizontalCellAlignment.Center :
c == 20 ? HorizontalCellAlignment.Center :
c == 50 ? HorizontalCellAlignment.Center :
c == 52 ? HorizontalCellAlignment.Center :
c == 54 ? HorizontalCellAlignment.Center :
c == 56 ? HorizontalCellAlignment.Center :
c == 57 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
// FormatCode = c == 5 ? "0000000000000" :"",
})
}),
})
};
// stream write to excel file
SpreadsheetWriter.Write(filename, workbook);
// SpreadsheetWriter.Write()
isgen = true;
FComposite.Clear();
FComposite = null;
return(isgen);
}
