def load_ostatki_sklad(wsdl_client, cursor):
# загрузка остатков товаров
# rg99 остатки товаров sp3603 - фирма (" 1 " - артмаркет?); sp101 - товар; sp100 - склад (' 12БЛ '); SP5183 - дата розлива; SP102 - количество
#филиал Зея RG405 - SP4062 фирма, SP408 - номенклатура, SP418 - склад, SP3117 - цена прод, SP8981 - дата розлива, SP411 - количество
logging.info('Выборка фирм')
cursor.execute(
'''SELECT descr,sp4805 as idartmarket, sp4805 as firma FROM SC13''')
logging.info('Выборка фирм завершена')
rows_firma = cursor.fetchall()
for row_firma in rows_firma:
if not check_firma(row_firma, 0):
continue
if row_firma['idartmarket'].strip() == '':
continue
logging.info('Выборка складов')
cursor.execute('''SELECT id,SP5639 as idartmarket FROM SC31 ''')
logging.info('Выборка складов завершена')
rows_sklad = cursor.fetchall()
for row_sklad in rows_sklad:
if row_sklad['idartmarket'].strip() == '':
continue
logging.info('Выборка остатков начало')
cursor.execute(
'''SELECT sc33.sp4802 as idtovar,sum(sp102) as ostatok,sp6055 as sebestoimost
from rg99 left join sc33 on rg99.sp101=sc33.id where (period='2018-12-01 00:00:00.000')
and (sp100=%s) and (sp3603=' 1 ') group by sc33.sp4802,sp6055''',
row_sklad['id'])
#
logging.info('Запрос остатков выполнен')
row = cursor.fetchall()
logging.info('Курсор получен')
send_ostatki_sklad(wsdl_client, cursor, row, row_firma, row_sklad)
def rashod(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, sp4133 as priniat_ot, sp4139 as summa, SP6119 as idartmarket,
_1sjourn.iddoc FROM DH4132 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc13 WITH (NOLOCK) on SP1005=sc13.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):
logging.warning(row_header['firma'])
continue
if row_header['idartmarket'] is None or row_header[
'idartmarket'].strip() == '':
logging.error(';'.join(['Пустой ид', row_header['docno']]))
continue
logging.warning(row_header)
load_rashod_kassa(wsdl_client, row_header)
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 check_rashod(cursor, wsdl_client):
check_list = list()
# check_list.append({'doctype': 410, 'idartmarket': 'SP6060'})
# check_list.append({'doctype': 469, 'idartmarket': 'SP6072'})
# check_list.append({'doctype': 3716, 'idartmarket': 'SP6071'})
# check_list.append({'doctype': 297, 'idartmarket': 'SP6076'}) # списание
# check_list.append({'doctype': 434, 'idartmarket': 'SP6059'}) # приход
check_list.append({'doctype': 239, 'idartmarket': 'SP6079'}) # перемещение
check_list.append({
'doctype': 310,
'idartmarket': 'SP6077'
}) # ввод остатков
logging.warning('rashod control start')
for check_item in check_list:
logging.warning(check_item)
cursor.execute('''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc, docno, '''
+ check_item['idartmarket'] +
''' as idartmarket, _1sjourn.iddoc,
sc13.sp4805 as firma, iddocdef FROM DH''' +
str(check_item['doctype']) + ''' as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc and _1sjourn.iddocdef='''
+ str(check_item['doctype']) +
''' left join sc13 WITH (NOLOCK) on SP1005=sc13.id
where (CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) between '2019-01-01' and '2020-12-31')
''')
rows = cursor.fetchall()
counter = 0
for row in rows:
counter = counter + 1
if counter == 1000:
logging.warning(counter)
counter = 0
# print(row)
if not check_firma(row, 0):
continue
res = wsdl_client.client.service.doc_check(row['idartmarket'],
check_item['doctype'])
isclosed = is_process_doc(row['closed'])
if res == -100 and isclosed == 1 or res == 100 and isclosed == 1:
logging.warning([
res, row['datedoc'], row['docno'], isclosed, row['iddoc']
])
# load_rashod(cursor, wsdl_client, {'TYPEID': check_item['doctype'], 'OBJID': row['iddoc']})
elif res == 200 and not isclosed == 1:
logging.warning([
"Ошибка проведен", res, row['datedoc'], row['docno'],
isclosed, row['iddoc']
])
logging.warning('rashod control compete')
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 load_client_balance(cursor, wsdl_client):
# SP6065 тип клиента 1 покупатель 2 поставщик
logging.info('Выборка фирм')
cursor.execute(
'''SELECT descr,sp4805 as idartmarket,id, sp4805 as firma''')
logging.info('Выборка фирм завершена')
rows_firma = cursor.fetchall()
for row_firma in rows_firma:
if not check_firma(row_firma, 0):
continue
if row_firma['idartmarket'].strip() != '':
logging.info('Выборка остатков начало')
cursor.execute(
'''
SELECT sc46.sp4807 as client,sum(SP171) as ostatok,SP6065 from RG169
left join sc46 on ltrim(rtrim(SP170)) = '1A '+ltrim(rtrim(sc46.id))
where (period='2018-12-01 00:00:00.000') and (SP2671=%s) group by sc46.sp4807,SP6065
''', row_firma['id'])
# + нам должны, - мы должны
# and (sc46.SP6065=1)
logging.info('Запрос остатков выполнен')
rows = cursor.fetchall()
logging.info('Курсор получен')
header = wsdl_client.header_type(
document_type=1,
firma=row_firma['idartmarket'].strip(),
sklad='')
row_list_dolg_post = []
row_list_dolg = []
row_list_avans = []
row_list_avans_post = []
client_list = []
for row in rows:
if row['client'] is None:
continue
if row['SP6065'] == 1:
if row['ostatok'] < 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=-1 *
row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_avans.append(row_nom)
elif row['ostatok'] > 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_dolg.append(row_nom)
elif row['SP6065'] == 2:
if row['ostatok'] > 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_dolg_post.append(row_nom)
elif row['ostatok'] < 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=-1 *
row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_avans_post.append(row_nom)
if not "'" + row['client'] + "'" in client_list:
client_list.append("'" + row['client'] + "'")
if not client_list:
continue
str_id = ",".join(client_list)
rows = wsdl_client.rows_type(rows=row_list_dolg)
document = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_avans)
document2 = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_dolg_post)
document3 = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_avans_post)
document4 = wsdl_client.document_type(header=header, rowslist=rows)
logging.info('Загрузка документа остатков')
# prmmode 1-долги клиентов (sc46.SP6065=1) row['ostatok']>0
# 2 - авнсы клиентов (sc46.SP6065=1) row['ostatok']<0
# 3- долги поставщикам
# 4 - авансы поставщиков
# n=client.service.load_ostatki_client(document3,3)
# проверено
# n=client.service.load_ostatki_client(document,1) 00-00000211
# n=client.service.load_ostatki_client(document4,3) 00-00000222
# n=client.service.load_ostatki_client(document2,3) 00-00000223
logging.info('Загрузка документа остатков завершена')
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 load_service_invoices(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, SP6127 as idartmarket, sP4558 as schf,
_1sjourn.iddoc FROM DH4553 as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc46 WITH (NOLOCK) on SP4557 = sc46.id
left join sc13 WITH (NOLOCK) on SP1005=sc13.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):
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
header = wsdl_client.header_type(
document_type=2,
firma=row_header['firma'].strip(),
sklad=row_header['schf'],
client=row_header['client'].strip(),
idartmarket=row_header['idartmarket'].strip(),
document_date=row_header['datedoc'],
nomerartmarket=row_header['docno'],
bdid=row_header['iddoc'])
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']]))
cursor.execute(
'''
select sp4556 as usluga, sp4559 as sum,
SP6128 as finance_id, SP6129 as for_market_id, sc46.sp4807 as client_row_id,
SP6126 as unit_id, sp6125 as cost_id
from DT4553
left join SC5552 on DT4553.SP5556 = SC5552.id
left join SC5554 on DT4553.SP5557 = SC5554.id
left join sc46 on SP5558 = sc46.id
left join SC3769 on SP6134 = SC3769.id
left join sc3773 on SP6135 = sc3773.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:
logging.info(row_table)
finance_id = ''
if row_table['finance_id'] is not None:
finance_id = row_table['finance_id']
for_market_id = ''
if row_table['for_market_id'] is not None:
for_market_id = row_table['for_market_id']
client_row_id = ''
if row_table['client_row_id'] is not None:
client_row_id = row_table['client_row_id']
client_list = []
if not "'" + client_row_id + "'" in client_list:
client_list.append("'" + client_row_id + "'")
if not client_list:
continue
str_id = ",".join(client_list)
hdb.get_client_groups(wsdl_client, cursor, str_id)
unit_id = ''
if row_table['unit_id'] is not None:
unit_id = row_table['unit_id']
cost_id = ''
if row_table['cost_id'] is not None:
cost_id = row_table['cost_id']
row_nom = wsdl_client.row_type(tovar=row_table['usluga'],
quantity=0,
price=0,
koef=0,
sum=row_table['sum'],
field_str1=finance_id,
field_str2=for_market_id,
field_str3=client_row_id,
field_str4=unit_id,
field_str5=cost_id)
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа счет на услуги', row_header['docno']]))
n = wsdl_client.client.service.load_invoice(document, isclosed, 0)
logging.info(';'.join(
['Загрузка документа на услуги', row_header['docno'], n]))
def load_dolgi(cursor, wsdl_client, prm_row_delta):
# взаиморасчеты
# 2989 - движенияденежныхсредств
# 4308 - выручкадоставка SP6083; sp4323 переброска
# 2964 - ПриходныйОрдерТБ Прих.орд.(торг.) SP6084
# 4179 - АктПереоценкиКлиенты Акт переоц. SP6085
# 4225 РасходныйОрдерТБ SP6082
logging.info(';'.join([
'Выборка взаиморасчетов',
str(prm_row_delta['TYPEID']), prm_row_delta['OBJID']
]))
if prm_row_delta['TYPEID'] == 2989:
idartmarket_str = 'SP6081'
doc_descr = 'Выписка банка'
elif prm_row_delta['TYPEID'] == 4308:
idartmarket_str = 'SP6083'
doc_descr = 'Выручка доставка'
elif prm_row_delta['TYPEID'] == 2964:
idartmarket_str = 'SP6084'
doc_descr = 'Прих.орд.(торг.)'
elif prm_row_delta['TYPEID'] == 4179:
idartmarket_str = 'SP6085'
doc_descr = 'Акт переоц.'
elif prm_row_delta['TYPEID'] == 4225:
idartmarket_str = 'SP6082'
doc_descr = 'Расх.орд.(торг.)'
if prm_row_delta['TYPEID'] == 2989:
select_str = '''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
''' + idartmarket_str + ''' as idartmarket, SP1415 as rschet,
_1sjourn.iddoc FROM DH''' + str(
prm_row_delta['TYPEID']) + ''' 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 SC1414 WITH (NOLOCK) on SP2990=SC1414.id
where _1sjourn.iddoc=%s and _1sjourn.iddocdef=%s
'''
elif prm_row_delta['TYPEID'] == 4308:
select_str = '''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
''' + idartmarket_str + ''' as idartmarket, '' as rschet,
_1sjourn.iddoc, sp4323 as perebroska FROM DH''' + str(
prm_row_delta['TYPEID']) + ''' as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
where _1sjourn.iddoc=%s and _1sjourn.iddocdef=%s
'''
else:
select_str = '''
SELECT closed, CAST(LEFT(Date_Time_IDDoc, 8) as DateTime) as datedoc,docno,
sc13.sp4805 as firma,
''' + idartmarket_str + ''' as idartmarket, '' as rschet,
_1sjourn.iddoc FROM DH''' + str(
prm_row_delta['TYPEID']) + ''' as dh WITH (NOLOCK)
left join _1sjourn WITH (NOLOCK) on dh.iddoc=_1sjourn.iddoc
left join sc13 WITH (NOLOCK) on SP1005=sc13.id
where _1sjourn.iddoc=%s and _1sjourn.iddocdef=%s
'''
cursor.execute(select_str,
(prm_row_delta['OBJID'], prm_row_delta['TYPEID']))
rows_header = cursor.fetchall()
logging.info('Выборка взаиморасчетов')
for row in rows_header:
if prm_row_delta['TYPEID'] == 4308 and row['perebroska'] == 1:
logging.warning('perebroska')
isclosed = is_process_doc(row['closed'])
if isclosed != 1:
pass
logging.info([
'Выборка взаиморасчетов опмание объекта', row['datedoc'],
row['docno']
])
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
header = wsdl_client.header_type(
document_type=2,
firma=row['firma'].strip(),
sklad=doc_descr,
client=row['rschet'].strip(),
idartmarket=row['idartmarket'].strip(),
document_date=row['datedoc'],
nomerartmarket=row['docno'])
cursor.execute(
'''select top 100 sc46.sp4807 as client,debkred, sp3711 as summa, 2 as typedvig,
sp3744 as docosnov from ra3707
left join sc46 on ltrim(rtrim(sp3710)) = ltrim(rtrim(sc46.id))
where ra3707.iddoc=%s''', row['iddoc'])
rows_table = cursor.fetchall()
row_list = []
for row_table in rows_table:
# docosnov_list = row_table['docosnov'].strip().split(' ')
# logging.warning(convert_base(docosnov_list[0], from_base=36))
# logging.warning([row_table['docosnov'], docosnov_list])
if row_table['debkred']:
debkred = 1
else:
debkred = 2
if debkred == 1 and row_table['typedvig'] == 1:
logging.error(';'.join(['контроль операции',
row['docno']]))
if row_table['client'] is None:
continue
row_nom = wsdl_client.row_type(
tovar=row_table['client'],
quantity=row_table['typedvig'],
price=3716,
koef=debkred,
sum=row_table['summa'],
tovar_filial=row_table['docosnov'].strip())
row_list.append(row_nom)
rows = wsdl_client.rows_type(rows=row_list)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа взаиморасчетов', row['docno']]))
n = wsdl_client.client.service.load_perebroska(document, isclosed)
logging.info(';'.join(
['Загрузка документа взаиморасчетов', row['docno'], n]))
else:
client_list = []
isclosed = is_process_doc(row['closed'])
if isclosed != 1:
pass
logging.info([
'Выборка взаиморасчетов опмание объекта', row['datedoc'],
row['docno']
])
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
header = wsdl_client.header_type(
document_type=2,
firma=row['firma'].strip(),
sklad=doc_descr,
client=row['rschet'].strip(),
idartmarket=row['idartmarket'].strip(),
document_date=row['datedoc'],
nomerartmarket=row['docno'])
# расход debkred 1
# покупатели
# debkred 1 уменьшение долга клиента
# debkred 0 увеличение долга клиента
# SP4372 - кред документ, документ основание
# TODO добавить документ основание
cursor.execute(
'''select sc46.sp4807 as client,debkred, sp171 as summa,
2 as typedvig, sp4372 as docosnov from ra169 WITH (NOLOCK)
left join sc46 WITH (NOLOCK) on ltrim(rtrim(SP170)) = '1A '+ltrim(rtrim(sc46.id))
where ra169.iddoc=%s
''', row['iddoc'])
rows_table_1 = cursor.fetchall()
if not rows_table_1 == [] and rows_table_1[0]['client'] is None:
cursor.execute(
'''select sc46.sp4807 as client,debkred, sp171 as summa,
2 as typedvig, sp4372 as docosnov from ra169 WITH (NOLOCK)
left join sc46 WITH (NOLOCK) on ltrim(rtrim(SP170)) = '1A '+ltrim(rtrim(sc46.id))
where ra169.iddoc=%s
''', row['iddoc'])
rows_table_1 = cursor.fetchall()
# поставщики
# debkred 1 уменьшение долга клиента
# debkred 0 увеличение долга клиента
cursor.execute(
'''
select sc46.sp4807 as client, debkred, SP936 as summa,
1 as typedvig, SP4373 as docosnov from ra933 WITH (NOLOCK)
left join sc46 WITH (NOLOCK) on SP934 = sc46.id
where ra933.iddoc=%s
''', row['iddoc'])
rows_table_2 = cursor.fetchall()
rows_table = rows_table_1 + rows_table_2
client_list = []
row_list = []
for row_table in rows_table:
docosnov_list = row_table['docosnov'].strip().split(' ')
logging.warning(convert_base(docosnov_list[0], from_base=36))
logging.warning([row_table['docosnov'], docosnov_list])
if row_table['debkred']:
debkred = 1
else:
debkred = 2
if debkred == 1 and row_table['typedvig'] == 1:
logging.error(';'.join(['контроль операции',
row['docno']]))
if row_table['client'] is None:
continue
row_nom = wsdl_client.row_type(tovar=row_table['client'],
quantity=row_table['typedvig'],
price=convert_base(
docosnov_list[0],
from_base=36),
koef=debkred,
sum=row_table['summa'],
tovar_filial=docosnov_list[1])
if not "'" + row_table['client'] + "'" in client_list:
client_list.append("'" + row_table['client'] + "'")
row_list.append(row_nom)
if not client_list:
# continue
pass
else:
str_id = ",".join(client_list)
hdb.get_client_groups(wsdl_client, cursor, str_id)
# print(row_list)
rows = wsdl_client.rows_type(rows=row_list)
document = wsdl_client.document_type(header=header, rowslist=rows)
logging.info(';'.join(
['Загрузка документа взаиморасчетов', row['docno']]))
n = wsdl_client.client.service.load_client_rashet(
document, isclosed)
logging.info(';'.join(
['Загрузка документа взаиморасчетов', row['docno'], n]))
def load_supplier_balance(cursor, wsdl_client):
logging.info('Выборка фирм')
cursor.execute(
'''SELECT descr,sp4805 as idartmarket,id, sp4805 as firma FROM SC13'''
)
logging.info('Выборка фирм завершена')
rows_firma = cursor.fetchall()
for row_firma in rows_firma:
if not check_firma(row_firma, 0):
continue
if row_firma['idartmarket'].strip() != '':
logging.info('Выборка остатков начало')
cursor.execute(
'''
SELECT sc46.sp4807 as client,sum(SP936) as ostatok,SP6065 from RG933
left join sc46 on ltrim(rtrim(SP934)) = ltrim(rtrim(sc46.id))
where (period='2018-12-01 00:00:00.000') and (SP2669=%s) group by sc46.sp4807,SP6065
''', row_firma['id'])
# + нам должны, - мы должны
logging.info('Запрос остатков выполнен')
rows = cursor.fetchall()
logging.info('Курсор получен')
header = wsdl_client.header_type(
document_type=2,
firma=row_firma['idartmarket'].strip(),
sklad='')
row_list_dolg_post = []
row_list_dolg = []
row_list_avans = []
row_list_avans_post = []
client_list = []
for row in rows:
if row['client'] is None:
continue
if row['SP6065'] == 1:
if row['ostatok'] < 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=-1 *
row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_avans.append(row_nom)
elif row['ostatok'] > 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_dolg.append(row_nom)
elif row['SP6065'] == 2:
if row['ostatok'] > 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_dolg_post.append(row_nom)
elif row['ostatok'] < 0:
row_nom = wsdl_client.row_type(tovar=row['client'],
quantity=-1 *
row['ostatok'],
price=0,
koef=0,
sum=0)
row_list_avans_post.append(row_nom)
if not "'" + row['client'] + "'" in client_list:
client_list.append("'" + row['client'] + "'")
if not client_list:
continue
str_id = ",".join(client_list)
rows = wsdl_client.rows_type(rows=row_list_dolg)
document = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_avans)
document2 = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_dolg_post)
document3 = wsdl_client.document_type(header=header, rowslist=rows)
rows = wsdl_client.rows_type(rows=row_list_avans_post)
document4 = wsdl_client.document_type(header=header, rowslist=rows)
logging.info('Загрузка документа остатков')
logging.info('Загрузка документа остатков завершена')
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 spisanie_filial(cursor, wsdl_client, prm_row_delta):
rows_header = get_spisanie_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):
continue
logging.warning('Загружаем списание')
header = wsdl_client.header_type(
document_type=2,
firma=cb_firma_id,
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, 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)
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_header['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)
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, 1)
logging.info(';'.join(
['Загрузка документа списание', row_header['docno'], 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']
]))