财务数据库 // Shell's Home

贝壳最近工作繁忙，一般都是晚上十一点到家睡觉，第二天早上继续上班的那种，所以blog基本没怎么动。现在放篇财务数据库的原型，大家参考一下吧。当然，是对程序员而言。像六牙四皂小姐这种下面估计是压根看不懂的，而且也不会有那种变态的资金准度要求。

首先建立一个账户表。

DROP TABLE IF EXISTS `accont_info`;
CREATE TABLE `accont_info` (
	   `id` int(11) NOT NULL auto_increment,
	   `username` varchar(40) NOT NULL,
	   `accont` varchar(40) NOT NULL,
	   `accont_type` int(11) NOT NULL default '0',
	   PRIMARY KEY (`id`),
	   UNIQUE KEY `username` (`username`,`accont`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;

输入用户名，账户名和账户类型，例如"许智翔",“招行账户”,2。账户类型中规定1是现金，2是存款，3是信用卡，4以上不计算。这样可以使用多个现金账户，存款账户和信用卡账户。然后利用子查询把所有类型间的相互行为统计出来。

然后是类型表。

DROP TABLE IF EXISTS `type_info`;
CREATE TABLE `type_info` (
	   `id` int(11) NOT NULL auto_increment,
	   `type` varchar(40) NOT NULL,
	   `subtype` varchar(40) default NULL,
	   PRIMARY KEY (`id`),
	   UNIQUE KEY `type` (`type`,`subtype`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=gbk;

最后是资金流动数据表，accont_id中填写出户账户名，to_accont中填写入户账户名。如果是外部(例如从别人那里拿钱或者给别人钱)，则写0。happen_time上填写发生时间，money上写金额，message上写备忘。

DROP TABLE IF EXISTS `money_info`;
CREATE TABLE `money_info` (
	`id` int(11) NOT NULL auto_increment,
	`accont_id` int(11) NOT NULL,
	`to_accont` int(11) default NULL,
	`happen_time` datetime NOT NULL,
	`type` int(11) NOT NULL,
	`record_modify` timestamp NOT NULL default CURRENT_TIMESTAMP on
	update CURRENT_TIMESTAMP,
	`money` decimal(9,2) NOT NULL,
	`message` varchar(400) default NULL,
	PRIMARY KEY (`id`),
	KEY `happen_time` (`happen_time`,`type`,`accont_id`)
) ENGINE=InnoDB AUTO_INCREMENT=914 DEFAULT CHARSET=gbk;

下面就是核心代码了。这个存储过程需要两个时间，统计的开始和结束时间。可以得出两个表，这段时间的收支和余额表，分类支出表。

DELIMITER $$
DROP PROCEDURE IF EXISTS `money`.`count_all` $$
CREATE DEFINER=`shell`@`%` PROCEDURE `count_all`(IN start_date
DATETIME, IN end_date DATETIME)
BEGIN
	declare cash_get DECIMAL(9,2);
	declare bank_get DECIMAL(9,2);
	declare card_get DECIMAL(9,2);
	declare cash_used DECIMAL(9,2);
	declare cash_bank DECIMAL(9,2);
	declare bank_used DECIMAL(9,2);
	declare bank_cash DECIMAL(9,2);
	declare card_used DECIMAL(9,2);
	select sum(money) into cash_get from money_info where
	happen_time>=start_date and happen_time<end_date and
	to_accont in (SELECT id FROM accont_info where accont_type=1);
	select sum(money) into bank_get from money_info where
	happen_time>=start_date and happen_time<end_date and
	to_accont in (SELECT id FROM accont_info where accont_type=2);
	select sum(money) into card_get from money_info where
	happen_time>=start_date and happen_time<end_date and
	to_accont in (SELECT id FROM accont_info where accont_type=3);
	select sum(money) into cash_used from money_info where
	happen_time>=start_date and happen_time<end_date and
	accont_id in (SELECT id FROM accont_info where accont_type=1);
	select sum(money) into cash_bank from money_info where
	happen_time>=start_date and happen_time<end_date and
	accont_id in (SELECT id FROM accont_info where accont_type=1) and
	to_accont in (SELECT id FROM accont_info where accont_type=2);
	select sum(money) into bank_used from money_info where
	happen_time>=start_date and happen_time<end_date and
	accont_id in (SELECT id FROM accont_info where accont_type=2);
	select sum(money) into bank_cash from money_info where
	happen_time>=start_date and happen_time<end_date and
	accont_id in (SELECT id FROM accont_info where accont_type=2) and
	to_accont in (SELECT id FROM accont_info where accont_type=1);
	select sum(money) into card_used from money_info where
	happen_time>=start_date and happen_time<end_date and
	accont_id in (SELECT id FROM accont_info where accont_type=3);
	select "开始日期",start_date
	union
	select "结束日期",end_date
	union
	select "现金总收入",cash_get
	union
	select "现金总支出",cash_used
	union
	select " 现金存款",cash_bank
	union
	select " 现金使用",cash_used-cash_bank
	union
	select "现金总余额",cash_get-cash_used
	union
	select "总存款",bank_get
	union
	select "存款使用",bank_used
	union
	select " 银行提款",bank_cash
	union
	select " 信用卡使用",card_used
	union
	select " 其他使用",bank_used-bank_cash-card_used
	union
	select "存款余额",bank_get-bank_used
	union
	select "信用卡平衡",card_get-card_used
	;
	select m.type as typeid,t.type as typename,sum(m.money)as money from
	money_info m left join type_info t on m.type=t.id
	where happen_time>=start_date and happen_time<end_date and
	to_accont=0 group by m.type;
END $$
DELIMITER ;

下面这个是上面的辅助代码，统计这个月的数据。

DELIMITER $$
DROP PROCEDURE IF EXISTS `money`.`count_this_month` $$
CREATE DEFINER=`shell`@`%` PROCEDURE `count_this_month`()
BEGIN
	set @now_start=if(day(current_date())<25,
	date_format(date_sub(current_date(),interval 1 month),'%Y-%m-25'),
	date_format(current_date(),'%Y-%m-25'));
	set @now_end=date_add(@now_start,interval 1 month);
	CALL count_all(@now_start,@now_end);
END $$
DELIMITER ;

最后这个是统计历史的收入，支出，现金支出，银行支出，信用卡支出和其他支出。

DELIMITER $$
DROP PROCEDURE IF EXISTS `money`.`count_month` $$
CREATE DEFINER=`shell`@`%` PROCEDURE `count_month`()
BEGIN
	create temporary table temp_in(
		yearmonth datetime,
		money DECIMAL(9,2)
	);
	create temporary table temp_out(
		yearmonth datetime,
		money DECIMAL(9,2)
	);
	create temporary table temp_cash(
		yearmonth datetime,
		money DECIMAL(9,2)
	);
	create temporary table temp_bank(
		yearmonth datetime,
		money DECIMAL(9,2)
	);
	create temporary table temp_card(
		yearmonth datetime,
		money DECIMAL(9,2)
	);
	insert into temp_in
	select date_format(date_sub(happen_time, interval 24 day),"%Y-%m-25")
	as yearmonth,sum(money)
	from money_info where accont_id=0 group by yearmonth;
	insert into temp_out
	select date_format(date_sub(happen_time, interval 24 day),"%Y-%m-25")
	as yearmonth,sum(money)
	from money_info where to_accont=0 group by yearmonth;
	insert into temp_cash
	select date_format(date_sub(happen_time, interval 24 day),"%Y-%m-25")
	as yearmonth,sum(money)
	from money_info where to_accont=0 and accont_id in (select id from
	accont_info where accont_type=1) group by yearmonth;
	insert into temp_bank
	select date_format(date_sub(happen_time, interval 24 day),"%Y-%m-25")
	as yearmonth,sum(money)
	from money_info where to_accont=0 and accont_id in (select id from
	accont_info where accont_type=2) group by yearmonth;
	insert into temp_card
	select date_format(date_sub(happen_time, interval 24 day),"%Y-%m-25&
	quot;) as yearmonth,sum(money)
	from money_info where to_accont=0 and accont_id in (select id from
	accont_info where accont_type=3) group by yearmonth;
	select o.yearmonth as startday,date_add(o.yearmonth,interval 1 month)
	as endday,
	ifnull(i.money,0) as money_in,ifnull(o.money,0) as money_out,ifnull
	(c.money,0) as money_cash,
	ifnull(b.money,0) as money_bank,ifnull(d.money,0) as money_card,
	ifnull(o.money,0)-ifnull(c.money,0)-ifnull(b.money,0)-ifnull(d.money,0)
	as money_other
	from temp_out o
	left join temp_in i on o.yearmonth=i.yearmonth
	left join temp_cash c on o.yearmonth=c.yearmonth
	left join temp_bank b on o.yearmonth=b.yearmonth
	left join temp_card d on o.yearmonth=d.yearmonth;
	drop temporary table if exists
	temp_in,temp_out,temp_cash,temp_bank,temp_card;
END $$
DELIMITER ;