仅供参考视图2(现金银行)

2024-01-04 00:38

本文主要是介绍仅供参考视图2(现金银行),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

现金银行表:

CREATE TABLE bank

(

  database_id bigint NOT NULL, -- 表主键||表主健

  branch_id character varying(50), -- branch_id||branch_id(传输相关)

  copy_version bigint, -- copy_version||copy_version(传输相关)

  created_time date, -- created_time||created_time(传输相关)

  data_creator character varying(50), -- data_creator||data_creator(传输相关)

  data_owner character varying(50), -- data_owner||data_owner(传输相关)

  handle_person character varying(100),-- handle_person||handle_person(from order to orderitem)

  last_modify_time date, -- last_modify_time||last_modify_time(传输相关)

  last_overwrite_time date, -- last_overwrite_time||last_overwrite_time(传输相关)

  last_uploaded_time date, -- last_uploaded_time||last_uploaded_time(传输相关)

  target_branch character varying(50), -- target_branch||target_branch(传输相关)

  transmit_status integer, -- transmit_status||transmit_status(传输相关)

  "version" bigint, -- version||version(传输相关)

 

  bank_id character varying(255), -- 账户ID||账户ID

  bank_short character varying(255), -- 账户名称||账户名称

  bank_type character varying(128), -- 账户类型(现金类/银行存款类)||账户类型

  bank_account character varying(255), -- 银行账号||银行账号

  bank_fullname character varying(100), -- 银行全名||银行全名

  init_date date, -- 开户日期||开户日期

  init_amount numeric(18,8), -- 初始金额||初始金额

  balance numeric(18,8), -- 当前的余额||余额

  subject_code character varying(255),

  CONSTRAINT pk_bank PRIMARY KEY (database_id)

)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

订单支付记录表:

CREATE TABLE order_payment_record

