视图用法1

分析总结

1. 会员积分变化处理(使用4个视图)

会员表:

CREATE TABLE store_member

(

client_name character varying(255), 会员姓名

client_id character varying(255), 会员ID

init_pts bigint, 初始积分

available_pts bigint, 可用积分

total_pts bigint, 总积分

balance double precision, 账上余额

)

会员积分变化表:

CREATE TABLE member_pts_record

(

member_id character varying(50) NOT NULL, 会员ID

business_type character varying(50), 业务类型

record_type integer, 记录类型

total_points integer, 总积分(获得的积分)

used_points integer, 使用积分

)

<1>怎样修改会员的可用积分和总积分

MemberPtsSumupView对应的视图为:

CREATE OR REPLACE VIEW view_class_pts_sumup AS

SELECT p.client_id, p.total_pts, p.available_pts, p.new_total_pts, p.new_vailable_pts

FROM view_temp_pts_sumup_2 p

WHERE p.total_pts <> p.new_total_pts OR p.available_pts <> p.new_vailable_pts;

if (UnitInfoUtil.isThisHeadQuarter())—会员积分在总店进行

Session s = m_factory.openSession();

String query = "from MemberPtsSumupView ";

List list = s.createQuery(query).list();

//first update points

if (null != list && list.size() > 0) {

String idList = "(";

Transaction tx = s.beginTransaction();

try {

int total = 0;

for (int i = 0; i < list.size(); i++) {

MemberPtsSumupView info = (MemberPtsSumupView) list.get(i);

idList = idList + "'" + info.getMemberID() + "',";

String update = "update StoreMember"

+ " o set o.availablePoints="

+ info.getNewAvailabePointes()

+ ",o.totalPoints=" + info.getNewTotalPoints()

+ ",o.lastModifyTime=:time "

+ ",o.dataOwner=:owner "

+ " where o.clientID='" + info.getMemberID()

+ "'";

Query cmd = s.createQuery(update);

// update the last update time

cmd.setTimestamp("time", new Timestamp(System

.currentTimeMillis()));

// set dataOwner here

cmd.setString("owner", UnitInfoUtil.getThisUnitCode());

int num = cmd.executeUpdate();

total = total + num;

}

tx.commit();

m_logger

.info("update by MemberPtsSumupView,number updated="

+ total);

} catch (Exception ex) {

tx.rollback();

logException(ex);

}

(统计会员表和会员积分变化表

新的总积分=新总积分+初始化积分

新的可用积分=新的总积分+初始化积分-新的使用积分

)

CREATE OR REPLACE VIEW view_temp_pts_sumup_2 AS

SELECT p.client_id, p.total_pts, p.init_pts, p.available_pts, p.new_total_pts + p.init_pts AS new_total_pts, p.new_total_pts + p.init_pts - p.new_used_pts AS new_vailable_pts

FROM view_temp_pts_sumup p;

CREATE OR REPLACE VIEW view_temp_pts_sumup AS

SELECT o.client_id, o.total_pts, o.init_pts, o.available_pts,

CASE

WHEN p.new_total_pts IS NULL THEN 0::bigint

ELSE p.new_total_pts

END AS new_total_pts,

CASE

WHEN p.new_used_pts IS NULL THEN 0::bigint

ELSE p.new_used_pts

END AS new_used_pts

FROM store_member o

LEFT JOIN view_temp_pts_groupby p ON p.member_id::text = o.client_id::text;

(

计算 会员积分变化表

<1>统计会员的使用积分作为新的使用积分

<2>统计会员的总积分(会员获取的积分)做为新的总积分(总的会员获取积分)

)

CREATE OR REPLACE VIEW view_temp_pts_groupby AS

SELECT p.member_id ,sum(p.used_points) AS new_used_pts, sum(p.total_points) AS new_total_pts

FROM member_pts_record p

GROUP BY p.member_id;

2/会员的储值卡平衡(使用3个视图)

会员储值卡变化表

CREATE TABLE account_change_table

(

refer_order_number character varying(50), 引用的订单

business_type character varying(50), 业务类型

action_type character varying(50), 变化类型

member_id character varying(50), 会员ID

change_amount double precision, 变化量

)

if (UnitInfoUtil.isThisHeadQuarter()) {

Session s = m_factory.openSession();

Transaction tx = s.beginTransaction();

try {

String query = "from AccountChangeRecordView ";

List members = s.createQuery(query).list();

if (null != members && members.size() > 0) {

for (int i = 0; i < members.size(); i++) {

AccountChangeRecordView view = (AccountChangeRecordView) members

.get(i);

String memberQuery = "from StoreMember o where o.clientID='"

+ view.getMemberID() + "'";

List list = s.createQuery(memberQuery).list();

StoreMember member = (StoreMember) list.get(0);

member.setBalance(view.getRealBalance());

Timestamp modifyTime = new Timestamp(System

.currentTimeMillis());

member.setLastModifyTime(modifyTime);

// set data owner

member.setDataOwner(UnitInfoUtil.getThisUnitCode());

// increase copy version.

member.increaseCopyVersion();

s.saveOrUpdate(member);

}

}

tx.commit();

if (null != members && members.size() > 0) {

m_logger

.info("update by AccountChangeRecordView,number updated="

+ members.size());

}

} catch (Exception ex) {

tx.rollback();

logException(ex);

}

s.close();

}

AccountChangeRecordView对应的视图:

CREATE OR REPLACE VIEW view_class_member_balance_checker AS

SELECT p.member_id, p.balance, p.real_balance

FROM view_temp_member_balance_checker_2 p

WHERE abs(p.balance - p.real_balance) > 0.1::double precision;

CREATE OR REPLACE VIEW view_temp_member_balance_checker_2 AS

SELECT p.client_id AS member_id,

CASE

WHEN p.balance IS NULL THEN 0::double precision

ELSE p.balance

END AS balance,

CASE

WHEN k.real_balance IS NULL THEN 0::double precision

ELSE k.real_balance

END AS real_balance

FROM store_member p

LEFT JOIN view_temp_member_balance_checker_1 k ON k.member_id::text = p.client_id::text;

CREATE OR REPLACE VIEW view_temp_member_balance_checker_1 AS

SELECT p.member_id, sum(p.change_amount) AS real_balance

FROM account_change_table p

GROUP BY p.member_id;

会员积分变化和会员储值变化总结:都是采用原始记录表加变化记录表处理。视图的创建基本规则是:

<1>查询 变化记录的 视图

<2>查询 原始记录和变化记录视图的 视图

<3>计算原始记录和变化记录视图的 视图的 视图

<4>筛选符合规则的记录的视图

3处理供应商

-- Table: product_supplier

-- DROP TABLE product_supplier;

CREATE TABLE product_supplier

(

client_name character varying(255),

client_id character varying(255),

credit_amount double precision,

init_bal double precision,

current_bal double precision,

business_amount double precision,

paid double precision,

uppaid double precision,

)

// 5. handle product supplier

if (UnitInfoUtil.isThisHeadQuarter()) {

Session s = m_factory.openSession();

String query = "from ProductSupplierView ";

List list = s.createQuery(query).list();

if (null != list && list.size() > 0) {

Transaction tx = s.beginTransaction();

try {

int total = 0;

for (int i = 0; i < list.size(); i++) {

ProductSupplierView info = (ProductSupplierView) list

.get(i);

Double unpaid = new Double(info.getBusinessAmountReal()

.doubleValue()

- info.getReturnAmountReal().doubleValue());

String update = "update ProductSupplier"

+ " o set o.businessAmount="

+ info.getBusinessAmountReal() + ",o.paid="

+ info.getReturnAmountReal() + ",o.unpaid="

+ unpaid + ",o.currentBal=" + info.getBalReal()

+ ",o.lastModifyTime=:time "

+ ",o.dataOwner=:owner "

+ " where o.clientName='"

+ info.getClientName() + "'";

Query cmd = s.createQuery(update);

// set dataOwner here

cmd.setString("owner", UnitInfoUtil.getThisUnitCode());

// update the last update time

cmd.setTimestamp("time", new Timestamp(System

.currentTimeMillis()));

int num = cmd.executeUpdate();

total = total + num;

}

tx.commit();

m_logger

.info("update by ProductSupplierView,number updated="

+ total);

} catch (Exception ex) {

tx.rollback();

logException(ex);

}

}

s.close();

}

ProductSupplierView对应的视图：

CREATE OR REPLACE VIEW view_class_supplier_sumup AS

SELECT p.client_name, p.business_amount, p.in_amount, p.paid, p.out_amount, p.current_bal, p.real_bal

FROM view_temp_supplier_sumup_2 p

WHERE abs(p.business_amount - p.in_amount) > 0.1::double precision OR abs(p.paid - p.out_amount) > 0.1::double precision OR abs(p.current_bal - p.real_bal) > 0.1::double precision;

CREATE OR REPLACE VIEW view_temp_supplier_sumup_2 AS

SELECT p.client_name, p.business_amount, p.in_amount, p.paid, p.out_amount, p.current_bal, p.init_bal + p.in_amount - p.out_amount + p.out_recieve - p.in_payment AS real_bal

FROM view_temp_supplier_sumup p;

CREATE OR REPLACE VIEW view_temp_supplier_sumup AS

SELECT o.client_name,

CASE

WHEN o.business_amount IS NULL THEN 0::double precision

ELSE o.business_amount

END AS business_amount,

CASE

WHEN o.paid IS NULL THEN 0::double precision

ELSE o.paid

END AS paid,

CASE

WHEN o.init_bal IS NULL THEN 0::double precision

ELSE o.init_bal

END AS init_bal,

CASE

WHEN o.current_bal IS NULL THEN 0::double precision

ELSE o.current_bal

END AS current_bal,

CASE

WHEN a.in_amount IS NULL THEN 0::double precision

ELSE a.in_amount

END AS in_amount,

CASE

WHEN b.out_amount IS NULL THEN 0::double precision

ELSE b.out_amount

END AS out_amount,

CASE

WHEN c.in_payment IS NULL THEN 0::double precision

ELSE c.in_payment

END AS in_payment,

CASE

WHEN d.out_recieve IS NULL THEN 0::double precision

ELSE d.out_recieve

END AS out_recieve

FROM product_supplier o

LEFT JOIN view_temp_inbound_sumup a ON a.parter_name::text = o.client_name::text

LEFT JOIN view_temp_outbound_sumup b ON b.parter_name::text = o.client_name::text

LEFT JOIN view_temp_inbound_payment_sumup c ON c.parter_name::text = o.client_name::text

LEFT JOIN view_temp_outbound_recieve_sumup d ON d.parter_name::text = o.client_name::text;

CREATE OR REPLACE VIEW view_temp_inbound_sumup AS

SELECT o.parter_name, sum(o.amount) AS in_quantity, sum(o.sub_total) AS in_amount

FROM inbound_order_item o

WHERE o.status::text = 'normal'::text

GROUP BY o.parter_name;

总结:基本思想同会员处理积分变化一样.

<1>统计各个订单变化总和。

<2>合并 供应商信息 和 各个订单变化总和

<3>计算合并的

<4>安条件计算出满足条件的视图.

4处理客户信息同供应商处理一样。

5.处理订单支付信息

view_class_payment_info

CREATE OR REPLACE VIEW view_class_payment_info AS

SELECT p.order_number, p.class_name, p.busiess_type, p.amount, p.paid, p.paid_real

FROM view_temp_payment_info p

WHERE p.paid <> p.paid_real OR p.paid IS NULL AND p.paid_real IS NOT NULL OR p.paid_real IS NULL AND p.paid IS NOT NULL;

CREATE OR REPLACE VIEW view_temp_payment_info AS

SELECT a.order_number, a.class_name, a.busiess_type, a.amount, a.paid, b.paid_real

FROM viewpayquery_temp_payable_detail a

LEFT JOIN viewpayquery_temp_paid_detail b ON b.refer_order_number::text = a.order_number::text;

CREATE OR REPLACE VIEW viewpayquery_temp_payable_detail AS

(( SELECT a.order_number, a.busiess_type, 'InboundOrder' AS class_name, a.amount, a.paid

FROM inbound_order a

WHERE a.status::text = 'normal'::text

UNION ALL

SELECT a.order_number, a.busiess_type, 'WholesaleOrderReturn' AS class_name, a.amount, a.paid

FROM wholesale_order_return a

WHERE a.status::text = 'normal'::text)

UNION ALL

SELECT a.order_number, a.busiess_type, 'OutboundOrder' AS class_name, a.amount, a.paid

FROM outbound_order a

WHERE a.status::text = 'normal'::text)

UNION ALL

SELECT a.order_number, a.busiess_type, 'WholesaleOrder' AS class_name, a.amount, a.paid

FROM wholesale_order a

WHERE a.status::text = 'normal'::text;

CREATE OR REPLACE VIEW viewpayquery_temp_paid_detail AS

(( SELECT a.refer_order_number, sum(a.payment) AS paid_real

FROM inbound_payment_item a

WHERE a.status::text = 'normal'::text

GROUP BY a.refer_order_number

UNION ALL

SELECT a.refer_order_number, sum(a.payment) AS paid_real

FROM wholesale_return_payment_item a

WHERE a.status::text = 'normal'::text

GROUP BY a.refer_order_number)

UNION ALL

SELECT a.refer_order_number, sum(a.payment) AS paid_real

FROM outbound_recieve_item a

WHERE a.status::text = 'normal'::text

GROUP BY a.refer_order_number)

UNION ALL

SELECT a.refer_order_number, sum(a.payment) AS paid_real

FROM wholesale_recieve_item a

WHERE a.status::text = 'normal'::text

GROUP BY a.refer_order_number;