Коллеги, добрый вечер!

Работаю с внешней системой, которая шлет запросы в указанном формате:

curl --location --request POST 'http://localhost/0/ServiceModel/BsoCaller.svc/DoWork' \
--header 'Content-Type: application/xml' \
--data-raw '<?xml version="1.0" encoding="UTF-8"?>
<Event>
</Event>'

Написал анонимный веб-сервис на подобие:

namespace Terrasoft.Configuration.MyNamespace
{
	using System;
	using System.IO;
	using System.Linq;
	using System.Runtime.Serialization;
	using System.Text;
	using System.ServiceModel;
	using System.ServiceModel.Activation;
	using System.ServiceModel.Web;
	using System.Web;
	using System.Xml.XPath;
	using System.Xml.Linq;
	using System.Reflection;
	using Terrasoft.Core;
	using Terrasoft.Common;
	using Terrasoft.Core.Entities;
	using Terrasoft.Web.Common;
 
	[ServiceContract]
	[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
	public class BsoCaller: BaseService 
	{
		[OperationContract]
		[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Xml, BodyStyle = WebMessageBodyStyle.Bare)]
		public void DoWork()
		{
			var xml = Encoding.UTF8.GetString(OperationContext.Current.RequestContext.RequestMessage.GetBody<byte[]>());
		}
	}
}

Пробую отправить запрос через postman в виде xml - получаю Bad Request.

Если выбираю Text - запрос отрабатывает.

Кто-нибудь сталкивался? Где допускаю ошибку?

Спасибо!

Нравится

15 комментариев

Добрый день! 

Первое что бросается в глаза это запрос:

curl --location --request POST 'http://localhost/0/ServiceModel/BsoCaller.svc/DoWorkhttp://localhost/0/…'

Это опечатка в данном посте или на самом деле такая адресная строка?

Руслан Хасанов,

Руслан, добрый день.

Это опечатка в данном посте) Поправил.

Я думаю что адресная строка должна быть примерно такой

http://localhost/0/ServiceModel/MyService/Handler

К тому же метод Handler должен возвращать результат работы, чтобы было что то видно.

Руслан Хасанов,

Руслан, извиняюсь. Подправил названия в веб-сервисе, чтобы было понятнее. Писал просто в торопях в кач-ве примера.

Суть в том, что если я оборачиваю тело в постмане в Text - все отрабатывает. С Xml же какая-то беда.

Хорошо, теперь ближе к правде. Не понятно, конечно, почему такое поведение с Text. Ваш метод DoWork ни чего не возвращает (void).  Я думаю что он должен делать:

return "some text result, for example";

а если конкретно в вашем случае, то return xml; 

И сигнатуру метода надо изменить на public string DoWork.....

Руслан Хасанов,

Руслан, дело в том, что в методе на самом деле не одна строка. Просто сюда решил не скидывать весь код, т.к. уже на этапе инициализации переменной xml возникает проблема.

Дальнейшая логика в методе парсит содержимое переменной xml и создается запись в разделе. И все это работает если слать через Text.

Методу не обязательно что-то возвращать. Если переделать под ваш вариант - к сожалению ничего не поменяется. Все тот же Status: 400 Bad Request в постмане.

Подозреваю что проблема в параметрах, что над методом. Но уже по моему все перепробовал.

Александр, а что именно за Bad Request? Там же в ответе должна быть более подробная информация о причине. Если при этом самодельный веб-сервис упал с ошибкой, её стек должен быть в логах сайта.

Зверев Александр,

Александр, вот что пишет:

The server encountered an error processing the request. The exception message is 'Error in line 2 position 145. Expecting element 'base64Binary' from namespace 'http://schemas.microsoft.com/2003/10/Serialization/'.. Encountered 'Element' with name 'Event', namespace 'http://schema.broadsoft.com/xsi'. '. See server logs for more details. The exception stack trace is:
 
