\documentclass[letter,10pt]{article}
\usepackage[brazilian]{babel}
\usepackage[T1]{fontenc}
\usepackage[latin1]{inputenc}
\usepackage{epsf, graphics, pifont}
\usepackage{subfigure}                     % uso de várias Figuras numa só.
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{url}
\usepackage[pdftex]{graphicx}
\usepackage{listings}
% \usepackage[pagebackref,colorlinks=true,urlcolor=red,citecolor=green,linkcolor=blue]{hyperref}
\graphicspath{{./figuras/}} 

\title{\Large MAC5853 -- Desenvolvimento de Sistemas de Computação\\
    \vspace*{5mm}{Administradora de Serviços Médicos}\\
    Fase 1}

\author{Jesús P. Mena-Chalco}
%\date{16 de Junho de 2004}

%% ------------------------------------------------------------------- %%
\setlength{\parskip}{.5pc}
\setlength{\paperwidth}{216mm}
% \setlength{\paperheight}{279mm}
%\setlength{\pdfpagewidth}{216mm}
%\setlength{\pdfpageheight}{279mm}
%\setlength{\topmargin}{1.3cm}          % deslocamento do topo do texto 
\setlength{\textwidth}{16.8cm}          % largura do texto
% \setlength{\textheight}{22.5cm}
\setlength\oddsidemargin{0cm}           % juntamente com a linha seg. dá impr.frente.
\setlength\evensidemargin{0cm}

\renewcommand{\baselinestretch}{1.5}

%% ------------------------------------------------------------------- %%
%% ------------------------------------------------------------------- %%
\begin{document}
\thispagestyle{empty}
\maketitle

%\lstset{language=SQL}
%\lstset{backgroundcolor=\color{yellow}}

%% ------------------------------------------------------------------- %%
\section{Introdução}
\label{sec:introducao}
O sistema de computação a ser desenvolvido é um sistema para o gerenciamento de
contratos, beneficiários, dependentes e execuções de serviços médicos oferecidos
por uma empresa Administradora de Serviços Médicos. Cada contrato, com valor de
mensalidade e data de validade, está associado a um beneficiário em particular e
mantém uma lista de serviços atribuídos tanto ao beneficiário quanto a seus
dependentes. As execuções dos serviços são dadas para um beneficiário ou um
dependente a qual deve estar relacionada com um médico, um centro médico, e uma
data de execução.

Os dados dos beneficiários com seus dependentes, médicos, contratos, serviços,
centros médicos e serviços executados são obtidos de um outro sistema de ``Ingresso''
que exporta estes dados em formato XML, sendo assim, uma das entradas ao
sistema. O sistema a ser desenvolvido permitirá a carga dos dados de forma
manual, com vistas a que em uma outra versão isto seja implementados como um
processo automático.

O sistema contará com três módulos: (a) um módulo de cadastro de contratos, em
que para um dado beneficiário se atribuirá os serviços disponíveis pela
administradora, (b) um módulo de cadastro de execuções de serviços médicos, e
(c) um módulo de classificação de contratos.

Será utilizada a linguagem de programação \texttt{Java} para o desenvolvimento
de todos os módulos, sendo configurado um banco de dados em um servidor
gerenciado com PostgreSQL.

Este relatório esta organizado da seguinte maneira: na
Seção~\ref{sec:arquitetura} mostramos a arquitetura criada para o
desenvolvimento do sistema de computação. Na Seção~\ref{sec:modelo}
mostramos o modelo de banco de dados usado. Na Seção~\ref{sec:cenarios} descrevemos
alguns cenários de exemplo. Finalmente, na Seção~\ref{sec:implementacao}
mostramos alguns aspectos de implementação.

%% ------------------------------------------------------------------- %%
\section{Arquitetura do sistema}
\label{sec:arquitetura}
Na Figura~\ref{fig:arquitetura} ilustramos um esquema correspondente à
arquitetura do sistema a ser desenvolvido.
\begin{figure}[!h]
    \centering
    \includegraphics[width=.8\textwidth]{arquitetura.pdf}
    \caption{Esquema da arquitetura do sistema de administração de serviços
    médicos a ser desenvolvido.}
    \label{fig:arquitetura}
\end{figure}


