Сгорание бонусов , SQL Запрос

[Hendehog]

Привет.
3/28
Есть задача , человек пришел, положил 1000 рублей, получил X бонусов.
Через 2 месяца эти бонусы должны сгореть.
Бонусы у нас реализованы через пакет! (очки)
Как сделать выборку с базы с даты по дату с определенным пакетом , и обнулить эти бонусы?
Я знаю, Neiron , ты нам поможешь :)

[sibgaba]

Аццкие хотелки, аццких маркетологов...
Выбрать вы их допустим сможете (но SQL запрос там будет 3-х этажный). А вот обнулить это не так просто как кажется... Вам понадобится транзакцию записать в БД которая эти бонусы будет списывать, иначе (если их просто удалить) в отчетах потом будет полная хрень...

[SH]

Всегда удивляло. Деньги же потом дешевле будут (инфляция)! А бонусы - повод зайти, даже через год.
Я еще понимаю, когда размер скидки уменьшают, если ходишь редко или мало.

[Hendehog]

Аццкие хотелки, аццких маркетологов...
Выбрать вы их допустим сможете (но SQL запрос там будет 3-х этажный). А вот обнулить это не так просто как кажется... Вам понадобится транзакцию записать в БД которая эти бонусы будет списывать, иначе (если их просто удалить) в отчетах потом будет полная хрень...

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

[sibgaba]

Необходимо одним запросом получается просто выбрать карты с определенным пакетом.
А другим обнулить строку отвечающую за бонусы.
Вы очень поверхностно представляете себе структуру БД...

Попробуем на примере:
01/01/16 на карту № 123 продали пакет "500 денег + 100 бонусов"
1. В БД в таблице Transaction прошли две записи:
123; счет денег; +500
123; счет бонусов; +100

2. Могу попутать с CRM но по моему еще есть табличка в которой хранится баланс (итого) по карте, соответственно там была обновлена запись по карте 123.

3. Гость пошел играть и сыграл 5 игр по цене 110. В Transaction легли записи:
123; счет денег; -110
123; счет денег; -110
123; счет денег; -110
123; счет денег; -110
123; счет денег; -60
123; счет бонусов; -50

Последние 2 записи это 5-я игра, которая была частично оплачена остатками живых денег, а частично бонусами.

Теперь если вы 01/02/16 возьмете и просто удалите из Transaction строку 2-ю строку из п1 "123; счет бонусов; +100", то какой баланс получится у карты? Денег 0, бонусов -50???

Поэтому я и говорю что 01/02/16 нельзя просто отменять начисление бонусов, нужно делать еще одну транзакцию, которой списывать с бонусного счета остаток не израсходованных бонусов. В приведенном примере что то типа:
"123 счет бонусов -50"

Ну а дальше еще сложнее. Если к приведенному примеру добавить что 15/01/16 этот же гость купил еще раз такой же пакет и не играл, то на 01/02/16 у него будет на счету 150 бонусов, из которых списать нужно только 50 (остальным жить еще 2 недели)

Т.е. нам нужно не просто списать все бонусы под ноль, а списать только не израсходованные, которые были начислены 30 дней назад.
Отсюда 3-х этажный SQL запрос.

Если вы настаиваете на том что взять и просто удалить начисление бонусов, то вот вам еще один пример, теперь с отчетами.
Вечером 01/01/16 балансовый отчет будет выглядеть так
Начислено: 500 денег, 100 бонусов
Потрачено 500 денег, 50 бонусов

Когда 01/02/16 вы удалите начисление бонусов и заново построите баланс за 01/01/16 то будет так:
Начислено: 500 денег, 0 бонусов
Потрачено 500 денег, 50 бонусов

Т.е. потрачено больше чем начислено...

[NeiroN]

Нужно одним запросом выбрать из транзакций бонусы внесенные за 1 день 30 дней назад - группируя по номерам карт - для тех карт где бонусы больше нуля. Затем курсором пройтись по этим картам и обнулить на них бонусы, создавая транзакции по обнулению бонусов.

Завтра попробую реализовать на языке MSSQL.

[sibgaba]

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

Затем курсором пройтись по этим картам и обнулить на них бонусы
Не пройдет в случае:

Если к приведенному примеру добавить что 15/01/16 этот же гость купил еще раз такой же пакет и не играл, то на 01/02/16 у него будет на счету 150 бонусов, из которых списать нужно только 50 (остальным жить еще 2 недели)

[NeiroN]

