public bool AddCourseToHistory(Account account, int courseId)
{
string command =
$@"
INSERT INTO dbo.tblCourseHistory (
acc_id,
crs_id
)
SELECT acc.acc_id,
'{courseId}'
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Delete(Account account)
{
string command =
$@"
DELETE gpf
FROM dbo.tblGPFSession gpf
WHERE gpf.acc_id = '{account.Id}'
DELETE coh
FROM dbo.tblCourseHistory coh
WHERE coh.acc_id = '{account.Id}'
DELETE acc
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Register(Account account)
{
if (account == null || string.IsNullOrWhiteSpace(account.Username) || string.IsNullOrWhiteSpace(account.Password))
{
return(false);
}
string command =
$@"
INSERT INTO dbo.tblAccount (
acc_username,
acc_password,
acc_first_name,
acc_last_name
)
SELECT
'{account.Username}',
'{account.Password}',
'{account.FirstName}',
'{account.LastName}'
";
/** Fields can be updated from profile after registering.
* acc_street,
* acc_city,
* acc_state,
* acc_zip,
* acc_phone,
* acc_role,
* deg_id,
* con_id
* '{account.Street}',
* '{account.City}',
* '{account.State}',
* '{account.Zip}',
* '{account.Phone}',
* '{account.Role}',
* '{account.Degree.Id}',
* '{account.Concentration.Id}'
*/
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool SaveSession(GPFSession session)
{
string command =
$@"
INSERT INTO dbo.tblGPFSession (
acc_id,
deg_id,
con_id,
gpf_entering_year,
gpf_entering_quarter,
gpf_classes_per_quarter,
gpf_class_delivery
)
SELECT acc.acc_id,
'{session.Degree.Id}',
'{session.Concentration.Id}',
'{session.EnteringTerm.Year}',
'{session.EnteringTerm.Quarter.Value}',
'{session.ClassesPerQuarter}',
'{session.ClassDeliveryOption.Value}'
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{session.Account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool RemoveCourseFromHistory(Account account, int courseId)
{
string command =
$@"
DELETE coh
FROM dbo.tblCourseHistory coh
WHERE coh.acc_id = '{account.Id}'
AND coh.crs_id = '{courseId}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(Account account)
{
if (account == null)
{
return(false);
}
string command = $@"
UPDATE acc SET ";
if (!String.IsNullOrWhiteSpace(account.FirstName))
{
command += $" acc.acc_first_name = '{account.FirstName}',";
}
if (!String.IsNullOrWhiteSpace(account.LastName))
{
command += $" acc.acc_last_name = '{account.LastName}',";
}
if (!String.IsNullOrWhiteSpace(account.Street))
{
command += $" acc.acc_street = '{account.Street}',";
}
if (!String.IsNullOrWhiteSpace(account.City))
{
command += $" acc.acc_city = '{account.City}',";
}
if (!String.IsNullOrWhiteSpace(account.State))
{
command += $" acc.acc_state = '{account.State}',";
}
if (!String.IsNullOrWhiteSpace(account.Zip))
{
command += $" acc.acc_zip = '{account.Zip}',";
}
if (account.Role != null)
{
command += $" acc.acc_role = '{account.Role.Value}',";
}
if (account.Degree != null)
{
command += $" acc.deg_id = '{account.Degree.Id}',";
}
if (account.Concentration != null)
{
command += $" acc.con_id = '{account.Concentration.Id}'";
}
command += $@"
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
/**
* acc.acc_phone = '{account.Phone}',
* acc.acc_role = '{account.Role.Value}',
*/
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
