public void exportAconselhamentoSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.patient_id,";
                sqlSelect += "		e.encounter_id,";
                sqlSelect += "		e.encounter_datetime,";
                sqlSelect += "		p.nid,";
                sqlSelect += "		resumo.obs";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							o.value_text as obs";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type=24 and o.concept_id=1553 and o.voided=0 and e.voided=0";
                sqlSelect += "				  ) resumo on resumo.encounter_id=e.encounter_id  ";
                sqlSelect += " WHERE	e.encounter_type=24 and  e.voided=0 and";
                sqlSelect += "			p.nid is not null and ";
                sqlSelect += "			p.datanasc is not null and ";
                sqlSelect += "			p.dataabertura is not null and ";
                sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_aconselhamento(nid) values(";
                        sqlInsert += "'" + readerSource.GetString(3) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento");

                        insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento);

                        exportActividadeAconselhamentoSeguimento(otherSource, target, readerSource.GetString(1), idAconselhamento);

                    }

                }

                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTOSEGUIMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message);

            }
        }
        private void exportCodedObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String  sqlSelect = " SELECT	p.patient_id, ";
                        sqlSelect += "		    e.encounter_id, ";
                        sqlSelect += "		    c.concept_id, ";
                        sqlSelect += "		    case c.concept_id ";
                        sqlSelect += "			    when 300 then 'TIPAGEM SANGUINEA' ";
                        sqlSelect += "			    when 1655 then 'RPR' ";
                        sqlSelect += "			    when 299 then 'VDRL' ";
                        sqlSelect += "			    when 307 then 'BACILOSCOPIA' ";
                        sqlSelect += "			    when 1030 then 'PCR' ";
                        sqlSelect += "			    when 1120 then 'Pele' ";
                        sqlSelect += "			    when 1127 then 'Aparelho Articular' ";
                        sqlSelect += "			    when 1126 then 'Genitais' ";
                        sqlSelect += "			    when 1415 then 'Mucosas' ";
                        sqlSelect += "			    when 1129 then 'Neurológico' ";
                        sqlSelect += "			    when 509 then 'Inguinais aumentadados' ";
                        sqlSelect += "			    when 1124 then 'Pulmonar - Respiracao' ";
                        sqlSelect += "			    when 1427 then 'Pulmonar - Respiracao' ";
                        sqlSelect += "			    when 1125 then 'Abdómen' ";
                        sqlSelect += "			    when 5112 then 'Axilas aumentados' ";
                        sqlSelect += "			    when 643 then 'Cervicais aumentados' ";
                        sqlSelect += "			    when 1425 then 'Estado Hidratação' ";
                        sqlSelect += "			    when 1239 then 'Local do Corpo' ";
                        sqlSelect += "			    when 1629 then 'Cardiológico - Auscultação' ";
                        sqlSelect += "			    when 1550 then 'Ap: Sopro Tubárico' ";
                        sqlSelect += "			    when 1540 then 'Parótidas aumentadas' ";
                        sqlSelect += "			    when 5945 then 'Febre' ";
                        sqlSelect += "			    when 1552 then 'Pulmonar - Auscultação' ";
                        sqlSelect += "			    when 1548 then 'ap - Fervores' ";
                        sqlSelect += "			    when 5334 then 'Candidíase da orofarínge' ";
                        sqlSelect += "			    when 1545 then 'Ap - Mv' ";
                        sqlSelect += "			    when 1551 then 'Ap: Sopro anfórico' ";
                        sqlSelect += "			    when 562 then 'Sopro Cardiaco' ";
                        sqlSelect += "			    when 1419 then 'Icterícia' ";
                        sqlSelect += "			    when 1549 then 'ap: Roncos' ";
                        sqlSelect += "			    when 1621 then 'Antibioticos' ";
                        sqlSelect += "			    when 161 then 'Linfadenopatia Generalizada' ";
                        sqlSelect += "			    when 6121 then 'Uso de Cotrimoxazol' ";
                        sqlSelect += "			    when 1533 then 'Antibioticos' ";
                        sqlSelect += "			    when 1535 then 'Vitamina A' ";
                        sqlSelect += "			    when 12 then 'Raio X: Torax' ";
                        sqlSelect += "			    when 1192 then 'Uso de Antiretroviral' ";
                        sqlSelect += "			    when 1538 then 'Uso de Anitfungal' ";
                        sqlSelect += "			    when 1088 then 'Regime' ";
                        sqlSelect += "			    when 1532 then 'LCR Patologico' ";
                        sqlSelect += "		    else cn.name end as codobservacao, ";
                        sqlSelect += "		    cnc.name as codestado, ";
                        sqlSelect += "		    p.nid,	 ";
                        sqlSelect += "		    o.obs_datetime as data	 ";
                        sqlSelect += " FROM	    t_paciente p  ";
                        sqlSelect += "		    inner join	encounter e on p.patient_id=e.patient_id ";
                        sqlSelect += "		    inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                        sqlSelect += "		    inner join	concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and  ";
                        sqlSelect += "					    cn.concept_name_type='FULLY_SPECIFIED'  ";
                        sqlSelect += "		    inner join	concept_name cnc on cnc.concept_id=o.value_coded and cnc.locale='pt' and  ";
                        sqlSelect += "					    cnc.concept_name_type='FULLY_SPECIFIED'  ";
                        sqlSelect += "		    inner join	concept c on c.concept_id=o.concept_id	 ";
                        sqlSelect += " WHERE	e.encounter_type in (1,3) and   ";
                        sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and  ";
                        sqlSelect += "		    c.datatype_id=2 and c.is_set=0  and p.datanasc is not null and ";
                        sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                        sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_observacaopaciente(codobservacao,codestado,nid,data) values(";
                        sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "','" + readerSource.GetString(5) + "',cdate('" + readerSource.GetMySqlDateTime(6) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message);

            }
        }
        public void exportDataExposicaoBusca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select p.nid,datacomecoufaltar.datacomecoufaltar,dataentregaactivista.dataentregaactivista,";
                sqlSelect += " pacientelocalizado.pacientelocalizado,pacientelocalizado.datalocalizacao,codmotivoabandono.codmotivoabandono";
                sqlSelect += "      From t_paciente p inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "      left join (	SELECT 	o.person_id,e.encounter_id,o.value_datetime as datacomecoufaltar";
                sqlSelect += "      FROM 	encounter e ";
                sqlSelect += "  inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "  WHERE e.encounter_type in (21) and o.concept_id=2004 and o.voided=0 and e.voided=0";
                sqlSelect += ") datacomecoufaltar on datacomecoufaltar.encounter_id=e.encounter_id";
                sqlSelect += "left join (	SELECT 	o.person_id,e.encounter_id,o.value_datetime as dataentregaactivista";
                sqlSelect += "FROM 	encounter e ";
                sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "WHERE 	e.encounter_type in (21) and o.concept_id=2173 and o.voided=0 and e.voided=0";
                sqlSelect += ") dataentregaactivista on dataentregaactivista.encounter_id=e.encounter_id";
                sqlSelect += "left join (	SELECT 	o.person_id,e.encounter_id,o.obs_datetime as datalocalizacao,";
                sqlSelect += "case o.value_coded";
                sqlSelect += "when 1065 then 'TRUE'";
                sqlSelect += "when 1066 then 'FALSE'";
                sqlSelect += "else '' end as pacientelocalizado";
                sqlSelect += "FROM 	encounter e 	";
                sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "WHERE 	e.encounter_type=21 and o.concept_id=2003 and o.voided=0 and e.voided=0	";
                sqlSelect += ") pacientelocalizado on pacientelocalizado.encounter_id=e.encounter_id";
                sqlSelect += "left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "case o.value_coded";
                sqlSelect += "when 2005 then 'ESQUECEU A DATA'";
                sqlSelect += "when 2006 then 'ESTA ACAMADO EM CASA'";
                sqlSelect += "when 2007 then 'DISTANCIA/DINHEIRO TRANSPORTE'";
                sqlSelect += "when 2008 then 'PROBLEMAS DE ALIMENTACAO'";
                sqlSelect += "when 2009 then 'PROBLEMAS FAMILIARES'";
                sqlSelect += "when 2010 then 'INSATISFACCAO COM SERVICO NO HDD'";
                sqlSelect += "when 2011 then 'VIAJOU'";
                sqlSelect += "when 2012 then 'DESMOTIVACAO'";
                sqlSelect += "when 2013 then 'TRATAMENTO TRADICIONAL' ";
                sqlSelect += "when 2014 then 'TRABALHO'";
                sqlSelect += "when 2015 then 'EFEITOS SECUNDARIOS ARV'";
                sqlSelect += "else '' end as codmotivoabandono";
                sqlSelect += "FROM 	encounter e ";
                sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "WHERE 	e.encounter_type=21 and o.concept_id in (2016,2017) and o.voided=0 and e.voided=0";
                sqlSelect += ") codmotivoabandono on codmotivoabandono.encounter_id=e.encounter_id";
                sqlSelect += "where e.encounter_type in (21) and  e.voided=0;";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_exposicaobusca(nid,datacomecoufaltar,dataentregaactivista,";
                        sqlInsert += "pacientelocalizado,datalocalizacao,codmotivoabandono) values( ";
                        sqlInsert += "'" + checkNull(readerSource, 0) + "',cdate('" + checkNull(readerSource, 1) + "'),'" + "',cdate('" + checkNull(readerSource, 2) + "'),'" + checkNull(readerSource, 3) + checkNull(readerSource, 4) + checkNull(readerSource, 5) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                     }
                }

                readerSource.Close();

            }
            catch (Exception e)
            {

                MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOBUSCA:"+e.Message);
            }
        }