\begin{itemize}
    \item A \emph{interface usuário-sistema} fornece um ambiente interativo para a
    especificação de informação correspondente ao cadastro de contratos e
    serviços, cadastro de execuções de serviços, consulta de classificação de
    contratos e carga de dados desde arquivos em formato XML.

    \item O \emph{módulo de cadastro de contratos e serviços} utiliza as
    informações especificadas pelo usuário para cadastrar novos contratos. A
    cada contrato poderão ser atribuídos um o vários serviços médicos. O valor
    de mensalidade será, então, aquele que este de acordo com a quantidade de
    serviços médicos considerados no contrato.

    \item O \emph{módulo de cadastro de execuções de serviços médicos} utiliza
    as informações do usuário para registrar uma nova execução de um serviço
    médico para um dado contrato. As execuções a serem realizadas deveram ir de
    acordo com os serviços médicos considerados ou indicados no contrato.

    \item O \emph{módulo de consulta de classificação de contratos médicos}
    permite diferenciar contratos em deficitários ou não deficitários. Um
    contrato é considerado deficitário se o valor dos serviços médicos
    executados, em um determinado mês, é maior que três vezes o valor da sua
    mensalidade.

    \item O \emph{módulo de carga de dados de arquivos XML}, permite popular o
    banco de dados com as informações de beneficiários e seus dependentes,
    médicos, contratos e seus serviços, serviços executados e centros médicos.
\end{itemize}

Para o desenvolvimento do sistema serão considerados somente dois atores. Veja na
Figura~\ref{fig:diagrama_casos_uso} uma representação do diagrama dos casos de
uso:

\begin{itemize}
    \item Usuário do sistema, que é responsável de efetuar o cadastro de
    contratos médicos, dos serviços associados aos contratos, das execuções de
    serviços providenciados aos beneficiários ou dependentes; e as consultas de
    classificações de contratos.
        
    \item Administrador do sistema, que é responsável de efetuar a carga de
    dados de arquivos XML provenientes de um sistema de ``Ingresso''. Esse
    sistema permitirá unicamente o cadastro de novos beneficiários, dependentes,
    serviços, centros médicos, médicos e dados inicias de contratos e execuções
    de serviços.
\end{itemize}

\begin{figure}[!h]
    \centering
    \includegraphics[width=.45\textwidth]{diagrama_casos_uso.pdf}
    \caption{Diagrama de casos de uso.}
    \label{fig:diagrama_casos_uso}
\end{figure}

%% ------------------------------------------------------------------- %%
\section{Modelo de dados}
\label{sec:modelo}
O diagrama ERX, correspondente ao modelo entidade-relacionamento, está mostrado na
Figura~\ref{fig:diagrama-ERX}. Neste modelo, as principais entidades
consideradas foram:  médico, beneficiário, contrato, serviço e centro médico.
Baseados na nossa interpretação do enunciado do problema, foram consideradas as
seguintes restrições no modelamento:

\begin{itemize}
    \item Um médico pode ter muitas especialidades (veja o conjunto de
    relacionamentos \texttt{esp\_med} na Figura~\ref{fig:diagrama-ERX}).

    \item Um beneficiário pode ou não ter muitos dependentes (conjunto de
    relacionamentos \texttt{benef\_dep}).
    
    \item Um beneficiário pode ou não ter muitos contratos (conjunto de
    relacionamentos \texttt{cont\_benef}).

    \item A um contrato são associados um o vários serviços (conjunto de
    relacionamentos \texttt{serv\_cont}). 

    \item Para a execução de um novo serviço médico somente são considerados os
    serviços descritos no contrato (agregação \texttt{serv\_cont}). Tanto a data
    de execução, quanto o centro médico, médico responsável e possível dependente são
    registrados (conjunto de relacionamentos \texttt{execucao\_serv\_cont}). Se
    o dependente não for especificado na execução do serviço então será
    considerado ao beneficiário como pessoa que fará uso do serviço.
\end{itemize}

O modelo físico que será adotado no desenvolvimento do sistema de administração
de serviços públicos está mostrado na Figura~\ref{fig:modelo-fisico}. No
Apêndice~\ref{ape:tabelas} é apresentada uma descrição de cada tabela com seus
respectivos atributos, chaves primárias e estrangeiras.
\begin{figure}[!p]
    \centering
    \includegraphics[width=.9\textheight, angle=90]{diagrama_ERX1.pdf}
    \caption{Modelo entidade-relacionamento.}
    \label{fig:diagrama-ERX}
\end{figure}

\begin{figure}[!p]
    \centering
    \includegraphics[width=.9\textheight, angle=90]{modelo_fisico.png}
    \caption{Diagrama do modelo físico.}
    \label{fig:modelo-fisico}
\end{figure}

