仅供参考视图

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;