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 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 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);
}
}
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);
}
}
private void exportDataAdultoA(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 += " codprofissao.codprofissao, ";
sqlSelect += " codnivel.codnivel, ";
sqlSelect += " nrconviventes.nrconviventes, ";
sqlSelect += " codestadocivil.codestadocivil, ";
sqlSelect += " nrconjuges.nrconjuges, ";
sqlSelect += " serologiaHivconjuge.serologiaHivconjuge, ";
sqlSelect += " Nrprocesso.Nrprocesso, ";
sqlSelect += " outrosparceiros.outrosparceiros, ";
sqlSelect += " nrfilhos.nrfilhos, ";
sqlSelect += " nrfilhostestados.nrfilhostestados, ";
sqlSelect += " nrfilhoshiv.nrfilhoshiv, ";
sqlSelect += " tabaco.tabaco, ";
sqlSelect += " alcool.alcool, ";
sqlSelect += " droga.droga, ";
sqlSelect += " nrparceiros.nrparceiros, ";
sqlSelect += " antecedentesgenelogicos.antecedentesgenelogicos, ";
sqlSelect += " datamestruacao.datamestruacao, ";
sqlSelect += " aborto.aborto, ";
sqlSelect += " ptv.ptv, ";
sqlSelect += " gravida.gravida, ";
sqlSelect += " semanagravidez.semanagravidez, ";
sqlSelect += " dataprevistoparto.dataprevistoparto, ";
sqlSelect += " dataparto.dataparto, ";
sqlSelect += " tipoaleitamento.tipoaleitamento, ";
sqlSelect += " Alergiamedicamentos.Alergiamedicamentos, ";
sqlSelect += " Alergiasquais.Alergiasquais, ";
sqlSelect += " Antecedentestarv.Antecedentestarv, ";
sqlSelect += " antecedentesquais.antecedentesquais, ";
sqlSelect += " exposicaoacidental.exposicaoacidental ";
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,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=5 and o.concept_id=1459 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 1445 then 'NENHUM'";
sqlSelect +=" when 1446 then 'PRIMARIO'";
sqlSelect +=" when 1447 then 'SECUNDARIO BASICO'";
sqlSelect +=" when 6124 then 'TECNICO BASICO'";
sqlSelect +=" when 1444 then 'SECUNDARIO MEDIO'";
sqlSelect +=" when 6125 then 'TECNICO MEDIO'";
sqlSelect +=" when 1448 then 'UNIVERSITARIO'";
sqlSelect +=" else 'OUTRO' end as codnivel";
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=5 and o.concept_id=1443 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) codnivel on codnivel.encounter_id=e.encounter_id ";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as nrconviventes ";
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=5 and o.concept_id=1656 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) nrconviventes on nrconviventes.encounter_id=e.encounter_id ";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1057 then 'S'";
sqlSelect +=" when 5555 then 'C'";
sqlSelect +=" when 1059 then 'V'";
sqlSelect +=" when 1060 then 'U'";
sqlSelect +=" when 1056 then 'SEPARADO'";
sqlSelect +=" when 1058 then 'DIVORCIADO'";
sqlSelect +=" else 'OUTRO' end as codestadocivil";
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=5 and o.concept_id=1054 and o.voided=0 and e.voided=0";
sqlSelect +=" ) codestadocivil on codestadocivil.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as nrconjuges";
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=5 and o.concept_id=5557 and o.voided=0 and e.voided=0";
sqlSelect +=" ) nrconjuges on nrconjuges.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1169 then 'POSETIVO'";
sqlSelect +=" when 1066 then 'NEGATIVO'";
sqlSelect +=" when 1457 then 'SEM INFORMACAO'";
sqlSelect +=" else '' end as serologiaHivconjuge";
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=5 and o.concept_id=1449 and o.voided=0 and e.voided=0";
sqlSelect +=" ) serologiaHivconjuge on serologiaHivconjuge.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_text as outrosparceiros";
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=5 and o.concept_id=1451 and o.voided=0 and e.voided=0";
sqlSelect +=" ) outrosparceiros on outrosparceiros.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as nrfilhos";
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=5 and o.concept_id=5573 and o.voided=0 and e.voided=0";
sqlSelect +=" ) nrfilhos on nrfilhos.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as nrfilhostestados";
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=5 and o.concept_id=1452 and o.voided=0 and e.voided=0";
sqlSelect +=" ) nrfilhostestados on nrfilhostestados.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as nrfilhoshiv";
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=5 and o.concept_id=1453 and o.voided=0 and e.voided=0";
sqlSelect +=" ) nrfilhoshiv on nrfilhoshiv.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1065 then true";
sqlSelect +=" when 1066 then false";
sqlSelect +=" else false end as tabaco";
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=5 and o.concept_id=1388 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) tabaco on tabaco.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1065 then true";
sqlSelect +=" when 1066 then false";
sqlSelect +=" else false end as alcool";
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=5 and o.concept_id=1603 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) alcool on alcool.encounter_id=e.encounter_id ";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1065 then true";
sqlSelect +=" when 1066 then false";
sqlSelect +=" else false end as droga";
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=5 and o.concept_id=105 and o.voided=0 and e.voided=0";
sqlSelect +=" ) droga on droga.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1662 then 1";
sqlSelect +=" when 1663 then 2";
sqlSelect +=" when 1664 then 3";
sqlSelect +=" else 4 end as nrparceiros";
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=5 and o.concept_id=1666 and o.voided=0 and e.voided=0";
sqlSelect +=" ) nrparceiros on nrparceiros.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_text as antecedentesgenelogicos";
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=5 and o.concept_id=1394 and o.voided=0 and e.voided=0";
sqlSelect +=" ) antecedentesgenelogicos on antecedentesgenelogicos.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as datamestruacao";
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=5 and o.concept_id=1465 and o.voided=0 and e.voided=0";
sqlSelect +=" ) datamestruacao on datamestruacao.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id, ";
sqlSelect +=" case o.value_coded ";
sqlSelect +=" when 50 then true ";
sqlSelect +=" when 1066 then false ";
sqlSelect +=" else false end as aborto ";
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=5 and o.concept_id=1667 and o.voided=0 and e.voided=0";
sqlSelect +=" ) aborto on aborto.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1065 then true";
sqlSelect +=" when 1066 then false";
sqlSelect +=" else false end as ptv";
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=5 and o.concept_id=1466 and o.voided=0 and e.voided=0";
sqlSelect +=" ) ptv on ptv.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 44 then true ";
sqlSelect +=" when 1066 then false ";
sqlSelect +=" else false end as gravida";
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=5 and o.concept_id=1982 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) gravida on gravida.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as semanagravidez";
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=5 and o.concept_id=1279 and o.voided=0 and e.voided=0";
sqlSelect +=" ) semanagravidez on semanagravidez.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as dataprevistoparto";
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=5 and o.concept_id=1600 and o.voided=0 and e.voided=0";
sqlSelect +=" ) dataprevistoparto on dataprevistoparto.encounter_id=e.encounter_id ";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as dataparto";
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=5 and o.concept_id=5599 and o.voided=0 and e.voided=0";
sqlSelect +=" ) dataparto on dataparto.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 5526 then 'MATERNO'";
sqlSelect +=" when 5254 then 'ARTIFICIAL'";
sqlSelect +=" when 6046 then 'MISTO'";
sqlSelect +=" else 'OUTRO' end as tipoaleitamento";
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=5 and o.concept_id=1151 and o.voided=0 and e.voided=0";
sqlSelect +=" ) tipoaleitamento on tipoaleitamento.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 1067 then 'NAO SABE' ";
sqlSelect +=" else 'OUTRO' end as Alergiamedicamentos ";
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=5 and o.concept_id=1601 and o.voided=0 and e.voided=0 ";
sqlSelect +=" ) Alergiamedicamentos on Alergiamedicamentos.encounter_id=e.encounter_id";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as Alergiasquais ";
sqlSelect +=" FROM encounter e ";
sqlSelect +=" inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect +=" WHERE e.encounter_type=5 and o.concept_id=1517 and o.voided=0 and e.voided=0";
sqlSelect +=" ) Alergiasquais on Alergiasquais.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1065 then true ";
sqlSelect +=" when 1066 then false ";
sqlSelect +=" else false end as Antecedentestarv";
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=5 and o.concept_id=1192 and o.voided=0 and e.voided=0";
sqlSelect +=" ) Antecedentestarv on Antecedentestarv.encounter_id=e.encounter_id";
sqlSelect +=" left join ( SELECT o.person_id,o.encounter_id,cn.name as antecedentesquais";
sqlSelect +=" FROM obs o inner join concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and cn.concept_name_type='FULLY_SPECIFIED'";
sqlSelect +=" inner join encounter e on e.encounter_id=o.encounter_id and o.voided=0 and cn.voided=0 and e.voided=0";
sqlSelect +=" WHERE e.encounter_type=5 and o.concept_id=1087 and o.voided=0 and cn.voided=0 and e.voided=0 ";
sqlSelect +=" ) antecedentesquais on antecedentesquais.encounter_id=e.encounter_id ";
sqlSelect +=" left join ( SELECT o.person_id,e.encounter_id,";
sqlSelect +=" case o.value_coded";
sqlSelect +=" when 1443 then -1";
sqlSelect +=" when 1066 then 0";
sqlSelect += " when 1457 then -99";
sqlSelect +=" else '' end as exposicaoacidental";
sqlSelect +=" FROM encounter e ";
sqlSelect +=" inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect +=" WHERE e.encounter_type=5 and o.concept_id=1687 and o.voided=0 and e.voided=0";
sqlSelect +=" ) exposicaoacidental on exposicaoacidental.encounter_id=e.encounter_id";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as Nrprocesso ";
sqlSelect += " FROM encounter e ";
sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect += " WHERE e.encounter_type=5 and o.concept_id=1450 and o.voided=0 and e.voided=0";
sqlSelect += " ) Nrprocesso on Nrprocesso.encounter_id=e.encounter_id ";
sqlSelect += " where e.encounter_type=5 and e.voided=0 and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'";
sqlSelect += " and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.nid is not null and p.datanasc is not null ";
commandTarge.Connection = target;
commandTarge.CommandType = CommandType.Text;
commandSource.Connection = source;
commandSource.CommandType = CommandType.Text;
commandSource.CommandText = sqlSelect;
//MessageBox.Show(sqlSelect);
readerSource = commandSource.ExecuteReader();
if (readerSource.HasRows)
{
while (readerSource.Read())
{
String nid = readerSource.GetString(0);
sqlInsert = "Insert into t_adulto(nid) values('" + nid + "')";
commandTarge.CommandText = sqlInsert;
commandTarge.ExecuteNonQuery();
insertUtil.updateStringValue("t_adulto", "codprofissao", commandTarge, readerSource, 1, "nid", nid);
insertUtil.updateStringValue("t_adulto", "codnivel", commandTarge, readerSource, 2, "nid", nid);
insertUtil.updateStringValue("t_adulto", "nrconviventes", commandTarge, readerSource, 3, "nid", nid);
insertUtil.updateStringValue("t_adulto", "codestadocivil", commandTarge, readerSource, 4, "nid", nid);
insertUtil.updateStringValue("t_adulto", "nrconjuges", commandTarge, readerSource, 5, "nid", nid);
insertUtil.updateStringValue("t_adulto", "serologiaHivconjuge", commandTarge, readerSource, 6, "nid", nid);
insertUtil.updateStringValue("t_adulto", "nrprocesso", commandTarge, readerSource, 7, "nid", nid);
insertUtil.updateStringValue("t_adulto", "outrosparceiros", commandTarge, readerSource, 8, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "nrfilhos", commandTarge, readerSource, 9, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "nrfilhostestados", commandTarge, readerSource, 10, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "nrfilhoshiv", commandTarge, readerSource, 11, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "tabaco", commandTarge, readerSource, 12, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "alcool", commandTarge, readerSource, 13, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "droga", commandTarge, readerSource, 14, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "nrparceiros", commandTarge, readerSource, 15, "nid", nid);
insertUtil.updateStringValue("t_adulto", "antecedentesgenelogicos", commandTarge, readerSource, 16, "nid", nid);
insertUtil.updateDateValue("t_adulto", "datamestruacao", commandTarge, readerSource, 17, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "aborto", commandTarge, readerSource, 18, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "ptv", commandTarge, readerSource, 19, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "gravida", commandTarge, readerSource, 20, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "semanagravidez", commandTarge, readerSource, 21, "nid", nid);
insertUtil.updateDateValue("t_adulto", "dataprevistoparto", commandTarge, readerSource, 22, "nid", nid);
insertUtil.updateStringValue("t_adulto", "dataparto", commandTarge, readerSource, 23, "nid", nid);
insertUtil.updateStringValue("t_adulto", "tipoaleitamento", commandTarge, readerSource, 24, "nid", nid);
insertUtil.updateStringValue("t_adulto", "Alergiamedicamentos", commandTarge, readerSource, 25, "nid", nid);
insertUtil.updateStringValue("t_adulto", "Alergiasquais", commandTarge, readerSource, 26, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "Antecedentestarv", commandTarge, readerSource, 27, "nid", nid);
insertUtil.updateStringValue("t_adulto", "antecedentesquais", commandTarge, readerSource, 28, "nid", nid);
insertUtil.updateStringValue("t_adulto", "exposicaoacidental", commandTarge, readerSource, 29, "nid", nid);
}
commandTarge.CommandText = "update t_adulto set gravida=true where semanagravidez is not null";
commandTarge.ExecuteNonQuery();
commandTarge.CommandText = "Update t_adulto set puerpera=true where dataparto is not null";
commandTarge.ExecuteNonQuery();
}
readerSource.Close();
//}
//catch (Exception e)
//{
// MessageBox.Show("Houve erro ao Exportar T_ADULTO A: " + e.Message);
//}
}
private void exportDataAdultoB(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, ";
sqlSelect += " historiaactual.historiaactual, ";
sqlSelect += " hipotesedediagnostico.hipotesedediagnostico, ";
sqlSelect += " codkarnosfsky.codkarnosfsky, ";
sqlSelect += " geleira.geleira, ";
sqlSelect += " electricidade.electricidade, ";
sqlSelect += " sexualidade.sexualidade ";
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,o.value_text as historiaactual ";
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=1 and o.concept_id=1671 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) historiaactual on historiaactual.encounter_id=e.encounter_id ";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as hipotesedediagnostico ";
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=1 and o.concept_id=1649 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) hipotesedediagnostico on hipotesedediagnostico.encounter_id=e.encounter_id ";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as codkarnosfsky ";
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=1 and o.concept_id=5283 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) codkarnosfsky on codkarnosfsky.encounter_id=e.encounter_id ";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id, ";
sqlSelect += " case o.value_coded ";
sqlSelect += " when 1065 then true ";
sqlSelect += " when 1066 then false ";
sqlSelect += " else false end as geleira ";
sqlSelect += " FROM encounter e ";
sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect += " WHERE e.encounter_type=5 and o.concept_id=1455 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) geleira on geleira.encounter_id=e.encounter_id ";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id, ";
sqlSelect += " case o.value_coded ";
sqlSelect += " when 1065 then true ";
sqlSelect += " when 1066 then false ";
sqlSelect += " else false end as electricidade ";
sqlSelect += " FROM encounter e ";
sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect += " WHERE e.encounter_type=5 and o.concept_id=5609 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) electricidade on electricidade.encounter_id=e.encounter_id ";
sqlSelect += " left join ( SELECT o.person_id,e.encounter_id, ";
sqlSelect += " case o.value_coded ";
sqlSelect += " when 1376 then 'HETEROSSEXUAL' ";
sqlSelect += " when 1377 then 'HOMOSSEXUAL' ";
sqlSelect += " when 1378 then 'BISSEXUAL' ";
sqlSelect += " else 'OUTRO' end as sexualidade ";
sqlSelect += " FROM encounter e ";
sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id ";
sqlSelect += " WHERE e.encounter_type=5 and o.concept_id=1375 and o.voided=0 and e.voided=0 ";
sqlSelect += " ) sexualidade on sexualidade.encounter_id=e.encounter_id ";
sqlSelect += " WHERE e.encounter_type in (1,5) 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_adulto(nid) values(";
//sqlInsert += "'" + readerSource.GetString(1) + "','" + readerSource.GetString(2) + "','" + readerSource.GetString(3) + "',cdate('" + readerSource.GetMySqlDateTime(4) + "'))";
//commandTarge.CommandText = sqlInsert;
//commandTarge.ExecuteNonQuery();
String nid = readerSource.GetString(0);
insertUtil.updateStringValue("t_adulto", "historiaactual", commandTarge, readerSource, 1, "nid", nid);
insertUtil.updateStringValue("t_adulto", "hipotesedediagnostico", commandTarge, readerSource, 2, "nid", nid);
insertUtil.updateNumericValue("t_adulto", "codkarnosfsky", commandTarge, readerSource, 3, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "geleira", commandTarge, readerSource, 4, "nid", nid);
insertUtil.updateBooleanValue("t_adulto", "electricidade", commandTarge, readerSource, 5, "nid", nid);
insertUtil.updateStringValue("t_adulto", "sexualidade", commandTarge, readerSource, 6, "nid", nid);
}
}
readerSource.Close();
//}
//catch (Exception e)
//{
// MessageBox.Show("Houve erro ao Exportar T_ADULTO B: " + e.Message);
//}
}
