public ActionResult QualificationCrew()
{
using (EmployeesContext db = new EmployeesContext())
{
var cq = (from c in db.allcrew
join p in db.personDetails on c.person equals p.id
join a in db.airType on c.forAircraftType equals a.id
select new
{
name = p.name,
aircrafts = a.model
}).ToList().GroupBy(user => user.name);
List <qualifyGrid> allData = new List <qualifyGrid>();
foreach (var group in cq)
{
string jData = "";
foreach (var entry in group)
{
jData += entry.aircrafts + " , ";
}
jData = jData.Substring(0, jData.Length - 3);
qualifyGrid q = new qualifyGrid {
name = group.Key,
aircrafts = jData
};
allData.Add(q);
}
return(View(allData.OrderBy(x => x.name).ToList()));
}
}
public ActionResult Index()
{
ViewBag.Message = "M";
using (EmployeesContext db = new EmployeesContext())
{
var cabincrew = from e in db.crew
join p in db.personDetails on e.cabinCrewId equals p.id
join f in db.flight on e.flightId equals f.id
join a in db.aircraftDetails on f.aircraft equals a.id
join at in db.airType on a.aircraftType equals at.id
join r in db.routeDetails on f.route equals r.id
join ap in db.airportDetails on r.fromAirport equals ap.id
join ap2 in db.airportDetails on r.toAirport equals ap2.id
select new crewGrid
{
name = p.name,
flightDay = f.flightDay,
aircraftModel = at.model,
fromAirport = ap.code,
toAirport = ap2.code,
startDate = e.startDate,
crewId = e.cabinCrewId,
flightId = f.id
};
return(View(cabincrew.ToList()));
}
}
public ActionResult AddorEdit(Employee emp)
{
try
{
if (emp.ImageUpload != null)
{
string fileName = Path.GetFileNameWithoutExtension(emp.ImageUpload.FileName);
string extension = Path.GetExtension(emp.ImageUpload.FileName);
fileName = fileName + DateTime.Now.ToString("yymmssfff") + extension;
emp.ImagePath = "~/AppFiles/Images/" + fileName;
emp.ImageUpload.SaveAs(Path.Combine(Server.MapPath("~/AppFiles/Images/"), fileName));
}
using (EmployeesContext db = new EmployeesContext())
{
if (emp.EmployeeID == 0)
{
db.Employees.Add(emp);
db.SaveChanges();
}
else
{
db.Entry(emp).State = EntityState.Modified;
db.SaveChanges();
}
}
return(Json(new { success = true, html = GlobalClass.RenderRazorViewToString(this, "ViewAll", GetAllEmployee()), message = "Submitted Successfully" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { success = true, message = ex.Message }, JsonRequestBehavior.AllowGet));
}
}
IEnumerable <Employee> GetAllEmployee()
{
using (EmployeesContext db = new EmployeesContext())
{
return(db.Employees.ToList <Employee>());
}
}
private List <flightslist> GetAllFlights()
{
using (EmployeesContext db = new EmployeesContext())
{
var dayIndex = new List <string> {
"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
};
var flightlist = (from fl in db.flight
join r in db.routeDetails on fl.route equals r.id
join a in db.airportDetails on r.fromAirport equals a.id
join a2 in db.airportDetails on r.toAirport equals a2.id
select new
{
idx = fl.id,
f = fl.flightDay,
a1 = a.code,
a2 = a2.code
}).AsEnumerable().Select(x => new flightslist()
{
id = x.idx,
day = x.f,
fromairport = x.a1,
flightmes = String.Format("{0} | {1} => {2}", x.f, x.a1, x.a2)
}).OrderBy(c => dayIndex.IndexOf(c.day)).ThenBy(c => c.fromairport);
return(flightlist.ToList());
}
}
internal void Run()
{
using (var db = new EmployeesContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
while (true)
{
var input = Console.ReadLine();
var commandTokens = input.Split(' ');
var commandName = commandTokens[0];
var commandArgs = commandTokens.Skip(1).ToArray();
var command = CommandParser.Parse(serviceProvider, commandName);
var result = command.Execute(commandArgs);
Console.WriteLine(result);
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, EmployeesContext context, ILoggerFactory loggerFactory)
{
if (env.IsDevelopment())
{
app.UseBrowserLink();
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
}
loggerFactory.AddFile(Path.Combine(Directory.GetCurrentDirectory(), "logger.txt"));
var logger = loggerFactory.CreateLogger("FileLogger");
app.Use(async(x, next) =>
{
// Do work that doesn't write to the Response.
await next.Invoke();
logger.LogInformation("[{0}] URL запроса: {1}, HTTP заголовки: {2}", DateTime.Now, x.Request.GetDisplayUrl(), JsonConvert.SerializeObject(x.Request.Headers.ToList()));
});
app.UseStaticFiles();
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Employees}/{action=Index}/{id?}");
});
(new DbInitializer(context)).CreateSuperChief();
}
private IQueryable <crewGrid> getStaffFlights()
{
using (EmployeesContext db = new EmployeesContext())
{
var cabincrew = from e in db.crew
join p in db.personDetails on e.cabinCrewId equals p.id
join f in db.flight on e.flightId equals f.id
join a in db.aircraftDetails on f.aircraft equals a.id
join at in db.airType on a.aircraftType equals at.id
join r in db.routeDetails on f.route equals r.id
join ap in db.airportDetails on r.fromAirport equals ap.id
join ap2 in db.airportDetails on r.toAirport equals ap2.id
select new crewGrid
{
name = p.name,
flightDay = f.flightDay,
aircraftModel = at.model,
fromAirport = ap.code,
toAirport = ap2.code,
startDate = e.startDate,
crewId = e.cabinCrewId,
flightId = f.id
};
//return View(cabincrew.ToList());
return(cabincrew);
}
}
private static Group GetEmployeeGroup(int groupID)
{
using (var db = new EmployeesContext())
{
return(db.Groups.Find(groupID));
}
}
public static void Main()
{
Mapper.Initialize(cfg =>
{
cfg.CreateMap <Employee, EmployeeDto>()
.ForMember(dto => dto.ManagerLastName,
opt => opt.MapFrom(src => src.Manager.LastName));
});
EmployeesContext context = new EmployeesContext();
context.Database.Initialize(true);
var employeeDtos = context.Employees
.Where(e => e.Birthday.Year < 1990)
.OrderByDescending(e => e.Salary)
.ProjectTo <EmployeeDto>()
.ToList();
foreach (var dto in employeeDtos)
{
string manager = dto.ManagerLastName == null ? "[no manager]" : dto.ManagerLastName;
Console.WriteLine($"{dto.FirstName} {dto.LastName} {dto.Salary:F2} - Manager: {manager}");
}
}
/*Original code (end) -----------------------------------------------------*/
private static void TestEmployees()
{
// Employee DB created? (Library example method)
using (var context = new EmployeesContext())
{
// Creates the database if not exists
var dbNotExisting = context.Database.EnsureCreated(); // dbNotExisting = true -> create DB, false -> does nothing
// Add some data if the DB does not exist
// (If you run the code below and the primary keys of these books are already declared in the DB it will throw an error)
Console.WriteLine($"EmployeeDB do not exist = {dbNotExisting}");
if (dbNotExisting)
{
// Adds some employees
context.Employees.Add(new Employee
{
Name = "Justin",
Surname = "Time",
Job = "Rellotger",
Salary = 1000
});
context.Employees.Add(new Employee
{
Name = "Elena",
Surname = "Nito Del Bosque",
Job = "Jardiner",
Salary = 5000
});
// Saves changes
context.SaveChanges();
}
}
}
private static Employee GetEmployee(int employeeID)
{
using (var db = new EmployeesContext())
{
return(db.Employees.Find(employeeID));
}
}
public static void Initialize(EmployeesContext context)
{
context.Database.EnsureCreated();
if (context.EmployeeList.Any())
{
return;
}
var employees = new Employee[]
{
new Employee {
Name = "lala", Surname = "blabla"
},
new Employee {
Name = "lala", Surname = "blabla"
},
new Employee {
Name = "lala", Surname = "blabla"
}
};
foreach (Employee employee in employees)
{
context.EmployeeList.Add(employee);
}
context.SaveChanges();
}
public ActionResult DeleteCrewFlight(int cId, int fId)
{
using (EmployeesContext db = new EmployeesContext())
{
Employee e = db.crew.Find(cId, fId);
if (e == null)
{
return(HttpNotFound()); // it really should be found, unless
}
// the user edited the URL string
var cabincrew = from ex in db.crew
join p in db.personDetails on cId equals p.id
join f in db.flight on fId equals f.id
join a in db.aircraftDetails on f.aircraft equals a.id
join at in db.airType on a.aircraftType equals at.id
join r in db.routeDetails on f.route equals r.id
join ap in db.airportDetails on r.fromAirport equals ap.id
join ap2 in db.airportDetails on r.toAirport equals ap2.id
select new crewGrid
{
name = p.name,
flightDay = f.flightDay,
aircraftModel = at.model,
fromAirport = ap.code,
toAirport = ap2.code,
startDate = e.startDate,
crewId = e.cabinCrewId,
flightId = f.id
};
crewGrid c = cabincrew.First();
return(View(c));
}
}
private static void InitializeDatabase()
{
using (var context = new EmployeesContext())
{
context.Database.Initialize(true);
}
}
private static void SeedDataBase(IEnumerable <Employee> employees)
{
using (EmployeesContext context = new EmployeesContext())
{
context.Employees.AddRange(employees);
context.SaveChanges();
}
}
private static void DbInitializer()
{
using (var db = new EmployeesContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
}
private static void ResetDatabase()
{
using (var db = new EmployeesContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
}
private static void SeedDatabase(IEnumerable <Employee> managers)
{
using (var context = new EmployeesContext())
{
context.Employees.AddRange(managers);
context.SaveChanges();
}
}
public ActionResult newCabinAssignment(Employee e)
{
if (ModelState.IsValid)
{
using (EmployeesContext db = new EmployeesContext()){
var isQualified = (
from cc in db.allcrew
from att in db.flight
where
cc.person == e.cabinCrewId &&
att.id == e.flightId &&
cc.forAircraftType == att.aircraft
select new {
cc.person
}).Count();
if (isQualified > 0)
{
var isDuplicate = (
from ee in db.crew
where
ee.cabinCrewId == e.cabinCrewId &&
ee.flightId == e.flightId
select new
{
ee.cabinCrewId
}).Count();
if (isDuplicate == 0)
{
db.crew.Add(e);
db.SaveChanges();
return(RedirectToAction("Index"));
}
else
{
ViewBag.Message = "Crew member already assigned to flight";
}
}
else
{
ViewBag.Message = "Crew member not qualified for the specified flight";
}
}
}
var crew = GetAllCrew();
ViewBag.list = new SelectList(crew.ToList(), "id", "name");
var flightlist = GetAllFlights();
ViewBag.flist = new SelectList(flightlist.ToList(), "id", "flightmes");
Employee ex = new Employee();
return(View(ex));
}
private static void EmployeesWithOneProjectLazy(EmployeesContext context)
{
var employees = context.Employees.Where(c => c.EmployeesProjects.Count == 1).Select(c => new { Name = c.FirstName + " " + c.LastName, DepartmentName = c.Department.Name }).ToList();
foreach (var e in employees)
{
var result = $"{e.Name} - {e.DepartmentName}";
}
}
public EmployRepositoryTest()
{
string mySqlEmployeesConnStr =
"0nmBbjjPY3PGbA6j+7Ul0Od1V+u8TMv8E1oQrIvrJTqG8JHkQaQ40CGThX5pKBsAVir1FefOpPPZpgsFZLA6eO8fRum5wnZkcxGWw9aq0ovHRM0OhKYf1GS0YK2slp1jMaKpA0HDylDsswiZ3CByr0cUGPwqSEn04hJAd3FXfbWPpGlUZ4zQz0MO4avuEA1Z";
connStrMySql = AesCryptoUtil.Decrypt(mySqlEmployeesConnStr);
dbOptionsbuilder = new DbContextOptionsBuilder <EmployeesContext>().UseMySQL(connStrMySql);
context = new EmployeesContext(dbOptionsbuilder.Options);
}
public EmployeeService(
EmployeesContext employeesContext,
IMapper mapper,
ILogger <EmployeeService> logger)
{
_employeesContext = employeesContext;
_mapper = mapper;
_logger = logger;
}
private static void OrderBy1(EmployeesContext context)
{
var employees = context.Employees.OrderBy(d => d.Department.Name).ThenBy(d => d.FirstName).ToList();
foreach (var emp in employees)
{
var result = $"{emp.FirstName} - {emp.Department.Name}";
}
}
private static void OptimizeQuery(EmployeesContext context)
{
var employees = context.Employees.Where(c => c.Subordinates.Any(s => s.Address.Town.Name.StartsWith("B"))).Select(c => c.FirstName).Distinct().ToList();
foreach (var emp in employees)
{
string result = $"{emp}";
}
}
private static void QueryWithLazyLoading(EmployeesContext context)
{
var employees = context.Employees.ToList();
foreach (var emp in employees)
{
string result = $"{emp.FirstName} - {emp.Department.Name} - {emp.Address.AddressText}";
}
}
private static void QueryWithEagerLoadingSelect(EmployeesContext context)
{
var employees = context.Employees.Include("Department").Include("Address").Select(c => new { FirstName = c.FirstName, DepartmentName = c.Department.Name, Adress = c.Address.AddressText }).ToList();
foreach (var emp in employees)
{
string result = $"{emp.FirstName} - {emp.DepartmentName} - {emp.Adress}";
}
}
private static void SalaryBelow3000Lazy(EmployeesContext context)
{
var employees = context.Employees.Where(c => c.Salary < 3000).Select(c => new { FirstName = c.FirstName, DepartmentName = c.Department.Name, Adress = c.Address.AddressText }).ToList();
foreach (var emp in employees)
{
string result = $"{emp.FirstName} - {emp.DepartmentName} - {emp.Adress}";
}
}
private EmployeesContext CreateInMemoryDbContext()
{
var builder = new DbContextOptionsBuilder <EmployeesContext>();
builder.UseInMemoryDatabase(databaseName: _inMemoryDbName);
var context = new EmployeesContext(builder.Options);
return(context);
}
public EmployeesController(EmployeesContext context)
{
if (context == null)
{
_context = new EmployeesContext(new DbContextOptions <EmployeesContext>());
}
else
{
_context = context;
}
}
