Issue Statement of Services

Question

Good afternoon staff I am trying to issue a statement of services from my record.

follows my database

CREATE TABLE `cad_cliente` (
    `id_cliente` smallint(5) UNSIGNED NOT NULL,
    `nome_cliente` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Extraindo dados da tabela `cad_cliente`
--

INSERT INTO `cad_cliente` (`id_cliente`, `nome_cliente`) 
VALUES
    (10, 'Fernanda Rocha'),
    (11, 'Érica Veloso Lima'),
    (12, 'Fernando Mota');

CREATE TABLE `cad_financeiro` (
    `id_financeiro` smallint(5) UNSIGNED NOT NULL,
    `id_trabalho` varchar(45) NOT NULL,
    `valor_financeiro` decimal(10,2) NOT NULL,
    `debito_credito_financeiro` varchar(100) NOT NULL,
    `pago_financeiro` varchar(30) NOT NULL,
    `descricao_financeiro` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `cad_financeiro` (`id_financeiro`, `id_trabalho`, `valor_financeiro`, `debito_credito_financeiro`, `pago_financeiro`, `descricao_financeiro`) VALUES
(25, '1183', '985.00', 'D', 'N', '08 Provisórios Convencionais R$ 560,00\r\n05 Provisórios Com Reforço   R$ 425,00\r\n\r\n                                                                            '),
(30, '1225', '80.00', 'D\r\n', 'N', '01 Provisorio'),
(31, '1226', '330.00', 'D\r\n', 'N', '01 Emax'),
(32, '1227', '250.00', 'C', 'N', 'PAGAMENTO');

CREATE TABLE `cad_paciente` (
  `id_paciente` smallint(5) UNSIGNED NOT NULL,
  `nome_paciente` varchar(45) NOT NULL,
  `id_cliente` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cad_paciente` (`id_paciente`, `nome_paciente`, `id_cliente`) VALUES
(1123, 'Gildete', '11'),
(1165, 'Agnaldo', '11'),
(1166, 'Suelene', '11'),
(1167, 'PAGAMENTO', '11'),
(1168, 'Maria Aparecida', '11'),
(1169, 'Bernadete (Dra Isadora)', '19'),
(1170, 'Lisa', '36'),
(1171, 'Nathalia Alves', '56');

CREATE TABLE `cad_trabalho` (
  `id_trabalho` smallint(5) UNSIGNED NOT NULL,
  `id_paciente` varchar(45) NOT NULL,
  `id_dente` varchar(45) NOT NULL,
  `id_servico` varchar(45) NOT NULL,
  `id_cor` varchar(45) NOT NULL,
  `observacao_trabalho` text NOT NULL,
  `data_entrada_trabalho` date NOT NULL,
  `data_saida_trabalho` date NOT NULL,
  `pronto_trabalho` date NOT NULL,
  `substrato_trabalho` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Extraindo dados da tabela `cad_trabalho`
--

INSERT INTO `cad_trabalho` (`id_trabalho`, `id_paciente`, `id_dente`, `id_servico`, `id_cor`, `observacao_trabalho`, `data_entrada_trabalho`, `data_saida_trabalho`, `pronto_trabalho`, `substrato_trabalho`) 
VALUES
    (1181, '1120', '36', '01 Zirconia Sobre Implante', 'A3,5 Cervical /A3 Oclusal', '', '2016-05-30', '2016-06-06', '0000-00-00', 'Implante'),
    (1182, '1122', '22,12,26', 'Facetas', 'D22 e 12 A2 / D26 A3,5', '', '2016-05-31', '2016-06-01', '0000-00-00', 'D22 A3 / D12 A3'),
    (1183, '1123', '', 'Provisorio Superior Com Barra', 'A2', '', '2016-06-01', '2016-06-08', '2016-06-03', ''),
    (1220, '1160', '', '', '', '', '2016-06-03', '2016-06-10', '0000-00-00', ''),
    (1225, '1165', '', '', '', '', '0000-00-00', '0000-00-00', '2016-03-29', ''),
    (1226, '1166', '', '', '', '', '0000-00-00', '0000-00-00', '2016-03-17', ''),
    (1227, '1167', '', '', '', '', '0000-00-00', '0000-00-00', '2016-03-17', '');

I saw in this post a very nice extract I tried to do as it is done there but I could not create the table view with all the necessary information that is displayed as lowered by id_cliente

data        paciente         descricao  debito   credito    saldo
2016-03-29  Agnaldo          Provisorio  80,00    00,00   - 80,00
2016-05-20  Maria Aparecida  01 Emax    350,00    00,00   -430,00
2016-06-06  PAGAMENTO        PAGAMENTO   00,00   250,00   -180,00

After a long time I came to this SELECT but he can’t return the data the way I need it

SELECT *,
SUM(IF(debito_credito_financeiro = 'D', valor_financeiro, 0)) AS debito,
SUM(IF(debito_credito_financeiro = 'C', valor_financeiro, 0)) AS credito,
(SELECT SUM(IF(debito_credito_financeiro = 'C', valor_financeiro, -valor_financeiro)) FROM cad_financeiro AS L2 WHERE cad_financeiro.id_financeiro >= L2.id_financeiro) AS saldo
FROM cad_financeiro
JOIN cad_trabalho ON cad_financeiro.id_trabalho = cad_trabalho.id_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
WHERE cad_cliente.id_cliente = '11'
GROUP BY cad_cliente.id_cliente, id_financeiro ORDER BY id_financeiro

Answer 1

Basically where you’re wearing:

SUM(IF(debito_credito_financeiro = 'D', valor_financeiro, 0)) AS debito,

Change to

sum(case when debito_credito_financeiro = 'D' then valor_financeiro else 0 end) as debito,

Also change to credit....

sum(case when f.debito_credito_financeiro = 'C' then f.valor_financeiro else 0 end) as credito,

For the total just use one less the other:

(sum(case when f.debito_credito_financeiro = 'C' then f.valor_financeiro else 0 end) 
  - sum(case when f.debito_credito_financeiro = 'D' then f.valor_financeiro else 0 end) )as saldo

See the full consultation:

SELECT 
t.pronto_trabalho
, p.nome_paciente
, descricao_financeiro
, sum(case when f.debito_credito_financeiro = 'D' then f.valor_financeiro else 0 end) as debito
, sum(case when f.debito_credito_financeiro = 'C' then f.valor_financeiro else 0 end) as credito
, (sum(case when f.debito_credito_financeiro = 'C' then f.valor_financeiro else 0 end) 
  - sum(case when f.debito_credito_financeiro = 'D' then f.valor_financeiro else 0 end) )as saldo
FROM cad_financeiro as f
join cad_trabalho as t on t.id_trabalho = f.id_trabalho
join cad_paciente as p on p.id_paciente = t.id_paciente
join cad_cliente as i on i.id_cliente = p.id_cliente
group by t.pronto_trabalho, p.id_paciente

Outcome of the consultation:

'2016-03-17', 'Suelene', '01 Emax', '330.00', '0.00', '-330.00'
'2016-03-17', 'PAGAMENTO', 'PAGAMENTO', '0.00', '250.00', '250.00'
'2016-03-29', 'Agnaldo', '01 Provisorio', '80.00', '0.00', '-80.00'
'2016-06-03', 'Gildete', '08 Provisórios Convencionais R$ 560,00\r\n05 Provisórios Com Reforço   R$ 425,00', '985.00', '0.00', '-985.00'

Important: Check your table cad_financeiro, in some line the field debito_credito_financeiro has break lines (\r, \n or \r\n), because in the data you posted above there were.

Remember to adjust the group by according to your need.

I hope to have helped and wish you good luck in your project!