//////////////////////////////////
///////// Static Members /////////
//////////////////////////////////
public static void AuthenticateAccessForm(UndertimeUsers pUndertimeUsers, string pUsername, string pUndertimeCode)
{
bool blnHasRecord;
using (SqlConnection cn = new SqlConnection(clsSpeedo.SpeedoConnectionString))
{
SqlCommand cmd = cn.CreateCommand();
if (pUndertimeUsers == UndertimeUsers.Requestor)
{
cmd.CommandText = "SELECT username FROM HR.UnderTime WHERE utcode='" + pUndertimeCode + "' AND username='******'";
}
else if (pUndertimeUsers == UndertimeUsers.Approver)
{
cmd.CommandText = "SELECT apphname FROM HR.UnderTime WHERE utcode='" + pUndertimeCode + "' AND apphname='" + 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(UndertimeUsers pUTUsers, 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 (pUTUsers == UndertimeUsers.Requestor)
{
cmd.CommandText = "SELECT COUNT(utcode) FROM HR.Undertime WHERE username='******'" + (pStatus == "all" ? "" : " AND utstat='" + pStatus + "'");
}
else if (pUTUsers == UndertimeUsers.Approver)
{
cmd.CommandText = "SELECT COUNT(utcode) FROM HR.Undertime WHERE apphname='" + pUserName + "'" + (pStatus == "all" ? "" : " AND utstat='" + 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);
}
public static DataTable GetPageRecords(UndertimeUsers pUTUsers, 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 (pUTUsers == UndertimeUsers.Requestor)
{
cmd.CommandText = "SELECT * FROM (SELECT utcode,datefile,dateapp,apphname,utstat,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.Undertime WHERE username='******') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd;
}
else if (pUTUsers == UndertimeUsers.Approver)
{
cmd.CommandText = "SELECT * FROM (SELECT utcode,datefile,dateapp,apphname,utstat,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.Undertime WHERE apphname='" + pUserName + "') AS pao WHERE RowNum BETWEEN "+ intStart + " AND " + intEnd;
}
}
else
{
if (pUTUsers == UndertimeUsers.Requestor)
{
cmd.CommandText = "SELECT * FROM (SELECT utcode,datefile,dateapp,apphname,utstat,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.Undertime WHERE username='******' AND utstat='" + pStatus + "') AS pao WHERE RowNum BETWEEN " + intStart + " AND " + intEnd;
}
else if (pUTUsers == UndertimeUsers.Approver)
{
cmd.CommandText = "SELECT * FROM (SELECT utcode,datefile,dateapp,apphname,utstat,username,ROW_NUMBER() OVER(ORDER BY datefile DESC) AS RowNum FROM HR.Undertime WHERE apphname='" + pUserName + "' AND utstat='" + pStatus + "') AS pao WHERE RowNum BETWEEN " + intStart + " AND " + intEnd;
}
}
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tblReturn);
}
return(tblReturn);
}
public static DataTable GetTopRecords(UndertimeUsers pUTUsers, int pTop, string pUserName)
{
DataTable tblReturn = new DataTable();
using (SqlConnection cn = new SqlConnection(clsHrms.HrmsConnectionString))
{
SqlCommand cmd = cn.CreateCommand();
switch (pUTUsers)
{
case UndertimeUsers.Requestor:
cmd.CommandText = "SELECT TOP " + pTop + " utcode,datefile,dateapp,apphname,utstat,username FROM HR.Undertime WHERE username='******' ORDER BY datefile DESC";
break;
case UndertimeUsers.Approver:
cmd.CommandText = "SELECT TOP " + pTop + " utcode,datefile,dateapp,apphname,utstat,username FROM HR.Undertime WHERE apphname='" + pUserName + "' AND utstat='F' ORDER BY datefile DESC";
break;
}
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(tblReturn);
}
return(tblReturn);
}
