internal void UpdateStaff(int id, Staff staff)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
string commandText =
@"UPDATE staff
SET fName=@FNAME, lName=@LNAME, username=@USERNAME, password=@PASSWORD, email=@EMAIL, accessLevel=@ACCESSLEVEL
WHERE staffId=@STAFFID";
MySqlCommand command = new MySqlCommand(commandText, conn);
command.Parameters.Add("@FNAME", MySqlDbType.VarChar);;
command.Parameters["@FNAME"].Value = staff.fName;
command.Parameters.Add("@LNAME", MySqlDbType.VarChar);
command.Parameters["@LNAME"].Value = staff.lName;
command.Parameters.Add("@USERNAME", MySqlDbType.VarChar);
command.Parameters["@USERNAME"].Value = staff.username;
command.Parameters.Add("@PASSWORD", MySqlDbType.VarChar);
command.Parameters["@PASSWORD"].Value = staff.password;
command.Parameters.Add("@EMAIL", MySqlDbType.VarChar);
command.Parameters["@EMAIL"].Value = staff.email;
command.Parameters.Add("@ACCESSLEVEL", MySqlDbType.Int32);
command.Parameters["@ACCESSLEVEL"].Value = staff.accessLevel;
command.Parameters.Add("@STAFFID", MySqlDbType.Int32);
command.Parameters["@STAFFID"].Value = id;
command.ExecuteNonQuery();
MySqlConnectionManager.CloseConnection(conn);
}
}
}
internal void CreateStaff(Staff staff)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
string commandText =
@"INSERT INTO staff (fName, lName, username, password, email, accessLevel, creationDate)
VALUES(@FNAME, @LNAME, @USERNAME, @PASSWORD, @EMAIL, @ACCESSLEVEL, @CREATIONDATE)";
MySqlCommand command = new MySqlCommand(commandText, conn);
command.Parameters.Add("@FNAME", MySqlDbType.VarChar);;
command.Parameters["@FNAME"].Value = staff.fName;
command.Parameters.Add("@LNAME", MySqlDbType.VarChar);
command.Parameters["@LNAME"].Value = staff.lName;
command.Parameters.Add("@USERNAME", MySqlDbType.VarChar);
command.Parameters["@USERNAME"].Value = staff.username;
command.Parameters.Add("@PASSWORD", MySqlDbType.VarChar);
command.Parameters["@PASSWORD"].Value = staff.password;
command.Parameters.Add("@EMAIL", MySqlDbType.VarChar);
command.Parameters["@EMAIL"].Value = staff.email;
command.Parameters.Add("@ACCESSLEVEL", MySqlDbType.Int32);
command.Parameters["@ACCESSLEVEL"].Value = staff.accessLevel;
command.Parameters.Add("@CREATIONDATE", MySqlDbType.DateTime);
command.Parameters["@CREATIONDATE"].Value = staff.creationDate;
command.ExecuteNonQuery();
MySqlConnectionManager.CloseConnection(conn);
}
}
}
// RETURNS: One check that matches the given <id>
internal Check GetCheck(int id)
{
var check = new Check();
using (MySqlConnection conn = new MySqlConnection(_connectionString))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand("SELECT * FROM checkDB.`chekue` AS checks WHERE checks.idCheck = " + Convert.ToString(id), conn))
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
check = new Check()
{
IdCheck = reader.GetInt32("idCheck"),
IdAccount = reader.GetInt32("idAccount"),
IdStore = reader.GetInt32("idStore"),
CheckNum = reader.GetInt32("checkNum"),
Amount = reader.GetFloat("amount"),
DateWritten = reader.GetDateTime("dateWritten"),
AmountPaid = (!reader.IsDBNull(reader.GetOrdinal("amountPaid"))) ? reader.GetFloat("amountPaid") : -1,
PaidDate = (!reader.IsDBNull(reader.GetOrdinal("paidDate"))) ? reader.GetDateTime("paidDate") : new DateTime(0)
};
}
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(check);
}
internal List <Check> GetAll()
{
var checks = new List <Check>();
using (MySqlConnection conn = new MySqlConnection(_connectionString))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand("SELECT * FROM checkDB.`chekue`", conn))
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
checks.Add(new Check()
{
IdCheck = reader.GetInt32("idCheck"),
IdAccount = reader.GetInt32("idAccount"),
IdStore = reader.GetInt32("idStore"),
CheckNum = reader.GetInt32("checkNum"),
Amount = reader.GetFloat("amount"),
//DateWritten = reader.GetDateTime("dateWritten"),
//AmountPaid = (!reader.IsDBNull(reader.GetOrdinal("amountPaid"))) ? reader.GetFloat("amountPaid") : -1,
//PaidDate = (!reader.IsDBNull(reader.GetOrdinal("paidDate"))) ? reader.GetDateTime("paidDate") : new DateTime(0)
});
}
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(checks);
}
internal IEnumerable <CheckDisplayRow> RetrieveAllCheckDisplayRows()
{
List <CheckDisplayRow> rows = new List <CheckDisplayRow>();
CheckDisplayRow row = null;
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand("SELECT * FROM checkdb.checkdisplayrow", conn))
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
row = new CheckDisplayRow()
{
idCheck = reader.GetInt32("idCheck"),
idAccount = reader.GetInt32("idAccount"),
idStore = reader.GetInt32("idStore"),
checkNum = reader.GetInt32("checkNum"),
amount = reader.GetFloat("amount"),
dateWritten = reader.GetDateTime("dateWritten"),
//amountPaid = reader.GetFloat("amountPaid"),//nullable
//paidDate = reader.GetDateTime("paidDate"),//nullable
amountPaid = (!reader.IsDBNull(reader.GetOrdinal("amountPaid"))) ? reader.GetFloat("amountPaid") : -1,
paidDate = (!reader.IsDBNull(reader.GetOrdinal("paidDate"))) ? reader.GetDateTime("paidDate") : new DateTime(0),
routingNum = reader.GetInt32("routingNum"),
accountNum = reader.GetInt32("accountNum"),
fName = reader.GetString("fName"),
lName = reader.GetString("lName"),
//fName2 = reader.GetString("fName2"),//nullable
//lName2 = reader.GetString("lName2"),//
fName2 = (!reader.IsDBNull(reader.GetOrdinal("fName2"))) ? reader.GetString("fName2") : "",
lName2 = (!reader.IsDBNull(reader.GetOrdinal("lName2"))) ? reader.GetString("lName2") : "",
address = reader.GetString("address"),
city = reader.GetString("city"),
state = reader.GetString("state"),
zipcode = reader.GetInt32("zipcode"),
//phoneNum = reader.GetString("phoneNum"),//nullable
phoneNum = (!reader.IsDBNull(reader.GetOrdinal("phoneNum"))) ? reader.GetString("phoneNum") : "",
bName = reader.GetString("bName"),
bAddress = reader.GetString("bAddress"),
bCity = reader.GetString("bCity"),
bState = reader.GetString("bState"),
bZipcode = reader.GetInt32("bZipcode"),
idStoreGroup = reader.GetInt32("idStoreGroup"),
sName = reader.GetString("sName"),
sAddress = reader.GetString("sAddress"),
sCity = reader.GetString("sCity"),
sState = reader.GetString("sState"),
sZipcode = reader.GetInt32("sZipcode")
};
rows.Add(row);
}
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(rows);
}
internal void UpdateCheckDisplayRow(int id, CheckDisplayRow row)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
string commandText =
@"UPDATE chekue
SET idAccount=@IDACCOUNT, idStore=@IDSTORE, checkNum=@CHECKNUM, amount=@AMOUNT, dateWritten=@DATEWRITTEN{AMOUNTPAID}{PAIDDATE}
WHERE idCheck=@IDCHECK";
if (row.amountPaid != -1)
{
commandText = commandText.Replace("{AMOUNTPAID}", ", amountPaid=@AMOUNTPAID");
}
else
{
commandText = commandText.Replace("{AMOUNTPAID}", "");
}
if (row.paidDate != null && row.paidDate != new DateTime(0))
{
commandText = commandText.Replace("{PAIDDATE}", ", paidDate=@PAIDDATE");
}
else
{
commandText = commandText.Replace("{PAIDDATE}", "");
}
MySqlCommand command = new MySqlCommand(commandText, conn);
//add and set all the parameters here. Some hefty crying must first occur before I implement this....
if (row.amountPaid != -1)
{
command.Parameters.Add("@AMOUNTPAID", MySqlDbType.Float);
command.Parameters["@AMOUNTPAID"].Value = row.amountPaid;
}
if (row.paidDate != null && row.paidDate != new DateTime(0))
{
command.Parameters.Add("@PAIDDATE", MySqlDbType.DateTime);
command.Parameters["@PAIDDATE"].Value = row.paidDate;
}
command.Parameters.Add("@IDACCOUNT", MySqlDbType.Int32);
command.Parameters["@IDACCOUNT"].Value = row.idAccount;
command.Parameters.Add("@IDSTORE", MySqlDbType.Int32);
command.Parameters["@IDSTORE"].Value = row.idStore;
command.Parameters.Add("@CHECKNUM", MySqlDbType.Int32);
command.Parameters["@CHECKNUM"].Value = row.checkNum;
command.Parameters.Add("@AMOUNT", MySqlDbType.Float);
command.Parameters["@AMOUNT"].Value = row.amount;
command.Parameters.Add("@DATEWRITTEN", MySqlDbType.DateTime);
command.Parameters["@DATEWRITTEN"].Value = row.dateWritten;
command.Parameters.Add("@IDCHECK", MySqlDbType.Int32);
command.Parameters["@IDCHECK"].Value = id;
command.ExecuteNonQuery();
MySqlConnectionManager.CloseConnection(conn);
}
}
}
internal void DeleteStaff(int id)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand("DELETE FROM checkDB.`staff` WHERE staff.idStaff = " + Convert.ToString(id), conn))
{
command.ExecuteNonQuery();
}
MySqlConnectionManager.CloseConnection(conn);
}
}
}
internal void CreateCheckDisplayRow(CheckDisplayRow row)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
string commandtext = "CALL CreateCheck(@STORENUM,@BANKNAME,@RNUM,@ANUM,@CNUM,@FIRSTNAME,@LASTNAME,@FIRSTNAME2,@LASTNAME2,@INADDRESS,@INCITY,@INSTATE,@INZIPCODE,@PHONE,@CHECKAMOUNT,@CHECKDATE)";
using (MySqlCommand command = new MySqlCommand(commandtext, conn))
{
command.Parameters.Add("@STORENUM", MySqlDbType.Int32);
command.Parameters["@STORENUM"].Value = row.idStore;
command.Parameters.Add("@BANKNAME", MySqlDbType.VarChar);
command.Parameters["@BANKNAME"].Value = row.bName;
command.Parameters.Add("@ANUM", MySqlDbType.Int32);
command.Parameters["@ANUM"].Value = row.idAccount;
command.Parameters.Add("@RNUM", MySqlDbType.Int32);
command.Parameters["@RNUM"].Value = row.routingNum;
command.Parameters.Add("@CNUM", MySqlDbType.Int32);
command.Parameters["@CNUM"].Value = row.checkNum;
command.Parameters.Add("@FIRSTNAME", MySqlDbType.VarChar);
command.Parameters["@FIRSTNAME"].Value = row.fName;
command.Parameters.Add("@LASTNAME", MySqlDbType.VarChar);
command.Parameters["@LASTNAME"].Value = row.lName;
command.Parameters.Add("@FIRSTNAME2", MySqlDbType.VarChar);
command.Parameters["@FIRSTNAME2"].Value = (row.fName2 != null && row.fName2 != "") ? row.fName2 : null;
command.Parameters.Add("@LASTNAME2", MySqlDbType.VarChar);
command.Parameters["@LASTNAME2"].Value = (row.lName2 != null && row.lName2 != "") ? row.lName2 : null;
command.Parameters.Add("@INADDRESS", MySqlDbType.VarChar);
command.Parameters["@INADDRESS"].Value = row.address;
command.Parameters.Add("@INCITY", MySqlDbType.VarChar);
command.Parameters["@INCITY"].Value = row.city;
command.Parameters.Add("@INSTATE", MySqlDbType.VarChar);
command.Parameters["@INSTATE"].Value = row.state;
command.Parameters.Add("@INZIPCODE", MySqlDbType.Int32);
command.Parameters["@INZIPCODE"].Value = row.zipcode;
command.Parameters.Add("@PHONE", MySqlDbType.VarChar);
command.Parameters["@PHONE"].Value = (row.phoneNum != null && row.phoneNum != "") ? row.phoneNum : null;
command.Parameters.Add("@CHECKAMOUNT", MySqlDbType.Float);
command.Parameters["@CHECKAMOUNT"].Value = row.amount;
command.Parameters.Add("@CHECKDATE", MySqlDbType.DateTime);
command.Parameters["@CHECKDATE"].Value = row.dateWritten;
command.ExecuteNonQuery();
}
MySqlConnectionManager.CloseConnection(conn);
}
}
}
// FUNCTION: Adds a new check to the database
// RETURNS: Returns the check added to confirm that it was added
internal Check AddCheck(Check check)
{
string commandstring = "INSERT INTO checkDB.`chekue` AS checks (idCheck, idAccount, idStore, checkNum, amount, dateWritten) VALUES ({VALUESTRING})";
string valuestring = "" + check.IdCheck + "," + check.IdAccount + "," + check.IdStore + "," + check.CheckNum + "," + check.Amount + "," + check.DateWritten.ToString("yyyy-MM-dd");
commandstring = commandstring.Replace("{VALUESTRING}", valuestring);
using (MySqlConnection conn = new MySqlConnection(_connectionString))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand(commandstring, conn))
{
command.ExecuteNonQuery();
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(check);
}
internal IEnumerable <Staff> RetrieveAllStaff()
{
var staff = new List <Staff>();
var staffMember = new Staff();
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (MySqlCommand command = new MySqlCommand("SELECT * FROM checkDB.`staff`", conn))
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
staffMember = new Staff()
{
/*IdCheck = reader.GetInt32("idCheck"),
* IdAccount = reader.GetInt32("idAccount"),
* IdStore = reader.GetInt32("idStore"),
* CheckNum = reader.GetInt32("checkNum"),
* Amount = reader.GetFloat("amount"),
* DateWritten = reader.GetDateTime("dateWritten"),
* AmountPaid = (!reader.IsDBNull(reader.GetOrdinal("amountPaid"))) ? reader.GetFloat("amountPaid") : -1,
* PaidDate = (!reader.IsDBNull(reader.GetOrdinal("paidDate"))) ? reader.GetDateTime("paidDate") : new DateTime(0)*/
idStaff = reader.GetInt32("idStaff"),
fName = reader.GetString("fName"),
lName = reader.GetString("lName"),
username = reader.GetString("username"),
password = reader.GetString("password"),
email = reader.GetString("email"),
accessLevel = reader.GetInt32("accessLevel"),
creationDate = reader.GetDateTime("creationDate")
};
staff.Add(staffMember);
}
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(staff);
}
internal IEnumerable <Store> GetAll()
{
var stores = new List <Store>();
using (var conn = new MySqlConnection(_connectionString))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
using (var command = new MySqlCommand("SELECT * FROM checkDB.`store`", conn))
{
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
stores.Add(new Store()
{
IdStore = reader.GetInt32("idStore"),
IdStoreGroup = reader.GetInt32("idStoreGroup"),
SName = reader.GetString("sName"),
Address = reader.GetString("address"),
City = reader.GetString("city"),
State = reader.GetString("state"),
Zipcode = reader.GetInt32("zipcode")
//IdCheck = reader.GetInt32("idCheck"),
//IdAccount = reader.GetInt32("idAccount"),
//IdStore = reader.GetInt32("idStore"),
//CheckNum = reader.GetInt32("checkNum"),
//Amount = reader.GetFloat("amount"),
//DateWritten = reader.GetDateTime("dateWritten"),
//AmountPaid = (!reader.IsDBNull(reader.GetOrdinal("amountPaid"))) ? reader.GetFloat("amountPaid") : -1,
//PaidDate = (!reader.IsDBNull(reader.GetOrdinal("paidDate"))) ? reader.GetDateTime("paidDate") : new DateTime(0)
});
}
}
MySqlConnectionManager.CloseConnection(conn);
}
}
return(stores);
}