<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~
  ~      Copyright (c) 2018-2025, lengleng All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the yifu4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: lengleng (wangiegie@gmail.com)
  ~
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yifu.cloud.plus.v1.yifu.archives.mapper.TFascTemplateMapper">

  <resultMap id="tFascTemplateMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TFascTemplate">
    <id property="id" column="id"/>
    <result property="companyId" column="company_id"/>
    <result property="companyName" column="company_name"/>
    <result property="signName" column="sign_name"/>
    <result property="signOpenId" column="sign_open_id"/>
    <result property="signTemplateId" column="sign_template_id"/>
    <result property="signTemplateName" column="sign_template_name"/>
    <result property="signTemplateStatus" column="sign_template_status"/>
    <result property="creatorMemberName" column="creator_member_name"/>
    <result property="fascCreateTime" column="fasc_create_time"/>
    <result property="fascUpdateTime" column="fasc_update_time"/>
    <result property="zhangName" column="zhang_name"/>
    <result property="isMianQian" column="is_mian_qian"/>
    <result property="contractType" column="contract_type"/>
    <result property="isMustEdit" column="is_must_edit"/>
    <result property="createBy" column="CREATE_BY"/>
    <result property="createName" column="CREATE_NAME"/>
    <result property="createTime" column="CREATE_TIME"/>
    <result property="updateBy" column="UPDATE_BY"/>
    <result property="updateTime" column="UPDATE_TIME"/>
    <result property="companyActorId" column="company_actor_id"/>
    <result property="personActorId" column="person_actor_id"/>

	  <!-- 非数据库字段，用来比较厚，更新日志 -->
	<result property="isMustNow" column="is_must_now"/>
	<result property="isMustAll" column="is_must_all"/>
  </resultMap>

	<resultMap id="tFascTemplateExportMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.TFascTemplateExportVo">
    <result property="companyName" column="company_name"/>
    <result property="signName" column="sign_name"/>
    <result property="signOpenId" column="sign_open_id"/>
    <result property="signTemplateId" column="sign_template_id"/>
    <result property="signTemplateName" column="sign_template_name"/>
    <result property="signTemplateStatus" column="sign_template_status"/>
    <result property="creatorMemberName" column="creator_member_name"/>
    <result property="fascUpdateTime" column="fasc_update_time"/>
    <result property="zhangName" column="zhang_name"/>
    <result property="isMianQian" column="is_mian_qian"/>
    <result property="contractType" column="contract_type"/>
    <result property="isMustEdit" column="is_must_edit"/>
  </resultMap>
    <sql id="Base_Column_List">
                a.id,
                a.company_id,
                a.company_name,
                a.sign_name,
                a.sign_open_id,
                a.sign_template_id,
                a.sign_template_name,
                a.sign_template_status,
                a.creator_member_name,
                a.fasc_create_time,
                a.fasc_update_time,
                a.zhang_name,
                a.is_mian_qian,
                a.contract_type,
                a.is_must_edit,
                a.CREATE_BY,
                a.CREATE_NAME,
                a.CREATE_TIME,
                a.UPDATE_BY,
                a.UPDATE_TIME,
                a.company_actor_id,
                a.person_actor_id
    </sql>
    <sql id="tFascTemplate_where">
        <if test="tFascTemplate != null">
                    <if test="tFascTemplate.id != null and tFascTemplate.id.trim() != ''">
                        AND a.id = #{tFascTemplate.id}
                    </if>
                    <if test="tFascTemplate.companyId != null and tFascTemplate.companyId.trim() != ''">
                        AND a.company_id like concat('%', #{tFascTemplate.companyId}, '%')
                    </if>
                    <if test="tFascTemplate.companyName != null and tFascTemplate.companyName.trim() != ''">
                        AND a.company_name like concat('%', #{tFascTemplate.companyName}, '%')
                    </if>
                    <if test="tFascTemplate.signName != null and tFascTemplate.signName.trim() != ''">
                        AND a.sign_name = #{tFascTemplate.signName}
                    </if>
                    <if test="tFascTemplate.signOpenId != null and tFascTemplate.signOpenId.trim() != ''">
                        AND a.sign_open_id = #{tFascTemplate.signOpenId}
                    </if>
                    <if test="tFascTemplate.signTemplateId != null and tFascTemplate.signTemplateId.trim() != ''">
                        AND a.sign_template_id = #{tFascTemplate.signTemplateId}
                    </if>
                    <if test="tFascTemplate.signTemplateName != null and tFascTemplate.signTemplateName.trim() != ''">
                        AND a.sign_template_name like concat('%', #{tFascTemplate.signTemplateName}, '%')
                    </if>
                    <if test="tFascTemplate.signTemplateStatus != null and tFascTemplate.signTemplateStatus.trim() != ''">
                        AND a.sign_template_status = #{tFascTemplate.signTemplateStatus}
                    </if>
                    <if test="tFascTemplate.creatorMemberName != null and tFascTemplate.creatorMemberName.trim() != ''">
                        AND a.creator_member_name like concat('%', #{tFascTemplate.creatorMemberName}, '%')
                    </if>
                    <if test="tFascTemplate.fascCreateTime != null">
                        AND a.fasc_create_time = #{tFascTemplate.fascCreateTime}
                    </if>
                    <if test="tFascTemplate.fascUpdateTime != null">
                        AND a.fasc_update_time = #{tFascTemplate.fascUpdateTime}
                    </if>
                    <if test="tFascTemplate.zhangName != null and tFascTemplate.zhangName.trim() != ''">
                        AND a.zhang_name like concat('%', #{tFascTemplate.zhangName}, '%')
                    </if>
                    <if test="tFascTemplate.isMianQian != null and tFascTemplate.isMianQian.trim() != ''">
                        AND a.is_mian_qian = #{tFascTemplate.isMianQian}
                    </if>
                    <if test="tFascTemplate.contractType != null and tFascTemplate.contractType.trim() != ''">
                        AND a.contract_type = #{tFascTemplate.contractType}
                    </if>
                    <if test="tFascTemplate.isMustEdit != null and tFascTemplate.isMustEdit.trim() != ''">
                        AND a.is_must_edit = #{tFascTemplate.isMustEdit}
                    </if>

			<if test="tFascTemplate.isMustEditArray != null and tFascTemplate.isMustEditArray.length > 0">
				AND a.is_must_edit in
				<foreach item="statusStr" index="index" collection="tFascTemplate.isMustEditArray" open="("
						 separator="," close=")">
					#{statusStr}
				</foreach>
			</if>

                    <if test="tFascTemplate.createBy != null and tFascTemplate.createBy.trim() != ''">
                        AND a.CREATE_BY = #{tFascTemplate.createBy}
                    </if>
                    <if test="tFascTemplate.createName != null and tFascTemplate.createName.trim() != ''">
                        AND a.CREATE_NAME = #{tFascTemplate.createName}
                    </if>
                    <if test="tFascTemplate.createTime != null">
                        AND a.CREATE_TIME = #{tFascTemplate.createTime}
                    </if>
                    <if test="tFascTemplate.updateBy != null and tFascTemplate.updateBy.trim() != ''">
                        AND a.UPDATE_BY = #{tFascTemplate.updateBy}
                    </if>
                    <if test="tFascTemplate.updateTime != null">
                        AND a.UPDATE_TIME = #{tFascTemplate.updateTime}
                    </if>
        </if>
    </sql>
    <!--tFascTemplate简单分页查询-->
    <select id="getTFascTemplatePage" resultMap="tFascTemplateMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_fasc_template a
        <where>
            a.delete_flag = '0'
            <include refid="tFascTemplate_where"/>
        </where>
        ORDER BY CASE WHEN a.sign_name = '安徽皖信人力资源管理有限公司' THEN 1 ELSE 0 END DESC
		,a.fasc_create_time DESC
    </select>

    <!--tFascTemplate简单分页查询-->
    <select id="getTFascTemplateList" resultMap="tFascTemplateMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_fasc_template a
        <where>
			a.delete_flag = '0'
			<include refid="tFascTemplate_where"/>
        </where>
        ORDER BY CASE WHEN a.sign_name = '安徽皖信人力资源管理有限公司' THEN 1 ELSE 0 END DESC
		,a.fasc_create_time DESC
    </select>
	<!--全量-->
    <select id="getTFascTemplateListAll" resultMap="tFascTemplateMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_fasc_template a
        <where>
			1=1
			<include refid="tFascTemplate_where"/>
        </where>
        ORDER BY CASE WHEN a.sign_name = '安徽皖信人力资源管理有限公司' THEN 1 ELSE 0 END DESC
		,a.fasc_create_time DESC
    </select>
    <!--导出-->
    <select id="getTFascTemplateExport" resultMap="tFascTemplateExportMap">
        SELECT
			a.company_name,
			a.sign_name,
			a.sign_open_id,
			a.sign_template_id,
			a.sign_template_name,
			a.creator_member_name,
			date_format(a.fasc_update_time, '%Y-%m-%d %H:%i:%s') fasc_update_time,
			a.zhang_name,
			if(a.sign_template_status = 'valid', '启用', if(a.sign_template_status = 'invalid', '停用', '草稿')) sign_template_status,
			if(a.is_mian_qian='0','否','是') is_mian_qian,
			a.contract_type,
			if(a.is_must_edit = '2', '已配置', if(a.sign_template_status = '1', '部分配置', '未配置')) is_must_edit
        FROM t_fasc_template a
        <where>
			a.delete_flag = '0'
			<include refid="tFascTemplate_where"/>
			<if test="idList != null and idList.size>0">
				AND a.id in
				<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
        </where>
        ORDER BY CASE WHEN a.sign_name = '安徽皖信人力资源管理有限公司' THEN 1 ELSE 0 END DESC
		,a.fasc_create_time DESC
		<if test="tFascTemplate != null">
			<if test="tFascTemplate.limitStart != null">
				limit #{tFascTemplate.limitStart},#{tFascTemplate.limitEnd}
			</if>
		</if>
    </select>

	<!--导出条数-->
	<select id="getTFascTemplateExportCount" resultType="java.lang.Long">
        SELECT
			count(1)
        FROM t_fasc_template a
        <where>
			a.delete_flag = '0'
			<include refid="tFascTemplate_where"/>
			<if test="idList != null and idList.size>0">
				AND a.id in
				<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
        </where>
    </select>

    <!--获取所有要更新是否必填的模板数据-->
    <select id="getTFascTemplateResutList" resultMap="tFascTemplateMap">
		SELECT
			A.id,
			A.sign_template_id,
			A.is_must_edit,
			-- 统计必填且有字段ID的数量
			(SELECT COUNT(*) FROM t_fasc_template_detail B
			 WHERE B.delete_flag='0' and B.sign_template_id = A.sign_template_id
			   AND B.is_must = 1 AND B.hr_field_id IS NOT NULL AND B.hr_field_id != '') as is_must_now,
			-- 统计总明细数量
			(SELECT COUNT(*) FROM t_fasc_template_detail B
		WHERE B.delete_flag='0' and B.sign_template_id = A.sign_template_id AND B.is_must = 1) as is_must_all
		FROM t_fasc_template A WHERE A.delete_flag = '0'
		<if test="templateId != null and templateId.trim() != ''">
			and A.sign_template_id = #{templateId}
		</if>
    </select>

	<!--获取签署方下拉列表-->
	<select id="getSignNamePage" resultMap="tFascTemplateMap">
		SELECT
			DISTINCT a.sign_name
		FROM t_fasc_template a
		<where> a.delete_flag = '0'
			<if test="signName != null and signName.trim() != ''">
				AND a.sign_name like concat('%', #{signName}, '%')
			</if>
		</where>
		ORDER BY CASE WHEN a.sign_name = '安徽皖信人力资源管理有限公司' THEN 1 ELSE 0 END DESC
		,a.fasc_create_time DESC
	</select>

</mapper>
