public bool PWReset(int nUserID)
{
bool _bReturTF = false;
try
{
NewUser oNewUser = new NewUser();
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, "SELECT * FROM User_Table AS ut WHERE [user_id]=" + nUserID);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oNewUser = CreateObject(oReader);
}
reader.Close();
_conn.Close();
string QueryString = "UPDATE User_Table SET user_name='" + oNewUser.user_name_ini + "',user_pass='******',UpdateBy=" + EMSGlobal._nCurrentUserID + " WHERE user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
_bReturTF = true;
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(_bReturTF);
}
public DataSet GetBrnsbyDS()
{
DataSet oDataSet = new DataSet();
try
{
string QueryString = "SELECT * FROM tbl_Branchs ORDER BY brn_id";
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oDataSet.Load(reader, LoadOption.OverwriteChanges, new string[1]);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
return(oDataSet);
}
public AddBranchs Gets(int nID)
{
AddBranchs oAddBranchs = null;
try
{
string QueryString = "SELECT * FROM tbl_Branchs WHERE brn_id IN (SELECT brn_id FROM tbl_TeacherAsign WHERE tch_id=" + nID + ")";
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oAddBranchs = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException("Failed to Get AddBranchs", e);
}
return(oAddBranchs);
}
public AddBranchs GetsByType(int nbrn_type)
{
AddBranchs oAddBranchs = null;
try
{
string QueryString = "SELECT * FROM tbl_Branchs WHERE brn_type=" + nbrn_type;
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oAddBranchs = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException("Failed to Get AddBranchs", e);
}
return(oAddBranchs);
}
public NewUsers Get()
{
NewUsers oNewUsers = null;
try
{
IDataReader reader = null;
reader = ExecuteQueryFunctions.ExeReader(_conn, "SELECT * FROM User_Table AS ut WHERE user_id !=1");
oNewUsers = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oNewUsers);
}
public UserFunctions Gets(int nID)
{
UserFunctions oUserFunctions = null;
try
{
string QueryString = "SELECT User_Function_table.*,(SELECT CAST(CASE WHEN FPT.UFID = User_Function_table.UFID and [user_id]=" + nID + " THEN 1 ELSE 0 END AS bit)) as IsCheck FROM User_Function_table Left Outer Join Function_permission_Table AS FPT ON User_Function_table.UFID=FPT.UFID and FPT.[user_id]=" + nID;
IDataReader reader = null;
reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oUserFunctions = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oUserFunctions);
}
//public ID Save(UserFunction oUserFunction)
//{
// try
// {
// SqlConnection conn = new SqlConnection(_connectionString);
// conn.Open();
// if (oUserFunction.IsNew)
// {
// BusinessObject.Factory.SetID(oUserFunction, new ID(ExecuteQueryFunctions.GetNewID(conn, "SELECT MAX(id) FROM Table")));
// string QueryString = "INSERT INTO User_Function_table (UFID,Function_Name,Function_AddedBy,DBSDT)"
// + "VALUES(" +
// oUserFunction.ObjectID + ",'" +
// oUserFunction.Function_Name + "'," +
// EMSGlobal._nCurrentUserID + ",'" +
// DateTime.Now + "')";
// ExecuteQueryFunctions.ExeNonQuery(conn, QueryString);
// }
// else
// {
// UserFunctionDA.Update(conn, oUserFunction);
// }
// conn.Close();
// BusinessObject.Factory.SetObjectState(oUserFunction, ObjectState.Saved);
// }
// catch (Exception e)
// {
// throw new ServiceException(e.Message, e);
// }
// return oUserFunction.ID;
//}
//public void Delete(int oID)
//{
// try
// {
// SqlConnection conn = new SqlConnection(_connectionString);
// UserFunctionDA.Delete(conn, oID);
// conn.Close();
// }
// catch (Exception e)
// {
// throw new ServiceException(e.Message, e);
// }
//}
//public UserFunction Get(int id)
//{
// UserFunction oUserFunction = new UserFunction();
// try
// {
// SqlConnection conn = new SqlConnection(_connectionString);
// IDataReader reader = UserFunctionDA.Get(conn, id);
// NullHandler oReader = new NullHandler(reader);
// if (reader.Read())
// {
// oUserFunction = CreateObject(oReader);
// }
// reader.Close();
// conn.Close();
// }
// catch (Exception e)
// {
// throw new ServiceException(e.Message, e);
// }
// return oUserFunction;
//}
public UserFunctions Get()
{
UserFunctions oUserFunctions = null;
try
{
string QueryString = "SELECT * FROM User_Function_table";
IDataReader reader = null;
reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oUserFunctions = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oUserFunctions);
}
public NewUsers GetsByeString(string sStr)
{
NewUsers oNewUsers = null;
try
{
IDataReader reader = null;
string QueryString = "SELECT * FROM User_Table WHERE user_id !=1 AND " + sStr;
reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oNewUsers = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oNewUsers);
}
public BankAccounts GetsAccounts(int nBrnID)
{
BankAccounts oBankAccounts = null;
try
{
string QueryString = "SELECT * FROM [tbl_bankAccount] WHERE bankID=" + nBrnID;
IDataReader reader = null;
reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oBankAccounts = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oBankAccounts);
}
public ManuInterfaces Gets(int nUserID)
{
ManuInterfaces oManuInterfaces = null;
try
{
string QueryString = "SELECT Menu_Table.*,(SELECT CAST(CASE WHEN UPT.MenuID = Menu_Table.Menu_id and user_id=" + nUserID + " THEN 1 ELSE 0 END AS bit)) as IsCheck FROM Menu_Table Left Outer Join Menu_Permission_Table AS UPT ON Menu_Table.Menu_id=UPT.MenuID and UPT.user_id=" + nUserID + " ORDER BY Menu_id";
IDataReader reader = null;
reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
oManuInterfaces = CreateObjects(reader);
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
return(oManuInterfaces);
}
public NewUser Get(int nID)
{
NewUser oNewUser = new NewUser();
try
{
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, "SELECT * FROM User_Table AS ut WHERE user_id=" + nID);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oNewUser = CreateObject(oReader);
}
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oNewUser);
}
public BankAccount Get(int id)
{
BankAccount oBankAccount = new BankAccount();
try
{
string QueryString = "SELECT * FROM [tbl_bankAccount]";
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oBankAccount = CreateObject(oReader);
}
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oBankAccount);
}
public ManuInterface Get(int id)
{
ManuInterface oManuInterface = new ManuInterface();
try
{
string QueryString = "SELECT Menu_Table.*,(SELECT CAST(CASE WHEN Menu_id=1 THEN 1 ELSE 0 END AS bit)) as IsCheck FROM Menu_Table Where Menu_id=" + id + " ORDER BY Menu_id";
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oManuInterface = CreateObject(oReader);
}
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
return(oManuInterface);
}
public AddBranch Get(int nID)
{
AddBranch oAddBranch = new AddBranch();
try
{
string QueryString = "SELECT * FROM tbl_Branchs WHERE brn_id=" + nID;
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oAddBranch = CreateObject(oReader);
}
reader.Close();
_conn.Close();
}
catch (Exception e)
{
throw new ServiceException("Failed to Get AddBranch", e);
}
return(oAddBranch);
}
public LoginInterface Get(LoginInterface oLoginInterface)
{
try
{
bool result = true;
string sUN = "";
string sUP = "";
//int nUserType = (int) oLoginInterface.user_type;
sUN = oLoginInterface.user_name;
//sUP = EMSGlobal.Encrypt(oLoginInterface.Password);
sUP = oLoginInterface.user_pass;
//SqlCommand cmd = new SqlCommand("SP_GetUserLoginInfo", _conn);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new SqlParameter("@user_name", SqlDbType.VarChar)).Value = oLoginInterface.user_name;
//cmd.Parameters.Add(new SqlParameter("@user_pass", SqlDbType.VarChar)).Value = oLoginInterface.user_pass;
//cmd.Parameters.Add(new SqlParameter("@MacAddres", SqlDbType.VarChar)).Value = oLoginInterface.MacAddres;
//if (_conn.State == ConnectionState.Open) { }
//else { cmd.Connection.Open(); }
//IDataReader reader = cmd.ExecuteReader();
//NullHandler oReader = new NullHandler(reader);
//if (reader.Read())
//{
// oLoginInterface = CreateObject(oReader);
//}
//cmd.Dispose();
//cmd.Connection.Close();
//if (oLoginInterface.ID.ToInt32 > 0)
//{
// if (oLoginInterface.ObjectID == 1)
// { }
// else
// {
// if (oLoginInterface.MacAddres == string.Empty)
// {
// throw new Exception("Unauthorised Machin. Please contact with admin.");
// }
// if (oLoginInterface.IsAuthorise == false)
// {
// throw new Exception("Unauthorised Machin. Please contact with admin.");
// }
// if (oLoginInterface.user_lock == true)
// {
// throw new Exception("Account is Locked. Please contact with admin.");
// }
// if (oLoginInterface.user_status == EnumUserStatus.Suspend)
// {
// throw new Exception("Account has been suspended. Please contact with admin.");
// }
// if (oLoginInterface.user_islogon == true)
// {
// throw new Exception("Someone using this ID. Please Try another ID.");
// }
// string QueryString3 = "UPDATE User_Table SET user_islogon=1 WHERE user_id=" + oLoginInterface.ObjectID;
// ExecuteQueryFunctions.ExeSclr(_conn, QueryString3);
// }
//}
//else
//{
// throw new Exception("Incorrect User ID. Please type a correct User ID.");
//}
string QueryString = "SELECT COUNT(*) FROM User_Table WHERE user_name ='" + sUN + "' AND user_pass ='******'";
result = ExecuteQueryFunctions.ExeSclr(_conn, QueryString);
if (result)
{
string QueryString2 = "SELECT * FROM User_Table WHERE user_name ='" + sUN + "' AND user_pass ='******'";
IDataReader reader = ExecuteQueryFunctions.ExeReader(_conn, QueryString2);
NullHandler oReader = new NullHandler(reader);
if (reader.Read())
{
oLoginInterface = CreateObject(oReader);
}
reader.Close();
//string QueryString3 = "SELECT * FROM tbl_UserSecurity AS TUS WHERE TUS.user_id=" + oLoginInterface.ObjectID;
_conn.Close();
if (oLoginInterface.ObjectID == 1)
{
}
else
{
//if ((int)oLoginInterface.user_type != nUserType)
//{
// throw new Exception("Yor are not authenticated in this type of user. Please select your area.");
//}
if (oLoginInterface.user_lock == true)
{
throw new Exception("Account is Locked. Please contact with admin.");
}
if (oLoginInterface.user_status == EnumUserStatus.Suspend)
{
throw new Exception("Account has been suspended. Please contact with admin.");
}
if (oLoginInterface.user_islogon == true)
{
throw new Exception("Someone using this ID. Please Try another ID.");
}
//string QueryString3 = "UPDATE User_Table SET user_islogon=1 WHERE user_id=" + oLoginInterface.ObjectID;
//ExecuteQueryFunctions.ExeSclr(_conn, QueryString3);
}
//ExecuteQueryFunctions.ExeNonQuery(_conn, "EXEC dbo.SP_UpdateCelcInst");
}
else
{
throw new Exception("Incorrect User ID. Please type a correct User ID.");
}
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
return(oLoginInterface);
}