%% ------------------------------------------------------------------- %%
\section{Cenários de operação}
\label{sec:cenarios}
% ---------
\subsection{Cenário 1: Cadastro de contratos médicos}
O cadastro de um novo contrato requer:
\begin{itemize}
\item Verificação de contratos existentes no sistema de administração de serviços médicos:
{\small
\begin{verbatim}
SELECT beneficiario.cpf, beneficiario.nome, contrato.numero, contrato.data_validade, 
contrato.valor_mensalidade FROM beneficiario, contrato WHERE beneficiario.cpf=contrato.cpf_beneficiario
ORDER BY beneficiario.cpf;

     cpf     |        nome        | numero | data_validade | valor_mensalidade
-------------+--------------------+--------+---------------+-------------------
 11120030044 | João Nogeira Serra | C00001 | 2008-12-31    |            250.56
 23143487647 | Ricardo takeshi    | C00001 | 2006-07-15    |            104.32
 33487637646 | Eliane Silva       | C00001 | 2010-12-31    |            345.89
\end{verbatim}}

\item Seleção dos serviços oferecidos pela administradora a serem considerados no novo contrato: 
{\small
\begin{verbatim}
SELECT servico.codigo, tipo_servico.nome AS tipo_servico, servico.descricao, servico.valor 
FROM servico, tipo_servico WHERE servico.codigo_tipo_servico=tipo_servico.codigo;

 codigo | tipo_servico |       descricao       |  valor
--------+--------------+-----------------------+---------
 S01    | Consulta     | Consulta clínica      |   45.00
 S02    | Consulta     | Consulta ortopedia    |   25.00
 S03    | Consulta     | Consulta oftalmologia |   10.00
 S04    | Consulta     | Consulta Ginecologia  |   15.00
 S05    | Diagnose     | Diagnose dermatologia |   25.00
 S06    | Cirúrgico    | Cirurgia maior        | 3000.00
 S07    | Cirúrgico    | Cirurgia menor        | 2600.45
 S08    | Cirúrgico    | Cirurgia eletiva      | 1750.23
 S09    | Terapêutico  | Terapêutico A         |  800.20
 S10    | Terapêutico  | Terapêutico B         |  300.60
 S11    | Terapêutico  | Terapêutico C         |  250.00
\end{verbatim}}

\item Cadastro do novo contrato. Tanto a data de validade quanto o valor de
mensalidade devem ser especificados. Por exemplo, para o usuário com CFP número
23143487647 o cadastro do novo contrato C00002 será:
{\small
\begin{verbatim}
INSERT INTO contrato (numero,cpf_beneficiario,data_validade,valor_mensalidade)
VALUES ('C00002',23143487647,'15/07/2007',199.99);
\end{verbatim}}

Uma possível atribuição de quatro novos serviços médicos no contrato será dado
pelas seguintes instruções:
{\small
\begin{verbatim}
INSERT INTO servico_contrato (numero_contrato,cpf_beneficiario,codigo_servico)
VALUES ('C00002',23143487647,'S01');
INSERT INTO servico_contrato (numero_contrato,cpf_beneficiario,codigo_servico)
VALUES ('C00002',23143487647,'S02');
INSERT INTO servico_contrato (numero_contrato,cpf_beneficiario,codigo_servico)
VALUES ('C00002',23143487647,'S03');
INSERT INTO servico_contrato (numero_contrato,cpf_beneficiario,codigo_servico)
VALUES ('C00002',23143487647,'S04');
\end{verbatim}}

Assim, os serviços atribuídos no novo contrato será dada por:
{\small
\begin{verbatim}
SELECT ts.nome, s.descricao, s.valor, c.data_validade
FROM contrato c, servico s, servico_contrato sc, tipo_servico ts
WHERE c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND
s.codigo=sc.codigo_servico AND s.codigo_tipo_servico=ts.codigo AND 
c.numero='C00002' AND c.cpf_beneficiario=23143487647 ORDER BY ts.nome;

   nome   |       descricao       | valor | data_validade
----------+-----------------------+-------+---------------
 Consulta | Consulta clínica      | 45.00 | 2007-07-15
 Consulta | Consulta ortopedia    | 25.00 | 2007-07-15
 Consulta | Consulta oftalmologia | 10.00 | 2007-07-15
 Consulta | Consulta Ginecologia  | 15.00 | 2007-07-15
\end{verbatim}}

\end{itemize}

% ---------
\subsection{Cenário 2: Cadastro de execuções de serviços médicos}

Para cadastrar as execuções de serviços médicos serão necessárias várias
atividades. No exemplo a seguir supomos que deseja-se cadastrar vários serviços
médicos no contrato C00002 do beneficiário cujo CPF seja 23143487647.

\begin{itemize}

\item Seleção de um possível dependente do beneficiário:
%INSERT INTO dependente VALUES ('D00008','Liliam Dires Lara',23143487647,'20/02/1979','esposa');
{\small
\begin{verbatim}
SELECT d.nome, d.data_nascimento, d.grau_parentesco, b.nome AS beneficiario
FROM dependente d, beneficiario b
WHERE b.cpf=d.cpf_beneficiario AND b.cpf=23143487647 ORDER BY d.nome;

          nome        | data_nascimento | grau_parentesco | beneficiario
----------------------+-----------------+-----------------+-----------------
 Liliam Dires Lara    | 1979-02-20      | esposa          | Ricardo takeshi
\end{verbatim}}

