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