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