/// <summary>
/// Begins the transaction and returns the transaction object with open state connection.
/// Make sure to call EndTransaction method to close transaction
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="err">The err.</param>
/// <param name="givenIsolationLevel">The given isolation level.</param>
/// <returns></returns>
public iDB2Transaction BeginTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, IsolationLevel?givenIsolationLevel = null)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
iDB2Transaction db2Trans = null;
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
if (givenIsolationLevel == null)
{
db2Trans = DBDataAccessEntity.BeginTransaction(destinationDatabase, ref err);
}
else
{
db2Trans = DBDataAccessEntity.BeginTransaction(destinationDatabase, ref err);
}
if (err.HasError)
{
db2Trans = null;
}
//'End If
return(db2Trans);
}
/// <summary>
/// Use this for Insert, Update and Delete
/// Functionality to provide the gateway to access the Data Access Layer with Transaction object
/// If any exception transaction will be rollbacked here
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="givenTransaction">The given transaction.</param>
/// <returns></returns>
public ErrorObj DB2Access(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
err = DBDataAccessEntity.AccessWithTransaction(givenTransaction);
if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null))
{
ResultDataSet = DBDataAccessEntity.ResultDataSet;
}
else
{
givenTransaction.Rollback();
//before call this get the previous error details
string errMessage = err.ErrorMessage;
err = EndTransaction(destinationDatabase, givenTransaction);
if (err.HasError)
{
errMessage = errMessage + ";" + err.ErrorMessage;
}
//whether end transaction gives error or not
//always assign err object has error
err.HasError = true;
err.ErrorMessage = errMessage;
}
return(err);
}
protected int DB2Access(TalentDB2Access talentDB2AccessDetail, iDB2Transaction givenTransaction)
{
int affectedRows = 0;
if (settings.EnableDB2AccessQueue)
{
settings.DB2AccessQueue.Add(talentDB2AccessDetail);
}
else
{
ErrorObj err = new ErrorObj();
if (givenTransaction == null)
{
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
}
else
{
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT, givenTransaction);
}
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentDB2AccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
}
return(affectedRows);
}
public void ComenzarTransaccion()
{
if (_Transaccion == null)
{
_Transaccion = _Conexion.BeginTransaction();
}
}
public void CompleteTransaction()
{
useTransaction = false;
_transaction.Commit();
_transaction.Dispose();
_transaction = null;
}
public void AbortTransaction()
{
useTransaction = false;
_transaction.Rollback();
_transaction.Dispose();
_transaction = null;
}
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;
}
}
}
/// <summary>
/// Deletes payment type information from MD501
/// </summary>
/// <param name="type51"></param>
/// <param name="code51"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
public int DeleteData(string type51, string code51, iDB2Transaction givenTransaction = null)
{
int affectedRows = 0;
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
ErrorObj err = new ErrorObj();
try
{
string commandText = "UPDATE MD501 SET ACTR51 = 'D',USER51=@USER51,PGMD51=@PGMD51,UPDT51=@UPDT51 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51";
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentDB2AccessDetail.CommandElements.CommandText = commandText;
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", type51, 4));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", code51, 15));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@USER51", settings.AgentName, 10));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@PGMD51", settings.StoredProcedure, 10));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDT51", settings.iSeriesTodaysDate, iDB2DbType.iDB2Decimal));
//Execute
affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction);
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
return(affectedRows);
}
//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 void Commit()
{
Transaction.Commit();
Connection.Dispose();
Connection.Close();
Transaction = null;
Connection = null;
}
public void Rollback()
{
transaction.Rollback();
connection.Dispose();
connection.Close();
transaction = null;
connection = null;
}
public void Commit()
{
transaction.Commit();
connection.Dispose();
connection.Close();
transaction = null;
connection = null;
}
public void Rollback()
{
this.Transaction.Rollback();
this.Connection.Dispose();
this.Connection.Close();
this.Transaction = null;
this.Connection = null;
}
/// <summary>
/// Execute an iDB2Command (that returns no resultset) against an existing database transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new iDB2Parameter(":prodid", 24));
/// </remarks>
/// <param name="trans">an existing database transaction</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(iDB2Transaction trans, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters)
{
iDB2Command cmd = new iDB2Command();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
/// <summary>
/// Ends the transaction and close the reader by passing the object to DBDataAccess
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="givenTransaction">The given transaction.</param>
/// <param name="readerToClose">The reader to close.</param>
/// <returns>Error Object</returns>
public ErrorObj EndTransaction(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction, iDB2DataReader readerToClose)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
DBDataAccessEntity.EndTransaction(destinationDatabase, ref err, givenTransaction, readerToClose);
return(err);
}
public static long GetSequenceTrans(iDB2Connection conn, string tableName)
{
//iDB2Transaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead);
iDB2Transaction trans = conn.BeginTransaction();
long id = 0;
try {
string sql = "SELECT VALUE FROM SEQ_TABLE WHERE KEY=@KEY";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@KEY", "USER")
};
Object obj = ExecuteScalar(trans, CommandType.Text, sql, parms);
if (obj != null)
{
id = Convert.ToInt32(obj);
string sqlUpdate = "UPDATE SEQ_TABLE SET VALUE=@NEW_ID WHERE VALUE=@OLD_ID AND KEY=@KEY";
iDB2Parameter[] parmsUpdate = new iDB2Parameter[] {
new iDB2Parameter("@NEW_ID", id + 1),
new iDB2Parameter("@OLD_ID", id),
new iDB2Parameter("@KEY", tableName)
};
ExecuteNonQuery(trans, CommandType.Text, sqlUpdate, parmsUpdate);
}
else
{
id = 1;
string sqlInsert = "INSERT INTO SEQ_TABLE(KEY,VALUE) VALUES(@KEY,@VALUE)";
iDB2Parameter[] parmsInsert = new iDB2Parameter[] {
new iDB2Parameter("@KEY", tableName),
new iDB2Parameter("@VALUE", id + 1)
};
ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parmsInsert);
}
trans.Commit();
return(id);
}
catch (Exception ex)
{
log.Error(ex.Message);
log.Error(ex.StackTrace);
trans.Rollback();
return(0);
}
}
//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);
}
/// <summary>
/// Execute the command obejct with transaction
/// </summary>
/// <param name="givenTransaction">The given transaction.</param>
/// <returns>Error Object</returns>
public ErrorObj AccessWithTransaction(iDB2Transaction givenTransaction)
{
ErrorObj err = new ErrorObj();
iDB2Command db2CommandEntity = new iDB2Command();
db2CommandEntity.Transaction = givenTransaction;
db2CommandEntity.Connection = givenTransaction.Connection;
err = PrepareCommandElement(db2CommandEntity);
if (!(err.HasError))
{
try
{
if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet)
{
this.ResultDataSet = ExecuteDataSet(db2CommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
this.ResultDataSet = ExecuteNonQuery(db2CommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader)
{
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar)
{
}
}
catch (iDB2Exception db2Ex)
{
err.HasError = true;
err.ErrorMessage = db2Ex.Message + "; Err Code:" + System.Convert.ToString(db2Ex.ErrorCode) + "; MessageDetails:" + db2Ex.MessageDetails + "; Source:" + db2Ex.Source;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, db2Ex.StackTrace.ToString(), err.ErrorMessage);
}
catch (Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
}
return(err);
}
/// <summary>
/// Opens the connection and create and returns the transaction object
/// </summary>
/// <param name="err">The error object as ref</param>
/// <param name="givenIsolationLevel">The given isolation level.</param>
/// <returns>iDB2Transaction instance</returns>
public iDB2Transaction BeginTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, IsolationLevel?givenIsolationLevel = null)
{
iDB2Transaction db2Trans = null;
err = ConnectionByDestinationDBOpen(destinationDatabase);
if (!err.HasError)
{
if (givenIsolationLevel == null)
{
db2Trans = conTALENTTKT.BeginTransaction();
}
else
{
db2Trans = conTALENTTKT.BeginTransaction((IsolationLevel)givenIsolationLevel);
}
}
else
{
db2Trans = null;
}
return(db2Trans);
}
//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);
}
}
/// <summary>
/// Execute a iDB2Command (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <param name="transaction">A valid SqlTransaction</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 object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(iDB2Transaction transaction, CommandType commandType, string commandText, params iDB2Parameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
// Create a command and prepare it for execution
iDB2Command cmd = new iDB2Command();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return(retval);
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private static void PrepareCommand(iDB2Command cmd, iDB2Connection conn, iDB2Transaction trans, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
{
cmd.Transaction = trans;
}
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (iDB2Parameter parm in commandParameters)
{
cmd.Parameters.Add(parm);
}
}
/*if (commandParameters != null)
* {
* cmd.DeriveParameters();
* //cmd.Parameters["@myKEY"].Value = "USER_NO";
* foreach (var parm in commandParameters)
* cmd.Parameters["@"+ parm.Key].Value = parm.Value;
* }*/
}
public void StartTransaction()
{
// Apre una transazione su cui eseguire comandi
// --------------------------------------------
try
{
objConnection = new iDB2Connection(strConn);
objConnection.Open();
// Creiamo un timestamp da usare negli aggiornamenti
// -------------------------------------------------
CreateTimestamp();
objTransaction = objConnection.BeginTransaction();
blnTrans = true;
objCommand = new iDB2Command();
objCommand.CommandTimeout = 180;
objCommand.Connection = objConnection;
objCommand.Transaction = objTransaction;
}
catch (Exception ex)
{
objException = ex;
}
}
//Update Invoice in service Trade to Done
private void SendInvoiceUpdates(iDB2Command db2Command)
{
st.insertLog("Sending invoice update to Service Trade.", "info", "NA", LogId);
Dictionary <string, Invoice> jdeInvList = new Dictionary <string, Invoice>();
using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction())
{
db2Command.Transaction = db2Transaction;
db2Command.CommandText = string.Format("SELECT DISTINCT SIID, SIAN8, SIJOB, SIDOCO, SIDCTO, SICRDT FROM FSTINV WHERE SISTAT = 'P'");
iDB2DataReader reader = db2Command.ExecuteReader();
while (reader.Read())
{
Invoice temp = new Invoice();
temp.SIID = ((string)reader["SIID"]).Trim();
temp.type = "invoice";
//always set this to processed status to avoid breaking the status in ST
temp.status = "processed";
temp.jobId = Int32.Parse((string)reader["SIJOB"]);
//Change Invoice Number (Concat JDE Document Number and Document Type with -)
temp.invoiceNumber = string.Format("{0}-{1}", reader["SIDOCO"], reader["SIDCTO"]);
temp.transactionDate = (Int32)((DateTime)reader["SICRDT"] - unixBaseTime).TotalSeconds;
//temp.assignedUserId= Int32.Parse((string)reader["SITECH"]);
//09/18/2015 - This limits the invoices being sent for update in ST.
//if (!jdeInvList.ContainsKey((string)reader["SIAN8"]))
//{
// jdeInvList.Add((string)reader["SIAN8"], temp);
// sent++;
//}
if (reader["SIAN8"] != null)
{
//12/19/16 JC: This will avoid duplicate records.
if (!jdeInvList.ContainsKey(temp.SIID))
{
jdeInvList.Add((string)temp.SIID, temp);
}
}
}
reader.Close();
}
db2Command.Transaction.Dispose();
if (jdeInvList.Count > 0)
{
st.insertLog(string.Format("Found {0} invoices for update", jdeInvList.Count), "Info", "NA", LogId);
foreach (var jde in jdeInvList)
{
//var status = jde.Value.status;
//Query the single invoice from the service Trade
var invoice = serviceTrade.GetInvoice(jde.Key);
//Mark the invoice as done in FSTINV, if the status of the invoice is void in Service Trade
if (invoice.Status == "void" || invoice.Status != "pending_accounting")
{
st.insertLog(string.Format("The status of invoice number {0} is not <pending_accounting> in service Trade.", jde.Key), "Error", jde.Key.ToString(), LogId);
SetInvoiceToDone(db2Command, jde.Value);
}
else
{
if (serviceTrade.UpdateInvoice(jde.Value))
{
st.insertLog(string.Format("The status of invoice number {0} is set to processed in Service Trade.", jde.Key), "Info", jde.Key.ToString(), LogId);
SetInvoiceToDone(db2Command, jde.Value);
}
}
}
}
else
{
st.insertLog("No invoice update to be sent.", "Info", "NA", LogId);
}
}
//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 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.");
}
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="variableKey1Value"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int UpdateData(string businessUnit, string variableKey1Value, System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction)
{
int affectedRows = 0;
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
ErrorObj err = new ErrorObj();
try
{
string[] setValue = new string[configs.Count - 1 + 1];
for (int i = 0; i <= configs.Count - 1; i++)
{
setValue[i] = configs[i].DefaultName + " = @UPDATED_VALUE" + i.ToString();
}
string values = string.Join(",", setValue);
string commandText = string.Format("Update CD00A SET {0} WHERE CONO0A = @CompanyCode", values);
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentDB2AccessDetail.CommandElements.CommandText = commandText;
talentDB2AccessDetail.CommandElements.CommandParameter.Clear();
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
for (int i = 0; i <= configs.Count - 1; i++)
{
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDATED_VALUE" + i.ToString(), System.Convert.ToString(configs[i].UpdatedValue)));
}
//Execute
affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction);
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
return(affectedRows);
}
public override int UpdateCurrentValueDB2(System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction = null)
{
return(UpdateData(businessUnit, variableKey1Value, configs, givenTransaction));
}
public Session(string connectionString)
{
this.Connection = new iDB2Connection(connectionString);
this.Connection.Open();
this.Transaction = this.Connection.BeginTransaction();
}
