查询集团账户信息详情菜单（livebos）

1、菜单需求说明

1、查询条件

根据输入的条件查询出对应的符合条件的数据记录（支持多条件）：
业务系统、客户号、所属营业部、所属单位、手机号、邮箱、认证日期、绑定日期、开户日期、用户类型、是否中航证券员工、是否绑定交易账号
image.png

2、返回结果展示参数

统一账号、客户号、认证客户名称、绑定的亲友信息、业务系统、所属营业部、认证日期、绑定日期、认证手机、认证邮箱、所属单位、 是否中航证券员工、绑定员工手机号、用户类型、推荐人
image.pngimage.png

3、菜单涉及的数据库表

以客户号为查询条件的查询语句例子：

  1. select a.cid,a.khh,a.khmc,case when a.ywxt=1000 then '集中交易' else '融资融券' end as ywxt,
  2. a.yyb,a.sj,a.ssdw,a.rzksrq,a.bdrq,case when a.yhlx=1 then '集团户' else '亲友户' end as yhlx,
  3. case when a.yhqx=1 then '是' else '否' end as yhqx,a.email,a.tjr,a.fid from
  4. (select c.cid,a.khh,c.khmc,a.ywxt,a.yyb,c.sj,c.ssdw,c.rzksrq,c.bdrq,c.yhlx,c.yhqx,c.email,c.tjr,c.fid
  5. from tywzh a,(select b.khh,a.khmc,a.sj,a.rzksrq,a.bdrq,a.yhlx,a.ssdw,a.yhqx,a.email,a.tjr,a.cid,a.fid from tjtzhxx a,tkhxx b where (a.cid=b.cid and  b.khzt=0 and b.ygtcid is not null) or (a.sj=b.sj and b.khmc=a.khmc  and  a.cid is null and b.khzt=0 and b.ygtcid is not null)) c
  6. where a.khh=c.khh) a where a.khh='000700006236'

tywzh(业务账户表)：
主键是ywxt、ywzh字段
image.png

tkhxx(客户信息表)：
主键是khh字段
image.png

tjtzhxx(集团账户信息表)：
image.png

2、菜单(livebos)添加

1、在功能模块下创建数据集Sql

数据集设置查询条件、设计sql语句调用存储过程
image.png
image.png

配置页面展示方案：
image.png

3、虚拟对象配置

配置你的查询结果要展示的列记录
image.png

设计结果展示方案：
image.png

4、挂载菜单功能权限树

挂载之后在测试环境可以看到该菜单
image.png

