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