// GET api/values/5
public IHttpActionResult GetContactosById(int id)
{
List <Contacto> oContacto = new List <Contacto>();
string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString;
iDB2Connection connection = new iDB2Connection(connString);
string query = "select * from davidec1.contactos where id = @id";
iDB2Command db2Command = new iDB2Command();
db2Command.Connection = connection;
db2Command.CommandText = query;
db2Command.Parameters.AddWithValue("@id", id);
connection.Open();
iDB2DataReader reader = db2Command.ExecuteReader();
while (reader.Read())
{
oContacto.Add(new Contacto()
{
Id = Convert.ToInt32(reader.GetValue(0).ToString()),
Nombre = reader.GetValue(1).ToString(),
Telefono = reader.GetValue(2).ToString(),
Correo = reader.GetValue(3).ToString(),
Edad = Convert.ToInt32(reader.GetValue(4).ToString())
});
}
return(Ok(oContacto));
}
private void CaricaFornitore()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT o.qsta,o.Nord," +
"concat(concat(substring(o.dord, 7, 2), '/') , " +
"concat(concat(substring(o.dord, 5, 2) , '/') , " +
"substring(o.dord, 1, 4)))," +
" a.acrag1 " +
" FROM $EMIEDATI.oda200f o " +
" left join $d_emil.acf00f a " +
" on o.dtip = a.actpcd and o.cfor = a.acscon " +
" where o.qsta <> 'X'" +
" group by o.qsta,o.Nord,o.dord, a.acrag1";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
//dataGridView1.DataSource = dt;
//dataGridView1.Columns[0].HeaderText = "Stato";
//dataGridView1.Columns[1].HeaderText = "Numero Ordine";
//dataGridView1.Columns[2].HeaderText = "Data Ordine";
//dataGridView1.Columns[3].HeaderText = "Fornitore";
//for (int i = 0; i < dataGridView1.Columns.Count; i++)
// dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.prog) " +
" FROM $EMIEDATI.oda200f o " +
" where NORD = " + _n;
iDB2DataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
try
{ return(myReader.GetInt32(0)); }
catch
{ return(0); }
}
}
else
{
return(0);
}
DBCONN.Close();
return(0);
}
private void btnGetLog_Click(object sender, EventArgs e)
{
//ceonnect to the database
try
{
connection = new iDB2Connection("DataSource=deathstar.gtc.edu;DefaultCollection=FLIGHT2019");
command = connection.CreateCommand();
//grab the data
command.CommandText = "SELECT * FROM MAINTLOG "
+ "WHERE PLANENO = '" + txtPlaneNum.Text.ToUpper() + "'";
connection.Open();
dataReader = command.ExecuteReader();
//Display to listbox
if (dataReader.Read())
{
listBox1.Items.Add("Log Number: " + dataReader.GetString(1));
listBox1.Items.Add("Task ID: " + dataReader.GetString(2));
listBox1.Items.Add("Maintence Start Date: " + dataReader.GetString(3));
listBox1.Items.Add("Maintence End Date: " + dataReader.GetString(4));
listBox1.Items.Add("Status: " + dataReader.GetString(5));
listBox1.Items.Add("Maintence Cost: " + dataReader.GetString(6));
}
else
{
txtPlaneNum.Text = "No results found for specified Plane Number id.";
}
dataReader.Close();
}
catch (Exception ex) { txtPlaneNum.Text = ex.Message; }
}
private void btnGetTask_Click(object sender, EventArgs e)
{
try
{
connection = new iDB2Connection("DataSource=deathstar.gtc.edu");
command = connection.CreateCommand();
command.CommandText =
"SELECT taskDesc " +
"FROM Tasks T JOIN WorkOrder WO ON T.taskId = WO.taskId " +
"WHERE WO.orderId = '" + txtWorkOrder.Text.ToUpper() + "'";
connection.Open();
reader = command.ExecuteReader();
if (reader.Read())
{
txtTask.Text = reader.GetString(0);
}
else
{
txtTask.Text = "No results found for specified work order id.";
}
reader.Close();
}
catch (Exception ex) { txtTask.Text = ex.Message; }
}
public string EjecutarSelectResult(string consulta)
{
try
{
AS400ConnectionString.Open();
string datoDevuelto = string.Empty;
iDB2Command command = new iDB2Command();
command.Connection = AS400ConnectionString;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = consulta;
command.CommandTimeout = 600000;
iDB2DataReader dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
datoDevuelto = dr.GetString(0);
break;
}
}
return(datoDevuelto);
}
catch (Exception ex)
{
return("0");
//throw new Exception("Se produjo un problema al reaizar un select en AS400: ", ex);
}
}
void impostaarticolo()
{
Caricamento carica = new Caricamento();
carica.Show();
Application.DoEvents();
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT ararti, ardsar" +
" FROM $emiedati.art00f a " +
" order by ardsar";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
CMB_Articolo.DataSource = dt;
CMB_Articolo.DisplayMember = "ardsar";
CMB_Articolo.ValueMember = "ararti";
carica.Close();
DBCONN.Close();
}
private void getGroupIds()
{
//remove all preexisting data from the list
groupIds.Clear();
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "Select group_id from ISSYSDTA.SHORETELINBOUNDGROUPS";
cmd.Connection = cn;
cn.Open();
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results
while (readFile.Read())
{
groupIds.Add(readFile.GetiDB2Integer(0));
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
}
public iDB2DataReader GetDataReaderFromQuery(string strQuery, CommandType intCommandType)
{
var objCmd = default(iDB2Command);
iDB2DataReader objDr;
var objConn = default(iDB2Connection);
try
{
objConn = new iDB2Connection(strConn);
objCmd = new iDB2Command(strQuery, objConn);
objCmd.CommandType = intCommandType;
objConn.Open();
objDr = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
objDr = default;
if (objCmd is object)
{
objCmd.Dispose();
}
if (objConn is object)
{
objConn.Close();
objConn.Dispose();
}
objException = ex;
}
return(objDr);
}
private string obtenerTipoOperacion(string ordenServicio)
{
string tipoOperacion = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SELECT CTPOOP FROM [email protected] WHERE NORSRN = @NORSRN", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tipoOperacion = (dr.IsDBNull(dr.GetOrdinal("CTPOOP")) ? 1 : dr.GetInt32(dr.GetOrdinal("CTPOOP"))) == 1 ? "I" : "E";
}
}
catch (Exception ex)
{
tipoOperacion = "I";
}
finally
{
cn.Close();
}
return(tipoOperacion);
}
public string ObtenerNaveViaje(string ordenServicio)
{
string NaveViaje = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_OBTENERNAVE_VIAJE", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XNORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NaveViaje = (dr.IsDBNull(dr.GetOrdinal("TCMPVP")) ? "" : dr.GetString(dr.GetOrdinal("TCMPVP")).Trim()) + " - " + (dr.IsDBNull(dr.GetOrdinal("NVJES")) ? "" : dr.GetString(dr.GetOrdinal("NVJES")));
}
}
catch (Exception ex)
{
NaveViaje = "";
}
finally
{
cn.Close();
}
return(NaveViaje);
}
public static void Main()
{
Console.WriteLine($"Profiler attached: {Samples.SampleHelpers.IsProfilerAttached()}");
var command = new iDB2Command("MyCommand");
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
}
try
{
command.ExecuteScalar();
}
catch (Exception)
{
}
try
{
command.ExecuteReader();
}
catch (Exception)
{
}
Console.WriteLine("Done");
}
public List <PendientesLlenos> ListaStockLlenos()
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_CONSULTA_DSREPSTOCKCONTLLENOS_V2", cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.YardCode = dr.IsDBNull(dr.GetOrdinal("NUMIDE")) ? "" : dr.GetString(dr.GetOrdinal("NUMIDE"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
private void SubmitBtn_Click(object sender, EventArgs e)
{
if (AnDText.Text.Length > 0 && AnDText.Text != null)
{
AnDBox.Items.Clear();
AnDBox.Items.Add("FLIGHTS");
try
{
SQL = "select f.FTRTID, r.rtardepart,r.rtararrivl,f.flightno from flight2019.flight f inner join flight2019.route r on f.FTRTID = r.RTID where f.FLIGHTNO='";
SQL += AnDText.Text + "'";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
String departcity = "ERROR IF UNCHANGED";
String arrivalcity = "ERROR IF UNCHANGED";
arcodes.TryGetValue(dataReader.GetString(1), out departcity);
arcodes.TryGetValue(dataReader.GetString(2), out arrivalcity);
AnDBox.Items.Add("Flight ID: " + dataReader.GetString(0) + " Departing: " + departcity.Trim() + " Arriving at: " + arrivalcity.Trim());
}
connection.Close();
}
catch (Exception ex)
{
AnDBox.Items.Add(ex.Message);
}
}
}
public AllFlights()
{
InitializeComponent();
loadARCodes();
FlightBox.Items.Clear();
FlightBox.Items.Add("FLIGHTS");
try
{
SQL = "select f.FTRTID, r.rtardepart,r.rtararrivl from flight2019.flight f inner join flight2019.route r on FTRTID = RTID";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
String departcity = "ERROR IF UNCHANGED";
String arrivalcity = "ERROR IF UNCHANGED";
arcodes.TryGetValue(dataReader.GetString(1), out departcity);
arcodes.TryGetValue(dataReader.GetString(2), out arrivalcity);
FlightBox.Items.Add("Flight ID: " + dataReader.GetString(0) + " Departing: " + departcity.Trim() + " Arriving at: " + arrivalcity.Trim());
}
connection.Close();
}
catch (Exception ex)
{
FlightBox.Items.Add(ex.Message);
}
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.nord)" +
" FROM $EMIEDATI.oda200f o ";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
if (myReader.HasRows)
{
return(myReader.GetInt32(0));
}
else
{
return(0);
}
}
/// <summary>
/// With the sql string and the active connection retrieve the data
/// </summary>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <returns></returns>
private static iDB2DataReader ExecuteSql(string sql, iDB2Connection conn)
{
iDB2DataReader d_rdr = null;
try
{
using (iDB2Command cmd = new iDB2Command(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
d_rdr = cmd.ExecuteReader();
}
}
catch (iDB2DCFunctionErrorException ex)
{
throw new FaultException <DatabaseFault>(new DatabaseFault()
{
DbOperation = "Reading the database",
DbReason = "Exception accessing database",
DbMessage = ex.InnerException.Message
}, "Reading db error");
}
catch (iDB2SQLErrorException ex)
{
throw new FaultException <DatabaseFault>(new DatabaseFault()
{
DbOperation = "Sql error",
DbReason = "Exception accessing database",
DbMessage = ex.Message
}, "Sql error");
}
return(d_rdr);
}
private void DspPlane_Click(object sender, EventArgs e)
{
try
{
//Select everything within the AIRPLANE table
SQL = "Select * FROM AIRPLANE";
comm = new iDB2Command(SQL, conn);
conn.Open();
reader = comm.ExecuteReader();
while (reader.Read())
{
//Use reader and the listbox to display all non null values in the table
DisplayPlane.Items.Add("Plane#: " + reader.GetString(0) + " Max Flight Dist: "
+ reader.GetString(1) + " FClass: " + reader.GetString(2) + " CClass: "
+ reader.GetString(3) + " PAvail: " + reader.GetString(4) + " MaintMiles: "
+ reader.GetString(5) + " PlaneModel: " + reader.GetString(6) + " PlaneMake: "
+ reader.GetString(7) + " APARCD: " + reader.GetString(8) + " Date Maintenence: "
+ reader.GetString(12));
}
conn.Close();
}
//Any DB error catches
catch (iDB2Exception ie)
{
DisplayPlane.Items.Add(ie.Message);
}
catch (Exception ex)
{
DisplayPlane.Items.Add(ex.Message);
}
}
public AllAirports()
{
InitializeComponent();
AirportBox.Items.Clear();
AirportBox.Items.Add("AIRPORTS");
AirportBox.Items.Add("AIRPORT CODE, AIRPORT NAME");
try
{
SQL = "select ARCD,arnm from flight2019.airport";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
AirportBox.Items.Add(dataReader.GetString(0) + ", " + dataReader.GetString(1));
}
connection.Close();
}
catch (Exception ex)
{
AirportBox.Items.Add(ex.Message);
}
}
public iDB2DataReader GetDataReaderFromProcedureOnTrans(string strSQLWithoutCALLString, iDB2Parameter[] sqlParameters)
{
var objCmd = default(iDB2Command);
iDB2DataReader drProcedure;
try
{
objCmd = new iDB2Command("{CALL " + strSQLWithoutCALLString + "}", objConnection);
objCmd.Transaction = objTransaction;
objCmd.CommandType = CommandType.StoredProcedure;
foreach (var objPar in sqlParameters)
{
objCmd.Parameters.Add(objPar);
}
drProcedure = objCmd.ExecuteReader();
}
catch (Exception ex)
{
drProcedure = default;
if (objCmd is object)
{
objCmd.Dispose();
}
objException = ex;
}
return(drProcedure);
}
private void SubmitBtn2_Click(object sender, EventArgs e)
{
if (CrewText.Text != null && CrewText.Text.Length > 0)
{
CrewBox.Items.Clear();
CrewBox.Items.Add("CREW FOR FLIGHT: " + CrewText.Text);
try
{
SQL = "select f.flightno, c.pilots,c.attendants,c.resclerk,c.maint,c.janitor,c.refueling from flight2019.flight f inner join flight2019.crew c on f.flightno = c.flightno where f.FLIGHTNO='";
SQL += CrewText.Text + "'";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
CrewBox.Items.Add("Pilots: " + dataReader.GetString(1));
CrewBox.Items.Add("Attendants: " + dataReader.GetString(2));
CrewBox.Items.Add("ResClerks: " + dataReader.GetString(3));
CrewBox.Items.Add("Maint: " + dataReader.GetString(4));
CrewBox.Items.Add("Janitor: " + dataReader.GetString(5));
CrewBox.Items.Add("Refueling: " + dataReader.GetString(6));
}
connection.Close();
}
catch (Exception ex)
{
CrewBox.Items.Add(ex.Message);
}
}
}
private void SceltaFornitore_Load(object sender, EventArgs e)
{
Caricamento carica = new Caricamento();
carica.Show();
Application.DoEvents();
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT acscon, acrag1" +
" FROM $d_emil.acf00f a " +
" where actpcd = 'F'" +
" order by acrag1";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
Cmb_Fornitore.DataSource = dt;
Cmb_Fornitore.DisplayMember = "acrag1";
Cmb_Fornitore.ValueMember = "acscon";
carica.Close();
DBCONN.Close();
//primo numero libero
lbl_ordine.Text = Convert.ToString(PrimoNumeroLibero() + 1);
}
public string ObtenerRazonSocialAS400(string ruc)
{
string dResultado = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
try
{
iDB2Command cmd = new iDB2Command("SELECT * FROM RZZM01 WHERE NRUC = @Ruc FETCH FIRST 1 ROWS ONLY", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Ruc", SqlDbType.VarChar).Value = ruc;
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dResultado = dr.IsDBNull(dr.GetOrdinal("TCMPCL")) ? "" : dr.GetString(dr.GetOrdinal("TCMPCL"));
}
}
catch (Exception ex)
{
dResultado = "";
}
finally
{
cn.Close();
}
return(dResultado);
}
public MaintenanceLogs()
{
InitializeComponent();
LogBox.Items.Clear();
LogBox.Items.Add("Maint Logs");
try
{
SQL = "select * from flight2019.MaintLog";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
LogBox.Items.Add("Log#: " + dataReader.GetString(1));
LogBox.Items.Add("Plane#: " + dataReader.GetString(2));
LogBox.Items.Add("TaskID: " + dataReader.GetString(3));
LogBox.Items.Add("Maint Start Date: " + dataReader.GetString(4));
LogBox.Items.Add("Maint End Date: " + dataReader.GetString(5));
LogBox.Items.Add("Status: " + dataReader.GetString(6));
}
connection.Close();
}
catch (Exception ex)
{
LogBox.Items.Add(ex.Message);
}
}
private Boolean checkValidClient(iDB2Connection DBConnection, DataRow myDR, Int32 rowCnt)
{
if (myDR[0].ToString() == null || myDR[0].ToString() == "" || myDR[0].ToString() == " " || myDR[2].ToString() == null || myDR[2].ToString() == "" || myDR[2].ToString() == " ")
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
try
{
iDB2Command cmd = DBConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT CLTSTS FROM {0}.CLTMST WHERE CLTCAS = @CLIENT_ID", SCHEMA);
cmd.DeriveParameters();
cmd.Parameters["@CLIENT_ID"].Value = myDR[0].ToString();
iDB2DataReader db2r = cmd.ExecuteReader(CommandBehavior.Default);
DataTable myDT = new DataTable();
myDT.Load(db2r);
if (myDT.Rows.Count > 0)
{
if (myDT.Rows[0][0].ToString() == "A")
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.LightGreen;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.LightGreen;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.LightGreen;
Application.DoEvents();
return(true);
}
else
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
}
else
{
dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red;
dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red;
Application.DoEvents();
return(false);
}
}
catch (Exception)
{
throw;
}
}
public List <PendientesLlenos> ListaPendientesLlenos(string tipo)
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTEGRACIONTDEPOT_JMY", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XTIPO", iDB2DbType.iDB2VarChar).Value = tipo;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.NUMID = dr.IsDBNull(dr.GetOrdinal("NUMID")) ? 0 : dr.GetInt32(dr.GetOrdinal("NUMID"));
a.VesselIMO = dr.IsDBNull(dr.GetOrdinal("CINDVP")) ? "" : dr.GetString(dr.GetOrdinal("CINDVP"));
a.OceanCarrier = dr.IsDBNull(dr.GetOrdinal("CLINE")) ? "" : dr.GetString(dr.GetOrdinal("CLINE"));
a.CustomerERPCode = dr.IsDBNull(dr.GetOrdinal("NRUCA")) ? "" : dr.GetString(dr.GetOrdinal("NRUCA"));
a.YardCode = dr.IsDBNull(dr.GetOrdinal("CTPDEP")) ? "" : dr.GetString(dr.GetOrdinal("CTPDEP"));
a.ActionType = dr.IsDBNull(dr.GetOrdinal("CACTTP")) ? "" : dr.GetString(dr.GetOrdinal("CACTTP"));
a.VoyageNumber = dr.IsDBNull(dr.GetOrdinal("VOYNUM")) ? "" : dr.GetString(dr.GetOrdinal("VOYNUM"));
a.VoyageStopExternalCode = dr.IsDBNull(dr.GetOrdinal("CRECAL")) ? "" : dr.GetString(dr.GetOrdinal("CRECAL"));
a.ReferenceType = dr.IsDBNull(dr.GetOrdinal("CTDCRF")) ? "" : dr.GetString(dr.GetOrdinal("CTDCRF"));
a.ReferenceNumber = dr.IsDBNull(dr.GetOrdinal("NDCREF")) ? "" : dr.GetString(dr.GetOrdinal("NDCREF"));
a.OperationType = dr.IsDBNull(dr.GetOrdinal("CTIPOP")) ? "" : dr.GetString(dr.GetOrdinal("CTIPOP"));
a.PortOfDischarge = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfDelivery = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfReceipt = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.PortOfLoading = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.ShipmentType = dr.IsDBNull(dr.GetOrdinal("CTPCNC")) ? "" : dr.GetString(dr.GetOrdinal("CTPCNC"));
a.ActivityCode = dr.IsDBNull(dr.GetOrdinal("CACTVC")) ? "" : dr.GetString(dr.GetOrdinal("CACTVC"));
a.direction = dr.IsDBNull(dr.GetOrdinal("CDIREC")) ? "" : dr.GetString(dr.GetOrdinal("CDIREC"));
a.Status = dr.IsDBNull(dr.GetOrdinal("CSTSMV")) ? "" : dr.GetString(dr.GetOrdinal("CSTSMV"));
a.VehiclePlate = dr.IsDBNull(dr.GetOrdinal("NPLCUN")) ? "" : dr.GetString(dr.GetOrdinal("NPLCUN"));
a.ExecutionDate = dr.IsDBNull(dr.GetOrdinal("FCHMOV")) ? "" : dr.GetString(dr.GetOrdinal("FCHMOV"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
a.EquipmentTypeSizeISOCode = dr.IsDBNull(dr.GetOrdinal("ISOCODE")) ? "" : dr.GetString(dr.GetOrdinal("ISOCODE"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
/// <summary>
/// Returns downloaded Dictioanry with UPC codes matching items.
/// </summary>
public Dictionary <string, string> DownloadUpcForItemsAsync(BindableCollection <IpgModel> _ipgsCollection)
{
Console.WriteLine("Trying to connect to Reflex for downloading UPC codes...");
// Dictionary for holding Ean to Upc map.
Dictionary <string, string> Ean_Upc = new Dictionary <string, string>();
// Preparing a formatted list of items.
string _items = ConcatenateItemsIntoList(_ipgsCollection);
try
{
conn.Open();
if (conn != null)
{
Console.WriteLine("Successfully connected to Reflex for downloading UPC codes");
// Below are DB2 functions needed for executing query
string _queryString = $"SELECT VICART, VICIVL FROM {Environment}.HLVLIDP WHERE VICART IN {_items} and VICTYI = 'EAN_1' Order by VICIVL ";
iDB2Command comm = conn.CreateCommand();
comm.CommandText = _queryString;
iDB2DataReader reader = comm.ExecuteReader();
// Reader in while goes through all rows of results from Reflex.
while (reader.Read())
{
// Adds new key-value to a Dictionary.
Ean_Upc.Add(reader.GetString(0).ToString().Trim(), reader.GetString(1).ToString().Trim());
}
Console.WriteLine("Dictionary EAN_UPC created");
// Some cleaning needed.
reader.Close();
comm.Dispose();
// Return Dictionary
return(Ean_Upc);
}
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex);
Console.WriteLine(ex.StackTrace);
return(null);
}
finally
{
conn.Close();
}
// This will never reach by needs to be here because of error "Not all is returning value".
return(Ean_Upc);
}
public String CalcConsec(string tabla, int longitud)
{
String consecutivo = "0";
String consecutivoAct = "0";
StringBuilder query = new StringBuilder();
try
{
db.Open();
IDbTransaction idbt = db.BeginTransaction(IsolationLevel.RepeatableRead);
query.Clear();
query.Append(" SELECT DIGITS(DEC(DEC(TRIM(CCDESC), " + longitud + ", 0) + 1, " + longitud + ", 0))");
query.Append(" FROM ZCC");
query.Append(" WHERE CCTABL = 'SECUENCE' AND CCCODE = '" + tabla + "'");
IDbCommand cmm = new iDB2Command(query.ToString(), db, idbt);
IDataReader dr = cmm.ExecuteReader();
dr.Read();
consecutivo = dr.GetString(0);
dr.Close();
query.Clear();
if (consecutivo != "0")
{
consecutivoAct = consecutivo;
if (consecutivo == "99")
{
consecutivoAct = "00";
}
query.Append(" UPDATE ZCC SET");
query.Append(" CCDESC = '" + consecutivoAct + "'");
query.Append(" WHERE CCTABL = 'SECUENCE' AND CCCODE = '" + tabla + "'");
cmm.CommandText = query.ToString();
int r = cmm.ExecuteNonQuery();
}
idbt.Commit();
db.Close();
}
catch (iDB2SQLErrorException ex)
{
db.Close();
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
return(consecutivo);
}
public void Query_QuerySql_NoInsight()
{
string queryString = "SELECT * FROM Beer WHERE Country = ?";
iDB2Command command = new iDB2Command(queryString, Database);
command.Parameters.Add("Country", iDB2DbType.iDB2VarChar, 3).Value = "CO";
Database.Open();
using (iDB2DataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
DataTable dt = new DataTable("Beer");
dt.Load(reader);
}
}
public DataTable getVitalType(iDB2Connection DBConnection, string SCHEMA, string vitalDescription)
{
try
{
iDB2Command cmd = DBConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT EVLTYPE FROM {0}.EMRVTLMST WHERE EVLDSC = '{1}'", SCHEMA, vitalDescription);
iDB2DataReader db2r = cmd.ExecuteReader(CommandBehavior.Default);
DataTable myMasterDT = new DataTable();
myMasterDT.Load(db2r);
return(myMasterDT);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//This function returns true if the invoice found in the FSTINV table
private bool InvoiceExists(iDB2Command db2Command, string st)
{
bool exists = false;
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT * FROM FSTINV WHERE SIID = {0}", st);
iDB2DataReader reader = db2Command.ExecuteReader();
exists = reader.HasRows;
reader.Close();
db2Command.Transaction.Dispose();
}
return(exists);
}
static void Main(string[] args)
{
//Nomi tabelle
//SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DBNAME';
//Nomi colonne
//SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;
//numerocolonne
//SELECT max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?;
Console.WriteLine("host: ");
string host = Console.ReadLine();
Console.WriteLine("username: "******"password: "******"DB Name: ");
string dbname = Console.ReadLine();
Console.WriteLine("Namespace: ");
string namespacename = Console.ReadLine();
conn = new iDB2Connection("Data Source="+host+";user id="+user+";password="******";");
conn.Open();
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + dbname + "'";
row = cmd.ExecuteReader();
List<string> tablenames = new List<string>();
while (row.Read())
{
tablenames.Add(row["TABLE_NAME"].ToString());
}
row.Close();
foreach (var nome in tablenames)
{
Console.WriteLine(nome);
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= '" + nome + "' AND TABLE_SCHEMA = '" + dbname + "' ORDER BY ORDINAL_POSITION";
List<string> columnNames = new List<string>();
List<string> columnDefaults = new List<string>();
List<string> areNullables = new List<string>();
List<string> dataTypes = new List<string>();
row = cmd.ExecuteReader();
while (row.Read())
{
columnNames.Add(row["COLUMN_NAME"].ToString());
columnDefaults.Add(row["COLUMN_DEFAULT"].ToString());
areNullables.Add(row["IS_NULLABLE"].ToString());
dataTypes.Add(row["DATA_TYPE"].ToString());
}
row.Close();
for (int k = 0; k < columnNames.Count; k++)
{
if (dataTypes[k].ToLower() == "varchar" || dataTypes[k].ToLower() == "date" || dataTypes[k].ToLower() == "timestamp" || dataTypes[k].ToLower() == "text" || dataTypes[k].ToLower() == "datetime" || dataTypes[k].ToLower() == "national character large object" || dataTypes[k].ToLower() == "national character varying")
dataTypes[k] = "string";
if (dataTypes[k].ToLower() == "tinyint" || dataTypes[k].ToLower() == "bigint" || dataTypes[k].ToLower() == "smallint" || dataTypes[k].ToLower() == "mediumint" || dataTypes[k].ToLower() == "bit" || dataTypes[k].ToLower() == "integer")
dataTypes[k] = "int";
if (dataTypes[k].ToLower() == "decimal" || dataTypes[k].ToLower() == "numeric")
dataTypes[k] = "double";
if (dataTypes[k].ToLower() == "character" || dataTypes[k].ToLower() == "character varying")
dataTypes[k] = "string";
if (columnNames[k].ToLower() == "class")
columnNames[k] = "class_var";
if (columnNames[k].ToLower() == "int")
columnNames[k] = "int_var";
}
string pathstring = "./";
pathstring = Path.Combine(pathstring, nome + ".cs");
if (!File.Exists(pathstring))
{
StreamWriter sw = File.AppendText(pathstring);
sw.WriteLine("using System;");
sw.WriteLine("using System.Collections.Generic;");
sw.WriteLine("using System.Linq;");
sw.WriteLine("using System.Threading.Tasks;");
sw.WriteLine("using System.Text;");
sw.WriteLine("using IBM.Data.DB2.iSeries;");
sw.WriteLine("");
sw.WriteLine("namespace " + namespacename);
sw.WriteLine("{");
sw.WriteLine("\tclass " + UppercaseFirst(nome));
sw.WriteLine("\t{");
int i = 0;
foreach (var columnName in columnNames)
{
string defaultvalue = "null";
if (columnDefaults[i] != "")
{
if (Regex.IsMatch(columnDefaults[i], @"\d"))
defaultvalue = columnDefaults[i];
else
defaultvalue = "\"" + columnDefaults[i] + "\"";
}
if ((dataTypes[i].ToLower() == "int" || dataTypes[i].ToLower() == "double") && columnDefaults[i] == "")
defaultvalue = "0";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == ""))
defaultvalue = "Char.MinValue";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i] != ""))
defaultvalue = "'" + columnDefaults[i] + "'";
if (((dataTypes[i].ToLower() == "float" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "float" && columnDefaults[i] == "")) || ((dataTypes[i].ToLower() == "double" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] == "")))
defaultvalue = "0.0f";
if ((dataTypes[i].ToLower() == "float" && columnDefaults[i] != "") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] != ""))
defaultvalue = columnDefaults[i] + "f";
if (defaultvalue == "\"''\"" || defaultvalue == "\"CURRENT_DATE\"" || defaultvalue == "\"' '\"")
defaultvalue = "\"\"";
sw.WriteLine("\t\t" + dataTypes[i] + " " + columnName.ToLower() + " { get; set; } = " + defaultvalue + ";");
sw.WriteLine("\t\t" + dataTypes[i] + " OLD_" + columnName.ToLower() + " = " + defaultvalue + ";");
sw.WriteLine("");
i++;
}
sw.WriteLine("\t\tiDB2Connection conn = new iDB2Connection(\"Data Source=<HOST>;user id=<USER>;password=<PASSWORD>;\");");
sw.WriteLine("");
List<string> parametri = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
parametri.Add(dataTypes[k] + " _" + columnNames[k].ToLower());
}
var parametristring = String.Join(", ", parametri);
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "() { }");
sw.WriteLine("");
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "(" + parametristring + ")");
sw.WriteLine("\t\t{");
for (int k = 0; k < parametri.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + " = _" + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void delete()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> whereStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
whereStatementArray.Add(columnNames[k] + " = @" + columnNames[k]);
}
var whereStatementString = String.Join(" AND ", whereStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"DELETE FROM " + dbname + "." + nome + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);"); //always varchar so i don't have problem to handle strings
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void update()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> updateStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
updateStatementArray.Add(columnNames[k] + " = @new" + columnNames[k]);
}
var updateStatementString = String.Join(", ", updateStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"UPDATE " + dbname + "." + nome + " SET " + updateStatementString + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter OLD_" + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@new" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + "Parameter.Value = OLD_" + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(OLD_" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tprivate void updateOldValues()");
sw.WriteLine("\t\t{");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + " = " + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t}");
sw.WriteLine("\t}");
sw.WriteLine("}");
sw.Close();
}
}
conn.Close();
Console.ReadLine();
}
private void getCampaignData()
{
//remove all preexisting data from the list
campaignData.Clear();
// Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("Campaigns");
dt.Columns.Add("CampaignName");
dt.Columns.Add("CampaignDisplayName");
dt.Columns.Add("CallMethod");
dt.Columns.Add("AccountsRemaining");
dt.Columns.Add("InitialAccounts");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "select m.rmp2prjt "
+ ",m.CallMethod "
+ ",COALESCE(c.cnt, 0) AS nowAccounts "
+ ",COALESCE(m.cnt, 0 ) AS initialAccounts "
+ "FROM "
+ "(SELECT rmp2prjt "
+ ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod "
+ ",count(*) as cnt "
+ "FROM monprddta.crmmascopy "
+ "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' "
+ "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) m "
+ "LEFT JOIN "
+ "(SELECT rmp2prjt "
+ ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod "
+ ",count(*) as cnt "
+ "FROM cdqfil.crmmas "
+ "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' "
+ "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) c on c.rmp2prjt = m.rmp2prjt and c.callmethod = m.callmethod";
cmd.Connection = cn;
//prepare for Date parameter
String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString();
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@Date"].Value = date;
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["Campaigns"].NewRow();
datarow["CampaignName"] = readFile.GetString(0).Trim();
datarow["CallMethod"] = readFile.GetString(1);
datarow["AccountsRemaining"] = readFile.GetiDB2Integer(2);
datarow["InitialAccounts"] = readFile.GetiDB2Integer(3);
//add the row to the data table Campaigns
dataStructure.Tables["Campaigns"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["CampaignName"].ToString())
{
case "MAS":
row["CampaignDisplayName"] = "MAS";
sortId = 0;
break;
case "MASSP":
row["CampaignDisplayName"] = "MAS SP";
sortId = 1;
break;
case "MASSS":
row["CampaignDisplayName"] = "ALT";
sortId = 2;
break;
case "MASSPS":
row["CampaignDisplayName"] = "ALT SP";
sortId = 3;
break;
case "MASFC":
row["CampaignDisplayName"] = "FOCUS";
sortId = 4;
break;
case "MASSPFC":
row["CampaignDisplayName"] = "FOCUS SP";
sortId = 5;
break;
case "MASWC":
row["CampaignDisplayName"] = "WC";
sortId = 6;
break;
case "MASWCSP":
row["CampaignDisplayName"] = "WC SP";
sortId = 7;
break;
case "M2GEN":
row["CampaignDisplayName"] = "2G";
sortId = 8;
break;
case "M2GSP":
row["CampaignDisplayName"] = "2G SP";
sortId = 9;
break;
case "MASPS":
row["CampaignDisplayName"] = "BULK";
sortId = 10;
break;
case "2NDPASS":
row["CampaignDisplayName"] = "2NDPASS";
sortId = 11;
break;
case "INBOUND":
row["CampaignDisplayName"] = "INBOUND";
sortId = 12;
break;
default:
//nothing
break;
}
campaignData.Add(new Campaign(sortId
, row["CampaignName"].ToString()
, row["CampaignDisplayName"].ToString()
, row["CallMethod"].ToString()
, row["InitialAccounts"].ToString()
, row["AccountsRemaining"].ToString(), "0", "0"));
}
}
private void getCampaignBucketData()
{
//remove all preexisting data from the list
campaignBuckets.Clear();
//Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("CampaignBuckets");
dt.Columns.Add("Campaign");
dt.Columns.Add("Bucket");
dt.Columns.Add("CallMethod");
dt.Columns.Add("Count");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "SELECT bucket, CCPPRJ, 'P' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket)<>ccpprj left join cdqfil.crmmas on ccpprj=rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt=@Date and rmp2rflg in ('1','H') and rmdnc='' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj UNION SELECT bucket, CCPPRJ, 'M' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket) <> ccpprj left join cdqfil.crmmas on ccpprj = rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt = @Date and rmp2rflg in ('M', 'h') and rmdnc = '' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj ORDER BY type, ccpprj, bucket";
cmd.Connection = cn;
//prepare for Date parameter
String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString();
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@Date"].Value = date;
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["CampaignBuckets"].NewRow();
datarow["Bucket"] = readFile.GetString(0).Trim();
datarow["Campaign"] = readFile.GetString(1).Trim();
datarow["CallMethod"] = readFile.GetString(2);
datarow["Count"] = readFile.GetString(3);
//add the row to the data table Campaigns
dataStructure.Tables["CampaignBuckets"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["Campaign"].ToString())
{
case "MAS":
//do nothing as it's named correctly already
break;
case "MASSP":
row["Campaign"] = "MAS SP";
break;
case "MASSS":
row["Campaign"] = "ALT";
break;
case "MASSPS":
row["Campaign"] = "ALT SP";
break;
case "MASFC":
row["Campaign"] = "FOCUS";
break;
case "MASSPFC":
row["Campaign"] = "FOCUS SP";
break;
default:
//nothing
break;
}
campaignBuckets.Add(new CampaignBucket(row["Campaign"].ToString(), row["Bucket"].ToString(), row["CallMethod"].ToString(), row["Count"].ToString()));
}
}
private void getCampaignMetricData()
{
//remove all preexisting data from the list
campaignMetrics.Clear();
//Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("CampaignMetric");
dt.Columns.Add("CampaignName");
dt.Columns.Add("CampaignDisplayName");
dt.Columns.Add("CallMethod");
dt.Columns.Add("Metric");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "select * from monprddta.campaignmetrics";
cmd.Connection = cn;
cn.Open();
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["CampaignMetric"].NewRow();
datarow["CallMethod"] = readFile.GetString(0).Trim();
datarow["CampaignName"] = readFile.GetString(1).Trim();
datarow["Metric"] = readFile.GetiDB2Decimal(2);
//add the row to the data table Campaigns
dataStructure.Tables["CampaignMetric"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["CampaignName"].ToString())
{
case "MAS":
row["CampaignDisplayName"] = "MAS";
break;
case "MASSP":
row["CampaignDisplayName"] = "MAS SP";
break;
case "MASSS":
row["CampaignDisplayName"] = "ALT";
break;
case "MASSPS":
row["CampaignDisplayName"] = "ALT SP";
break;
case "MASFC":
row["CampaignDisplayName"] = "FOCUS";
break;
case "MASSPFC":
row["CampaignDisplayName"] = "FOCUS SP";
break;
case "MASWC":
row["CampaignDisplayName"] = "WC";
break;
case "MASWCSP":
row["CampaignDisplayName"] = "WC SP";
break;
case "M2GEN":
row["CampaignDisplayName"] = "2G";
break;
case "M2GSP":
row["CampaignDisplayName"] = "2G SP";
break;
case "MASPS":
row["CampaignDisplayName"] = "BULK";
break;
case "2NDPASS":
row["CampaignDisplayName"] = "2NDPASS";
break;
case "INBOUND":
row["CampaignDisplayName"] = "INBOUND";
break;
default:
//nothing
break;
}
campaignMetrics.Add(new CampaignMetric(row["CampaignName"].ToString(), row["CampaignDisplayName"].ToString(), row["CallMethod"].ToString(), row["Metric"].ToString()));
}
}
public List<PaymentDetail> GetGeniusCheques(string paymentIDs)
{
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("GetGeniusCheques({0})", paymentIDs));
}
this.BuildConnectionString();
List<PaymentDetail> paymentDetails = null;
ArgumentCheck.ArgumentNullOrEmptyCheck(this.GeniusConnectionString, Constants.ERRORMESSAGE_GENIUS_CONNECTIONSTRING);
ArgumentCheck.ArgumentNullOrEmptyCheck(this.GeniusQuery, Constants.ERRORMESSAGE_GENIUS_QUERY);
try
{
using (iDB2Connection conn = new iDB2Connection(this.GeniusConnectionString))
{
string selectSql = String.Format(this.GeniusQuery, this.GeniusSchema, paymentIDs);
using (iDB2Command cmd = new iDB2Command(selectSql, conn))
{
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("Genius Sql:", selectSql));
}
iDB2DataReader reader = null;
try
{
cmd.CommandTimeout = this.CommandTimeout ;
conn.Open();
if (logger.IsDebugEnabled)
{
logger.Debug("Connection opened");
}
reader = cmd.ExecuteReader();
while (reader.Read())
{
PaymentDetail paymentDetail = new PaymentDetail();
paymentDetail.PaymentRequestID = reader.GetInt64(0);
paymentDetail.ChequeNumber = reader.GetString(1).TrimEnd();
if (paymentDetails == null)
{
paymentDetails = new List<PaymentDetail>();
}
paymentDetails.Add(paymentDetail);
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetGeniusCheques - {0}", ex.Message));
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetGeniusCheques - {0}", ex.Message));
throw ex;
}
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("AddErrorLogHeader({0}) => returns: paymnet details",paymentIDs));
}
return paymentDetails;
}