DECLARE @start datetime = DATEADD(day,-31,GETDATE())
DECLARE @stop datetime = DATEADD(day,-30,GETDATE())
DECLARE @date datetime = GETDATE()
DECLARE @unit int = 12--(SELECT [UNIT] FROM [gkArcade].[gk].[UNITS] WHERE [NAME] = 'Очки-Бонусы')
DECLARE @activity int = 329--(SELECT [ACTIVITY] FROM [gkArcade].[gk].[ACTIVITIES] WHERE [NAME] = 'Списание очков с истекшим сроком действия')
DECLARE @t_guid uniqueidentifier
DECLARE @value money, @bonus money, @new_bonus money
DECLARE @card int, @level int, @deal int, @EMPLOYEE int
DECLARE REM_BONUS CURSOR READ_ONLY FOR
SELECT T.[CARD] AS [CARD], C.[Level] AS [LEVEL], T.[EMPLOYEE] AS [EMPLOYEE], C.[Bonus] AS [BONUS]
FROM [gkArcade].[gk].[GK_TRANSACTS] T
LEFT JOIN [gkArcade].[gk].[GK_CARDS] C ON T.[CARD] = C.[Card]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@start AND [DATE]<@stop AND C.[Bonus] > 0
SET @t_guid = newid()
SET @deal = (SELECT TOP 1 [DEAL] FROM [gkArcade].[gk].[GK_TRANSACTS] ORDER BY [DATE] DESC)+1
PRINT @t_guid

OPEN REM_BONUS
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
WHILE (@@FETCH_STATUS <> -1) BEGIN
SET @new_bonus = (
SELECT SUM([VALUE]) FROM [gkArcade].[gk].[GK_TRANSACTS]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@stop AND [DATE]<@date AND [CARD] = @card)
IF @bonus > @new_bonus BEGIN
SET @value = @bonus - @new_bonus
INSERT INTO [gkArcade].[gk].[GK_TRANSACTS]
([DATE]
,[ACTIVITY]
,[CARD]
,[ACCOUNT_TYPE]
,[VALUE]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[DEAL]
,[EMPLOYEE]
,[LEVEL]
,[ARCADE]
,[GUID]
,[TRANSACT_GUID])
VALUES
(@date
,@activity
,@card
,@unit
,-@VALUE
,1
,1
,1
,@deal
,@EMPLOYEE
,@LEVEL
,1
,newid()
,@t_guid)
INSERT INTO [gkArcade].[gk].[TRANSACTS] (
[DATE]
,[ACTIVITY]
,[CARD]
,[EMPLOYEE]
,[VALUE1]
,[UNIT1]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[LEVEL]
,[DEAL]
,[GUID]
,[TRANSACT_GUID])
VALUES(@date,
@activity,
@card,
@EMPLOYEE,
@value,
@unit,
1,
1,
1,
@level,
@deal,
newid(),
@t_guid)
UPDATE [gkArcade].[gk].[GK_CARDS] SET [Bonus] = [Bonus] - @value WHERE [Card] = @card
END
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
END
CLOSE REM_BONUS
Хотя это еще надо править и так далее. Исполнять нужно этот скрипт ночью каждый день
Добавил анализ начисленных бонусов за следующие дни и уменшение по условию если остались бонусы старше 30 дней. Но тут еще большой вопрос из какой таблицы делать выборку - делаю выборку только по картам получившим бонус 30 дней назад - для сокращения количества карт. Хотя первичный проход для удаления всех бонусов старше 30 дней - должен быть другой - с выборкой по всем кртам(часа на 3 запрсик)

P.S. У вас там получается что нужно удалять очки - но принцип тот же получить целевой список карт, посчитать последующие внесения для каждой, выполнить обнуление если внесений менше баланса

[sibgaba]

(но SQL запрос там будет 3-х этажный)

Простите. Был не прав.
8-ми этажный...

​NeiroN - Респект и уважуха...

[Hendehog]

DECLARE @start datetime = DATEADD(day,-31,GETDATE())
DECLARE @stop datetime = DATEADD(day,-30,GETDATE())
DECLARE @date datetime = GETDATE()
DECLARE @unit int = 12--(SELECT [UNIT] FROM [gkArcade].[gk].[UNITS] WHERE [NAME] = 'Очки-Бонусы')
DECLARE @activity int = 329--(SELECT [ACTIVITY] FROM [gkArcade].[gk].[ACTIVITIES] WHERE [NAME] = 'Списание очков с истекшим сроком действия')
DECLARE @t_guid uniqueidentifier
DECLARE @value money, @bonus money, @new_bonus money
DECLARE @card int, @level int, @deal int, @EMPLOYEE int
DECLARE REM_BONUS CURSOR READ_ONLY FOR
SELECT T.[CARD] AS [CARD], C.[Level] AS [LEVEL], T.[EMPLOYEE] AS [EMPLOYEE], C.[Bonus] AS [BONUS]
FROM [gkArcade].[gk].[GK_TRANSACTS] T
LEFT JOIN [gkArcade].[gk].[GK_CARDS] C ON T.[CARD] = C.[Card]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@start AND [DATE]<@stop AND C.[Bonus] > 0
SET @t_guid = newid()
SET @deal = (SELECT TOP 1 [DEAL] FROM [gkArcade].[gk].[GK_TRANSACTS] ORDER BY [DATE] DESC)+1
PRINT @t_guid

