public void Commit()
{
transaction.Commit();
connection.Dispose();
connection.Close();
transaction = null;
connection = null;
}
public bool conectar()
{
try
{
AS400ConnectionString.Open();
AS400ConnectionString.Close();
return(true);
}
catch (Exception ex)
{
string error = ex.Message;
return(false);
}
}
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 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);
}
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj TALENTTKTClose()
{
ErrorObj err = new ErrorObj();
//------------------------------------------------------------------------
// Warning : Using ErrorObj here when closing the DB does cause a
// problem as it will clear the err object when an actual
// error has occured elsewhere
//------------------------------------------------------------------------
try
{
if (!(conTALENTTKT == null))
{
if (conTALENTTKT.State == ConnectionState.Open)
{
conTALENTTKT.Close();
}
}
conTALENTTKT = null;
}
catch (Exception ex)
{
const string strError4 = "Failed to close the TALENTTKT database connection";
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError4;
err.ErrorNumber = "TACDBAC-P6";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
return(err);
}
return(err);
}
public bool SubmitJob(string jobName, string param, out string oMessage)
{
bool res = false;
oMessage = string.Empty;
try
{
string Cmd = "SBMJOB CMD(CALL " + jobName + " PARM('" + param + "')) ";
string sql = "CALL QSYS.QCMDEXC('" + Cmd.Replace("'", "''") + "'," + Cmd.Length.ToString("0000000000.00000") + ")";
_conn = new iDB2Connection(ConnectionString);
_conn.Open();
_command = new iDB2Command(sql, _conn);
_command.ExecuteNonQuery();
_conn.Close();
res = true;
}
catch (iDB2Exception iEx)
{
oMessage = iEx.Message;
}
catch (Exception ex)
{
oMessage = ex.Message;
}
return(res);
}
private void EndDatabaseSession()
{
if (_dB2connection != null && _dB2connection.State == ConnectionState.Open)
{
_dB2connection.Close();
}
}
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);
}
// PUT api/values/5
public void Put(int id, [FromBody] Contacto oContacto)
{
string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString;
iDB2Connection connection = new iDB2Connection(connString);
connection.Open();
iDB2Command db2Command = new iDB2Command();
string query = "UPDATE DAVIDEC1.CONTACTOS " +
"SET NOMBRE = @nombre , " +
"TELEFONO = @telefono , " +
"CORREO = @correo , " +
"EDAD = @edad " +
"WHERE ID = @id";
db2Command.Connection = connection;
db2Command.CommandText = query;
db2Command.Parameters.Add(new iDB2Parameter("@nombre", oContacto.Nombre));
db2Command.Parameters.Add(new iDB2Parameter("@telefono", oContacto.Telefono));
db2Command.Parameters.Add(new iDB2Parameter("@correo", oContacto.Correo));
db2Command.Parameters.Add(new iDB2Parameter("@edad", oContacto.Edad));
db2Command.Parameters.Add(new iDB2Parameter("@id", id));
db2Command.ExecuteNonQuery();
connection.Close();
}
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);
}
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 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);
}
}
}
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 static void ShippingReport()
{
using (iDB2Connection conn = new iDB2Connection(Get("iSeries")))
{
using (iDB2Command cmd = conn.CreateCommand())
{
cmd.CommandText = "VT2662AP.ShippingReport";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
iDB2Parameter parm = cmd.Parameters.Add("@SHIPDATE", iDB2DbType.iDB2Decimal);
cmd.Parameters["@SHIPDATE"].Value = 20151219;
conn.Open();
iDB2DataReader myshipment = cmd.ExecuteReader();
if (myshipment.HasRows)
{
while (myshipment.Read())
{
Console.WriteLine("{0} has {1} - {2} pick {3} {4}", myshipment.GetString(0),
myshipment.GetString(1), myshipment.GetString(2), myshipment.GetString(3),
myshipment.GetString(8));
}
}
myshipment.Close();
conn.Close();
}
}
}
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 void Desconectar()
{
if (_Conexion != null && _Conexion.State.Equals(ConnectionState.Open))
{
_Conexion.Close();
}
}
public static DataSet GetDataSet(string commandText, CommandType commandType, iDB2Parameter[] parameters = null)
{
try
{
using (iDB2Connection connection = new iDB2Connection(ConfigurationManager.ConnectionStrings["DB2"].ConnectionString))
{
connection.Open();
using (iDB2Command command = new iDB2Command(commandText, commandType, connection))
{
command.CommandTimeout = 300;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
var dataset = new DataSet();
iDB2DataAdapter dataAdaper = new iDB2DataAdapter(command);
dataAdaper.Fill(dataset);
connection.Close();
return(dataset);
}
}
}
catch (Exception ex)
{
throw;
}
}
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);
}
public void EndTransaction(bool blnCommit)
{
try
{
if (blnCommit == true)
{
objTransaction.Commit();
}
else
{
objTransaction.Rollback();
}
}
catch (Exception ex)
{
objException = ex;
}
finally
{
//objClear.CleanupPooledConnections();
objConnection.Close();
objTransaction.Dispose();
objTransaction = null;
blnTrans = false;
if (objCommand is object)
{
objCommand.Dispose();
objCommand = null;
}
}
}
private void JoshuaForm_Load(object sender, EventArgs e)
{
string sql;
string connectionString;
try
{
connectionString = "Data Source = deathstar.gtc.edu; User ID = ITPA485; Initial Catalog = S101FF5C";
sql = "SELECT * FROM EMPLOYEE";
connection = new iDB2Connection(connectionString);
dataAdapter = new iDB2DataAdapter(sql, connection);
EMPLOYEE = new DataSet();
dataAdapter.Fill(EMPLOYEE);
foreach (DataRow pRow in EMPLOYEE.Tables[0].Rows)
{
listBox1.Items.Add(pRow[1] + " " + pRow[2] + " " + pRow[17]);
}
connection.Close();
}
catch (Exception ex)
{
listBox1.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 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);
}
}
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);
}
public void InsertarStockLlenos(PendientesLlenos input)
{
//
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_ACCIONES_DSSTOCK", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NUMUID", SqlDbType.VarChar).Value = Guid.NewGuid().ToString();
cmd.Parameters.Add("@NUMID", SqlDbType.Decimal).Value = 0;
cmd.Parameters.Add("@NUMIDE", SqlDbType.VarChar).Value = input.YardCode;
cmd.Parameters.Add("@NROCON", SqlDbType.VarChar).Value = input.EquipmentNumber;
cmd.Parameters.Add("@DOCREF", SqlDbType.VarChar).Value = "";
cmd.Parameters.Add("@TIPOPE", SqlDbType.VarChar).Value = "L";
cmd.Parameters.Add("@FECREG", SqlDbType.Decimal).Value = (DateTime.Now.ToString("yyyyMMdd"));
cmd.Parameters.Add("@HRSREG", SqlDbType.Decimal).Value = (DateTime.Now.ToString("HHmmss"));
cmd.Parameters.Add("@USRREG", SqlDbType.VarChar).Value = "CARGAINI";
cmd.Parameters.Add("@SESTRG", SqlDbType.VarChar).Value = "A";
cmd.Parameters.Add("@ACCION", SqlDbType.VarChar).Value = "I";
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
}
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 void grabarUbicacionesLlenos(UbicacionContenedor datos, decimal fultac, decimal hultac)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTTRANSDEP_GRABA_UBICACION_RZIN03", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("IN_CTDCRF", iDB2DbType.iDB2VarChar).Value = datos.TipoDocumento;
cmd.Parameters.Add("IN_NDCREF", iDB2DbType.iDB2VarChar).Value = datos.DocumentoReferencia;
cmd.Parameters.Add("IN_CPRCNO", iDB2DbType.iDB2VarChar).Value = datos.Contenedor.Substring(0, 4);
cmd.Parameters.Add("IN_NSRCNO", iDB2DbType.iDB2VarChar).Value = datos.Contenedor.Substring(4, datos.Contenedor.Length - 4);
cmd.Parameters.Add("IN_TUBCAL", iDB2DbType.iDB2VarChar).Value = datos.Ubicacion;
cmd.Parameters.Add("IN_NROMVM", iDB2DbType.iDB2VarChar).Value = datos.NumeroMovimientos;
cmd.Parameters.Add("IN_FULTAC", iDB2DbType.iDB2Numeric).Value = fultac;
cmd.Parameters.Add("IN_HULTAC", iDB2DbType.iDB2Numeric).Value = hultac;
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
}
public void grabarUbicaciones(string TipDoc, string DocRef, string Sigla, string Serie, string Ubicacion, int NroMovimientos)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTTRANSDEP_GRABA_UBICACION_RZIN02", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("IN_CTDCRF", iDB2DbType.iDB2VarChar).Value = TipDoc;
cmd.Parameters.Add("IN_NDCREF", iDB2DbType.iDB2VarChar).Value = DocRef;
cmd.Parameters.Add("IN_CPRCNO", iDB2DbType.iDB2VarChar).Value = Sigla;
cmd.Parameters.Add("IN_NSRCNO", iDB2DbType.iDB2VarChar).Value = Serie;
cmd.Parameters.Add("IN_TUBCAL", iDB2DbType.iDB2VarChar).Value = Ubicacion;
cmd.Parameters.Add("IN_NROMVM", iDB2DbType.iDB2VarChar).Value = NroMovimientos;
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
}
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);
}
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 static object ExecuteDb2Scalar(CommandType commandType, string instructionSQL, Object parameters = null)
{
object rpta;
try
{
var strEsquema = ConfigurationManager.AppSettings["Esquema"];
Ambito.Esquema = strEsquema;
Contexto contexto = new Contexto(Ambito.Esquema);
iDB2Connection conexion = new iDB2Connection();
conexion = contexto.getConexionRNSLIB;
if (conexion.State == ConnectionState.Open)
{
conexion.Close();
}
conexion.Open();
iDB2Command dbCmd = new iDB2Command();
dbCmd.Connection = conexion;
dbCmd.CommandType = commandType;
dbCmd.CommandText = instructionSQL;
iDB2Parameter[] db2Parameters = GetParameters(parameters);
if (db2Parameters.Length > 0)
{
dbCmd.Parameters.AddRange(db2Parameters);
}
rpta = dbCmd.ExecuteScalar();
conexion.Close();
}
catch (Exception ex)
{
rpta = ex.Message;
return(rpta);
}
return(rpta);
}
public static DataTable ExecuteDb2Query(CommandType commandType, string instructionSQL, Object parameters = null)
{
try
{
var strEsquema = ConfigurationManager.AppSettings["Esquema"];
Ambito.Esquema = strEsquema;
Contexto contexto = new Contexto(Ambito.Esquema);
iDB2Connection conexion = new iDB2Connection();
conexion = contexto.getConexionRNSLIB;
if (conexion.State == ConnectionState.Open)
{
conexion.Close();
}
conexion.Open();
iDB2Command dbCmd = new iDB2Command();
dbCmd.Connection = conexion;
dbCmd.CommandType = commandType;
dbCmd.CommandText = instructionSQL;
iDB2Parameter[] db2Parameters = GetParameters(parameters);
if (db2Parameters.Length > 0)
{
dbCmd.Parameters.AddRange(db2Parameters);
}
iDB2DataAdapter da = new iDB2DataAdapter();
da.SelectCommand = dbCmd;
DataTable dtResult = new DataTable();
da.Fill(dtResult);
conexion.Close();
return(dtResult);
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
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 updateCampaignMetricData(string metric, string callMethod, string campaign)
{
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "update monprddta.campaignmetrics set Metric = @metric where CallType = @callMethod and Campaign = @campaign";
cmd.Connection = cn;
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@metric"].Value = Convert.ToDecimal(metric);
cmd.Parameters["@callMethod"].Value = callMethod;
cmd.Parameters["@campaign"].Value = campaign;
cmd.ExecuteNonQuery();
//Clean up - Close connections
cmd.Dispose();
cn.Close();
}
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 void upsertGroup(Group group)
{
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "merge into issysdta.shoreteleccinboundgroupdaily as t "
+ "using ( values( cast(@groupId as smallint) "
+ ",cast(@groupName as varchar(40)) "
+ ",cast(@queuedCallsAboveTasa as smallint) "
+ ",cast(@queuedCalls as smallint) "
+ ",cast(@maxQueTime as integer) "
+ ",cast(@agentsLoggedIn as smallint) "
+ ",cast(@agentsIdle as smallint) "
+ ",cast(@acdInCalls as integer) "
+ ",cast(@tsf as smallint) "
+ ",cast(@abandonedCalls as integer) "
+ ")) "
+ "as zac(group_id "
+ ", group_name "
+ ", queued_calls_above_tasa "
+ ", queued_calls "
+ ", max_que_time "
+ ", agents_logged_in "
+ ", agents_idle "
+ ", acd_in_calls "
+ ", tsf "
+ ", abandoned_calls) "
+ "on t.group_id = zac.group_id "
+ "when matched then update "
+ "set t.group_name = zac.group_name "
+ ", t.queued_calls_above_tasa = zac.queued_calls_above_tasa "
+ ", t.queued_calls = zac.queued_calls "
+ ", t.max_que_time = zac.max_que_time "
+ ", t.agents_logged_in = zac.agents_logged_in "
+ ", t.agents_idle = zac.agents_idle "
+ ", t.acd_in_calls = zac.acd_in_calls "
+ ", t.tsf = zac.tsf "
+ ", t.abandoned_calls = zac.abandoned_calls "
+ "when not matched then "
+ "insert (group_id "
+ ", group_name "
+ ", queued_calls_above_tasa "
+ ", queued_calls "
+ ", max_que_time "
+ ", agents_logged_in "
+ ", agents_idle "
+ ", acd_in_calls "
+ ", tsf "
+ ", abandoned_calls) "
+ "values (zac.group_id "
+ ", zac.group_name "
+ ", zac.queued_calls_above_tasa "
+ ", zac.queued_calls "
+ ", zac.max_que_time "
+ ", zac.agents_logged_in "
+ ", zac.agents_idle "
+ ", zac.acd_in_calls "
+ ", zac.tsf "
+ ", zac.abandoned_calls) ";
cmd.Connection = cn;
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@groupName"].Value = group.GroupName;
cmd.Parameters["@queuedCallsAboveTasa"].Value = Convert.ToInt32(group.InboundAboveTargetAverageSpeedAnswer);
cmd.Parameters["@queuedCalls"].Value = Convert.ToInt32(group.InboundCallsInQueue);
cmd.Parameters["@maxQueTime"].Value = Convert.ToInt32(group.InboundMaxInQueue);
cmd.Parameters["@agentsLoggedIn"].Value = group.NumberOfAgents;
cmd.Parameters["@agentsIdle"].Value = group.NumberIdle;
cmd.Parameters["@acdInCalls"].Value = group.AutomaticCallDistribution;
cmd.Parameters["@tsf"].Value = group.TargetServiceFactor;
cmd.Parameters["@abandonedCalls"].Value = group.AbandonedCalls;
cmd.Parameters["@groupId"].Value = group.Id;
cmd.ExecuteNonQuery();
//Clean up - Close connections
cmd.Dispose();
cn.Close();
}
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"));
}
}
