evento con varias query
saludos a todos, quiero poner en un evento varias query para que ejecute todo segun el orden que las pongo, pero no logra ejecutar todo sino solo la primera
1. esta bien generada la consulta?
2. El usar los eventos no tiene ninguna limitacion?
3. Como podria hacer para ejecutar todo en un solo evento? o tendria que hacerlo con un cron mediante php?
=================================
delimiter |
CREATE event evento_lanzatest ON schedule every 5 minute do
BEGIN
INSERT INTO alioz_fact.ventas_cab (pruc,idcpe,proceso,grupo,tipo,tcom,ser,nro,fec,fecv,moneda,tafec,tdoc,ruc,rsoc,dir,orden,grem,subtot,gravada,exonera,inafecta,gratis,dscto,igv,isc,total,perc)
select
'20507462082' as 'RUC Emisor',
concat((case(est.cf_942) when 'Factura' then '01' when 'Boleta' then '03' Else '00' END),'F001',inv.invoice_no) as 'idcpe',
'P',
'0',
'01',
(case(est.cf_942)
when 'Factura' then '01'
when 'Boleta' then '03'
Else '00' END) as 'tcom',
'F001' as 'ser',
inv.invoice_no as 'nro',
(case (isnull(duedate)) when 1 then DATE_FORMAT(CURDATE()-1, "%Y-%m-%d") when 0 then duedate END) as 'fec',
(case (isnull(duedate)) when 1 then DATE_FORMAT(CURDATE(), "%Y-%m-%d") when 0 then duedate END) as 'fecv',
mon.currency_code,
'01',
'6',
ruc.cf_757 as 'ruc',
va.accountname as 'rsoc',
vads.bill_street as'dir',
inv.purchaseorder as 'orden',
est.cf_952 as 'grem',
CAST(inv.subtotal AS DECIMAL (20,2)) as 'subtot',
CAST(inv.pre_tax_total AS DECIMAL (20,2)) as 'gravada',
'0.00' as 'exonera',
'0.00' as 'inafecta',
'0.00' as 'Gratis',
(IF (inv.discount_amount <> '0.00000000', (CASE(ISNULL( inv.discount_amount )) WHEN 1 THEN 0 WHEN 0 THEN CAST(inv.discount_amount AS DECIMAL (20,2)) END) ,IF(inv.discount_percent <> '0.000',(CASE(ISNULL( ((inv.discount_percent*inv.subtotal)/100) )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((inv.discount_percent*inv.subtotal)/100) AS DECIMAL (20,2)) END),'0.00'))) as'dscto',
CAST(inv.pre_tax_total*0.18 AS DECIMAL (20,2)) as 'IGV',
'0.00' as 'ISC',
CAST(inv.total AS DECIMAL (20,2)) as 'Total',
'0.00'
from vtiger_invoice inv
left join vtiger_account va ON va.accountid = inv.accountid
left join vtiger_accountscf ruc ON ruc.accountid = inv.accountid
left join vtiger_currency_info mon ON mon.id = inv.currency_id
left join vtiger_invoicecf est on est.invoiceid = inv.invoiceid
left join vtiger_accountbillads vads on vads.accountaddressid = va.accountid
where est.cf_1012='666666'
order by inv.invoiceid desc;
update alioz_vtig724.vtiger_invoice vti left join alioz_fact.ventas_cab vca ON vti.invoice_no = vca.nro set invoicestatus='Enviada'
where vti.invoicestatus = 'Rectificativa';
INSERT INTO alioz_fact.ventas_det (identi, idcpe, it, id_cab, cod, det, umed, cant, tpre, pre, subt, dscto, igv, tot, timp, exo, ina, grav, grat, total, unimed)
select
concat('01','F001',vinv.invoice_no,'-',sequence_no) as 'identi',
concat('01','F001',vinv.invoice_no) as 'idcpe',
sequence_no as 'it',
'0' as 'id_cab',
(IF (vpro.product_no like 'PRO%', vpro.product_no ,IF(vserv.service_no like 'SER%',vserv.service_no,'nada'))) as 'cod',
(IF (vpro.product_no like 'PRO%', vpro.productname ,IF(vserv.service_no like 'SER%', vserv.servicename,'nada'))) as 'det',
(IF (vpro.product_no like 'PRO%', 'NIU' ,'ZZ')) as 'umed',
quantity as 'cant',
'01' as 'tpre',
(CASE(ISNULL( listprice )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice AS DECIMAL (20,2)) END) as 'pre',
(CASE(ISNULL( listprice * quantity )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice * quantity AS DECIMAL (20,2)) END) as 'subt',
(CASE(ISNULL( invp.discount_amount )) WHEN 1 THEN 0 WHEN 0 THEN CAST(invp.discount_amount AS DECIMAL (20,2)) END) as 'dscto',
(CASE(ISNULL( (listprice*quantity)*0.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*0.18) AS DECIMAL (20,2)) END) as 'igv',
(CASE(ISNULL( (listprice*quantity)*1.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*1.18) AS DECIMAL (20,2)) END) as 'tot',
'10' as 'timp',
'0.00' as 'exo',
'0.00' as 'ina',
(CASE(ISNULL( listprice*quantity )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice*quantity AS DECIMAL (20,2)) END) as 'grav',
'0.00' as 'grat',
(CASE(ISNULL( (listprice*quantity)*1.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*1.18) AS DECIMAL (20,2)) END) as 'total',
(IF (vpro.product_no like 'PRO%', vpro.usageunit,IF(vserv.service_no like 'SER%' ,vserv.service_usageunit,'nada'))) as 'unimed'
from alioz_vtig724.vtiger_inventoryproductrel invp
left join alioz_vtig724.vtiger_products vpro ON vpro.productid = invp.productid
left join alioz_vtig724.vtiger_invoice vinv ON vinv.invoiceid = invp.id
left join alioz_vtig724.vtiger_service vserv ON vserv.serviceid = invp.productid
where vinv.invoicestatus='Enviando detalle a Sunat'
order by vinv.invoiceid desc;
update alioz_vtig724.vtiger_invoice vti left join alioz_fact.ventas_cab vca ON vti.invoice_no = vca.nro set invoicestatus='Enviada a Sunat'
where vti.invoicestatus = 'Enviando detalle a Sunat';
END |
delimiter ;