566 lines
26 KiB
Python
566 lines
26 KiB
Python
import decimal
|
||
import time
|
||
import numpy as np
|
||
import pyodbc
|
||
|
||
|
||
def sql_connect():
|
||
server = 'prdsql'
|
||
#server = 'cloud.magok.ru'
|
||
database = 'mag_pbi'
|
||
username = 'goglev'
|
||
password = 'BOMoGbUZ1p'
|
||
connection_string = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'
|
||
|
||
conn = pyodbc.connect(connection_string)
|
||
print("successfully connected...")
|
||
print("#" * 20)
|
||
return conn
|
||
|
||
|
||
def get_totals(conn, counter, group):
|
||
sql_query = """
|
||
SELECT --TOP (1000)
|
||
id, -- 0
|
||
artic_id, -- 1
|
||
COALESCE([Количество], 0) AS [Количество], -- 2
|
||
[ЗатратыМП], -- 3
|
||
COALESCE([Закупка], 0) AS [Закупка], -- 4
|
||
COALESCE([Доставка], 0) AS [Доставка], -- 5
|
||
[НДС], -- 6
|
||
COALESCE([Сборка], 0) AS [Сборка], -- 7
|
||
COALESCE([Таможня], 0) AS [Таможня], -- 8
|
||
[Курс usd2] AS [Курс usd2], -- 9
|
||
[Период] AS [Период], -- 10
|
||
[Валюта документа] AS [Валюта документа], -- 11
|
||
[Вид операции] AS [Вид операции], -- 12
|
||
[Статья] AS [Статья], -- 13
|
||
COALESCE([ЗатратыСкладХранение], 0) AS [ЗатратыСкладХранение], -- 14
|
||
COALESCE([Приемка], 0) AS [Приемка], -- 15
|
||
COALESCE([АтсМаркировка], 0) AS [АтсМаркировка], -- 16
|
||
COALESCE([СборкаЗаказа], 0) AS [СборкаЗаказа], -- 17
|
||
COALESCE([Закупка, usd2], 0) AS [Закупка, usd2], -- 18
|
||
COALESCE([Доставка USD2+2], 0) AS [Доставка USD2+2], -- 19
|
||
COALESCE([НДС USD2+2], 0) AS [НДС USD2+2], -- 20
|
||
COALESCE([Таможня USD2+2], 0) AS [Таможня USD2+2], -- 21
|
||
COALESCE([Сборка USD2+2], 0) AS [Сборка USD2+2] -- 22
|
||
FROM
|
||
(
|
||
SELECT
|
||
id,
|
||
artic_id,
|
||
COALESCE([Количество], 0) AS [Количество],
|
||
[ЗатратыМП],
|
||
COALESCE([Закупка], 0) AS [Закупка],
|
||
COALESCE([Доставка], 0) AS [Доставка],
|
||
[НДС],
|
||
COALESCE([Сборка], 0) AS [Сборка],
|
||
COALESCE([Таможня], 0) AS [Таможня],
|
||
[Курс usd2],
|
||
[Период],
|
||
[Валюта документа],
|
||
[Вид операции],
|
||
[Статья],
|
||
[ЗатратыСкладХранение],
|
||
[Приемка],
|
||
[АтсМаркировка],
|
||
[СборкаЗаказа],
|
||
COALESCE([Закупка, usd2], 0) AS [Закупка, usd2],
|
||
COALESCE([Доставка USD2+2], 0) AS [Доставка USD2+2],
|
||
COALESCE([НДС USD2+2], 0) AS [НДС USD2+2],
|
||
COALESCE([Таможня USD2+2], 0) AS [Таможня USD2+2],
|
||
COALESCE([Сборка USD2+2], 0) AS [Сборка USD2+2]
|
||
FROM [mag_pbi].[pbi].[СебестоимостьОт2022]
|
||
WHERE
|
||
[artic_id] IN (SELECT
|
||
[artic_id]
|
||
FROM [mag_pbi].[pbi].[GroupsOfNomenclature]
|
||
where """ + group + """ = """ + str(counter) + """)
|
||
and [Период] >= '2022'
|
||
--and [artic_id] = 'e4532cdc-a827-11e5-80c7-305a3a00e12b'
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
id,
|
||
artic_id,
|
||
COALESCE([Количество], 0),
|
||
[ЗатратыМП],
|
||
COALESCE([Закупка], 0),
|
||
COALESCE([Доставка], 0),
|
||
[НДС],
|
||
COALESCE([Сборка], 0),
|
||
COALESCE([Таможня], 0),
|
||
0,
|
||
'2021',
|
||
NULL,
|
||
'Закупка',
|
||
'Ввод остатков до 22',
|
||
0,
|
||
0,
|
||
0,
|
||
0,
|
||
0,
|
||
0,
|
||
0,
|
||
0,
|
||
0
|
||
FROM [mag_pbi].[pbi].[СебестоимостьИтогиДо2022]
|
||
where [artic_id] IN (SELECT
|
||
[artic_id]
|
||
FROM [mag_pbi].[pbi].[GroupsOfNomenclature]
|
||
where """ + group + """ = """ + str(counter) + """)
|
||
-- and [artic_id] = 'e4532cdc-a827-11e5-80c7-305a3a00e12b'
|
||
) AS tabMain
|
||
--WHERE 1=1
|
||
|
||
ORDER BY
|
||
[artic_id], [Период] ASC, [Закупка] DESC;
|
||
"""
|
||
cursor = conn.cursor()
|
||
cursor.execute(sql_query)
|
||
pbi_totals = cursor.fetchall()
|
||
print(time.ctime(), ' Записей к обработке: ', len(pbi_totals))
|
||
return pbi_totals
|
||
|
||
|
||
def find_sumof_total(sebes_sales, date, current_sales_id, current_params, last_string_values):
|
||
quantity = current_params['quantity']
|
||
sumZakupka = current_params['sumZakupka']
|
||
sumZakupkaUSD2 = current_params['sumZakupkaUSD2']
|
||
sum_dostavka = current_params['sum_dostavka']
|
||
sum_dostavkaUSD2 = current_params['sum_dostavkaUSD2']
|
||
sum_NAT = current_params['sum_NAT']
|
||
sum_NATUSD2 = current_params['sum_NATUSD2']
|
||
sum_sborka = current_params['sum_sborka']
|
||
sum_sborkaUSD2 = current_params['sum_sborkaUSD2']
|
||
sum_zatratiMP = current_params['sum_zatratiMP']
|
||
sum_custom = current_params['sum_custom']
|
||
sum_customUSD2 = current_params['sum_customUSD2']
|
||
sum_skladHranenie = current_params['sum_skladHranenie']
|
||
sum_Priemka = current_params['sum_Priemka']
|
||
sum_AtsMarkirovka = current_params['sum_AtsMarkirovka']
|
||
sum_SborkaZakaza = current_params['sum_SborkaZakaza']
|
||
i = current_params['counter']
|
||
len_sebes_sales = len(sebes_sales)
|
||
while i < len_sebes_sales:
|
||
try:
|
||
curr_str = sebes_sales[i]
|
||
except:
|
||
print('Ошибка в иттераторе, тек ид =', current_sales_id)
|
||
print(i)
|
||
print(len_sebes_sales)
|
||
break
|
||
|
||
if curr_str[10] > date or current_sales_id != curr_str[0]:
|
||
break
|
||
else:
|
||
i += 1
|
||
|
||
if curr_str[12] == 'Закупка' or curr_str[12] == 'Сборка заказа' or curr_str[12] == 'Приемка' or curr_str[12] == 'Атс маркировка':
|
||
quantity += curr_str[2]
|
||
|
||
if curr_str[9] > 0:
|
||
usd2_course = curr_str[9]
|
||
else:
|
||
if curr_str[13] == 'Ввод остатков до 22':
|
||
# для ввода остатков возьём курс на 1 января 2023 года
|
||
usd2_course = 75.7785
|
||
else:
|
||
usd2_course = 1
|
||
|
||
sumZakupka += curr_str[4]
|
||
if curr_str[13] == 'Ввод остатков до 22':
|
||
sumZakupkaUSD2 += decimal.Decimal(float(sumZakupka) / usd2_course)
|
||
else:
|
||
sumZakupkaUSD2 += curr_str[18]
|
||
|
||
|
||
#if curr_str[11] == 'руб.':
|
||
# sumZakupkaUSD += sumZakupka / usd2_course
|
||
|
||
sum_dostavka += curr_str[5]
|
||
sum_dostavkaUSD2 += curr_str[19]
|
||
sum_NAT += curr_str[6]
|
||
sum_NATUSD2 += curr_str[20]
|
||
sum_sborka += curr_str[7]
|
||
sum_sborkaUSD2 += curr_str[22]
|
||
sum_zatratiMP += curr_str[3]
|
||
sum_custom += curr_str[8]
|
||
sum_customUSD2 += curr_str[21]
|
||
sum_skladHranenie += curr_str[14]
|
||
sum_Priemka += curr_str[15]
|
||
sum_AtsMarkirovka += curr_str[16]
|
||
sum_SborkaZakaza += curr_str[17]
|
||
|
||
if curr_str[12] == 'Продажа':
|
||
quantity -= last_string_values['quantity']
|
||
sumZakupka -= last_string_values['sumZakupka']
|
||
sumZakupkaUSD2 -= last_string_values['sumZakupkaUSD2']
|
||
sum_dostavka -= last_string_values['sum_dostavka']
|
||
sum_dostavkaUSD2 -= last_string_values['sum_dostavkaUSD2']
|
||
sum_NAT -= last_string_values['sum_NAT']
|
||
sum_NATUSD2 -= last_string_values['sum_NATUSD2']
|
||
sum_sborka -= last_string_values['sum_sborka']
|
||
sum_sborkaUSD2 -= last_string_values['sum_sborkaUSD2']
|
||
sum_zatratiMP -= last_string_values['sum_zatratiMP']
|
||
sum_custom -= last_string_values['sum_custom']
|
||
sum_customUSD2 -= last_string_values['sum_customUSD2']
|
||
sum_skladHranenie -= last_string_values['sum_skladHranenie']
|
||
sum_Priemka -= last_string_values['sum_Priemka']
|
||
sum_AtsMarkirovka -= last_string_values['sum_AtsMarkirovka']
|
||
sum_SborkaZakaza -= last_string_values['sum_SborkaZakaza']
|
||
|
||
if quantity <= 0:
|
||
quantity = 0
|
||
sumZakupka = 0
|
||
sumZakupkaUSD2 = 0
|
||
sum_dostavka = 0
|
||
sum_NAT = 0
|
||
sum_sborka = 0
|
||
sum_zatratiMP = 0
|
||
sum_custom = 0
|
||
sum_skladHranenie = 0
|
||
sum_Priemka = 0
|
||
sum_AtsMarkirovka = 0
|
||
sum_SborkaZakaza = 0
|
||
sum_dostavkaUSD2 = 0
|
||
sum_NATUSD2 = 0
|
||
sum_sborkaUSD2 = 0
|
||
sum_customUSD2 = 0
|
||
|
||
|
||
return {'quantity': quantity, 'sumZakupka': sumZakupka, 'sumZakupkaUSD2': sumZakupkaUSD2,
|
||
'sum_dostavka': sum_dostavka,
|
||
'sum_NAT': sum_NAT, 'sum_sborka': sum_sborka, 'sum_zatratiMP': sum_zatratiMP, 'sum_custom': sum_custom,
|
||
'sum_skladHranenie': sum_skladHranenie, 'sum_Priemka': sum_Priemka, 'sum_AtsMarkirovka': sum_AtsMarkirovka, 'sum_SborkaZakaza': sum_SborkaZakaza,
|
||
'sum_dostavkaUSD2': sum_dostavkaUSD2, 'sum_NATUSD2': sum_NATUSD2, 'sum_sborkaUSD2': sum_sborkaUSD2, 'sum_customUSD2': sum_customUSD2,
|
||
'counter': i}
|
||
|
||
|
||
|
||
def update_to_DB_Sales(arr, conn, year):
|
||
try:
|
||
cursor = conn.cursor()
|
||
conn.autocommit = False
|
||
cursor.fast_executemany = True
|
||
table = '[СебестоимостьОт2022]'
|
||
if year == '2022':
|
||
table = '[СебестоимостьОт2022]'
|
||
elif year == '2023':
|
||
table = '[Себестоимость2023]'
|
||
elif year == '2024':
|
||
table = '[Себестоимость2024]'
|
||
|
||
cursor.executemany(
|
||
"""UPDATE [mag_pbi].[pbi].""" + table + """
|
||
SET
|
||
[Закупка] = ? -- 0
|
||
, [Закупка, usd2] = ? -- 1
|
||
, [Доставка] = ? -- 2
|
||
, [Сборка] = ? -- 3
|
||
, [НДС] = ? -- 4
|
||
, [ЗатратыМП] = ? -- 5
|
||
, [Таможня] = ? -- 6
|
||
, [ЗатратыСкладХранение] = ? -- 7
|
||
, [Учетная стоимость] = ? -- 8
|
||
, [Учетная стоимость USD2+2] = ? -- 9
|
||
, [Учетная цена] = ? -- 10
|
||
, [Учетная цена USD2+2] = ? -- 11
|
||
, [artic_id] = ? -- 12
|
||
, [Приемка] = ? -- 13
|
||
, [АтсМаркировка] = ? -- 14
|
||
, [СборкаЗаказа] = ? -- 15
|
||
, [Доставка USD2+2] = ? -- 16
|
||
, [НДС USD2+2] = ? -- 17
|
||
, [Таможня USD2+2] = ? -- 18
|
||
, [Сборка USD2+2] = ? -- 19
|
||
WHERE id = ?""",
|
||
arr)
|
||
|
||
|
||
except pyodbc.DatabaseError as err:
|
||
print('ошибка', err)
|
||
conn.rollback()
|
||
else:
|
||
conn.commit()
|
||
print(time.ctime(), 'обновлено', len(arr), ': записей')
|
||
finally:
|
||
conn.autocommit = True
|
||
|
||
|
||
def update_to_DB_sebest(arr, conn, year):
|
||
try:
|
||
cursor = conn.cursor()
|
||
conn.autocommit = False
|
||
cursor.fast_executemany = True
|
||
table = '[СебестоимостьОт2022]'
|
||
if year == '2022':
|
||
table = '[СебестоимостьОт2022]'
|
||
elif year == '2023':
|
||
table = '[Себестоимость2023]'
|
||
elif year == '2024':
|
||
table = '[Себестоимость2024]'
|
||
cursor.executemany(
|
||
"UPDATE [mag_pbi].[pbi]." + table + " SET [Учетная стоимость] = ?, [Учетная стоимость USD2+2] = ?, [Учетная цена] = ?, [Учетная цена USD2+2] = ? WHERE id = ?",
|
||
arr)
|
||
|
||
|
||
except pyodbc.DatabaseError as err:
|
||
print('ошибка', err)
|
||
conn.rollback()
|
||
else:
|
||
conn.commit()
|
||
print(time.ctime(), 'обновлено', len(arr), ': записей')
|
||
finally:
|
||
conn.autocommit = True
|
||
|
||
|
||
def exec_procedure(conn, procedure_name):
|
||
sql_query = 'EXEC ' + procedure_name
|
||
cursor = conn.cursor()
|
||
cursor.execute(sql_query).commit()
|
||
|
||
|
||
def getMaxCounter(conn, group):
|
||
sql_query = "SELECT MAX(" + group + ") FROM [mag_pbi].[pbi].[GroupsOfNomenclature]"
|
||
|
||
cursor = conn.cursor()
|
||
cursor.execute(sql_query)
|
||
maxCounter = cursor.fetchall()
|
||
return maxCounter[0][0]
|
||
|
||
|
||
|
||
def arr_to_nparr(arr):
|
||
return np.asarray(arr)
|
||
|
||
|
||
def count_sebest(connect, counter, group):
|
||
upload = True
|
||
ind = 0
|
||
arr_sebes = []
|
||
arr_sales = []
|
||
last_utid = ''
|
||
pbi_all = get_totals(connect, counter, group)
|
||
nparr_all = arr_to_nparr(pbi_all)
|
||
|
||
for str in pbi_all:
|
||
ind += 1
|
||
date = str[10]
|
||
utid = str[1]
|
||
id = str[0]
|
||
tableGod = str[14]
|
||
zakupkaRUB = round(0, 15)
|
||
zakupkaUSD2 = round(0, 15)
|
||
NAT = round(0, 15)
|
||
NATUSD2 = round(0, 15)
|
||
zatratiMP = round(0, 15)
|
||
sborka = round(0, 15)
|
||
dostavka = round(0, 15)
|
||
dostavkaUSD2 = round(0, 15)
|
||
custom = round(0, 15)
|
||
customUSD2 = round(0, 15)
|
||
skladHranenie = round(0, 15)
|
||
priemka = round(0, 15)
|
||
atsMarkirovka = round(0, 15)
|
||
sborkaZakaza = round(0, 15)
|
||
|
||
|
||
if utid != last_utid:
|
||
fltr = np.asarray([utid])
|
||
pbi_all_current_utid = nparr_all[np.in1d(nparr_all[:, 1], fltr)]
|
||
last_utid = utid
|
||
current_params = {'quantity': 0, 'sumZakupka': 0, 'sumZakupkaUSD2': 0,
|
||
'sum_dostavka': 0, 'sum_NAT': 0, 'sum_sborka': 0, 'sum_zatratiMP': 0, 'sum_custom': 0, 'sum_skladHranenie': 0, 'sum_Priemka': 0,
|
||
'sum_AtsMarkirovka': 0, 'sum_SborkaZakaza': 0, 'sum_dostavkaUSD2': 0, 'sum_NATUSD2': 0, 'sum_sborkaUSD2': 0, 'sum_customUSD2': 0,
|
||
'sum_zakupkaUSD2': 0,
|
||
'counter': 0}
|
||
last_string_values = {'quantity': 0, 'sumZakupka': 0, 'sumZakupkaUSD2': 0,
|
||
'sum_dostavka': 0, 'sum_NAT': 0, 'sum_sborka': 0, 'sum_zatratiMP': 0, 'sum_custom': 0, 'sum_skladHranenie': 0, 'sum_Priemka': 0,
|
||
'sum_AtsMarkirovka': 0, 'sum_SborkaZakaza': 0, 'sum_dostavkaUSD2': 0, 'sum_NATUSD2': 0, 'sum_sborkaUSD2': 0, 'sum_customUSD2': 0,
|
||
'sum_zakupkaUSD2': 0}
|
||
|
||
all_params = find_sumof_total(pbi_all_current_utid, date, str[0], current_params, last_string_values)
|
||
current_params = all_params.copy()
|
||
|
||
sale_quantity = str[2]
|
||
|
||
if str[9] > 0:
|
||
usd2_course = str[9]
|
||
else:
|
||
usd2_course = 1
|
||
|
||
sum_quantity = all_params['quantity']
|
||
sum_zakupka = all_params['sumZakupka']
|
||
sum_zakupkaUSD2 = all_params['sumZakupkaUSD2']
|
||
sum_dostavka = all_params['sum_dostavka']
|
||
sum_dostavkaUSD2 = all_params['sum_dostavkaUSD2']
|
||
sum_NAT = all_params['sum_NAT']
|
||
sum_NATUSD2 = all_params['sum_NATUSD2']
|
||
sum_sborka = all_params['sum_sborka']
|
||
sum_sborkaUSD2 = all_params['sum_sborkaUSD2']
|
||
sum_zatratiMP = all_params['sum_zatratiMP']
|
||
sum_custom = all_params['sum_custom']
|
||
sum_customUSD2 = all_params['sum_customUSD2']
|
||
sum_skladHranenie = all_params['sum_skladHranenie']
|
||
sum_Priemka = all_params['sum_Priemka']
|
||
sum_AtsMarkirovka = all_params['sum_AtsMarkirovka']
|
||
sum_SborkaZakaza = all_params['sum_SborkaZakaza']
|
||
|
||
|
||
if (str[12] == 'Закупка' or str[12] == 'Сборка заказа' or str[12] == 'Приемка' or str[12] == 'Атс маркировка') and str[13] != 'Ввод остатков до 22':
|
||
|
||
uchet_price = 0
|
||
uchet_price_usd22 = 0
|
||
uchet_stoimost = 0
|
||
uchet_stoimost_usd22 = 0
|
||
|
||
zakupkaRUB = round(sum_zakupka, 15)
|
||
zakupkaUSD2 = round(sum_zakupkaUSD2, 15)
|
||
|
||
|
||
if sum_quantity != 0:
|
||
dostavka = round(sum_dostavka, 15)
|
||
dostavkaUSD2 = round(sum_dostavkaUSD2, 15)
|
||
NAT = round(sum_NAT, 15)
|
||
NATUSD2 = round(sum_NATUSD2, 15)
|
||
sborka = round(sum_sborka, 15)
|
||
sborkaUSD2 = round(sum_sborkaUSD2, 15)
|
||
zatratiMP = round(sum_zatratiMP, 15)
|
||
custom = round(sum_custom, 15)
|
||
customUSD2 = round(sum_customUSD2, 15)
|
||
skladHranenie = round(sum_skladHranenie, 15)
|
||
priemka = round(sum_Priemka, 15)
|
||
atsMarkirovka = round(sum_AtsMarkirovka, 15)
|
||
sborkaZakaza = round(sum_SborkaZakaza, 15)
|
||
|
||
uchet_price = round((dostavka + NAT + zakupkaRUB + custom + sborka) / sum_quantity, 15)
|
||
uchet_price_usd22 = round((dostavkaUSD2 + NATUSD2 + zakupkaUSD2 + customUSD2 + sborkaUSD2) / sum_quantity, 15)
|
||
|
||
if sale_quantity != 0:
|
||
uchet_stoimost = round(uchet_price * sale_quantity, 15)
|
||
uchet_stoimost_usd22 = round(uchet_price_usd22 * sale_quantity, 15)
|
||
|
||
|
||
arr_sebes.append((uchet_stoimost, uchet_stoimost_usd22, uchet_price, uchet_price_usd22, id))
|
||
|
||
|
||
elif str[12] == 'Продажа':
|
||
|
||
if sale_quantity != 0:
|
||
|
||
if sum_zakupka < 0:
|
||
sum_zakupka = 0
|
||
|
||
if sale_quantity > sum_quantity or sum_quantity == 0:
|
||
zakupkaRUB = round(sum_zakupka, 15)
|
||
zakupkaUSD2 = round(sum_zakupkaUSD2, 15)
|
||
else:
|
||
zakupkaRUB = round(decimal.Decimal(sum_zakupka) / sum_quantity * sale_quantity, 15)
|
||
zakupkaUSD2 = round(decimal.Decimal(sum_zakupkaUSD2) / sum_quantity * sale_quantity, 15)
|
||
|
||
#zakupkaUSD2 = round(sum_zakupkaUSD2 / usd2_course, 15)
|
||
|
||
if sale_quantity > sum_quantity or sum_quantity == 0:
|
||
dostavka = round(sum_dostavka, 15)
|
||
dostavkaUSD2 = round(sum_dostavkaUSD2, 15)
|
||
NAT = round(sum_NAT, 15)
|
||
NATUSD2 = round(sum_NATUSD2, 15)
|
||
sborka = round(sum_sborka, 15)
|
||
sborkaUSD2 = round(sum_sborkaUSD2, 15)
|
||
zatratiMP = round(sum_zatratiMP, 15)
|
||
custom = round(sum_custom, 15)
|
||
customUSD2 = round(sum_customUSD2, 15)
|
||
skladHranenie = round(sum_skladHranenie, 15)
|
||
priemka = round(sum_Priemka, 15)
|
||
atsMarkirovka = round(sum_AtsMarkirovka, 15)
|
||
sborkaZakaza = round(sum_SborkaZakaza, 15)
|
||
else:
|
||
dostavka = round(sum_dostavka / sum_quantity * sale_quantity, 15)
|
||
dostavkaUSD2 = round(sum_dostavkaUSD2 / sum_quantity * sale_quantity, 15)
|
||
NAT = round(sum_NAT / sum_quantity * sale_quantity, 15)
|
||
NATUSD2 = round(sum_NATUSD2 / sum_quantity * sale_quantity, 15)
|
||
sborka = round(sum_sborka / sum_quantity * sale_quantity, 15)
|
||
sborkaUSD2 = round(sum_sborkaUSD2 / sum_quantity * sale_quantity, 15)
|
||
zatratiMP = round(sum_zatratiMP / sum_quantity * sale_quantity, 15)
|
||
custom = round(sum_custom / sum_quantity * sale_quantity, 15)
|
||
customUSD2 = round(sum_customUSD2 / sum_quantity * sale_quantity, 15)
|
||
skladHranenie = round(sum_skladHranenie / sum_quantity * sale_quantity, 15)
|
||
priemka = round(sum_Priemka / sum_quantity * sale_quantity, 15)
|
||
atsMarkirovka = round(sum_AtsMarkirovka / sum_quantity * sale_quantity, 15)
|
||
sborkaZakaza = round(sum_SborkaZakaza / sum_quantity * sale_quantity, 15)
|
||
|
||
|
||
last_string_values = {'quantity': sale_quantity, 'sumZakupka': zakupkaRUB, 'sumZakupkaUSD2': zakupkaUSD2,
|
||
'sum_dostavka': dostavka,
|
||
'sum_NAT': NAT, 'sum_sborka': sborka, 'sum_zatratiMP': zatratiMP,
|
||
'sum_custom': custom, 'sum_skladHranenie': skladHranenie, 'sum_Priemka': priemka,
|
||
'sum_AtsMarkirovka': atsMarkirovka, 'sum_SborkaZakaza': sborkaZakaza,
|
||
'sum_dostavkaUSD2': dostavkaUSD2, 'sum_NATUSD2': NATUSD2, 'sum_sborkaUSD2': sborkaUSD2, 'sum_customUSD2': customUSD2}
|
||
|
||
if zakupkaRUB != 0 or zakupkaUSD2 != 0 or NAT != 0 or sborka != 0 or dostavka != 0 or custom != 0:
|
||
uchet_stoimost = round((dostavka + NAT + zakupkaRUB + custom + sborka), 15)
|
||
uchet_stoimost_usd22 = round((dostavkaUSD2 + NATUSD2 + zakupkaUSD2 + customUSD2 + sborkaUSD2), 15)
|
||
uchet_price = round((dostavka + NAT + zakupkaRUB + custom + sborka) / sale_quantity, 15)
|
||
uchet_price_usd22 = round((dostavkaUSD2 + NATUSD2 + zakupkaUSD2 + customUSD2 + sborkaUSD2) / sale_quantity, 15)
|
||
|
||
arr_sales.append(
|
||
(float(-zakupkaRUB), float(-zakupkaUSD2), float(-dostavka), float(-sborka), float(-NAT), float(-zatratiMP), float(-custom),
|
||
float(-skladHranenie), float(uchet_stoimost),
|
||
float(uchet_stoimost_usd22), float(uchet_price), float(uchet_price_usd22), utid, float(-priemka), float(-atsMarkirovka), float(-sborkaZakaza),
|
||
float(-dostavkaUSD2), float(-NATUSD2), float(-sborkaUSD2), float(-customUSD2),
|
||
id))
|
||
|
||
# break
|
||
|
||
if ind % 10000 == 0:
|
||
print(time.ctime(), ' ', ind, 'проходов')
|
||
if upload:
|
||
if len(arr_sales) > 0:
|
||
print(time.ctime(), ' записей sales: ', len(arr_sales))
|
||
update_to_DB_Sales(arr_sales, connect, '2022')
|
||
|
||
if len(arr_sebes) > 0:
|
||
print(time.ctime(), ' записей sebest: ', len(arr_sebes))
|
||
update_to_DB_sebest(arr_sebes, connect, '2022')
|
||
|
||
|
||
upload = False
|
||
|
||
|
||
|
||
#флаг для обработки без заполнения таблиц ...
|
||
fill_BuysSells = True
|
||
fill_PriemkaMarkSborka = True
|
||
union_sebes = True
|
||
print('start time', time.ctime())
|
||
connect = sql_connect()
|
||
# заполним таблицы данными закупки ...
|
||
if fill_BuysSells:
|
||
print('Заполнение таблицы данными ~ 14 мин, 8kk записей ...')
|
||
exec_procedure(connect, '[sebest].[Заполнить себестоимостьОт2022(закупки+продажи)]')
|
||
|
||
if fill_PriemkaMarkSborka:
|
||
print('Заполнение таблицы данными ~ 30 мин, 5kk записей ...')
|
||
exec_procedure(connect, '[sebest].[Заполнить себестоимостьОт2022(Сборка)]')
|
||
|
||
|
||
|
||
group = '[2k]'
|
||
max_counter = getMaxCounter(connect, group)
|
||
print('Группа: ', group, ' Всего групп - ', max_counter)
|
||
for counter in range(max_counter):
|
||
counter += 1
|
||
count_sebest(connect, counter, group)
|
||
print(time.ctime(), ' группа ', counter, ' обработана')
|
||
|
||
|
||
# заполним сводную таблицу для дальнейшего использования ...
|
||
if union_sebes:
|
||
print('Объединение в сводную таблицу ~ 10 мин ...')
|
||
exec_procedure(connect, '[pbi].[ЗаполнитьСебестоимостьСводныйОт2022]')
|
||
|
||
|
||
connect.close()
|
||
print('end time ', time.ctime()) |