protected void Page_Load(object sender, EventArgs e) { string SrtConnString = WebConfigurationManager.ConnectionStrings["SageConnnetionString"].ConnectionString; SqlConnection db = new SqlConnection(SrtConnString); try { db.Open(); if (!Page.IsPostBack) { string Username = ""; string Email = ""; List <string> _Userinfo = new List <string>(); _Userinfo = (List <string>)Session["Userinfo"]; if (Session["Userinfo"] != null) { Username = _Userinfo[3].ToString(); Email = _Userinfo[1].ToString(); } else { Response.Redirect(ResolveClientUrl("~/")); } StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT rw.RewardName, "); sb.AppendLine(" rw.RewardPicture, "); sb.AppendLine(" rd.Quantity, "); sb.AppendLine(" rd.Point, "); sb.AppendLine(" rd.Status, "); sb.AppendLine(" rd.CreatedDate "); sb.AppendLine(" FROM HR_Redeem as rd "); sb.AppendLine(" LEFT JOIN HR_Rewards as rw on rd.RW_ID = rw.RW_ID "); sb.AppendLine(" WHERE 1=1 "); sb.AppendLine(" and rd.Username = '******' "); sb.AppendLine(" ORDER BY rd.CreatedDate DESC "); SqlCommand sql = new SqlCommand(sb.ToString(), db); DataTable table = new DataTable(); table.Load(sql.ExecuteReader()); if (table.Rows.Count > 0) { RptRedeem.DataSource = table; } RptRedeem.DataBind(); } } catch (Exception ex) { } finally { db.Close(); } }
protected void Page_Load(object sender, EventArgs e) { string SrtConnString = WebConfigurationManager.ConnectionStrings["SageConnnetionString"].ConnectionString; SqlConnection db = new SqlConnection(SrtConnString); try { db.Open(); if (!Page.IsPostBack) { List <string> _Userinfo = new List <string>(); _Userinfo = (List <string>)Session["Userinfo"]; if (Session["Userinfo"] != null) { string Username = _Userinfo[3].ToString(); string Email = _Userinfo[1].ToString(); HdnUser.Value = Username; HdnEmail.Value = Email; } else { Response.Redirect(ResolveClientUrl("~/")); } } string page = HdnPage.Value; int paging = page == "" ? 1 : Convert.ToInt32(page); int startrow = (20 * (paging - 1)) + 1; int endrow = (20 * paging); string status = DdlStatus.SelectedValue; string requestby = TxtRequestBy.Text; StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT rd.CreatedDate, "); sb.AppendLine(" rd.RD_ID, "); sb.AppendLine(" rw.RewardName, "); sb.AppendLine(" rd.Quantity, "); sb.AppendLine(" ISNULL(em.Fname, '') + ' ' + ISNULL(em.Lname, '') as EmpName, "); sb.AppendLine(" case when rd.Status = 'W' then 'Pending' else "); sb.AppendLine(" case when rd.Status = 'A' then 'Completed' else "); sb.AppendLine(" case when rd.Status = 'R' then 'Reject' else '' end end end as Status, "); sb.AppendLine(" ROW_NUMBER() OVER(ORDER BY isnull(rd.CreatedDate, 0) DESC) AS row "); sb.AppendLine(" FROM HR_Redeem as rd "); sb.AppendLine(" LEFT JOIN HR_Rewards as rw on rw.RW_ID = rd.RW_ID "); sb.AppendLine(" LEFT JOIN CRM.dbo.vWSE_HR_EmployeeData as em on em.Email = rd.Email "); sb.AppendLine(" WHERE 1=1 "); if (!status.Equals("")) { sb.AppendLine(" and rd.Status = '" + status + "' "); } if (!requestby.Equals("")) { sb.AppendLine(" and ISNULL(em.Fname, '') + ' ' + ISNULL(em.Lname, '') LIKE '%" + requestby + "%' "); } SqlCommand sql = new SqlCommand(sb.ToString(), db); DataTable table = new DataTable(); table.Load(sql.ExecuteReader()); double p = Math.Ceiling(Convert.ToDouble(table.Rows.Count) / Convert.ToDouble(20)); string showpage = ""; if (p > 1) { if (p < 6) { for (int i = 0; i < p; i++) { if (paging == (i + 1)) { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_active\" >" + (i + 1) + "</span>"; } else { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_inactive\" >" + (i + 1) + "</span>"; } } } else { double maxpage = (paging + 4) >= p ? p : (paging + 4); if (paging == 1 || paging == 2 || paging == 3) { switch (paging) { case 2: maxpage = maxpage - 1; break; case 3: maxpage = maxpage - 2; break; } for (int i = 0; i < maxpage; i++) { if (paging == (i + 1)) { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_active\" >" + (i + 1) + "</span>"; } else { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_inactive\" >" + (i + 1) + "</span>"; } } if (p > (paging + 4)) { showpage += "<span page=\"" + p + "\" class=\"page_inactive\">»</span>"; } } else if (paging == (p - 2) || paging == (p - 1) || paging == p) { int _maxpage = paging; if (paging == (p - 2)) { maxpage = (paging - 2) >= 1 ? (paging - 2) : 1; _maxpage = paging + 2; } else if (paging == (p - 1)) { maxpage = (paging - 3) >= 1 ? (paging - 3) : 1; _maxpage = paging + 1; } else { maxpage = (paging - 4) >= 1 ? (paging - 4) : 1; } //maxpage = (paging - 4) >= 1 ? (paging - 4) : 1; Response.Write("maxpage: " + maxpage); if ((paging - 4) > 1) { showpage += "<span page=\"1\" class=\"page_inactive\">«</span>"; } for (int i = Convert.ToInt32(maxpage - 1); i < _maxpage; i++) { if (paging == (i + 1)) { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_active\" >" + (i + 1) + "</span>"; } else { showpage += "<span page=\"" + (i + 1) + "\" class=\"page_inactive\" >" + (i + 1) + "</span>"; } } } else { showpage += "<span page=\"1\" class=\"page_inactive\">«</span>"; showpage += "<span page=\"" + (paging - 2) + "\" class=\"page_inactive\" >" + (paging - 2) + "</span>"; showpage += "<span page=\"" + (paging - 1) + "\" class=\"page_inactive\" >" + (paging - 1) + "</span>"; showpage += "<span page=\"" + paging + "\" class=\"page_active\" >" + paging + "</span>"; showpage += "<span page=\"" + (paging + 1) + "\" class=\"page_inactive\" >" + (paging + 1) + "</span>"; showpage += "<span page=\"" + (paging + 2) + "\" class=\"page_inactive\" >" + (paging + 2) + "</span>"; showpage += "<span page=\"" + p + "\" class=\"page_inactive\">»</span>"; } } LitPage.Text = showpage; LitPageButton.Text = showpage; } else { LitPage.Text = ""; LitPageButton.Text = ""; } DataView Dvtable = table.DefaultView; if (table.Rows.Count > 0) { Dvtable.RowFilter = "row >= " + startrow + " and row <= " + endrow; } table = Dvtable.ToTable(); if (table.Rows.Count > 0) { RptRedeem.DataSource = table; } RptRedeem.DataBind(); } catch (Exception ex) { Response.Write(ex.Message + "<br/>"); Response.Write(ex.StackTrace + "<br/>"); } finally { db.Close(); } }