构造借款人基础数据
2024年10月28日大约 5 分钟
构造借款人基础数据
一、手工构造
- 打开用户系统
- 注册借款人账号
- 开通资金托管账号
- 申请额度，管理员后台审核通过
二、直接操作数据库构造
2.1 czbk_member库（账号）
//mb_member：为用户主表
//id：为系统自动生成，主键
//trust_account：托管账号，为16位自然数,与`p2p_mock`库`p2p_account`表中account字段一致
INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('542', '13022111111', '6020200507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL, '13022111111', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
//mb_member_info：为用户信息表
//id：为系统自动生成，主键
//member_id：为用户ID，与`czbk_member`库`mb_member`表中的ID一致
//member_name：为用户名，与`czbk_member`库`mb_member`表中的name一致
INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES ('529', '542', '13022111111', NULL, NULL, '小石头', '男', '1986-11-18', '429001198611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);2.2 czbk_finance库 （借款额度）
//fn_loan_amount：为借款额度表
//id：为系统自动生成，主键
//member_id：为用户ID，与`czbk_member`库`mb_member`表中的ID一致
//member_name：为用户名，与`czbk_member`库`mb_member`表中的name一致
//credit_amount:为申请的借款额度
INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('529', '542', '13022111111', '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000');2.3 p2p_mock库（mock模拟接口表）
//p2p_account：为mock的第三方的账户表
//id：为系统自动生成，主键
//account:托管账号，为16位自然数
INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('119', '6020200507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03');2.4 czbk_finance库（借款标）
//fn_loan：为借款标主表
//id：为系统自动生成，主键
//ind:借款订单号系统生成唯一32位（不能重复）
//serialno:借款标编号(不能重复)
//member_id：为用户ID，与`czbk_member`库`mb_member`表中的ID一致
//member_name：为用户名，与`czbk_member`库`mb_member`表中的name一致
//amount:为借款金额
INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES ('246', 'f5380d59791773a8abad4141ff90a5a4', '202005070001', '数据库构造借款1', '542', '13022111111', '100000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1609501013', '2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');2.5 fn_loan_info（为借款标明细表）
//fn_loan_info：为借款标明细表
//id：为系统自动生成，主键
//loan_id：为借款标主表ID，与`czbk_finance`库`fn_loan`表中的ID一致
INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, `product_process`, `information`) VALUES ('238', '246', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL);2.6 fn_loan_amount（为借款额度表）
//fn_loan_amount：为借款额度表
//credit_amount_freeze：为冻结金额，已申请借款的金额
//member_id：2.7 fn_loan_amount_log（额度变化日志表）
//fn_loan_amount_log：额度变化日志表
INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`,
`add_time`) VALUES ('542','13022111111', 1, 4, '100000', '借款冻结', '1972826569');三、优化综合语句
-- 清空操作
delete from `czbk_member`.`mb_member` where id=869;
delete from `czbk_member`.`mb_member_info` where id=987;
delete from `czbk_finance`.`fn_loan_amount` where id=987;
delete from `p2p_mock`.`p2p_account` where id=911;
-- 借款
delete from `czbk_finance`.`fn_loan` where id=642;
delete from `czbk_finance`.`fn_loan_info` where id=832;
delete from `czbk_finance`.`fn_loan_amount_log` where id=869;
INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('869', '13099775533', '6021234507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL, '13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES ('987', '869', '13099775533', NULL, NULL, '小石头', NULL, '1986-11-18', '429009118611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('987', '869', '13099775533', '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000');
INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('911', '6021234507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03');
-- 借款
INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES ('642', 'f5380d59791773a8abad4141ff90a5a4', '202008231111', '数据库构造借款1', '869', '13099775533', '100000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1609501013', '2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');
INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, `product_process`, `information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL);
UPDATE `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000' WHERE `member_id`='869';
INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`,
`add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结', '1972826569');