(

  database_id bigint NOT NULL, -- 主键||主键

  "version" bigint,

  target_branch character varying(50),

  transmit_status bigint,

  branch_id character varying(50), -- branch_id||branch_id(传输相关)

  copy_version bigint, -- copy_version||copy_version(传输相关)

  created_time date, -- created_time||created_time(传输相关)

  data_creator character varying(50), -- data_creator||data_creator(传输相关)

  data_owner character varying(50), -- data_owner||data_owner(传输相关)

  handle_person character varying(100), -- handle_person||handle_person(from order to orderitem)

  last_modify_time date, -- last_modify_time||last_modify_time(传输相关)

  last_overwrite_time date, -- last_overwrite_time||last_overwrite_time(传输相关)

  last_uploaded_time date, -- last_uploaded_time||last_uploaded_time(传输相关)

 

  order_number character varying(255), -- 订单号||订单号

  self_order_number character varying(255), -- 自编单号||自编单号

  order_type character varying(255), -- 订单类型||订单类型

  order_total numeric(18,8), -- 订单总额||订单总额

  balance numeric(18,8), -- 当前的余额||余额

  bank_account character varying(255), -- 银行账号||银行账号

  bank_fullname character varying(100), -- 银行全名||银行全名

  bank_short character varying(255), -- 账户名称||账户名称

  bank_type character varying(128), -- 账户类型(现金类/银行存款类)||账户类型

  init_amount numeric(18,8), -- 初始金额||初始金额

  init_date date, -- 开户日期||开户日期

  bank_id character varying(255), -- 账户ID||账户ID

  payment_flag boolean, -- 收付款标志||收付款标志

  cur_paid_amount numeric(18,8), -- 本次付款金额||本次付款金额

  payment_method character varying(255), -- 付款方式||付款方式(多付款/其他)

  order_date date, -- order_date||order_date(订单日期相关)

  order_month character varying(20), -- order_month||order_month(订单日期相关)

  order_state character varying(64), -- order_state||order_state(from order to orderitem)

  order_year character varying(20), -- order_year||order_year(订单日期相关)           

  user_define1 character varying(64), -- 用户定义字段1||用户定义字段1

  user_define2 character varying(64), -- 用户定义字段2||用户定义字段2

  user_define3 character varying(64), -- 用户定义字段3||用户定义字段3

  user_define4 character varying(64), -- 用户定义字段4||用户定义字段4

  user_define5 character varying(64), -- 用户定义字段5||用户定义字段5

  plus_minus_flag integer, -- 表明金额是正还是付||表明金额是正还是付

  CONSTRAINT pk_order_payment_info PRIMARY KEY (database_id)

日期表:(用户动态选择时间查询)

CREATE TABLE dates

(

  database_id bigint NOT NULL,

  date_id integer,

  start_date timestamp without time zone,

  end_date timestamp without time zone,

  stock_benchmark integer,

  partner_name character varying(50),

  notes character varying(255),

  partner_id character varying(128),

  CONSTRAINT dates_pkey PRIMARY KEY (database_id)

)

 

 

 

 

1.       初始银行当前余额

CREATE OR REPLACE VIEW view_class_bank_balance_info AS

SELECT tempv_bank_current_balance_info.database_id, tempv_bank_current_balance_info.bank_fullname, tempv_bank_current_balance_info.balance, tempv_bank_current_balance_info.current_balance

FROM tempv_bank_current_balance_info

       WHERE tempv_bank_current_balance_info.balance<>tempv_bank_current_balance_info.current_balance;

 

    

CREATE OR REPLACE VIEW tempv_bank_current_balance_info AS

 SELECT b.database_id, b.bank_fullname,

        CASE

            WHEN b.balance IS NULL THEN 0::numeric

            ELSE b.balance

        END AS balance, c.current_balance

   FROM bank b

   LEFT JOIN ( SELECT a.bank_fullname, sum(a.balance_each) AS current_balance

           FROM(SELECT order_payment_record.database_id, order_payment_record.bank_fullname,

                        CASE

                            WHEN order_payment_record.plus_minus_flag = 1 THEN order_payment_record.cur_paid_amount

                            WHEN order_payment_record.plus_minus_flag = -1 THEN - order_payment_record.cur_paid_amount

                            ELSE 0::numeric

                        END AS balance_each

                   FROM order_payment_record

                  WHERE order_payment_record.cur_paid_amount IS NOT NULL

        UNION ALL

                 SELECT bank.database_id, bank.bank_fullname, bank.init_amount AS balance_each

                   FROM bank) a

          GROUP BY a.bank_fullname) c ON b.bank_fullname::text = c.bank_fullname::text;

 

 

 

 

2.选择时间处理明细

CREATE OR REPLACE VIEW view_class_bank_flow_detail AS

 SELECT a.database_id, a.order_number, a.self_order_number, a.order_date, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,

        CASE

            WHEN a.plus_minus_flag = 1 THEN '收入'::text

            WHEN a.plus_minus_flag = -1 THEN '支出'::text

            ELSE ''::text

        END AS inout_flag, a.plus_minus_flag

   FROM order_payment_record a

  WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name

           FROM dates

          WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date

           FROM dates

          WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date

           FROM dates

          WHERE dates.date_id = 3));

 

 

 

 

 

 

private void refreshBalanceDetail() {

        String bankFullName = m_bank.getText();

        Date[] orig = changeDate((Date) m_startDate.getValue(),

                (Date) m_endDate.getValue(), getDateID(), bankFullName);

        try {

            List bankDetaillist = null;

            String query = "from ViewClassBankFlowDetail o";

  bankDetaillist = getDataSourceManager().universalQuery(query, null);

  m_balanceDetailTable.insertList(bankDetaillist, true);

 

 

 

            List list = null;

            String query2 = "from ViewClassBankFlowGeneral o";

            list = getDataSourceManager().universalQuery(query2, null);

ViewClassBankFlowGeneral balanceGeneral = new ViewClassBankFlowGeneral();

            balanceGeneral.addBalanceList(list);

            restoreBalanceGeneral(balanceGeneral);

        } catch (Exception ex) {

            ex.printStackTrace();

        }

        changeDate(orig, getDateID());

}

 

 

 

private Date[] changeDate(Date[] dates, int id) {

        Date start = dates[0];

        Date end = dates[1];

        return changeDate(start, end, id, null);

}

 

