/// <summary>
/// Returns web API url from database
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public string GetSystemDefaultsURL()
{
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string commandText = "SELECT VALUE FROM DBO.TBL_ECOMMERCE_MODULE_DEFAULTS_BU WHERE BUSINESS_UNIT = @BUSINESS_UNIT AND DEFAULT_NAME = \'SYSTEM_DEFAULTS_URL\'";
ErrorObj err = new ErrorObj();
string retVal = string.Empty;
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
System.Data.DataTable with_3 = talentSqlAccessDetail.ResultDataSet.Tables[0];
if (with_3.Rows.Count > 0)
{
retVal = with_3.Rows[0]["VALUE"].ToString();
}
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(retVal);
}
/// <summary>
/// Retrieve BusinessUnits
/// </summary>
/// <param name="application"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable RetrieveBusinessUnit(string application)
{
DataTable outputDataTable = new DataTable();
string commandText = "SELECT distinct BUSINESS_UNIT FROM tbl_url_bu WHERE APPLICATION=@Application";
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@Application", application));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(outputDataTable);
}
/// <summary>
/// Returns agent details by session ID
/// </summary>
/// <param name="SessionID"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable RetrieveAgentDetailsFromSessionID(string sessionID)
{
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
var commandText = string.Format("select SESSIONID, AGENT_NAME, COMPANY from dbo.tbl_agent a where a.SESSIONID = \'{0}\' and not exists(select 1 from dbo.tbl_agent x where x.AGENT_NAME = a.AGENT_NAME and x.TIMESTAMP_UPDATED > a.TIMESTAMP_UPDATED AND x.SESSIONID <> a.SESSIONID) ", sessionID);
DataTable agentDetails = new DataTable();
try
{
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
agentDetails = talentSqlAccessDetail.ResultDataSet.Tables[0];
if (agentDetails.Rows.Count > 0)
{
settings.AgentName = System.Convert.ToString(agentDetails.Rows[0]["AGENT_NAME"]);
settings.Company = System.Convert.ToString(agentDetails.Rows[0]["COMPANY"]);
}
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(agentDetails);
}
public override DataTable RetrieveDTForList(System.Collections.Generic.List <DataColumn> dataColumns, System.Collections.Generic.List <KeyColumn> variableKeyColumns)
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
var columns = (from c in dataColumns select c.Name).Union(from c in variableKeyColumns select c.Name);
string commandText = string.Format("SELECT {0} FROM tbl_payment_type", string.Join(", ", columns.ToArray()));
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(outputDataTable);
}
/// <summary>
/// Deletes Configurations
/// </summary>
/// <param name="paymentCode"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int DeleteData(string paymentCode, SqlTransaction givenTransaction)
{
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
string commandText = "DELETE FROM tbl_payment_type WHERE payment_type_code = @PAYMENT_TYPE_CODE";
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PAYMENT_TYPE_CODE", paymentCode));
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
return(affectedRows);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int IncrementUniqueId(SqlTransaction givenTransaction)
{
string commandText = "UPDATE [tbl_defaults] SET UniqueId = UniqueId + 1";
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
//Return results
return(affectedRows);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="masterConfig"></param>
/// <param name="variableKey1"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable RetrieveConfigurationItem(string masterConfig, string variableKey1)
{
string commandText = "SELECT * FROM tbl_config_detail WHERE master_config = @MASTER_CONFIG AND variable_key1 = @VARIABLE_KEY1";
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@MASTER_CONFIG", masterConfig));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY1", variableKey1));
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(outputDataTable);
}
public override List<ConfigurationEntity> RetrieveAllValues(string businessUnit, string[] defaultKeys, string[] variableKeys, string displayName = "")
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string commandText = string.Format("SELECT * FROM tbl_control_text_lang WHERE (business_unit = @BUSINESS_UNIT) AND control_code = @CONTROL_CODE" + (displayName != string.Empty ? " AND TEXT_CODE like \'%\'+ @TEXT_CODE + \'%\'" : "") + " ORDER BY ID");
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@CONTROL_CODE", defaultKeys[0]));
if (displayName != string.Empty)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@TEXT_CODE", displayName));
}
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return GetConfigurationData(outputDataTable, defaultKeys, variableKeys);
}
public override List <ConfigurationEntity> RetrieveAllValues(string businessUnit, string[] defaultKeys, string[] variableKeys, string displayName = "")
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string commandText = "SELECT distinct default_name,value FROM dbo.tbl_ecommerce_module_defaults_bu WHERE business_unit = @BUSINESS_UNIT" + (displayName != string.Empty ? " AND DEFAULT_NAME like \'%\' + @DEFAULT_NAME + \'%\'" : "");
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
if (displayName != string.Empty)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@DEFAULT_NAME", displayName));
}
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(GetConfigurationData(outputDataTable, defaultKeys, variableKeys));
}
/// <summary>
/// Returns the group ID
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public int GetGroupID()
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
StringBuilder commandText = new StringBuilder();
ErrorObj err = new ErrorObj();
try
{
commandText.AppendLine("select value from dbo.tbl_ecommerce_module_defaults_bu a where a.DEFAULT_NAME = \'TALENT_DEFAULTS_GROUP_ID\'");
commandText.AppendLine("update dbo.tbl_ecommerce_module_defaults_bu");
commandText.AppendLine("set VALUE = cast(VALUE as int) + 1 ");
commandText.AppendLine("where DEFAULT_NAME = \'TALENT_DEFAULTS_GROUP_ID\'");
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText.ToString();
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(int.Parse(System.Convert.ToString(outputDataTable.Rows[0]["value"])));
}
/// <summary>
/// Returns current configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="configs"></param>
/// <returns></returns>
/// <remarks></remarks>
private Dictionary <string, string> RetrieveCurrentValues(string businessUnit, System.Collections.Generic.List <ConfigurationItem> configs)
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
System.Collections.Generic.List <string> codes = new System.Collections.Generic.List <string>();
string commandText = "SELECT * FROM tbl_activity_template_type WHERE BUSINESS_UNIT = @BUSINESS_UNIT AND NAME = @NAME";
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@NAME", Name));
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(GetCurrentValues(outputDataTable, configs));
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public DataTable SelectSQLPaymentTypes()
{
string sqlStatement = "SELECT tp.PAYMENT_TYPE_ID, tp.PAYMENT_TYPE_CODE, tp.PAYMENT_TYPE_DESCRIPTION, tp.IS_OTHER_TYPE, " +
" tpbu.DEFAULT_PAYMENT_TYPE " +
" FROM tbl_payment_type tp " +
" LEFT OUTER JOIN tbl_payment_type_bu tpbu ON tpbu.PAYMENT_TYPE_CODE = tp.PAYMENT_TYPE_CODE AND tpbu.BUSINESS_UNIT = @BusinessUnit ";
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = sqlStatement;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BusinessUnit", businessUnit));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(outputDataTable);
}
public override List <ConfigurationEntity> RetrieveAllValues(string businessUnit, string[] defaultKeys, string[] variableKeys, string displayName = "")
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string commandText = string.Format("SELECT * FROM tbl_activity_template_type WHERE BUSINESS_UNIT=@BUSINESS_UNIT AND NAME=@NAME");
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@NAME", variableKeys[0]));
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(GetConfigurationData(outputDataTable, defaultKeys, variableKeys));
}
public ConfigurationEntity GetConfigurationEntity(string configId)
{
string commandText = "SELECT * FROM tbl_config_detail WHERE config_id=@CONFIG_ID";
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@CONFIG_ID", configId));
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
if (outputDataTable.Rows.Count > 0)
{
return(ConfigurationEntity.GetEntity(outputDataTable.Rows[0]));
}
return(null);
}
public override DataTable RetrieveDTForList()
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string commandText = string.Format("select a.GROUP_ID as ID, convert(varchar,min(TIMESTAMP),120) TIMESTAMP, min(user_name) USER_NAME, min(MODULE_NAME) MODULE_NAME, count(1) as COMMAND_COUNT, MIN(BUSINESS_UNIT) as BUSINESS_UNIT, MIN(DATA_SOURCE) AS DATA_SOURCE, min(CATALOG) as CATALOG from tbl_config_detail_audit a group by a.GROUP_ID");
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_DEFINITION);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(outputDataTable);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
public int GetUniqueId(SqlTransaction givenTransaction)
{
int result = 0;
string commandText = "SELECT UniqueId FROM [tbl_defaults]";
int uniqueId = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
uniqueId = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
return(uniqueId);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="tableName"></param>
/// <param name="defaultNames"></param>
/// <returns></returns>
/// <remarks></remarks>
private string[] GetMasterConfigurations(string tableName, string[] defaultNames)
{
string[] whereClause = new string[defaultNames.Count() - 1 + 1];
for (int i = 0; i <= defaultNames.Count() - 1; i++)
{
whereClause[i] = "default_name = @DEFAULT_NAME" + i.ToString();
}
string where = string.Join(" OR ", whereClause);
string commandText = string.Format("SELECT * FROM tbl_config_detail WHERE table_name=@TABLE_NAME AND variable_key1=@VARIABLE_KEY1 AND variable_key2=@VARIABLE_KEY2 AND variable_key3=@VARIABLE_KEY3 AND variable_key4=@VARIABLE_KEY4 AND ({0})", where);
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@TABLE_NAME", tableName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY1", settings.VariableKey1));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY2", settings.VariableKey2));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY3", settings.VariableKey3));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY4", settings.VariableKey4));
for (int i = 0; i <= defaultNames.Count() - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_NAME" + i.ToString(), defaultNames[i]));
}
ErrorObj err = new ErrorObj();
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
System.Collections.Generic.List <string> masterConfigIds = new System.Collections.Generic.List <string>();
string masterConfigId = string.Empty;
if (outputDataTable != null)
{
foreach (DataRow row in outputDataTable.Rows)
{
masterConfigId = Utilities.CheckForDBNull_String(row["config_id"]);
masterConfigIds.Add(masterConfigId);
}
}
return(masterConfigIds.ToArray());
}
public override void AddMissingValues(System.Collections.Generic.List <ConfigurationItem> configs, Dictionary <string, string> currentValues)
{
List <ConfigurationItem> missingConfigs;
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
ErrorObj err = new ErrorObj();
List <string> items = new List <string>();
string commandText = String.Empty;
string values = string.Empty;
try
{
missingConfigs = configs.Where(c => !currentValues.ContainsKey(c.DefaultName)).ToList();
if (missingConfigs.Count > 0)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PARTNER_CODE", ALL_STRING));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PAGE_CODE", ALL_STRING));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@HIDE_IN_MAINTENANCE", bool.FalseString));
for (int i = 0; i < missingConfigs.Count; i++)
{
int j = i + 1;
ConfigurationItem config = missingConfigs[i];
String text = string.Format("SELECT @BUSINESS_UNIT, @PARTNER_CODE, @PAGE_CODE, @CONTROL_CODE_{0}, @ATTR_NAME_{0}, @ATTR_VALUE_{0}, @DESCRIPTION_{0}, @HIDE_IN_MAINTENANCE", j);
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@CONTROL_CODE_" + j, config.DefaultKey1));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ATTR_NAME_" + j, config.DefaultName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ATTR_VALUE_" + j, config.CurrentValue));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@DESCRIPTION_" + j, config.Description));
items.Add(text);
}
values = String.Join(" UNION ", items.ToArray());
commandText = string.Format("INSERT into tbl_control_attribute {0}", values);
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="entity"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
public bool SaveEntity(ConfigurationEntity entity, string moduleName, SqlTransaction givenTransaction)
{
bool result = false;
string commandText = "INSERT INTO [tbl_config_detail] ([CONFIG_ID],[TABLE_NAME], [MASTER_CONFIG], [DEFAULT_KEY1],[DEFAULT_KEY2], [DEFAULT_KEY3], [DEFAULT_KEY4], [VARIABLE_KEY1], [VARIABLE_KEY2], [VARIABLE_KEY3], [VARIABLE_KEY4], [DISPLAY_NAME], [DEFAULT_NAME],[DEFAULT_VALUE], [ALLOWED_VALUE], [ALLOWED_PLACE_HOLDER], [Description], [MODULE_NAME], [CREATED_TIMESTAMP])" +
" VALUES (@CONFIG_ID, @TABLE_NAME, @MASTER_CONFIG, @DEFAULT_KEY1, @DEFAULT_KEY2, @DEFAULT_KEY3, @DEFAULT_KEY4, @VARIABLE_KEY1, @VARIABLE_KEY2, @VARIABLE_KEY3, @VARIABLE_KEY4, @DISPLAY_NAME, @DEFAULT_NAME, @DEFAULT_VALUE, @ALLOWED_VALUE, @ALLOWED_PLACE_HOLDER, @Description, @ModuleName, GETDATE())";
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@CONFIG_ID", entity.ConfigurationId));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@TABLE_NAME", entity.TableName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@MASTER_CONFIG", entity.MasterConfigId));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_KEY1", entity.DefaultKey1));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_KEY2", entity.DefaultKey2));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_KEY3", entity.DefaultKey3));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_KEY4", entity.DefaultKey4));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY1", entity.VariableKey1));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY2", entity.VariableKey2));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY3", entity.VariableKey3));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@VARIABLE_KEY4", entity.VariableKey4));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DISPLAY_NAME", entity.DisplayName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_NAME", entity.DefaultName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@DEFAULT_VALUE", entity.DefaultValue));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@ALLOWED_VALUE", entity.AllowedValues));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@ALLOWED_PLACE_HOLDER", entity.AllowedPlaceHolders));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@Description", entity.Description));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@ModuleName", moduleName));
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
if (affectedRows > 0)
{
affectedRows = IncrementUniqueId(givenTransaction);
if (affectedRows > 0)
{
return(true);
}
}
return(false);
}
public Dictionary <string, string> RetrieveDefaultPaymentTypes(string businessUnit, string[] paymentTypeCodes)
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
int length = paymentTypeCodes.Length;
System.Collections.Generic.List <string> @params = new System.Collections.Generic.List <string>();
for (int i = 0; i <= length - 1; i++)
{
@params.Add("@PAYMENT_TYPE_CODE" + i.ToString());
}
string commandText = string.Format("SELECT PAYMENT_TYPE_CODE, DEFAULT_PAYMENT_TYPE FROM tbl_payment_type_bu WHERE business_unit = @BUSINESS_UNIT AND payment_type_code IN ({0})", string.Join(",", @params.ToArray()));
ErrorObj err = new ErrorObj();
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
for (int i = 0; i <= length - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter(@params[i], paymentTypeCodes[i]));
}
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
Dictionary <string, string> results = new Dictionary <string, string>();
foreach (DataRow row in outputDataTable.Rows)
{
string key = row["PAYMENT_TYPE_CODE"].ToString();
string value = row["DEFAULT_PAYMENT_TYPE"].ToString();
if (!results.ContainsKey(key))
{
results.Add(key, value);
}
}
return(results);
}
/// <summary>
/// Inserts serialized transaction into the database
/// </summary>
/// <param name="ms"></param>
/// <param name="GroupID"></param>
/// <param name="ModuleName"></param>
/// <param name="UserName"></param>
/// <param name="TableName"></param>
/// <param name="Action"></param>
/// <param name="DataSource"></param>
/// <param name="Catalog"></param>
/// <param name="BusinessUnit"></param>
/// <param name="GivenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
public bool Insert(MemoryStream ms, string GroupID, string ModuleName, string UserName, string TableName, string Action, string DataSource, string Catalog, string BusinessUnit, SqlTransaction GivenTransaction = null)
{
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
var commandText = string.Format("INSERT INTO dbo.tbl_config_detail_audit(GROUP_ID, USER_NAME, MODULE_NAME, COMMAND, TIMESTAMP,TABLE_NAME,ACTION,DATA_SOURCE,CATALOG,BUSINESS_UNIT) values (@GROUP_ID, @USER_NAME, @MODULE_NAME, @COMMAND, GETDATE(),@TABLE_NAME,@ACTION,@DATA_SOURCE,@CATALOG,@BUSINESS_UNIT)");
bool retVal = true;
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@GROUP_ID", GroupID));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@USER_NAME", UserName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@MODULE_NAME", ModuleName.ToUpper()));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@COMMAND", ms.GetBuffer(), SqlDbType.VarBinary));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@TABLE_NAME", TableName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ACTION", Action));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@DATA_SOURCE", DataSource));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@CATALOG", Catalog));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", BusinessUnit.ToUpper()));
//Execute
if (GivenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_DEFINITION, false);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_DEFINITION, GivenTransaction, false);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
}
catch (Exception ex)
{
retVal = false;
Utilities.InsertErrorLog(settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
finally
{
talentSqlAccessDetail = null;
}
return(retVal);
}
/// <summary>
/// Updates configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int UpdateData(string businessUnit, System.Collections.Generic.List <ConfigurationItem> configs, SqlTransaction givenTransaction)
{
StringBuilder cases = new StringBuilder();
for (int i = 0; i <= configs.Count - 1; i++)
{
cases.Append(" WHEN @MARKETING_CAMPAIGN_CODE" + i.ToString() + " THEN @MARKETING_CAMPAIGN_DESCRIPTION" + i.ToString());
}
string[] whereClause = new string[configs.Count - 1 + 1];
for (int i = 0; i <= configs.Count - 1; i++)
{
whereClause[i] = "(MARKETING_CAMPAIGN_CODE = @MARKETING_CAMPAIGN_CODE" + i.ToString() + ")";
}
string where = string.Join(" OR ", whereClause);
var commandText = string.Format("UPDATE dbo.tbl_marketing_campaigns_lang SET VALUE = CASE MARKETING_CAMPAIGN_CODE {0} END WHERE business_unit = @BUSINESS_UNIT AND PARTNER_CODE = @PARTNERCODE AND ({1})", cases.ToString(), where);
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PARTNERCODE", partnerCode));
for (int i = 0; i <= configs.Count - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@MARKETING_CAMPAIGN_CODE" + i.ToString(), configs[i].DefaultName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@MARKETING_CAMPAIGN_DESCRIPTION" + i.ToString(), configs[i].UpdatedValue));
}
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
//Return results
return(affectedRows);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="oldGUID"></param>
/// <param name="newGUID"></param>
/// <remarks></remarks>
public string UpdateConfigId(string configId, SqlTransaction givenTransaction = null)
{
TalentDataAccess talentAccessDetail = new TalentDataAccess();
System.Collections.Generic.List <ConfigurationItem> configs = new System.Collections.Generic.List <ConfigurationItem>();
int affectedRows = 0;
ErrorObj err = new ErrorObj();
//Dim con As New SqlConnection
string commandText = "UPDATE tbl_config_detail SET CONFIG_ID=@NEW_CONFIG_ID WHERE CONFIG_ID=@CONFIG_ID";
//Dim command As New SqlCommand(query, con)
talentAccessDetail.Settings = settings;
talentAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentAccessDetail.CommandElements.CommandText = commandText;
ConfigurationEntity configEntity = GetConfigurationEntity(configId);
if (configEntity == null)
{
return(null);
}
var newConfigId = guidGenerator.GenerateGUIDString(GetUniqueId(givenTransaction), configEntity);
talentAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@NEW_CONFIG_ID", newConfigId));
talentAccessDetail.CommandElements.CommandParameter.Add(Utilities.ConstructParameter("@CONFIG_ID", configId));
if (givenTransaction == null)
{
err = talentAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG);
}
else
{
err = talentAccessDetail.SQLAccess(DestinationDatabase.TALENT_CONFIG, givenTransaction);
}
if ((!(err.HasError)) && (!(talentAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentAccessDetail = null;
if (affectedRows > 0)
{
affectedRows = IncrementUniqueId(givenTransaction);
if (affectedRows > 0)
{
return(newConfigId);
}
}
return(null);
}
/// <summary>
/// Updates configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int UpdateData(string businessUnit, System.Collections.Generic.List <ConfigurationItem> configs, SqlTransaction givenTransaction)
{
StringBuilder cases = new StringBuilder();
for (int i = 0; i <= configs.Count - 1; i++)
{
cases.Append(" WHEN @ATTR_NAME" + System.Convert.ToString(i) + " THEN @ATTR_VALUE" + System.Convert.ToString(i));
}
string[] whereClause = new string[configs.Count - 1 + 1];
for (int i = 0; i <= configs.Count - 1; i++)
{
whereClause[i] = "(attr_name = @ATTR_NAME" + System.Convert.ToString(i) + " AND control_code = @CONTROL_CODE" + System.Convert.ToString(i) + ")";
}
string where = string.Join(" OR ", whereClause);
var commandText = string.Format("UPDATE tbl_control_attribute SET attr_value = CASE attr_name {0} END WHERE business_unit = @BUSINESS_UNIT AND ({1})", cases.ToString(), where);
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
for (int i = 0; i <= configs.Count - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ATTR_NAME" + System.Convert.ToString(i), configs[i].DefaultName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ATTR_VALUE" + System.Convert.ToString(i), configs[i].UpdatedValue));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@CONTROL_CODE" + System.Convert.ToString(i), configs[i].DefaultKey1));
}
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
//Return results
return(affectedRows);
}
public override void AddMissingValues(System.Collections.Generic.List <ConfigurationItem> configs, Dictionary <string, string> currentValues)
{
List <ConfigurationItem> missingConfigs;
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
ErrorObj err = new ErrorObj();
string commandText = string.Empty;
try
{
missingConfigs = configs.Where(c => !currentValues.ContainsKey(c.DefaultName)).ToList();
if (missingConfigs.Count > 0)
{
foreach (ConfigurationItem config in missingConfigs)
{
commandText = "INSERT INTO tbl_error_messages VALUES(@LANGUAGE_CODE, @BUSINESS_UNIT, @PARTNER_CODE, @MODULE, @PAGE_CODE, @ERROR_CODE, @ERROR_MESSAGE)";
//Construct The Call
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@LANGUAGE_CODE", LANG_ENG));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PARTNER_CODE", ALL_STRING));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@MODULE", ALL_STRING));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PAGE_CODE", ALL_STRING));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ERROR_CODE", config.DefaultName));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@ERROR_MESSAGE", config.CurrentValue));
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
}
/// <summary>
/// Returns current configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="configs"></param>
/// <returns></returns>
/// <remarks></remarks>
private Dictionary <string, string> RetrieveCurrentValues(string businessUnit, System.Collections.Generic.List <ConfigurationItem> configs)
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
string[] whereClause = new string[configs.Count - 1 + 1];
string where = string.Empty;
string commandText = string.Empty;
ErrorObj err = new ErrorObj();
try
{
for (int i = 0; i <= configs.Count - 1; i++)
{
whereClause[i] = "(marketing_campaign_code = @MARKETING_CAMPAIGN_CODE" + i.ToString() + ")";
}
where = string.Join(" OR ", whereClause);
commandText = string.Format("SELECT distinct ID,MARKETING_CAMPAIGN_CODE,MARKETING_CAMPAIGN_DESCRIPTION FROM dbo.tbl_marketing_campaigns_lang WHERE business_unit = @BUSINESS_UNIT AND partner_code = @PARTNER AND ({0})", where);
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PARTNER_CODE", partnerCode));
for (int i = 0; i <= configs.Count - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@MARKETING_CAMPAIGN_CODE" + i.ToString(), configs[i].DefaultName));
}
talentSqlAccessDetail.CommandElements.CommandText = commandText;
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(GetCurrentValues(outputDataTable));
}
/// <summary>
/// Insert Configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="paymentCode"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <param name="key"></param>
/// <returns></returns>
/// <remarks></remarks>
private int InsertData(string businessUnit, string paymentCode, System.Collections.Generic.List <ConfigurationItem> configs, SqlTransaction givenTransaction, ref string key)
{
Dictionary <string, string> setValue = new Dictionary <string, string>();
for (int i = 0; i <= configs.Count - 1; i++)
{
string name = configs[i].DefaultName;
setValue.Add(name, string.Format("@{0}", name));
}
string columns = string.Format("({0})", string.Join(", ", setValue.Keys));
string values = string.Format("VALUES({0})", string.Join(", ", setValue.Values));
var commandText = string.Format("INSERT INTO tbl_payment_type {0} {1}", columns, values);
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
for (int i = 0; i <= configs.Count - 1; i++)
{
string paramName = string.Format("@{0}", configs[i].DefaultName);
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter(paramName, configs[i].UpdatedValue));
if (configs[i].DefaultName == "PAYMENT_TYPE_CODE")
{
key = configs[i].UpdatedValue;
}
}
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
//Return results
return(affectedRows);
}
/// <summary>
/// Returns a set of serialized transactions for a specified group
/// </summary>
/// <param name="GroupID"></param>
/// <returns></returns>
/// <remarks></remarks>
public List <TalentDataAccess> GetCommandsByGroupId(int GroupID)
{
DataTable outputDataTable = new DataTable();
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
TalentDataAccess objTalentAccessDetail = default(TalentDataAccess);
string commandText = string.Format("SELECT COMMAND FROM tbl_config_detail_audit WHERE GROUP_ID=@GroupID ORDER BY ID");
System.Collections.Generic.List <TalentDataAccess> lstTalentAccessDetail = new System.Collections.Generic.List <TalentDataAccess>();
ErrorObj err = new ErrorObj();
BinaryFormatter bf = new BinaryFormatter();
byte[] byteArr = null;
MemoryStream ms = default(MemoryStream);
try
{
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@GroupID", GroupID));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.TALENT_DEFINITION);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
outputDataTable = talentSqlAccessDetail.ResultDataSet.Tables[0];
}
foreach (DataRow dr in outputDataTable.Rows)
{
byteArr = (byte[])(dr["COMMAND"]);
ms = new MemoryStream(byteArr);
objTalentAccessDetail = (TalentDataAccess)(bf.Deserialize(ms));
lstTalentAccessDetail.Add(objTalentAccessDetail);
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(lstTalentAccessDetail);
}
/// <summary>
/// Confirms if the PaymentType Code is custom or system. true - Custom | false - System
/// </summary>
/// <param name="paymentTypeCode"></param>
/// <returns></returns>
public bool IsCustomPaymentTypeCode(string PaymentTypeCode)
{
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
string commandText = "SELECT 1 FROM TBL_PAYMENT_TYPE WHERE PAYMENT_TYPE_CODE = @PAYMENT_TYPE_CODE AND IS_OTHER_TYPE = 1";
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@PAYMENT_TYPE_CODE", PaymentTypeCode));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = talentSqlAccessDetail.ResultDataSet.Tables[0].Rows.Count;
}
talentSqlAccessDetail = null;
return(affectedRows > 0 ? true : false);
}
/// <summary>
/// Updates configurations
/// </summary>
/// <param name="businessUnit"></param>
/// <param name="name"></param>
/// <param name="configs"></param>
/// <param name="givenTransaction"></param>
/// <returns></returns>
/// <remarks></remarks>
private int UpdateData(string businessUnit, string name, System.Collections.Generic.List <ConfigurationItem> configs, SqlTransaction givenTransaction)
{
string[] setValue = new string[configs.Count - 1 + 1];
string defaultName = "";
for (int i = 0; i <= configs.Count - 1; i++)
{
defaultName = configs[i].DefaultName;
setValue[i] = string.Format("{0} = @VAR_{0}", defaultName);
}
string values = string.Join(" , ", setValue);
var commandText = string.Format("UPDATE tbl_activity_template_type SET {0} WHERE BUSINESS_UNIT = @BUSINESS_UNIT AND NAME = @NAME", values);
int affectedRows = 0;
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
//Construct The Call
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = commandText;
for (int i = 0; i <= configs.Count - 1; i++)
{
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter(string.Format("@VAR_{0}", configs[i].DefaultName), configs[i].UpdatedValue));
}
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@BUSINESS_UNIT", businessUnit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@NAME", name));
//Execute
if (givenTransaction == null)
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
}
else
{
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005, givenTransaction);
}
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentSqlAccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
talentSqlAccessDetail = null;
//Return results
return(affectedRows);
}