public List <Case> GetAllCase()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM Sag " +
"JOIN Advokat ON Sag.AdvokatID = Advokat.AdvokatId" +
" JOIN Klient ON Sag.KlientNr = Klient.KlientNr";
List <Case> listOfSag = new List <Case>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Case @sag = new Case();
@sag.ID = Convert.ToInt32(x[0]);
@sag.WorkTitle = x[1];
@sag.StartDate = x[2];
@sag.EndDate = DateTime.TryParse(x[3], out DateTime dateTime) ? dateTime : default(DateTime);
@sag.Kilometers = x[4];
@sag.TimeEstimat = x[5];
@sag.CaseDescription = x[6];
@sag.internal_Notes = x[7];
@sag.ClientNr = Convert.ToInt32(x[8]);
@sag.EmployeeID = Convert.ToInt32(x[9]);
@sag.ServiceTypeID = Convert.ToInt32(x[10]);
listOfSag.Add(@sag);
}
return(listOfSag);
}
public void AddEmployeeServiceToEmployee(int id, int serviceTypeID)//By Julius
{
SqlCommand com = new SqlCommand();
string sqlString = $"INSERT INTO Tjenesteydelse(AdvokatId, YdelsesTypeNr) VALUES (@advokatId, @ydelsesTypeNr)";
com.Parameters.Add(new SqlParameter("advokatId", id));
com.Parameters.Add(new SqlParameter("ydelsesTypeNr", serviceTypeID));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public void AddEducationToEmployee(string efteruddannelse, int advokatId)// By Daniella
{
SqlCommand com = new SqlCommand();
string sqlString = $"INSERT INTO Efteruddannelse(Navn, AdvokatId) VALUES (@efteruddannelse, @advokatId)";
com.Parameters.Add(new SqlParameter("efteruddannelse", efteruddannelse));
com.Parameters.Add(new SqlParameter("advokatId", advokatId));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public void CreateClient(Client klient)//By Thomas
{
SqlCommand com = new SqlCommand();
string sqlString = $"INSERT INTO KLient(Navn, Adresse, TelefonNr) VALUES (@Navn , @Adresse , @TelefonNr)";
com.Parameters.Add(new SqlParameter("Navn", klient.Name));
com.Parameters.Add(new SqlParameter("Adresse", klient.Address));
com.Parameters.Add(new SqlParameter("TelefonNr", klient.PhoneNo));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public void UpdateClient(Client klient) // By Thomas
{
SqlCommand com = new SqlCommand();
string sqlString =
$"UPDATE Klient SET Navn =@Navn, Adresse = @Adresse, TelefonNr = @TelefonNr " +
$"WHERE KlientNr = {klient.ID}";
com.Parameters.Add(new SqlParameter("Navn", klient.Name));
com.Parameters.Add(new SqlParameter("Adresse", klient.Address));
com.Parameters.Add(new SqlParameter("TelefonNr", klient.PhoneNo));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
/* Når en advokat skal have tilføjet et speciale/efteruddannelse, skal man i vores database bare indtaste et "navn" på specialet + "advokat id'et", som skal have denne efteruddannelse.
* Havde forstillet mig, at man i vores ViewListe skal kunne vælge "vis advokater" og derinde så tilføje efteruddannelse ud fra en "valgt" advokats id.
* - Dennie
*/
public void UpdateEmployee(Employee advokat) // by Thomas
{
SqlCommand com = new SqlCommand();
String sqlString =
$"UPDATE Advokat SET Navn = @Navn " +
$"WHERE AdvokatID =@AdvokatId";
com.Parameters.Add(new SqlParameter("AdvokatId", advokat.ID));
com.Parameters.Add(new SqlParameter("Navn", advokat.Name));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public List <EmployeeService> GetEmployeeService(int employeeID)//Daniella
{
string sqlString = "SELECT * FROM Tjenesteydelse " +
"Where AdvokatId = " + employeeID;
List <EmployeeService> listofEmployeeService = new List <EmployeeService>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
EmployeeService employeeService = new EmployeeService();
employeeService.EmployeeID = Convert.ToInt32(x[0]);
employeeService.ServiceID = Convert.ToInt32(x[1]);
listofEmployeeService.Add(employeeService);
}
return(listofEmployeeService);
}
public List <Education> GetAllEducation()//By Julius
{
string sqlString = "SELECT * FROM Efteruddannelse";
List <Education> listOfEfteruddannelse = new List <Education>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Education @Efteruddannelse = new Education();
@Efteruddannelse.Name = x[0];
@Efteruddannelse.EmployeeID = Convert.ToInt32(x[1]);
listOfEfteruddannelse.Add(@Efteruddannelse);
}
return(listOfEfteruddannelse);
}
public List <ServiceType> GetAllServiceType()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM YdelseType";
List <ServiceType> listOfYdelsetype = new List <ServiceType>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
ServiceType @ydelseType = new ServiceType();
@ydelseType.ID = Convert.ToInt32(x[0]);
@ydelseType.Name = x[1];
listOfYdelsetype.Add(@ydelseType);
}
return(listOfYdelsetype);
}
public List <EmployeeService> GetAllEmployeeService()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM Tjenesteydelse";
List <EmployeeService> listOfTjenesteydelse = new List <EmployeeService>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
EmployeeService tjenesteydelse = new EmployeeService();
tjenesteydelse.EmployeeID = Convert.ToInt32(x[0]);
tjenesteydelse.ServiceID = Convert.ToInt32(x[1]);
listOfTjenesteydelse.Add(tjenesteydelse);
}
return(listOfTjenesteydelse);
}
public List <Employee> GetAllEmployee()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM Advokat";
List <Employee> listOfAdvokat = new List <Employee>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Employee @advokat = new Employee();
@advokat.ID = Convert.ToInt32(x[0]);
@advokat.Name = x[1];
listOfAdvokat.Add(@advokat);
}
return(listOfAdvokat);
}
public List <Client> GetAllClient()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM Klient";
List <Client> listOfKlient = new List <Client>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Client @klient = new Client();
@klient.ID = Convert.ToInt32(x[0]);
@klient.Name = x[1];
@klient.Address = x[2];
@klient.PhoneNo = x[3];
listOfKlient.Add(@klient);
}
return(listOfKlient);
}
public List <Employee> GetEmployeesFromServiceType(int ydelsesTypeNr)// By Daniella //By Julius //Denne linje kode
{
string sqlString = @"SELECT * FROM ADVOKAT
JOIN Tjenesteydelse ON Tjenesteydelse.AdvokatId = Advokat.AdvokatId
WHERE YdelsesTypeNr = " + ydelsesTypeNr;
List <Employee> listOfAdvokat = new List <Employee>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Employee @advokat = new Employee();
@advokat.ID = Convert.ToInt32(x[0]);
@advokat.Name = x[1];
listOfAdvokat.Add(@advokat);
}
return(listOfAdvokat);
}
public void UpdateService(Service ydelse)
{
SqlCommand com = new SqlCommand();
string sqlString =
$"UPDATE Ydelse SET Startdato = @StartDato , YdelsesBeskrivelse =@YdelsesBeskrivelse , Pris = @Pris ,Timer = @Timer , SagsNr = @SagsNr , AdvokatId = @AdvokatId " +
$"WHERE YdelsesNr ={ydelse.ID}";
//com.Parameters.Add(new SqlParameter("Navn", advokat.Name));
com.Parameters.Add(new SqlParameter("Startdato", ydelse.StartDate));
com.Parameters.Add(new SqlParameter("YdelsesBeskrivelse", ydelse.Services_description));
com.Parameters.Add(new SqlParameter("Pris", ydelse.Price));
com.Parameters.Add(new SqlParameter("Timer", ydelse.Timer));
com.Parameters.Add(new SqlParameter("SagsNr", ydelse.CaseID));
com.Parameters.Add(new SqlParameter("AdvokatId", ydelse.CaseID));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public void UpdateCase(Case @case)// By Daniella, refactored by Julius
{
string sqlString =
$"UPDATE Sag SET Arbejdstitel = @Arbejdstitel, StartDato = @StartDato , SlutDato = @SlutDato , Kørselstimer = @Kørselstimer , TimeEstimat = @TimeEstimat , SagsBeskrivelse = @SagsBeskrivelse , InterneNoter = @InterneNoter , KlientNr = @KlientNr , AdvokatId = @AdvokatId , YdelsesTypeNr = @YdelsesTypeNr " +
$"WHERE SagsNr = {@case.ID}";
SqlCommand com = new SqlCommand();
com.Parameters.Add(new SqlParameter("Arbejdstitel", @case.WorkTitle));
com.Parameters.Add(new SqlParameter("Startdato", @case.StartDate));
com.Parameters.Add(new SqlParameter("SlutDato", @case.EndDate.ToString()));
com.Parameters.Add(new SqlParameter("Kørselstimer", @case.Kilometers));
com.Parameters.Add(new SqlParameter("TimeEstimat", @case.TimeEstimat));
com.Parameters.Add(new SqlParameter("SagsBeskrivelse", @case.CaseDescription));
com.Parameters.Add(new SqlParameter("InterneNoter", @case.internal_Notes));
com.Parameters.Add(new SqlParameter("KlientNr", @case.ClientNr));
com.Parameters.Add(new SqlParameter("AdvokatId", @case.EmployeeID));
com.Parameters.Add(new SqlParameter("YdelsesTypeNr", @case.ServiceTypeID));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
public List <Education> GetEducationsFromEmployee(int employeeID)//Dniella
{
string sqlString = "SELECT * FROM Efteruddannelse " +
"JOIN Advokat ON Efteruddannelse.AdvokatId = Advokat.AdvokatId " +
$"WHERE Efteruddannelse.AdvokatId = {employeeID}";
List <Education> listOfEducation = new List <Education>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Education @Efteruddannelse = new Education();
@Efteruddannelse.Name = x[0];
@Efteruddannelse.EmployeeID = Convert.ToInt32(x[1]);
listOfEducation.Add(@Efteruddannelse);
}
return(listOfEducation);
}
public List <Service> GetAllServices()// By Daniella //By Julius
{
string sqlString = "SELECT * FROM Ydelse";
List <Service> listOfYdelse = new List <Service>();
List <List <string> > rawReadValue = SQLDatabaseUtilities.ReadSqlCommand(sqlString);
foreach (List <string> x in rawReadValue)
{
Service @ydelse = new Service();
@ydelse.ID = Convert.ToInt32(x[0]);
@ydelse.StartDate = x[1];
@ydelse.Services_description = x[2];
@ydelse.Price = Convert.ToInt32(x[3]);
@ydelse.Timer = Convert.ToInt32(x[4]);
@ydelse.CaseID = Convert.ToInt32(x[5]);
@ydelse.EmployeeID = Convert.ToInt32(x[6]);
listOfYdelse.Add(@ydelse);
}
return(listOfYdelse);
}
public void CreateService(Service ydelse)
{
{
SqlCommand com = new SqlCommand();
string sqlString = $"INSERT INTO Ydelse( StartDato, YdelsesBeskrivelse, Pris, Timer, SagsNr, AdvokatId )" + $"values( @StartDato , @YdelsesBeskrivelse ,@Pris ,@Timer , @SagsNr , @AdvokatId )";
com.Parameters.Add(new SqlParameter("StartDato", ydelse.StartDate));
com.Parameters.Add(new SqlParameter("YdelsesBeskrivelse", ydelse.Services_description));
com.Parameters.Add(new SqlParameter("Pris", ydelse.Price));
com.Parameters.Add(new SqlParameter("Timer", ydelse.Timer));
com.Parameters.Add(new SqlParameter("SagsNr", ydelse.CaseID));
com.Parameters.Add(new SqlParameter("AdvokatId", ydelse.EmployeeID));
SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
}
/*
* public void CreateClient(Client klient)//By Thomas
* {
* SqlCommand com = new SqlCommand();
* string sqlString = $"INSERT INTO KLient(Navn, Adresse, TelefonNr) VALUES (@Navn , @Adresse , @TelefonNr)";
* SQLDatabaseUtilities.RunSqlInsertCommand(sqlString, com);
*
* }
*/
}