////////////////////////////////// ///////// Static Members ///////// ////////////////////////////////// public static void AuthenticateAccessForm(IARUsers pIARUsers, string pUsername, string pIARCode) { bool blnHasRecord; using (SqlConnection cn = new SqlConnection(clsSpeedo.SpeedoConnectionString)) { SqlCommand cmd = cn.CreateCommand(); if (pIARUsers == IARUsers.Requestor) { cmd.CommandText = "SELECT username FROM HR.IAR WHERE iarcode='" + pIARCode + "' AND username='******'"; } else if (pIARUsers == IARUsers.ApproverHead) { cmd.CommandText = "SELECT apphname FROM HR.IAR WHERE iarcode='" + pIARCode + "' AND apphname='" + pUsername + "'"; } else if (pIARUsers == IARUsers.ApproverDivision) { cmd.CommandText = "SELECT appdname FROM HR.IAR WHERE iarcode='" + pIARCode + "' AND appdname='" + pUsername + "'"; } cn.Open(); SqlDataReader dr = cmd.ExecuteReader(); blnHasRecord = dr.Read(); dr.Close(); } if (!blnHasRecord) { System.Web.HttpContext.Current.Response.Redirect("~/AccessDenied.aspx"); } }
public static string GetPaging(IARUsers pIARUsers, int pPage, string pUsername, string pStatus, string pPageName) { string strReturn = ""; int intPageSize = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["pagesize"]); int intTRows = 0; int intTRowsTemp = 0; int intPage = 1; using (SqlConnection cn = new SqlConnection(clsHrms.HrmsConnectionString)) { SqlCommand cmd = cn.CreateCommand(); if (pIARUsers == IARUsers.Requestor) { cmd.CommandText = "SELECT COUNT(iarcode) FROM HR.IAR WHERE username='******'" + (pStatus == "ALL" ? "" : " AND status='" + pStatus + "'"); } else if (pIARUsers == IARUsers.ApproverHead) { cmd.CommandText = "SELECT COUNT(iarcode) FROM HR.IAR WHERE apphname='" + pUsername + "'" + (pStatus == "ALL" ? "" : " AND apphstat='" + pStatus + "'"); } else if (pIARUsers == IARUsers.ApproverDivision) { cmd.CommandText = "SELECT COUNT(iarcode) FROM HR.IAR WHERE appdname='" + pUsername + "'" + (pStatus == "ALL" ? "" : " AND apphstat='A' AND appdstat='" + pStatus + "'"); } cn.Open(); try { intTRows = int.Parse(cmd.ExecuteScalar().ToString()); } catch { intTRows = 0; } } intTRowsTemp = intTRows; while (intTRowsTemp > 0) { if (pPage == intPage) { strReturn += (intPage == 1 ? "" : ",") + " " + intPage; } else { strReturn += " <a href='" + pPageName + ".aspx?page=" + intPage + "'>" + intPage + "</a>"; } intPage++; intTRowsTemp -= intPageSize; } return(strReturn); }
///////// Web Methods ///////// public static DataTable GetPageRecords(IARUsers pIARUsers, int pPage, string pUsername, string pStatus) { DataTable tblReturn = new DataTable(); int intPageSize = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["pagesize"]); int intStart = ((pPage - 1) * intPageSize) + 1; int intEnd = pPage * intPageSize; using (SqlConnection cn = new SqlConnection(clsHrms.HrmsConnectionString)) { SqlCommand cmd = cn.CreateCommand(); if (pStatus == "ALL") { if (pIARUsers == IARUsers.Requestor) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE username='******') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd; } else if (pIARUsers == IARUsers.ApproverHead) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE apphname='" + pUsername + "') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd; } else if (pIARUsers == IARUsers.ApproverDivision) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE appdname='" + pUsername + "' AND apphstat='A') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd; } } else { if (pIARUsers == IARUsers.Requestor) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE username='******' AND status='" + pStatus + "') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd; } else if (pIARUsers == IARUsers.ApproverHead) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE apphname='" + pUsername + "' AND apphstat='" + pStatus + "') AS pao WHERE RowNum BETWEEN " + intStart + " AND " + intEnd; } else if (pIARUsers == IARUsers.ApproverDivision) { cmd.CommandText = "SELECT * FROM (SELECT iarcode,datefile,datestrt,dateend,reason,apphname,apphstat,appdname,appdstat,status,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.IAR WHERE appdname='" + pUsername + "' AND appdstat='" + pStatus + "' AND apphstat='A') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd; } } cn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(tblReturn); } return(tblReturn); }