public List <Wall> GetWall(WallComing page)
{
HttpStatusCode responseCode = HttpStatusCode.OK;
CommonController cc = new CommonController();
List <Wall> wList = new List <Wall>();
MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sq_traffiti"].ConnectionString);
try
{
int pageSize = 20;
int offset = (page.pageNum - 1) * pageSize;
cn.Open();
MySqlCommand cmd = new MySqlCommand(@"select s.wall_id, s.content, s.photo_path, s.author_id, a.author_name, a.profile_pic,
l.location_en as location, s.created_date, w.fav_count, w.like_count
from wall_snap s, author a, location l, wall w
where s.author_id = a.author_id and s.location_id = l.location_id
and s.wall_id = w.wall_id
order by s.created_date desc limit @pageSize offset @offset", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@pageSize", MySqlDbType.Int32).Value = pageSize;
cmd.Parameters.Add("@offset", MySqlDbType.Int32).Value = offset;
cmd.Parameters.Add("@lang_id", MySqlDbType.Int32).Value = page.lang_id;
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Wall w = new Wall();
w.wall_id = Convert.ToInt32(dr["wall_id"]);
w.content = dr["content"].ToString();
w.photo_path = dr["photo_path"].ToString();
w.author_id = Convert.ToInt32(dr["author_id"]);
w.author_name = dr["author_name"].ToString();
w.profile_pic = dr["profile_pic"].ToString();
w.location = dr["location"].ToString();
w.date_text = cc.CalculateDateTime(Convert.ToDateTime(dr["created_date"]), page.lang_id);
w.fav_count = Convert.ToInt32(dr["fav_count"]);
w.like_count = Convert.ToInt32(dr["like_count"]);
wList.Add(w);
}
}
catch (Exception ex)
{
responseCode = HttpStatusCode.InternalServerError;
ErrorHandling(responseCode, 1, ex.Message);
}
finally
{
cn.Close();
}
return(wList);
}
public List <WallDetail> GetProfileWallList(WallComing wc)
{
CommonController cc = new CommonController();
List <WallDetail> wdList = new List <WallDetail>();
MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sq_traffiti"].ConnectionString);
try
{
cn.Open();
MySqlCommand cmd = new MySqlCommand(@"select w.wall_id, w.author_id, w.created_date, w.like_count, w.fav_count, w.location_id,
a.author_name, a.profile_pic,
case @lang when 1 then l.location_en when 2 then l.location_tc when 3 then l.location_sc end as location
from wall w, author a, location l where w.author_id = a.author_id and w.location_id = l.location_id
and w.author_id = @authorID order by w.created_date desc", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wc.wall_id;
cmd.Parameters.Add("@lang", MySqlDbType.Int32).Value = wc.lang_id;
cmd.Parameters.Add("@authorID", MySqlDbType.Int32).Value = wc.authorID;
MySqlDataReader detailDr = cmd.ExecuteReader();
while (detailDr.Read())
{
WallDetail wd = new WallDetail();
wd.wall_id = Convert.ToInt32(detailDr["wall_id"]);
wd.location = detailDr["location"].ToString();
wd.author_id = Convert.ToInt32(detailDr["author_id"]);
wd.author_name = detailDr["author_name"].ToString();
wd.profile_pic = detailDr["profile_pic"].ToString();
wd.date_text = cc.CalculateDateTime(Convert.ToDateTime(detailDr["created_date"]), wc.lang_id);
wd.fav_count = Convert.ToInt32(detailDr["fav_count"]);
wd.like_count = Convert.ToInt32(detailDr["like_count"]);
wdList.Add(wd);
}
detailDr.Close();
for (int i = 0; i < wdList.Count; i++)
{
cmd = new MySqlCommand("select photo_path from photo where wall_id = @wall_id order by is_default desc", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wdList[i].wall_id;
DataSet ds = new DataSet();
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
ad.Fill(ds);
wdList[i].photoList = new List <string>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["photo_path"] != DBNull.Value && !string.IsNullOrEmpty(dr["photo_path"].ToString()))
{
wdList[i].photoList.Add(dr["photo_path"].ToString());
}
}
}
//MySqlCommand contentCmd = new MySqlCommand("select content from wall_section where wall_id = @wall_id order by display_order", cn);
//contentCmd.CommandType = CommandType.Text;
//contentCmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wc.wall_id;
//ds = new DataSet();
//ad = new MySqlDataAdapter(contentCmd);
//ad.Fill(ds);
//wd.contentList = new List<string>();
//foreach (DataRow dr in ds.Tables[0].Rows)
//{
// wd.contentList.Add(dr["content"].ToString());
//}
}
catch (Exception ex)
{ }
finally
{
cn.Close();
}
return(wdList);
}