public List <clsSales> getSales(DateTime from, DateTime to)
{
List <clsSales> list = new List <clsSales>();
clsSales oSales;
clsAccountUser oEmployee;
string sQuery = "SELECT idSalesControl, SalesControl.SCReceiptCode, Employee.idEmployee, Employee.EMPLastName, Employee.EMPFirstName, Employee.EMPMIddleName, " +
" SalesControl.SCCustFullName, SalesControl.SCCustAddress, SalesControl.SCCustContactNo, SalesControl.SCDateOfPurchase, SCDateCreated, SCStatus " +
" FROM SalesControl INNER JOIN Employee on Employee.idEmployee = SalesControl.idEmployee WHERE SalesControl.SCDateOfPurchase >= @fromdate " +
"AND SalesControl.SCDateOfPurchase <= @todate";
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
try
{
oConnection.Open();
using (SqlCommand oCommand = new SqlCommand(sQuery, oConnection))
{
oCommand.Parameters.AddWithValue("@fromdate", from);
oCommand.Parameters.AddWithValue("@todate", to);
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
if (oReader.HasRows)
{
while (oReader.Read())
{
oSales = new clsSales();
oEmployee = new clsAccountUser();
oSales.ID = oReader.GetInt32(oReader.GetOrdinal("idSalesControl"));
oSales.Code = oReader.GetString(oReader.GetOrdinal("SCReceiptCode"));
oEmployee.ID = oReader.GetInt32(oReader.GetOrdinal("idEmployee"));
oEmployee.Firstname = oReader.GetString(oReader.GetOrdinal("EMPFirstName"));
oEmployee.Middlename = oReader.GetString(oReader.GetOrdinal("EMPMiddleName"));
oEmployee.Lastname = oReader.GetString(oReader.GetOrdinal("EMPLastName"));
oSales.Employee = oEmployee;
oSales.CustFullName = oReader.GetString(oReader.GetOrdinal("SCCustFullName"));
oSales.CustAddress = oReader.GetString(oReader.GetOrdinal("SCCustAddress"));
oSales.CustContactNo = oReader.GetString(oReader.GetOrdinal("SCCustContactNo"));
oSales.DateOfPurchase = oReader.GetDateTime(oReader.GetOrdinal("SCDateOfPurchase"));
oSales.DateCreated = oReader.GetDateTime(oReader.GetOrdinal("SCDateCreated"));
oSales.Status = oReader.GetBoolean(oReader.GetOrdinal("SCStatus"));
list.Add(oSales);
}
}
}
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
}
return(list);
}
public static bool VerifyAccount(string username, string password)
{
clsAccountUser auth = new clsAccountUser();
currentUser = auth.authenticate(username, password);
if (currentUser != null)
{
return(true);
}
return(false);
}
public clsAccountUser authenticate(string username, string password)
{
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
try
{
oConnection.Open();
using (SqlCommand oCommand = new SqlCommand("SELECT idEmployee, EMPUsername, EMPFirstName, EMPLastName, EMPDateCreated, EMPStatus FROM Employee " +
" WHERE EMPUsername = @username AND EMPPassword = @password AND EMPStatus = 1", oConnection))
{
oCommand.Parameters.AddWithValue("@username", username);
oCommand.Parameters.AddWithValue("@password", password);
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
if (oReader.HasRows)
{
clsAccountUser clsAccountUser = new clsAccountUser();
if (oReader.Read())
{
clsAccountUser.ID = oReader.GetInt32(oReader.GetOrdinal("idEmployee"));
clsAccountUser.Username = oReader.GetString(oReader.GetOrdinal("EMPUsername"));
clsAccountUser.Firstname = oReader.GetString(oReader.GetOrdinal("EMPFirstName"));
clsAccountUser.Middlename = oReader.GetString(oReader.GetOrdinal("EMPLastName"));
clsAccountUser.Lastname = oReader.GetString(oReader.GetOrdinal("EMPLastName"));
clsAccountUser.DateCreated = oReader.GetDateTime(oReader.GetOrdinal("EMPDateCreated"));
clsAccountUser.Status = oReader.GetBoolean(oReader.GetOrdinal("EMPStatus"));
}
return(clsAccountUser);
}
General.showMessageBox("Warning", "There seems to be an error in username or password", MsgTypes.warning, "Sign-in Error");
}
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger);
}
}
return(null);
}
public List <clsAccountUser> getList()
{
List <clsAccountUser> list = new List <clsAccountUser>();
clsAccountUser account;
string sQuery = "SELECT idEmployee, EMPUsername, EMPFirstName, EMPMiddleName, EMPLastName, EMPDateCreated, EMPStatus FROM Employee ";
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
try
{
oConnection.Open();
using (SqlCommand oCommand = new SqlCommand(sQuery, oConnection))
{
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
while (oReader.Read())
{
account = new clsAccountUser();
account.id = oReader.GetInt32(oReader.GetOrdinal("idEmployee"));
account.Username = oReader.GetString(oReader.GetOrdinal("EMPUsername"));
account.firstname = oReader.GetString(oReader.GetOrdinal("EMPFirstName"));
account.middlename = oReader.GetString(oReader.GetOrdinal("EMPMiddleName"));
account.lastname = oReader.GetString(oReader.GetOrdinal("EMPLastName"));
account.datecreated = oReader.GetDateTime(oReader.GetOrdinal("EMPDateCreated"));
account.status = oReader.GetBoolean(oReader.GetOrdinal("EMPStatus"));
list.Add(account);
}
}
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger);
}
}
return(list);
}
public bool delete(clsAccountUser account)
{
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
oConnection.Open();
try
{
using (SqlCommand oCommand = new SqlCommand("DELETE FROM Employee WHERE idEmployee = @idEmployee", oConnection))
{
oCommand.Parameters.AddWithValue("@idEmployee", account.ID);
oCommand.ExecuteNonQuery();
return(true);
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger, ex.Source);
}
}
return(false);
}
