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