public int EditContent(Change_Request newobj)
{
var result = 0;
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//MYSQL database set PK content_id auto increment
var sqlcmd = @"UPDATE content SET Content_Name = @contentname,Year = @year,
Update_User_Id = @userid,
Last_Update_Date_Time = @updatetime,Video_Key = @video,
Wiki_Key = @wiki,Detail = @detail,Picture_Id = @pictureid
WHERE Region_Id = @regionid AND Category_Id = @categoryid
AND Content_Id = @contentid";
using (var cmd = new MySqlCommand(sqlcmd, conn))
{
cmd.Parameters.AddWithValue("@contentid", newobj.Content_Id);
cmd.Parameters.AddWithValue("@regionid", newobj.Region_Id);
cmd.Parameters.AddWithValue("@categoryid", newobj.Category_Id);
cmd.Parameters.AddWithValue("@contentname", newobj.Content_Name);
cmd.Parameters.AddWithValue("@year", newobj.year);
cmd.Parameters.AddWithValue("@userid", newobj.userid);
cmd.Parameters.AddWithValue("@updatetime", DateTime.Now);
cmd.Parameters.AddWithValue("@video", newobj.video_key);
cmd.Parameters.AddWithValue("@wiki", newobj.wiki_key);
cmd.Parameters.AddWithValue("@detail", newobj.detail);
cmd.Parameters.AddWithValue("@pictureid", newobj.picture_id);
result = cmd.ExecuteNonQuery();
}
conn.Close();
return(result);
}
//maybe add a Active and Reason column for cr?
public int AddOneChangeRequest(Change_Request newobj)
{
int result = 0;
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var sqlcmd = @"INSERT INTO change_request (Region_Id,Category_Id,Content_Id,
Content_Name,Year,Update_User_Id,Last_Update_Date_Time,Video_Key,
Wiki_Key,Detail,Picture_Id,Reason,Request_Type,Active
)VALUE(@regionid,@categoryid,@contentid,@contentname,@year,
@userid,@updatetime,@video,@wiki,@detail,@pictureid,@reason,@type,@active)";
using (var cmd = new MySqlCommand(sqlcmd, conn))
{
//cmd.Parameters.AddWithValue("@change_id", newobj.Change_Request_Id);
cmd.Parameters.AddWithValue("@regionid", newobj.Region_Id);
cmd.Parameters.AddWithValue("@categoryid", newobj.Category_Id);
if (newobj.Content_Id != 0)
{
// It a edit change request
cmd.Parameters.AddWithValue("@contentid", newobj.Content_Id);
}
else
{
// add new
cmd.Parameters.AddWithValue("@contentid", 0);
}
cmd.Parameters.AddWithValue("@contentname", newobj.Content_Name);
cmd.Parameters.AddWithValue("@year", newobj.year);
cmd.Parameters.AddWithValue("@userid", newobj.userid);
cmd.Parameters.AddWithValue("@updatetime", DateTime.Now);
cmd.Parameters.AddWithValue("@video", newobj.video_key);
cmd.Parameters.AddWithValue("@wiki", newobj.wiki_key);
cmd.Parameters.AddWithValue("@detail", newobj.detail);
cmd.Parameters.AddWithValue("@reason", newobj.reason);
if (newobj.Content_Id != 0)
{
//edit
cmd.Parameters.AddWithValue("@type", 1);
}
else
{
//add new
cmd.Parameters.AddWithValue("@type", 0);
}
cmd.Parameters.AddWithValue("@active", 1);
cmd.Parameters.AddWithValue("@pictureid", newobj.picture_id);
result = cmd.ExecuteNonQuery();
}
conn.Close();
return(result);
}
//not done
//insert into the content table
public int transfer(int crid)
{
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var promte_changeR = new Change_Request();
var sqlcmd = @"select * from change_request where Change_Request_Id = @crid";
using (var cmd = new MySqlCommand(sqlcmd, conn))
{
cmd.Parameters.AddWithValue("@crid", crid);
using (MySqlDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
promte_changeR.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]);
// not sure what happened, testing
promte_changeR.Region_Id = (int)rd["Region_Id"];
promte_changeR.Category_Id = Convert.ToInt32(rd["Category"]);
promte_changeR.Content_Id = Convert.ToInt32(rd["Content_Id"]);
promte_changeR.Content_Name = rd["Content_Name"].ToString();
promte_changeR.year = Convert.ToInt32(rd["Year"]);
promte_changeR.userid = Convert.ToInt32(rd["Update_User_Id"]);
promte_changeR.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]);
promte_changeR.video_key = rd["Video_Key"].ToString();
promte_changeR.wiki_key = rd["Wiki_Key"].ToString();
promte_changeR.detail = rd["Detail"].ToString();
promte_changeR.picture_id = Convert.ToInt32(rd["Picture_Id"]);
promte_changeR.request_type = Convert.ToInt32(rd["Request_Type"]);
}
}
}
var result = 0;
if (promte_changeR.request_type == 1)
{
//edit
result = EditContent(promte_changeR);
}
else
{
//add new
result = AddNewContent(promte_changeR);
}
RequestDeactivate(crid);
return(result);
}
public Change_Request GetOneChangeRequest(int crid)
{
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sqlcmd = @"SELECT cr.*,r.regionName AS Region_Name,c.Category_Name AS Category_Name,u.Name AS UserName,p.Picture_Path AS Path FROM change_request cr LEFT JOIN region r ON (r.regionID = cr.Region_Id)
LEFT JOIN category c ON (c.Category_Id = cr.Category_Id) LEFT JOIN user u ON(u.User_Id = cr.Update_User_Id) LEFT JOIN picture p ON(p.Picture_Id = cr.Picture_Id) WHERE Change_Request_Id = @change_id ";
Change_Request ci = new Change_Request();
using (var cmd = new MySqlCommand(sqlcmd, conn))
{
cmd.Parameters.AddWithValue("@change_id", crid);
using (MySqlDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]);
// not sure what happened, testing
ci.Region_Id = (int)rd["Region_Id"];
ci.regionName = rd["Region_Name"].ToString();
ci.Category_Id = Convert.ToInt32(rd["Category_Id"]);
ci.categoryName = rd["Category_Name"].ToString();
ci.Content_Id = Convert.ToInt32(rd["Content_Id"]);
ci.Content_Name = rd["Content_Name"].ToString();
ci.year = Convert.ToInt32(rd["Year"]);
ci.userid = Convert.ToInt32(rd["Update_User_Id"]);
ci.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]);
ci.video_key = rd["Video_Key"].ToString();
ci.wiki_key = rd["Wiki_Key"].ToString();
ci.detail = rd["Detail"].ToString();
ci.picture_id = Convert.ToInt32(rd["Picture_Id"]);
ci.reason = (rd["Reason"]).ToString();
ci.picname = rd["Path"].ToString();
}
}
}
conn.Close();
return(ci);
}
public ActionResult AddOneChangeRequest(string ddlregionforCR, string ddlcategoryforCR, string contentname, string year, string reason, string detail, string wiki, string video, HttpPostedFileBase picfile)
{
var path = string.Empty;
var userid = 1;//temp
var picid = 0;
if (picfile != null)
{
if (picfile.ContentLength > 0)
{
var filename = Path.GetFileName(picfile.FileName);
path = Path.Combine(Server.MapPath("~/UploadPicture"), filename);
picfile.SaveAs(path);
picid = m.SavePicturePath(filename, userid);
}
}
//if picid = 0 then user didn't upload a picture
var newchange = new Change_Request();
newchange.Region_Id = Convert.ToInt32(ddlregionforCR);
newchange.Category_Id = Convert.ToInt32(ddlcategoryforCR);
newchange.picture_id = picid;
newchange.year = Convert.ToInt32(year);
newchange.video_key = video;
newchange.wiki_key = wiki;
newchange.Content_Name = contentname;
newchange.detail = detail;
newchange.reason = reason;
newchange.picture_id = picid;
var o = m.AddOneChangeRequest(newchange);
return(RedirectToAction("Index"));
}
public void EditOne(Change_Request newobj)
{
var o = m.EditOneChangeRequest(newobj);
}
