Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <channel>
- <id>9561488e-4860-4132-9de8-de8322cdcc58</id>
- <name>to File DR Export file 02 Provider</name>
- <description></description>
- <enabled>true</enabled>
- <version>2.2.1.5861</version>
- <lastModified>
- <time>1382654895099</time>
- <timezone>America/Los_Angeles</timezone>
- </lastModified>
- <revision>15</revision>
- <sourceConnector>
- <name>sourceConnector</name>
- <properties>
- <property name="DataType">Database Reader</property>
- <property name="URL">jdbc:postgresql://host:port/dbname</property>
- <property name="ack"></property>
- <property name="ackScript">// This update script will be executed once for ever result returned from the above query.
- var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://host:port/dbname','','');
- dbConn.close();</property>
- <property name="driver">org.postgresql.Driver</property>
- <property name="host">query</property>
- <property name="password"></property>
- <property name="pollingFrequency">5000000</property>
- <property name="pollingType">interval</property>
- <property name="processResultsInOrder">1</property>
- <property name="query"></property>
- <property name="script">var dbConn = getMRDBConnection();
- var result = dbConn.executeCachedQuery("select p.provider_key as providerid, pcp.provider_alias_type_key as providertype, pcp.alias as provider_alias_npi, f.id as locationID, ft.label as locationtype, n.last as lastname, n.first as firstname, n.middle as middlename, n.suffix as suffix, n.prefix as prefix, n.degree as providertype, " +
- "p.gender as gender, com_email.address as email, com_phone.address as phone, com_phone.communication_type_key as comtypekey from provider p " +
- "left outer join name n on p.name_key=n.name_key " +
- "LEFT OUTER JOIN provider_alias pcp on (p.provider_key=pcp.provider_key AND pcp.provider_alias_type_key=4) " +
- "left outer JOIN facility f on (pcp.facility_key=f.facility_key) " +
- "LEFT OUTER JOIN facility_type ft on (f.facility_type_key=ft.facility_type_key) " +
- "left outer join communication com_email on (com_email.communication_key=p.email_contact_key and com_email.code_key=16) " +
- "left outer join communication com_phone on (com_phone.communication_key=p.email_contact_key and com_phone.code_key=15) " +
- "order by p.provider_ikey limit 10 ");
- dbConn.close();
- // You may access this result below with $('column_name')
- return result;</property>
- <property name="useAck">0</property>
- <property name="useScript">1</property>
- <property name="username"></property>
- </properties>
- <transformer>
- <steps>
- <step>
- <sequenceNumber>0</sequenceNumber>
- <name>Initialize the Export DB variables</name>
- <script>var PracticeCode = "";
- var SourceCode = "";
- var ProviderID = "";
- var ProviderAlternateID = "";
- var ProviderIndividualNPI = "";
- var ProviderPrimaryLocationCode = "";
- var ProviderDegree = "";
- var LevelIndicator = "";
- var FirstName = "";
- var MiddleName = "";
- var LastName = "";
- var Suffix = "";
- var Prefix = "";
- var SpecialtyCode1 = "";
- var SpecialtyLabel1 = "";
- var SpecialtyCode1TargetCode = "";
- var SpecialtyCode1TargetLabel = "";
- var SpecialtyCode2 = "";
- var SpecialtyLabel2 = "";
- var SpecialtyCode2TargetCode = "";
- var SpecialtyCode2TargetLabel = "";
- var SpecialtyCode3 = "";
- var SpecialtyLabel3 = "";
- var SpecialtyCode3TargetCode = "";
- var SpecialtyCode3TargetLabel = "";
- var GenderCode = "";
- var GenderLabel = "";
- var GenderTargetCode = "";
- var GenderTargetLabel = "";
- var EmailAddress = "";
- var OfficePhone = "";
- var OtherPhone = "";
- var ActionCode = "";
- var LastUpdateDate = "";
- var LastUpdateTime = "";
- //map all the values from the original source Mirth Results query to the variables
- ProviderID = msg['providerid'].toString();
- ProviderIndividualNPI = msg['provider_alias_npi'].toString();
- SourceCode = msg['locationid'].toString();
- PracticeCode = msg['locationid'].toString();
- LastName = msg['lastname'].toString();
- FirstName = msg['firstname'].toString();
- MiddleName = msg['middlename'].toString();
- Suffix = msg['suffix'].toString();
- Prefix = msg['prefix'].toString();
- GenderLabel = msg['gender'].toString();
- EmailAddress = msg['email'].toString();
- OfficePhone = msg['phone'].toString();</script>
- <type>JavaScript</type>
- <data class="map">
- <entry>
- <string>Script</string>
- <string>var PracticeCode = "";
- var SourceCode = "";
- var ProviderID = "";
- var ProviderAlternateID = "";
- var ProviderIndividualNPI = "";
- var ProviderPrimaryLocationCode = "";
- var ProviderDegree = "";
- var LevelIndicator = "";
- var FirstName = "";
- var MiddleName = "";
- var LastName = "";
- var Suffix = "";
- var Prefix = "";
- var SpecialtyCode1 = "";
- var SpecialtyLabel1 = "";
- var SpecialtyCode1TargetCode = "";
- var SpecialtyCode1TargetLabel = "";
- var SpecialtyCode2 = "";
- var SpecialtyLabel2 = "";
- var SpecialtyCode2TargetCode = "";
- var SpecialtyCode2TargetLabel = "";
- var SpecialtyCode3 = "";
- var SpecialtyLabel3 = "";
- var SpecialtyCode3TargetCode = "";
- var SpecialtyCode3TargetLabel = "";
- var GenderCode = "";
- var GenderLabel = "";
- var GenderTargetCode = "";
- var GenderTargetLabel = "";
- var EmailAddress = "";
- var OfficePhone = "";
- var OtherPhone = "";
- var ActionCode = "";
- var LastUpdateDate = "";
- var LastUpdateTime = "";
- //map all the values from the original source Mirth Results query to the variables
- ProviderID = msg['providerid'].toString();
- ProviderIndividualNPI = msg['provider_alias_npi'].toString();
- SourceCode = msg['locationid'].toString();
- PracticeCode = msg['locationid'].toString();
- LastName = msg['lastname'].toString();
- FirstName = msg['firstname'].toString();
- MiddleName = msg['middlename'].toString();
- Suffix = msg['suffix'].toString();
- Prefix = msg['prefix'].toString();
- GenderLabel = msg['gender'].toString();
- EmailAddress = msg['email'].toString();
- OfficePhone = msg['phone'].toString();</string>
- </entry>
- </data>
- </step>
- <step>
- <sequenceNumber>1</sequenceNumber>
- <name>Additional DB Queries</name>
- <script>var dbConn = getMRDBConnection();
- var result = dbConn.executeCachedQuery("select p.provider_key as providerid, ce.code as specialtycode, ce.label as specialtylabel, con.code as specialtytargetcode, con.label as specialtytargetlabel, prov.provideralias " +
- "from provider p " +
- "left outer join ( select p.provider_key, array_to_string(array_agg(pcp.alias), '^') AS provideralias " +
- " from provider p LEFT OUTER JOIN provider_alias pcp on (p.provider_key=pcp.provider_key AND (pcp.provider_alias_type_key != 3 and pcp.provider_alias_type_key != 4 and pcp.provider_alias_type_key != 9)) " +
- " group by p.provider_key ) prov on (prov.provider_key=p.provider_key) " +
- "left outer join provider_specialty ps on (p.provider_key=ps.provider_key) " +
- "left outer join specialty s on (ps.specialty_key=s.specialty_key) " +
- "left outer join coded_element ce on (ce.coded_element_key=s.specialty_code_key) " +
- "left outer join concept con on (con.concept_key=ce.concept_key) " +
- "LEFT OUTER JOIN provider_alias pcp on (p.provider_key=pcp.provider_key AND (pcp.provider_alias_type_key != 2 and pcp.provider_alias_type_key != 9)) " +
- "where p.provider_key = '" + msg['providerid'].toString() + "'");
- var count = 0;
- while(result.next())
- {
- if (count == 0)
- {
- SpecialtyCode1 = result.getString(2);
- SpecialtyLabel1 = result.getString(3);
- SpecialtyCode1TargetCode = result.getString(4);
- SpecialtyCode1TargetLabel = result.getString(5);
- ProviderAlternateID = result.getString(6);
- }
- if (count == 1)
- {
- SpecialtyCode2 = result.getString(2);
- SpecialtyLabel2 = result.getString(3);
- SpecialtyCode2TargetCode = result.getString(4);
- SpecialtyCode2TargetLabel = result.getString(5);
- }
- if (count == 2)
- {
- SpecialtyCode3 = result.getString(2);
- SpecialtyLabel3 = result.getString(3);
- SpecialtyCode3TargetCode = result.getString(4);
- SpecialtyCode3TargetLabel = result.getString(5);
- }
- count++;
- /*
- if (result.getString(6) != "")
- {
- if (ProviderAlternateID != "")
- {
- ProviderAlternateID = result.getString(6);
- }
- else
- {
- ProviderAlternateID += ProviderAlternateID + "^" + result.getString(6);
- }
- }
- */
- }</script>
- <type>JavaScript</type>
- <data class="map">
- <entry>
- <string>Script</string>
- <string>var dbConn = getMRDBConnection();
- var result = dbConn.executeCachedQuery("select p.provider_key as providerid, ce.code as specialtycode, ce.label as specialtylabel, con.code as specialtytargetcode, con.label as specialtytargetlabel, prov.provideralias " +
- "from provider p " +
- "left outer join ( select p.provider_key, array_to_string(array_agg(pcp.alias), '^') AS provideralias " +
- " from provider p LEFT OUTER JOIN provider_alias pcp on (p.provider_key=pcp.provider_key AND (pcp.provider_alias_type_key != 3 and pcp.provider_alias_type_key != 4 and pcp.provider_alias_type_key != 9)) " +
- " group by p.provider_key ) prov on (prov.provider_key=p.provider_key) " +
- "left outer join provider_specialty ps on (p.provider_key=ps.provider_key) " +
- "left outer join specialty s on (ps.specialty_key=s.specialty_key) " +
- "left outer join coded_element ce on (ce.coded_element_key=s.specialty_code_key) " +
- "left outer join concept con on (con.concept_key=ce.concept_key) " +
- "LEFT OUTER JOIN provider_alias pcp on (p.provider_key=pcp.provider_key AND (pcp.provider_alias_type_key != 2 and pcp.provider_alias_type_key != 9)) " +
- "where p.provider_key = '" + msg['providerid'].toString() + "'");
- var count = 0;
- while(result.next())
- {
- if (count == 0)
- {
- SpecialtyCode1 = result.getString(2);
- SpecialtyLabel1 = result.getString(3);
- SpecialtyCode1TargetCode = result.getString(4);
- SpecialtyCode1TargetLabel = result.getString(5);
- ProviderAlternateID = result.getString(6);
- }
- if (count == 1)
- {
- SpecialtyCode2 = result.getString(2);
- SpecialtyLabel2 = result.getString(3);
- SpecialtyCode2TargetCode = result.getString(4);
- SpecialtyCode2TargetLabel = result.getString(5);
- }
- if (count == 2)
- {
- SpecialtyCode3 = result.getString(2);
- SpecialtyLabel3 = result.getString(3);
- SpecialtyCode3TargetCode = result.getString(4);
- SpecialtyCode3TargetLabel = result.getString(5);
- }
- count++;
- /*
- if (result.getString(6) != "")
- {
- if (ProviderAlternateID != "")
- {
- ProviderAlternateID = result.getString(6);
- }
- else
- {
- ProviderAlternateID += ProviderAlternateID + "^" + result.getString(6);
- }
- }
- */
- }</string>
- </entry>
- </data>
- </step>
- <step>
- <sequenceNumber>2</sequenceNumber>
- <name>Extract the Staging DB</name>
- <script>//creating a new connection to the Staging table to extract data
- /*
- var dbConn2 = getStgImportDBConnection();
- var result = dbConn2.executeCachedQuery("select * from stg_loc where locationCode='" + locationCode + "'");
- while(result.next())
- {
- actionCode = result.getString(13);
- lastUpdateDate = result.getString(14);
- lastUpdateTime = result.getString(15);
- logger.info("value of actionCode is " + actionCode);
- logger.info("value of lastUpdateDate is " + lastUpdateDate);
- logger.info("value of lastUpdateTime is " + lastUpdateTime);
- }
- dbConn2.close();
- */</script>
- <type>JavaScript</type>
- <data class="map">
- <entry>
- <string>Script</string>
- <string>//creating a new connection to the Staging table to extract data
- /*
- var dbConn2 = getStgImportDBConnection();
- var result = dbConn2.executeCachedQuery("select * from stg_loc where locationCode='" + locationCode + "'");
- while(result.next())
- {
- actionCode = result.getString(13);
- lastUpdateDate = result.getString(14);
- lastUpdateTime = result.getString(15);
- logger.info("value of actionCode is " + actionCode);
- logger.info("value of lastUpdateDate is " + lastUpdateDate);
- logger.info("value of lastUpdateTime is " + lastUpdateTime);
- }
- dbConn2.close();
- */</string>
- </entry>
- </data>
- </step>
- <step>
- <sequenceNumber>3</sequenceNumber>
- <name>Populate the Export DB</name>
- <script>var columns = "practicecode, sourcecode, providerid, provideralternateid, providerindividualnpi, " +
- "providerprimarylocationcode, providerdegree, levelindicator, firstname, middlename, " +
- "lastname, suffix, prefix, specialtycode1, specialtylabel1, " +
- "specialtycode1targetcode, specialtylabel1targetlabel, specialtycode2, specialtylabel2, specialtycode2targetcode, " +
- "specialtylabel2targetlabel, specialtycode3, specialtylabel3, specialtycode3targetcode, specialtylabel3targetlabel, " +
- "gendercode, genderlabel, gendertargetcode, gendertargetlabel, emailaddress, " +
- "officephone, otherphone, actioncode, lastupdatedate, lastupdatetime";
- var query = "INSERT INTO stg_prov (" + columns + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
- //to_number(?,'99G999D9S') - to case a varchar to int
- var params = new java.util.ArrayList();
- params.add(PracticeCode);
- params.add(SourceCode);
- params.add(ProviderID);
- params.add(ProviderAlternateID);
- params.add(ProviderIndividualNPI);
- params.add(ProviderPrimaryLocationCode);
- params.add(ProviderDegree);
- params.add(LevelIndicator);
- params.add(FirstName);
- params.add(MiddleName);
- params.add(LastName);
- params.add(Suffix);
- params.add(Prefix);
- params.add(SpecialtyCode1);
- params.add(SpecialtyLabel1);
- params.add(SpecialtyCode1TargetCode);
- params.add(SpecialtyCode1TargetLabel);
- params.add(SpecialtyCode2);
- params.add(SpecialtyLabel2);
- params.add(SpecialtyCode2TargetCode);
- params.add(SpecialtyCode2TargetLabel);
- params.add(SpecialtyCode3);
- params.add(SpecialtyLabel3);
- params.add(SpecialtyCode3TargetCode);
- params.add(SpecialtyCode3TargetLabel);
- params.add(GenderCode);
- params.add(GenderLabel);
- params.add(GenderTargetCode);
- params.add(GenderTargetLabel);
- params.add(EmailAddress);
- params.add(OfficePhone);
- params.add(OtherPhone);
- params.add(ActionCode);
- params.add(LastUpdateDate);
- params.add(LastUpdateTime);
- var dbConn3 = getStgExportDBConnection();
- var result = dbConn3.executeUpdate(query, params);</script>
- <type>JavaScript</type>
- <data class="map">
- <entry>
- <string>Script</string>
- <string>var columns = "practicecode, sourcecode, providerid, provideralternateid, providerindividualnpi, " +
- "providerprimarylocationcode, providerdegree, levelindicator, firstname, middlename, " +
- "lastname, suffix, prefix, specialtycode1, specialtylabel1, " +
- "specialtycode1targetcode, specialtylabel1targetlabel, specialtycode2, specialtylabel2, specialtycode2targetcode, " +
- "specialtylabel2targetlabel, specialtycode3, specialtylabel3, specialtycode3targetcode, specialtylabel3targetlabel, " +
- "gendercode, genderlabel, gendertargetcode, gendertargetlabel, emailaddress, " +
- "officephone, otherphone, actioncode, lastupdatedate, lastupdatetime";
- var query = "INSERT INTO stg_prov (" + columns + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
- //to_number(?,'99G999D9S') - to case a varchar to int
- var params = new java.util.ArrayList();
- params.add(PracticeCode);
- params.add(SourceCode);
- params.add(ProviderID);
- params.add(ProviderAlternateID);
- params.add(ProviderIndividualNPI);
- params.add(ProviderPrimaryLocationCode);
- params.add(ProviderDegree);
- params.add(LevelIndicator);
- params.add(FirstName);
- params.add(MiddleName);
- params.add(LastName);
- params.add(Suffix);
- params.add(Prefix);
- params.add(SpecialtyCode1);
- params.add(SpecialtyLabel1);
- params.add(SpecialtyCode1TargetCode);
- params.add(SpecialtyCode1TargetLabel);
- params.add(SpecialtyCode2);
- params.add(SpecialtyLabel2);
- params.add(SpecialtyCode2TargetCode);
- params.add(SpecialtyCode2TargetLabel);
- params.add(SpecialtyCode3);
- params.add(SpecialtyLabel3);
- params.add(SpecialtyCode3TargetCode);
- params.add(SpecialtyCode3TargetLabel);
- params.add(GenderCode);
- params.add(GenderLabel);
- params.add(GenderTargetCode);
- params.add(GenderTargetLabel);
- params.add(EmailAddress);
- params.add(OfficePhone);
- params.add(OtherPhone);
- params.add(ActionCode);
- params.add(LastUpdateDate);
- params.add(LastUpdateTime);
- var dbConn3 = getStgExportDBConnection();
- var result = dbConn3.executeUpdate(query, params);</string>
- </entry>
- </data>
- </step>
- </steps>
- <inboundTemplate encoding="base64">PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+Cjxy
- ZXN1bHQ+CiAgICA8cHJvdmlkZXJpZD52YWx1ZTwvcHJvdmlkZXJpZD4KICAgIDxwcm92aWRlcnR5
- cGU+dmFsdWU8L3Byb3ZpZGVydHlwZT4KICAgIDxwcm92aWRlcl9hbGlhc19ucGk+dmFsdWU8L3By
- b3ZpZGVyX2FsaWFzX25waT4KICAgIDxsb2NhdGlvbmlkPnZhbHVlPC9sb2NhdGlvbmlkPgogICAg
- PGxvY2F0aW9udHlwZT52YWx1ZTwvbG9jYXRpb250eXBlPgogICAgPGxhc3RuYW1lPnZhbHVlPC9s
- YXN0bmFtZT4KICAgIDxmaXJzdG5hbWU+dmFsdWU8L2ZpcnN0bmFtZT4KICAgIDxtaWRkbGVuYW1l
- PnZhbHVlPC9taWRkbGVuYW1lPgogICAgPHN1ZmZpeD52YWx1ZTwvc3VmZml4PgogICAgPHByZWZp
- eD52YWx1ZTwvcHJlZml4PgogICAgPGdlbmRlcj52YWx1ZTwvZ2VuZGVyPgogICAgPGVtYWlsPnZh
- bHVlPC9lbWFpbD4KICAgIDxwaG9uZT52YWx1ZTwvcGhvbmU+CiAgICA8Y29tdHlwZWtleT52YWx1
- ZTwvY29tdHlwZWtleT4KPC9yZXN1bHQ+Cg==</inboundTemplate>
- <outboundTemplate encoding="base64"></outboundTemplate>
- <inboundProtocol>XML</inboundProtocol>
- <outboundProtocol>XML</outboundProtocol>
- <inboundProperties>
- <property name="stripNamespaces">true</property>
- </inboundProperties>
- <outboundProperties>
- <property name="stripNamespaces">true</property>
- </outboundProperties>
- </transformer>
- <filter>
- <rules/>
- </filter>
- <transportName>Database Reader</transportName>
- <mode>SOURCE</mode>
- <enabled>true</enabled>
- <version>2.2.1.5861</version>
- </sourceConnector>
- <destinationConnectors>
- <connector>
- <name>Destination 1</name>
- <properties>
- <property name="DataType">Channel Writer</property>
- <property name="host">sink</property>
- <property name="synchronised">0</property>
- <property name="template">${message.encodedData}</property>
- </properties>
- <transformer>
- <steps/>
- <inboundTemplate encoding="base64">PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+Cjxy
- ZXN1bHQ+CiAgICA8cHJvdmlkZXJpZD52YWx1ZTwvcHJvdmlkZXJpZD4KICAgIDxwcm92aWRlcnR5
- cGU+dmFsdWU8L3Byb3ZpZGVydHlwZT4KICAgIDxwcm92aWRlcl9hbGlhc19ucGk+dmFsdWU8L3By
- b3ZpZGVyX2FsaWFzX25waT4KICAgIDxsb2NhdGlvbmlkPnZhbHVlPC9sb2NhdGlvbmlkPgogICAg
- PGxvY2F0aW9udHlwZT52YWx1ZTwvbG9jYXRpb250eXBlPgogICAgPGxhc3RuYW1lPnZhbHVlPC9s
- YXN0bmFtZT4KICAgIDxmaXJzdG5hbWU+dmFsdWU8L2ZpcnN0bmFtZT4KICAgIDxtaWRkbGVuYW1l
- PnZhbHVlPC9taWRkbGVuYW1lPgogICAgPHN1ZmZpeD52YWx1ZTwvc3VmZml4PgogICAgPHByZWZp
- eD52YWx1ZTwvcHJlZml4PgogICAgPGdlbmRlcj52YWx1ZTwvZ2VuZGVyPgogICAgPGVtYWlsPnZh
- bHVlPC9lbWFpbD4KICAgIDxwaG9uZT52YWx1ZTwvcGhvbmU+CiAgICA8Y29tdHlwZWtleT52YWx1
- ZTwvY29tdHlwZWtleT4KPC9yZXN1bHQ+Cg==</inboundTemplate>
- <inboundProtocol>XML</inboundProtocol>
- <outboundProtocol>XML</outboundProtocol>
- <inboundProperties>
- <property name="stripNamespaces">true</property>
- </inboundProperties>
- <outboundProperties>
- <property name="stripNamespaces">true</property>
- </outboundProperties>
- </transformer>
- <filter>
- <rules/>
- </filter>
- <transportName>Channel Writer</transportName>
- <mode>DESTINATION</mode>
- <enabled>true</enabled>
- <version>2.2.1.5861</version>
- </connector>
- </destinationConnectors>
- <properties>
- <property name="clearGlobalChannelMap">true</property>
- <property name="dont_store_filtered">false</property>
- <property name="encryptData">false</property>
- <property name="error_messages_only">false</property>
- <property name="initialState">started</property>
- <property name="max_message_age">-1</property>
- <property name="store_messages">true</property>
- <property name="synchronous">true</property>
- <property name="transactional">false</property>
- </properties>
- <preprocessingScript>// Modify the message variable below to pre process data
- return message;</preprocessingScript>
- <postprocessingScript>// This script executes once after a message has been processed
- return;</postprocessingScript>
- <deployScript>// This script executes once when the channel is deployed
- // You only have access to the globalMap and globalChannelMap here to persist data
- return;</deployScript>
- <shutdownScript>// This script executes once when the channel is undeployed
- // You only have access to the globalMap and globalChannelMap here to persist data
- return;</shutdownScript>
- </channel>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement