DECLARE @companyId bigint
SELECT @companyId = id FROM datos_facturacion WHERE legal_name = 'PRODUCTOS ALIMENTICIOS BOCADELI, SOCIEDAD ANONIMA DE CAPITAL VARIABLE'

DECLARE @businessPartnerId bigint
SELECT @businessPartnerId = id FROM datos_facturacion WHERE document_id = '06142101931049'

INSERT INTO dtes (company_id, business_partner_id, type, sequence, identifier, sale_installation, payment_type, payment_method, payment_schedule, payment_span, payment_reference, signature, status, subtotal, discount, tax, total)
VALUES 
(@companyId, @businessPartnerId, 'Factura', '001', NEWID(), '01', 'Contado', 'Efectivo', 'Sin plazo', NULL, NULL, 'DTE-03-12345678-000000000000001', 'Aceptado', 100, NULL, 13, 113),
(@companyId, @businessPartnerId, 'Factura', '002', NEWID(), '02', 'Crédito', 'Tarjeta de crédito', 'Mensual', 6, 'REF1234', 'DTE-03-12345678-000000000000002', 'Aceptado', 200, 20, 34, 214),
(@companyId, @businessPartnerId, 'Factura', '003', NEWID(), '03', 'Contado', 'Transferencia', 'Sin plazo', NULL, NULL, 'DTE-03-12345678-000000000000003', 'Rechazado', 150, NULL, 23, 173),
(@companyId, @businessPartnerId, 'Factura', '004', NEWID(), '04', 'Crédito', 'Cheque', 'Mensual', 12, 'REF5678', 'DTE-03-12345678-000000000000004', 'Aceptado', 80, 5, 12, 87),
(@companyId, @businessPartnerId, 'Factura', '005', NEWID(), '05', 'Contado', 'Efectivo', 'Sin plazo', NULL, NULL, 'DTE-03-12345678-000000000000005', 'Aceptado', 300, NULL, 45, 345),
(@companyId, @businessPartnerId, 'Factura', '006', NEWID(), '06', 'Crédito', 'Tarjeta de crédito', 'Mensual', 6, 'REF2345', 'DTE-03-12345678-000000000000006', 'Rechazado', 120, 10, 20, 130),
(@companyId, @businessPartnerId, 'Factura', '007', NEWID(), '07', 'Contado', 'Transferencia', 'Sin plazo', NULL, NULL, 'DTE-03-12345678-000000000000007', 'Aceptado', 250, NULL, 38, 288),
(@companyId, @businessPartnerId, 'Factura', '008', NEWID(), '08', 'Crédito', 'Cheque', 'Mensual', 12, 'REF7890', 'DTE-03-12345678-000000000000008', 'Aceptado', 150, 15, 23, 158),
(@companyId, @businessPartnerId, 'Factura', '009', NEWID(), '09', 'Contado', 'Efectivo', 'Sin plazo', NULL, NULL, 'DTE-03-12345678-000000000000009', 'Aceptado', 50, NULL, 8, 58)

INSERT INTO items (document_id, code, type, unit, quantity, description, unit_price, subtotal, discount, tax, total)
VALUES 
(1, 'P001', 'Producto', 'Unidad', 10, 'Pan integral', 1.50, 15.00, 0.00, 2.85, 17.85),
(1, 'P002', 'Producto', 'Unidad', 5, 'Galletas de avena', 2.00, 10.00, 0.00, 1.90, 11.90),
(1, 'S001', 'Servicio', 'Hora', 2, 'Asesoría nutricional', 20.00, 40.00, 0.00, 7.60, 47.60),
(2, 'P003', 'Producto', 'Unidad', 3, 'Pastel de zanahoria', 12.00, 36.00, 0.00, 6.84, 42.84),
(2, 'P004', 'Producto', 'Unidad', 1, 'Torta de chocolate', 25.00, 25.00, 0.00, 4.75, 29.75),
(3, 'S002', 'Servicio', 'Hora', 4, 'Catering para evento', 100.00, 400.00, 0.00, 76.00, 476.00),
(4, 'P005', 'Producto', 'Unidad', 20, 'Pan francés', 0.50, 10.00, 0.00, 1.90, 11.90),
(4, 'P006', 'Producto', 'Unidad', 8, 'Pan de queso', 1.00, 8.00, 0.00, 1.52, 9.52),
(4, 'S003', 'Servicio', 'Hora', 3, 'Montaje y decoración', 50.00, 150.00, 0.00, 28.50, 178.50),
(5, 'P007', 'Producto', 'Unidad', 15, 'Donas de chocolate', 1.25, 18.75, 0.00, 3.56, 22.31),
(5, 'P008', 'Producto', 'Unidad', 10, 'Muffins de arándanos', 2.00, 20.00, 0.00, 3.80, 23.80),
(5, 'S004', 'Servicio', 'Hora', 5, 'Servicio de meseros', 25.00, 125.00, 0.00, 23.75, 148.75);


select * from datos_facturacion
select * from dtes
select * from items


/*
RESULTADO EN JSON
*/

{"id":1,"billable_type":"Empresa","name":"PRODUCTOS ALIMENTICIOS BOCADELI","legal_name":"PRODUCTOS ALIMENTICIOS BOCADELI, SOCIEDAD ANONIMA DE CAPITAL VARIABLE","document_type":"NIT","document_id":"06142101931049","tax_id":"123456789","business_line":"Fabricantes y distribuidores de productos comestibles","country":"El Salvador","state":"San Salvador","city":"Soyapango","address":"FINAL AVENIDA CERRO VERDE, COL. SIERRA MORENA NO. 2","phone":"2297-9000","email":"info@bocadeli.com","d":[{"id":2,"company_id":1,"business_partner_id":1,"type":"Factura","sequence":"002","identifier":"E13B725A-E41C-4592-A601-83049F4467EF","sale_installation":"02","payment_type":"Crédito","payment_method":"Tarjeta de crédito","payment_schedule":"Mensual","payment_span":6,"payment_reference":"REF1234","signature":"DTE-03-12345678-000000000000002","status":"Aceptado","subtotal":200.00,"discount":20.00,"tax":34.00,"total":214.00,"items":[{"id":4,"document_id":2,"code":"P003","type":"Producto","unit":"Unidad","quantity":3,"description":"Pastel de zanahoria","unit_price":12.00,"subtotal":36.00,"discount":0.00,"tax":6.84,"total":42.84},{"id":5,"document_id":2,"code":"P004","type":"Producto","unit":"Unidad","quantity":1,"description":"Torta de chocolate","unit_price":25.00,"subtotal":25.00,"discount":0.00,"tax":4.75,"total":29.75}]}]}