\item Seleção de um médico para a execução do serviço:
{\small
\begin{verbatim}
SELECT e.nome AS especialidade, m.nome, m.crm, m.cpf
FROM medico m, especialidade e, especialidade_medico em
WHERE e.codigo=em.codigo_especialidade AND em.cpf_medico=m.cpf ORDER BY e.nome;

 especialidade |      nome      |    crm     |     cpf
---------------+----------------+------------+-------------
 Cardiologista | Patricia Bueno | CRM-909010 | 23467730011
 Cirurgião     | Patricia Bueno | CRM-909010 | 23467730011
 Clínico geral | Michael Zhang  | CRM-909020 | 45890456269
 Pediatra      | Juan Barrera   | CRM-909030 |  9457692002
 Pediatra      | Michael Zhang  | CRM-909020 | 45890456269
\end{verbatim}}

\item Seleção de um centro médico para a execução do serviço:
{\small
\begin{verbatim}
SELECT tcm.nome AS tipo_centro, cm.nome, cm.endereco, cm.cgc
FROM centro_medico cm, tipo_centro_medico tcm
WHERE cm.codigo_tipo_centro=tcm.codigo ORDER BY tcm.nome;

 tipo_centro |           nome            |       endereco       |    cgc
-------------+---------------------------+----------------------+------------
 Clínica     | Clínica Santo André       | Av. Rio Pequeno 1030 | 3003431300
 Hospital    | Hospital das Clínicas     | Av. Rebouças 4030    | 2003431200
 Laboratório | Laboratório do Bom Pastor | Av. Paulista 5040    | 4003431400
\end{verbatim}}

\item Seleção de um serviço médico contemplado no contrato:
{\small
\begin{verbatim}
SELECT ts.nome, s.descricao, s.valor, c.data_validade
FROM contrato c, servico s, servico_contrato sc, tipo_servico ts
WHERE c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND
s.codigo=sc.codigo_servico AND s.codigo_tipo_servico=ts.codigo AND 
c.numero='C00002' AND c.cpf_beneficiario=23143487647 ORDER BY ts.nome;

   nome   |       descricao       | valor | data_validade
----------+-----------------------+-------+---------------
 Consulta | Consulta clínica      | 45.00 | 2007-07-15
 Consulta | Consulta ortopedia    | 25.00 | 2007-07-15
 Consulta | Consulta oftalmologia | 10.00 | 2007-07-15
 Consulta | Consulta Ginecologia  | 15.00 | 2007-07-15
\end{verbatim}}

\item Cadastro de novos serviços a serem executados:
{\small
\begin{verbatim}
INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico,codigo_dependente)
VALUES ('C00002',23143487647,'S01','E0001','20/07/2006','3003431300',23467730011,'D00008');

INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico,codigo_dependente)
VALUES ('C00002',23143487647,'S01','E0002','22/07/2006','3003431300',23467730011,'D00008');

INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico,codigo_dependente)
VALUES ('C00002',23143487647,'S01','E0003','25/07/2006','3003431300',23467730011,'D00008');

INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico)
VALUES ('C00002',23143487647,'S02','E0001','22/07/2006','2003431200',45890456269);

INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico)
VALUES ('C00002',23143487647,'S03','E0002','25/07/2006','2003431200',9457692002);

INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico,
numero_execucao,data,cgc_centro_medico,cpf_medico)
VALUES ('C00002',23143487647,'S01','E0003','30/07/2006','2003431200',23467730011);
\end{verbatim}}

Assim, o comando necessário para listar os serviços executados ou a serem
executados é:
{\small
\begin{verbatim}
SELECT s.descricao AS servico, ex.numero_execucao AS execucao, ex.data, 
cm.nome AS centro_medico, m.nome AS medico, c.data_validade
FROM contrato c, servico_contrato sc, servico s, execucao_servico ex, medico m,
centro_medico cm 
WHERE c.numero='C00002' AND c.cpf_beneficiario=23143487647 AND
c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND
s.codigo=sc.codigo_servico AND ex.numero_contrato=sc.numero_contrato AND
ex.cpf_beneficiario=sc.cpf_beneficiario AND ex.codigo_servico=sc.codigo_servico AND
m.cpf=ex.cpf_medico AND cm.cgc=ex.cgc_centro_medico
ORDER BY ex.data ASC;

        servico        | execucao |    data    |     centro_medico     | medico         | data_validade
-----------------------+----------+------------+-----------------------+----------------+---------------
 Consulta clínica      | E0001    | 2006-07-20 | Clínica Santo André   | Patricia Bueno | 2007-07-15
 Consulta clínica      | E0002    | 2006-07-22 | Clínica Santo André   | Patricia Bueno | 2007-07-15
 Consulta ortopedia    | E0001    | 2006-07-22 | Hospital das Clínicas | Michael Zhang  | 2007-07-15
 Consulta clínica      | E0003    | 2006-07-25 | Clínica Santo André   | Patricia Bueno | 2007-07-15
 Consulta oftalmologia | E0002    | 2006-07-25 | Hospital das Clínicas | Juan Barrera   | 2007-07-15
\end{verbatim}}

