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);
}
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);
}
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);
}
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);
}
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);
}
}
public ICollection <PieChartObjectModel> GetPieChartData(int?studyYear = null, int?academicYear = null)
{
ICollection <PieChartObjectModel> lista = new List <PieChartObjectModel>();
try
{
OracleConnection connection = _context.OpenConnection();
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT Count(*) as Broj,t4.title Smjer FROM BP07.User_Enrollment t1"
+ " JOIN BP07.LabGroup t2 ON t1.LabGroupId = t2.Id"
+ " JOIN BP07.Course_Department t3 ON t2.Course_DepartmentId = t3.Id"
+ " JOIN BP07.Department t4 ON t3.DepartmentId = t4.id"
+ " WHERE t1.studyyearid ='" + studyYear.ToString() + "' AND t3.academicyearid = '" + academicYear.ToString() + "'"
+ " GROUP BY t4.title";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int broj = reader.GetInt32(0);
string smjer = reader.GetString(1);
PieChartObjectModel o = new PieChartObjectModel {
Name = smjer, Y = broj
};
lista.Add(o);
}
return(lista);
}
catch (Exception e)
{
}
return(null);
}
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);
}
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);
//}
}