private static bool Avalable(string conn, string user_id)
{
string cmd1 = "SELECT user_posts FROM public.users WHERE user_id = " + user_id;
string cmd2 = "SELECT subscription_limited FROM public.users, public.subscription WHERE user_subscription = subscription_id AND user_id = " + user_id;
return(Convert.ToInt32(SqlData.ExeNpSqlToTable(cmd1, conn).Rows[0]["user_posts"].ToString()) < Convert.ToInt32(SqlData.ExeNpSqlToTable(cmd2, conn).Rows[0]["subscription_limited"].ToString()));
}
public static int NewPost(string conn, List <string> input)
{
if (Avalable(conn, input[0]))
{
string insert = "INSERT INTO public.post (user_id, post_area, post_address, post_price, post_direction, post_floors, post_type, post_detail, post_restricted, post_header, post_image, post_view) VALUES ";
string values = "";
foreach (string s in input)
{
if (values.Length > 0)
{
values += ", ";
}
values += "'" + s + "'";
}
values = "(" + values + ", 0)";
string cmd = insert + values;
SqlData.ExeNpSqlToTable(cmd, conn);
return(1);
}
else
{
return(0);
}
}
//protected string GetUserId()
//{
// string cmd = "SELECT user_id FROM public.users WHERE user_account = '" + HttpContext.Current.User.Identity.Name + "'";
// string res = "";
// DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
// foreach (DataRow r in tbl.Rows)
// {
// res = r["user_id"].ToString();
// }
// return res;
//}
protected void Page_Load(object sender, EventArgs e)
{
restricted.Items[0].Selected = true;
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
string cmd = "SELECT w.ward_name, w.ward_id FROM public.ward AS w, public.district AS d WHERE w.district_id = d.district_id AND d.district_name = '" + district.SelectedValue + "'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
foreach (DataRow r in tbl.Rows)
{
ListItem new_item = new ListItem(r["ward_name"].ToString(), r["ward_id"].ToString());
ward.Items.Add(new_item);
}
if (!IsPostBack)
{
district.Items.Add(new ListItem(" ", "0"));
ward.Items.Add(new ListItem(" ", "0"));
street.Items.Add(new ListItem(" ", "0"));
DataTable tbl2 = Function.GetDistrict(conn);
foreach (DataRow r in tbl2.Rows)
{
district.Items.Add(new ListItem(r["district_name"].ToString(), r["district_id"].ToString()));
}
}
}
protected DataTable ValidAuthentication(string username, string password)
{
string cmd = "SELECT user_role FROM public.users WHERE user_name = '" + username + "' AND user_password = '******'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
return(tbl);
}
public static DataTable GetPostByDistrict(string conn, string district_id, string price, string area, string type, string user_id)
{
string price_cmd;
string area_cmd;
string type_cmd;
if (price == "-1")
{
price_cmd = " post_price > -1 ";
}
else
{
if (price == "10")
{
price_cmd = " post_price >= 10 ";
}
else
{
price_cmd = " post_price >= (" + price + " - 1) AND post_price <= (" + price + " + 1) ";
}
}
if (area == "-1")
{
area_cmd = " post_area > -1 ";
}
else
{
if (area == "100")
{
area_cmd = " post_area >= 100 ";
}
else
{
area_cmd = " post_area >= (" + area + " - 10) AND post_area <= (" + area + " + 10) ";
}
}
if (type == "-1")
{
type_cmd = " (post_type = 'Chung cư' OR post_type = 'Thổ cư') ";
}
else
{
type_cmd = " post_type = '" + type + "' ";
}
if (user_id != null)
{
user_id = " AND p.user_id = " + user_id;
}
else
{
user_id = "";
}
string cmd = "SELECT p.* FROM public.post AS p,public.district AS d,public.street AS s,public.ward AS w, public.users AS u WHERE u.user_id = p.user_id AND d.district_id = " + district_id + " " + user_id + " and d.district_id = w.district_id and w.ward_id = s.ward_id and p.post_address = s.street_id AND" + area_cmd + "AND" + price_cmd + "AND" + type_cmd + " ORDER BY (u.user_subscription) DESC";
//return cmd;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetPostDetail(string conn, string post_id)
{
string cmd = "UPDATE public.post SET post_view = post_view + 1 WHERE post_id = " + post_id;
SqlData.ExeNpSqlCmd(cmd, conn);
cmd = "SELECT * FROM public.post WHERE post_id = '" + post_id + "'";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetPostListForUser(string conn, string price, string area, string type, string user_id)
{
string price_cmd;
string area_cmd;
string type_cmd;
if (price == "-1")
{
price_cmd = " post_price > -1 ";
}
else
{
if (price == "10")
{
price_cmd = " post_price >= 10 ";
}
else
{
price_cmd = " post_price >= (" + price + " - 1) AND post_price <= (" + price + " + 1) ";
}
}
if (area == "-1")
{
area_cmd = " post_area > -1 ";
}
else
{
if (area == "100")
{
area_cmd = " post_area >= 100 ";
}
else
{
area_cmd = " post_area >= (" + area + " - 10) AND post_area <= (" + area + " + 10) ";
}
}
if (type == "-1")
{
type_cmd = " (post_type = 'Chung cư' OR post_type = 'Thổ cư') ";
}
else
{
type_cmd = " post_type = '" + type + "' ";
}
if (user_id != null)
{
user_id = " AND public.post.user_id = " + user_id;
}
string cmd = "SELECT * FROM public.post, public.users WHERE public.post.post_approve = true " + user_id + " AND public.post.user_id = public.users.user_id AND" + price_cmd + "AND" + area_cmd + "AND" + type_cmd + "ORDER BY (user_subscription) DESC";
//return cmd;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable ValidAuthentication(string conn, string username, string password)
{
string cmd = "SELECT user_role FROM public.users WHERE user_account = '" + username + "' AND user_password = '******'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, conn);
if (tbl.Rows.Count != 0)
{
UpdateLogIn(conn, username);
}
return(tbl);
}
protected void Page_Load(object sender, EventArgs e)
{
string id = "";
string cmd = "SELECT user_id FROM public.users WHERE user_name = '" + HttpContext.Current.User.Identity.Name + "'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
if (tbl.Rows.Count != 0)
{
id = tbl.Rows[0]["user_id"].ToString();
}
userInfor.Attributes.Add("href", "User/AuthorizedUser/UserInformation.aspx?id=" + id);
}
protected string GetUserId()
{
string cmd = "SELECT user_id FROM public.users WHERE user_account = '" + HttpContext.Current.User.Identity.Name + "'";
string res = "";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
foreach (DataRow r in tbl.Rows)
{
res = r["user_id"].ToString();
}
return(res);
}
protected void Button2_Click(object sender, EventArgs e)
{
string cmd = "SELECT s.street_name, s.street_id FROM public.street AS s, public.ward AS w WHERE s.ward_id = w.ward_id AND w.ward_id = '" + ward.SelectedValue + "'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
street.Items.Clear();
foreach (DataRow r in tbl.Rows)
{
ListItem new_item = new ListItem(r["street_name"].ToString(), r["street_id"].ToString());
street.Items.Add(new_item);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string cmd = "SELECT w.ward_name, w.ward_id FROM public.ward AS w, public.district AS d WHERE w.district_id = d.district_id AND d.district_id = '" + district.SelectedValue + "'";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
ward.Items.Clear();
foreach (DataRow r in tbl.Rows)
{
ListItem new_item = new ListItem(r["ward_name"].ToString(), r["ward_id"].ToString());
ward.Items.Add(new_item);
}
}
public static string GetUserId(string conn, string username)
{
string cmd = "SELECT user_id FROM public.users WHERE user_account = '" + username + "'";
string res = "";
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, conn);
foreach (DataRow r in tbl.Rows)
{
res = r["user_id"].ToString();
}
return(res);
}
public static string GetMess(string conn, string user_id)
{
string cmd = "SELECT noti FROM public.notifications WHERE user_id = " + user_id;
DataTable tbl = SqlData.ExeNpSqlToTable(cmd, conn);
if (tbl.Rows.Count != 0)
{
return(tbl.Rows[0]["noti"].ToString());
}
else
{
return("");
}
}
public static DataTable GetPostByStreet(string conn, string street_id, string price, string area, string user_id)
{
string price_cmd;
string area_cmd;
if (price == "-1")
{
price_cmd = " post_price > -1 ";
}
else
{
if (price == "10")
{
price_cmd = " post_price >= 10 ";
}
else
{
price_cmd = " post_price >= (" + price + " - 1) AND post_price <= (" + price + " + 1) ";
}
}
if (area == "-1")
{
area_cmd = " post_area > -1 ";
}
else
{
if (area == "100")
{
area_cmd = " post_area >= 100 ";
}
else
{
area_cmd = " post_area >= (" + area + " - 10) AND post_area <= (" + area + " + 10) ";
}
}
if (user_id != null)
{
user_id = " AND p.user_id = " + user_id;
}
else
{
user_id = "";
}
string cmd = "SELECT p.* FROM public.post AS p,public.street AS s WHERE s.street_id = '" + street_id + "' " + user_id + " AND s.street_id = p.post_address AND" + area_cmd + "AND" + price_cmd;
//return cmd;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetPostByDistrict(string conn, string district_id, string price, string area, string user_id)
{
string price_cmd;
string area_cmd;
if (price == "-1")
{
price_cmd = " post_price > -1 ";
}
else
{
if (price == "10")
{
price_cmd = " post_price >= 10 ";
}
else
{
price_cmd = " post_price >= (" + price + " - 1) AND post_price <= (" + price + " + 1) ";
}
}
if (area == "-1")
{
area_cmd = " post_area > -1 ";
}
else
{
if (area == "100")
{
area_cmd = " post_area >= 100 ";
}
else
{
area_cmd = " post_area >= (" + area + " - 10) AND post_area <= (" + area + " + 10) ";
}
}
if (user_id != null)
{
user_id = " AND p.user_id = " + user_id;
}
else
{
user_id = "";
}
string cmd = "SELECT p.* FROM public.post AS p,public.district AS d,public.street AS s,public.ward AS w WHERE d.district_id = '" + district_id + "' " + user_id + " and d.district_id = w.district_id and w.ward_id = s.ward_id and p.post_address = s.street_id AND" + area_cmd + "AND" + price_cmd;
//return cmd;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
// protected string mess;
protected void Page_Load(object sender, EventArgs e)
{
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
List <string> userInfor = Function.GetUserInfor(conn, Server.UrlDecode(Request.QueryString["id"]));
DoB.Text = userInfor[0] + "/" + userInfor[1] + "/" + userInfor[2];
Username.Text = userInfor[3];
Address.Text = userInfor[5];
Phonenumber.Text = userInfor[6];
Subscription.Text = userInfor[7];
string cmd = "SELECT COUNT(post_id) AS post_num FROM public.post WHERE user_id = '" + Server.UrlDecode(Request.QueryString["id"]) + "'";
PostNum.Text = SqlData.ExeNpSqlToTable(cmd, conn).Rows[0]["post_num"].ToString();
Label1.Text = Function.GetMess(conn, GetUserId());
}
public static DataTable GetPostByUser(string conn, string user_id, List <string> orderBy, string order, string state, string approve)
{
StringBuilder sb = new StringBuilder();
string approve_cmd;
foreach (string s in orderBy)
{
if (sb.Length > 0)
{
sb.Append(',');
}
sb.Append(s);
}
string listOfOrder = sb.ToString();;
if (state == "2")
{
state = "(post_restricted = 0 OR post_restricted = 1)";
}
else
{
state = "(post_restricted = " + state + ")";
}
if (approve == "Pending")
{
approve_cmd = " post_approve = false AND ";
}
else
{
if (approve == "Approve")
{
approve_cmd = " post_approve = true AND ";
}
else
{
approve_cmd = " (post_approve = true OR post_approve = false) AND ";
}
}
string cmd = "SELECT * FROM public.post WHERE user_id = " + user_id + " AND " + approve_cmd + state + " ORDER BY (" + listOfOrder + ") " + order;
//return cmd;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static List <string> GetUserInfor(string conn, string user_id)
{
List <string> result = new List <string>();
string cmd = "SELECT EXTRACT(DAY FROM user_dob) AS day FROM public.users WHERE user_id = '" + user_id + "'";
result.Add(SqlData.ExeNpSqlToTable(cmd, conn).Rows[0]["day"].ToString());
cmd = "SELECT EXTRACT(MONTH FROM user_dob) AS month FROM public.users WHERE user_id = '" + user_id + "'";
result.Add(SqlData.ExeNpSqlToTable(cmd, conn).Rows[0]["month"].ToString());
cmd = "SELECT EXTRACT(YEAR FROM user_dob) AS year FROM public.users WHERE user_id = '" + user_id + "'";
result.Add(SqlData.ExeNpSqlToTable(cmd, conn).Rows[0]["year"].ToString());
cmd = "SELECT * FROM public.users WHERE user_id = '" + user_id + "'";
using (DataTable tbl = SqlData.ExeNpSqlToTable(cmd, conn))
{
result.Add(tbl.Rows[0]["user_name"].ToString());
result.Add(tbl.Rows[0]["user_password"].ToString());
result.Add(tbl.Rows[0]["user_address"].ToString());
result.Add(tbl.Rows[0]["user_phonenumber"].ToString());
result.Add(tbl.Rows[0]["user_subscription"].ToString());
}
return(result);
}
public static DataTable GetRecentPost(string conn, string user_id)
{
string cmd = "SELECT * FROM public.post AS post RIGHT JOIN (SELECT * FROM public.history WHERE user_id = " + user_id + ") AS history ON post.post_id = history.post_id ORDER BY (visit_time) DESC LIMIT 5";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetAddress(string conn, string street_id)
{
string cmd = "SELECT a.district_id, b.ward_id FROM public.district AS a INNER JOIN public.ward AS b ON a.district_id = b.district_id INNER JOIN public.street AS c ON b.ward_id = c.ward_id WHERE c.street_id = " + street_id;
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetUserList(string conn)
{
string cmd = "SELECT user_id, user_name, user_lastseen AS lastseen, user_posts FROM public.users WHERE user_role = 2 ORDER BY (user_id) ASC";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetStreet(string conn, string ward_id)
{
string cmd = "SELECT * FROM public.street WHERE ward_id = '" + ward_id + "'";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetDistrict(string conn)
{
string cmd = "SELECT * FROM public.district";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static string GetMess(string conn, string user_id)
{
string cmd = "SELECT noti FROM public.notifications WHERE user_id = " + user_id;
return(SqlData.ExeNpSqlToTable(cmd, conn).Rows[0]["noti"].ToString());
}
public static DataTable GetWard(string conn, string district_id)
{
string cmd = "SELECT * FROM public.ward WHERE district_id = '" + district_id + "'";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}
public static DataTable GetTopViewPost(string conn)
{
string cmd = "WITH tmp AS (SELECT * FROM public.post WHERE post_view != 0 ORDER BY(post_view) DESC) SELECT* FROM tmp, public.users WHERE tmp.post_approve = true AND tmp.user_id = public.users.user_id ORDER BY(user_subscription, post_view) DESC limit 5";
return(SqlData.ExeNpSqlToTable(cmd, conn));
}