\end{itemize}

% ---------
\subsection{Cenário 3: Consulta de classificação de contratos médicos}
% A classificação dos contratos em tipos deficitários e não deficitários será:

%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico,codigo_dependente)
%VALUES ('C00001',11120030044,'S07','E0001','03/04/2006','2003431200',23467730011,'D00001');
%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico,codigo_dependente)
%VALUES ('C00001',11120030044,'S09','E0001','26/05/2003','3003431300',9457692002,'D00003');

%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico)
%VALUES ('C00001',33487637646,'S10','E0001','02/08/2004','3003431300',45890456269);
%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico)
%VALUES ('C00001',33487637646,'S11','E0001','02/08/2004','3003431300',45890456269);

%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico)
%VALUES ('C00001',23143487647,'S03','E0001','20/02/2006','3003431300',23467730011);
%INSERT INTO execucao_servico (numero_contrato,cpf_beneficiario,codigo_servico, numero_execucao,data,cgc_centro_medico,cpf_medico)
%VALUES ('C00001',23143487647,'S04','E0001','05/01/2006','4003431400',23467730011);

\begin{itemize}

\item Lista de todas as execuções ordenadas por data de realização:

{\small
\begin{verbatim}
SELECT ex.data AS data_execucao, b.cpf, b.nome, c.numero AS contrato, s.descricao AS servico, s.valor
FROM beneficiario b, contrato c, servico s, servico_contrato sc, execucao_servico ex
WHERE b.cpf=c.cpf_beneficiario AND
c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND
s.codigo=sc.codigo_servico AND sc.numero_contrato=ex.numero_contrato AND
sc.cpf_beneficiario=ex.cpf_beneficiario AND sc.codigo_servico=ex.codigo_servico
ORDER BY ex.data;

 data_execucao |     cpf     |        nome        | contrato |        servico        |  valor
---------------+-------------+--------------------+----------+-----------------------+---------
 2003-05-26    | 11120030044 | João Nogeira Serra | C00001   | Terapêutico A         |  800.20
 2004-08-02    | 33487637646 | Eliane Silva       | C00001   | Terapêutico B         |  300.60
 2004-08-02    | 33487637646 | Eliane Silva       | C00001   | Terapêutico C         |  250.00
 2006-01-05    | 23143487647 | Ricardo takeshi    | C00001   | Consulta Ginecologia  |   15.00
 2006-02-20    | 23143487647 | Ricardo takeshi    | C00001   | Consulta oftalmologia |   10.00
 2006-04-03    | 11120030044 | João Nogeira Serra | C00001   | Cirurgia menor        | 2600.45
 2006-07-20    | 23143487647 | Ricardo takeshi    | C00002   | Consulta clínica      |   45.00
 2006-07-22    | 23143487647 | Ricardo takeshi    | C00002   | Consulta clínica      |   45.00
 2006-07-22    | 23143487647 | Ricardo takeshi    | C00002   | Consulta ortopedia    |   25.00
 2006-07-25    | 23143487647 | Ricardo takeshi    | C00002   | Consulta clínica      |   45.00
 2006-07-25    | 23143487647 | Ricardo takeshi    | C00002   | Consulta oftalmologia |   10.00
\end{verbatim}}

\item Lista dos valores dos serviços executados e agrupados por beneficiário:
{\small
\begin{verbatim}
SELECT c.data_validade, b.cpf, b.nome, c.numero AS contrato, SUM(s.valor) as total
FROM beneficiario b, contrato c, servico s, servico_contrato sc, execucao_servico ex
WHERE b.cpf=c.cpf_beneficiario AND
c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND
s.codigo=sc.codigo_servico AND sc.numero_contrato=ex.numero_contrato AND
sc.cpf_beneficiario=ex.cpf_beneficiario AND sc.codigo_servico=ex.codigo_servico
GROUP BY c.data_validade, b.cpf, b.nome, c.numero;

 data_validade |     cpf     |        nome        | contrato |  total
---------------+-------------+--------------------+----------+---------
 2006-07-15    | 23143487647 | Ricardo takeshi    | C00001   |   25.00
 2007-07-15    | 23143487647 | Ricardo takeshi    | C00002   |  170.00
 2008-12-31    | 11120030044 | João Nogeira Serra | C00001   | 3400.65
 2010-12-31    | 33487637646 | Eliane Silva       | C00001   |  550.60
\end{verbatim}}

