ENVIRONMENT DIVISION.
input-output section.
file-control.
select optional archivo-csv assign to "C:\ARTI.CSV"
organization is line sequential.
DATA DIVISION.
file section.
fd archivo-csv label records standard.
01 reg-csv pic x(280).
WORKING-STORAGE SECTION.
01 registro.
02 csv-articulo pic z(6).
02 pic x value ";".
02 csv-denominacion pic x(100).
02 pic x value ";".
02 csv-rubro pic x(35).
02 pic x value ";".
02 csv-cant-uni pic zzzz9,999.
02 pic x value ";".
02 csv-faccionado pic xx.
02 pic x value ";".
02 csv-kgfle pic zzzz9,999.
02 pic x value ";".
02 csv-porcaja pic zzzz9,999.
02 pic x value ";".
02 csv-porpallet pic zzzz9,999.
02 pic x value ";".
02 csv-desprom pic zzzz9,999.
02 pic x value ";".
02 csv-hasprom pic zzzz9,999.
02 pic x value ";".
02 csv-asociado pic z(6).
02 pic x value ";".
02 csv-suspendido pic xx.
02 pic x value ";".
02 csv-preciolibre pic xx.
02 pic x value ";".
02 csv-costo pic z(9)9,99.
02 pic x value ";".
02 csv-cosfec pic x(10).
02 pic x value ";".
02 csv-publico pic z(9)9,99.
02 pic x value ";".
02 csv-mayor pic z(9)9,99.
02 pic x value ";".
02 csv-iva pic z9,99.
01 wfraccionado pic xx.
01 wprelibre pic xx.
01 wsus pic xx.
01 wcosto pic 9(12)v99.
01 xfecha pic 9999/99/99.
01 redefines xfecha.
02 wfecha pic x(10).
01 wpublico pic 9(12)v99.
01 wmayor pic 9(12)v99.
01 RETORNO PIC S9(9) COMP-5.
01 COMANDO PIC x(4096).
PROCEDURE DIVISION.
#include "copys\declara-com.cpy".
comienzo.
*> paso el string de conexión - tengo un origen de datos configurado por ODBC
move "DSN=POWERBI;UID=sa;PWD=**********" to "ConnectionString" of BD.
*> conecto a la BBDD
invoke BD "Connect" returning RETORNO.
*> Elimino la BBDD
move spaces to comando.
move 'DROP TABLE IF EXISTS dbo."Articulos"' to comando.
INVOKE BD "Execute" USING comando RETURNING RETORNO.
*> Creo la BBDD
move spaces to comando.
string 'CREATE TABLE dbo."Articulos"' delimited by size
' ( ' delimited by size
'articulo integer, ' delimited by size
'denominacion character varying(100), ' delimited by size
'rubro character varying(35), ' delimited by size
'cant_uni numeric(8,3), ' delimited by size
'faccionado character varying(2), ' delimited by size
'kgfle numeric(8,3), ' delimited by size
'porcaja numeric(8,3), ' delimited by size
'porpallet numeric(8,3), ' delimited by size
'desprom numeric(8,3), ' delimited by size
'hasprom numeric(8,3), ' delimited by size
'asociado integer, ' delimited by size
'suspendido character varying(2), ' delimited by size
'preciolibre character varying(2), ' delimited by size
'costo numeric(12,2), ' delimited by size
'cosfec date, ' delimited by size
'publico numeric(12,2), ' delimited by size
'mayor numeric(12,2), ' delimited by size
'iva numeric(5,2) ' delimited by size
')' delimited by size
into comando
end-string.
INVOKE BD "Execute" USING comando RETURNING RETORNO.
open input arti nivel output archivo-csv.
move spaces to reg-csv.
string 'articulo;' delimited by size
'denominacion;' delimited by size
'rubro;' delimited by size
'cant_uni; ' delimited by size
'faccionado; ' delimited by size
'kgfle; ' delimited by size
'porcaja; ' delimited by size
'porpallet; ' delimited by size
'desprom; ' delimited by size
'hasprom; ' delimited by size
'asociado; ' delimited by size
'suspendido; ' delimited by size
'preciolibre; ' delimited by size
'costo; ' delimited by size
'cosfec' delimited by size
'publico' delimited by size
'mayor; ' delimited by size
'iva' delimited by size
into reg-csv
end-string.
write reg-csv.
initialize artcla.
start arti key is not < artcla invalid go sale.
leo.
read arti next at end go sale.
initialize nivcla.
move artniv1 to nivniv1.
read nivel invalid move spaces to nivdenom.
if artfracc-s
move "SI" to wfraccionado
else
move "NO" to wfraccionado
end-if.
if artsus-s
move "SI" to wsus
else
move "NO" to wsus
end-if.
if artpreciolibre-s
move "SI" to wprelibre
else
move "NO" to wprelibre
end-if.
move artcos to wcosto.
move artcos-fec to fecha-amd.
perform amd-to-dma.
perform valida-fecha.
if error-fecha = 1 or artcos-fec = zeros
move 20000101 to fecha-amd
end-if
move fecha-amd to xfecha.
compute wpublico rounded = artpre1 * (1 + artiva-ins / 100).
compute wmayor rounded = artpre2 * (1 + artiva-ins / 100).
move articulo to csv-articulo.
move artdenom to csv-denominacion.
move nivdenom to csv-rubro.
move artcant-uni to csv-cant-uni.
move wfraccionado to csv-faccionado.
move artkgfle to csv-kgfle.
move artporcaja to csv-porcaja.
move artporpallet to csv-porpallet.
move artdesprom to csv-desprom.
move zeros to csv-hasprom.
move artasociado to csv-asociado.
move wsus to csv-suspendido.
move wprelibre to csv-preciolibre.
move wcosto to csv-costo.
move wfecha to csv-cosfec.
move wpublico to csv-publico.
move wmayor to csv-mayor.
move artiva-ins to csv-iva.
inspect csv-denominacion replacing all ";" by " ".
inspect csv-rubro replacing all ";" by " ".
inspect registro replacing all "," by ".".
write reg-csv from registro.
go leo.
sale.
close arti nivel.
move spaces to comando.
string "BULK INSERT dbo.Articulos "
'FROM "c:\ARTI.CSV" '
'WITH ( '
' FIRSTROW = 2 '
", FIELDTERMINATOR = ';' "
", ROWTERMINATOR = '0x0a'); "
DELIMITED BY SIZE INTO comando
end-string.
INVOKE BD "Execute" USING comando RETURNING RETORNO.
INVOKE BD "Disconnect" RETURNING RETORNO.
exit program.