Автообновление данных на сервере и пересчёт предшествующего периода

Автор Софья, 13 октября 2015, 11:15:22

« назад - далее »

Софья

Добрый день всем.

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

У меня есть 2 документа, которые входят в одно приложение. Один подгружает суммы, другой чеки за день (во вложениях Documents и Checks соответственно). Изначально думали, что они будут в одном скрипте, но так получилось, что пришлось их разделить. У нас непростое представление даты в системе, и если объединить их в одном коде, то получается наложение, как итог – данные некорректные, задвоенные + обилие синтетических ключей.

Есть также файл, куда отдельно выгружен календарь. Это сделано для того, чтобы каждый раз в скрипте не прогружались лишние поля. Для наглядности прикрепляю таблицу с ассоциированными полями, чтобы Вы видели, как коррелируют эти документы (вложение 1).

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

В QMC я нашла вкладку Reload в разделе Documents, и у меня возник вопрос, как скорректировать данный код, чтобы после обновления у меня появлялись новые дни в приложении?

И чтобы при этом они так же загружались в исторический период, в qvd-файлы?

Вообще, конечная задача выглядит так:

Данные до определённой контрольной точки (даты) могут меняться. Именно поэтому необходимо, чтобы система каждый день в 6 часов утра начинала пересчитывать данные от контрольной точки до сегодняшней даты.

Допустим, контрольные точки – первое число каждого месяца. Если сегодня 13, то система с утра должна пересчитать данные до 11 числа + добавить 12 число. Если 25 октября, то принцип тот же самый. От первого до 23 пересчет + добавление в систему 24 числа, данных за вчера.  Наступает 1 ноября, контрольная точка, система должна пересчитать месяц и на этот раз уже записать его в qvd-файл, в исторические данные, после 1 ноября уже никто не может менять данные.

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

Documents:SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00р.;-# ##0,00р.';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';
SET MonthNames='январь;февраль;март;апрель;май;июнь;июль;август;сентябрь;октябрь;ноябрь;декабрь';
SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
SET vL1Date = "'12.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vL2Date = "'12.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";
SET vDo1Date = "'10.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDo2Date = "'14.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";   

Документы:
LOAD ID AS ID_DOCUMENT,     
TYPE AS ТИП,   
DEPARTMENT AS ID_DEPARTMENT,
PARTN_DOC,
DOC_DATE,
SECOND(DOC_DATE) AS SECOND,           
MINUTE(DOC_DATE) AS MINUTE,
HOUR(DOC_DATE) AS HOUR,                         
DAY(DOC_DATE) AS DAY,
MONTH(DOC_DATE) AS MONTH,
YEAR(DOC_DATE) AS YEAR,
PRODUCT AS ID_PRODUCT,
F15007748 AS ЦЕНА,
F15007746 AS РОЗ_ЦЕНА,
F14286852 AS КОЛВО_ТОВАРА,
LINE_NO AS ЧЕК,
SF AS СУММА_ЗА_ДЕНЬ,
SR AS СУММА_РОЗ,
СКИДКА_В_РУБ,
RAZAND,
BEZNAL AS БЕЗНАЛ,
VOZVRAT AS ВОЗВРАТ,
POZ AS КОЛ_ВО_ПОЗИЦИЙ;     
SQL SELECT --+ INDEX(DB1_DOCUMENT DB1_DOCUMENT_BY_TYPE_DATE)
    D1.ID,
    D1.TYPE,
    D1.DEPARTMENT,
    D1.PARTN_DOC,
    TO_CHAR(TRUNC(L.F14286866), 'dd.mm.yyyy') AS DOC_DATE,
    L.PRODUCT,
    L.F15007748,
    L.F15007746,
    L.F14286852,
    L.LINE_NO,
    L.F15007748*L.F14286852 AS SF,
    L.F15007746*L.F14286852 AS SR,
    L.F15007746*L.F14286852-L.F15007748*L.F14286852 AS СКИДКА_В_РУБ,
    (L.F15007746*L.F14286852-L.F15007748*L.F14286852)*100 AS RAZAND,
    (SELECT CASE WHEN L2.F6684673 = HEXTORAW('0095000101BC0001') THEN 0 ELSE 1 END
    FROM ETK00."DB1_LINE" L2 WHERE L2.DOCUMENT = D1.ID AND L2.TYPE = 40304642 AND L2.LINE_NO = L.LINE_NO AND ROWNUM <= 1) AS BEZNAL,
    (CASE WHEN L.F14286852 > 0 THEN 0 ELSE 1 END) AS VOZVRAT,
    (SELECT COUNT(*) FROM ETK00."DB1_LINE" where DOCUMENT = D1.id and TYPE = 40304641 and LINE_NO = L.LINE_NO) AS POZ
