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(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 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 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 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'] ]))