Shell's Home

Jan 25, 2008 - 4 minute read - Comments

财务数据库

贝壳最近工作繁忙,一般都是晚上十一点到家睡觉,第二天早上继续上班的那种,所以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 ;

怎么样,很简单实用吧。建议实用mysql+mysqlgui。图形的录入和统计,方便的备份。比以前写的方便多了。