现金银行表:
CREATE TABLE bank
(
database_id bigint NOT NULL, -- 表主键||表主健
branch_id character varying(50), -- branch_id||branch_id(传输相关)
copy_version bigint, -- copy_version||copy_version(传输相关)
created_time date, -- created_time||created_time(传输相关)
data_creator character varying(50), -- data_creator||data_creator(传输相关)
data_owner character varying(50), -- data_owner||data_owner(传输相关)
handle_person character varying(100),-- handle_person||handle_person(from order to orderitem)
last_modify_time date, -- last_modify_time||last_modify_time(传输相关)
last_overwrite_time date, -- last_overwrite_time||last_overwrite_time(传输相关)
last_uploaded_time date, -- last_uploaded_time||last_uploaded_time(传输相关)
target_branch character varying(50), -- target_branch||target_branch(传输相关)
transmit_status integer, -- transmit_status||transmit_status(传输相关)
"version" bigint, -- version||version(传输相关)
bank_id character varying(255), -- 账户ID||账户ID
bank_short character varying(255), -- 账户名称||账户名称
bank_type character varying(128), -- 账户类型(现金类/银行存款类)||账户类型
bank_account character varying(255), -- 银行账号||银行账号
bank_fullname character varying(100), -- 银行全名||银行全名
init_date date, -- 开户日期||开户日期
init_amount numeric(18,8), -- 初始金额||初始金额
balance numeric(18,8), -- 当前的余额||余额
subject_code character varying(255),
CONSTRAINT pk_bank PRIMARY KEY (database_id)
)
订单支付记录表:
CREATE TABLE order_payment_record
(
database_id bigint NOT NULL, -- 主键||主键
"version" bigint,
target_branch character varying(50),
transmit_status bigint,
branch_id character varying(50), -- branch_id||branch_id(传输相关)
copy_version bigint, -- copy_version||copy_version(传输相关)
created_time date, -- created_time||created_time(传输相关)
data_creator character varying(50), -- data_creator||data_creator(传输相关)
data_owner character varying(50), -- data_owner||data_owner(传输相关)
handle_person character varying(100), -- handle_person||handle_person(from order to orderitem)
last_modify_time date, -- last_modify_time||last_modify_time(传输相关)
last_overwrite_time date, -- last_overwrite_time||last_overwrite_time(传输相关)
last_uploaded_time date, -- last_uploaded_time||last_uploaded_time(传输相关)
order_number character varying(255), -- 订单号||订单号
self_order_number character varying(255), -- 自编单号||自编单号
order_type character varying(255), -- 订单类型||订单类型
order_total numeric(18,8), -- 订单总额||订单总额
balance numeric(18,8), -- 当前的余额||余额
bank_account character varying(255), -- 银行账号||银行账号
bank_fullname character varying(100), -- 银行全名||银行全名
bank_short character varying(255), -- 账户名称||账户名称
bank_type character varying(128), -- 账户类型(现金类/银行存款类)||账户类型
init_amount numeric(18,8), -- 初始金额||初始金额
init_date date, -- 开户日期||开户日期
bank_id character varying(255), -- 账户ID||账户ID
payment_flag boolean, -- 收付款标志||收付款标志
cur_paid_amount numeric(18,8), -- 本次付款金额||本次付款金额
payment_method character varying(255), -- 付款方式||付款方式(多付款/其他)
order_date date, -- order_date||order_date(订单日期相关)
order_month character varying(20), -- order_month||order_month(订单日期相关)
order_state character varying(64), -- order_state||order_state(from order to orderitem)
order_year character varying(20), -- order_year||order_year(订单日期相关)
user_define1 character varying(64), -- 用户定义字段1||用户定义字段1
user_define2 character varying(64), -- 用户定义字段2||用户定义字段2
user_define3 character varying(64), -- 用户定义字段3||用户定义字段3
user_define4 character varying(64), -- 用户定义字段4||用户定义字段4
user_define5 character varying(64), -- 用户定义字段5||用户定义字段5
plus_minus_flag integer, -- 表明金额是正还是付||表明金额是正还是付
CONSTRAINT pk_order_payment_info PRIMARY KEY (database_id)
日期表：（用户动态选择时间查询）
CREATE TABLE dates
(
database_id bigint NOT NULL,
date_id integer,
start_date timestamp without time zone,
end_date timestamp without time zone,
stock_benchmark integer,
partner_name character varying(50),
notes character varying(255),
partner_id character varying(128),
CONSTRAINT dates_pkey PRIMARY KEY (database_id)
)
1. 初始银行当前余额
CREATE OR REPLACE VIEW view_class_bank_balance_info AS
SELECT tempv_bank_current_balance_info.database_id, tempv_bank_current_balance_info.bank_fullname, tempv_bank_current_balance_info.balance, tempv_bank_current_balance_info.current_balance
FROM tempv_bank_current_balance_info
WHERE tempv_bank_current_balance_info.balance<>tempv_bank_current_balance_info.current_balance;
CREATE OR REPLACE VIEW tempv_bank_current_balance_info AS
SELECT b.database_id, b.bank_fullname,
CASE
WHEN b.balance IS NULL THEN 0::numeric
ELSE b.balance
END AS balance, c.current_balance
FROM bank b
LEFT JOIN ( SELECT a.bank_fullname, sum(a.balance_each) AS current_balance
FROM(SELECT order_payment_record.database_id, order_payment_record.bank_fullname,
CASE
WHEN order_payment_record.plus_minus_flag = 1 THEN order_payment_record.cur_paid_amount
WHEN order_payment_record.plus_minus_flag = -1 THEN - order_payment_record.cur_paid_amount
ELSE 0::numeric
END AS balance_each
FROM order_payment_record
WHERE order_payment_record.cur_paid_amount IS NOT NULL
UNION ALL
SELECT bank.database_id, bank.bank_fullname, bank.init_amount AS balance_each
FROM bank) a
GROUP BY a.bank_fullname) c ON b.bank_fullname::text = c.bank_fullname::text;
2.选择时间处理明细
CREATE OR REPLACE VIEW view_class_bank_flow_detail AS
SELECT a.database_id, a.order_number, a.self_order_number, a.order_date, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,
CASE
WHEN a.plus_minus_flag = 1 THEN '收入'::text
WHEN a.plus_minus_flag = -1 THEN '支出'::text
ELSE ''::text
END AS inout_flag, a.plus_minus_flag
FROM order_payment_record a
WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name
FROM dates
WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date
FROM dates
WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date
FROM dates
WHERE dates.date_id = 3));
private void refreshBalanceDetail() {
String bankFullName = m_bank.getText();
Date[] orig = changeDate((Date) m_startDate.getValue(),
(Date) m_endDate.getValue(), getDateID(), bankFullName);
try {
List bankDetaillist = null;
String query = "from ViewClassBankFlowDetail o";
bankDetaillist = getDataSourceManager().universalQuery(query, null);
m_balanceDetailTable.insertList(bankDetaillist, true);
List list = null;
String query2 = "from ViewClassBankFlowGeneral o";
list = getDataSourceManager().universalQuery(query2, null);
ViewClassBankFlowGeneral balanceGeneral = new ViewClassBankFlowGeneral();
balanceGeneral.addBalanceList(list);
restoreBalanceGeneral(balanceGeneral);
} catch (Exception ex) {
ex.printStackTrace();
}
changeDate(orig, getDateID());
}
private Date[] changeDate(Date[] dates, int id) {
Date start = dates[0];
Date end = dates[1];
return changeDate(start, end, id, null);
}
//Date[0]--> startDate, Date[1]-->endDate
private Date[] changeDate(Date newStartDate, Date newEndDate, int id,
String partName) {
try {
String query = "from Dates d where d.dateID=" + id;
List list = getDataSourceManager().universalQuery(query, null);
Dates dates = (Dates) list.get(0);
Date oldStartDate = dates.getStartDate();
Date oldEndDate = dates.getEndDate();
// set new dates and save it
if (null != newStartDate) {
dates.setStartDate(newStartDate);
}
if (null != newEndDate) {
Calendar calendar = Calendar
.getInstance(new Locale("zh", "cn"));
calendar.setTime(newEndDate);
calendar.add(Calendar.DATE, 1);
newEndDate = calendar.getTime();
dates.setEndDate(newEndDate);
}
if (null != partName) {
dates.setPartnerName(partName);
} else {
dates.setPartnerName(null);
}
getDataSourceManager().universalSaver(dates);
Date[] result = new Date[2];
result[0] = oldStartDate;
result[1] = oldEndDate;
return result;
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
2. 选择时间处理期初，本期收入，本期支出等
CREATE OR REPLACE VIEW view_class_bank_flow_general AS
( SELECT a.bank_fullname, '期初余额' AS balance_name, sum(a.balance) AS balance
FROM ( SELECT a.order_number, a.order_type, a.bank_fullname,
CASE
WHEN a.plus_minus_flag = 1 THEN a.cur_paid_amount
WHEN a.plus_minus_flag = -1 THEN - a.cur_paid_amount
ELSE 0::numeric
END AS balance,
CASE
WHEN a.plus_minus_flag = 1 THEN '收入'::text
WHEN a.plus_minus_flag = -1 THEN '支出'::text
ELSE ''::text
END AS inout_flag
FROM order_payment_record a
WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name
FROM dates
WHERE dates.date_id = 3))::text) AND a.order_date < (( SELECT dates.start_date
FROM dates
WHERE dates.date_id = 3))
UNION ALL
SELECT '', '', a.bank_fullname, a.init_amount, '余额'
FROM bank a
WHERE a.bank_fullname::text = ((( SELECT dates.partner_name
FROM dates
WHERE dates.date_id = 3))::text)) a
GROUP BY a.bank_fullname
UNION ALL
SELECT a.bank_fullname, '本期支出' AS balance_name, sum(a.balance) AS balance
FROM ( SELECT a.order_number, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,
CASE
WHEN a.plus_minus_flag = 1 THEN '收入'::text
WHEN a.plus_minus_flag = -1 THEN '支出'::text
ELSE ''::text
END AS inout_flag
FROM order_payment_record a
WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name
FROM dates
WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date
FROM dates
WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date
FROM dates
WHERE dates.date_id = 3))) a
WHERE a.inout_flag = '支出'::text
GROUP BY a.bank_fullname)
UNION ALL
SELECT a.bank_fullname, '本期收入' AS balance_name, sum(a.balance) AS balance
FROM ( SELECT a.order_number, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,
CASE
WHEN a.plus_minus_flag = 1 THEN '收入'::text
WHEN a.plus_minus_flag = -1 THEN '支出'::text
ELSE ''::text
END AS inout_flag
FROM order_payment_record a
WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name
FROM dates
WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date
FROM dates
WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date
FROM dates
WHERE dates.date_id = 3))) a
WHERE a.inout_flag = '收入'::text
GROUP BY a.bank_fullname;
分享到：
相关推荐
视图工具视图工具视图工具视图工具视图工具视图工具视图工具视图工具视图工具
图片懒加载判断图片是否滚动到视图内，使用jquery.lazyload.js插件，还得大于五十个字，真麻烦随便凑凑字吧，仅供参考
对话框，视图 对话框，视图 对话框，视图
在采用非RUP标准工具进行RUP建模时，常常不知道如何组织建模元素，本文件中的两幅图片分别是 常规视图+模型 和 简单视图+模型 的组织结构参考。（当然你可能根据自己的需要自定义合适的结构）
③ 在查询分析器中用SQL语句来创建视图View3和视图View4，视图View3的要求与View1相同，视图View4与视图View2的条件相同。 2、数据控制实验 ① 假定系统有U1、U2、U3 、U4、U5、U6六个登录用户，试将查询图书表的...
VC 2010 单文档多视图的例子，仅供参考
这是滚动视图与列表视图的共存效果源码，我们知道滚动视图与列表视图共存会存在滚动的问题，并且列表视图只显示一个半选项。 只要当列表视图的高度设定一定的值时，列表视图同样地会显示对应的高度的选项。 因此我们...
QQ的视图视图、列表视图、网格视图和滚动视图。越界
MFC视图编程 MFC视图编程 MFC视图编程 MFC视图编程
详细的介绍mfc树视图与列表视图的创建与用法，方便初学者学习
参考资料-project日历视图中的操作.zip
月视图和周视图切换控件来舒适的调节编制文件
通过视图的设计和定义，掌握建立视图的基本方法，包括通过单张表建立视图和通过多张表建立视图，通过在定义的视图上进行查询，删除，更新，插入操作，理解视图的概念，掌握简单的视图查询方法。 3. 实验预备知识 在...
计算机视觉中的多视图几何(英文版-第2版)，计算机视觉的经典教材
2、 建立视图名为成绩视图，包含学生的学号，姓名，年龄，课程号，成绩 3、 建立视图名为学生平均成绩，包含学生的姓名，学号，平均成绩 4、 视图可以更新表中的数据吗？ 5、 用视图成绩视图更新xsgl中的姓名字段...
2、修改视图，观察结果。 ⑴将计科系学生的视图cs_student中学号为200215122的学生姓名修改为“刘辰”。 ⑵向视图cs_student中插入一个新的学生记录。 ⑶删除视图cs_student中学号为200215123的记录。 3、删除...
【例2】创建计算机专业学生的平均成绩视图ccj_avg，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 【例3】创建视图ccj_avg_t5，显示计算机专业平均成绩前5名的学生，包括学号（在视图中...
（2）多源视图：建立每个会员的订单视图（包含会员编号、会员名称、订单编号、下单日期、货品总价）。 （3）在已有视图上定义的新视图：建立价格小于1000元的‘奔驰’品牌的汽车配件视图。 （4）表达式的视图：...
基于WINDOWS下的MFC滚动视图、HTML视图实用教程
用VC实现SDI上视图静态分割分割同时多个视图间进行通讯通讯，与数据访问