爱可深思▼ 2015▼ 内测 情景 财务 明细 订正

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

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

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

任务说明

用户1(U1),余额0元,用户1有三件物品出售。
物品1,10元;物品2,20元;物品3,30元;
用户2(U2),余额500元,想买用户1的物品。

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

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

数据关系

ER图和数据

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。

问题来了

(1)写一个交易服务,支持以上情景。

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

(3)用户1投诉:2015元旦,账户金额异常。
请问如何为其查账?
对于查账,本系统需要哪些改进。

《006.内测.情景之迷你财务记账》 迷你版的财务记账,包括了余额,交易和明细三块。能够满足基本的业务功能:报表,对账,红订正。
题图:晋商票号账本。晋商票号,纵横明清500年的"华尔街",跨国的金融大鳄,创下诸多辉煌。