To explain my case, I need first to explain how a dispensation of medicines from the People's Pharmacy (the Federal Government's program) works.
I am making a system for better dispensing organization here at my pharmacy.
How does dispensing work?
1) The patient arrives at the pharmacy with the prescription, document with photo and CPF.
2) If the prescription is within validity (it is valid for 180 days from the date of the consultation), we will dispense the medications for the 30-day equivalent.
3) Dispensing is done normally and saved in DB.
Note:
- A patient can have X valid recipes;
- A prescription can have AND medicines;
Well, the system was working well, when I realized that I was updating the dispensation, when it needs to be registered (for a later consultation). The system typically displays all patient prescriptions using the following query:
Complete PHP query that displays all recipes per patient:
$sql1 = "SELECT a.crm, a.data_receita, a.data_dispensacao, a.receita, a.id_paciente, b.classe, a.id AS id_disp, GROUP_CONCAT(b.medicamento SEPARATOR '|||') medicamentos, GROUP_CONCAT(a.quantidade SEPARATOR '|||') quantidades FROM dispensacao a INNER JOIN medicamentos b ON b.id = a.id_medicamento WHERE a.id_paciente = {$ln['id']} GROUP BY data_receita";
$query1 = $mysqli->query($sql1);
while($lnReceita = $query1->fetch_array()){
echo '
<div id="'.$lnReceita['id_disp'].'" class="card receita-dispensada card-receitas-'.$lnReceita['id_disp'].'">
<div class="card-content">
<div class="card-content-inner">
<div class="row" style="margin-bottom: 15px">
<div class="col-50"><i class="icon f7-icons size-16">today</i> <b>Data da Receita</b></div>
<div class="col-50"><a href="#" class="data-receita">'.date('d/m/Y', strtotime($lnReceita['data_receita'])).'</a> <span class="apagar_receita" style="left: 90%; position: absolute"><a href="#">Apagar</a></span> </div>
</div>
<div class="row" style="margin-bottom: 15px">
<div class="col-50"><i class="icon f7-icons size-16">today_fill</i> <b>Data da Dispensação</b></div>
<div class="col-50"><a href="#" data-receita="'.$lnReceita['data_receita'].'" data-classe="'.$lnReceita['classe'].'" data-crm="'.$lnReceita['crm'].'" data-idpac="'.$lnReceita['id_paciente'].'" class="data-dispensacao">'.date('d/m/Y', strtotime($lnReceita['data_dispensacao'])).'</a></div>
</div>
<div class="row" style="margin-bottom: 15px">
<div class="col-50"><i class="icon f7-icons size-16">card</i> <b>CRM</b></div>
<div class="col-50">'.$lnReceita['crm'].'</div>
</div>
<div class="row" style="margin-bottom: 15px">
<div class="col-50"><i class="icon f7-icons size-16">list</i> <b>Medicamentos Dispensados</b></div>
<div class="col-50">
<div class="data-table card centralized">
<table>
<thead>
<tr>
<th class="label-cell">Medicamento</th>
<th class="numeric-cell">Quantidade</th>
</tr>
</thead>
<tbody id="list_med">
';
$medicamentos = explode('|||', $lnReceita['medicamentos']);
$quantidades = explode('|||', $lnReceita['quantidades']);
for ($i = 0; $i < count($medicamentos); $i++) {
echo '
<tr>
<td class="label-cell">'.$medicamentos[$i].'</td>
<td class="numeric-cell">'.$quantidades[$i].'</td>
</tr>';
}
echo '
</tbody>
</table>
</div>
</div>
</div>
<div class="row receita" style="margin-bottom: 15px">
<div class="col-50"><i class="icon f7-icons size-16">images</i> <b>Receita</b></div>
<div class="col-50"><img id="img_receita_'.$lnReceita['id_disp'].'" src="'.PATH_IMGS.''.$lnReceita['receita'].'" width="100" height="100"></div>
</div>
</div>
<div class="row">
<div class="col-50"></div>
<div class="col-50 theme-orange">
<a href="#" class="button button-fill acoes_print" style="margin-top: -5px"><i class="material-icons color-white icon_btn">print</i><span class="label_btn"> Imprimir</span></a>
</div>
</div>
</div>
</div>
';
}
echo ' </div>
<div class="row">
<a style="padding-bottom: 15px; height: 30px" href="#" class="col-100 button button-fill add_receita"><i class="icon f7-icons size-12 color-white">add_round</i> Adicionar Receita</a>
</div>
</div>
</div>
</div>';
}
ERROR:
At each dispensation, it is added to the DB, and the only thing that changes a patient, is the date_dispensation, which, in the above query, REPEAT the drug.
DB Structure:
-- --------------------------------------------------------
--
-- Estrutura para tabela 'dispensacao'
--
CREATE TABLE 'dispensacao' (
'id' int(11) NOT NULL,
'id_paciente' int(11) NOT NULL,
'id_medicamento' int(11) NOT NULL,
'quantidade' int(11) NOT NULL,
'crm' int(11) NOT NULL,
'data_receita' date NOT NULL,
'data_dispensacao' date NOT NULL,
'receita' varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Estrutura para tabela 'medicamentos'
--
CREATE TABLE 'medicamentos' (
'id' int(11) NOT NULL,
'medicamento' varchar(255) NOT NULL,
'laboratorio' varchar(255) NOT NULL,
'classe' varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Estrutura para tabela 'pacientes'
--
CREATE TABLE 'pacientes' (
'id' int(11) NOT NULL,
'nome' varchar(255) NOT NULL,
'cpf' varchar(20) NOT NULL,
'telefone' varchar(30) NOT NULL,
'endereco' varchar(255) NOT NULL,
'documento' varchar(255) NOT NULL,
'procuracao' varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Estrutura para tabela 'usuarios'
--
CREATE TABLE 'usuarios' (
'id' int(11) NOT NULL,
'nome' varchar(255) NOT NULL,
'email' varchar(255) NOT NULL,
'senha' varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Índices de tabelas apagadas
--
--
-- Índices de tabela 'dispensacao'
--
ALTER TABLE 'dispensacao'
ADD PRIMARY KEY ('id');
--
-- Índices de tabela 'medicamentos'
--
ALTER TABLE 'medicamentos'
ADD PRIMARY KEY ('id');
--
-- Índices de tabela 'pacientes'
--
ALTER TABLE 'pacientes'
ADD PRIMARY KEY ('id');
--
-- Índices de tabela 'usuarios'
--
ALTER TABLE 'usuarios'
ADD PRIMARY KEY ('id');
--
-- AUTO_INCREMENT de tabelas apagadas
--
--
-- AUTO_INCREMENT de tabela 'dispensacao'
--
ALTER TABLE 'dispensacao'
MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=284;
--
-- AUTO_INCREMENT de tabela 'medicamentos'
--
ALTER TABLE 'medicamentos'
MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
--
-- AUTO_INCREMENT de tabela 'pacientes'
--
ALTER TABLE 'pacientes'
MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;
--
-- AUTO_INCREMENT de tabela 'usuarios'
--
ALTER TABLE 'usuarios'
MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
What I expect:
Just do not repeat the medication, with each new dispensation of the same prescription for the patient!
Thank you in advance!