Beispiel #4
0
        public void exportDataHistEstadoPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {

            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String  sqlSelect = " select	p.patient_id,";
		                sqlSelect+="        e.encounter_id, ";
		                sqlSelect+="        p.nid, ";
		                sqlSelect+="        case    o.value_coded ";
		                sqlSelect+="                when 1707 then 'Abandono' ";
		                sqlSelect+="                when 1706 then 'Transferido para' ";
		                sqlSelect+="                when 1366 then 'Morte' ";
		                sqlSelect+="                when 1704 then 'HIV Negativo' ";
		                sqlSelect+="                when 1709 then 'Suspender Tarv' ";
		                sqlSelect+="                else 'Outro' end as codestado, ";
		                sqlSelect+="        e.encounter_datetime as dataestado, ";
		                sqlSelect+="        destino.destinopaciente ";
                        sqlSelect+=" from	t_paciente p ";
		                sqlSelect+="        inner join encounter e on p.patient_id=e.patient_id ";
		                sqlSelect+="        inner join obs o on o.encounter_id=e.encounter_id and o.person_id=e.patient_id ";
		                sqlSelect+="        left join	( ";
                        sqlSelect+="						select	e.encounter_id, ";
                        sqlSelect+="								o.value_text as destinopaciente ";
                        sqlSelect+="						from	encounter e ";
                        sqlSelect+="								inner join obs o on e.encounter_id=o.encounter_id ";
                        sqlSelect+="						where	e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=2059 ";
                        sqlSelect+="					) destino on e.encounter_id=destino.encounter_id ";
                        sqlSelect+=" where	e.encounter_type in (18,6,9) and o.concept_id in (1708,6138) and o.voided=0 and e.voided=0 and p.nid is not null and ";
                        sqlSelect += "		p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_histestadopaciente(nid,codestado,dataestado) values(";
                        sqlInsert += "'" + readerSource.GetString(2) + "','" + readerSource.GetString(3) + "',cdate('" + readerSource.GetMySqlDateTime(4) + "'))";


                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 id = insertUtil.getMaxID(target, "t_histestadopaciente", "ID");


                        insertUtil.updateStringValue("t_histestadopaciente", "destinopaciente", commandTarge, readerSource, 5, "ID", id);

                    }
                    
                }

                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_HISTESTADOPACIENTE (MODULO EXPORTTTARV.CS): " + e.Message);

            }
        }
        private void exportNumericObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
            MySqlCommand commandSource = new MySqlCommand();
            OleDbCommand commandTarge = new OleDbCommand();
            MySqlDataReader readerSource;

            String sqlInsert;

            InsertUtils insertUtil = new InsertUtils();

            String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
            String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

            String  sqlSelect = " SELECT	p.patient_id, ";
                    sqlSelect += "		    e.encounter_id, ";
                    sqlSelect += "		    c.concept_id, ";
                    sqlSelect += "		    case c.concept_id ";
                    sqlSelect += "			    when 730 then 'CD4' ";
                    sqlSelect += "			    when 5497 then 'CD4' ";
                    sqlSelect += "              when 5085 then 'Tensão Arterial' ";
                    sqlSelect += "			    when 5086 then 'Tensão Arterial' ";
                    sqlSelect += "			    when 653 then 'AST' ";
                    sqlSelect += "			    when 654 then 'ALT' ";
                    sqlSelect += "			    when 1021 then 'LINFOCITO' ";
                    sqlSelect += "			    when 952 then 'LINFOCITO' ";
                    sqlSelect += "			    when 1022 then 'NEUTROFILO' ";
                    sqlSelect += "			    when 1330 then 'NEUTROFILO' ";
                    sqlSelect += "			    when 1024 then 'EOSINOFILO' ";
                    sqlSelect += "			    when 1332 then 'EOSINOFILO' ";
                    sqlSelect += "			    when 1025 then 'BASOFILO' ";
                    sqlSelect += "			    when 1333 then 'BASOFILO' ";
                    sqlSelect += "			    when 1023 then 'MONOCITO' ";
                    sqlSelect += "			    when 1331 then 'MONOCITO' ";
                    sqlSelect += "			    when 1017 then 'CMHC' ";
                    sqlSelect += "			    when 851 then 'VCM' ";
                    sqlSelect += "			    when 21 then 'Hemoglobina' ";
                    sqlSelect += "			    when 1018 then 'HGM' ";
                    sqlSelect += "			    when 678 then 'WBC' ";
                    sqlSelect += "			    when 679 then 'RBC' ";
                    sqlSelect += "			    when 5283 then 'INDICE DE KARNOFSKY' ";
                    sqlSelect += "			    when 5314 then 'Períneo' ";
                    sqlSelect += "			    when 1342 then 'IMC' ";
                    sqlSelect += "			    when 5088 then 'Temperatura' ";
                    sqlSelect += "			    when 5089 then 'Peso' ";
                    sqlSelect += "			    when 5090 then 'Altura' ";
                    sqlSelect += "			    when 5087 then 'Pulmonar - Auscultação' ";
                    sqlSelect += "			    when 5242 then 'Pulmonar - Respiracao' ";
                    sqlSelect += "			    when 729 then 'Plaquetas' ";
                    sqlSelect += "			    when 1015 then 'HTC' ";
                    sqlSelect += "			    when 5195 then 'Esplenomegáglia' ";
                    sqlSelect += "		    else cn.name end as codobservacao, ";
                    sqlSelect += "		    case c.concept_id ";
                    sqlSelect += "			    when 730 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 5497 then 'ABSOLUTO' ";
                    sqlSelect += "			    when 5085 then 'SUPERIOR' ";
                    sqlSelect += "			    when 5086 then 'INFERIOR' ";
                    sqlSelect += "			    when 1021 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 952 then 'ABSOLUTO' ";
                    sqlSelect += "			    when 1022 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 1330 then 'ABSOLUTO' ";
                    sqlSelect += "			    when 1024 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 1332 then 'ABSOLUTO' ";
                    sqlSelect += "			    when 1025 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 1333 then 'ABSOLUTO' ";
                    sqlSelect += "			    when 1023 then 'PERCENTUAL' ";
                    sqlSelect += "			    when 1331 then 'ABSOLUTO' ";
                    sqlSelect += "		    else null end as codestado, ";
                    sqlSelect += "		    o.value_numeric as valor,	 ";
                    sqlSelect += "		    p.nid,	 ";
                    sqlSelect += "		    o.obs_datetime as data	 ";
                    sqlSelect += " FROM	    t_paciente p ";
                    sqlSelect += "		    inner join	encounter e on p.patient_id=e.patient_id ";
                    sqlSelect += "		    inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                    sqlSelect += "		    inner join	concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and  cn.concept_name_type='FULLY_SPECIFIED'";
                    sqlSelect += "		    inner join	concept c on c.concept_id=o.concept_id      ";
                    sqlSelect += " WHERE	e.encounter_type in (1,3) and   ";
                    sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and  ";
                    sqlSelect += "		    c.datatype_id=1 and c.is_set=0 and p.datanasc is not null and ";
                    sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                    sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

            commandTarge.Connection = target;
            commandTarge.CommandType = CommandType.Text;

            commandSource.Connection = source;
            commandSource.CommandType = CommandType.Text;
            commandSource.CommandText = sqlSelect;
            readerSource = commandSource.ExecuteReader();

            if (readerSource.HasRows)
            {

                while (readerSource.Read())
                {

                    sqlInsert = "Insert into t_observacaopaciente(codobservacao,valor,nid,data) values(";
                    sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(5) + "','" + readerSource.GetString(6) + "',cdate('" + readerSource.GetMySqlDateTime(7) + "'))";
                    commandTarge.CommandText = sqlInsert;
                    commandTarge.ExecuteNonQuery();
                    if (!readerSource.IsDBNull(4))
                    {
                        Int32 idObs = insertUtil.getMaxID(target, "t_observacaopaciente", "idobservacao");
                        commandTarge.CommandText = "Update t_observacaopaciente set codestado='" + readerSource.GetString(4) + "' where idobservacao=" + idObs;
                        commandTarge.ExecuteNonQuery();
                    }

                }
            }
            readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, NUMERICO (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message);

            }
        }
        private void exportInfeccoesOportunistasSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String  sqlSelect = " SELECT	case	o.concept_id ";
                        sqlSelect += "				when 1564 then 'I' ";
                        sqlSelect += "				when 1565 then 'II' ";
                        sqlSelect += "				when 1566 then 'III' ";
                        sqlSelect += "				when 1569 then 'IV' ";
                        sqlSelect += "				when 1558 then 'I' ";
                        sqlSelect += "				when 1561 then 'II' ";
                        sqlSelect += "				when 1562 then 'III' ";
                        sqlSelect += "				when 2066 then 'IV' ";
                        sqlSelect += "				end as estadioms, ";
                        sqlSelect += "		if(length(cn.name)>50,concat(left(cn.name,45),' ...'),cn.name) as codigoio, ";
                        sqlSelect += "		p.nid, ";
                        sqlSelect += "		o.obs_datetime as data ";
                        sqlSelect += " FROM	t_paciente p ";
                        sqlSelect += "		inner join	encounter e on p.patient_id=e.patient_id ";
                        sqlSelect += "		inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                        sqlSelect += "		inner join	concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and ";
                        sqlSelect += "					cn.concept_name_type='FULLY_SPECIFIED' ";
                        sqlSelect += " WHERE	e.encounter_type in (6,9) and o.concept_id in (1564,1565,1566,1569,1558,1561,1562,2066) and ";
                        sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                   // Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_infeccoesoportunisticaseguimento(idseguimento,estadiooms,codigoio,Nid,Data) values(";
                        sqlInsert += ""+idSeguimento+",'" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "','" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_INFECCOESOPORTUNISTASSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message);

            }
        }
        private void UpdateScreenTB(MySqlConnection source, OleDbConnection target)
        {
            try
            {
            MySqlCommand commandSource = new MySqlCommand();
            OleDbCommand commandTarge = new OleDbCommand();
            MySqlDataReader readerSource;

            String sqlInsert;

            InsertUtils insertUtil = new InsertUtils();

            String sqlSelect = " SELECT	e.encounter_datetime,";
            sqlSelect += "              p.nid ";
            sqlSelect += " FROM	    t_paciente p";
            sqlSelect += "		    inner join	encounter e on p.patient_id=e.patient_id ";
            sqlSelect += " WHERE	e.encounter_type=20 and ";
            sqlSelect += "		                e.voided=0 and p.nid is not null ";

            commandTarge.Connection = target;
            commandTarge.CommandType = CommandType.Text;

            commandSource.Connection = source;
            commandSource.CommandType = CommandType.Text;
            commandSource.CommandText = sqlSelect;
            readerSource = commandSource.ExecuteReader();

            if (readerSource.HasRows)
            {

                //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                while (readerSource.Read())
                {

                    sqlInsert = "update t_seguimento set screeningtb='SIM' where nid='" + readerSource.GetString(1) + "' and dataseguimento=cdate('" + readerSource.GetString(0) + "')";

                    commandTarge.CommandText = sqlInsert;

                    commandTarge.ExecuteNonQuery();

                }
            }
            readerSource.Close();
            //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_DIAGNOSTICOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message);

            }
        }
        private void exportDataTratamentoTB(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            //try
            //{
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	distinct p.nid, ";
                sqlSelect += "		dataInicio.dataInicio ";
                sqlSelect += " FROM	t_paciente p  ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id ";
                sqlSelect += "        inner join  ";
                sqlSelect += "        (	SELECT	o.person_id,e.encounter_id,o.value_datetime as dataInicio ";
                sqlSelect += "			FROM 	encounter e ";
                sqlSelect += "					inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "			WHERE 	o.concept_id=1113  and o.voided=0 and e.voided=0 ";
                sqlSelect += "			UNION ";
                sqlSelect += "			SELECT	o.person_id,e.encounter_id,o.obs_datetime as dataInicio ";
                sqlSelect += "			FROM 	encounter e ";
                sqlSelect += "					inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "			WHERE 	o.concept_id=1268 and o.value_coded=1256 and o.voided=0 and e.voided=0 ";
                sqlSelect += "         ) dataInicio on dataInicio.encounter_id=e.encounter_id ";
                sqlSelect += " WHERE	";
                sqlSelect += "		e.voided=0 and p.nid is not null and  ";
                sqlSelect += "		p.datanasc is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'";
                sqlSelect += " GROUP BY nid,dataInicio ";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_tratamentoTB(nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "')";

                        // sqlInsert = "Insert into t_tratamentoTB(nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "ON DUPLICATE KEY UPDATE datafim='" + readerSource.GetString(2) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        //insertUtil.updateNumericValue("t_tratamentoTB", "data", commandTarge, readerSource, 1, "nid", readerSource.GetString(0));
                        //insertUtil.updateStringValue("t_tratamentoTB", "datafim", commandTarge, readerSource, 2, "nid", readerSource.GetString(0));
                    }
                }

                readerSource.Close();

                sqlSelect = "SELECT	p.nid, ";
                sqlSelect += "				datafim.datafim  ";
                sqlSelect += " FROM	t_paciente p   ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id  ";
                sqlSelect += "        inner join   ";
                sqlSelect += "		(	SELECT 	o.person_id,e.encounter_id,o.value_datetime as datafim ";
                sqlSelect += "            FROM 	encounter e  ";
                sqlSelect += "                    inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "            WHERE 	e.encounter_type in (6,9) and o.concept_id=6120 and o.voided=0 and e.voided=0 ";
                sqlSelect += "         ) datafim on datafim.encounter_id=e.encounter_id ";

                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {
                        insertUtil.updateDateValue("t_tratamentoTB", "datafim", commandTarge, readerSource, 1, "nid", readerSource.GetString(0));
                    }
                }
                readerSource.Close();
            //}
            //catch (Exception e)
            //{

            //    MessageBox.Show("Houve erro ao Exportar tabela T_TRATAMENTOTB:" + e.Message);
            //}
        }
        private void exportDataExposicaoTarvNascenca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            //try
            //{
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select distinct p.nid,tarv.tarv";
                sqlSelect += "      From t_paciente p inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "      inner join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                  case o.value_coded";
                sqlSelect += "                   when 631 then 'NVP'";
                sqlSelect += "                   when 797 then 'AZT'";
                sqlSelect += "                   when 792 then 'D4T+3TC+NVP'";
                sqlSelect += "                   when 1800 then 'TARV'";
                sqlSelect += "                   when 1801 then 'AZT+NVP'";
                sqlSelect += "                   when 630 then 'AZT+3TC'";
                sqlSelect += "                   when 628 then '3TC'";
                sqlSelect += "                   else '' end as tarv";
                sqlSelect += "      FROM 	encounter e";
                sqlSelect += "              inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "      WHERE 	e.encounter_type=7 and o.concept_id=1503 and o.voided=0 and e.voided=0";
                sqlSelect += "               ) tarv on tarv.encounter_id=e.encounter_id";
                sqlSelect += "      where e.encounter_type=7 and p.nid is not null and  e.voided=0 and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "';";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {
                        sqlInsert = "Insert into t_esposicaotarvnascenca(nid,tarv) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                     }
                }

                readerSource.Close();

            //}
            //catch (Exception e)
            //{

            //    MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOTARVNASCENCA:"+e.Message);
            //}
        }
        private void btImport_Click(object sender, EventArgs e)
        {
            //Validacoes de campos
            if (String.IsNullOrEmpty(txtHost.Text))
            {
                MessageBox.Show("The field Server Host must not be empty...");
                txtHost.Focus();
                return;
            }
            if (String.IsNullOrEmpty(txtPort.Text))
            {
                MessageBox.Show("The field Port must not be empty...");
                txtPort.Focus();
                return;
            }

            if (String.IsNullOrEmpty(txtUsername.Text))
            {
                MessageBox.Show("The field User Name must not be empty...");
                txtUsername.Focus();
                return;
            }

            if (String.IsNullOrEmpty(txtPassword.Text))
            {
                MessageBox.Show("The field Password must not be empty...");
                txtPassword.Focus();
                return;
            }

            if (String.IsNullOrEmpty(txtDataSource.Text))
            {
                MessageBox.Show("The field Data Source must not be empty...");
                txtDataSource.Focus();
                return;
            }

            lblSMS.Text = "Preparando dados iniciais...";
            lblSMS.Refresh();

            String dataInicialMySQL = dataInicial.Value.Year + "/" + dataInicial.Value.Month + "/" + dataInicial.Value.Day;
            String dataFinalMySQL = dataFinal.Value.Year + "/" + dataFinal.Value.Month + "/" + dataFinal.Value.Day;

            //Utils para conexao MySQL - OpenMRS
            OpenMRSUtils mysqlUtil = new OpenMRSUtils(txtHost.Text, txtUsername.Text, txtPassword.Text);

            //Utils para conexao MS Access - Sistema HDD
            AccessUtils accessUtil = new AccessUtils(txtDataSource.Text);

            //Tem algumas funcoes auxiliares de insercao de dados
            InsertUtils insertUtils = new InsertUtils();

            //Inserir na tabela t_hdd com base no distrito seleccionado
            insertUtils.insertHDD(accessUtil.getConexao, cboDistrito);

               // //Levar o codigo da unidade sanitaria seleccionada
            //String codHDD = insertUtils.getHDDID(accessUtil.getConexao);

               // //Levar o codigo do distrito como primeiros 4 digitos do codigo da unidade sanitaria
               // String codDistrito = codHDD.Substring(0, 4);
            //Levar o codigo do distrito como primeiros 4 digitos do codigo da unidade sanitaria
            //String codDistrito = codHDD.Substring(0, 4);

            //Zerar a tabela t_paciente garantir que e nova insercao
            insertUtils.emptyTPaciente(accessUtil.getConexao);

            barraProgresso.Value = 5;

            lblSMS.Text = "A executar a chamada do Stored Procedure no OpenMRS...";
            lblSMS.Refresh();

            //Chamar a procedure MySQL para encher a tabela t_paciente no OpenMRS
            String codUS = cboDistrito.Text.Substring(0, 6);
            insertUtils.callFillTPacienteTable(mysqlUtil.getConexao, dataFinalMySQL, codUS, codUS.Substring(0,4));

            barraProgresso.Value = 10;

            lblSMS.Text = "Enchendo a tabela t_paciente no ACCESS...";
            lblSMS.Refresh();

            //Encher a tabela de t_bairro
            insertUtils.insertBairro(accessUtil.getConexao, mysqlUtil.getConexao);

            //Encher a tabela de t_funcionario
            insertUtils.insertFuncionario(accessUtil.getConexao, mysqlUtil.getConexao);

            // //Encher a tabela t_paciente MS Access
            ExportTPaciente.ExportData(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 15;
            barraProgresso.Refresh();

            //Export t_adulto
            lblSMS.Text = "Enchendo a tabela t_adulto...";
            lblSMS.Refresh();

            ExportTAdulto eAdulto = new ExportTAdulto();
            eAdulto.exportDataAdulto(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 20;
            barraProgresso.Refresh();

            lblSMS.Text = "Enchendo a tabela t_pai, t_mae e t_crianca...";
            lblSMS.Refresh();

            ExportCrianca ec = new ExportCrianca();

            ExportTMae em = new ExportTMae();

            ExportTPai ep = new ExportTPai();

            em.exportDataTMae(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);
            ep.exportDataTPai(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);
            ec.exportDataCrianca(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 25;
            barraProgresso.Refresh();

            lblSMS.Text = "Export a tabela t_seguimento (Leva muito tempo) ...";
            lblSMS.Refresh();

            ExportTSeguimento eSeguimento = new ExportTSeguimento();

            eSeguimento.exportDataTSeguimento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection);

            barraProgresso.Value = 55;
            barraProgresso.Refresh();

            lblSMS.Text = "Export a tabela t_tarv,t_histestadopaciente ...";
            lblSMS.Refresh();

            ExportTTarv etarv = new ExportTTarv();

            etarv.exportData(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            etarv.exportDataHistEstadoPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 70;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_resultadoLaboratorio ...";
            lblSMS.Refresh();

            ExportTResultadoLab lab = new ExportTResultadoLab();
            lab.exportDataTResultadoLaboratorio(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 75;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_aconselhamento ...";
            lblSMS.Refresh();

            ExportTAconselhamento eAconselhamento = new ExportTAconselhamento();

            eAconselhamento.exportAconselhamento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection);

            eAconselhamento.exportAconselhamentoSeguimento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection);

            barraProgresso.Value = 80;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_antecedentesClinicos, t_observacaopaciente,t_contacto ...";
            lblSMS.Refresh();

            ExportAntecedentesClinicosPaciente eAntecedente = new ExportAntecedentesClinicosPaciente();
            ExportTObservacaoPaciente eObs = new ExportTObservacaoPaciente();

            ExportTContacto contacto = new ExportTContacto();

            eAntecedente.exportAntecedentesClinicosPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            eObs.exportDataTObservacaoPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            contacto.exportContacto(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 85;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_exposicaotarvmae, t_exposicaotarvnascenca ...";
            lblSMS.Refresh();

            ExportExposicaoTarv expTarv = new ExportExposicaoTarv();
            expTarv.exportDataExposicaoTARV(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 90;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_tratamentoTB, t_QuestionarioTB ...";
            lblSMS.Refresh();

            ExportTTratamentoTB eTB = new ExportTTratamentoTB();
            eTB.exportDataTB(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            barraProgresso.Value = 95;
            barraProgresso.Refresh();

            lblSMS.Text = "Export da tabela t_Busaca ...";
            lblSMS.Refresh();

            ExportBuscaActiva eBusca = new ExportBuscaActiva();
            eBusca.exportDataBuscaActiva(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date);

            if (chkTemGaac.Checked)
            {
                lblSMS.Text = "Export da tabela t_Gaac ...";
                lblSMS.Refresh();
                ExportTGaac eGaac = new ExportTGaac();
                eGaac.exportTGAAC(mysqlUtil.getConexao, accessUtil.getConexao, mysqlUtil.getOtherConnection);
            }

            barraProgresso.Value = 100;
            barraProgresso.Refresh();

               lblSMS.Text = "Exportacao Terminada ...";
               lblSMS.Refresh();
        }
        private void exportDataQuestionarioTB(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            //try
            //{
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select distinct codopcao.codopcao, ";
                sqlSelect += "              if(codopcao.codopcao is not null,TRUE,FALSE) as estadoopcao,";
                sqlSelect += "              p.nid," ;
                sqlSelect += "              e.encounter_datetime as data";
                sqlSelect += "      From t_paciente p inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "      inner join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                  case o.value_coded";
                sqlSelect += "                  when 1760 then 'Tosse há mais de 3 Semanas?'";
                sqlSelect += "                  when 1761 then 'Tosse com sangue? '";
                sqlSelect += "                  when 1762 then 'Suores á noite por mais de 3 semanas?'";
                sqlSelect += "                  when 1763 then 'Febre por mais de 3 semanas?'";
                sqlSelect += "                  when 1764 then 'Perdeu peso (mais de 3 Kg no ultimo mês)?'";
                sqlSelect += "                  when 1765 then 'Alguém na família está tratando a TB?'";
                sqlSelect += "                  else null end as codopcao";
                sqlSelect += "                FROM 	encounter e";
                sqlSelect += "                      inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "               WHERE 	e.encounter_type=20 and o.concept_id=1766 and o.voided=0 and e.voided=0	 ";
                sqlSelect += "                      ) codopcao on codopcao.encounter_id=e.encounter_id";
                sqlSelect += "       where e.encounter_type=20 and  e.voided=0 and p.nid is not null and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "              e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.datanasc is not null ";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_questionarioTB(codopcao,nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateBooleanValue("t_questionarioTB", "estadoopcao", commandTarge, readerSource, 1, "nid", readerSource.GetString(2));
                    }
                }

                readerSource.Close();

            //}
            //catch (Exception e)
            //{

            //    MessageBox.Show("Houve erro ao Exportar tabela T_QUESTIONARIOTB:" + e.Message);
            //}
        }
        private void btnDeidentify_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtDataSource.Text))
            {
                MessageBox.Show("The field Data Source must not be empty...");
                txtDataSource.Focus();
                return;
            }
            AccessUtils accessUtil = new AccessUtils(txtDataSource.Text);

            InsertUtils insertUtils = new InsertUtils();

               System.Data.DataTable   dt = insertUtils.deidenfyDataBase(accessUtil.getConexao,accessUtil.getOtherConexao);

               Excel.Application oXL;
               Excel.Workbook oWB;
               Excel.Worksheet oSheet;
               Excel.Range oRange;

               // Start Excel and get Application object.
               oXL = new Excel.ApplicationClass();

               // Set some properties
               oXL.Visible = true;
               oXL.UserControl = true;

            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

               oXL.DisplayAlerts = false;

               // Get a new workbook.

               oWB = oXL.Workbooks.Add(Missing.Value);

               //System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;

               // Get the active sheet
               oSheet = (Excel.Worksheet)oWB.ActiveSheet;
               oSheet.Name = "NIDS";

               // Process the DataTable
               // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
               //DataTable dt = Customers.RetrieveAsDataTable();

               int rowCount = 1;

               foreach (DataRow dr in dt.Rows)
               {
               rowCount += 1;
               for (int i = 1; i < dt.Columns.Count + 1; i++)
               {
                   // Add the header the first time through
                   if (rowCount == 2)
                   {
                       oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                   }
                   oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
               }
               }

               // Resize the columns
               oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                         oSheet.Cells[rowCount, dt.Columns.Count]);
               oRange.EntireColumn.AutoFit();

               // Save the sheet and close
               oSheet = null;
               oRange = null;
               oWB.SaveAs("Deidentify_"+cboDistrito.Text+".xls", Excel.XlFileFormat.xlWorkbookNormal,
               Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               Excel.XlSaveAsAccessMode.xlExclusive,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value);
               oWB.Close(Missing.Value, Missing.Value, Missing.Value);
               oWB = null;
               oXL.Quit();

               // Clean up
               // NOTE: When in release mode, this does the trick
               GC.WaitForPendingFinalizers();
               GC.Collect();
               GC.WaitForPendingFinalizers();
               GC.Collect();
               System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            MessageBox.Show("Completed");
        }
        private void exportActividadeAconselhamentoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idAconselhamento)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String sqlSelect = " SELECT	p.patient_id,";
                sqlSelect += "		    e.encounter_id,";
                sqlSelect += "		    p.nid,";
                sqlSelect += "		    e.encounter_datetime as data,";
                sqlSelect += "		    tipo.tipoactividade ";
                sqlSelect += " FROM	t_paciente p";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "		left join	(	SELECT 	o.person_id,";
                sqlSelect += "								o.encounter_id,";
                sqlSelect += "								case o.value_coded";
                sqlSelect += "									when 2044 then 'Apoio Psicologico Individual'";
                sqlSelect += "									when 2045 then 'Apoio Social e Familiar'";
                sqlSelect += "									when 2046 then 'Informacao e Educacao Sobre Prevencao'";
                sqlSelect += "								end as tipoactividade";
                sqlSelect += "						FROM 	encounter e ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						WHERE 	e.encounter_type=24 and o.concept_id=2047 and o.voided=0 and e.voided=0";
                sqlSelect += "					) tipo on tipo.encounter_id=e.encounter_id";
                sqlSelect += " WHERE	p.nid is not null and ";
                sqlSelect += "			p.datanasc is not null and ";
                sqlSelect += "			e.voided=0 and e.encounter_type=24 and ";
                sqlSelect += "			p.dataabertura is not null and ";
                sqlSelect += "		    e.encounter_id=" + encounterId;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    //Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento");

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_actividadeaconselhamento(idaconselhamento,nid,data) values(";
                        sqlInsert += "" + idAconselhamento + ",'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateStringValue("t_actividadeaconselhamento", "tipoactividade", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento);

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_ACTIVIDADEACONSELHAMENTOSEGUIMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message);

            }
        }
        private void exportActividadeAconselhamento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idAconselhamento)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String  sqlSelect = " SELECT	p.patient_id,";
                        sqlSelect += "		    e.encounter_id,";
                        sqlSelect += "		    p.nid,";
                        sqlSelect += "		    e.encounter_datetime as data,";
                        sqlSelect += "		    sessao.nrsessao,";
                        sqlSelect += "		    tipo.tipoactividade,";
                        sqlSelect += "		    confidente.apresentouconfidente";
                        sqlSelect += " FROM	t_paciente p";
                        sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id";
                        sqlSelect += "		left join	(	SELECT 	o.person_id,";
                        sqlSelect += "								o.encounter_id,";
                        sqlSelect += "								o.value_numeric as nrsessao";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type in (19,29) and o.concept_id= 1724 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) sessao on sessao.encounter_id=e.encounter_id";
                        sqlSelect += "		left join	(	SELECT 	o.person_id,";
                        sqlSelect += "								o.encounter_id,";
                        sqlSelect += "								case o.value_coded";
                        sqlSelect += "									when 1725 then 'GRUPO'";
                        sqlSelect += "									when 1726 then 'INDIVIDUAL'";
                        sqlSelect += "								end as tipoactividade";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type in (19,29) and o.concept_id= 1727 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) tipo on tipo.encounter_id=e.encounter_id";
                        sqlSelect += "		left join	(	SELECT 	o.person_id,";
                        sqlSelect += "								o.encounter_id,";
                        sqlSelect += "								case o.value_coded";
                        sqlSelect += "									when 1065 then true";
                        sqlSelect += "									when 1066 then false";
                        sqlSelect += "								end as apresentouconfidente";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type in (19,29) and o.concept_id= 1728 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) confidente on confidente.encounter_id=e.encounter_id";
                        sqlSelect += " WHERE	p.nid is not null and ";
                        sqlSelect += "			p.datanasc is not null and ";
                        sqlSelect += "			e.voided=0 and e.encounter_type in (19,29) and ";
                        sqlSelect += "			p.dataabertura is not null and ";
                        sqlSelect += "		    e.encounter_id=" + encounterId;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    //Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento");

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_actividadeaconselhamento(idaconselhamento,nid,data) values(";
                        sqlInsert += "" + idAconselhamento + ",'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateNumericValue("t_actividadeaconselhamento", "nrsessao", commandTarge, readerSource, 4, "idaconselhamento",idAconselhamento);
                        insertUtil.updateStringValue("t_actividadeaconselhamento", "tipoactividade", commandTarge, readerSource, 5, "idaconselhamento", idAconselhamento);
                        insertUtil.updateBooleanValue("t_actividadeaconselhamento", "apresentouconfidente", commandTarge, readerSource, 6, "idaconselhamento",idAconselhamento);
                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_ACTIVIDADEACONSELHAMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message);

            }
        }
        private void exportDiagnosticoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String sqlSelect = " SELECT	";
                sqlSelect += "		case cn.concept_id ";
                sqlSelect += "			when 123 then 'MALARIA' ";
                sqlSelect += "			when 68 then 'DESNUTRIÇÃO AGUDA MODERADA (DAM)' ";
                sqlSelect += "			when 3 then 'ANEMIA' ";
                sqlSelect += "		else cn.name end as diagnostico, ";
                sqlSelect += "              p.nid,";
                sqlSelect += "              o.obs_datetime as data";
                sqlSelect += " FROM	    t_paciente p";
                sqlSelect += "		    inner join	encounter e on p.patient_id=e.patient_id";
                sqlSelect += "		    inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id";
                sqlSelect += "		    inner join	concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and ";
                sqlSelect += "					    cn.concept_name_type='FULLY_SPECIFIED'";
                sqlSelect += " WHERE	e.encounter_type in (6,9) and o.concept_id=1406 and ";
                sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                    while (readerSource.Read())
                    {
                        String aux = readerSource.GetString(0);
                        if(aux.Length>50 ){
                            aux =aux.Substring(0,45);
                            aux +=" ...";
                        }
                        sqlInsert = "Insert into t_diagnosticoseguimento(idseguimento,coddiagnostico,Nid,Data) values(";
                        sqlInsert += "" + idSeguimento + ",'" + aux + "','" + readerSource.GetString(1) + "',cdate('" + readerSource.GetMySqlDateTime(2) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_DIAGNOSTICOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message);

            }
        }
        public void exportContacto(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.nid,nome.nome,apelido.apelido,telefone.telefone ";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join (SELECT	e.patient_id, ";
                sqlSelect += "				e.encounter_id,	 ";
                sqlSelect += "				o.value_text as nome ";
                sqlSelect += "		 FROM 	encounter e  ";
                sqlSelect += "				inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "				WHERE 	e.encounter_type in (5,7) and o.concept_id=1441 and ";
                sqlSelect += "						o.voided=0 and e.voided=0  ";
                sqlSelect += "		 ) nome  ";
                sqlSelect += "		 on p.patient_id=nome.patient_id ";
                sqlSelect += "		left join (SELECT	e.patient_id, ";
                sqlSelect += "				e.encounter_id,	 ";
                sqlSelect += "				o.value_text as apelido ";
                sqlSelect += "		 FROM 	encounter e  ";
                sqlSelect += "				inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "				WHERE 	e.encounter_type in (5,7) and o.concept_id=1442 and ";
                sqlSelect += "						o.voided=0 and e.voided=0  ";
                sqlSelect += "		 ) apelido  ";
                sqlSelect += "		 on p.patient_id=apelido.patient_id ";
                sqlSelect += "		left join (SELECT	e.patient_id, ";
                sqlSelect += "				e.encounter_id,	 ";
                sqlSelect += "				o.value_text as telefone ";
                sqlSelect += "		 FROM 	encounter e  ";
                sqlSelect += "				inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "				WHERE 	e.encounter_type in (5,7) and o.concept_id=1611 and ";
                sqlSelect += "						o.voided=0 and e.voided=0  ";
                sqlSelect += "		 ) telefone  ";
                sqlSelect += "		 on p.patient_id=telefone.patient_id ";
                sqlSelect += " WHERE	p.nid is not null and p.datanasc is not null  and ";
                sqlSelect += "          p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_contacto(nid,nome) values(";
                        sqlInsert += "'" + readerSource.GetString(0) + "','"+readerSource.GetString(1)+"')";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateStringValue("t_contacto", "apelido", commandTarge, readerSource, 2, "nid", readerSource.GetString(0));

                        insertUtil.updateStringValue("t_contacto", "telefone", commandTarge, readerSource, 3, "nid", readerSource.GetString(0));

                    }
                }
                readerSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_CONTACTO: " + e.Message);

            }
        }
        public void exportDataTSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate,MySqlConnection otherSource)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String  sqlSelect = " SELECT    p.patient_id,";
                        sqlSelect += "		    e.encounter_id,";
                        sqlSelect += "		    p.nid,";
                        sqlSelect += "		    YEAR(e.encounter_datetime)-YEAR(p.datanasc) as idade,";
                        sqlSelect += "		    if(YEAR(e.encounter_datetime)-YEAR(p.datanasc)<2,PERIOD_DIFF(DATE_FORMAT(e.encounter_datetime,'%Y%m'),DATE_FORMAT(p.datanasc,'%Y%m')),null) meses,";
                        sqlSelect += "          estadohiv.estadohiv,";
                        sqlSelect += "          e.encounter_datetime as dataseguimento,";
                        sqlSelect += "          estadiooms.estadiooms,";
                        sqlSelect += "          dataproximaconsulta.dataproximaconsulta,";
                        sqlSelect += "          Gravidez.Gravidez,";
                        sqlSelect += "          outrodiagnostico.diagnostico,";
                        sqlSelect += "          gravidez1.gravida ";
                        sqlSelect += " FROM 	t_paciente p ";
                        sqlSelect += "		    inner join encounter e on e.patient_id=p.patient_id";
                        sqlSelect += "		    left join	(	SELECT 	o.person_id,e.encounter_id,";
                        sqlSelect += "								case o.value_coded";
                        sqlSelect += "								when 703 then 'Positivo'";
                        sqlSelect += "								when 664 then 'Negativo'";
                        sqlSelect += "								when 1138 then 'Indeterminado'";
                        sqlSelect += "								else 'OUTRO' end as estadohiv";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id ";
                        sqlSelect += "						WHERE 	e.encounter_type=9 and o.concept_id=1040 and o.voided=0 and e.voided=0	";
                        sqlSelect += "					) estadohiv on estadohiv.encounter_id=e.encounter_id";
                        sqlSelect += "		    left join	(	SELECT  o.person_id,e.encounter_id,";
                        sqlSelect += "								case o.value_coded";
                        sqlSelect += "								when 1204 then 'I'";
                        sqlSelect += "								when 1205 then 'II'";
                        sqlSelect += "								when 1206 then 'III'";
                        sqlSelect += "								when 1207 then 'IV'";
                        sqlSelect += "								else 'OUTRO' end as estadiooms";
                        sqlSelect += "						FROM 	encounter e 			";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type in (6,9) and o.concept_id=5356 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) estadiooms on estadiooms.encounter_id=e.encounter_id";
                        sqlSelect += "		    left join	(	SELECT 	o.person_id,";
                        sqlSelect += "								e.encounter_id,";
                        sqlSelect += "								o.value_datetime as dataproximaconsulta";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type in (6,9) and o.concept_id=1410 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) dataproximaconsulta on dataproximaconsulta.encounter_id=e.encounter_id";
                        sqlSelect += "		    left join	(	SELECT 	o.person_id,";
                        sqlSelect += "								o.encounter_id,";
                        sqlSelect += "								o.value_numeric as Gravidez";
                        sqlSelect += "						FROM 	encounter e ";
                        sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                        sqlSelect += "						WHERE 	e.encounter_type=6 and o.concept_id= 5992 and o.voided=0 and e.voided=0";
                        sqlSelect += "					) Gravidez on Gravidez.encounter_id=e.encounter_id";
                        sqlSelect += "          left join	(	SELECT 	o.person_id, ";
                        sqlSelect += "				                    o.encounter_id, ";
                        sqlSelect += "				                    o.value_text as diagnostico ";
                        sqlSelect += "		                     FROM 	encounter e ";
                        sqlSelect += "				                    inner join obs o on e.encounter_id=o.encounter_id ";
                        sqlSelect += "		                     WHERE 	e.encounter_type in (6,9) and o.concept_id= 1649 and o.voided=0 and e.voided=0 ";
                        sqlSelect += "	                      ) outrodiagnostico on outrodiagnostico.encounter_id=e.encounter_id ";
                        sqlSelect += "          left join	(	SELECT 	o.person_id, ";
                        sqlSelect += "				                    o.encounter_id, ";
                        sqlSelect += "				                    'SIM' as gravida ";
                        sqlSelect += "		                     FROM 	encounter e ";
                        sqlSelect += "				                    inner join obs o on e.encounter_id=o.encounter_id ";
                        sqlSelect += "		                     WHERE 	e.encounter_type in (6,9) and o.concept_id= 1982 and o.voided=0 and e.voided=0 and o.value_coded=44";
                        sqlSelect += "	                      ) gravidez1 on gravidez1.encounter_id=e.encounter_id ";

                        sqlSelect += " WHERE	e.encounter_type in (6,9) and  ";
                        sqlSelect += "		e.voided=0 and ";
                        sqlSelect += "		p.nid is not null and ";
                        sqlSelect += "		p.dataabertura is not null and ";
                        sqlSelect += "		p.datanasc is not null and ";
                        sqlSelect += "		p.dataabertura between '"+startDateMySQL+"' and '"+endDateMySQL+"' and ";
                        sqlSelect += "		e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

               commandTarge.Connection = target;
               commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_seguimento(nid,idade,dataseguimento,tiposeguimento) values(";
                        sqlInsert += "'" + readerSource.GetString(2) + "'," + readerSource.GetString(3) + ",cdate('" + readerSource.GetMySqlDateTime(6) + "'),'Seguinte')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                        insertUtil.updateNumericValue("t_seguimento", "meses", commandTarge, readerSource, 4, "idseguimento", idSeguimento);
                        insertUtil.updateStringValue("t_seguimento", "estadohiv", commandTarge, readerSource, 5, "idseguimento", idSeguimento);
                        insertUtil.updateStringValue("t_seguimento", "estadiooms", commandTarge, readerSource, 7, "idseguimento", idSeguimento);
                        insertUtil.updateDateValue("t_seguimento", "dataproximaconsulta", commandTarge, readerSource, 8, "idseguimento", idSeguimento);
                        insertUtil.updateNumericValue("t_seguimento", "Gravidez", commandTarge, readerSource, 9, "idseguimento", idSeguimento);
                        insertUtil.updateStringValue("t_seguimento", "Observacao", commandTarge, readerSource, 10, "idseguimento", idSeguimento);
                        insertUtil.updateStringValue("t_seguimento", "gravida", commandTarge, readerSource, 11, "idseguimento", idSeguimento);

                        exportInfeccoesOportunistasSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento);
                        exportTratamentoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento);
                        exportDiagnosticoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento);
                    }

                }

                readerSource.Close();
                UpdateScreenTB(source, target);

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_SEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message);

            }
        }
        public static void ExportData(MySqlConnection source, OleDbConnection target,DateTime startDate,DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                //String sqlSelect="Select patient_id 0,hdd 1,dataabertura 2,nid 3,sexo 4,datanasc 5,idade 6,meses 7,";
                //sqlSelect+="coddistrito 8,codproveniencia 9,designacaoprov 10,designacaoprov 11,emtarv 12,datainiciotarv 13,";
                //sqlSelect +="codestado 14,destinopaciente 15,datasaidatarv 16,datadiagnostico 17,aconselhado 18,referidohdd 19,";
                //sqlSelect +="datareferidohdd 20,aceitabuscaactiva 21,dataaceitabuscaactiva 22,referidobuscaactiva 23,";
                //sqlSelect +="datareferenciabuscaactiva 24,situacaohiv 25 from t_paciente where dataabertura between "+startDate+ " and " + endDate;

                String sqlSelect = " Select patient_id,hdd,dataabertura,nid,sexo,datanasc,idade,meses,";
                sqlSelect += "      coddistrito,codproveniencia,designacaoprov,Codigoproveniencia,emtarv,datainiciotarv,";
                sqlSelect += "      codestado,destinopaciente,datasaidatarv,datadiagnostico,aconselhado,referidohdd,";
                sqlSelect += "      datareferidohdd,aceitabuscaactiva,dataaceitabuscaactiva,referidobuscaactiva,";
                sqlSelect += "      datareferenciabuscaactiva,situacaohiv,nome,identificacao,codbairro,celula,avenida, ";
                sqlSelect += "      codregime,apelido,provider_id,tipopaciente,cirurgias,transfusao,estadiooms,emtratamentotb from t_paciente ";
                sqlSelect += " where datanasc is not null and nid is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {
                        sqlInsert = "Insert into t_paciente(hdd,dataabertura,nid,sexo,datanasc,idade,coddistrito) values(";
                        sqlInsert += "'" + checkNull(readerSource, 1) + "',cdate('" + checkNull(readerSource, 2) + "'),'" + checkNull(readerSource, 3) + "','" + checkNull(readerSource, 4) + "',cdate('" + checkNull(readerSource, 5) + "')," + checkNull(readerSource, 6) + ",'" + checkNull(readerSource, 8) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateNumericValue("t_paciente", "meses", commandTarge, readerSource, 7, "nid", readerSource.GetString(3));
                        insertUtil.updateStringValue("t_paciente", "codproveniencia", commandTarge, readerSource, 9, "nid", readerSource.GetString(3));
                        insertUtil.updateStringValue("t_paciente", "designacaoprov", commandTarge, readerSource, 10, "nid", readerSource.GetString(3));
                        insertUtil.updateStringValue("t_paciente", "Codigoproveniencia", commandTarge, readerSource, 11, "nid", readerSource.GetString(3));

                        insertUtil.updateBooleanValue("t_paciente", "emtarv", commandTarge, readerSource, 12, "nid", readerSource.GetString(3));
                        insertUtil.updateDateValue("t_paciente", "datainiciotarv", commandTarge, readerSource, 13, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "codestado", commandTarge, readerSource, 14, "nid", readerSource.GetString(3));
                        insertUtil.updateStringValue("t_paciente", "destinopaciente", commandTarge, readerSource, 15, "nid", readerSource.GetString(3));

                        insertUtil.updateDateValue("t_paciente", "datasaidatarv", commandTarge, readerSource, 16, "nid", readerSource.GetString(3));
                        insertUtil.updateDateValue("t_paciente", "datadiagnostico", commandTarge, readerSource, 17, "nid", readerSource.GetString(3));

                        insertUtil.updateBooleanValue("t_paciente", "aconselhado", commandTarge, readerSource, 18, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "referidohdd", commandTarge, readerSource, 19, "nid", readerSource.GetString(3));

                        insertUtil.updateDateValue("t_paciente", "datareferidohdd", commandTarge, readerSource, 20, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "aceitabuscaactiva", commandTarge, readerSource, 21, "nid", readerSource.GetString(3));

                        insertUtil.updateDateValue("t_paciente", "dataaceitabuscaactiva", commandTarge, readerSource, 22, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "referidobuscaactiva", commandTarge, readerSource, 23, "nid", readerSource.GetString(3));

                        insertUtil.updateDateValue("t_paciente", "datareferenciabuscaactiva", commandTarge, readerSource, 24, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "situacaohiv", commandTarge, readerSource, 25, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "nome", commandTarge, readerSource, 26, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "identificacao", commandTarge, readerSource, 27, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "codbairro", commandTarge, readerSource, 28, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "celula", commandTarge, readerSource, 29, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "avenida", commandTarge, readerSource, 30, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "codregime", commandTarge, readerSource, 31, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "apelido", commandTarge, readerSource, 32, "nid", readerSource.GetString(3));

                        insertUtil.updateNumericValue("t_paciente", "codfuncionario", commandTarge, readerSource, 33, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "tipopaciente", commandTarge, readerSource, 34, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "cirurgias", commandTarge, readerSource, 35, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "transfusao", commandTarge, readerSource, 36, "nid", readerSource.GetString(3));

                        insertUtil.updateStringValue("t_paciente", "estadiooms", commandTarge, readerSource, 37, "nid", readerSource.GetString(3));

                        insertUtil.updateBooleanValue("t_paciente", "emtratamentotb", commandTarge, readerSource, 38, "nid", readerSource.GetString(3));

                    }
                }

                readerSource.Close();

            }
            catch (Exception e)
            {

                MessageBox.Show("Houve erro ao Exportar tabela T_PACIENTE:" + e.Message);
            }
        }
        private void exportTratamentoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String  sqlSelect = " SELECT ";
                        sqlSelect += "		cn.name as codtratamento, ";
                        sqlSelect += "          p.nid,";
                        sqlSelect += "          o.obs_datetime as data";
                        sqlSelect += " FROM	    t_paciente p";
                        sqlSelect += "		    inner join	encounter e on p.patient_id=e.patient_id";
                        sqlSelect += "		    inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id";
                        sqlSelect += "		    inner join	concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and ";
                        sqlSelect += "					    cn.concept_name_type='FULLY_SPECIFIED'";
                        sqlSelect += " WHERE	e.encounter_type in (6,9) and o.concept_id=1719 and ";
                        sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_tratamentoseguimento(idseguimento,codtratamento,nid,data) values(";
                        sqlInsert += "" + idSeguimento + ",'" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "',cdate('" + readerSource.GetMySqlDateTime(2) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_TRATAMENTOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message);

            }
        }
        public void exportDataCrianca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select distinct p.nid,tipoparto.tipoparto,local.local,termo.termo,pesonascimento.pesonascimento,";
                sqlSelect += "             exposicaotarvmae.exposicaotarvmae,exposicaotarvnascenca.exposicaotarvnascenca,";
                sqlSelect += "             patologianeonatal.patologianeonatal,injeccoes.injeccoes,escarificacoes.escarificacoes,";
                sqlSelect += "             extracoesdentarias.extracoesdentarias,aleitamentomaterno.aleitamentomaterno,";
                sqlSelect += "             aleitamentoexclusivo.aleitamentoexclusivo,idadedesmame.idadedesmame,pavcompleto.pavcompleto,";
                sqlSelect += "             idadecronologica.idadecronologica,bailey.bailey";
                sqlSelect += "      From t_paciente p inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "           left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1170 then 'VAGINAL'";
                sqlSelect += "                                  when 1171 then 'CESARIANA'";
                sqlSelect += "                                  else '' end as tipoparto";
                sqlSelect += "                           FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                           WHERE 	e.encounter_type=7 and o.concept_id=5630 and o.voided=0 and e.voided=0";
                sqlSelect += "                       ) tipoparto on tipoparto.encounter_id=e.encounter_id";
                sqlSelect += "            left join (	SELECT 	o.person_id,e.encounter_id,o.value_text as local";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                          WHERE 	e.encounter_type in (7) and o.concept_id=1505 and o.voided=0 and e.voided=0";
                sqlSelect += "                       ) local on local.encounter_id=e.encounter_id";
                sqlSelect += "            left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  else '' end as termo";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1500 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) termo on termo.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,o.value_numeric as pesonascimento";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                          WHERE 	e.encounter_type in (7) and o.concept_id=5916 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) pesonascimento on pesonascimento.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  when 1457 then -99";
                sqlSelect += "                                  else 0 end as exposicaotarvmae";
                sqlSelect += "                          FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1501 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) exposicaotarvmae on exposicaotarvmae.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  when 1457 then -99";
                sqlSelect += "                                  else '' end as exposicaotarvnascenca";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1502 and o.voided=0 and e.voided=0";
                sqlSelect += "                         ) exposicaotarvnascenca on exposicaotarvnascenca.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,o.value_text as patologianeonatal";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                          WHERE 	e.encounter_type in (7) and o.concept_id=1506 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) patologianeonatal on patologianeonatal.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  when 1457 then -99";
                sqlSelect += "                                  else '' end as injeccoes";
                sqlSelect += "                          FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1507 and o.voided=0 and e.voided=0";
                sqlSelect += "                       ) injeccoes on injeccoes.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  when 1457 then -99";
                sqlSelect += "                                  else '' end as escarificacoes";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1509 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) escarificacoes on escarificacoes.encounter_id=e.encounter_id";
                sqlSelect += "              left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  when 1457 then -99";
                sqlSelect += "                                  else '' end as extracoesdentarias";
                sqlSelect += "                          FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                          WHERE 	e.encounter_type=7 and o.concept_id=1508 and o.voided=0 and e.voided=0	";
                sqlSelect += "                        ) extracoesdentarias on extracoesdentarias.encounter_id=e.encounter_id";
                sqlSelect += "              left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  else '' end as aleitamentomaterno ";
                sqlSelect += "                           FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                           WHERE 	e.encounter_type=7 and o.concept_id=6061 and o.voided=0 and e.voided=0	";
                sqlSelect += "                         ) aleitamentomaterno on aleitamentomaterno.encounter_id=e.encounter_id";
                sqlSelect += "              left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 5526 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  else '' end as aleitamentoexclusivo";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                   inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                         WHERE 	e.encounter_type=7 and o.concept_id=1613 and o.voided=0 and e.voided=0";
                sqlSelect += "                       ) aleitamentoexclusivo on aleitamentoexclusivo.encounter_id=e.encounter_id";
                sqlSelect += "             left join (	SELECT 	o.person_id,e.encounter_id,o.value_numeric as idadedesmame";
                sqlSelect += "                          FROM 	encounter e";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                          WHERE 	e.encounter_type in (7) and o.concept_id=1510 and o.voided=0 and e.voided=0";
                sqlSelect += "                        ) idadedesmame on idadedesmame.encounter_id=e.encounter_id";
                sqlSelect += "              left join (	SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                                  case o.value_coded";
                sqlSelect += "                                  when 1065 then -1";
                sqlSelect += "                                  when 1066 then 0";
                sqlSelect += "                                  else '' end as pavcompleto";
                sqlSelect += "                           FROM 	encounter e ";
                sqlSelect += "                                  inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                           WHERE 	e.encounter_type=7 and o.concept_id=1511 and o.voided=0 and e.voided=0";
                sqlSelect += "                         ) pavcompleto on pavcompleto.encounter_id=e.encounter_id";
                sqlSelect += "               left join (	SELECT 	o.person_id,e.encounter_id,o.value_numeric as idadecronologica";
                sqlSelect += "                              FROM 	encounter e";
                sqlSelect += "                                      inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                              WHERE 	e.encounter_type=7 and o.concept_id=1512 and o.voided=0 and e.voided=0";
                sqlSelect += "                          ) idadecronologica on idadecronologica.encounter_id=e.encounter_id";
                sqlSelect += "               left join (	SELECT 	o.person_id,e.encounter_id,o.value_numeric as bailey";
                sqlSelect += "                              FROM 	encounter e ";
                sqlSelect += "                                      inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                              WHERE 	e.encounter_type=7 and o.concept_id=1514 and o.voided=0 and e.voided=0";
                sqlSelect += "                          ) bailey on bailey.encounter_id=e.encounter_id";

                sqlSelect += " where e.encounter_type=7 and  e.voided=0 and nid is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' group by nid;";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        //sqlInsert = "Insert into t_crianca(nid,local) values('" + readerSource.GetString(0) + "','"+ checkNull(readerSource, 2) + "')";
                        sqlInsert = "Insert into t_crianca(nid) values('" + readerSource.GetString(0) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateStringValue("t_crianca", "tipoparto", commandTarge, readerSource, 1, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "[local]", commandTarge, readerSource, 2, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "termo", commandTarge, readerSource, 3, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "pesonascimento", commandTarge, readerSource, 4, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "exposicaotarvmae", commandTarge, readerSource, 5, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "exposicaotarvnascenca", commandTarge, readerSource, 6, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "patologianeonatal", commandTarge, readerSource, 7, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "injeccoes", commandTarge, readerSource, 8, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "escarificacoes", commandTarge, readerSource, 9, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "extracoesdentarias", commandTarge, readerSource, 10, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "aleitamentomaterno", commandTarge, readerSource, 11, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "aleitamentoexclusivo", commandTarge, readerSource, 12, "nid", readerSource.GetString(0));
                        insertUtil.updateNumericValue("t_crianca", "idadedesmame", commandTarge, readerSource, 13, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "pavcompleto", commandTarge, readerSource, 14, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "idadecronologica", commandTarge, readerSource, 15, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_crianca", "bailey", commandTarge, readerSource, 16, "nid", readerSource.GetString(0));

                    }
                }

                String sqlUpdateMae = "UPDATE t_crianca,t_mae SET t_crianca.idmae=t_mae.idmae where t_crianca.nid=t_mae.nid;";
                commandTarge.CommandText = sqlUpdateMae;
                commandTarge.ExecuteNonQuery();

                String sqlUpdatePai = "UPDATE t_crianca,t_pai SET t_crianca.idpai=t_pai.idpai where t_crianca.nid=t_pai.nid;";
                commandTarge.CommandText = sqlUpdatePai;
                commandTarge.ExecuteNonQuery();

                readerSource.Close();

            }
            catch (Exception e)
            {

                MessageBox.Show("Houve erro ao Exportar tabela T_CRIANCA:" + e.Message);
            }
        }
