# table of orders (cmp_order_id) which when completed,

# will cause another order (dec_order_id) to be auto-declined.

CREATE TEMPORARY TABLE tmp_order_decliners

(

  cmp_order_id int unsigned not null,

  dec_order_id int unsigned not null,

  KEY cmp_order_id(cmp_order_id),

  KEY dec_order_id(dec_order_id)

);

# this is the mapping.

INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES 

$WCINCLUDE|OrderDeclineOrderList$

;

# The actual query

SELECT 

CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="',

IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()),

'" enc_order_id="',

eo.enc_order_id,

'" status="-4" comments="xxxx-xxxxx by ',

ceo.enc_order_id,

' (',

ceo.order_name,

')" /></ORDERS>') AS `eorder_xml`,

'?f=xxxxx&s=xxxxxx' AS `URL`

FROM tmp_order_decliners t

INNER JOIN xxxxxxx ceo

  ON ceo.order_id=t.cmp_order_id

  AND ceo.status=2

INNER JOIN xxxxx eo

  ON eo.order_id=t.dec_order_id

  AND (eo.status=0 OR eo.status=3)

  AND eo.pat_id=ceo.pat_id

  AND ceo.completed_dt >= eo.create_dt

2019-01-16 16:49:15   info   : (307) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 1102, type: QUERY_TYPE_READ, stmt: CREATE TEMPORARY TABLE /* db=xxxxxxxx dbuser=xxxxxxxx file=xxxxxx.c func=SysRep_Execute user_comment=uid:xxxx,uname:xxxx,rc:RC201806,hdl:xxxxxxx,aid:xxxx line:1718*/ tmp_order_decliners (   cmp_order_id int unsigned not null,   dec_order_id int unsigned not null,   KEY cmp_order_id(cmp_order_id),   KEY dec_order_id(dec_order_id) );

# this is the mapping.

INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES

(3151, 3152)

;

# The actual query

SELECT

CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="', IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()), '" enc_order_id="', eo.enc_order_id, '" status="-4" comments="Auto-Declined by ', ceo.enc_order_id, ' (', ceo.order_name, ')" /></ORDERS>') AS `eorder_xml`, '?f=ajaxpost&s=encounterorders' AS `URL` FROM tmp_order_decliners t INNER JOIN encounter_orders ceo   ON ceo.order_id=t.cmp_order_id   AND ceo.status=2 INNER JOIN encounter_orders eo   ON eo.order_id=t.dec_order_id   A

2019-01-16 16:49:15   info   : (307) Route query to slave: fw-mm-db1    [xxxxxxx]:3306 <

2019-01-16 17:01:03   info   : (414) Write operation detected, next 100 queries routed to master

2019-01-16 17:01:03   info   : (414) Write operation detected, queries routed to master for 1 seconds

2019-01-16 17:01:03   info   : (414) Added temporary table xxxxxxxx.tmp_order_decliners

2019-01-16 17:01:03   info   : (414) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 1105, type: QUERY_TYPE_WRITE|QUERY_TYPE_CREATE_TMP_TABLE, stmt: CREATE TEMPORARY TABLE /* db=xxxxxxxx dbuser=xxxxxxxx file=xxxxxx.c func=SysRep_Perform user_comment=uid:xxxxx,uname:xxxxxx,rc:RC201806,hdl:xxxxxxx,aid:xxxx line:1904*/ tmp_order_decliners (   cmp_order_id int unsigned not null,   dec_order_id int unsigned not null,   KEY cmp_order_id(cmp_order_id),   KEY dec_order_id(dec_order_id) );

# this is the mapping.

INSERT INTO tmp_order_decliners (cmp_order_id, dec_order_id) VALUES

(3151, 3152)

;

# The actual query

SELECT

CONCAT('<ORDERS><ENCORDER pat_id="',eo.pat_id,'" completed_dt="', IF(ceo.completed_dt != '0000-00-00 00:00:00', ceo.completed_dt, NOW()), '" enc_order_id="', eo.enc_order_id, '" status="-4" comments="Auto-Declined by ', ceo.enc_order_id, ' (', ceo.order_name, ')" /></ORDERS>') AS `eorder_xml`, '?f=ajaxpost&s=encounterorders' AS `URL` FROM tmp_order_decliners t INNER JOIN encounter_orders ceo   ON ceo.order_id=t.cmp_order_id   AND ceo.status=2 INNER JOIN encounter_orders eo   ON eo.order_id=t.dec_order_id

2019-01-16 17:01:03   info   : (414) Route query to master: db2   [xxxxxxx]:3306 <

2019-01-16 17:01:03   info   : (414) Reply complete, last reply from db2

[MM-RW-Service]

type=service

router=readwritesplit

max_slave_replication_lag=1

strict_multi_stmt=1

master_accept_reads=1

servers=db1,db2

user=maxscale

password=xXXXXX

filters=CCRFilter | QueryLogger

[CCRFilter]

type=filter

module=ccrfilter

time=1

count=100

# Query Log Filter

[QueryLogger]

type=filter

module=qlafilter

separator=" | "

log_data=date,session,user,reply_time,query

log_type=unified

filebase=/var/log/maxscale/querylog