CREATE TABLE `tbl_traledger` (
`id` int(11)
,`invno` varchar(25)
,`refinv` varchar(25)
,`dty` varchar(255)
,`did` varchar(21)
,`amo` double
,`cid` varchar(21)
,`cty` varchar(255)
,`chkno` varchar(255)
,`chkdt` date
,`otnote` varchar(350)
,`recon` int(11)
,`ref` varchar(35)
,`curid` int(11)
,`xrate` decimal(10,2)
,`apdate` date
,`brid` int(11)
,`uid` int(11)
,`date` datetime
);





DROP TABLE IF EXISTS `tbl_traledger`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `tbl_traledger`  AS  select `tbl_trarecord`.`id` AS `id`,`tbl_trarecord`.`invno` AS `invno`,`tbl_trarecord`.`refinv` AS `refinv`,`tbl_trarecord`.`dty` AS `dty`,`tbl_trarecord`.`did` AS `did`,`tbl_trarecord`.`amo` AS `amo`,`tbl_trarecord`.`cid` AS `cid`,`tbl_trarecord`.`cty` AS `cty`,`tbl_trarecord`.`chkno` AS `chkno`,`tbl_trarecord`.`chkdt` AS `chkdt`,`tbl_trarecord`.`otnote` AS `otnote`,`tbl_trarecord`.`recon` AS `recon`,`tbl_trarecord`.`ref` AS `ref`,`tbl_trarecord`.`curid` AS `curid`,`tbl_trarecord`.`xrate` AS `xrate`,`tbl_trarecord`.`apdate` AS `apdate`,`tbl_trarecord`.`brid` AS `brid`,`tbl_trarecord`.`uid` AS `uid`,`tbl_trarecord`.`date` AS `date` from `tbl_trarecord` union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`cost` * `tbl_traproduct`.`p_qty` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,`tbl_purchase`.`curid` AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_purchase` on(`tbl_purchase`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'PU' union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,`tbl_purchase`.`type` AS `dty`,`tbl_purchase`.`supid` AS `did`,`tbl_purchase`.`itemdis` AS `amo`,cast('42' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`itemdis` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,`tbl_purchase`.`type` AS `dty`,`tbl_purchase`.`supid` AS `did`,`tbl_purchase`.`disamo` AS `amo`,cast('41' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`disamo` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('25' as signed) AS `did`,`tbl_purchase`.`vatamo` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`vatamo` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('26' as signed) AS `did`,`tbl_purchase`.`taxamo` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`taxamo` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('27' as signed) AS `did`,`tbl_purchase`.`otamo` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`otamo` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('27' as signed) AS `did`,`tbl_purchase`.`spmoney` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`spmoney` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('44' as signed) AS `did`,`tbl_purchase`.`freight` AS `amo`,`tbl_purchase`.`supid` AS `cid`,`tbl_purchase`.`type` AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`freight` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,NULL AS `refinv`,`tbl_purchase`.`type` AS `dty`,`tbl_purchase`.`supid` AS `did`,`tbl_purchase`.`less` AS `amo`,cast('43' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`less` > 0 union all select `tbl_purchase`.`id` AS `id`,`tbl_purchase`.`invno` AS `invno`,`tbl_purchase`.`invno` AS `refinv`,`tbl_purchase`.`type` AS `dty`,`tbl_purchase`.`supid` AS `did`,`tbl_purchase`.`rawcash` - `tbl_purchase`.`changes` AS `amo`,cast(`SPLIT_STRING`(`tbl_purchase`.`creditid`,'_',2) as signed) AS `cid`,`SPLIT_STRING`(`tbl_purchase`.`creditid`,'_',1) AS `cty`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_purchase`.`details`) > 3,if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_purchase`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_purchase`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_purchase`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,`tbl_purchase`.`ref` AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_purchase`.`apdate` AS `apdate`,`tbl_purchase`.`brid` AS `brid`,`tbl_purchase`.`uid` AS `uid`,`tbl_purchase`.`date` AS `date` from `tbl_purchase` where `tbl_purchase`.`rawcash` - `tbl_purchase`.`changes` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,`tbl_preturn`.`type` AS `dty`,`tbl_preturn`.`cusid` AS `did`,`tbl_traproduct`.`cost` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_preturn` on(`tbl_preturn`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'PR' union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('42' as signed) AS `did`,`tbl_preturn`.`itemdis` AS `amo`,`tbl_preturn`.`cusid` AS `cid`,`tbl_preturn`.`type` AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`itemdis` > 0 union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('41' as signed) AS `did`,`tbl_preturn`.`disamo` AS `amo`,`tbl_preturn`.`cusid` AS `cid`,`tbl_preturn`.`type` AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`disamo` > 0 union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,NULL AS `refinv`,`tbl_preturn`.`type` AS `dty`,`tbl_preturn`.`cusid` AS `did`,`tbl_preturn`.`vatamo` AS `amo`,cast('25' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`vatamo` > 0 union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,NULL AS `refinv`,`tbl_preturn`.`type` AS `dty`,`tbl_preturn`.`cusid` AS `did`,`tbl_preturn`.`taxamo` AS `amo`,cast('26' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`taxamo` > 0 union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('45' as signed) AS `did`,`tbl_preturn`.`less` AS `amo`,`tbl_preturn`.`cusid` AS `cid`,`tbl_preturn`.`type` AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`less` > 0 union all select `tbl_preturn`.`id` AS `id`,`tbl_preturn`.`invno` AS `invno`,`tbl_preturn`.`invno` AS `refinv`,`SPLIT_STRING`(`tbl_preturn`.`debitid`,'_',1) AS `dty`,cast(`SPLIT_STRING`(`tbl_preturn`.`debitid`,'_',2) as signed) AS `did`,`tbl_preturn`.`rawcash` - `tbl_preturn`.`changes` AS `amo`,`tbl_preturn`.`cusid` AS `cid`,`tbl_preturn`.`type` AS `cty`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_preturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_preturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_preturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_preturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_preturn`.`apdate` AS `apdate`,`tbl_preturn`.`brid` AS `brid`,`tbl_preturn`.`uid` AS `uid`,`tbl_preturn`.`date` AS `date` from `tbl_preturn` where `tbl_preturn`.`rawcash` - `tbl_preturn`.`changes` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sales`.`type` AS `dty`,`tbl_sales`.`cusid` AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SE' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_out` AS `amo`,'34' AS `cid`,'LE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SE' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'14' AS `did`,`tbl_traproduct`.`cost` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SE' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'42' AS `did`,`tbl_traproduct`.`icdis` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SE' and `tbl_traproduct`.`icdis` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'41' AS `did`,`tbl_traproduct`.`icinvdis` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SE' and `tbl_traproduct`.`icinvdis` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('37' as signed) AS `did`,`tbl_sales`.`itemdis` AS `amo`,`tbl_sales`.`cusid` AS `cid`,`tbl_sales`.`type` AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`itemdis` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('36' as signed) AS `did`,`tbl_sales`.`disamo` AS `amo`,`tbl_sales`.`cusid` AS `cid`,`tbl_sales`.`type` AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`disamo` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sales`.`type` AS `dty`,`tbl_sales`.`cusid` AS `did`,`tbl_sales`.`vatamo` AS `amo`,cast('10' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`vatamo` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sales`.`type` AS `dty`,`tbl_sales`.`cusid` AS `did`,`tbl_sales`.`aitamo` AS `amo`,cast('38' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`aitamo` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sales`.`type` AS `dty`,`tbl_sales`.`cusid` AS `did`,`tbl_sales`.`otamo` AS `amo`,cast('39' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`otamo` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sales`.`type` AS `dty`,`tbl_sales`.`cusid` AS `did`,`tbl_sales`.`freight` AS `amo`,cast('12' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`freight` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('40' as signed) AS `did`,`tbl_sales`.`less` AS `amo`,`tbl_sales`.`cusid` AS `cid`,`tbl_sales`.`type` AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`less` > 0 union all select `tbl_sales`.`id` AS `id`,`tbl_sales`.`invno` AS `invno`,`tbl_sales`.`invno` AS `refinv`,`SPLIT_STRING`(`tbl_sales`.`debitid`,'_',1) AS `dty`,cast(`SPLIT_STRING`(`tbl_sales`.`debitid`,'_',2) as signed) AS `did`,`tbl_sales`.`rawcash` - `tbl_sales`.`changes` AS `amo`,`tbl_sales`.`cusid` AS `cid`,`tbl_sales`.`type` AS `cty`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,`tbl_sales`.`ref` AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sales`.`apdate` AS `apdate`,`tbl_sales`.`brid` AS `brid`,`tbl_sales`.`uid` AS `uid`,`tbl_sales`.`date` AS `date` from `tbl_sales` where `tbl_sales`.`rawcash` - `tbl_sales`.`changes` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`cost` * `tbl_traproduct`.`p_in` AS `amo`,'14' AS `cid`,'LE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sreturn` on(`tbl_sreturn`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SR' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_in` AS `amo`,`tbl_sreturn`.`cusid` AS `cid`,`tbl_sreturn`.`type` AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sreturn` on(`tbl_sreturn`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SR' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'34' AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_in` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sreturn` on(`tbl_sreturn`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SR' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`icdis` * `tbl_traproduct`.`p_in` AS `amo`,'42' AS `cid`,'LE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SR' and `tbl_traproduct`.`icdis` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`icinvdis` * `tbl_traproduct`.`p_in` AS `amo`,'41' AS `cid`,'LE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_sales` on(`tbl_sales`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'SR' and `tbl_traproduct`.`icinvdis` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sreturn`.`type` AS `dty`,`tbl_sreturn`.`cusid` AS `did`,`tbl_sreturn`.`itemdis` AS `amo`,cast('37' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`itemdis` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sreturn`.`type` AS `dty`,`tbl_sreturn`.`cusid` AS `did`,`tbl_sreturn`.`disamo` AS `amo`,cast('36' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`disamo` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('10' as signed) AS `did`,`tbl_sreturn`.`vatamo` AS `amo`,`tbl_sreturn`.`cusid` AS `cid`,`tbl_sreturn`.`type` AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`vatamo` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('38' as signed) AS `did`,`tbl_sreturn`.`aitamo` AS `amo`,`tbl_sreturn`.`cusid` AS `cid`,`tbl_sreturn`.`type` AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`aitamo` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sreturn`.`type` AS `dty`,`tbl_sreturn`.`cusid` AS `did`,`tbl_sreturn`.`less` AS `amo`,cast('46' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`less` > 0 union all select `tbl_sreturn`.`id` AS `id`,`tbl_sreturn`.`invno` AS `invno`,`tbl_sreturn`.`invno` AS `refinv`,`tbl_sreturn`.`type` AS `dty`,`tbl_sreturn`.`cusid` AS `did`,`tbl_sreturn`.`rawcash` - `tbl_sreturn`.`changes` AS `amo`,cast(`SPLIT_STRING`(`tbl_sreturn`.`creditid`,'_',2) as signed) AS `cid`,`SPLIT_STRING`(`tbl_sreturn`.`creditid`,'_',1) AS `cty`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sreturn`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sreturn`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sreturn`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sreturn`.`apdate` AS `apdate`,`tbl_sreturn`.`brid` AS `brid`,`tbl_sreturn`.`uid` AS `uid`,`tbl_sreturn`.`date` AS `date` from `tbl_sreturn` where `tbl_sreturn`.`rawcash` - `tbl_sreturn`.`changes` > 0 union all select `tbl_sersalesde`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sersales`.`type` AS `dty`,`tbl_sersales`.`cusid` AS `did`,`tbl_sersalesde`.`price` * `tbl_sersalesde`.`qty` AS `amo`,`tbl_sersalesde`.`sid` AS `cid`,'SE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,if(`tbl_sersalesde`.`sid` = 0,`tbl_sersalesde`.`name`,NULL) AS `ref`,`tbl_sersales`.`curid` AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersalesde`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from (`tbl_sersalesde` left join `tbl_sersales` on(`tbl_sersales`.`id` = `tbl_sersalesde`.`seid`)) union all select `tbl_sersalesde`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'14' AS `did`,`tbl_sersalesde`.`cost` * `tbl_sersalesde`.`qty` AS `amo`,`tbl_sersalesde`.`sid` AS `cid`,'SE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,if(`tbl_sersalesde`.`sid` = 0,`tbl_sersalesde`.`name`,NULL) AS `ref`,`tbl_sersales`.`curid` AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersalesde`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from (`tbl_sersalesde` left join `tbl_sersales` on(`tbl_sersales`.`id` = `tbl_sersalesde`.`seid`)) union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('37' as signed) AS `did`,`tbl_sersales`.`itemdis` AS `amo`,`tbl_sersales`.`cusid` AS `cid`,`tbl_sersales`.`type` AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`itemdis` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('36' as signed) AS `did`,`tbl_sersales`.`disamo` AS `amo`,`tbl_sersales`.`cusid` AS `cid`,`tbl_sersales`.`type` AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`disamo` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sersales`.`type` AS `dty`,`tbl_sersales`.`cusid` AS `did`,`tbl_sersales`.`vatamo` AS `amo`,cast('10' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`vatamo` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sersales`.`type` AS `dty`,`tbl_sersales`.`cusid` AS `did`,`tbl_sersales`.`aitamo` AS `amo`,cast('38' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`aitamo` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sersales`.`type` AS `dty`,`tbl_sersales`.`cusid` AS `did`,`tbl_sersales`.`otamo` AS `amo`,cast('39' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`otamo` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,`tbl_sersales`.`type` AS `dty`,`tbl_sersales`.`cusid` AS `did`,`tbl_sersales`.`freight` AS `amo`,cast('12' as signed) AS `cid`,'LE' AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`freight` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,cast('40' as signed) AS `did`,`tbl_sersales`.`less` AS `amo`,`tbl_sersales`.`cusid` AS `cid`,`tbl_sersales`.`type` AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`less` > 0 union all select `tbl_sersales`.`id` AS `id`,`tbl_sersales`.`invno` AS `invno`,`tbl_sersales`.`invno` AS `refinv`,`SPLIT_STRING`(`tbl_sersales`.`debitid`,'_',1) AS `dty`,cast(`SPLIT_STRING`(`tbl_sersales`.`debitid`,'_',2) as signed) AS `did`,`tbl_sersales`.`rawcash` - `tbl_sersales`.`changes` AS `amo`,`tbl_sersales`.`cusid` AS `cid`,`tbl_sersales`.`type` AS `cty`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL),NULL) AS `chkno`,cast(if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CHK',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',3),NULL),NULL) as date) AS `chkdt`,if(octet_length(`tbl_sersales`.`details`) > 3,if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'CRD',concat((select `tbl_bank`.`name` from `tbl_bank` where `tbl_bank`.`id` = `SPLIT_STRING`(`tbl_sersales`.`details`,'_',4)),'-',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2)),if(`SPLIT_STRING`(`tbl_sersales`.`details`,'_',1) = 'MOB',`SPLIT_STRING`(`tbl_sersales`.`details`,'_',2),NULL)),NULL) AS `otnote`,0 AS `recon`,`tbl_sersales`.`ref` AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_sersales`.`apdate` AS `apdate`,`tbl_sersales`.`brid` AS `brid`,`tbl_sersales`.`uid` AS `uid`,`tbl_sersales`.`date` AS `date` from `tbl_sersales` where `tbl_sersales`.`rawcash` - `tbl_sersales`.`changes` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'50' AS `did`,`tbl_traproduct`.`cost` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_disposal` on(`tbl_disposal`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'DS' union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,if(`tbl_disposal`.`type` is null or octet_length(`tbl_disposal`.`type`) < 1,'LE',`tbl_disposal`.`type`) AS `dty`,if(`tbl_disposal`.`type` is null or octet_length(`tbl_disposal`.`type`) < 1,'49',`tbl_disposal`.`cusid`) AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_out` AS `amo`,`tbl_traproduct`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_disposal` on(`tbl_disposal`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'DS' and `tbl_traproduct`.`price` > 0 union all select `tbl_traproduct`.`id` AS `id`,`tbl_traproduct`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_traproduct`.`pid` AS `did`,`tbl_traproduct`.`price` * `tbl_traproduct`.`p_out` AS `amo`,'51' AS `cid`,'LE' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_traproduct`.`apdate` AS `apdate`,`tbl_traproduct`.`brid` AS `brid`,`tbl_traproduct`.`uid` AS `uid`,`tbl_traproduct`.`date` AS `date` from (`tbl_traproduct` left join `tbl_disposal` on(`tbl_disposal`.`invno` = `tbl_traproduct`.`invno`)) where `tbl_traproduct`.`mods` = 'DS' and `tbl_traproduct`.`price` > 0 union all select `tbl_disposal`.`id` AS `id`,`tbl_disposal`.`invno` AS `invno`,`tbl_disposal`.`invno` AS `refinv`,'LE' AS `dty`,cast('2' as signed) AS `did`,`tbl_disposal`.`amount` AS `amo`,`tbl_disposal`.`cusid` AS `cid`,`tbl_disposal`.`type` AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,`tbl_disposal`.`note` AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,`tbl_disposal`.`apdate` AS `apdate`,`tbl_disposal`.`brid` AS `brid`,`tbl_disposal`.`uid` AS `uid`,`tbl_disposal`.`date` AS `date` from `tbl_disposal` where `tbl_disposal`.`amount` > 0 union all select `tbl_waranty`.`id` AS `id`,`tbl_waranty`.`invno` AS `invno`,NULL AS `refinv`,'PD' AS `dty`,`tbl_waranty`.`pid` AS `did`,`tbl_waranty`.`sat_amount` AS `amo`,`tbl_waranty`.`cusid` AS `cid`,`tbl_waranty`.`type` AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,cast(`tbl_waranty`.`d_date` as date) AS `apdate`,`tbl_waranty`.`brid` AS `brid`,`tbl_waranty`.`uid` AS `uid`,`tbl_waranty`.`d_date` AS `date` from `tbl_waranty` where `tbl_waranty`.`sat_amount` > 0 union all select `tbl_waranty`.`id` AS `id`,if(`tbl_waranty`.`binvno` is not null,`tbl_waranty`.`binvno`,`tbl_waranty`.`invno`) AS `invno`,NULL AS `refinv`,'SU' AS `dty`,`tbl_waranty`.`supid` AS `did`,`tbl_waranty`.`sat_amount` AS `amo`,`tbl_waranty`.`pid` AS `cid`,'PD' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,0 AS `curid`,0 AS `xrate`,cast(`tbl_waranty`.`d_date` as date) AS `apdate`,`tbl_waranty`.`brid` AS `brid`,`tbl_waranty`.`uid` AS `uid`,`tbl_waranty`.`d_date` AS `date` from `tbl_waranty` where `tbl_waranty`.`sat_amount` > 0 union all select `tbl_salarysheet`.`id` AS `id`,`tbl_salarysheet`.`payslip` AS `invno`,NULL AS `refinv`,'LE' AS `dty`,'48' AS `did`,`tbl_salarysheet`.`tsalary` AS `amo`,`tbl_salarysheet`.`empid` AS `cid`,'EM' AS `cty`,NULL AS `chkno`,NULL AS `chkdt`,NULL AS `otnote`,0 AS `recon`,NULL AS `ref`,1 AS `curid`,0 AS `xrate`,`tbl_salarysheet`.`apdate` AS `apdate`,`tbl_employe`.`wbrid` AS `brid`,`tbl_salarysheet`.`uid` AS `uid`,`tbl_salarysheet`.`date` AS `date` from (`tbl_salarysheet` left join `tbl_employe` on(`tbl_employe`.`id` = `tbl_salarysheet`.`empid`)) ;
