1 | using System.Collections.Immutable;
|
---|
2 | using System.Data;
|
---|
3 | using Dapper;
|
---|
4 | using Microsoft.AspNetCore.Mvc;
|
---|
5 | using Microsoft.EntityFrameworkCore;
|
---|
6 | using Npgsql;
|
---|
7 | using WineTrackerWebApi.Helpers;
|
---|
8 | using WineTrackerWebApi.Models.Employee;
|
---|
9 | using WineTrackerWebApi.Models.ErrorHandle;
|
---|
10 | using WineTrackerWebApi.Models.Vehicle;
|
---|
11 |
|
---|
12 | namespace WineTrackerWebApi.Controllers
|
---|
13 | {
|
---|
14 | [Route("api/[controller]")]
|
---|
15 | [ApiController]
|
---|
16 | public class EmployeeController : ControllerBase
|
---|
17 | {
|
---|
18 | private readonly string _connectionString;
|
---|
19 |
|
---|
20 | public EmployeeController(IConfiguration configuration)
|
---|
21 | {
|
---|
22 | _connectionString = configuration.GetConnectionString("DefaultConnection");
|
---|
23 | }
|
---|
24 |
|
---|
25 | // GET: api/Employee
|
---|
26 | [HttpGet]
|
---|
27 | public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
|
---|
28 | {
|
---|
29 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
30 | {
|
---|
31 | const string query = $@"
|
---|
32 | select
|
---|
33 | e.employee_id as EmployeeId,
|
---|
34 | e.warehouse_id as WarehouseId,
|
---|
35 | w.warehouse_name as WarehouseName,
|
---|
36 | e.employee_name as EmployeeName,
|
---|
37 | e.employee_surname as EmployeeSurname
|
---|
38 | from employee e
|
---|
39 | join warehouse w on w.warehouse_id = e.warehouse_id";
|
---|
40 | var employees = await db.QueryAsync<Employee>(query);
|
---|
41 | return Ok(employees);
|
---|
42 | }
|
---|
43 | }
|
---|
44 |
|
---|
45 | // GET: api/Employee/5
|
---|
46 | [HttpGet("{id}")]
|
---|
47 | public async Task<ActionResult<Employee>> GetEmployee(int id)
|
---|
48 | {
|
---|
49 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
50 | {
|
---|
51 | var parameters = new { id = id };
|
---|
52 |
|
---|
53 | string query = $@"
|
---|
54 | select
|
---|
55 | e.employee_id as EmployeeId,
|
---|
56 | e.warehouse_id as WarehouseId,
|
---|
57 | w.warehouse_name as WarehouseName,
|
---|
58 | e.employee_name as EmployeeName,
|
---|
59 | e.employee_surname as EmployeeSurname
|
---|
60 | from employee e
|
---|
61 | join warehouse w on w.warehouse_id = e.warehouse_id
|
---|
62 | where e.employee_id = @id";
|
---|
63 |
|
---|
64 | var employee = await db.QueryFirstAsync<Employee>(query, parameters);
|
---|
65 |
|
---|
66 | return Ok(employee);
|
---|
67 | }
|
---|
68 | }
|
---|
69 |
|
---|
70 | // GET: api/GetEmployeeVehicleDetails/5
|
---|
71 | [HttpGet("GetEmployeeVehicleDetails/{id}")]
|
---|
72 | public async Task<ActionResult<IEnumerable<VehicleDetails>>> GetEmployeeVehicleDetails(int id)
|
---|
73 | {
|
---|
74 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
75 | {
|
---|
76 | var parameters = new { id = id };
|
---|
77 |
|
---|
78 | string query = $@"
|
---|
79 | select
|
---|
80 | v.vehicle_id as VehicleId,
|
---|
81 | v.registration as Registration,
|
---|
82 | CONCAT('Type: ', vt.vehicle_type_name ,' | Make: ', vd.make, ' | Model: ', vd.model, ' | Capacity: ', vd.capacity) as Details
|
---|
83 | from employee_drives_vehicle edv
|
---|
84 | join vehicle v on v.vehicle_id = edv.vehicle_id
|
---|
85 | join vehicle_details vd on v.vehicle_details_id = vd.vehicle_details_id
|
---|
86 | join vehicle_type vt on vd.vehicle_type_id = vt.vehicle_type_id
|
---|
87 | where edv.employee_id = @id";
|
---|
88 |
|
---|
89 | var employeeVehicleDetails = await db.QueryAsync<VehicleDetails>(query, parameters);
|
---|
90 |
|
---|
91 | return Ok(employeeVehicleDetails);
|
---|
92 | }
|
---|
93 | }
|
---|
94 |
|
---|
95 | // PUT: api/Employee/5
|
---|
96 | // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
|
---|
97 | [HttpPut]
|
---|
98 | public async Task<IActionResult> PutEmployee([FromBody] Employee employee)
|
---|
99 | {
|
---|
100 | try
|
---|
101 | {
|
---|
102 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
103 | {
|
---|
104 | db.Open();
|
---|
105 | using (var transaction = db.BeginTransaction()) // Start a transaction
|
---|
106 | {
|
---|
107 | var parameters = new
|
---|
108 | {
|
---|
109 | EmployeeId = employee.EmployeeId,
|
---|
110 | WarehouseId = employee.WarehouseId,
|
---|
111 | EmployeeName = employee.EmployeeName.Trim(),
|
---|
112 | EmployeeSurname = employee.EmployeeSurname.Trim(),
|
---|
113 | };
|
---|
114 |
|
---|
115 | await ModelValidation.DoesEmployeeExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.EmployeeName, parameters.EmployeeSurname, parameters.EmployeeId);
|
---|
116 |
|
---|
117 | string updateCustomer = $@"
|
---|
118 | update employee
|
---|
119 | set
|
---|
120 | warehouse_id = @WarehouseId,
|
---|
121 | employee_name = @EmployeeName,
|
---|
122 | employee_surname = @EmployeeSurname
|
---|
123 | where employee_id = @EmployeeId";
|
---|
124 |
|
---|
125 | await db.ExecuteAsync(updateCustomer, parameters, transaction: transaction);
|
---|
126 |
|
---|
127 | transaction.Commit();
|
---|
128 | return Ok();
|
---|
129 | }
|
---|
130 | }
|
---|
131 | }
|
---|
132 | catch (Exception ex)
|
---|
133 | {
|
---|
134 | return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
|
---|
135 | }
|
---|
136 | }
|
---|
137 |
|
---|
138 |
|
---|
139 | // POST: api/Employee
|
---|
140 | // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
|
---|
141 | [HttpPost]
|
---|
142 | public async Task<IActionResult> PostEmployee([FromBody] Employee employee)
|
---|
143 | {
|
---|
144 | try
|
---|
145 | {
|
---|
146 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
147 | {
|
---|
148 | db.Open();
|
---|
149 | using (var transaction = db.BeginTransaction()) // Start a transaction
|
---|
150 | {
|
---|
151 | var parameters = new
|
---|
152 | {
|
---|
153 | EmployeeId = employee.EmployeeId,
|
---|
154 | WarehouseId = employee.WarehouseId,
|
---|
155 | EmployeeName = employee.EmployeeName.Trim(),
|
---|
156 | EmployeeSurname = employee.EmployeeSurname.Trim(),
|
---|
157 | };
|
---|
158 |
|
---|
159 | await ModelValidation.DoesEmployeeExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.EmployeeName, parameters.EmployeeSurname);
|
---|
160 |
|
---|
161 | string addEmployee = $@"
|
---|
162 | INSERT INTO public.employee (warehouse_id, employee_name, employee_surname)
|
---|
163 | VALUES (@WarehouseId, @EmployeeName, @EmployeeSurname);";
|
---|
164 |
|
---|
165 | await db.ExecuteAsync(addEmployee, parameters, transaction: transaction);
|
---|
166 |
|
---|
167 | transaction.Commit();
|
---|
168 | return Ok();
|
---|
169 | }
|
---|
170 | }
|
---|
171 | }
|
---|
172 | catch (Exception ex)
|
---|
173 | {
|
---|
174 | return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
|
---|
175 | }
|
---|
176 | }
|
---|
177 |
|
---|
178 | // POST: api/EmployeeVehicle
|
---|
179 | // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
|
---|
180 | [HttpPost("PostEmployeeVehicle")]
|
---|
181 | public async Task<ActionResult<EmployeeVehicle>> PostEmployeeVehicle([FromBody] EmployeeVehicle employeeVehicle)
|
---|
182 | {
|
---|
183 | try
|
---|
184 | {
|
---|
185 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
186 | {
|
---|
187 |
|
---|
188 | var parameters = new
|
---|
189 | {
|
---|
190 | EmployeeId = employeeVehicle.EmployeeId,
|
---|
191 | VehicleId = employeeVehicle.VehicleId,
|
---|
192 | };
|
---|
193 |
|
---|
194 | string addEmployeeVehicle = $@"
|
---|
195 | INSERT INTO public.employee_drives_vehicle (employee_id, vehicle_Id)
|
---|
196 | VALUES (@EmployeeId, @VehicleId);";
|
---|
197 |
|
---|
198 | await db.ExecuteAsync(addEmployeeVehicle, parameters);
|
---|
199 |
|
---|
200 | return Ok();
|
---|
201 | }
|
---|
202 | }
|
---|
203 | catch (Exception ex)
|
---|
204 | {
|
---|
205 | return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
|
---|
206 | }
|
---|
207 | }
|
---|
208 |
|
---|
209 | // DELETE: api/Employee/5
|
---|
210 | [HttpDelete("{id}")]
|
---|
211 | public async Task<IActionResult> DeleteEmployee(int id)
|
---|
212 | {
|
---|
213 | try
|
---|
214 | {
|
---|
215 | //Check if there is a shipment load linked to the employee we are trying to delete, if so return a message and dont let the user delete the employee.
|
---|
216 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
217 | {
|
---|
218 | db.Open();
|
---|
219 | using (var transaction = db.BeginTransaction()) // Start a transaction
|
---|
220 | {
|
---|
221 | var parameters = new { id = id };
|
---|
222 |
|
---|
223 | string employeeLinkToShipmentLoadQuery = $@"
|
---|
224 | select Count(*)
|
---|
225 | from shipment s
|
---|
226 | where employee_id = @id";
|
---|
227 |
|
---|
228 | var isEmployeeLinkedToShipmentLoad = await db.QueryFirstAsync<int>(employeeLinkToShipmentLoadQuery, parameters);
|
---|
229 | if (isEmployeeLinkedToShipmentLoad > 0)
|
---|
230 | return BadRequest(new ErrorHandler { Name = "Employee Cannot Be Deleted", Description = "Employee cannot be deleted because it is linked to a shipment load" });
|
---|
231 |
|
---|
232 | string deleteEmployee = $@"delete from employee where employee_id = @id";
|
---|
233 | string deleteEmployeeVehicleLink = $@"delete from employee_drives_vehicle edv where employee_id = @id";
|
---|
234 |
|
---|
235 | await db.ExecuteAsync(deleteEmployeeVehicleLink, parameters, transaction: transaction);
|
---|
236 | await db.ExecuteAsync(deleteEmployee, parameters, transaction: transaction);
|
---|
237 |
|
---|
238 | transaction.Commit();
|
---|
239 |
|
---|
240 | return Ok();
|
---|
241 | }
|
---|
242 | }
|
---|
243 | }
|
---|
244 | catch (Exception ex)
|
---|
245 | {
|
---|
246 | return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
|
---|
247 | }
|
---|
248 | }
|
---|
249 |
|
---|
250 | // DELETE: api/Employee/5
|
---|
251 | [HttpDelete("RemoveVehicleFromEmployee/{employeeId}/{vehicleId}")]
|
---|
252 | public async Task<IActionResult> RemoveVehicleFromEmployee(int employeeId, int vehicleId)
|
---|
253 | {
|
---|
254 | try
|
---|
255 | {
|
---|
256 | using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
|
---|
257 | {
|
---|
258 | var parameters = new { employeeId = employeeId, vehicleId = vehicleId };
|
---|
259 |
|
---|
260 | string deleteEmployeeVehicleLink = $@"delete from employee_drives_vehicle edv where employee_id = @employeeId and vehicle_id = @vehicleId";
|
---|
261 |
|
---|
262 | await db.ExecuteAsync(deleteEmployeeVehicleLink, parameters);
|
---|
263 |
|
---|
264 | return Ok();
|
---|
265 | }
|
---|
266 | }
|
---|
267 | catch (Exception ex)
|
---|
268 | {
|
---|
269 | return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
|
---|
270 | }
|
---|
271 | }
|
---|
272 | }
|
---|
273 | }
|
---|