Adjustment and Refunds (MySQL)

Fork
CREATE TABLE `payments` (
  `payment_id` int(11) NOT NULL,
  `active` varchar(1) NOT NULL,
  `client_id` int(11) NOT NULL,
  `pay_date` varchar(10) NOT NULL,
  `pay_type` varchar(30) NOT NULL,
  `pay_amount` varchar(30) NOT NULL,
  `pay_desc` varchar(100) NOT NULL,
  `invoice_date` varchar(30) NOT NULL,
  `invoice_no` int(11) NOT NULL,
  `modified` varchar(30)  DEFAULT NULL,
  `modified_user` varchar(30)  DEFAULT NULL,
  `added_user` varchar(30)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO `payments` (`payment_id`, `active`, `client_id`, `pay_date`, `pay_type`, `pay_amount`, `pay_desc`, `invoice_date`, `invoice_no`, `modified`, `modified_user`, `added_user`) VALUES
(1, '1', 1, '03-10-2022', 'Desconto', '25', 'Desconto por pagamento em dinheiro', '03-10-2022', 1, NULL, NULL, 'demo'),
(2, '1', 1, '03-10-2022', 'Dinheiro', '394.96', '', '03-10-2022', 1, NULL, NULL, 'demo'),
(3, '1', 4, '04-10-2022', 'Debito', '769.94', '', '04-10-2022', 3, NULL, NULL, 'demo'),
(4, '1', 2, '04-10-2022', 'Dinheiro', '290', '', '03-10-2022', 2, NULL, NULL, 'demo'),
(5, '1', 6, '04-10-2022', 'Desconto', '25', 'Cupom de Desconto A00025', '04-10-2022', 4, NULL, NULL, 'demo'),
(6, '1', 6, '04-10-2022', 'Credito', '675', 'Parcelado em 2X', '04-10-2022', 4, NULL, NULL, 'demo'),
(7, '1', 13, '05-10-2022', 'Credito', '149.95', 'Visa', '05-10-2022', 6, NULL, NULL, 'demo'),
(8, '1', 15, '05-10-2022', 'Debito', '497.59', '', '05-10-2022', 7, NULL, NULL, 'demo'),
(9, '1', 2, '12-10-2022', 'Dinheiro', '50', '', '05-10-2022', 5, NULL, NULL, 'demo'),
(10, '1', 2, '12-10-2022', 'AJUSTE', '49.95', '', '05-10-2022', 5, NULL, NULL, 'demo'),
(11, '1', 1, '11-11-2022', 'Desconto', '24.99', '', '11-11-2022', 10, NULL, NULL, 'admin'),
(12, '1', 1, '11-11-2022', 'PIX', '224.86', '', '11-11-2022', 10, NULL, NULL, 'admin'),
(13, '1', 2, '29-11-2022', 'Debito', '439.6', '', '29-11-2022', 11, NULL, NULL, 'admin'),
(14, '1', 9, '25-09-2023', 'PIX', '579.95', '&quot;valor&quot;', '14-10-2022', 8, NULL, NULL, 'demo'),
(15, '1', 13, '28-09-2023', 'PIX', '879.54', '', '25-09-2023', 13, NULL, NULL, 'demo'),
(16, '1', 4, '05-01-2024', 'PIX', '394.06', '', '05-01-2024', 14, NULL, NULL, 'demo'),
(17, '1', 1, '16-01-2024', 'Debito', '284.60', '', '24-10-2022', 9, NULL, NULL, 'demo'),
(18, '1', 2, '17-01-2024', 'Credito', '78.2', '', '16-01-2024', 15, NULL, NULL, 'demo'),
(19, '1', 5, '18-01-2024', 'Credito', '1959.92', '', '18-01-2024', 17, NULL, NULL, 'demo'),
(20, '1', 16, '18-01-2024', 'Debito', '1969.76', '', '18-01-2024', 18, NULL, NULL, 'demo'),
(21, '1', 11, '18-01-2024', 'Debito', '1999.76', '', '18-01-2024', 19, NULL, NULL, 'demo'),
(22, '1', 15, '19-01-2024', 'Dinheiro', '122.78', '', '17-01-2024', 16, NULL, NULL, 'demo'),
(23, '1', 1, '19-01-2024', 'Debito', '282.78', '', '19-01-2024', 20, NULL, NULL, 'demo'),
(24, '1', 2, '19-01-2024', 'PIX', '277.79', '', '19-01-2024', 21, NULL, NULL, 'demo'),
(25, '1', 4, '19-01-2024', 'Credito', '779.65', 'VISA', '19-01-2024', 22, NULL, NULL, 'Usuário'),
(26, '1', 3, '19-01-2024', 'Credito', '407.87', 'Mastercard', '19-01-2024', 23, NULL, NULL, 'demo'),
(27, '1', 3, '19-01-2024', 'Credito', '409.7', 'Mastercard', '19-01-2024', 24, NULL, NULL, 'demo'),
(28, '1', 14, '19-01-2024', 'Desconto', '50', '', '19-01-2024', 25, NULL, NULL, 'demo'),
(29, '1', 14, '19-01-2024', 'Dinheiro', '859.45', '', '19-01-2024', 25, NULL, NULL, 'demo'),
(30, '1', 17, '19-01-2024', 'Credito', '2019.86', 'Mastercard', '19-01-2024', 26, NULL, NULL, 'demo'),
(31, '1', 17, '19-01-2024', 'Dinheiro', '100', '', '19-01-2024', 26, NULL, NULL, 'Usuário'),
(32, '1', 1, '20-01-2024', 'Debito', '240', '', '20-01-2024', 28, NULL, NULL, 'demo'),
(33, '1', 6, '20-01-2024', 'PIX', '257.88', '', '19-01-2024', 27, NULL, NULL, 'demo'),
(34, '1', 13, '20-01-2024', 'Desconto', '9.75', '', '20-01-2024', 29, NULL, NULL, 'demo'),
(35, '1', 13, '20-01-2024', 'Dinheiro', '220', '', '20-01-2024', 29, NULL, NULL, 'demo'),
(36, '1', 13, '22-01-2024', 'PIX', '292.99', '', '22-01-2024', 33, NULL, NULL, 'demo'),
(37, '1', 18, '22-01-2024', 'Credito', '489.9', 'VISA', '20-01-2024', 30, NULL, NULL, 'demo'),
(38, '1', 18, '22-01-2024', 'Debito', '189.99', '', '21-01-2024', 31, NULL, NULL, 'demo'),
(39, '1', 14, '22-01-2024', 'Outro', '229.75', 'TED', '22-01-2024', 32, NULL, NULL, 'demo'),
(40, '1', 17, '22-01-2024', 'Debito', '90', '', '22-01-2024', 34, NULL, NULL, 'Usuário'),
(41, '1', 4, '22-01-2024', 'Credito', '329.65', 'Mastercard', '22-01-2024', 35, NULL, NULL, 'Usuário'),
(42, '1', 19, '22-01-2024', 'Credito', '309.89', 'Mastercard', '22-01-2024', 36, NULL, NULL, 'demo'),
(43, '1', 2, '22-01-2024', 'Credito', '269.75', '', '22-01-2024', 37, NULL, NULL, 'demo'),
(44, '1', 2, '22-01-2024', 'Credito', '279.55', 'Visa', '22-01-2024', 38, NULL, NULL, 'demo'),
(45, '1', 10, '23-01-2024', 'PIX', '119.96', '', '23-01-2024', 39, NULL, NULL, 'demo'),
(46, '1', 7, '23-01-2024', 'Credito', '2039.92', 'Visa', '23-01-2024', 40, NULL, NULL, 'Usuário'),
(47, '1', 18, '23-01-2024', 'Dinheiro', '240', '', '23-01-2024', 41, NULL, NULL, 'Usuário'),
(48, '1', 8, '24-01-2024', 'Credito', '303.32', 'Mastercard', '24-01-2024', 42, NULL, NULL, 'demo'),
(49, '1', 22, '25-01-2024', 'Credito', '699.8', 'Mastercard', '25-01-2024', 44, NULL, NULL, 'demo'),
(50, '1', 21, '25-01-2024', 'PIX', '149.75', '', '25-01-2024', 43, NULL, NULL, 'demo'),
(51, '1', 21, '25-01-2024', 'Dinheiro', '80', '', '25-01-2024', 43, NULL, NULL, 'demo'),
(52, '1', 23, '25-01-2024', 'Debito', '459.65', '', '25-01-2024', 45, NULL, NULL, 'demo'),
(53, '1', 25, '26-01-2024', 'Credito', '2239.9', 'Mastercard', '26-01-2024', 46, NULL, NULL, 'demo'),
(54, '1', 14, '26-01-2024', 'Credito', '160.9', 'Visa', '26-01-2024', 47, NULL, NULL, 'demo'),
(55, '1', 9, '30-01-2024', 'Credito', '402.77', 'Visa', '30-01-2024', 48, NULL, NULL, 'demo'),
(56, '1', 10, '30-01-2024', 'PIX', '597.74', '', '30-01-2024', 49, NULL, NULL, 'demo'),
(57, '1', 1, '30-01-2024', 'Dinheiro', '1500', '', '30-01-2024', 50, NULL, NULL, 'demo'),
(58, '1', 28, '31-01-2024', 'Credito', '2179.96', 'Mastercard', '31-01-2024', 53, NULL, NULL, 'demo'),
(59, '1', 13, '31-01-2024', 'PIX', '1999.9', '', '30-01-2024', 51, NULL, NULL, 'demo'),
(60, '1', 14, '01-02-2024', 'Debito', '399.91', '', '01-02-2024', 54, NULL, NULL, 'demo'),
(61, '1', 31, '05-02-2024', 'PIX', '2549.8', '', '05-02-2024', 56, NULL, NULL, 'demo'),
(62, '0', 8, '04-04-2024', 'Boleto', '267', '', '30-01-2024', 52, '08-04-2024', 'demo', 'demo'),
(63, '1', 10, '04-04-2024', 'Boleto', '1199780.01', '', '04-04-2024', 76, NULL, NULL, 'demo'),
(64, '0', 8, '08-04-2024', 'ESTORNO', '267', 'Cliente insatisfeito', '30-01-2024', 52, '08-04-2024', 'demo', 'demo'),
(65, '0', 8, '08-04-2024', 'ESTORNO', '265', 'cliente sad', '30-01-2024', 52, '09-04-2024', 'demo', 'demo'),
(66, '0', 8, '09-04-2024', 'ESTORNO', '-265.00', '', '30-01-2024', 52, '09-04-2024', 'demo', 'demo'),
(67, '0', 8, '09-04-2024', 'ESTORNO', '-300', '', '30-01-2024', 52, '09-04-2024', 'demo', 'demo'),
(68, '1', 8, '09-04-2024', 'Boleto', '267.88', '', '30-01-2024', 52, NULL, NULL, 'demo'),
(69, '1', 8, '09-04-2024', 'ESTORNO', '-267.88', '', '30-01-2024', 52, NULL, NULL, 'demo'),
(70, '0', 8, '09-04-2024', 'ESTORNO', '-303.32', '', '24-01-2024', 42, '09-04-2024', 'demo', 'demo'),
(71, '1', 8, '09-04-2024', 'AJUSTE', '303.32', '', '24-01-2024', 42, NULL, NULL, 'demo'),
(72, '0', 8, '09-04-2024', 'ESTORNO', '-600', '', '24-01-2024', 42, '09-04-2024', 'demo', 'demo'),
(73, '1', 8, '09-04-2024', 'AJUSTE', '267.88', '', '30-01-2024', 52, NULL, NULL, 'demo'),
(74, '1', 10, '09-04-2024', 'Desconto', '10', '', '09-04-2024', 77, NULL, NULL, 'demo'),
(75, '1', 8, '09-04-2024', 'ESTORNO', '-303.32', '', '24-01-2024', 42, NULL, NULL, 'demo'),
(76, '1', 30, '09-04-2024', 'Boleto', '14666.51', '', '25-03-2024', 74, NULL, NULL, 'demo'),
(77, '1', 9, '09-04-2024', 'ESTORNO', '-579.95', '', '14-10-2022', 8, NULL, NULL, 'demo'),
(78, '1', 9, '09-04-2024', 'AJUSTE', '579.95', '', '14-10-2022', 8, NULL, NULL, 'demo'),
(79, '1', 32, '05-07-2024', 'Debito', '2000', '', '25-03-2024', 75, NULL, NULL, 'demo'),
(80, '0', 13, '28-08-2024', 'AJUSTE', '400', '', '25-09-2023', 12, '28-08-2024', 'demo', 'demo'),
(81, '1', 30, '28-08-2024', 'AJUSTE', '2000', '', '02-02-2024', 55, NULL, NULL, 'demo'),
(82, '1', 9, '28-08-2024', 'ESTORNO', '-402.77', '', '30-01-2024', 48, NULL, NULL, 'demo'),
(83, '1', 13, '28-08-2024', 'Boleto', '419.8', '', '25-09-2023', 12, NULL, NULL, 'demo'),
(84, '1', 13, '28-08-2024', 'ESTORNO', '-120', '', '25-09-2023', 12, NULL, NULL, 'demo'),
(85, '1', 13, '28-08-2024', 'AJUSTE', '120', '', '25-09-2023', 12, NULL, NULL, 'demo'),
(86, '1', 9, '28-08-2024', 'AJUSTE', '200', '', '30-01-2024', 48, NULL, NULL, 'demo'),
(87, '1', 32, '28-08-2024', 'Desconto', '89.7', '', '28-08-2024', 78, NULL, NULL, 'demo'),
(88, '1', 32, '28-08-2024', 'PIX', '200', '', '28-08-2024', 78, NULL, NULL, 'demo'),
(89, '1', 32, '28-08-2024', 'ESTORNO', '-100', '', '28-08-2024', 78, NULL, NULL, 'demo'),
(90, '1', 32, '28-08-2024', 'AJUSTE', '200', '', '28-08-2024', 78, NULL, NULL, 'demo'),
(91, '1', 32, '28-08-2024', 'Credito', '300', '', '28-08-2024', 79, NULL, NULL, 'demo'),
(92, '1', 32, '28-08-2024', 'ESTORNO', '-100', '', '28-08-2024', 79, NULL, NULL, 'demo'),
(93, '1', 32, '28-08-2024', 'AJUSTE', '551.2', '', '28-08-2024', 79, NULL, NULL, 'demo'),
(94, '1', 9, '28-08-2024', 'AJUSTE', '202.77', '', '30-01-2024', 48, NULL, NULL, 'demo'),
(95, '0', 4, '03-09-2024', 'ESTORNO', '-100', '', '22-01-2024', 35, '03-09-2024', 'demo', 'demo'),
(96, '0', 4, '03-09-2024', 'ESTORNO', '-229.65', '', '22-01-2024', 35, '03-09-2024', 'demo', 'demo'),
(97, '1', 11, '03-09-2024', 'ESTORNO', '-100', '', '18-01-2024', 19, NULL, NULL, 'demo'),
(98, '1', 11, '03-09-2024', 'ESTORNO', '-1899.76', '', '18-01-2024', 19, NULL, NULL, 'demo'),
(99, '1', 11, '03-09-2024', 'AJUSTE', '1999.76', '', '18-01-2024', 19, NULL, NULL, 'demo');


SELECT e.*
FROM (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS e
JOIN (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount

UNION ALL

SELECT a.*
FROM (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS e
JOIN (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount
Fork
Embed on website
