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);
}
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
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 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);
}
}
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);
}
}
