Open Patrickkappert opened 4 years ago
Yo implementé unas consultas directamente en la BD para generar unos listados de facturas emitidas y recibidas del tipo:
Nº Fra | Data | client | NIF | BASE IMPOSABLE (4%, 10%, 21%, 0%) | IVA (4%, 10%, 21%) | Total factura | Tipus cobrament |
---|
con las bases imponibles e iva para cada tipo. Si te interesa puedo pegar aquí las consultas.
pues si, me interesa
Aqui lo tienes. Un par de consideraciones:
Al entrar facturas de proveedores rellenamos siempre el campo comentaris
para introducir el número de factura de proveedor. Tambien rellenamos obligatoriamente el campo Referència de pagament
al revisar las comandas. Esos números deben coincidir para poder hacer el agregado de comandas por factura.
Los listados para el iva son las vistas emeses
y rebudes
.
Veras que hay algunas funciones auxiliares que habia creado desde phpmyadmin.
Creo que podras importarlo todo desde phpmyadmin solamente cambiando el DEFINER
.
Desde phpmyadmin puedes abrir las vistas emeses
y rebudes
y exportar los listados a csv o excel.
Lo ideal seria implementar unos informes bonitos en la aplicacion. Pero no he tenido tiempo todavia. Necesitaria comentarlo con @jorix para ver como introduzco los scripts sql y que formato de informe utilizo.
DROP FUNCTION IF EXISTS `get_date_for_shop`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_date_for_shop` (`the_cart_id` INT) RETURNS DATE READS SQL DATA
BEGIN
DECLARE the_date date;
select date_for_shop into the_date
from
aixada_cart c
where
c.id = the_cart_id;
return the_date;
END$$
DROP FUNCTION IF EXISTS `get_uf_name`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_uf_name` (`the_cart_id` INT) RETURNS VARCHAR(100) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE the_name varchar(100);
select aixada_uf.name into the_name
from
aixada_cart inner join aixada_uf
where
aixada_cart.uf_id = aixada_uf.id
and
aixada_cart.id = the_cart_id;
return the_name;
END$$
DROP FUNCTION IF EXISTS `get_uf_nif`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_uf_nif` (`the_cart_id` INT) RETURNS VARCHAR(100) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE the_nif varchar(100);
select aixada_member.nif into the_nif
from
aixada_cart inner join aixada_member
where
aixada_cart.uf_id = aixada_member.uf_id
and
aixada_cart.id = the_cart_id
limit 1;
return the_nif;
END$$
DROP FUNCTION IF EXISTS `get_purchase_base`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_base` (`the_cart_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA
BEGIN
DECLARE base_x decimal(10,2);
select
sum( CAST( si.quantity / (1 + si.iva_percent / 100) * si.unit_price_stamp as decimal(10,2)) ) into base_x
from
aixada_shop_item si
where
si.cart_id = the_cart_id and si.iva_percent = the_iva;
return base_x;
END$$
DROP FUNCTION IF EXISTS `get_purchase_iva`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_iva` (`the_cart_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA
BEGIN
DECLARE iva_x decimal(10,2);
select
sum( CAST( si.quantity / (1 + si.iva_percent / 100) * si.unit_price_stamp * si.iva_percent / 100 as decimal(10,2)) ) into iva_x
from
aixada_shop_item si
where
si.cart_id = the_cart_id and si.iva_percent = the_iva;
return iva_x;
END$$
DROP FUNCTION IF EXISTS `get_purchase_total`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_total` (`the_cart_id` INT) RETURNS FLOAT(10,2) READS SQL DATA
begin
declare total_price decimal(10,2);
select
sum( CAST(si.quantity * si.unit_price_stamp as decimal(10,2)) ) into total_price
from
aixada_shop_item si
where
si.cart_id = the_cart_id;
return total_price;
end$$
DROP FUNCTION IF EXISTS `get_account_quantity`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_account_quantity` (`the_payment_ref` VARCHAR(100)) RETURNS FLOAT(10,2) READS SQL DATA
BEGIN
DECLARE the_qty decimal(10,2);
SELECT quantity into the_qty
from aixada_account
where description = the_payment_ref
and payment_method_id = 8
order by id desc
limit 1;
return the_qty;
END$$
DROP FUNCTION IF EXISTS `get_order_base`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_base` (`the_order_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA
BEGIN
DECLARE base_x decimal(10,2);
select
cast(
sum(
ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100) / (1 + ots.iva_percent / 100)
)
as decimal(10,2)
) into base_x
from
aixada_order_to_shop ots
where
ots.order_id = the_order_id and ots.iva_percent = the_iva and ots.arrived = 1;
return base_x;
END$$
DROP FUNCTION IF EXISTS `get_order_iva`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_iva` (`the_order_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA
BEGIN
DECLARE iva_x decimal(10,2);
select
cast(
sum(
ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100) / (1 + ots.iva_percent / 100) * ots.iva_percent / 100
)
as decimal(10,2)
) into iva_x
from
aixada_order_to_shop ots
where
ots.order_id = the_order_id and ots.iva_percent = the_iva and ots.arrived = 1;
return iva_x;
END$$
DROP FUNCTION IF EXISTS `get_order_total`$$
CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_total` (`the_order_id` INT) RETURNS FLOAT(10,2) READS SQL DATA
begin
declare total_price decimal(10,2);
select
cast(
sum(
ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100)
)
as decimal(10,2)
) into total_price
from
aixada_order_to_shop ots
where
ots.order_id = the_order_id and ots.arrived = 1;
return total_price;
end$$
-- --------------------------------------------------------
--
-- Stand-in structure for view `emeses`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `emeses`;
CREATE TABLE `emeses` (
`cart_id` int(11)
,`data_c` date
,`client_n` varchar(100)
,`nif` varchar(100)
,`base_4` float(10,2)
,`base_10` float(10,2)
,`base_21` float(10,2)
,`base_0` float(10,2)
,`iva_4` float(10,2)
,`iva_10` float(10,2)
,`iva_21` float(10,2)
,`total_factura` float(10,2)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `rebudes`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `rebudes`;
CREATE TABLE `rebudes` (
`payment_ref` varchar(255)
,`count(payment_ref)` bigint(21)
,`date_for_order` date
,`provider_id` int(11)
,`sum(base_4)` double(19,2)
,`sum(base_10)` double(19,2)
,`sum(base_21)` double(19,2)
,`sum(base_0)` double(19,2)
,`sum(iva_4)` double(19,2)
,`sum(iva_10)` double(19,2)
,`sum(iva_21)` double(19,2)
,`sum(total_factura)` double(19,2)
,`get_account_quantity(payment_ref)` float(10,2)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `rebudes_item`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `rebudes_item`;
CREATE TABLE `rebudes_item` (
`id` int(11)
,`payment_ref` varchar(255)
,`date_for_order` date
,`provider_id` int(11)
,`base_4` float(10,2)
,`base_10` float(10,2)
,`base_21` float(10,2)
,`base_0` float(10,2)
,`iva_4` float(10,2)
,`iva_10` float(10,2)
,`iva_21` float(10,2)
,`total_factura` float(10,2)
);
-- --------------------------------------------------------
--
-- Structure for view `emeses`
--
DROP TABLE IF EXISTS `emeses`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `emeses` AS select `si`.`cart_id` AS `cart_id`,`get_date_for_shop`(`si`.`cart_id`) AS `data_c`,`get_uf_name`(`si`.`cart_id`) AS `client_n`,`get_uf_nif`(`si`.`cart_id`) AS `nif`,`get_purchase_base`(`si`.`cart_id`,4) AS `base_4`,`get_purchase_base`(`si`.`cart_id`,10) AS `base_10`,`get_purchase_base`(`si`.`cart_id`,21) AS `base_21`,`get_purchase_base`(`si`.`cart_id`,0) AS `base_0`,`get_purchase_iva`(`si`.`cart_id`,4) AS `iva_4`,`get_purchase_iva`(`si`.`cart_id`,10) AS `iva_10`,`get_purchase_iva`(`si`.`cart_id`,21) AS `iva_21`,`get_purchase_total`(`si`.`cart_id`) AS `total_factura` from `aixada_shop_item` `si` group by `si`.`cart_id` ;
-- --------------------------------------------------------
--
-- Structure for view `rebudes`
--
DROP TABLE IF EXISTS `rebudes`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `rebudes` AS select `rebudes_item`.`payment_ref` AS `payment_ref`,count(`rebudes_item`.`payment_ref`) AS `count(payment_ref)`,`rebudes_item`.`date_for_order` AS `date_for_order`,`rebudes_item`.`provider_id` AS `provider_id`,sum(`rebudes_item`.`base_4`) AS `sum(base_4)`,sum(`rebudes_item`.`base_10`) AS `sum(base_10)`,sum(`rebudes_item`.`base_21`) AS `sum(base_21)`,sum(`rebudes_item`.`base_0`) AS `sum(base_0)`,sum(`rebudes_item`.`iva_4`) AS `sum(iva_4)`,sum(`rebudes_item`.`iva_10`) AS `sum(iva_10)`,sum(`rebudes_item`.`iva_21`) AS `sum(iva_21)`,sum(`rebudes_item`.`total_factura`) AS `sum(total_factura)`,`get_account_quantity`(`rebudes_item`.`payment_ref`) AS `get_account_quantity(payment_ref)` from `rebudes_item` group by `rebudes_item`.`payment_ref`,`rebudes_item`.`provider_id` order by `rebudes_item`.`payment_ref` ;
-- --------------------------------------------------------
--
-- Structure for view `rebudes_item`
--
DROP TABLE IF EXISTS `rebudes_item`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `rebudes_item` AS select `aixada_order`.`id` AS `id`,`aixada_order`.`payment_ref` AS `payment_ref`,`aixada_order`.`date_for_order` AS `date_for_order`,`aixada_order`.`provider_id` AS `provider_id`,`get_order_base`(`aixada_order`.`id`,4) AS `base_4`,`get_order_base`(`aixada_order`.`id`,10) AS `base_10`,`get_order_base`(`aixada_order`.`id`,21) AS `base_21`,`get_order_base`(`aixada_order`.`id`,0) AS `base_0`,`get_order_iva`(`aixada_order`.`id`,4) AS `iva_4`,`get_order_iva`(`aixada_order`.`id`,10) AS `iva_10`,`get_order_iva`(`aixada_order`.`id`,21) AS `iva_21`,`get_order_total`(`aixada_order`.`id`) AS `total_factura` from `aixada_order` order by `aixada_order`.`provider_id` ;
COMMIT;
Aqui lo tienes. Un par de consideraciones:
1. Al entrar facturas de proveedores rellenamos siempre el campo `comentaris` para introducir el número de factura de proveedor. Tambien rellenamos obligatoriamente el campo `Referència de pagament` al revisar las comandas. Esos números deben coincidir para poder hacer el agregado de comandas por factura. 2. Los listados para el iva son las vistas `emeses` y `rebudes`. Veras que hay algunas funciones auxiliares que habia creado desde phpmyadmin. Creo que podras importarlo todo desde phpmyadmin solamente cambiando el `DEFINER`.
Desde phpmyadmin puedes abrir las vistas
emeses
yrebudes
y exportar los listados a csv o excel.Lo ideal seria implementar unos informes bonitos en la aplicacion. Pero no he tenido tiempo todavia. Necesitaria comentarlo con @jorix para ver como introduzco los scripts sql y que formato de informe utilizo.
DROP FUNCTION IF EXISTS `get_date_for_shop`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_date_for_shop` (`the_cart_id` INT) RETURNS DATE READS SQL DATA BEGIN DECLARE the_date date; select date_for_shop into the_date from aixada_cart c where c.id = the_cart_id; return the_date; END$$ DROP FUNCTION IF EXISTS `get_uf_name`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_uf_name` (`the_cart_id` INT) RETURNS VARCHAR(100) CHARSET utf8 READS SQL DATA BEGIN DECLARE the_name varchar(100); select aixada_uf.name into the_name from aixada_cart inner join aixada_uf where aixada_cart.uf_id = aixada_uf.id and aixada_cart.id = the_cart_id; return the_name; END$$ DROP FUNCTION IF EXISTS `get_uf_nif`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_uf_nif` (`the_cart_id` INT) RETURNS VARCHAR(100) CHARSET utf8 READS SQL DATA BEGIN DECLARE the_nif varchar(100); select aixada_member.nif into the_nif from aixada_cart inner join aixada_member where aixada_cart.uf_id = aixada_member.uf_id and aixada_cart.id = the_cart_id limit 1; return the_nif; END$$ DROP FUNCTION IF EXISTS `get_purchase_base`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_base` (`the_cart_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA BEGIN DECLARE base_x decimal(10,2); select sum( CAST( si.quantity / (1 + si.iva_percent / 100) * si.unit_price_stamp as decimal(10,2)) ) into base_x from aixada_shop_item si where si.cart_id = the_cart_id and si.iva_percent = the_iva; return base_x; END$$ DROP FUNCTION IF EXISTS `get_purchase_iva`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_iva` (`the_cart_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA BEGIN DECLARE iva_x decimal(10,2); select sum( CAST( si.quantity / (1 + si.iva_percent / 100) * si.unit_price_stamp * si.iva_percent / 100 as decimal(10,2)) ) into iva_x from aixada_shop_item si where si.cart_id = the_cart_id and si.iva_percent = the_iva; return iva_x; END$$ DROP FUNCTION IF EXISTS `get_purchase_total`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_purchase_total` (`the_cart_id` INT) RETURNS FLOAT(10,2) READS SQL DATA begin declare total_price decimal(10,2); select sum( CAST(si.quantity * si.unit_price_stamp as decimal(10,2)) ) into total_price from aixada_shop_item si where si.cart_id = the_cart_id; return total_price; end$$ DROP FUNCTION IF EXISTS `get_account_quantity`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_account_quantity` (`the_payment_ref` VARCHAR(100)) RETURNS FLOAT(10,2) READS SQL DATA BEGIN DECLARE the_qty decimal(10,2); SELECT quantity into the_qty from aixada_account where description = the_payment_ref and payment_method_id = 8 order by id desc limit 1; return the_qty; END$$ DROP FUNCTION IF EXISTS `get_order_base`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_base` (`the_order_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA BEGIN DECLARE base_x decimal(10,2); select cast( sum( ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100) / (1 + ots.iva_percent / 100) ) as decimal(10,2) ) into base_x from aixada_order_to_shop ots where ots.order_id = the_order_id and ots.iva_percent = the_iva and ots.arrived = 1; return base_x; END$$ DROP FUNCTION IF EXISTS `get_order_iva`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_iva` (`the_order_id` INT, `the_iva` INT) RETURNS FLOAT(10,2) READS SQL DATA BEGIN DECLARE iva_x decimal(10,2); select cast( sum( ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100) / (1 + ots.iva_percent / 100) * ots.iva_percent / 100 ) as decimal(10,2) ) into iva_x from aixada_order_to_shop ots where ots.order_id = the_order_id and ots.iva_percent = the_iva and ots.arrived = 1; return iva_x; END$$ DROP FUNCTION IF EXISTS `get_order_total`$$ CREATE DEFINER=`lagranera`@`%` FUNCTION `get_order_total` (`the_order_id` INT) RETURNS FLOAT(10,2) READS SQL DATA begin declare total_price decimal(10,2); select cast( sum( ots.unit_price_stamp * ots.quantity / (1 + ots.rev_tax_percent / 100) ) as decimal(10,2) ) into total_price from aixada_order_to_shop ots where ots.order_id = the_order_id and ots.arrived = 1; return total_price; end$$ -- -------------------------------------------------------- -- -- Stand-in structure for view `emeses` -- (See below for the actual view) -- DROP VIEW IF EXISTS `emeses`; CREATE TABLE `emeses` ( `cart_id` int(11) ,`data_c` date ,`client_n` varchar(100) ,`nif` varchar(100) ,`base_4` float(10,2) ,`base_10` float(10,2) ,`base_21` float(10,2) ,`base_0` float(10,2) ,`iva_4` float(10,2) ,`iva_10` float(10,2) ,`iva_21` float(10,2) ,`total_factura` float(10,2) ); -- -------------------------------------------------------- -- -- Stand-in structure for view `rebudes` -- (See below for the actual view) -- DROP VIEW IF EXISTS `rebudes`; CREATE TABLE `rebudes` ( `payment_ref` varchar(255) ,`count(payment_ref)` bigint(21) ,`date_for_order` date ,`provider_id` int(11) ,`sum(base_4)` double(19,2) ,`sum(base_10)` double(19,2) ,`sum(base_21)` double(19,2) ,`sum(base_0)` double(19,2) ,`sum(iva_4)` double(19,2) ,`sum(iva_10)` double(19,2) ,`sum(iva_21)` double(19,2) ,`sum(total_factura)` double(19,2) ,`get_account_quantity(payment_ref)` float(10,2) ); -- -------------------------------------------------------- -- -- Stand-in structure for view `rebudes_item` -- (See below for the actual view) -- DROP VIEW IF EXISTS `rebudes_item`; CREATE TABLE `rebudes_item` ( `id` int(11) ,`payment_ref` varchar(255) ,`date_for_order` date ,`provider_id` int(11) ,`base_4` float(10,2) ,`base_10` float(10,2) ,`base_21` float(10,2) ,`base_0` float(10,2) ,`iva_4` float(10,2) ,`iva_10` float(10,2) ,`iva_21` float(10,2) ,`total_factura` float(10,2) ); -- -------------------------------------------------------- -- -- Structure for view `emeses` -- DROP TABLE IF EXISTS `emeses`; CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `emeses` AS select `si`.`cart_id` AS `cart_id`,`get_date_for_shop`(`si`.`cart_id`) AS `data_c`,`get_uf_name`(`si`.`cart_id`) AS `client_n`,`get_uf_nif`(`si`.`cart_id`) AS `nif`,`get_purchase_base`(`si`.`cart_id`,4) AS `base_4`,`get_purchase_base`(`si`.`cart_id`,10) AS `base_10`,`get_purchase_base`(`si`.`cart_id`,21) AS `base_21`,`get_purchase_base`(`si`.`cart_id`,0) AS `base_0`,`get_purchase_iva`(`si`.`cart_id`,4) AS `iva_4`,`get_purchase_iva`(`si`.`cart_id`,10) AS `iva_10`,`get_purchase_iva`(`si`.`cart_id`,21) AS `iva_21`,`get_purchase_total`(`si`.`cart_id`) AS `total_factura` from `aixada_shop_item` `si` group by `si`.`cart_id` ; -- -------------------------------------------------------- -- -- Structure for view `rebudes` -- DROP TABLE IF EXISTS `rebudes`; CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `rebudes` AS select `rebudes_item`.`payment_ref` AS `payment_ref`,count(`rebudes_item`.`payment_ref`) AS `count(payment_ref)`,`rebudes_item`.`date_for_order` AS `date_for_order`,`rebudes_item`.`provider_id` AS `provider_id`,sum(`rebudes_item`.`base_4`) AS `sum(base_4)`,sum(`rebudes_item`.`base_10`) AS `sum(base_10)`,sum(`rebudes_item`.`base_21`) AS `sum(base_21)`,sum(`rebudes_item`.`base_0`) AS `sum(base_0)`,sum(`rebudes_item`.`iva_4`) AS `sum(iva_4)`,sum(`rebudes_item`.`iva_10`) AS `sum(iva_10)`,sum(`rebudes_item`.`iva_21`) AS `sum(iva_21)`,sum(`rebudes_item`.`total_factura`) AS `sum(total_factura)`,`get_account_quantity`(`rebudes_item`.`payment_ref`) AS `get_account_quantity(payment_ref)` from `rebudes_item` group by `rebudes_item`.`payment_ref`,`rebudes_item`.`provider_id` order by `rebudes_item`.`payment_ref` ; -- -------------------------------------------------------- -- -- Structure for view `rebudes_item` -- DROP TABLE IF EXISTS `rebudes_item`; CREATE ALGORITHM=UNDEFINED DEFINER=`lagranera`@`%` SQL SECURITY DEFINER VIEW `rebudes_item` AS select `aixada_order`.`id` AS `id`,`aixada_order`.`payment_ref` AS `payment_ref`,`aixada_order`.`date_for_order` AS `date_for_order`,`aixada_order`.`provider_id` AS `provider_id`,`get_order_base`(`aixada_order`.`id`,4) AS `base_4`,`get_order_base`(`aixada_order`.`id`,10) AS `base_10`,`get_order_base`(`aixada_order`.`id`,21) AS `base_21`,`get_order_base`(`aixada_order`.`id`,0) AS `base_0`,`get_order_iva`(`aixada_order`.`id`,4) AS `iva_4`,`get_order_iva`(`aixada_order`.`id`,10) AS `iva_10`,`get_order_iva`(`aixada_order`.`id`,21) AS `iva_21`,`get_order_total`(`aixada_order`.`id`) AS `total_factura` from `aixada_order` order by `aixada_order`.`provider_id` ; COMMIT;
Si lo trabajáis y termináis implementando podríais hecharnos una mano, es muy interesante este aporte!
Un saludo!
Hola @Argar @Patrickkappert he subido una rama lagranera
a mi fork
¿Podeis probarlo y ver si se acerca a lo que os iria bien?
Gracias
genial, mañana me lo miro
Hola, alguien sabe si hay versiones de Aixada preparada para ayudar en hacer las declaraciones trimestrales para hacienda?
gracias