at System.Runtime.Serialization.DataContractSerializer.InternalReadObject(XmlReaderDelegator xmlReader, Boolean verifyObjectName, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.ReadObjectHandleExceptions(XmlReaderDelegator reader, Boolean verifyObjectName, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.ReadObject(XmlDictionaryReader reader) at System.ServiceModel.Channels.Message.GetBodyCore[T](XmlDictionaryReader reader, XmlObjectSerializer serializer) at Terrasoft.Configuration.BsoCesolutions.UsrNewCallHandler.WebhookHandler() at SyncInvokeWebhookHandler(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Подаю в постмане такое тело в xml:

<?xml version="1.0" encoding="UTF-8"?>
<xsi:Event xsi1:type="xsi:SubscriptionEvent" xmlns:xsi="http://schema.broadsoft.com/xsi" xmlns:xsi1="http://www.w3.org/2001/XMLSchema-instance">
    <xsi:eventData xsi1:type="xsi:CallReleasedEvent">
        <xsi:call>
            <xsi:callId>2222222222222222</xsi:callId>
            <xsi:extTrackingId>222222222222</xsi:extTrackingId>
            <xsi:networkCallId>2222222222222222</xsi:networkCallId>
            <xsi:startTime>2222222222222</xsi:startTime>
            <xsi:answerTime>22222222222222</xsi:answerTime>
            <xsi:releaseTime>2222222222222</xsi:releaseTime>
        </xsi:call>
    </xsi:eventData>
</xsi:Event>

Решил переписать немного метод:

[OperationContract]
[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Xml, BodyStyle = WebMessageBodyStyle.Bare)]
public string DoWork()
{	
    using (var reader = OperationContext.Current.RequestContext.RequestMessage.GetReaderAtBodyContents())
    {
        return reader.ReadOuterXml();
    }
}

Теперь ошибки нету, но получаю такое тело в постмане:

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/"/>

А нужно получить по сути тоже самое, что подал. Просто в виде строки.

Вот аналогичный случай, но, смотрю, Вы совет оттуда уже попробовали. Там ещё советуют приводить сразу к нужному типу класса с такими полями.

 

И Вы уверены, что в ответе ReadOuterXml не требуемая строка? Может, теряется уже на обратном пути. Попробуйте для теста писать значение куда-то в базу.

Зверев Александр,

Боюсь привести к нужному типу сразу такой запрос затруднительно.

Еще как выяснилось если писать в базу - строка вообще пустая)

Не сильно теперь понимаю почему постман вообще что-то возвращает.

Пробовал так же пройтись по всем методам из XmlDictionaryReader, но ничего подходящего не нашел.

Александр Горчаков пишет:
Не сильно теперь понимаю почему постман вообще что-то возвращает.

Так это, наверное, пустую строку так сериализировало.

 

 

Зверев Александр,

Да, уже убедился.

Вообщем пока все еще не понятно как нормально отлавливать xml post-запросы в wcf..

Вообще, в системе куча примеров сервисов, но все на Json.

 

А с WebMessageBodyStyle.Bare точно всё правильно? Смотрю тут примеры, Ваш запрос больше на WebMessageBodyStyle.Wrapped похож.

Зверев Александр,

Wrapped пробовал для обоих методов. Результаты аналогичные.

Попробуйте ещё советы из этой темы, в частности, создать сервис для теста сначала не в 7.Х, а в отдельной своей программе и проверить, всё ли нормально в конфиге.

Показать все комментарии

Доброго дня.

Существует деталь "Продукты в заказе". В ней отображены группы, в которых настроены несколько фильтров, и согласно которым подбираются продукты.

После выбора группы и прокрутки отобразившегося списка продуктов, в консоли выводятся запросы, которые система направляет в БД.

По окончанию списка продуктов и прокрутке вниз (в этой же области списка) запросы продолжают отправляться в БД, несмотря на то, что все продукты уже прогружены.
Получается, что система всё-равно пытается найти подходящие продукты, отчего система принимает на 1-2 секунды режим загрузки.

Почему так происходит и должна ли по логике система слать запросы в БД, если все продукты группы уже загружены?

Нравится

7 комментариев

Доброе утро.

Это на последней версии наблюдается такое поведение системы?

Алла Савельева,

доброго дня, Алла.

Версия 7.13.1.769

Не совсем понятно, о каких «запросах в БД», которые «выводятся в консоли» речь. Вы имеете в виду «Инструменты разработчика» в браузере или профайлер из MS SQL?

Если речь о запросах  в браузере к серверу, то на версии 7.14.3 такого поведения не наблюдаю. Попробуйте и Вы развернуть на сайте демо-версию и воспроизвести в ней, чтобы выяснить, дело в более старой версии софта или, может, в каких-то доработках окна выбора.

Зверев Александр,

На сегодняшний день актуальной текущей версией является 7.14.4, поэтому лучше проверять сразу на ней.

Дарья Сошина пишет:
Версия 7.13.1.769

Используемая Вами версия устаревшая, поэтому рекомендую проверить возникает ли проблема на самой последней версии 7.14.4. Если данная проблема есть и в этой версии, обратитесь в службу поддержки, возможно, что Вы обнаружили не выявленную ранее ошибку. 

Алла, спасибо, когда заказывал демку, её ещё не давали. Сейчас, по идее, совсем скоро должна уже 7.15 появиться.

Зверев Александр,

Здравствуйте, Александр.

Алла Савельева,

Попробовала на демке 7.14.4. Индицент не повторился. В действительности, как Александр подметил, была доработка окна выбора.

Зверев Александр пишет:
Не совсем понятно, о каких «запросах в БД», которые «выводятся в консоли» речь. Вы имеете в виду «Инструменты разработчика» в браузере или профайлер из MS SQL?

В консоли браузера при скролле наблюдала формирование запросов. 

Могла ли доработка так повлиять на поведение системы и можно ли это как-то проверить?

Могла, конечно. Мы же не знаем, что это за доработка. Проверить можно, оставив Вашу  7.13 без неё (например, подняв старый бекап и подключив сайт к нему) или накатываем доработанных схем на 7.14.

Показать все комментарии

Коллеги всем доброго времени суток!
Столкнулся с проблемой, не могу добавить в запрос справочную колонку, при формировании запроса приложение сваливается.
Создал функцию:
 

(function () {
	var SysUserInRole = "SysUserInRole";
	var columns = ["Id", "SysRole.Region"];
	var store = Ext.create("Terrasoft.store.BaseStore",{
		model: SysUserInRole
	});
	var queryConfig = Ext.create("Terrasoft.QueryConfig", {
		modelName: SysUserInRole,
		columns: columns
	});
	var filters = Ext.create("Terrasoft.Filter", {
		property: "SysUser.Id",
		value: Terrasoft.CurrentUserInfo.userId
	});
	store.load({
		queryConfig: queryConfig,
		filters: filters,
		callback: function(records, operation, success) {
			try {
				debugger;
				if(success && records) {
					Terrasoft.WaUserInRoles = records;
				} else {
					window.console.info(operation);
					window.console.info(success);
					window.console.info(records);
				}
			} catch(ex) {
				Terrasoft.Mask.hide({force: true});
				Terrasoft.Logger.error(ex, Terrasoft.LogDestination.Console);
				window.console.error(ex);
				return;
			}
		},
		scope: this
	});
}());

Добавил ее в манифест в блок:
 

{
	"ApplicationRequiredModels": [
		"SysAdminUnit",
		"SysUserInRole"
	],
	"CustomSchemas": [
		....
		"WaMobileGetCurrentUserRoles"
	],
}

При выполнении не может определить lockupModel:
 

var lookupModel = Ext.ClassManager.get(columnConfig.modelName);

http://prntscr.com/o3ctl3
 

В чем может быть проблема? Заранее благодарен.

Нравится

1 комментарий

не актуально, разобрался. Нужно было добавить Region в блок манифеста ApplicationRequiredModels

Показать все комментарии

Здравствуйте! Подскажите пожалуйста, есть ли возможность сделать в bpm используя класс Insert, запрос такого вида:

Insert into "имя сторонней базы"."имя таблицы этой базы" (...) values (...);

т.е. сделать инсерт в другую стороннюю базу, или прочитать от туда таблицу. Если да, то как сделать.

Нравится

3 комментария

Скорее всего нет. Особенно, если система в облаке)

Хотя я бы потестил на локальной версии. Пользователю, под которым BPM лезет в бд, выдал права на другую базу, и запрос бы оформил через CustomQuery

Можно создать view с запросом на выборку, триггерами вместо вставки, изменения и удаления в стороннюю базу. Затем создать привязанную к ней схему с такими же колонками и проставленным признаком «представление» и работать с ней так же, как с обычной.

Спасибо за ответы, да я уже примерно так и начал делать, с  использованием тригеров, и т.д.

