private void exportNumericObsPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'CD4' "; sqlSelect += " when 5497 then 'CD4' "; sqlSelect += " when 5085 then 'Tensão Arterial' "; sqlSelect += " when 5086 then 'Tensão Arterial' "; sqlSelect += " when 653 then 'AST' "; sqlSelect += " when 654 then 'ALT' "; sqlSelect += " when 1021 then 'LINFOCITO' "; sqlSelect += " when 952 then 'LINFOCITO' "; sqlSelect += " when 1022 then 'NEUTROFILO' "; sqlSelect += " when 1330 then 'NEUTROFILO' "; sqlSelect += " when 1024 then 'EOSINOFILO' "; sqlSelect += " when 1332 then 'EOSINOFILO' "; sqlSelect += " when 1025 then 'BASOFILO' "; sqlSelect += " when 1333 then 'BASOFILO' "; sqlSelect += " when 1023 then 'MONOCITO' "; sqlSelect += " when 1331 then 'MONOCITO' "; sqlSelect += " when 1017 then 'CMHC' "; sqlSelect += " when 851 then 'VCM' "; sqlSelect += " when 21 then 'Hemoglobina' "; sqlSelect += " when 1018 then 'HGM' "; sqlSelect += " when 678 then 'WBC' "; sqlSelect += " when 679 then 'RBC' "; sqlSelect += " when 5283 then 'INDICE DE KARNOFSKY' "; sqlSelect += " when 5314 then 'Períneo' "; sqlSelect += " when 1342 then 'IMC' "; sqlSelect += " when 5088 then 'Temperatura' "; sqlSelect += " when 5089 then 'Peso' "; sqlSelect += " when 5090 then 'Altura' "; sqlSelect += " when 5087 then 'Pulmonar - Auscultação' "; sqlSelect += " when 5242 then 'Pulmonar - Respiracao' "; sqlSelect += " when 729 then 'Plaquetas' "; sqlSelect += " when 1015 then 'HTC' "; sqlSelect += " when 5195 then 'Esplenomegáglia' "; sqlSelect += " else cn.name end as codobservacao, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'PERCENTUAL' "; sqlSelect += " when 5497 then 'ABSOLUTO' "; sqlSelect += " when 5085 then 'SUPERIOR' "; sqlSelect += " when 5086 then 'INFERIOR' "; sqlSelect += " when 1021 then 'PERCENTUAL' "; sqlSelect += " when 952 then 'ABSOLUTO' "; sqlSelect += " when 1022 then 'PERCENTUAL' "; sqlSelect += " when 1330 then 'ABSOLUTO' "; sqlSelect += " when 1024 then 'PERCENTUAL' "; sqlSelect += " when 1332 then 'ABSOLUTO' "; sqlSelect += " when 1025 then 'PERCENTUAL' "; sqlSelect += " when 1333 then 'ABSOLUTO' "; sqlSelect += " when 1023 then 'PERCENTUAL' "; sqlSelect += " when 1331 then 'ABSOLUTO' "; sqlSelect += " else null end as codestado, "; sqlSelect += " o.value_numeric as valor, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as data "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and cn.concept_name_type='FULLY_SPECIFIED'"; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " WHERE e.encounter_type in (1,3) and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " c.datatype_id=1 and c.is_set=0 and p.datanasc is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_observacaopaciente(codobservacao,valor,nid,data) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(5) + "','" + readerSource.GetString(6) + "',cdate('" + readerSource.GetMySqlDateTime(7) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); if (!readerSource.IsDBNull(4)) { Int32 idObs = insertUtil.getMaxID(target, "t_observacaopaciente", "idobservacao"); commandTarge.CommandText = "Update t_observacaopaciente set codestado='" + readerSource.GetString(4) + "' where idobservacao=" + idObs; commandTarge.ExecuteNonQuery(); } } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_OBSERVACAOPACIENTE, NUMERICO (MODULO EXPORTTOBSERVACAOPACIENTE.CS): " + e.Message); } }
public void exportData(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = "Select p.patient_id,"; sqlSelect += "e.encounter_id,"; sqlSelect += "p.nid,"; sqlSelect += "e.encounter_datetime as datatarv,"; sqlSelect += "regime.codRegime,"; sqlSelect += "case o.value_coded"; sqlSelect += " when 1256 then 'Inicia'"; sqlSelect += " when 1257 then 'Manter'"; sqlSelect += " when 1259 then 'Alterar'"; sqlSelect += " when 1369 then 'Transfer de'"; sqlSelect += " when 1705 then 'Reiniciar'"; sqlSelect += " when 1708 then 'Saida'"; sqlSelect += " else 'OUTRO' end as tipotarv,"; sqlSelect += " proxima.dataproxima,"; sqlSelect += " aviada.qtdComp,"; sqlSelect += " saldo.qtdSaldo,"; sqlSelect += " outro.dataoutro"; sqlSelect += " from t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id and o.person_id=e.patient_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 792 then 'D4T+3TC+NVP'"; sqlSelect += " when 6110 then 'D4T20+3TC+NVP'"; sqlSelect += " when 1827 then 'D4T30+3TC+EFV'"; sqlSelect += " when 6103 then 'D4T+3TC+LPV'"; sqlSelect += " when 1651 then 'AZT+3TC+NVP'"; sqlSelect += " when 1703 then 'AZT+3TC+EFV'"; sqlSelect += " when 1702 then 'AZT+3TC+NFV'"; sqlSelect += " when 6100 then 'AZT+3TC+LPV'"; sqlSelect += " when 817 then 'AZT+3TC+ABC'"; sqlSelect += " when 6104 then 'ABC+3TC+EFV'"; sqlSelect += " when 6106 then 'ABC+3TC+LPV/r'"; sqlSelect += " when 6105 then 'ABC+3TC+NVP'"; sqlSelect += " when 6243 then 'TDF+3TC+NVP'"; sqlSelect += " when 6244 then 'AZT+3TC+RTV'"; sqlSelect += " when 1700 then 'AZT+DDl+NFV'"; sqlSelect += " when 633 then 'EFV'"; sqlSelect += " when 625 then 'D4T'"; sqlSelect += " when 631 then 'NVP'"; sqlSelect += " when 628 then '3TC'"; sqlSelect += " when 6107 then 'TDF+AZT+3TC+LPV/r'"; sqlSelect += " when 6236 then 'D4T+DDI+RTV-IP'"; sqlSelect += " when 1701 then 'ABC+DDI+NFV'"; sqlSelect += " else 'OUTROS' end as codRegime"; sqlSelect += " from encounter e inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and o.concept_id=1088 and e.encounter_type=18"; sqlSelect += " ) regime on regime.encounter_id=e.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_datetime as dataproxima"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=5096"; sqlSelect += " ) proxima on e.encounter_id=proxima.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_numeric as qtdComp"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1715"; sqlSelect += " ) aviada on e.encounter_id=aviada.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_numeric as qtdSaldo"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1713"; sqlSelect += " ) saldo on e.encounter_id=saldo.encounter_id"; sqlSelect += " left join ("; sqlSelect += " select e.encounter_id,"; sqlSelect += " o.value_datetime as dataoutro"; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=1190"; sqlSelect += " ) outro on e.encounter_id=outro.encounter_id"; sqlSelect += " where e.voided=0 and e.encounter_type=18 and o.voided=0 and o.concept_id=1255 and o.value_coded<>1708 and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; sqlSelect += " and p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and p.nid is not null and regime.codregime is not null"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_tarv(nid,datatarv,tipotarv,codregime) values("; sqlInsert += "'" + readerSource.GetString(2) + "',cdate('" + readerSource.GetMySqlDateTime(3) + "'),'" + readerSource.GetString(5) + "','" + readerSource.GetString(4) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idTarv = insertUtil.getMaxID(target, "t_tarv", "idtarv"); //insertUtil.updateStringValue("t_tarv", "codregime", commandTarge, readerSource, 4, "encounter_id", readerSource.GetString(1)); insertUtil.updateDateValue("t_tarv", "dataproxima", commandTarge, readerSource, 6, "idtarv", idTarv); insertUtil.updateNumericValue("t_tarv", "QtdComp", commandTarge, readerSource, 7, "idtarv", idTarv); insertUtil.updateNumericValue("t_tarv", "QtdSaldo", commandTarge, readerSource, 8, "idtarv", idTarv); insertUtil.updateDateValue("t_tarv", "dataoutroservico", commandTarge, readerSource, 9, "idtarv", idTarv); } commandTarge.CommandText = "update t_tarv set dias=QtdComp/2 where QtdComp is not null"; commandTarge.ExecuteNonQuery(); } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_TARV: " + e.Message); } }
public void exportDataHistEstadoPaciente(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " select p.patient_id,"; sqlSelect+=" e.encounter_id, "; sqlSelect+=" p.nid, "; sqlSelect+=" case o.value_coded "; sqlSelect+=" when 1707 then 'Abandono' "; sqlSelect+=" when 1706 then 'Transferido para' "; sqlSelect+=" when 1366 then 'Morte' "; sqlSelect+=" when 1704 then 'HIV Negativo' "; sqlSelect+=" when 1709 then 'Suspender Tarv' "; sqlSelect+=" else 'Outro' end as codestado, "; sqlSelect+=" e.encounter_datetime as dataestado, "; sqlSelect+=" destino.destinopaciente "; sqlSelect+=" from t_paciente p "; sqlSelect+=" inner join encounter e on p.patient_id=e.patient_id "; sqlSelect+=" inner join obs o on o.encounter_id=e.encounter_id and o.person_id=e.patient_id "; sqlSelect+=" left join ( "; sqlSelect+=" select e.encounter_id, "; sqlSelect+=" o.value_text as destinopaciente "; sqlSelect+=" from encounter e "; sqlSelect+=" inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect+=" where e.voided=0 and o.voided=0 and e.encounter_type=18 and o.concept_id=2059 "; sqlSelect+=" ) destino on e.encounter_id=destino.encounter_id "; sqlSelect+=" where e.encounter_type in (18,6,9) and o.concept_id in (1708,6138) and o.voided=0 and e.voided=0 and p.nid is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_histestadopaciente(nid,codestado,dataestado) values("; sqlInsert += "'" + readerSource.GetString(2) + "','" + readerSource.GetString(3) + "',cdate('" + readerSource.GetMySqlDateTime(4) + "'))"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 id = insertUtil.getMaxID(target, "t_histestadopaciente", "ID"); insertUtil.updateStringValue("t_histestadopaciente", "destinopaciente", commandTarge, readerSource, 5, "ID", id); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_HISTESTADOPACIENTE (MODULO EXPORTTTARV.CS): " + e.Message); } }
public void exportDataTSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate,MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " YEAR(e.encounter_datetime)-YEAR(p.datanasc) as idade,"; sqlSelect += " if(YEAR(e.encounter_datetime)-YEAR(p.datanasc)<2,PERIOD_DIFF(DATE_FORMAT(e.encounter_datetime,'%Y%m'),DATE_FORMAT(p.datanasc,'%Y%m')),null) meses,"; sqlSelect += " estadohiv.estadohiv,"; sqlSelect += " e.encounter_datetime as dataseguimento,"; sqlSelect += " estadiooms.estadiooms,"; sqlSelect += " dataproximaconsulta.dataproximaconsulta,"; sqlSelect += " Gravidez.Gravidez,"; sqlSelect += " outrodiagnostico.diagnostico,"; sqlSelect += " gravidez1.gravida "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 703 then 'Positivo'"; sqlSelect += " when 664 then 'Negativo'"; sqlSelect += " when 1138 then 'Indeterminado'"; sqlSelect += " else 'OUTRO' end as estadohiv"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=9 and o.concept_id=1040 and o.voided=0 and e.voided=0 "; sqlSelect += " ) estadohiv on estadohiv.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1204 then 'I'"; sqlSelect += " when 1205 then 'II'"; sqlSelect += " when 1206 then 'III'"; sqlSelect += " when 1207 then 'IV'"; sqlSelect += " else 'OUTRO' end as estadiooms"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=5356 and o.voided=0 and e.voided=0"; sqlSelect += " ) estadiooms on estadiooms.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_datetime as dataproximaconsulta"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id=1410 and o.voided=0 and e.voided=0"; sqlSelect += " ) dataproximaconsulta on dataproximaconsulta.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " o.encounter_id,"; sqlSelect += " o.value_numeric as Gravidez"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type=6 and o.concept_id= 5992 and o.voided=0 and e.voided=0"; sqlSelect += " ) Gravidez on Gravidez.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " o.encounter_id, "; sqlSelect += " o.value_text as diagnostico "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id= 1649 and o.voided=0 and e.voided=0 "; sqlSelect += " ) outrodiagnostico on outrodiagnostico.encounter_id=e.encounter_id "; sqlSelect += " left join ( SELECT o.person_id, "; sqlSelect += " o.encounter_id, "; sqlSelect += " 'SIM' as gravida "; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and o.concept_id= 1982 and o.voided=0 and e.voided=0 and o.value_coded=44"; sqlSelect += " ) gravidez1 on gravidez1.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type in (6,9) and "; sqlSelect += " e.voided=0 and "; sqlSelect += " p.nid is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura between '"+startDateMySQL+"' and '"+endDateMySQL+"' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_seguimento(nid,idade,dataseguimento,tiposeguimento) values("; sqlInsert += "'" + readerSource.GetString(2) + "'," + readerSource.GetString(3) + ",cdate('" + readerSource.GetMySqlDateTime(6) + "'),'Seguinte')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); insertUtil.updateNumericValue("t_seguimento", "meses", commandTarge, readerSource, 4, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "estadohiv", commandTarge, readerSource, 5, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "estadiooms", commandTarge, readerSource, 7, "idseguimento", idSeguimento); insertUtil.updateDateValue("t_seguimento", "dataproximaconsulta", commandTarge, readerSource, 8, "idseguimento", idSeguimento); insertUtil.updateNumericValue("t_seguimento", "Gravidez", commandTarge, readerSource, 9, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "Observacao", commandTarge, readerSource, 10, "idseguimento", idSeguimento); insertUtil.updateStringValue("t_seguimento", "gravida", commandTarge, readerSource, 11, "idseguimento", idSeguimento); exportInfeccoesOportunistasSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); exportTratamentoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); exportDiagnosticoSeguimento(otherSource, target, readerSource.GetString(1), idSeguimento); } } readerSource.Close(); UpdateScreenTB(source, target); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_SEGUIMENTO (MODULO EXPORTTSEGUIMENTO.CS): " + e.Message); } }
private void exportTActividade(MySqlConnection source, OleDbConnection target, Int32 idGaac) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String sqlSelect = " SELECT p.nid,gm.start_date,gm.end_date,rl.name as reason,g.gaac_identifier, "; sqlSelect += " gm.description "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join gaac_member gm on p.patient_id=gm.member_id "; sqlSelect += " inner join gaac g on g.gaac_id=gm.gaac_id "; sqlSelect += " left join gaac_reason_leaving_type rl on rl.gaac_reason_leaving_type_id=gm.reason_leaving_type "; sqlSelect += " WHERE g.voided=0 and gm.voided=0 and gm.gaac_id=" + idGaac; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { // Int32 idSeguimento = insertUtil.getMaxID(target, "t_seguimento", "idseguimento"); while (readerSource.Read()) { sqlInsert = "Insert into t_gaac_actividades(nid,dataInscricao,numGAAC) values("; sqlInsert += "'" + readerSource.GetString(0) + "','" + readerSource.GetMySqlDateTime(1) + "'," + Convert.ToInt32( readerSource.GetString(4)) + ")"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 maxActividade = insertUtil.getMaxID(target, "t_gaac_actividades", "ID"); insertUtil.updateStringValue("t_gaac_actividades", "motivo", commandTarge, readerSource, 3, "ID", maxActividade); insertUtil.updateStringValue("t_gaac_actividades", "observacao", commandTarge, readerSource, 5, "ID", maxActividade); insertUtil.updateDateValue("t_gaac_actividades", "dataSaida", commandTarge, readerSource, 2, "ID", maxActividade); } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_GAAC_ACTIVIDADES (Modulo ExportTGaac.cs " + e.Message); } }
private void exportNumericLabResult(MySqlConnection source, OleDbConnection target, DateTime startDate,DateTime endDate) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id, "; sqlSelect += " e.encounter_id, "; sqlSelect += " c.concept_id, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'CD4' "; sqlSelect += " when 5497 then 'CD4' "; sqlSelect += " when 653 then 'AST' "; sqlSelect += " when 654 then 'ALT' "; sqlSelect += " when 1021 then 'L' "; sqlSelect += " when 952 then 'L' "; sqlSelect += " when 1022 then 'N' "; sqlSelect += " when 1330 then 'N' "; sqlSelect += " when 1024 then 'E' "; sqlSelect += " when 1332 then 'E' "; sqlSelect += " when 1025 then 'B' "; sqlSelect += " when 1333 then 'B' "; sqlSelect += " when 1023 then 'M' "; sqlSelect += " when 1331 then 'M' "; sqlSelect += " when 1017 then 'CMHC' "; sqlSelect += " when 851 then 'VGM' "; sqlSelect += " when 21 then 'Hemoglobina' "; sqlSelect += " when 1018 then 'HGM' "; sqlSelect += " when 678 then 'WBC' "; sqlSelect += " when 679 then 'RBC' "; sqlSelect += " when 1015 then 'HTC' "; sqlSelect += " when 729 then 'Plaquetas' "; sqlSelect += " when 1016 then 'RDW' "; sqlSelect += " when 1307 then 'MPV' "; sqlSelect += " when 1011 then 'CK' "; sqlSelect += " when 857 then 'Ureia' "; sqlSelect += " when 790 then 'Creatinina' "; sqlSelect += " when 848 then 'Albumina' "; sqlSelect += " when 655 then 'Bilirrubina T' "; sqlSelect += " when 887 then 'GLC' "; sqlSelect += " when 1297 then 'Bilirrubina C' "; sqlSelect += " when 1299 then 'Amilase' "; sqlSelect += " when 855 then 'VS' "; sqlSelect += " else cn.name end as codexame, "; sqlSelect += " p.nid, "; sqlSelect += " o.obs_datetime as dataresultado, "; sqlSelect += " case c.concept_id "; sqlSelect += " when 730 then 'PERCENTUAL' "; sqlSelect += " when 5497 then 'ABSOLUTO' "; sqlSelect += " when 1021 then 'PERCENTUAL' "; sqlSelect += " when 952 then 'ABSOLUTO' "; sqlSelect += " when 1022 then 'PERCENTUAL' "; sqlSelect += " when 1330 then 'ABSOLUTO' "; sqlSelect += " when 1024 then 'PERCENTUAL' "; sqlSelect += " when 1332 then 'ABSOLUTO' "; sqlSelect += " when 1025 then 'PERCENTUAL' "; sqlSelect += " when 1333 then 'ABSOLUTO' "; sqlSelect += " when 1023 then 'PERCENTUAL' "; sqlSelect += " when 1331 then 'ABSOLUTO' "; sqlSelect += " else null end as codparametro, "; sqlSelect += " o.value_numeric as resultado, "; sqlSelect += " pedido.data_pedido "; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on p.patient_id=e.patient_id "; sqlSelect += " inner join obs o on o.encounter_id=e.encounter_id and e.patient_id=o.person_id "; sqlSelect += " inner join concept_name cn on cn.concept_id=o.concept_id and cn.locale='pt' and "; sqlSelect += " cn.concept_name_type='FULLY_SPECIFIED' "; sqlSelect += " inner join concept c on c.concept_id=o.concept_id "; sqlSelect += " left join ( "; sqlSelect += " select e.encounter_id, "; sqlSelect += " o.value_datetime as data_pedido "; sqlSelect += " from encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " where e.voided=0 and o.voided=0 and e.encounter_type=13 and o.concept_id=6246 "; sqlSelect += " ) pedido on e.encounter_id=pedido.encounter_id "; sqlSelect += " WHERE e.encounter_type=13 and "; sqlSelect += " o.voided=0 and cn.voided=0 and e.voided=0 and p.nid is not null and p.datanasc is not null and "; sqlSelect += " c.datatype_id=1 and c.is_set=0 and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_resultadoslaboratorio(codexame,nid,dataresultado,resultado) values("; sqlInsert += "'" + readerSource.GetString(3) + "','" + readerSource.GetString(4) + "',cdate('" + readerSource.GetString(5) + "'),'" + readerSource.GetDouble(7) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idLab = insertUtil.getMaxID(target, "t_resultadoslaboratorio", "idresultado"); if (!readerSource.IsDBNull(6)) { commandTarge.CommandText = "Update t_resultadoslaboratorio set codparametro='" + readerSource.GetString(6) + "' where idresultado=" + idLab; commandTarge.ExecuteNonQuery(); } if (!readerSource.IsDBNull(8)) { commandTarge.CommandText = "Update t_resultadoslaboratorio set datapedido=cdate('" + readerSource.GetString(8) + "') where idresultado=" + idLab; commandTarge.ExecuteNonQuery(); } } } readerSource.Close(); //otherSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_RESULTADOLABORATORIO, NUMERICO (MODULO EXPORTTRESULTADOLAB.CS): " + e.Message); } }
public void exportAconselhamentoSeguimento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " e.encounter_datetime,"; sqlSelect += " p.nid,"; sqlSelect += " resumo.obs"; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_text as obs"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type=24 and o.concept_id=1553 and o.voided=0 and e.voided=0"; sqlSelect += " ) resumo on resumo.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type=24 and e.voided=0 and"; sqlSelect += " p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_aconselhamento(nid) values("; sqlInsert += "'" + readerSource.GetString(3) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento); exportActividadeAconselhamentoSeguimento(otherSource, target, readerSource.GetString(1), idAconselhamento); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTOSEGUIMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }
public void exportAconselhamento(MySqlConnection source, OleDbConnection target, DateTime startDate, DateTime endDate, MySqlConnection otherSource) { try { MySqlCommand commandSource = new MySqlCommand(); OleDbCommand commandTarge = new OleDbCommand(); MySqlDataReader readerSource; String sqlInsert; InsertUtils insertUtil = new InsertUtils(); String startDateMySQL = startDate.Year + "/" + startDate.Month + "/" + startDate.Day; String endDateMySQL = endDate.Year + "/" + endDate.Month + "/" + endDate.Day; String sqlSelect = " SELECT p.patient_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " p.nid,"; sqlSelect += " criteriosmedicos.criteriosmedicos,"; sqlSelect += " conceitos.conceitos,"; sqlSelect += " interessado.interessado,"; sqlSelect += " confidente.confidente,"; sqlSelect += " apareceregularmente.apareceregularmente,"; sqlSelect += " riscopobreaderencia.riscopobreaderencia,"; sqlSelect += " regimetratamento.regimetratamento,"; sqlSelect += " prontotarv.prontotarv,"; sqlSelect += " prontotarv.datapronto,"; sqlSelect += " obs.obs"; sqlSelect += " FROM t_paciente p "; sqlSelect += " inner join encounter e on e.patient_id=p.patient_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then 'SIM'"; sqlSelect += " when 1066 then 'NAO'"; sqlSelect += " end as criteriosmedicos"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1248 and o.voided=0 and e.voided=0 "; sqlSelect += " ) criteriosmedicos on criteriosmedicos.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as conceitos"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1729 and o.voided=0 and e.voided=0 "; sqlSelect += " ) conceitos on conceitos.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as interessado"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1736 and o.voided=0 and e.voided=0 "; sqlSelect += " ) interessado on interessado.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as confidente"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1739 and o.voided=0 and e.voided=0 "; sqlSelect += " ) confidente on confidente.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as apareceregularmente"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id"; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1743 and o.voided=0 and e.voided=0 "; sqlSelect += " ) apareceregularmente on apareceregularmente.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as riscopobreaderencia"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1749 and o.voided=0 and e.voided=0 "; sqlSelect += " ) riscopobreaderencia on riscopobreaderencia.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as regimetratamento"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1752 and o.voided=0 and e.voided=0 "; sqlSelect += " ) regimetratamento on regimetratamento.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then true"; sqlSelect += " when 1066 then false"; sqlSelect += " end as prontotarv,"; sqlSelect += " case o.value_coded"; sqlSelect += " when 1065 then o.obs_datetime"; sqlSelect += " else null end as datapronto"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1756 and o.voided=0 and e.voided=0"; sqlSelect += " ) prontotarv on prontotarv.encounter_id=e.encounter_id"; sqlSelect += " left join ( SELECT o.person_id,"; sqlSelect += " e.encounter_id,"; sqlSelect += " o.value_text as obs"; sqlSelect += " FROM encounter e "; sqlSelect += " inner join obs o on e.encounter_id=o.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and o.concept_id=1757 and o.voided=0 and e.voided=0"; sqlSelect += " ) obs on obs.encounter_id=e.encounter_id "; sqlSelect += " WHERE e.encounter_type in (19,29) and e.voided=0 and"; sqlSelect += " p.nid is not null and "; sqlSelect += " p.datanasc is not null and "; sqlSelect += " p.dataabertura is not null and "; sqlSelect += " p.dataabertura between '" + startDateMySQL + "' and '" + endDateMySQL + "' and "; sqlSelect += " e.encounter_datetime between '" + startDateMySQL + "' and '" + endDateMySQL + "'"; commandTarge.Connection = target; commandTarge.CommandType = CommandType.Text; commandSource.Connection = source; commandSource.CommandType = CommandType.Text; commandSource.CommandText = sqlSelect; readerSource = commandSource.ExecuteReader(); if (readerSource.HasRows) { while (readerSource.Read()) { sqlInsert = "Insert into t_aconselhamento(nid) values("; sqlInsert += "'" + readerSource.GetString(2) + "')"; commandTarge.CommandText = sqlInsert; commandTarge.ExecuteNonQuery(); Int32 idAconselhamento = insertUtil.getMaxID(target, "t_aconselhamento", "idaconselhamento"); insertUtil.updateStringValue("t_aconselhamento", "criteriosmedicos", commandTarge, readerSource, 3, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "conceitos", commandTarge, readerSource, 4, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "interessado", commandTarge, readerSource, 5, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "confidente", commandTarge, readerSource, 6, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "apareceregularmente", commandTarge, readerSource, 7, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "riscopobreaderencia", commandTarge, readerSource, 8, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "regimetratamento", commandTarge, readerSource, 9, "idaconselhamento", idAconselhamento); insertUtil.updateBooleanValue("t_aconselhamento", "prontotarv", commandTarge, readerSource, 10, "idaconselhamento", idAconselhamento); insertUtil.updateDateValue("t_aconselhamento", "datapronto", commandTarge, readerSource, 11, "idaconselhamento", idAconselhamento); insertUtil.updateStringValue("t_aconselhamento", "obs", commandTarge, readerSource, 12, "idaconselhamento", idAconselhamento); exportActividadeAconselhamento(otherSource, target, readerSource.GetString(1), idAconselhamento); } } readerSource.Close(); } catch (Exception e) { MessageBox.Show("Houve erro ao Exportar T_ACONSELHAMENTO (MODULO EXPORTTACONSELHAMENTO.CS): " + e.Message); } }