public void runQuery(string queryStatement)
{
DataTableGenerator sql = new DataTableGenerator();
SqlConnection con = new SqlConnection(db.GolfHomieDBConString);
con.Open();
SqlCommand command = new SqlCommand(queryStatement, con);
command.ExecuteNonQuery();
con.Close();
}
public void runQuery(string queryStatement)
{
DataTableGenerator sql = new DataTableGenerator();
SqlConnection con = new SqlConnection(db.GolfHomieDBConString);
con.Open();
SqlCommand command = new SqlCommand(queryStatement, con);
command.ExecuteNonQuery();
con.Close();
}
public JsonResult GetCourses()
{
DataTableGenerator dtGen = new DataTableGenerator();
DataTable coursesDT = new DataTable();
coursesDT = dtGen.GetDataTable(@"SELECT id,coursename,par,blueteeRating,WhiteTeeRating,CourseSlope,AddressID From Courses");
string coursesDTstring = dtGen.ConvertDataTableToJSONString(coursesDT);
return Json(coursesDTstring, JsonRequestBehavior.AllowGet);
}
public JsonResult GetUsers()
{
DataTableGenerator dtGen = new DataTableGenerator();
DataTable usersDT = new DataTable();
usersDT = dtGen.GetDataTable("Select id,fname,lname,email,username from Users");
string usersJSONString = dtGen.ConvertDataTableToJSONString(usersDT);
return Json(usersJSONString, JsonRequestBehavior.AllowGet);
}
public JsonResult CheckLogin(UsersModel user)
{
//use email and password to get full user model
DataTable dt = new DataTableGenerator().GetDataTable("Select * from Users where email = '" + user.email.ToString() + "'and Password = '******'");
if(dt.Rows.Count == 1 )
{
DataRow dr = dt.Rows[0];
UsersModel model = new UsersModel();
model.email = dr["email"].ToString();
model.password = dr["password"].ToString();
model.id = Convert.ToInt32(dr["id"]);
model.fname = dr["fname"].ToString();
model.lname = dr["lname"].ToString();
model.username = dr["username"].ToString();
//Clear Previous SESSION
Session.Clear();
//SET THE GLOBAL SESSION USER INFO
Session["id"] = model.id;
Session["email"] = model.email;
Session["password"] = model.password;
Session["fname"] = model.fname;
Session["lname"] = model.lname;
Session["username"] = model.username;
return Json(model, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new UsersModel(),JsonRequestBehavior.AllowGet);
}
}
public JsonResult GetScores()
{
DataTableGenerator dtGen = new DataTableGenerator();
DataTable scoresDT = new DataTable();
scoresDT = dtGen.GetDataTable(@"
select top 20 scores.id,users.id,scores.courseid,courses.coursename,scores.score,scores.dateplayed,scores.adjustedscore
from scores inner join users
on scores.userid = users.id
inner join courses on courses.id = scores.courseid
where users.id = " + Convert.ToInt32(Session["id"]) + "order by scores.dateplayed DESC");
string scoresJSONString = dtGen.ConvertDataTableToJSONString(scoresDT);
return Json(scoresJSONString, JsonRequestBehavior.AllowGet);
}
public decimal AdjustScore(int score, int courseid)
{
DataTable courseDT = new DataTable();
DataTableGenerator dtGen = new DataTableGenerator();
courseDT = dtGen.GetDataTable("SELECT * FROM Courses where id = " + courseid);
DataRow dr = courseDT.Rows[0];
CoursesModel course = new CoursesModel();
course.id = Convert.ToInt32(dr["id"]);
course.coursename = dr["coursename"].ToString();
course.whiteteerating = Convert.ToDecimal(dr["whiteteerating"]);
course.courseslope = Convert.ToDecimal(dr["courseslope"]);
decimal adjustedScore = (score - course.whiteteerating) * 113 / course.courseslope;
return(adjustedScore);
}
public decimal AdjustScore(int score,int courseid)
{
DataTable courseDT = new DataTable();
DataTableGenerator dtGen = new DataTableGenerator();
courseDT = dtGen.GetDataTable("SELECT * FROM Courses where id = " + courseid);
DataRow dr = courseDT.Rows[0];
CoursesModel course = new CoursesModel();
course.id = Convert.ToInt32(dr["id"]);
course.coursename = dr["coursename"].ToString();
course.whiteteerating = Convert.ToDecimal(dr["whiteteerating"]);
course.courseslope = Convert.ToDecimal(dr["courseslope"]);
decimal adjustedScore = (score - course.whiteteerating) * 113 / course.courseslope;
return adjustedScore;
}
public JsonResult Register(UsersModel newUser)
{
DataTable dt = new DataTableGenerator().GetDataTable(@"Select * from Users where email = '" + newUser.email.ToString() + "'");
if (dt.Rows.Count == 1)
{
return Json(new UsersModel(), JsonRequestBehavior.AllowGet);
}
else
{
UsersModel model = new UsersModel();
SqlTool sql = new SqlTool();
model.fname = newUser.fname;
model.lname = newUser.lname;
model.username = newUser.username;
model.password = newUser.password;
model.email = newUser.email;
sql.runQuery("Insert into Users (fname,lname,email,password,username) select '" + model.fname.ToString() + "','" +
model.lname.ToString() + "','" + model.email.ToString() + "','" + model.password.ToString() + "','" +
model.username + "'");
return Json(model, JsonRequestBehavior.AllowGet);
}
}
