#!/home/antrks/perl5/perlbrew/perls/perl-5.20.3/bin/perl -w
# encoding: utf-8 (# coding: utf-8)

use warnings;
use strict;

use English;
use 5.014;

use DBI;
use Data::Dumper;
use utf8;
use Encode;
use Carp;
use DBD::Oracle qw(SQLCS_NCHAR :ora_types);
use Getopt::Long qw(GetOptions);

binmode STDOUT, ':utf8';
$ENV{NLS_LANG}  = 'AMERICAN_RUSSIA.AL32UTF8';
$ENV{NLS_NCHAR} = 'AL32UTF8';

my $oracle_dbh = DBI->connect(
    'dbi:Oracle:host=x.x.x.x;sid=hydra3;port=1521',
    'AIS_NET/ais_net',
    '',
    {   RaiseError          => 1,
        PrintError          => 1,
        ShowErrorStatement  => 0,
        AutoCommit          => 0,
        AutoInactiveDestroy => 1
    }
) or croak "Can't connect to oracle: $DBI::errstr\n";
$oracle_dbh->{ora_ph_csform} = SQLCS_NCHAR;

$oracle_dbh->do(
    qq{
    BEGIN
    MAIN.INIT(
        vch_VC_IP        => 'x.x.x.x',
        vch_VC_USER      => 'migrator',
        vch_VC_PASS      => 'a',
        vch_VC_APP_CODE  => 'NETSERV_ARM_ISP',
        vch_VC_CLN_APPID => 'import_pc_internet.pl');
        MAIN.SET_ACTIVE_FIRM(
        num_N_FIRM_ID => 100);
    END;
}
) or croak;

my $mysql_dbh  = DBI->connect( "DBI:mysql:database=traffstat:host=xxx", "xxx", "xxx" ) or die $DBI::errstr;
my $mysql2_dbh = DBI->connect( "DBI:mysql:database=bill:host=xxx", "xxx", "xxx" ) or die $DBI::errstr;

$mysql_dbh->{'mysql_enable_utf8'} = 1;
$mysql_dbh->do("SET NAMES utf8");

