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); }