//Date[0]--> startDate, Date[1]-->endDate

    private Date[] changeDate(Date newStartDate, Date newEndDate, int id,

            String partName) {

        try {

            String query = "from Dates d where d.dateID=" + id;

         List list = getDataSourceManager().universalQuery(query, null);

            Dates dates = (Dates) list.get(0);

            Date oldStartDate = dates.getStartDate();

            Date oldEndDate = dates.getEndDate();

            // set new dates and save it

            if (null != newStartDate) {

                dates.setStartDate(newStartDate);

            }

            if (null != newEndDate) {

                Calendar calendar = Calendar

                        .getInstance(new Locale("zh", "cn"));

                calendar.setTime(newEndDate);

                calendar.add(Calendar.DATE, 1);

                newEndDate = calendar.getTime();

                dates.setEndDate(newEndDate);

            }

            if (null != partName) {

                dates.setPartnerName(partName);

            } else {

                dates.setPartnerName(null);

            }

            getDataSourceManager().universalSaver(dates);

            Date[] result = new Date[2];

            result[0] = oldStartDate;

            result[1] = oldEndDate;

            return result;

        } catch (Exception ex) {

            ex.printStackTrace();

            return null;

        }

    }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.       选择时间处理期初,本期收入,本期支出等

 

CREATE OR REPLACE VIEW view_class_bank_flow_general AS

( SELECT a.bank_fullname, '期初余额' AS balance_name, sum(a.balance) AS balance

   FROM ( SELECT a.order_number, a.order_type, a.bank_fullname,

                CASE

                    WHEN a.plus_minus_flag = 1 THEN a.cur_paid_amount

                    WHEN a.plus_minus_flag = -1 THEN - a.cur_paid_amount

                    ELSE 0::numeric

                END AS balance,

                CASE

                    WHEN a.plus_minus_flag = 1 THEN '收入'::text

                    WHEN a.plus_minus_flag = -1 THEN '支出'::text

                    ELSE ''::text

                END AS inout_flag

           FROM order_payment_record a

          WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name

                   FROM dates

                  WHERE dates.date_id = 3))::text) AND a.order_date < (( SELECT dates.start_date

                   FROM dates

                  WHERE dates.date_id = 3))

UNION ALL

         SELECT '', '', a.bank_fullname, a.init_amount, '余额'

           FROM bank a

          WHERE a.bank_fullname::text = ((( SELECT dates.partner_name

                   FROM dates

                  WHERE dates.date_id = 3))::text)) a

  GROUP BY a.bank_fullname

UNION ALL

 SELECT a.bank_fullname, '本期支出' AS balance_name, sum(a.balance) AS balance

   FROM ( SELECT a.order_number, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,

                CASE

                    WHEN a.plus_minus_flag = 1 THEN '收入'::text

                    WHEN a.plus_minus_flag = -1 THEN '支出'::text

                    ELSE ''::text

                END AS inout_flag

           FROM order_payment_record a

          WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name

                   FROM dates

                  WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date

                   FROM dates

                  WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date

                   FROM dates

                  WHERE dates.date_id = 3))) a

  WHERE a.inout_flag = '支出'::text

  GROUP BY a.bank_fullname)

UNION ALL

 SELECT a.bank_fullname, '本期收入' AS balance_name, sum(a.balance) AS balance

   FROM ( SELECT a.order_number, a.order_type, a.bank_fullname, a.cur_paid_amount AS balance,

                CASE

                    WHEN a.plus_minus_flag = 1 THEN '收入'::text

                    WHEN a.plus_minus_flag = -1 THEN '支出'::text

                    ELSE ''::text

                END AS inout_flag

           FROM order_payment_record a

          WHERE a.cur_paid_amount IS NOT NULL AND a.cur_paid_amount <> 0::numeric AND a.bank_fullname::text = ((( SELECT dates.partner_name

                   FROM dates

                  WHERE dates.date_id = 3))::text) AND a.order_date >= (( SELECT dates.start_date

                   FROM dates

                  WHERE dates.date_id = 3)) AND a.order_date < (( SELECT dates.end_date

                   FROM dates

                  WHERE dates.date_id = 3))) a

  WHERE a.inout_flag = '收入'::text

  GROUP BY a.bank_fullname;

这篇关于仅供参考视图2(现金银行)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/567589

相关文章

数据视图(AngularJS)

<!DOCTYPE html><html ng-app="home.controller"><head><meta charset="utf-8"><title>数据视图</title><link href="page/common/css/bootstrap.min.css" rel="stylesheet"><script src="page/common/js/angular.js"></