\item Lista de classificação de gasto total por mês dos beneficiários:
{\small
\begin{verbatim}
SELECT date_part('year',ex.data) AS ano, date_part('month',ex.data) AS mes,
c.numero AS contrato, c.data_validade, b.nome AS beneficiario,
c.valor_mensalidade AS mensalidade, sum(s.valor) as total_gasto, c.valor_mensalidade*3 as "mensalidade*3"
FROM beneficiario b, contrato c, servico s, servico_contrato sc, execucao_servico ex
WHERE 
c.numero=sc.numero_contrato AND c.cpf_beneficiario=sc.cpf_beneficiario AND s.codigo=sc.codigo_servico AND
sc.numero_contrato=ex.numero_contrato AND sc.cpf_beneficiario=ex.cpf_beneficiario AND
sc.codigo_servico=ex.codigo_servico AND b.cpf=c.cpf_beneficiario
GROUP BY  ano, mes, c.numero,c.data_validade, beneficiario, c.valor_mensalidade
ORDER BY ano, mes;

 ano  | mes | contrato | data_validade |    beneficiario    | mensalidade | total_gasto | mensalidade*3
------+-----+----------+---------------+--------------------+-------------+-------------+---------------
 2003 |   5 | C00001   | 2008-12-31    | João Nogeira Serra |      250.56 | 800.20      |        751.68
 2004 |   8 | C00001   | 2010-12-31    | Eliane Silva       |      345.89 | 550.60      |       1037.67
 2006 |   1 | C00001   | 2006-07-15    | Ricardo takeshi    |      104.32 | 15.00       |        312.96
 2006 |   2 | C00001   | 2006-07-15    | Ricardo takeshi    |      104.32 | 10.00       |        312.96
 2006 |   4 | C00001   | 2008-12-31    | João Nogeira Serra |      250.56 | 2600.45     |        751.68
 2006 |   7 | C00002   | 2007-07-15    | Ricardo takeshi    |      199.99 | 170.00      |        599.97
\end{verbatim}}

\item Lista de classificação dos contratos em tipos deficitários e não deficitários:
{\small
\begin{verbatim}
 contrato | data_validade |    beneficiario    | deficitario 
----------+---------------+--------------------+-------------
 C00001   | 2006-07-15    | Ricardo takeshi    |         Nao 
 C00002   | 2007-07-15    | Ricardo takeshi    |         Nao 
 C00001   | 2008-12-31    | João Nogeira Serra |         Sim 
 C00001   | 2010-12-31    | Eliane Silva       |         Nao 
\end{verbatim}}
\end{itemize}

%% ------------------------------------------------------------------- %%
\section{Aspectos de implementação}
\label{sec:implementacao}

%% ------------------------------------------------------------------- %%
\subsection{Formatos de entrada}
A principal entrada do sistema de gerenciamento de serviços médicos serão os
arquivos XML e as interfaces de cadastro de contratos e serviços. O formato dos
arquivos de entrada está determinado por uma especificação de DTD que
determinará a forma dos arquivos válidos.

A seguir detalhamos os arquivos necessários para a carga dos dados:
\begin{itemize}
\item Beneficiários e dependentes: Arquivo \texttt{beneficiarios.xml}.
{\small
\begin{verbatim}
<beneficiarios>
    <beneficiario>
        <cpf>...</cpf>
        <nome>...</nome>
        <rg>...</rg>
        <endereco>...</endereco>
        <telefone>...</telefone>
        <dependentes>
            <dependente>
                <codigo>...</codigo>
                <nome>...</nome>
                <data_nascimento>...</data_nascimento>
                <grau_parentesco>...</grau_parentesco>
            </dependente>    
        </dependentes>
    <beneficiario>
</beneficiarios>
\end{verbatim}}

\item Médicos e especialidades: Arquivo \texttt{medicos.xml}
{\small
\begin{verbatim}
<especialidades>
    <especialidade>
        <codigo>...</codigo>
        <nome>...</nome>
    </especialidade>
</especialidades>

<medicos>
    <medico>
        <cpf>...</cpf>
        <nome>...</nome>
        <rg>...</rg>
        <endereco>...</endereco>
        <telefone>...</telefone>
        <crm>...</crm>
        <especialidades>
            <especialidad>...</especialidad> 
        </especialidades>
    </medico>
</medicos>
\end{verbatim}}

