update.sql

CREATE VIEW `bc_bill_type_view` AS select t.*,t1.name parent_name,t2.value type_value from bc_bill_type t left join bc_bill_type t1 on t1.id = t.parent_id left join bc_dict_data t2 on t2.code = t.type and t2.type_code = 'bill_type';
CREATE VIEW `bc_card_view` AS select card.*, cardType.name as card_type_name, user.name as user_name from bc_card card left join bc_card_type cardType on cardType.id = card.card_type_id left join bc_user user on user.id = card.user_id;
CREATE VIEW `bc_dict_data_view` AS select t.*, t1.value as type_code_value from bc_dict_data t left join bc_dict_type t1 on t1.code = t.type_code;
CREATE VIEW `bd_bill_view` AS select bill.*, user.name as user_name, billType.name as bill_type_name, billType.type as bill_type_type, dictData.value as bill_type_type_value, card.name as card_name, card.user_id as card_user_id, cardUser.name as card_user_name, targetCard.name as target_card_name, targetCard.user_id as target_card_user_id, targetCardUser.name as target_card_user_name from bd_bill bill left join bc_bill_type billType on billType.id = bill.bill_type_id left join bc_user user on user.id = bill.user_id left join bc_card card on card.id = bill.card_id left join bc_user cardUser on cardUser.id = card.user_id left join bc_card targetCard on targetCard.id = bill.target_card_id left join bc_user targetCardUser on targetCardUser.id = targetCard.user_id left join bc_dict_data dictData on dictData.code = billType.type and dictData.type_code = 'bill_type';
CREATE VIEW `bc_bill_template_view` AS select billTemplate.*, user.name as user_name, billType.name as bill_type_name, billType.type as bill_type_type, billTypeDict.value as bill_type_value, card.name as card_name, card.user_id as card_user_id, cardUser.name as card_user_name, targetCard.name as target_card_name, targetCard.user_id as target_card_user_id, targetCardUser.name as target_card_user_name from bc_bill_template billTemplate left join bc_bill_type billType on billType.id = billTemplate.bill_type_id left join bc_dict_data billTypeDict on billTypeDict.code = billType.type and billTypeDict.type_code = 'bill_type' left join bc_user user on user.id = billTemplate.user_id left join bc_card card on card.id = billTemplate.card_id left join bc_user cardUser on cardUser.id = card.user_id left join bc_card targetCard on targetCard.id = billTemplate.target_card_id left join bc_user targetCardUser on targetCardUser.id = targetCard.user_id ;
CREATE VIEW `bd_stat_bill_m_view` AS select uuid() as id, t.bill_type_id, t.card_id, t.user_id, card.name as card_name, user.name as user_name, billType.name as bill_type_name, t.money, billType.type as billTypeType, str_to_date(concat(t.date_time, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s')as date_time from (select ROUND(sum(t.money), 2) as money, DATE_FORMAT(t.date_time, '%Y-%m') as date_time, t.bill_type_id, t.card_id, t.user_id from bd_bill t left join bc_bill_type t1 on t1.id = t.bill_type_id group by DATE_FORMAT(t.date_time, '%Y-%m'), t.user_id, t.bill_type_id, t.card_id) t left join bc_card card on card.id = t.card_id left join bc_user user on user.id = t.user_id left join bc_bill_type billType on billType.id = t.bill_type_id;
CREATE VIEW `bc_fund_view` AS select t.*, t1.name as fund_buss_type_name, t2.name as fund_type_name from bc_fund t left join bc_fund_buss_type t1 on t1.id = t.fund_buss_type_id left join bc_fund_type t2 on t2.id = t.fund_type_id ;
CREATE VIEW `bd_fund_deal_view` AS SELECT t.*, t1.code AS fund_code, t1.name AS fund_name, t2.name AS user_name, t3.value AS status_value, t4.value AS data_status_value FROM bd_fund_deal t LEFT JOIN bc_fund t1 ON t1.id = t.fund_id LEFT JOIN bc_user t2 ON t2.id = t.user_id LEFT JOIN bc_dict_data t3 ON t3.code = t.status AND t3.type_code = 'fund_deal_status' LEFT JOIN bc_dict_data t4 ON t4.code = t.data_status AND t4.type_code = 'fund_data_status' ;
CREATE VIEW `bd_fund_deal_sell_view` AS select t.*, t1.value as data_status_value, t3.name as fund_name from bd_fund_deal_sell t left join bc_dict_data t1 on t1.code = t.data_status and t1.type_code = 'fund_data_status' left join bd_fund_deal t2 on t2.id = t.fund_deal_id left join bc_fund t3 on t3.id = t2.fund_id ;
CREATE VIEW `bd_fund_price_view` AS select t.*, t1.code as fund_code, t1.name as fund_name from bd_fund_price t left join bc_fund t1 on t1.id = t.fund_id;