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