OPEN REM_BONUS
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
WHILE (@@FETCH_STATUS <> -1) BEGIN
SET @new_bonus = (
SELECT SUM([VALUE]) FROM [gkArcade].[gk].[GK_TRANSACTS]
WHERE [ACTIVITY]>400 AND [ACTIVITY]<500 AND [DATE]>@stop AND [DATE]<@date AND [CARD] = @card)
IF @bonus > @new_bonus BEGIN
SET @value = @bonus - @new_bonus
INSERT INTO [gkArcade].[gk].[GK_TRANSACTS]
([DATE]
,[ACTIVITY]
,[CARD]
,[ACCOUNT_TYPE]
,[VALUE]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[DEAL]
,[EMPLOYEE]
,[LEVEL]
,[ARCADE]
,[GUID]
,[TRANSACT_GUID])
VALUES
(@date
,@activity
,@card
,@unit
,-@VALUE
,1
,1
,1
,@deal
,@EMPLOYEE
,@LEVEL
,1
,newid()
,@t_guid)
INSERT INTO [gkArcade].[gk].[TRANSACTS] (
[DATE]
,[ACTIVITY]
,[CARD]
,[EMPLOYEE]
,[VALUE1]
,[UNIT1]
,[QUANT]
,[CREATOR]
,[CREATORADDR]
,[LEVEL]
,[DEAL]
,[GUID]
,[TRANSACT_GUID])
VALUES(@date,
@activity,
@card,
@EMPLOYEE,
@value,
@unit,
1,
1,
1,
@level,
@deal,
newid(),
@t_guid)
UPDATE [gkArcade].[gk].[GK_CARDS] SET [Bonus] = [Bonus] - @value WHERE [Card] = @card
END
FETCH NEXT FROM REM_BONUS INTO @card, @level, @EMPLOYEE, @bonus
END
CLOSE REM_BONUS
Хотя это еще надо править и так далее. Исполнять нужно этот скрипт ночью каждый день
Добавил анализ начисленных бонусов за следующие дни и уменшение по условию если остались бонусы старше 30 дней. Но тут еще большой вопрос из какой таблицы делать выборку - делаю выборку только по картам получившим бонус 30 дней назад - для сокращения количества карт. Хотя первичный проход для удаления всех бонусов старше 30 дней - должен быть другой - с выборкой по всем кртам(часа на 3 запрсик)

P.S. У вас там получается что нужно удалять очки - но принцип тот же получить целевой список карт, посчитать последующие внесения для каждой, выполнить обнуление если внесений менше баланса

Спасибо тебе человек.Ты крут.
Мы упростили запрос
Это выборка по пакетам по дате , как это запрос скрестить с выборкой нужного нам конкретного пакета из таблицы?

use gkArcade2
DECLARE @start datetime = '2016-20-01'
DECLARE @stop datetime = '2016-08-02'
DECLARE @date datetime = GETDATE()
DECLARE @t_guid uniqueidentifier
DECLARE @value money, @bonus money, @new_bonus money
DECLARE @card int, @level int, @deal int, @EMPLOYEE int
SELECT T.[CARD] AS [CARD], C.[Level] AS [LEVEL], T.[EMPLOYEE] AS [EMPLOYEE], C.[Bonus] AS [BONUS]
FROM [gkArcade2].[gk].[GK_TRANSACTS] T
LEFT JOIN [gkArcade2].[gk].[GK_CARDS] C ON T.[CARD] = C.[Card]
WHERE [ACTIVITY] = 311 AND [DATE]>@start AND [DATE]<@stop AND C.[Bonus] > 0
SET @t_guid = newid()
SET @deal = (SELECT TOP 1 [DEAL] FROM [gkArcade2].[gk].[GK_TRANSACTS] ORDER BY [DATE] DESC)+1





SELECT * FROM [gkArcade2].[gk].[PACKAGES]

Нам нужен пакет с PACKAGE = 2146624

Заранее Спасибо.

[NeiroN]

Стыдно такое спрашивать ...

WHERE [ACTIVITY] = 311 AND [DATE]>@start AND [DATE]<@stop AND C.[Bonus] > 0 AND [PARAM1]=2146624