/// <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)); }