You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
302 lines
34 KiB
302 lines
34 KiB
/*
|
|
Navicat Premium Data Transfer
|
|
|
|
Source Server : chang
|
|
Source Server Type : MySQL
|
|
Source Server Version : 50726
|
|
Source Host : 192.168.211.132:3306
|
|
Source Schema : renren_cloud
|
|
|
|
Target Server Type : MySQL
|
|
Target Server Version : 50726
|
|
File Encoding : 65001
|
|
|
|
Date: 27/08/2021 17:50:43
|
|
*/
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- ----------------------------
|
|
-- Table structure for tb_import_info
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `tb_import_info`;
|
|
CREATE TABLE `tb_import_info` (
|
|
`import_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'import_id',
|
|
`account_ownership` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '我，她',
|
|
`import_time` datetime NOT NULL COMMENT '导入时间',
|
|
`stock_month` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '所属月份',
|
|
PRIMARY KEY (`import_id`) USING BTREE
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
|
|
|
|
-- ----------------------------
|
|
-- Records of tb_import_info
|
|
-- ----------------------------
|
|
INSERT INTO `tb_import_info` VALUES (1, '我', '2021-08-26 10:05:38', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (2, '我', '2021-08-26 10:06:32', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (3, '我', '2021-08-26 10:07:23', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (4, '我', '2021-08-26 10:12:49', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (5, '我', '2021-08-26 10:17:53', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (6, '我', '2021-08-26 10:20:08', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (7, '我', '2021-08-26 10:20:16', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (8, '我', '2021-08-26 10:22:39', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (9, '我', '2021-08-26 10:23:42', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (10, '我', '2021-08-26 10:33:53', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (11, '我', '2021-08-26 11:21:25', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (12, '23', '2021-08-27 11:26:38', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (13, '我', '2021-08-26 03:40:12', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (14, '我', '2021-08-26 03:43:54', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (15, '我', '2021-08-26 03:47:49', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (16, '我', '2021-08-26 03:51:39', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (17, '我', '2021-08-26 03:57:31', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (18, '我', '2021-08-26 03:59:31', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (19, '我', '2021-08-26 05:46:38', '202108');
|
|
INSERT INTO `tb_import_info` VALUES (20, '我', '2021-08-26 05:49:22', '202108');
|
|
|
|
-- ----------------------------
|
|
-- Table structure for tb_stock_trans
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `tb_stock_trans`;
|
|
CREATE TABLE `tb_stock_trans` (
|
|
`stock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
|
`buy_trans_date` date NULL DEFAULT NULL COMMENT '买入日期',
|
|
`sell_trans_date` date NULL DEFAULT NULL COMMENT '卖出日期',
|
|
`stock_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证券代码',
|
|
`stock_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证券名称',
|
|
`cost_price` float(10, 2) NOT NULL COMMENT '成本价格',
|
|
`sell_price` float(10, 2) NOT NULL COMMENT '卖出价格',
|
|
`trans_quantity` smallint(11) NOT NULL COMMENT '成交数量',
|
|
`cost_amount` float(10, 2) NOT NULL COMMENT '成本总金额 ',
|
|
`sell_amount` float(10, 2) NOT NULL COMMENT '卖出总金额 ',
|
|
`profit_loss_amount` float(10, 2) NOT NULL COMMENT '盈利/亏损',
|
|
`profit_loss_percent` float(10, 2) NOT NULL COMMENT '盈利/亏损百分比',
|
|
`total_charge` float(10, 2) NOT NULL COMMENT '总费用',
|
|
`shareholder_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '股东代码',
|
|
`capital_account` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '资金账号',
|
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
|
PRIMARY KEY (`stock_id`) USING BTREE
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 60 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
|
|
|
|
-- ----------------------------
|
|
-- Records of tb_stock_trans
|
|
-- ----------------------------
|
|
INSERT INTO `tb_stock_trans` VALUES (53, '2021-07-14', '2021-07-19', '002984', '森麒麟', 35.35, 32.85, 200, 7070.00, 6570.00, -500.00, -0.07, 17.50, '110773014', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (54, '2021-07-05', '2021-07-21', '002254', '泰和新材', 20.19, 22.19, 300, 6057.00, 6657.00, 600.00, 0.10, 16.99, '110773014', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (55, '2021-07-12', '2021-07-26', '002553', '南方轴承', 10.70, 14.29, 500, 5350.00, 7143.00, 1793.00, 0.34, 22.14, '110773014', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (56, '2021-07-22', '2021-07-27', '002236', '大华股份', 22.53, 20.88, 200, 4506.00, 4176.00, -330.00, -0.07, 14.18, '110773014', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (57, '2021-07-05', '2021-07-27', '600452', '涪陵电力', 14.34, 14.31, 400, 5736.00, 5724.00, -12.00, 0.00, 15.95, 'A540420805', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (58, '2021-07-23', '2021-07-28', '603859', '能科股份', 37.40, 35.80, 200, 7480.00, 7160.00, -320.00, -0.04, 19.16, 'A540420805', '1790235186', NULL);
|
|
INSERT INTO `tb_stock_trans` VALUES (59, '2021-07-12', '2021-07-28', '603050', '科林电气', 13.62, 13.50, 400, 5448.00, 5400.00, -48.00, -0.01, 15.62, 'A540420805', '1790235186', NULL);
|
|
|
|
-- ----------------------------
|
|
-- Table structure for tb_trans_detail
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `tb_trans_detail`;
|
|
CREATE TABLE `tb_trans_detail` (
|
|
`trans_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
|
`import_id` int(11) NOT NULL COMMENT '导入id',
|
|
`trans_date` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '发生日期',
|
|
`trans_time` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '成交时间',
|
|
`busi_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证券买入,证券卖出',
|
|
`stock_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证券代码',
|
|
`stock_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '证券名称',
|
|
`trans_price` float(10, 2) NOT NULL COMMENT '成交价格',
|
|
`trans_quantity` smallint(11) NOT NULL COMMENT '成交数量',
|
|
`trans_amount` float(10, 2) NOT NULL COMMENT '成交金额 ',
|
|
`share_balance` smallint(11) NULL DEFAULT NULL COMMENT '股份余额',
|
|
`service_charge` float(10, 2) NULL DEFAULT NULL COMMENT '手续费',
|
|
`stamp_duty` float(10, 2) NULL DEFAULT NULL COMMENT '印花税',
|
|
`transfer_fee` float(10, 2) NULL DEFAULT NULL COMMENT '过户费',
|
|
`additional_charge` float(10, 2) NULL DEFAULT NULL COMMENT '附加费',
|
|
`exchange_fees` float(10, 2) NULL DEFAULT NULL COMMENT '交易所清算费',
|
|
`occurrence_amount` float(10, 2) NULL DEFAULT NULL COMMENT '发生金额',
|
|
`capital_balance` float(10, 2) NULL DEFAULT NULL COMMENT '资金本次余额',
|
|
`entrusting_serial` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '委托编号',
|
|
`serial_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '流水号',
|
|
`shareholder_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '股东代码',
|
|
`capital_account` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '资金账号',
|
|
`currency` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '币种',
|
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
|
|
`gather_flag` smallint(11) NULL DEFAULT NULL COMMENT '汇总标识：0，未汇总，1，已汇总',
|
|
PRIMARY KEY (`trans_id`) USING BTREE
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 137 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
|
|
|
|
-- ----------------------------
|
|
-- Records of tb_trans_detail
|
|
-- ----------------------------
|
|
INSERT INTO `tb_trans_detail` VALUES (69, 20, '20210701', '19:02:39', '拆出报价购回', '660001', '报价标准券', 2.32, -3310, 331021.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 331021.03, 369442.28, '1306', '86804299824250096', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (70, 20, '20210701', '19:03:55', '报价回购拆出', '660001', '报价标准券', 2.33, 3030, 303000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -303000.00, 66442.27, '1128', '86804299824250096', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：58.03，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (71, 20, '20210702', '19:24:42', '拆出报价购回', '660001', '报价标准券', 2.33, -3030, 303058.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 303058.03, 377326.19, '1128', '86804304119220192', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (72, 20, '20210702', '19:25:49', '报价回购拆出', '660001', '报价标准券', 2.33, 3020, 302000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -302000.00, 75326.19, '629', '86804304119220192', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：19.28，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (73, 20, '20210702', '14:31:16', '证券卖出', '300816', '艾可蓝', 78.40, -100, 7840.00, 0, 6.27, 7.84, 0.00, 0.00, 0.00, 7825.89, 74268.16, '28886', '86804304922680800', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (74, 20, '20210705', '19:04:32', '拆出报价购回', '660001', '报价标准券', 2.33, -3020, 302019.28, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 302019.28, 375086.97, '629', '86804317004125904', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (75, 20, '20210705', '19:06:32', '报价回购拆出', '660001', '报价标准券', 2.33, 2980, 298000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -298000.00, 77086.96, '2373', '86804317004125904', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：19.02，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (76, 20, '20210705', '10:29:11', '证券卖出', '600398', '海澜之家', 6.83, -1400, 9562.00, 0, 7.65, 9.56, 0.19, 0.00, 0.00, 9544.60, 84870.79, '12950', '86804317805021696', 'A540420805', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (77, 20, '20210705', '14:40:04', '证券买入', '002254', '泰和新材', 20.19, 300, 6057.00, 300, 5.00, 0.00, 0.00, 0.00, 0.00, -6062.00, 78808.79, '26317', '86804317805021696', '110773014', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (78, 20, '20210705', '14:46:12', '证券买入', '600452', '涪陵电力', 14.34, 400, 5736.00, 400, 5.00, 0.00, 0.11, 0.00, 0.00, -5741.11, 73067.68, '26065', '86804317805021696', 'A540420805', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (79, 20, '20210706', '20:07:26', '股息红利税补缴', '', '', 0.00, 0, 0.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -35.84, 77051.12, '0', '86804321299085504', NULL, '1790235186', '人民币', '股息红利税差异化处理资金下账 证券代码:\'600398\' 初始持有日期:\'20210616\'计税日期:\'20210705\' 税号:\'808796\'补税税率:\'0.200000\' 需纳税数量:\'700.000000\' 减持日期:\'20210705\',海澜之家,700股', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (80, 20, '20210706', '19:37:21', '拆出报价购回', '660001', '报价标准券', 2.33, -2980, 298019.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 298019.03, 375070.12, '2373', '86804321299089296', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (81, 20, '20210706', '19:38:12', '报价回购拆出', '660001', '报价标准券', 2.22, 3010, 301000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -301000.00, 74070.14, '1224', '86804321299089296', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：18.31，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (82, 20, '20210707', '19:51:45', '拆出报价购回', '660001', '报价标准券', 2.22, -3010, 301018.31, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 301018.31, 375088.44, '1224', '86804325594055008', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (83, 20, '20210707', '19:52:14', '报价回购拆出', '660001', '报价标准券', 2.20, 2910, 291000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -291000.00, 84088.45, '129', '86804325594055008', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.54，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (84, 20, '20210708', '19:34:15', '拆出报价购回', '660001', '报价标准券', 2.20, -2910, 291017.53, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 291017.53, 375106.00, '129', '86804329889025104', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (85, 20, '20210708', '19:36:51', '报价回购拆出', '660001', '报价标准券', 2.12, 2980, 298000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -298000.00, 77105.99, '2226', '86804329889025104', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：51.93，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (86, 20, '20210709', '19:34:27', '拆出报价购回', '660001', '报价标准券', 2.12, -2980, 298051.94, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 298051.94, 375157.91, '2226', '86804334183992800', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (87, 20, '20210709', '19:35:07', '报价回购拆出', '660001', '报价标准券', 2.12, 2970, 297000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -297000.00, 78157.92, '747', '86804334183992800', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.25，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (88, 20, '20210712', '19:12:23', '拆出报价购回', '660001', '报价标准券', 2.12, -2970, 297017.25, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 297017.25, 364367.06, '747', '86804347068895296', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (89, 20, '20210712', '19:14:07', '报价回购拆出', '660001', '报价标准券', 2.27, 2770, 277000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -277000.00, 87367.06, '1288', '86804347068895296', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.23，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (90, 20, '20210712', '14:50:49', '证券买入', '002553', '南方轴承', 10.70, 500, 5350.00, 500, 5.00, 0.00, 0.00, 0.00, 0.00, -5355.00, 72802.92, '48255', '86804347869079808', '110773014', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (91, 20, '20210712', '14:51:28', '证券买入', '603050', '科林电气', 13.62, 400, 5448.00, 400, 5.00, 0.00, 0.11, 0.00, 0.00, -5453.11, 67349.81, '48288', '86804347869079808', 'A540420805', '1790235186', '人民币', '证券买入', 1);
|
|
INSERT INTO `tb_trans_detail` VALUES (92, 20, '20210713', '19:45:56', '拆出报价购回', '660001', '报价标准券', 2.27, -2770, 277017.22, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 277017.22, 364384.28, '1288', '86804351363857904', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (93, 20, '20210713', '19:47:15', '报价回购拆出', '660001', '报价标准券', 2.20, 2810, 281000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -281000.00, 83384.29, '1374', '86804351363857904', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：16.94，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (94, 20, '20210714', '19:18:22', '拆出报价购回', '660001', '报价标准券', 2.20, -2810, 281016.94, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 281016.94, 357325.56, '1374', '86804355658826896', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (95, 20, '20210714', '19:19:07', '报价回购拆出', '660001', '报价标准券', 2.18, 2850, 285000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -285000.00, 72325.57, '114', '86804355658826896', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.02，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (96, 20, '20210714', '14:55:03', '证券买入', '002984', '森麒麟', 35.35, 200, 7070.00, 200, 5.66, 0.00, 0.00, 0.00, 0.00, -7075.66, 76308.63, '39088', '86804356462961104', '110773014', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (97, 20, '20210715', '19:27:15', '拆出报价购回', '660001', '报价标准券', 2.18, -2850, 285017.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 285017.03, 357342.59, '114', '86804359953797600', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (98, 20, '20210715', '19:29:26', '报价回购拆出', '660001', '报价标准券', 2.16, 2840, 284000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -284000.00, 73342.59, '920', '86804359953797600', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：50.42，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (99, 20, '20210716', '19:08:12', '拆出报价购回', '660001', '报价标准券', 2.16, -2840, 284050.41, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 284050.41, 364028.91, '920', '86804364248762304', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (100, 20, '20210716', '19:09:43', '报价回购拆出', '660001', '报价标准券', 2.28, 2850, 285000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -285000.00, 79028.90, '1003', '86804364248762304', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.8，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (101, 20, '20210716', '9:31:32', '证券卖出', '601018', '宁波港', 3.91, -1700, 6648.00, 0, 5.32, 6.65, 0.14, 0.00, 0.00, 6635.89, 79978.48, '4370', '86804365053958800', 'A540420805', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (102, 20, '20210719', '20:29:10', '股息红利税补缴', '', '', 0.00, 0, 0.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -7.38, 85579.68, '0', '86804377133661696', NULL, '1790235186', '人民币', '股息红利税差异化处理资金下账 证券代码:\'601018\' 初始持有日期:\'20210406\'计税日期:\'20210716\' 税号:\'456780\'补税税率:\'0.100000\' 需纳税数量:\'900.000000\' 减持日期:\'20210716\',宁波港,900股', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (103, 20, '20210719', '20:29:10', '股息红利税补缴', '', '', 0.00, 0, 0.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -6.56, 85573.12, '0', '86804377133661696', NULL, '1790235186', '人民币', '股息红利税差异化处理资金下账 证券代码:\'601018\' 初始持有日期:\'20210527\'计税日期:\'20210716\' 税号:\'456787\'补税税率:\'0.100000\' 需纳税数量:\'800.000000\' 减持日期:\'20210716\',宁波港,800股', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (104, 20, '20210719', '19:53:08', '拆出报价购回', '660001', '报价标准券', 2.28, -2850, 285017.81, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 285017.81, 370590.91, '1003', '86804377133664304', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (105, 20, '20210719', '19:54:50', '报价回购拆出', '660001', '报价标准券', 2.26, 2900, 290000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -290000.00, 80590.92, '1431', '86804377133664304', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.96，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (106, 20, '20210719', '10:24:28', '证券卖出', '002984', '森麒麟', 32.85, -200, 6570.00, 0, 5.26, 6.58, 0.00, 0.00, 0.00, 6558.16, 85587.06, '24495', '86804377934266096', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (107, 20, '20210720', '19:13:19', '拆出报价购回', '660001', '报价标准券', 2.26, -2900, 290017.97, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 290017.97, 370608.88, '1431', '86804381428630208', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (108, 20, '20210720', '19:14:37', '报价回购拆出', '660001', '报价标准券', 2.24, 2880, 288000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -288000.00, 82608.88, '1446', '86804381428630208', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.67，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (109, 20, '20210721', '19:33:42', '拆出报价购回', '660001', '报价标准券', 2.24, -2880, 288017.66, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 288017.66, 377271.56, '1446', '86804385723594704', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (110, 20, '20210721', '19:34:40', '报价回购拆出', '660001', '报价标准券', 2.24, 2910, 291000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -291000.00, 86271.56, '807', '86804385723594704', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.86，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (111, 20, '20210721', '14:19:45', '证券卖出', '002254', '泰和新材', 22.19, -300, 6657.00, 0, 5.33, 6.66, 0.00, 0.00, 0.00, 6645.01, 89253.89, '30262', '86804386527240096', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (112, 20, '20210722', '19:25:41', '拆出报价购回', '660001', '报价标准券', 2.24, -2910, 291017.88, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 291017.88, 372778.41, '807', '86804390018565504', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (113, 20, '20210722', '19:27:19', '报价回购拆出', '660001', '报价标准券', 2.24, 2920, 292000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -292000.00, 80778.42, '1345', '86804390018565504', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：53.76，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (114, 20, '20210722', '11:07:16', '证券买入', '002236', '大华股份', 22.53, 200, 4506.00, 200, 5.00, 0.00, 0.00, 0.00, 0.00, -4511.00, 81760.56, '31187', '86804390818673696', '110773014', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (115, 20, '20210723', '19:30:00', '拆出报价购回', '660001', '报价标准券', 2.24, -2920, 292053.75, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 292053.75, 373402.97, '1345', '86804394313532000', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (116, 20, '20210723', '19:31:33', '报价回购拆出', '660001', '报价标准券', 2.24, 2940, 294000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -294000.00, 79402.98, '2062', '86804394313532000', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：18.04，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (117, 20, '20210723', '14:39:43', '证券卖出', '300641', '正丹股份', 7.49, -700, 5243.00, 0, 5.00, 5.24, 0.00, 0.00, 0.00, 5232.76, 86011.18, '38093', '86804395116109104', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (118, 20, '20210723', '14:50:34', '证券卖出', '002553', '南方轴承', 14.16, -200, 2832.00, 300, 5.00, 2.83, 0.00, 0.00, 0.00, 2824.17, 88835.35, '39465', '86804395116109104', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (119, 20, '20210723', '14:53:52', '证券买入', '603859', '能科股份', 37.40, 200, 7480.00, 200, 5.98, 0.00, 0.15, 0.00, 0.00, -7486.13, 81349.22, '39549', '86804395116109104', 'A540420805', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (120, 20, '20210726', '19:42:37', '拆出报价购回', '660001', '报价标准券', 2.24, -2940, 294018.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 294018.03, 377722.72, '2062', '86804407198440096', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (121, 20, '20210726', '19:43:27', '报价回购拆出', '660001', '报价标准券', 2.20, 3000, 300000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -300000.00, 77722.71, '707', '86804407198440096', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：18.08，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (122, 20, '20210726', '13:48:00', '证券卖出', '002553', '南方轴承', 14.37, -300, 4311.00, 0, 5.00, 4.31, 0.00, 0.00, 0.00, 4301.69, 83704.67, '38672', '86804407999418496', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (123, 20, '20210727', '19:40:58', '拆出报价购回', '660001', '报价标准券', 2.20, -3000, 300018.09, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 300018.09, 387620.78, '707', '86804411493402000', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (124, 20, '20210727', '19:42:22', '报价回购拆出', '660001', '报价标准券', 2.12, 3080, 308000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -308000.00, 79620.77, '628', '86804411493402000', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.89，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (125, 20, '20210727', '10:28:03', '证券卖出', '002236', '大华股份', 20.88, -200, 4176.00, 0, 5.00, 4.18, 0.00, 0.00, 0.00, 4166.82, 81889.53, '21117', '86804412298383200', '110773014', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (126, 20, '20210727', '14:34:49', '证券卖出', '600452', '涪陵电力', 14.31, -400, 5724.00, 0, 5.00, 5.72, 0.12, 0.00, 0.00, 5713.16, 87602.69, '44240', '86804412298383200', 'A540420805', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (127, 20, '20210728', '19:36:27', '拆出报价购回', '660001', '报价标准券', 2.12, -3080, 308017.88, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 308017.88, 395061.12, '628', '86804415788372000', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (128, 20, '20210728', '19:37:57', '报价回购拆出', '660001', '报价标准券', 2.12, 3140, 314000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -314000.00, 81061.12, '615', '86804415788372000', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：18.24，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (129, 20, '20210728', '9:32:59', '证券卖出', '603859', '能科股份', 35.80, -200, 7160.00, 0, 5.73, 7.16, 0.14, 0.00, 0.00, 7146.97, 86767.74, '3899', '86804416589972192', 'A540420805', '1790235186', '人民币', '证券卖出', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (130, 20, '20210728', '10:08:14', '证券卖出', '603050', '科林电气', 13.50, -400, 5400.00, 0, 5.00, 5.40, 0.11, 0.00, 0.00, 5389.49, 92157.23, '15158', '86804416589972192', 'A540420805', '1790235186', '人民币', '证券卖出', 1);
|
|
INSERT INTO `tb_trans_detail` VALUES (131, 20, '20210728', '14:49:47', '证券买入', '300682', '朗新科技', 17.03, 300, 5109.00, 300, 5.00, 0.00, 0.00, 0.00, 0.00, -5114.00, 87043.23, '34473', '86804416589972192', '110773014', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (132, 20, '20210729', '19:54:33', '拆出报价购回', '660001', '报价标准券', 2.12, -3140, 314018.25, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 314018.25, 389929.25, '615', '86804420083336192', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (133, 20, '20210729', '19:56:11', '报价回购拆出', '660001', '报价标准券', 2.12, 3100, 310000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -310000.00, 79929.26, '907', '86804420083336192', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：54.02，提前终止年利率：.3-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (134, 20, '20210729', '14:51:32', '证券买入', '601965', '中国汽研', 17.15, 300, 5145.00, 300, 5.00, 0.00, 0.10, 0.00, 0.00, -5150.10, 75911.02, '45505', '86804420883340000', 'A540420805', '1790235186', '人民币', '证券买入', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (135, 20, '20210730', '19:23:14', '拆出报价购回', '660001', '报价标准券', 2.12, -3100, 310054.03, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 310054.03, 389983.28, '907', '86804424378303008', '110773014', '1790235186', '人民币', '深圳报价回购(购回交易)-132002', 0);
|
|
INSERT INTO `tb_trans_detail` VALUES (136, 20, '20210730', '19:24:54', '报价回购拆出', '660001', '报价标准券', 2.12, 3040, 304000.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, -304000.00, 85983.28, '946', '86804424378303008', '110773014', '1790235186', '人民币', '深圳报价回购(初始交易):正常购回利息：17.66，提前终止年利率：.3-132002', 0);
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for test2
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `test2`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `test2`(IN p_month VARCHAR(10))
|
|
BEGIN
|
|
DECLARE v_stock_code VARCHAR(10) DEFAULT "";
|
|
|
|
DECLARE v_sum_trans_quantity int DEFAULT -1;
|
|
# 总行数
|
|
DECLARE cnt INT DEFAULT 0;
|
|
|
|
# 循环变量i
|
|
DECLARE i INT DEFAULT 0;
|
|
|
|
DEClARE trans_detail_cursor CURSOR FOR
|
|
SELECT distinct a.stock_code FROM tb_trans_detail a WHERE a.busi_type = '证券卖出'
|
|
and SUBSTR(trans_date,1,6) = p_month and gather_flag = 0;
|
|
|
|
SELECT count(distinct a.stock_code) INTO cnt FROM tb_trans_detail a WHERE a.busi_type = '证券卖出'
|
|
and SUBSTR(trans_date,1,6) = p_month and gather_flag = 0;
|
|
|
|
OPEN trans_detail_cursor;
|
|
|
|
REPEAT
|
|
|
|
SET i := i+1;
|
|
|
|
FETCH trans_detail_cursor into v_stock_code;
|
|
|
|
|
|
-- set v_igt_id = -1;
|
|
|
|
SELECT sum(trans_quantity) into v_sum_trans_quantity FROM tb_trans_detail a WHERE
|
|
gather_flag = 0 and stock_code = v_stock_code;
|
|
|
|
IF v_sum_trans_quantity = 0 THEN -- 数量未零是已经完成的交易
|
|
|
|
SELECT v_stock_code;
|
|
|
|
INSERT INTO tb_stock_trans (
|
|
buy_trans_date,
|
|
sell_trans_date,
|
|
stock_code,
|
|
stock_name,
|
|
cost_price,
|
|
sell_price,
|
|
trans_quantity,
|
|
cost_amount,
|
|
sell_amount,
|
|
profit_loss_amount,
|
|
profit_loss_percent,
|
|
total_charge,
|
|
shareholder_code,
|
|
capital_account
|
|
)
|
|
SELECT buy_trans_date,
|
|
sell_trans_date,
|
|
a.stock_code,
|
|
stock_name,
|
|
cost_price,
|
|
sell_price,
|
|
buy_sum_trans_quantity,
|
|
cost_amount,
|
|
sell_amount,
|
|
sell_amount-cost_amount profit_loss_amount,
|
|
(sell_amount-cost_amount)/cost_amount profit_loss_percent,
|
|
sell_charge+cost_charge total_charge,
|
|
shareholder,
|
|
capitalaccount
|
|
from
|
|
(SELECT max(shareholder_code) shareholder,
|
|
max(capital_account) capitalaccount,
|
|
max(trans_date) sell_trans_date,
|
|
max(stock_code) stock_code,
|
|
max(stock_name) stock_name,
|
|
|
|
sum(trans_quantity) sell_sum_trans_quantity,
|
|
sum(trans_amount)/sum(-trans_quantity) sell_price,
|
|
sum(trans_amount) sell_amount,
|
|
sum(service_charge)+sum(stamp_duty)+sum(transfer_fee)+sum(additional_charge) sell_charge
|
|
|
|
FROM tb_trans_detail a WHERE a.busi_type = '证券卖出' and gather_flag = 0 and stock_code = v_stock_code) a,
|
|
(SELECT min(trans_date) buy_trans_date,
|
|
max(stock_code) stock_code,
|
|
sum(trans_quantity) buy_sum_trans_quantity,
|
|
sum(trans_amount)/sum(trans_quantity) cost_price,
|
|
sum(trans_amount) cost_amount,
|
|
sum(service_charge)+sum(stamp_duty)+sum(transfer_fee)+sum(additional_charge) cost_charge
|
|
FROM tb_trans_detail a WHERE a.busi_type = '证券买入' and gather_flag = 0 and stock_code = v_stock_code) b
|
|
WHERE a.stock_code = b.stock_code and a.stock_code = v_stock_code;
|
|
|
|
UPDATE tb_trans_detail SET gather_flag = 1 and stock_code = v_stock_code;
|
|
|
|
END IF;
|
|
|
|
UNTIL i>=cnt END REPEAT;
|
|
# 循环结束
|
|
|
|
CLOSE trans_detail_cursor;
|
|
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|