\item Serviços e tipos de serviços médicos: Arquivo \texttt{servicos.xml}
{\small
\begin{verbatim}
<tipo_servicos>
    <tipo_servico>
        <codigo>...</codigo>
        <nome>...</nome>
    </tipo_servico>
</tipo_servicos>

<servicos>
    <servico>
        <codigo>...</codigo>
        <descricao>...</descricao>
        <valor>...</valor>
        <codigo_tipo_servico>...</codigo_tipo_Serviços>
    </servico>
</servicos>
\end{verbatim}}

\item Centros médicos e tipos de centros: Arquivo \texttt{centros\_medicos.xml}
{\small
\begin{verbatim}
<tipos_centros_medicos>
    <tipo_centro_medico>
        <codigo>...</codigo>
        <nome>...</nome>
    </tipo_centro_medico>
</tipos_centros_medicos>

<centros_medicos>
    <centro_medico>
        <cgc>...</cgc>
        <nome>...</nome>
        <endereco>...</endereco>
        <telefone>...</telefone>
        <inscricao_estadual>...</inscricao_estadual>
        <codigo_tipo_centro_medico>...</codigo_tipo_centro_medico>
    </centro_medico>
</centros_medicos>
\end{verbatim}}

\end{itemize}

Os arquivos de entrada que não mantenham as estruturas descritas serão
consideradas como entradas inválidas para o sistema. Nesse caso, o Administrador (ator)
receberá uma mensagem de erro de formato.

%% ------------------------------------------------------------------- %%
\subsection{Esboço da interface gráfica}
Nas Figuras~\ref{fig:interface01} e \ref{fig:interface02} mostramos as duas
principais interfaces para o cadastro de um novo contrato e serviços médicos,
respectivamente.

\begin{figure}[!h]
    \centering
    \includegraphics[width=.75\textwidth]{interface01.png}
    \caption{Interface de cadastro de um novo contrato.}
    \label{fig:interface01}
\end{figure}

\begin{figure}[!h]
    \centering
    \includegraphics[width=.55\textwidth]{interface02.png}
    \caption{Interface de cadastro de novos serviços.}
    \label{fig:interface02}
\end{figure}

