006.内测.情景之迷你财务记账

[img]http://www.moilioncircle.com/images/post/actions/006/006.jpg[/img]

[b]# 006.内测.情景之迷你财务记账[/b]

迷你版的财务记账，包括了余额，交易和明细三块。
能够满足基本的记账要求：对账，红订正，做报表。

@史荣久 / 2015-03-01 / CC-BY-SA-3.0

[b]## 任务说明[/b]

用户1（U1），余额0元，用户1有三件物品出售。
物品1，10元；物品2，20元；物品3，30元；
用户2（U2），余额500元，想买用户1的物品。

余额表，可更新，只关心用户的余额情况。
交易表，只插入，只关心每笔交易的金额和流向。
明细表，只插入，只关心每笔交易的金额对物品分配。

在过去在纸质帐本上，用黑笔记账，称为黑数据。
订正时，用红笔划掉黑数据，填上新数据，称为红数据。
红订正，指上述过程，而此处指对一笔交易的完全取消。
即，插入和黑数据的'金额'，'记录ID'相反的数据。

[b]## 数据关系[/b]

[img]http://www.moilioncircle.com/images/post/actions/006/erd.png[/img]

CREATE TABLE IF NOT EXISTS `CASH_ACCOUNT` (
  `UID` INT NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`UID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `ITEM` (
  `IID` INT NOT NULL COMMENT '物品ID',
  `PRICE` DECIMAL(10,2) NOT NULL COMMENT '价格',
  PRIMARY KEY (`IID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `CASH_BALANCE` (
  `UID` INT NOT NULL COMMENT '用户ID',
  `BALANCE` DECIMAL(10,2) NOT NULL COMMENT '余额',
  `CREATE_ON` MEDIUMTEXT NOT NULL COMMENT '创建事件',
  `UPDATE_ON` MEDIUMTEXT NOT NULL COMMENT '更新事件',
  PRIMARY KEY (`UID`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `CASH_TRANSACTION` (
  `TID` BIGINT NOT NULL COMMENT '交易ID',
  `RECORD_ID` INT NOT NULL COMMENT '记录ID',
  `AMOUNT` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
  `BUYER_ID` INT NOT NULL COMMENT '买家UID',
  `SELLER_ID` INT NOT NULL COMMENT '卖家UID',
  `CREATE_ON` MEDIUMTEXT NOT NULL COMMENT '创建事件',
  `CREATE_AT` DATETIME NOT NULL COMMENT '插入时间',
  `EXTRA_INFO` VARCHAR(255) NULL COMMENT '附加信息',
  PRIMARY KEY (`TID`, `RECORD_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS `CASH_TXDETAIL` (
  `TID` BIGINT NOT NULL COMMENT '交易ID',
  `RECORD_ID` INT NOT NULL COMMENT '记录ID',
  `DETAIL_ID` INT NOT NULL COMMENT '明细ID',
  `AMOUNT` DECIMAL(10,2) NOT NULL COMMENT '金额',
  `ITEM_ID` INT NOT NULL COMMENT '物品ID',
  PRIMARY KEY (`TID`, `RECORD_ID`, `DETAIL_ID`))
ENGINE = InnoDB;

-- 初始数据
INSERT INTO `ITEM`(`IID`,`PRICE`) VALUES
(1,10),(2,20),(3,30);

INSERT INTO `CASH_ACCOUNT`(`UID`) VALUES
(1),(2);

INSERT INTO `CASH_BALANCE`
(`UID`,`BALANCE`,`CREATE_ON`,`UPDATE_ON`)
VALUES
(1,0,1,'2015-01-01 0:0:0'),
(2,500,1,'2015-01-01 0:0:0');

## 情景举例

【情景一】用户2买了用户1的物品各一件。数据变化如下：
假设购买事件ID为2，当前时间为'2015-01-10 09:21:45'

-- 余额变化
UPDATE `CASH_BALANCE` SET 
`BALANCE` = `BALANCE` + 60, `UPDATE_ON` = 2
WHERE `UID` = 1;

UPDATE `CASH_BALANCE` SET 
`BALANCE` = `BALANCE` - 60, `UPDATE_ON` = 2
WHERE `UID` = 2;

-- 交易记录
INSERT INTO `CASH_TRANSACTION` (`TID`,
`RECORD_ID`,`AMOUNT`,`BUYER_ID`,`SELLER_ID`,
`CREATE_ON`,`CREATE_AT`,`EXTRA_INFO`)
VALUES (1,1,60,2,1,2,
'2015-01-10 09:21:45','样本数据');

-- 明细记录
INSERT INTO `CASH_TXDETAIL` (`TID`,
`RECORD_ID`,`DETAIL_ID`,`AMOUNT`,`ITEM_ID`)
VALUES
(1,1,1,10,1),(1,1,2,20,2),(1,1,3,30,3);

【情景二】红订正，取消情景一的交易。数据变化如下：
假设订正事件ID为3，当前时间为'2015-01-10 10:21:45'

-- 余额变化
UPDATE `CASH_BALANCE` SET 
`BALANCE` = `BALANCE` - 60, `UPDATE_ON` = 3
WHERE `UID` = 1;

UPDATE `CASH_BALANCE` SET 
`BALANCE` = `BALANCE` + 60, `UPDATE_ON` = 3
WHERE `UID` = 2;

-- 交易记录
INSERT INTO `CASH_TRANSACTION` (`TID`,
`RECORD_ID`,`AMOUNT`,`BUYER_ID`,`SELLER_ID`,
`CREATE_ON`,`CREATE_AT`,`EXTRA_INFO`)
SELECT `TID`,
-`RECORD_ID`,-`AMOUNT`,`BUYER_ID`,`SELLER_ID`,
3,'2015-01-10 10:21:45',`EXTRA_INFO`
FROM `CASH_TRANSACTION` WHERE `TID` = 1

存在红订正的交易，SUM(AMOUNT)=0。

[b]## 问题来了[/b]

（1）写一个交易服务，支持以上情景。

（2）做一个报告（文字即可），显示2015年，
卖的最多的10件物品（含取消的）
退货最多的10件物品（交易取消的）
消费最多的10个用户(不含取消)

（3）用户1投诉：2015元旦，账户金额异常。
请问如何为其查账？
对于查账，本系统需要哪些改进。

----
题图：晋商票号账本。晋商票号，纵横明清500年的"华尔街"，跨国的金融大鳄，创下诸多辉煌。
原文：[url]http://www.moilioncircle.com/actions/006.quiz.case-mini-accounting.html[/url]