public IActionResult Index(string dateReservation, int?client_id, int?services_id, int?employee_id)
{
ViewBag.clientList = GenerateMultiSelectListWithClient();
ViewBag.serviceList = GenerateMultiSelectListWithServices();
ViewBag.employeeList = GenerateMultiSelectListWithEmployee();
DateTime reservationdate;
var reservationList = getVReservation.GetVReservations();
if (!String.IsNullOrEmpty(dateReservation))
{
reservationdate = DateTime.Parse(dateReservation);
reservationList = reservationList.Where(x => x.reservation_date.ToShortDateString() == reservationdate.ToShortDateString()).ToList();
}
if ((client_id > 0) || (services_id > 0) || (employee_id > 0))
{
if (services_id > 0)
{
reservationList = reservationList.Where(x => x.services_id == services_id).ToList();
}
if (employee_id > 0)
{
string empName = selectEmployee.GetRowsForTable(SGetAllRowsFromSpecificTable.EmployeeSelectAllRowsQuery())
.Where(x => x.employee_id == employee_id)
.FirstOrDefault().employee_name;
reservationList = reservationList.Where(y => y.employee_name == empName).ToList();
}
}
return(View(reservationList));
}
private MultiSelectList GenerateMultiSelectListWithEmployee()
{
var employeeList = selectEmployee.GetRowsForTable(SGetAllRowsFromSpecificTable.EmployeeSelectAllRowsQuery());
List <Employee> listE = new List <Employee> {
new Employee {
employee_id = 0, employee_name = "Wybierz pracownika"
}
};
if (!employeeList.Any())
{
return(new MultiSelectList(listE.Select(x => new
{
key = x.employee_id,
value = x.employee_name
}), "key", "value"));
}
foreach (var i in employeeList)
{
listE.Add(i);
}
return(new MultiSelectList(listE.Select(x => new
{
key = x.employee_id,
value = x.employee_name
}), "key", "value"));
}
public int InsertObjectToDB(Employee dataObject)
{
var insertEmployes = SInsertScripts.SqlLiteDBInsertEmployee(dataObject);
string result = DBConnectAndExecute.ExecuteQuery(insertEmployes);
if (result != string.Empty)
{
return(-1);
}
var lastAddedServices = selectEmployee.GetRowsForTable(SGetAllRowsFromSpecificTable.EmployeeSelectAllRowsQuery())
.Where(x => x.employee_id == dataObject.employee_id).First();
return(lastAddedServices.employee_id);
}
public static void SetDataToCmbEmployee(ComboBox cmbEmployee)
{
selectEmployee = new SelectEmployee();
List <Employee> listEmployee = new List <Employee>();
listEmployee.Add(new Employee {
employee_id = 0, employee_name = "wybierz pracownika"
});
foreach (var i in selectEmployee.GetRowsForTable(SGetAllRowsFromSpecificTable.EmployeeSelectAllRowsQuery()))
{
listEmployee.Add(i);
}
cmbEmployee.DataSource = listEmployee;
cmbEmployee.DisplayMember = "employee_name";
cmbEmployee.ValueMember = "employee_id";
}
public List <VReservation> GetVReservations()
{
var clientsList = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery());
var servicesList = selectServices.GetRowsForTable(SGetAllRowsFromSpecificTable.ServicesSelectAllRowsQuery());
var reservationList = selectReservation.GetRowsForTable(SGetAllRowsFromSpecificTable.ReservationSelectAllRowsQuery());
var employeeList = selectEmployee.GetRowsForTable(SGetAllRowsFromSpecificTable.EmployeeSelectAllRowsQuery());
var result = reservationList.Join(clientsList,
x => x.client_id,
y => y.client_id,
(x, y) => new
{
clName = y.client_name,
clSname = y.client_sname,
clPhone = y.client_phone,
clDesc = y.client_description,
resId = x.reservation_id,
resDate = x.reservation_date,
resTime = x.reservation_time,
serId = x.services_id,
empID = x.employee_id,
clId = y.client_id
})
.Join(servicesList,
x => x.serId,
y => y.services_id,
(x, y) => new
{
x.resId,
x.resDate,
x.resTime,
x.clName,
x.clSname,
x.clPhone,
x.clDesc,
y.services_name,
y.services_id,
x.empID,
x.clId
})
.Join(employeeList,
x => x.empID,
y => y.employee_id,
(x, y) => new
{
x.clName,
x.clSname,
x.clPhone,
x.clDesc,
x.resId,
x.resDate,
x.resTime,
x.services_id,
x.services_name,
empName = y.employee_name,
x.empID,
x.clId
}).Select(x => new VReservation
{
reservation_id = x.resId,
reservation_date = x.resDate,
reservation_time = x.resTime,
client_name = x.clName,
client_sname = x.clSname,
client_phone = x.clPhone,
client_description = x.clDesc,
services_name = x.services_name,
services_id = x.services_id,
employee_name = x.empName,
client_id = x.clId,
employee_id = x.empID,
client_data = x.clName + " " + x.clSname + " " + x.clPhone + " " + x.clDesc
}).OrderByDescending(x => x.reservation_date.ToShortDateString()).ThenByDescending(y => y.reservation_time).ToList();
return(result);
}
