ZhengZhou103/target/classes/Sql/base.sql

-- 基础类命名空间
#namespace("Base")
   -- 获取整个单位类型与职务+分管信息树数据
  #sql("getLoginInfoByUserName")
    select person_id,person_name,pwd,ifnull(wx_openid,0) as wx_openid,ifnull(qq_openid,0) as qq_openid,
    identity_id,bureau_id,city_id,area_id
    from t_sys_loginperson where b_use = 1 and login_name =?
  #end
  --  获取整个单位类型与职务+分管信息树数据
  #sql("getOrgTypePrincipalshipTree")
      select t1.id,t1.code,t1.name,t1.parent_id as pId,(case when length(t1.code)>6 then 0 else 1 end) as open,
          (case when length(t1.code)=10 then 1 else 0 end ) as nocheck,
          (select count(1) from t_base_org_type_principalship as t2 where t2.parent_id=t1.id) as is_leaf,sort_id,b_use,
          is_school,level,bureau_level
      from t_base_org_type_principalship as t1 where b_use=1 order by sort_id
  #end

  --  获取单位类型与职务+分管信息树数据指定结点的信息
  #sql("getOrgTypePrincipalshipById")
      select t1.id,t1.code,t1.name,t1.parent_id as pId,t1.sort_id,t1.b_use,t1.is_school,t1.level,t1.bureau_level,
        (select count(1) from t_base_org_type_principalship as t2 where t2.parent_id=t1.id) as is_leaf
      from t_base_org_type_principalship as t1  where t1.id=#para(0) order by t1.sort_id
  #end

  --  获取单位类型与职务+分管信息树数据指定结点的信息
  #sql("getOrgTypePrincipalshipByParentId")
      select t1.id,t1.code,t1.name,t1.parent_id as pId,t1.sort_id,t1.b_use,t1.is_school,t1.level,t1.bureau_level,
        (select count(1) from t_base_org_type_principalship as t2 where t2.parent_id=t1.id) as is_leaf
      from t_base_org_type_principalship as t1 where t1.parent_id=#para(0) and t1.b_use=1  order by sort_id
  #end

  -- 和上面的是一个东西，但要细分一下，比如幼儿园+中小学
  #sql("getOrgTypePrincipalshipByParentIdAndBigType")
      select t1.id,t1.name,t1.parent_id as pId,t1.sort_id,t1.b_use,t1.is_school,t1.level,t1.bureau_level,
        (select count(1) from t_base_org_type_principalship as t2 where t2.parent_id=t1.id) as is_leaf
      from t_base_org_type_principalship as t1 where t1.parent_id=#para(0) and t1.b_use=1 and t1.is_school=#para(1) order by sort_id
  #end

  --  修改一个OrgTypePrincipalship节点
  #sql("updateOrgTypePrincipalshipNode")
      update t_base_org_type_principalship set code=?,name=?,parent_id=?,sort_id=?,b_use=?,is_school=? where id=?
  #end

  --  删除一个OrgTypePrincipalship节点
  #sql("delOrgTypePrincipalshipById")
      delete from t_base_org_type_principalship where id=?
  #end

  --  获取单位类型与职务+分管信息树数据指定结点下一级的数据ByCode
  #sql("getOrgTypePrincipalshipByParentparent_code")
    select id,code,name,parent_id as pId,sort_id,b_use,is_school,level,bureau_level
    from t_base_org_type_principalship where code like ? and length(code)=length(?)+2 and b_use=1 order by sort_id
  #end

 --  获取指定CODE的个数
  #sql("t_base_org_type_principalship_CountByCode")
    select count(1) as c from t_base_org_type_principalship  where code=? and b_use=1
  #end

  --  获取指定CODE的个数,不包括自己
  #sql("t_base_org_type_principalship_CountByCodeExceptSelf")
    select count(1) as c from t_base_org_type_principalship  where code=? and id!=? and b_use=1
  #end

  --  通过单位ID获取职务权限树
  #sql("getPrincipalshipTreeByBureauId")
      select t1.id,t1.code,t1.name,t1.sort_id,t1.b_use,t1.is_school,t1.level,t1.bureau_level,
      (case when length(t1.code)=6 then -1 else t1.parent_id end ) as parent_id,
      (select count(1) from  t_base_org_type_principalship as t2 where t2.parent_id=t1.id and t2.b_use=1) as is_leaf
        from t_base_org_type_principalship as t1
      where t1.code like   concat((select code from t_base_org_type_principalship
      where id=(select org_type_id from t_base_organization where org_id=?)),'%') and t1.b_use=1
      order by t1.sort_id
  #end

  -- 获取职务列表打印EXCEL，用来方便第三方系统提供接入的职务与系统对应关系
  #sql("getPrincipalshipThirdParty")
  select (case when substr(t1.code,1,4)='0101' then '市级' else '县区级' end ) as level_name,
          (select t2.name from t_base_org_type_principalship as t2 where t2.code=SUBSTR(t1.code,1,6)) as org_type_name,
          t1.name as zhiwu_name ,
          ifnull((select group_concat(name) as cols from t_base_org_type_principalship where parent_id=t1.id),'无') as fenguan
          from t_base_org_type_principalship as t1 where  t1.level =4  and t1.b_use=1 order by t1.code
  #end

  -- 获取当前学期
  #sql("getCurrentTerm")
      select xq_id,(case xq_code when '09' then xn else xn+1 end) as xn,xq,
      xqmc,ksrq,jsrq,memo,xq_code,is_current from t_base_term where is_current=1
  #end
    -- 按年份获取学期列表
  #sql("getTermListByYear")
      select xq_id,(case xq_code when '09' then xn else xn+1 end) as xn,xq,
      xqmc,ksrq,jsrq,memo,xq_code,is_current from t_base_term where xn=#(batch_year)
  #end
  -- 获取当前人员
  #sql("getCurrentPerson")
      select t1.*,t4.rule_id
      from t_sys_loginperson as t1
      left join t_base_student as t2 on t2.sfzh=AES_DECRYPT(from_base64(t1.idcard_code),'DsideaL4r5t6y7u!')
      left join t_base_class as t3 on t2.class_id=t3.class_id
      LEFT JOIN t_jw_major_rule AS t4 ON t3.zydm = t4.zhuanye_code
                AND t3.entry_year = t4.entry_year
                AND t3.type_id = t4.zhaosheng_type_id
                AND t3.xq_code = t4.entry_term_code  AND t4.rule_type_id=1
      where t1.person_id='#(person_id)'
  #end

  -- 获取当前学生所在班级
  #sql("getCurrentClass")
      select c.* from t_base_class as c left join t_sys_loginperson as l on c.class_id=l.s_class_id where l.person_id='#(person_id)' and l.identity_id=6
  #end
  -- 获取人员（主部门）系列表
  #sql("getZhuDepartmentTypeId1List")
        select l.login_name,case
        when o.department_type_id=1 then o.org_id
        when po.department_type_id=1 then po.org_id
        end as org_id
        from t_sys_loginperson as l
        LEFT JOIN t_base_organization as o on l.org_id=o.org_id
        LEFT JOIN t_base_organization as po on o.parent_id=po.org_id
        where l.person_id='#(person_id)' and (o.department_type_id=1 or po.department_type_id=1 )
  #end
  -- 获取人员（辅部门）系列表
  #sql("getFuDepartmentTypeId1List")
        select l.login_name,case
        when o.department_type_id=1 then o.org_id
        when po.department_type_id=1 then po.org_id
        end as org_id
        from t_sys_loginperson as l
        LEFT JOIN t_base_teacher_org as bto on l.person_id=bto.person_id
        LEFT JOIN t_base_organization as o on bto.org_id=o.org_id
        LEFT JOIN t_base_organization as po on o.parent_id=po.org_id
        where l.person_id='#(person_id)' and (o.department_type_id=1 or po.department_type_id=1 )
  #end
  -- 获取某人是管理员的专业列表
  #sql("getZhuanYeList")
      select zt.zhuanye_id,z.zhuanye_code,z.zhuanye_name
        from t_zhaosheng_zhuanye as z
        left join t_zhaosheng_zhuanye_teacher as zt  on zt.zhuanye_id=z.zhuanye_id
        where zt.person_id='#(person_id)'
        and z.b_use=1 and #(batch_year) BETWEEN z.begin_year AND z.end_year
  #end
  -- 获取所有专业列表
  #sql("getAllZhuanYeList")
      select z.zhuanye_id,z.zhuanye_code,z.zhuanye_name
        from t_zhaosheng_zhuanye as z
        where z.b_use=1 and #(batch_year) BETWEEN z.begin_year AND z.end_year
  #end
  -- 获取学期列表
  #sql("getTermList")
      select xq_id,xn,xq,xqmc,
      date_format(ksrq,'%Y-%m-%d') as ksrq,
      date_format(jsrq,'%Y-%m-%d') as jsrq,is_current
      from t_base_term where
      xq_id<=(select xq_id from t_base_term where is_current=1)
      #if(addOne)
        +1
      #end
      and xn>=2018  order by xq_id
      #if(action_asc)
            asc
       #else
            desc
       #end
  #end
  -- 获取校历的文字描述信息
  #sql("getXiaoLiMemo")
    select memo from t_base_term where xq_id=#(xq_id)
  #end

  -- 修改校历的文字描述信息
  #sql("updateXiaoLiMemo")
    update t_base_term set memo='#(memo)',ksrq='#(ksrq)',jsrq='#(jsrq)' where xq_id=#(xq_id)
  #end

   -- 是不是在职务列表中
    #sql("isInDutyList")
        select count(*) as c  from t_person_duty_charge where person_id='#(person_id)' and duties_id in
        (
            #for(x:ids)
                #(for.index == 0 ? "" : ",")  #para(x)
            #end
        )
    #end
    -- 查询人员职务列表
    #sql("selectDutyList")
        select `name` from t_base_org_type_principalship where id in
        ( #for(x:ids)
            #(for.index == 0 ? "" : ",")
            #para(x)
        #end )
    #end
    -- 获取所有的用户城市列表
    #sql("getAllUserCity")
        select city_id,city_name,sort_id from t_base_user_city where b_use=1 order by sort_id
    #end

    -- 教师选择器专用（多选）
    #sql("selectPersonsByBureauId")
        select person_id,person_name from t_sys_loginperson where bureau_id=? and b_use=1 and identity_id=5
            order by person_name,sort_id
    #end
    -- 系内人员选择器（主部门待选）
    #sql("selectZhuPersonsByOrgId")
        select sl.person_id,sl.person_name
        from t_sys_loginperson as sl
        LEFT JOIN t_base_organization AS bo ON bo.org_id = sl.org_id
        where sl.bureau_id=? and sl.b_use=1 and sl.identity_id=5
        and (bo.org_id=? or bo.parent_id=?)
        order by sl.person_name,sl.sort_id
    #end
    -- 系内人员选择器（辅部门待选）
    #sql("selectFuPersonsByOrgId")
        select sl.person_id,sl.person_name
        from t_sys_loginperson as sl
		LEFT JOIN t_base_teacher_org as bto on sl.person_id=bto.person_id
        LEFT JOIN t_base_organization AS bo ON bo.org_id = bto.org_id
        where sl.bureau_id=? and sl.b_use=1 and sl.identity_id=5
        and (bo.org_id=? or bo.parent_id=?)
        order by sl.person_name,sl.sort_id
    #end
    -- 系内人员选择器（已选）
    #sql("selectAlreadyPersonsByOrgId")
        select sl.person_id,sl.person_name
        from t_zhaosheng_zhuanye_teacher as sl
        LEFT JOIN t_base_organization AS bo ON bo.org_id = sl.org_id
        where sl.zhuanye_id=? and sl.org_id=?
        order by sl.person_name
    #end
     --把原已选清空
    #sql("clearZhuanyeOrgTeacher")
        delete from t_zhaosheng_zhuanye_teacher where zhuanye_id=? and org_id=?
    #end
    -- 获取场地列表
    #sql("getSiteList")
        select t1.site_id,t1.type_id,t2.type_name,t1.site_code,t1.site_name,t1.site_storied_building,
        t3.building_name as site_storied_building_name,t1.floor,t1.room_number,t1.capacity_normal,
        (case t1.for_examination when 1 then '是' else '否' end) as for_examination_memo,
        t1.for_examination,t1.capacity_examination,t1.sort_id,
        (
            select t5.class_name as class_name
            from t_base_class as t5 inner join t_base_term as t2 on t5.xq_id=t2.xq_id
            inner join t_zhaosheng_zhuanye as t3 on t5.zydm=t3.zhuanye_code
            inner join t_zhaosheng_person_type as t4 on t5.person_type_id=t4.person_type_id
            where t5.class_id=t1.class_id
        ) as class_name
        from t_base_site as t1 left join t_base_site_type as t2 on t1.type_id=t2.type_id
        left join t_base_site_storied_building as t3 on t1.site_storied_building=t3.building_id
        where 1=1
        #if(site_storied_building>0)
            and t1.site_storied_building=#(site_storied_building)
        #end
        #if(floor>0)
            and t1.floor=#(floor)
        #end
        #if(type_id)
            and t1.type_id=#(type_id)
        #end
        #if(for_class_id>0)
            and (t1.class_id is not null and  t1.class_id<>'')
        #else
            and (t1.class_id is null or t1.class_id='')
        #end
        order by t1.sort_id
    #end

    -- 获取场地类型
    #sql("getSiteTypeList")
        select type_id,type_name,sort_id from t_base_site_type order by sort_id
    #end

    -- 获取教学楼列表
    #sql("getBindBuildingList")
        select building_id,building_name,sort_id from t_base_site_storied_building order by sort_id
    #end
    -- 获取教学楼楼层列表
    #sql("getBuildingLevelList")
        select DISTINCT floor from t_base_site where site_storied_building=#(site_storied_building)  order by floor
    #end

    -- 获取指定单位下多机构的教师列表
    #sql("getBureauPersonListByOrgId")
        select t1.person_id,t1.person_name from t_sys_loginperson as t1
        where t1.b_use=1
            #if(org_id)
                    and (t1.org_id in (
                            select t2.org_id from t_base_organization as t2 where t2.parent_id='#(org_id)'
                            union
                            select '#(org_id)'
                        )
                    )
                    or t1.person_id in (
                        select person_id from t_base_teacher_org as t2 where t2.org_id in (
                            select t2.org_id from t_base_organization as t2 where t2.parent_id='#(org_id)'
                            union
                            select '#(org_id)'
                        )
                    )
                #end
        order by t1.person_name;
    #end
    -- 获取指定单位下的场地列表
    #sql("getBureauBuildingList")
        select distinct t1.building_id,t1.building_name from t_base_site_storied_building as t1
        order by t1.sort_id;
    #end
    -- 获取指定单位下的场地列表
    #sql("getBaseSiteList")
        select distinct t1.site_id,t1.site_name from t_base_site as t1
        where t1.site_storied_building=#(building_id)
        order by t1.sort_id;
    #end
    -- 获取指定单位下的班级列表
    #sql("getBureauClassList")
        select distinct t1.class_id,t1.class_name from t_base_class as t1
        where ifnull(graduate_flag,0)=0
        #if(zhuanye_code)
            and t1.zydm='#(zhuanye_code)'
        #end
        #if(batch_year)
            and t1.entry_year=#(batch_year);
        #end
    #end

    -- 获取固定的班级
    #sql("getfixedClass")
    select t1.class_id,t1.class_name
            from t_base_class as t1 inner join t_base_term as t2 on t1.xq_id=t2.xq_id
            inner join t_zhaosheng_zhuanye as t3 on t1.zydm=t3.zhuanye_code
            inner join t_zhaosheng_person_type as t4 on t1.person_type_id=t4.person_type_id
            order by t1.zydm,t1.xq_id,t1.class_name
    #end
#end