Показать все комментарии

Версия 7.9.2.2410
Нужно сделать несколько похожих запросов выбора контакта и если какой то вернет ID запустить БП.
Как я понял запросы можно сделать и в БП.
Вот пример запроса:
Выбрать контакта у которого поле1 > значение1
и есть активности где активность.поле1 = значение2
Много запросов я не строил но документации есть полно.
Решил строить и выполнять запрос при нажатии на кнопку,

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
rootSchemaName: "Contact"});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "Id");

var exists = this.Terrasoft.createExistsFilter("[Activity:Contact].Name");
                                       
esq.filters.addItem(exists);

для проверки Запроса должна работать функция esq.GetSelectQuery(), но выдает - esq.getSelectQuery is not a function
будет 4-8 запросов, меняться будет только значение1 и значение2

Нравится

10 комментариев

Добрый день, Виталий!

К сожалению, GetSelectQuery - это серверная функция, вызвать ее можно только в C# коде. На клиентской стороне получить текст запроса в данный момент нельзя.

Используйте метод executeQuery.

"Мотков Илья" написал:К сожалению, GetSelectQuery - это серверная функция, вызвать ее можно только в C# коде.

Подозревал это, а как продебажить код С# в БП?
"Мотков Илья" написал:Используйте метод executeQuery.

Примера я не нашел нигде, ни здесь ни в гугле не нашел примеров и через отладчик.

Делаю через БП, будет 1 запрос, остальное кодом переберу, проблема в добавлении дополнительных фильтров, вот что сейчас есть:

UserConnection userConnection = Get<UserConnection>("UserConnection");
var esqContact = new EntitySchemaQuery(userConnection.EntitySchemaManager, "Contact");
esqContact.AddColumn("Name");
var exists = esqContact.CreateExistsFilter("[Activity:Contact:Id].Id");
var subQueryFilters = new EntitySchemaQueryFilterCollection(esqContact, LogicalOperationStrict.Or);
subQueryFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal, "Result", "b85afbbc-8c0b-472a-8808-680911ceec75"));
exists.SubFilters.AddItem(subQueryFilters);
 
esqContact.Filters.Add(exists);
 
var esqOptions = new EntitySchemaQueryOptions()
{
    PageableDirection = PageableSelectDirection.First,
    PageableRowCount = 1,
    PageableConditionValues = new Dictionary<string, object>()
};
 
var Contact = esqContact.GetEntityCollection(userConnection, esqOptions);
Terrasoft.Configuration.MsgChannelUtilities.PostMessage(userConnection, "CreateActivityForContact", esqContact.GetSelectQuery(userConnection).GetSqlText());
return true;

ошибку выбивает на .SubFilters.AddItem, но я видел примеры таких решений на комюнити и в академии

Решил, если написать так:

UserConnection userConnection = Get<UserConnection>("UserConnection");
var esqContact = new EntitySchemaQuery(userConnection.EntitySchemaManager, "Contact");
esqContact.AddColumn("Name");
 
var GroupFilters = new EntitySchemaQueryFilterCollection(esqContact, LogicalOperationStrict.Or);
GroupFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "[Activity:Contact:Id].Result", "b85afbbc-8c0b-472a-8808-680911ceec75"));
GroupFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "[Activity:Contact:Id].Result",  "6e6b0240-dd96-4db7-b549-2d4c468ae035"));
GroupFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "[Activity:Contact:Id].Result",  "2394c81b-cad7-4bd0-9404-4b72772a8f83"));
GroupFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "[Activity:Contact:Id].Result",  "608a810c-396d-49fd-851b-9e7f39b9dbf7"));
 GroupFilters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "[Activity:Contact:Id].Result",  "c73d5e1f-978c-4f62-9a3f-80d24a5d389d"));
esqContact.Filters.Add(GroupFilters);
 
var Contact = esqContact.GetEntityCollection(userConnection);
Terrasoft.Configuration.MsgChannelUtilities.PostMessage(userConnection, "CreateActivityForContact", esqContact.GetSelectQuery(userConnection).GetSqlText());
return true;

то получится это:

SELECT
	[Contact].[Name] [Name]
FROM
	[dbo].[Contact] [Contact] WITH(NOLOCK)
WHERE
	EXISTS (
SELECT
	[Activity].[Id] [Id]
FROM
	[dbo].[Activity] [Activity] WITH(NOLOCK)
WHERE
	[Contact].[Id] = [Activity].[ContactId]
	AND ([Activity].[ResultId] = @P1
	OR [Activity].[ResultId] = @P2
	OR [Activity].[ResultId] = @P3
	OR [Activity].[ResultId] = @P4
	OR [Activity].[ResultId] = @P5))

как объяснить ошибку?
если я прописал

var esqContact = new EntitySchemaQuery(userConnection.EntitySchemaManager, "Contact");
esqContact.AddColumn("Name");
esqContact.AddColumn("Id");
....
foreach(var Contact in Contacts) {
	 var Contact = Contact.GetTypedColumnValue<Guid>("Id");
}
....

другие поля нормально видит и выбирает

"Радчук Виталий Владимирович" написал:как объяснить ошибку
если я прописал

Приведите весь код, вероятно ошибка не в esqContact.AddColumn("Id");, а в моменте получения данных по этой колонке, и стоит использовать алиас. var contactIdColumnName = esqContact.AddColumn("Name");
и уже вот имя записанное тут contactIdColumnName использовать при получении.

"Радчук Виталий Владимирович" написал:как объяснить ошибку
если я прописал

Приведите весь код, вероятно ошибка не в esqContact.AddColumn("Id");, а в моменте получения данных по этой колонке, и стоит использовать алиас. var contactIdColumnName = esqContact.AddColumn("Name");
и уже вот имя записанное тут contactIdColumnName использовать при получении.

вот код

UserConnection userConnection = Get<UserConnection>("UserConnection");
var esqContact = new EntitySchemaQuery(userConnection.EntitySchemaManager, "Contact");
esqContact.AddColumn("Name");
esqContact.AddColumn("Id");
var UsrCounterTOLLColumn = esqContact.AddColumn("UsrCounterTOLL");
UsrCounterTOLLColumn.OrderByAsc(1);
var CreatedOnLColumn = esqContact.AddColumn("CreatedOn");
CreatedOnLColumn.OrderByAsc(2);
 
