1.数据库连接

tangzy

2021-12-04 269b7d3e853794aefb36fab0fe8390f325d57374

1.数据库连接

 pom.xml

@@ -277,6 +277,11 @@
            <artifactId>ezmorph</artifactId>
            <version>1.0.6</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>2.5.1</version>
        </dependency>
    </dependencies>

    <build>

 src/main/java/org/springblade/common/launch/LauncherServiceImpl.java

@@ -36,7 +36,7 @@
    public void launcher(SpringApplicationBuilder builder, String appName, String profile, boolean isLocalDev) {
        Properties props = System.getProperties();
        PropsUtil.setProperty(props, "spring.cloud.sentinel.transport.dashboard", LauncherConstant.sentinelAddr(profile));
        PropsUtil.setProperty(props, "spring.datasource.dynamic.enabled", "false");
        PropsUtil.setProperty(props, "spring.datasource.dynamic.enabled", "ture");
        // 开启elk日志
        //PropsUtil.setProperty(props, "blade.log.elk.destination", LauncherConstant.elkAddr(profile));
    }

 src/main/java/org/springblade/modules/information/controller/InformationController.java

@@ -1829,4 +1829,10 @@
        return R.data(lists);
    }

    @PostMapping("/selectBxc")
    public R<IPage> selectBxc(String jurisdiction,String type,Query query) {
        IPage maps = informationService.selectBxc(Condition.getPage(query), jurisdiction,type);
        return R.data(maps);
    }

}

 src/main/java/org/springblade/modules/information/mapper/InformationMapper.java

@@ -89,6 +89,7 @@
    List<Map<Object,Object>> selJur();
    String selJurchilder(String id);
    List<Map<Object,Object>> seleJuList(String substring);
    List<Map<Object,Object>> selectBxc(IPage page, String jurisdiction,String type);

    Map<String, String> queryYearZ(String year,String jurisdiction,String deptid);
    Map<String, String> queryYearZc(String year,String jurisdiction,String deptid);

 src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml

@@ -777,7 +777,7 @@
        IFNULL( C.fznum, 0 ) AS fznum,
        H.departmentid,
        H.jurisdiction
        FROM (SELECT departmentid, jurisdiction FROM sys_information WHERE stats!=1  GROUP BY departmentid, jurisdiction)
        FROM (SELECT departmentid, jurisdiction FROM sys_information WHERE stats!=1 GROUP BY departmentid, jurisdiction)
        H
        LEFT JOIN (
        SELECT COUNT(*) AS thisnum,
@@ -1405,7 +1405,7 @@
        u.dept_id
        FROM
        sys_performance p
        INNER JOIN blade_user u ON p.securityId = u.id WHERE 1=1
        INNER JOIN blade_user u ON p.securityId = u.id WHERE 1=1 AND score=3
        <if test="type!=null and type=='1'.toString()">
            and to_days(p.time) = to_days(now())
        </if>
@@ -3053,39 +3053,39 @@
                 a.id = b.parent_id
        union all
        (
        select a.id,
               a.dept_name        name,
               ifnull(b.count, 0) num
        from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
                 left join
             (
                 select sj.id,
                        sj.parent_id,
                        count(*) count
                 from sys_performance sp
            select a.id,
                   a.dept_name        name,
                   ifnull(b.count, 0) num
            from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
                     left join
                     blade_user bu
                 (
                     select sj.id,
                            sj.parent_id,
                            count(*) count
                     from sys_performance sp
                         left join
                         blade_user bu
                     on
                         bu.id = sp.securityId
                         left join
                         sys_information si
                         on
                         bu.dept_id = si.departmentid
                         left join
                         sys_jurisdiction sj
                         on
                         sj.id = si.jurisdiction
                     where
                         1=1
                       and (si.stats = 2
                        or si.stats =4)
                       and bu.is_deleted = 0
                       and bu.`status` = 1
                       and sp.score = 3
                     group by si.jurisdiction
                 ) b
                 on
                     bu.id = sp.securityId
                     left join
                     sys_information si
                     on
                     bu.dept_id = si.departmentid
                     left join
                     sys_jurisdiction sj
                     on
                     sj.id = si.jurisdiction
                 where
                     1=1
                   and (si.stats = 2
                    or si.stats =4)
                   and bu.is_deleted = 0
                   and bu.`status` = 1
                   and sp.score = 3
                 group by si.jurisdiction
             ) b
             on
                 a.id = b.id
                     a.id = b.id
        )
    </select>

@@ -3122,34 +3122,34 @@
                 a.id = b.parent_id
        union all
        (
        select a.id, a.dept_name name, ifnull(b.count, 0) num
        from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
                 left join
             (
                 select sj.id,
                        sj.parent_id,
                        count(*) count
                 from
                     blade_user bu
            select a.id, a.dept_name name, ifnull(b.count, 0) num
            from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
                     left join
                     sys_information si
                 on
                     bu.dept_id = si.departmentid
                     left join
                     sys_jurisdiction sj
                 (
                     select sj.id,
                            sj.parent_id,
                            count(*) count
                     from
                         blade_user bu
                         left join
                         sys_information si
                     on
                     sj.id = si.jurisdiction
                 where
                     1=1
                   and (si.stats = 2
                    or si.stats =4)
                   and bu.is_deleted = 0
                   and bu.`status` = 1
                   and bu.examination_type = 1
                 group by si.jurisdiction
             ) b
             on
                 a.id = b.id
                         bu.dept_id = si.departmentid
                         left join
                         sys_jurisdiction sj
                         on
                         sj.id = si.jurisdiction
                     where
                         1=1
                       and (si.stats = 2
                        or si.stats =4)
                       and bu.is_deleted = 0
                       and bu.`status` = 1
                       and bu.examination_type = 1
                     group by si.jurisdiction
                 ) b
                 on
                     a.id = b.id
        )
    </select>

@@ -3218,66 +3218,66 @@
                 g.id = f.parent_id
        union all
        (
        select g.id, g.dept_name name, ifnull(f.count, 0) num
        from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g
                 left join
             (
                 select e.id, e.parent_id, count(*) count
                 from (
                     select d.*, ifnull(FORMAT((num2/num1)*100, 2), 0) num3, sj.* from (
                     select a.dept_id, ifnull(b.count, 0) num1, ifnull(c.count, 0) num2 from
                     (
                     select
                     bu.dept_id, ifnull(count (*), 0) count
                     from blade_user bu
                     left join blade_role br
                     on bu.role_id = br.id
                     where
                     role_alias = "安保人员"
                     and bu.is_deleted = 0
                     and bu.`status` = 1
                     group by dept_id
                     ) a
            select g.id, g.dept_name name, ifnull(f.count, 0) num
            from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g
                     left join
                     (
                     select count (*) count, bu.dept_id from
                     exam_score es
                     left join
                     blade_user bu
                     on
                     bu.id = es.user_id
                     where 1=1 group by bu.dept_id
                     ) b
                     on
                     a.dept_id = b.dept_id
                     left join
                     (
                     select count (*) count, bu.dept_id from
                     exam_score es
                     left join
                     blade_user bu
                     on
                     bu.id = es.user_id
                     where qualified = 0 group by bu.dept_id
                     ) c
                     on a.dept_id = c.dept_id
                     ) d
                     left join
                     sys_information si
                     on
                     si.departmentid = d.dept_id
                     left join
                     sys_jurisdiction sj
                     on
                     sj.id = si.jurisdiction
                     where d.num1!=0
                     and (stats = 2 or stats=4)
                     ) e
                 where e.num3 &lt; 50
                 group by e.id
             ) f
             on
                 g.id = f.id
                 (
                     select e.id, e.parent_id, count(*) count
                     from (
                         select d.*, ifnull(FORMAT((num2/num1)*100, 2), 0) num3, sj.* from (
                         select a.dept_id, ifnull(b.count, 0) num1, ifnull(c.count, 0) num2 from
                         (
                         select
                         bu.dept_id, ifnull(count (*), 0) count
                         from blade_user bu
                         left join blade_role br
                         on bu.role_id = br.id
                         where
                         role_alias = "安保人员"
                         and bu.is_deleted = 0
                         and bu.`status` = 1
                         group by dept_id
                         ) a
                         left join
                         (
                         select count (*) count, bu.dept_id from
                         exam_score es
                         left join
                         blade_user bu
                         on
                         bu.id = es.user_id
                         where 1=1 group by bu.dept_id
                         ) b
                         on
                         a.dept_id = b.dept_id
                         left join
                         (
                         select count (*) count, bu.dept_id from
                         exam_score es
                         left join
                         blade_user bu
                         on
                         bu.id = es.user_id
                         where qualified = 0 group by bu.dept_id
                         ) c
                         on a.dept_id = c.dept_id
                         ) d
                         left join
                         sys_information si
                         on
                         si.departmentid = d.dept_id
                         left join
                         sys_jurisdiction sj
                         on
                         sj.id = si.jurisdiction
                         where d.num1!=0
                         and (stats = 2 or stats=4)
                         ) e
                     where e.num3 &lt; 50
                     group by e.id
                 ) f
                 on
                     g.id = f.id
        )
    </select>

@@ -3476,4 +3476,30 @@
        )
    </select>

    <select id="selectBxc" resultType="java.util.HashMap">
        SELECT u.real_name as realName,
        d.dept_name as deptName,
        u.sex,
        u.cardid,
        ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING(u.cardid, 7, 4), 0) AS age,
        u.securitynumber,
        u.phone,
        p.score
        FROM sys_performance p
        LEFT JOIN blade_user u ON p.securityId = u.id
        LEFT JOIN blade_dept d ON d.id = p.departmentid
        LEFT JOIN sys_jurisdiction j ON j.id = p.jurisdiction
        WHERE 1 =1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and (j.id = #{jurisdiction} or j.parent_id = #{jurisdiction})
        </if>
        <if test="type!=null and type=='1'.toString()">
            and to_days(p.time) = to_days(now())
        </if>
        <if test="type!=null and type=='2'.toString()">
            and DATE_FORMAT(p.time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
        </if>
        and p.score = 3
    </select>

</mapper>

 src/main/java/org/springblade/modules/information/service/IInformationService.java

@@ -197,4 +197,5 @@
     * @return
     */
    List<ResponseVo> getSecurityHoldAndSoidAndDispatchGroupCountList(Integer type);
    IPage selectBxc(IPage<List> page,String jurisdiction,String type);
}

 src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java

@@ -436,4 +436,9 @@
    public List<ResponseVo> getSecurityHoldAndSoidAndDispatchGroupCountList(Integer type) {
        return baseMapper.getSecurityHoldAndSoidAndDispatchGroupCountList(type);
    }

    @Override
    public IPage selectBxc(IPage page,String jurisdiction,String type) {
        return page.setRecords(baseMapper.selectBxc(page,jurisdiction,type));
    }
}

 src/main/java/org/springblade/modules/licet/service/impl/LicetServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.licet.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.licet.entity.Licet;
