private DataTable GetOverDraftsAccountdList()
{
DataTable cardData = new DataTable();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnection("FCC_LIVE");
string str = "SELECT "
+ " cust_ac_no account, "
+ " acy_avl_bal balance "
+ " FROM FCUSR01.sttm_cust_account "
+ " where account_class = 'TCI001'";
OracleCommand cmd = new OracleCommand(str, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(cardData);
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(cardData);
}
public OracleIndexes(OracleDBConnection connection, String indexName)
{
InitializeComponent();
this.dbConnection = connection;
OracleConnection databaseConnection = connection.getDatabaseConnection();
String query = "SELECT INDEX_NAME, INDEX_TYPE, USER_INDEXES.TABLE_NAME, UNIQUENESS "
+ "FROM USER_INDEXES, USER_TABLES "
+ "WHERE INDEX_NAME = '" + indexName + "' "
+ "AND USER_INDEXES.TABLE_NAME = USER_TABLES.TABLE_NAME";
OracleCommand command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
while (reader.Read())
{
this.indexList.Rows.Add("NAME", reader["INDEX_NAME"]);
this.indexList.Rows.Add("TYPE", reader["INDEX_TYPE"]);
this.indexList.Rows.Add("TABLE", reader["TABLE_NAME"]);
this.indexList.Rows.Add("UNIQUENESS", reader["UNIQUENESS"]);
}
reader.Close();
}
private DataTable GetWelcomeLetterList(string fromDate, string toDate)
{
DataTable cardData = new DataTable();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnection("CW_IM");
OracleCommand cmd = new OracleCommand("Get_Welcome_Letter", conn);
OracleParameter p_fromDate = new OracleParameter("from_date", fromDate);
p_fromDate.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p_fromDate);
OracleParameter p_toDate = new OracleParameter("to_date", toDate);
p_toDate.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p_toDate);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter sysCursor = new OracleParameter("sys_cursor", OracleType.Cursor);
sysCursor.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sysCursor);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(cardData);
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(cardData);
}
public OracleView(OracleDBConnection connection)
{
InitializeComponent();
this.dbConnection = connection;
this.dbConnection.createConnection();
InitializeRefresh();
}
private DataTable GetMasterCardList()
{
DataTable cardData = new DataTable();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnection("CW_DW");
//string today = DateTime.Today.ToString("yyyyMMdd");
string str = "(select "
+ " ded2(px_dw005_pan,'') PAN "
+ " from dw005 "
+ " where fx_dw005_crd_brn = 'MC') "
+ " union "
+ "(select "
+ " ded2(px_dw006_own_pan,'') PAN "
+ " from dw006 "
+ "where fx_dw006_crd_brn = 'MC') ";
OracleCommand cmd = new OracleCommand(str, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(cardData);
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(cardData);
}
public OracleSessionDetails(OracleDBConnection connection)
{
InitializeComponent();
this.dbConnection = connection;
OracleConnection databaseConnection = dbConnection.getDatabaseConnection();
String query;
OracleCommand command = new OracleCommand("DBMS_SESSION.set_identifier", databaseConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(dbConnection.getUsername(), OracleDbType.Char);
command.ExecuteNonQuery();
query = "SELECT SYS_CONTEXT('userenv', 'SID') AS SID, "
+ "SYS_CONTEXT('userenv', 'HOST') AS HOST, "
+ "USER AS username "
+ "FROM dual";
command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
while (reader.Read())
{
this.sessionListing.Rows.Add("SID", reader["SID"]);
this.sessionListing.Rows.Add("HOST", reader["HOST"]);
this.sessionListing.Rows.Add("DATABASE", dbConnection.getDatabaseConnection().DataSource);
this.sessionListing.Rows.Add("USER", reader["USERNAME"]);
}
reader.Close();
}
public OracleDll(OracleDBConnection connection, String objectType, String objectName)
{
InitializeComponent();
this.dbConnection = connection;
DataTable data = new DataTable();
OracleConnection databaseConnection = connection.getDatabaseConnection();
String query = "SELECT DBMS_METADATA.GET_DDL('" + objectType + "',"
+ "'" + objectName + "')AS DDL FROM DUAL";
Console.WriteLine(query);
OracleCommand command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
data.Load(reader);
foreach (DataRow row in data.Rows)
{
String ColumnData = row[0].ToString();
this.richTextBox1.AppendText(ColumnData);
}
reader.Close();
}
private DataTable GetMasterCardList(string date)
{
DataTable cardData = new DataTable();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnection("CW_DW");
OracleCommand cmd = new OracleCommand("Get_U70013_Report", conn);
OracleParameter reportDate = new OracleParameter("reportDate", date);
reportDate.Direction = ParameterDirection.Input;
cmd.Parameters.Add(reportDate);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter sysCursor = new OracleParameter("sysCursor", OracleType.Cursor);
sysCursor.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sysCursor);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(cardData);
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(cardData);
}
public OracleTableSpaceData(OracleDBConnection connection)
{
InitializeComponent();
this.dbConnection = connection;
OracleConnection databaseConnection = connection.getDatabaseConnection();
String query = "SELECT ts.TABLESPACE_NAME, "
+ "TO_CHAR(SUM(NVL(fs.bytes, 0)) / 1024 / 1024, '99,999,990.99') AS MB_FREE "
+ "FROM "
+ "USER_FREE_SPACE fs, "
+ "USER_TABLESPACES ts, "
+ "USER_USERS us "
+ "WHERE fs.TABLESPACE_NAME(+) = ts.TABLESPACE_NAME "
+ "GROUP BY ts.tablespace_name";
OracleCommand command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
DataTable data = new DataTable();
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
data.Load(reader);
this.tableSpaceData.DataSource = data;
reader.Close();
}
private DataTable GetLOC_CardType_Branch2(string pan_clear)
{
OracleConnection conn = new OracleConnection();
DataTable data = new DataTable();
try
{
conn = OracleDBConnection.OpenConnection("CCPS_DW");
OracleCommand cmd = new OracleCommand("Get_LOC_T112", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter pan_p = new OracleParameter("pan_in", pan_clear);
pan_p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(pan_p);
OracleParameter results = new OracleParameter("sys_cursor", OracleType.Cursor);
results.Direction = ParameterDirection.Output;
cmd.Parameters.Add(results);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(data);
conn.Close();
return(data);
}
catch (Exception ex)
{
conn.Close();
return(data);
}
}
private void ConnectionButton_Click(object sender, EventArgs e)
{
if (this.ConnectionDetails1.Text == "Server Name")
{
SQLServerConnection databaseConnection = new SQLServerConnection(this.ConnectionDetailsText1.Text,
this.ConnectionDetailsText2.Text, this.ConnectionDetailsText3.Text, this.ConnectionDetailsText4.Text);
String result = databaseConnection.createConnection();
MessageBox.Show(result);
SqlConnection dbConnection = databaseConnection.getDatabaseConnection();
if (result == "Connection Established. Press OK to continue.")
{
new SQLServerView(databaseConnection).Show();
this.Dispose();
}
}
else
{
OracleDBConnection databaseConnection = new OracleDBConnection(this.ConnectionDetailsText1.Text,
this.ConnectionDetailsText2.Text, this.ConnectionDetailsText4.Text);
String result = databaseConnection.createConnection();
MessageBox.Show(result);
if (result == "Connection Established. Press OK to continue.")
{
new OracleView(databaseConnection).Show();
this.Dispose();
}
}
}
private DataTable GetInfor_Card(string card_clear)
{
DataTable cardData = new DataTable();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnection("CCPS_DW");
string str_get_pan = "select ecd2(" + card_clear + ",'') from dual";
OracleCommand cmd = new OracleCommand(str_get_pan, conn);
cmd.CommandType = CommandType.Text;
string pan_temp = Convert.ToString(cmd.ExecuteScalar());
string str_get_infor = "select loc,branch,card_type from "
+ "( select PX_DW005_PAN pan,F9_DW005_LOC_ACCT loc ,FX_DW005_CRD_PGM card_type,FX_DW005_BRCH_CDE branch "
+ "from dw005 "
+ "union "
+ "select PX_DW006_OWN_PAN pan,F9_DW006_LOC_ACCT loc ,FX_DW006_CRD_PGM card_type,FX_DW006_BRCH_CDE branch "
+ "from dw006 ) "
+ "where pan='"
+ pan_temp + "'";
string brach_temp = null;
cmd = new OracleCommand(str_get_infor, conn);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
brach_temp = dr["branch"].ToString();
}
dr.Close();
//OracleDataAdapter da = new OracleDataAdapter(cmd);
//string temp = da.ToString();
//da.Fill(cardData);
// string str_get_infor = "select loc,branch,card_type from "
//+ "( select PX_DW005_PAN pan,F9_DW005_LOC_ACCT loc ,FX_DW005_CRD_PGM card_type,FX_DW005_BRCH_CDE branch "
//+ "from dw005 "
//+ "union "
//+ "select PX_DW006_OWN_PAN pan,F9_DW006_LOC_ACCT loc ,FX_DW006_CRD_PGM card_type,FX_DW006_BRCH_CDE branch "
//+ "from dw006 ) "
//+ "where pan='"
//+ str_get_pan + "'";
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(cardData);
}
public TableViewerOracle(String tableName, OracleDBConnection connection, bool option)
{
InitializeComponent();
this.dbConnection = connection;
this.tableName = tableName;
if (option)
{
this.TableOptions.TabPages.Remove(TableAddColumn);
this.partitionNumber.Dispose();
this.tablePartitions.Dispose();
}
else
{
partitions();
}
refreshInitializeTable();
}
public static DBConnection GetDBConnection(string ConnectionString)
{
DBConnection dBConnection = null;
var providerName = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionString].ProviderName;
switch (providerName)
{
case "Oracle.DataAccess.Client":
dBConnection = new OracleDBConnection(ConnectionString);
break;
case "System.Data.SqlClient":
dBConnection = new SqlDBConnection(ConnectionString);
break;
default:
break;
}
return(dBConnection);
}
private string GetLOC_CardType_Branch(string pan_clear)
{
OracleConnection conn = new OracleConnection();
string str_out = null;
try
{
conn = OracleDBConnection.OpenConnection("CCPS_DW");
OracleCommand cmd = new OracleCommand("Get_LOC_T112_T", conn);
OracleParameter pan_p = new OracleParameter("pan_in", pan_clear);
pan_p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(pan_p);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter loc_p = new OracleParameter("loc_out", OracleType.NVarChar, 17);
loc_p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(loc_p);
OracleParameter card_type_p = new OracleParameter("card_type_out", OracleType.NVarChar, 17);
card_type_p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(card_type_p);
OracleParameter branch_p = new OracleParameter("branch_out", OracleType.NVarChar, 17);
branch_p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(branch_p);
cmd.ExecuteNonQuery();
str_out = cmd.Parameters["loc_out"].Value.ToString()
+ "|" + cmd.Parameters["branch_out"].Value.ToString()
+ "|" + cmd.Parameters["card_type_out"].Value.ToString();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
return(null);
}
return(str_out);
}
private void HienThiAllMessages(DataGridView grd, List <classMessage> Messages)
{
//try
//{
int n = Messages.Count;
string[] message;
for (int i = 0; i < n; i++)
{
int k = 0;
if (Messages[i] != null)
{
message = new string[128];
message[k++] = Messages[i].DataElement[0];
message[k++] = Messages[i].DataElement[1];
message[k++] = Messages[i].DataElement[2];
message[k++] = FormatAmount(Messages[i].DataElement[3], Messages[i].DataElement[48]);
message[k++] = FormatAmount(Messages[i].DataElement[4], Messages[i].DataElement[49]);
message[k++] = FormatAmount(Messages[i].DataElement[5], Messages[i].DataElement[50]);
message[k++] = Messages[i].DataElement[48]; //DE49
message[k++] = Messages[i].DataElement[49]; //DE50
message[k++] = Messages[i].DataElement[50]; //DE51
message[k++] = Messages[i].DataElement[71]; //DE72
message[k++] = Messages[i].DataElement[8];
message[k++] = Messages[i].DataElement[9];
message[k++] = Messages[i].DataElement[11];
message[k++] = Messages[i].DataElement[13];
message[k++] = Messages[i].DataElement[21];
message[k++] = Messages[i].DataElement[22];
message[k++] = Messages[i].DataElement[23];
message[k++] = Messages[i].DataElement[24];
message[k++] = Messages[i].DataElement[25];
message[k++] = Messages[i].DataElement[29];
message[k++] = Messages[i].DataElement[30];
message[k++] = Messages[i].DataElement[31];
message[k++] = Messages[i].DataElement[32];
message[k++] = Messages[i].DataElement[36];
message[k++] = Messages[i].DataElement[37];
message[k++] = Messages[i].DataElement[39];
message[k++] = Messages[i].DataElement[40];
message[k++] = Messages[i].DataElement[41];
message[k++] = Messages[i].DataElement[42];
message[k++] = Messages[i].DataElement[47];
//message[25] = Messages[i].DataElement[48];
//message[26] = Messages[i].DataElement[49];
//message[27] = Messages[i].DataElement[50];
message[k++] = Messages[i].DataElement[53];
message[k++] = Messages[i].DataElement[54];
message[k++] = Messages[i].DataElement[61];
message[k++] = Messages[i].DataElement[62];
message[k++] = Messages[i].DataElement[70];
//message[33] = Messages[i].DataElement[71];
message[k++] = Messages[i].DataElement[72];
message[k++] = Messages[i].DataElement[92];
message[k++] = Messages[i].DataElement[93];
message[k++] = Messages[i].DataElement[94];
message[k++] = Messages[i].DataElement[99];
message[k++] = Messages[i].DataElement[110];
///////hhhh them phi giao dich
//message[k++] = Messages[i].DataElement[122];
message[k++] = Messages[i].AdditionalData[150];
//message[k++] = Messages[i].DataElement[123];
////message[k++] = FormatAmount(Messages[i].AdditionalData[151],Messages[i].AdditionalData[150]);
message[k++] = Messages[i].AdditionalData[151];
//message[k++] = Messages[i].DataElement[124];
message[k++] = Messages[i].AdditionalData[152];
//message[k++] = Messages[i].DataElement[126];
////message[k++] = FormatAmount(Messages[i].AdditionalData[153], Messages[i].AdditionalData[152]);
message[k++] = Messages[i].AdditionalData[153];
//22222
//string card_full = Messages[i].DataElement[1];
//string infor = null;
//infor=GetLOC_CardType_Branch(card_full);
//if (infor != null)
//{
// message[k++] = infor.Substring(0,12);
// message[k++] = infor.Substring(13,3);
// message[k++] = infor.Substring(17);
//}
//////
//string card_full = Messages[i].DataElement[1];
//DataTable table = new DataTable();
//table.Rows.Clear();
//GetInfor_Card(card_full);
//table = GetLOC_CardType_Branch2(card_full);
//if (table.Rows.Count > 0)
//{
// foreach (DataRow row in table.Rows)
// {
// message[k++] = row.ItemArray[0].ToString();
// message[k++] = row.ItemArray[1].ToString();
// message[k++] = row.ItemArray[2].ToString();
// }
//}
////////
//////////3333
string card_full = null;
card_full = Messages[i].DataElement[1];
OracleConnection conn = new OracleConnection();
conn = OracleDBConnection.OpenConnection("CCPS_DW");
if (card_full != null)
{
string str_get_pan = "select ecd2(" + card_full + ",'') from dual";
OracleCommand cmd = new OracleCommand(str_get_pan, conn);
cmd.CommandType = CommandType.Text;
string pan_temp = null;
pan_temp = Convert.ToString(cmd.ExecuteScalar());
if (pan_temp != null)
{
string str_get_infor = "select loc,branch,card_type from "
+ "( select PX_DW005_PAN pan,F9_DW005_LOC_ACCT loc ,FX_DW005_CRD_PGM card_type,FX_DW005_BRCH_CDE branch "
+ "from dw005 "
+ "union "
+ "select PX_DW006_OWN_PAN pan,F9_DW006_LOC_ACCT loc ,FX_DW006_CRD_PGM card_type,FX_DW006_BRCH_CDE branch "
+ "from dw006 ) "
+ "where pan='"
+ pan_temp + "'";
string brach_temp = null;
cmd = new OracleCommand(str_get_infor, conn);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr["loc"].ToString() != null)
{
message[k++] = dr["loc"].ToString();
}
if (dr["branch"].ToString() != null)
{
message[k++] = dr["branch"].ToString();
}
if (dr["card_type"].ToString() != null)
{
message[k++] = dr["card_type"].ToString();
}
dr.Close();
}
}
conn.Close();
//////////
////////
grd.Rows.Add(message);
}
}
//}
//catch (Exception ex)
//{
// classMasterCardToolLogWriter.WriteLog("Error GetKichHoatThe()at DataAccess, " + ex.Message);
//}
}
public OracleDBOperation(AbstractDBConnection con, AbstractEntity entity) : base(con, entity)
{
this.connection = (OracleDBConnection)con;
}
public OracleDBOperation(AbstractDBConnection con) : base(con)
{
this.connection = (OracleDBConnection)con;
}