5、存储过程

  1. CREATE OR REPLACE PROCEDURE PTYZH_CXJTZHXX_XB(o_result     OUT SYS_REFCURSOR,
  2.                                           i_pageno     IN NUMBER,
  3.                                           i_pagelength IN NUMBER,
  4.                                           i_totalrows  OUT NUMBER,
  5.                                           i_ywxt       IN VARCHAR2, --业务系统
  6.                                           i_khh        IN VARCHAR2, --客户号
  7.                                                               i_yyb        IN NUMBER,   --营业部
  8.                                           i_ssdw       IN VARCHAR2, --所属单位
  9.                                           i_sj         IN VARCHAR2, --认证手机
  10.                                           i_email      IN VARCHAR2, --认证邮箱
  11.                                           i_rzrq_start IN NUMBER,  --认证日期
  12.                                                               i_rzrq_end   IN NUMBER,  --认证日期
  13.                                           i_bdrq_start IN NUMBER,  --绑定日期
  14.                                                               i_bdrq_end   IN NUMBER,  --绑定日期
  15.                                           i_khrq_start IN NUMBER,  --开户日期
  16.                                                               i_khrq_end   IN NUMBER,  --开户日期
  17.                                                               i_yhlx       IN NUMBER,  --用户类型
  18.                                           i_yhqx       IN VARCHAR2, --是否中航证券员工
  19.                                           i_cid        IN VARCHAR2 --是否绑定交易账号
  20.                                           ) AS
  21.   l_collist VARCHAR2(32767);
  22.   l_code    NUMBER(10);
  23.   l_note    VARCHAR2(100);
  24.   l_sort    VARCHAR2(100);
  27.   l_sql    VARCHAR2(3000); 
  28.   l_sql1   VARCHAR2(1500); 
  29.   l_where  VARCHAR2(2000); 
  31. BEGIN
  34.   l_sort    := ' a.khh ';
  35.   l_collist := ' * ';
  37.   l_sql    := 'select a.cid,a.khh,a.khmc,case when a.ywxt=1000 then ''集中交易'' else ''融资融券'' end as ywxt,a.yyb,a.sj,a.ssdw,a.rzksrq,a.bdrq,case when a.yhlx=1 then ''集团户'' else ''亲友户'' end as yhlx,case when a.yhqx=1 then ''是'' else ''否'' end as yhqx,a.email,a.tjr,a.fid from ';
  39.   l_sql1     := '(select c.cid,a.khh,c.khmc,a.ywxt,a.yyb,c.sj,c.ssdw,c.rzksrq,c.bdrq,c.yhlx,c.yhqx,c.email,c.tjr,c.fid,a.khrq
  40. from tywzh a,(select b.khh,a.khmc,a.sj,a.rzksrq,a.bdrq,a.yhlx,a.ssdw,a.yhqx,a.email,a.tjr,a.cid,a.fid from tjtzhxx a,tkhxx b where (a.cid=b.cid and  b.khzt=0 and b.ygtcid is not null) or (a.sj=b.sj and b.khmc=a.khmc  and  a.cid is null and b.khzt=0 and b.ygtcid is not null)) c
  41. where a.khh=c.khh) a'; 
  43.   l_where   := ' where  a.ywxt in (1000,1001) ';
  45.    --业务系统做为查询条件
  46.   IF i_ywxt IS NOT NULL THEN
  47.     l_where := l_where || ' and a.ywxt = ' || i_ywxt;
  48.   END IF;
  50.   --客户号做为查询条件
  51.   IF i_khh IS NOT NULL THEN
  52.     l_where := l_where || ' and a.khh = ''' || i_khh || '''';
  53.   END IF;
  55.   --认证所属单位作为查询条件
  56.   IF i_ssdw IS NOT NULL THEN
  57.     l_where := l_where || ' and a.ssdw like ''%' || i_ssdw || '%''';
  58.   END IF;
  60.   --认证手机号作为查询条件
  61.   IF i_sj IS NOT NULL THEN
  62.     l_where := l_where || ' and a.sj = ' || i_sj;
  63.   END IF;
  65.   --营业部作为查询条件
  66.   IF i_yyb IS NOT NULL THEN
  67.     l_where := l_where || ' and a.yyb = ' || i_yyb;
  68.   END IF;
  70.  --认证邮箱作为查询条件
  71.   IF i_email IS NOT NULL THEN
  72.     l_where := l_where || ' and a.email like ''%' || i_email || '%''';
  73.   END IF;
  75.   --用户类型做为查询条件
  76.   IF i_yhlx IS NOT NULL THEN
  77.     l_where := l_where || ' and a.yhlx = ' || i_yhlx;
  78.   END IF;
  80.    --是中航证券员工做为查询条件
  81.   IF i_yhqx IS NOT NULL AND i_yhqx = 1 THEN
  82.     l_where := l_where || ' and a.yhqx = ' || i_yhqx;
  83.   END IF;
  85.    --是中航证券员工做为查询条件  || i_yhqx
  86.   IF i_yhqx IS NOT NULL AND i_yhqx != 1 THEN
  87.     l_where := l_where || ' and a.yhqx != 1';
  88.   END IF;
  93.  --认证日期作为查询条件
  94.   IF i_rzrq_start IS NOT NULL AND i_rzrq_end IS NULL THEN
  95.     l_where := l_where || ' and a.rzksrq >= ' || i_rzrq_start;
  96.   END IF;
  98.   IF i_rzrq_start IS NULL AND i_rzrq_end IS NOT NULL THEN
  99.     l_where := l_where || ' and a.rzksrq <= ' || i_rzrq_end;
  100.   END IF;
  102.   IF i_rzrq_start IS NOT NULL AND i_rzrq_end IS NOT NULL THEN
  103.     l_where := l_where || ' and a.rzksrq >= ' || i_rzrq_start ||
  104.                ' and a.rzksrq <= ' || i_rzrq_end;
  105.   END IF;
  108.   --绑定日期作为查询条件
  109.   IF i_bdrq_start IS NOT NULL AND i_bdrq_end IS NULL THEN
  110.     l_where := l_where || ' and a.bdrq >= ' || i_bdrq_start;
  111.   END IF;
  113.   IF i_bdrq_start IS NULL AND i_bdrq_end IS NOT NULL THEN
  114.     l_where := l_where || ' and a.bdrq <= ' || i_bdrq_end;
  115.   END IF;
  117.   IF i_bdrq_start IS NOT NULL AND i_bdrq_end IS NOT NULL THEN
  118.     l_where := l_where || ' and a.bdrq >= ' || i_bdrq_start ||
  119.                ' and a.bdrq <= ' || i_bdrq_end;
  120.   END IF;
  123.     --开户日期作为查询条件
  124.   IF i_khrq_start IS NOT NULL AND i_khrq_end IS NULL THEN
  125.     l_where := l_where || ' and a.khrq >= ' || i_khrq_start;
  126.   END IF;
  128.   IF i_khrq_start IS NULL AND i_khrq_end IS NOT NULL THEN
  129.     l_where := l_where || ' and a.khrq <= ' || i_khrq_end;
  130.   END IF;
  132.   IF i_khrq_start IS NOT NULL AND i_khrq_end IS NOT NULL THEN
  133.     l_where := l_where || ' and a.khrq >= ' || i_khrq_start ||
  134.                ' and a.khrq <= ' || i_khrq_end;
  135.   END IF;
  139.   --是否绑定交易账号作为查询条件1
  140.   IF i_cid IS NOT NULL AND i_cid = 0 THEN
  141.     l_where := l_where || ' and a.cid is null ';
  142.   END IF;
  143.   --是否绑定交易账号作为查询条件2
  144.   IF i_cid IS NOT NULL AND i_cid = 1 THEN
  145.     l_where := l_where || ' and a.cid is not null ';
  146.   END IF;
  149.   l_sql := l_sql || l_sql1 || l_where;
  150.   dbms_output.put_line(l_sql);
  151.   pcx_paging_query(l_code,
  152.                    l_note,
  153.                    o_result,
  154.                    i_pageno,
  155.                    i_pagelength,
  156.                    i_totalrows,
  157.                    sqls         => l_sql,
  158.                    collist      => l_collist,
  159.                    haswhere     => TRUE,
  160.                    groupislast  => CASE
  161.                                      WHEN l_sort IS NULL THEN
  162.                                       FALSE
  163.                                      ELSE
  164.                                       TRUE
  165.                                    END,
  166.                    i_sort       => l_sort,
  167.                    i_haswith    => FALSE,
  168.                    i_recount    => TRUE);
  170. END PTYZH_CXJTZHXX_XB;