@@ -31,6 +32,7 @@
 * @since 2021-09-06
 */
@Service
@DS("slaves")
public class LicetServiceImpl extends ServiceImpl<LicetMapper, Licet> implements ILicetService {

    @Override

 src/main/java/org/springblade/modules/licetuser/service/impl/LicetuserServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.licetuser.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.licetuser.entity.Licetuser;
@@ -33,6 +34,7 @@
 * @since 2021-09-06
 */
@Service
@DS("slaves")
public class LicetuserServiceImpl extends ServiceImpl<LicetuserMapper, Licetuser> implements ILicetuserService {

    @Override

 src/main/java/org/springblade/modules/location/mapper/LiveLocationMapper.xml

@@ -216,17 +216,22 @@

    <!--查询指令接收人的定位信息-->
    <select id="getDirectiveLiveLocationVo" resultType="org.springblade.modules.location.vo.LiveLocationVo">
        select
        *
        from
        sys_live_location
        where
        1=1
        SELECT
        l.*,
        u.real_name as rname,
        u.phone,
        d.dept_name as deptname
        FROM
        sys_live_location l
        LEFT JOIN blade_user u ON u.id = l.worker_id
        LEFT JOIN blade_dept d ON u.dept_id = d.id
        WHERE
        1 =1
        <if test="liveLocation.type!=null and liveLocation.type!=''">
            and type = #{liveLocation.type}
            and l.type = #{liveLocation.type}
        </if>
        <if test="liveLocation.workerId!=null and liveLocation.workerId!=''">
            and worker_id = #{liveLocation.workerId}
            and l.worker_id = #{liveLocation.workerId}
        </if>
    </select>