esqContact.Filters.Add(esqContact.CreateFilterWithParameters(FilterComparisonType.Equal,
 "UsrContactStage", "b9f5501f-d028-4547-b694-ea7c2dbf8006"));
фильтры
var Contacts = esqContact.GetEntityCollection(userConnection);
foreach(var Contact in Contacts) {
	 var contact = Contact.GetTypedColumnValue<Guid>("Id");
}

нужна именно айдишка для дальнейшего выполнения БП

все получилось:

var IdColumnName = esqContact.AddColumn("Id").Name;
...
Set("Contact",  Contact.GetTypedColumnValue<string>(IdColumnName));
Показать все комментарии

В карточке детали, есть справочное поле: UsrParametr (который строится к справочнику: UsrRefParameter, с полями: Name, Description и UsrStatus из справочника UsrRefStatus, с полями: Name, Description).

Как отфильтровать поле UsrParametr, по имени статуса, т.е. по полю UsrStatus из Справочника UsrRefStatus.

Так же, если можно, объясните сам процесс построения запросов, если не затруднит, прокомментируйте построчно приведенный ниже код, а так же, хотелось бы подробного объяснения, для чего используется в запросе " : " и для чего " . "
Заранее спасибо.
Код:

"ActivityCategory": {
     dataValueType: Terrasoft.DataValueType.LOOKUP,
     dependencies: [
      {
       columns: ["ActivityCategory"],
       methodName: "onActivityCategoryChange"
      }
     ]
    },
    "Result": {
     lookupListConfig: {
      filters: [
       function() {
        var type = this.get("ActivityCategory");
        var filterGroup = Ext.create("Terrasoft.FilterGroup");
        filterGroup.add("ActivityCategory",
         Terrasoft.createColumnFilterWithParameter(
          Terrasoft.ComparisonType.EQUAL,
          "[ActivityCategoryResultEntry:ActivityResult].ActivityCategory",
          type.value));
        filterGroup.add("BusinessProcessOnly",
         Terrasoft.createColumnFilterWithParameter(
          Terrasoft.ComparisonType.EQUAL,
          "BusinessProcessOnly",
          0));
        return filterGroup;
       }
      ]
     }
    },

Нравится

2 комментария

Здравствуйте!

Детализирую:

"ActivityCategory": {
     dataValueType: Terrasoft.DataValueType.LOOKUP,
     dependencies: [
      {
       columns: ["ActivityCategory"],
       methodName: "onActivityCategoryChange"
      }
     ]
    },

При изменении значения в поле ActivityCategory вызывается метод onActivityCategoryChange. Реализация этого метода:

onActivityCategoryChange: function() {
	var activityCategory = this.get("ActivityCategory");
	if (activityCategory && activityCategory.value === ConfigurationConstants.Activity.ActivityCategory.Meeting) {
		this.set("ShowInScheduler", true);
	}
}

Согласно этому методу в поле ShowInScheduler (Отображать в расписании) устанавливается значение true, если выбранная категория - встреча.

