private void button1_Click_1(object sender, EventArgs e)
{
// svuota file di passaggio
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "delete from ODA201P ";
myCommand.ExecuteNonQuery();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[3].Value != null)
{
myCommand.CommandText = "insert into ODA201P " +
" values('" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'," +
dataGridView1.Rows[i].Cells[1].Value.ToString() + "," +
"'" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "'," +
"'" + dataGridView1.Rows[i].Cells[3].Value.ToString() + "'," +
"'" + dataGridView1.Rows[i].Cells[4].Value.ToString() + "')";
myCommand.ExecuteNonQuery();
//MessageBox.Show(dataGridView1.Rows[i].Cells[3].Value.ToString());
}
}
FrmStampa fsta = new FrmStampa();
fsta.ShowDialog();
}
// 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 void Call(Session session)
{
string program = this.LibraryWhereProgram.Name + "." + this.Name;
command = new iDB2Command(program, session.Connection, session.Transaction);
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in this.Parameters)
{
iDB2Parameter param = new iDB2Parameter();
parameter.Fill(param);
this.command.Parameters.Add(param);
}
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
foreach (Parameter parameter in this.Parameters)
{
iDB2Parameter db2Parameter = command.Parameters[parameter.Name];
if (db2Parameter != null)
{
parameter.UpdateValue(db2Parameter);
}
}
}
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 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 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 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 bool Call400Pgm(string cmdtext, IBM.Data.DB2.iSeries.iDB2Connection cn)
{
// ------------------------------------------------------------
// This method runs a command on the iSeries using QCMDEXC.
//
// cmdtext is the command or program you want to call.
// cn is an open iDB2Connection the command will be run on.
// If the command runs without error, this method returns true.
//------------------------------------------------------------
bool rc = true;
// Construct a string which contains the call to QCMDEXC.
// Because QCMDEXC uses single quote characters, we must
// delimit single quote characters in the command text
// with an extra single quote.
string pgmParm = "CALL QSYS.QCMDEXC('" + cmdtext.Replace("'", "''") + "', " + cmdtext.Length.ToString("0000000000.00000") + ")";
iDB2Command cmd = new iDB2Command(pgmParm, cn);
try
{
cmd.ExecuteNonQuery();
}
catch
{
rc = false;
}
cmd.Dispose();
return(rc);
}
//This Function inserts comments to the FSTINVC Table
private void InsertComments(iDB2Command db2Command, FSTINV inv)
{
st.insertLog(string.Format("Inserting notes for {0} to FSTINVC table.", inv.SIID), "Info", inv.SIID.ToString(), HillerServiceDataMigrator.LogId);
string tableName = "FSTINVC";
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("INSERT INTO {0} (SCID, SCCMTS, SCCRDT) VALUES(@p1, @p2, @p3) WITH NONE",
tableName);
db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Notes.Replace("'", "''")));
db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));
// st.insertLog(db2Command.CommandText, "FSTINVC: Insert Query", "NA",LogId);
var recordsAffected = db2Command.ExecuteNonQuery();
db2Command.Transaction.Commit();
st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
}
}
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);
}
/// <summary>
/// Runs a command on the IBM i.
/// </summary>
/// <param name="cmdText">The command text.</param>
/// <param name="cn">The connection object.</param>
/// <returns></returns>
public static bool RunCmd(string cmdText, iDB2Connection cn)
{
bool rc = true;
// Construct a string which contains the call to QCMDEXC.
string pgmParm = String.Format("CALL QSYS.QCMDEXC('{0}', {1})", cmdText.Replace("'", "''").Trim(), cmdText.Trim().Length.ToString("0000000000.00000"));
using (cn)
{
using (iDB2Command cmd = new iDB2Command(pgmParm, cn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
rc = false;
}
}
}
// Return success or failure
return rc;
}
/// <summary>
/// Runs a command on the IBM i.
/// </summary>
/// <param name="cmdText">The command text.</param>
/// <param name="cn">The connection object.</param>
/// <returns></returns>
public static bool RunCmd(string cmdText, iDB2Connection cn)
{
bool rc = true;
// Construct a string which contains the call to QCMDEXC.
string pgmParm = String.Format("CALL QSYS.QCMDEXC('{0}', {1})", cmdText.Replace("'", "''").Trim(), cmdText.Trim().Length.ToString("0000000000.00000"));
using (cn)
{
using (iDB2Command cmd = new iDB2Command(pgmParm, cn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
rc = false;
}
}
}
// Return success or failure
return(rc);
}
/// <summary>
/// Execute an iDB2Command (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new iDB2Parameter(":prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of iDB2Paramters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(iDB2Connection connection, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters)
{
iDB2Command cmd = new iDB2Command();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
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);
}
private void btnInsert_Click(object sender, EventArgs e)
{
try
{
string insertStatement = "INSERT INTO ITPA400.WorkOrder " +
"(orderID, planeNo, taskID, strDat, endDat, woStat, partsLbr, lbrHours) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
iDB2Connection connection = new iDB2Connection("DataSource=deathstar.gtc.edu");
iDB2Command command = new iDB2Command(insertStatement, connection);
command.Parameters.Add(new iDB2Parameter("orderID", iDB2DbType.iDB2Char, 10));
command.Parameters.Add(new iDB2Parameter("planeNo", iDB2DbType.iDB2Char, 10));
command.Parameters.Add(new iDB2Parameter("taskID", iDB2DbType.iDB2Char, 10));
command.Parameters.Add(new iDB2Parameter("strDat", iDB2DbType.iDB2Date));
command.Parameters.Add(new iDB2Parameter("endDat", iDB2DbType.iDB2Date));
command.Parameters.Add(new iDB2Parameter("woStat", iDB2DbType.iDB2Char, 10));
command.Parameters.Add(new iDB2Parameter("partsLbr", iDB2DbType.iDB2Decimal, 10));
command.Parameters.Add(new iDB2Parameter("lbrHours", iDB2DbType.iDB2Decimal, 7));
command.Parameters["orderID"].Value = txtWrkOrdID.Text;
command.Parameters["planeNo"].Value = cbxPlaneNo.Text;
command.Parameters["taskID"].Value = cbxTaskID.Text;
command.Parameters["strDat"].Value = dteStart.Value;
command.Parameters["endDat"].Value = dteEnd.Checked ? dteEnd.Value : iDB2Date.Null;
command.Parameters["woStat"].Value = txtStatus.Text;
command.Parameters["partsLbr"].Value = numCost.Value;
command.Parameters["lbrHours"].Value = numHours.Value;
command.Connection.Open();
if (command.ExecuteNonQuery() == 1)
{
labelResult.Visible = true;
labelResult.Text = "Row successfully inserted";
labelResult.ForeColor = Color.Green;
}
else
{
labelResult.Visible = true;
labelResult.Text = "Row insertion failed";
labelResult.ForeColor = Color.Red;
}
connection.Close();
}
catch (iDB2Exception ex)
{
MessageBox.Show(ex.MessageDetails);
}
}
/// <summary>
/// Execute a database query which does not include a select
/// </summary>
/// <param name="connString">Connection string to database</param>
/// <param name="cmdType">Command type either stored procedure or SQL</param>
/// <param name="cmdText">Acutall SQL Command</param>
/// <param name="commandParameters">Parameters to bind to the command</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters)
{
// Create a new iDB2 command
iDB2Command cmd = new iDB2Command();
//Create a connection
using (iDB2Connection connection = new iDB2Connection(connectionString))
{
//Prepare the command
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
//Execute the command
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
}
public string ejecutarInsertUpdateDelete(string consulta)
{
try
{
int resul = 0;
AS400ConnectionString.Open();
iDB2Command command = AS400ConnectionString.CreateCommand();
command = new iDB2Command(consulta, AS400ConnectionString);
resul = command.ExecuteNonQuery();
return(resul.ToString());
}
catch (Exception ex)
{
throw new Exception("Se produjo un problema al realizar un insert en AS400: ", ex);
}
finally { AS400ConnectionString.Close(); }
}
/// <summary>
/// Executes the command object using executenonquery method and
/// returns a dataset with a table with single column and row ROWS_AFFECTED
/// </summary>
/// <param name="commandToExecute">The command to execute.</param>
/// <returns>A dataset with a table with single column and row ROWS_AFFECTED</returns>
private DataSet ExecuteNonQuery(iDB2Command commandToExecute)
{
DataSet outputDataSet = null;
int rowsAffected = commandToExecute.ExecuteNonQuery();
DataTable tempDataTable = new DataTable();
tempDataTable.Columns.Add("ROWS_AFFECTED", typeof(string));
DataRow tempDataRow = null;
tempDataRow = tempDataTable.NewRow();
tempDataRow["ROWS_AFFECTED"] = rowsAffected;
tempDataTable.Rows.Add(tempDataRow);
outputDataSet = new DataSet();
outputDataSet.Tables.Add(tempDataTable);
tempDataTable = null;
return(outputDataSet);
}
// DELETE api/values/5
public void Delete(int id)
{
string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString;
iDB2Connection connection = new iDB2Connection(connString);
connection.Open();
iDB2Command db2Command = new iDB2Command();
string query = "DELETE FROM DAVIDEC1.CONTACTOS " +
"WHERE ID = @id";
db2Command.Connection = connection;
db2Command.CommandText = query;
db2Command.Parameters.Add(new iDB2Parameter("@id", id));
db2Command.ExecuteNonQuery();
connection.Close();
}
private void btn_cancella_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
Program._Stato = "M";
var riga = dataGridView1.SelectedRows[0].Index;
var _risposta = MessageBox.Show("Conferma Cancellazione Ordine n." +
dataGridView1[1, riga].Value.ToString() + Environment.NewLine +
" Del " + dataGridView1[2, riga].Value.ToString() + Environment.NewLine +
" Di " + dataGridView1[3, riga].Value.ToString(), "Cancellazione Ordine", MessageBoxButtons.YesNo);
if (_risposta == DialogResult.Yes)
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "delete from " +
" $emiedati.oda200f " +
" where nord = " + dataGridView1[1, riga].Value.ToString();
try
{
myCommand.ExecuteNonQuery();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
Ricarica();
}
}
else
{
MessageBox.Show("Selezionare l'ordine da cancellare");
}
}
public void ActualizarINTF(int NUMID)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
try
{
iDB2Command cmd = new iDB2Command("UPDATE RZINTF SET ESTINT = 'C' WHERE NUMID = @NUMID", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NUMID", SqlDbType.VarChar).Value = NUMID;
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
}
// POST api/values
public void Post([FromBody] Contacto oContacto)
{
string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString;
iDB2Connection connection = new iDB2Connection(connString);
connection.Open();
iDB2Command db2Command = new iDB2Command();
string query = "INSERT INTO DAVIDEC1.CONTACTOS " +
"(NOMBRE, TELEFONO, CORREO, EDAD) " +
"VALUES (@nombre, @telefono, @correo, @edad)";
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.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// Executes a statement on the IBM i that doesn't return data (INSERT, UPDATE, DELETE)
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
public void ExecuteNonQuery(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
cmd.ExecuteNonQuery();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
}
//Mark the Status to Done "D" on FSTINV
private void SetInvoiceToDone(iDB2Command db2Command, Invoice inv)
{
st.insertLog("Set " + inv.SIID + " invoice to done", "info", inv.SIID.ToString(), LogId);
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos))
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("UPDATE FSTINV SET SISTAT = @status WHERE SIID = {0}", inv.SIID);
db2Command.DeriveParameters();
db2Command.Parameters["@status"].Value = 'D';
// st.insertLog(db2Command.CommandText, "FSTINV Update Query", "NA", LogId);
var recordsAffected = db2Command.ExecuteNonQuery();
db2Command.Transaction.Commit();
db2Command.Transaction.Dispose();
st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
}
}
private bool ExecuteQuery(string strQuery)
{
try
{
lstResponse.Add(SetResponse(strQuery, string.Empty, ResponseStatusType.LOG));
iDB2Command sqlCMD = new iDB2Command(strQuery, objSQLCon);
if (objSQLCon.State == ConnectionState.Closed)
{
objSQLCon.Open();
}
sqlCMD.ExecuteNonQuery();
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
return(true);
}
catch (iDB2Exception ex)
{
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL));
return(false);
}
catch (Exception ex)
{
if (objSQLCon.State == ConnectionState.Open)
{
objSQLCon.Close();
}
lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL));
return(false);
}
}
public bool WriteData(string strSQL, CommandType intCmdType)
{
iDB2Connection objCn;
try
{
objCn = new iDB2Connection(strConn);
objCn.Open();
objCommand = new iDB2Command(strSQL, objCn);
objCommand.CommandType = intCmdType;
objCommand.CommandText = strSQL;
intNumRecords = objCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
objException = ex;
return(false);
}
finally
{
}
return(true);
}
private void button1_Click(object sender, EventArgs e)
{
// svuota file di passaggio
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "delete from ODA202P ";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT ACRAG1, ACRAG2, ACVIA, ACCAP, ACCITT, ACPROV " +
"FROM $d_emil.acf00f " +
" WHERE ACTPCD = 'F' and ACSCON = '" + _cf + "'";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
string _rg1 = "";
string _rg2 = "";
string _via = "";
string _cap = "";
string _cit = "";
string _prov = "";
foreach (DataRow r in dt.Rows)
{
_rg1 = r[0].ToString().Replace("'", "''");
_rg2 = r[1].ToString().Replace("'", "''");
_via = r[2].ToString().Replace("'", "''");
_cap = r[3].ToString().Substring(0, 5).Replace("'", "''");
_cit = r[4].ToString().Replace("'", "''");
_prov = r[5].ToString().Substring(0, 2).Replace("'", "''");
}
for (int i = 0; i < DGV_dettaglio.Rows.Count; i++)
{
if (DGV_dettaglio.Rows[i].Cells[3].Value != null)
{
myCommand.CommandText = "insert into ODA202P " +
"(NORD,PROG, DORD, DTIP, CFOR, ACRAG1," +
"ACRAG2, ACVIA, ACCAP, ACCIT, ACPROV, DRIC,CART," +
"ARDSAR, ARUNMI, QORD) " +
" values('" + _n + "','" +
DGV_dettaglio.Rows[i].Cells[7].Value.ToString() + "','" +
_do.Replace("/", "") + "','F'," + "'" + _cf + "','" + _f +
"','" + _rg2 + "','" + _via + "','" + _cap + "','" + _cit + "','" + _prov + "','" +
DGV_dettaglio.Rows[i].Cells[6].Value.ToString().Replace("/", "") + "'," +
"'" + DGV_dettaglio.Rows[i].Cells[0].Value.ToString() + "'," +
"'" + DGV_dettaglio.Rows[i].Cells[1].Value.ToString() + "'," +
"'" + DGV_dettaglio.Rows[i].Cells[2].Value.ToString() + "','" +
DGV_dettaglio.Rows[i].Cells[3].Value.ToString().Replace(",", ".") + "')";
myCommand.ExecuteNonQuery();
//MessageBox.Show(dataGridView1.Rows[i].Cells[3].Value.ToString());
}
}
Frm_ModuloOdf Podf = new Frm_ModuloOdf();
Podf.WindowState = FormWindowState.Maximized;
Podf.ShowDialog();
}
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 static void UpdateFoundNotPart(string guid)
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetPartNotFoundUpdateMasterString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.UpdateCommand = cmd;
objDataAdapter.UpdateCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char);
cmd.Parameters["@retrn"].Value = 'R';
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
private void Btn_inserisci_Click(object sender, EventArgs e)
{
Stato _stato = new Stato();
if (Program._Stato == _stato.Inserisci)
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "insert into " +
" $emiedati.oda200f " +
" (NORD,PROG,DORD,DTIP,CFOR,DRIC,CART,QORD,QRIC,QSTA) " +
" values(" +
_n + "," +
(PrimoNumeroLibero() + 10) + "," +
Convert.ToDateTime(_do).ToString("yyyyMMdd") + "," +
"'F','" +
_cf + "'," +
DTPRichiesta.Value.ToString("yyyyMMdd") + ",'" +
CMB_Articolo.SelectedValue + "'," +
Convert.ToDecimal(txt_qta.Text) + "," +
0 + ",'" +
_stato.Inserisci + "'" +
") ";
try
{
myCommand.ExecuteNonQuery();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
DBCONN.Close();
Ricarica();
}
if (Program._Stato == _stato.Modifica)
{
if (txt_riga.Text != _r)
{
if (ControllaDoppi())
{
MessageBox.Show("Progressivo esiste già modificare progressivo");
return;
}
}
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
if (chkDel.Checked)
{
if (MessageBox.Show("Confermi la cancellazione della riga?", "Cancella Riga", MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
else
{
myCommand.CommandText = "delete from " +
" $emiedati.oda200f " +
" where nord = " + _n +
" and prog = " + _r;
}
}
else
{
myCommand.CommandText = "update " +
" $emiedati.oda200f " +
" set " +
" cart = '" + CMB_Articolo.SelectedValue + "'," +
" qord = " + txt_qta.Text.Replace(",", ".") + "," +
" dric = " + DTPRichiesta.Value.ToString("yyyyMMdd") + "," +
" qric = " + TxtQtaEvasione.Text.Replace(",", ".") + "," +
" qsta = '" + CMB_tipoEvasione.SelectedValue + "'," +
" prog = " + Convert.ToDecimal(txt_riga.Text) +
" where nord = " + _n +
" and prog = " + _r;
}
try
{
myCommand.ExecuteNonQuery();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
return;
}
DBCONN.Close();
Ricarica();
StatoModifica();
//Btn_inserisci.Enabled = false;
//label1.Visible = false;
//label2.Visible = false;
//label3.Visible = false;
//DTPRichiesta.Visible = false;
//CMB_Articolo.Visible = false;
//txt_qta.Visible = false;
}
}
//This function inserts the invoice into the FSTINV Table
private void InsertInvoiceToJDE(iDB2Command db2Command, FSTINV inv)
{
st.insertLog(string.Format("Inserting {0} to FSTINV table.", inv.Name), "Info", inv.SIID.ToString(), LogId);
string tableName = inv.GetType().Name;
if (inv.Items.Length > 0)
{
for (int x = 0; x < inv.Items.Length; x++)
{
//10.12.16 JC: Check whether this is a recurring service (parameter send invoice and a invoice item)
// inv.Items[x].IsRecurringService = serviceTrade.IsRecurringService(inv, inv.Items[x]);
// st.insertLog(string.Format("New vs Recurring Check result: {0}, {1}, {2}", inv.SIID, inv.Items[x].LibItem.Name, inv.Items[x].IsRecurringService), "Info", "NA", LogId);
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SILAN8, SISSTS, SIINV, SITECH, SILOC, SITYPE, SITOTL, SIPO, SIITEM, SIITYP, SIDESC, SIQTY, SIPRIC, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG, SIUSER1) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22) WITH NONE",
tableName);
//Recurring service
// db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", inv.Items[x].IsRecurringService ? "Yes" : "No"));
db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", "NA"));
//Flag to Determine Service Type
db2Command.CommandText = db2Command.CommandText.Replace("@p21", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : ""));
//Salesman 3
db2Command.CommandText = db2Command.CommandText.Replace("@p20", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null));
//Salesman 2
db2Command.CommandText = db2Command.CommandText.Replace("@p19", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null));
//Salesman 1
db2Command.CommandText = db2Command.CommandText.Replace("@p18", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null));
//Service Trade Job ID
db2Command.CommandText = db2Command.CommandText.Replace("@p17", string.Format("'{0}'", inv.Job.JobId));
//Created Date
db2Command.CommandText = db2Command.CommandText.Replace("@p16", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));
//Item Price
db2Command.CommandText = db2Command.CommandText.Replace("@p15", string.Format("'{0}'", inv.Items[x].Price));
//Item Quanity
db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", Math.Round(inv.Items[x].Quantity, 2))); //6.23.16 JC - Hiller wants to limit the item qty to two decimal places.
//Service Trade Item Description
db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", inv.Items[x].Description.Length <= 50 ? (inv.Items[x].Description).Replace("'", "''") : (inv.Items[x].Description.Substring(0, 50)).Replace("'", "''")));
//Service Trade Item Type
db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", inv.Items[x].LibItem.Type));
//ITEM ID
db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", inv.Items[x].LibItem.Code));
//Customer PO
db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty)));
//Service Trade ID
db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
//JDE Address Book number for the Company
db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.External.JdeId));
//JDE Address Book number for the Location
db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Location.External.JdeId));
//Service Trade invoice Status
db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.Status));
//Service Trade Invoice number
db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", inv.InvoiceNumber));
//Service Trade Technician
db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null));
//Service Trade Location (e.g. FT. Walton 123, Pensacola 122)
db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null));
//Service Trade Invoice Type
db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", inv.Type));
//Inovice Total Amount
db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", inv.TotalPrice));
// st.insertLog(db2Command.CommandText, "FSTINV: Insert Query multiple items", "NA", LogId);
var recordsAffected = db2Command.ExecuteNonQuery();
db2Command.Transaction.Commit();
st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
}
}
}
else
{
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SISSTS, SIINV, SITECH, SILOC, SITOTL, SIPO, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14) WITH NONE",
tableName);
db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : ""));
db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null));
db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null));
db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null));
db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", inv.Job.JobId));
db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID));
db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.CompanyId));
db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Status));
db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.InvoiceNumber));
db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null));
db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null));
db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.TotalPrice));
db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty)));
db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat()));
// st.insertLog(db2Command.CommandText, "FSTINV: Insert Query no items", "NA", LogId);
var recordsAffected = db2Command.ExecuteNonQuery();
db2Command.Transaction.Commit();
st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId);
}
}
//Onces inserted, change the status to processed for updating ServiceTrade
inv.Status = "processed";
}
private static void UpdateFoundPart(string guid, GetMasterResult getResult)
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetPartFoundUpdateMasterString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.UpdateCommand = cmd;
objDataAdapter.UpdateCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@usrid", iDB2DbType.iDB2Char);
cmd.Parameters["@usrid"].Value = (getResult.UserID.Trim().Length > 0) ? getResult.UserID : " ";
cmd.Parameters.Add("@branded", iDB2DbType.iDB2Char);
cmd.Parameters["@branded"].Value = getResult.Branded;
cmd.Parameters.Add("@comcode", iDB2DbType.iDB2Char);
cmd.Parameters["@comcode"].Value = (getResult.Commodity_Code.Trim().Length > 0)
? getResult.Commodity_Code
: " ";
cmd.Parameters.Add("@level", iDB2DbType.iDB2Integer);
cmd.Parameters["@level"].Value = getResult.Level;
cmd.Parameters.Add("@status", iDB2DbType.iDB2Char);
cmd.Parameters["@status"].Value = (getResult.Status.Trim().Length > 0) ? getResult.Status : " ";
cmd.Parameters.Add("@rtnble", iDB2DbType.iDB2Char);
cmd.Parameters["@rtnble"].Value = getResult.Returnable;
cmd.Parameters.Add("@tariffcd", iDB2DbType.iDB2Char);
cmd.Parameters["@tariffcd"].Value = (getResult.Tariff_Code.Trim().Length > 0)
? getResult.Tariff_Code
: " ";
cmd.Parameters.Add("@amsc", iDB2DbType.iDB2Char);
cmd.Parameters["@amsc"].Value = (getResult.AMSC.Trim().Length > 0) ? getResult.AMSC : " ";
cmd.Parameters.Add("@tqty", iDB2DbType.iDB2Integer);
cmd.Parameters["@tqty"].Value = getResult.Technical_Qty;
cmd.Parameters.Add("@svclife", iDB2DbType.iDB2Integer);
cmd.Parameters["@svclife"].Value = getResult.Service_Life;
cmd.Parameters.Add("@pkgcode", iDB2DbType.iDB2Char);
cmd.Parameters["@pkgcode"].Value = (getResult.Package_Code.Trim().Length > 0)
? getResult.Package_Code
: " ";
cmd.Parameters.Add("@info", iDB2DbType.iDB2Char);
cmd.Parameters["@info"].Value = (getResult.Information.Trim().Length > 0)
? getResult.Information
: " ";
cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char);
cmd.Parameters["@retrn"].Value = 'R';
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
private void MigrateUnprocessedLocations(iDB2Command db2Command)
{
Dictionary <string, Location> jdeLocList = new Dictionary <string, Location>();
// log.Debug("Migrating Location");
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT * FROM FSTLOCN WHERE SLSTAT != '{0}'", "P");
iDB2DataReader reader = db2Command.ExecuteReader();
while (reader.Read())
{
Location temp = new Location();
temp.slan8 = ((string)reader["SLAN8"]).Trim();
temp.slpan8 = ((string)reader["SLPAN8"]).Trim();
temp.name = ((string)reader["SLNAME"]).Trim();
temp.addressStreet = ((string)reader["SLADDR"]).Trim();
temp.addressCity = ((string)reader["SLCITY"]).Trim();
temp.addressState = ((string)reader["SLST"]).Trim();
temp.addressPostalCode = ((string)reader["SLZIP"]).Trim();
temp.phoneNumber = ((string)reader["SLPHON"]).Trim();
temp.type = Char.Parse(((string)reader["SLACTN"]).Trim());
temp.officeIds = new int[] { Int32.Parse(((string)reader["SLMCU"]).Trim()) };
jdeLocList.Add(temp.slan8, temp);
}
reader.Close();
db2Command.Transaction.Dispose();
}
if (jdeLocList.Count > 0)
{
// log.Info(string.Format("Found {0} locations for update", jdeLocList.Count));
foreach (var jde in jdeLocList)
{
// log.Debug("Migrating " + jde.Value.name);
bool success = jde.Value.type == 'A' ? serviceTrade.AddLocation(jde.Value) : serviceTrade.UpdateLocation(jde.Value);
if (success)
{
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos))
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("UPDATE FSTLOCN SET SLSTAT = @slstat WHERE SLAN8 = @key", jde.Key);
db2Command.DeriveParameters();
db2Command.Parameters["@slstat"].Value = 'P';
db2Command.Parameters["@key"].Value = jde.Key;
try
{
db2Command.ExecuteNonQuery();
}
catch (Exception ex)
{
// log.Error("Location update unsuccessful ", ex);
}
db2Command.Transaction.Dispose();
}
}
}
}
else
{
// log.Info("No location found for processing.");
}
}
private void MigrateUnprocessedCompaniesFromJDE(iDB2Command db2Command)
{
Dictionary <string, Company> jdeCompList = new Dictionary <string, Company>();
st.insertLog("Migrating Companies", "Info", "NA", LogId);
iDB2DataReader reader;
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT * FROM FSTCOMP WHERE SCSTAT != '{0}'", "P");
reader = db2Command.ExecuteReader();
while (reader.Read())
{
Company temp = new Company();
temp.scan8 = (string)reader["SCAN8"];
temp.type = Char.Parse((string)reader["SCACTN"]);
//Company Name
temp.name = ((string)reader["SCNAME"]).Trim();
temp.addressStreet = ((string)reader["SCADDR"]).Trim();
temp.addressCity = ((string)reader["SCCITY"]).Trim();
temp.addressState = ((string)reader["SCST"]).Trim();
temp.addressPostalCode = ((string)reader["SCZIP"]).Trim();
temp.customer = Int32.Parse((string)reader["SCCUST"]) == 1 ? true : false;
temp.vendor = Int32.Parse((string)reader["SCVEND"]) == 1 ? true : false;
temp.phoneNumber = ((string)reader["SCPHON"]).Trim();
temp.status = ((string)reader["SCSSTS"]).Trim();
jdeCompList.Add((string)reader["SCAN8"], temp);
}
reader.Close();
db2Command.Transaction.Dispose();
}
if (jdeCompList.Count > 0)
{
st.insertLog(string.Format("Found {0} companies for update", jdeCompList.Count), "Info", "NA", HillerServiceDataMigrator.LogId);
foreach (var jde in jdeCompList)
{
st.insertLog("Migrating " + jde.Value.name, "Info", "NA", HillerServiceDataMigrator.LogId);
bool success = jde.Value.type == 'A' ? serviceTrade.AddCompany(jde.Value) : serviceTrade.UpdateCompany(jde.Value);
if (success)
{
// log.Debug("Updating Migrated Companies.");
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos))
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = "UPDATE FSTCOMP SET SCSTAT = @scstat WHERE SCAN8 = @key";
db2Command.DeriveParameters();
db2Command.Parameters["@scstat"].Value = 'P';
db2Command.Parameters["@key"].Value = jde.Key;
// log.Debug(db2Command.CommandText);
try
{
db2Command.ExecuteNonQuery();
}
catch (Exception ex)
{
// log.Error("Company update unsuccessful ", ex);
}
db2Command.Transaction.Dispose();
}
}
}
}
else
{
// log.Info("No company found for processing.");
}
}
private static void InsertCrossParts(string guid, GetMasterResult getResult)
{
foreach (CrossPart s in getResult.CrossPartList)
{
using (
var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
string query = GetCrossPartInsertString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(query, conn);
cmd.Connection.Open();
objDataAdapter.InsertCommand = cmd;
objDataAdapter.InsertCommand.CommandTimeout = 0;
cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char);
cmd.Parameters["@guid"].Value = guid;
cmd.Parameters.Add("@item", iDB2DbType.iDB2Char);
cmd.Parameters["@item"].Value = s.PartNumber;
cmd.Parameters.Add("@brand", iDB2DbType.iDB2Char);
cmd.Parameters["@brand"].Value = s.Brand;
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
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();
}
/// <summary>
/// Executes a stored procedure on the IBM i that doesn't return data.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
public void ExecuteStoredProcedure(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) { parameters(cmd.Parameters); }
try
{
cmd.ExecuteNonQuery();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
}