 src/main/java/org/springblade/modules/location/vo/LiveLocationVo.java

@@ -58,5 +58,8 @@
     * 指令接收人id
     */
    private String userIds;
    private String deptname;
    private String rname;
    private String phone;

}

 src/main/java/org/springblade/modules/permit/service/impl/PermitServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.permit.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.permit.entity.Permit;
@@ -31,6 +32,7 @@
 * @since 2021-07-12
 */
@Service
@DS("slaves")
public class PermitServiceImpl extends ServiceImpl<PermitMapper, Permit> implements IPermitService {

    @Override

 src/main/java/org/springblade/modules/record/service/impl/RecordServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.record.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.record.entity.Record;
@@ -31,6 +32,7 @@
 * @since 2021-07-12
 */
@Service
@DS("slaves")
public class RecordServiceImpl extends ServiceImpl<RecordMapper, Record> implements IRecordService {

    @Override

 src/main/java/org/springblade/modules/recordk/entity/Recordk.java

@@ -151,6 +151,8 @@
    private String contacts;
    private String contactscell;
    private String approve;
    private String cardid;
    private String ptype;
    //派出所审批
    private String papprove;
    //县审批
@@ -163,6 +165,7 @@
    private String xopinion;
    //市意见
    private String sopinion;
    private String jurisdiction;
//
//    @ApiModelProperty(value = "审批时间")
//    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")

