private long SqlGetCustomerInfo(string dbQuery)
{
long temp = 0;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp = reader.GetInt64("TRANS_Cust_ID");
}
dbCon.Close();
}
return(temp);
}
catch
{
dbCon.Close();
return(-1);
}
}
private string SqlGetMovieById(string dbQuery)
{
string temp = null;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp = reader.GetString("MOV_INFO_TITLE");
}
dbCon.Close();
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private int SqlGetMappingId(string dbQuery)
{
int maxKey = 1;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var location = reader.GetOrdinal("MAX(`TRANS2MOV_TRANS_ID`)");
if (!reader.IsDBNull(location))
{
maxKey = reader.GetInt32("MAX(`TRANS2MOV_TRANS_ID`)") + 1;
}
}
dbCon.Close();
}
return(maxKey);
}
catch
{
dbCon.Close();
return(-1);
}
}
private List <Movie2ClassResults> SQLGetPopularList(string dbQuery)
{
var temp = new List <Movie2ClassResults>();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Movie2ClassResults results = new Movie2ClassResults();
results.MovieId = reader.GetInt32("TRANS2MOV_MOV_ID");
results.count = reader.GetInt32("count(TRANS2MOV_MOV_ID)");
temp.Add(results);
results = null;
}
dbCon.Close();
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private List <MovieTitles> SqlGetMovieList(string dbQuery)
{
var temp = new List <MovieTitles>();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var mov = new MovieTitles();
mov.MovieId = reader.GetInt64("MOV_INFO_UNIQ_ID");
mov.Title = reader.GetString("MOV_INFO_TITLE");
mov.ReturnDate = reader.GetString("MOV_STATUS_DATE");
temp.Add(mov);
mov = null;
}
dbCon.Close();
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
public bool UpdateMovieInfo(Movie newMovieInfo)
{
string updateMovieInfoQuery;
if (newMovieInfo.Status == 0)
{
updateMovieInfoQuery = $"UPDATE {DatabaseUtils.Databasename}.movieinfo " +
"SET " +
$"MOV_INFO_TITLE = '{newMovieInfo.Title}', " +
$"MOV_INFO_RELEASE_YEAR = '{newMovieInfo.ReleaseYear}', " +
$"MOV_INFO_GENRE = '{newMovieInfo.Genre}', " +
$"MOV_INFO_UPC = '{newMovieInfo.Upc}', " +
$"MOV_STATUS = '{newMovieInfo.Status}', " +
$"MOV_STATUS_DATE = null " +
$"WHERE MOV_INFO_UNIQ_ID = '{newMovieInfo.MovieId}';";
}
else
{
updateMovieInfoQuery = $"UPDATE {DatabaseUtils.Databasename}.movieinfo " +
"SET " +
$"MOV_INFO_TITLE = '{newMovieInfo.Title}', " +
$"MOV_INFO_RELEASE_YEAR = '{newMovieInfo.ReleaseYear}', " +
$"MOV_INFO_GENRE = '{newMovieInfo.Genre}', " +
$"MOV_INFO_UPC = '{newMovieInfo.Upc}', " +
$"MOV_STATUS = '{newMovieInfo.Status}' " +
$"WHERE MOV_INFO_UNIQ_ID = '{newMovieInfo.MovieId}';";
}
var updateMovie = DatabaseUtils.Instance();
return(updateMovie.MakeDbQuery(updateMovieInfoQuery));
}
public bool MakeDbQuery(string dbQuery)
{
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
try
{
cmd.ExecuteNonQuery();
dbQuery = null;
dbCon.Close();
return(true);
}
catch (Exception)
{
dbQuery = null;
dbCon.Close();
return(false);
}
}
return(false);
}
catch
{
dbCon.Close();
return(false);
}
}
private int SQL_GetLastInsertedId()
{
var dbQuery = "SELECT LAST_INSERT_ID();";
var lastInsertedKey = -1;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
try
{
var reader = cmd.ExecuteReader();
while (reader.Read())
{
lastInsertedKey = reader.GetInt32(0);
}
return(lastInsertedKey);
}
catch (Exception)
{
return(lastInsertedKey);
}
}
return(lastInsertedKey);
}
catch
{
dbCon.Close();
return(-1);
}
}
public bool MakeCustomerInactive(int id)
{
var disableCustomerQuery = $"UPDATE {DatabaseUtils.Databasename}.customers " +
$"SET CUST_Active = 0 " +
$"WHERE CUST_ID = {id}";
var updateCustomer = DatabaseUtils.Instance();
return(updateCustomer.MakeDbQuery(disableCustomerQuery));
}
private Permission SqlGetPermissionById(string dbQuery)
{
var temp = new Permission();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp.EmpPermiss = reader.GetInt32("EMP_Permiss");
temp.EmpCreate = reader.GetBoolean("EMP_Create");
temp.EmpEdit = reader.GetBoolean("EMP_Edit");
temp.EmpDisable = reader.GetBoolean("EMP_Disable");
temp.CustCreate = reader.GetBoolean("CUST_Create");
temp.CustEdit = reader.GetBoolean("CUST_Edit");
temp.CustDisable = reader.GetBoolean("CUST_Disable");
temp.CustSearch = reader.GetBoolean("CUST_Search");
temp.CustViewHist = reader.GetBoolean("CUST_ViewHist");
temp.CustEditHist = reader.GetBoolean("Cust_EditHist");
temp.InvAdd = reader.GetBoolean("INV_Add");
temp.InvEdit = reader.GetBoolean("INV_Edit");
temp.InvDisable = reader.GetBoolean("INV_Disable");
temp.InvRent = reader.GetBoolean("INV_Rent");
temp.InvReturn = reader.GetBoolean("INV_Return");
temp.InvHold = reader.GetBoolean("INV_Hold");
temp.RepOverdue = reader.GetBoolean("REP_Overdue");
temp.RepPopular = reader.GetBoolean("REP_Popular");
temp.RepCheckedOut = reader.GetBoolean("REP_CheckedOut");
temp.RepOnHold = reader.GetBoolean("REP_OnHold");
}
dbCon.Close();
}
if (temp.EmpPermiss == 0)
{
//No Match in the DB
return(null);
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private Customer SqlGetCustomerById(string dbQuery)
{
var temp = new Customer();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp.CustomerId = reader.GetInt32($"CUST_ID");
temp.NameFirst = reader.GetString($"CUST_Name_First");
temp.NameMiddleIn = reader.GetString($"CUST_Name_Middle_In");
temp.NameLast = reader.GetString($"CUST_Name_Last");
temp.AddLine1 = reader.GetString($"CUST_Add_Line1");
var location = reader.GetOrdinal($"CUST_Add_Line2");
if (!reader.IsDBNull(location))
{
temp.AddLine2 = reader.GetString($"CUST_Add_Line2");
}
temp.AddCity = reader.GetString($"CUST_Add_City");
temp.AddState = reader.GetString($"CUST_Add_State");
temp.AddZip = reader.GetInt32($"CUST_Add_Zip");
temp.PhoneNumber = reader.GetString($"CUST_PhoneNumber");
temp.Email = reader.GetString($"CUST_Email");
temp.Newsletter = reader.GetBoolean($"CUST_Newsletter");
temp.AccountBalance = reader.GetInt32($"CUST_AccountBalance");
temp.Active = reader.GetBoolean($"CUST_Active");
}
dbCon.Close();
}
if (temp.CustomerId == 0)
{
//No Match in the DB
return(null);
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
public bool EditEmployeeAccount(EmployeeInfoToShare updatedEmployee)
{
var updateEmployeeInfoQuery = $"UPDATE {DatabaseUtils.Databasename}.employeelist " +
$"SET EMP_Name_First = '{updatedEmployee.FirstName}', " +
$"EMP_Name_Last = '{updatedEmployee.LastName}', " +
$"EMP_Active = '{Convert.ToInt32(updatedEmployee.Active)}', " +
$"EMP_Type = '{updatedEmployee.EmployeeType}', " +
$"EMP_PhoneNumber = '{updatedEmployee.PhoneNumber}' " +
$"WHERE EMP_ID = '{updatedEmployee.EmployeeId}';";
var updateEmployee = DatabaseUtils.Instance();
return(updateEmployee.MakeDbQuery(updateEmployeeInfoQuery));
}
public bool CreateNewUser(TempEmployee temp)
{
if (PasswordUtils.IsPasswordFormatValid(temp.RawPw))
{
Employee newEmployee = temp;
newEmployee.PwHash = PasswordUtils.Hash(temp.RawPw);
var createUserQuery = $"INSERT INTO {DatabaseUtils.Databasename}.employeelist(EMP_Name_First, EMP_Name_Last, EMP_PW_Hash,EMP_Active, EMP_Type, EMP_PhoneNumber) " +
$"VALUES('{newEmployee.FirstName}', '{newEmployee.LastName}','{newEmployee.PwHash}','1','{newEmployee.EmployeeType}',{newEmployee.PhoneNumber});";
var createUser = DatabaseUtils.Instance();
return(createUser.MakeDbQuery(createUserQuery));
}
return(false);
}
public bool EditEmployeePW(Login updatedEmployee)
{
if (PasswordUtils.IsPasswordFormatValid(updatedEmployee.Password))
{
var passwordHash = PasswordUtils.Hash(updatedEmployee.Password);
var updateEmployeeInfoQuery = $"UPDATE {DatabaseUtils.Databasename}.employeelist " +
$"SET EMP_PW_Hash = '{passwordHash}' " +
$"WHERE EMP_ID = '{updatedEmployee.Username}';";
var updateEmployee = DatabaseUtils.Instance();
return(updateEmployee.MakeDbQuery(updateEmployeeInfoQuery));
}
return(false);
}
public bool UpdateCustomer(Customer updatedInfo)
{
string updateCustomerQuery;
if (updatedInfo.AddLine2 == null)
{
updateCustomerQuery = $"UPDATE {DatabaseUtils.Databasename}.customers " +
"SET " +
$"CUST_Name_First = '{updatedInfo.NameFirst}', " +
$"CUST_Name_Middle_In = '{updatedInfo.NameMiddleIn}', " +
$"CUST_Name_Last = '{updatedInfo.NameLast}', " +
$"CUST_Add_Line1 = '{updatedInfo.AddLine1}', " +
$"CUST_Add_City = '{updatedInfo.AddCity}', " +
$"CUST_Add_State = '{updatedInfo.AddState}', " +
$"CUST_Add_Zip = '{updatedInfo.AddZip}', " +
$"CUST_PhoneNumber = '{updatedInfo.PhoneNumber}', " +
$"CUST_Email = '{updatedInfo.Email}', " +
$"CUST_Newsletter = '{Convert.ToInt32(updatedInfo.Newsletter)}', " +
$"CUST_AccountBalance = '{updatedInfo.AccountBalance}', " +
$"CUST_Active = '{Convert.ToInt32(updatedInfo.Active)}' " +
$"WHERE CUST_ID = '{updatedInfo.CustomerId}';";
}
else
{
updateCustomerQuery = $"UPDATE {DatabaseUtils.Databasename}.customers " +
"SET " +
$"CUST_Name_First = '{updatedInfo.NameFirst}', " +
$"CUST_Name_Middle_In = '{updatedInfo.NameMiddleIn}', " +
$"CUST_Name_Last = '{updatedInfo.NameLast}', " +
$"CUST_Add_Line1 = '{updatedInfo.AddLine1}', " +
$"CUST_Add_Line2 = '{updatedInfo.AddLine2}', " +
$"CUST_Add_City = '{updatedInfo.AddCity}', " +
$"CUST_Add_State = '{updatedInfo.AddState}', " +
$"CUST_Add_Zip = '{updatedInfo.AddZip}', " +
$"CUST_PhoneNumber = '{updatedInfo.PhoneNumber}', " +
$"CUST_Email = '{updatedInfo.Email}', " +
$"CUST_Newsletter = '{Convert.ToInt32(updatedInfo.Newsletter)}', " +
$"CUST_AccountBalance = '{updatedInfo.AccountBalance}', " +
$"CUST_Active = '{Convert.ToInt32(updatedInfo.Active)}' " +
$"WHERE CUST_ID = '{updatedInfo.CustomerId}';";
}
var updateEmployee = DatabaseUtils.Instance();
return(updateEmployee.MakeDbQuery(updateCustomerQuery));
}
public List <MovieId> AddMovie(NewMovie toAdd)
{
List <MovieId> addedMovies = new List <MovieId>();
for (int i = 0; i < toAdd.Qty; i++)
{
var addMovieStringQuery = $"INSERT INTO {DatabaseUtils.Databasename}.movieinfo(MOV_INFO_TITLE, MOV_INFO_RELEASE_YEAR, MOV_INFO_GENRE, MOV_INFO_UPC, MOV_STATUS) " +
$"VALUES('{toAdd.Title}', '{toAdd.ReleaseYear}', '{toAdd.Genre}', '{toAdd.Upc}', 0)";
var addMovie = DatabaseUtils.Instance();
MovieId addedMovie = new MovieId();
addedMovie.Id = addMovie.MakeDbQuery(addMovieStringQuery, true);
addedMovies.Add(addedMovie);
}
return(addedMovies);
}
private string SqlGetEmployeeTitle(string dbQuery)
{
string employeeTitle = null;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
employeeTitle = reader.GetString("EMP_TITLE");
}
dbCon.Close();
}
return(employeeTitle);
}
public bool ReturnMovies(MovieList ToReturn)
{
string updateMovieInfoQuery;
foreach (var movie in ToReturn.movieList)
{
updateMovieInfoQuery = $"UPDATE {DatabaseUtils.Databasename}.movieinfo " +
"SET " +
$"MOV_STATUS = '0', " +
$"MOV_STATUS_DATE = null " +
$"WHERE MOV_INFO_UNIQ_ID = '{movie.Id}';";
var updateMovie = DatabaseUtils.Instance();
if (!updateMovie.MakeDbQuery(updateMovieInfoQuery))
{
return(false);
}
}
return(true);
}
public int MakeNewCustomer(Customer toAdd)
{
string createUserQuery;
if (toAdd.AddLine2 == null)
{
createUserQuery =
$"INSERT INTO {DatabaseUtils.Databasename}.customers(CUST_Name_First, CUST_Name_Middle_In, CUST_Name_Last, CUST_Add_Line1, CUST_Add_Line2, CUST_Add_City, CUST_Add_State, CUST_Add_Zip, CUST_PhoneNumber, CUST_Email, CUST_Newsletter, CUST_AccountBalance, CUST_Active ) " +
$"VALUES('{toAdd.NameFirst}', '{toAdd.NameMiddleIn}', '{toAdd.NameLast}', '{toAdd.AddLine1}', null , '{toAdd.AddCity}', '{toAdd.AddState}', '{toAdd.AddZip}', '{toAdd.PhoneNumber}', '{toAdd.Email}', {toAdd.Newsletter}, '{toAdd.AccountBalance}', {toAdd.Active})";
}
else
{
createUserQuery =
$"INSERT INTO {DatabaseUtils.Databasename}.customers(CUST_Name_First, CUST_Name_Middle_In, CUST_Name_Last, CUST_Add_Line1, CUST_Add_Line2, CUST_Add_City, CUST_Add_State, CUST_Add_Zip, CUST_PhoneNumber, CUST_Email, CUST_Newsletter, CUST_AccountBalance, CUST_Active ) " +
$"VALUES('{toAdd.NameFirst}', '{toAdd.NameMiddleIn}', '{toAdd.NameLast}', '{toAdd.AddLine1}', '{toAdd.AddLine2}', '{toAdd.AddCity}', '{toAdd.AddState}', '{toAdd.AddZip}', '{toAdd.PhoneNumber}', '{toAdd.Email}', {toAdd.Newsletter}, '{toAdd.AccountBalance}', {toAdd.Active})";
}
var makeCustomer = DatabaseUtils.Instance();
return(makeCustomer.MakeDbQuery(createUserQuery, true));
}
//SQL Queries go Below!
public Employee SqlGetEmployee(string dbQuery)
{
var temp = new Employee();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp.EmployeeId = reader.GetInt32("EMP_ID");
temp.FirstName = reader.GetString("EMP_Name_First");
temp.LastName = reader.GetString("EMP_Name_Last");
temp.PwHash = reader.GetString("EMP_PW_Hash");
temp.Active = reader.GetBoolean("EMP_Active");
temp.EmployeeType = reader.GetInt32("EMP_Type");
temp.PhoneNumber = reader.GetString("EMP_PhoneNumber");
}
dbCon.Close();
}
if (temp.EmployeeId == 0)
{
//No Match in the DB
return(null);
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private Movie SqlGetMovieById(string dbQuery)
{
var temp = new Movie();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp.MovieId = reader.GetInt64("MOV_INFO_UNIQ_ID");
temp.Title = reader.GetString("MOV_INFO_TITLE");
temp.ReleaseYear = reader.GetString("MOV_INFO_RELEASE_YEAR");
temp.Genre = reader.GetString("MOV_INFO_GENRE");
temp.Upc = reader.GetString("MOV_INFO_UPC");
temp.Status = reader.GetInt32("MOV_STATUS");
}
dbCon.Close();
}
if (temp.MovieId == 0)
{
//No Match in the DB
return(null);
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private List <PopularCustomerInfo> SqlGetCustIDs(string dbQuery, int numberOfResults)
{
var temp = new List <PopularCustomerInfo>();
int numOfEntriesRead = 0;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var curCust = new PopularCustomerInfo();
curCust.customerId = reader.GetInt64($"TRANS_CUST_ID");
curCust.transactionCount = reader.GetInt32($"count");
temp.Add(curCust);
numOfEntriesRead++;
if (numOfEntriesRead >= numberOfResults && numberOfResults != 0)
{
break;
}
}
dbCon.Close();
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
private List <Movie> SqlGetMoviesBySearch(string dbQuery)
{
var temp = new List <Movie>();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var mov = new Movie();
mov.MovieId = reader.GetInt64("MOV_INFO_UNIQ_ID");
mov.Title = reader.GetString("MOV_INFO_TITLE");
mov.ReleaseYear = reader.GetString("MOV_INFO_RELEASE_YEAR");
mov.Genre = reader.GetString("MOV_INFO_GENRE");
mov.Upc = reader.GetString("MOV_INFO_UPC");
mov.Status = reader.GetInt32("MOV_STATUS");
temp.Add(mov);
mov = null;
}
dbCon.Close();
}
return(temp);
}
catch (Exception e)
{
dbCon.Close();
return(null);
}
}
private Customer SqlGetCustomerById(string dbQuery)
{
var temp = new Customer();
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
temp.CustomerId = reader.GetInt32($"CUST_ID");
temp.NameFirst = reader.GetString($"CUST_Name_First");
temp.NameLast = reader.GetString($"CUST_Name_Last");
temp.PhoneNumber = reader.GetString($"CUST_PhoneNumber");
temp.Active = reader.GetBoolean($"CUST_Active");
}
dbCon.Close();
}
if (temp.CustomerId == 0)
{
//No Match in the DB
return(null);
}
return(temp);
}
catch
{
dbCon.Close();
return(null);
}
}
public int MakeDbQuery(string dbQuery, bool getLastKey)
{
var key = -1;
var dbCon = DatabaseUtils.Instance();
dbCon.DatabaseName = DatabaseUtils.Databasename;
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(dbQuery, dbCon.Connection);
try
{
cmd.ExecuteNonQuery();
dbQuery = null;
if (getLastKey)
{
key = SQL_GetLastInsertedId();
}
dbCon.Close();
return(key);
}
catch (Exception)
{
dbQuery = null;
dbCon.Close();
return(key);
}
}
return(key);
}
catch
{
dbCon.Close();
return(-1);
}
}
public long MakeTransaction(TransInput forTrans)
{
Transaction trans = new Transaction();
var AddMov2TransInfo = DatabaseUtils.Instance();
string trans2MovIdQuery = $"SELECT MAX(`TRANS2MOV_TRANS_ID`) FROM transactions2movies;";
int transID = SqlGetMappingId(trans2MovIdQuery);
string addMov2TransInfoQuery =
$"INSERT INTO {DatabaseUtils.Databasename}.transactions2movies (TRANS2MOV_TRANS_ID,TRANS2MOV_MOV_ID) " +
$"VALUES ";
foreach (var movId in forTrans.MovieList)
{
addMov2TransInfoQuery += $"({transID},{movId.Id})";
if (movId.Id != forTrans.MovieList[forTrans.MovieList.Count - 1].Id)
{
addMov2TransInfoQuery += ", ";
}
else
{
addMov2TransInfoQuery += ";";
}
}
if (AddMov2TransInfo.MakeDbQuery(addMov2TransInfoQuery))
{
trans.TransId = transID;
trans.Date = DateTime.Now.ToString("yyyy-MM-dd");
trans.EmpId = forTrans.EmployeeId;
trans.Fees = 0;
trans.FeesPaid = forTrans.LateFeePaid;
trans.PymtCard = forTrans.PaymentCard;
trans.PymtType = forTrans.PaymentType;
trans.RemBalance = 0;
int runningCost = 0;
foreach (var movieCost in forTrans.MovieList)
{
runningCost += movieCost.Cost;
}
trans.TotalPaid = runningCost + trans.FeesPaid;
trans.CustId = forTrans.CustomerId;
string newTransQuery =
$"INSERT INTO {DatabaseUtils.Databasename}.transactions(TRANS_ID, TRANS_Date, TRANS_Employee, TRANS_Fees, TRANS_Fees_Paid, TRANS_Total_Paid, TRANS_Rem_Balance, TRANS_Cust_ID, TRANS_Payment_Type, TRANS_Card_Num) " +
$"VALUES('{trans.TransId}', '{trans.Date}', '{trans.EmpId}', '{trans.Fees}', '{trans.FeesPaid}', '{trans.TotalPaid}', '{trans.RemBalance}', '{trans.CustId}', '{trans.PymtType}', '{trans.PymtCard}');";
if (AddMov2TransInfo.MakeDbQuery(newTransQuery))
{
foreach (var movId in forTrans.MovieList)
{
string updateMovieStatusQuery = $"UPDATE {DatabaseUtils.Databasename}.movieinfo " +
$"SET MOV_STATUS = 1, MOV_RETURN_DATE = \"{movId.DueDate}\" " +
$"WHERE MOV_INFO_UNIQ_ID = {movId.Id};";
if (!AddMov2TransInfo.MakeDbQuery(updateMovieStatusQuery))
{
return(-1);
}
}
}
return(trans.TransId);
}
return(-1);
}