Beispiel #21
0
        public void exportData(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {

            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select	p.patient_id,";
                sqlSelect += "e.encounter_id,";
                sqlSelect += "p.nid,";
                sqlSelect += "e.encounter_datetime as datatarv,";
                sqlSelect += "regime.codRegime,";
                sqlSelect += "case o.value_coded";
                sqlSelect += "	when 1256 then 'Inicia'";
                sqlSelect += "	when 1257 then 'Manter'";
                sqlSelect += "	when 1259 then 'Alterar'";
                sqlSelect += "	when 1369 then 'Transfer de'";
                sqlSelect += "	when 1705 then 'Reiniciar'";
                sqlSelect += "	when 1708 then 'Saida'";
                sqlSelect += "	else 'OUTRO' end as tipotarv,";
                sqlSelect += "	proxima.dataproxima,";
                sqlSelect += "	aviada.qtdComp,";
                sqlSelect += "	saldo.qtdSaldo,";
                sqlSelect += "	outro.dataoutro";
                sqlSelect += " from	t_paciente p ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "		inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "		left join	(";
                sqlSelect += "						select e.encounter_id,";
                sqlSelect += "								case o.value_coded";
                sqlSelect += "								when 792 then 'D4T+3TC+NVP'";
                sqlSelect += "								when 6110 then 'D4T20+3TC+NVP'";
                sqlSelect += "								when 1827 then 'D4T30+3TC+EFV'";
                sqlSelect += "								when 6103 then 'D4T+3TC+LPV'";
                sqlSelect += "								when 1651 then 'AZT+3TC+NVP'";
                sqlSelect += "								when 1703 then 'AZT+3TC+EFV'";
                sqlSelect += "								when 1702 then 'AZT+3TC+NFV'";
                sqlSelect += "								when 6100 then 'AZT+3TC+LPV'";
                sqlSelect += "								when 817 then 'AZT+3TC+ABC'";
                sqlSelect += "								when 6104 then 'ABC+3TC+EFV'";
                sqlSelect += "								when 6106 then 'ABC+3TC+LPV/r'";
                sqlSelect += "								when 6105 then 'ABC+3TC+NVP'";
                sqlSelect += "								when 6243 then 'TDF+3TC+NVP'";
                sqlSelect += "								when 6244 then 'AZT+3TC+RTV'";
                sqlSelect += "								when 1700 then 'AZT+DDl+NFV'";
                sqlSelect += "								when 633 then 'EFV'";
                sqlSelect += "								when 625 then 'D4T'";
                sqlSelect += "								when 631 then 'NVP'";
                sqlSelect += "								when 628 then '3TC'";
                sqlSelect += "								when 6107 then 'TDF+AZT+3TC+LPV/r'";
                sqlSelect += "								when 6236 then 'D4T+DDI+RTV-IP'";
                sqlSelect += "								when 1701 then 'ABC+DDI+NFV'";
                sqlSelect += "								else 'OUTROS' end as codRegime";
                sqlSelect += "						from	encounter e inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and o.concept_id=1088 and e.encounter_type=18";
                sqlSelect += "					) regime on regime.encounter_id=e.encounter_id";
                sqlSelect += "		left join	(";
                sqlSelect += "						select	e.encounter_id,";
                sqlSelect += "								o.value_datetime as dataproxima";
                sqlSelect += "						from	encounter e ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=5096";
                sqlSelect += "					) proxima on e.encounter_id=proxima.encounter_id";
                sqlSelect += "		left join	(";
                sqlSelect += "						select	e.encounter_id,";
                sqlSelect += "								o.value_numeric as qtdComp";
                sqlSelect += "						from	encounter e ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1715";
                sqlSelect += "					) aviada on e.encounter_id=aviada.encounter_id";
                sqlSelect += "		left join	(";
                sqlSelect += "						select	e.encounter_id,";
                sqlSelect += "								o.value_numeric as qtdSaldo";
                sqlSelect += "						from	encounter e ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1713";
                sqlSelect += "					) saldo on e.encounter_id=saldo.encounter_id";
                sqlSelect += "		left join	(";
                sqlSelect += "						select	e.encounter_id,";
                sqlSelect += "								o.value_datetime as dataoutro";
                sqlSelect += "						from	encounter e ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1190";
                sqlSelect += "					) outro on e.encounter_id=outro.encounter_id";
                sqlSelect += " where e.voided=0 and e.encounter_type=18 and o.voided=0 and o.concept_id=1255 and o.value_coded<>1708 and e.encounter_datetime between  '" + startDateMySQL + "' and '" + endDateMySQL + "'";
                sqlSelect += " and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.nid is not null and regime.codregime is not null";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_tarv(nid,datatarv,tipotarv,codregime) values(";
                        sqlInsert += "'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'),'" + readerSource.GetString(5) + "','" + readerSource.GetString(4) + "')";


                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 idTarv = insertUtil.getMaxID(target, "t_tarv", "idtarv");

                        //insertUtil.updateStringValue("t_tarv", "codregime", commandTarge, readerSource, 4, "encounter_id", readerSource.GetString(1));

                        insertUtil.updateDateValue("t_tarv", "dataproxima", commandTarge, readerSource, 6, "idtarv", idTarv);

                        insertUtil.updateNumericValue("t_tarv", "QtdComp", commandTarge, readerSource, 7, "idtarv", idTarv);

                        insertUtil.updateNumericValue("t_tarv", "QtdSaldo", commandTarge, readerSource, 8, "idtarv", idTarv);

                        insertUtil.updateDateValue("t_tarv", "dataoutroservico", commandTarge, readerSource, 9, "idtarv", idTarv);
                    }
                    commandTarge.CommandText = "update t_tarv set dias=QtdComp/2 where QtdComp is not null";
                    commandTarge.ExecuteNonQuery();
                }
                
                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_TARV: " + e.Message);

            }
        }
        private void exportNumericLabResult(MySqlConnection source, OleDbConnection target, DateTime startDate,DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.patient_id, ";
                sqlSelect += "		e.encounter_id, ";
                sqlSelect += "		c.concept_id, ";
                sqlSelect += "		case c.concept_id ";
                sqlSelect += "			when 730 then 'CD4' ";
                sqlSelect += "			when 5497 then 'CD4' ";
                sqlSelect += "			when 653 then 'AST' ";
                sqlSelect += "			when 654 then 'ALT' ";
                sqlSelect += "			when 1021 then 'L' ";
                sqlSelect += "			when 952 then 'L' ";
                sqlSelect += "			when 1022 then 'N' ";
                sqlSelect += "			when 1330 then 'N' ";
                sqlSelect += "			when 1024 then 'E' ";
                sqlSelect += "			when 1332 then 'E' ";
                sqlSelect += "			when 1025 then 'B' ";
                sqlSelect += "			when 1333 then 'B' ";
                sqlSelect += "			when 1023 then 'M' ";
                sqlSelect += "			when 1331 then 'M' ";
                sqlSelect += "			when 1017 then 'CMHC' ";
                sqlSelect += "			when 851 then 'VGM' ";
                sqlSelect += "			when 21 then 'Hemoglobina' ";
                sqlSelect += "			when 1018 then 'HGM' ";
                sqlSelect += "			when 678 then 'WBC' ";
                sqlSelect += "			when 679 then 'RBC' ";
                sqlSelect += "			when 1015 then 'HTC' ";
                sqlSelect += "			when 729 then 'Plaquetas' ";
                sqlSelect += "			when 1016 then 'RDW' ";
                sqlSelect += "			when 1307 then 'MPV' ";
                sqlSelect += "			when 1011 then 'CK' ";
                sqlSelect += "			when 857 then 'Ureia' ";
                sqlSelect += "			when 790 then 'Creatinina' ";
                sqlSelect += "			when 848 then 'Albumina' ";
                sqlSelect += "			when 655 then 'Bilirrubina T' ";
                sqlSelect += "			when 887 then 'GLC' ";
                sqlSelect += "			when 1297 then 'Bilirrubina C' ";
                sqlSelect += "			when 1299 then 'Amilase' ";
                sqlSelect += "			when 855 then 'VS' ";
                sqlSelect += "		else cn.name end as codexame, ";
                sqlSelect += "		p.nid,	";
                sqlSelect += "		o.obs_datetime as dataresultado, ";
                sqlSelect += "		case c.concept_id ";
                sqlSelect += "			when 730 then 'PERCENTUAL' ";
                sqlSelect += "			when 5497 then 'ABSOLUTO' ";
                sqlSelect += "			when 1021 then 'PERCENTUAL' ";
                sqlSelect += "			when 952 then 'ABSOLUTO' ";
                sqlSelect += "			when 1022 then 'PERCENTUAL' ";
                sqlSelect += "			when 1330 then 'ABSOLUTO' ";
                sqlSelect += "			when 1024 then 'PERCENTUAL' ";
                sqlSelect += "			when 1332 then 'ABSOLUTO' ";
                sqlSelect += "			when 1025 then 'PERCENTUAL' ";
                sqlSelect += "			when 1333 then 'ABSOLUTO' ";
                sqlSelect += "			when 1023 then 'PERCENTUAL' ";
                sqlSelect += "			when 1331 then 'ABSOLUTO' ";
                sqlSelect += "		else null end as codparametro, ";
                sqlSelect += "		o.value_numeric as resultado, ";
                sqlSelect += "		pedido.data_pedido ";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join	encounter e on p.patient_id=e.patient_id ";
                sqlSelect += "		inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                sqlSelect += "		inner join	concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and ";
                sqlSelect += "					cn.concept_name_type='FULLY_SPECIFIED' ";
                sqlSelect += "		inner join	concept c on c.concept_id=o.concept_id   ";
                sqlSelect += "      left join	( ";
                sqlSelect += "						select	e.encounter_id, ";
                sqlSelect += "								o.value_datetime as data_pedido ";
                sqlSelect += "						from	encounter e  ";
                sqlSelect += "								inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "						where	e.voided=0 and o.voided=0 and e.encounter_type=13 and o.concept_id=6246 ";
                sqlSelect += "					) pedido on e.encounter_id=pedido.encounter_id ";
                sqlSelect += " WHERE	e.encounter_type=13 and  ";
                sqlSelect += "		    o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and p.datanasc is not null and ";
                sqlSelect += "		    c.datatype_id=1 and c.is_set=0 and	";
                sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_resultadoslaboratorio(codexame,nid,dataresultado,resultado) values(";
                        sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "',cdate('" + readerSource.GetString(5) + "'),'" + readerSource.GetDouble(7) + "')";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();
                        Int32 idLab = insertUtil.getMaxID(target, "t_resultadoslaboratorio", "idresultado");
                        if (!readerSource.IsDBNull(6))
                        {

                            commandTarge.CommandText = "Update t_resultadoslaboratorio set codparametro='" + readerSource.GetString(6) + "' where idresultado=" + idLab;
                            commandTarge.ExecuteNonQuery();
                        }
                        if (!readerSource.IsDBNull(8))
                        {

                            commandTarge.CommandText = "Update t_resultadoslaboratorio set datapedido=cdate('" + readerSource.GetString(8) + "') where idresultado=" + idLab;
                            commandTarge.ExecuteNonQuery();
                        }

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_RESULTADOLABORATORIO, NUMERICO (MODULO EXPORTTRESULTADOLAB.CS): " + e.Message);

            }
        }
        public void exportDataBuscaActiva(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            //try
            //{
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

            
                String sqlSelect = " SELECT	distinct p.nid, ";
                sqlSelect += "		datacomecoufaltar.datacomecoufaltar, ";
                sqlSelect += "		dataentregaactivista.dataentregaactivista, ";
                sqlSelect += "		pacientelocalizado.pacientelocalizado, ";
                sqlSelect += "		pacientelocalizado.datalocalizacao, ";
                sqlSelect += "		codmotivoabandono.codmotivoabandono, ";
                sqlSelect += "		codreferencia.codreferencia, ";
                sqlSelect += "		entregueconvite.entregueconvite, ";
                sqlSelect += "		confidenteidentificado.confidenteidentificado, ";
                sqlSelect += "		codinformacaodadapor.codinformacaodadapor, ";
                sqlSelect += "		codservicorefere.codservicorefere, ";
                sqlSelect += "		e.encounter_datetime, ";
                sqlSelect += "		observacao.observacao ";
                sqlSelect += " FROM	t_paciente p  ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id ";
                sqlSelect += "		inner join ";
                sqlSelect += "		(   SELECT 	o.person_id,e.encounter_id,o.value_datetime as datacomecoufaltar ";
				sqlSelect += "	        FROM 	encounter e ";
                sqlSelect += "			        inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "	        WHERE 	e.encounter_type=21 and o.concept_id=2004 and o.voided=0 and e.voided=0 ";
                sqlSelect += "      ) datacomecoufaltar on e.encounter_id=datacomecoufaltar.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id,e.encounter_id,o.value_datetime as dataentregaactivista ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2173 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) dataentregaactivista on dataentregaactivista.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							if(o.value_coded=1065,o.obs_datetime,null) as datalocalizacao, ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 1065 then 'SIM' ";
                sqlSelect += "								when 1066 then 'NAO' ";
                sqlSelect += "							else null end as pacientelocalizado ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2003 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) pacientelocalizado on pacientelocalizado.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 2005 then 'ESQUECEU A DATA' ";
                sqlSelect += "								when 2006 then 'ESTA ACAMADO EM CASA' ";
                sqlSelect += "								when 2007 then 'DISTANCIA/DINHEIRO TRANSPORTE' ";
                sqlSelect += "								when 2008 then 'PROBLEMAS DE ALIMENTACAO' ";
                sqlSelect += "								when 2009 then 'PROBLEMAS FAMILIARES' ";
                sqlSelect += "								when 2010 then 'INSATISFACCAO COM SERVICO NO HDD' ";
                sqlSelect += "								when 2011 then 'VIAJOU' ";
                sqlSelect += "								when 2012 then 'DESMOTIVACAO' ";
                sqlSelect += "								when 2013 then 'TRATAMENTO TRADICIONAL' ";
                sqlSelect += "								when 2014 then 'TRABALHO' ";
                sqlSelect += "								when 2015 then 'EFEITOS SECUNDARIOS ARV' ";
                sqlSelect += "								when 2017 then 'OUTRO' ";
                sqlSelect += "							else o.value_coded end as codmotivoabandono ";
                sqlSelect += "					FROM 	encounter e 	 ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2016 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) codmotivoabandono on codmotivoabandono.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 1797 then 'Encaminhado para a US' ";
                sqlSelect += "								when 1977 then 'Encaminhado para os grupos de apoio' ";
                sqlSelect += "								when 5488 then 'Orientado sobre a toma correcta dos ARV' ";
                sqlSelect += "								when 2159 then 'Familiar foi referido para a US' ";
                sqlSelect += "							else 'OUTRO' end as codreferencia ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=1272 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) codreferencia on codreferencia.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							if(o.value_datetime is not null,'SIM','NAO') as entregueconvite ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2179 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) entregueconvite on entregueconvite.encounter_id=e.encounter_id   ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id,	 ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 1065 then 'SIM' ";
                sqlSelect += "								when 1066 then 'NAO' ";
                sqlSelect += "							else null end as confidenteidentificado ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=1739 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) confidenteidentificado on confidenteidentificado.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 2034 then 'Vizinho' ";
                sqlSelect += "								when 2033 then 'Confidente' ";
                sqlSelect += "								when 2035 then 'Familiar' ";
                sqlSelect += "								when 2036 then 'Secretário do Bairro' ";
                sqlSelect += "							else 'OUTRO' end as codinformacaodadapor ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2037 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) codinformacaodadapor on codinformacaodadapor.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							case o.value_coded ";
                sqlSelect += "								when 2175 then 'TARV Adulto' ";
                sqlSelect += "								when 2174 then 'TARV Pediatrico' ";
                sqlSelect += "								when 1414 then 'PNCT' ";
                sqlSelect += "								when 1598 then 'PTV' ";
                sqlSelect += "							else 'OUTRO' end as codservicorefere ";
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2176 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) codservicorefere on codservicorefere.encounter_id=e.encounter_id ";
                sqlSelect += "		left join (	SELECT 	o.person_id, ";
                sqlSelect += "							e.encounter_id, ";
                sqlSelect += "							o.value_text as observacao ";                
                sqlSelect += "					FROM 	encounter e  ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id  ";
                sqlSelect += "					WHERE 	e.encounter_type=21 and o.concept_id=2041 and o.voided=0 and e.voided=0 ";
                sqlSelect += "				  ) observacao on observacao.encounter_id=e.encounter_id ";
                sqlSelect += " WHERE    e.encounter_type=21 and  e.voided=0 and p.nid is not null and p.datanasc is not null and ";
                sqlSelect += "          p.dataabertura between '"+startDateMySQL+"' and '"+endDateMySQL+"' and e.encounter_datetime between '"+startDateMySQL+"' and '"+endDateMySQL+"'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        
                        sqlInsert = "Insert into t_buscaactiva(nid,datacomecoufaltar) values( ";
                        sqlInsert += "'" + readerSource.GetString(0) + "',cdate('" + readerSource.GetDateTime(1) + "'))";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        //data primeira tentativa
                        if (!readerSource.IsDBNull(11))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set dataprimeiratentativa=cdate('" + readerSource.GetDateTime(11) + "') where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }


                        //Data Entrega
                        if (!readerSource.IsDBNull(2))
                        {
                            commandTarge.CommandText="Update t_buscaactiva set dataentregaactivista=cdate('"+readerSource.GetDateTime(2)+"') where nid='"+readerSource.GetString(0)+"' and datacomecoufaltar=cdate('"+readerSource.GetDateTime(1)+"')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //localizado
                        if (!readerSource.IsDBNull(3))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set pacientelocalizado='" + readerSource.GetString(3) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //Data Localizado
                        if (!readerSource.IsDBNull(4))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set datalocalizacao=cdate('" + readerSource.GetDateTime(4) + "') where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //Cod Motivo Abadono
                        if (!readerSource.IsDBNull(5))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set codmotivoabandono='" + readerSource.GetString(5) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //Cod Referencia
                        if (!readerSource.IsDBNull(6))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set codreferencia='" + readerSource.GetString(6) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //Entregue o convite
                        if (!readerSource.IsDBNull(7))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set entregueconvite='" + readerSource.GetString(7) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //confidente identificado
                        if (!readerSource.IsDBNull(8))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set confidenteidentificado='" + readerSource.GetString(8) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        //Cod informacao dada por
                        if (!readerSource.IsDBNull(9))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set confidenteidentificado='" + readerSource.GetString(9) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }
                        //Cod servico que refere
                        if (!readerSource.IsDBNull(10))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set Codservicoquerefere='" + readerSource.GetString(10) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }

                        

                        if (!readerSource.IsDBNull(12))
                        {
                            commandTarge.CommandText = "Update t_buscaactiva set Observacao='" + readerSource.GetString(12) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')";
                            commandTarge.ExecuteNonQuery();
                        }
                        

                    }
                }

                readerSource.Close();

            //}
            //catch (Exception e)
            //{

            //    MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOBUSCA:" + e.Message);
            //}
        }
        private void exportCodedLabResult(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.patient_id, ";
                sqlSelect += "		        e.encounter_id, ";
                sqlSelect += "		        c.concept_id, ";
                sqlSelect += "		        case c.concept_id ";
                sqlSelect += "			        when 300 then 'TIPAGEM SANGUINEA' ";
                sqlSelect += "			        when 1655 then 'RPR' ";
                sqlSelect += "			        when 299 then 'VDRL' ";
                sqlSelect += "			        when 307 then 'BACILOSCOPIA' ";
                sqlSelect += "			        when 1030 then 'PCR' ";
                sqlSelect += "		        else cn.name end as codexame, ";
                sqlSelect += "		        p.nid,	";
                sqlSelect += "		        o.obs_datetime as dataresultado, ";
                sqlSelect += "		        case o.value_coded ";
                sqlSelect += "			        when 1229 then 'NAO REACTIVO' ";
                sqlSelect += "			        when 1228 then 'REACTIVO' ";
                sqlSelect += "			        when 1304 then 'MA QUALIDADE DE AMOSTRA' ";
                sqlSelect += "			        when 664 then 'NEGATIVO' ";
                sqlSelect += "			        when 703 then 'POSITIVO' ";
                sqlSelect += "			        when 1138 then 'INDETERMINADO' ";
                sqlSelect += "			        when 690 then 'A POSITIVO' ";
                sqlSelect += "			        when 692 then 'A NEGATIVO' ";
                sqlSelect += "			        when 694 then 'B POSITIVO' ";
                sqlSelect += "			        when 696 then 'B NEGATIVO' ";
                sqlSelect += "			        when 699 then 'O POSITIVO' ";
                sqlSelect += "			        when 701 then 'O NEGATIVO' ";
                sqlSelect += "			        when 1230 then 'AB POSITIVO' ";
                sqlSelect += "			        when 1231 then 'AB NEGATIVO' ";
                sqlSelect += "		        else 'OUTRO' end as codparametro ";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join	encounter e on p.patient_id=e.patient_id ";
                sqlSelect += "		inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                sqlSelect += "		inner join	concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and ";
                sqlSelect += "					cn.concept_name_type='FULLY_SPECIFIED' ";
                sqlSelect += "		inner join	concept c on c.concept_id=o.concept_id  ";
                sqlSelect += " WHERE	e.encounter_type=13 and  ";
                sqlSelect += "		o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and p.datanasc is not null and ";
                sqlSelect += "		c.datatype_id=2 and c.is_set=0	and ";
                sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_resultadoslaboratorio(codexame,nid,dataresultado,codparametro) values(";
                        sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "',cdate('" + readerSource.GetString(5) + "'),'" + readerSource.GetString(6) + "')";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_RESULTADOLABORATORIO, CODED (MODULO EXPORTTRESULTADOLAB.CS): " + e.Message);

            }
        }
        private void exportObsDataPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.nid,e.encounter_datetime,e.provider_id ";
                sqlSelect += "       FROM	t_paciente p ";
                sqlSelect += "		        inner join	encounter e on p.patient_id=e.patient_id ";
                sqlSelect += "       WHERE	e.encounter_type in (1,3) and e.voided=0 and ";
                sqlSelect += "		        p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		        e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_observacaodata(nid,data,medico) values(";
                        sqlInsert += "'" + readerSource.GetString(0) + "',cdate('" + readerSource.GetString(1) + "')," + readerSource.GetInt32(2) + ")";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message);

            }
        }