% ------------------------------------------------------------------------- %
% ------------------------------------------------------------------------- %
\newpage 
\
\appendix
\section{Tabelas do modelo físico}
\label{ape:tabelas}
% ------------------------------------------------------------------------- %
\subsection{medico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  cpf         & numeric   &  11 & Não & Sim & Número de CPF do médico\\
  nome         & varchar     & 100 & Não & Não & Nome do médico \\
  rg         & char         &  10 & Não & Sim & Número de RG do médico \\
  endereco  & varchar   & 255 & Sim & Não & Endereço do médico\\
  telefone  & number    &  20 & Sim & Não & Número de telefone do médico\\
  crm       & char      &  10 & Não & Sim & Número de CRM do médico\\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (cpf).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{especialidade}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  codigo    & char         &   3 & Não & Sim & Código da especialidade \\
  nome         & varchar     & 100 & Não & Não & Nome da especialidade \\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (codigo).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{especialidade\_medico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  cpf\_medico           & numeric &  11 & Não & Não & Número de CPF do médico \\
  codigo\_especialidade & char    &   3 & Não & Não & Código da especialidade \\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (cpf\_medico, codigo\_especialidade).
    \item Chaves estrangeiras: 
         \begin{itemize}
            \item (cpf\_medico) REFERENCES medico (cpf).
            \item (codigo\_especialidade) REFERENCES especialidade (codigo).
        \end{itemize}
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{beneficiario}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  cpf      & numeric &  11 & Não & Sim & Número de CPF do beneficiário \\ 
  nome     & varchar & 100 & Não & Não & Nome do dependente \\ 
  rg       &    char &  10 & Não & Sim & Número de RG do médico \\
  endereco & varchar & 255 & Sim & Não & Endereço do beneficiário \\ 
  telefone &    char &  20 & Sim & Não & Número de telefone do beneficiário \\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (cpf). 
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{dependente}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  codigo           & char    &   6 & Não & Sim & Código do dependente \\
  nome             & char    &  40 & Não & Não & Nome do dependente \\
  cpf\_beneficiario& numeric &  11 & Não & Não & Número de CPF do beneficiário \\ 
  data\_nascimento & date    &   - & Sim & Não & Data de nascimento do dependente \\ 
  grau\_parentesco & varchar &  30 & Sim & Não & Grau de parentesco com o beneficiário \\ 
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (codigo). 
    \item Chave estrangeira: (cpf\_beneficiario) REFERENCES beneficiario (cpf).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{contrato}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  numero             & char    &     6  & Não & Não & Número de contrato \\
  cpf\_beneficiario  & numeric &    11  & Não & Não & Número de CPF do beneficiário \\ 
  data\_validade     & date    &     -  & Sim & Não & Data de validade do contrato \\ 
  valor\_mensalidade & varchar & (10,2) & Sim & Não & Valor de mensalidade \\ 
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (numero, cpf\_beneficiario). 
    \item Chave estrangeira: (cpf\_beneficiario) REFERENCES beneficiario (cpf).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{tipo\_servico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  codigo    & char         &   3 & Não & Sim & Código do tipo de serviço \\
  nome         & varchar     & 100 & Não & Sim & Nome do tipo de serviço \\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (codigo).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{servico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  codigo                & char    &     3  & Não & Sim & Código de seviço \\
  descricao             & varchar &   255  & Não & Não & Descrição do serviço \\ 
  valor                 & numeric & (10,2) & Sim & Não & Valor do serviço \\ 
  codigo\_tipo\_servico & char    &     3  & Não & Não & Código do tipo de serviço \\ 
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (codigo). 
    \item Chave estrangeira: (codigo\_tipo\_servico) REFERENCES tipo\_servico (codigo).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{servico\_contrato}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  numero\_contrato & char    &   6 & Não & Não & Número de contrato \\
  cpf\_beneficiario& numeric &  11 & Não & Não & Número de CPF do beneficiário \\
  codigo\_servico  & char    &   3 & Não & Não & Código do serviço \\
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (numero\_contrato, cpf\_beneficiario, codigo\_servico).
    \item Chaves estrangeiras: 
         \begin{itemize}
            \item (numero\_contrato, cpf\_beneficiario) REFERENCES contrato (numero, cpf\_beneficiario).
            \item (codigo\_servico) REFERENCES servico (codigo).
        \end{itemize}
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{tipo\_centro\_medico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  codigo    & char         &   3 & Não & Sim & Código do tipo de centro medico \\
  nome         & varchar     & 100 & Não & Sim & Nome do tipo de centro médico \\ \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (codigo).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{centro\_medico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  cgc                  & char      &   3 & Não & Sim & Número de CGC do centro medico \\
  nome                 & varchar & 100 & Não & Não & Nome do centro medico \\
  endereco             & varchar & 255 & Sim & Não & Endereço do centro medico \\
  telefone             & char      &  20 & Sim & Não & Número de telefone \\
  inscrição\_estadual  & char      &  20 & Sim & Não & Número de inscrição estadual \\
  codigo\_tipo\_centro & char      &   3 & Não & Não & Código do tipo de centro medico \\
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (cgc).
    \item Chave estrangeira: (codigo\_tipo\_centro) REFERENCES tipo\_centro\_medico (codigo).
\end{itemize}

% ------------------------------------------------------------------------- %
\subsection{execucao\_servico}
\begin{tabular}{|llrccl|} \hline
  Campo & Tipo & Tamanho & Permite NULL & Valor único & Descrição \\ \hline
  % 
  numero\_contrato    & char    &   6 & Não & Não & Número de contrato \\
  cpf\_beneficiario   & numeric &  11 & Não & Não & Número de CPF do beneficiário \\
  codigo\_servico     & char    &   3 & Não & Não & Código do serviço \\
  numero\_execucao    & char    &   5 & Não & Não & Número de execução \\
  date                & date    &   - & Sim & Não & Data de execução \\
  cgc\_centro\_medico & char    &   3 & Não & Não & Número de CGC do centro médico \\
  cpf\_medico         & numeric &  11 & Não & Não & Número de CPF do médico \\
  codigo\_dependente  & char    &   6 & Sim & Não & Código do dependente \\
  \hline
\end{tabular}

\begin{itemize}
    \item Chave primária: (numero\_contrato, cpf\_beneficiario, codigo\_servico, numero\_execucao)
    \item Chaves estrangeiras: 
         \begin{itemize}
            \item (numero\_contrato, cpf\_beneficiario, codigo\_servico) REFERENCES
            servico\_contrato (numero\_contrato, cpf\_beneficiario, codigo\_servico).
            \item (cgc\_centro\_medico) REFERENCES centro\_medico (cgc).
            \item (cpf\_medico) REFERENCES medico (cpf).
            \item (codigo\_dependente) REFERENCES dependente (codigo).
        \end{itemize}
\end{itemize}


% ------------------------------------------------------------------------- %

%% ------------------------------------------------------------------- %%
% \bibliographystyle{amsplain}
% \bibliographystyle{amsalpha}
% \bibliography{bib}

\end{document}