FROM ETK00."DB1_DOCUMENT" D1, ETK00."DB1_LINE" L
WHERE D1.CLASS = 40304641
AND D1.TYPE = 40304641
AND D1.STATE = 1
AND L.TYPE = 40304641
AND D1.ID = L.DOCUMENT
AND D1.DOC_DATE >= TO_DATE($(vDo1Date))
AND D1.DOC_DATE <= TO_DATE($(vDo2Date))
AND L.F14286866 >= TO_DATE($(vL1Date))
AND L.F14286866 <= TO_DATE($(vL2Date));

STORE Документы INTO 12_OCTOBER.qvd (QVD);

Checks:SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00р.;-# ##0,00р.';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';
SET MonthNames='январь;февраль;март;апрель;май;июнь;июль;август;сентябрь;октябрь;ноябрь;декабрь';
SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
SET vL1Date = "'12.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vL2Date = "'12.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";
SET vDo1Date = "'10.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDo2Date = "'14.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";     

Документы:
LOAD ID AS ID_DOCUMENT,     
DOC_DATE,
CCC AS КОЛВОЧЕКОВ;     
SQL SELECT D.ID,
    TRUNC(L.F14286866) AS DOC_DATE,
    COUNT(DISTINCT L.LINE_NO||d.partn_doc) AS CCC
FROM ETK00."DB1_DOCUMENT" D, ETK00."DB1_LINE" L, ETK00."DB1_AGENT" A1
WHERE D.CLASS = 40304641
AND D.TYPE = 40304641
AND L.TYPE = 40304641
AND D.ID = L.DOCUMENT
AND A1.ID = D.DEPARTMENT
AND D.DOC_DATE >= TO_DATE($(vDo1Date))
AND D.DOC_DATE <= TO_DATE($(vDo2Date))
AND L.F14286866 >= TO_DATE($(vL1Date))
AND L.F14286866 <= TO_DATE($(vL2Date))
GROUP BY d.id, TRUNC(L.F14286866);



kvv

Добрый день!
В таких случаях использую функции: now(), today(), MonthEnd.
То есть, если сегодня 13.10.2015, то:
let v_Today = today();
let v_TodayMinus2 = today() - 2;
let v_TodayMinus1 = today() - 1;
let v_MonthEnd = MonthEnd(today());


И дальше уже настроить логику. Если не последний день месяца то менять данные и наоборот.
Как-то так.

Софья

Искренне благодарю, буду пробовать, чуть позже отпишусь о результатах.

Софья


kvv

Могу предположить, что нужно "поиграться" с форматом даты или с кавычками.
Смотрите второй скриншот - у меня так отрабатывает селект.

Софья

Kvv, нашла у себя ошибку. Всё верно Вы сказали, проблема с форматом.

Если функция today() выдаёт дату, то у меня поле DOC_DATE дату хранит в таком виде:

SET vDate1 = "'17.08.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDate2 = "'17.08.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";


В моём случае время обязательно должно быть, т. к. документы не просто содержат дату, но и точное время, допустим, 19.11.2011 9:34:23.

