public static Cstm GetCustomerObjectFromUserName(string connectionString, string userName, Dber dberr)
{
try
{
var customerMasterObject = new Cstm();
var query = string.Format("select * from cstm where cs_uname = '{0}'", userName);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to customer master object
if (data.Tables[0].Rows.Count > 0)
{
return GetCstmObject(data.Tables[0].Rows[0]);
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_NOFIND);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_NOFIND);
return null;
}
}
public static Pkit Read(string connectionString, string id, Dber dberr)
{
try
{
var PkitMasterObject = new Pkit();
var query = string.Format("select * from Pkit where id = {0}", id);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to Error master object
if (data.Tables[0].Rows.Count > 0)
{
PkitMasterObject.id = data.Tables[0].Rows[0]["id"].ToString();
PkitMasterObject.public_key = data.Tables[0].Rows[0]["public_key"].ToString();
PkitMasterObject.private_key = data.Tables[0].Rows[0]["private_key"].ToString();
PkitMasterObject.cs_no = data.Tables[0].Rows[0]["cs_no"].ToString();
return PkitMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PKIT_ERROR);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PKIT_ERROR);
return null;
}
}
public static Txnm Read(string connectionString, string code, Dber dberr)
{
try
{
var transactionTypeMasterObject = new Txnm();
var query = string.Format("select * from txnm where tran_code = '{0}'", code);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
if (data.Tables[0].Rows.Count > 0)
{
transactionTypeMasterObject.tran_desc = data.Tables[0].Rows[0]["tran_desc"] != null ? data.Tables[0].Rows[0]["tran_desc"].ToString() : "";
transactionTypeMasterObject.tran_code = data.Tables[0].Rows[0]["tran_code"] != null ? data.Tables[0].Rows[0]["tran_code"].ToString() : "";
transactionTypeMasterObject.tran_fin_type = data.Tables[0].Rows[0]["tran_fin_type"] != null ? data.Tables[0].Rows[0]["tran_fin_type"].ToString() : "";
transactionTypeMasterObject.tran_id = data.Tables[0].Rows[0]["tran_id"].ToString();
transactionTypeMasterObject.tran_pvga = Convert.ToInt16(data.Tables[0].Rows[0]["tran_pvga"].ToString());
transactionTypeMasterObject.tran_pvgb = Convert.ToInt16(data.Tables[0].Rows[0]["tran_pvgb"].ToString());
return transactionTypeMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_TXNM_NOFIND);
return null;
}
}
catch //(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_TXNM_NOFIND);
return null;
}
}
public static Cstm Read(string connectionString, string id, Dber dberr)
{
try
{
var customerMasterObject = new Cstm();
var query = string.Format("select * from cstm where cs_no = {0}", id);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to customer master object
if (data.Tables[0].Rows.Count > 0)
{
return GetCstmObject(data.Tables[0].Rows[0]);
//customerMasterObject.cs_no = data.Tables[0].Rows[0]["cs_no"].ToString();
//customerMasterObject.cs_type = data.Tables[0].Rows[0]["cs_type"].ToString();
//customerMasterObject.cs_fname = data.Tables[0].Rows[0]["cs_fname"].ToString();
//customerMasterObject.cs_mname = data.Tables[0].Rows[0]["cs_mname"].ToString();
//customerMasterObject.cs_lname = data.Tables[0].Rows[0]["cs_lname"].ToString();
//customerMasterObject.cs_access = data.Tables[0].Rows[0]["cs_access"].ToString();
//customerMasterObject.cs_addr1 = data.Tables[0].Rows[0]["cs_addr1"].ToString();
//customerMasterObject.cs_addr2 = data.Tables[0].Rows[0]["cs_addr2"].ToString();
//customerMasterObject.cs_branch = data.Tables[0].Rows[0]["cs_branch"].ToString();
//customerMasterObject.cs_city = data.Tables[0].Rows[0]["cs_city"].ToString();
//customerMasterObject.cs_email = data.Tables[0].Rows[0]["cs_email"].ToString();
//customerMasterObject.cs_phn = data.Tables[0].Rows[0]["cs_phn"].ToString();
//customerMasterObject.cs_zip = data.Tables[0].Rows[0]["cs_zip"].ToString();
//customerMasterObject.cs_uname = data.Tables[0].Rows[0]["cs_uname"].ToString();
//customerMasterObject.cs_pass = data.Tables[0].Rows[0]["cs_pass"].ToString();
//customerMasterObject.cs_secq1 = data.Tables[0].Rows[0]["cs_sec_qs1"].ToString();
//customerMasterObject.cs_secq2 = data.Tables[0].Rows[0]["cs_sec_qs2"].ToString();
//customerMasterObject.cs_secq3 = data.Tables[0].Rows[0]["cs_sec_qs3"].ToString();
//customerMasterObject.cs_state = data.Tables[0].Rows[0]["cs_state"].ToString();
//customerMasterObject.cs_uid = data.Tables[0].Rows[0]["cs_uid"].ToString();
//customerMasterObject.cs_ans1 = data.Tables[0].Rows[0]["cs_sec_ans1"].ToString();
//customerMasterObject.cs_ans2 = data.Tables[0].Rows[0]["cs_sec_ans2"].ToString();
//customerMasterObject.cs_ans3 = data.Tables[0].Rows[0]["cs_sec_ans3"].ToString();
//return customerMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_NOFIND);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_NOFIND);
return null;
}
}
public static Empm Read(string connectionString, string emp_id, Dber dberr)
{
try
{
var employeeMasterObject = new Empm();
var query = string.Format("select * from empm where emp_id = {0}", emp_id);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to employee master object
if (data.Tables[0].Rows.Count > 0)
{
employeeMasterObject.emp_no = data.Tables[0].Rows[0]["emp_id"].ToString();
employeeMasterObject.emp_fname = data.Tables[0].Rows[0]["emp_fname"].ToString();
employeeMasterObject.emp_mname = data.Tables[0].Rows[0]["emp_mname"].ToString();
employeeMasterObject.emp_lname = data.Tables[0].Rows[0]["emp_lname"].ToString();
employeeMasterObject.emp_phn = data.Tables[0].Rows[0]["emp_phn"].ToString();
employeeMasterObject.emp_addr1 = data.Tables[0].Rows[0]["emp_addr1"].ToString();
employeeMasterObject.emp_addr2 = data.Tables[0].Rows[0]["emp_addr2"].ToString();
employeeMasterObject.emp_city = data.Tables[0].Rows[0]["emp_city"].ToString();
employeeMasterObject.emp_state = data.Tables[0].Rows[0]["emp_state"].ToString();
employeeMasterObject.emp_zip = data.Tables[0].Rows[0]["emp_zip"].ToString();
employeeMasterObject.emp_uname = data.Tables[0].Rows[0]["emp_uname"].ToString();
employeeMasterObject.emp_pass = data.Tables[0].Rows[0]["emp_pass"].ToString();
employeeMasterObject.emp_pvg = Convert.ToInt32(data.Tables[0].Rows[0]["emp_pvg"]);
employeeMasterObject.emp_mngr = data.Tables[0].Rows[0]["emp_mngr"].ToString();
employeeMasterObject.emp_brnch = data.Tables[0].Rows[0]["emp_brnch"].ToString();
employeeMasterObject.emp_secq1 = data.Tables[0].Rows[0]["emp_secq1"].ToString();
employeeMasterObject.emp_ans1 = data.Tables[0].Rows[0]["emp_ans1"].ToString();
employeeMasterObject.emp_secq2 = data.Tables[0].Rows[0]["emp_secq2"].ToString();
employeeMasterObject.emp_ans2 = data.Tables[0].Rows[0]["emp_ans2"].ToString();
employeeMasterObject.emp_ans2 = data.Tables[0].Rows[0]["emp_ans2"].ToString();
employeeMasterObject.emp_email = data.Tables[0].Rows[0]["emp_email"].ToString();
return employeeMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMPM_NOFIND);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMPM_NOFIND);
return null;
}
}
public static Finhist Read(string connectionString, string ref_no, Dber dberr)
{
try
{
var FinHistMasterObject = new Finhist();
var query = string.Format("select * from FinHist where ref_no = {0}", ref_no);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to ErrorHistry master object
if (data.Tables[0].Rows.Count > 0)
{
FinHistMasterObject.ref_no = data.Tables[0].Rows[0]["ref_no"].ToString();
FinHistMasterObject.init_empid = data.Tables[0].Rows[0]["init_empid"].ToString();
FinHistMasterObject.tran_date = data.Tables[0].Rows[0]["tran_date"].ToString();
FinHistMasterObject.tran_desc = data.Tables[0].Rows[0]["tran_desc"].ToString();
FinHistMasterObject.tran_timestamp = data.Tables[0].Rows[0]["tran_timestamp"].ToString();
FinHistMasterObject.rem_bal = data.Tables[0].Rows[0]["rem_bal"].ToString();
FinHistMasterObject.cr_amt = Convert.ToDecimal(data.Tables[0].Rows[0]["cr_amt"]);
FinHistMasterObject.dr_amt = Convert.ToDecimal(data.Tables[0].Rows[0]["dr_amt"]);
FinHistMasterObject.ac_no = data.Tables[0].Rows[0]["ac_no"].ToString();
FinHistMasterObject.apprv_empid = data.Tables[0].Rows[0]["apprv_empid"].ToString();
FinHistMasterObject.init_csno = data.Tables[0].Rows[0]["init_csno"].ToString();
return FinHistMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FINHIST_READ);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FINHIST_READ);
return null;
}
}
public static DataSet GetUserAccountBalance(string connectionString, string customerNumber, int privilege, Dber dberr)
{
try
{
var query = string.Format("select ac_no, ac_type, ac_bal from actm where CS_NO1 = {0} and AC_ACTIV = 'True' and ac_pvg >= {1}", customerNumber, privilege);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
if (data.Tables[0].Rows.Count > 0)
{
return data;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
public static DataSet GetPendingTransactionsForAccount(string connectionString, string ac_no, Dber dberr)
{
try
{
var query = string.Format(string.Format("select * from Pendtxn where ac_no = '{0}'", ac_no));
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
if(data!=null)
{
return data;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH);
return null;
}
}
catch
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH);
return null;
}
}
public static DataSet ReadAll(string connectionString, Dber dberr)
{
try
{
var query = string.Format("select * from txnm");
return DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
}
catch //(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_TXNM_READALL);
return null;
}
}
public static Actm Read(string connectionString, string acc_no, Dber dberr)
{
try
{
var accountMasterObject = new Actm();
var query = string.Format("select * from actm where ac_no = {0}", acc_no);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to account master object
if (data.Tables[0].Rows.Count > 0)
{
accountMasterObject.ac_bal = Convert.ToDecimal(data.Tables[0].Rows[0]["ac_bal"]);
accountMasterObject.ac_cr_flag = data.Tables[0].Rows[0]["ac_cr_flag"].ToString();
accountMasterObject.ac_dr_flag = data.Tables[0].Rows[0]["ac_dr_flag"].ToString();
accountMasterObject.ac_hold = Convert.ToDecimal(data.Tables[0].Rows[0]["ac_hold"]);
accountMasterObject.ac_no = data.Tables[0].Rows[0]["ac_no"].ToString();
accountMasterObject.ac_open_dt = data.Tables[0].Rows[0]["ac_open_dt"].ToString();
accountMasterObject.ac_pvg = Convert.ToInt32(data.Tables[0].Rows[0]["ac_pvg"]);
accountMasterObject.ac_type = data.Tables[0].Rows[0]["ac_type"].ToString();
accountMasterObject.cs_no1 = data.Tables[0].Rows[0]["cs_no1"].ToString();
accountMasterObject.cs_no2 = data.Tables[0].Rows[0]["cs_no2"].ToString();
accountMasterObject.ac_activ = Convert.ToBoolean(data.Tables[0].Rows[0]["ac_activ"].ToString());
return accountMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
catch(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
public static Pendtxn Read(string connectionString, string ref_no, Dber dberr)
{
try
{
var PendingTxnMasterObject = new Pendtxn();
var query = string.Format("select * from Pendtxn where ref_no = {0}", ref_no);
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
//assign the data object to Error master object
if (data.Tables[0].Rows.Count > 0)
{
PendingTxnMasterObject.init_empid = data.Tables[0].Rows[0]["init_empid"].ToString();
PendingTxnMasterObject.init_csno = data.Tables[0].Rows[0]["init_csno"].ToString();
PendingTxnMasterObject.ref_no = data.Tables[0].Rows[0]["ref_no"].ToString();
PendingTxnMasterObject.tran_date = data.Tables[0].Rows[0]["tran_date"].ToString();
PendingTxnMasterObject.tran_desc = data.Tables[0].Rows[0]["tran_desc"].ToString();
PendingTxnMasterObject.tran_pvgb = data.Tables[0].Rows[0]["tran_pvgb"].ToString();
PendingTxnMasterObject.ac_no = data.Tables[0].Rows[0]["ac_no"].ToString();
PendingTxnMasterObject.cr_amt = Convert.ToDecimal(data.Tables[0].Rows[0]["cr_amt"]);
PendingTxnMasterObject.dr_amt = Convert.ToDecimal(data.Tables[0].Rows[0]["dr_amt"]);
PendingTxnMasterObject.tran_data = data.Tables[0].Rows[0]["tran_data"].ToString();
return PendingTxnMasterObject;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_READ);
return null;
}
}
catch
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_READ);
return null;
}
}
public static int Create(string connectionString, Txnm dataObject, Dber dberr)
{
try
{
var query = string.Format(@"INSERT INTO [TXNM] ([TRAN_DESC], [TRAN_PVGA], [TRAN_PVGB], [TRAN_FIN_TYPE]) OUTPUT INSERTED.TRAN_ID VALUES ('{0}',{1},{2},'{3}')",
dataObject.tran_desc, dataObject.tran_pvga, dataObject.tran_pvgb, dataObject.tran_fin_type);
return (int) DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_TXNM_CREATE);
return -1;
}
}
public static bool Update(string connectionString, Pkit dataObject, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[PKIT]
SET
[PUBLIC_KEY] = '{0}'
,[PRIVATE_KEY] = '{1}'
WHERE CS_NO = {2}", dataObject.public_key, dataObject.private_key, dataObject.cs_no);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PKIT_ERROR);
return false;
}
}
public static int Create(string connectionString, Pkit dataObject, Dber dberr)
{
try
{
var query = string.Format(@"INSERT INTO [SBS].[dbo].[PKIT]
([PUBLIC_KEY]
,[PRIVATE_KEY]
,[CS_NO])
OUTPUT INSERTED.id
VALUES
('{0}' ,'{1}','{2}' )",
dataObject.public_key, dataObject.private_key, dataObject.cs_no);
return (int)DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PKIT_ERROR);
return -1;
}
}
public static bool Delete(string connectionString, string id, Dber dberr)
{
try
{
var query = string.Format("delete from cstm where cs_no = {0}", id);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FAIL_UPDATE_PWD);
return false;
}
}
public static bool UpdatePassword (string connectionString, string userId, string password, Dber dberr)
{
try
{
var query = string.Format("update cstm set cs_pass = '******' where cs_no = {1}", password, userId);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FAIL_UPDATE_PWD);
return false;
}
}
private static bool UpdateUserId(string connectionString, int customerId, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[CSTM]
SET
[CS_UNAME] = '{0}'
WHERE CS_NO = {0}", customerId);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FAIL_UPDATE_PWD);
return false;
}
}
public static bool deactivateCustomer(string connectionString, String cusNo, String deacType, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[CSTM]
SET [CS_TYPE] = {0} WHERE CS_NO = {1}", deacType, cusNo);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMP_CUS_DELETE);
return false;
}
}
public static bool Update(string connectionString, Cstm dataObject, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[CSTM]
SET
[CS_FNAME] = '{0}'
,[CS_MNAME] = '{1}'
,[CS_LNAME] = '{2}'
,[CS_ADDR1] = '{3}'
,[CS_ADDR2] = '{4}'
,[CS_ZIP] = '{5}'
,[CS_CITY] = '{6}'
,[CS_STATE] = '{7}'
,[CS_PHN] = '{8}'
,[CS_EMAIL] = '{9}'
,[CS_SEC_QS1] = '{10}'
,[CS_SEC_ANS1] = '{11}'
,[CS_SEC_QS2] = '{12}'
,[CS_SEC_ANS2] = '{13}'
,[CS_SEC_QS3] = '{14}'
,[CS_SEC_ANS3] = '{15}'
WHERE CS_NO = {16}", dataObject.cs_fname, dataObject.cs_mname, dataObject.cs_lname,
dataObject.cs_addr1, dataObject.cs_addr2, dataObject.cs_zip, dataObject.cs_city, dataObject.cs_state, dataObject.cs_phn,
dataObject.cs_email, dataObject.cs_secq1, dataObject.cs_ans1, dataObject.cs_secq2,
dataObject.cs_ans2, dataObject.cs_secq3, dataObject.cs_ans3, dataObject.cs_no);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch(Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_NOFIND);
return false;
}
}
public static int Create(string connectionString, Cstm dataObject, Dber dberr)
{
try
{
var s = Utility.ValidData(dataObject);
var query = string.Format(@"INSERT INTO [SBS].[dbo].[CSTM]
([CS_TYPE],[CS_FNAME],[CS_MNAME],[CS_LNAME],[CS_ADDR1],[CS_ADDR2],[CS_ZIP],[CS_CITY]
,[CS_STATE],[CS_PHN],[CS_EMAIL],[CS_UID],[CS_BRANCH],[CS_SEC_QS1],[CS_SEC_ANS1],[CS_SEC_QS2],[CS_SEC_ANS2]
,[CS_SEC_QS3],[CS_SEC_ANS3],[CS_ACCESS],[CS_UNAME],[CS_PASS])
OUTPUT INSERTED.CS_NO
VALUES
('{0}' ,'{1}','{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,'{8}' ,'{9}' ,'{10}' ,'{11}',
'{12}' ,'{13}' ,'{14}' ,'{15}' ,'{16}' ,'{17}' ,'{18}' ,'{19}' ,'{20}' ,'{21}')",
dataObject.cs_type, dataObject.cs_fname, dataObject.cs_mname, dataObject.cs_lname,
dataObject.cs_addr1, dataObject.cs_addr2, dataObject.cs_zip, dataObject.cs_city, dataObject.cs_state,
dataObject.cs_phn, dataObject.cs_email, dataObject.cs_uid, dataObject.cs_branch, dataObject.cs_secq1,
dataObject.cs_ans1, dataObject.cs_secq2, dataObject.cs_ans2, dataObject.cs_secq3, dataObject.cs_ans3,
dataObject.cs_access, dataObject.cs_uname, dataObject.cs_pass);
var customerId = (int)DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
UpdateUserId(connectionString, customerId, dberr);
UpdatePKIInformation(connectionString, customerId.ToString(), dberr);
return customerId;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_CSTM_CREATE);
return -1;
}
}
private static bool UpdateUserId(string connectionString, int employeeId, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[EMPM]
SET
[EMP_UNAME] = '{0}'
WHERE EMP_ID = {0}", employeeId);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMPM_UPDATE);
return false;
}
}
public static DataSet GetAccessiblePendingTransactions(string connectionString, string pvgb, Dber dberr, bool isAdmin)
{
try
{
var condition = "<=";
var accountColumn = string.Empty;
if (!isAdmin) accountColumn = @" case
when DR_AMT = 0 and CR_AMT = 0 then ''
when CR_AMT = 0 then '-' + CONVERT(varchar (max), DR_AMT)
else CONVERT(varchar (max), CR_AMT)
end Amount, ";
if (isAdmin) condition = "=";
var query = string.Format(string.Format(@"select
[REF_NO] as [Reference Number],
[TRAN_DATE] as [Transaction Date],
[AC_NO] as [Account Number],
[INIT_CSNO] as [Customer Number],
[TRAN_DESC] as [Transaction Details],
{2}
'' as Command
from PENDTXN where tran_pvgb {0} {1}", condition, pvgb, accountColumn));
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
if (data != null)
{
return data;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH);
return null;
}
}
catch
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH);
return null;
}
}
public static bool UpdateAccountBalance(string connectionString, string accountNumber, decimal amount, Dber dberr)
{
try
{
var query = string.Format("update actm set ac_bal = {0} where ac_no = '{1}'", amount, accountNumber);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return false;
}
}
public static string GetTranDataFromRefNumber(string connectionString, string ref_no, Dber dberr)
{
try
{
var query = string.Format(string.Format(@"select tran_data from PENDTXN where ref_no = {0}", ref_no));
var data = DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
if (data != null)
{
return data.ToString();
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
catch
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH);
return "Transaction Error: " + Mnemonics.DbErrorCodes.DBERR_PENDTXN_NOFETCH;
}
}
public static int Create(string connectionString, Actm dataObject, Dber dberr)
{
try
{
if (dataObject.cs_no2.Trim() == string.Empty) dataObject.cs_no2 = null;
var query = string.Format(@"INSERT INTO [SBS].[dbo].[ACTM]
([CS_NO1],[CS_NO2],[AC_TYPE],[AC_BAL],[AC_HOLD],[AC_PVG],[AC_DR_FLAG],[AC_CR_FLAG],[AC_OPEN_DT],[AC_ACTIV])
OUTPUT INSERTED.AC_NO
VALUES
({0},{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
dataObject.cs_no1, dataObject.cs_no2 == "0" ? "null" :dataObject.cs_no2, dataObject.ac_type, dataObject.ac_bal, dataObject.ac_hold,
dataObject.ac_pvg, dataObject.ac_dr_flag, dataObject.ac_cr_flag, dataObject.ac_open_dt, dataObject.ac_activ);
return (int)DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_CREATE);
return -1;
}
}
public static bool deactivateEmployee(string connectionString, String empNo, String deacType, Dber dberr)
{
try
{
var query = string.Format(@"UPDATE [SBS].[dbo].[EMPM]
SET [EMP_PVG] = {0} WHERE [EMP_ID] = {1}", deacType, empNo);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMP_CUS_DELETE);
return false;
}
}
public static String getEmpNumberFromEmpId(string connectionString, string emp_id, Dber dberr)
{
try
{
// put query here, and return employee number
return "1234";
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_EMPM_NOFIND);
return " ";
}
}
public static DataSet GetMerchantAccessibleCustomerData(string connectionString, string employeeId, Dber dberr)
{
try
{
var query = string.Format("select * from cstm where cs_type > 0");
var data = DbAccess.ExecuteQuery(connectionString, CommandType.Text, query);
if (data.Tables[0].Rows.Count > 0)
{
return data;
}
else
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return null;
}
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_FAIL_UPDATE_PWD);
return null;
}
}
public static bool Delete (string connectionString, string acc_no, Dber dberr)
{
try
{
var query = string.Format("delete from actm where ac_no = {0}", acc_no);
return DbAccess.ExecuteNonQuery(connectionString, CommandType.Text, query) == 1;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_DELETE);
return false;
}
}
public static int Create(string connectionString, Empm dataObject, Dber dberr)
{
try
{
var query = string.Format(@"INSERT INTO [SBS].[dbo].[EMPM]
([EMP_FNAME],[EMP_MNAME],[EMP_LNAME],[EMP_ADDR1],[EMP_ADDR2]
,[EMP_ZIP],[EMP_CITY],[EMP_STATE],[EMP_BRNCH],[EMP_PHN],[EMP_EMAIL]
,[EMP_MNGR],[EMP_PVG],[EMP_SECQ1],[EMP_ANS1],[EMP_SECQ2],[EMP_ANS2]
,[EMP_UNAME],[EMP_PASS])
OUTPUT INSERTED.EMP_ID
VALUES
('{0}' ,'{1}' ,'{2}' ,'{3}','{4}' ,'{5}','{6}' ,'{7}' ,'{8}' ,'{9}' ,'{10}' ,'{11}' ,'{12}' ,'{13}'
,'{14}' ,'{15}' ,'{16}' ,'{17}', '{18}')",
dataObject.emp_fname, dataObject.emp_mname, dataObject.emp_lname, dataObject.emp_addr1, dataObject.emp_addr2,
dataObject.emp_zip, dataObject.emp_city, dataObject.emp_state, dataObject.emp_brnch, dataObject.emp_phn,
dataObject.emp_email, dataObject.emp_mngr, dataObject.emp_pvg, dataObject.emp_secq1, dataObject.emp_ans1,
dataObject.emp_secq2, dataObject.emp_ans2, dataObject.emp_uname, dataObject.emp_pass);
var employeeId = (int)DbAccess.ExecuteScalar(connectionString, CommandType.Text, query);
UpdateUserId(connectionString, employeeId, dberr);
UpdatePKIInformation(connectionString, employeeId.ToString(), dberr);
return employeeId;
}
catch (Exception ex)
{
dberr.setError(Mnemonics.DbErrorCodes.DBERR_ACTM_NOFIND);
return -1;
}
}