Пример #1
0
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)
Пример #2
0
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)
Пример #3
0
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]))
Пример #4
0
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')
Пример #5
0
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]))
Пример #6
0
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('Загрузка документа остатков завершена')
Пример #7
0
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]))
Пример #8
0
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]))
Пример #9
0
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]))
Пример #10
0
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('Загрузка документа остатков завершена')
Пример #11
0
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])
Пример #12
0
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]))
Пример #13
0
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]))
Пример #14
0
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']
        ]))