Beispiel #26
0
        public void exportTGAAC(MySqlConnection source, OleDbConnection target, MySqlConnection otherSource)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String sqlSelect = " SELECT l.name as hdd, ";
                sqlSelect += "          g.gaac_identifier as numGaac, ";
                sqlSelect += "		    g.start_date as datainicio, ";
                sqlSelect += "		    at.name as afinidade, ";
                sqlSelect += "          g.date_crumbled as dataDesintegracao, ";
                sqlSelect += "		    p.nid as nidPontoFocal, ";
                sqlSelect += "          g.description as observacao, ";
                sqlSelect += "          g.gaac_id ";
                sqlSelect += " FROM 	gaac g  ";
                sqlSelect += "		    left join t_paciente p on g.focal_patient_id=p.patient_id ";
                sqlSelect += "		    left join gaac_affinity_type at on g.affinity_type=at.gaac_affinity_type_id";
                sqlSelect += "		    left join location l on g.location_id=l.location_id ";
                sqlSelect += " WHERE	g.voided=0  ";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        Int32 numGaac = Convert.ToInt32(readerSource.GetString(1));
                        sqlInsert = "Insert into t_gaac(hdd,numGAAC,datainicio,afinidade) values(";
                        sqlInsert += "'" + readerSource.GetString(0) + "'," + numGaac + ",cdate('" + readerSource.GetMySqlDateTime(2) + "'),'" + readerSource.GetString(3) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        insertUtil.updateDateValue("t_gaac", "dataDesintegracao", commandTarge, readerSource, 4, "numGAAC", numGaac);
                        insertUtil.updateDateValue("t_gaac", "nidPontoFocal", commandTarge, readerSource, 5, "numGAAC", numGaac);
                        insertUtil.updateDateValue("t_gaac", "observacao", commandTarge, readerSource, 6, "numGAAC", numGaac);

                        this.exportTActividade(otherSource,target,readerSource.GetInt32(7));

                    }

                }

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_GAAC (MODULO EXPORTTGAAC.CS): " + e.Message);

            }
        }
        private void exportTextObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.patient_id, ";
                sqlSelect += "		e.encounter_id, ";
                sqlSelect += "		c.concept_id, ";
                sqlSelect += "		    case c.concept_id ";
                sqlSelect += "			    when 1671 then 'Historia Actual' ";
                sqlSelect += "			    when 1474 then 'Terapeutica Outro' ";
                sqlSelect += "			    when 1649 then 'Outros Diagnosticos' ";
                sqlSelect += "			    when 1424 then 'Tipo Lesao de Cavidade Orofaringea' ";
                sqlSelect += "			    when 1543 then 'Exame Pulmonar Auscultacao, Outro' ";
                sqlSelect += "			    when 1423 then 'Tipo de Lesao da Pele' ";
                sqlSelect += "			    when 1396 then 'Outro Exame Abdomen' ";
                sqlSelect += "			    when 1407 then 'Outro Exame Neurologico' ";
                sqlSelect += "			    when 1678 then 'Outos exames Genitais' ";
                sqlSelect += "			    when 1542 then 'Exame Oral' ";
                sqlSelect += "			    when 1556 then 'Membros Inferiores' ";
                sqlSelect += "			    when 1555 then 'Membros Superiores' ";
                sqlSelect += "			    when 1554 then 'Perineo' ";
                sqlSelect += "			    when 1541 then 'Outros Tratamentos' ";
                sqlSelect += "			    when 1553 then 'Outras Massas' ";
                sqlSelect += "			    when 1536 then 'Uso de Cotrimoxazol, Especificamente' ";
                sqlSelect += "			    when 1642 then 'RX: Torax, Outro' ";
                sqlSelect += "		    else cn.name end as codobservacao, ";
                sqlSelect += "		o.value_text as valor, ";
                sqlSelect += "		p.nid,	 ";
                sqlSelect += "		o.obs_datetime as dataresultado ";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join	encounter e on p.patient_id=e.patient_id ";
                sqlSelect += "		inner join	obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id ";
                sqlSelect += "		inner join	concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and  ";
                sqlSelect += "					cn.concept_name_type='FULLY_SPECIFIED' ";
                sqlSelect += "		inner join	concept c on c.concept_id=o.concept_id	 ";
                sqlSelect += " WHERE	e.encounter_type in (1,3) and  ";
                sqlSelect += "		o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and  ";
                sqlSelect += "		c.datatype_id=3 and c.is_set=0 and p.datanasc is not null and ";
                sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_observacaopaciente(codobservacao,Observacao,nid,data) values(";
                        sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "','" + readerSource.GetString(5) + "',cdate('" + readerSource.GetMySqlDateTime(6) + "'))";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                    }
                }
                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message);

            }
        }
