视图用法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;