Ваш скрин видела, принцип работы поняла, но я не могу у себя на входе задать переменную, которая содержит today() - ошибку как раз выдаёт ту, о которой писала.

Для наглядности прикреплю код и поясню принцип работы:
SKLAD:
SQL SELECT
    ID,
    CLASS,
    TYPE,
    DEBT_A1 AS ID_DEPARTMENT,
    D2,
    DOC_DATE,
    YEAR,
    MONTH,
    DAYNAMES,
    DAY,
    DEBT_A2 AS ID_PRODUCT,
    DEBT_A3 AS ID_PARTI,
    Z_CENA,
    0 AS FACT_CENA,
    KOL,
    KOL2,
    0 AS SUM_ZAK,
    0 AS SUM_ZAK2,
    0 AS SUM_FACT,
    0 AS SUM_FACT2,
    0 AS МАРЖА,
    0 AS DELIMOE,
    KOL-KOL2 AS OSTATOK   
FROM
(SELECT
    D.ID,
    D.CLASS,
    D.TYPE,
    T.DEBT_A1,
    D.DOC_DATE AS D2,
    TO_CHAR(D.DOC_DATE, 'dd.mm.yyyy') AS DOC_DATE,
    TO_CHAR(D.DOC_DATE, 'yyyy') AS YEAR,
    TO_CHAR(D.DOC_DATE, 'month') AS MONTH,
    TO_CHAR(D.DOC_DATE, 'day') AS DAYNAMES,
    TO_CHAR(D.DOC_DATE, 'dd') AS DAY,
    T.DEBT_A2,
    T.DEBT_A3,
    (SELECT P.F15007745 FROM DB1_PRODUCT P WHERE P.CLASS = 14745602 AND P.ID = T.DEBT_A3) AS Z_CENA,
CASE WHEN D.TYPE = 42402202 AND T.DEBT_A2 IS NOT NULL THEN T.AMOUNT
WHEN D.TYPE = 14286904 AND T.AMOUNT >0 THEN T.AMOUNT
         WHEN D.TYPE IN (3342376, 14286910, 14286911, 15925258, 6684753, 42401843, 14286901, 28835993, 655294492, 28835859, 14286903, 14286906) AND T.AMOUNT >0 THEN T.AMOUNT
         ELSE 0 END AS KOL,
    CASE WHEN D.TYPE = 40042591 AND T.DEBT_A1 IS NOT NULL THEN T.AMOUNT
    WHEN D.TYPE IN (14286904, 14286911, 655294492, 42401843, 14286901) AND T.AMOUNT <0 THEN -T.AMOUNT
    ELSE 0 END AS KOL2
FROM DB1_DOCUMENT D, DB1_TRANSACTION T
WHERE D.CLASS IN (14286850, 14286851)
AND D.TYPE IN (3342376, 14286910, 14286904, 14286911, 15925258, 6684753, 42401843, 14286901, 28835993, 655294492,
14286903, 14286906, 14286858, 14286853, 14286852, 14286862, 42401842, 28835859, 14286850, 3342375, 15925257, 6684754,
15138834, 655294493, 14286859, 42402202, 15138834, 42402075, 40042591, 14286854, 15138835, 57868291, 40042591)
AND D.STATE = 1
AND D.ID = T.DOCUMENT
AND T.CURRENCY = '0002002401AC0001'
AND D.DOC_DATE >= TO_DATE($(vDate1))
AND D.DOC_DATE <= TO_DATE($(vDate2))
)


На входе подаётся дата в формате, который прописан в SET - со временем. Здесь задаю не только дату, но и время, чтобы все документы за сутки вошли.

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

Но вот тут и главная проблема, как мне задать дату на входе? Есть ли подобные функции? Как today(), только чтобы было, допустим, начало дня, и время по умолчанию 0:00:00,  и конец дня, где время было бы 23:59:59?

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


bibis

Добрый день. Честноговоря не очень понял затруднения и почему бы сразу в запросе не использовать
sysdate/current_date

