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();
}
}
