internal bool ChangeHashSalt(string UserID, string HashSalt)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
try
{
parameters.Add("p_userid", Convert.ToInt32(UserID), ParameterDirection.Input);
parameters.Add("p_salt", HashSalt, ParameterDirection.Input);
var result = false;
using (MySqlTransaction tran = conn.BeginTransaction())
{
try
{
var a = db.ExecuteNonQuery(conn, tran, CommandType.StoredProcedure, CommonUtility.GetAppSettingKey(Constants.AuthToken.SP_UpdateSalt), parameters);
tran.Commit();
result = true;
}
catch
{
tran.Rollback();
result = false;
}
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public List <Employee> GetEmployee(Employee objEntity)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlDataReader reader = null;
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_associateid", objEntity.associateid, ParameterDirection.Input);
parameters.Add("p_associateno", objEntity.associateno, ParameterDirection.Input);
parameters.Add("p_departmentid", objEntity.departmentid, ParameterDirection.Input);
parameters.Add("p_departmentname", objEntity.departmentname, ParameterDirection.Input);
parameters.Add("p_name", objEntity.name, ParameterDirection.Input);
parameters.Add("p_isactive", objEntity.isactive, ParameterDirection.Input);
using (reader = db.GetReader(conn, CommandType.StoredProcedure, Constants.StoredProcedures.ussp_associate, parameters))
{
dt = db.Load(reader, true);
}
return(CommonUtility.ToList <Employee>(dt));
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public List <user> GetUsers(user obj)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlDataReader reader = null;
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_id", obj.id, ParameterDirection.Input);
parameters.Add("p_userid", obj.userid, ParameterDirection.Input);
parameters.Add("p_usertypeid", obj.usertypeid, ParameterDirection.Input);
parameters.Add("p_firstname", obj.firstname, ParameterDirection.Input);
parameters.Add("p_lastname", obj.lastname, ParameterDirection.Input);
parameters.Add("p_emailid", obj.emailid, ParameterDirection.Input);
parameters.Add("p_mobile", obj.mobile, ParameterDirection.Input);
parameters.Add("p_isenabled", obj.isenabled, ParameterDirection.Input);
parameters.Add("p_islockedout", obj.islockedout, ParameterDirection.Input);
using (reader = db.GetReader(conn, CommandType.StoredProcedure, Constants.StoredProcedures.ussp_user, parameters))
{
dt = db.Load(reader, true);
}
return(CommonUtility.ToList <user>(dt));
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public List <buildingfloor> Getfloors(buildingfloor objbuildingfloor)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlDataReader reader = null;
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_floorid", objbuildingfloor.floorid, ParameterDirection.Input);
parameters.Add("p_floorname", objbuildingfloor.floorname, ParameterDirection.Input);
parameters.Add("p_buildingname", objbuildingfloor.buildingname, ParameterDirection.Input);
parameters.Add("p_isactive", objbuildingfloor.isactive, ParameterDirection.Input);
using (reader = db.GetReader(conn, CommandType.StoredProcedure, Constants.StoredProcedures.ussp_floor, parameters))
{
dt = db.Load(reader, true);
}
return(CommonUtility.ToList <buildingfloor>(dt));
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public List <seat> GetSeats(seat objseat)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlDataReader reader = null;
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_seatid", objseat.seatid, ParameterDirection.Input);
parameters.Add("p_floorid", objseat.floorid, ParameterDirection.Input);
parameters.Add("p_seatlabel", objseat.seatlabel, ParameterDirection.Input);
parameters.Add("p_booked", objseat.isbooked, ParameterDirection.Input);
using (reader = db.GetReader(conn, CommandType.StoredProcedure, Constants.StoredProcedures.ussp_seat, parameters))
{
dt = db.Load(reader, true);
}
return(CommonUtility.ToList <seat>(dt));
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public dashboard GetDashboard(string floorid)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlDataReader reader = null;
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_floorid", floorid, ParameterDirection.Input);
using (reader = db.GetReader(conn, CommandType.StoredProcedure, Constants.StoredProcedures.ussp_dashboard, parameters))
{
dt = db.Load(reader, true);
}
return(CommonUtility.ToList <dashboard>(dt).FirstOrDefault());
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public int SyncLeave(List <seatvacancy> obj)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
try
{
var leavexml = new StringBuilder();
leavexml.Append("<es>");
foreach (var o in obj)
{
//leavexml.Append("<e examineeid=\"" + o.examineeid + "\"");
//leavexml.Append(" rollnumber=\"" + o.rollnumber + "\"");
//leavexml.Append(" examenginetestid=\"" + o.examenginetestid + "\"");
//leavexml.Append(" name=\"" + o.name + "\"");
//leavexml.Append(" idcardnumber=\"" + o.idcardnumber + "\"");
//leavexml.Append(" email=\"" + o.email + "\"");
//leavexml.Append(" fathername=\"" + o.fathername + "\"");
//leavexml.Append(" mothername=\"" + o.mothername + "\"");
//leavexml.Append(" dob=\"" + o.dob + "\"");
//leavexml.Append(" trainingcentreid=\"" + o.trainingcentreid + "\"");
//leavexml.Append(" mobile=\"" + o.mobile + "\"");
//leavexml.Append(" profilepicurl=\"" + o.profilepicurl + "\"");
//leavexml.Append(" profilepicbinary=\"" + o.profilepicbinary + "\" />");
}
leavexml.Append("</es>");
DataTable dt = new DataTable();
parameters.Add("p_leavexml", leavexml.ToString(), ParameterDirection.Input);
int result;
using (MySqlTransaction tran = conn.BeginTransaction())
{
try
{
var ds = db.ExecuteDataSet(conn, tran, CommandType.StoredProcedure, parameters, Constants.StoredProcedures.uasp_leave);
tran.Commit();
result = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
catch (MySqlException ex)
{
tran.Rollback();
throw ex;
}
}
return(result);
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
internal string GetHashSalt(string UserID)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
try
{
parameters.Add("p_userid", Convert.ToInt32(UserID), ParameterDirection.Input);
return(db.GetScalar(conn, CommonUtility.GetAppSettingKey(Constants.AuthToken.SP_GetSalt), parameters, CommandType.StoredProcedure).ToString());
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
internal T ValidateAdminCredentials <T>(T objAdminEntity) where T : new()
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
DataSet dataSet = new DataSet();
try
{
PropertyInfo useridPropertyInfo = typeof(T).GetProperty(CommonUtility.GetAppSettingKey(Constants.AuthToken.UserEntity_UserId));
PropertyInfo passwordPropertyInfo = typeof(T).GetProperty(CommonUtility.GetAppSettingKey(Constants.AuthToken.UserEntity_Password));
var userid = (string)useridPropertyInfo.GetValue(objAdminEntity, null);
var password = (string)passwordPropertyInfo.GetValue(objAdminEntity, null);
MySqlParameter p_userid = new MySqlParameter("p_userid", userid);
p_userid.Direction = ParameterDirection.Input;
MySqlParameter p_password = new MySqlParameter("p_password", password);
p_password.Direction = ParameterDirection.Input;
MySqlParameter p_secretkey = new MySqlParameter("p_secretkey", CommonUtility.GetAppSettingKey(Constants.AuthToken.AES_KEY));
p_secretkey.Direction = ParameterDirection.Input;
MySqlCommand cm = new MySqlCommand(CommonUtility.GetAppSettingKey(Constants.AuthToken.SP_ObtainAuthToken), conn);
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.Parameters.Add(p_userid);
cm.Parameters.Add(p_password);
cm.Parameters.Add(p_secretkey);
MySqlDataAdapter da = new MySqlDataAdapter(cm);
da.Fill(dataSet);
var lstAdminEntity = CommonUtility.ToList <T>(dataSet.Tables[0]);
return(lstAdminEntity.Count > 0 ? lstAdminEntity.First() : objAdminEntity);
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public int AllocateSeat(seatallocation objseatallocation)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_allocationid", objseatallocation.allocationid, ParameterDirection.Input);
parameters.Add("p_employeeno", objseatallocation.employeeno, ParameterDirection.Input);
parameters.Add("p_seatid", objseatallocation.seatno, ParameterDirection.Input);
parameters.Add("p_startdate", objseatallocation.startdate, ParameterDirection.Input);
parameters.Add("p_enddate", objseatallocation.enddate, ParameterDirection.Input);
int result;
using (MySqlTransaction tran = conn.BeginTransaction())
{
try
{
result = db.ExecuteNonQuery(conn, tran, CommandType.StoredProcedure, Constants.StoredProcedures.uasp_seatallocation, parameters);
tran.Commit();
}
catch (MySqlException ex)
{
tran.Rollback();
throw ex;
}
}
return(result);
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
public Int64 SaveUser(user obj)
{
MySqlDatabaseFactory db = new MySqlDatabaseFactory();
Parameters parameters = new Parameters();
MySqlConnection conn = db.GetDatabaseConnection();
try
{
DataTable dt = new DataTable();
parameters.Add("p_id", obj.id, ParameterDirection.Input);
parameters.Add("p_userid", obj.userid, ParameterDirection.Input);
parameters.Add("p_usertypeid", obj.usertypeid, ParameterDirection.Input);
parameters.Add("p_firstname", obj.firstname, ParameterDirection.Input);
parameters.Add("p_middlename", obj.middlename, ParameterDirection.Input);
parameters.Add("p_lastname", obj.lastname, ParameterDirection.Input);
parameters.Add("p_emailid", obj.emailid, ParameterDirection.Input);
parameters.Add("p_phone", obj.phone, ParameterDirection.Input);
parameters.Add("p_mobile", obj.mobile, ParameterDirection.Input);
parameters.Add("p_profilepic", obj.profilepic, ParameterDirection.Input);
parameters.Add("p_country", obj.country, ParameterDirection.Input);
parameters.Add("p_address", obj.address, ParameterDirection.Input);
parameters.Add("p_city", obj.city, ParameterDirection.Input);
parameters.Add("p_state", obj.state, ParameterDirection.Input);
parameters.Add("p_zip", obj.zip, ParameterDirection.Input);
parameters.Add("p_isenabled", obj.isenabled, ParameterDirection.Input);
parameters.Add("p_code", obj.code, ParameterDirection.Input);
parameters.Add("p_hash", obj.hash, ParameterDirection.Input);
parameters.Add("p_password", obj.password, ParameterDirection.Input);
parameters.Add("p_salt", obj.salt, ParameterDirection.Input);
parameters.Add("p_addedby", obj.addedby, ParameterDirection.Input);
parameters.Add("p_editedby", obj.editedby, ParameterDirection.Input);
parameters.Add("p_timestamp", obj.timestamp, ParameterDirection.Input);
parameters.Add("p_islockedout", obj.islockedout, ParameterDirection.Input);
parameters.Add("p_encrykey", obj.encrykey, ParameterDirection.Input);
parameters.Add("p_roles", obj.roles, ParameterDirection.Input);
int result;
using (MySqlTransaction tran = conn.BeginTransaction())
{
try
{
var ds = db.ExecuteDataSet(conn, tran, CommandType.StoredProcedure, parameters, Constants.StoredProcedures.uasp_user);
tran.Commit();
result = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
catch (MySqlException ex)
{
tran.Rollback();
throw ex;
}
}
return(result);
}
catch (MySqlException odbcEx)
{
throw odbcEx;
}
catch (Exception ex)
{
throw ex;
}
finally { db.CloseConnection(conn); }
}