my $get_users_from_hd = $oracle_dbh->prepare(qq{SELECT N_DOC_ID,VC_REM  FROM SD_V_DOCUMENTS WHERE VC_REM LIKE 'client_contract.client_contract_id%'}) or die $oracle_dbh->errstr;
my $get_buh_id = $mysql2_dbh->prepare(qq{SELECT buh_id FROM client_contract WHERE client_contract_id = ?})  or die $mysql2_dbh->errstr;
my $get_tarif_info = $mysql_dbh->prepare(qq{SELECT tarifs.id, tarifs.name, tarifs.monthly as price, ceil(tarifs.speed/1000) as volume, ceil(tarifs.limitspeed/1000) as limitvolume, tarifs.daylimit
FROM tarifs,firms
WHERE firms.buh_id=? and tarifs.id=firms.tarif
ORDER BY tarifs.id}) or die $mysql_dbh->errstr;
$get_users_from_hd->execute() or die $DBI::errstr;
while ( my $hd_data = $get_users_from_hd->fetchrow_hashref() ) {
    if ($hd_data->{VC_REM} =~ /(\d+)/) {
        my $client_contract_id = $1;
        $get_buh_id->execute($client_contract_id);
        my $buh_id = $get_buh_id->fetchrow_hashref();
        $get_tarif_info->execute($buh_id->{buh_id});
        my $ref = $get_tarif_info->fetchrow_hashref();
        if ($ref->{id}) {
            $ref->{title} = 'Импортированный ПЦ №' . $ref->{id};
            my $sql;
            eval {
            #1. Формирование документа и родительской строки
            my $q_step1 = $oracle_dbh->prepare(qq{
                DECLARE
                num_N_DOC_ID                        SD_DOCUMENTS.N_DOC_ID%TYPE:=NULL;
                num_N_GOOD_ID                       SD_PRICE_ORDERS_C.N_GOOD_ID%TYPE:=NULL;
                num_N_LINE_NO           NUMBER;
                num_N_PRICE_ORDER_LINE_ID       SD_PRICE_ORDERS_C.N_PRICE_LINE_ID%TYPE:=NULL;
                BEGIN
                BEGIN
                    SELECT N_Doc_Id
                    INTO num_N_DOC_ID
                    FROM SD_DOCUMENTS
                    WHERE (N_Doc_State_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Actual') OR N_Doc_State_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Draft'))
                    AND VC_DOC_NO= :p_title_1;
                    EXCEPTION WHEN no_data_found THEN
                    num_N_DOC_ID := NULL;
                    END;
                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_T_PUT(
                num_N_DOC_ID => num_N_DOC_ID,
                num_N_DOC_TYPE_ID => SS_CONSTANTS_PKG_S.DOC_TYPE_PriceOrder,
                num_N_PARENT_DOC_ID => :p_n_doc_id,
                dt_D_DOC => TO_DATE('2011-01-19','YYYY-MM-DD'),
                dt_D_TIME => TO_DATE('2011-01-19','YYYY-MM-DD'),
                dt_D_BEGIN => TO_DATE('2011-01-19','YYYY-MM-DD'),
                num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                num_N_CALC_DESIGN_PROC_ID => SS_CONSTANTS_PKG_S.CALC_PROC_PriceWithTAX,
                num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                num_N_DEFER_TYPE_ID => SS_CONSTANTS_PKG_S.DEFER_TYPE_WO_Defer,
                num_N_SCHED_DEFER_TYPE_ID => SS_CONSTANTS_PKG_S.SCHED_DEFER_TYPE_From_Begin,
                num_N_WORKFLOW_ID => SS_CONSTANTS_PKG_S.WFLOW_PriceOrder,
                num_N_SCHED_DEFER_PAY_DAYS => 7,
                vch_VC_DOC_NO => :p_title_2,
                vch_VC_REM => :p_name,
                ch_C_FL_PURCHASING_PRICE => 'Y',
                num_N_FIRM_ID => 100
                );
                COMMIT;
                SELECT N_Doc_Id
                INTO num_N_DOC_ID
                FROM SD_DOCUMENTS
                WHERE (N_Doc_State_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Actual') OR N_Doc_State_ID = SYS_CONTEXT('CONST', 'DOC_STATE_Draft'))
                AND VC_DOC_NO= :p_title_3;

                SELECT N_Good_Id
                INTO num_N_GOOD_ID
                FROM Sr_Goods
                WHERE C_Active='Y' AND VC_REM = 'tarifs.id ' || :p_tarif_id;

                BEGIN
                    SELECT min(N_Price_Line_Id)
                    INTO num_N_PRICE_ORDER_LINE_ID
                    FROM Sd_Price_Orders_C
                    WHERE N_GOOD_ID = num_N_GOOD_ID;
                EXCEPTION WHEN no_data_found THEN
                    num_N_PRICE_ORDER_LINE_ID := NULL;
                END;

                -- родитель
                num_N_LINE_NO := 1000000;
                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => num_N_GOOD_ID,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => :p_price,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Unknown,
                    num_N_RATING_UNIT_ID => '',
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Base
                );

                END;
            });

            $q_step1->bind_param(":p_title_1",$ref->{title});
            $q_step1->bind_param(":p_n_doc_id",$hd_data->{N_DOC_ID});
            $q_step1->bind_param(":p_title_2",$ref->{title});
            $q_step1->bind_param(":p_name",$ref->{name});
            $q_step1->bind_param(":p_title_3",$ref->{title});
            $q_step1->bind_param(":p_tarif_id",$ref->{id});
            $q_step1->bind_param(":p_price",$ref->{price});
            $q_step1->execute();
            $oracle_dbh->commit();

#           sleep(1);

            #2. Генерруем состав
            my $q_step2 = $oracle_dbh->prepare(qq{
                DECLARE
                    num_N_DOC_ID                        SD_DOCUMENTS.N_DOC_ID%TYPE:=NULL;
                    num_N_GOOD_ID                       SD_PRICE_ORDERS_C.N_GOOD_ID%TYPE:=NULL;
                    num_N_LINE_NO           NUMBER;
                    num_N_PRICE_ORDER_LINE_ID       SD_PRICE_ORDERS_C.N_PRICE_LINE_ID%TYPE:=NULL;
                    num_N_PRICE_ORDER_P_LINE_ID     SD_PRICE_ORDERS_C.N_PRICE_LINE_ID%TYPE:=NULL;
                    BEGIN
                        BEGIN
                        SELECT N_Doc_Id
                        INTO num_N_DOC_ID
                        FROM Sd_Documents
                        -- WHERE (N_Doc_State_ID <> SYS_CONTEXT('CONST', 'DOC_STATE_Canceled'))  AND VC_DOC_NO= :p_title_1;
                        WHERE (N_Doc_State_ID <> SYS_CONTEXT('CONST', 'DOC_STATE_Canceled'))  AND VC_DOC_NO= :p_title_2;
                    EXCEPTION WHEN no_data_found THEN
                        num_N_DOC_ID := NULL;
                    END;
                    BEGIN
                    SELECT N_Good_Id
                    INTO num_N_GOOD_ID
                    FROM Sr_Goods
                    WHERE C_Active='Y' AND VC_REM = 'tarifs.id ' || :p_tarif_id;
                    EXCEPTION WHEN no_data_found THEN
                    num_N_GOOD_ID := NULL;
                    END;
                    IF num_N_GOOD_ID IS NOT NULL THEN
                    BEGIN
                        SELECT min(N_Price_Line_Id)
                        INTO num_N_PRICE_ORDER_LINE_ID
                        FROM Sd_V_Price_Orders_C
                        WHERE N_GOOD_ID = num_N_GOOD_ID;
                    EXCEPTION WHEN no_data_found THEN
                        num_N_PRICE_ORDER_LINE_ID := NULL;
                    END;
                    END IF;

                -- фиксируем родительскую услугу
                num_N_PRICE_ORDER_P_LINE_ID := num_N_PRICE_ORDER_LINE_ID;

                -- родитель
                num_N_LINE_NO := 1000000;
                -- лимит трафика (daylimit > 0) Интернет-трафик вх.
                IF :p_daylimit_1 > 0 THEN
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40213701,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MByte,
                    num_N_QUANT_RATING => 1,
                    num_N_QUANT_PRICE => 1,
                    num_N_RATING_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Byte,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_QUANT_LAST => :p_daylimit_2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_SPEED_VOLUME => :p_limitvolume,
                    num_N_SPEED_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MbitSec,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );
                END IF;

                -- Интернет-трафик вх.
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40213701,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MByte,
                    num_N_QUANT_RATING => 1,
                    num_N_QUANT_PRICE => 1,
                    num_N_RATING_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Byte,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_SPEED_VOLUME => :p_volume_1,
                    num_N_SPEED_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MbitSec,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );

                -- Интернет-трафик исх.
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40213501,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MByte,
                    num_N_QUANT_RATING => 1,
                    num_N_QUANT_PRICE => 1,
                    num_N_RATING_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Byte,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_SPEED_VOLUME => :p_volume_2,
                    num_N_SPEED_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MbitSec,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );

                -- Локальный трафик вх.
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40214201,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MByte,
                    num_N_QUANT_RATING => 1,
                    num_N_QUANT_PRICE => 1,
                    num_N_RATING_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Byte,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );

                -- Локальный трафик исх.
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40214001,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_MByte,
                    num_N_QUANT_RATING => 1,
                    num_N_QUANT_PRICE => 1,
                    num_N_RATING_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Byte,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO   => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );

                -- Доступ в Интернет
                IF num_N_PRICE_ORDER_LINE_ID IS NOT NULL THEN
                    num_N_PRICE_ORDER_LINE_ID := num_N_PRICE_ORDER_LINE_ID + 100;
                END IF;
                num_N_LINE_NO := num_N_LINE_NO + 1;

                SD_PRICE_ORDERS_PKG.SD_PRICE_ORDERS_C_PUT (
                    num_N_PRICE_ORDER_LINE_ID => num_N_PRICE_ORDER_LINE_ID,
                    num_N_PRICE_ORDER_DOC_ID => num_N_DOC_ID,
                    num_N_GOOD_ID => 40217401,
                    num_N_TAX_RATE_ID => SS_CONSTANTS_PKG_S.ANUM_DOC_String_Const,
                    num_N_CURRENCY_ID => SS_CONSTANTS_PKG_S.CURR_Ruble,
                    num_N_PRICE => 0,
                    num_N_SUM_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Math2,
                    num_N_UNIT_ID => SS_CONSTANTS_PKG_S.UNIT_Unknown,
                    num_N_QUANT_ROUNDING_ID => SS_CONSTANTS_PKG_S.ROUNDING_Ceil2,
                    num_N_LINE_NO => num_N_LINE_NO,
                    num_N_PAR_LINE_ID => num_N_PRICE_ORDER_P_LINE_ID,
                    num_N_PRICE_LINE_TYPE_ID => SS_CONSTANTS_PKG_S.PRC_LINE_TYPE_Period
                );
                -- вставляем
                -- изменяем состояние на актуальное
                SD_DOC_STATES_PKG.SD_DOCUMENTS_CHANGE_STATE(
                    num_N_DOC_ID   => num_N_DOC_ID,
                    num_N_Old_DOC_STATE_ID => CONST.DOC_STATE_Draft,
                    num_N_New_DOC_STATE_ID => CONST.DOC_STATE_Actual
                );
                END;
            });
#           $q_step2->bind_param(":p_title_1", $ref->{title});
            $q_step2->bind_param(":p_title_2", $ref->{title});
            $q_step2->bind_param(":p_tarif_id",$ref->{id});
            $q_step2->bind_param(":p_daylimit_1",$ref->{daylimit});
            $q_step2->bind_param(":p_daylimit_2",$ref->{daylimit});
            $q_step2->bind_param(":p_limitvolume",$ref->{limitvolume});
            $q_step2->bind_param(":p_volume_1",$ref->{volume});
            $q_step2->bind_param(":p_volume_2",$ref->{volume});
            $q_step2->execute();
            $oracle_dbh->commit();
            };
            print "buh_id=> $buh_id->{buh_id}".($@ ? ' error: '.$@ : '')."\n";
            sleep(1);
        }
    }
}