CREATE TABLE `tbl_ledger` (
`id` varchar(13)
,`code` varchar(15)
,`grid` int(11)
,`groups` varchar(35)
,`sgrid` int(11)
,`sgroup` varchar(35)
,`ledger` varchar(73)
,`clsid` int(11)
,`class` varchar(35)
);


DROP TABLE IF EXISTS `tbl_ledger`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `tbl_ledger`  AS  select convert(concat('LE',`tbl_acledger`.`id`) using utf8) collate utf8_general_ci AS `id`,`tbl_acledger`.`code` AS `code`,`tbl_acledger`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acledger`.`sgrid` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_acledger`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_acledger` left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acledger`.`grid`)) left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = `tbl_acledger`.`sgrid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('BA',`tbl_bacount`.`id`) AS `id`,NULL AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,concat(`tbl_bank`.`sort`,'/',`tbl_bacount`.`acno`) AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from ((((`tbl_bacount` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '2')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_bank` on(`tbl_bank`.`id` = `tbl_bacount`.`bid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('PD',`tbl_item`.`id`) AS `id`,`tbl_item`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_item`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_item` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '3')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('CU',`tbl_customer`.`id`) AS `id`,`tbl_customer`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_customer`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_customer` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '4')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`))union all select concat('CL',`tbl_client`.`id`) AS `id`,`tbl_client`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_client`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_client` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '4')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('SU',`tbl_supplier`.`id`) AS `id`,`tbl_supplier`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_supplier`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_supplier` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '13')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`))union all select concat('CR',`tbl_carrier`.`id`) AS `id`,`tbl_carrier`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_carrier`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_carrier` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '13')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('EM',`tbl_employe`.`id`) AS `id`,`tbl_employe`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_employe`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_employe` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '33')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('SE',`tbl_seritem`.`id`) AS `id`,`tbl_seritem`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,`tbl_seritem`.`name` AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_seritem` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '26')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('MO',`tbl_acmobile`.`id`) AS `id`,NULL AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,concat(`tbl_acmobile`.`name`,' - ',`tbl_acmobile`.`mobile`) AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_acmobile` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = '34')) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) union all select concat('LO',`tbl_loanid`.`id`) AS `id`,`tbl_loanid`.`code` AS `code`,`tbl_acsubgroup`.`grid` AS `grid`,`tbl_acgroup`.`name` AS `groups`,`tbl_acsubgroup`.`id` AS `sgrid`,`tbl_acsubgroup`.`name` AS `sgroup`,if(`tbl_loanid`.`acno` is not null,concat(`tbl_loanid`.`name`,' - ',`tbl_loanid`.`acno`),concat(`tbl_loanid`.`name`,' - ',`tbl_loanid`.`mobile`)) AS `ledger`,`tbl_acgroup`.`clsid` AS `clsid`,`tbl_aclass`.`name` AS `class` from (((`tbl_loanid` left join `tbl_acsubgroup` on(`tbl_acsubgroup`.`id` = if(`tbl_loanid`.`type` = 0,'35','36'))) left join `tbl_acgroup` on(`tbl_acgroup`.`id` = `tbl_acsubgroup`.`grid`)) left join `tbl_aclass` on(`tbl_aclass`.`id` = `tbl_acgroup`.`clsid`)) ;
