// `AddVisitorCheckInOut`(IN p_name VARCHAR(100),IN p_address VARCHAR(500), IN p_fromcompany VARCHAR(100),
//IN p_tocompany INT, IN p_telno VARCHAR(20),IN p_persontovisit VARCHAR(100), IN p_eventid INT, IN p_temp INT,
//IN p_visitortype INT, IN p_keys VARCHAR(100), IN p_passid INT, IN p_gateid INT, IN p_vehicleplatenumber VARCHAR(20),
//IN p_guardname VARCHAR(100),IN p_remarks VARCHAR(500), IN p_checkintime DATETIME, IN p_checkouttime DATETIME)
public int InsertVisitorCheckInOut(VisitorCheckInOut p_VisitorCheckInOut)
{
try
{
int result = 0;
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("p_nricid", p_VisitorCheckInOut.nricid),
new MySqlParameter("p_name", p_VisitorCheckInOut.name),
new MySqlParameter("p_address", p_VisitorCheckInOut.address),
new MySqlParameter("p_fromcompany", p_VisitorCheckInOut.fromcompany),
new MySqlParameter("p_tocompany", p_VisitorCheckInOut.tocompany),
new MySqlParameter("p_telno", p_VisitorCheckInOut.telno),
new MySqlParameter("p_persontovisit", p_VisitorCheckInOut.persontovisit),
new MySqlParameter("p_eventid", p_VisitorCheckInOut.eventid),
new MySqlParameter("p_temp", p_VisitorCheckInOut.temp),
new MySqlParameter("p_visitortype", p_VisitorCheckInOut.visitortypeID),
new MySqlParameter("p_keyid", p_VisitorCheckInOut.keyid),
new MySqlParameter("p_passid", p_VisitorCheckInOut.passid),
new MySqlParameter("p_gateid", p_VisitorCheckInOut.gateid),
new MySqlParameter("p_vehicleplatenumber", p_VisitorCheckInOut.vehicleplatenumber),
new MySqlParameter("p_guardname", p_VisitorCheckInOut.guardname),
new MySqlParameter("p_remarks", p_VisitorCheckInOut.remarks),
new MySqlParameter("p_checkintime", p_VisitorCheckInOut.checkintime),
new MySqlParameter("p_checkouttime", p_VisitorCheckInOut.checkouttime),
new MySqlParameter("p_snapshotpath", p_VisitorCheckInOut.snapshotpath),
new MySqlParameter("p_deploymentid", p_VisitorCheckInOut.deploymentid),
new MySqlParameter("p_blockorphone", p_VisitorCheckInOut.blockorphone),
new MySqlParameter("p_fullnricid", p_VisitorCheckInOut.fullnricid),
//new MySqlParameter("p_visitingcompany",p_VisitorCheckInOut.visitingcompany),
new MySqlParameter("p_purpose", p_VisitorCheckInOut.purpose),
new MySqlParameter("p_IsBlockGuest", p_VisitorCheckInOut.IsBlockGuest),
new MySqlParameter("p_BlockType", p_VisitorCheckInOut.BlockType),
new MySqlParameter("p_BlockRemarks", p_VisitorCheckInOut.BlockRemarks),
new MySqlParameter("p_BlockDays", 0),
new MySqlParameter("p_BlockedDate", p_VisitorCheckInOut.BlockedDate),
new MySqlParameter("p_UnblockedDate", p_VisitorCheckInOut.UnblockedDate),
new MySqlParameter("p_result", MySqlDbType.Int32, 2, ParameterDirection.Output, false, 1, 1, "Out", DataRowVersion.Default, result)
};
return(MySQLDB.MySQLDBHelper.ExecuteNonQuery("AddVisitorCheckInOut", CommandType.StoredProcedure, parameters));
}
catch (Exception ex)
{
throw ex;
}
}
public VisitorCheckInOut SelectVisitorCheckInOutByID(String p_NRICID, int p_EventId)
{
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("p_nricid", p_NRICID),
new MySqlParameter("p_EventId", p_EventId)
};
string sqlQuery = "SELECT `nricid`,`name`,`address`,`fromcompany`,`tocompany`,`telno`,`persontovisit`,`eventid`,`temp`,`visitortype`,`keyid`,`passid`,`gateid`,`vehicleplatenumber`,`guardname`,`remarks`,`checkintime`,`checkouttime`,`snapshotpath`,`deploymentid`,`Purpose`,`IsBlockGuest`,`BlockType`,`BlockRemarks`,`BlockDays`,`BlockedDate`,`UnblockedDate` FROM `visitorcheckinout` WHERE `nricid` = '"+ p_NRICID + "' AND `eventid` = " + p_EventId + " ORDER BY `checkintime` DESC;";
DataTable dt = MySQLDB.MySQLDBHelper.ExecuteSelectCommand(sqlQuery, CommandType.Text);
//DataTable dt = MySQLDB.MySQLDBHelper.ExecuteSelectCommand("GetVisitorCheckInOutByID", CommandType.StoredProcedure, parameters);
VisitorCheckInOut objVisitorCheckInOut = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
objVisitorCheckInOut = new VisitorCheckInOut();
objVisitorCheckInOut.nricid = dr["nricid"].ToString();
objVisitorCheckInOut.passid = dr["passid"] != null && dr["passid"].ToString() != "" ? Convert.ToInt32(dr["passid"]) : 0;
objVisitorCheckInOut.address = dr["address"].ToString();
//objVisitorCheckInOut.blockorphone = dr["blockorphone"].ToString();
objVisitorCheckInOut.checkintime = Convert.ToDateTime(dr["checkintime"].ToString());
if (dr["checkouttime"].ToString() == null && dr["checkouttime"].ToString() == "")
{
objVisitorCheckInOut.checkouttime = Convert.ToDateTime(dr["checkouttime"].ToString());
}
//objVisitorCheckInOut.eventid = Convert.ToInt32(dr["eventid"].ToString());
objVisitorCheckInOut.fromcompany = dr["fromcompany"].ToString();
objVisitorCheckInOut.gateid = dr["gateid"] != null && dr["gateid"].ToString() != "" ? Convert.ToInt32(dr["gateid"]) : 0;
objVisitorCheckInOut.guardname = dr["guardname"].ToString();
objVisitorCheckInOut.keyid = dr["keyid"] != null && dr["keyid"].ToString() != "" ? Convert.ToInt32(dr["keyid"]) : 0;
objVisitorCheckInOut.name = dr["name"].ToString();
objVisitorCheckInOut.persontovisit = dr["persontovisit"].ToString();
objVisitorCheckInOut.remarks = dr["remarks"].ToString();
objVisitorCheckInOut.telno = dr["telno"].ToString();
objVisitorCheckInOut.temp = dr["temp"] != null && dr["temp"].ToString() != "" ? Convert.ToInt32(dr["temp"].ToString()) : 0;
//objVisitorCheckInOut.tocompany = Convert.ToInt32(dr["tocompany"].ToString());
objVisitorCheckInOut.snapshotpath = dr["snapshotpath"].ToString();
objVisitorCheckInOut.deploymentid = dr["deploymentid"] != null && dr["deploymentid"].ToString() != "" ? Convert.ToInt32(dr["deploymentid"]) : 0;
objVisitorCheckInOut.visitortypeID = dr["visitortype"] != null && dr["visitortype"].ToString() != "" ? Convert.ToInt32(dr["visitortype"]) : 0;
//objVisitorCheckInOut.visitingcompany = dr["visitingcompany"].ToString();
objVisitorCheckInOut.purpose = dr["purpose"].ToString();
objVisitorCheckInOut.IsBlockGuest = dr["IsBlockGuest"] != null && dr["IsBlockGuest"].ToString() != "" ? Convert.ToInt32(dr["IsBlockGuest"]) : 0;
objVisitorCheckInOut.BlockType = dr["BlockType"].ToString();
objVisitorCheckInOut.BlockDays = dr["BlockDays"] != null && dr["BlockDays"].ToString() != "" ? Convert.ToInt32(dr["BlockDays"].ToString()) : 0;
objVisitorCheckInOut.BlockRemarks = dr["BlockRemarks"].ToString();
if (dr["BlockedDate"].ToString() == null && dr["BlockedDate"].ToString() == "")
{
objVisitorCheckInOut.BlockedDate = Convert.ToDateTime(dr["BlockedDate"]);
}
if (dr["UnblockedDate"].ToString() == null && dr["UnblockedDate"].ToString() == "")
{
objVisitorCheckInOut.UnblockedDate = Convert.ToDateTime(dr["UnblockedDate"]);
}
}
dt.Clear();
dt.Dispose();
return(objVisitorCheckInOut);
}
public List <VisitorCheckInOut> SelectVisitorCheckInOutByFilters(int currentUserType, int deploymentId, int startdate, int startmonth, int startyear, int enddate, int endmonth, int endyear)
{
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("currentUserType", currentUserType),
new MySqlParameter("p_deploymentId", deploymentId)
};
List <VisitorCheckInOut> VisitorCheckInOutCol = new List <VisitorCheckInOut>();
DataTable dt = MySQLDB.MySQLDBHelper.ExecuteSelectCommand("GetVisitorCheckInOut", CommandType.StoredProcedure, parameters);
VisitorCheckInOut objVisitorCheckInOut = null;
int i = 1;
switch (startmonth)
{
case 2:
if (startdate > 28)
{
if (startyear % 4 == 0)
{
startdate = 29;
}
else
{
startdate = 28;
}
}
break;
case 4:
case 6:
case 9:
case 11:
if (startdate > 30)
{
startdate = 30;
}
break;
default:
break;
}
DateTime startDate = new DateTime();
try
{
startDate = new DateTime(startyear, startmonth, startdate);
}
catch (Exception ex)
{
}
switch (endmonth)
{
case 2:
if (enddate > 28)
{
if (endyear % 4 == 0)
{
enddate = 29;
}
else
{
enddate = 28;
}
}
break;
case 4:
case 6:
case 9:
case 11:
if (enddate > 30)
{
enddate = 30;
}
break;
default:
break;
}
DateTime endDate = new DateTime();
try
{
endDate = new DateTime(endyear, endmonth, enddate);
}
catch (Exception ex)
{
}
foreach (DataRow dr in dt.Rows)
{
DateTime dte = Convert.ToDateTime(dr["checkintime"].ToString());
if (dte != null)
{
//if (startDate == dte || endDate == dte || (startDate < dte && endDate > dte))
if (startDate == dte || endDate == dte || startDate < dte)
{
objVisitorCheckInOut = new VisitorCheckInOut();
objVisitorCheckInOut.visitorid = Convert.ToInt32(dr["visitorid"].ToString());
objVisitorCheckInOut.nricid = dr["nricid"].ToString();
objVisitorCheckInOut.passid = dr["passid"] != null && dr["passid"].ToString() != "" ? Convert.ToInt32(dr["passid"].ToString()) : 0;
objVisitorCheckInOut.address = dr["address"].ToString();
objVisitorCheckInOut.blockorphone = dr["blockorphone"].ToString();
objVisitorCheckInOut.checkintime = Convert.ToDateTime(dr["checkintime"].ToString());
if (dr["checkouttime"].ToString() != null && dr["checkouttime"].ToString() != "")
{
objVisitorCheckInOut.checkouttime = Convert.ToDateTime(dr["checkouttime"].ToString());
}
objVisitorCheckInOut.eventid = dr["eventid"] != null && dr["eventid"].ToString() != "" ? Convert.ToInt32(dr["eventid"].ToString()) : 0;
objVisitorCheckInOut.fromcompany = dr["fromcompany"].ToString();
objVisitorCheckInOut.gateid = dr["gateid"] != null && dr["gateid"].ToString() != "" ? Convert.ToInt32(dr["gateid"].ToString()) : 0;
objVisitorCheckInOut.guardname = dr["guardname"].ToString();
objVisitorCheckInOut.keyid = dr["keyid"] != null && dr["keyid"].ToString() != "" ? Convert.ToInt32(dr["keyid"]) : 0;
objVisitorCheckInOut.name = dr["name"].ToString();
objVisitorCheckInOut.persontovisit = dr["persontovisit"].ToString();
objVisitorCheckInOut.remarks = dr["remarks"].ToString();
objVisitorCheckInOut.telno = dr["telno"].ToString();
objVisitorCheckInOut.temp = dr["temp"] != null && dr["temp"].ToString() != "" ? Convert.ToInt32(dr["temp"].ToString()) : 0;
//objVisitorCheckInOut.tocompany = Convert.ToInt32(dr["tocompany"].ToString());
objVisitorCheckInOut.snapshotpath = dr["snapshotpath"].ToString();
objVisitorCheckInOut.deploymentid = dr["deploymentid"] != null && dr["deploymentid"].ToString() != "" ? Convert.ToInt32(dr["deploymentid"]) : 0;
//objVisitorCheckInOut.visitingcompany = dr["visitingcompany"].ToString();
objVisitorCheckInOut.purpose = dr["purpose"].ToString();
objVisitorCheckInOut.IsBlockGuest = dr["IsBlockGuest"] != null && dr["IsBlockGuest"].ToString() != "" ? Convert.ToInt32(dr["IsBlockGuest"].ToString()) : 0;
objVisitorCheckInOut.BlockType = dr["BlockType"].ToString();
objVisitorCheckInOut.BlockDays = dr["BlockDays"] != null && dr["BlockDays"].ToString() != "" ? Convert.ToInt32(dr["BlockDays"].ToString()) : 0;;
objVisitorCheckInOut.BlockRemarks = dr["BlockRemarks"].ToString();
if (dr["BlockedDate"].ToString() != null && dr["BlockedDate"].ToString() != "")
{
objVisitorCheckInOut.BlockedDate = Convert.ToDateTime(dr["BlockedDate"]);
}
if (dr["UnblockedDate"].ToString() != null && dr["UnblockedDate"].ToString() != "")
{
objVisitorCheckInOut.UnblockedDate = Convert.ToDateTime(dr["UnblockedDate"]);
}
VisitorCheckInOutCol.Add(objVisitorCheckInOut);
i = i + 1;
}
}
}
dt.Clear();
dt.Dispose();
return(VisitorCheckInOutCol);
}
public List <VisitorCheckInOut> SelectVisitorCheckInOut(int currentUserType, int deploymentId)
{
MySqlParameter[] parameters = new MySqlParameter[] {
new MySqlParameter("currentUserType", currentUserType),
new MySqlParameter("p_deploymentId", deploymentId)
};
List <VisitorCheckInOut> VisitorCheckInOutCol = new List <VisitorCheckInOut>();
DataTable dt = MySQLDB.MySQLDBHelper.ExecuteSelectCommand("GetVisitorCheckInOut", CommandType.StoredProcedure, parameters);
VisitorCheckInOut objVisitorCheckInOut = null;
foreach (DataRow dr in dt.Rows)
{
objVisitorCheckInOut = new VisitorCheckInOut();
objVisitorCheckInOut.visitorid = Convert.ToInt32(dr["visitorid"].ToString());
objVisitorCheckInOut.nricid = dr["nricid"].ToString();
objVisitorCheckInOut.passid = dr["passid"] != null && dr["passid"].ToString() != "" ? Convert.ToInt32(dr["passid"].ToString()) : 0;
objVisitorCheckInOut.address = dr["address"].ToString();
objVisitorCheckInOut.blockorphone = dr["blockorphone"].ToString();
objVisitorCheckInOut.checkintime = Convert.ToDateTime(dr["checkintime"].ToString());
if (dr["checkouttime"].ToString() != null && dr["checkouttime"].ToString() != "")
{
objVisitorCheckInOut.checkouttime = Convert.ToDateTime(dr["checkouttime"].ToString());
}
objVisitorCheckInOut.eventid = dr["eventid"] != null && dr["eventid"].ToString() != "" ? Convert.ToInt32(dr["eventid"].ToString()) : 0;
objVisitorCheckInOut.fromcompany = dr["fromcompany"].ToString();
objVisitorCheckInOut.gateid = dr["gateid"] != null && dr["gateid"].ToString() != "" ? Convert.ToInt32(dr["gateid"].ToString()) : 0;
objVisitorCheckInOut.guardname = dr["guardname"].ToString();
objVisitorCheckInOut.keyid = dr["keyid"] != null && dr["keyid"].ToString() != "" ? Convert.ToInt32(dr["keyid"].ToString()) : 0;
objVisitorCheckInOut.name = dr["name"].ToString();
objVisitorCheckInOut.persontovisit = dr["persontovisit"].ToString();
objVisitorCheckInOut.remarks = dr["remarks"].ToString();
objVisitorCheckInOut.telno = dr["telno"].ToString();
objVisitorCheckInOut.temp = dr["temp"] != null && dr["temp"].ToString() != "" ? Convert.ToInt32(dr["temp"].ToString()) : 0;
//objVisitorCheckInOut.tocompany = Convert.ToInt32(dr["tocompany"].ToString());
objVisitorCheckInOut.snapshotpath = dr["snapshotpath"].ToString();
if (dr["NRICFront"].ToString() != null && dr["NRICFront"].ToString() != "")
{
objVisitorCheckInOut.NRICFront = ConfigurationManager.AppSettings["GetNRICPath"] + "/" + dr["NRICFront"].ToString();
}
if (dr["NRICBack"].ToString() != null && dr["NRICBack"].ToString() != "")
{
objVisitorCheckInOut.NRICBack = ConfigurationManager.AppSettings["GetNRICPath"] + "/" + dr["NRICBack"].ToString();
}
objVisitorCheckInOut.deploymentid = dr["deploymentid"] != null && dr["deploymentid"].ToString() != "" ? Convert.ToInt32(dr["deploymentid"]) : 0;
// objVisitorCheckInOut.visitortypeID = Convert.ToInt32(dr["visitortypeID"].ToString());
//objVisitorCheckInOut.visitingcompany = dr["visitingcompany"].ToString();
objVisitorCheckInOut.purpose = dr["purpose"].ToString();
objVisitorCheckInOut.IsBlockGuest = dr["IsBlockGuest"] != null && dr["IsBlockGuest"].ToString() != "" ? Convert.ToInt32(dr["IsBlockGuest"]) : 0;
objVisitorCheckInOut.BlockType = dr["BlockType"].ToString();
objVisitorCheckInOut.BlockDays = dr["BlockDays"] != null && dr["BlockDays"].ToString() != "" ? Convert.ToInt32(dr["BlockDays"].ToString()) : 0;
objVisitorCheckInOut.BlockRemarks = dr["BlockRemarks"].ToString();
if (dr["BlockedDate"].ToString() != null && dr["BlockedDate"].ToString() != "")
{
objVisitorCheckInOut.BlockedDate = Convert.ToDateTime(dr["BlockedDate"]);
}
if (dr["UnblockedDate"].ToString() != null && dr["UnblockedDate"].ToString() != "")
{
objVisitorCheckInOut.UnblockedDate = Convert.ToDateTime(dr["UnblockedDate"]);
}
VisitorCheckInOutCol.Add(objVisitorCheckInOut);
}
dt.Clear();
dt.Dispose();
return(VisitorCheckInOutCol);
}
public int EditVisitorCheckInOut(VisitorCheckInOut p_VisitorCheckInOut)
{
return(objVisitorCheckInOutDBA.UpadateVisitorCheckInOut(p_VisitorCheckInOut));
}
public int AddVisitorCheckInOut(VisitorCheckInOut p_VisitorCheckInOut)
{
return(objVisitorCheckInOutDBA.InsertVisitorCheckInOut(p_VisitorCheckInOut));
}
