#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long;
use Data::Dumper;
use Time::Local;
use Math::BigFloat;
use File::Basename;
use POSIX qw(strftime);
# Clase para manejar los parámetros de entrada
package InputParser {
use strict;
use warnings;
use Getopt::Long;
sub new {
my ($class, $argv_ref) = @_;
my %options;
GetOptions(
\%options,
'totallines|l',
'mobileoperatorid|m',
'invoicedate|d=s',
'verbose|debug|v',
'output|o=s',
'env|e=s',
'help|h|?',
'man|m',
) or usage();
my $self = {
totallines => $options{totallines},
mobileoperatorid => $options{mobileoperatorid},
invoicedate => $options{invoicedate},
verbose => $options{verbose},
output => $options{output},
env => $options{env},
help => $options{help},
man => $options{man},
};
bless $self, $class;
return $self;
}
sub usage {
my $message = <<'EOS';
Usage: extract_rmca.pl [options]
-l, --totallines
-m --mobileoperatorid
-d, --invoicedate Invoice date
-v, --verbose Verbose mode
-o, --output Output directory
-e, --env Environment
-h, --help Print usage information
-m, --man Print detailed manual
EOS
print $message;
exit 1;
}
}
# Clase para manejar los logs
package Logger {
use strict;
use warnings;
sub new {
my ($class, $log_file) = @_;
open(my $fh, '>>', $log_file) or die "No se puede abrir el archivo '$log_file': $!";
my $self = { fh => $fh };
bless $self, $class;
return $self;
}
sub log {
my ($self, $message) = @_;
my ($sec, $min, $hour, $mday, $mon, $year) = localtime();
$mon++;
$year += 1900;
my $fecha_log = sprintf("%02d/%02d/%4d %02d:%02d:%02d", $mday, $mon, $year, $hour, $min, $sec);
my $fh = $self->{fh};
printf $fh "%s - %s\n", $fecha_log, $message;
print "$fecha_log - $message\n";
}
sub close {
my ($self) = @_;
close $self->{fh};
}
}
# Clase para manejar la consulta de facturas
package InvoiceQuery {
use strict;
use warnings;
use File::Spec;
use Data::Dumper;
use JSON;
sub new {
my ($class, $sqlplus_cmd, $verbose, $delay_log, $dir_out, $logger,$invoice_counter, $num_package) = @_;
my $lote =0;
my $self = {
sqlplus_cmd => $sqlplus_cmd,
verbose => $verbose,
delay_log => $delay_log,
dir_out => $dir_out,
logger => $logger,
invoice_counter => $invoice_counter,
num_package => $num_package,
lote => $lote
};
bless $self, $class;
return $self;
}
sub convert_to_json {
my ($self, $line,$logger,$lote) = @_;
my %json;
my %invoice;
my @invoice_lines;
my @taxes;
my @fields = split /;/, $line;
$invoice{'id'} = $fields[0];
$invoice{'doc_date'} = $fields[1];
$invoice{'doc_type'} = $fields[2];
$invoice{'payment_type'} = $fields[3];
$invoice{'invoice_type'} = $fields[4];
$invoice{'start_cycle_date'} = $fields[5];
$invoice{'end_cycle_date'} = $fields[6];
$invoice{'posting_date'} = $fields[7];
$invoice{'due_date'} = $fields[8];
$invoice{'currency'} = $fields[9];
$invoice{'tax_id'} = $fields[10];
$invoice{'iban'} = $fields[11];
$invoice{'biller'} = $fields[12];
$invoice{'crm_id'} = $fields[13];
$invoice{'total_amount'} = $fields[14];
$invoice{'deal_credit_card'} = $fields[15];
$invoice{'tenant'} = $fields[16];
$invoice{'segment'} = $fields[17];
$invoice{'credit'} = $fields[18];
$invoice{'invoices_references'} = [];#$fields[19];
my %invoice_line;
my @concept_lines;
$invoice_line{'id'} = $fields[20];
$invoice_line{'contract_id'} = $fields[21];
#$invoice_line{'operator'} = $fields[22];
my %concept_line;
$concept_line{'code'} = $fields[23];
$concept_line{'base_amount'} = $fields[24];
#$concept_line{'product'} = $fields[25];
$concept_line{'description'} = $fields[26];
$concept_line{'tax_code'} = $fields[27];
$concept_line{'tracking_id'} = $fields[28];
push @concept_lines, \%concept_line;
$invoice_line{'concept_lines'} = \@concept_lines;
push @invoice_lines, \%invoice_line;
#$invoice{'invoice_lines'} = \@invoice_lines;
$invoice{'invoice_lines'} =[];
$invoice{'out_of_invoice_lines'} = \@invoice_lines;#$fields[29];
my %tax;
$tax{'tax_base'} = $fields[29];
$tax{'tax_amount'} = $fields[30];
$tax{'tax_total'} = $fields[31];
$tax{'tax_code'} =$fields[32];
#TODO $fields[33]; es el lote
push @taxes, \%tax;
$invoice{'taxes'} = \@taxes;
my $json_str = encode_json \%invoice;
return $json_str
}
sub execute {
my ($self) = @_;
my $logger = $self->{logger};
my $condicionadicional_account="and 1=1";
my $invoicetype=1;
my $cond_mo="AND 1=1";
my $invoice_date="AND 1=1";
my $cond_paydate="AND 1=1";
# Consulta de facturas a remesar
my $sql_invoices = <<"SQL_CONT";
@{[ $self->{sqlplus_cmd} ]} << SQL_1
WHENEVER SQLERROR EXIT 1
SET FEEDBACK OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON LINE 32767 ARRAYSIZE 200
alter session set NLS_NUMERIC_CHARACTERS= '.,';
VARIABLE ACCOUNTID NUMBER;
/
--id_process MAX14
SELECT INVOICENR --id
||';'|| TO_CHAR(IH.INVOICEDATE,'DD-MM-YY HH24:MI:SS') --doc_date
||';'|| 'falta' --doc_type
||';'|| 'D' --payment_type
||';'|| IH.INVOICETYPE --invoice_type
||';'|| TO_CHAR(IH.INIINVOICEPERIOD,'DD-MM-YY HH24:MI:SS') --start_cycle_date
||';'|| TO_CHAR(IH.ENDINVOICEPERIOD,'DD-MM-YY HH24:MI:SS') --end_cycle_date
||';'|| TO_CHAR(IH.INVOICEDATE,'DD-MM-YY HH24:MI:SS') --posting_date
||';'|| TO_CHAR(IH.PAYDATE,'DD-MM-YY HH24:MI:SS')--due_date
||';'|| 'EUR' --currency
||';'|| IH.DOCUMENTNUMBER --tax_id
||';'|| IH.BANKACCOUNT --iban
||';'|| 'MYSIM' --biller
||';'|| IH.ACCOUNTID --crm_id
||';'|| IH.INVOICETOTALPAY --total_amount
||';'|| '' --deal_credit_card
||';'|| M.TENANT --tenant
||';'|| 'telco' --segment
||';'|| 'false' --credit
||';'|| IH.INVOICENR --invoices_references
--invoice_lines
||';'|| 'vacio por ahora' --id_lines
||';'|| A.ACCOUNTNR --contract_id
||';'|| IH.MOBILEOPERATORID --operator
-- concept_lines
||';'|| '999' --code
||';'|| IH.BASEVALUE --base_amount
||';'|| '999' --product
||';'|| IH.INVOICENR --description
||';'|| '0' --tax_code_lines
||';'|| IH.INVOICENR || IH.ACCOUNTID --tracking_id idFactura+idlinea(60)
--TODO||';'|| 0 --out_of_invoice_lines aqui va
--taxes
||';'|| IH.BASEVALUE --tax_base
||';'|| IH.TAXVALUE --tax_amount
||';'|| '0' --tax_total
||';'|| '0' --tax_code
||';'|| M.LOGISTICOPERATORPREFIX || TO_CHAR(IH.INVOICEDATE, 'YYYYMMDD') --N Lote
||';'
FROM APOLLO_PROP.INVOICEHEADER IH
INNER JOIN APOLLO_PROP.ACCOUNT A ON IH.ACCOUNTID = A.ACCOUNTID
INNER JOIN APOLLO_PROP.MOBILEOPERATOR M ON IH.MOBILEOPERATORID = M.MOBILEOPERATORID
WHERE
NEWINVOICE ='Y'
--AND IH.INVOICEDATE > SYSDATE -3
--AND IH.ACTDATE > SYSDATE -1
--AND IH.INVOICENR IN('LC230000000144' ,'MV23000045')
AND INVOICEDATE = TO_DATE('2023-04-01', 'YYYY-MM-DD')
AND IH.MOBILEOPERATORID=$self->{mobileoperatorid} --TODO: end
;
quit
SQL_1
SQL_CONT
$logger->log("Consulta: \n$sql_invoices") if $self->{verbose};
#0. Verificar directorios
my $dir_files = './data';
unless (-d $dir_files) {
mkdir $dir_files or die "No se pudo crear el directorio $dir_files: $!";
}
my $dir_filesQuery = './data/query';
unless (-d $dir_filesQuery) {
mkdir $dir_filesQuery or die "No se pudo crear el directorio $dir_filesQuery: $!";
}
# 1. Obtener el número incremental del nombre
#my $date = localtime->strftime("%Y%m%d");
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
my $date = sprintf("%04d%02d%02d", $year+1900, $mon+1, $mday);
my $count = 0;
my $id_process = "$self->{lote}${count}";
my @invoices;
my $filename = "${dir_files}/${date}_${count}.json";
$filename = File::Spec->catfile($self->{dir_out}, $filename);
open(my $fh, '>', $filename) or die $logger->log("No se puede crear el archivo $filename: $!");
my $log_filename = File::Spec->catfile($self->{dir_out}, "${dir_filesQuery}/${date}_RMCA_PROCESS.log");
open(my $log_fh, '>', $log_filename) or die $logger->log("No se puede crear el archivo $log_filename: $!");
open(my $sql_fh, '-|', $sql_invoices) or die $logger->log("No se puede ejecutar el comando SQL: $!");
# 2. Leer resultados de $sql_fh y almacenarlos en el archivo
while (<$sql_fh>) {
chomp;
$logger->log("leyendo resultados: $_") if $self->{verbose};
$self->{total_invoices}++;
#TODO COMENTAR log_fh
print $log_fh "$_\n"; # Imprimir en el archivo de log
my $invoice = $self->convert_to_json($_);
my $json_invoice = decode_json($invoice);
push @invoices, $json_invoice;
$logger->log("JSON line: $invoice") if $self->{verbose};
# 3. Escribir la línea en formato JSON al archivo de salida
$self->{invoice_counter} = $self->{invoice_counter}+1;
if ($self->{invoice_counter} % $self->{num_package} == 0) {
#$logger->log("LOTE:$self->{lote}${count}");
my %json = (
#'id_process' => $id_process,
'id_process' => "$self->{lote}${count}",#TODO: Lote doesnt work
'invoices' => \@invoices
);
my $json_str = encode_json \%json;
print $fh "$json_str\n";
@invoices = ();
close($fh);
$count++;
$id_process = "$self->{lote}${count}";
$filename = "${dir_files}/$self->{lote}_${count}.json"; #TODO: Concatenate
$filename = File::Spec->catfile($self->{dir_out}, $filename);
open($fh, '>', $filename) or die $logger->log("No se puede crear el archivo $filename: $!");
}
}
#Ultimo archivo------------------------
my %json = (
#'id_process' => $id_process,
'id_process' => "$self->{lote}${count}", #TODO: Lote doesnt work
'invoices' => \@invoices
);
my $json_str = encode_json \%json;
print $fh "$json_str\n";
close($sql_fh);
close($fh);
close($log_fh);
#-----------------------------------------
$logger->log("Consulta: \n$sql_invoices") if $self->{verbose};
return $self->{invoice_counter};
}
}
# Programa principal
my $input_parser = InputParser->new(\@ARGV);
my $num_package = $3 || 100;
# Verificar que el directorio de salida exista o crearlo
my $dir_logs = './LOG';
unless (-d $dir_logs) {
mkdir $dir_logs or die "No se pudo crear el directorio $dir_logs: $!";
}
# Creamos una instancia de la clase Logger
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
my $logDate = sprintf("%04d%02d%02d", $year+1900, $mon+1, $mday);
#my $logDate = localtime->strftime("%Y%m%d");
my $logger = Logger->new("${dir_logs}/extract_rmca$logDate.log");
$logger->log('INI PROCESS');
my $start_time = time();
# Default values
my $invoice_counter = 0;
my $delay_log = 60;
my $total_lines_size = 100;
#TODO sistema mostrar totales cada 60 segundos invoice_counter
my $sqlplus_cmd = "sqlplus -s MAS-BILLING/Pr3pr0B1ll1\@EMYSIM";#env -> $SQLPLUS_CONECTION
my $dir_out = $input_parser->{output} || ".";
# Creamos una instancia de la clase InvoiceQuery
my $invoice_query = InvoiceQuery->new($sqlplus_cmd, $input_parser->{verbose}, $delay_log, $dir_out, $logger, $invoice_counter, $num_package);
# Ejecutamos la consulta
$invoice_counter = $invoice_query->execute();
my $elapsed_time_h = (timelocal(localtime)- $start_time)/3600;
my $elapsed_time_m = ((timelocal(localtime)- $start_time) / 60) % 60;
my $elapsed_time_s = (timelocal(localtime)- $start_time)%60;
my $elapsedString = sprintf("%02d:%02d:%02d", $elapsed_time_h, $elapsed_time_m, $elapsed_time_s);
$logger->log("Time exec: $elapsedString");
$logger->log("Number items: $invoice_counter");
$logger->log("END PROCESS");
$logger->close(); Write, Run & Share Perl code online using OneCompiler's Perl online compiler for free. It's one of the robust, feature-rich online compilers for Perl language, running on the latest version 5.22.1. Getting started with the OneCompiler's Perl compiler is simple and pretty fast. The editor shows sample boilerplate code when you choose language as Perl and start coding.
OneCompiler's Perl online editor supports stdin and users can give inputs to programs using the STDIN textbox under the I/O tab. Following is a sample Perl program which takes name as input and prints hello message with your name.
my $name = <STDIN>;
print "Hello $name.\n";
Perl(Practical Extraction and Report Language) is especially desined for text processing by Larry Wall.
There is no need to specify the type of the data in Perl as it is loosely typed language.
| Type | Description | Usage |
|---|---|---|
| Scalar | Scalar is either a number or a string or an address of a variable(reference) | $var |
| Arrays | Array is an ordered list of scalars, you can access arrays with indexes which starts from 0 | @arr = (1,2,3) |
| Hash | Hash is an unordered set of key/value pairs | %ul = (1,'foo', 2, 'bar) |
In Perl, there is no need to explicitly declare variables to reserve memory space. When you assign a value to a variable, declaration happens automatically.
$var-name =value; #scalar-variable
@arr-name = (values); #Array-variables
%hashes = (key-value pairs); # Hash-variables
If, If-else, Nested-Ifs are used when you want to perform a certain set of operations based on conditional expressions.
if(conditional-expression){
//code
}
if(conditional-expression){
//code if condition is true
}else{
//code if condition is false
}
if(condition-expression1){
//code if above condition is true
}else if(condition-expression2){
//code if above condition is true
}
else if(condition-expression3){
//code if above condition is true
}
...
else{
//code if all the conditions are false
}
There is no case or switch in perl, instead we use given and when to check the code for multiple conditions.
given(expr){
when (value1)
{//code if above value is matched;}
when (value2)
{//code if above value is matched;}
when (value3)
{//code if above value is matched;}
default
{//code if all the above cases are not matched.}
}
For loop is used to iterate a set of statements based on a condition.
for(Initialization; Condition; Increment/decrement){
// code
}
While is also used to iterate a set of statements based on a condition. Usually while is preferred when number of iterations are not known in advance.
while(condition) {
// code
}
Do-while is also used to iterate a set of statements based on a condition. It is mostly used when you need to execute the statements atleast once.
do {
// code
} while (condition);
Sub-routines are similar to functions which contains set of statements. Usually sub-routines are written when multiple calls are required to same set of statements which increases re-usuability and modularity.
sub subroutine_name
{
# set of Statements
}
subroutine_name();
subroutine_name(arguments-list); // if arguments are present