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