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;
分享到:
相关推荐
图片懒加载判断图片是否滚动到视图内,使用jquery.lazyload.js插件,还得大于五十个字,真麻烦随便凑凑字吧,仅供参考
VC 2010 单文档多视图的例子,仅供参考
--建立视图 --if exists(select * from sysobjects where name='视图名') -- drop view 视图名 --go --create view 视图名 --as --select 字段名 from 表名 [条件] --go --主外健约束语句没有执行 use T90ERP go ...
提供了控件的基本使用(Android 学习基础): Android中视图View的创建方式? 本章介绍了Android中哪三种基本的控件? 事件驱动模型的三要素? 仅供学习参考,如侵权请联系删除
使用一个XML文档(StudentBase1.xml)来描述其中包含的所有信息,基本保持原有数据的形式(不要将...结果示例如下图所示(该图仅供参考,可以把它放大查看,要求对CID、MID、SID必须使用XML属性,其他字段使用XML元素)
[实验内容和步骤] 1、安装XMLSPY集成开发环境,新建XML文档,在各种不同的编辑视图中(text,...结果示例如下图所示(该图仅供参考,可以把它放大查看,要求对aID、mID、spyID必须使用XML属性,其他字段使用XML元素):
是因为该demo确实话费了不少时间。 高仿IOS“网易新闻”客户端,仿的是首页可以左右拖动,且在ios6和ios7下不同的拖动效果。 此例子仅供参考。
利用定时器画出一个COS曲线 仅供参考 第一个VC成品。。。。。。。。。。。。
本科毕业论文设计,使用matplotlib技术设计并实现一套用于便捷、灵活的...下载后请首先打开README.md文件(如有),仅供学习参考, 切勿用于商业用途。 -------- ----------------------------------------------------
首先声明这种方法有个弊端就是如果在使用大纲视图之前各个标题有编号,在大纲视图下更改标题级别时可能会使编号消失,需要重新插入编号,这只是生成目录的一种方法,而且用的人不在少数,在这里仅供参考。...
5、若硬件差异,请根据自身情况适当调整代码,程序仅供参考。 6、代码有注释说明,请耐心阅读。 7、技术v:349014857; 8、配套开发例程、操作指南,点击左侧头像进主页查找。 9、资料为压缩包文件,请安装解压软件...
MFC备忘录系统,一个最基本信息管理系统+源代码+文档说明+列表视图+系统报告+流程图+ppt+萌新运行教程 - 小白不懂运行,下载完...下载后请首先打开README.md文件(如有),仅供学习参考, 切勿用于商业用途。 --------
本项目是一个Windows远程控制系统,项目完全采用C#.NET开发,...实现了中间会话服务器,支持多主控端同时监控,支持Web端,欢迎点Start关注,项目不定时更新,源代码仅供参考,不得用于非法用途,否则一切后果自负。
通过企业管理器或是查询分析器实现各个二维关系(建议最好用SQL代码实现),要求建立相关的索引: (1)根据系统功能需求设计相应的查询视图 (2)要求根据系统功能需求建立存储过程 ...仅供参考学习使用
文档视图结构(Document/View Architecture)是MFC的精髓,也是Observer模式的具体...开发结合起来,最后提供一个简单但是全面的Document/View Architecture项目开发(主要是界面框架设计和开发)的实际例子,供参考。
仅供参考,共享资源,仅用于商用 完全响应式布局(支持电脑、平板、手机等所有主流设备) 基于最新版本的Bootstrap 3.3.6 提供3套不同风格的皮肤 支持多种布局方式 使用最流行的的扁平化设计 提供了诸多的UI组件 ...
学习成绩管理数据库系统,创建表,视图,存储过程等,仅供参考~
本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。 索引 一、概述 所有的Mysql列类型都可以被索引。 mysql支持BTREE索引、...
学习之作,仅供参考! 采用Servlet作为控制器,采用JSP+Ajax作为视图层;
代码不是很全,可以利用布局视图axPageLayoutControl1控件进行一些操作,但开发的不全面,仅供参考