public List<Results> GetListResults()
{
List<Results> list = new List<Results>();
Results ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT r.*,f.*,t.Name as teamA,tb.Name as teamB,fl.FieldName FROM Results r inner join fixtures f on r.FixtureId = f.FieldsId inner join Teams t on f.TeamIdA = t.TeamsId inner join Teams tb on f.TeamIdB =tb.TeamsId inner join Fields fl on f.FieldsId =fl.FieldsId", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Results();
ins.ResultsId = Convert.ToInt32(drI["ResultsId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.FixturesID = Convert.ToInt32(drI["FixtureId"]);
ins.fixtures = drI["teamA"].ToString()+" "+drI["PiontsA"].ToString() + " VS " + drI["teamB"].ToString()+" "+drI["PointsB"].ToString();
ins.field = drI["FieldName"].ToString();
ins.PointsA = drI["PointsA"].ToString();
ins.PointsB = drI["PointsB"].ToString();
ins.TimeInMatch = drI["TimeInMatch"].ToString();
ins.Comentry = drI["Comentry"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public ActionResult _UpdateResults(Results ins)
{
//...ViewData...
ins.ClientId = Convert.ToInt32(HttpContext.Session["ClientId"]);
Results o = ResRep.GetResults(ins.ResultsId);
Results ins2 = ResRep.UpdateResults(ins,o.FixturesID);
//...Notify...
string regIds = AppRep.GetAllRegIds(ins.ClientId);
if (!regIds.Equals(""))
{
comrep.NewUpdateData(regIds, "CMD_EDITResults", ins2.ResultsId.ToString());
}
//...Repopulate Grid...
List<Results> lst = new List<Results>();
lst = ResRep.GetListResults(Convert.ToInt32(HttpContext.Session["ClientId"]));
return View(new GridModel(lst));
}
public ActionResult _InsertResults(Results ins)
{
ins.ClientId = Convert.ToInt32(HttpContext.Session["ClientId"]);
//...Insert into Database...
if(ins.Comentry == null)
{
ins.Comentry = "No Commentry";
}
Results ins2 = ResRep.InsertResults(ins);
//...Notify...
string regIds = AppRep.GetAllRegIds(ins.ClientId);
List<string> reg = AppRep.GetAllRegIdsList(ins.ClientId);
if (!regIds.Equals(""))
{
// comrep.NewUpdateData(regIds, "CMD_NEWResultWithID", ins2.ResultsId.ToString());
comrep.NewsyncData(regIds, "CMD_NEWResults", reg, ins.ClientId);
}
//...Facebook...
string access_token = FacebookPost.GetAccessToken(ins.ClientId);
if (!access_token.Equals(""))
{
string page_id = FacebookPost.GetPageId(ins.ClientId);
if (!page_id.Equals(""))
{
FacebookPost.Post(access_token, page_id, "Results Update: " + ins.Comentry);
}
}
//...Repopulate Grid...
List<Results> lst = new List<Results>();
lst = ResRep.GetListResults(Convert.ToInt32(HttpContext.Session["ClientId"]));
return View(new GridModel(lst));
}
public List<Results> GetListResults(int ClientId)
{
List<Results> list = new List<Results>();
Results ins;
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("select r.*,s.Schoolabbreviation as teamA,sb.Schoolabbreviation as teamB,t.Age as AgeA,tb.Age as AgeB,t.Ranks as RankA,tb.Ranks as RankB,fd.FieldName from Results r inner join fixtures f on r.FixtureId = f.FixturesId inner join Teams t on f.TeamIdA = t.TeamsId inner join Teams tb on f.TeamIdB = tb.TeamsId inner join Fields fd on f.FieldsId = fd.FieldsId inner join Schools s on t.SchoolId = s.SchoolId inner join Schools sb on tb.SchoolId = sb.SchoolId where r.ClientId ='" + ClientId + "' ORDER BY ResultsId DESC", con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins = new Results();
ins.ResultsId = Convert.ToInt32(drI["ResultsId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.FixturesID = Convert.ToInt32(drI["FixtureId"]);
ins.TeamA = drI["teamA"].ToString()+" "+drI["AgeA"].ToString()+"/"+drI["RankA"].ToString();
ins.TeamB = drI["teamB"].ToString() + " " + drI["AgeB"].ToString() + "/" + drI["RankB"].ToString();
ins.fixtures = ins.TeamA + " VS " + ins.TeamB;
ins.field = drI["FieldName"].ToString();
ins.PointsA = drI["PointsA"].ToString();
ins.PointsB = drI["PointsB"].ToString();
ins.TimeInMatch = drI["TimeInMatch"].ToString();
ins.Comentry = drI["Comentry"].ToString();
list.Add(ins);
}
}
drI.Close();
con.Close();
return list;
}
public Results UpdateResults(Results ins, int fixtureid)
{
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
//...Update Picture...
//...Update Record...
cmdI.Parameters.Clear();
cmdI.CommandText = "f_Admin_Update_Results";
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@ResultsId", ins.ResultsId);
cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId);
cmdI.Parameters.AddWithValue("@PointsA", ins.PointsA);
cmdI.Parameters.AddWithValue("@PointsB", ins.PointsB);
cmdI.Parameters.AddWithValue("@FixturesID", fixtureid);
cmdI.Parameters.AddWithValue("@Comentry", ins.Comentry);
cmdI.Parameters.AddWithValue("@TimeInMatch", ins.TimeInMatch);
cmdI.ExecuteNonQuery();
cmdI.Connection.Close();
return ins;
}
public Results InsertResults(Results ins)
{
//...Get User and Date Data...
string strTrx = "Insert_Results";
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
//...Command Interface...
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx;
trx = con.BeginTransaction(strTrx);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
//...Insert Picture...
//...Insert Record...
cmdI.Parameters.Clear();
cmdI.CommandText = "f_Admin_Insert_Results";
//cmdI.Connection.Open();
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@ClientId", ins.ClientId);
cmdI.Parameters.AddWithValue("@PointsA", ins.PointsA);
cmdI.Parameters.AddWithValue("@PointsB", ins.PointsB);
cmdI.Parameters.AddWithValue("@FixtrureId", ins.FixturesID);
cmdI.Parameters.AddWithValue("@Comentry", ins.Comentry);
cmdI.Parameters.AddWithValue("@TimeInMatch", ins.TimeInMatch);
//...Return new ID...
ins.ResultsId = (int)cmdI.ExecuteScalar();
//...Commit Transaction...
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null) trx.Rollback();
//...Save Error to Log...
Functions func = new Functions();
func.LogError(ex.ToString());
}
finally
{
//...Check for close and respond accordingly..
if (con.State != ConnectionState.Closed)
{
con.Close();
}
//...Clean up...
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return ins;
}
public Results GetResults(int ResultsId)
{
Results ins = new Results();
//...Database Connection...
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI;
//...SQL Commands...
cmdI = new SqlCommand("SELECT * FROM Results WHERE ResultsId =" + ResultsId, con);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
//...Retrieve Data...
if (drI.HasRows)
{
while (drI.Read())
{
ins.ResultsId = Convert.ToInt32(drI["ResultsId"]);
ins.ClientId = Convert.ToInt32(drI["ClientId"]);
ins.FixturesID = Convert.ToInt32(drI["FixtureId"]);
ins.PointsA = drI["PointsA"].ToString();
ins.PointsB = drI["PointsB"].ToString();
ins.TimeInMatch = drI["TimeInMatch"].ToString();
ins.Comentry = drI["Comentry"].ToString();
}
}
drI.Close();
con.Close();
return ins;
}