public override List <ConfigurationEntity> RetrieveAlliSeriesValues(string companyCode, string[] defaultKeys, string[] variableKeys, string selectedColumns = "")
{
DataTable outputDataTable = new DataTable();
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
ErrorObj err = new ErrorObj();
string columns = (selectedColumns != string.Empty ? selectedColumns : "*");
try
{
string commandText = string.Format("SELECT {0} FROM CD00A WHERE CONO0A = @CompanyCode", selectedColumns);
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentDB2AccessDetail.CommandElements.CommandText = commandText;
talentDB2AccessDetail.CommandElements.CommandParameter.Clear();
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
//Execute
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
return(GetConfigurationData(outputDataTable, defaultKeys, variableKeys));
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="configs"></param>
/// <returns></returns>
/// <remarks></remarks>
private Dictionary <string, string> RetrieveCurrentValuesData(System.Collections.Generic.List <ConfigurationItem> configs)
{
DataTable outputDataTable = new DataTable();
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
ErrorObj err = new ErrorObj();
try
{
string commandText = "SELECT * FROM CD00A WHERE CONO0A = @CompanyCode";
//Construct The Call
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentDB2AccessDetail.CommandElements.CommandText = commandText;
talentDB2AccessDetail.CommandElements.CommandParameter.Clear();
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
}
finally
{
talentDB2AccessDetail = null;
}
return(GetCurrentValues(outputDataTable, configs));
}
/// <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);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="companyCode"></param>
/// <param name="type51"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable SelectByCompAndType(string companyCode, string type51)
{
//how to get company code, type code and active flag
string sqlStatement = string.Empty;
sqlStatement = " SELECT CODE51, DESC51, VALU51 FROM MD501 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type AND ACTR51 = 'A' ";
DataTable outputDataTable = new DataTable();
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
try
{
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentDB2AccessDetail.CommandElements.CommandParameter.Clear();
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type", type51, 4));
ErrorObj err = new ErrorObj();
talentDB2AccessDetail.CommandElements.CommandText = sqlStatement;
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
return(outputDataTable);
}
/// <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);
var commandText = string.Format("UPDATE MD501 SET {0} WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51", 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));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", configs[0].DefaultKey1, 4));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", variableKey1Value, 15));
for (int i = 0; i <= configs.Count - 1; i++)
{
if (configs[i].DefaultName == "UPDT51" || configs[i].DefaultName == "VALU51")
{
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDATED_VALUE" + i.ToString(), System.Convert.ToString(configs[i].UpdatedValue), iDB2DbType.iDB2Decimal));
}
else
{
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);
}
/// <summary>
///
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="variableKey1Value"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
private int InsertData(System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction)
{
int affectedRows = 0;
TalentDB2Access talentDB2AccessDetail;
ErrorObj err = new ErrorObj();
string commandText = string.Empty;
StringBuilder columnNames, columnValues;
try
{
talentDB2AccessDetail = new TalentDB2Access();
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
commandText = "INSERT INTO MD501({0}) VALUES({1})";
columnNames = new StringBuilder();
columnValues = new StringBuilder();
for (int i = 0; i <= configs.Count - 1; i++)
{
columnNames.Append(configs[i].DefaultName + ",");
columnValues.Append("@" + configs[i].DefaultName + ",");
if (configs[i].DefaultName == "UPDT51" || configs[i].DefaultName == "VALU51")
{
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@" + configs[i].DefaultName, configs[i].UpdatedValue, iDB2DbType.iDB2Decimal));
}
else
{
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@" + configs[i].DefaultName, configs[i].UpdatedValue));
}
}
commandText = string.Format(commandText, columnNames.ToString().Substring(0, columnNames.ToString().Length - 1), columnValues.ToString().Substring(0, columnValues.ToString().Length - 1));
talentDB2AccessDetail.CommandElements.CommandText = commandText;
//Execute
affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction);
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
return(affectedRows);
}
public bool DoesDescriptionItemExist(string type, string code)
{
int affectedRows = 0;
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
ErrorObj err = new ErrorObj();
string commandText = "SELECT 1 FROM MD501 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51 AND ACTR51 = 'A'";
talentDB2AccessDetail.Settings = settings;
talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentDB2AccessDetail.CommandElements.CommandText = commandText;
talentDB2AccessDetail.CommandElements.CommandParameter.Clear();
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", type, 4));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", code, 15, iDB2DbType.iDB2Char));
talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3));
//Execute
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
affectedRows = talentDB2AccessDetail.ResultDataSet.Tables[0].Rows.Count;
}
talentDB2AccessDetail = null;
return(affectedRows > 0 ? false : true);
}
