public string GetTrnRef()
{
int _nVGMID = 0;
string sRetStr = "";
string _flag = "000";
try
{
string QueryString = "SELECT CONVERT(VARCHAR,ISNULL(MAX(dt.TranRef),0)) FROM tbl_DailyTransactions dt WHERE dt.DBSDT='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
sRetStr = ExecuteQueryFunctions.ExeRetStr(_conn, QueryString);
if (sRetStr == "0")
{
sRetStr = "001";
}
else
{
_nVGMID = Convert.ToInt32(sRetStr.Substring(sRetStr.Length - 3, 3)) + 1;
_flag = _flag.Substring(0, _flag.Length - _nVGMID.ToString().Length);
sRetStr = _flag + _nVGMID;
}
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(sRetStr);
}
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(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 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 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 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 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 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 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(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 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 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 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 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 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 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 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 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 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 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 ID Save(ManuInterface oManuInterface)
{
try
{
if (oManuInterface.IsNew)
{
BusinessObject.Factory.SetID(oManuInterface, new ID(ExecuteQueryFunctions.GetNewID(_conn, "SELECT MAX(Menu_id) FROM Menu_Table")));
//ManuInterfaceDA.Insert(conn, oManuInterface);
}
//else
//{
// ManuInterfaceDA.Update(conn, oManuInterface);
//}
//conn.Close();
BusinessObject.Factory.SetObjectState(oManuInterface, ObjectState.Saved);
}
catch (Exception e)
{
throw new ServiceException(e.Message);
}
return(oManuInterface.ID);
}
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);
}