//receive transections

CREATE VIEW cash_bank_mobilebank_view AS select `t4`.`_table_name` AS `_table_name`,`t4`.`_prefex` AS `_prefex`,`t4`.`_id` AS `_id`,concat(`t4`.`_prefex`,'-',`t4`.`_id`) AS `_transection_unique_id`,`t4`.`account_no` AS `account_no`,`t4`.`own_account_no` AS `own_account_no`,`t4`.`transection_no` AS `transection_no`,`t4`.`transection_date` AS `transection_date`,`t4`.`debit` AS `debit`,`t4`.`credit` AS `credit`,`t4`.`description` AS `description`,`t4`.`status` AS `status`,`t4`.`created_by` AS `created_by`,`t4`.`ref_no` AS `ref_no` from (select 'cash_books' AS `_table_name`,'CBA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`refeance_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`person_type` AS `ref_no` from `cash_books` `t1` where ifnull(`t1`.`debit`,0) > 0 union all select 'mobile_bank_accounts' AS `_table_name`,'MBA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`transection_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`ref_no` AS `ref_no` from `mobile_bank_accounts` `t1` where ifnull(`t1`.`debit`,0) > 0 union all select 'bank_accounts' AS `_table_name`,'BA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`transection_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`ref_no` AS `ref_no` from `bank_accounts` `t1` where ifnull(`t1`.`debit`,0) > 0) `t4` order by `t4`.`transection_date` desc

//Payment Transection

CREATE VIEW cash_bank_mobilebank_payment_view AS select `t4`.`_table_name` AS `_table_name`,`t4`.`_prefex` AS `_prefex`,`t4`.`_id` AS `_id`,concat(`t4`.`_prefex`,'-',`t4`.`_id`) AS `_transection_unique_id`,`t4`.`account_no` AS `account_no`,`t4`.`own_account_no` AS `own_account_no`,`t4`.`transection_no` AS `transection_no`,`t4`.`transection_date` AS `transection_date`,`t4`.`debit` AS `debit`,`t4`.`credit` AS `credit`,`t4`.`description` AS `description`,`t4`.`status` AS `status`,`t4`.`created_by` AS `created_by`,`t4`.`ref_no` AS `ref_no` from (select 'cash_books' AS `_table_name`,'CBA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`refeance_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`person_type` AS `ref_no` from `cash_books` `t1` where ifnull(`t1`.`credit`,0) > 0 union all select 'mobile_bank_accounts' AS `_table_name`,'MBA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`transection_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`ref_no` AS `ref_no` from `mobile_bank_accounts` `t1` where ifnull(`t1`.`credit`,0) > 0 union all select 'bank_accounts' AS `_table_name`,'BA' AS `_prefex`,`t1`.`id` AS `_id`,`t1`.`account_no` AS `account_no`,`t1`.`own_account_no` AS `own_account_no`,`t1`.`transection_no` AS `transection_no`,`t1`.`transection_date` AS `transection_date`,ifnull(`t1`.`debit`,0) AS `debit`,ifnull(`t1`.`credit`,0) AS `credit`,`t1`.`description` AS `description`,`t1`.`status` AS `status`,`t1`.`created_by` AS `created_by`,`t1`.`ref_no` AS `ref_no` from `bank_accounts` `t1` where ifnull(`t1`.`credit`,0) > 0) `t4` order by `t4`.`transection_date` desc

CREATE VIEW customer_supplier_both_accounts AS SELECT 'supplier' AS _type, `id`, `account_no`, `transection_no`,`transection_date`,  `description`,  `debit`, `credit`, `status`, `created_by`, `updated_by`, `deleted_at`, `created_at`, `updated_at` FROM `supplier_accounts` 
UNION ALL 
SELECT 'customer' AS _type, `id`, `account_no`, `transection_no`, `transection_date`, `description`, `debit`, `credit`,  `status`, `created_by`, `updated_by`, `deleted_at`, `created_at`, `updated_at` FROM `customer_accounts`