Теперь по второй части кода:

    "Result": {
     lookupListConfig: {
      filters: [
       function() {
        var type = this.get("ActivityCategory");
        var filterGroup = Ext.create("Terrasoft.FilterGroup");
        filterGroup.add("ActivityCategory",
         Terrasoft.createColumnFilterWithParameter(
          Terrasoft.ComparisonType.EQUAL,
          "[ActivityCategoryResultEntry:ActivityResult].ActivityCategory",
          type.value));
        filterGroup.add("BusinessProcessOnly",
         Terrasoft.createColumnFilterWithParameter(
          Terrasoft.ComparisonType.EQUAL,
          "BusinessProcessOnly",
          0));
        return filterGroup;
       }
      ]
     }

Данный код фильтрует значения в поле "Результат". Для выбора доступны значения, для которых:

  1. В справочнике ActivityCategoryResultEntry определяются возможные результаты для разных категорий активности. Добавляется фильтрация значений по категории активности
  2. Также не отображаются результаты, для которых уставлено значение "Использовать только в процессах"

Благодарю.

Показать все комментарии

Кто-то знает этот запрос (для чего он, зачем и можно ли оптимизировать):

exec sp_executesql N'SELECT
        [vw_Account].[ID] AS [ID],
        [vw_Account].[Name] AS [Name]
FROM
        [dbo].[vw_Account] AS [vw_Account]
WHERE(EXISTS
        (SELECT
                [vw_Contact].[ID] AS [ID]
        FROM
                [dbo].[vw_Contact] AS [vw_Contact]
        WHERE([vw_Contact].[AccountID] = [vw_Account].[ID] AND
                EXISTS
                (SELECT
                        [tbl_AdminUnit].[ID] AS [ID]
                FROM
                        [dbo].[tbl_AdminUnit] AS [tbl_AdminUnit]
                WHERE([tbl_AdminUnit].[UserContactID] = [vw_Contact].[ID] AND
                        [tbl_AdminUnit].[UserIsEnabled] = @P1)))))
ORDER BY
        2 ASC'
,N'@P1 int',1

Нравится

18 комментариев

Угадайка?

Выбор контрагентов, указанных для активных пользователей системы.
В каком месте хранится искать неохота.

Можно exists убрать и условие ядреное, через inner join как мне кажется... только надо ли?

"Александр Кудряшов" написал:Можно exists убрать

Спасибо, Александр!
К сожалеюнию, пока нельзя убрать, так как не знаю откуда оно формируется. Но когда узнаю, то постараюсь сделать поле "IsUserAccount", в которое запихивать 1 при создании нового юзера с новым юзерКонтрагентом. тада выборка будет проще

SELECT
        [vw_Account].[ID] AS [ID],
        [vw_Account].[Name] AS [Name]
FROM
        [dbo].[vw_Account] AS [vw_Account]
WHERE IsUserAccount IS NOT NULL

Мне кажется отработает раз в 100 быстрее

SELECT
        [vw_Account].[ID] AS [ID],
        [vw_Account].[Name] AS [Name]
FROM
        [dbo].[[tbl_AdminUnit] AS [[tbl_AdminUnit]
inner join [vw_Contact] on [tbl_AdminUnit].[UserContactID] = [vw_Contact].[ID]
inner join [vw_Account] on [vw_Contact].[AccountID] = [vw_Account].[ID]
 
WHERE [tbl_AdminUnit].[UserIsEnabled] = @P1
 
ORDER BY
        2 ASC',N'@P1 int',1

формируется именно из системы, где-то сервис запроса есть, можно его переписать...

а что реально долго работает и тормозит систему???

ps почитал изыскания пытливых умов - вроде как exists в такой ситуации даже быстрее inner'а отработает

затер сообщение. был неправ :lol:

"AlexLS" написал:в которое запихивать 1 при создании нового юзера с новым юзерКонтрагентом

не правильно - надо это поле перезаписывать при изменении признака Активен любого пользователя, привязанного к этому контрагенту и выставлять в 0, если активных пользователей не осталось

"Андросов Дмитрий" написал:не правильно - надо это поле перезаписывать при изменении признака Активен любого пользователя, привязанного к этому контрагенту и выставлять в 0, если активных пользователей не осталось

Дмитрий, спасибо за ответ! Остается малое - выяснить откуда этот зловещий код выполняется!

"Александр Кудряшов" написал:а что реально долго работает и тормозит систему???

к сожалению из "самых страшных" в нашей терре он на 5 месте (остальные прорабатываю), а по частоте выполнения лидирует! То есть суммарный дюрейшен у юзеров зашкаливает!!!

а вообще это похоже на запрос для какого-то справочника типа "Контрагенты ответсвенных"
поищите UserIsEnabled с помощью GREP (у меня есть только в wnd_LicenseManagerScript, но там не то)
вот для сравнения запрос для справочника Ответсвенный (поиск по имени)

exec sp_executesql N'SELECT TOP 2
	[tbl_Contact].[ID] AS [ID],
	[tbl_Contact].[Name] AS [Name]
FROM
	[dbo].[vw_Contact] AS [tbl_Contact]
INNER JOIN
	[dbo].[tbl_AdminUnit] AS [tbl_AdminUnit] ON [tbl_AdminUnit].[UserContactID] = [tbl_Contact].[ID]
WHERE([tbl_AdminUnit].[UserIsEnabled] = @P1 AND
	[tbl_Contact].[Name] LIKE @P2 + ''%'')
ORDER BY
	1 ASC',N'@P1 int,@P2 nvarchar(4000)',1,N'слепо'

"Андросов Дмитрий" написал:но там не то

Я вот тоже не нашел :)
Похожие запросы есть в Users, LicenseManager, но вот точно такого же не было

"Андросов Дмитрий" написал:поищите UserIsEnabled с помощью GREP

к сожалению эта штука ищет только в scr_
а вот в sq_ увы...

"Александр Кудряшов" написал:Похожие запросы есть в Users, LicenseManager, но вот точно такого же не было

хехех. еще бы уметь его находить...

этот запрос выполняется перед function wnd_MainOnPrepare(Window). Как я понимаю, его и ядро юзает...

Выполняется он при старте перед:

exec sp_executesql N'SELECT
	[tbl_SystemSetting].[ID] AS [ID],
	[tbl_SystemSetting].[Code] AS [Code],
	[tbl_SystemSetting].[ValueTypeID] AS [ValueTypeID],
	[tbl_SystemSetting].[StringValue] AS [StringValue],
	[tbl_SystemSetting].[FloatValue] AS [FloatValue],
	[tbl_SystemSetting].[IntegerValue] AS [IntegerValue],
	[tbl_SystemSetting].[BooleanValue] AS [BooleanValue],
	[tbl_SystemSetting].[DateTimeValue] AS [DateTimeValue],
	[tbl_SystemSetting].[DictionaryRecordID] AS [DictionaryRecordID],
	[tbl_SystemSetting].[EnumItemID] AS [EnumItemID],
	[tbl_SystemSetting].[IsCaching] AS [IsCaching]
FROM
	[dbo].[tbl_SystemSetting] AS [tbl_SystemSetting]
WHERE([tbl_SystemSetting].[Code] = @P1)',N'@P1 nvarchar(4000)',N'UpdateActiveSessionPeriod'

Так это... раз уж во все тяжкие (с)
Дебаггер в scr_Main и по шагам идем, ждем появления в профайлере заветных строчек

"Александр Кудряшов" написал:

Так это... раз уж во все тяжкие (с)
Дебаггер в scr_Main и по шагам идем, ждем появления в профайлере заветных строчек


Александр, а есть код выполняющийся перед wnd_MainOnPrepare?

"AlexLS" написал:а есть код выполняющийся перед wnd_MainOnPrepare?

Это уже пограничная область знаний, тут То Самое Ядро может что-то вызывать. Это за пределами моих знаний:smile:
Там раньше окно авторизации и проверка лицензии как раз идет, окно авторизации вроде бы в конфигурации от греха подальше спрятали

"Александр Кудряшов" написал:Там раньше окно авторизации и проверка лицензии как раз идет

она и есть, наверняка, проверка лицензии... Где еще может понадобиться контрагент пользователя? А значит ничего с этим не сделаете. Вам стоит по этому вопросу обращаться напрямую в тех поддержку, причем желательно сразу на кого-нибудь из старожилов с третьей линии выходить :wink: Но, как правило, по всем вопросам, связанным с лицензиями все заканчивается заявлением, что механизм лицензирования закрыт и сакрален :lol:

"Александр Кудряшов" написал:тут То Самое Ядро может что-то вызывать

Александр, вот мне кажется что ядро вызывает банальный sq_ который и поправили под собственные нужды не задумываясь, что оно где-то глобально юзается!!!

Кстати, оно же (запрос) вызывается и Outlook'ом...

"Андросов Дмитрий" написал:по всем вопросам, связанным с лицензиями все заканчивается заявлением, что механизм лицензирования закрыт и сакрален

Дмитрий, мне кажется что лицензии врядли вьюхой проверять будут, скорее всего обращались бы напрямую к tbl_Account и, так же кажется, что разработчики Террасофта, все-таки не настолько ... (тут каждый сам себе додумает), чтобы такой идиотский код. Хотя, возможно, и у них есть отдельные личности, к примеру разработчики лицензирования :smile:
В принципе, в недалекой перспективе, предполагаем переход на TSv7 и эта тайна может так и остаться не разрешенной, хотя кто знает, может на смену мне прийдут новые :biggrin: с этим же вопросом!

Кстати, очередной банальный (уж простите) вопрос: если выгрузить все сервисы в файлы и потом какой-нить утилиткой "поиск текста в xml" пройтись, тогда шансы на поиск увеличатся?

"AlexLS" написал:чтобы такой идиотский код

Ход абсолютно верен.
У простых пользователей НЕТ доступа к tbl_Account, а есть доступ только к vw_Account.
И проверка лицензии не то, ради чего стоит ломать имеющийся движок обращения к данным.

"Александр Кудряшов" написал:И проверка лицензии не то, ради чего стоит ломать имеющийся движок обращения к данным.

Да, может и так... поискал в sq_ по слову "UserIsEnabled" нашлось в sq_AdminUnit, sq_Contact, sq_ContactInTask, sq_User

Судя по Grep-поиску в скриптах и нахождению етого слова в wnd_LicenseManagerScript ... закрываю тему, благо ясно что этот код зашит

Показать все комментарии

Добрый день. Я уже как-то поднимала этот вопрос тут Тогда окончательный я так и не получила. Напомню, что задача была, задавая дату, получить на основе журнала изменений количество продаж в состоянии в работе на эту дату. Эта первоначальная задача решена запросом

SELECT count (ID) FROM tbl_Opportunity
WHERE EXISTS
(SELECT ID FROM
(SELECT top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  FROM tbl_OpportunityLog WHERE
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]AND
[tbl_OpportunityLog].[CreatedOn] :CreatedOn
ORDER BY [tbl_OpportunityLog].[CreatedOn] DESC) AS OpportunityTable
WHERE [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] = :Stage3))
GROUP BY
        [tbl_Opportunity].[Title]

