mckaygerhard
c7fd3942c5
* enlace al sql directo en el git de documentos * enlaces y fotos directo en el git de documentos
345 lines
15 KiB
SQL
345 lines
15 KiB
SQL
|
|
/* ************* AGREGAR ITEMS item 1 */
|
|
|
|
-- AQU! SE ESCRIBIO 1 EN EL INPUT DE CODIGI DE ITEM A BUSCAR
|
|
|
|
SELECT `item_id`, `name`, `name`
|
|
FROM `ospos_items`
|
|
WHERE `deleted` =0 AND `item_type` IN(0, 2) AND `name` LIKE '%1%' ESCAPE '!'
|
|
ORDER BY `name` ASC
|
|
|
|
SELECT `item_id`, `item_number`, `name`
|
|
FROM `ospos_items`
|
|
WHERE `deleted` =0 AND `item_type` IN(0, 2) AND `item_number` LIKE '%1%' ESCAPE '!'
|
|
ORDER BY `item_number` ASC
|
|
|
|
SELECT *
|
|
FROM `ospos_item_kits`
|
|
WHERE `name` LIKE '%1%' ESCAPE '!'
|
|
ORDER BY `name` ASC
|
|
|
|
-- AQUI ES PARA MOSTRAR AL EMPLOYEE LO ENCONTRADO por este codigo item
|
|
|
|
SELECT *
|
|
FROM `ospos_items`
|
|
WHERE ( `ospos_items`.`item_number` = '1' OR `ospos_items`.`item_id` = 1 )
|
|
AND `ospos_items`.`deleted` =0
|
|
LIMIT 1
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
WHERE `location_id` = '1'
|
|
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '1' AND `location_id` = '1'
|
|
|
|
SELECT * FROM `ospos_dinner_tables` WHERE `status` =0 AND `deleted` =0
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
JOIN `ospos_permissions` AS `permissions` ON `permissions`.`location_id` = `ospos_stock_locations`.`location_id`
|
|
JOIN `ospos_grants` AS `grants` ON `grants`.`permission_id` = `permissions`.`permission_id`
|
|
WHERE `person_id` = '1' AND `permissions`.`permission_id` LIKE 'sales%' ESCAPE '!' AND `deleted` =0
|
|
|
|
-- AQUI YA AGREGO EL ITEM ID pero ahora actualiza la venta verificando los taxes, hay un solo item por ahora
|
|
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '1'
|
|
|
|
/* ************* AGREGAR ITEMS item 1 FIN */
|
|
|
|
/* ************* AGREGAR ITEMS item 2 INI */
|
|
|
|
-- AQU! SE ESCRIBIO 2 EN EL INPUT DE CODIGI DE ITEM A BUSCAR
|
|
|
|
SELECT `item_id`, `name`, `name`
|
|
FROM `ospos_items`
|
|
WHERE `deleted` =0 AND `item_type` IN(0, 2) AND `name` LIKE '%2%' ESCAPE '!'
|
|
ORDER BY `name` ASC
|
|
|
|
SELECT `item_id`, `item_number`, `name`
|
|
FROM `ospos_items`
|
|
WHERE `deleted` =0 AND `item_type` IN(0, 2) AND `item_number` LIKE '%2%' ESCAPE '!'
|
|
ORDER BY `item_number` ASC
|
|
|
|
SELECT *
|
|
FROM `ospos_item_kits`
|
|
WHERE `name` LIKE '%2%' ESCAPE '!'
|
|
ORDER BY `name` ASC
|
|
|
|
-- AQUI ES PARA MOSTRAR AL EMPLOYEE LO ENCONTRADO por este codigo item
|
|
|
|
SELECT *
|
|
FROM `ospos_items`
|
|
WHERE ( `ospos_items`.`item_number` = '2' OR `ospos_items`.`item_id` = 2 )
|
|
AND `ospos_items`.`deleted` =0
|
|
LIMIT 1
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
WHERE `location_id` = '1'
|
|
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '2' AND `location_id` = '1'
|
|
|
|
SELECT * FROM `ospos_dinner_tables` WHERE `status` =0 AND `deleted` =0
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
JOIN `ospos_permissions` AS `permissions` ON `permissions`.`location_id` = `ospos_stock_locations`.`location_id`
|
|
JOIN `ospos_grants` AS `grants` ON `grants`.`permission_id` = `permissions`.`permission_id`
|
|
WHERE `person_id` = '1' AND `permissions`.`permission_id` LIKE 'sales%' ESCAPE '!'
|
|
AND `deleted` =0
|
|
|
|
-- AQUI YA AGREGO EL ITEM ID pero ahora actualiza la venta verificando los taxes, hay ahora dos items
|
|
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '1'
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '2'
|
|
|
|
/* ******** INI RELACION DE CLIENTE CON LA VENTA ACTUAL ********* */
|
|
|
|
-- AQUI ESCRIBE "a" PARA BUSCAR UN CUSTOMER O UN CLIENTE
|
|
|
|
SELECT *
|
|
FROM `ospos_customers`
|
|
JOIN `ospos_people` ON `ospos_customers`.`person_id` = `ospos_people`.`person_id`
|
|
WHERE (
|
|
`first_name` LIKE '%a%' ESCAPE '!'
|
|
OR `last_name` LIKE '%a%' ESCAPE '!'
|
|
OR CONCAT(first_name, " ", last_name) LIKE '%a%' ESCAPE '!'
|
|
OR `email` LIKE '%a%' ESCAPE '!'
|
|
OR `phone_number` LIKE '%a%' ESCAPE '!'
|
|
OR `company_name` LIKE '%a%' ESCAPE '!'
|
|
)
|
|
AND `deleted` =0
|
|
ORDER BY `last_name` ASC
|
|
|
|
-- AQUI YA LO MUESTRA EN EL INPUT BOX para que lo seleccione y relacione a la venta
|
|
-- CUANDO LO RELACIONA BUSCA SI ESTE TIENE VENTAS PENDIENTE DESPUES RELACIONA LOS ITEMS
|
|
-- LAS RELACIONES DE ITEM O DE PAGOS (puede que antes de relacionra cleinte ya tenga pagos)
|
|
-- SE REALIZAN EMPLEANDO TABLAS TEMPORALES de los items y de los pagos:
|
|
|
|
SELECT *
|
|
FROM `ospos_customers`
|
|
JOIN `ospos_people` ON `ospos_people`.`person_id` = `ospos_customers`.`person_id`
|
|
WHERE `ospos_customers`.`person_id` = '2'
|
|
|
|
-- crea uan tabla temporal de los items que ya estan en la venta
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS ospos_sales_items_temp (INDEX(sale_id))
|
|
(
|
|
SELECT
|
|
sales.sale_id AS sale_id,
|
|
AVG(sales_items.discount_percent) AS avg_discount,
|
|
SUM(sales_items.quantity_purchased) AS quantity
|
|
FROM ospos_sales AS sales
|
|
INNER JOIN ospos_sales_items AS sales_items
|
|
ON sales_items.sale_id = sales.sale_id
|
|
WHERE sales.customer_id = '2'
|
|
GROUP BY sale_id
|
|
)
|
|
-- despues crea una tabla temporal de los pagos que ya estan en la venta
|
|
SELECT SUM(sales_payments.payment_amount) AS total, MIN(sales_payments.payment_amount) AS min, MAX(sales_payments.payment_amount) AS max, AVG(sales_payments.payment_amount) AS average, ROUND(AVG(sales_items_temp.avg_discount), 2) AS avg_discount, ROUND(SUM(sales_items_temp.quantity), 0) AS quantity
|
|
FROM `ospos_sales`
|
|
JOIN `ospos_sales_payments` AS `sales_payments` ON `ospos_sales`.`sale_id` = `sales_payments`.`sale_id`
|
|
JOIN `ospos_sales_items_temp` AS `sales_items_temp` ON `ospos_sales`.`sale_id` = `sales_items_temp`.`sale_id`
|
|
WHERE `ospos_sales`.`customer_id` = '2' AND `ospos_sales`.`sale_status` =0
|
|
GROUP BY `ospos_sales`.`customer_id`
|
|
-- borra todo esto
|
|
DROP TEMPORARY TABLE IF EXISTS ospos_sales_items_temp
|
|
-- la relacion de estos se realiza despues en el objeto sesion ya que la venta esta alli antes de insertarse
|
|
|
|
-- verifica este cleinte uan vez realacionado y busc los datoa apra presentar en pantalla
|
|
SELECT *
|
|
FROM `ospos_customers`
|
|
JOIN `ospos_people` ON `ospos_people`.`person_id` = `ospos_customers`.`person_id`
|
|
WHERE `ospos_customers`.`person_id` = '2'
|
|
|
|
-- actualiza las tazas de tax de cada item en la venta
|
|
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '1'
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '2'
|
|
|
|
/* ******** FIN RELACION DE CLIENTE CON LA VENTA ACTUAL ********* */
|
|
|
|
/* ******** INI realcionar pagos (es lo mismo para cada pago, solo verificaciones) ********* */
|
|
|
|
-- los datos especificos los manejan solo en php, en sql se realizan verificaciones unicamente:
|
|
|
|
-- verifica el empleado, esto es por si la venta estaba suspendida
|
|
SELECT *
|
|
FROM `ospos_employees`
|
|
JOIN `ospos_people` ON `ospos_people`.`person_id` = `ospos_employees`.`person_id`
|
|
WHERE `ospos_employees`.`person_id` = '1'
|
|
|
|
-- verifica el cliente relacionado no haya cambiado, por si se retomo la venta y cambio el nombre del cliente
|
|
SELECT *
|
|
FROM `ospos_customers`
|
|
JOIN `ospos_people` ON `ospos_people`.`person_id` = `ospos_customers`.`person_id`
|
|
WHERE `ospos_customers`.`person_id` = '2'
|
|
-- vuelve recorer los items en la venta pra relacionar con el cliente si este cambio
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS ospos_sales_items_temp (INDEX(sale_id))
|
|
(
|
|
SELECT
|
|
sales.sale_id AS sale_id,
|
|
AVG(sales_items.discount_percent) AS avg_discount,
|
|
SUM(sales_items.quantity_purchased) AS quantity
|
|
FROM ospos_sales AS sales
|
|
INNER JOIN ospos_sales_items AS sales_items
|
|
ON sales_items.sale_id = sales.sale_id
|
|
WHERE sales.customer_id = '2'
|
|
GROUP BY sale_id
|
|
)
|
|
-- aqui verifica pagos anteriores en la venta actual segun su estado (de la venta)
|
|
SELECT SUM(sales_payments.payment_amount) AS total, MIN(sales_payments.payment_amount) AS min, MAX(sales_payments.payment_amount) AS max, AVG(sales_payments.payment_amount) AS average, ROUND(AVG(sales_items_temp.avg_discount), 2) AS avg_discount, ROUND(SUM(sales_items_temp.quantity), 0) AS quantity
|
|
FROM `ospos_sales`
|
|
JOIN `ospos_sales_payments` AS `sales_payments` ON `ospos_sales`.`sale_id` = `sales_payments`.`sale_id`
|
|
JOIN `ospos_sales_items_temp` AS `sales_items_temp` ON `ospos_sales`.`sale_id` = `sales_items_temp`.`sale_id`
|
|
WHERE `ospos_sales`.`customer_id` = '2'
|
|
AND `ospos_sales`.`sale_status` =0
|
|
GROUP BY `ospos_sales`.`customer_id`
|
|
-- una vez actualizadas las relaciones se elimina la tabla temporal
|
|
DROP TEMPORARY TABLE IF EXISTS ospos_sales_items_temp
|
|
|
|
-- revisa si es un restaurante
|
|
SELECT * FROM `ospos_dinner_tables` WHERE `status` =0 AND `deleted` =0
|
|
|
|
-- revision de existencia en la lcoacion especifica es por cad item, cuidado
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
JOIN `ospos_permissions` AS `permissions` ON `permissions`.`location_id` = `ospos_stock_locations`.`location_id`
|
|
JOIN `ospos_grants` AS `grants` ON `grants`.`permission_id` = `permissions`.`permission_id`
|
|
WHERE `person_id` = '1'
|
|
AND `permissions`.`permission_id` LIKE 'sales%' ESCAPE '!'
|
|
AND `deleted` =0
|
|
|
|
-- revision de las tazas de tax de cad item en la venta
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '1'
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '2'
|
|
|
|
/* ********* INI savado de la venta actual con dos pagos dos items un cliente ********* */
|
|
|
|
SELECT *
|
|
FROM `ospos_customers`
|
|
JOIN `ospos_people` ON `ospos_people`.`person_id` = `ospos_customers`.`person_id`
|
|
WHERE `ospos_customers`.`person_id` = '2'
|
|
|
|
-- ******* MANEJO DE PAGOS Y CABECERA DE LA VENTA SALVAR: ********
|
|
|
|
INSERT INTO `ospos_sales` (`sale_time`, `customer_id`, `employee_id`, `comment`, `sale_status`, `invoice_number`, `quote_number`, `work_order_number`, `dinner_table_id`, `sale_type`, `exinput1`, `exinput5`) VALUES ('2018-05-28 16:14:42', '2', '1', '', 0, NULL, NULL, NULL, NULL, 0, '0', '')
|
|
|
|
INSERT INTO `ospos_sales_payments` (`sale_id`, `payment_type`, `payment_amount`) VALUES (2, 'Cash', 20)
|
|
|
|
INSERT INTO `ospos_sales_payments` (`sale_id`, `payment_type`, `payment_amount`) VALUES (2, 'Debit Card', 16.71)
|
|
|
|
-- ******* MANEJO DE ITEMS ************
|
|
|
|
-- VAMOS CON EL ITEM 1 AHORA:
|
|
SELECT `ospos_items`.*, `ospos_suppliers`.`company_name`
|
|
FROM `ospos_items`
|
|
LEFT JOIN `ospos_suppliers` ON `ospos_suppliers`.`person_id` = `ospos_items`.`supplier_id`
|
|
WHERE `item_id` = '1'
|
|
|
|
-- inserta el item primero el de id=1 en el detalle de la venta:
|
|
INSERT INTO `ospos_sales_items` (`sale_id`, `item_id`, `line`, `description`, `serialnumber`, `quantity_purchased`, `discount_percent`, `item_cost_price`, `item_unit_price`, `item_location`, `print_option`) VALUES (2, '1', 1, '', '', 1, '1.00', '10.00', '15.00', '1', 0)
|
|
|
|
-- manejo de existencia e inventario para el item primero de id=1:
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '1'
|
|
AND `location_id` = '1' /* de aqui obtiene el 99 y lo rebajara a 98 para el item id=1 */
|
|
-- actualiza el inventario para el primer item el de id=1:
|
|
UPDATE `ospos_item_quantities` SET `quantity` = 98, `item_id` = '1', `location_id` = '1'
|
|
WHERE `item_id` = '1'
|
|
AND `location_id` = '1'
|
|
|
|
-- procede a rebajar el inventario pro transaccion:
|
|
INSERT INTO `ospos_inventory` (`trans_date`, `trans_items`, `trans_user`, `trans_location`, `trans_comment`, `trans_inventory`) VALUES ('2018-05-28 16:14:42', '1', '1', '1', 'POS 2', -1)
|
|
|
|
-- buscamos el taxes para el primer item el de id = 1 en esta venta ya insertada:
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '1'
|
|
-- se encontro 2 taxes para el item 1 que estaba en esta venta con id = 1
|
|
INSERT INTO `ospos_sales_items_taxes` (`sale_id`, `item_id`, `line`, `name`, `percent`, `tax_type`, `rounding_code`, `cascade_tax`, `cascade_sequence`, `item_tax_amount`) VALUES (2, '1', 1, '', '1.000', 1, 1, 0, 0, 0.15)
|
|
INSERT INTO `ospos_sales_items_taxes` (`sale_id`, `item_id`, `line`, `name`, `percent`, `tax_type`, `rounding_code`, `cascade_tax`, `cascade_sequence`, `item_tax_amount`) VALUES (2, '1', 1, '', '2.000', 1, 1, 0, 0, 0.3)
|
|
|
|
-- VAMOS CON EL ITEM 2 AHORA:
|
|
SELECT `ospos_items`.*, `ospos_suppliers`.`company_name`
|
|
FROM `ospos_items`
|
|
LEFT JOIN `ospos_suppliers` ON `ospos_suppliers`.`person_id` = `ospos_items`.`supplier_id`
|
|
WHERE `item_id` = '2'
|
|
|
|
-- inserta el item segundo el de id=2 en el detalle de la venta:
|
|
INSERT INTO `ospos_sales_items` (`sale_id`, `item_id`, `line`, `description`, `serialnumber`, `quantity_purchased`, `discount_percent`, `item_cost_price`, `item_unit_price`, `item_location`, `print_option`) VALUES (2, '2', 2, '', '', 1, '1.00', '13.00', '21.00', '1', 0)
|
|
|
|
-- manejo de existencia e inventario para el item segundo de id=2:
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '2'
|
|
AND `location_id` = '1'
|
|
-- actualiza el inventario pero para el segundo item el de id=2
|
|
UPDATE `ospos_item_quantities` SET `quantity` = 199, `item_id` = '2', `location_id` = '1'
|
|
WHERE `item_id` = '2'
|
|
AND `location_id` = '1'
|
|
|
|
-- procede a rebajar el inventario pro transaccion del id item 2:
|
|
INSERT INTO `ospos_inventory` (`trans_date`, `trans_items`, `trans_user`, `trans_location`, `trans_comment`, `trans_inventory`) VALUES ('2018-05-28 16:14:42', '2', '1', '1', 'POS 2', -1)
|
|
|
|
-- buscamos el taxes para el segundo item el de id = 2 en esta venta ya insertada:
|
|
SELECT * FROM `ospos_items_taxes` WHERE `item_id` = '2'
|
|
-- se encontro 2 taxes para el item 2 que estaba en esta venta con id = 2 pero este solo una es de porcentaje:
|
|
INSERT INTO `ospos_sales_items_taxes` (`sale_id`, `item_id`, `line`, `name`, `percent`, `tax_type`, `rounding_code`, `cascade_tax`, `cascade_sequence`, `item_tax_amount`) VALUES (2, '2', 2, '', '3.000', 1, 1, 0, 0, 0.62)
|
|
INSERT INTO `ospos_sales_taxes` (`sale_id`, `tax_type`, `tax_group`, `sale_tax_basis`, `sale_tax_amount`, `print_sequence`, `name`, `tax_rate`, `sales_tax_code`, `rounding_code`) VALUES (2, 1, '1% ', '14.8500', 0.15, 0, '', '1.000', '', 1)
|
|
INSERT INTO `ospos_sales_taxes` (`sale_id`, `tax_type`, `tax_group`, `sale_tax_basis`, `sale_tax_amount`, `print_sequence`, `name`, `tax_rate`, `sales_tax_code`, `rounding_code`) VALUES (2, 1, '3% ', '20.7900', 0.62, 0, '', '3.000', '', 1)
|
|
INSERT INTO `ospos_sales_taxes` (`sale_id`, `tax_type`, `tax_group`, `sale_tax_basis`, `sale_tax_amount`, `print_sequence`, `name`, `tax_rate`, `sales_tax_code`, `rounding_code`) VALUES (2, 1, '2% ', '14.8500', 0.3, 1, '', '2.000', '', 1)
|
|
|
|
-- ******* FIN MANEJO DE ITEMS ************
|
|
|
|
-- si es con restaurante actualiza las tablas o mesas afectadas:
|
|
UPDATE `ospos_dinner_tables` SET `status` = 0 WHERE `dinner_table_id` IS NULL
|
|
|
|
/* ******** FIN DE INSERCION Y SALVADO DE VENTA ACTUAL ********* */
|
|
|
|
|
|
SELECT `sales_items`.`sale_id`, `sales_items`.`item_id`, `sales_items`.`description`, `serialnumber`, `line`, `quantity_purchased`, `item_cost_price`, `item_unit_price`, `discount_percent`, `item_location`, `print_option`, `items`.`name` as `name`, `category`, `item_type`, `stock_type`
|
|
FROM `ospos_sales_items` as `sales_items`
|
|
JOIN `ospos_items` as `items` ON `sales_items`.`item_id` = `items`.`item_id`
|
|
WHERE `sales_items`.`sale_id` = '2'
|
|
ORDER BY `line` ASC
|
|
|
|
SELECT *
|
|
FROM `ospos_items`
|
|
WHERE (
|
|
`ospos_items`.`item_number` = '1'
|
|
OR `ospos_items`.`item_id` = 1
|
|
)
|
|
AND `ospos_items`.`deleted` =0
|
|
LIMIT 1
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
WHERE `location_id` = '1'
|
|
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '1'
|
|
AND `location_id` = '1'
|
|
|
|
SELECT *
|
|
FROM `ospos_items`
|
|
WHERE (
|
|
`ospos_items`.`item_number` = '2'
|
|
OR `ospos_items`.`item_id` = 2
|
|
)
|
|
AND `ospos_items`.`deleted` =0
|
|
LIMIT 1
|
|
|
|
SELECT *
|
|
FROM `ospos_stock_locations`
|
|
WHERE `location_id` = '1'
|
|
|
|
SELECT *
|
|
FROM `ospos_item_quantities`
|
|
WHERE `item_id` = '2'
|
|
AND `location_id` = '1'
|
|
|