/// <summary> /// Перенаправитель для обработки параметров: value is Function или value is VariableDef или это просто значение /// </summary> /// <param name="value"></param> /// <param name="convertValue"></param> /// <param name="convertIdentifier"></param> /// <returns></returns> public virtual string SQLTranslSwitch(object value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (value is Function) { return(((value as Function).FunctionDef.Language as SQLWhereLanguageDef).SQLTranslFunction(value as Function, convertValue, convertIdentifier)); } if (value is VariableDef) { if ((value as VariableDef).Language != null) { // if ((value as VariableDef).Type==SQLWhereLanguageDef.LanguageDef.BoolType) // { // return string.Format("{0}=1",((value as VariableDef).Language as SQLWhereLanguageDef).SQLTranslVariable((value as VariableDef),convertValue,convertIdentifier)); // } // else // { return(((value as VariableDef).Language as SQLWhereLanguageDef).SQLTranslVariable(value as VariableDef, convertValue, convertIdentifier)); // } } return(SQLTranslVariable(value as VariableDef, convertValue, convertIdentifier)); } return(convertValue(value)); }
private string GetConditionForExistExact(Function func, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { var f1 = new Function(GetFunctionDefByStringedView(funcCountWithLimit), func.Parameters[0], func.Parameters[1]); var f2 = new Function(GetFunctionDefByStringedView(funcCount), func.Parameters[0]); Function fres = GetFunction(funcAND, GetFunction(funcEQ, f1, f2), GetFunction(funcG, f2, 0)); return(base.SQLTranslFunction(fres, convertValue, convertIdentifier)); }
/// <summary> /// Преобразовать значение в SQL строку /// </summary> /// <param name="function">Функция</param> /// <param name="convertValue">делегат для преобразования констант</param> /// <param name="convertIdentifier">делегат для преобразования идентификаторов</param> /// <returns></returns> public override string FunctionToSql( SQLWhereLanguageDef sqlLangDef, Function value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { // реализована ЛИШЬ ЧАСТИЧНАЯ поддержка Access ExternalLangDef langDef = sqlLangDef as ExternalLangDef; if (value.FunctionDef.StringedView == "OnlyDate") { return(string.Format("cdate (int( {0} ) )", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } return(base.FunctionToSql(sqlLangDef, value, convertValue, convertIdentifier)); }
private string ConvertIdentifierForDetail(string identifier, string agregatorName, string agregatorAlias, string detailAlias, delegatePutIdentifierToBrackets convertIdentifier) { string alias; if (identifier.StartsWith(agregatorName + ".")) { identifier = identifier.Remove(0, agregatorName.Length + 1); alias = agregatorAlias; } else { alias = detailAlias; } return(string.Format("{0}.{1}", convertIdentifier(alias), convertIdentifier(identifier))); }
/// <summary> /// В зависимости от CaseInsensitive добавляет UPPER /// </summary> /// <param name="value">Function.Parameters[i]</param> /// <param name="convertValue"></param> /// <param name="convertIdentifier"></param> /// <returns></returns> protected string AddUpper(object value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { string retStr; if (CaseInsensitive && (value is VariableDef && ((VariableDef)value).Type.StringedView == "String")) { retStr = "UPPER( " + SQLTranslSwitch(value, convertValue, convertIdentifier) + " )"; return(retStr); } if (CaseInsensitive && value is String) { retStr = SQLTranslSwitch(value.ToString().ToUpper(), convertValue, convertIdentifier); return(retStr); } retStr = SQLTranslSwitch(value, convertValue, convertIdentifier); return(retStr); }
private string GetConditionForExistAllExact(Function func, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (!(func.Parameters[1] is Function) || ((Function)func.Parameters[1]).FunctionDef.StringedView != funcEQ && ((Function)func.Parameters[1]).FunctionDef.StringedView != funcIN) { throw new Exception("<" + func.FunctionDef.StringedView + "> parameter1 must be <=> or <in>"); } var conditionFunc = (Function)func.Parameters[1]; Function funcAdv = null; if (func.Parameters.Count > 2) { var advParams = new ArrayList(); for (int i = 1; i < func.Parameters.Count; i++) { advParams.Add(func.Parameters[i]); } funcAdv = GetFunction(funcAND, advParams.ToArray()); } if (funcAdv == null) { funcAdv = conditionFunc; } var vdefDet = func.Parameters[0] as VariableDef; var funcOperand1 = new Function(GetFunctionDefByStringedView(funcExistAll), vdefDet, conditionFunc); Function funcOperand2 = GetFunction(funcEQ, 0, GetFunction(funcCountWithLimit, vdefDet, GetFunction(funcNOT, funcAdv))); Function function = GetFunction(funcAND, funcOperand1, funcOperand2); return(base.SQLTranslFunction(function, convertValue, convertIdentifier)); }
/// <summary> /// Преобразовать значение в SQL строку /// </summary> /// <param name="function">Функция</param> /// <param name="convertValue">делегат для преобразования констант</param> /// <param name="convertIdentifier">делегат для преобразования идентификаторов</param> /// <returns></returns> public override string FunctionToSql( SQLWhereLanguageDef sqlLangDef, Function value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { ExternalLangDef langDef = sqlLangDef as ExternalLangDef; if (value.FunctionDef.StringedView == "TODAY") { return("sysdate"); } if ( value.FunctionDef.StringedView == "YearPart" || value.FunctionDef.StringedView == "MonthPart" || value.FunctionDef.StringedView == "DayPart") { return(string.Format("EXTRACT ({0} FROM {1})", value.FunctionDef.StringedView.Substring(0, value.FunctionDef.StringedView.Length - 4), langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if ( value.FunctionDef.StringedView == "hhPart") { return(string.Format("TO_CHAR({1}, \'{0}\')", "HH24", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "miPart") { return(string.Format("TO_CHAR({1}, \'{0}\')", "MI", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DayOfWeek") { // здесь требуется преобразование из DATASERVICE return(string.Format("TO_CHAR({1}, \'{0}\')", "D", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDayOfWeekZeroBased) { throw new NotImplementedException(string.Format("Function {0} is not implemented for Oracle", langDef.funcDayOfWeekZeroBased)); } if (value.FunctionDef.StringedView == langDef.funcDaysInMonth) { // здесь требуется преобразование из DATASERVICE string.Format("to_char(last_day(to_date('01.'||{0}||'.'||{1},'dd.mm.yyyy')),'dd')", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier)); return(string.Empty); } if (value.FunctionDef.StringedView == "OnlyDate") { return(string.Format("TRUNC({0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "CurrentUser") { return(string.Format("'{0}'", CurrentUserService.CurrentUser.FriendlyName)); // у нее нет параметров // langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier)); } if (value.FunctionDef.StringedView == "OnlyTime") { return(string.Format("TRUNC({0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DATEDIFF") { var ret = string.Empty; if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Year") { ret = string.Format("EXTRACT (YEAR FROM {1}) - EXTRACT (YEAR FROM {0})", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Month") { ret = string.Format("(EXTRACT (YEAR FROM {1}) - EXTRACT (YEAR FROM {0})) * 12 + (EXTRACT (MONTH FROM {1}) - EXTRACT (MONTH FROM {0}))", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Week") { ret = string.Format("(TRUNC({1},'DAY') - TRUNC({0},'DAY'))/7", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Day") { ret = string.Format("TRUNC({1}) - TRUNC({0})", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "quarter") { ret = string.Format("(EXTRACT (YEAR FROM {1}) - EXTRACT (YEAR FROM {0})) * 4 + (TO_CHAR({1}, 'Q') - TO_CHAR({0}, 'Q'))", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } return(ret); } if (value.FunctionDef.StringedView == "SUM" || value.FunctionDef.StringedView == "AVG" || value.FunctionDef.StringedView == "MAX" || value.FunctionDef.StringedView == "MIN") { ICSSoft.STORMNET.Business.LoadingCustomizationStruct lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); DetailVariableDef dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = new View(); lcs.View.DefineClassType = dvd.View.DefineClassType; lcs.View.AddProperty(dvd.ConnectMasterPorp); string[] prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; ArrayList al = new ArrayList(); object par = langDef.TransformObject(value.Parameters[1], dvd.StringedView, al); foreach (string s in al) { lcs.View.AddProperty(s); } string Slct = GenerateSQLSelect(lcs, false).Replace("STORMGENERATEDQUERY", "SGQ" + Guid.NewGuid().ToString().Replace("-", string.Empty)); string CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); // FunctionalLanguage.Function numFunc = (value.Parameters[1] as FunctionalLanguage.Function); string sumExpression = langDef.SQLTranslSwitch(par, convertValue, convertIdentifier); string res = string.Empty; res = string.Format( "( SELECT {0} From ( " + "SELECT {6}({5}) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), // convertIdentifier(dvd.OwnerConnectProp), Slct, // ВНИМАНИЕ ЗДЕСЬ ТРЕБУЕТСЯ ИЗМЕНИТь ISNULL на вычислитель в определенном DATASERVICE "NVL(" + sumExpression + ",0)", value.FunctionDef.StringedView); for (int k = 0; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += "))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcCountWithLimit || value.FunctionDef.StringedView == "Count") { var lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); var dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = dvd.View.Clone(); lcs.LimitFunction = value.FunctionDef.StringedView == langDef.funcCountWithLimit ? langDef.TransformVariables((FunctionalLanguage.Function)value.Parameters[1], dvd.StringedView, null) : langDef.GetFunction("True"); var prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; var Slct = GenerateSQLSelect(lcs, true); var CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); var res = string.Format( "( NVL( ( SELECT {0} From ( " + "SELECT Count(*) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), Slct); for (int k = 1; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += ")),0))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcToChar) { if (value.Parameters.Count == 2) { return(string.Format( "SUBSTR(TO_CHAR({0}), 1, {1})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1])); } if (value.Parameters.Count == 3) { return(string.Format( "SUBSTR(TO_CHAR({0}, {2}), 1, {1})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1], DateFormats.GetOracleDateFormat((int)value.Parameters[2]))); } } else { throw new NotImplementedException(string.Format( "Функция {0} не реализована для Oracle", value.FunctionDef.StringedView)); } return(string.Empty); }
/// <summary> /// Преобразовать значение в SQL строку /// </summary> /// <param name="function">Функция</param> /// <param name="convertValue">делегат для преобразования констант</param> /// <param name="convertIdentifier">делегат для преобразования идентификаторов</param> /// <returns></returns> public override string FunctionToSql( SQLWhereLanguageDef sqlLangDef, Function value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { ExternalLangDef langDef = sqlLangDef as ExternalLangDef; if (value.FunctionDef.StringedView == "TODAY") { return("getdate()"); } if ( value.FunctionDef.StringedView == "YearPart" || value.FunctionDef.StringedView == "MonthPart" || value.FunctionDef.StringedView == "DayPart") { return(string.Format("{0}({1})", value.FunctionDef.StringedView.Substring(0, value.FunctionDef.StringedView.Length - 4), langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if ( value.FunctionDef.StringedView == "hhPart" || value.FunctionDef.StringedView == "miPart") { return(string.Format("datepart({0},{1})", value.FunctionDef.StringedView.Substring(0, value.FunctionDef.StringedView.Length - 4), langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DayOfWeek") { //здесь требуется преобразование из DATASERVICE return(string.Format("(datepart({0}, {1})+@@DATEFIRST-2)%7 + 1", "DW", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDayOfWeekZeroBased) { // здесь требуется преобразование из DATASERVICE return(string.Format( "(datepart({0}, {1})+@@DATEFIRST-1)%7", "DW", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDaysInMonth) { //здесь требуется преобразование из DATASERVICE string monthStr = String.Format("LTRIM(RTRIM(STR({0})))", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier)); string yearStr = String.Format("LTRIM(RTRIM(STR({0})))", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier)); monthStr = String.Format("CASE WHEN LEN({0})=1 THEN '0'+{0} ELSE {0} END", monthStr); return(string.Format("DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,CAST({0}+{1}+'01' AS DATETIME))+1,0)))", yearStr, monthStr)); } if (value.FunctionDef.StringedView == "OnlyDate") { return(string.Format("cast(CONVERT(varchar(8), {1}, {0}) as datetime)", "112", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "CurrentUser") { return(string.Format("'{0}'", CurrentUserService.CurrentUser.FriendlyName)); } if (value.FunctionDef.StringedView == "OnlyTime") { return(string.Format("cast(CONVERT(varchar(8), {1}, {0}) as datetime)", "114", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DATEDIFF") { return(string.Format("DATEDIFF ( {0} , {1} , {2})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "SUM" || value.FunctionDef.StringedView == "AVG" || value.FunctionDef.StringedView == "MAX" || value.FunctionDef.StringedView == "MIN") { var lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); var dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = new View(); lcs.View.DefineClassType = dvd.View.DefineClassType; lcs.View.AddProperty(dvd.ConnectMasterPorp); var prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; var al = new ArrayList(); var par = langDef.TransformObject(value.Parameters[1], dvd.StringedView, al); foreach (string s in al) { lcs.View.AddProperty(s); } var Slct = GenerateSQLSelect(lcs, false).Replace("STORMGENERATEDQUERY", "SGQ" + Guid.NewGuid().ToString().Replace("-", string.Empty)); var CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); string sumExpression = langDef.SQLTranslSwitch(par, convertValue, convertIdentifier); string res = string.Empty; res = string.Format( "( SELECT {0} From ( " + "SELECT {6}({5}) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), //convertIdentifier(dvd.OwnerConnectProp), Slct, //ВНИМАНИЕ ЗДЕСЬ ТРЕБУЕТСЯ ИЗМЕНИТь ISNULL на вычислитель в определенном DATASERVICE "isnull(" + sumExpression + ",0)", value.FunctionDef.StringedView); for (int k = 0; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += "))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcCountWithLimit || value.FunctionDef.StringedView == "Count") { var lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); var dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = dvd.View.Clone(); lcs.LimitFunction = value.FunctionDef.StringedView == langDef.funcCountWithLimit ? langDef.TransformVariables((FunctionalLanguage.Function)value.Parameters[1], dvd.StringedView, null) : langDef.GetFunction("True"); var prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; var Slct = GenerateSQLSelect(lcs, true); var CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); var res = string.Format( "( Isnull( ( SELECT {0} From ( " + "SELECT Count(*) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}",//),0))", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), //convertIdentifier(dvd.OwnerConnectProp), Slct); for (int k = 1; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += ")),0))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcToUpper) { return(string.Format( "Upper({0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcToLower) { return(string.Format( "Lower({0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDateAdd) { return(string.Format( "dateadd({0}, {1}, {2})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcToChar) { // Общее преобразование в строку, задается значение и длина строки if (value.Parameters.Count == 2) { return(string.Format( "CONVERT(VARCHAR({1}), {0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1])); } // Преобразование даты и времени в строку; кроме значения, числом задается стиль // даты-времени, например, 104 (dd.mm.yyyy). // Стили перечислены здесь: http://msdn.microsoft.com/ru-ru/library/ms187928.aspx if (value.Parameters.Count == 3) { return(string.Format( "CONVERT(VARCHAR({1}), {0}, {2})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1], value.Parameters[2])); } } return(string.Empty); }
private string GetConditionForExistDetails(Function func, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (!(DataService is SQLDataService)) { throw new Exception(string.Format("Кострукция ограничения {0} поддерживает только SQL сервис данных.", funcExistDetails)); } string wrongParametersMessage = string.Format( "Кострукция ограничения {0} поддерживает только операцию сравнения между двумя различными детейловыми свойствами одного уровня.", funcExistDetails); if (!CheckParametersFunctionForExistDetails(func)) { throw new Exception(wrongParametersMessage); } var detail1 = (DetailVariableDef)func.Parameters[0]; var detail2 = (DetailVariableDef)func.Parameters[1]; var conditionFunc = (Function)func.Parameters[2]; string detailAlias1 = convertIdentifier(detail1.StringedView); string detailAlias2 = convertIdentifier(detail2.StringedView); string agregatorPropName1 = convertIdentifier(detail1.ConnectMasterPorp); string agregatorPropName2 = convertIdentifier(detail2.ConnectMasterPorp); string[] agregatorKeys1 = detail1.OwnerConnectProp.Length == 0 ? new [] { "STORMMainObjectKey" } : detail1.OwnerConnectProp; string[] agregatorKeys2 = detail2.OwnerConnectProp.Length == 0 ? new[] { "STORMMainObjectKey" } : detail2.OwnerConnectProp; var conditionParameters = conditionFunc.Parameters; if (!CheckConditionFunctionForExistDetails(conditionFunc.FunctionDef) || conditionParameters.Count != 2 || !(conditionParameters[0] is VariableDef) || !(conditionParameters[1] is VariableDef)) { throw new Exception(wrongParametersMessage); } string selectForDetail1 = GetSelectForDetailVariableDef(detail1, null); selectForDetail1 = selectForDetail1.Replace("STORMMainObjectKey", "STORMMainObjectKey1"); string selectForDetail2 = GetSelectForDetailVariableDef(detail2, null); selectForDetail2 = selectForDetail2.Replace("STORMMainObjectKey", "STORMMainObjectKey2"); // формируем условие для функции string propName1 = ((VariableDef)conditionParameters[0]).StringedView; if (propName1.StartsWith(detail1.StringedView + ".")) { propName1 = propName1.Remove(0, detail1.StringedView.Length + 1); } string propName2 = ((VariableDef)conditionParameters[1]).StringedView; if (propName2.StartsWith(detail2.StringedView + ".")) { propName2 = propName2.Remove(0, detail2.StringedView.Length + 1); } string propIdetifier1 = convertIdentifier(propName1); string propIdetifier2 = convertIdentifier(propName2); string condition = string.Format("({0} {1} {2})", string.Format("{0}.{1}", detailAlias1, propIdetifier1), conditionFunc.FunctionDef.StringedView, string.Format("{0}.{1}", detailAlias2, propIdetifier2)); var joinCondition = new StringBuilder(); foreach (var keyName in agregatorKeys1) { if (joinCondition.Length != 0) { joinCondition.Append(" and "); } joinCondition.AppendFormat("{0} = {1}.{2}", keyName, detailAlias1, agregatorPropName1); } foreach (var keyName in agregatorKeys2) { joinCondition.AppendFormat(" and {0} = {1}.{2}", keyName, detailAlias2, agregatorPropName2); } string result = string.Format( "exists(select * from ({0}) {3} join ({1}) {4} on {2} and ({5}))", selectForDetail1, selectForDetail2, joinCondition, detailAlias1, detailAlias2, condition); return(result); }
/// <summary> /// Преобразовать значение в SQL строку /// </summary> /// <param name="function">Функция</param> /// <param name="convertValue">делегат для преобразования констант</param> /// <param name="convertIdentifier">делегат для преобразования идентификаторов</param> /// <returns></returns> public static string ToSQLString(Function function, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { return((function.FunctionDef.Language as SQLWhereLanguageDef).SQLTranslFunction(function, convertValue, convertIdentifier)); }
/// <summary> /// Транслировать в SQL функцию /// </summary> /// <param name="value">функция</param> /// <param name="convertValue">конвертилка выражений</param> /// <param name="convertIdentifier">помещатель в скобки-кавычки</param> /// <returns></returns> protected virtual string SQLTranslFunction(Function value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (value.Parameters.Count == 2) { if (value.FunctionDef.StringedView == "=" && ((value.Parameters[1] == null) || (value.Parameters[1].GetType() == typeof(string) && ((string)value.Parameters[1]) == string.Empty))) { return("(" + SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) + " IS NULL )"); } } var valueType = value.Parameters[0].GetType(); if (valueType.IsGenericType && valueType.GetGenericTypeDefinition() == typeof(Nullable <>)) { valueType = Nullable.GetUnderlyingType(valueType); } switch (value.FunctionDef.StringedView) { case "SQL": return(value.Parameters[0].ToString()); case "ISNULL": return("(" + SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) + " IS NULL )"); case "NOT": string result = string.Empty; if (value.Parameters[0] is VariableDef) { VariableDef vdf = (VariableDef)value.Parameters[0]; var vdType = vdf.Type.NetCompatibilityType; if (vdType.IsGenericType && vdType.GetGenericTypeDefinition() == typeof(Nullable <>)) { vdType = Nullable.GetUnderlyingType(vdType); } if (vdType == SQLWhereLanguageDef.LanguageDef.BoolType.NetCompatibilityType) { return(string.Format("( NOT ({0}={1}))", convertIdentifier(vdf.StringedView), convertValue(true))); } } else if (valueType == typeof(bool)) { return(string.Format("( NOT ({0}={1}))", convertValue((bool)value.Parameters[0]), convertValue(true))); } if (result == string.Empty) { result = "( NOT " + SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) + " )"; } return(result); case "LIKE": string par1 = string.Empty, par2 = string.Empty; par1 = AddUpper(value.Parameters[0], convertValue, convertIdentifier); par2 = AddUpper(value.Parameters[1], convertValue, convertIdentifier); if (value.Parameters[1] != null && value.Parameters[1].GetType() == typeof(string)) { par2 = par2.Replace(UserLikeAnyStringSymbol, QueryLikeAnyStringSymbol).Replace(UserLikeAnyCharacterSymbol, QueryLikeAnyCharacterSymbol); } return(string.Format("( {0} like {1} )", par1, par2)); case "OR": case "AND": case "+": case "*": case "-": case "/": case "<": case "<=": case "=": case ">=": case ">": case "<>": string[] pars = null; pars = new string[value.Parameters.Count]; for (int i = 0; i < pars.Length; i++) { pars[i] = string.Empty; if (value.Parameters[i] is VariableDef) { var vdf = (VariableDef)value.Parameters[i]; Type vdType = vdf.Type.NetCompatibilityType; if (vdType.IsGenericType && vdType.GetGenericTypeDefinition() == typeof(Nullable <>)) { vdType = Nullable.GetUnderlyingType(vdType); } if (vdType == LanguageDef.BoolType.NetCompatibilityType) { String funcStringed = value.FunctionDef.StringedView; // Если не применена операция "=" или "<>" для двух булевых типов. if (!(((funcStringed == "=") || (funcStringed == "<>")) && (pars.Length == 2))) { pars[i] = string.Format("(({0}={1}))", convertIdentifier(vdf.StringedView), convertValue(true)); } } } Type parType = null; if (value.Parameters[i] != null) { parType = value.Parameters[i].GetType(); if (parType.IsGenericType && parType.GetGenericTypeDefinition() == typeof(Nullable <>)) { parType = Nullable.GetUnderlyingType(parType); } } // Для И и ИЛИ. if ((parType == typeof(bool)) && (value.FunctionDef.StringedView == LanguageDef.funcAND || value.FunctionDef.StringedView == LanguageDef.funcOR)) { pars[i] = string.Format("(({0}={1}))", convertValue(value.Parameters[i]), convertValue(true)); } if (pars[i] == string.Empty) { pars[i] = AddUpper(value.Parameters[i], convertValue, convertIdentifier); } #region Обработка ситуации, когда операндом у функции "=" или "<>" идут функции. if (((value.FunctionDef.StringedView == "=") || (value.FunctionDef.StringedView == "<>")) && (value.Parameters[i] is Function) && ((Function)value.Parameters[i]).FunctionDef.ReturnType.NetCompatibilityType == LanguageDef.BoolType.NetCompatibilityType) { var func = (Function)value.Parameters[i]; String funcStringed = func.FunctionDef.StringedView; String trimmedAllPars = TrimAndUnbrake(pars[i]); // Упрощение запроса: если true, то не нужен case. if ((funcStringed.ToLower() == "true") || (trimmedAllPars == "0")) { pars[i] = "1"; } else if (((funcStringed == funcNOT) && // если функция not (func.Parameters.Count == 1) && // у нее 1 параметр (func.Parameters[0] is Function) && // этот параметр функция (((Function)func.Parameters[0]).FunctionDef.StringedView.ToLower() == "true")) || (trimmedAllPars == "1")) { pars[i] = "0"; } else { pars[i] = String.Format("(case when {0} then 1 else 0 end)", pars[i]); } } #endregion } result = "( " + string.Join(" " + value.FunctionDef.StringedView + " ", pars) + ")"; return(result); case "IN": if (OptimizeINOperator && value.Parameters.Count == 2 && (! (value.Parameters[1] is Function && (value.Parameters[1] as Function).FunctionDef.StringedView == "SQL") )) { pars = new string[value.Parameters.Count]; for (int i = 0; i < pars.Length; i++) { pars[i] = AddUpper(value.Parameters[i], convertValue, convertIdentifier); } return("( " + string.Join(" " + "=" + " ", pars) + ")"); } else if (value.Parameters.Count == 1) { return(SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) + " is null"); } pars = new string[value.Parameters.Count - 1]; for (int i = 1; i < value.Parameters.Count; i++) { pars[i - 1] = AddUpper(value.Parameters[i], convertValue, convertIdentifier); } return("( " + AddUpper(value.Parameters[0], convertValue, convertIdentifier) + " in (" + string.Join(",", pars) + "))"); case "BETWEEN": return("( " + AddUpper(value.Parameters[0], convertValue, convertIdentifier) + " between " + AddUpper(value.Parameters[1], convertValue, convertIdentifier) + " and " + AddUpper(value.Parameters[2], convertValue, convertIdentifier) + ")"); default: throw new Exception("Not found function :" + value.FunctionDef.StringedView); } }
/// <summary> /// Транслировать в SQL переменную /// </summary> /// <param name="value">переменная</param> /// <param name="convertValue">конвертилка выражений</param> /// <param name="convertIdentifier">помещатель в скобки-кавычки</param> /// <returns></returns> protected virtual string SQLTranslVariable(VariableDef value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { return(convertIdentifier(value.StringedView)); }
/// <summary> /// Преобразовать значение в SQL строку /// </summary> /// <param name="function">Функция</param> /// <param name="convertValue">делегат для преобразования констант</param> /// <param name="convertIdentifier">делегат для преобразования идентификаторов</param> /// <returns></returns> public override string FunctionToSql( SQLWhereLanguageDef sqlLangDef, Function value, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { ExternalLangDef langDef = sqlLangDef as ExternalLangDef; if (value.FunctionDef.StringedView == "TODAY") { return("current_timestamp"); } if ( value.FunctionDef.StringedView == "YearPart" || value.FunctionDef.StringedView == "MonthPart" || value.FunctionDef.StringedView == "DayPart") { return(string.Format("EXTRACT ({0} FROM {1})", value.FunctionDef.StringedView.Substring(0, value.FunctionDef.StringedView.Length - 4), langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if ( value.FunctionDef.StringedView == "hhPart" || value.FunctionDef.StringedView == "miPart") { string strView = value.FunctionDef.StringedView == "hhPart" ? "HOUR" : "MINUTE"; return(string.Format("EXTRACT ({0} FROM {1})", strView, langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DayOfWeek") { return(string.Format("EXTRACT ({0} FROM {1})", "ISODOW", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDayOfWeekZeroBased) { return(string.Format("EXTRACT ({0} FROM {1})", "DOW", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == langDef.funcDaysInMonth) { // здесь требуется преобразование из DATASERVICE return(string.Format("DATE_PART('days', DATE_TRUNC('month', to_date('01.{0}.{1}','dd.mm.yyyy')) + '1 MONTH'::INTERVAL - DATE_TRUNC('month', to_date('01.{0}.{1}','dd.mm.yyyy')) )", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "OnlyDate") { return(string.Format("date_trunc('day',{0})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "CurrentUser") { return(string.Format("'{0}'", CurrentUserService.CurrentUser.FriendlyName)); } if (value.FunctionDef.StringedView == "OnlyTime") { return(string.Format("(to_timestamp(0)+({0} - {0}::date))", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier))); } if (value.FunctionDef.StringedView == "DATEDIFF") { var ret = string.Empty; if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Year") { ret = string.Format("DATE_PART('year', {1}) - DATE_PART('year', {0})", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Month") { ret = string.Format("(DATE_PART('year', {1}) - DATE_PART('year', {0})) * 12 + (DATE_PART('month', {1}) - DATE_PART('month', {0}))", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Week") { ret = string.Format("TRUNC(DATE_PART('day', {1} - {0})/7)", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "Day") { ret = string.Format("DATE_PART('day', {1} - {0})", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } else if (langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier) == "quarter") { ret = string.Format("EXTRACT(QUARTER FROM {1})-EXTRACT(QUARTER FROM {0})+4*(DATE_PART('year', {1}) - DATE_PART('year', {0}))", langDef.SQLTranslSwitch(value.Parameters[1], convertValue, convertIdentifier), langDef.SQLTranslSwitch(value.Parameters[2], convertValue, convertIdentifier)); } return(ret); } if (value.FunctionDef.StringedView == "SUM" || value.FunctionDef.StringedView == "AVG" || value.FunctionDef.StringedView == "MAX" || value.FunctionDef.StringedView == "MIN") { var lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); var dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = new View(); lcs.View.DefineClassType = dvd.View.DefineClassType; lcs.View.AddProperty(dvd.ConnectMasterPorp); var prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; var al = new ArrayList(); var par = langDef.TransformObject(value.Parameters[1], dvd.StringedView, al); foreach (string s in al) { lcs.View.AddProperty(s); } var Slct = GenerateSQLSelect(lcs, false).Replace("STORMGENERATEDQUERY", "SGQ" + Guid.NewGuid().ToString().Replace("-", string.Empty)); var CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); string sumExpression = langDef.SQLTranslSwitch(par, convertValue, convertIdentifier); string res = string.Empty; res = string.Format( "( SELECT {0} From ( " + "SELECT {6}({5}) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), // convertIdentifier(dvd.OwnerConnectProp), Slct, // ВНИМАНИЕ ЗДЕСЬ ТРЕБУЕТСЯ ИЗМЕНИТь ISNULL на вычислитель в определенном DATASERVICE "COALESCE(" + sumExpression + ",0)", value.FunctionDef.StringedView); for (int k = 0; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += "))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcCountWithLimit || value.FunctionDef.StringedView == "Count") { var lcs = new ICSSoft.STORMNET.Business.LoadingCustomizationStruct(null); var dvd = (DetailVariableDef)value.Parameters[0]; lcs.LoadingTypes = new Type[] { dvd.View.DefineClassType }; lcs.View = dvd.View.Clone(); lcs.LimitFunction = value.FunctionDef.StringedView == langDef.funcCountWithLimit ? langDef.TransformVariables((FunctionalLanguage.Function)value.Parameters[1], dvd.StringedView, null) : langDef.GetFunction("True"); var prevRetVars = langDef.retVars; langDef.retVars = new string[] { dvd.ConnectMasterPorp }; var Slct = GenerateSQLSelect(lcs, true); var CountIdentifier = convertIdentifier("g" + Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 29)); var res = string.Format( "( COALESCE( ( SELECT {0} From ( " + "SELECT Count(*) {0},{1} from ( {4} )pip group by {1} ) " + " ahh where {1} in ({3}", CountIdentifier, convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(Information.GetClassStorageName(dvd.View.DefineClassType)), convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[0]), Slct); for (int k = 1; k < dvd.OwnerConnectProp.Length; k++) { res += "," + convertIdentifier("STORMGENERATEDQUERY") + "." + convertIdentifier(dvd.OwnerConnectProp[k]); } res += ")),0))"; langDef.retVars = prevRetVars; return(res); } if (value.FunctionDef.StringedView == langDef.funcToChar) { if (value.Parameters.Count == 2) { return(string.Format( "({0})::varchar({1})", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1])); } if (value.Parameters.Count == 3) { return(string.Format( "(to_char({0}, '{2}')::varchar({1}))", langDef.SQLTranslSwitch(value.Parameters[0], convertValue, convertIdentifier), value.Parameters[1], DateFormats.GetPostgresDateFormat((int)value.Parameters[2]))); } } throw new NotImplementedException(string.Format( "Функция {0} не реализована для Postgres", value.FunctionDef.StringedView)); }
private string GetConditionForExist(Function func, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (!(DataService is SQLDataService)) { throw new Exception(string.Format("Кострукция ограничения {0} поддерживает только SQL сервис данных.", funcExist)); } var dvd = (DetailVariableDef)func.Parameters[0]; string[] agregatorKeys = dvd.OwnerConnectProp.Length == 0 ? new[] { "STORMMainObjectKey" } : dvd.OwnerConnectProp; string detailAlias = "STORMGENERATEDQUERY_S"; string agregatorAlias = "STORMGENERATEDQUERY"; // генерируем подзапрос для exists string selectForCondition = GetSelectForDetailVariableDef(dvd, null); selectForCondition = selectForCondition.Replace(convertIdentifier(agregatorAlias), convertIdentifier(detailAlias)); object conditionFunc = func.Parameters[1]; if (conditionFunc != null) { if (!(conditionFunc is Function)) { // Если функция была задана переменной логического типа, то необходимо сформировать функцию conditionFunc = GetFunction(funcAND, conditionFunc); } else { // Для случаев, когда для обработки детейлового атрибута применяется И, ИЛИ, НЕ из базового языка ((Function)conditionFunc).FunctionDef.Language = this; } conditionFunc = TransformVariables((Function)conditionFunc, dvd.StringedView, new ArrayList()); } // генерируем where часть для подзапроса в exists string whereForConition = SQLTranslFunction((Function)conditionFunc, convertValue, identifier => ConvertIdentifierForDetail(identifier, dvd.ConnectMasterPorp, agregatorAlias, detailAlias, convertIdentifier)); string condition = string.Format("{0} WHERE {1}", selectForCondition, whereForConition); string result = string.Format("exists({4} and {2}.{0} = {3}.{1})", convertIdentifier(agregatorKeys[0]), convertIdentifier(dvd.ConnectMasterPorp), convertIdentifier(agregatorAlias), convertIdentifier(detailAlias), condition); for (int k = 1; k < agregatorKeys.Length; k++) { result += string.Format(" or {0} in (Select {1} from ({2}) ahh)", convertIdentifier(agregatorKeys[k]), convertIdentifier(dvd.ConnectMasterPorp), condition); } return(result); }
private string GetConditionForExistAll(Function func, delegateConvertValueToQueryValueString convertValue, delegatePutIdentifierToBrackets convertIdentifier) { if (!(func.Parameters[1] is Function) || ((Function)func.Parameters[1]).FunctionDef.StringedView != funcEQ && ((Function)func.Parameters[1]).FunctionDef.StringedView != funcIN) { throw new Exception("<" + func.FunctionDef.StringedView + "> parameter1 must be <=> or <in>"); } var conditionFunc = (Function)func.Parameters[1]; Function funcAdv = null; if (func.Parameters.Count > 2) { if (func.Parameters.Count == 3) { funcAdv = (Function)func.Parameters[2]; } else { var advParams = new ArrayList(); for (int i = 2; i < func.Parameters.Count; i++) { advParams.Add(func.Parameters[i]); } funcAdv = GetFunction(funcAND, advParams.ToArray()); } } if (conditionFunc.FunctionDef.StringedView == funcEQ) { var f = new Function(GetFunctionDefByStringedView(funcExist), func.Parameters[0], funcAdv == null ? func.Parameters[1] : GetFunction(funcAND, funcAdv, func.Parameters[1])); return(SQLTranslFunction(f, convertValue, convertIdentifier)); } var vdefDet = func.Parameters[0] as VariableDef; var vdefIn = conditionFunc.Parameters[0] as VariableDef; var newpars = new object[conditionFunc.Parameters.Count - 1]; for (int i = 1; i < conditionFunc.Parameters.Count; i++) { Function funcIn = GetFunction(funcEQ, vdefIn, conditionFunc.Parameters[i]); Function funcOperand = funcAdv == null ? funcIn : GetFunction(funcAND, funcIn, funcAdv); newpars[i - 1] = new Function(GetFunctionDefByStringedView(funcExist), vdefDet, funcOperand); } Function function = GetFunction(funcAND, newpars); return(base.SQLTranslFunction(function, convertValue, convertIdentifier)); }