def vvodostatka_tovar(cursor, wsdl_client, prm_row_delta):
# оприходование
rows_header = get_vvodostatka_header(cursor, 0, prm_row_delta)
for row_header in rows_header:
if not check_firma(row_header, 0, True) or not check_docid(
row_header, 0) or not check_sklad(row_header, 0):
continue
header = wsdl_client.header_type(
document_type=2,
firma=row_header['firma'].strip(),
sklad=row_header['sklad'].strip(),
client='',
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'])
isclosed = is_process_doc(row_header['closed'])
rows_table = get_vvodostatka_rows(cursor, 0, row_header)
row_list = []
tovar_list = []
for row_table in rows_table:
row_nom = wsdl_client.row_type(tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pdate=row_table['id_pdate'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
if row_table['id_pdate']:
unload_production_date(cursor, wsdl_client,
row_table['bdid_pdate'])
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
prm_id_str=str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа ввод остатка', row_header['docno']]))
n = wsdl_client.client.service.load_vvodostatka_tovar(
document, isclosed, 0)
logging.info(';'.join(
['Загрузка документа ввод остатка', row_header['docno'], n]))
def load_rashod_filial(cursor, wsdl_client, prm_row_header):
row = prm_row_header
logging.warning(row)
isclosed = is_process_doc(row['closed'])
if check_client(row, 1, isclosed):
client_list = []
if not "'" + row['client'] + "'" in client_list:
client_list.append("'" + row['client'] + "'")
if client_list == []:
return
str_id = ",".join(client_list)
get_client_groups_filial(wsdl_client=wsdl_client,
prm_cursor=cursor,
prm_id_list=str_id)
rows_table = get_rashod_rows(cursor, 1, 0, row)
row_list = []
tovar_list = []
header = wsdl_client.header_type(
document_type=2,
firma=cb_firma_id,
sklad=row['sklad'].strip(),
client=row['client'].strip(),
idartmarket=row['idartmarket'].strip(),
document_date=row['datedoc'],
nomerartmarket=row['docno'],
bdid=row['iddoc'].strip(),
bdtype=row['iddocdef'],
skidka_procent=row['skidka'])
logging.info(row['docno'])
for row_table in rows_table:
logging.warning(row_table)
if row_table['idtovar'] is None:
continue
elif not row_table['idtovar'].strip().isdigit():
logging.error(
["Некорректный код товара", row_table['idtovar']])
row_nom = wsdl_client.row_type(
tovar=0,
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
else:
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
logging.warning(str_id)
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=3,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=1)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(
['Загрузка документа расхода', row['docno'], row['datedoc']])
# document_partii_rows = wsdl_client.rows_partii_type(rows=[])
# document_partii = wsdl_client.document_partii_type(rowslist=document_partii_rows)
list_partii = []
if isclosed == 1:
logging.info('Выборка партий расхода')
cursor.execute(
'''
select
SC84.code as idtovar_artmarket, SC84.SP8450 as idtovarfil,
ltrim(rtrim(_1sjourn.iddoc)) as prihodid, _1sjourn.iddocdef as prihodtype,
docno as prihodno,CAST(LEFT(_1sjourn.Date_Time_IDDoc, 8) as DateTime) as prihoddate,
SP342 as ostatok, SP343 as stoimost, SP6818 as prodstoimost, 1 as prodaga
from RA328
left join SC84 WITH (NOLOCK) on RA328.SP331=SC84.id
left join SC214 WITH (NOLOCK) on RA328.SP341=SC214.id
left join _1sjourn WITH (NOLOCK) on ltrim(rtrim(substring(ltrim(rtrim(SP216)),
charindex(' ',ltrim(rtrim(SP216))),100)))=ltrim(rtrim(_1sjourn.iddoc))
where RA328.iddoc=%s
''', row['iddoc'])
logging.info('Выборка партий расхода завершена')
rows_table_partii = cursor.fetchall()
for row_partii in rows_table_partii:
if not row_partii['idtovar_artmarket'].strip().isdigit():
row_nom_partii = wsdl_client.row_partii_type(
tovar=0,
prihod_id=row_partii['prihodid'],
prihod_type=row_partii['prihodtype'],
prihod_no=row_partii['prihodno'],
prihod_date=row_partii['prihoddate'],
ostatok=row_partii['ostatok'],
stoimost=row_partii['stoimost'],
prodstoimost=row_partii['prodstoimost'],
prodaga=row_partii['prodaga'],
tovar_filial=row_partii['idtovarfil'])
else:
row_nom_partii = wsdl_client.row_partii_type(
tovar=row_partii['idtovar_artmarket'],
prihod_id=row_partii['prihodid'],
prihod_type=row_partii['prihodtype'],
prihod_no=row_partii['prihodno'],
prihod_date=row_partii['prihoddate'],
ostatok=row_partii['ostatok'],
stoimost=row_partii['stoimost'],
prodstoimost=row_partii['prodstoimost'],
prodaga=row_partii['prodaga'],
tovar_filial=row_partii['idtovarfil'])
list_partii.append(row_nom_partii)
document_partii_rows = wsdl_client.rows_partii_type(rows=list_partii)
document_partii = wsdl_client.document_partii_type(
rowslist=document_partii_rows)
n = wsdl_client.client.service.load_rashod_tovar(
document, document_partii, isclosed, '', '', 1)
logging.info(['Загрузка документа расхода', row['docno'], n])
def load_rashod(cursor, wsdl_client, prm_row_delta):
# расходы 410 - расходнаянакладная экспедитор SP4485
# расходы 469 - расходнаяреализатора экспедитор SP4487
# расходы 3716 - расходнаядоставка экспедитор SP3745
logging.info('Выборка расходов заголовки')
if prm_row_delta['TYPEID'] == 410:
cursor.execute(
'''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
sc46.sp4807 as client,
sc31.SP5639 as sklad,
SP6060 as idartmarket,
'' as agent,
sprexpeditor.SP4808 as expeditor,
sprexpeditor.descr as expeditorname,
sp3693 as isnal,
_1sjourn.iddoc, iddocdef, SP4380 as percentage_discount,
SP5573 as road_number, sp6140 as transportid,
SP3634 as price_type, SP5977 as discount_amount, SP4381 as total_discount,
SP4792 as margin_percent, SP4793 as margin_amount
FROM DH410 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc46 WITH (NOLOCK) on SP413 = sc46.id
left join sc31 WITH (NOLOCK) on SP412 = sc31.id
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
left join SC3246 as sprexpeditor WITH (NOLOCK) on SP4485 = sprexpeditor.id
left join sc5529 as sprtransport WITH (NOLOCK) on sprexpeditor.sp5533 = sprtransport.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
elif prm_row_delta['TYPEID'] == 469:
cursor.execute(
'''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
sc46.sp4807 as client,
sc31.SP5639 as sklad,
SP6072 as idartmarket,
0 as isnal,
'' as agent,
sprexpeditor.SP4808 as expeditor,
sprexpeditor.descr as expeditorname,
_1sjourn.iddoc, iddocdef, SP5369 as percentage_discount,
SP5574 as road_number, sp6140 as transportid,
SP3635 as price_type, SP5978 as discount_amount, SP5979 as total_discount,
SP5370 as margin_percent, 0 as margin_amount
FROM DH469 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
left join sc46 WITH (NOLOCK) on SP472 = sc46.id
left join sc31 WITH (NOLOCK) on SP471 = sc31.id
left join SC3246 as sprexpeditor WITH (NOLOCK) on SP4487 = sprexpeditor.id
left join sc5529 as sprtransport WITH (NOLOCK) on sprexpeditor.sp5533 = sprtransport.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
elif prm_row_delta['TYPEID'] == 3716:
cursor.execute(
'''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
sc46.sp4807 as client,
sc31.SP5639 as sklad,
SP6071 as idartmarket,
0 as isnal,
spragent.SP4808 as agent,
spragent.descr as agentname,
spragent.parentid as agentparentid,
sprexpeditor.SP4808 as expeditor,
sprexpeditor.descr as expeditorname,
_1sjourn.iddoc, iddocdef, SP4383 as percentage_discount,
SP5572 as road_number, sp6140 as transportid,
SP3726 as price_type, SP5976 as discount_amount, SP4384 as total_discount,
0 as margin_percent, 0 as margin_amount
FROM DH3716 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
left join sc46 WITH (NOLOCK) on SP3718 = sc46.id
left join sc31 WITH (NOLOCK) on SP3717 = sc31.id
left join SC3246 as spragent WITH (NOLOCK) on SP4639 = spragent.id
left join SC3246 as sprexpeditor WITH (NOLOCK) on SP3745 = sprexpeditor.id
left join sc5529 as sprtransport WITH (NOLOCK) on sprexpeditor.sp5533 = sprtransport.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
logging.info('Выборка расходов заголовки завершена')
rows_header = cursor.fetchall()
for row in rows_header:
client_list = []
# list_partii=[]
isclosed = is_process_doc(row['closed'])
if not check_firma(row, 0, True):
continue
if row['idartmarket'] is None or row['idartmarket'].strip() == '':
# if isclosed == 1:
logging.error(';'.join(['Пустой ид', row['docno']]))
# continue
if row['sklad'] is None or row['sklad'].strip() == '':
if isclosed == 1:
logging.error(';'.join(['Пустой склад', row['docno']]))
continue
if row['client'] is None or row['client'].strip() == '':
if isclosed == 1:
logging.error(';'.join(['Пустой клиент', row['docno']]))
# continue
if not row['client'] is None and not "'" + row[
'client'] + "'" in client_list:
client_list.append("'" + row['client'] + "'")
if not client_list:
continue
pass
else:
price_code = nomenklatura.get_price_code_from_enumeration(
row['price_type'])
str_id = ",".join(client_list)
get_client_groups(wsdl_client, cursor, str_id)
logging.warning([row['firma'], row['sklad'], row['client']])
header = wsdl_client.header_type(
document_type=2,
firma=row['firma'].strip(),
sklad=row['sklad'].strip(),
client=row['client'].strip(),
idartmarket=row['idartmarket'].strip(),
document_date=row['datedoc'],
nomerartmarket=row['docno'],
bdid=row['iddoc'].strip(),
bdtype=row['iddocdef'],
skidka_procent=row['percentage_discount'],
base_id=row['road_number'],
price_type=price_code,
discount_amount=row['discount_amount'],
discount_total=row['total_discount'],
margin_percent=row['margin_percent'],
margin_amount=row['margin_amount'])
logging.info('Выборка строк расхода')
if prm_row_delta['TYPEID'] == 410:
cursor.execute(
'''
select sp4802 as idtovar, SP424 as kolvo, SP427 as koef, SP426 as price,
SP5641 as id_pdate, SC5196.id as bdid_pdate,
SP428 as sum, SP6074 as price_goods,
SP5730 as egais_reference_id, SC5724.SP5727 as reference2
from dt410 WITH (NOLOCK)
left join sc33 WITH (NOLOCK) on SP423 = sc33.id
left join SC5196 on SP5205=SC5196.id
left join SC5724 WITH (NOLOCK) on SC5724.id = SP5730
where iddoc=%s
''', row['iddoc'])
elif prm_row_delta['TYPEID'] == 469:
cursor.execute(
'''
select sp4802 as idtovar, SP483 as kolvo, SP486 as koef, SP485 as price,
SP5641 as id_pdate, SC5196.id as bdid_pdate,
SP487 as sum, SP6075 as price_goods,
SP5731 as egais_reference_id, SC5724.SP5727 as reference2
from dt469 WITH (NOLOCK)
left join sc33 WITH (NOLOCK) on SP482=sc33.id
left join SC5196 on SP5207=SC5196.id
left join SC5724 WITH (NOLOCK) on SC5724.id = SP5731
where iddoc=%s''', row['iddoc'])
elif prm_row_delta['TYPEID'] == 3716:
cursor.execute(
'''
select sp4802 as idtovar, SP3731 as kolvo, SP3734 as koef, SP3733 as price, SP3735 as sum,
SP5641 as id_pdate, SC5196.id as bdid_pdate, SP4917 as price_goods,
SP5729 as egais_reference_id, SC5724.SP5727 as reference2
from dt3716 WITH (NOLOCK)
left join sc33 WITH (NOLOCK) on SP3730=sc33.id
left join SC5196 on SP5203=SC5196.id
left join SC5724 WITH (NOLOCK) on SC5724.id = SP5729
where iddoc=%s
''', row['iddoc'])
logging.info('Выборка строк расхода завершена')
rows_table = cursor.fetchall()
row_list = []
tovar_list = []
sum_total = 0
for row_table in rows_table:
if row_table['id_pdate']:
unload_production_date(cursor, wsdl_client,
row_table['bdid_pdate'])
if row_table['reference2'] is not None and row_table[
'reference2'].strip() != '':
unload_egais_reference(cursor, wsdl_client,
row_table['egais_reference_id'])
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pdate=row_table['id_pdate'],
price_goods=row_table['price_goods'],
field_str1=row_table['reference2'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
sum_total += row_table['sum']
if row['isnal'] == 1:
header_kassa = {}
header_kassa['firma'] = row['firma']
header_kassa['priniat_ot'] = row['client']
header_kassa['idartmarket'] = row['idartmarket']
header_kassa['datedoc'] = row['datedoc']
header_kassa['docno'] = row['docno']
header_kassa['closed'] = row['closed']
header_kassa['summa'] = sum_total
load_prihod_kassa(wsdl_client, header_kassa)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
prm_id_str=str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=1,
wsdl_client=wsdl_client)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(['Загрузка документа расхода', row['docno']]))
# hdb_agent=
if row['agent'] != '' and row['agent'] is not None:
unload_agents(wsdl_client,
agent_id=row['agent'],
agent_parent_id=row['agentparentid'],
agent_name=row['agentname'])
if row['expeditor'] != '' and row['expeditor'] is not None:
unload_agents(wsdl_client,
agent_id=row['expeditor'],
agent_parent_id='',
agent_name=row['expeditorname'],
transport_id=row['transportid'])
list_partii = []
if isclosed == 1 and False:
logging.info('Выборка партий расхода')
cursor.execute(
'''
select
sp4802 as idtovar_artmarket, ltrim(rtrim(_1sjourn.iddoc)) as prihodid,
_1sjourn.iddocdef as prihodtype, docno as prihodno,
CAST(LEFT(_1sjourn.Date_Time_IDDoc, 8) as DateTime) as prihoddate,
SP1133 as ostatok, SP2655 as stoimost, SP2799 as prodstoimost, SP4307 as prodaga
from ra1130 WITH (NOLOCK)
left join sc33 WITH (NOLOCK) on ra1130.sp1131 = sc33.id
left join _1sjourn WITH (NOLOCK) on ltrim(rtrim(substring(ltrim(rtrim(sp1132)),
charindex(' ',ltrim(rtrim(sp1132))),100)))=ltrim(rtrim(_1sjourn.iddoc))
where ra1130.iddoc=%s
''', row['iddoc'])
logging.info('Выборка партий расхода завершена')
rows_table_partii = cursor.fetchall()
for row_partii in rows_table_partii:
row_nom_partii = wsdl_client.row_partii_type(
tovar=row_partii['idtovar_artmarket'],
prihod_id=row_partii['prihodid'],
prihod_type=row_partii['prihodtype'],
prihod_no=row_partii['prihodno'],
prihod_date=row_partii['prihoddate'],
ostatok=row_partii['ostatok'],
stoimost=row_partii['stoimost'],
prodstoimost=row_partii['prodstoimost'],
prodaga=row_partii['prodaga'])
list_partii.append(row_nom_partii)
document_partii_rows = wsdl_client.rows_partii_type(rows=list_partii)
document_partii = wsdl_client.document_partii_type(
rowslist=document_partii_rows)
n = wsdl_client.client.service.load_rashod_tovar(
document, document_partii, isclosed, row['agent'],
row['expeditor'], 0)
logging.info(';'.join(['Загрузка документа расхода', row['docno'], n]))
def main():
logging.warning('Начало работы')
logging.warning(convert_base('6053', to_base=36))
# client.wsdl.dump()
# list = []
# os.environ['TDSDUMP'] = 'stdout'
# try:
while True:
k = input('Введите команду:')
if k == '0':
break
elif k == 'ч':
load_chicago()
elif k == 'контроль':
check_rashod(cursor, wsdl_client)
elif k == 'ценынач':
nomenklatura.load_nomenklatura(cursor, prm_id_str='', prm_id_mode=1, prm_with_parent=0, prm_update_mode=1,
prm_unload_price=3678, prm_unload_price_date='2018-12-31',
wsdl_client=wsdl_client)
elif k == 'цены':
start_date_0 = date(2020, 11, 20)
end_date = date(2020, 12, 1)
nomenklatura.unload_price(wsdl_client, cursor, start_date_0, end_date)
elif k == 'штрихкода':
nomenklatura.unload_ean_codes(cursor=cursor, wsdl_client=wsdl_client.client)
elif k == 'док':
month_num = 12
year_num = 2020
start_date = date(year_num, month_num, 1)
end_date = date(year_num, month_num, monthrange(year_num, month_num)[1])
dolgi.load_docs(cursor, wsdl_client, start_date, end_date)
elif k == 'авто' or k == 'avto':
auto_load(cursor)
elif k == 'фирма':
hdb.load_firm(client, cursor)
elif k == 'агентгруппы':
hdb.unload_agent_groups(client, cursor)
elif k == 'агент':
hdb.unload_agent(client, cursor)
elif k == 'минцены':
nomenklatura.unload_wholesale_min_price(cursor=cursor, wsdl_client=wsdl_client.client)
elif k == 'склад':
hdb.load_storage(client, cursor)
elif k == 'клиент':
hdb.get_client_groups(wsdl_client, cursor)
elif k == 'клиентструк':
hdb.load_client_structure(cursor, wsdl_client)
elif k == 'регион':
hdb.get_region_groups(cursor, wsdl_client=wsdl_client)
elif k == 'остаткипоставщик':
dolgi.load_supplier_balance(cursor, wsdl_client)
elif k == 'остаткиклиент':
dolgi.load_client_balance(cursor, wsdl_client)
elif k == 'остаткисклад':
ostatki.load_ostatki_sklad(wsdl_client, cursor)
elif k == 'dump':
client.wsdl.dump()
conn.close()
logging.info('Конец работы')
def auto_load(prm_cursor):
white_list = []
load_all = 1
delete_change_records = True
if load_all == 1:
white_list.append(3716) # расходнаядоставка v_alko++
white_list.append(410) # расходнаянакладная v_alko++
white_list.append(469) # расходнаяреализатора v_alko++
white_list.append(297) # списания v_alko++
white_list.append(310) # ввод остатков v_alko++
white_list.append(434) # приход v_alko++
white_list.append(239) # перемещение v_alko++
white_list.append(4425) # заказ поставщику v_alko++
white_list.append(4553) # счет на услуги service invoices
white_list.append(2989) # движенияденежныхсредств v_alko++
white_list.append(4308) # выручкадоставка sp4323 переброска v_alko++
white_list.append(2964) # ПриходныйОрдерТБ v_alko++
white_list.append(4179) # АктПереоценкиКлиенты v_alko++
white_list.append(4225) # РасходныйОрдерТБ v_alko++
white_list.append(4114) # приходный ордер Б
white_list.append(4132) # расходный ордер Б
white_list.append(33) # номенклатура
white_list.append(46) # контрагенты
white_list.append(31) # склады
white_list.append(5468) # производители импортеры
white_list.append(5196) # даты розлива
white_list.append(4840) # клиенты агента
white_list.append(4843) # ассортимент агента
white_list.append(3769) # подразделения
white_list.append(3773) # статьи затрат
white_list.append(5552) # источник финансирования SP6128
white_list.append(5554) # для маркетинга SP6129
white_list.append(5510) # аналитики
white_list.append(5584) # доверенности
white_list.append(5529) # транспортное средство
white_list.append(1183) # счета контрагентов банк
white_list.append(5494) # скидки клиентам
white_list.append(5724) # справки егаис
white_list.append(5682) # акцизные марки
if load_all == 0:
# white_list.append(5724) # справки егаис
# white_list.append(3716) # расходнаядоставка v_alko++
# white_list.append(410) # расходнаянакладная v_alko++
# white_list.append(469) # расходнаяреализатора v_alko++
#
# white_list.append(297) # списания v_alko++
#
# white_list.append(310) # ввод остатков v_alko++
# white_list.append(434) # приход v_alko++
# white_list.append(239) # перемещение v_alko++
# white_list.append(4425) # заказ поставщику v_alko++
# white_list.append(4553) # счет на услуги service invoices
#
# white_list.append(2989) # движенияденежныхсредств v_alko++
# white_list.append(4308) # выручкадоставка sp4323 переброска v_alko++
# white_list.append(2964) # ПриходныйОрдерТБ v_alko++
# white_list.append(4179) # АктПереоценкиКлиенты v_alko++
# white_list.append(4225) # РасходныйОрдерТБ v_alko++
#
# white_list.append(4114) # приходный ордер Б
# white_list.append(4132) # расходный ордер Б
#
# white_list.append(33) # номенклатура
# white_list.append(46) # контрагенты
# white_list.append(31) # склады
#
# white_list.append(5468) # производители импортеры
# white_list.append(5196) # даты розлива
# white_list.append(4840) # клиенты агента
# white_list.append(4843) # ассортимент агента
#
# white_list.append(3769) # подразделения
#
# white_list.append(3773) # статьи затрат
#
# white_list.append(5552) # источник финансирования SP6128
# white_list.append(5554) # для маркетинга SP6129
#
# white_list.append(5510) # аналитики
# white_list.append(5584) # доверенности
# white_list.append(5529) # транспортное средство
# white_list.append(1183) # счета контрагентов банк
# white_list.append(5494) # скидки клиентам
white_list.append(5682) # акцизные марки
commit_limit = 100
commit_count = 0
while True:
logging.warning('Выборка изменений')
# коды баз 'P1 ','БП '
# типы объектов: номенклатура - 33,
try:
prm_cursor.execute(
'''update _1SUPDTS set DWNLDID='1122!!' where (DBSIGN = 'P1 ') and not (DWNLDID='1122!!')''')
conn.commit()
except Exception as e:
logging.error('ошибка при обновлении таблицы _1SUPDTS')
logging.error(e)
# pass
prm_cursor.execute('''SELECT top 100000 * from _1SUPDTS WITH (NOLOCK) where (DBSIGN = 'P1 ') and
(DWNLDID='1122!!') ''')
rows_delta = prm_cursor.fetchall()
try:
load_chicago()
except Exception as e:
logging.exception(e)
for row_delta in tqdm(rows_delta):
# try:
# load_chicago()
# except Exception as e:
# logging.exception(e)
# logging.info(['typeid', row_delta['TYPEID']])
if not (row_delta['TYPEID'] in white_list):
# logging.info(['TYPEID', row_delta['TYPEID']])
if load_all == 1:
try:
prm_cursor.execute('''delete from _1SUPDTS where (DBSIGN = 'P1 ') and (DWNLDID='1122!!') and
(TYPEID=%s)''', row_delta['TYPEID'])
# prm_cursor.execute('''delete from _1SUPDTS where (DBSIGN = 'P1 ') and (DWNLDID='1122!!') and
# (OBJID=%s) and (TYPEID=%s)''', (row_delta['OBJID'], row_delta['TYPEID']))
conn.commit()
logging.info(';'.join(['Удалены изменения объект', str(row_delta['OBJID']),
str(row_delta['TYPEID'])]))
break
except:
logging.error(';'.join(['Ошибка отмены изменений объекта', str(row_delta['OBJID']),
str(row_delta['TYPEID'])]))
continue
logging.info(row_delta['TYPEID'])
# номенклатура
if row_delta['TYPEID'] == 33:
str_id = ",".join(["'" + row_delta['OBJID'] + "'"])
nomenklatura.load_nomenklatura(prm_cursor, prm_id_str=str_id, prm_id_mode=1, prm_with_parent=0,
prm_update_mode=1, wsdl_client=wsdl_client)
# скидки клиентам
elif row_delta['TYPEID'] == 5494:
logging.info(row_delta['TYPEID'])
hdb.unload_customer_discounts(prm_cursor, wsdl_client, row_delta['OBJID'])
# счета контрагентов
elif row_delta['TYPEID'] == 1183:
logging.info(row_delta['TYPEID'])
hdb.unload_bank_accounts(prm_cursor, wsdl_client, row_delta['OBJID'])
# транспорт
elif row_delta['TYPEID'] == 5529:
logging.info(row_delta['TYPEID'])
hdb.unload_transport(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# доверенности
elif row_delta['TYPEID'] == 5584:
logging.info(row_delta['TYPEID'])
hdb.unload_attorney(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# аналитика
elif row_delta['TYPEID'] == 5510:
logging.info(row_delta['TYPEID'])
hdb.unload_analytics(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# статьи затрат
elif row_delta['TYPEID'] == 3773:
logging.info(row_delta['TYPEID'])
hdb.unload_cost(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# для маркетинга
elif row_delta['TYPEID'] == 5554:
logging.info(row_delta['TYPEID'])
hdb.unload_for_marketing(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# источник финансирования
elif row_delta['TYPEID'] == 5552:
logging.info(row_delta['TYPEID'])
hdb.unload_financing(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# подразделение
elif row_delta['TYPEID'] == 3769:
logging.info(row_delta['TYPEID'])
hdb.unload_unit(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# склады
elif row_delta['TYPEID'] == 31:
hdb.unload_warehouse(prm_cursor, wsdl_client, row_delta['OBJID'])
# производитель импортер
elif row_delta['TYPEID'] == 5468:
hdb.unload_maker(prm_cursor, wsdl_client.client, row_delta['OBJID'])
# контрагенты
elif row_delta['TYPEID'] == 46:
# client_list.append("'" + row_delta['OBJID'] + "'")
str_id = ",".join(["'" + row_delta['OBJID'] + "'"])
hdb.get_client_groups(wsdl_client, cursor, prm_id_list=str_id, prm_parent_id_list=None,
prm_id_list_mode=1)
# ассортимент агента
elif row_delta['TYPEID'] == 4843:
hdb.unload_agent_products(prm_cursor, wsdl_client, row_delta['OBJID'])
# клиенты агента
elif row_delta['TYPEID'] == 4840:
hdb.unload_agent_clients(prm_cursor, wsdl_client, row_delta['OBJID'])
# даты розлива
elif row_delta['TYPEID'] == 5196:
hdb.unload_production_date(prm_cursor, wsdl_client, row_delta['OBJID'])
# справки егаис
elif row_delta['TYPEID'] == 5724:
hdb.unload_egais_reference(prm_cursor, wsdl_client, row_delta['OBJID'])
# приходный ордер Б
elif row_delta['TYPEID'] == 4114:
kassa.prihod(prm_cursor, wsdl_client, row_delta)
# continue
# расходный ордер Б
elif row_delta['TYPEID'] == 4132:
kassa.rashod(prm_cursor, wsdl_client, row_delta)
# continue
# перемещение
elif row_delta['TYPEID'] == 239:
sklad.move_tovar(prm_cursor, wsdl_client, row_delta)
# заказ поставщику
elif row_delta['TYPEID'] == 4425:
dolgi.load_order_supplier(prm_cursor, wsdl_client, row_delta)
# счет на услуги
elif row_delta['TYPEID'] == 4553:
dolgi.load_service_invoices(prm_cursor, wsdl_client, row_delta)
# списание
elif row_delta['TYPEID'] == 297:
sklad.spisanie(prm_cursor, wsdl_client, row_delta)
# оприходование
elif row_delta['TYPEID'] == 310:
sklad.vvodostatka_tovar(prm_cursor, wsdl_client, row_delta)
# взаиморасчеты
elif row_delta['TYPEID'] in [2989, 4308, 2964, 4179, 4225]: # взаиморасчеты
dolgi.load_dolgi(prm_cursor, wsdl_client, row_delta)
# расход
elif row_delta['TYPEID'] in [410, 469, 3716]:
rashod.load_rashod(prm_cursor, wsdl_client, row_delta)
# приходы
elif row_delta['TYPEID'] == 434:
# приходы
prihod.load_prihod(prm_cursor, wsdl_client, row_delta)
try:
if delete_change_records:
prm_cursor.execute('''delete from _1SUPDTS where (DBSIGN = 'P1 ') and (DWNLDID='1122!!')
and (OBJID=%s) and (TYPEID=%s)''', (row_delta['OBJID'], row_delta['TYPEID']))
commit_count += 1
if commit_count == commit_limit:
conn.commit()
commit_count = 0
logging.warning('commit')
logging.info(';'.join(['Загружен объект', str(row_delta['OBJID']), str(row_delta['TYPEID'])]))
except Exception as e:
logging.error(';'.join(['Ошибка загрузки объекта', str(row_delta['OBJID']),
str(row_delta['TYPEID'])]))
logging.error(e)
logging.warning('Выборка изменений завершена')
time.sleep(10)
def load_order_supplier(cursor, wsdl_client, prm_row_delta):
logging.info('Выборка заказ заголовки')
cursor.execute(
'''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc, docno,
sc13.sp4805 as firma, sc31.SP5639 as sklad,sc46.sp4807 as client, SP6114 as idartmarket,
_1sjourn.iddoc,SP4430 as fotgruz, SP4530 as foplata, SP4431 as fprihod,
SP4427 as shipment_date, SP4428 as payment_date, SP4429 as arrival_date,
SP4750 as defer_days, SP1008 as osnovanie FROM DH4425 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc46 WITH (NOLOCK) on SP4426 = sc46.id
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
left join sc31 WITH (NOLOCK) on SP5605=sc31.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
logging.info('Выборка заказ заголовки завершена')
rows_header = cursor.fetchall()
for row_header in rows_header:
if not check_firma(row_header, 0, True):
continue
if row_header['idartmarket'] is None or row_header[
'idartmarket'].strip() == '':
logging.error(';'.join(['Пустой ид', row_header['docno']]))
continue
if row_header['client'] is None or row_header['client'].strip() == '':
logging.error(';'.join(['Пустой клиент', row_header['docno']]))
continue
sklad = row_header['sklad']
if row_header['sklad'] is None or row_header['sklad'].strip() == '':
sklad = ''
logging.warning(row_header)
not_in_road = 0
if row_header['fotgruz'] == 0 or row_header['fprihod'] == 1:
not_in_road = 1
if row_header['shipment_date'] is None or row_header[
'shipment_date'] == datetime.datetime(1753, 1, 1, 0, 0):
shipment_date = datetime.datetime(1, 1, 1, 0, 0)
else:
shipment_date = row_header['shipment_date']
if row_header['payment_date'] is None or row_header[
'payment_date'] == datetime.datetime(1753, 1, 1, 0, 0):
payment_date = datetime.datetime(1, 1, 1, 0, 0)
else:
payment_date = row_header['payment_date']
if row_header['arrival_date'] is None or row_header[
'arrival_date'] == datetime.datetime(1753, 1, 1, 0, 0):
arrival_date = datetime.datetime(1, 1, 1, 0, 0)
else:
arrival_date = row_header['arrival_date']
header = wsdl_client.header_type(
document_type=2,
firma=row_header['firma'].strip(),
sklad=sklad.strip(),
client=row_header['client'].strip(),
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'],
zatr_nashi=not_in_road,
zatr_post=row_header['fotgruz'],
naedinicu=row_header['foplata'],
vozvrat=row_header['fprihod'],
bdid=row_header['osnovanie'],
skidka_procent=row_header['defer_days'],
field_date=shipment_date,
field_date2=payment_date,
field_date3=arrival_date)
isclosed = is_process_doc(row_header['closed'])
client_list = []
if not "'" + row_header['client'] + "'" in client_list:
client_list.append("'" + row_header['client'] + "'")
if not client_list:
continue
str_id = ",".join(client_list)
hdb.get_client_groups(wsdl_client, cursor, str_id)
logging.info(';'.join(['Выборка строк заказ', row_header['docno']]))
prm_datedoc = datetime.datetime.strftime(row_header['datedoc'],
'%Y-%m-%d')
logging.info(prm_datedoc)
cursor.execute(
'''
select sc33.sp4802 as idtovar, SP4437 as kolvo, 1 as koef, SP4438 as price,
SP4439 as sum from DT4425
left join sc33 on SP4434=sc33.id
where iddoc=%s
''', (prm_row_delta['OBJID']))
logging.info(';'.join(
['Выборка строк заказ завершена', row_header['docno']]))
rows_table = cursor.fetchall()
# print(rows_table)
row_list = []
tovar_list = []
# rows_table = []
for row_table in rows_table:
row_nom = wsdl_client.row_type(tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
prm_id_str=str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа заказ', row_header['docno']]))
n = wsdl_client.client.service.load_order(document, isclosed, 0)
logging.info(';'.join(
['Загрузка документа заказ', row_header['docno'], n]))
def move_tovar_filial(cursor, wsdl_client, prm_row_delta):
rows_header = get_move_header(cursor, 1, prm_row_delta)
for row_header in rows_header:
logging.warning([
check_firma(row_header, 1),
check_docid(row_header, 1),
check_sklad(row_header, 1)
])
if not check_firma(row_header, 1) or not check_docid(
row_header, 1) or not check_sklad(row_header, 1):
logging.warning(row_header['sklad_in'])
if check_firma(row_header, 1) and check_docid(row_header, 1) and \
row_header['sklad_in'].strip() in filial_sklad_white_list:
logging.warning('Загружаем оприходованием')
header = wsdl_client.header_type(
document_type=2,
firma=cb_firma_id,
sklad=row_header['sklad_in'].strip(),
client='',
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'])
isclosed = is_process_doc(row_header['closed'])
rows_table = get_move_rows(cursor, 1, row_header)
row_list = []
tovar_list = []
for row_table in rows_table:
if not row_table['idtovar'].strip().isdigit():
logging.error(
["Некорректный код товара", row_table['idtovar']])
row_nom = wsdl_client.row_type(
tovar=0,
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
else:
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=3,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=1)
document = wsdl_client.document_type(header=header,
rowslist=rows)
logging.info(';'.join(
['Загрузка документа ввод остатка', row_header['docno']]))
n = wsdl_client.client.service.load_vvodostatka_tovar(
document, isclosed, 1)
logging.info(';'.join([
'Загрузка документа ввод остатка', row_header['docno'], n
]))
continue
logging.warning(row_header)
header = wsdl_client.header_type(
document_type=2,
firma=cb_firma_id,
sklad=row_header['sklad'].strip(),
client=row_header['sklad_in'].strip(),
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'])
isclosed = is_process_doc(row_header['closed'])
rows_table = get_move_rows(cursor, 1, row_header)
row_list = []
tovar_list = []
for row_table in rows_table:
if not row_table['idtovar'].strip().isdigit():
logging.error(
["Некорректный код товара", row_table['idtovar']])
row_nom = wsdl_client.row_type(
tovar=0,
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
else:
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
tovar_filial=row_table['idtovarfil'])
# if row_table['idtovar'] == None:
# continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=3,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=1)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа перемещение', row_header['docno']]))
n = wsdl_client.client.service.load_peremesh(document, isclosed, 1)
logging.info(
['Загрузка документа перемещение', row_header['docno'], n])
def spisanie(cursor, wsdl_client, prm_row_delta):
rows_header = get_spisanie_header(cursor, 0, prm_row_delta)
for row_header in rows_header:
if not check_firma(row_header, 0, True) or not check_docid(
row_header, 0) or not check_sklad(row_header, 0):
continue
header = wsdl_client.header_type(
document_type=2,
firma=row_header['firma'].strip(),
sklad=row_header['sklad'].strip(),
client='',
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'])
isclosed = is_process_doc(row_header['closed'])
rows_table = get_spisanie_rows(cursor, 0, row_header)
row_list = []
tovar_list = []
for row_table in rows_table:
if row_table['id_pdate']:
unload_production_date(cursor, wsdl_client,
row_table['bdid_pdate'])
if row_table['reference2'] is not None and row_table[
'reference2'].strip() != '':
unload_egais_reference(cursor, wsdl_client,
row_table['egais_reference_id'])
row_nom = wsdl_client.row_type(tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pdate=row_table['id_pdate'],
field_str1=row_table['reference2'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
prm_id_str=str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client)
list_partii = []
if isclosed == 1 and False:
logging.info('Выборка партий списания')
cursor.execute(
'''
select
sp4802 as idtovar_artmarket, ltrim(rtrim(_1sjourn.iddoc)) as prihodid, _1sjourn.iddocdef as prihodtype,
docno as prihodno,CAST(LEFT(_1sjourn.Date_Time_IDDoc, 8) as DateTime) as prihoddate,
SP1133 as ostatok, SP2655 as stoimost, SP2799 as prodstoimost, SP4307 as prodaga
from ra1130 WITH (NOLOCK)
left join sc33 WITH (NOLOCK) on ra1130.sp1131 = sc33.id
left join _1sjourn WITH (NOLOCK) on ltrim(rtrim(substring(ltrim(rtrim(sp1132)),
charindex(' ',ltrim(rtrim(sp1132))),100)))=ltrim(rtrim(_1sjourn.iddoc))
where ra1130.iddoc=%s
''', prm_row_delta['OBJID'])
logging.info('Выборка партий списаний завершена')
rows_table_partii = cursor.fetchall()
for row_partii in rows_table_partii:
row_nom_partii = wsdl_client.row_partii_type(
tovar=row_partii['idtovar_artmarket'],
prihod_id=row_partii['prihodid'],
prihod_type=row_partii['prihodtype'],
prihod_no=row_partii['prihodno'],
prihod_date=row_partii['prihoddate'],
ostatok=row_partii['ostatok'],
stoimost=row_partii['stoimost'],
prodstoimost=row_partii['prodstoimost'],
prodaga=row_partii['prodaga'])
list_partii.append(row_nom_partii)
document_partii_rows = wsdl_client.rows_partii_type(rows=list_partii)
document_partii = wsdl_client.document_partii_type(
rowslist=document_partii_rows)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа списание', row_header['docno']]))
n = wsdl_client.client.service.load_spisanie(document, document_partii,
isclosed, 0)
logging.info(';'.join(
['Загрузка документа списание', row_header['docno'], n]))
def send_ostatki_sklad(wsdl_client,
cursor,
prm_ostatki_list,
prm_row_firma,
prm_row_sklad,
is_filial=0):
header = wsdl_client.header_type(
document_type=1,
firma=prm_row_firma['idartmarket'].strip(),
sklad=prm_row_sklad['idartmarket'].strip())
row_list = []
tovar_list = []
for r in prm_ostatki_list:
if r['ostatok'] <= 0:
continue
#logging.info(r)
if is_filial == 1:
if not r['idtovar'].strip().isdigit():
logging.error(["Некорректный код товара", r['idtovar']])
#continue
row = wsdl_client.row_type(tovar='0',
quantity=r['ostatok'],
price=r['sebestoimost'],
tovar_filial=r['idtovarfil'])
else:
#continue
row = wsdl_client.row_type(tovar=r['idtovar'],
quantity=r['ostatok'],
price=r['sebestoimost'],
tovar_filial=r['idtovarfil'])
if not "'" + r['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + r['idtovar'] + "'")
row_list.append(row)
if tovar_list == []:
logging.info('Товары для загрузки не найдены')
else:
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
if is_filial == 1:
pass
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=3,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=is_filial)
else:
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=is_filial)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info('Загрузка документа остатков')
wsdl_client.client.service.load_ostatki_tovar(document, is_filial)
logging.info('Загрузка документа остатков завершена')
def load_vozvrat_filial(cursor, wsdl_client, prm_row_delta):
logging.info('Выборка приходов заголовки')
cursor.execute(
'''
select closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
SC4014.SP5011 as firma, SC172.SP573 as client, sc55.SP8452 as sklad, SP9329 as idartmarket,
_1sjourn.iddoc,0 as zatr_nashi,0 as zatr_post,0 as naedinicu,
'1' as isreturn
from DH1656 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join SC4014 WITH (NOLOCK) on SP4056=SC4014.id
left join SC172 WITH (NOLOCK) on SP1629 = SC172.id
left join SC55 WITH (NOLOCK) on SP1639 = SC55.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
logging.info('Выборка приходов заголовки завершена')
rows_header = cursor.fetchall()
for row_header in rows_header:
is_return = 0
if row_header['idartmarket'] is None or row_header[
'idartmarket'].strip() == '':
logging.error(';'.join(['Пустой ид', row_header['docno']]))
continue
if row_header['sklad'] is None or row_header['sklad'].strip() == '':
logging.error(';'.join(['Пустой склад', row_header['docno']]))
continue
if row_header['client'] is None or row_header['client'].strip() == '':
logging.error(';'.join(['Пустой клиент', row_header['docno']]))
continue
header = wsdl_client.header_type(
document_type=2,
firma=cb_firma_id,
sklad=row_header['sklad'].strip(),
client=row_header['client'].strip(),
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'],
zatr_nashi=row_header['zatr_nashi'],
zatr_post=row_header['zatr_post'],
naedinicu=row_header['naedinicu'],
vozvrat=is_return)
isclosed = is_process_doc(row_header['closed'])
client_list = []
if not "'" + row_header['client'] + "'" in client_list:
client_list.append("'" + row_header['client'] + "'")
if not client_list:
continue
str_id = ",".join(client_list)
get_client_groups_filial(wsdl_client=wsdl_client,
prm_cursor=cursor,
prm_id_list=str_id)
logging.info(';'.join(['Выборка строк прихода', row_header['docno']]))
prm_datedoc = datetime.datetime.strftime(row_header['datedoc'],
'%Y-%m-%d')
logging.info(prm_datedoc)
cursor.execute(
'''
select SC84.code as idtovar, SC84.SP8450 as idtovarfil, SP1645 as kolvo, SP1647 as koef,
SP1648 as price, SP1649 as sum, SP4245 as pricepriobr from Dt1656
left join SC84 on SP1644 = SC84.id where iddoc=%s ''',
(prm_row_delta['OBJID']))
logging.info(';'.join(
['Выборка строк прихода завершена', row_header['docno']]))
rows_table = cursor.fetchall()
logging.info(rows_table)
row_list = []
tovar_list = []
for row_table in rows_table:
if not row_table['idtovar'].strip().isdigit():
logging.error(
["Некорректный код товара", row_table['idtovar']])
row_nom = wsdl_client.row_type(
tovar=0,
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pricepriobr=row_table['pricepriobr'],
tovar_filial=row_table['idtovarfil'])
else:
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pricepriobr=row_table['pricepriobr'],
tovar_filial=row_table['idtovarfil'])
if row_table['idtovar'] is None:
pass
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
logging.warning(str_id)
nomenklatura.load_nomenklatura(cursor,
str_id,
prm_id_mode=3,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client,
is_filial=1)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info([
'Загрузка документа прихода', row_header['docno'],
row_header['datedoc']
])
n = wsdl_client.client.service.load_prihod_tovar(document, isclosed, 1)
logging.info([
'Загрузка документа прихода завершена', row_header['docno'],
row_header['datedoc'], n
])
def load_prihod(cursor, wsdl_client, prm_row_delta):
logging.info('Выборка приходов заголовки')
cursor.execute(
'''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,sc46.sp4807 as client,sc31.SP5639 as sklad,SP6059 as idartmarket,
_1sjourn.iddoc,SP4172 as zatr_nashi,SP4176 as zatr_post,SP4173 as naedinicu,
sp446 as isreturn, SP5591 as number_in, SP5592 as date_in, sp2698 as doc_base
FROM dh434 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc46 WITH (NOLOCK) on SP437 = sc46.id
left join sc31 WITH (NOLOCK) on SP436 = sc31.id
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
where _1sjourn.iddoc=%s
''', prm_row_delta['OBJID'])
#SP446 pr_nakl
logging.info('Выборка приходов заголовки завершена')
rows_header = cursor.fetchall()
for row_header in rows_header:
is_return = 0
if row_header['isreturn'] == ' 3J ':
is_return = 1
if not check_firma(row_header, 0, True):
continue
if row_header['idartmarket'] is None or row_header[
'idartmarket'].strip() == '':
logging.error(';'.join(['Пустой ид', row_header['docno']]))
continue
if row_header['sklad'] is None or row_header['sklad'].strip() == '':
logging.error(';'.join(['Пустой склад', row_header['docno']]))
continue
if row_header['client'] is None or row_header['client'].strip() == '':
logging.error(';'.join(['Пустой клиент', row_header['docno']]))
continue
if row_header['date_in'] is None or row_header[
'date_in'] == datetime.datetime(1753, 1, 1, 0, 0):
date_in = datetime.datetime(1, 1, 1, 0, 0)
else:
date_in = row_header['date_in']
base_type = 0
base_id = ''
if row_header['doc_base'] != ' 0 0 ':
docosnov_list = row_header['doc_base'].strip().split(' ')
base_type = convert_base(docosnov_list[0], from_base=36)
base_id = docosnov_list[1]
header = wsdl_client.header_type(
document_type=2,
firma=row_header['firma'].strip(),
sklad=row_header['sklad'].strip(),
client=row_header['client'].strip(),
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'],
zatr_nashi=row_header['zatr_nashi'],
zatr_post=row_header['zatr_post'],
naedinicu=row_header['naedinicu'],
vozvrat=is_return,
bdid=row_header['number_in'],
field_date=date_in,
base_type=base_type,
base_id=base_id)
isclosed = is_process_doc(row_header['closed'])
client_list = []
if not "'" + row_header['client'] + "'" in client_list:
client_list.append("'" + row_header['client'] + "'")
if not client_list:
continue
str_id = ",".join(client_list)
get_client_groups(wsdl_client, cursor, str_id)
logging.info(';'.join(['Выборка строк прихода', row_header['docno']]))
prm_datedoc = datetime.datetime.strftime(row_header['datedoc'],
'%Y-%m-%d')
logging.info(prm_datedoc)
cursor.execute(
'''select sc33.sp4802 as idtovar,SP449 as kolvo,SP452 as koef,SP451 as price,
SP453 as sum, value as pricepriobr, SP5641 as id_pdate, SC5196.id as bdid_pdate
from dt434
left join sc33 on sp448=sc33.id
left join SC5196 on SP5201=SC5196.id
left join
(select a.objid as idtovar,ISNULL(cast(value as numeric(14,2)),0) as value,date from (
select objid,id,
max(substring(convert(varchar,date,120),1,10)
+right('0000000000'+cast(time as varchar),10)
+docid
+right('0000000000'+cast(row_id as varchar),10)) md
from _1SCONST where _1SCONST.id=38 and
date<=%s group by objid,id) a
inner join (select objid,id,date,value,
substring(convert(varchar,date,120),1,10)
+right('0000000000'+cast(time as varchar),10)
+docid
+right('0000000000'+cast(row_id as varchar),10) ld
from _1SCONST where _1SCONST.id=38 ) b
on a.objid=b.objid and md=ld ) cpriobr on sc33.id= cpriobr.idtovar
where iddoc=%s
''', (prm_datedoc, prm_row_delta['OBJID']))
logging.info(';'.join(
['Выборка строк прихода завершена', row_header['docno']]))
rows_table = cursor.fetchall()
row_list = []
tovar_list = []
for row_table in rows_table:
if row_table['id_pdate']:
unload_production_date(cursor, wsdl_client,
row_table['bdid_pdate'])
row_nom = wsdl_client.row_type(
tovar=row_table['idtovar'],
quantity=row_table['kolvo'],
price=row_table['price'],
koef=row_table['koef'],
sum=row_table['sum'],
pricepriobr=row_table['pricepriobr'],
pdate=row_table['id_pdate'])
if row_table['idtovar'] is None:
continue
if not "'" + row_table['idtovar'] + "'" in tovar_list:
tovar_list.append("'" + row_table['idtovar'] + "'")
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
str_id = ",".join(tovar_list)
nomenklatura.load_nomenklatura(cursor,
prm_id_str=str_id,
prm_id_mode=2,
prm_with_parent=0,
prm_update_mode=0,
wsdl_client=wsdl_client)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа прихода', row_header['docno']]))
n = wsdl_client.client.service.load_prihod_tovar(document, isclosed, 0)
logging.info(';'.join([
'Загрузка документа прихода', row_header['docno'], n,
datetime.datetime.strftime(date_in, '%Y-%m-%d'),
row_header['number_in']
]))