public IActionResult Post([FromBody] ComputerEmployee computerEmployee)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
_context.ComputerEmployee.Add(computerEmployee);
try
{
_context.SaveChanges();
}
catch (DbUpdateException)
{
if (ComputerEmployeeExists(computerEmployee.ComputerEmployeeId))
{
return(new StatusCodeResult(StatusCodes.Status409Conflict));
}
else
{
throw;
}
}
return(CreatedAtRoute("GetSingleComputerEmployee", new { id = computerEmployee.ComputerEmployeeId }, computerEmployee));
}
public IActionResult Put(int id, [FromBody] ComputerEmployee computerEmployee)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
_context.ComputerEmployee.Update(computerEmployee);
try
{
_context.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!ComputerEmployeeExists(computerEmployee.ComputerEmployeeId))
{
return(NotFound());
}
else
{
throw;
}
}
return(CreatedAtRoute("GetSingleComputerEmployee", new { id = computerEmployee.ComputerEmployeeId }, computerEmployee));
}
public async Task <IActionResult> PutEmployeeComputer([FromRoute] int id, [FromBody] ComputerEmployee employeeComputer)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE ComputerEmployee
SET AssignDate = @assignDate,
EmployeeId = @employeeId,
ComputerId = @computerId
WHERE Id = @id";
cmd.Parameters.Add(new SqlParameter("@assignDate", employeeComputer.AssignDate));
cmd.Parameters.Add(new SqlParameter("@employeeId", employeeComputer.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@computerId", employeeComputer.ComputerId));
cmd.Parameters.Add(new SqlParameter("@id", id));
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(NoContent());
}
throw new Exception("No rows affected");
}
}
}
public IActionResult Delete(int id)
{
ComputerEmployee computerEmployee = _context.ComputerEmployee.Single(t => t.ComputerEmployeeId == id);
if (computerEmployee == null)
{
return(NotFound());
}
_context.ComputerEmployee.Remove(computerEmployee);
_context.SaveChanges();
return(Ok(computerEmployee));
}
public async Task <IActionResult> GetEmployeeComputerById([FromRoute] int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT ce.Id, ce.AssignDate, ce.UnassignDate, ce.EmployeeId, ce.ComputerId,
e.FirstName, e.LastName, e.DepartmentId, e.IsSupervisor,
c.PurchaseDate, c.DecomissionDate, c.Make, c.Manufacturer
FROM ComputerEmployee ce
LEFT JOIN Employee e ON ce.EmployeeId = e.Id
LEFT JOIN Computer c ON ce.ComputerId = c.Id
WHERE ce.Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", id));
SqlDataReader reader = cmd.ExecuteReader();
ComputerEmployee employeeComputer = null;
while (reader.Read())
{
employeeComputer = new ComputerEmployee
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
AssignDate = reader.GetDateTime(reader.GetOrdinal("AssignDate")),
UnassignDate = reader.IsDBNull(reader.GetOrdinal("UnassignDate")) ? (DateTime?)null : (DateTime?)reader.GetDateTime(reader.GetOrdinal("UnassignDate")),
EmployeeId = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
Employee = new Employee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
DepartmentId = reader.GetInt32(reader.GetOrdinal("DepartmentId")),
IsSupervisor = reader.GetBoolean(reader.GetOrdinal("IsSupervisor")),
},
ComputerId = reader.GetInt32(reader.GetOrdinal("ComputerId")),
Computer = new Computer
{
Id = reader.GetInt32(reader.GetOrdinal("ComputerId")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
DecomissionDate = reader.GetDateTime(reader.GetOrdinal("DecomissionDate")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Manufacturer = reader.GetString(reader.GetOrdinal("Manufacturer"))
}
};
}
reader.Close();
return(Ok(employeeComputer));
}
}
}
public async Task <IActionResult> PostEmployeeComputer([FromBody] ComputerEmployee employeeComputer)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO ComputerEmployee (AssignDate, EmployeeId, ComputerId)
OUTPUT INSERTED.Id
VALUES (@assignDate, @employeeId, @computerId)";
cmd.Parameters.Add(new SqlParameter("@assignDate", employeeComputer.AssignDate));
cmd.Parameters.Add(new SqlParameter("@employeeId", employeeComputer.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@computerId", employeeComputer.ComputerId));
int newId = (int)cmd.ExecuteScalar();
employeeComputer.Id = newId;
return(CreatedAtRoute("GetEmployeeComputerById", new { id = newId }, employeeComputer));
}
}
}
public IActionResult Get(int id)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
try
{
ComputerEmployee computerEmployee = _context.ComputerEmployee.Single(t => t.ComputerEmployeeId == id);
if (computerEmployee == null)
{
return(NotFound());
}
return(Ok(computerEmployee));
}
catch (System.InvalidOperationException ex)
{
return(NotFound());
}
}
private ComputerEmployee GetComputerEmployeeByEmployeeId(int id)
{
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT ce.Id, ce.ComputerId, ce.EmployeeId, ce.AssignDate
FROM ComputerEmployee ce
WHERE ce.EmployeeId = @id AND ce.UnassignDate IS NULL
";
cmd.Parameters.Add(new SqlParameter("@id", id));
var reader = cmd.ExecuteReader();
ComputerEmployee computerEmployee = new ComputerEmployee();
if (reader.Read())
{
if (!reader.IsDBNull(reader.GetOrdinal("EmployeeId")))
{
computerEmployee = new ComputerEmployee()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
EmployeeId = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
ComputerId = reader.GetInt32(reader.GetOrdinal("ComputerId")),
AssignDate = reader.GetDateTime(reader.GetOrdinal("AssignDate"))
};
}
}
reader.Close();
return(computerEmployee);
}
}
}
}
public static void Initialize(IServiceProvider serviceProvider)
{
using (var context = new BangazonContext(serviceProvider.GetRequiredService <DbContextOptions <BangazonContext> >()))
{
if (context.Customer.Any())
{
return;
}
//seeding CUSTOMERS
var customers = new Customer[]
{
new Customer {
FirstName = "Jelly",
LastName = "Otter",
Active = 0
},
new Customer {
FirstName = "Nigel",
LastName = "Thornberry",
Active = 1
},
new Customer {
FirstName = "Frank",
LastName = "Underwood",
Active = 1
},
};
foreach (Customer i in customers)
{
context.Customer.Add(i);
}
context.SaveChanges();
//seeding COMPUTERS
var computers = new Computer[]
{
new Computer {
},
new Computer {
},
new Computer {
}
};
foreach (Computer i in computers)
{
context.Computer.Add(i);
}
context.SaveChanges();
//seeding DEPARTMENTS
var departments = new Department[]
{
new Department {
Name = "Marketing",
Budget = 123456
},
new Department {
Name = "Accounting",
Budget = 234567
}
};
foreach (Department i in departments)
{
context.Department.Add(i);
}
context.SaveChanges();
//seeding EMPLOYEES
var employees = new Employee[]
{
new Employee {
Name = "Jon Snow"
},
new Employee {
Name = "Stephen Spielburg"
},
new Employee {
Name = "Megan Berry"
}
};
foreach (Employee i in employees)
{
context.Employee.Add(i);
}
context.SaveChanges();
//seeding PAYMENT TYPES
var paymentTypes = new PaymentType[]
{
new PaymentType {
Type = "Visa",
AccountNumber = "12345667890",
CustomerId = customers.Single(firstname => firstname.FirstName == "Jelly").CustomerId,
},
new PaymentType {
Type = "MasterCard",
AccountNumber = "2345678901",
CustomerId = customers.Single(firstname => firstname.FirstName == "Nigel").CustomerId,
},
new PaymentType {
Type = "AmericanExpress",
AccountNumber = "3456789012",
CustomerId = customers.Single(firstname => firstname.FirstName == "Frank").CustomerId,
}
};
foreach (PaymentType i in paymentTypes)
{
context.PaymentType.Add(i);
}
context.SaveChanges();
//seeding PRODUCT TYPES
var productTypes = new ProductType[]
{
new ProductType {
Type = "Household"
},
new ProductType {
Type = "Appliance"
},
new ProductType {
Type = "Automotive"
}
};
foreach (ProductType i in productTypes)
{
context.ProductType.Add(i);
}
context.SaveChanges();
//seeding PRODUCTS
var products = new Product[]
{
new Product {
ProductTypeId = productTypes.Single(s => s.Type == "Household").ProductTypeId,
SellerId = customers.Single(s => s.FirstName == "Jelly").CustomerId,
Name = "Lamp",
Description = "Lights stuff up",
Price = 13
},
new Product {
ProductTypeId = productTypes.Single(s => s.Type == "Appliance").ProductTypeId,
SellerId = customers.Single(s => s.FirstName == "Nigel").CustomerId,
Name = "Blender",
Description = "Mixes stuff",
Price = 13
},
new Product {
ProductTypeId = productTypes.Single(s => s.Type == "Automotive").ProductTypeId,
SellerId = customers.Single(s => s.FirstName == "Frank").CustomerId,
Name = "Crowbar",
Description = "Prys at stuff",
Price = 13
}
};
foreach (Product i in products)
{
context.Product.Add(i);
}
context.SaveChanges();
//seeding TRAINING PROGRAMS
var trainingPrograms = new TrainingProgram[]
{
new TrainingProgram {
Name = "Doing Your Best",
MaxAttendees = 12,
StartDate = new DateTime(2018, 8, 28, 2, 3, 0)
},
new TrainingProgram {
Name = "Excelling at Excel",
MaxAttendees = 21,
StartDate = new DateTime(2017, 8, 28, 2, 3, 0)
},
new TrainingProgram {
Name = "Bring Your Things",
MaxAttendees = 3,
StartDate = new DateTime(2016, 7, 28, 2, 3, 0)
},
new TrainingProgram {
Name = "Do Life Better",
MaxAttendees = 45,
StartDate = new DateTime(2015, 7, 28, 2, 3, 0)
}
};
foreach (TrainingProgram i in trainingPrograms)
{
context.TrainingProgram.Add(i);
}
context.SaveChanges();
//seeding ORDERS, scrambling order of customers so no initial sellerId will equal a buyerId
var orders = new Order[]
{
new Order {
CustomerId = customers.Single(f => f.FirstName == "Nigel").CustomerId,
PaymentTypeId = paymentTypes.Single(t => t.Type == "Visa").PaymentTypeId
},
new Order {
CustomerId = customers.Single(f => f.FirstName == "Frank").CustomerId,
PaymentTypeId = paymentTypes.Single(t => t.Type == "MasterCard").PaymentTypeId
},
new Order {
CustomerId = customers.Single(f => f.FirstName == "Jelly").CustomerId,
PaymentTypeId = paymentTypes.Single(t => t.Type == "AmericanExpress").PaymentTypeId
}
};
foreach (Order i in orders)
{
context.Order.Add(i);
}
context.SaveChanges();
//seeding ORDER-PRODUCTS, matching up with the initialized orders and products by id
var orderProducts = new OrderProduct[]
{
new OrderProduct {
OrderId = orders.Single(o => o.OrderId == 1).OrderId,
ProductId = products.Single(o => o.ProductId == 1).ProductId
},
new OrderProduct {
OrderId = orders.Single(o => o.OrderId == 2).OrderId,
ProductId = products.Single(o => o.ProductId == 2).ProductId
},
new OrderProduct {
OrderId = orders.Single(o => o.OrderId == 3).OrderId,
ProductId = products.Single(o => o.ProductId == 3).ProductId
}
};
foreach (OrderProduct i in orderProducts)
{
context.OrderProduct.Add(i);
}
context.SaveChanges();
//seeding COMPUTER-EMPLOYEES
var computerEmployees = new ComputerEmployee[]
{
new ComputerEmployee {
EmployeeId = employees.Single(e => e.Name == "Jon Snow").EmployeeId,
ComputerId = computers.Single(c => c.ComputerId == 1).ComputerId,
InDate = DateTime.Now
},
new ComputerEmployee {
EmployeeId = employees.Single(e => e.Name == "Stephen Spielburg").EmployeeId,
ComputerId = computers.Single(c => c.ComputerId == 2).ComputerId,
InDate = DateTime.Now
},
new ComputerEmployee {
EmployeeId = employees.Single(e => e.Name == "Megan Berry").EmployeeId,
ComputerId = computers.Single(c => c.ComputerId == 3).ComputerId,
InDate = DateTime.Now
}
};
foreach (ComputerEmployee i in computerEmployees)
{
context.ComputerEmployee.Add(i);
}
context.SaveChanges();
}
}
