|
|
(не показано 6 промежуточных версий 2 участников) |
Строка 6: |
Строка 6: |
| | | |
| Особенности отдельных сущностей. | | Особенности отдельных сущностей. |
− | * В интервалах цен Дата конца — всегда не входит. | + | * В интервалах цен Дата конца - всегда не входит. |
| * Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации. | | * Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации. |
| * Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах. | | * Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах. |
| * Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код. | | * Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код. |
− | * На строку продажи действуют две скидки: указанная в продаже (t_sale.discount) и дополнительная, назначенная индивидуально для строки (t_sale_str.discount). | + | * На строку продажи действуют две скидки: указанная в продаже и дополнительная, назначенная индивидуально для строки. |
− | * Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж — дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС. | + | * Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж - дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС. |
| * Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой. | | * Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой. |
− | * Поставки и Продажи имеют два состояния: Черновик (new) и Исполнена (done). При исполнении документа изменяются остатки на складе. Пока документ — черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно. | + | * Поставки и Продажи имеют два состояния: Черновик и Исполнена. При исполнении документа изменяются остатки на складе. Пока документ - черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно. |
− | * Текущий остаток (t_rest) и История остатков (t_rest_hist) содержат текущий остаток на складе. Таблицы заполнены с учетом всех накладных и должны изменяться при их исполнении. В задания входит разработка соовтетствующих процедур, а заполненность таблиц используется в заданиях на запросы.
| + | |
− | * Продажи месяца (t_sale_rep) — таблица месячных отчетов по продажам. Естественно, в реальных системах отчетность устроена сложнее, однако отдельные таблицы для месячной или другой регулярной отчетности — достаточно типичная вещь. Отчет содержит данные за каждый месяц по каждому товару. Отчетные данные представлены как в штуках, так и в денежном выражении, и при построении итоговых отчетов, как правило, используются именно данные в деньгах. Товарный запас на складе оценивается в ценах поставки. Оценка на конец месяца получается следующим образом: к стоимости на начало месяца добавляют сумму закупок за месяц, а затем вычитают стоимость доли товара, которая была продана за месяц: out_sum = (inp_sum+supply_sum) * (1-sale_qty/(inp_qty+supply_qty))
| + | |
| | | |
| == Задачи для запроса данных == | | == Задачи для запроса данных == |
| | | |
− | ;QUERY-01: Найти товары, цены на которые отличаются от цены на модель.
| + | * Найти товары, цены на которые отличаются от цены на модель. |
− | ;QUERY-02: Найти продаже, цены без скидки в которых отличаются от цены на товар.
| + | * Найти продаже, цены без скидки в которых отличаются от цены на товар. |
− | ;QUERY-03: Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла — число нижележащих узлов.
| + | * Проверить гипотезу, что там, где цены продажи отличаются от цены каталога цены в каталоге просто были испорчены. Для этого надо для каждой модели, для которых есть продажи с ценами, отличающимися от цен на товар, посмотреть продажи с совпадающими и несовпадающими ценами. |
− | ;QUERY-04: Вывод таблицы подразделений с отступами в соответствии с уровнями каталога
| + | * Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла - число нижележащих узлов. |
− | ;QUERY-05: Вывод каталога с отступами, для листа с товарами — число моделей и товаров в ним.
| + | * Вывод таблицы подразделений с отступами в соответствии с уровнями каталога |
− | ;QUERY-06: Вывод каталога с отступами, для каждого узла — число моделей и товаров в нем и нижележащих узлах
| + | * Вывод каталога с отступами, для листа с товарами - число моделей и товаров в ним. |
− | ;QUERY-07: Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все — без учета скидок на строки
| + | * Вывод каталога с отступами, для каждого узла - число моделей и товаров в нем и нижележащих узлах |
− | ;QUERY-08: Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label).
| + | * Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все - без учета скидок на строки |
− | ;QUERY-09: Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете — число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные.
| + | * Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label). |
− | ;QUERY-10: Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж.
| + | * Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете - число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные. |
− | ;QUERY-11: Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей
| + | * Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж. |
| + | * Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей |
| | | |
− | ;QUERY-12: Вывод таблицы подразделений, для каждого узла — сумма продаж за заданный месяц. Написать запрос по таблице с отчетом и по исходным документам. Проверить соответствие. | + | ; Пока не готовы данные |
− | ;QUERY-13: Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки. Остатки выбираются из таблицы остатков, а приход и уход — из документов. С помощью отчета проверить таблицу остатков.
| + | * Вывод таблицы подразделений, для каждого узла - стоимость товаров на складе. |
− | ;QUERY-14: Различные отчеты по прибылям от продажи — в разрезе моделей, товарных групп, торговых марок, клиентов и подразделений. Сортировка по максимальной абсолютной и относительной прибыли.
| + | * Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки, выбор наилучших. |
| + | * Отчет по прибылям от продажи - в разрезе товаров и подразделений. |
| + | * Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности. |
| | | |
− | Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности.
| |
| | | |
| == Задачи для программирования == | | == Задачи для программирования == |
| | | |
− | {{caution}} Задачи на программирование правильно начинать с реализации теста, который бы проверял правильность вашей программы. Тест должен вставлять некоторые модельные данные, а потом контролировать, что получен ожидаемый результат. Для тестирования создаются временные записи и для простоты можно использовать фиксированные значения ключей. По соглашению, для специальных целей резервируются малые значения ключей, от 100 до 1000 (до 100 обычно используется для специальных объектов).
| + | * Триггер, блокирующий продажу товара себе в убыток, то есть по цене, меньшей чем имеющаяся сейчас у товара на складе. |
| + | * Триггер, блокирующий установку скидки более 20% для не vip клиентов. |
| + | * Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки. |
| + | * Триггер для строки поставки, автоматически вычисляющий сумму по строкам при изменении цены или количества. |
| + | * Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки - меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице. |
| + | * Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки. |
| + | * Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование. |
| + | * Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур. |
| + | * Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений. |
| + | * Процедура или триггер исполнения поставки, изменяющая количество и стоимость товара на складе. |
| + | * Процедура или триггер исполнения продажи, изменяющая количество и стоимость товара на складе. Необходимо блокировать продаже отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом. |
| | | |
− | Например, если мы рассмотрим задачу по вычислению суммы и НДС поставки при изменении строк, то тест может выглядеть примерно следующим образом.
| |
− | <code-oracle8>
| |
− | CREATE OR REPLACE PROCEDURE test_supply_summa AS
| |
− | l_ware t_ware.id_ware%TYPE := 4289542; /* Товар T203Z2-0050 L Ветровка JACKET COCTEAU темно-синий р.L */
| |
− | l_summa t_supply.summa%TYPE;
| |
− | l_nds t_supply.nds%TYPE;
| |
− | BEGIN
| |
− | INSERT INTO t_supply (id_supply, code, dt, id_supplier)
| |
− | VALUES(101, 'Test-1', DATE'2007-09-01', 1001 /*COLUMBIA*/);
| |
− | dbms_output.put_line('Test insert line');
| |
− | /* В начальном состоянии следующий insert не пройдет: нужен триггер, вычисляющий summa и nds строки */
| |
− | INSERT INTO t_supply_str(id_supply_str, id_supply, num, id_ware, qty, price)
| |
− | VALUES(101, 101, 1, l_ware, 2, 20);
| |
− | SELECT summa, nds INTO l_summa, l_nds
| |
− | FROM t_supply_str
| |
− | WHERE id_supply_str = 101;
| |
− | IF NVL(l_summa, 0) <> 40 OR NVL(l_nds, 0) <> ROUND(40*18/118, 2) THEN
| |
− | raise_application_error(-20001, 'На шаге 1 в строке получено summa='||l_summa||' nds='||l_nds
| |
− | ||', а ожидалось summa=40 nds='||ROUND(40*18/118, 2));
| |
− | END IF;
| |
− | SELECT summa, nds INTO l_summa, l_nds
| |
− | FROM t_supply
| |
− | WHERE id_supply = 101;
| |
− | IF NVL(l_summa, 0) <> 40 OR NVL(l_nds, 0) <> ROUND(40*18/118, 2) THEN
| |
− | raise_application_error(-20001, 'На шаге 1 в заголовке получено summa='||l_summa||' nds='||l_nds
| |
− | ||', а ожидалось summa=40 nds='||ROUND(40*18/118, 2));
| |
− | END IF;
| |
− | dbms_output.put_line('Test update line');
| |
− | UPDATE t_supply_str
| |
− | SET qty = 5
| |
− | WHERE id_supply_str = 101;
| |
− | /* Проверки ... */
| |
− | dbms_output.put_line('Test insert second line');
| |
− | /* ... */
| |
− | dbms_output.put_line('Test delete line');
| |
− | /* ... */
| |
− | dbms_output.put_line('Test completed');
| |
− | END;
| |
− | </code-oracle8>
| |
− | По мере развития теста некоторые части очевидным образом уйдут в процедуры, отдельные или вложенные, а сама процедура может превратиться в пакет.
| |
| | | |
− | Далее приведен набор задач для решения. Задачи разной сложности, и их не следует делать все подряд, правильно выбрать какую-нибудь.
| + | {{replicate-from-custiswiki-to-lib}} |
− | | + | |
− | ;TASK-01: Триггер для строки поставки, автоматически вычисляющий сумму и НДС строки. Следующий шаг — вычисление в триггере суммы и НДС заголовка поставки по изменению в строках.
| + | |
− | ;TASK-02: Триггер, блокирующий установку скидки более 20% для «неVIP»-клиентов.
| + | |
− | ;TASK-03: Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки.
| + | |
− | ;TASK-04: Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки — меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице.
| + | |
− | ;TASK-05: Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки.
| + | |
− | ;TASK-06: Процедура или триггер исполнения поставки и продажи, изменяющая текущее количество товара на складе. Естественно, должна обрабатыватьcя отмена исполнения. Для проверки, помимо теста, можно отменить исполнение всех накладных — остаток должен стать нулевым. После этого — снова исполнить все накладные и проверить, что результат совпал — используя для сравнения таблицу истории остатков.
| + | |
− | ;TASK-07: Доработка предыдущего: при исполнении накладной необходимо блокировать продажу отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом.
| + | |
− | ;TASK-08: Аналогично, триггера или процедуры, но для ведения таблицы истории остатков.
| + | |
− | ;TASK-09: Аналогично, триггера или процедуры, но для ведения таблицы отчетов по продажам.
| + | |
− | ;TASK-10: Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование.
| + | |
− | ;TASK-11: Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур.
| + | |
− | ;TASK-12: Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений.
| + | |
База данных эмулирует географически распределенную торговую компанию, торгующую с центрального склада. Каждое низовое подразделение отвечает за определенный регион (город), где набирает клиентов и организует продажи. Ассортимент товаров, цены и закупки осуществляются централизованно. Часть схемы, относящаяся к закупкам (и остаткам) пока не наполнена данными.
Особенности отдельных сущностей.