public void exportAconselhamentoSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " e.encounter_datetime,"; sqlSelect += " p.nid,"; sqlSelect += " resumo.obs"; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_text as obs"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=24 and o.concept_id=1553 and o.voided=0 and e.voided=0"; sqlSelect += " ) resumo on resumo.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type=24 and e.voided=0 and"; sqlSelect += " p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_aconselhamento(nid) values("; sqlInsert += "'" + readerSource.GetString(3) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento); exportActividadeAconselhamentoSeguimento(otherSource, target, readerSource.GetString(1), idAconselhamento); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTOSEGUIMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }
private void exportCodedObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 300 then 'TIPAGEM SANGUINEA' "; sqlSelect += " when 1655 then 'RPR' "; sqlSelect += " when 299 then 'VDRL' "; sqlSelect += " when 307 then 'BACILOSCOPIA' "; sqlSelect += " when 1030 then 'PCR' "; sqlSelect += " when 1120 then 'Pele' "; sqlSelect += " when 1127 then 'Aparelho Articular' "; sqlSelect += " when 1126 then 'Genitais' "; sqlSelect += " when 1415 then 'Mucosas' "; sqlSelect += " when 1129 then 'Neurológico' "; sqlSelect += " when 509 then 'Inguinais aumentadados' "; sqlSelect += " when 1124 then 'Pulmonar - Respiracao' "; sqlSelect += " when 1427 then 'Pulmonar - Respiracao' "; sqlSelect += " when 1125 then 'Abdómen' "; sqlSelect += " when 5112 then 'Axilas aumentados' "; sqlSelect += " when 643 then 'Cervicais aumentados' "; sqlSelect += " when 1425 then 'Estado Hidratação' "; sqlSelect += " when 1239 then 'Local do Corpo' "; sqlSelect += " when 1629 then 'Cardiológico - Auscultação' "; sqlSelect += " when 1550 then 'Ap: Sopro Tubárico' "; sqlSelect += " when 1540 then 'Parótidas aumentadas' "; sqlSelect += " when 5945 then 'Febre' "; sqlSelect += " when 1552 then 'Pulmonar - Auscultação' "; sqlSelect += " when 1548 then 'ap - Fervores' "; sqlSelect += " when 5334 then 'Candidíase da orofarínge' "; sqlSelect += " when 1545 then 'Ap - Mv' "; sqlSelect += " when 1551 then 'Ap: Sopro anfórico' "; sqlSelect += " when 562 then 'Sopro Cardiaco' "; sqlSelect += " when 1419 then 'Icterícia' "; sqlSelect += " when 1549 then 'ap: Roncos' "; sqlSelect += " when 1621 then 'Antibioticos' "; sqlSelect += " when 161 then 'Linfadenopatia Generalizada' "; sqlSelect += " when 6121 then 'Uso de Cotrimoxazol' "; sqlSelect += " when 1533 then 'Antibioticos' "; sqlSelect += " when 1535 then 'Vitamina A' "; sqlSelect += " when 12 then 'Raio X: Torax' "; sqlSelect += " when 1192 then 'Uso de Antiretroviral' "; sqlSelect += " when 1538 then 'Uso de Anitfungal' "; sqlSelect += " when 1088 then 'Regime' "; sqlSelect += " when 1532 then 'LCR Patologico' "; sqlSelect += " else cn.name end as codobservacao, "; sqlSelect += " cnc.name as codestado, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as data "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept_name cnc on cnc.concept_id=o.value_coded and cnc.locale='pt' and "; sqlSelect += " cnc.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " WHERE e.encounter_type in (1,3) and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " c.datatype_id=2 and c.is_set=0 and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_observacaopaciente(codobservacao,codestado,nid,data) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "','" + readerSource.GetString(5) + "',cdate('" + readerSource.GetMySqlDateTime(6) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message); } }
public void exportDataExposicaoBusca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select p.nid,datacomecoufaltar.datacomecoufaltar,dataentregaactivista.dataentregaactivista,"; sqlSelect += " pacientelocalizado.pacientelocalizado,pacientelocalizado.datalocalizacao,codmotivoabandono.codmotivoabandono"; sqlSelect += " From t_paciente p inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as datacomecoufaltar"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (21) and o.concept_id=2004 and o.voided=0 and e.voided=0"; sqlSelect += ") datacomecoufaltar on datacomecoufaltar.encounter_id=e.encounter_id"; sqlSelect += "left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as dataentregaactivista"; sqlSelect += "FROM encounter e "; sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += "WHERE e.encounter_type in (21) and o.concept_id=2173 and o.voided=0 and e.voided=0"; sqlSelect += ") dataentregaactivista on dataentregaactivista.encounter_id=e.encounter_id"; sqlSelect += "left join ( SELECT o.person_id,e.encounter_id,o.obs_datetime as datalocalizacao,"; sqlSelect += "case o.value_coded"; sqlSelect += "when 1065 then 'TRUE'"; sqlSelect += "when 1066 then 'FALSE'"; sqlSelect += "else '' end as pacientelocalizado"; sqlSelect += "FROM encounter e "; sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += "WHERE e.encounter_type=21 and o.concept_id=2003 and o.voided=0 and e.voided=0 "; sqlSelect += ") pacientelocalizado on pacientelocalizado.encounter_id=e.encounter_id"; sqlSelect += "left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += "case o.value_coded"; sqlSelect += "when 2005 then 'ESQUECEU A DATA'"; sqlSelect += "when 2006 then 'ESTA ACAMADO EM CASA'"; sqlSelect += "when 2007 then 'DISTANCIA/DINHEIRO TRANSPORTE'"; sqlSelect += "when 2008 then 'PROBLEMAS DE ALIMENTACAO'"; sqlSelect += "when 2009 then 'PROBLEMAS FAMILIARES'"; sqlSelect += "when 2010 then 'INSATISFACCAO COM SERVICO NO HDD'"; sqlSelect += "when 2011 then 'VIAJOU'"; sqlSelect += "when 2012 then 'DESMOTIVACAO'"; sqlSelect += "when 2013 then 'TRATAMENTO TRADICIONAL' "; sqlSelect += "when 2014 then 'TRABALHO'"; sqlSelect += "when 2015 then 'EFEITOS SECUNDARIOS ARV'"; sqlSelect += "else '' end as codmotivoabandono"; sqlSelect += "FROM encounter e "; sqlSelect += "inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += "WHERE e.encounter_type=21 and o.concept_id in (2016,2017) and o.voided=0 and e.voided=0"; sqlSelect += ") codmotivoabandono on codmotivoabandono.encounter_id=e.encounter_id"; sqlSelect += "where e.encounter_type in (21) and e.voided=0;"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_exposicaobusca(nid,datacomecoufaltar,dataentregaactivista,"; sqlInsert += "pacientelocalizado,datalocalizacao,codmotivoabandono) values( "; sqlInsert += "'" + checkNull(readerSource, 0) + "',cdate('" + checkNull(readerSource, 1) + "'),'" + "',cdate('" + checkNull(readerSource, 2) + "'),'" + checkNull(readerSource, 3) + checkNull(readerSource, 4) + checkNull(readerSource, 5) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOBUSCA:"+e.Message); } }
public void exportDataHistEstadoPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " select p.patient_id,"; sqlSelect+=" e.encounter_id, "; sqlSelect+=" p.nid, "; sqlSelect+=" case o.value_coded "; sqlSelect+=" when 1707 then 'Abandono' "; sqlSelect+=" when 1706 then 'Transferido para' "; sqlSelect+=" when 1366 then 'Morte' "; sqlSelect+=" when 1704 then 'HIV Negativo' "; sqlSelect+=" when 1709 then 'Suspender Tarv' "; sqlSelect+=" else 'Outro' end as codestado, "; sqlSelect+=" e.encounter_datetime as dataestado, "; sqlSelect+=" destino.destinopaciente "; sqlSelect+=" from t_paciente p "; sqlSelect+=" inner join encounter e on p.patient_id=e.patient_id "; sqlSelect+=" inner join obs o on o.encounter_id=e.encounter_id and o.person_id=e.patient_id "; sqlSelect+=" left join ( "; sqlSelect+=" select e.encounter_id, "; sqlSelect+=" o.value_text as destinopaciente "; sqlSelect+=" from encounter e "; sqlSelect+=" inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect+=" where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=2059 "; sqlSelect+=" ) destino on e.encounter_id=destino.encounter_id "; sqlSelect+=" where e.encounter_type in (18,6,9) and o.concept_id in (1708,6138) and o.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_histestadopaciente(nid,codestado,dataestado) values("; sqlInsert += "'" + readerSource.GetString(2) + "','" + readerSource.GetString(3) + "',cdate('" + readerSource.GetMySqlDateTime(4) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 id = insertUtil.getMaxID(target, "t_histestadopaciente", "ID"); insertUtil.updateStringValue("t_histestadopaciente", "destinopaciente", commandTarge, readerSource, 5, "ID", id); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_HISTESTADOPACIENTE (MODULO EXPORTTTARV.CS): " + e.Message); } }
private void exportNumericObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'CD4' "; sqlSelect += " when 5497 then 'CD4' "; sqlSelect += " when 5085 then 'Tensão Arterial' "; sqlSelect += " when 5086 then 'Tensão Arterial' "; sqlSelect += " when 653 then 'AST' "; sqlSelect += " when 654 then 'ALT' "; sqlSelect += " when 1021 then 'LINFOCITO' "; sqlSelect += " when 952 then 'LINFOCITO' "; sqlSelect += " when 1022 then 'NEUTROFILO' "; sqlSelect += " when 1330 then 'NEUTROFILO' "; sqlSelect += " when 1024 then 'EOSINOFILO' "; sqlSelect += " when 1332 then 'EOSINOFILO' "; sqlSelect += " when 1025 then 'BASOFILO' "; sqlSelect += " when 1333 then 'BASOFILO' "; sqlSelect += " when 1023 then 'MONOCITO' "; sqlSelect += " when 1331 then 'MONOCITO' "; sqlSelect += " when 1017 then 'CMHC' "; sqlSelect += " when 851 then 'VCM' "; sqlSelect += " when 21 then 'Hemoglobina' "; sqlSelect += " when 1018 then 'HGM' "; sqlSelect += " when 678 then 'WBC' "; sqlSelect += " when 679 then 'RBC' "; sqlSelect += " when 5283 then 'INDICE DE KARNOFSKY' "; sqlSelect += " when 5314 then 'Períneo' "; sqlSelect += " when 1342 then 'IMC' "; sqlSelect += " when 5088 then 'Temperatura' "; sqlSelect += " when 5089 then 'Peso' "; sqlSelect += " when 5090 then 'Altura' "; sqlSelect += " when 5087 then 'Pulmonar - Auscultação' "; sqlSelect += " when 5242 then 'Pulmonar - Respiracao' "; sqlSelect += " when 729 then 'Plaquetas' "; sqlSelect += " when 1015 then 'HTC' "; sqlSelect += " when 5195 then 'Esplenomegáglia' "; sqlSelect += " else cn.name end as codobservacao, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'PERCENTUAL' "; sqlSelect += " when 5497 then 'ABSOLUTO' "; sqlSelect += " when 5085 then 'SUPERIOR' "; sqlSelect += " when 5086 then 'INFERIOR' "; sqlSelect += " when 1021 then 'PERCENTUAL' "; sqlSelect += " when 952 then 'ABSOLUTO' "; sqlSelect += " when 1022 then 'PERCENTUAL' "; sqlSelect += " when 1330 then 'ABSOLUTO' "; sqlSelect += " when 1024 then 'PERCENTUAL' "; sqlSelect += " when 1332 then 'ABSOLUTO' "; sqlSelect += " when 1025 then 'PERCENTUAL' "; sqlSelect += " when 1333 then 'ABSOLUTO' "; sqlSelect += " when 1023 then 'PERCENTUAL' "; sqlSelect += " when 1331 then 'ABSOLUTO' "; sqlSelect += " else null end as codestado, "; sqlSelect += " o.value_numeric as valor, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as data "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and cn.concept_name_type='FULLY_SPECIFIED'"; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " WHERE e.encounter_type in (1,3) and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " c.datatype_id=1 and c.is_set=0 and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_observacaopaciente(codobservacao,valor,nid,data) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(5) + "','" + readerSource.GetString(6) + "',cdate('" + readerSource.GetMySqlDateTime(7) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); if (!readerSource.IsDBNull(4)) { Int32 idObs = insertUtil.getMaxID(target, "t_observacaopaciente", "idobservacao"); commandTarge.CommandText = "Update t_observacaopaciente set codestado='" + readerSource.GetString(4) + "' where idobservacao=" + idObs; commandTarge.ExecuteNonQuery(); } } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, NUMERICO (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message); } }
private void exportInfeccoesOportunistasSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT case o.concept_id "; sqlSelect += " when 1564 then 'I' "; sqlSelect += " when 1565 then 'II' "; sqlSelect += " when 1566 then 'III' "; sqlSelect += " when 1569 then 'IV' "; sqlSelect += " when 1558 then 'I' "; sqlSelect += " when 1561 then 'II' "; sqlSelect += " when 1562 then 'III' "; sqlSelect += " when 2066 then 'IV' "; sqlSelect += " end as estadioms, "; sqlSelect += " if(length(cn.name)>50,concat(left(cn.name,45),' ...'),cn.name) as codigoio, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as data "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id in (1564,1565,1566,1569,1558,1561,1562,2066) and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { // Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); while (readerSource.Read()) { sqlInsert = "Insert into t_infeccoesoportunisticaseguimento(idseguimento,estadiooms,codigoio,Nid,Data) values("; sqlInsert += ""+idSeguimento+",'" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "','" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_INFECCOESOPORTUNISTASSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
private void UpdateScreenTB(MySqlConnection source, OleDbConnection target) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT e.encounter_datetime,"; sqlSelect += " p.nid "; sqlSelect += " FROM t_paciente p"; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type=20 and "; sqlSelect += " e.voided=0 and p.nid is not null "; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); while (readerSource.Read()) { sqlInsert = "update t_seguimento set screeningtb='SIM' where nid='" + readerSource.GetString(1) + "' and dataseguimento=cdate('" + readerSource.GetString(0) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_DIAGNOSTICOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
private void exportDataTratamentoTB(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { //try //{ MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT distinct p.nid, "; sqlSelect += " dataInicio.dataInicio "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id "; sqlSelect += " inner join "; sqlSelect += " ( SELECT o.person_id,e.encounter_id,o.value_datetime as dataInicio "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE o.concept_id=1113 and o.voided=0 and e.voided=0 "; sqlSelect += " UNION "; sqlSelect += " SELECT o.person_id,e.encounter_id,o.obs_datetime as dataInicio "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE o.concept_id=1268 and o.value_coded=1256 and o.voided=0 and e.voided=0 "; sqlSelect += " ) dataInicio on dataInicio.encounter_id=e.encounter_id "; sqlSelect += " WHERE "; sqlSelect += " e.voided=0 and p.nid is not null and "; sqlSelect += " p.datanasc is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; sqlSelect += " GROUP BY nid,dataInicio "; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_tratamentoTB(nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "')"; // sqlInsert = "Insert into t_tratamentoTB(nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "ON DUPLICATE KEY UPDATE datafim='" + readerSource.GetString(2) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); //insertUtil.updateNumericValue("t_tratamentoTB", "data", commandTarge, readerSource, 1, "nid", readerSource.GetString(0)); //insertUtil.updateStringValue("t_tratamentoTB", "datafim", commandTarge, readerSource, 2, "nid", readerSource.GetString(0)); } } readerSource.Close(); sqlSelect = "SELECT p.nid, "; sqlSelect += " datafim.datafim "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id "; sqlSelect += " inner join "; sqlSelect += " ( SELECT o.person_id,e.encounter_id,o.value_datetime as datafim "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=6120 and o.voided=0 and e.voided=0 "; sqlSelect += " ) datafim on datafim.encounter_id=e.encounter_id "; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { insertUtil.updateDateValue("t_tratamentoTB", "datafim", commandTarge, readerSource, 1, "nid", readerSource.GetString(0)); } } readerSource.Close(); //} //catch (Exception e) //{ // MessageBox.Show("Houve erro ao Exportar tabela T_TRATAMENTOTB:" + e.Message); //} }
private void exportDataExposicaoTarvNascenca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { //try //{ MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select distinct p.nid,tarv.tarv"; sqlSelect += " From t_paciente p inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " inner join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 631 then 'NVP'"; sqlSelect += " when 797 then 'AZT'"; sqlSelect += " when 792 then 'D4T+3TC+NVP'"; sqlSelect += " when 1800 then 'TARV'"; sqlSelect += " when 1801 then 'AZT+NVP'"; sqlSelect += " when 630 then 'AZT+3TC'"; sqlSelect += " when 628 then '3TC'"; sqlSelect += " else '' end as tarv"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1503 and o.voided=0 and e.voided=0"; sqlSelect += " ) tarv on tarv.encounter_id=e.encounter_id"; sqlSelect += " where e.encounter_type=7 and p.nid is not null and e.voided=0 and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "';"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_esposicaotarvnascenca(nid,tarv) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //} //catch (Exception e) //{ // MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOTARVNASCENCA:"+e.Message); //} }
private void btImport_Click(object sender, EventArgs e) { //Validacoes de campos if (String.IsNullOrEmpty(txtHost.Text)) { MessageBox.Show("The field Server Host must not be empty..."); txtHost.Focus(); return; } if (String.IsNullOrEmpty(txtPort.Text)) { MessageBox.Show("The field Port must not be empty..."); txtPort.Focus(); return; } if (String.IsNullOrEmpty(txtUsername.Text)) { MessageBox.Show("The field User Name must not be empty..."); txtUsername.Focus(); return; } if (String.IsNullOrEmpty(txtPassword.Text)) { MessageBox.Show("The field Password must not be empty..."); txtPassword.Focus(); return; } if (String.IsNullOrEmpty(txtDataSource.Text)) { MessageBox.Show("The field Data Source must not be empty..."); txtDataSource.Focus(); return; } lblSMS.Text = "Preparando dados iniciais..."; lblSMS.Refresh(); String dataInicialMySQL = dataInicial.Value.Year + "/" + dataInicial.Value.Month + "/" + dataInicial.Value.Day; String dataFinalMySQL = dataFinal.Value.Year + "/" + dataFinal.Value.Month + "/" + dataFinal.Value.Day; //Utils para conexao MySQL - OpenMRS OpenMRSUtils mysqlUtil = new OpenMRSUtils(txtHost.Text, txtUsername.Text, txtPassword.Text); //Utils para conexao MS Access - Sistema HDD AccessUtils accessUtil = new AccessUtils(txtDataSource.Text); //Tem algumas funcoes auxiliares de insercao de dados InsertUtils insertUtils = new InsertUtils(); //Inserir na tabela t_hdd com base no distrito seleccionado insertUtils.insertHDD(accessUtil.getConexao, cboDistrito); // //Levar o codigo da unidade sanitaria seleccionada //String codHDD = insertUtils.getHDDID(accessUtil.getConexao); // //Levar o codigo do distrito como primeiros 4 digitos do codigo da unidade sanitaria // String codDistrito = codHDD.Substring(0, 4); //Levar o codigo do distrito como primeiros 4 digitos do codigo da unidade sanitaria //String codDistrito = codHDD.Substring(0, 4); //Zerar a tabela t_paciente garantir que e nova insercao insertUtils.emptyTPaciente(accessUtil.getConexao); barraProgresso.Value = 5; lblSMS.Text = "A executar a chamada do Stored Procedure no OpenMRS..."; lblSMS.Refresh(); //Chamar a procedure MySQL para encher a tabela t_paciente no OpenMRS String codUS = cboDistrito.Text.Substring(0, 6); insertUtils.callFillTPacienteTable(mysqlUtil.getConexao, dataFinalMySQL, codUS, codUS.Substring(0,4)); barraProgresso.Value = 10; lblSMS.Text = "Enchendo a tabela t_paciente no ACCESS..."; lblSMS.Refresh(); //Encher a tabela de t_bairro insertUtils.insertBairro(accessUtil.getConexao, mysqlUtil.getConexao); //Encher a tabela de t_funcionario insertUtils.insertFuncionario(accessUtil.getConexao, mysqlUtil.getConexao); // //Encher a tabela t_paciente MS Access ExportTPaciente.ExportData(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 15; barraProgresso.Refresh(); //Export t_adulto lblSMS.Text = "Enchendo a tabela t_adulto..."; lblSMS.Refresh(); ExportTAdulto eAdulto = new ExportTAdulto(); eAdulto.exportDataAdulto(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 20; barraProgresso.Refresh(); lblSMS.Text = "Enchendo a tabela t_pai, t_mae e t_crianca..."; lblSMS.Refresh(); ExportCrianca ec = new ExportCrianca(); ExportTMae em = new ExportTMae(); ExportTPai ep = new ExportTPai(); em.exportDataTMae(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); ep.exportDataTPai(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); ec.exportDataCrianca(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 25; barraProgresso.Refresh(); lblSMS.Text = "Export a tabela t_seguimento (Leva muito tempo) ..."; lblSMS.Refresh(); ExportTSeguimento eSeguimento = new ExportTSeguimento(); eSeguimento.exportDataTSeguimento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection); barraProgresso.Value = 55; barraProgresso.Refresh(); lblSMS.Text = "Export a tabela t_tarv,t_histestadopaciente ..."; lblSMS.Refresh(); ExportTTarv etarv = new ExportTTarv(); etarv.exportData(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); etarv.exportDataHistEstadoPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 70; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_resultadoLaboratorio ..."; lblSMS.Refresh(); ExportTResultadoLab lab = new ExportTResultadoLab(); lab.exportDataTResultadoLaboratorio(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 75; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_aconselhamento ..."; lblSMS.Refresh(); ExportTAconselhamento eAconselhamento = new ExportTAconselhamento(); eAconselhamento.exportAconselhamento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection); eAconselhamento.exportAconselhamentoSeguimento(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date, mysqlUtil.getOtherConnection); barraProgresso.Value = 80; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_antecedentesClinicos, t_observacaopaciente,t_contacto ..."; lblSMS.Refresh(); ExportAntecedentesClinicosPaciente eAntecedente = new ExportAntecedentesClinicosPaciente(); ExportTObservacaoPaciente eObs = new ExportTObservacaoPaciente(); ExportTContacto contacto = new ExportTContacto(); eAntecedente.exportAntecedentesClinicosPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); eObs.exportDataTObservacaoPaciente(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); contacto.exportContacto(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 85; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_exposicaotarvmae, t_exposicaotarvnascenca ..."; lblSMS.Refresh(); ExportExposicaoTarv expTarv = new ExportExposicaoTarv(); expTarv.exportDataExposicaoTARV(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 90; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_tratamentoTB, t_QuestionarioTB ..."; lblSMS.Refresh(); ExportTTratamentoTB eTB = new ExportTTratamentoTB(); eTB.exportDataTB(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); barraProgresso.Value = 95; barraProgresso.Refresh(); lblSMS.Text = "Export da tabela t_Busaca ..."; lblSMS.Refresh(); ExportBuscaActiva eBusca = new ExportBuscaActiva(); eBusca.exportDataBuscaActiva(mysqlUtil.getConexao, accessUtil.getConexao, dataInicial.Value.Date, dataFinal.Value.Date); if (chkTemGaac.Checked) { lblSMS.Text = "Export da tabela t_Gaac ..."; lblSMS.Refresh(); ExportTGaac eGaac = new ExportTGaac(); eGaac.exportTGAAC(mysqlUtil.getConexao, accessUtil.getConexao, mysqlUtil.getOtherConnection); } barraProgresso.Value = 100; barraProgresso.Refresh(); lblSMS.Text = "Exportacao Terminada ..."; lblSMS.Refresh(); }
private void exportDataQuestionarioTB(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { //try //{ MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select distinct codopcao.codopcao, "; sqlSelect += " if(codopcao.codopcao is not null,TRUE,FALSE) as estadoopcao,"; sqlSelect += " p.nid," ; sqlSelect += " e.encounter_datetime as data"; sqlSelect += " From t_paciente p inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " inner join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1760 then 'Tosse há mais de 3 Semanas?'"; sqlSelect += " when 1761 then 'Tosse com sangue? '"; sqlSelect += " when 1762 then 'Suores á noite por mais de 3 semanas?'"; sqlSelect += " when 1763 then 'Febre por mais de 3 semanas?'"; sqlSelect += " when 1764 then 'Perdeu peso (mais de 3 Kg no ultimo mês)?'"; sqlSelect += " when 1765 then 'Alguém na família está tratando a TB?'"; sqlSelect += " else null end as codopcao"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=20 and o.concept_id=1766 and o.voided=0 and e.voided=0 "; sqlSelect += " ) codopcao on codopcao.encounter_id=e.encounter_id"; sqlSelect += " where e.encounter_type=20 and e.voided=0 and p.nid is not null and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.datanasc is not null "; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_questionarioTB(codopcao,nid,data) values('" + readerSource.GetString(0) + "','" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateBooleanValue("t_questionarioTB", "estadoopcao", commandTarge, readerSource, 1, "nid", readerSource.GetString(2)); } } readerSource.Close(); //} //catch (Exception e) //{ // MessageBox.Show("Houve erro ao Exportar tabela T_QUESTIONARIOTB:" + e.Message); //} }
private void btnDeidentify_Click(object sender, EventArgs e) { if (String.IsNullOrEmpty(txtDataSource.Text)) { MessageBox.Show("The field Data Source must not be empty..."); txtDataSource.Focus(); return; } AccessUtils accessUtil = new AccessUtils(txtDataSource.Text); InsertUtils insertUtils = new InsertUtils(); System.Data.DataTable dt = insertUtils.deidenfyDataBase(accessUtil.getConexao,accessUtil.getOtherConexao); Excel.Application oXL; Excel.Workbook oWB; Excel.Worksheet oSheet; Excel.Range oRange; // Start Excel and get Application object. oXL = new Excel.ApplicationClass(); // Set some properties oXL.Visible = true; oXL.UserControl = true; System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); oXL.DisplayAlerts = false; // Get a new workbook. oWB = oXL.Workbooks.Add(Missing.Value); //System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; // Get the active sheet oSheet = (Excel.Worksheet)oWB.ActiveSheet; oSheet.Name = "NIDS"; // Process the DataTable // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE //DataTable dt = Customers.RetrieveAsDataTable(); int rowCount = 1; foreach (DataRow dr in dt.Rows) { rowCount += 1; for (int i = 1; i < dt.Columns.Count + 1; i++) { // Add the header the first time through if (rowCount == 2) { oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName; } oSheet.Cells[rowCount, i] = dr[i - 1].ToString(); } } // Resize the columns oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dt.Columns.Count]); oRange.EntireColumn.AutoFit(); // Save the sheet and close oSheet = null; oRange = null; oWB.SaveAs("Deidentify_"+cboDistrito.Text+".xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Close(Missing.Value, Missing.Value, Missing.Value); oWB = null; oXL.Quit(); // Clean up // NOTE: When in release mode, this does the trick GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; MessageBox.Show("Completed"); }
private void exportActividadeAconselhamentoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idAconselhamento) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " e.encounter_datetime as data,"; sqlSelect += " tipo.tipoactividade "; sqlSelect += " FROM t_paciente p"; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 2044 then 'Apoio Psicologico Individual'"; sqlSelect += " when 2045 then 'Apoio Social e Familiar'"; sqlSelect += " when 2046 then 'Informacao e Educacao Sobre Prevencao'"; sqlSelect += " end as tipoactividade"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=24 and o.concept_id=2047 and o.voided=0 and e.voided=0"; sqlSelect += " ) tipo on tipo.encounter_id=e.encounter_id"; sqlSelect += " WHERE p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " e.voided=0 and e.encounter_type=24 and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " e.encounter_id=" + encounterId; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { //Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); while (readerSource.Read()) { sqlInsert = "Insert into t_actividadeaconselhamento(idaconselhamento,nid,data) values("; sqlInsert += "" + idAconselhamento + ",'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateStringValue("t_actividadeaconselhamento", "tipoactividade", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACTIVIDADEACONSELHAMENTOSEGUIMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }
private void exportActividadeAconselhamento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idAconselhamento) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " e.encounter_datetime as data,"; sqlSelect += " sessao.nrsessao,"; sqlSelect += " tipo.tipoactividade,"; sqlSelect += " confidente.apresentouconfidente"; sqlSelect += " FROM t_paciente p"; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " o.value_numeric as nrsessao"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id= 1724 and o.voided=0 and e.voided=0"; sqlSelect += " ) sessao on sessao.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1725 then 'GRUPO'"; sqlSelect += " when 1726 then 'INDIVIDUAL'"; sqlSelect += " end as tipoactividade"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id= 1727 and o.voided=0 and e.voided=0"; sqlSelect += " ) tipo on tipo.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as apresentouconfidente"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id= 1728 and o.voided=0 and e.voided=0"; sqlSelect += " ) confidente on confidente.encounter_id=e.encounter_id"; sqlSelect += " WHERE p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " e.voided=0 and e.encounter_type in (19,29) and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " e.encounter_id=" + encounterId; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { //Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); while (readerSource.Read()) { sqlInsert = "Insert into t_actividadeaconselhamento(idaconselhamento,nid,data) values("; sqlInsert += "" + idAconselhamento + ",'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateNumericValue("t_actividadeaconselhamento", "nrsessao", commandTarge, readerSource, 4, "idaconselhamento",idAconselhamento); insertUtil.updateStringValue("t_actividadeaconselhamento", "tipoactividade", commandTarge, readerSource, 5, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_actividadeaconselhamento", "apresentouconfidente", commandTarge, readerSource, 6, "idaconselhamento",idAconselhamento); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACTIVIDADEACONSELHAMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }
private void exportDiagnosticoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT "; sqlSelect += " case cn.concept_id "; sqlSelect += " when 123 then 'MALARIA' "; sqlSelect += " when 68 then 'DESNUTRIÇÃO AGUDA MODERADA (DAM)' "; sqlSelect += " when 3 then 'ANEMIA' "; sqlSelect += " else cn.name end as diagnostico, "; sqlSelect += " p.nid,"; sqlSelect += " o.obs_datetime as data"; sqlSelect += " FROM t_paciente p"; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id"; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id"; sqlSelect += " inner join concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED'"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=1406 and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); while (readerSource.Read()) { String aux = readerSource.GetString(0); if(aux.Length>50 ){ aux =aux.Substring(0,45); aux +=" ..."; } sqlInsert = "Insert into t_diagnosticoseguimento(idseguimento,coddiagnostico,Nid,Data) values("; sqlInsert += "" + idSeguimento + ",'" + aux + "','" + readerSource.GetString(1) + "',cdate('" + readerSource.GetMySqlDateTime(2) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_DIAGNOSTICOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
public void exportContacto(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.nid,nome.nome,apelido.apelido,telefone.telefone "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join (SELECT e.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " o.value_text as nome "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type in (5,7) and o.concept_id=1441 and "; sqlSelect += " o.voided=0 and e.voided=0 "; sqlSelect += " ) nome "; sqlSelect += " on p.patient_id=nome.patient_id "; sqlSelect += " left join (SELECT e.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " o.value_text as apelido "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type in (5,7) and o.concept_id=1442 and "; sqlSelect += " o.voided=0 and e.voided=0 "; sqlSelect += " ) apelido "; sqlSelect += " on p.patient_id=apelido.patient_id "; sqlSelect += " left join (SELECT e.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " o.value_text as telefone "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type in (5,7) and o.concept_id=1611 and "; sqlSelect += " o.voided=0 and e.voided=0 "; sqlSelect += " ) telefone "; sqlSelect += " on p.patient_id=telefone.patient_id "; sqlSelect += " WHERE p.nid is not null and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_contacto(nid,nome) values("; sqlInsert += "'" + readerSource.GetString(0) + "','"+readerSource.GetString(1)+"')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateStringValue("t_contacto", "apelido", commandTarge, readerSource, 2, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_contacto", "telefone", commandTarge, readerSource, 3, "nid", readerSource.GetString(0)); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_CONTACTO: " + e.Message); } }
public void exportDataTSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate,MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " YEAR(e.encounter_datetime)-YEAR(p.datanasc) as idade,"; sqlSelect += " if(YEAR(e.encounter_datetime)-YEAR(p.datanasc)<2,PERIOD_DIFF(DATE_FORMAT(e.encounter_datetime,'%Y%m'),DATE_FORMAT(p.datanasc,'%Y%m')),null) meses,"; sqlSelect += " estadohiv.estadohiv,"; sqlSelect += " e.encounter_datetime as dataseguimento,"; sqlSelect += " estadiooms.estadiooms,"; sqlSelect += " dataproximaconsulta.dataproximaconsulta,"; sqlSelect += " Gravidez.Gravidez,"; sqlSelect += " outrodiagnostico.diagnostico,"; sqlSelect += " gravidez1.gravida "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 703 then 'Positivo'"; sqlSelect += " when 664 then 'Negativo'"; sqlSelect += " when 1138 then 'Indeterminado'"; sqlSelect += " else 'OUTRO' end as estadohiv"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=9 and o.concept_id=1040 and o.voided=0 and e.voided=0 "; sqlSelect += " ) estadohiv on estadohiv.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1204 then 'I'"; sqlSelect += " when 1205 then 'II'"; sqlSelect += " when 1206 then 'III'"; sqlSelect += " when 1207 then 'IV'"; sqlSelect += " else 'OUTRO' end as estadiooms"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=5356 and o.voided=0 and e.voided=0"; sqlSelect += " ) estadiooms on estadiooms.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_datetime as dataproximaconsulta"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=1410 and o.voided=0 and e.voided=0"; sqlSelect += " ) dataproximaconsulta on dataproximaconsulta.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " o.value_numeric as Gravidez"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=6 and o.concept_id= 5992 and o.voided=0 and e.voided=0"; sqlSelect += " ) Gravidez on Gravidez.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " o.encounter_id, "; sqlSelect += " o.value_text as diagnostico "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id= 1649 and o.voided=0 and e.voided=0 "; sqlSelect += " ) outrodiagnostico on outrodiagnostico.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " o.encounter_id, "; sqlSelect += " 'SIM' as gravida "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id= 1982 and o.voided=0 and e.voided=0 and o.value_coded=44"; sqlSelect += " ) gravidez1 on gravidez1.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and "; sqlSelect += " e.voided=0 and "; sqlSelect += " p.nid is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura between '"+startDateMySQL+"' and '"+endDateMySQL+"' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_seguimento(nid,idade,dataseguimento,tiposeguimento) values("; sqlInsert += "'" + readerSource.GetString(2) + "'," + readerSource.GetString(3) + ",cdate('" + readerSource.GetMySqlDateTime(6) + "'),'Seguinte')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); insertUtil.updateNumericValue("t_seguimento", "meses", commandTarge, readerSource, 4, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "estadohiv", commandTarge, readerSource, 5, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "estadiooms", commandTarge, readerSource, 7, "idseguimento", idSeguimento); insertUtil.updateDateValue("t_seguimento", "dataproximaconsulta", commandTarge, readerSource, 8, "idseguimento", idSeguimento); insertUtil.updateNumericValue("t_seguimento", "Gravidez", commandTarge, readerSource, 9, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "Observacao", commandTarge, readerSource, 10, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "gravida", commandTarge, readerSource, 11, "idseguimento", idSeguimento); exportInfeccoesOportunistasSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); exportTratamentoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); exportDiagnosticoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); } } readerSource.Close(); UpdateScreenTB(source, target); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_SEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
public static void ExportData(MySqlConnection source, OleDbConnection target,DateTime startDate,DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; //String sqlSelect="Select patient_id 0,hdd 1,dataabertura 2,nid 3,sexo 4,datanasc 5,idade 6,meses 7,"; //sqlSelect+="coddistrito 8,codproveniencia 9,designacaoprov 10,designacaoprov 11,emtarv 12,datainiciotarv 13,"; //sqlSelect +="codestado 14,destinopaciente 15,datasaidatarv 16,datadiagnostico 17,aconselhado 18,referidohdd 19,"; //sqlSelect +="datareferidohdd 20,aceitabuscaactiva 21,dataaceitabuscaactiva 22,referidobuscaactiva 23,"; //sqlSelect +="datareferenciabuscaactiva 24,situacaohiv 25 from t_paciente where dataabertura between "+startDate+ " and " + endDate; String sqlSelect = " Select patient_id,hdd,dataabertura,nid,sexo,datanasc,idade,meses,"; sqlSelect += " coddistrito,codproveniencia,designacaoprov,Codigoproveniencia,emtarv,datainiciotarv,"; sqlSelect += " codestado,destinopaciente,datasaidatarv,datadiagnostico,aconselhado,referidohdd,"; sqlSelect += " datareferidohdd,aceitabuscaactiva,dataaceitabuscaactiva,referidobuscaactiva,"; sqlSelect += " datareferenciabuscaactiva,situacaohiv,nome,identificacao,codbairro,celula,avenida, "; sqlSelect += " codregime,apelido,provider_id,tipopaciente,cirurgias,transfusao,estadiooms,emtratamentotb from t_paciente "; sqlSelect += " where datanasc is not null and nid is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_paciente(hdd,dataabertura,nid,sexo,datanasc,idade,coddistrito) values("; sqlInsert += "'" + checkNull(readerSource, 1) + "',cdate('" + checkNull(readerSource, 2) + "'),'" + checkNull(readerSource, 3) + "','" + checkNull(readerSource, 4) + "',cdate('" + checkNull(readerSource, 5) + "')," + checkNull(readerSource, 6) + ",'" + checkNull(readerSource, 8) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateNumericValue("t_paciente", "meses", commandTarge, readerSource, 7, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "codproveniencia", commandTarge, readerSource, 9, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "designacaoprov", commandTarge, readerSource, 10, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "Codigoproveniencia", commandTarge, readerSource, 11, "nid", readerSource.GetString(3)); insertUtil.updateBooleanValue("t_paciente", "emtarv", commandTarge, readerSource, 12, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "datainiciotarv", commandTarge, readerSource, 13, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "codestado", commandTarge, readerSource, 14, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "destinopaciente", commandTarge, readerSource, 15, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "datasaidatarv", commandTarge, readerSource, 16, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "datadiagnostico", commandTarge, readerSource, 17, "nid", readerSource.GetString(3)); insertUtil.updateBooleanValue("t_paciente", "aconselhado", commandTarge, readerSource, 18, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "referidohdd", commandTarge, readerSource, 19, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "datareferidohdd", commandTarge, readerSource, 20, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "aceitabuscaactiva", commandTarge, readerSource, 21, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "dataaceitabuscaactiva", commandTarge, readerSource, 22, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "referidobuscaactiva", commandTarge, readerSource, 23, "nid", readerSource.GetString(3)); insertUtil.updateDateValue("t_paciente", "datareferenciabuscaactiva", commandTarge, readerSource, 24, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "situacaohiv", commandTarge, readerSource, 25, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "nome", commandTarge, readerSource, 26, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "identificacao", commandTarge, readerSource, 27, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "codbairro", commandTarge, readerSource, 28, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "celula", commandTarge, readerSource, 29, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "avenida", commandTarge, readerSource, 30, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "codregime", commandTarge, readerSource, 31, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "apelido", commandTarge, readerSource, 32, "nid", readerSource.GetString(3)); insertUtil.updateNumericValue("t_paciente", "codfuncionario", commandTarge, readerSource, 33, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "tipopaciente", commandTarge, readerSource, 34, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "cirurgias", commandTarge, readerSource, 35, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "transfusao", commandTarge, readerSource, 36, "nid", readerSource.GetString(3)); insertUtil.updateStringValue("t_paciente", "estadiooms", commandTarge, readerSource, 37, "nid", readerSource.GetString(3)); insertUtil.updateBooleanValue("t_paciente", "emtratamentotb", commandTarge, readerSource, 38, "nid", readerSource.GetString(3)); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar tabela T_PACIENTE:" + e.Message); } }
private void exportTratamentoSeguimento(MySqlConnection source, OleDbConnection target, String encounterId, Int32 idSeguimento) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT "; sqlSelect += " cn.name as codtratamento, "; sqlSelect += " p.nid,"; sqlSelect += " o.obs_datetime as data"; sqlSelect += " FROM t_paciente p"; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id"; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id"; sqlSelect += " inner join concept_name cn on cn.concept_id=o.value_coded and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED'"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=1719 and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and e.encounter_id=" + encounterId; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { //Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); while (readerSource.Read()) { sqlInsert = "Insert into t_tratamentoseguimento(idseguimento,codtratamento,nid,data) values("; sqlInsert += "" + idSeguimento + ",'" + readerSource.GetString(0) + "','" + readerSource.GetString(1) + "',cdate('" + readerSource.GetMySqlDateTime(2) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_TRATAMENTOSEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
public void exportDataCrianca(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select distinct p.nid,tipoparto.tipoparto,local.local,termo.termo,pesonascimento.pesonascimento,"; sqlSelect += " exposicaotarvmae.exposicaotarvmae,exposicaotarvnascenca.exposicaotarvnascenca,"; sqlSelect += " patologianeonatal.patologianeonatal,injeccoes.injeccoes,escarificacoes.escarificacoes,"; sqlSelect += " extracoesdentarias.extracoesdentarias,aleitamentomaterno.aleitamentomaterno,"; sqlSelect += " aleitamentoexclusivo.aleitamentoexclusivo,idadedesmame.idadedesmame,pavcompleto.pavcompleto,"; sqlSelect += " idadecronologica.idadecronologica,bailey.bailey"; sqlSelect += " From t_paciente p inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1170 then 'VAGINAL'"; sqlSelect += " when 1171 then 'CESARIANA'"; sqlSelect += " else '' end as tipoparto"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=5630 and o.voided=0 and e.voided=0"; sqlSelect += " ) tipoparto on tipoparto.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as local"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1505 and o.voided=0 and e.voided=0"; sqlSelect += " ) local on local.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " else '' end as termo"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1500 and o.voided=0 and e.voided=0"; sqlSelect += " ) termo on termo.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as pesonascimento"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=5916 and o.voided=0 and e.voided=0"; sqlSelect += " ) pesonascimento on pesonascimento.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " when 1457 then -99"; sqlSelect += " else 0 end as exposicaotarvmae"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1501 and o.voided=0 and e.voided=0"; sqlSelect += " ) exposicaotarvmae on exposicaotarvmae.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " when 1457 then -99"; sqlSelect += " else '' end as exposicaotarvnascenca"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1502 and o.voided=0 and e.voided=0"; sqlSelect += " ) exposicaotarvnascenca on exposicaotarvnascenca.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as patologianeonatal"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1506 and o.voided=0 and e.voided=0"; sqlSelect += " ) patologianeonatal on patologianeonatal.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " when 1457 then -99"; sqlSelect += " else '' end as injeccoes"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1507 and o.voided=0 and e.voided=0"; sqlSelect += " ) injeccoes on injeccoes.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " when 1457 then -99"; sqlSelect += " else '' end as escarificacoes"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1509 and o.voided=0 and e.voided=0"; sqlSelect += " ) escarificacoes on escarificacoes.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " when 1457 then -99"; sqlSelect += " else '' end as extracoesdentarias"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1508 and o.voided=0 and e.voided=0 "; sqlSelect += " ) extracoesdentarias on extracoesdentarias.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " else '' end as aleitamentomaterno "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=6061 and o.voided=0 and e.voided=0 "; sqlSelect += " ) aleitamentomaterno on aleitamentomaterno.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 5526 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " else '' end as aleitamentoexclusivo"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1613 and o.voided=0 and e.voided=0"; sqlSelect += " ) aleitamentoexclusivo on aleitamentoexclusivo.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as idadedesmame"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1510 and o.voided=0 and e.voided=0"; sqlSelect += " ) idadedesmame on idadedesmame.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then -1"; sqlSelect += " when 1066 then 0"; sqlSelect += " else '' end as pavcompleto"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1511 and o.voided=0 and e.voided=0"; sqlSelect += " ) pavcompleto on pavcompleto.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as idadecronologica"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1512 and o.voided=0 and e.voided=0"; sqlSelect += " ) idadecronologica on idadecronologica.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as bailey"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1514 and o.voided=0 and e.voided=0"; sqlSelect += " ) bailey on bailey.encounter_id=e.encounter_id"; sqlSelect += " where e.encounter_type=7 and e.voided=0 and nid is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' group by nid;"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { //sqlInsert = "Insert into t_crianca(nid,local) values('" + readerSource.GetString(0) + "','"+ checkNull(readerSource, 2) + "')"; sqlInsert = "Insert into t_crianca(nid) values('" + readerSource.GetString(0) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateStringValue("t_crianca", "tipoparto", commandTarge, readerSource, 1, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "[local]", commandTarge, readerSource, 2, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "termo", commandTarge, readerSource, 3, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "pesonascimento", commandTarge, readerSource, 4, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "exposicaotarvmae", commandTarge, readerSource, 5, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "exposicaotarvnascenca", commandTarge, readerSource, 6, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "patologianeonatal", commandTarge, readerSource, 7, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "injeccoes", commandTarge, readerSource, 8, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "escarificacoes", commandTarge, readerSource, 9, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "extracoesdentarias", commandTarge, readerSource, 10, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "aleitamentomaterno", commandTarge, readerSource, 11, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "aleitamentoexclusivo", commandTarge, readerSource, 12, "nid", readerSource.GetString(0)); insertUtil.updateNumericValue("t_crianca", "idadedesmame", commandTarge, readerSource, 13, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "pavcompleto", commandTarge, readerSource, 14, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "idadecronologica", commandTarge, readerSource, 15, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_crianca", "bailey", commandTarge, readerSource, 16, "nid", readerSource.GetString(0)); } } String sqlUpdateMae = "UPDATE t_crianca,t_mae SET t_crianca.idmae=t_mae.idmae where t_crianca.nid=t_mae.nid;"; commandTarge.CommandText = sqlUpdateMae; commandTarge.ExecuteNonQuery(); String sqlUpdatePai = "UPDATE t_crianca,t_pai SET t_crianca.idpai=t_pai.idpai where t_crianca.nid=t_pai.nid;"; commandTarge.CommandText = sqlUpdatePai; commandTarge.ExecuteNonQuery(); readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar tabela T_CRIANCA:" + e.Message); } }
public void exportData(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select p.patient_id,"; sqlSelect += "e.encounter_id,"; sqlSelect += "p.nid,"; sqlSelect += "e.encounter_datetime as datatarv,"; sqlSelect += "regime.codRegime,"; sqlSelect += "case o.value_coded"; sqlSelect += " when 1256 then 'Inicia'"; sqlSelect += " when 1257 then 'Manter'"; sqlSelect += " when 1259 then 'Alterar'"; sqlSelect += " when 1369 then 'Transfer de'"; sqlSelect += " when 1705 then 'Reiniciar'"; sqlSelect += " when 1708 then 'Saida'"; sqlSelect += " else 'OUTRO' end as tipotarv,"; sqlSelect += " proxima.dataproxima,"; sqlSelect += " aviada.qtdComp,"; sqlSelect += " saldo.qtdSaldo,"; sqlSelect += " outro.dataoutro"; sqlSelect += " from t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 792 then 'D4T+3TC+NVP'"; sqlSelect += " when 6110 then 'D4T20+3TC+NVP'"; sqlSelect += " when 1827 then 'D4T30+3TC+EFV'"; sqlSelect += " when 6103 then 'D4T+3TC+LPV'"; sqlSelect += " when 1651 then 'AZT+3TC+NVP'"; sqlSelect += " when 1703 then 'AZT+3TC+EFV'"; sqlSelect += " when 1702 then 'AZT+3TC+NFV'"; sqlSelect += " when 6100 then 'AZT+3TC+LPV'"; sqlSelect += " when 817 then 'AZT+3TC+ABC'"; sqlSelect += " when 6104 then 'ABC+3TC+EFV'"; sqlSelect += " when 6106 then 'ABC+3TC+LPV/r'"; sqlSelect += " when 6105 then 'ABC+3TC+NVP'"; sqlSelect += " when 6243 then 'TDF+3TC+NVP'"; sqlSelect += " when 6244 then 'AZT+3TC+RTV'"; sqlSelect += " when 1700 then 'AZT+DDl+NFV'"; sqlSelect += " when 633 then 'EFV'"; sqlSelect += " when 625 then 'D4T'"; sqlSelect += " when 631 then 'NVP'"; sqlSelect += " when 628 then '3TC'"; sqlSelect += " when 6107 then 'TDF+AZT+3TC+LPV/r'"; sqlSelect += " when 6236 then 'D4T+DDI+RTV-IP'"; sqlSelect += " when 1701 then 'ABC+DDI+NFV'"; sqlSelect += " else 'OUTROS' end as codRegime"; sqlSelect += " from encounter e inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and o.concept_id=1088 and e.encounter_type=18"; sqlSelect += " ) regime on regime.encounter_id=e.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_datetime as dataproxima"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=5096"; sqlSelect += " ) proxima on e.encounter_id=proxima.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_numeric as qtdComp"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1715"; sqlSelect += " ) aviada on e.encounter_id=aviada.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_numeric as qtdSaldo"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1713"; sqlSelect += " ) saldo on e.encounter_id=saldo.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_datetime as dataoutro"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1190"; sqlSelect += " ) outro on e.encounter_id=outro.encounter_id"; sqlSelect += " where e.voided=0 and e.encounter_type=18 and o.voided=0 and o.concept_id=1255 and o.value_coded<>1708 and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; sqlSelect += " and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.nid is not null and regime.codregime is not null"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_tarv(nid,datatarv,tipotarv,codregime) values("; sqlInsert += "'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'),'" + readerSource.GetString(5) + "','" + readerSource.GetString(4) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idTarv = insertUtil.getMaxID(target, "t_tarv", "idtarv"); //insertUtil.updateStringValue("t_tarv", "codregime", commandTarge, readerSource, 4, "encounter_id", readerSource.GetString(1)); insertUtil.updateDateValue("t_tarv", "dataproxima", commandTarge, readerSource, 6, "idtarv", idTarv); insertUtil.updateNumericValue("t_tarv", "QtdComp", commandTarge, readerSource, 7, "idtarv", idTarv); insertUtil.updateNumericValue("t_tarv", "QtdSaldo", commandTarge, readerSource, 8, "idtarv", idTarv); insertUtil.updateDateValue("t_tarv", "dataoutroservico", commandTarge, readerSource, 9, "idtarv", idTarv); } commandTarge.CommandText = "update t_tarv set dias=QtdComp/2 where QtdComp is not null"; commandTarge.ExecuteNonQuery(); } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_TARV: " + e.Message); } }
private void exportNumericLabResult(MySqlConnection source, OleDbConnection target, DateTime startDate,DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'CD4' "; sqlSelect += " when 5497 then 'CD4' "; sqlSelect += " when 653 then 'AST' "; sqlSelect += " when 654 then 'ALT' "; sqlSelect += " when 1021 then 'L' "; sqlSelect += " when 952 then 'L' "; sqlSelect += " when 1022 then 'N' "; sqlSelect += " when 1330 then 'N' "; sqlSelect += " when 1024 then 'E' "; sqlSelect += " when 1332 then 'E' "; sqlSelect += " when 1025 then 'B' "; sqlSelect += " when 1333 then 'B' "; sqlSelect += " when 1023 then 'M' "; sqlSelect += " when 1331 then 'M' "; sqlSelect += " when 1017 then 'CMHC' "; sqlSelect += " when 851 then 'VGM' "; sqlSelect += " when 21 then 'Hemoglobina' "; sqlSelect += " when 1018 then 'HGM' "; sqlSelect += " when 678 then 'WBC' "; sqlSelect += " when 679 then 'RBC' "; sqlSelect += " when 1015 then 'HTC' "; sqlSelect += " when 729 then 'Plaquetas' "; sqlSelect += " when 1016 then 'RDW' "; sqlSelect += " when 1307 then 'MPV' "; sqlSelect += " when 1011 then 'CK' "; sqlSelect += " when 857 then 'Ureia' "; sqlSelect += " when 790 then 'Creatinina' "; sqlSelect += " when 848 then 'Albumina' "; sqlSelect += " when 655 then 'Bilirrubina T' "; sqlSelect += " when 887 then 'GLC' "; sqlSelect += " when 1297 then 'Bilirrubina C' "; sqlSelect += " when 1299 then 'Amilase' "; sqlSelect += " when 855 then 'VS' "; sqlSelect += " else cn.name end as codexame, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as dataresultado, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'PERCENTUAL' "; sqlSelect += " when 5497 then 'ABSOLUTO' "; sqlSelect += " when 1021 then 'PERCENTUAL' "; sqlSelect += " when 952 then 'ABSOLUTO' "; sqlSelect += " when 1022 then 'PERCENTUAL' "; sqlSelect += " when 1330 then 'ABSOLUTO' "; sqlSelect += " when 1024 then 'PERCENTUAL' "; sqlSelect += " when 1332 then 'ABSOLUTO' "; sqlSelect += " when 1025 then 'PERCENTUAL' "; sqlSelect += " when 1333 then 'ABSOLUTO' "; sqlSelect += " when 1023 then 'PERCENTUAL' "; sqlSelect += " when 1331 then 'ABSOLUTO' "; sqlSelect += " else null end as codparametro, "; sqlSelect += " o.value_numeric as resultado, "; sqlSelect += " pedido.data_pedido "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " left join ( "; sqlSelect += " select e.encounter_id, "; sqlSelect += " o.value_datetime as data_pedido "; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=13 and o.concept_id=6246 "; sqlSelect += " ) pedido on e.encounter_id=pedido.encounter_id "; sqlSelect += " WHERE e.encounter_type=13 and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and p.datanasc is not null and "; sqlSelect += " c.datatype_id=1 and c.is_set=0 and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_resultadoslaboratorio(codexame,nid,dataresultado,resultado) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "',cdate('" + readerSource.GetString(5) + "'),'" + readerSource.GetDouble(7) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idLab = insertUtil.getMaxID(target, "t_resultadoslaboratorio", "idresultado"); if (!readerSource.IsDBNull(6)) { commandTarge.CommandText = "Update t_resultadoslaboratorio set codparametro='" + readerSource.GetString(6) + "' where idresultado=" + idLab; commandTarge.ExecuteNonQuery(); } if (!readerSource.IsDBNull(8)) { commandTarge.CommandText = "Update t_resultadoslaboratorio set datapedido=cdate('" + readerSource.GetString(8) + "') where idresultado=" + idLab; commandTarge.ExecuteNonQuery(); } } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_RESULTADOLABORATORIO, NUMERICO (MODULO EXPORTTRESULTADOLAB.CS): " + e.Message); } }
public void exportDataBuscaActiva(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { //try //{ MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT distinct p.nid, "; sqlSelect += " datacomecoufaltar.datacomecoufaltar, "; sqlSelect += " dataentregaactivista.dataentregaactivista, "; sqlSelect += " pacientelocalizado.pacientelocalizado, "; sqlSelect += " pacientelocalizado.datalocalizacao, "; sqlSelect += " codmotivoabandono.codmotivoabandono, "; sqlSelect += " codreferencia.codreferencia, "; sqlSelect += " entregueconvite.entregueconvite, "; sqlSelect += " confidenteidentificado.confidenteidentificado, "; sqlSelect += " codinformacaodadapor.codinformacaodadapor, "; sqlSelect += " codservicorefere.codservicorefere, "; sqlSelect += " e.encounter_datetime, "; sqlSelect += " observacao.observacao "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id "; sqlSelect += " inner join "; sqlSelect += " ( SELECT o.person_id,e.encounter_id,o.value_datetime as datacomecoufaltar "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2004 and o.voided=0 and e.voided=0 "; sqlSelect += " ) datacomecoufaltar on e.encounter_id=datacomecoufaltar.encounter_id "; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_datetime as dataentregaactivista "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2173 and o.voided=0 and e.voided=0 "; sqlSelect += " ) dataentregaactivista on dataentregaactivista.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " if(o.value_coded=1065,o.obs_datetime,null) as datalocalizacao, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 1065 then 'SIM' "; sqlSelect += " when 1066 then 'NAO' "; sqlSelect += " else null end as pacientelocalizado "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2003 and o.voided=0 and e.voided=0 "; sqlSelect += " ) pacientelocalizado on pacientelocalizado.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 2005 then 'ESQUECEU A DATA' "; sqlSelect += " when 2006 then 'ESTA ACAMADO EM CASA' "; sqlSelect += " when 2007 then 'DISTANCIA/DINHEIRO TRANSPORTE' "; sqlSelect += " when 2008 then 'PROBLEMAS DE ALIMENTACAO' "; sqlSelect += " when 2009 then 'PROBLEMAS FAMILIARES' "; sqlSelect += " when 2010 then 'INSATISFACCAO COM SERVICO NO HDD' "; sqlSelect += " when 2011 then 'VIAJOU' "; sqlSelect += " when 2012 then 'DESMOTIVACAO' "; sqlSelect += " when 2013 then 'TRATAMENTO TRADICIONAL' "; sqlSelect += " when 2014 then 'TRABALHO' "; sqlSelect += " when 2015 then 'EFEITOS SECUNDARIOS ARV' "; sqlSelect += " when 2017 then 'OUTRO' "; sqlSelect += " else o.value_coded end as codmotivoabandono "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2016 and o.voided=0 and e.voided=0 "; sqlSelect += " ) codmotivoabandono on codmotivoabandono.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 1797 then 'Encaminhado para a US' "; sqlSelect += " when 1977 then 'Encaminhado para os grupos de apoio' "; sqlSelect += " when 5488 then 'Orientado sobre a toma correcta dos ARV' "; sqlSelect += " when 2159 then 'Familiar foi referido para a US' "; sqlSelect += " else 'OUTRO' end as codreferencia "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=1272 and o.voided=0 and e.voided=0 "; sqlSelect += " ) codreferencia on codreferencia.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " if(o.value_datetime is not null,'SIM','NAO') as entregueconvite "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2179 and o.voided=0 and e.voided=0 "; sqlSelect += " ) entregueconvite on entregueconvite.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 1065 then 'SIM' "; sqlSelect += " when 1066 then 'NAO' "; sqlSelect += " else null end as confidenteidentificado "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=1739 and o.voided=0 and e.voided=0 "; sqlSelect += " ) confidenteidentificado on confidenteidentificado.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 2034 then 'Vizinho' "; sqlSelect += " when 2033 then 'Confidente' "; sqlSelect += " when 2035 then 'Familiar' "; sqlSelect += " when 2036 then 'Secretário do Bairro' "; sqlSelect += " else 'OUTRO' end as codinformacaodadapor "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2037 and o.voided=0 and e.voided=0 "; sqlSelect += " ) codinformacaodadapor on codinformacaodadapor.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 2175 then 'TARV Adulto' "; sqlSelect += " when 2174 then 'TARV Pediatrico' "; sqlSelect += " when 1414 then 'PNCT' "; sqlSelect += " when 1598 then 'PTV' "; sqlSelect += " else 'OUTRO' end as codservicorefere "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2176 and o.voided=0 and e.voided=0 "; sqlSelect += " ) codservicorefere on codservicorefere.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " o.value_text as observacao "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and o.concept_id=2041 and o.voided=0 and e.voided=0 "; sqlSelect += " ) observacao on observacao.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type=21 and e.voided=0 and p.nid is not null and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '"+startDateMySQL+"' and '"+endDateMySQL+"' and e.encounter_datetime between '"+startDateMySQL+"' and '"+endDateMySQL+"'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_buscaactiva(nid,datacomecoufaltar) values( "; sqlInsert += "'" + readerSource.GetString(0) + "',cdate('" + readerSource.GetDateTime(1) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); //data primeira tentativa if (!readerSource.IsDBNull(11)) { commandTarge.CommandText = "Update t_buscaactiva set dataprimeiratentativa=cdate('" + readerSource.GetDateTime(11) + "') where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Data Entrega if (!readerSource.IsDBNull(2)) { commandTarge.CommandText="Update t_buscaactiva set dataentregaactivista=cdate('"+readerSource.GetDateTime(2)+"') where nid='"+readerSource.GetString(0)+"' and datacomecoufaltar=cdate('"+readerSource.GetDateTime(1)+"')"; commandTarge.ExecuteNonQuery(); } //localizado if (!readerSource.IsDBNull(3)) { commandTarge.CommandText = "Update t_buscaactiva set pacientelocalizado='" + readerSource.GetString(3) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Data Localizado if (!readerSource.IsDBNull(4)) { commandTarge.CommandText = "Update t_buscaactiva set datalocalizacao=cdate('" + readerSource.GetDateTime(4) + "') where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Cod Motivo Abadono if (!readerSource.IsDBNull(5)) { commandTarge.CommandText = "Update t_buscaactiva set codmotivoabandono='" + readerSource.GetString(5) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Cod Referencia if (!readerSource.IsDBNull(6)) { commandTarge.CommandText = "Update t_buscaactiva set codreferencia='" + readerSource.GetString(6) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Entregue o convite if (!readerSource.IsDBNull(7)) { commandTarge.CommandText = "Update t_buscaactiva set entregueconvite='" + readerSource.GetString(7) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //confidente identificado if (!readerSource.IsDBNull(8)) { commandTarge.CommandText = "Update t_buscaactiva set confidenteidentificado='" + readerSource.GetString(8) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Cod informacao dada por if (!readerSource.IsDBNull(9)) { commandTarge.CommandText = "Update t_buscaactiva set confidenteidentificado='" + readerSource.GetString(9) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } //Cod servico que refere if (!readerSource.IsDBNull(10)) { commandTarge.CommandText = "Update t_buscaactiva set Codservicoquerefere='" + readerSource.GetString(10) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } if (!readerSource.IsDBNull(12)) { commandTarge.CommandText = "Update t_buscaactiva set Observacao='" + readerSource.GetString(12) + "' where nid='" + readerSource.GetString(0) + "' and datacomecoufaltar=cdate('" + readerSource.GetDateTime(1) + "')"; commandTarge.ExecuteNonQuery(); } } } readerSource.Close(); //} //catch (Exception e) //{ // MessageBox.Show("Houve erro ao Exportar tabela T_EXPOSICAOBUSCA:" + e.Message); //} }
private void exportCodedLabResult(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 300 then 'TIPAGEM SANGUINEA' "; sqlSelect += " when 1655 then 'RPR' "; sqlSelect += " when 299 then 'VDRL' "; sqlSelect += " when 307 then 'BACILOSCOPIA' "; sqlSelect += " when 1030 then 'PCR' "; sqlSelect += " else cn.name end as codexame, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as dataresultado, "; sqlSelect += " case o.value_coded "; sqlSelect += " when 1229 then 'NAO REACTIVO' "; sqlSelect += " when 1228 then 'REACTIVO' "; sqlSelect += " when 1304 then 'MA QUALIDADE DE AMOSTRA' "; sqlSelect += " when 664 then 'NEGATIVO' "; sqlSelect += " when 703 then 'POSITIVO' "; sqlSelect += " when 1138 then 'INDETERMINADO' "; sqlSelect += " when 690 then 'A POSITIVO' "; sqlSelect += " when 692 then 'A NEGATIVO' "; sqlSelect += " when 694 then 'B POSITIVO' "; sqlSelect += " when 696 then 'B NEGATIVO' "; sqlSelect += " when 699 then 'O POSITIVO' "; sqlSelect += " when 701 then 'O NEGATIVO' "; sqlSelect += " when 1230 then 'AB POSITIVO' "; sqlSelect += " when 1231 then 'AB NEGATIVO' "; sqlSelect += " else 'OUTRO' end as codparametro "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " WHERE e.encounter_type=13 and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and p.datanasc is not null and "; sqlSelect += " c.datatype_id=2 and c.is_set=0 and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_resultadoslaboratorio(codexame,nid,dataresultado,codparametro) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "',cdate('" + readerSource.GetString(5) + "'),'" + readerSource.GetString(6) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_RESULTADOLABORATORIO, CODED (MODULO EXPORTTRESULTADOLAB.CS): " + e.Message); } }
private void exportObsDataPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.nid,e.encounter_datetime,e.provider_id "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " WHERE e.encounter_type in (1,3) and e.voided=0 and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_observacaodata(nid,data,medico) values("; sqlInsert += "'" + readerSource.GetString(0) + "',cdate('" + readerSource.GetString(1) + "')," + readerSource.GetInt32(2) + ")"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message); } }
public void exportTGAAC(MySqlConnection source, OleDbConnection target, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT l.name as hdd, "; sqlSelect += " g.gaac_identifier as numGaac, "; sqlSelect += " g.start_date as datainicio, "; sqlSelect += " at.name as afinidade, "; sqlSelect += " g.date_crumbled as dataDesintegracao, "; sqlSelect += " p.nid as nidPontoFocal, "; sqlSelect += " g.description as observacao, "; sqlSelect += " g.gaac_id "; sqlSelect += " FROM gaac g "; sqlSelect += " left join t_paciente p on g.focal_patient_id=p.patient_id "; sqlSelect += " left join gaac_affinity_type at on g.affinity_type=at.gaac_affinity_type_id"; sqlSelect += " left join location l on g.location_id=l.location_id "; sqlSelect += " WHERE g.voided=0 "; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { Int32 numGaac = Convert.ToInt32(readerSource.GetString(1)); sqlInsert = "Insert into t_gaac(hdd,numGAAC,datainicio,afinidade) values("; sqlInsert += "'" + readerSource.GetString(0) + "'," + numGaac + ",cdate('" + readerSource.GetMySqlDateTime(2) + "'),'" + readerSource.GetString(3) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateDateValue("t_gaac", "dataDesintegracao", commandTarge, readerSource, 4, "numGAAC", numGaac); insertUtil.updateDateValue("t_gaac", "nidPontoFocal", commandTarge, readerSource, 5, "numGAAC", numGaac); insertUtil.updateDateValue("t_gaac", "observacao", commandTarge, readerSource, 6, "numGAAC", numGaac); this.exportTActividade(otherSource,target,readerSource.GetInt32(7)); } } } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_GAAC (MODULO EXPORTTGAAC.CS): " + e.Message); } }
private void exportTextObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 1671 then 'Historia Actual' "; sqlSelect += " when 1474 then 'Terapeutica Outro' "; sqlSelect += " when 1649 then 'Outros Diagnosticos' "; sqlSelect += " when 1424 then 'Tipo Lesao de Cavidade Orofaringea' "; sqlSelect += " when 1543 then 'Exame Pulmonar Auscultacao, Outro' "; sqlSelect += " when 1423 then 'Tipo de Lesao da Pele' "; sqlSelect += " when 1396 then 'Outro Exame Abdomen' "; sqlSelect += " when 1407 then 'Outro Exame Neurologico' "; sqlSelect += " when 1678 then 'Outos exames Genitais' "; sqlSelect += " when 1542 then 'Exame Oral' "; sqlSelect += " when 1556 then 'Membros Inferiores' "; sqlSelect += " when 1555 then 'Membros Superiores' "; sqlSelect += " when 1554 then 'Perineo' "; sqlSelect += " when 1541 then 'Outros Tratamentos' "; sqlSelect += " when 1553 then 'Outras Massas' "; sqlSelect += " when 1536 then 'Uso de Cotrimoxazol, Especificamente' "; sqlSelect += " when 1642 then 'RX: Torax, Outro' "; sqlSelect += " else cn.name end as codobservacao, "; sqlSelect += " o.value_text as valor, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as dataresultado "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " WHERE e.encounter_type in (1,3) and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " c.datatype_id=3 and c.is_set=0 and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_observacaopaciente(codobservacao,Observacao,nid,data) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "','" + readerSource.GetString(5) + "',cdate('" + readerSource.GetMySqlDateTime(6) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, CODED (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message); } }
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 exportDataTMae(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select p.nid as nid,nome.nome,idade.idade,vivo.vivo,doente.doente,doenca.doenca,codprofissao.codprofissao,"; sqlSelect += " resultadohiv.resultadohiv,emtarv.emtarv"; sqlSelect += " From t_paciente p inner join encounter e on e.patient_id=p.patient_id" ; sqlSelect += " inner join ( SELECT o.person_id,e.encounter_id,o.value_text as nome"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1477 and o.voided=0 and e.voided=0"; sqlSelect += " ) nome on nome.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_numeric as idade"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1478 and o.voided=0 and e.voided=0"; sqlSelect += " ) idade on idade.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then 'SIM'"; sqlSelect += " when 1066 then 'NAO'"; sqlSelect += " when 1457 then 'Sem Informação'"; sqlSelect += " else '' end as vivo"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1479 and o.voided=0 and e.voided=0"; sqlSelect += " ) vivo on vivo.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then 'SIM'"; sqlSelect += " when 1066 then 'NAO'"; sqlSelect += " when 1457 then 'Sem Informação'"; sqlSelect += " else '' end as doente"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1480 and o.voided=0 and e.voided=0"; sqlSelect += " ) doente on doente.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as doenca"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1481 and o.voided=0 and e.voided=0"; sqlSelect += " ) doenca on doenca.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,o.value_text as codprofissao"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " WHERE e.encounter_type in (7) and o.concept_id=1482 and o.voided=0 and e.voided=0"; sqlSelect += " ) codprofissao on codprofissao.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 703 then 'POSITIVO'"; sqlSelect += " when 664 then 'NEGATIVO'"; sqlSelect += " when 1138 then 'INDETERMINADO'"; sqlSelect += " when 1118 then 'NAO FEZ'"; sqlSelect += " when 1457 then 'Sem Informação'"; sqlSelect += " else '' end as resultadohiv"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1483 and o.voided=0 and e.voided=0"; sqlSelect += " ) resultadohiv on resultadohiv.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then 'SIM'"; sqlSelect += " when 1066 then 'NAO'"; sqlSelect += " when 1457 then 'Sem Informação'"; sqlSelect += " else '' end as emtarv"; sqlSelect += " FROM encounter e"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=7 and o.concept_id=1484 and o.voided=0 and e.voided=0"; sqlSelect += " ) emtarv on emtarv.encounter_id=e.encounter_id"; sqlSelect += " where e.encounter_type=7 and p.nid is not null and e.voided=0 and nome.nome is not null and dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_mae(nid) values('" + readerSource.GetString(0) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); insertUtil.updateStringValue("t_mae", "nome", commandTarge, readerSource, 1, "nid", readerSource.GetString(0)); insertUtil.updateNumericValue("t_mae", "idade", commandTarge, readerSource, 2, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "vivo", commandTarge, readerSource, 3, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "doente", commandTarge, readerSource, 4, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "doenca", commandTarge, readerSource, 5, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "codprofissao", commandTarge, readerSource, 6, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "resultadohiv", commandTarge, readerSource, 7, "nid", readerSource.GetString(0)); insertUtil.updateStringValue("t_mae", "emtarv", commandTarge, readerSource, 8, "nid", readerSource.GetString(0)); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar tabela T_MAE:" + e.Message); } }
public void exportAconselhamento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " criteriosmedicos.criteriosmedicos,"; sqlSelect += " conceitos.conceitos,"; sqlSelect += " interessado.interessado,"; sqlSelect += " confidente.confidente,"; sqlSelect += " apareceregularmente.apareceregularmente,"; sqlSelect += " riscopobreaderencia.riscopobreaderencia,"; sqlSelect += " regimetratamento.regimetratamento,"; sqlSelect += " prontotarv.prontotarv,"; sqlSelect += " prontotarv.datapronto,"; sqlSelect += " obs.obs"; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then 'SIM'"; sqlSelect += " when 1066 then 'NAO'"; sqlSelect += " end as criteriosmedicos"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1248 and o.voided=0 and e.voided=0 "; sqlSelect += " ) criteriosmedicos on criteriosmedicos.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as conceitos"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1729 and o.voided=0 and e.voided=0 "; sqlSelect += " ) conceitos on conceitos.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as interessado"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1736 and o.voided=0 and e.voided=0 "; sqlSelect += " ) interessado on interessado.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as confidente"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1739 and o.voided=0 and e.voided=0 "; sqlSelect += " ) confidente on confidente.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as apareceregularmente"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1743 and o.voided=0 and e.voided=0 "; sqlSelect += " ) apareceregularmente on apareceregularmente.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as riscopobreaderencia"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1749 and o.voided=0 and e.voided=0 "; sqlSelect += " ) riscopobreaderencia on riscopobreaderencia.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as regimetratamento"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1752 and o.voided=0 and e.voided=0 "; sqlSelect += " ) regimetratamento on regimetratamento.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as prontotarv,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then o.obs_datetime"; sqlSelect += " else null end as datapronto"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1756 and o.voided=0 and e.voided=0"; sqlSelect += " ) prontotarv on prontotarv.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_text as obs"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1757 and o.voided=0 and e.voided=0"; sqlSelect += " ) obs on obs.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and e.voided=0 and"; sqlSelect += " p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_aconselhamento(nid) values("; sqlInsert += "'" + readerSource.GetString(2) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); insertUtil.updateStringValue("t_aconselhamento", "criteriosmedicos", commandTarge, readerSource, 3, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "conceitos", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "interessado", commandTarge, readerSource, 5, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "confidente", commandTarge, readerSource, 6, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "apareceregularmente", commandTarge, readerSource, 7, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "riscopobreaderencia", commandTarge, readerSource, 8, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "regimetratamento", commandTarge, readerSource, 9, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "prontotarv", commandTarge, readerSource, 10, "idaconselhamento", idAconselhamento); insertUtil.updateDateValue("t_aconselhamento", "datapronto", commandTarge, readerSource, 11, "idaconselhamento", idAconselhamento); insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 12, "idaconselhamento", idAconselhamento); exportActividadeAconselhamento(otherSource, target, readerSource.GetString(1), idAconselhamento); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }