MySQL Forums
Forum List  »  Spanish

evento con varias query
Posted by: Josue Puma Jimenez
Date: May 01, 2018 02:28PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
evento con varias query
401
May 01, 2018 02:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.