#!/usr/bin/perl

use strict;
use DBI;


require "/etc/cherry/cherry.cfg";

require "/usr/lib/cherry/dateutils";
require "$CFG::BASE/dicts/russian.dict";


my $sth;
my $select;
my $rv;

my $dbh = DBI->connect("dbi:$CFG::DB_TYPE:dbname=$CFG::DB_NAME;host=$CFG::DB_HOST",
                    "$CFG::DB_USER",
                    "$CFG::DB_USER_PASSWORD",
                    { RaiseError => 1, AutoCommit => 0 });



my $mbegin = previous_month_begin( time2str(time()) ) ;
my $mend = month_end(previous_month_begin( time2str(time())) )  . ' 23:59:59';

my $oper_id='RC';


my $service_type='internet';
my $service_date = $mend;
my ($service_descr)=$dbh->selectrow_array("SELECT descr FROM bill_service_types WHERE oper_id='$oper_id' AND service_type='$service_type'");

$service_descr=month_name($service_date) . " " . $service_descr;

my $bill_type= 'invoice';
my $bill_date = $mend;
my $bill_currency_type = 'RUB'; 


$select = 
"SELECT ".
"client_id,currency_type,sum(amount) " .
"FROM base_sessions_view ".
"WHERE oper_id='$oper_id' AND session_begin BETWEEN '$mbegin' AND '$mend' AND session_type='inet_fixed_ip' ".
"GROUP BY client_id,currency_type "
;


$sth = $dbh->prepare($select);
$rv = $sth->execute;


while( my ($client_id,$service_currency_type,$service_sum) =  $sth->fetchrow_array) {

	my ($service_id) = $dbh->selectrow_array("SELECT nextval('bill_given_services_id_seq') ");

	#services
	my $sql = "INSERT INTO bill_given_services " .
	"(service_id,oper_id,client_id,service_date,currency_type,service_type,service_sum,descr,unit, " .
	"first_operator,first_date) ".
	"VALUES ".
	"('$service_id','$oper_id','$client_id','$service_date','$service_currency_type','$service_type',$service_sum,'$service_descr','RUB','autobill','$mend')";

	my $rv = $dbh->do( $sql );


	my ($bill_number,$number_format) = $dbh->selectrow_array("SELECT bill_number,number_format FROM bill_bill_types WHERE oper_id='$oper_id' AND bill_type='$bill_type'");

	$bill_number++;

        $dbh->do("UPDATE bill_bill_types SET bill_number=$bill_number WHERE oper_id='$oper_id' AND bill_type='$bill_type' ");

	my ($bill_id) = $dbh->selectrow_array("SELECT nextval('base_bills_id_seq') ");

	my($client_type)= $dbh->selectrow_array("SELECT client_type FROM base_clients WHERE oper_id='$oper_id' AND client_id='$client_id' ");

        my ($rate) = $dbh->selectrow_array("SELECT base_currency_history.rate FROM base_currency_history WHERE base_currency_history.currency_from = '$service_currency_type' AND base_currency_history.currency_to = '$bill_currency_type' AND base_currency_history.date <= '$bill_date'  order by base_currency_history.date desc limit 1 ");

	my  ($taxes) = $dbh->selectrow_array("SELECT taxes FROM bill_bill_taxes WHERE oper_id='$oper_id' AND ( client_type = '$client_type' OR client_type='*') AND ( bill_type = '$bill_type' OR bill_type='*') AND date<='$service_date' ORDER BY date DESC");

        my $tax_rate = 1;
        foreach my $tax (split(' ', $taxes)) {
                $tax_rate *= ($tax + 100) / 100;
        }


        my $bill_sum_without_taxes= $service_sum * $rate ;
        my $bill_sum= $bill_sum_without_taxes * $tax_rate;


	
	#bills

        $dbh->do("INSERT INTO bill_bills (oper_id,client_id,bill_id,bill_date,bill_number,currency_type,bill_type,bill_sum_without_taxes,bill_sum,first_operator,first_date) VALUES ('$oper_id','$client_id','$bill_id','$bill_date','$bill_number','RUB','$bill_type',$bill_sum_without_taxes,$bill_sum, 'autobill','$mend')");


	#bill_services

        $dbh->do("INSERT INTO bill_bill_services (oper_id,client_id,bill_id,service_id,service_sum,service_sum_with_taxes,first_operator,first_date) VALUES ('$oper_id','$client_id','$bill_id','$service_id',$bill_sum_without_taxes,$bill_sum,'autobill','$mend')");

	
}


$dbh->commit;
$dbh->disconnect;








