/// <summary>
/// error map script get
/// </summary>
/// <param name="searchTbl"></param>
/// <param name="isLogGet"></param>
/// <param name="topCount"></param>
/// <param name="sortdirection"></param>
/// <returns></returns>
public static string TBLQueryGet(ErrorMapConfigTbl searchTbl, bool isLogGet = false
, int topCount = 0
, CommonEnum.SortDirection sortdirection = CommonEnum.SortDirection.None)
{
string topStr = string.Empty;
if (topCount > 0)
{
topStr = "top " + topCount;
}
string orderBy = string.Empty;
if (sortdirection == CommonEnum.SortDirection.ASC)
{
orderBy = "order by 1 asc";
}
else if (sortdirection == CommonEnum.SortDirection.DESC)
{
orderBy = "order by 1 desc";
}
return(string.Format("select {0} * from {1} where 1=1 {2} {3} {4} {5}"
, topStr
, (isLogGet ? "ErrorMapLog" : "ErrorMap")
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.messageType, "and MessageType")
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.errorCode, "and errorCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.textRegex, "and TextRegEx")
, orderBy));
}
/// <summary>
/// db query get
/// </summary>
/// <param name="searchTbl"></param>
/// <returns></returns>
public static string TBLQueryGet(POSToAmadeusDefaultSegmentMapTbl searchTbl)
{
return(string.Format("select * from PoSToAmadeusDefaultSegmentMap where 1=1 {0} {1} {2} {3}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.OfficeID, "and OfficeID", noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.jurisdictionCode, "and jurisdictionCode", noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.companyCode, "and companyCode", noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.managementUnitCode, "and managementUnitCode", noReturnIfNoValue: true)));
}
/// <summary>
/// get where script
/// </summary>
/// <param name="config"></param>
/// <param name="configType"></param>
/// <returns></returns>
public static string TBLQueryGetWhere(ConfigurationSettingDataTbl config, ConfigSettingType configType)
{
string where = string.Empty;
switch (configType)
{
case ConfigSettingType.POS:
where = string.Format("where 1=1 {0} {1} {2} {3} {4}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.jurisdictionCode, "and JurisdictionCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.companyCode, "and CompanyCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.managementUnitCode, "and ManagementUnitCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.settingName, "and SettingName")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.environment, "and EnvironmentName"));
break;
case ConfigSettingType.PoSToWorldspanDefaultSegmentMap:
where = string.Format("where 1=1 {0} {1} {2}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.jurisdictionCode, "and JurisdictionCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.companyCode, "and CompanyCode")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.managementUnitCode, "and ManagementUnitCode")
);
break;
case ConfigSettingType.Client:
where = string.Format("where 1=1 {0} {1} {2}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.clientID, "and ClientID") //clientID='config.clientID'
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.settingName, "and SettingName")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.environment, "and EnvironmentName"));
break;
case ConfigSettingType.Supplier:
string updatedBy = "";
if (config.LastUpdatedBy != null)
{
if (config.UpdateType == supplierUpdateType.Add)
{
updatedBy = CarSCSCommonHelper.SubConditionQueryGet <string>(config.LastUpdatedBy, "and CreatedBy");
}
if (config.UpdateType == supplierUpdateType.Update)
{
updatedBy = CarSCSCommonHelper.SubConditionQueryGet <string>(config.LastUpdatedBy, "and LastUpdatedBy");
}
}
where = string.Format("where 1=1 {0} {1} {2} {3}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.supplierID, "and SupplierID") //SupplierID='config.supplierID'
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.settingName, "and SettingName")
, CarSCSCommonHelper.SubConditionQueryGet <string>(config.environment, "and EnvironmentName")
, updatedBy);
break;
}
return(where);
}
/// <summary>
/// update SupplierConfiguration
/// </summary>
/// <param name="settingName"></param>
/// <param name="settingValue"></param>
/// <param name="env"></param>
/// <param name="supplierID"></param>
public void SupplierConfigUpdate(string settingName, string settingValue, string env = null, string supplierID = null)
{
ConfigurationSettingDataTbl config = new ConfigurationSettingDataTbl(env: env, sName: settingName, sID: supplierID);
string where = TBLQueryGetWhere(config, ConfigSettingType.Supplier);
if (string.IsNullOrEmpty(where))
{
Assert.Fail("no where condition for SupplierConfigUpdate");
}
string sql = string.Format("update SupplierConfiguration set SettingValue = '{0}' {1}", settingValue, where);
CarSCSCommonHelper.DBExecuteNonQuery(this.ConnectionString, sql);
}
/// <summary>
/// SupplierItemMap table query get
/// </summary>
/// <param name="searchTbl"></param>
/// <returns></returns>
public static string TBLQueryGet(SupplierItemMaptbl searchTbl)
{
string supplySubsetID = "and SupplySubsetID";
string itemKey = "and ItemKey";
string itemValue = "and ItemValue";
return(string.Format("select * from SupplierItemMap where 1=1 {0} {1} {2} "
, CarSCSCommonHelper.SubConditionQueryGet <int>(searchTbl.SupplySubsetID, supplySubsetID, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.ItemKey, itemKey, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.ItemValue, itemValue, noReturnIfNoValue: true)
));
}
/// <summary>
/// error map entity get by DB returns
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static ErrorMapConfigTbl ErrorMapTblGet(SqlDataReader reader)
{
ErrorMapConfigTbl tbl = new ErrorMapConfigTbl();
tbl.messageType = reader["MessageType"].ToString();
tbl.errorCode = reader["ErrorCode"].ToString();
tbl.textRegex = reader["TextRegEx"].ToString();
tbl.errorNode = reader["ErrorNode"].ToString();
tbl.message = reader["Message"].ToString();
tbl.interruptProcessing = reader["InterruptProcessingBool"].ToString();
tbl.logError = reader["LogErrorBool"].ToString();
tbl.CreateDate = Convert.ToDateTime(reader["CreateDate"].ToString());
tbl.CreatedBy = reader["CreatedBy"].ToString();
tbl.UpdateDate = CarSCSCommonHelper.ColumnNameCheck <DateTime>(reader, "UpdateDate", DateTime.MinValue);
tbl.updatedBy = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "LastUPdatedBy", null);
tbl.errorMapID = Convert.ToInt32(reader["ErrorMapID"].ToString());
return(tbl);
}
/// <summary>
/// ExternalSupplyServiceDomainValueMap entity list get by query
/// </summary>
/// <param name="query"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public static List <ExternalSupplyServiceDomainValueMaptbl> ESSDVMtblsGet(
string query, string connectionString)
{
List <ExternalSupplyServiceDomainValueMaptbl> tbls = new List <ExternalSupplyServiceDomainValueMaptbl>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = query;
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
ExternalSupplyServiceDomainValueMaptbl tbl;
string domainTypeColname = "DomainType";
if (!CarSCSCommonHelper.ColumnExists(reader, domainTypeColname))
{
domainTypeColname = "DomainTypeID";
}
while (reader.Read())
{
tbl = new ExternalSupplyServiceDomainValueMaptbl();
tbl.SupplierID = reader["SupplierID"].ToString();
tbl.MessageSystemID = reader["MessageSystemID"].ToString();
tbl.DomainType = reader[domainTypeColname].ToString();
tbl.DomainValue = reader["DomainValue"].ToString();
tbl.ExternalDomainValue = reader["ExternalDomainValue"].ToString();
tbl.CreateDate = Convert.ToDateTime(reader["CreateDate"].ToString());
tbl.CreatedBy = reader["CreatedBy"].ToString();
tbl.UpdateDate = Convert.ToDateTime(reader["UpdateDate"].ToString());
tbl.LastUpdatedBy = reader["LastUpdatedBy"].ToString();
tbls.Add(tbl);
}
conn.Close();
}
return(tbls);
}
/// <summary>
/// ExternalSupplyServiceDomainValueMap table query get
/// </summary>
/// <param name="searchTbl"></param>
/// <returns></returns>
public static string TBLQueryGet(ExternalSupplyServiceDomainValueMaptbl searchTbl, string[] columnNames = null)
{
string domainTypeStr = "and DomainType";
string eDomainValueStr = "and ExternalDomainValue";
string domainValueStr = "and DomainValue";
string supplierIDStr = "and SupplierID";
string messageSystemIDStr = "and MessageSystemID";
if (columnNames != null)
{
if (columnNames.Length > 0)
{
domainTypeStr = "and " + columnNames[0];
}
if (columnNames.Length > 1)
{
eDomainValueStr = "and " + columnNames[1];
}
if (columnNames.Length > 2)
{
domainValueStr = "and " + columnNames[2];
}
if (columnNames.Length > 3)
{
supplierIDStr = "and " + columnNames[3];
}
if (columnNames.Length > 4)
{
messageSystemIDStr = "and " + columnNames[4];
}
}
return(string.Format("select * from ExternalSupplyServiceDomainValueMap where 1=1 {0} {1} {2} {3} {4}"
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.DomainType, domainTypeStr, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.ExternalDomainValue, eDomainValueStr, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.DomainValue, domainValueStr, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.SupplierID, supplierIDStr, noReturnIfNoValue: true)
, CarSCSCommonHelper.SubConditionQueryGet <string>(searchTbl.MessageSystemID, messageSystemIDStr, noReturnIfNoValue: true)));
}
/// <summary>
/// get ConfigurationSettingDataTbl entity list by query
/// </summary>
/// <param name="query"></param>
/// <param name="connectionString"></param>
/// <param name="configIDColName"></param>
/// <param name="defaultNullValue"></param>
/// <returns></returns>
public static List <ConfigurationSettingDataTbl> CSDTblsGet(string query, string connectionString
, string configIDColName = "PoSConfigurationID", string defaultNullValue = null)
{
List <ConfigurationSettingDataTbl> tbls = new List <ConfigurationSettingDataTbl>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = query;
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
ConfigurationSettingDataTbl tbl;
while (reader.Read())
{
tbl = new ConfigurationSettingDataTbl();
tbl.environment = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "environmentName", defaultNullValue);
tbl.settingName = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "settingName", defaultNullValue);
tbl.value = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "SettingValue", defaultNullValue);
tbl.jurisdictionCode = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "jurisdictionCode", defaultNullValue);
tbl.companyCode = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "companyCode", defaultNullValue);
tbl.managementUnitCode = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "managementUnitCode", defaultNullValue);
tbl.supplierID = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "supplierID", defaultNullValue);
tbl.clientID = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "clientID", defaultNullValue);
tbl.CreateDate = CarSCSCommonHelper.ColumnNameCheck <DateTime>(reader, "CreateDate", DateTime.MinValue);
tbl.CreatedBy = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "CreatedBy", defaultNullValue);
tbl.UpdateDate = CarSCSCommonHelper.ColumnNameCheck <DateTime>(reader, "UpdateDate", DateTime.MinValue);
tbl.LastUpdatedBy = CarSCSCommonHelper.ColumnNameCheck <string>(reader, "LastUpdatedBy", defaultNullValue);
tbl.ConfigurationID = CarSCSCommonHelper.ColumnNameCheck <int>(reader, configIDColName, 0);
tbl.AuditActionID = CarSCSCommonHelper.ColumnNameCheck <int>(reader, "AuditActionID", 0);
tbls.Add(tbl);
}
conn.Close();
}
return(tbls);
}
public ConfigurationDBHelper(CommonEnum.ServiceName service, ConfigSettingType configType)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(service);
this.ConfigurationType = configType;
}
public ConfigurationDBHelper(CommonEnum.ServieProvider serviceProvider, ConfigSettingType configType)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(ConfigSettingHelper.ServiceNameGet(serviceProvider));
this.ConfigurationType = configType;
}
public string ConfigurationIDNameGet(bool isLogGet)
{
return(CarSCSCommonHelper.ConfigurationIDColNameGet(this.ConfigurationType, isLogGet));
}
public POSToAmadeusDefaultSegmentMapTblHelper(CommonEnum.ServieProvider serviceProvider)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(Util.ConfigSettingHelper.ServiceNameGet(serviceProvider));
}
public ExternalSupplyServiceDomainValueMapHelper(CommonEnum.ServieProvider serviceProvider)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(Util.ConfigSettingHelper.ServiceNameGet(serviceProvider));
}
public ErrorMapHelper(CommonEnum.ServiceName service)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(service);
}
public ErrorMapHelper(CommonEnum.ServieProvider serviceProvider)
{
this.ConnectionString = CarSCSCommonHelper.DBConnectionStringGet(Util.ConfigSettingHelper.ServiceNameGet(serviceProvider));
}
