仅供参考视图

1.       订单付款视图

CREATE OR REPLACE VIEW view_class_payment_info AS

 SELECT p.order_number, p.class_name, p.paid_amount, p.paid_amount_real

   FROM view_temp_payment_info p

  WHERE p.paid_amount <> p.paid_amount_real;

 

 

CREATE OR REPLACE VIEW view_temp_payment_info AS

 SELECT a.order_number, a.class_name,

        CASE

            WHEN a.paid_amount IS NULL THEN 0::numeric

            ELSE a.paid_amount

        END AS paid_amount,

        CASE

            WHEN b.paid_amount_real IS NULL THEN 0::numeric

            ELSE b.paid_amount_real

        END AS paid_amount_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, 'InboundOrder' AS class_name, a.paid_amount

   FROM inbound_order a

  WHERE a.order_state::text = '有效'::text

UNION ALL

 SELECT a.order_number, 'SaleInvoice' AS class_name, a.paid_amount

   FROM sale_invoice a

  WHERE a.order_state::text = '有效'::text;

 

 

CREATE OR REPLACE VIEW viewpayquery_temp_paid_detail AS

 SELECT a.refer_order_number, sum(a.sub_total_amt) AS paid_amount_real

   FROM inbound_payment_item a

  WHERE a.order_state::text = '有效'::text

  GROUP BY a.refer_order_number

UNION ALL

 SELECT a.refer_order_number, sum(a.sub_total_amt) AS paid_amount_real

   FROM sale_receive_item a

  WHERE a.order_state::text = '有效'::text

  GROUP BY a.refer_order_number;

 

public static int makePaymentInfoConsistent(Session session)

           throws Exception {

       String query = "from ViewClassPaymentInfo ";

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

       int total = 0;

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

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

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

       String update = "update " + info.getClassName()

              + " o set o.paidAmount=" + info.getPaidAmountReal()

               + ",o.lastModifyTime=:time, o.dataOwner=:owner "

              + " where o.orderNumber='"+ info.getOrderNumber() + "'";

              Query cmd = session.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;

           }

       }

       return total;

}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.       订单退款视图

CREATE OR REPLACE VIEW view_class_return_info AS

 SELECT p.order_number, p.class_name, p.return_amount, p.return_amount_real

   FROM view_temp_return_info p

  WHERE p.return_amount <> p.return_amount_real;

 

CREATE OR REPLACE VIEW view_temp_return_info AS

 SELECT a.order_number, a.class_name,

        CASE

            WHEN a.return_amount IS NULL THEN 0::numeric

            ELSE a.return_amount

        END AS return_amount,

        CASE

            WHEN b.return_amount_real IS NULL THEN 0::numeric

            ELSE b.return_amount_real

        END AS return_amount_real

   FROM viewpayquery_temp_returnable_detail a

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

 

CREATE OR REPLACE VIEW viewpayquery_temp_returnable_detail AS

 SELECT a.order_number, 'InboundOrder' AS class_name, a.return_amount

   FROM inbound_order a

  WHERE a.order_state::text = '有效'::text

UNION ALL

 SELECT a.order_number, 'SaleInvoice' AS class_name, a.return_amount

   FROM sale_invoice a

  WHERE a.order_state::text = '有效'::text;

 

CREATE OR REPLACE VIEW viewpayquery_temp_returned_detail AS

 SELECT a.refer_order_number, sum(a.sub_total_amt) AS return_amount_real

   FROM inbound_return_item a

  WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text

  GROUP BY a.refer_order_number

UNION ALL

 SELECT a.refer_order_number, sum(a.sub_total_amt) AS return_amount_real

   FROM sale_return_item a

  WHERE a.order_state::text = '有效'::text AND a.classification_status1::text = '冲抵往来'::text

  GROUP BY a.refer_order_number;

 

 

 

 

 

public static int makeReturnInfoConsistent(Session session)

           throws Exception {

       String query = "from ViewClassReturnInfo ";

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

       int total = 0;

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

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

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

           String update = "update " + info.getClassName()

              + " o set o.returnAmount=" + info.getReturnAmountReal()

              + ",o.lastModifyTime=:time, o.dataOwner=:owner "

              + " where o.orderNumber='"+ info.getOrderNumber() + "'";

              Query cmd = session.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;

           }

       }

       return total;

    }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE OR REPLACE VIEW view_class_received_quantity_info AS

 SELECT a.database_id, a.order_number, a.item_number, a.class_name, a.received_quantity_real, a.received_quantity

   FROM tempv_received_quantity_info a

  WHERE a.received_quantity_real <> a.received_quantity;

 

CREATE OR REPLACE VIEW tempv_received_quantity_info AS

 SELECT a.database_id, a.order_number, a.item_number, a.class_name,

        CASE

            WHEN b.received_quantity_real IS NULL THEN 0::numeric

            ELSE b.received_quantity_real

        END AS received_quantity_real,

        CASE

            WHEN a.received_quantity IS NULL THEN 0::numeric

            ELSE a.received_quantity

        END AS received_quantity

   FROM tempv_received_quantity_detail a

   LEFT JOIN tempv_real_received_quantity_detail b ON b.refer_order_number::text = a.order_number::text AND b.refer_item_number = a.item_number;

 

CREATE OR REPLACE VIEW tempv_received_quantity_detail AS

 SELECT a.database_id, a.order_number, a.item_number, 'PurchaseOrderItem' AS class_name, a.commit_quantity AS received_quantity

   FROM purchase_order_item a

  WHERE a.order_state::text = '有效'::text

UNION ALL

 SELECT a.database_id, a.order_number, a.item_number, 'SaleOrderItem' AS class_name, a.commit_quantity AS received_quantity

   FROM sale_order_item a

  WHERE a.order_state::text = '有效'::text;

 

CREATE OR REPLACE VIEW tempv_real_received_quantity_detail AS

 SELECT a.refer_order_number, a.refer_item_number, sum(a.unit_quantity) AS received_quantity_real

   FROM inbound_order_item a

  WHERE a.refer_order_number IS NOT NULL AND a.refer_item_number IS NOT NULL AND a.order_state::text = '有效'::text

  GROUP BY a.refer_order_number, a.refer_item_number

UNION ALL

 SELECT a.refer_order_number, a.refer_item_number, sum(a.unit_quantity) AS received_quantity_real

   FROM sale_invoice_item a

  WHERE a.refer_order_number IS NOT NULL AND a.refer_item_number IS NOT NULL AND a.order_state::text = '有效'::text

GROUP BY a.refer_order_number, a.refer_item_number;