Остался открытым вопрос , как изменить этот запрос, чтобы получить ответ на вопрос "какой был потенциал этих продаж на требуемую дату".
У меня получается вытянуть только сегодняшний потенциал - sum ([tbl_Opportunity].[Cash]) , на все другие изменения запроса sql ругается - sum ([tbl_OpportunityLog].[Cash]), sum([OpportunityTable].[Cash]....
А варианты запросов сразу к журналу изменений приводят к дублированию продаж.

Нравится

7 комментариев

Вот навернула тут inner join, чтобы было можно выбирать данные из журнала изменений.
Остался вопрос (написала его в тексте запроса, чтобы было понятнее)

SELECT [tbl_Opportunity].[Title], 
CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) As CreatedOn, 
sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) as CashToday,
sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [AvgCashLog]
 
FROM tbl_Opportunity inner join [tbl_OpportunityLog] on 
 [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] and
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and
[tbl_OpportunityLog].[StatusName] = :STATUS AND
 ([tbl_OpportunityLog].[StageStage] =:Stage1 OR
 [tbl_OpportunityLog].[StageStage] = :Stage2 OR
 [tbl_OpportunityLog].[StageStage] = :Stage3) 
 
// в ЭТОМ МЕСТЕ НАДО еще добавить условие на выбор записи журнала изменений с МАКСИМАЛЬНОЙ датой CreatedOn. КАК?
 
 
 WHERE exists
(SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue from 
(Select top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  from tbl_OpportunityLog WHERE 
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]and
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
ORDER BY [tbl_OpportunityLog].[CreatedOn] Desc) As OpportunityTable
Where [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] = :Stage3)) 
GROUP BY
	[tbl_Opportunity].[Title], [tbl_Opportunity].[CreatedOn]

Спасибо за Ваше обращение. Мы зарегистрировали его в Службе технической поддержки и назначили специалиста, который сможет Вас компетентно проконсультировать по данному вопросу

where [tbl_OpportunityLog].[CreatedOn] = (select max([CreatedOn]) from [tbl_OpportunityLog])

Два раза подряд WHERE такого sql не понимает.
Скобки тоже не помогли.

Если вообще убрать WHERE и продолжить писать условия через and для выборки INNER JOIN, то получится пустое множество.

Виктория, я демонстрировал конструкцию.
В вашем запросе она должна располагаться так:

SELECT [tbl_Opportunity].[Title], 
CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) AS CreatedOn, 
sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) AS CashToday,
sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [AvgCashLog]
 
FROM tbl_Opportunity INNER JOIN [tbl_OpportunityLog] ON 
 [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] AND
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) AND
[tbl_OpportunityLog].[StatusName] = :STATUS AND
 ([tbl_OpportunityLog].[StageStage] =:Stage1 OR
 [tbl_OpportunityLog].[StageStage] = :Stage2 OR
 [tbl_OpportunityLog].[StageStage] = :Stage3) 
 
 WHERE EXISTS
(SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue FROM 
(SELECT top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  FROM tbl_OpportunityLog WHERE 
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]AND
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
ORDER BY [tbl_OpportunityLog].[CreatedOn] DESC) AS OpportunityTable
WHERE [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] = :Stage3)) AND
 [tbl_OpportunityLog].[CreatedOn] = (SELECT max([CreatedOn]) FROM [tbl_OpportunityLog])
GROUP BY
        [tbl_Opportunity].[Title], [tbl_Opportunity].[CreatedOn]

Так, как Вы написали , тоже же получается пустое множество.

Да и не нужна там эта строка.
После WHERE EXISTS очень правильный запрос уже написан. Он одновременно 1) проверяет существуют ли в журнале изменений записи, дата которых меньше, чем указанная, а состояния равны указанным. Таких записей может быть несколько 2) и выбирает с помощью top (1) CreatedOn и сортировки DESC максимальную из дат , которая удовлетворяет требованию.

Проблема в другом. Проблема в том, что никак не получается узнать какая же Сумма потенциала была на тот момент в этой продаже. Не получается обратиться к OpportunityTable, которая сформирована таким образом.
Чтобы это как-то исправить я добавила INNER JOIN. Те. тут я хочу повторить эту же самую выборку, но таким образом, чтобы я могла к ней обращаться. Поэтому 1) я повторила ограничение по дате, по статусу и по состоянию. 2) Но никак не могу выбрать именно максимальную по дате запись журнала изменений.

ПОЛУЧИЛОСЬ! надо было просто не бояться впихнуть еще один селект

SELECT [tbl_Opportunity].[Title], 
CONVERT(DATE, [tbl_Opportunity].[CreatedOn], 102) As CreatedOn, 
sum([tbl_Opportunity].[Cash])/count([tbl_OpportunityLog].[id]) as CashToday,
sum([tbl_OpportunityLog].[Cash])/count([tbl_OpportunityLog].[id]) AS [CashLogOnDate]
 
