public bool ChangePass(string _sNewUN, string sUserPass, string sOldUN)
{
bool _bReturTF = false;
try
{
bool _bNewUNIsExist = false;
_bNewUNIsExist = ExecuteQueryFunctions.ExeSclr(_conn, "SELECT * FROM User_Table AS ut WHERE user_name='" + _sNewUN + "'");
if (_bNewUNIsExist)
{
throw new Exception("This user name already exist. Try another please.");
}
else
{
string QueryString = "UPDATE User_Table SET user_name='" + _sNewUN + "',user_pass='******',UpdateBy=" + EMSGlobal._nCurrentUserID + " WHERE user_name='" + sOldUN + "' AND user_id=" + EMSGlobal._nCurrentUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
_bReturTF = true;
}
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(_bReturTF);
}
public ID Save(BankAccount oBankAccount)
{
try
{
if (oBankAccount.IsNew)
{
BusinessObject.Factory.SetID(oBankAccount, new ID(ExecuteQueryFunctions.GetNewID(_conn, "SELECT MAX(ObjectID) FROM [tbl_bankAccount]")));
string QueryString = "INSERT INTO [tbl_bankAccount] (ObjectID,bankID,branchName,bankName,accountNumber,accountType,currentBalance,GLhead,DBUserID,DBSDT)"
+ " VALUES ("
+ oBankAccount.ObjectID + ","
+ oBankAccount.bankID + ",'"
+ oBankAccount.branchName + "','"
+ oBankAccount.bankName + "','"
+ oBankAccount.accountNumber + "',"
+ (int)oBankAccount.EnumAccountType + ","
+ oBankAccount.currentBalance + ",'"
+ oBankAccount.GLhead + "',"
+ EMSGlobal._nCurrentUserID + ",'"
+ DateTime.Now + "')";
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
}
BusinessObject.Factory.SetObjectState(oBankAccount, ObjectState.Saved);
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
return(oBankAccount.ID);
}
public ID Save(UserFunctionality oUserFunctionality)
{
bool bIsExist = false;
try
{
if (oUserFunctionality.IsNew)
{
string QueryString = "SELECT COUNT(*) FROM Function_permission_Table WHERE UFID=" + oUserFunctionality.UFID + " AND user_id=" + oUserFunctionality.User_ID;
bIsExist = ExecuteQueryFunctions.ExeSclr(_conn, QueryString);
if (!bIsExist)
{
BusinessObject.Factory.SetID(oUserFunctionality, new ID(ExecuteQueryFunctions.GetNewID(_conn, "SELECT MAX(UFPID) FROM Function_permission_Table")));
string QueryString2 = "INSERT INTO Function_permission_Table (UFPID,UFID,user_id,DBUserID,DBSDT)"
+ "VALUES(" +
oUserFunctionality.ObjectID + "," +
oUserFunctionality.UFID + "," +
oUserFunctionality.User_ID + "," +
EMSGlobal._nCurrentUserID + ",'" +
DateTime.Now + "')";
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString2);
}
}
BusinessObject.Factory.SetObjectState(oUserFunctionality, ObjectState.Saved);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oUserFunctionality.ID);
}
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 void LogOut(int CurrentUserID, bool bLogOut)
{
try
{
string QueryString = "UPDATE User_Table SET user_islogon=0 WHERE [user_id]=" + CurrentUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
}
public void UserStatus(int CurrentUserID, int _nUStatus)
{
try
{
string QueryString = "UPDATE User_Table SET user_status=" + _nUStatus + " WHERE user_id=" + CurrentUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
}
public void DeletePermission(int nMenuID, int nUserID)
{
try
{
string QueryString = "DELETE FROM Menu_Permission_Table WHERE MenuID=" + nMenuID + " AND user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
}
public bool UserStatus(int nSelectedUserID, int _nUStatus)
{
try
{
string QueryString = "UPDATE User_Table SET user_status=" + _nUStatus + " WHERE user_id=" + nSelectedUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
}
public bool UpdateUser_IsLogon(int nUserID, bool LogUnlog)
{
try
{
string QueryString = "UPDATE User_Table SET user_islogon='" + LogUnlog + "' WHERE user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public bool Delete(int nBrnID)
{
try
{
string QueryString = "DELETE FROM tbl_Branchs WHERE brn_id=" + nBrnID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public bool UpdateUser(NewUser oNewUser, int nEditID)
{
try
{
string QueryString = "UPDATE User_Table SET [user_fst_name]='" + oNewUser.user_fst_name + "', [user_lst_name]='" + oNewUser.user_lst_name + "',[user_type]=" + (int)oNewUser.user_type + ", [UpdateBy]=" + EMSGlobal._nCurrentUserID + " WHERE user_id=" + nEditID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
//public void Delete(int oID)
//{
// try
// {
// SqlConnection conn = new SqlConnection(_connectionString);
// UserFunctionalityDA.Delete(conn, oID);
// conn.Close();
// }
// catch (Exception e)
// {
// throw new ServiceException(e.Message, e);
// }
//}
public bool RemoveFunction(int nUFID, int nUserID)
{
try
{
string QueryString = "DELETE Function_permission_Table WHERE UFID =" + nUFID + " AND user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
_conn.Close();
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public void UpdateUser_IsLock(int oID, bool LockUnlock)
{
try
{
SqlConnection conn = new SqlConnection(EMSConFunc.ConString());
string QueryString = "UPDATE User_Table SET user_lock='" + LockUnlock + "' WHERE user_id=" + oID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
}
public bool UserDelete(int nUserID)
{
try
{
string QueryString = "DELETE FROM User_Table WHERE user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
string QueryString2 = "DELETE FROM Menu_Permission_Table WHERE user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString2);
string QueryString3 = "DELETE FROM [Function_permission_Table] WHERE user_id=" + nUserID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString3);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public bool UpdateBranch(AddBranch oAddBranch, int nBrnID)
{
try
{
string QueryString = "UPDATE tbl_Branchs SET " +
"brn_type=" + (int)oAddBranch.ebrn_type + "," +
"brn_title='" + oAddBranch.brn_title + "'," +
"brn_location='" + oAddBranch.brn_location + "'," +
"DBUserID=" + EMSGlobal._nCurrentUserID + "," +
"DBSDT='" + DateTime.Now + "' " +
"WHERE brn_id=" + nBrnID + "";
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public bool UpDateBankAccount(BankAccount oBankAccount, int nEditID)
{
try
{
string QueryString = "UPDATE [tbl_bankAccount] SET "
+ "branchName = '" + oBankAccount.branchName + "',"
+ "accountNumber = '" + oBankAccount.accountNumber + "',"
+ "accountType = " + (int)oBankAccount.EnumAccountType + ","
+ "UpdateBy = " + EMSGlobal._nCurrentUserID + ""
+ " WHERE ObjectID=" + nEditID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public ID Save(NewUser oNewUser)
{
try
{
if (oNewUser.IsNew)
{
BusinessObject.Factory.SetID(oNewUser, new ID(ExecuteQueryFunctions.GetNewID(_conn, "SELECT MAX(user_id) FROM User_Table")));
string sSN = "0000";
sSN = sSN.Substring(0, sSN.Length - oNewUser.ObjectID.ToString().Length) + oNewUser.ObjectID;
oNewUser.user_name = "User" + sSN;
oNewUser.user_pass = "******";
oNewUser.user_status = EnumUserStatus.Active;
oNewUser.user_code = (int)oNewUser.user_type + oNewUser.ObjectID.ToString();
string QueryString2 = "INSERT INTO User_Table ([user_id],[brn_id],[user_code],[user_fst_name],[user_lst_name],[user_name_ini],[user_name],[user_pass],[user_type],[user_islogon],[user_lock],[user_status],[OwnerName],[OwnerID],[DBSDT])"
+ "VALUES(" +
oNewUser.ObjectID + "," +
"0,'" +
oNewUser.user_code + "','" +
oNewUser.user_fst_name + "','" +
oNewUser.user_lst_name + "','" +
oNewUser.user_name + "','" +
oNewUser.user_name + "','" +
oNewUser.user_pass + "'," +
(int)oNewUser.user_type + ",'" +
oNewUser.user_islogon + "','" +
oNewUser.user_lock + "'," +
(int)oNewUser.user_status + ",'" +
EMSGlobal._sCurrenUserName + "'," +
EMSGlobal._nCurrentUserID + ",'" +
DateTime.Now + "')";
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString2);
}
BusinessObject.Factory.SetObjectState(oNewUser, ObjectState.Saved);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oNewUser.ID);
}
public bool Delete(int nID)
{
try
{
string sRetStr = ExecuteQueryFunctions.ExeRetStr(_conn, "SELECT CONVERT(VARCHAR,ISNULL(currentBalance,0)) FROM [tbl_bankAccount] WHERE ObjectID=" + nID);
if (Convert.ToDouble(sRetStr) > 0)
{
throw new ServiceException("Delete not posible, It has balance.!");
}
else
{
string QueryString = "DELETE FROM [tbl_bankAccount] WHERE ObjectID=" + nID;
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString);
return(true);
}
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
}
public ID Save(AddBranch oAddBranch)
{
bool _isBranchExist = false;
try
{
if (oAddBranch.IsNew)
{
string QueryString = "SELECT * FROM tbl_Branchs WHERE brn_title='" + oAddBranch.brn_title + "'";
_isBranchExist = ExecuteQueryFunctions.ExeIsExist(QueryString);
if (!_isBranchExist)
{
BusinessObject.Factory.SetID(oAddBranch, new ID(ExecuteQueryFunctions.GetNewID(_conn, "SELECT MAX(brn_id) FROM tbl_Branchs")));
string QueryString2 = "INSERT INTO tbl_Branchs (brn_id,brn_type,brn_title,brn_location,DBUserID,DBSDT)"
+ "VALUES(" +
oAddBranch.ObjectID + "," +
(int)oAddBranch.ebrn_type + ",'" +
oAddBranch.brn_title + "','" +
oAddBranch.brn_location + "'," +
EMSGlobal._nCurrentUserID + ",'" +
DateTime.Now + "')";
ExecuteQueryFunctions.ExeNonQuery(_conn, QueryString2);
}
else
{
throw new ServiceException("This Title Already Exist.");
}
}
BusinessObject.Factory.SetObjectState(oAddBranch, ObjectState.Saved);
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oAddBranch.ID);
}
