Migracion DAP 2021

script.sql

set @dap_wf_service_id = 140;
set @tce_id = 36582;
set @jumarome_id = 259618;
set @dap_service_type = 6;
set @warehouse_id = 620;
set @migration_description = 'bodega_dap';
set @support_user_id = 260408;




# Creacion de Workflowservice para DAP
insert into workflow_service(id,org_id,name,type,active,created_at,creator_id,user_required, category)
select @dap_wf_service_id,@tce_id,'Depósito Aduanero Público',@dap_service_type,1,NOW(),@jumarome_id,0,0;

# Creacion de bodegas para DAP
# Bodegas de DAP empezaran en id: 620

insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 620,'Bodega 1',1,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;

insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 621,'Bodega 2',1,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;

insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 622,'Bodega 3',1,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;

insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 623,'Bodega 4',1,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;


insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 624,'Bodega 8',1,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;
select count(*) from persona where descripcion = 'bodega_dap';

# Esta sera la bodega de distribucion
insert into warehouse(id,name,warehouse_type,country_id,city,address,inventory_managed,organization_id,creator_id,
                      created_at,service_id)
select 1000,'Bodega de Distribución DAP',3,2734,'Guayaquil','AV. DE LAS AMERICAS',1,@tce_id,@support_user_id,NOW(),@dap_wf_service_id;


# Migracion de clientes

# Creacion de registros en tabla persona
insert into persona(tce_persona_id,anulado,fecha_creacion,fecha_modificacion,nombre,identificacion,
                              identificacion_tipo,direccion,telefono,org_id,org_space_id,descripcion)
select c.id,0,NOW(),NOW(),c.nombre,c.identificacion,1,c.direccion,c.telefono,@tce_id,32,@migration_description
from tcedap.dap_clientes c
left join persona p on p.identificacion = c.identificacion and p.org_id = @tce_id  and p.anulado = 0
where p.persona_id is null  and c.anulado = 0;


# Creacion de registros en tabla cuenta2
insert into cuenta2(persona_id,credito_deudor,credito_acreedor,network_connection_status,is_client,pais_id,com_language)
select persona_id,0,0,0,1,2734,2 from starcargo.persona where descripcion = @migration_description;

# Creacion de tipos para clientes creados
insert into cuenta_tipo(cuenta_persona_id,cuenta_tipo_catalogo_id)
select c.persona_id,1 from starcargo.cuenta2 c
inner join starcargo.persona p on c.persona_id = p.persona_id
and (p.descripcion = @migration_description);

# creacion de eventos de creacion de clientes
insert into order_event(resource_type,order_id,event_type,author_name,author_reference,event_date,creation_date,user_id)

select 5 resource_type, c.persona_id resource_key,651 event_type, 'Support Terminal de Cargas del Ecuador' author_name,
       'support' author_reference,p.fecha_creacion event_date, p.fecha_creacion creation_date,@support_user_id
from cuenta2 c
inner join starcargo.persona p on c.persona_id = p.persona_id
where p.descripcion = @migration_description
and p.fecha_eliminacion is null ;

# Se necesitara actualizar la referencia de los clientes

update persona p
inner join tcedap.dap_clientes c
on p.identificacion = c.identificacion
set p.tce_persona_id = c.id
where p.org_id = @tce_id
and c.anulado = 0;

# Actualizacion de columnas representante legal e identificacion de representnate legal
update cuenta2 c
    inner join persona p on p.persona_id = c.persona_id
    inner join tcedap.dap_clientes dc on dc.id = p.tce_persona_id

set c.legal_representative = dc.representante_legal,
    c.legal_representative_identification = dc.identificacion_representante_legal;


#Migracion de productos

# creacion de columna auxiliar para referenciar producto


alter table inventory_product add column tce_producto_id int;
#creacion de indice de nota_pedido en tabla dap_producto:
create index dap_producto__nota_pedido_index
	on tcedap.dap_producto (nota_pedido_id);
create index persona__deposito_persona_index
	on starcargo.persona (tce_persona_id);