FROM tbl_Opportunity inner join [tbl_OpportunityLog] on 
 [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID] and
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and
[tbl_OpportunityLog].[StatusName] =:STATUS AND
 ([tbl_OpportunityLog].[StageStage] =:Stage1 OR
 [tbl_OpportunityLog].[StageStage] = :Stage2 OR
 [tbl_OpportunityLog].[StageStage] = :Stage3)and
 [tbl_OpportunityLog].[CreatedOn] = (SELECT max([tbl_OpportunityLog1].[CreatedOn]) from  [tbl_OpportunityLog] as   [tbl_OpportunityLog1] 
 where  
 [tbl_OpportunityLog1].[RecordID] = [tbl_Opportunity].[ID] and
[tbl_OpportunityLog1].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102) and
[tbl_OpportunityLog1].[StatusName] = :STATUS AND
 ([tbl_OpportunityLog1].[StageStage] =:Stage1 OR
 [tbl_OpportunityLog1].[StageStage] = :Stage2 OR
 [tbl_OpportunityLog1].[StageStage] = :Stage3)
  )
 WHERE exists
(SELECT ID,StatusName, StageStage, Title, RecordID, Cash, Revenue from 
(Select top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  from tbl_OpportunityLog WHERE 
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]and
[tbl_OpportunityLog].[CreatedOn] <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
ORDER BY [tbl_OpportunityLog].[CreatedOn] Desc) As OpportunityTable
Where [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] =:Stage3))
GROUP BY
	[tbl_Opportunity].[Title], [tbl_Opportunity].[CreatedOn]
Показать все комментарии

В процессе администрирования базы данных возникла необходимость определить причину возникновения ошибки. Определенный объём информации импортируется в базу данных, с которым далее пользователи работают. В процессе заполнения определенного набора полей автоматически высчитывалась итоговая сумма в поле «Итого». Но в определённый промежуток времени использования продукта начали появляться ошибки, связанные с несоответствием значения поля «Итого» сумме полей из которых оно вычисляется («Сумма покупки», «Наценка», «Сбор» и т.д.). Так как ошибку не получалось явно повторить, необходимо было разработать механизм для решения данной проблемы.

Естественно самой реальной и первой причиной возникновения такой ошибки приходила идея о сбоях в работе событий полей окна редактирования (то есть значения в полях изменялись, а события данных полей(-я) не срабатывали).

В основу решения было положено создание двух таблиц в базе данных для ведения логов, что происходят с записью набора данных. Первая таблица WindowLog, а вторая TriggerLog.

Первая таблица WindowLog включает в себя поля «Дата создания»(CreatedOn), «Идентификатор записи» (RecordID), «Ответственный» (WindowsUser), «Имя поля породившего событие»(FieldName), «Итого» и поля из которых оно вычисляется («Сумма покупки», «Наценка», «Сбор» и т.д.). Для наполнения таблицы было использованы события невизуального компонента окна dlData: dlDataOnDatasetDataChange, dlDataOnDatasetBeforePost и dlDataOnDatasetAfterPost. В скрипте в событиях была создана функция, которая формировала SQL запрос к таблице WindowLog базы данных с фиксацией информации по указанным полям на момент срабатывания события.

Запрос:

INSERT INTO WindowLog (*набор полей*)
SELECT (*набор полей*) -- Dataset('поле1'), Dataset('поле2'), Dataset('поле2')

Вторая таблица TriggerLog включает в себя поля «Дата создания»(CreatedOn), «Идентификатор записи» (RecordID), «Состояние» (до изменения записи и после), «SystemUser», «Итого» и поля из которых оно вычисляется («Сумма покупки», «Наценка», «Сбор» и т.д.). Для заполнения данной таблицы был создан триггер на инструкцию UPDATE проблемной таблицы с двумя запросами вставки значений в таблицу. В одном запросе вставлялись значения до изменений, а во втором после.

Запрос №1:

INSERT INTO TriggerLog (*набор полей*)       
SELECT (*набор полей*)
FROM deleted

Запрос №2:

INSERT INTO TriggerLog (*набор полей*)       
SELECT (*набор полей*)
FROM inserted

Результатом использования данного решения на основе анализа таблицы WindowLog было установлено, что срабатывают все события окна редактирования, влияющие на вычисление значения поля «Итого». В процессе использования окна редактирования и после сохранения записи значения поля «Итого» были корректны.

Проанализировав записи в таблице TriggerLog было установлено, что в результате выполнения инструкции UPDATE было внесено некорректное значение. Сопоставив даты создания записей в таблице TriggerLog и WindowLog было установлено, что инструкция UPDATE была вызвана не в результате манипуляций с окном редактирования, а иным источником. На основании поля «SystemUser» таблицы TriggerLog было установлено что изменения были внесены с помощью импортера данных.

Таблицу TriggerLog возможно расширить, добавив в нее поля, которые помогут ускорить процесс обнаружение источника изменений записи базы данных. Список дополнительных полей может выгладять следующим образом: ApplicationName, LoginName, HostName.

PS: Принимаю предложения на доработку вашей конфигурации!!! Для более детальной информации можно связаться по следующему e-mail адресу: providnui@ukr.net !!!

В случае возникновения дополнительных вопрос по теме могу поделиться более детальной информацией.

Всем удачи в этом не легком процессе!!!

Нравится

0 комментариев
Показать все комментарии

Помогите пожалуйста с запросом.
Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 1, Стадии 2 или Стадии 3 было.
Таблица Продажи логируется. Соответственно, возникла мысль, что надо делать следующий по логике запрос:
1) выбрать все продажи,
2) выбрать связанные с этими продажами записи журнала изменений,
3) сделать фильтр - дата записи журнала изменений меньше, чем заданная пользователем дата
4) выбрать среди них запись с максимальной датой
5) проверить является ли поле состояние этой записи = "в работе", а также равняется ли поле Стадия одной из трех указанных стадий.

Делаю следующий запрос. В результате получаю неправильный результат: находятся не последние записи журнала изменений, а все, у которых состояние = в работе, а стадия = одной из 3х необходимых стадий.
Как исправить ошибку?

