现金银行表:
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;
2711
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
