本文主要是介绍仅供参考视图，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
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;
这篇关于仅供参考视图的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
