/// <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);
}
// *** Start programmer edit section *** (ПроверкаНаличияТоваров CustomMembers)
// *** End programmer edit section *** (ПроверкаНаличияТоваров CustomMembers)
// *** Start programmer edit section *** (OnUpdateЗаказ CustomAttributes)
// *** End programmer edit section *** (OnUpdateЗаказ CustomAttributes)
public virtual ICSSoft.STORMNET.DataObject[] OnUpdateЗаказ(АСУ_Склад.Заказ UpdatedObject)
{
// *** Start programmer edit section *** (OnUpdateЗаказ)
// Определим массив, который будем возвращать для обновления.
DataObject[] ret = new DataObject[0];
// Проверим на то, что пришедший объект - это именно то, что нам нужно (создан или изменён и статус установлен в Оплачено).
if ((UpdatedObject.GetStatus() == ICSSoft.STORMNET.ObjectStatus.Created || UpdatedObject.GetStatus() == ICSSoft.STORMNET.ObjectStatus.Altered) && Array.IndexOf(UpdatedObject.GetAlteredPropertyNames(), "Статус") >= 0 && UpdatedObject.Статус == Состояние_Заказа.Оплаченный)
{
// Построим ограничение и вычитаем все объекты ТоварНаСкладе, которые нам подходят.
Заказ заказ = UpdatedObject;
SQLWhereLanguageDef langdef = SQLWhereLanguageDef.LanguageDef;
ICSSoft.STORMNET.FunctionalLanguage.Function lf = null;
for (int i = 0; i < заказ.Строка_Заказа.Count; i++)
{
if (lf != null)
{
if (заказ.Строка_Заказа[i].Товар != null)
{
lf = langdef.GetFunction(langdef.funcOR, lf, langdef.GetFunction(langdef.funcEQ,
new VariableDef(langdef.GuidType, "Товар"),
заказ.Строка_Заказа[i].Товар.__PrimaryKey));
}
}
else
{
if (заказ.Строка_Заказа[i].Товар != null)
{
lf = langdef.GetFunction(langdef.funcEQ,
new VariableDef(langdef.GuidType, "Товар"),
заказ.Строка_Заказа[i].Товар.__PrimaryKey);
}
}
}
ICSSoft.STORMNET.Business.LoadingCustomizationStruct lcs = ICSSoft.STORMNET.Business.LoadingCustomizationStruct.GetSimpleStruct(typeof(Товар_на_Складе), "ТоварНаСкладеE");
lcs.LimitFunction = lf;
ICSSoft.STORMNET.DataObject[] objs = ICSSoft.STORMNET.Business.DataServiceProvider.DataService.LoadObjects(lcs);
// Разместим вычитанные объекты в отсортированном списке для удобного доступа в дальнейшем.
System.Collections.SortedList sl = new System.Collections.SortedList();
//for (int i = 0; i < objs.Length; i++)
//{
// if (sl.ContainsKey(((Товар_на_Складе)objs[i]).Товар.__PrimaryKey))
// {
// ((System.Collections.ArrayList)sl[objs[i].__PrimaryKey]).Add(objs[i]);
// }
// else
// {
// System.Collections.ArrayList списокТоваров = new System.Collections.ArrayList();
// списокТоваров.Add(objs[i]);
// sl.Add(((Товар_на_Складе)objs[i]).Товар.__PrimaryKey, списокТоваров);
// }
//}
// Определим строчку для сообщения об ошибке.
string errStr = string.Empty;
ArrayList retObjs = new ArrayList();
// Проверим наличие товара на складах, если не хватает, то выдадим сообщение об ошибке, если хватает, то вычитаем количество.
for (int i = 0; i < заказ.Строка_Заказа.Count; i++)
{
if (sl.ContainsKey(заказ.Строка_Заказа[i].Товар.__PrimaryKey))
{
ArrayList arl = ((System.Collections.ArrayList)sl[заказ.Строка_Заказа[i].Товар.__PrimaryKey]);
int количествоНаСкладах = 0; for (int j = 0; j < arl.Count; j++)
{
количествоНаСкладах +=
((Товар_на_Складе)arl[j]).Количество;
}
if (количествоНаСкладах <
заказ.Строка_Заказа[i].Количество)
{
errStr += " Не хватает товара \"" + заказ.Строка_Заказа[i].Товар.Название + "\" в наличии: " + количествоНаСкладах + ", требуется " + заказ.Строка_Заказа[i].Количество + Environment.NewLine;
}
else
{
int колич = заказ.Строка_Заказа[i].Количество;
for (int j = 0; j < arl.Count; j++)
{
if (колич > 0 &&
((Товар_на_Складе)arl[j]).Количество > колич)
{
((Товар_на_Складе)arl[j]).Количество -= колич;
колич = 0; retObjs.Add(arl[j]);
}
else
{
if (колич > 0)
{
колич -= ((Товар_на_Складе)arl[j]).Количество;
((Товар_на_Складе)arl[j]).Количество = 0;
retObjs.Add(arl[j]);
}
}
}
}
}
else
{
errStr += "Товар \""+
заказ.Строка_Заказа[i].Товар.Название + "\" в наличии отсутствует." + Environment.NewLine;
}
}
// В случае, если чего-то не хватило, сообщаем об этом пользователю.
if (errStr != string.Empty)
{
throw new Exception(errStr);
}
// Если всё нормально, то возвращаем массив объектов, которые надо обновить.
ret = new DataObject[retObjs.Count]; retObjs.CopyTo(ret, 0);
}
return(ret);
// *** End programmer edit section *** (OnUpdateЗаказ)
}
/// <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));
}
