2021-08-12

-- 菜单
INSERT INTO sys_permission_info(id, pid, name, type, sign, href, sort, icon, target, descript, status, del_flag, create_user, create_time, update_user, update_time) VALUES (207, 168, '运营报表', 1, 'business:operationReport', 'business/operationReport/init', 3, '', '_self', '', 0, 0, 1, '2021-12-13 14:31:43', 0, '2021-12-17 14:01:34');
INSERT INTO sys_permission_info(id, pid, name, type, sign, href, sort, icon, target, descript, status, del_flag, create_user, create_time, update_user, update_time) VALUES (208, 168, '统计图表', 1, 'business:statisticsChart', 'business/statisticsChart/init', 2, '', '_self', '', 0, 0, 1, '2021-12-17 13:57:29', 0, '2021-12-17 14:01:36');

-- 权限
INSERT INTO sys_role_permission(id, role_id, permission_id) VALUES (3730, 9, 207);
INSERT INTO sys_role_permission(id, role_id, permission_id) VALUES (3731, 1, 207);
INSERT INTO sys_role_permission(id, role_id, permission_id) VALUES (3732, 9, 208);
INSERT INTO sys_role_permission(id, role_id, permission_id) VALUES (3733, 1, 208);

--新增字段
ALTER TABLE t_customer_info
ADD COLUMN profile_picture VARCHAR ( 255 ) DEFAULT NULL COMMENT '宝宝头像地址' AFTER guardian_relation,
ADD COLUMN current_meal_info_id INT ( 11 ) DEFAULT NULL COMMENT '当前测评套餐Id' AFTER cur_reside_addr,
ADD COLUMN future_meal_info_id INT ( 11 ) DEFAULT NULL COMMENT '已购买未开始测评的套餐ID' AFTER current_meal_info_id;

alter table t_eval_record_detail
add column score decimal(11,0) DEFAULT NULL COMMENT '得分' after product_item_select;

ALTER TABLE t_eval_record
ADD COLUMN hearing_and_language_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '语言与感知得分' AFTER height,
ADD COLUMN vision_and_cognition_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '平衡与大运动得分' AFTER hearing_and_language_score,
ADD COLUMN balance_and_great_movement_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '精细动作得分' AFTER vision_and_cognition_score,
ADD COLUMN fine_motor_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '适应能力得分' AFTER balance_and_great_movement_score,
ADD COLUMN social_behavior_and_adaptability_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '社会行为得分' AFTER fine_motor_score,
ADD COLUMN total_score DECIMAL ( 11, 0 ) DEFAULT NULL COMMENT '总得分' AFTER social_behavior_and_adaptability_score;

-- detail的分数
update t_eval_record_detail a set score=(select b.score from t_product_item_options b where a.product_item_id=b.product_item_id and a.product_item_select=b.item_options_char);
-- 各项测评分
update t_eval_record a set
a.hearing_and_language_score = (select sum(b.score) from t_eval_record_detail b where b.product_id=1 and b.eval_record_id=a.id);
update t_eval_record a set
a.vision_and_cognition_score = (select sum(b.score) from t_eval_record_detail b where b.product_id=2 and b.eval_record_id=a.id);
update t_eval_record a set
a.balance_and_great_movement_score = (select sum(b.score) from t_eval_record_detail b where b.product_id=3 and b.eval_record_id=a.id);
update t_eval_record a set
a.fine_motor_score = (select sum(b.score) from t_eval_record_detail b where b.product_id=4 and b.eval_record_id=a.id);
update t_eval_record a set
a.social_behavior_and_adaptability_score = (select sum(b.score) from t_eval_record_detail b where b.product_id=5 and b.eval_record_id=a.id);
-- 总分
update t_eval_record a set
a.total_score = ROUND((hearing_and_language_score+vision_and_cognition_score+balance_and_great_movement_score+fine_motor_score+social_behavior_and_adaptability_score)/5,2);