public List <Change_Request_Index> GetAllChangeRequest_Index()
{
//directly using sql
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//ACTIVE = 1 : ACTIVE ACTIVE = 0 : INACTIVE
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 cr.Active = 1 ORDER BY cr.Last_Update_Date_Time";
MySqlCommand cmd = new MySqlCommand(sqlcmd, conn);
List <Change_Request_Index> c = new List <Change_Request_Index>();
using (MySqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
Change_Request_Index ci = new Change_Request_Index();
ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]);
ci.Region_Id = Convert.ToInt32(rd["Region_Id"]);
ci.regionName = rd["Region_Name"].ToString();
ci.categoryName = rd["Category_Name"].ToString();
ci.Category_Id = Convert.ToInt32(rd["Category_Id"]);
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.usename = rd["UserName"].ToString();
ci.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]);
ci.picture_id = Convert.ToInt32(rd["Picture_Id"]);
ci.picname = rd["Path"].ToString();
c.Add(ci);
}
}
conn.Close();
return(c);
}
public List <Change_Request_Index> GetAllChangeRequest_Index_with_filter(int rid, int cid, int yearcode)
{
/*
* yearList.Add(new SelectListItem() { Text = "1990~ The End of the Cold War", Value = "1" });
* yearList.Add(new SelectListItem() { Text = "1914~ Great War", Value = "2" });
* yearList.Add(new SelectListItem() { Text = "1837~ The age of Victoria", Value = "3" });
* yearList.Add(new SelectListItem() { Text = "1776~ American Revolution", Value = "4" });
* yearList.Add(new SelectListItem() { Text = "1688~ Glorious Revolution", Value = "5" });
* yearList.Add(new SelectListItem() { Text = "1453~ The Fall of Constantinople", Value = "6" });
* yearList.Add(new SelectListItem() { Text = "962~ The Holly Roman Empire", Value = "7" });
* yearList.Add(new SelectListItem() { Text = "476~ The End of the Roman Empire", Value = "8" });
* yearList.Add(new SelectListItem() { Text = "A.D.", Value = "9" });
* yearList.Add(new SelectListItem() { Text = "B.C.", Value = "10" });
*/
conn.ConnectionString = mysqlconnection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//ACTIVE = 1 : ACTIVE ACTIVE = 0 : INACTIVE
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 cr.Active = 1";// ORDER BY cr.Last_Update_Date_Time";
if (rid != 999)
{
sqlcmd = sqlcmd + @" AND cr.Region_Id = @rid ";
}
if (cid != 999)
{
sqlcmd = sqlcmd + @" AND cr.Category_Id = @cid ";
}
if (yearcode != 11)
{
if (yearcode == 1)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1990 ";
}
else if (yearcode == 2)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1914 AND cr.year < 1990 ";
}
else if (yearcode == 3)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1837 AND cr.year < 1914 ";
}
else if (yearcode == 4)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1776 AND cr.year < 1837 ";
}
else if (yearcode == 5)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1688 AND cr.year < 1776 ";
}
else if (yearcode == 6)
{
sqlcmd = sqlcmd + @" AND cr.year >= 1453 AND cr.year < 1688 ";
}
else if (yearcode == 7)
{
sqlcmd = sqlcmd + @" AND cr.year >= 962 AND cr.year < 1453 ";
}
else if (yearcode == 8)
{
sqlcmd = sqlcmd + @" AND cr.year >= 476 AND cr.year < 962 ";
}
else if (yearcode == 9)
{
sqlcmd = sqlcmd + @" AND cr.year >= 0 AND cr.year < 476 ";
}
else if (yearcode == 10)
{
sqlcmd = sqlcmd + @" AND cr.year < 0 ";
}
}
sqlcmd = sqlcmd + @" ORDER BY cr.Year, c.Category_Name,r.regionName";
List <Change_Request_Index> c = new List <Change_Request_Index>();
using (MySqlCommand cmd = new MySqlCommand(sqlcmd, conn)) {
if (rid != 999)
{
cmd.Parameters.AddWithValue("@rid", rid);
}
if (cid != 999)
{
cmd.Parameters.AddWithValue("@cid", cid);
}
using (MySqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
Change_Request_Index ci = new Change_Request_Index();
ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]);
ci.Region_Id = Convert.ToInt32(rd["Region_Id"]);
ci.regionName = rd["Region_Name"].ToString();
ci.categoryName = rd["Category_Name"].ToString();
ci.Category_Id = Convert.ToInt32(rd["Category_Id"]);
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.usename = rd["UserName"].ToString();
ci.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]);
ci.picture_id = Convert.ToInt32(rd["Picture_Id"]);
ci.picname = rd["Path"].ToString();
c.Add(ci);
}
}
};
conn.Close();
return(c);
}
