/// <summary>
/// Deletes the user based on username.
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool DeleteUser(string userName)
{
bool result = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
try
{
cmd.CommandText = "removeUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userName", userName);
connection.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
DbLogging.Log(e);
result = false;
}
finally
{
connection.Close();
cmd.Parameters.Clear();
}
return(result);
}
/// <summary>
/// Adds a company to the database.
/// </summary>
/// <param name="name"> The company name </param>
/// <param name="street">The company's street</param>
/// <param name="postalCode">The company postal code </param>
/// <param name="city">The company city </param>
/// <param name="country">The country </param>
/// <param name="phone">The phone number </param>
/// <param name="fax">The fax number </param>
/// <param name="year">The year</param>
/// <returns>A boolean </returns>
public bool AddCompany(string name, string street, string postalCode, string city, string country, string phone, string fax, string year)
{
bool result = true;
int yearParsed = 0;
bool yearParsedResult = false;
if (year.Length <= 4)
{
yearParsedResult = int.TryParse(year, out yearParsed);
}
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = "addCompany";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@street", street);
cmd.Parameters.AddWithValue("@postalcode", postalCode);
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@country", country);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@fax", fax);
cmd.Parameters.AddWithValue("@year", yearParsed);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
result = false;
DbLogging.Log(e);
}
finally
{
connection.Close();
cmd.Parameters.Clear();
}
return(result);
}
/// <summary>
/// Auto creates and returns the desired table by
/// using parameters present in the command
/// object. This method implements support for
/// stored procedures.
/// </summary>
/// <param name="command">The command object holding desired command information.</param>
/// <param name="table">The table to return data into.</param>
/// <exception cref="SqlException"></exception>
/// <returns>int -> The return value of store procedure</returns>
private int FillTable(SqlCommand command, DataTable table)
{
SqlDataReader reader = null; //Used for reading from database
SqlParameter procReturn = null; //Holds return value from a stored procedure
int retVal = 0; //Less than 0 if problem occurs
try
{
/* Get return value if running a stored procedure */
if (command.CommandType == CommandType.StoredProcedure)
{
procReturn = command.Parameters.Add("return_value", SqlDbType.Int);
procReturn.Direction = ParameterDirection.ReturnValue;
}
/* Open connection and execute reader */
command.Connection.Open();
reader = command.ExecuteReader();
/* Load data into table */
table.Load(reader);
/* Set retVal to what was returned from stored procedure (if applicable) */
if (command.CommandType == CommandType.StoredProcedure)
{
retVal = (int)procReturn.Value;
}
}
catch (SqlException e)
{
DbLogging.Log(e);
/* Rethrow */
throw;
}
finally
{
/* Close connection and reader if open */
if (reader != null)
{
reader.Close();
}
command.Connection.Close();
}
return(retVal);
}
/// <summary>
/// Gets the reports.
/// </summary>
/// <param name="Table"></param>
/// <param name="funcName"></param>
/// <param name="companyName"></param>
/// <param name="employeeType"></param>
/// <param name="Week"></param>
/// <returns></returns>
public bool GetReports(DataTable Table, string funcName, string companyName, string employeeType = "", string Week = "")
{
bool result = true;
// Create the command and set the connection.
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null; //Used for reading from database
cmd.Connection = connection;
try
{
cmd.CommandText = funcName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName;
if (employeeType != "")
{
cmd.Parameters.Add("@employeeType", SqlDbType.NVarChar).Value = employeeType;
}
if (Week != "") // Not all them need a week so don't add parametr if dont need to
{
cmd.Parameters.Add("@StartDay", SqlDbType.Date).Value = Convert.ToDateTime(Week);
}
cmd.Connection.Open();
reader = cmd.ExecuteReader();
/* Load data into table */
Table.Load(reader);
}
catch (SqlException ex)
{
DbLogging.Log(ex);
}
finally
{
connection.Close();
cmd.Parameters.Clear();
}
return(result);
}
/// <summary>
/// Gets a list of all company names stored
/// in the database.
/// </summary>
/// <exception cref="SqlException"></exception>
/// <returns>DataTable -> List of all company names</returns>
public DataTable GetCompanyNames()
{
DataTable companyNames = new DataTable();
try
{
/* Set to get all company names from database */
string cmdText = "SELECT name FROM Companies ORDER BY name ASC";
SqlCommand cmd = new SqlCommand(cmdText, connection);
/* Get the company names */
FillTable(cmd, companyNames);
}
catch (SqlException e)
{
DbLogging.Log(e);
throw;
}
return(companyNames);
}
/// <summary>
/// Gets the date of incorporation.
/// </summary>
/// <param name="companyName"></param>
/// <returns></returns>
public string GetDateOfIncorporation(string companyName)
{
SqlDataReader reader = null;
string incorporationYear = "";
string cmdStr = "SELECT DISTINCT yearOfIncorporation FROM companies WHERE name=@companyName";
try
{
SqlCommand command = new SqlCommand(cmdStr, connection);
command.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName;
/* Open connection and get data */
connection.Open();
reader = command.ExecuteReader();
/* Get the year of incorporation from reader */
if (reader.HasRows)
{
if (reader.Read())
{
incorporationYear = reader["yearOfIncorporation"].ToString();
}
}
}
catch (SqlException ex)
{
DbLogging.Log(ex);
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
connection.Close();
}
return(incorporationYear);
}
/// <summary>
/// Addds a user to the database
/// </summary>
/// <param name="userName">The username</param>
/// <param name="firstName">The firstname </param>
/// <param name="lastName">The lastname</param>
/// <param name="securityClearence"> Security Clearence </param>
/// <param name="password">The password</param>
/// <returns></returns>
public bool AddUser(string userName, string firstName, string lastName, int securityClearence, string password)
{
bool result = true;
// Create the command and set the connection.
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
try
{
cmd.CommandText = "addUser";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@userName", SqlDbType.NVarChar).Value = userName;
cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName;
cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName;
cmd.Parameters.Add("@securityId", SqlDbType.Int).Value = securityClearence;
cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
result = false;
DbLogging.Log(e);
}
finally
{
connection.Close();
cmd.Parameters.Clear();
}
return(result);
}
/// <summary>
/// Adds a time card
/// </summary>
/// <param name="EmployeeID"> The employee id </param>
/// <param name="TCDate"> The time card date </param>
/// <param name="Hours"> The hours 1</param>
/// <returns></returns>
public bool AddTimecard(int EmployeeID, DateTime TCDate, decimal Hours)
{
bool result = true;
// Create the command and set the connection.
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
try
{
cmd.CommandText = "addTimeCard";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@employeeID", SqlDbType.BigInt).Value = EmployeeID;
cmd.Parameters.Add("@date", SqlDbType.Date).Value = TCDate;
cmd.Parameters.Add("@hours", SqlDbType.Decimal).Value = Hours;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
DbLogging.Log(e);
}
finally
{
connection.Close();
cmd.Parameters.Clear();
}
return(result);
}
/// <summary>
/// Inserts an employee of either seasonal, fulltime, part
/// </summary>
/// <param name="firstName">Employee first name</param>
/// <param name="lastName">Employee last name</param>
/// <param name="sin">Employee sin or business number</param>
/// <param name="pay">Pay amount of the employee</param>
/// <param name="companyName">Name of the company employee works for</param>
/// <param name="dateOfBirth">Employee date of birth</param>
/// <param name="dateOfHire">Employee date of hire</param>
/// <param name="dateOfTermination">Employee date of termination</param>
/// <param name="reasonForLeaving">Employee reason for leaving</param>
/// <param name="employeeType">Type of employee</param>
/// <param name="season">The season the employee is working (if seasonal)</param>
/// <param name="seasonYear">The year the employee is working (if seasonal)</param>
/// <param name="Clearance">If admin or general user is inserting</param>
public void CreateEmployee(string firstName, string lastName, string sin, string pay, string companyName,
string dateOfBirth, string dateOfHire, string dateOfTermination, string reasonForLeaving, string employeeType,
string season, string seasonYear, string Clearance)
{
SqlCommand cmd = new SqlCommand("", connection);
cmd.CommandType = CommandType.StoredProcedure;
try
{
/* Remove spaces from sin */
sin = Regex.Replace(sin, "[\\s]", "");
/* Add all common parameters */
cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName;
cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName;
cmd.Parameters.Add("@sin", SqlDbType.NChar).Value = sin;
cmd.Parameters.Add("@employedWithCompany", SqlDbType.Bit).Value = true;
cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = companyName;
cmd.Parameters.Add("@dateOfBirth", SqlDbType.Date).Value = dateOfBirth;
/* Add specific parameters */
if (employeeType != "seasonal")
{
if (!string.IsNullOrEmpty(dateOfTermination) && Clearance == "1")
{
cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = reasonForLeaving;
cmd.Parameters.Add("@dateOfTermination", SqlDbType.Date).Value = dateOfTermination;
}
else
{
cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = DBNull.Value;
cmd.Parameters.Add("@dateOfTermination", SqlDbType.Date).Value = DBNull.Value;
}
cmd.Parameters.Add("@dateOfHire", SqlDbType.Date).Value = dateOfHire;
}
else
{
cmd.Parameters.Add("@season", SqlDbType.NVarChar).Value = season;
cmd.Parameters.Add("@seasonYear", SqlDbType.NChar).Value = seasonYear;
cmd.Parameters.Add("@reasonForLeaving", SqlDbType.NVarChar).Value = DBNull.Value;
}
/* Set to valid if admin, false otherwise */
if (Clearance == "1")
{
cmd.Parameters.Add("@isValid", SqlDbType.Bit).Value = true;
cmd.Parameters.Add("@pay", SqlDbType.Money).Value = pay;
}
else
{
cmd.Parameters.Add("@isValid", SqlDbType.Bit).Value = false;
cmd.Parameters.Add("@pay", SqlDbType.Money).Value = DBNull.Value;
}
/* Call appropriate method */
switch (employeeType)
{
case "fulltime":
cmd.CommandText = "CreateFullTime";
break;
case "parttime":
cmd.CommandText = "CreatePartTime";
break;
case "contract":
cmd.CommandText = "CreateContract";
break;
case "seasonal":
cmd.CommandText = "CreateSeasonal";
break;
}
connection.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
DbLogging.Log(ex);
throw;
}
finally
{
connection.Close();
}
}