SELECT
[tbl_Opportunity].[Title] AS [Title],
MAX(CAST(CONVERT(VARCHAR(8), [tbl_OpportunityLog].[CreatedOn], 112) AS DATETIME)) AS [CreatedOn],
[tbl_OpportunityLog].[StatusName] AS [StatusName],
[tbl_OpportunityLog].[StageStage] AS [StageStage],
SUM([tbl_OpportunityLog].[Revenue]) AS [Revenue],
SUM([tbl_OpportunityLog].[Cash]) AS [Cash],
CAST(CONVERT(VARCHAR(8), [tbl_Opportunity].[CreatedOn], 112) AS DATETIME) AS [CreatedOnOpportunity]
FROM
[dbo].[tbl_Opportunity] AS [tbl_Opportunity]
LEFT OUTER JOIN
[dbo].[tbl_OpportunityLog] AS [tbl_OpportunityLog] ON [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
WHERE([tbl_Opportunity].[ID] = [tbl_OpportunityLog].[RecordID] AND
[tbl_OpportunityLog].[CreatedOn] [tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3))
GROUP BY
[tbl_Opportunity].[Title],
[tbl_OpportunityLog].[StatusName],
[tbl_OpportunityLog].[StageStage],
CAST(CONVERT(VARCHAR(8), [tbl_Opportunity].[CreatedOn], 112) AS DATETIME)

Нравится

10 комментариев

"Тихенко Виктория" написал:Задача такая: на любой момент времени (заданный пользователем) вывести сколько Продаж в состоянии "в работе" на Стадии 1, Стадии 2 или Стадии 3 было.

select count (ID) from tbl_Opportunity
where exist
(select ID from tbl_OpportunityLog where [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3))

Увы, но так тоже получится выбрать продажи, у которых просто существует где-то во времени запись в журнале изменений "в работе" и одна из трех стадий.
Например, у меня есть Продажа для Клиента.
Журнал изменений:
дата изменений 01.04.2013, в работе, стадия 1.
15.04.2013, в работе, стадия 2.
30.04.2013, отменена, стадия 2.
10.05.2013, в работе, стадия 3.

Так вот, если делать подобный запрос на 01.05.2013 , то продажа попадет в результат запроса. Ведь существует запись журнала, дата изменения которой меньше 01.05.2013, состояние в работе, стадия 2.
Другое дело что существует и запись на 30.04.2013, которая этому требованию уже не удовлетворяет. И 01.05.2013 Продажа для клиента фактически не была в работе.
И надо как-то именно эту запись проверять на состояние и стадию.

надо так попробовать

select count (ID) from tbl_Opportunity
where exist
(select top 1 ID from tbl_OpportunityLog where [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :Status AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3) order by [tbl_OpportunityLog].[CreatedOn] desc)

У меня sql ругается на exist , на второй SELECT , на запись WHERE [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID].

Кажется, что все должно получится, если отсортировать, а потом выбрать первую запись, но не получается никак.

потому что exists (буква s в конце), извините)

Все ближе к цели :smile:
Теперь по-крайней мере , если у меня для какой-то продажи существовало 3 записи в журнале изменений, считается в результат только 1 продажа.
Но все равно Продажа для Клиента из моего примера в результат попадает.
Даже не зависит от того, поставить в конце сортировку DESC или ASC.
Получается что выбирает все же не первую запись, а лишь бы соответствовала условию про состояния и стадии.

будем извращаться дальше)

SELECT count (ID) FROM tbl_Opportunity
WHERE exist
(SELECT top 1 ID FROM 
(Select ID from tbl_OpportunityLog WHERE [tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn AND
[tbl_OpportunityLog].[StatusName] = :STATUS AND
([tbl_OpportunityLog].[StageStage] = :Stage1 OR
[tbl_OpportunityLog].[StageStage] = :Stage2 OR
[tbl_OpportunityLog].[StageStage] = :Stage3) ORDER BY [tbl_OpportunityLog].[CreatedOn] DESC)
)

А вот и правильный ответ!!

SELECT count (ID) FROM tbl_Opportunity 
WHERE exists
(SELECT ID from 
(Select top (1) CreatedOn,StatusName, StageStage, Title, ID, RecordID, Cash, Revenue  from tbl_OpportunityLog WHERE 
[tbl_OpportunityLog].[RecordID] = [tbl_Opportunity].[ID]and
[tbl_OpportunityLog].[CreatedOn] <= :CreatedOn
ORDER BY [tbl_OpportunityLog].[CreatedOn] Desc) As OpportunityTable
Where [OpportunityTable].[StatusName] = :STATUS AND
 ([OpportunityTable].[StageStage] =:Stage1 OR
 [OpportunityTable].[StageStage] = :Stage2 OR
 [OpportunityTable].[StageStage] = :Stage3))
GROUP BY
	[tbl_Opportunity].[Title]

Итак, в запросе получилось наконец-то правильное число продаж, или список этих продаж, если вместо count (ID) написать просто Title. Это хорошо.

Теперь возникает следующая задача - как изменить запрос, чтобы еще и старые данные по сумме потенциала продажи вытянуть?
Продолжая пример: на сегодня Продажа для Клиента имеет Потенциал дохода продажи (поле Cash) = 6000. А в в журнале изменений есть строка 15.04.2013, в работе, стадия 2, Cash =2500.

Те выполняя запрос на 20.04.2013 я хочу получить в списке мою продажу, состояние в работе, стадия 2, и поле Cash = 2500 , а не 6000.
(последний результат я получу, если в первом select написать sum([tbl_Opportunity].[Cash ]).

Пробовала изменить начало запроса так:

SELECT [tbl_Opportunity].[Title], 
sum([OpportunityTableLog].[Cash]) AS [CashLog],
[OpportunityTableLog].[StageStage] AS [StageStageLog] ,
[OpportunityTableLog].[StatusName] AS [StatusNameLog] ,
FROM tbl_Opportunity inner join
(select ID, CreatedOn,RecordID, Cash, StageStage, StatusName, Title from tbl_OpportunityLog) As OpportunityTableLog 
on [OpportunityTableLog].[RecordID] = [tbl_Opportunity].[ID]

В результате предсказуемо получила все подряд записи журнала изменений.
Пробовала также вставить в блок inner join все, что раньше было в блоке exist, но sql ругается :(
Замучил меня этот запрос.

а почему вы вообще привязались к таблице tbl_Opportunity?))
делайте запрос с tbl_OpportunityLog если вам нужны все данные (или большая часть) оттуда

Показать все комментарии