# No se puede migrar productos que no tienen codigo de referencia
# El codigo de referencia sera el id natural que identificara al producto
insert into inventory_product(name, created_at,creator_id,organization_id,client_id,product_code, hs_code, brand_name, total_suppliers)
SELECT dp.descripcion,dp.fecha_creacion ,@support_user_id,@tce_id,p.persona_id, dp.cod_referencia, dp.cod_subpartida, dp.marca, 0
FROM tcedap.dap_producto dp
INNER JOIN tcedap.nota_pedido_java np ON np.nota_pedido_id = dp.nota_pedido_id
INNER JOIN persona p on p.tce_persona_id = np.cliente_id
WHERE np.id_contrato_relacionado IS NULL
AND np.anulado = 0
AND dp.anulado = 0
and dp.cod_referencia <> ''
and dp.cod_referencia not in(
    select product_code from inventory_product
    where product_code is not null and deleted_at is null )
GROUP BY np.cliente_id,dp.cod_referencia;


# creacion de indice en tabla de productos
create index inventory_product__product_code_index
	on inventory_product (product_code);

# Migracion de ordenes

alter table ot add column tce_nota_pedido_id int null ;

alter table ot modify nota_pedido varchar(200) null;


insert into ot(inventory_scope,org_id, entidad, warehouse_id, cliente_persona_id, operation_date, contract_number, nota_pedido, departure_authorization_date,
currency_id, inventory_status, anulado, estado, creador_persona_id, fecha_creacion,fecha_modificacion, tce_nota_pedido_id)
select 1,36582 org_id,
       if(dr.id_contrato_relacionado is not null,3,2) entity,
       620 warehouse_id,
       p.persona_id,
       ifnull(dr.fecha_solicitud, dr.fecha_creacion) operation_date,
       dr.codigo_alterno numero_contrato,
       dr.numero_pedido,
       dr.fecha_autorizacion_salida,
       case
           when dr.moneda = 'USD (DOLARES)' or dr.moneda = 'DOLAR' or dr.moneda = 'DOLARES' then 1
           when dr.moneda = 'EUR' or dr.moneda = 'EUROS' then 25
           when dr.moneda = 'JPY' or dr.moneda = 'EUROS' then 44
           else null
       end currency_id,
       if(dr.estado like '%COMPLET%',1,0) inventory_status,
       0 anulado,
       if(dr.estado like '%COMPLET%' or dr.estado like '%AUTORIZADA%',2,1) estado,
       259619 creator_id,
       dr.fecha_creacion,
       dr.fecha_creacion,
       dr.nota_pedido_id
from tcedap.nota_pedido_java dr
inner join persona p on dr.cliente_id = p.tce_persona_id
left join ot o on dr.nota_pedido_id = o.tce_nota_pedido_id
where dr.anulado = 0
and o.tce_nota_pedido_id is null;



insert into ot_tramite(tramite_id, medio_transporte, puerto_origen_catalogo_base_id, puerto_destino_catalogo_base_id)
select o.tramite_id,
       case
           when dr.medio_transporte = 'AEREO' then 2
           when dr.medio_transporte = 'MARITIMO' then 1
           else 1
       end medio_transporte,
       IFNULL(cp.id_puerto,(select pu.id_puerto
					from cae_puerto pu
					inner join pais pa on pu.pais = pa.id_catalogo_base
					where pu.descripcion = dr.puerto_embarque && pa.nombre_es = dr.pais_origen
					limit 1)) puerto_origen_id,
       case
           when dr.puerto_desembarque = 'GYE' or dr.moneda = 'GUAYQUIL' or dr.moneda = 'GUAYAQUIL' then 29849
           when dr.puerto_desembarque = 'MANTA' then 4664
           when dr.puerto_desembarque = 'ESMERALDAS' then 4653
           when dr.puerto_desembarque = 'PUERTO BOLIVAR' then 4671
           when dr.puerto_desembarque = 'ESMERALDAS' then 4653
           else null
       end puerto_destino_id