Beispiel #28
0
        private void exportTActividade(MySqlConnection source, OleDbConnection target, Int32 idGaac)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String sqlSelect = " SELECT	p.nid,gm.start_date,gm.end_date,rl.name as reason,g.gaac_identifier, ";
                sqlSelect += "		gm.description ";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join	gaac_member gm on p.patient_id=gm.member_id ";
                sqlSelect += "		inner join	gaac g on g.gaac_id=gm.gaac_id ";
                sqlSelect += "		left join	gaac_reason_leaving_type rl on rl.gaac_reason_leaving_type_id=gm.reason_leaving_type ";
                sqlSelect += " WHERE	g.voided=0 and gm.voided=0 and gm.gaac_id=" + idGaac;

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {

                    // Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento");

                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_gaac_actividades(nid,dataInscricao,numGAAC) values(";
                        sqlInsert += "'" + readerSource.GetString(0) + "','" + readerSource.GetMySqlDateTime(1) + "'," + Convert.ToInt32( readerSource.GetString(4)) + ")";
                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 maxActividade = insertUtil.getMaxID(target, "t_gaac_actividades", "ID");
                        insertUtil.updateStringValue("t_gaac_actividades", "motivo", commandTarge, readerSource, 3, "ID", maxActividade);
                        insertUtil.updateStringValue("t_gaac_actividades", "observacao", commandTarge, readerSource, 5, "ID", maxActividade);
                        insertUtil.updateDateValue("t_gaac_actividades", "dataSaida", commandTarge, readerSource, 2, "ID", maxActividade);

                    }
                }
                readerSource.Close();
                //otherSource.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_GAAC_ACTIVIDADES (Modulo ExportTGaac.cs " + e.Message);

            }
        }
