private async void btnPrikazi_Click(object sender, EventArgs e)
{
var search = new StudentSearchRequest()
{
//Ime = txtStudent.Text,
//Prezime = txtStudent.Text,
Indeks = txtStudent.Text
};
var result = await _apiService.Get <List <Models.StudentAdmin> >(search);
dgvStudent.AutoGenerateColumns = false;
dgvStudent.DataSource = result;
}
public IHttpActionResult Get([FromUri] StudentSearchRequest searchRequest)
{
if (searchRequest == null || !ModelState.IsValid)
{
return(BadRequest("Invalid Bad Request"));
}
var response = service.Search(searchRequest);
var toReturn = new StudentLV
{
data = response.Data.ToList().Select(x => x.CreateFrom()).ToList(),
recordsFiltered = response.FilteredCount,
recordsTotal = response.TotalCount
};
return(Ok(toReturn));
}
public async Task <IHttpActionResult> StudentList([FromBody] StudentSearchRequest request)
{
try
{
var list = await _service.SearchStudent(request);
await _service.CommitAsync();
return(Ok(new { List = list }));
}
catch (Exception ex)
{
await _service.RollbackAsync();
return(BadRequest(GetError(ex)));
}
}
public ActionResult Print()
{
var rawClasses = Request["Classes"];
var rawShicvas = Request["Shicvas"];
var rawLines = Request["Lines"];
var rawStations = Request["Stations"];
var req = new StudentSearchRequest
{
PageSize = Int32.MaxValue,
PageNumber = 1,
SortColumn = Request["SortColumn"],
SortOrder = Request["SortOrder"],
StudentId = Request["Id"].Trim(),
Name = Request["Name"].Trim(),
Class = rawClasses.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
Shicva = rawShicvas.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
LineIds = rawLines.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
StationIds = rawStations.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
City = Request["City"].Trim().ToLower(),
Street = Request["Street"].Trim().ToLower(),
House = Request["House"].Trim().ToLower(),
Active = int.Parse(Request["Active"]),
PayStatus = int.Parse(Request["PayStatus"]),
Subcidy = int.Parse(Request["Subcidy"]),
SibilingAtSchool = int.Parse(Request["Sibiling"]),
SpecialRequest = int.Parse(Request["Request"]),
DistanceFromSchoolFrom = int.Parse(Request["DFSFrom"]) * 1000,
DistanceFromSchoolTo = int.Parse(Request["DFSTo"]) * 1000,
DistanceFromStationFrom = int.Parse(Request["DFStFrom"]),
DistanceFromStationTo = int.Parse(Request["DFStTo"]),
Direction = int.Parse(Request["Direction"])
};
using (var logic = new tblStudentLogic())
{
var ttl = 0;
ViewBag.List = logic.GetStudentsForTable(req, out ttl);
}
return(View());
}
public async Task <IEnumerable <VStudent> > SearchStudent(StudentSearchRequest request)
{
var schoolYearIdParameter = new SqlParameter
{
ParameterName = "schoolYearId",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.BigInt,
Value = request.SchoolYearId
};
var classroomIdParameter = new SqlParameter
{
ParameterName = "classroomId",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.BigInt,
Value = request.ClassroomId
};
return(await _repository.ExecWithStoreProcedure("dbo.sp_SearchStudent @schoolYearId, @classroomId", schoolYearIdParameter, classroomIdParameter));
}
public SearchTemplateResponse <Student> Search(StudentSearchRequest searchRequest)
{
int fromRow = (searchRequest.PageNo - 1) * searchRequest.PageSize;
int toRow = searchRequest.PageSize;
bool searchNameSpecified = !string.IsNullOrEmpty(searchRequest.Name);
Expression <Func <Student, bool> > query =
s =>
(
(searchRequest.StudentId == 0 || searchRequest.StudentId.Equals(s.StudentId)) &&
(searchRequest.DateOfBirth == null ||
DbFunctions.TruncateTime(searchRequest.DateOfBirth.Value) ==
DbFunctions.TruncateTime(s.DateOfBirth)) &&
(searchNameSpecified &&
String.Concat(s.FirstName, " ", s.MiddleName, string.IsNullOrEmpty(s.MiddleName) ? "" : " ",
s.LastName).ToLower().Contains(searchRequest.Name.ToLower()) || !searchNameSpecified) &&
(!s.IsDeleted)
);
IEnumerable <Student> data = searchRequest.IsAsc
? DbSet
.Where(query)
.OrderBy(orderClause[searchRequest.OrderByColumn])
.Skip(fromRow)
.Take(toRow)
.ToList()
: DbSet
.Where(query)
.OrderByDescending(orderClause[searchRequest.OrderByColumn])
.Skip(fromRow)
.Take(toRow)
.ToList();
return(new SearchTemplateResponse <Student>
{
Data = data,
TotalCount = DbSet.Count(x => !x.IsDeleted),
FilteredCount = DbSet.Count(query)
});
}
public List <StudentAdmin> Get([FromQuery] StudentSearchRequest request)
{
var query = db.Student.AsQueryable();
if (!string.IsNullOrWhiteSpace(request?.Indeks))
{
query = query.Where(x => x.Ime.Contains(request.Indeks));
}
if (!string.IsNullOrWhiteSpace(request?.Indeks))
{
query = query.Where(x => x.Prezime.Contains(request.Indeks));
}
if (!string.IsNullOrWhiteSpace(request?.Indeks))
{
query = query.Where(x => x.Indeks.Contains(request.Indeks));
}
var lista = query.ToList();
List <Models.StudentAdmin> result = new List <Models.StudentAdmin>();
foreach (var x in lista)
{
result.Add(new Models.StudentAdmin
{
Ime = x.Ime,
Indeks = x.Indeks,
Prezime = x.Prezime,
StudentID = x.StudentId
});
}
return(result);
}
public List <StudentFullInfo> GetStudentsForTable(StudentSearchRequest request, out int total)
{
var res = new List <StudentFullInfo>();
using (var context = new BusProjectEntities())
{
context.Database.Log = Console.Write;
var cs = request.Class.Length;
var ss = request.Shicva.Length;
var ls = request.LineIds.Length;
var sts = request.StationIds.Length;
var lst = (from stud in context.tblStudents
join
stLine in context.StudentsToLines
on stud.pk equals stLine.StudentId into ps
from stLine in ps.DefaultIfEmpty()
join stat in context.Stations on stLine.StationId equals stat.Id into ps1
from stat in ps1.DefaultIfEmpty()
join line in context.Lines on stLine.LineId equals line.Id into ps2
from line in ps2.DefaultIfEmpty()
where (stLine.Date == null && stLine.mon != true && stLine.fri != true && stLine.sat != true && stLine.sun != true && stLine.thu != true && stLine.tue != true && stLine.wed != true) && //It is default line for student
((string.IsNullOrEmpty(request.StudentId)) || (stud.studentId.ToLower().Contains(request.StudentId.ToLower()))) &&
((ss == 0) || (request.Shicva.Contains(stud.Shicva))) &&
((cs == 0) || (request.Class.Contains(stud.@class))) &&
(ls == 0 || request.LineIds.Contains(stLine.LineId)) &&
(sts == 0 || request.StationIds.Contains(stLine.StationId)) &&
(string.IsNullOrEmpty(request.Name) || (stud.lastName + ", " + stud.firstName).ToLower().Contains(request.Name.ToLower())) &&
(string.IsNullOrEmpty(request.Address) || (stud.city + " " + stud.street + " " + stud.houseNumber).ToLower().Contains(request.Address.ToLower())) &&
(string.IsNullOrEmpty(request.City) || stud.city.ToLower().Contains(request.City)) &&
(string.IsNullOrEmpty(request.Street) || stud.street.ToLower().Contains(request.Street)) &&
(string.IsNullOrEmpty(request.House) || stud.houseNumber.ToString().Contains(request.House)) &&
(request.Active == 0 || (request.Active == 1 && stud.Active == true) || (request.Active == 2 && stud.Active == false)) &&
(request.registrationStatus == 0 || (request.registrationStatus == 1 && stud.registrationStatus == true) || (request.registrationStatus == 2 && stud.registrationStatus == false)) &&
(request.PayStatus == 0 || (request.PayStatus == 1 && stud.paymentStatus == true) || (request.PayStatus == 2 && stud.paymentStatus == false)) &&
(request.Subcidy == 0 || (request.Subcidy == 1 && stud.subsidy == true) || (request.Subcidy == 2 && stud.subsidy == false)) &&
(request.SibilingAtSchool == 0 || (request.SibilingAtSchool == 1 && stud.siblingAtSchool == true) || (request.SibilingAtSchool == 2 && stud.siblingAtSchool == false)) &&
(request.SpecialRequest == 0 || (request.SpecialRequest == 1 && stud.specialRequest == true) || (request.SpecialRequest == 2 && stud.specialRequest == false)) &&
(request.DistanceFromSchoolFrom == 0 || (stud.distanceFromSchool != null && stud.distanceFromSchool.Value >= request.DistanceFromSchoolFrom)) &&
(request.DistanceFromSchoolTo == 0 || (stud.distanceFromSchool != null && stud.distanceFromSchool.Value <= request.DistanceFromSchoolTo)) &&
(request.DistanceFromStationFrom == 0 || (stLine.distanceFromStation != null && stLine.distanceFromStation.Value >= request.DistanceFromStationFrom)) &&
(request.DistanceFromStationTo == 0 || (stLine.distanceFromStation != null && stLine.distanceFromStation.Value <= request.DistanceFromStationTo)) &&
(request.Direction == 0 || (request.Direction == 1 && stLine.Direction == 1) || (request.Direction == 2 && stLine.Direction == 0))
select new StudentFullInfo()
{
FirstName = stud.firstName,
LastName = stud.lastName,
Address = stud.city + " " + stud.street + " " + stud.houseNumber,
Active = stud.Active,
Class = stud.@class,
Id = stud.pk,
StudentId = stud.studentId,
registrationStatus = stud.registrationStatus,
PayStatus = stud.paymentStatus,
Shicva = stud.Shicva,
SibilingAtSchool = stud.siblingAtSchool,
SpecialRequest = stud.specialRequest ?? false,
DistanceToSchool = stud.distanceFromSchool.HasValue ? (int)stud.distanceFromSchool : 0,
LineName = stat != null ? stat.StationName + (line != null ? " (line " + line.LineNumber + ")" : "") : "--"
}).Distinct();
total = lst.Count();
if (string.IsNullOrEmpty(request.SortColumn))
{
request.SortColumn = "studentid";
}
if (string.IsNullOrEmpty(request.SortOrder))
{
request.SortOrder = "asc";
}
var skeep = (request.PageNumber - 1) * request.PageSize;
var take = request.PageSize;
switch (request.SortColumn.ToLower())
{
case "studentid":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.StudentId).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.StudentId).Skip(skeep).Take(take).ToList();
break;
case "name":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.FirstName).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.FirstName).Skip(skeep).Take(take).ToList();
break;
case "firstname":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.FirstName).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.FirstName).Skip(skeep).Take(take).ToList();
break;
case "lastname":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.LastName).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.LastName).Skip(skeep).Take(take).ToList();
break;
case "addr":
case "address":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.Address).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.Address).Skip(skeep).Take(take).ToList();
break;
case "shicva":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.Shicva).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.Shicva).Skip(skeep).Take(take).ToList();
break;
case "class":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.Class).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.Class).Skip(skeep).Take(take).ToList();
break;
case "registrationstatus":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.registrationStatus).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.registrationStatus).Skip(skeep).Take(take).ToList();
break;
case "payment":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.PayStatus).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.PayStatus).Skip(skeep).Take(take).ToList();
break;
case "active":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.Active).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.Active).Skip(skeep).Take(take).ToList();
break;
case "sibilingatschool":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.SibilingAtSchool).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.SibilingAtSchool).Skip(skeep).Take(take).ToList();
break;
case "specialrequest":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.SpecialRequest).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.SpecialRequest).Skip(skeep).Take(take).ToList();
break;
case "distance":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.DistanceToSchool).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.DistanceToSchool).Skip(skeep).Take(take).ToList();
break;
case "line":
res = request.SortOrder == "asc"
? lst.OrderBy(z => z.LineName).Skip(skeep).Take(take).ToList()
: lst.OrderByDescending(z => z.LineName).Skip(skeep).Take(take).ToList();
break;
}
}
return(res);
}
public SearchTemplateResponse <Student> Search(StudentSearchRequest searchRequest)
{
return(repository.Search(searchRequest));
}
public JsonResult StudentsForTable(GridSettings grid)
{
var rawClasses = Request["Classes"];
var rawShicvas = Request["Shicvas"];
var rawLines = Request["Lines"];
var rawStations = Request["Stations"];
var req = new StudentSearchRequest
{
SortColumn = grid.SortColumn,
SortOrder = grid.SortOrder,
PageNumber = grid.PageIndex,
PageSize = grid.PageSize,
StudentId = Request["Id"].Trim(),
Name = Request["Name"].Trim(),
Class = rawClasses.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
Shicva = rawShicvas.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
LineIds = rawLines.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
StationIds = rawStations.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
City = Request["City"].Trim().ToLower(),
Street = Request["Street"].Trim().ToLower(),
House = Request["House"].Trim().ToLower(),
Active = int.Parse(Request["Active"]),
registrationStatus = int.Parse(Request["registrationStatus"]),
PayStatus = int.Parse(Request["PayStatus"]),
Subcidy = int.Parse(Request["Subcidy"]),
SibilingAtSchool = int.Parse(Request["Sibiling"]),
SpecialRequest = int.Parse(Request["Request"]),
DistanceFromSchoolFrom = int.Parse(Request["DFSFrom"]) * 1000,
DistanceFromSchoolTo = int.Parse(Request["DFSTo"]) * 1000,
DistanceFromStationFrom = int.Parse(Request["DFStFrom"]),
DistanceFromStationTo = int.Parse(Request["DFStTo"]),
Direction = int.Parse(Request["Direction"])
};
var total = 0;
var logic = new tblStudentLogic();
var lst = logic.GetStudentsForTable(req, out total);
var jsonData = new
{
total = (int)Math.Ceiling((double)total / grid.PageSize),
page = grid.PageIndex,
records = total,
rows = (from stud in lst
select
new
{
id = stud.Id,
cell = new string[] {
stud.StudentId,
stud.FirstName,
stud.LastName,
stud.Class,
stud.Shicva,
stud.Address,
stud.registrationStatus.HasValue?stud.registrationStatus.ToString():"null",
stud.PayStatus.HasValue?stud.PayStatus.ToString():"null",
stud.Active.HasValue?stud.Active.ToString():"null",
stud.SibilingAtSchool.HasValue?stud.SibilingAtSchool.ToString():"null",
stud.SpecialRequest.ToString(),
stud.DistanceToSchool.ToString(),
stud.LineName
}
})
};
var r = new JsonResult {
Data = jsonData
};
return(r);
}
public FileContentResult GetExcel()
{
Response.Cookies.Add(new HttpCookie("fileDownload", "true"));
var rawClasses = Request["Classes"];
var rawShicvas = Request["Shicvas"];
var rawLines = Request["Lines"];
var rawStations = Request["Stations"];
var req = new StudentSearchRequest
{
PageSize = Int32.MaxValue,
PageNumber = 1,
SortColumn = Request["SortColumn"],
SortOrder = Request["SortOrder"],
StudentId = Request["Id"].Trim(),
Name = Request["Name"].Trim(),
Class = rawClasses.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
Shicva = rawShicvas.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries),
LineIds = rawLines.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
StationIds = rawStations.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray(),
City = Request["City"].Trim().ToLower(),
Street = Request["Street"].Trim().ToLower(),
House = Request["House"].Trim().ToLower(),
Active = int.Parse(Request["Active"]),
PayStatus = int.Parse(Request["PayStatus"]),
Subcidy = int.Parse(Request["Subcidy"]),
SibilingAtSchool = int.Parse(Request["Sibiling"]),
SpecialRequest = int.Parse(Request["Request"]),
DistanceFromSchoolFrom = int.Parse(Request["DFSFrom"]) * 1000,
DistanceFromSchoolTo = int.Parse(Request["DFSTo"]) * 1000,
DistanceFromStationFrom = int.Parse(Request["DFStFrom"]),
DistanceFromStationTo = int.Parse(Request["DFStTo"]),
Direction = int.Parse(Request["Direction"])
};
int ttl = 0;
List <StudentFullInfo> lst = null;
using (var logic = new tblStudentLogic())
{
lst = logic.GetStudentsForTable(req, out ttl);
}
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Students");
worksheet.Cell("A1").Value = "Id";
worksheet.Cell("B1").Value = "First Name";
worksheet.Cell("C1").Value = "Last Name";
worksheet.Cell("D1").Value = "Class";
worksheet.Cell("E1").Value = "Shicva";
worksheet.Cell("F1").Value = "Address";
worksheet.Cell("G1").Value = "Payment";
worksheet.Cell("H1").Value = "Active";
worksheet.Cell("I1").Value = "Sibiling";
worksheet.Cell("J1").Value = "Special request";
worksheet.Cell("K1").Value = "Distance to School";
worksheet.Cell("L1").Value = "Line & station";
worksheet.Cell("M1").Value = "Email";
worksheet.Cell("N1").Value = "Phone";
worksheet.Cell("O1").Value = "Subsidy";
worksheet.Cell("P1").Value = "Year Registration";
worksheet.Cell("Q1").Value = "School";
worksheet.Cell("A1").Style.Font.Bold = true;
worksheet.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("A").Width = 6;
worksheet.Cell("B1").Style.Font.Bold = true;
worksheet.Cell("B1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("B").Width = 12;
worksheet.Cell("C1").Style.Font.Bold = true;
worksheet.Cell("C1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("C").Width = 12;
worksheet.Cell("D1").Style.Font.Bold = true;
worksheet.Cell("D1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("D").Width = 8;
worksheet.Cell("E1").Style.Font.Bold = true;
worksheet.Cell("E1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("E").Width = 8;
worksheet.Cell("F1").Style.Font.Bold = true;
worksheet.Cell("F1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("F").Width = 20;
worksheet.Cell("G1").Style.Font.Bold = true;
worksheet.Cell("G1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("G").Width = 8;
worksheet.Cell("H1").Style.Font.Bold = true;
worksheet.Cell("H1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("H").Width = 8;
worksheet.Cell("I1").Style.Font.Bold = true;
worksheet.Cell("I1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("I").Width = 8;
worksheet.Cell("J1").Style.Font.Bold = true;
worksheet.Cell("J1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("J").Width = 14;
worksheet.Cell("K1").Style.Font.Bold = true;
worksheet.Cell("K1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("K").Width = 16;
worksheet.Cell("L1").Style.Font.Bold = true;
worksheet.Cell("L1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Column("L").Width = 20;
var row = 2;
foreach (var stud in lst)
{
worksheet.Cell(row, "A").Value = stud.StudentId;
worksheet.Cell(row, "B").Value = stud.FirstName;
worksheet.Cell(row, "C").Value = stud.LastName;
worksheet.Cell(row, "D").Value = stud.Class;
worksheet.Cell(row, "E").Value = stud.Shicva;
worksheet.Cell(row, "F").Value = stud.Address;
worksheet.Cell(row, "G").Value = stud.PayStatus == true ? "Yes" : "No";
worksheet.Cell(row, "H").Value = stud.Active == true ? "Yes" : "No";
worksheet.Cell(row, "I").Value = stud.SibilingAtSchool == true ? "Yes" : "No";
worksheet.Cell(row, "J").Value = stud.SpecialRequest == true ? "Yes" : "No";
worksheet.Cell(row, "K").Value = stud.DistanceToSchool + " m.";
worksheet.Cell(row, "L").Value = stud.LineName;
row++;
}
var ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
var sr = new BinaryReader(ms);
return(File(sr.ReadBytes((int)ms.Length), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Students.xlsx"));;
}
public async Task <IEnumerable <VStudent> > SearchStudent(StudentSearchRequest request)
{
return(await _vService.SearchStudent(request));
}
public List <Models.StudentAdmin> Get([FromQuery] StudentSearchRequest request)
{
return(_service.Get(request));
}