from tcedap.nota_pedido_java dr
inner join ot o on o.tce_nota_pedido_id = dr.nota_pedido_id
left join cae_puerto cp on dr.puerto_embarque = cp.codigo
left join ot_tramite t on o.tramite_id = t.tramite_id
where
dr.anulado = 0
and t.tramite_id is null;

update ot
set codigo = CONCAT('LH',tramite_id)
where tce_nota_pedido_id is not null;

# Registrar Servicios en


# crear servicios de dap
insert into warehouse_workflow_service(creator_id, created_at, warehouse_id, workflow_service_id)
select 260408,NOW(),id,service_id from warehouse where organization_id = 36582
and service_id = 140;

# Seteo de warehouse_service para ordenes y paquetes
update ot_tramite t
inner join ot o on o.tramite_id = t.tramite_id
inner join warehouse w on w.id = o.warehouse_id
set t.warehouse_service_id = w.service_id;

# Seteo de warehouse_service para paquetes de bodega simple
update ot_carga_detalle cd
inner join ot on ot.tramite_id = cd.ot_carga_id
inner join warehouse w on w.id = ot.warehouse_id
set cd.warehouse_service_id = w.service_id;

# Seteo de warehouse_service para paquetes de bodega simple
update ot_carga_detalle cd
inner join ot on ot.tramite_id = cd.receipt_order_id
inner join warehouse w on w.id = ot.warehouse_id
set cd.warehouse_service_id = w.service_id;



-- Fin de migracion ordenes

-- Migracion de Lotes de entrada

alter table inventory_operation add column tce_batch_id int null;

insert into inventory_operation(tce_batch_id,creator_id,modifier_id,created_at,operation_type,order_id,inventory_product_id,quantity,total_available,calculation_mode,cargo_value,insurance_value,freight_value)

select dp.id, @support_user_id,@support_user_id,dp.fecha_creacion,1 operation_type, o.tramite_id tramite_id,
      p.id product_id,dp.cantidad_inicial,
       (dp.cantidad_inicial - dp.cantidad_utilizada),2 calculation_mode,coalesce(dp.precio_unitario,0.00),
       coalesce(dp.seguro_item,0.00),coalesce(dp.flete_item,0.00)
from tcedap.dap_producto dp
inner join ot o  on  o.tce_nota_pedido_id = dp.nota_pedido_id
inner join tcedap.nota_pedido_java np on np.nota_pedido_id = o.tce_nota_pedido_id
inner join inventory_product p on p.product_code = dp.cod_referencia
WHERE np.id_contrato_relacionado IS NULL
and p.organization_id = @tce_id and p.deleted_at is null
and p.client_id = o.cliente_persona_id
and o.entidad = 2
and np.anulado = 0
and dp.anulado = 0;

# migracion de product-out
insert into inventory_product_out(storage_order_id, inventory_product_id, quantity, picking_strategy, creator_id, created_at)
select t.tramite_id, t.invetory_product_id, t.quantity, 0 picking_strategy, 259619 creator_id, now() created_at
from (
		select o.tramite_id, p.id invetory_product_id, sum(dp.cantidad_inicial) quantity
		from tcedap.dap_producto dp
		inner join tcedap.nota_pedido_java dn on dp.nota_pedido_id = dn.nota_pedido_id
		inner join tcedap.dap_clientes dc on dn.cliente_id = dc.id
		inner join persona c on dc.identificacion = c.identificacion and c.org_id = 36582
		inner join inventory_product p on (c.persona_id = p.client_id and dp.cod_referencia = p.product_code)
		inner join ot o on dn.nota_pedido_id = o.tce_nota_pedido_id
		where dn.id_contrato_relacionado is not null
		and dn.anulado = 0
		and dp.anulado = 0
		group by dp.nota_pedido_id, cod_referencia) t
left join inventory_product_out ipo on t.tramite_id = ipo.storage_order_id and ipo.inventory_product_id = t.invetory_product_id
where ipo.id is null;

# migracion de out batches
insert into inventory_out_batch(inventory_out_id, inventory_batch_id, quantity, creator_id, created_at)
select ipo.id, ib.id, dp.cantidad_inicial, 259619 creator_id, dp.fecha_creacion
from tcedap.dap_producto dp
inner join tcedap.nota_pedido_java dn on dp.nota_pedido_id = dn.nota_pedido_id
inner join tcedap.dap_producto_informativa dpi on dp.id_producto_relacionado = dpi.id
inner join tcedap.dap_producto dpr on dpi.id_producto_relacionado = dpr.id
inner join tcedap.nota_pedido_java dnr on dpr.nota_pedido_id = dnr.nota_pedido_id
inner join tcedap.dap_clientes dc on dn.cliente_id = dc.id
inner join persona c on dc.identificacion = c.identificacion and c.org_id = 36582
inner join inventory_product p on (c.persona_id = p.client_id and dp.cod_referencia = p.product_code)
inner join ot o on dn.nota_pedido_id = o.tce_nota_pedido_id
inner join inventory_product_out ipo on o.tramite_id = ipo.storage_order_id and p.id = ipo.inventory_product_id
inner join inventory_operation ib on dpi.id_producto_relacionado = ib.tce_batch_id
left join inventory_out_batch iob on iob.inventory_out_id = ipo.id and iob.inventory_batch_id = ib.id
where dn.id_contrato_relacionado is not null
and dn.anulado = 0
and dp.anulado = 0
and dnr.anulado = 0
and dpr.anulado = 0
and iob.id is null
;


# Definicion de ubicaciones para bodegas
# Ver agrupacion de bodegas

select ubicacion,count(o.id) from tcedap.dap_operacion o
inner join tcedap.nota_pedido_java np on np.nota_pedido_id = o.nota_pedido_id
where o.anulado = 0 and np.anulado = 0 and np.id_contrato_relacionado is   null
group by ubicacion;
# Crear las bodegas que hagan falta y registrar los servicios en las bodegas que hagan falta
# Los receipts que no tengan ubicacion iran a la bodega temporal de distribucion
# La ubicacion de los release sera la misma que de los reeceipts

# Registramos el servicio de dap en la bodega 10
insert into warehouse_workflow_service(creator_id, created_at, warehouse_id, workflow_service_id)
select @support_user_id,NOW(),601,@dap_wf_service_id;
# Registramos el servicio de dap en la bodega centro logistico
insert into warehouse_workflow_service(creator_id, created_at, warehouse_id, workflow_service_id)
select @support_user_id,NOW(),606,@dap_wf_service_id;



SET @bodega_8_id = 624;

# Ordenes de receipts a las cuales hay que definirles bodega

# 1008 receipts
select count(*) from ot where tce_nota_pedido_id is not null and entidad = 2;
# 1337releases
select count(*) from ot where tce_nota_pedido_id is not null and entidad = 3;

# en base a las ubicaciones registradas, hacemos el case


# Actualizacion de bodegas para ordenes de receipt
update ot inner join(

select ot.tramite_id,ubicacion,
       case
           when ubicacion= 'BODEGA 1' then 620
            when ubicacion= 'BODEGA 10' then 601
            when ubicacion= 'BODEGA 2' then 621
            when ubicacion= 'BODEGA 3' then 622
            when ubicacion= 'BODEGA 4' then 623
            when ubicacion = 'CENTRO LOGÍSTICO' then 606
            when ubicacion= 'BODEGA 8 DAP' OR ubicacion= 'BODEGA 8 FAUSTO'  then 624
            when ubicacion is null then 1000 # bodega de distribucion
           end warehouse_id from ot
left join tcedap.dap_operacion o on o.nota_pedido_id = ot.tce_nota_pedido_id and o.anulado = 0
where tce_nota_pedido_id is not null and entidad = 2
group by ot.tce_nota_pedido_id) x on x.tramite_id = ot.tramite_id
set ot.warehouse_id = x.warehouse_id;