Beispiel #29
0
        public void exportDataTMae(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = "Select p.nid as nid,nome.nome,idade.idade,vivo.vivo,doente.doente,doenca.doenca,codprofissao.codprofissao,";
                sqlSelect += "             resultadohiv.resultadohiv,emtarv.emtarv";
                sqlSelect += "      From t_paciente p inner join encounter e on e.patient_id=p.patient_id" ;
                sqlSelect += "      inner join ( SELECT o.person_id,e.encounter_id,o.value_text as nome";
                sqlSelect += "                  FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                  WHERE  e.encounter_type in (7) and o.concept_id=1477 and o.voided=0 and e.voided=0";
                sqlSelect += "                 ) nome on nome.encounter_id=e.encounter_id";
                sqlSelect += "     left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as idade";
                sqlSelect += "                 FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                 WHERE  e.encounter_type in (7) and o.concept_id=1478 and o.voided=0 and e.voided=0";
                sqlSelect += "                ) idade on idade.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                             case o.value_coded";
                sqlSelect += "                             when 1065 then 'SIM'";
                sqlSelect += "                             when 1066 then 'NAO'";
                sqlSelect += "                             when 1457 then 'Sem Informação'";
                sqlSelect += "                             else '' end as vivo";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                   WHERE  e.encounter_type=7 and o.concept_id=1479 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) vivo on vivo.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                              case o.value_coded";
                sqlSelect += "                              when 1065 then 'SIM'";
                sqlSelect += "                              when 1066 then 'NAO'";
                sqlSelect += "                              when 1457 then 'Sem Informação'";
                sqlSelect += "                              else '' end as doente";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                   WHERE  e.encounter_type=7 and o.concept_id=1480 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) doente on doente.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT o.person_id,e.encounter_id,o.value_text as doenca";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                   WHERE  e.encounter_type in (7) and o.concept_id=1481 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) doenca on doenca.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT o.person_id,e.encounter_id,o.value_text as codprofissao";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id";
                sqlSelect += "                   WHERE  e.encounter_type in (7) and o.concept_id=1482 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) codprofissao on codprofissao.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                              case o.value_coded";
                sqlSelect += "                              when 703 then 'POSITIVO'";
                sqlSelect += "                              when 664 then 'NEGATIVO'";
                sqlSelect += "                              when 1138 then 'INDETERMINADO'";
                sqlSelect += "                              when 1118 then 'NAO FEZ'";
                sqlSelect += "                              when 1457 then 'Sem Informação'";
                sqlSelect += "                              else '' end as resultadohiv";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "                   WHERE  e.encounter_type=7 and o.concept_id=1483 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) resultadohiv on resultadohiv.encounter_id=e.encounter_id";
                sqlSelect += "      left join ( SELECT 	o.person_id,e.encounter_id,";
                sqlSelect += "                              case o.value_coded";
                sqlSelect += "                              when 1065 then 'SIM'";
                sqlSelect += "                              when 1066 then 'NAO'";
                sqlSelect += "                              when 1457 then 'Sem Informação'";
                sqlSelect += "                              else '' end as emtarv";
                sqlSelect += "                   FROM 	encounter e";
                sqlSelect += "                          inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "                   WHERE  e.encounter_type=7 and o.concept_id=1484 and o.voided=0 and e.voided=0";
                sqlSelect += "                  ) emtarv on emtarv.encounter_id=e.encounter_id";
                sqlSelect += " where e.encounter_type=7 and p.nid is not null and  e.voided=0 and nome.nome is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_mae(nid) values('" + readerSource.GetString(0) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();
                        insertUtil.updateStringValue("t_mae", "nome", commandTarge, readerSource, 1, "nid", readerSource.GetString(0));
                        insertUtil.updateNumericValue("t_mae", "idade", commandTarge, readerSource, 2, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "vivo", commandTarge, readerSource, 3, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "doente", commandTarge, readerSource, 4, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "doenca", commandTarge, readerSource, 5, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "codprofissao", commandTarge, readerSource, 6, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "resultadohiv", commandTarge, readerSource, 7, "nid", readerSource.GetString(0));
                        insertUtil.updateStringValue("t_mae", "emtarv", commandTarge, readerSource, 8, "nid", readerSource.GetString(0));
                    }
                }

                readerSource.Close();

            }
            catch (Exception e)
            {

                MessageBox.Show("Houve erro ao Exportar tabela T_MAE:" + e.Message);
            }
        }
        public void exportAconselhamento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource)
        {
            try
            {
                MySqlCommand commandSource = new MySqlCommand();
                OleDbCommand commandTarge = new OleDbCommand();
                MySqlDataReader readerSource;

                String sqlInsert;

                InsertUtils insertUtil = new InsertUtils();

                String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
                String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day;

                String sqlSelect = " SELECT	p.patient_id,";
                sqlSelect += "		e.encounter_id,";
                sqlSelect += "		p.nid,";
                sqlSelect += "		criteriosmedicos.criteriosmedicos,";
                sqlSelect += "		conceitos.conceitos,";
                sqlSelect += "		interessado.interessado,";
                sqlSelect += "		confidente.confidente,";
                sqlSelect += "		apareceregularmente.apareceregularmente,";
                sqlSelect += "		riscopobreaderencia.riscopobreaderencia,";
                sqlSelect += "		regimetratamento.regimetratamento,";
                sqlSelect += "		prontotarv.prontotarv,";
                sqlSelect += "		prontotarv.datapronto,";
                sqlSelect += "		obs.obs";
                sqlSelect += " FROM	t_paciente p ";
                sqlSelect += "		inner join encounter e on e.patient_id=p.patient_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then 'SIM'";
                sqlSelect += "								when 1066 then 'NAO'";
                sqlSelect += "							end as criteriosmedicos";
                sqlSelect += "					FROM 	encounter e 	";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1248 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) criteriosmedicos on criteriosmedicos.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as conceitos";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1729 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) conceitos on conceitos.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as interessado";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1736 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) interessado on interessado.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as confidente";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1739 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) confidente on confidente.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as apareceregularmente";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1743 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) apareceregularmente on apareceregularmente.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as riscopobreaderencia";
                sqlSelect += "					FROM 	encounter e 	";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1749 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) riscopobreaderencia on riscopobreaderencia.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as regimetratamento";
                sqlSelect += "					FROM 	encounter e 	";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1752 and o.voided=0 and e.voided=0	";
                sqlSelect += "				  ) regimetratamento on regimetratamento.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then true";
                sqlSelect += "								when 1066 then false";
                sqlSelect += "							end as prontotarv,";
                sqlSelect += "							case o.value_coded";
                sqlSelect += "								when 1065 then o.obs_datetime";
                sqlSelect += "							else null end as datapronto";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1756 and o.voided=0 and e.voided=0";
                sqlSelect += "				  ) prontotarv on prontotarv.encounter_id=e.encounter_id";
                sqlSelect += "		left join (	SELECT 	o.person_id,";
                sqlSelect += "							e.encounter_id,";
                sqlSelect += "							o.value_text as obs";
                sqlSelect += "					FROM 	encounter e ";
                sqlSelect += "							inner join obs o on e.encounter_id=o.encounter_id ";
                sqlSelect += "					WHERE 	e.encounter_type in (19,29) and o.concept_id=1757 and o.voided=0 and e.voided=0";
                sqlSelect += "				  ) obs on obs.encounter_id=e.encounter_id  ";
                sqlSelect += " WHERE	e.encounter_type in (19,29) and  e.voided=0 and";
                sqlSelect += "			p.nid is not null and ";
                sqlSelect += "			p.datanasc is not null and ";
                sqlSelect += "			p.dataabertura is not null and ";
                sqlSelect += "		    p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and ";
                sqlSelect += "		    e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";

                commandTarge.Connection = target;
                commandTarge.CommandType = CommandType.Text;

                commandSource.Connection = source;
                commandSource.CommandType = CommandType.Text;
                commandSource.CommandText = sqlSelect;
                readerSource = commandSource.ExecuteReader();

                if (readerSource.HasRows)
                {
                    while (readerSource.Read())
                    {

                        sqlInsert = "Insert into t_aconselhamento(nid) values(";
                        sqlInsert += "'" + readerSource.GetString(2) + "')";

                        commandTarge.CommandText = sqlInsert;
                        commandTarge.ExecuteNonQuery();

                        Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento");

                        insertUtil.updateStringValue("t_aconselhamento", "criteriosmedicos", commandTarge, readerSource, 3, "idaconselhamento", idAconselhamento);

                        insertUtil.updateBooleanValue("t_aconselhamento", "conceitos", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento);
                        insertUtil.updateBooleanValue("t_aconselhamento", "interessado", commandTarge, readerSource, 5, "idaconselhamento", idAconselhamento);
                        insertUtil.updateBooleanValue("t_aconselhamento", "confidente", commandTarge, readerSource, 6, "idaconselhamento", idAconselhamento);

                        insertUtil.updateBooleanValue("t_aconselhamento", "apareceregularmente", commandTarge, readerSource, 7, "idaconselhamento", idAconselhamento);
                        insertUtil.updateBooleanValue("t_aconselhamento", "riscopobreaderencia", commandTarge, readerSource, 8, "idaconselhamento", idAconselhamento);

                        insertUtil.updateBooleanValue("t_aconselhamento", "regimetratamento", commandTarge, readerSource, 9, "idaconselhamento", idAconselhamento);
                        insertUtil.updateBooleanValue("t_aconselhamento", "prontotarv", commandTarge, readerSource, 10, "idaconselhamento", idAconselhamento);

                        insertUtil.updateDateValue("t_aconselhamento", "datapronto", commandTarge, readerSource, 11, "idaconselhamento", idAconselhamento);

                        insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 12, "idaconselhamento", idAconselhamento);

                        exportActividadeAconselhamento(otherSource, target, readerSource.GetString(1), idAconselhamento);

                    }

                }

                readerSource.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message);

            }
        }