问带案例的MySQL视图逻辑

EN

CREATE VIEW `b2b_data` AS SELECT 
`asteriskcdrdb`.`cdr`.`uniqueid` AS `callid`,
`asteriskcdrdb`.`cdr`.`calldate` AS `ENTER`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`did`,`asteriskcdrdb`.`cdr`.`src`) AS `caller_id`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),'outbound','inbound') AS `direction`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`src`,substr(`asteriskcdrdb`.`cdr`.`dstchannel`,5,3)) AS `AGENT`,
sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`,
`asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`,
if((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal'),`asteriskcdrdb`.`cdr`.`dstchannel`,`asteriskcdrdb`.`cdr`.`did`) AS `ROUTE`,
`asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE (((`asteriskcdrdb`.`cdr`.`dst` = '601') AND (`asteriskcdrdb`.`cdr`.`dcontext` = 'ext-group')) OR ((`asteriskcdrdb`.`cdr`.`dst` = '601') AND (`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal-xfer')) OR ((`asteriskcdrdb`.`cdr`.`dcontext` = 'from-internal') AND (`asteriskcdrdb`.`cdr`.`src` in ('143','146','317','318','320','225','214'))))

CREATE VIEW `b2b_data` AS SELECT
`asteriskcdrdb`.`cdr`.`uniqueid` AS `callid`,
`asteriskcdrdb`.`cdr`.`calldate` AS `ENTER`,

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`did` AS `caller_id`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`src` AS `caller_id`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN 'outbound' AS `direction`;
    WHEN 'from-internal-xfer' THEN 'inbound' AS `direction`;
    WHEN 'ext-local' THEN 'inbound' AS `direction`;
    WHEN 'ext-group' THEN 'inbound' AS `direction`;
    WHEN 'from-did-direct' THEN 'inbound' AS `direction`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`src` AS `AGENT`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
    WHEN 'ext-group' THEN substr(`asteriskcdrdb`.`cdr`.`dstchannel`;5;3)) AS `AGENT`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`dst` AS `AGENT`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'from-internal-xfer' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'ext-local' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'ext-group' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
    WHEN 'from-did-direct' THEN sec_to_time(`asteriskcdrdb`.`cdr`.`duration`) AS `AGENT_TIME`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`disposition` AS `DISPOSITION`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`dstchannel` AS `ROUTE`;
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`dstchannel` AS `ROUTE`;
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`did` AS `ROUTE`;
END CASE;

CASE `asteriskcdrdb`.`cdr`.`dcontext`
    WHEN 'from-internal' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`src` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'from-internal-xfer' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'ext-local' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
    WHEN 'ext-group' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` = '601';
    WHEN 'from-did-direct' THEN `asteriskcdrdb`.`cdr`.`recordingfile` AS `RECORDING` FROM `asteriskcdrdb`.`cdr` WHERE `asteriskcdrdb`.`cdr`.`dst` IN ('143';'146';'317';'318';'320';'225';'214');
END CASE;