# Actualizacion de bodegas para ordenes de release
update ot inner join(
select ot.tramite_id,o.ubicacion,
 case
           when ubicacion= 'BODEGA 1' then 620
            when ubicacion= 'BODEGA 10' then 601
            when ubicacion= 'BODEGA 2' then 621
            when ubicacion= 'BODEGA 3' then 622
            when ubicacion= 'BODEGA 4' then 623
            when ubicacion = 'CENTRO LOGÍSTICO' then 606
            when ubicacion= 'BODEGA 8 DAP' OR ubicacion= 'BODEGA 8 FAUSTO'  then 624
           end warehouse_id

from ot
inner join tcedap.nota_pedido_java np on np.nota_pedido_id = ot.tce_nota_pedido_id
left join tcedap.dap_operacion o on o.nota_pedido_id = np.id_contrato_relacionado and o.anulado = 0
where tce_nota_pedido_id is not null and entidad = 3 and o.ubicacion is not null
group by ot.tce_nota_pedido_id)x on x.tramite_id = ot.tramite_id
set ot.warehouse_id = x.warehouse_id;


# comprobacion de warehouses en ordenes migradas
select count(*) from ot where tce_nota_pedido_id is not null and warehouse_id is null;

# los paquetes deben estar ubicaciones, si no hay definidas hay que definirlas
# Definimos una ubicacion general para las bodegas que no tienen
# aqui estaran los paquetes migrados
insert into warehouse_location(warehouse_id,code,description,availability_status,location_type,creator_id,created_at,level,storage_mode,max_packages,max_weight_kg,max_cbm,total_packages,total_weight_kg,total_cbm)
select w.id,'R001','RECEIVING AREA',3,1,@support_user_id,now(),0,2,10000,100000.0000,100000.0000,0,0.0000,0.0000 from warehouse w
left join warehouse_location wl
on w.id = wl.warehouse_id and wl.location_type = 1
where wl.id is null;



# Queries para generar csv para migrar paquetes de receipt y release

# Con este query obtenemos el csv con el que se crearan los pallets en las tx de receipt
select ot.tramite_id,np.cantidad_pallets from ot
inner join tcedap.nota_pedido_java np  on np.nota_pedido_id = ot.tce_nota_pedido_id
where tce_nota_pedido_id is not null and ot.entidad = 2 and cantidad_pallets >0 ;


# Query  para comprobar si se migraron bien los pallets de receipts
# No deberian haber resultados si todo fue ok
select o.tramite_id, np.cantidad_pallets, sum(if (p.ot_carga_detalle_id is null, 0, 1)) total_detalles
from ot o
inner join tcedap.nota_pedido_java np on o.tce_nota_pedido_id = np.nota_pedido_id
left join ot_carga_detalle p on o.tramite_id = p.receipt_order_id and p.package_mode = 1
where o.entidad = 2
and np.cantidad_pallets > 0
group by o.tramite_id
having np.cantidad_pallets <> total_detalles;


# Con este query obtenemos el csv con el que se crearan los pallets en las tx de release
select  ot.tramite_id receipt_id,x.release_id,x.cantidad_pallets_salida  from ot inner join
(select ot.tramite_id release_id, np.id_contrato_relacionado, np.cantidad_pallets_salida from ot
inner join tcedap.nota_pedido_java np  on np.nota_pedido_id = ot.tce_nota_pedido_id
where ot.tce_nota_pedido_id is not null and ot.entidad = 3 and cantidad_pallets_salida >0)x
on x.id_contrato_relacionado = ot.tce_nota_pedido_id;


# Query  para comprobar si se vincularon los pallets en los releases
select o.tramite_id, o.warehouse_id, np.nota_pedido_id, np.cantidad_pallets_salida, sum(if (p.ot_carga_detalle_id is null, 0, 1)) total_detalles
from ot o
inner join tcedap.nota_pedido_java np on o.tce_nota_pedido_id = np.nota_pedido_id
left join ot_carga_detalle p on o.tramite_id = p.ot_carga_id and p.package_mode = 1
where o.entidad = 3
and np.cantidad_pallets_salida > 0
group by o.tramite_id
having np.cantidad_pallets_salida <> total_detalles;