12C 新特性,MOVE DATAFILE 在线移动 包括system, 附带改名 NID ,cdb_data_files视图坏了

ALTER DATABASE MOVE DATAFILE  可以改名 可以move file,全部一个命令。 resue 可以重用,keep好像不生效!!! system照移动不误-------- SQL> select file_name, status, online_status from dba_data_files where tablespace_name='SYSTEM'

架构全景视图

文章目录 一、战略规划二、业务架构Business Architecture2.1业务架构定义2.2 业务架构组成2.3 TOGAF2.3.1 Archimate建模(重要) 三、数据架构Data Architecture3.1 数据架构定义3.2 数据架构组成 四、应用架构Application Architecture4.1 应用架构定义4.2 应用架构组成 五、技术架构Technol

2409wtl,切换视图

原文 介绍 我从一个基于SDI(单文档接口)WTL向导的应用开始,添加了一些从控件继承的窗口和一些对话框窗口(表单视图),然后才发现我必须,使SDI框架动态加载和卸载子窗口. 本文演示了两个可用来完成的技术:在SDI应用中的视图间动态切换.这是我使用的两个. 技术 1技术:第一个方法涉及按需析构和重建视图实例.这更简单,且在不介意析构和重建窗口对象时效果很好. 2:按需创建视图,然后用

Django 第十七课 -- 视图 - FBV 与 CBV

目录 一. 前言 二. FBV 三. CBV 一. 前言 FBV(function base views) 基于函数的视图,就是在视图里使用函数处理请求。 CBV(class base views) 基于类的视图,就是在视图里使用类处理请求。 二. FBV 基于函数的视图其实我们前面章节一直在使用,就是使用了函数来处理用户的请求,查看以下实例: 路由配置: urlpat

什么是银行挤兑

银行挤兑是指大量银行客户因为对银行失去信心,担心银行可能无法满足其提款需求,而纷纷在短时间内集中到银行提取现金或转账的行为。这种情况可能会导致银行现金储备迅速减少,进而影响银行的正常运营和金融市场的稳定。 银行挤兑通常发生在以下几种情况:1. 银行出现经营困难,如资产质量恶化、流动性不足等。2. 市场传言或负面消息导致公众对银行失去信心。3. 经济环境不稳定,如金融危机、经济衰退等。4. 政策变动

android 动画 ——视图动画(View Animation)

android动画分为视图动画(View Animation)、属性动画(Property Animation) 想看属性动画(Property Animation):请移步至http://blog.csdn.net/u013424496/article/details/51700312 这里我们来说下视图动画(View Animation)的纯代码写法,还有一种是xml调用, 对于xml调

【mysql 视图】Mysql视图的创建

什么是视图? 视图是由查询结果形成的一张虚拟表。也就是我们可以理解为就是一种类似于表的数据对象。 什么时候要用到视图? 如果某个查询结果出现的非常频繁,也就是,要经常拿这个查询结果来做子查询。 视图与表的关系? 视图是表的查询结果,自然表的数据变了,会影响视图的结果。 创建视图 CREATE VIEW v_person as SELECT * from persons; SEL

使用视图方式操作MySQL数据表

7.1  认识视图 7.1.1  视图的含义 视图是一种常用的数据库对象,可以把它看成从一个或几个源表导出的虚表或存储在数据库中的查询,对视图所引用的源表来说,视图的作用类似于筛选。 视图一经定义后,就可以像源表一样被查询、修改和删除。视图为查看和存取数据提供了另外一种途径,使用查询可以完成的大多数操作,使用视图一样可以完成。 使用视图还可以简化数据操作。当通过视图修改数据时,相应源表的数

软件测试永远的家——银行测试,YYDS

为什么做金融类软件测试举个栗子,银行里的软件测试工程师。横向跟互联网公司里的测试来说,薪资相对稳定,加班少甚至基本没有,业务稳定。实在是测试类岗位中的香饽饽! 一、什么是金融行业 金融业是指经营金融商品的特殊行业,它包括银行业、保险业、信托业、证券业和租赁业 往往涉及证券、银行、基金、信托、保险、投行、期货等领域 二、金融行业的业务特点 随着金融行业的业务不断增加,金融交易模式的不断变化,