Последний выдох ГПЖ,
Garya,
Сейчас я поверхностно опишу структуру существующей БД.
Во первых это представление. Жёлтым выделенно поля из других таблиц, всё остальное для удобства поиска глазами.
Я прокоментирую. Тут есть скрытое поле ID, которое является внешним ключем и тд т тп, его не нужно видеть блондинкам. Надлее артикул который генерится по тригеру после добавления товара.
SQL
1.
UPDATE tblMain SET ProductCode = (SELECT ((select art from tblProductGroups where ProductType = <ProductType>)+'-'+(right ('0000'+(select (ISNULL((select (CAST ((cast((select right((select MAX (ProductCode) from tblMain where ProductType = <ProductType>), 4))as int)+1)as varchar(7)))) , ''))),4)))) WHERE ID = <ID> AND (ProductCode IS NULL OR ProductCode = '')
Далее идёт 3 цены в ГРН, и 3 цены в УЕ, далее 2 поля закупок. Есть 3 таблицы. Поставки, Поставленные товары и Закупочные цены. Первые 2 связанные по типу один ко многим. А третья самая интересная, в неё тригерами вбиваются каждый раз новые цены (для того, что бы не было такой неприятной вещи как представление, которое берёт данные из представление, а оно берёт данные из другого представления. По этому это своеобразные кэш.
Далее 2 колонки которые задают количество в ящике и упаковке.
Далее очень важная вещь которая очень тормозит программу, это Крупный опт от и Опт от. Изначально пользователь задаёт эти 2 значение для кажой позиции, что бы потом программа в зависимости от количества товара в чеке выбирала соответственную цену товара. То есть если цена больше или равно чем в колонке мелкий опт от то будет мелкооптовая, но если она ещё и больше чем в крупный опт от то соответственно будет Крупнооптовая цена. Далее я приведу тригер.
Следующее чудо мысли это сортировка, по который будут все позиции выровнена, это нужно не так сильно для пользователя как для сайта, что бы на сайте товары были в определённом порядке.
Далее идёт Группа и подгруппа товара, тоже нужна для сайта. И для того, что бы задавать Начальные буквы артикулов. На примере если это группа Бла-бла то и артикул будет начинаться типа AR-0000 а не BC-0000. Ну вы поняли.
Далее идёт заповедь пользователя, товары запрещено удалять из программы, так как сами знаете что бывает когда запись с внешним ключем пропадает. Потеряется вся отчетность и собьются артикулы немножко. По этому если товар нужно временно или навсегда убрать с сайта и вида то ставишь галочку и забываешь.
Далее Количество на складах, таких довольно много и для каждого своё значение. Решил не отводить в отдельную таблицу.
Следующее это по поводу УЕ. ТАм где УЕ выставлено, то каждый раз после изменения курса срабатывает триггер который пересчитывает только те цены в которых прописан УЕ, остальное не трогает.
SQL
1.
2.
3.
4.
5.
6.
7.
UPDATE tblMain
SET
Price = (select PriceUSD *(select UAH from tblCurrenciesCourses where ID = (select MAX(ID) from tblCurrenciesCourses))) ,
PriceOut1 = (select PriceOut1USD *(select UAH from tblCurrenciesCourses where ID = (select MAX(ID) from tblCurrenciesCourses))),
PriceOut2 = (select PriceOut2USD *(select UAH from tblCurrenciesCourses where ID = (select MAX(ID) from tblCurrenciesCourses)))
WHERE
PriceUSD IS NOT NUL
L
Это была главная таблица программы, откуда все остальные берут внешний ключ для товара. Мелки таблицы типа таблицы Группы товаров, Курсы валют и тд и тп я не буду обсуждать их довольно много и в них нет ничего интересного
Далее идёт вторая по важности таблица, это таблица продажи. Она к сожалению не доделана по известным причинам, но довольно прожорлива, в ней 20к записей, а в её подтаблице (свяхь 1 ко многим) 120к.
Я не буду её вам показывать, там много данных клиентов и бла-бла, я покажу лишь форму через которую в неё вбиваются данные.
Я не буду углублятся в тонкости, их тут много. Скажу лишь пару вещей. Есть таблица клиенты откуда можно по Коду клиента выбрать нужного. А в этом представлении будет подружаеться его имя и все адреса доставки на которые он заказывал, его все телефоны которые он указывал. Поле курс USD обычно никто не трогает, оно тригером вбивается из другой таблицы, но если клиент "VIP", то его можно изменить и соответственно все цены будут выбиваться дороже или дешевле.
Можно выбирать даты всякие, но одну изменить невозможно, это дата добавления записи. Ну тут короче есть ещё ID заказа, его внешний ключ думаю дальше не особо интересно.
Далее таблица Проданные товары, как помните на 120к записей. Она примичательна количество костылей и тригеров.
Структура таблицы Проданные товары
Тут есть код товара который выбирается из главной таблицы. Поиск нужного товара с учётом времени на ввод названия позиции или её артикула занимает всего 3 сек.
Далее название товара. Далее Количество, думаю ничего особенного.
А теперь идёт серия колонок которая показывает весь тлен и гениальность.
Дело в том, что пользователь может вообще не вбивать цену, она выставляет в зависимости от клиента, всяких данных из таблицы Продажи и в зависимости от Главной таблицы где те 2 поля которые задают минимальные количества для разных цен. Я приведу здесь куски этих триггеров.
Данный триггер Устанавливает цену в У.Е
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
update tblSalesProducts set PriceSaleUSD =
case
WHEN (select ISNULL(PriceUSD,0) from tblMain where id=<ProductID> ) = 0
THEN NULL
WHEN (select Favority from tblClients where ID=<tblSales>.<ClientID>)
THEN (select PriceUSD from tblMain where id=<ProductID>)
when
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) <
(select ISNULL(OptOT,DiscOt) from tblMain where ID=<ProductID>)
THEN
(select PriceOut2USD from tblMain where id=<ProductID>)
WHEN
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) >=
(select ISNULL(OptOT,DiscOt) from tblMain where ID=<ProductID>) and
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) <
(select DiscOt from tblMain where ID=<ProductID> )
THEN
(select PriceOut1USD from tblMain where id=<ProductID>)
when
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) >=
(select DiscOt from tblMain where ID=<ProductID>)
THEN
(select PriceUSD from tblMain where id=<ProductID>)
ELSE
(select PriceOut2USD from tblMain where ID=<ProductID>)
END
where ProductID = <ProductID> and SaleID = <SaleID> and PriceSaleUSD IS NULL and Ordinal = <Ordinal>
Данный триггер устанавливает цену в Грн.
1.
2.
3.
4.
5.
6.
7.
8.
9.
update tblSalesProducts set SalePrice =
case
WHEN ISNULL([PriceSaleUSD],0) = 0
THEN NULL
ELSE (select PriceSaleUSD * (select UAH from tblCurrenciesCourses where ID = (select MAX(ID) from tblCurrenciesCourses)) from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> )
END
where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> and SalePrice IS NULL
Это его вторая часть.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
update tblSalesProducts set SalePrice =
case
WHEN (select Favority from tblClients where ID=<tblSales>.<ClientID>)
THEN (select Price from tblMain where id=<ProductID>)
when
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) <
(select ISNULL(OptOT,DiscOt) from tblMain where ID=<ProductID>)
THEN
(select PriceOut2 from tblMain where id=<ProductID>)
WHEN
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) >=
(select ISNULL(OptOT,DiscOt) from tblMain where ID=<ProductID>) and
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) <
(select DiscOt from tblMain where ID=<ProductID> )
THEN
(select PriceOut1 from tblMain where id=<ProductID>)
when
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal>) >=
(select DiscOt from tblMain where ID=<ProductID>)
THEN
(select Price from tblMain where id=<ProductID>)
ELSE
(select PriceOut2 from tblMain where ID=<ProductID>)
END
where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> and SalePrice IS NULL
Сумма Грн.
ПОЛНАЯ с пересчётом цены у.е. в грн
1.
update tblSalesProducts set Amount = [SalePrice] * [Quantity] where ProductID = <ProductID> and SaleID = <SaleID>
Сумма у.е.
1.
update tblSalesProducts set PriceSaleUSDAmount = [PriceSaleUSD] * [Quantity] where ProductID = <ProductID> and SaleID = <SaleID>
Небольшой костыль
1.
UPDATE tblSales SET SaleAmount = (select sum(Amount) from tblSalesProducts where SaleID = <tblSales>.<ID>) WHERE ID = <tblSales>.<ID>
Сума грн не ПОЛНАЯ без пересчёта у.ев грн.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
update tblSalesProducts SET AmountUe =
case
WHEN ISNULL(<PriceSaleUSD>,0) = 0
THEN
(select SalePrice from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> ) *
(select Quantity from tblSalesProducts where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> )
ELSE NULL
END
where ProductID = <ProductID> and SaleID = <SaleID> and Ordinal = <Ordinal> and PriceSaleUSD IS NULL
Костыльчик
1.
UPDATE tblSales SET SaleAmount = (select sum(Amount)-sum(Amount)*ISNULL(<tblSales>.<Disc>,0) from tblSalesProducts where SaleID = <tblSales>.<ID>) WHERE ID = <tblSales>.<ID>
Это ещё не все триггеры, там есть ещё куча скрытых полей. Я не буду вдаватся в подробности зачем мне АЖ 3 суммы. Кто умный сам поймёт.
Далее там есть ещё серия триггеров, которые вносят изменения в Главную таблицу когда идёт продажа, то с определенного склада отнимается определенное количество но и так далее. Всё защищено, всё скрыто.
Потом идёт серия из кучи триггеров для всяких отчётов и прочего, ОЧЕНЬ ВЫСОКОНАГРУЗОЧНЫХ по этому они все выведеные в отдельную таблицу и призываются при желании. Вот один из них. Он позволяет выгрузить csv документ, для сайта таким образом на сайте обновится все цены, все названия ну вы поняли.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SELECT
ISNULL (ProductCode,'') AS Art ,
Product AS Product,
ISNULL (CAST(Price AS varchar(max)),'') AS Price ,
ISNULL (CAST(PriceOut1 AS varchar(max)),'') AS PriceOpt ,
ISNULL (CAST(PriceOut2 AS varchar(max)),'') AS PriceRoz ,
ISNULL(CAST( CASE WHEN Skrut = 0 THEN 1 ELSE 0 END AS varchar(max)),'')
AS Sklad,
ROW_NUMBER() OVER(ORDER BY orderbyG,orderbyT,Sort ) AS Row,
ISNULL (CAST(V_Y as varchar(max)),'') AS V_Upakovke,
ISNULL (CAST(V_9 as varchar(max)),'') AS V_Yawike,
ISNULL (CAST(Skrut as varchar(max)),'') AS Skrut
FROM
(SELECT NULL AS ProductCode,NULL AS Price,NULL AS PriceOut1,NULL AS PriceOut2, qdfmain.ProductGroup AS Product, tblProductGroups.SortT AS orderbyG,NULL AS orderbyT,NULL AS Sort,NULL AS Na_Sklade ,NULL as V_Y,NULL as V_9, NULL as Skrut
FROM
qdfMain
inner join tblProductGroups on tblProductGroups.ProductGroup = qdfMain.ProductGroup AND tblProductGroups.ProductType = qdfMain.ProductType
UNION
SELECT NULL,NULL,NULL,NULL, '!'+qdfmain.ProductType, tblProductGroups.SortT AS orderbG,tblProductGroups.SortG AS orderbyT,NULL AS Sort, NULL AS Na_Sklade,NULL,NULL, NULL as Skrut
FROM
qdfMain
inner join tblProductGroups on tblProductGroups.ProductGroup = qdfMain.ProductGroup AND tblProductGroups.ProductType = qdfMain.ProductType
UNION
SELECT ProductCode, ISNULL(Price,''), CAST(PriceOut1 as money), CAST(PriceOut2 as money), Product as Product, tblProductGroups.SortT ,tblProductGroups.SortG, Sort, SUM(QuantitySum) AS Na_Sklade, qdfMain.QuantityPerUnit as V_Y, qdfMain.QuantityPerUp as V_9, DeletePr as Skrut
FROM
qdfMain
inner join tblProductGroups on tblProductGroups.ProductGroup = qdfMain.ProductGroup AND tblProductGroups.ProductType = qdfMain.ProductType inner join qdfStoresState on qdfStoresState.ProductID = qdfMain.ID
GROUP BY ProductCode, Price, PriceOut1, PriceOut2, Product, SortT, SortG, Sort, QuantityPerUnit, QuantityPerUp, DeletePr ) a
ORDER BY orderbyG,orderbyT,Sort
Я попытался описать как можно проще структуру БД. Почему мне 1С не понравился, потому что он абсолютно подругому работает. Ну не так, ну абсолютно не так. Я не знаю как это объяснить.
Но если в 1С можно сделать подобный функционал без лишних колонок и кнопок то я только за 1С. А пока я спрашиваю мнение, что мне подойдёт лучше всего.
