HN代码书写规范实例

WpBggimapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zhetang.dao.bggl.WpBgglMapper" >
  <resultMap id="BaseResultMap" type="com.zhetang.model.bggl.WpBggl" >
    <id column="id" property="id" jdbcType="CHAR" />
    <result column="jbxx_bgszbm" property="jbxxBgszbm" jdbcType="VARCHAR" />
    <result column="jbxxx_bgr" property="jbxxxBgr" jdbcType="VARCHAR" />
    <result column="jbxx_bgzxbm" property="jbxxBgzxbm" jdbcType="VARCHAR" />
    <result column="jbxx_bgzxr" property="jbxxBgzxr" jdbcType="VARCHAR" />
    <result column="jbxx_bgsj_start" property="jbxxBgsjStart" jdbcType="TIMESTAMP" />
    <result column="jbxx_bgsj_end" property="jbxxBgsjEnd" jdbcType="TIMESTAMP" />
    <result column="jbxx_sqr" property="jbxxSqr" jdbcType="VARCHAR" />
    <result column="jbxx_jyms" property="jbxxJyms" jdbcType="VARCHAR" />
    <result column="jbxx_bgyj" property="jbxxBgyj" jdbcType="VARCHAR" />
    <result column="fxfxjkz" property="fxfxjkz" jdbcType="VARCHAR" />
    <result column="jbxx_djrq" property="jbxxDjrq" jdbcType="TIMESTAMP" />
    <result column="jbxx_fj" property="jbxxFj" jdbcType="VARCHAR" />
    <result column="jbxx_fjmz" property="jbxxFjmz" jdbcType="VARCHAR" />
    <result column="jbxx_status" property="jbxxStatus" jdbcType="VARCHAR" />
    <result column="lsgz_sjkssj" property="lsgzSjkssj" jdbcType="TIMESTAMP" />
    <result column="lsgz_sjwcsj" property="lsgzSjwcsj" jdbcType="TIMESTAMP" />
    <result column="lsgz_fj" property="lsgzFj" jdbcType="VARCHAR" />
    <result column="lsgz_fjmz" property="lsgzFjmz" jdbcType="VARCHAR" />
    <result column="lsgz_wcqksm" property="lsgzWcqksm" jdbcType="VARCHAR" />
    <result column="cjsj" property="cjsj" jdbcType="TIMESTAMP" />
    <result column="cjr" property="cjr" jdbcType="VARCHAR" />
    <result column="xgsj" property="xgsj" jdbcType="TIMESTAMP" />
    <result column="xgr" property="xgr" jdbcType="VARCHAR" />
    <result column="bglb" property="bglb" jdbcType="VARCHAR" />
    <result column="is_deleted" property="isDeleted" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, jbxx_bgszbm, jbxxx_bgr, jbxx_bgzxbm, jbxx_bgzxr, jbxx_bgsj_start, jbxx_bgsj_end,
    jbxx_sqr, jbxx_jyms, jbxx_bgyj, fxfxjkz, jbxx_djrq, jbxx_fj, jbxx_fjmz, jbxx_status,
    lsgz_sjkssj, lsgz_sjwcsj, lsgz_fj, lsgz_fjmz, lsgz_wcqksm, cjsj, cjr, xgsj, xgr,
    bglb, is_deleted
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select
    <include refid="Base_Column_List" />
    from wp_bggl
    where id = #{id,jdbcType=CHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from wp_bggl
    where id = #{id,jdbcType=CHAR}
  </delete>
  <insert id="insert" parameterType="com.zhetang.model.bggl.WpBggl" >
    insert into wp_bggl (id, jbxx_bgszbm, jbxxx_bgr,
      jbxx_bgzxbm, jbxx_bgzxr, jbxx_bgsj_start,
      jbxx_bgsj_end, jbxx_sqr, jbxx_jyms,
      jbxx_bgyj, fxfxjkz, jbxx_djrq,
      jbxx_fj, jbxx_fjmz, jbxx_status,
      lsgz_sjkssj, lsgz_sjwcsj, lsgz_fj,
      lsgz_fjmz, lsgz_wcqksm, cjsj,
      cjr, xgsj, xgr, bglb,
      is_deleted)
    values (#{id,jdbcType=CHAR}, #{jbxxBgszbm,jdbcType=VARCHAR}, #{jbxxxBgr,jdbcType=VARCHAR},
      #{jbxxBgzxbm,jdbcType=VARCHAR}, #{jbxxBgzxr,jdbcType=VARCHAR}, #{jbxxBgsjStart,jdbcType=TIMESTAMP},
      #{jbxxBgsjEnd,jdbcType=TIMESTAMP}, #{jbxxSqr,jdbcType=VARCHAR}, #{jbxxJyms,jdbcType=VARCHAR},
      #{jbxxBgyj,jdbcType=VARCHAR}, #{fxfxjkz,jdbcType=VARCHAR}, #{jbxxDjrq,jdbcType=TIMESTAMP},
      #{jbxxFj,jdbcType=VARCHAR}, #{jbxxFjmz,jdbcType=VARCHAR}, #{jbxxStatus,jdbcType=VARCHAR},
      #{lsgzSjkssj,jdbcType=TIMESTAMP}, #{lsgzSjwcsj,jdbcType=TIMESTAMP}, #{lsgzFj,jdbcType=VARCHAR},
      #{lsgzFjmz,jdbcType=VARCHAR}, #{lsgzWcqksm,jdbcType=VARCHAR}, #{cjsj,jdbcType=TIMESTAMP},
      #{cjr,jdbcType=VARCHAR}, #{xgsj,jdbcType=TIMESTAMP}, #{xgr,jdbcType=VARCHAR}, #{bglb,jdbcType=VARCHAR},
      #{isDeleted,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.zhetang.model.bggl.WpBggl" >
    insert into wp_bggl
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="jbxxBgszbm != null" >
        jbxx_bgszbm,
      </if>
      <if test="jbxxxBgr != null" >
        jbxxx_bgr,
      </if>
      <if test="jbxxBgzxbm != null" >
        jbxx_bgzxbm,
      </if>
      <if test="jbxxBgzxr != null" >
        jbxx_bgzxr,
      </if>
      <if test="jbxxBgsjStart != null" >
        jbxx_bgsj_start,
      </if>
      <if test="jbxxBgsjEnd != null" >
        jbxx_bgsj_end,
      </if>
      <if test="jbxxSqr != null" >
        jbxx_sqr,
      </if>
      <if test="jbxxJyms != null" >
        jbxx_jyms,
      </if>
      <if test="jbxxBgyj != null" >
        jbxx_bgyj,
      </if>
      <if test="fxfxjkz != null" >
        fxfxjkz,
      </if>
      <if test="jbxxDjrq != null" >
        jbxx_djrq,
      </if>
      <if test="jbxxFj != null" >
        jbxx_fj,
      </if>
      <if test="jbxxFjmz != null" >
        jbxx_fjmz,
      </if>
      <if test="jbxxStatus != null" >
        jbxx_status,
      </if>
      <if test="lsgzSjkssj != null" >
        lsgz_sjkssj,
      </if>
      <if test="lsgzSjwcsj != null" >
        lsgz_sjwcsj,
      </if>
      <if test="lsgzFj != null" >
        lsgz_fj,
      </if>
      <if test="lsgzFjmz != null" >
        lsgz_fjmz,
      </if>
      <if test="lsgzWcqksm != null" >
        lsgz_wcqksm,
      </if>
      <if test="cjsj != null" >
        cjsj,
      </if>
      <if test="cjr != null" >
        cjr,
      </if>
      <if test="xgsj != null" >
        xgsj,
      </if>
      <if test="xgr != null" >
        xgr,
      </if>
      <if test="bglb != null" >
        bglb,
      </if>
      <if test="isDeleted != null" >
        is_deleted,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=CHAR},
      </if>
      <if test="jbxxBgszbm != null" >
        #{jbxxBgszbm,jdbcType=VARCHAR},
      </if>
      <if test="jbxxxBgr != null" >
        #{jbxxxBgr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgzxbm != null" >
        #{jbxxBgzxbm,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgzxr != null" >
        #{jbxxBgzxr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgsjStart != null" >
        #{jbxxBgsjStart,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxBgsjEnd != null" >
        #{jbxxBgsjEnd,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxSqr != null" >
        #{jbxxSqr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxJyms != null" >
        #{jbxxJyms,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgyj != null" >
        #{jbxxBgyj,jdbcType=VARCHAR},
      </if>
      <if test="fxfxjkz != null" >
        #{fxfxjkz,jdbcType=VARCHAR},
      </if>
      <if test="jbxxDjrq != null" >
        #{jbxxDjrq,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxFj != null" >
        #{jbxxFj,jdbcType=VARCHAR},
      </if>
      <if test="jbxxFjmz != null" >
        #{jbxxFjmz,jdbcType=VARCHAR},
      </if>
      <if test="jbxxStatus != null" >
        #{jbxxStatus,jdbcType=VARCHAR},
      </if>
      <if test="lsgzSjkssj != null" >
        #{lsgzSjkssj,jdbcType=TIMESTAMP},
      </if>
      <if test="lsgzSjwcsj != null" >
        #{lsgzSjwcsj,jdbcType=TIMESTAMP},
      </if>
      <if test="lsgzFj != null" >
        #{lsgzFj,jdbcType=VARCHAR},
      </if>
      <if test="lsgzFjmz != null" >
        #{lsgzFjmz,jdbcType=VARCHAR},
      </if>
      <if test="lsgzWcqksm != null" >
        #{lsgzWcqksm,jdbcType=VARCHAR},
      </if>
      <if test="cjsj != null" >
        #{cjsj,jdbcType=TIMESTAMP},
      </if>
      <if test="cjr != null" >
        #{cjr,jdbcType=VARCHAR},
      </if>
      <if test="xgsj != null" >
        #{xgsj,jdbcType=TIMESTAMP},
      </if>
      <if test="xgr != null" >
        #{xgr,jdbcType=VARCHAR},
      </if>
      <if test="bglb != null" >
        #{bglb,jdbcType=VARCHAR},
      </if>
      <if test="isDeleted != null" >
        #{isDeleted,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zhetang.model.bggl.WpBggl" >
    update wp_bggl
    <set >
      <if test="jbxxBgszbm != null" >
        jbxx_bgszbm = #{jbxxBgszbm,jdbcType=VARCHAR},
      </if>
      <if test="jbxxxBgr != null" >
        jbxxx_bgr = #{jbxxxBgr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgzxbm != null" >
        jbxx_bgzxbm = #{jbxxBgzxbm,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgzxr != null" >
        jbxx_bgzxr = #{jbxxBgzxr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgsjStart != null" >
        jbxx_bgsj_start = #{jbxxBgsjStart,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxBgsjEnd != null" >
        jbxx_bgsj_end = #{jbxxBgsjEnd,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxSqr != null" >
        jbxx_sqr = #{jbxxSqr,jdbcType=VARCHAR},
      </if>
      <if test="jbxxJyms != null" >
        jbxx_jyms = #{jbxxJyms,jdbcType=VARCHAR},
      </if>
      <if test="jbxxBgyj != null" >
        jbxx_bgyj = #{jbxxBgyj,jdbcType=VARCHAR},
      </if>
      <if test="fxfxjkz != null" >
        fxfxjkz = #{fxfxjkz,jdbcType=VARCHAR},
      </if>
      <if test="jbxxDjrq != null" >
        jbxx_djrq = #{jbxxDjrq,jdbcType=TIMESTAMP},
      </if>
      <if test="jbxxFj != null" >
        jbxx_fj = #{jbxxFj,jdbcType=VARCHAR},
      </if>
      <if test="jbxxFjmz != null" >
        jbxx_fjmz = #{jbxxFjmz,jdbcType=VARCHAR},
      </if>
      <if test="jbxxStatus != null" >
        jbxx_status = #{jbxxStatus,jdbcType=VARCHAR},
      </if>
      <if test="lsgzSjkssj != null" >
        lsgz_sjkssj = #{lsgzSjkssj,jdbcType=TIMESTAMP},
      </if>
      <if test="lsgzSjwcsj != null" >
        lsgz_sjwcsj = #{lsgzSjwcsj,jdbcType=TIMESTAMP},
      </if>
      <if test="lsgzFj != null" >
        lsgz_fj = #{lsgzFj,jdbcType=VARCHAR},
      </if>
      <if test="lsgzFjmz != null" >
        lsgz_fjmz = #{lsgzFjmz,jdbcType=VARCHAR},
      </if>
      <if test="lsgzWcqksm != null" >
        lsgz_wcqksm = #{lsgzWcqksm,jdbcType=VARCHAR},
      </if>
      <if test="cjsj != null" >
        cjsj = #{cjsj,jdbcType=TIMESTAMP},
      </if>
      <if test="cjr != null" >
        cjr = #{cjr,jdbcType=VARCHAR},
      </if>
      <if test="xgsj != null" >
        xgsj = #{xgsj,jdbcType=TIMESTAMP},
      </if>
      <if test="xgr != null" >
        xgr = #{xgr,jdbcType=VARCHAR},
      </if>
      <if test="bglb != null" >
        bglb = #{bglb,jdbcType=VARCHAR},
      </if>
      <if test="isDeleted != null" >
        is_deleted = #{isDeleted,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=CHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zhetang.model.bggl.WpBggl" >
    update wp_bggl
    set jbxx_bgszbm = #{jbxxBgszbm,jdbcType=VARCHAR},
      jbxxx_bgr = #{jbxxxBgr,jdbcType=VARCHAR},
      jbxx_bgzxbm = #{jbxxBgzxbm,jdbcType=VARCHAR},
      jbxx_bgzxr = #{jbxxBgzxr,jdbcType=VARCHAR},
      jbxx_bgsj_start = #{jbxxBgsjStart,jdbcType=TIMESTAMP},
      jbxx_bgsj_end = #{jbxxBgsjEnd,jdbcType=TIMESTAMP},
      jbxx_sqr = #{jbxxSqr,jdbcType=VARCHAR},
      jbxx_jyms = #{jbxxJyms,jdbcType=VARCHAR},
      jbxx_bgyj = #{jbxxBgyj,jdbcType=VARCHAR},
      fxfxjkz = #{fxfxjkz,jdbcType=VARCHAR},
      jbxx_djrq = #{jbxxDjrq,jdbcType=TIMESTAMP},
      jbxx_fj = #{jbxxFj,jdbcType=VARCHAR},
      jbxx_fjmz = #{jbxxFjmz,jdbcType=VARCHAR},
      jbxx_status = #{jbxxStatus,jdbcType=VARCHAR},
      lsgz_sjkssj = #{lsgzSjkssj,jdbcType=TIMESTAMP},
      lsgz_sjwcsj = #{lsgzSjwcsj,jdbcType=TIMESTAMP},
      lsgz_fj = #{lsgzFj,jdbcType=VARCHAR},
      lsgz_fjmz = #{lsgzFjmz,jdbcType=VARCHAR},
      lsgz_wcqksm = #{lsgzWcqksm,jdbcType=VARCHAR},
      cjsj = #{cjsj,jdbcType=TIMESTAMP},
      cjr = #{cjr,jdbcType=VARCHAR},
      xgsj = #{xgsj,jdbcType=TIMESTAMP},
      xgr = #{xgr,jdbcType=VARCHAR},
      bglb = #{bglb,jdbcType=VARCHAR},
      is_deleted = #{isDeleted,jdbcType=VARCHAR}
    where id = #{id,jdbcType=CHAR}
  </update>

  <select id="listAll" resultType="java.util.HashMap" parameterType="com.zhetang.dto.WpBgglQueryDto">
    select distinct b.id, b.jbxx_bgszbm, b.jbxxx_bgr, b.jbxx_bgzxbm, b.jbxx_bgzxr
    , DATE_FORMAT(jbxx_bgsj_start,'%Y-%m-%d') as jbxx_bgsj_start
    , DATE_FORMAT(jbxx_bgsj_end,'%Y-%m-%d') as jbxx_bgsj_end,jbxx_sqr, b.jbxx_jyms, b.jbxx_bgyj, b.fxfxjkz,
    DATE_FORMAT(jbxx_djrq,'%Y-%m-%d') as jbxx_djrq, b.jbxx_fj, b.jbxx_fjmz,
    DATE_FORMAT(lsgz_sjkssj,'%Y-%m-%d') as lsgz_sjkssj,DATE_FORMAT(lsgz_sjwcsj,'%Y-%m-%d') as lsgz_sjwcsj
    , b.lsgz_fj, b.lsgz_fjmz, b.lsgz_wcqksm, b.cjsj, b.cjr, b.xgsj, b.xgr, b.bglb, b.is_deleted,
    b.jbxx_status
    ,w.id as processinstId,w.`status` as wfStatus
    ,o1.name as jbxx_bgszbm_name,o2.name as jbxx_bgzxbm_name
    from wp_bggl b
    left JOIN wf_processinst w on b.id = w.businessId
    left join base_organization o1 on b.jbxx_bgszbm = o1.id
    left join base_organization o2 on b.jbxx_bgzxbm = o2.id
    join base_data_table bdt on bdt.tableid=b.id
    <where>
      <if test="jbxxBgszbm!=null and jbxxBgszbm!=''">
        and o1.name like concat('%',concat(#{jbxxBgszbm},'%'))
      </if>
      <if test="jbxxxBgr!=null and jbxxxBgr!=''">
        and jbxxx_bgr like concat('%',concat(#{jbxxxBgr},'%'))
      </if>
      <if test="jbxxBgzxbm!=null and jbxxBgzxbm!=''">
        and o2.name like concat('%',concat(#{jbxxBgzxbm},'%'))
      </if>
      <if test="jbxxBgzxr!=null and jbxxBgzxr!=''">
        and jbxx_sqr like concat('%',concat(#{jbxxBgzxr},'%'))
      </if>
      <if test="jbxxBgsjStart!=null ">
        and jbxx_bgsj_start &gt;=#{jbxxBgsjStart}
      </if>
      <if test="jbxxBgsjEnd!=null ">
        and jbxx_bgsj_end &lt;=DATE_ADD(#{jbxxBgsjEnd},INTERVAL 1 DAY)
      </if>
      <if test="jbxxSqr!=null and jbxxSqr!=''">
        and jbxx_sqr like concat('%',concat(#{jbxxSqr},'%'))
      </if>
      <if test="djrqStart!=null ">
        and jbxx_djrq &gt;=#{djrqStart}
      </if>
      <if test="djrqEnd!=null ">
        and jbxx_djrq &lt;=#{djrqEnd}
      </if>
      <if test="isDeleted!=null">
        and is_deleted=#{isDeleted}
      </if>
      <if test="bglb!=null and bglb!=''">
        and bglb=#{bglb}
      </if>
      <if test="allColumn!=null and allColumn!=''">
        and concat(o1.name,jbxxx_bgr,o2.name,jbxx_bgzxr) like concat('%',concat(#{allColumn},'%'))
      </if>
      <if test="jbxxStatus!=null ">
        and b.jbxx_status=#{jbxxStatus}
      </if>
      <if test="wfStatus!=null">
        and w.`status`=#{wfStatus}
      </if>
      <if test="oidStr != null">
        AND bdt.oid in (${oidStr})
      </if>
    </where>
    order by b.cjsj desc
  </select>
  <select id="selectByIdWithWorkflow" resultType="java.util.HashMap">
     select  b.id, b.jbxx_bgszbm, b.jbxxx_bgr, b.jbxx_bgzxbm, b.jbxx_bgzxr
    , DATE_FORMAT(jbxx_bgsj_start,'%Y-%m-%d') as jbxx_bgsj_start
    , DATE_FORMAT(jbxx_bgsj_end,'%Y-%m-%d') as jbxx_bgsj_end,jbxx_sqr, b.jbxx_jyms, b.jbxx_bgyj, b.fxfxjkz,
    DATE_FORMAT(jbxx_djrq,'%Y-%m-%d') as jbxx_djrq, b.jbxx_fj, b.jbxx_fjmz,
    DATE_FORMAT(lsgz_sjkssj,'%Y-%m-%d') as lsgz_sjkssj,DATE_FORMAT(lsgz_sjwcsj,'%Y-%m-%d') as lsgz_sjwcsj
    , b.lsgz_fj, b.lsgz_fjmz, b.lsgz_wcqksm, b.cjsj, b.cjr, b.xgsj, b.xgr, b.bglb, b.is_deleted,
    b.jbxx_status
    ,w.id as processinstId,w.`status` as wfStatus
    ,o1.name as jbxx_bgszbm_name,o2.name as jbxx_bgzxbm_name
    from wp_bggl b
    left JOIN wf_processinst w on b.id = w.businessId
    left join base_organization o1 on b.jbxx_bgszbm = o1.id
    left join base_organization o2 on b.jbxx_bgzxbm = o2.id
    where b.id=#{id}
  </select>

</mapper>
View Code

 EoCollectMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hainei.mapper.economic.EoCollectMapper">
  <resultMap id="BaseResultMap" type="com.hainei.pojo.model.economic.EoCollect">
    <!--@mbg.generated generated on Wed Apr 13 11:04:54 CST 2022.-->
    <!--@Table eo_collect-->
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" />
    <result column="company" jdbcType="VARCHAR" property="company" />
    <result column="year" jdbcType="VARCHAR" property="year" />
    <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" />
    <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" />
    <result column="year_profit_rate" jdbcType="DECIMAL" property="yearProfitRate" />
    <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" />
    <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" />
    <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" />
    <result column="gmt_created_on" jdbcType="TIMESTAMP" property="gmtCreatedOn" />
    <result column="gmt_created_by" jdbcType="VARCHAR" property="gmtCreatedBy" />
    <result column="gmt_updated_by" jdbcType="VARCHAR" property="gmtUpdatedBy" />
    <result column="gmt_updated_on" jdbcType="TIMESTAMP" property="gmtUpdatedOn" />
    <result column="is_deleted" jdbcType="TINYINT" property="isDeleted" />
    <result column="version" jdbcType="INTEGER" property="version" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated generated on Wed Apr 13 11:04:54 CST 2022.-->
    id, company_auth, company, `year`, year_revenue, year_produce_value, year_profit_rate, 
    year_person_num, year_research_person_num, year_research_put, gmt_created_on, gmt_created_by, 
    gmt_updated_by, gmt_updated_on, is_deleted, version
  </sql>
  <resultMap id="listResultMap" type="com.hainei.pojo.vo.economic.EoCollectVO">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" />
    <result column="company" jdbcType="VARCHAR" property="company" />
    <result column="year" jdbcType="VARCHAR" property="year" />
    <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" />
    <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" />
     <result column="year_profit_rate" jdbcType="VARCHAR" property="yearProfitRate" />
    <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" />
    <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" />
    <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" />
    </resultMap>
    <resultMap id="listEoCollectAndQuarter" type="com.hainei.pojo.vo.economic.EoCollectAndQuarterVO">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="company_auth" jdbcType="VARCHAR" property="companyAuth" />
    <result column="company" jdbcType="VARCHAR" property="company" />
    <result column="year" jdbcType="VARCHAR" property="year" />
    <result column="year_revenue" jdbcType="DECIMAL" property="yearRevenue" />
    <result column="year_produce_value" jdbcType="DECIMAL" property="yearProduceValue" />
     <result column="quarter" jdbcType="INTEGER" property="quarter" />
    <result column="revenve" jdbcType="DECIMAL" property="revenue" />
    <result column="product_value" jdbcType="DECIMAL" property="productValue" />
     <result column="year_profit_rate" jdbcType="VARCHAR" property="yearProfitRate" />
    <result column="year_person_num" jdbcType="INTEGER" property="yearPersonNum" />
    <result column="year_research_person_num" jdbcType="INTEGER" property="yearResearchPersonNum" />
    <result column="year_research_put" jdbcType="VARCHAR" property="yearResearchPut" />
    </resultMap>
  <select id="listEoCollect" resultMap="listResultMap">
    select id, company_auth, company, `year`, year_revenue, year_produce_value, gmt_created_on,
    gmt_created_by, gmt_updated_by, gmt_updated_on, is_deleted, version, year_profit_rate,
    year_person_num, year_research_person_num, year_research_put
    from eo_collect d
    where 1=1
    <if test="company != null and company != ''">
     and   d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%'))
    </if>
    <if test="year != null and year != ''">
     and  d.year = #{year,jdbcType=VARCHAR}
    </if>
    <foreach close=")" collection="olists" index="index" item="oitem" open="and( 1!=1 " separator="">
                or d.company_auth = #{oitem}
    </foreach>
    <if test="olists.size == 0">
        and d.company_auth= "11111111111111111111111111111111111111111"
    </if>
     and d.is_deleted =0
     order by d.gmt_created_on desc
    </select>
  <select id="listEoCollectAndQuarter" resultMap="listEoCollectAndQuarter">
    select distinct d.id,q.`quarter`,q.revenve,q.product_value, d.company_auth, d.company, d.`year`, d.year_revenue,
     d.year_produce_value, year_profit_rate,year_person_num, year_research_person_num, year_research_put
    from eo_collect d
    left join eo_collect_quarter q
    on q.collect_id = d.id
    where 1=1
    <if test="company != null and company != ''">
     and   d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%'))
    </if>
    <foreach close=")" collection="olists" index="index" item="oitem" open="and( 1!=1 " separator="">
                or d.company_auth = #{oitem}
    </foreach>
    <if test="olists.size == 0">
        and d.company_auth= "11111111111111111111111111111111111111111"
    </if>
    <if test="yearList != null and yearList.size() != 0">
        <foreach close=")" collection="yearList" index="yearIndex" item="yearItem" open="and( 1!=1" separator="">
         or d.`year` = #{yearItem}
        </foreach>
    </if>
    <if test="companyList != null and companyList.size() != 0">
        <foreach close=")" collection="companyList" index="companyIndex" item="companyItem" open="and( 1!=1" separator="">
          or d.company = #{ companyItem,jdbcType=VARCHAR}
        </foreach>
    </if>
    <if test="quarterList != null and quarterList.size() != 0">
        <foreach close=")" collection="quarterList" index="quarterIndex" item="quarterItem" open="and( 1!=1" separator="">
         or q.quarter = #{ quarterItem,jdbcType=INTEGER}
        </foreach>
    </if>

     and d.is_deleted =0
     order by d.gmt_created_on desc
    </select>
  <select id="listByCompanyAndYear" resultMap="listResultMap">
  select id, company_auth, company, `year`, year_revenue, year_produce_value, gmt_created_on,
    gmt_created_by, gmt_updated_by, gmt_updated_on, is_deleted, version
    from eo_collect d
    where 1=1
    <if test="company != null and company != ''">
     and   d.company like concat('%',concat(#{company,jdbcType=VARCHAR},'%'))
    </if>
    <if test="year != null and year != ''">
     and  d.year = #{year,jdbcType=VARCHAR}
    </if>
     and d.is_deleted =0
     order by d.gmt_created_on desc
   </select>

</mapper>
View Code

 

WpBggIMapper.java

package com.zhetang.dao.bggl;

import com.zhetang.dto.WpBgglQueryDto;
import com.zhetang.model.bggl.WpBggl;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.HashMap;
import java.util.List;

@Mapper
public interface WpBgglMapper {
    int deleteByPrimaryKey(String id);

    int insert(WpBggl record);

    int insertSelective(WpBggl record);

    WpBggl selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(WpBggl record);

    int updateByPrimaryKey(WpBggl record);

    List<HashMap<String, Object>> listAll(WpBgglQueryDto dto);

    HashMap<String, Object> selectByIdWithWorkflow(@Param("id") String id);

}
View Code

 wpCbsDtzy.mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.hainei.mapper.wp.zyaqgl.WpCbsDtzyMapper" >
  <resultMap id="BaseResultMap" type="com.hainei.pojo.model.wp.zyaqgl.WpCbsDtzy" >
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" property="id" jdbcType="CHAR" />
    <result column="zyzmc" property="zyzmc" jdbcType="VARCHAR" />
    <result column="aqcs" property="aqcs" jdbcType="VARCHAR" />
    <result column="sqr" property="sqr" jdbcType="VARCHAR" />
    <result column="zydw" property="zydw" jdbcType="VARCHAR" />
    <result column="zykssj" property="zykssj" jdbcType="BIGINT" />
    <result column="zyjssj" property="zyjssj" jdbcType="BIGINT" />
    <result column="status" property="status" jdbcType="INTEGER" />
    <result column="zyzbh" property="zyzbh" jdbcType="VARCHAR" />
    <result column="whbs" property="whbs" jdbcType="VARCHAR" />
    <result column="jhr" property="jhr" jdbcType="VARCHAR" />
    <result column="ssaqjyr" property="ssaqjyr" jdbcType="VARCHAR" />
    <result column="is_deleted" property="isDeleted" jdbcType="TINYINT" />
    <result column="gmt_created_on" property="gmtCreatedOn" jdbcType="TIMESTAMP" />
    <result column="gmt_updated_on" property="gmtUpdatedOn" jdbcType="TIMESTAMP" />
    <result column="gmt_created_by" property="gmtCreatedBy" jdbcType="VARCHAR" />
    <result column="gmt_updated_by" property="gmtUpdatedBy" jdbcType="VARCHAR" />
    <result column="sqbm" property="sqbm" jdbcType="VARCHAR" />
    <result column="zydd" property="zydd" jdbcType="VARCHAR" />
    <result column="zyfw" property="zyfw" jdbcType="VARCHAR" />
    <result column="zyfwjt" property="zyfwjt" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List">
  id, sqr ,jhr, zykssj, zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr ,status
  </sql>
  <select id="getById" resultType="map">
    select DISTINCT
    id,zyzmc,zyzbh,sqbm,qttszy, sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr
    ,status,DATE_FORMAT(d.gmt_created_on,"%Y-%m-%d %H:%i:%s") gmtCreatedOn
    FROM wp_cbs_dtzy d
    where d.id = #{id,jdbcType=VARCHAR}
  </select>

  <select id="selectBySelective" resultType="map">
    select DISTINCT
    id,zyzmc,zyzbh,sqbm,qttszy,sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr
    ,status ,DATE_FORMAT(d.gmt_created_on,"%Y-%m-%d %H:%i:%s") gmtCreatedOn
    FROM wp_cbs_dtzy d
    <if test="gmtCreatedBy!='superAdmin'">
      INNER JOIN view_user_data_permission view_udp ON view_udp.table_id=d.id
    </if>
    WHERE 1=1
    <if test="sqr != null and sqr != ''" >
      AND d.sqr LIKE CONCAT(CONCAT('%',#{sqr,jdbcType=VARCHAR},'%'))
    </if>
    <if test="zyzmc != null and zyzmc != ''" >
      AND d.zyzmc LIKE CONCAT(CONCAT('%',#{zyzmc,jdbcType=VARCHAR},'%'))
    </if>
    <if test="zyzbh != null and zyzbh != ''" >
      AND d.zyzbh LIKE CONCAT(CONCAT('%',#{zyzbh,jdbcType=VARCHAR},'%'))
    </if>
    <if test="sqbm != null and sqbm != ''" >
      AND d.sqbm LIKE CONCAT(CONCAT('%',#{sqbm,jdbcType=VARCHAR},'%'))
    </if>
    <if test="jhr != null and jhr != ''" >
      AND d.jhr LIKE CONCAT(CONCAT('%',#{jhr,jdbcType=VARCHAR},'%'))
    </if>
    <if test="zydd != null and zydd != ''" >
      AND d.zydd LIKE CONCAT(CONCAT('%',#{zydd,jdbcType=VARCHAR},'%'))
    </if>
    <if test="zydw != null and zydw != ''" >
      AND d.zydw LIKE CONCAT(CONCAT('%',#{zydw,jdbcType=VARCHAR},'%'))
    </if>
    <if test="zyfw != null and zyfw != ''" >
      AND d.zyfw LIKE CONCAT(CONCAT('%',#{zyfw,jdbcType=VARCHAR},'%'))
    </if>
    <if test="whbs != null and whbs != ''" >
      AND d.whbs LIKE CONCAT(CONCAT('%',#{whbs,jdbcType=VARCHAR},'%'))
    </if>
    <if test="ssaqjyr != null and ssaqjyr != ''" >
      AND d.ssaqjyr LIKE CONCAT(CONCAT('%',#{ssaqjyr,jdbcType=VARCHAR},'%'))
    </if>
    <if test="status != null " >
      AND d.status = #{status}
    </if>
    <if test="zykssj != null and zyjssj != null">
      AND d.zykssj &gt;= #{zykssj,jdbcType=INTEGER} and d.zyjssj &lt;= #{zyjssj,jdbcType=INTEGER}
    </if>
    <if test="all != null and all != ''">
      AND CONCAT(d.zyzmc, d.zyzbh, d.sqr, d.ssaqjyr) LIKE
      CONCAT(CONCAT('%',#{all},'%'))
    </if>
    <choose>
      <when test="gmtCreatedBy=='superAdmin'">
        and 1=1
      </when>
      <otherwise>
        and  view_udp.user_id=#{gmtCreatedBy}
        and IF(view_udp.is_view_yourself=0,1=1,d.gmt_created_by=#{gmtCreatedBy})
      </otherwise>
    </choose>
    AND d.is_deleted= 0
    order by d.gmt_created_on desc
  </select>
  <select id="totalNums" resultType="map">
    select DISTINCT
    id,zyzmc,zyzbh,sqbm,qttszy,sqr ,jhr,FROM_UNIXTIME(d.zykssj/1000,"%Y-%m-%d %H:%i:%s") zykssj,FROM_UNIXTIME(d.zyjssj/1000,"%Y-%m-%d %H:%i:%s") zyjssj ,zydd ,zydw ,zyfw, whbs ,ssaqjyr
    ,status ,DATE_FORMAT(d.gmt_created_on,"%m") gmtCreatedOn,d.is_deleted isDeleted
    FROM wp_cbs_dtzy d
    WHERE d.is_deleted= 0
  </select>
</mapper>
View Code

 

 

WpFileServiceImpl.java

package com.zhetang.service.aqzd.impl;

import com.zhetang.admin.permission.model.BaseUser;
import com.zhetang.dao.aqzd.WpFileMapper;
import com.zhetang.model.aqzd.WpFile;
import com.zhetang.service.aqzd.WpFileService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/11/19 13:55
 * @Description:
 */
@Service
public class WpFileServiceImpl implements WpFileService {
    @Autowired
    private WpFileMapper fileMapper;

    @Override
    public void addFiles(List<WpFile> fileList,String uId,Byte type, HttpServletRequest request) {
        fileMapper.updateByUid(uId,type);
        BaseUser baseUser = (BaseUser) request.getSession().getAttribute("user");
        int sort = 1;
        for (WpFile file:fileList){
            file.setType(type);
            file.setSort(sort++);
            if (file.getId() == null || "".equals(file.getId())) {
                file.setuId(uId);
                String id = UUID.randomUUID().toString().replace("-", "");
                file.setCjr(baseUser.getId());
                file.setCjsj(new Date());
                file.setId(id);
                fileMapper.insertSelective(file);
            }else {
                //将假删除的状态改了
                file.setDeleteStatus(0);
                file.setXgr(baseUser.getId());
                file.setXgsj(new Date());
                fileMapper.updateByPrimaryKeySelective(file);
            }
        }

    }
}
View Code

 

 

数据库建表规范

 

 

TemplateController

package com.zhetang.controller;

import com.zhetang.core.vo.JsonResult;
import com.zhetang.dao.BaseTemplateDisposeMapper;
import com.zhetang.dao.BaseTemplateFieldMapper;
import com.zhetang.dao.ExportMapper;
import com.zhetang.model.BaseTemplateDispose;
import com.zhetang.model.BaseTemplateField;
import com.zhetang.util.ExportExcel;
import com.zhetang.util.ParseExcel;
import com.zhetang.utils.upload.FileModel;
import com.zhetang.utils.upload.FileUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.zhetang.util.ExportWord.exportDoc;
import static com.zhetang.util.ExportWord.exportDocx;
import static com.zhetang.util.ParseWord.parseDoc;
import static com.zhetang.util.ParseWord.parseDocx;

/**
 * @Auther: GUJUANJUAN
 * @Date: 2019/12/20 11:58
 * @Description:
 */
@Controller
@RequestMapping("template")
@PropertySource({"classpath:application.properties"})
public class TemplateController {

    @Value("${uploadPath}")
    private String uploadPath;
    @Value("${readPath}")
    private String readPath;
    @Autowired
    private ExportMapper exportMapper;
    @Autowired
    private BaseTemplateDisposeMapper disposeMapper;
    @Autowired
    private BaseTemplateFieldMapper fieldMapper;

    /**
     * 导入模板
     *
     * @param file
     * @return
     */
    @RequestMapping("import")
    @ResponseBody
    public JsonResult getListByMes(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        List<String> list = new ArrayList<>();
        String fileName = file.getOriginalFilename();
        File wordFile = null;
        try {
            if (fileName.endsWith("docx") || fileName.endsWith("doc")) {
                wordFile = new File(uploadPath + fileName);
                file.transferTo(wordFile);
            }
            if (fileName.endsWith("docx")) {
                list = parseDocx(wordFile);
                wordFile.delete();
            } else if (fileName.endsWith("doc")) {
                list = parseDoc(wordFile);
                wordFile.delete();
            } else if (fileName.endsWith("xls")) {
                list = ParseExcel.parseXls(file);
            } else if (fileName.endsWith("xlsx")) {
                list = ParseExcel.parseXlsx(file);
            } else {
                return JsonResult.build(400, "格式不正确");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return JsonResult.build(400, e.getMessage());
        }
        return JsonResult.ok(list);
    }


    /**
     * 检索字段导出 /template/export
     *
     * @param fileName 导出的文件名
     * @param id       导出配置id
     * @param field    检索字段名
     * @param value    检索值
     * @param response
     * @param request
     * @return
     */
    @RequestMapping(value = "/export")
    @ResponseBody
    public JsonResult export(String fileName, String id, String field, String value, HttpServletResponse response, HttpServletRequest request) {
        BaseTemplateDispose dispose = disposeMapper.selectByPrimaryKey(id);
        if (dispose == null) {
            return JsonResult.build(400, "模版不存在!");
        }
        List<Map<String, Object>> list = exportMapper.selectByKey(dispose.getTableName(), field, value);
        if (list.size() > 1) {
            return JsonResult.build(400, "检索数据超过一条,无法导出!");
        } else if (list.size() == 0) {
            return JsonResult.build(400, "没有检索到数据,无法导出!");
        }
        List<BaseTemplateField> fieldList = fieldMapper.selectByDisposeId(dispose.getId());
        Map<String, Object> map = list.get(0);
        //替换值
        Map<String, Object> params = new HashMap<>();
        for (BaseTemplateField field1 : fieldList) {
            params.put(field1.getName(), map.get(field1.getMatchField()));
        }
        File file = new File(uploadPath + dispose.getFileAddress().replace(readPath, ""));
        return exportFile(response, request, params, file, fileName,id);
    }

    /**
     * 文件导出
     *
     * @param response
     * @param request
     * @param params
     * @param file
     * @param newName
     * @return
     */
    private JsonResult exportFile(HttpServletResponse response, HttpServletRequest request, Map<String, Object> params, File file, String newName,String id) {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            MultipartFile multipartFile = new MockMultipartFile(file.getName(), inputStream);
            String fileName = multipartFile.getName();
            if (fileName.endsWith("docx")) {
                exportDocx(uploadPath +"word"+File.separator+ fileName, uploadPath, request, response, newName + ".docx", params);
            } else if (fileName.endsWith("doc")) {
                exportDoc(file, uploadPath, response, newName + ".doc", params,request);
            } else if (fileName.endsWith("xls")) {
                ExportExcel.exportXls(multipartFile, response, newName + ".xls", params);
            } else if (fileName.endsWith("xlsx")) {
                ExportExcel.exportXlsx(multipartFile, response, newName + ".xlsx", params);
            } else {
                return JsonResult.build(400, "格式不正确");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return JsonResult.build(400, "导出失败");
        } finally {
            try {
                inputStream.close();
                disposeMapper.updateExortCount(id);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return JsonResult.ok();
    }

    /**
     * 获取所有非基础表 /template/getAllTables
     *
     * @return
     */
    @RequestMapping("getAllTables")
    @ResponseBody
    public JsonResult getAllTables() {
        return JsonResult.ok(exportMapper.getAllTables());
    }

    /**
     * 获取表所有字段 /template/getAllColumn
     *
     * @param tableName
     * @return
     */
    @RequestMapping("getAllColumn")
    @ResponseBody
    public JsonResult getAllColumn(String tableName) {
        System.out.println("tableName = " + tableName);
        return JsonResult.ok(exportMapper.getAllColumn(tableName));
    }

}
View Code
posted @ 2020-01-08 09:05  wq9  阅读(213)  评论(0)    收藏  举报