Но по последней части вопроса, думаю подойдет
Timestamp( Today())        и    Timestamp( Today()+0.9999999)        соответственно.


Софья

Цитата: bibis от 20 октября  2015, 09:49:27  
Добрый день. Честно говоря, не очень понял затруднения и почему бы сразу в запросе не использовать
sysdate/current_date

Имеете в виду, в SQL, когда на входе задаю для DOC_DATE переменные? В чём будет их преимущество?

Затруднения, что не понимаю часто, как SQL с QlikView в скрипте соотносить. Не всегда их функции и условия коррелируют. И глубокого понимания QV нет с SQL, разбираюсь понемногу, отсюда и вопросы дотошно-подобные.

Софья

Цитата: kvv от 20 октября  2015, 09:40:42  
Попробуйте функцию now().

А как now() задать/обозначить, чтобы, допустим, от сегодняшней даты и времени, 20.10.2015 14:14:59 отнять период в 10 дней, но не до 14:14:59, а до 23:59:59?

Можно ли прописать другое время. Я так понимаю, что по умолчанию, если просто отниму 10 дней, то вторая дата получится 0 10.10.2015 14:14:59?

И где можно посмотреть информацию доступную по today(), now()  и т. д? В справке всё сухо написано, нюансы подобные не указаны.

bibis

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

Ну а соотносятся они просто, делаем
load  ;  Перед запросом .
И уже внутри этого оператора  творим с полями всё что нужно.

По второму сообщению я выше уже писал формулу, как получить 23:59:
В данном случае =Timestamp( Today()+10.9999999)
А вообще просто поэксперементируйте с датами, округлениями, переводами в числовой формат и т.п. в справке написано достаточно,  нужно просто самому покнопать, чтоб "проникнуться" :)

Софья

Цитата: bibis от 20 октября  2015, 10:19:51  
Ну мне просто непонятно , зачем вам вообще переменные задавать, если на первый взгляд хватит sqlя.

Цель конечная - сделать автоматическое обновление данных на сервере. Постоянно руками переписывать даты - это совсем не то, что нужно. Ну а чтобы подключить ежедневное автообновление в определённое время, нужно задать в скрипте переменные на вход динамичные, как today и today-14 (необходимо перезагружать период данный, т. к. в течение него данные меняются, чтобы корректными были). 

Получается, что из-за своего формата даты со временем я не могу применить today(), ибо он не подразумевает время. А привести данные к формату без времени - не проблема. Проблема их совместить, ибо на входе, когда задаю период для DOC_DATE, нужна дата в формате со временем.

Чувствую, что запутала вас всех. Как объяснить проблему - уже не знаю, вижу, что не могу донести доступно. Беда.


DmitryK

С добрым утром, Софья!

А чем проблема со временем?
Вам надо брать период с 00:00:00 по 23:59:59

Возьмем период с первого по 14 число октября.
Используя today() мы тем самым стираем время и получаем просто дату 01.10.2015 и т.д.
Так 01.10.2015 - это весь день, т.е. берутся данные с 01.10.2015 00:00:00 по 23:59:59. Конечный дата 14.10.2015. Тут аналогично - данные за весь деть. Тем самым мы получаем данные с 01.10.2015 00:00:00 по 14.10.2015 23:59:59.

Т.е. 01.10.2015 00:00:00 <=DATE_DOC <= 14.10.2015 23:59:59
Т.е. DATE_START <=DATE_DOC <= DATE_END

С уважением,
Дмитрий

bibis

Ну так вот я и спрашиваю, почему бы сразу в запросе не использовать
trunc(sysdate) = 00:00 сегодня   и trunc(sysdate)-14=00:00  2 недели назад

Софья

Цитата: bibis от 20 октября  2015, 10:41:21  
Ну так вот я и спрашиваю, почему бы сразу в запросе не использовать
trunc(sysdate) = 00:00 сегодня   и trunc(sysdate)-14=00:00  2 недели назад

Не подумала об этом способе, попробую.

Яндекс.Метрика