public static Client GetClientFullTextSearch(string searchText, out int clientId)
{
//error with non unique value in result
selectClient = new SelectClient();
List <Client> result = new List <Client>();
result = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery());
var client = result.Select(x => new
{
id = x.client_id,
text = x.client_name + " " + x.client_sname + " " + x.client_phone
}).Where(y => y.text.Contains(searchText));
if (client.Any() == false)
{
clientId = -1;
return(new Client {
client_id = 0, client_name = "brak", client_phone = "brak", client_sname = "brak", client_description = "brak"
});
}
var selectedClient = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery()).Where(x => x.client_id == client.First().id).First();
clientId = client.First().id;
return(selectedClient);
}
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"));
}
private MultiSelectList GenerateMultiSelectListWithServices()
{
var serviceList = selectServices.GetRowsForTable(SGetAllRowsFromSpecificTable.ServicesSelectAllRowsQuery());
List <Services> listS = new List <Services>()
{
new Services {
services_id = 0, services_name = "Wybierz usługę"
}
};
if (!serviceList.Any())
{
return(new MultiSelectList(listS.Select(x => new
{
key = x.services_id,
value = x.services_name
}), "key", "value"));
}
foreach (var i in serviceList)
{
listS.Add(i);
}
return(new MultiSelectList(listS.Select(x => new
{
key = x.services_id,
value = x.services_name
}), "key", "value"));
}
private MultiSelectList GenerateMultiSelectListWithClient()
{
var allclientList = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery());
List <Client> listC = new List <Client>()
{
new Client {
client_id = 0, client_name = "Wybierz klienta"
}
};
if (!allclientList.Any())
{
return(new MultiSelectList(listC.Select(x => new
{
key = x.client_id,
value = x.client_name + " " + x.client_sname
}), "key", "value"));
}
foreach (var i in allclientList)
{
listC.Add(i);
}
var result = new MultiSelectList(listC.Select(x => new
{
key = x.client_id,
value = x.client_name + " " + x.client_sname
}), "key", "value");
return(result);
}
public PartialViewResult AddClient(int?client_id)
{
ViewBag.clientList = GenerateMultiSelectListWithClient();
if (client_id != null)
{
var selectedClient = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Where(x => x.client_id == client_id).ToList();
return(PartialView(selectedClient));
}
return(PartialView());
}
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 int InsertObjectToDB(Reservation dataObject)
{
var insertReservation = SInsertScripts.SqlLiteDBInsertReservation(dataObject, dataObject.client_id, dataObject.services_id, dataObject.employee_id);
string result = DBConnectAndExecute.ExecuteQuery(insertReservation);
if (result != string.Empty)
{
return(-1);
}
var lastAddedReservation = selectReservation.GetRowsForTable(SGetAllRowsFromSpecificTable.ReservationSelectAllRowsQuery())
.Where(x => x.reservation_id == dataObject.reservation_id).First();
return(lastAddedReservation.reservation_id);
}
public int InsertObjectToDB(Client dataObject)
{
var insertClient = SInsertScripts.SqlLiteDBInsertClient(dataObject);
string result = DBConnectAndExecute.ExecuteQuery(insertClient);
if (result != string.Empty)
{
return(-1);
}
//this is not nessery because i passes in client_id in the parameter
var lastAddedClient = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Where(x => x.client_id == dataObject.client_id).First();
return(lastAddedClient.client_id);
}
private void CmbClientList_SelectedIndexChanged(object sender, EventArgs e)
{
if (ckbUpdateClient.Checked)
{
if (cmbClientList.SelectedIndex == 0)
{
FillClientControls(null, true);
return;
}
var selectedClient = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Where(x => x.client_id == cmbClientList.SelectedIndex).First();
FillClientControls(selectedClient, false);
}
}
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 static void SetDataToCmbServices(ComboBox cmbListServices)
{
selectServices = new SelectServices();
List <Services> listServices = new List <Services>();
listServices.Add(new Services {
services_id = 0, services_name = "usługa"
});
foreach (var i in selectServices.GetRowsForTable(SGetAllRowsFromSpecificTable.ServicesSelectAllRowsQuery()))
{
listServices.Add(i);
}
cmbListServices.DataSource = listServices;
cmbListServices.DisplayMember = "services_name";
cmbListServices.ValueMember = "services_id";
}
public static void SetDataToCmbClient(ComboBox cmbClientList)
{
selectClient = new SelectClient();
List <Client> clientList = new List <Client>();
clientList.Add(new Client {
client_id = 0, client_name = "klient"
});
foreach (var i in selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery()))
{
clientList.Add(new Client {
client_id = i.client_id, client_name = i.client_name + " " + i.client_sname
});
}
cmbClientList.DataSource = clientList;
cmbClientList.DisplayMember = "client_name";
cmbClientList.ValueMember = "client_id";
}
public void ShouldUpdateClient_when_IHaveHisId_returnTrue()
{
SqlLiteDB.SqlLiteDBCreateTableIFNotExist();
//search client
var clientResult = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery()).First();
//get his client_id
int clientId = clientResult.client_id;
//change his data
Client changeClient = new Client()
{
client_name = clientResult.client_name,
client_sname = clientResult.client_sname,
client_phone = clientResult.client_phone,
client_description = "zaktualizowny"
};
bool result = updateClient.UpdateObject(changeClient, clientId);
Assert.IsTrue(result);
//check result
}
public ActionResult AddReservation(Reservation reservation, string findClient)
{
int clientId = reservation.client_id;
if (!String.IsNullOrEmpty(findClient))
{
var clientList = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Select(x => new
{
desccription = x.client_name + " " + x.client_sname + " " + x.client_phone + " " + x.client_description,
clientId = x.client_id
}).ToList();
clientId = clientList.Where(x => x.desccription.Contains(findClient)).First().clientId;
}
insertObjectToDB.GetReservationIdAndInsertToDB(reservation.reservation_date, reservation.reservation_time.Hours, reservation.reservation_time.Minutes,
clientId, reservation.services_id, reservation.employee_id);
return(RedirectToAction("Index", "Reservation", new { visibleTrue = false }));
}
public PartialViewResult UpdateClient(int?client_id, string findClient)
{
ViewBag.clientList = GenerateMultiSelectListWithClient();
if ((client_id != null) || (client_id == 0))
{
return(PartialView(selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Where(x => x.client_id == client_id).First()));
}
if (!String.IsNullOrEmpty(findClient))
{
var clientList = selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Select(x => new
{
desccription = x.client_name + " " + x.client_sname + " " + x.client_phone + " " + x.client_description,
clientId = x.client_id
}).ToList();
return(PartialView(clientList.Where(x => x.desccription.Contains(findClient)).First()));
}
return(PartialView());
}
public bool GetReservationIdAndInsertToDB(DateTime reservationDate, int reservationHour, int reservationMinute, int clientID, int servicesID, int employeeID)
{
int reservationID = selectReservation.GetNextTabletId(SGetIdFromSpecificTable.queryGetLatestReservationID());
Reservation reservation = new Reservation()
{
reservation_id = reservationID,
reservation_date = reservationDate,
reservation_time = new TimeSpan(reservationHour, reservationMinute, 0),
client_id = clientID,
services_id = servicesID,
employee_id = employeeID
};
if (selectReservation.GetRowsForTable(SGetAllRowsFromSpecificTable.ReservationSelectAllRowsQuery())
.Any(x => x.reservation_date == reservation.reservation_date &&
x.reservation_time == reservation.reservation_time &&
x.employee_id == reservation.employee_id))
{
return(false);
}
SLogToFile.SaveDataTebleInToFile("reservation", reservation.ToString());
insertReservation.InsertObjectToDB(reservation);
return(true);
}
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);
}
public bool UpdateObject(Reservation dataobjectForChange, int id)
{
var updateReservation = SUpdateScripts.SqlLiteDBUpdateReservation(dataobjectForChange, id);
string result = DBConnectAndExecute.ExecuteQuery(updateReservation);
if (result != string.Empty)
{
return(false);
}
return(VerifyUpdateData(dataobjectForChange, selectReservation.GetRowsForTable(SGetAllRowsFromSpecificTable.ReservationSelectAllRowsQuery())
.Where(x => x.reservation_id == id).First()));
}
public bool UpdateObject(Client dataobjectForChange, int id)
{
var updateClient = SUpdateScripts.SqlLiteDBUpdateClient(dataobjectForChange, id);
string result = DBConnectAndExecute.ExecuteQuery(updateClient);
if (result != string.Empty)
{
return(false);
}
return(VerifyUpdateData(dataobjectForChange, selectClient.GetRowsForTable(SGetAllRowsFromSpecificTable.ClientSelectAllRowsQuery())
.Where(x => x.client_id == id).First()));
}