 src/main/java/org/springblade/modules/recordk/mapper/RecordkMapper.xml

@@ -34,6 +34,7 @@
        <result column="popinion" property="popinion"/>
        <result column="xopinion" property="xopinion"/>
        <result column="sopinion" property="sopinion"/>
        <result column="cardid" property="cardid"/>
    </resultMap>


@@ -43,19 +44,19 @@
        on
        sj.id = si.jurisdiction where 1=1
        <if test="recordk.ptype!=null and recordk.ptype!=''">
            and ptype=#{record.ptype}
            and ptype=#{recordk.ptype}
        </if>
        <if test="recordk.jurisdiction!=null and recordk.jurisdiction != '' and recordk.jurisdiction!='1372091709474910209'">
            and (sj.id = #{record.jurisdiction} or sj.parent_id = #{record.jurisdiction})
            and (sj.id = #{recordk.jurisdiction} or sj.parent_id = #{recordk.jurisdiction})
        </if>
        <if test="recordk.papprove!=null and recordk.papprove!=''">
            and papprove=#{record.papprove}
            and papprove=#{recordk.papprove}
        </if>
        <if test="recordk.xapprove!=null and recordk.xapprove!=''">
            and xapprove=#{record.xapprove}
            and xapprove=#{recordk.xapprove}
        </if>
        <if test="recordk.sapprove!=null and recordk.sapprove!=''">
            and sapprove=#{record.sapprove}
            and sapprove=#{recordk.sapprove}
        </if>
    </select>


 src/main/java/org/springblade/modules/recordk/service/impl/RecordkServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.recordk.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.recordk.entity.Recordk;
@@ -31,6 +32,7 @@
 * @since 2021-07-12
 */
@Service
@DS("slaves")
public class RecordkServiceImpl extends ServiceImpl<RecordkMapper, Recordk> implements IRecordkService {

    @Override

 src/main/java/org/springblade/modules/revoke/entity/Revoke.java

@@ -144,6 +144,8 @@
    private String contactscell;
    private String approve;
    private String jurisdiction;
    private String ptype;
    private String cardid;
    //派出所审批
    private String papprove;
    //县审批

 src/main/java/org/springblade/modules/revoke/mapper/RevokeMapper.xml

@@ -34,6 +34,7 @@
        <result column="popinion" property="popinion"/>
        <result column="xopinion" property="xopinion"/>
        <result column="sopinion" property="sopinion"/>
        <result column="cardid" property="cardid"/>
    </resultMap>


@@ -43,19 +44,19 @@
        on
        sj.id = si.jurisdiction where 1=1
        <if test="revoke.ptype!=null and revoke.ptype!=''">
            and ptype=#{record.ptype}
            and ptype=#{revoke.ptype}
        </if>
        <if test="revoke.jurisdiction!=null and revoke.jurisdiction != '' and revoke.jurisdiction!='1372091709474910209'">
            and (sj.id = #{record.jurisdiction} or sj.parent_id = #{record.jurisdiction})
            and (sj.id = #{revoke.jurisdiction} or sj.parent_id = #{revoke.jurisdiction})
        </if>
        <if test="revoke.papprove!=null and revoke.papprove!=''">
            and papprove=#{record.papprove}
            and papprove=#{revoke.papprove}
        </if>
        <if test="revoke.xapprove!=null and revoke.xapprove!=''">
            and xapprove=#{record.xapprove}
            and xapprove=#{revoke.xapprove}
        </if>
        <if test="revoke.sapprove!=null and revoke.sapprove!=''">
            and sapprove=#{record.sapprove}
            and sapprove=#{revoke.sapprove}
        </if>
    </select>


 src/main/java/org/springblade/modules/revoke/service/impl/RevokeServiceImpl.java

@@ -16,6 +16,7 @@
 */
package org.springblade.modules.revoke.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.modules.revoke.entity.Revoke;
@@ -31,6 +32,7 @@
 * @since 2021-07-12
 */
@Service
@DS("slaves")
public class RevokeServiceImpl extends ServiceImpl<RevokeMapper, Revoke> implements IRevokeService {

    @Override

 src/main/java/org/springblade/modules/system/entity/User.java

@@ -222,5 +222,6 @@
     */
    @TableField("equipment_code")
    private String equipmentCode;
    private String types;

}

 src/main/java/org/springblade/modules/system/mapper/UserMapper.xml

@@ -177,11 +177,18 @@
        </if>
        <if test="user.examinationType!=null and user.examinationType != ''">
            <if test="user.examinationType == 0">
                and (bu.examination_type = #{user.examinationType} or bu.examination_type is null or bu.examination_type ='')
                and (bu.examination_type = #{user.examinationType} or bu.examination_type is null or bu.examination_type
                ='')
            </if>
            <if test="user.examinationType == 1">
                and bu.examination_type = #{user.examinationType}
            </if>
        </if>
        <if test="user.types!=null and user.types=='1'.toString()">
            and to_days(bu.audit_time) = to_days(now())
        </if>
        <if test="user.types!=null and user.types=='2'.toString()">
            and DATE_FORMAT(bu.audit_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
        </if>
        <if test="user.sex!=null and user.sex != ''">
            and bu.sex = #{user.sex}
@@ -302,7 +309,9 @@

    <update id="updateUser">
        update blade_user
        set hold=#{hold},holdv=#{holdv},holdvtime=#{time}
        set hold=#{hold},
            holdv=#{holdv},
            holdvtime=#{time}
        where cardid = #{cardid}
    </update>

@@ -322,16 +331,14 @@

    <!--根据保安员编码查询保安信息-->
    <select id="getUserInfoBySecurityNumber" resultType="org.springblade.modules.system.vo.UserVO">
        select
        bu.*,
        ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING( cardid,7,4),0) age,
        bd.dept_name deptName
         from
         blade_user bu
         left join
         blade_dept bd
         on
         bu.dept_id = bd.id
        select bu.*,
               ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING(cardid, 7, 4), 0) age,
               bd.dept_name                                                  deptName
        from blade_user bu
                 left join
             blade_dept bd
             on
                 bu.dept_id = bd.id
        where securitynumber = #{securityNumber}
    </select>

@@ -340,10 +347,9 @@
        SELECT id, name, real_name RealName
        FROM blade_user
        where cardid = #{param1}
        and is_deleted = 0
        and status = 1
          and is_deleted = 0
          and status = 1
    </select>



    <select id="selectPeo" resultType="java.util.HashMap">

 src/main/java/org/springblade/modules/system/vo/UserVO.java

@@ -95,4 +95,5 @@
     */
    private Integer age;
    private Integer type;
    private String types;
}