using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace OrionGames
{
public partial class OrionGames : Form
{
SqlConnection sql_connection;
SqlDataReader myReader;
public OrionGames()
{
InitializeComponent();
//Connect to the Orion_Games database.
String connection_string = "Data Source=localhost; database=Orion_Games; Integrated Security = true; connection timeout=15;";
sql_connection = new SqlConnection(connection_string);
sql_connection.Open();
}
//This method checks that the string argument is comprised only of numbers.
public bool isNumeric(String s)
{
Regex myRegex = new Regex("^[0-9]+$");
if (myRegex.IsMatch(s))
return true;
return false;
}
private void btnHireNew_Click(object sender, EventArgs e)
{
textHireMemID.ReadOnly = false;
txtHireGmeID.ReadOnly = false;
dateHireDate.Enabled = true;
listHires.Enabled = true;
btnHireAddGme.Enabled = true;
btnHireSave.Enabled = true;
textHireMemID.Text = "";
txtHireGmeID.Text = "";
listHires.Items.Clear();
}
private void btnHireAddGme_Click(object sender, EventArgs e)
{
String[] game = new String[2];
String game_id = txtHireGmeID.Text;
SqlCommand find_game;
if (listHires.Items.Count >= 1 && listHires.FindItemWithText(game_id,true,0) != null)
{
MessageBox.Show("That game is already in the list");
return;
}
//Checks the validity of the game id that was entered. (Is it a number?, IS IT ALREADY IN THE LIST?)
if (isNumeric(game_id))
{
find_game = new SqlCommand("Select gid, gtitle, gstatus from games where gid = " + game_id, sql_connection);
myReader = find_game.ExecuteReader();
}
else
{
MessageBox.Show("Invalid Game ID");
return;
}
//There was a resulting row from the SQL query.
if (myReader.HasRows)
{
myReader.Read();
if (myReader[2].ToString() == "out")
{
MessageBox.Show("The Game is currently being hired by another member!");
myReader.Close();
return;
}
game[0] = myReader[0].ToString();
game[1] = myReader[1].ToString();
listHires.Items.Add(new ListViewItem(game));
txtHireGmeID.Text = "";
}
else
MessageBox.Show("The Game with an ID of " + game_id + " was not found!");
myReader.Close();
}
private void OrionGames_FormClosed(object sender, FormClosedEventArgs e)
{
//Closes connection to SQL server
sql_connection.Close();
}
//Takes a string of the form DD/MM/YYYY and returns a DateTime object.
public DateTime stringToDateTime(String date_string)
{
int dob_day = Convert.ToInt32(date_string.Substring(0, 2));
int dob_month = Convert.ToInt32(date_string.Substring(3, 2));
int dob_year = Convert.ToInt32(date_string.Substring(6, 4));
DateTime dob_dt = new DateTime(dob_year, dob_month, dob_day);
return dob_dt;
}
//Calculates members age
public int getMembersAge(String member_id)
{
int age = 0;
SqlCommand getDOB = new SqlCommand("select mdob from member where mid = " + member_id,sql_connection);
myReader = getDOB.ExecuteReader();
if (myReader.HasRows)
{
myReader.Read();
DateTime dob_dt = stringToDateTime(myReader[0].ToString());
// get the difference in years
age = DateTime.Now.Year - dob_dt.Year;
// subtract a year if the member has not yet had a birthday this year.
if (DateTime.Now.Month < dob_dt.Month || (DateTime.Now.Month == dob_dt.Month && DateTime.Now.Day < dob_dt.Day))
age--;
MessageBox.Show("The Members Age is : " + age);
}
myReader.Close();
return age;
}
//Takes a game classification and a member age and calculates whether the member is old enough to hire the game.
public bool gameIsAppropriate(String classification, int age)
{
const int R18 = 18;
const int M = 13;
switch (classification)
{
case "R18":
return age >= R18;
case "M":
return age >= M;
default:
return true;
}
}
//Searches for a member and returns true if it finds the member or false if not.
public bool memberExists(String member_id)
{
SqlCommand find_member = new SqlCommand("select * from member where mid = " + member_id, sql_connection);
myReader = find_member.ExecuteReader();
if (myReader.HasRows)
{
myReader.Close();
return true;
}
myReader.Close();
return false;
}
private void btnHireSave_Click(object sender, EventArgs e)
{
//Hire SqlParameters
SqlParameter hissuedate = new SqlParameter("@hissuedate",SqlDbType.Char);
SqlParameter hduedate = new SqlParameter("@hduedate",SqlDbType.Char);
SqlParameter mid = new SqlParameter("@mid",SqlDbType.Int);
SqlParameter identity = new SqlParameter("@identity", SqlDbType.Int); //Output Parameter
identity.Direction = ParameterDirection.Output;
//Includes SqlParameters
SqlParameter hid = new SqlParameter("@hid", SqlDbType.Int);
SqlParameter gid = new SqlParameter("@gid", SqlDbType.Int);
SqlParameter irating = new SqlParameter("@irating", SqlDbType.Int);
SqlParameter icomment = new SqlParameter("@icomment", SqlDbType.VarChar);
int member_age;
int games_hired; //Games actually hired. (User was old enough to hire em)
int num_inappropriate = 0;
double total_charge; //How much to charge member for the hire.
String member_id = textHireMemID.Text;
//Check that the text in textHireMemID only contains numbers.
if (!isNumeric(member_id))
{
MessageBox.Show("Invalid Member ID","Hire Not Saved!");
return;
}
string date_string = dateHireDate.Value.ToShortDateString().PadLeft(10,'0');
string due_date_string = dateHireDate.Value.AddDays(7).ToShortDateString().PadLeft(10,'0');
//Does The Member Exist?
if (!memberExists(member_id))
{
MessageBox.Show("There is no member with an ID of " + member_id,"Hire Not Saved!");
return;
}
//First we see what games the user is and isn't allowed hiring.
//Calculate Age
member_age = getMembersAge(member_id);
//Remove games that the member is too young to play.
foreach (ListViewItem lvi in listHires.Items)
{
SqlCommand get_classification = new SqlCommand("select gclas from games where gid = " + lvi.SubItems[0].Text, sql_connection);
myReader = get_classification.ExecuteReader();
myReader.Read();
if (!gameIsAppropriate(myReader[0].ToString(), member_age))
{
MessageBox.Show("The Member is not old enough to hire " + lvi.SubItems[1].Text,"Game Not Included In Hire");
listHires.Items.Remove(lvi);
num_inappropriate++;
}
myReader.Close();
}
games_hired = listHires.Items.Count;
total_charge = games_hired * 8.00;
if (games_hired > 0)
{
//After removing the inappropriate games, we have some left over.
try
{
hissuedate.Value = date_string;
hduedate.Value = due_date_string;
mid.Value = Convert.ToInt32(member_id);
SqlCommand hire_insert = new SqlCommand("insert into hire values (@hissuedate,@hduedate,@mid) SET @identity = SCOPE_IDENTITY()", sql_connection);
hire_insert.Parameters.Add(hissuedate);
hire_insert.Parameters.Add(hduedate);
hire_insert.Parameters.Add(mid);
hire_insert.Parameters.Add(identity);
hire_insert.ExecuteNonQuery();
//We need to make an includes row entry for every game in the hire.
SqlCommand includes_insert = new SqlCommand("insert into includes values (@hid,@gid,@irating,@icomment)", sql_connection);
includes_insert.Parameters.Add(hid);
includes_insert.Parameters.Add(gid);
includes_insert.Parameters.Add(irating);
includes_insert.Parameters.Add(icomment);
foreach (ListViewItem lvi in listHires.Items)
{
hid.Value = (int)identity.Value;
gid.Value = lvi.SubItems[0].Text;
irating.Value = DBNull.Value;
icomment.Value = DBNull.Value;
includes_insert.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
txtActionToTake.Text = "Charge the member " + total_charge.ToString("C") + ". Only give the member the " + games_hired.ToString() + " games listed above.";
btnHireSave.Enabled = false;
textHireMemID.ReadOnly = true;
txtHireGmeID.ReadOnly = true;
btnHireAddGme.Enabled = false;
dateHireDate.Enabled = false;
}
else
{
MessageBox.Show("There are no games to be included in the hire.", "Hire Not Saved!");
return;
}
}
private void btnReturnNew_Click(object sender, EventArgs e)
{
txtReturnComment.ReadOnly = false;
txtReturnGmeID.ReadOnly = false;
txtReturnMemID.ReadOnly = false;
dateReturnDate.Enabled = true;
cboRating.Enabled = true;
btnReturnAddGme.Enabled = true;
listReturns.Enabled = true;
btnReturnSave.Enabled = true;
}
private void btnReturnAddGme_Click(object sender, EventArgs e)
{
String game_id = txtReturnGmeID.Text;
String[] game = new String[4];
SqlCommand find_game;
if (listReturns.Items.Count >= 1 && listReturns.FindItemWithText(game_id, true, 0) != null)
{
MessageBox.Show("That game is already in the list!");
return;
}
//Check that a game with this ID exists.
if (isNumeric(game_id))
{
find_game = new SqlCommand("Select gid, gtitle, gstatus from games where gid = " + game_id, sql_connection);
myReader = find_game.ExecuteReader();
}
else
{
MessageBox.Show("Invalid Game ID");
return;
}
if (cboRating.SelectedIndex == -1)
{
MessageBox.Show("Choose A Rating!");
myReader.Close();
return;
}
//There was a resulting row from the SQL query.
if (myReader.HasRows)
{
myReader.Read();
if (myReader[2].ToString() == "in")
{
MessageBox.Show("The Game is not currently being hired!");
myReader.Close();
return;
}
game[0] = myReader[0].ToString();
game[1] = myReader[1].ToString();
game[2] = (String)cboRating.Items[cboRating.SelectedIndex];
game[3] = txtReturnComment.Text;
listReturns.Items.Add(new ListViewItem(game));
txtReturnGmeID.Text = "";
cboRating.SelectedIndex = -1;
txtReturnComment.Text = "";
}
else
MessageBox.Show("The Game with an ID of " + game_id + " was not found!");
myReader.Close();
}
private void btnReturnSave_Click(object sender, EventArgs e)
{
int num_overdue = 0;
double total_charge;
String member_id = txtReturnMemID.Text;
//The member_id supplied is only comprised of numbers?
if (!isNumeric(member_id))
{
MessageBox.Show("Invalid Member ID", "Hire Not Saved!");
return;
}
//Does The Member Exist?
if (!memberExists(member_id))
{
MessageBox.Show("There is no member with an ID of " + member_id, "Return Not Saved!");
return;
}
int games_returned = listReturns.Items.Count;
if (games_returned > 0)
{
foreach (ListViewItem lvi in listReturns.Items)
{
String hire_id;
//Get the number of the hire that contains the particular game we are looking at.
SqlCommand get_hire = new SqlCommand("select max(hid) from includes where gid = " + lvi.SubItems[0].Text, sql_connection);
myReader = get_hire.ExecuteReader();
myReader.Read();
hire_id = myReader[0].ToString();
myReader.Close();
//Is the game overdue?
//Get the due date.
SqlCommand get_due_date = new SqlCommand("select hduedate from hire where hid = " + hire_id, sql_connection);
myReader = get_due_date.ExecuteReader();
myReader.Read();
DateTime due_date = stringToDateTime(myReader[0].ToString());
myReader.Close();
//Compare the due date with the current date.
if (due_date.Date.CompareTo(dateReturnDate.Value.Date) < 0)
{
//This game is overdue!
num_overdue ++;
MessageBox.Show("This Game is Overdue!");
}
//Update the includes row entry with the new rating/comment.
SqlParameter irating = new SqlParameter("@irating", SqlDbType.Int);
SqlParameter icomment = new SqlParameter("@icomment", SqlDbType.VarChar);
SqlParameter hid = new SqlParameter("@hid", SqlDbType.Int);
SqlParameter gid = new SqlParameter("@gid", SqlDbType.Int);
irating.Value = Convert.ToInt32(lvi.SubItems[2].Text);
icomment.Value = lvi.SubItems[3].Text;
hid.Value = hire_id;
gid.Value = Convert.ToInt32(lvi.SubItems[0].Text);
SqlCommand update_includes = new SqlCommand("update includes set irating = @irating, icomment = @icomment where hid = @hid and gid = @gid",sql_connection);
update_includes.Parameters.Add(irating);
update_includes.Parameters.Add(icomment);
update_includes.Parameters.Add(hid);
update_includes.Parameters.Add(gid);
update_includes.ExecuteNonQuery();
}
if (num_overdue > 0)
{
total_charge = num_overdue * 2.00;
txtActionToTake.Text = "Charge member " + total_charge.ToString("C") + " for returning " + num_overdue.ToString() + " overdue games.";
//Update Members Overdue Counter (mnum_overdue)
SqlParameter mid = new SqlParameter("@mid", SqlDbType.Int);
mid.Value = Convert.ToInt32(member_id);
SqlCommand update_overdue = new SqlCommand("update member set mnum_overdue = mnum_overdue + " + num_overdue.ToString() + " where mid = @mid", sql_connection);
update_overdue.Parameters.Add(mid);
update_overdue.ExecuteNonQuery();
}
else
txtActionToTake.Text = "There were no overdue games returned. Give the member a pat on the back!";
btnReturnSave.Enabled = false;
btnReturnAddGme.Enabled = false;
dateReturnDate.Enabled = false;
cboRating.Enabled = false;
txtReturnComment.ReadOnly = true;
txtReturnGmeID.ReadOnly = true;
txtReturnMemID.ReadOnly = true;
txtReturnComment.ReadOnly = true;
listReturns.Items.Clear();
listReturns.Enabled = false;
}
else
{
MessageBox.Show("There are no games to be returned!", "Return Not Saved!");
return;
}
}
}
}