public Boolean ApproveMR(movementregister mr)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update MovementRegister set DocumentStatus=2, Comments='" + mr.Comments + "'" +
", ApproveTime = GETDATE() " +
", Approver='" + Login.userLoggedIn + "'" +
" where employeeID='" + mr.EmployeeID + "' and RowID = " + mr.rowID;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("approve", "MovementRegister", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public static Boolean checkMovementStatus(movementregister mr)
{
Boolean stat = true;
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID, EmployeeID, ExitTimePlanned, ReturnTimePlanned,Purpose " +
" from MovementRegister " +
" where createuser='******' and Status = 1 and DocumentStatus in (1,2,3,5)";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
stat = false;
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Movement Details For Status");
}
return(stat);
}
public Boolean updateMovementReg(movementregister mr, movementregister prevmr)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update MovementRegister set ExitTimePlanned ='" + mr.ExitTimePlanned.ToString("yyyy-MM-dd HH:mm:ss") + "'" +
", ReturnTimePlanned ='" + mr.ReturnTimePlanned.ToString("yyyy-MM-dd HH:mm:ss") +
"', ModeOfTravel ='" + mr.ModeOfTravel +
"', Comments ='" +
mr.Comments.Replace("'", "''") +
"', Approver='" + mr.ApproveUser +
"', Purpose='" + mr.Purpose +
"' where EmployeeID='" + prevmr.EmployeeID + "' and RowID = " + prevmr.rowID;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "MovementRegister", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateMovementReg(movementregister mr)
{
Boolean status = true;
try
{
if (mr.EmployeeID.Trim().Length == 0 || mr.EmployeeID == null)
{
return(false);
}
if (mr.ExitTimePlanned == null)
{
return(false);
}
if (mr.ReturnTimePlanned == null)
{
return(false);
}
//DateTime a = UpdateTable.getSQLDateTime();
//DateTime dt = mr.ExitTimePlanned;
//int secs = (mr.ExitTimePlanned - UpdateTable.getSQLDateTime()).Seconds;
//double sec = mr.ExitTimePlanned.Subtract(UpdateTable.getSQLDateTime()).TotalSeconds;
DateTime tdt = UpdateTable.getSQLDateTime();
Double val1 = mr.ExitTimePlanned.Subtract(tdt).TotalSeconds;
Double val2 = mr.ReturnTimePlanned.Subtract(mr.ExitTimePlanned).TotalSeconds;
if ((val1 < -300) || (val2 < 600))
{
return(false);
}
if (mr.Purpose.Trim().Length == 0 || mr.Purpose == null)
{
return(false);
}
if (mr.ModeOfTravel.Trim().Length == 0 || mr.ModeOfTravel == null)
{
return(false);
}
if (mr.ApproveUser.Trim().Length == 0 || mr.ApproveUser == null)
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Validation Failed.");
return(false);
}
return(status);
}
public Boolean CancelMovementReg(movementregister prevmr, int opt)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
string updateSQL1 = "update MovementRegister set Status = 98,Comments = '" + prevmr.Comments + "'" +
" where EmployeeID='" + prevmr.EmployeeID + "' and RowID = " + prevmr.rowID;
string updateSQL2 = "update MovementRegister set DocumentStatus = 98," +
"Comments = '" + prevmr.Comments + "'" +
" where EmployeeID='" + prevmr.EmployeeID + "' and RowID = " + prevmr.rowID;
switch (opt)
{
case 1:
updateSQL = updateSQL1;
break;
case 2:
updateSQL = updateSQL2;
break;
default:
updateSQL = "";
break;
}
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "MovementRegister", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
//Movement Register List For DashBoard
public List <movementregister> getMovementRegForDashboard()
{
movementregister mr;
List <movementregister> MRlist = new List <movementregister>();
try
{
string query = "select a.RowID, a.EmployeeID,b.Name, a.ExitTimePlanned, a.ReturnTimePlanned,a.Purpose, " +
" a.Status, a.DocumentStatus,a.Approver, c.Name, a.CreateTime" +
" from MovementRegister a, ViewUserEmployeeList b, ViewUserEmployeeList c" +
" where a.EmployeeID=b.EmployeeID and a.Approver = c.UserID" +
" and ((a.createuser='******' and a.DocumentStatus = 2 and a.Status = 1)" +
" or (a.Approver ='" + Login.userLoggedIn + "' and a.DocumentStatus = 1 and a.Status = 1)) order by a.CreateTime desc";
SqlConnection conn = new SqlConnection(Login.connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
mr = new movementregister();
mr.rowID = reader.GetInt32(0);
mr.EmployeeID = reader.GetString(1);
mr.EmployeeName = reader.GetString(2);
mr.ExitTimePlanned = reader.GetDateTime(3);
mr.ReturnTimePlanned = reader.GetDateTime(4);
mr.Purpose = reader.GetString(5);
mr.Status = reader.GetInt32(6);
mr.DocumentStatus = reader.GetInt32(7);
mr.ApproveUser = reader.GetString(8);
mr.Approver = reader.GetString(9);
mr.CreateTime = reader.GetDateTime(10);
MRlist.Add(mr);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MovemetnReg Data");
}
return(MRlist);
}
public Boolean insertMovementReg(movementregister mr)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into MovementRegister " +
" (EmployeeID,ExitTimePlanned,ReturnTimePlanned,Purpose,ModeOfTravel,Comments," +
"Approver,Status,DocumentStatus,CreateUser, CreateTime)" +
"values (" +
"'" + mr.EmployeeID + "'," +
"'" + mr.ExitTimePlanned.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
"'" + mr.ReturnTimePlanned.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
"'" + mr.Purpose + "'," +
"'" + mr.ModeOfTravel + "'," +
"'" + mr.Comments.Replace("'", "''") + "'," +
"'" + mr.ApproveUser + "'," +
mr.Status + "," +
mr.DocumentStatus + "," +
"'" + Login.userLoggedIn + "'," +
"GETDATE())";
//"'" + pheader.ForwardUser + "'," +
//"'" + pheader.ApproveUser + "'," +
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "MovementRegister", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean RejReqforCancelledMR(movementregister mr)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update MovementRegister set DocumentStatus=2, Comments='" + mr.Comments + "'" +
" where employeeID='" + mr.EmployeeID + "' and RowID = " + mr.rowID;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("Cancel", "MovementRegister", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <movementregister> getFilteredmovementregister(int opt)
{
movementregister mr;
List <movementregister> MRlist = new List <movementregister>();
try
{
string query1 = "select a.RowID, a.EmployeeID,b.Name, a.ExitTimePlanned, a.ReturnTimePlanned,a.Purpose, " +
" a.ModeOfTravel, a.OutTime, a.InTime, a.Comments, a.Status, a.DocumentStatus,a.Approver, c.Name, a.CreateTime," +
" a.ApproveTime, a.CreateUser,d.Name" +
" from MovementRegister a, ViewUserEmployeeList b, ViewUserEmployeeList c,ViewUserEmployeeList d" +
" where a.EmployeeID=b.EmployeeID and a.Approver = c.UserID and a.CreateUser = d.UserID and (a.createuser='******' and a.Status = 1 and a.DocumentStatus =1) order by CreateTime desc";
string query2 = "select a.RowID, a.EmployeeID,b.Name, a.ExitTimePlanned, a.ReturnTimePlanned,a.Purpose, " +
" a.ModeOfTravel, a.OutTime, a.InTime, a.Comments, a.Status, a.DocumentStatus,a.Approver, c.Name, a.CreateTime," +
" a.ApproveTime, a.CreateUser,d.Name" +
" from MovementRegister a, ViewUserEmployeeList b, ViewUserEmployeeList c,ViewUserEmployeeList d" +
" where a.EmployeeID=b.EmployeeID and a.Approver = c.UserID and a.CreateUser = d.UserID and (a.createuser='******' and a.DocumentStatus in (2,3,99,98,10,5,6)) order by CreateTime desc";
string query3 = "select a.RowID, a.EmployeeID,b.Name, a.ExitTimePlanned, a.ReturnTimePlanned,a.Purpose, " +
" a.ModeOfTravel, a.OutTime, a.InTime, a.Comments, a.Status, a.DocumentStatus,a.Approver, c.Name, a.CreateTime," +
" a.ApproveTime, a.CreateUser,d.Name" +
" from MovementRegister a, ViewUserEmployeeList b, ViewUserEmployeeList c,ViewUserEmployeeList d" +
" where a.EmployeeID=b.EmployeeID and a.Approver = c.UserID and a.CreateUser = d.UserID and (a.Approver='" + Login.userLoggedIn +
"' and a.Status = 1 and a.DocumentStatus in (1,5) and a.Status <> 98) order by CreateTime desc";
string query4 = "select a.RowID, a.EmployeeID,b.Name, a.ExitTimePlanned, a.ReturnTimePlanned,a.Purpose, " +
" a.ModeOfTravel, a.OutTime, a.InTime, a.Comments, a.Status, a.DocumentStatus,a.Approver, c.Name, a.CreateTime," +
" a.ApproveTime, a.CreateUser,d.Name" +
" from MovementRegister a, ViewUserEmployeeList b, ViewUserEmployeeList c,ViewUserEmployeeList d" +
" where a.EmployeeID=b.EmployeeID and a.Approver = c.UserID and a.CreateUser = d.UserID and a.Approver='" + Login.userLoggedIn +
"' and a.Status = 1 and a.DocumentStatus in (99,2,3,10,4) order by CreateTime desc";
SqlConnection conn = new SqlConnection(Login.connString);
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
case 4:
query = query4;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
mr = new movementregister();
mr.rowID = reader.GetInt32(0);
mr.EmployeeID = reader.GetString(1);
mr.EmployeeName = reader.GetString(2);
mr.ExitTimePlanned = reader.GetDateTime(3);
mr.ReturnTimePlanned = reader.GetDateTime(4);
mr.Purpose = reader.GetString(5);
mr.ModeOfTravel = reader.GetString(6);
mr.OutTime = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
mr.InTime = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
mr.Comments = reader.IsDBNull(9) ? "" : reader.GetString(9);
mr.Status = reader.GetInt32(10);
mr.DocumentStatus = reader.GetInt32(11);
mr.ApproveUser = reader.GetString(12);
mr.Approver = reader.GetString(13);
mr.CreateTime = reader.GetDateTime(14);
mr.ApproveTime = reader.IsDBNull(15) ? DateTime.MinValue : reader.GetDateTime(15);
mr.CreateUser = reader.GetString(16);
mr.Creator = reader.GetString(17);
MRlist.Add(mr);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying MovementReg Data");
}
return(MRlist);
}