<?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.rongwei.bscommon.sys.dao.SrmIssuesListDao">
    <resultMap id="BaseResultMap" type="com.rongwei.bsentity.domin.SrmIssuesListDo">
        <id column="ID" jdbcType="VARCHAR" property="id"/>
        <result column="TENANTID" jdbcType="VARCHAR" property="tenantid"/>
        <result column="ROPTION" jdbcType="VARCHAR" property="roption"/>
        <result column="DELETED" jdbcType="VARCHAR" property="deleted"/>
        <result column="REMARK" jdbcType="VARCHAR" property="remark"/>
        <result column="CREATEDATE" jdbcType="TIMESTAMP" property="createdate"/>
        <result column="CREATEUSERID" jdbcType="VARCHAR" property="createuserid"/>
        <result column="MODIFYDATE" jdbcType="TIMESTAMP" property="modifydate"/>
        <result column="MODIFYUSERID" jdbcType="VARCHAR" property="modifyuserid"/>
        <result column="CREATEUSERNAME" jdbcType="VARCHAR" property="createusername"/>
        <result column="MODIFYUSERNAME" jdbcType="VARCHAR" property="modifyusername"/>
        <result column="TRIGGERLEVEL" jdbcType="VARCHAR" property="triggerlevel"/>
        <result column="FACTORY" jdbcType="VARCHAR" property="factory"/>
        <result column="WORKSHOPORDEPARTMENT" jdbcType="VARCHAR" property="workshopordepartment"/>
        <result column="WORKSHOPSECTION" jdbcType="VARCHAR" property="workshopsection"/>
        <result column="FAILUREFREQUENCY" jdbcType="VARCHAR" property="failurefrequency"/>
        <result column="FAILUREDATE" jdbcType="TIMESTAMP" property="failuredate"/>
        <result column="TEAMMEMBERS" jdbcType="VARCHAR" property="teammembers"/>
        <result column="PSINITIATOR" jdbcType="VARCHAR" property="psinitiator"/>
        <result column="QUESTIONTITLE" jdbcType="VARCHAR" property="questiontitle"/>
        <result column="INTERIMMEASURES" jdbcType="VARCHAR" property="interimmeasures"/>
        <result column="STANDARDORTARGETCONDITIONORLIFEEXPECTANCY" jdbcType="VARCHAR"
                property="standardortargetconditionorlifeexpectancy"/>
        <result column="FAULTTYPE" jdbcType="VARCHAR" property="faulttype"/>
        <result column="IMPACTCATEGORY" jdbcType="VARCHAR" property="impactcategory"/>
        <result column="LOSTTIME" jdbcType="TIMESTAMP" property="losttime"/>
        <result column="PROBLEMANALYSISTOOLS" jdbcType="VARCHAR" property="problemanalysistools"/>
        <result column="ISSUESTATUS" jdbcType="VARCHAR" property="issuestatus"/>
        <result column="QUESTIONNO" jdbcType="VARCHAR" property="questionno"/>
        <result column="ASSETOREQUIPMENTNO" jdbcType="VARCHAR" property="assetorequipmentno"/>
        <result column="GUARANTOR" jdbcType="VARCHAR" property="guarantor"/>
        <result column="WORKORDER" jdbcType="VARCHAR" property="workorder"/>
        <result column="PROBLEMDESCRIPTION" jdbcType="VARCHAR" property="problemdescription"/>
        <result column="AMOUNTOFDAMAGES" jdbcType="DECIMAL" property="amountofdamages"/>
        <result column="FOLLOWUPDAYS" jdbcType="VARCHAR" property="followupdays"/>
        <result column="ATTACHMENTS" jdbcType="VARCHAR" property="attachments"/>
        <result column="SPA" jdbcType="VARCHAR" property="spa"/>
        <result column="LEVLE" jdbcType="VARCHAR" property="levle"/>
        <result column="STOPMINUTES" jdbcType="INTEGER" property="stopminutes"/>
        <result column="DISQUALIFICATION" jdbcType="DECIMAL" property="disqualification"/>
        <result column="DEPPSRESPERSON" jdbcType="VARCHAR" property="deppsresperson"/>
        <result column="LEADER" jdbcType="VARCHAR" property="leader"/>
        <result column="PROCESSINSTID" jdbcType="VARCHAR" property="processinstid"/>
        <result column="PROCESSINSTSTATUS" jdbcType="VARCHAR" property="processinststatus"/>
        <result column="SPAUSERID" jdbcType="VARCHAR" property="spauserid"/>
        <result column="ISCLOSEWF" jdbcType="VARCHAR" property="isclosewf"/>
        <result column="CLOSECAUSE" jdbcType="VARCHAR" property="closecause"/>
        <result column="PSSPAUSERIDS" jdbcType="VARCHAR" property="psspauserids"/>
        <result column="CAUSEANALYSISIMG" jdbcType="VARCHAR" property="causeanalysisimg"/>
        <result column="UNDETECTEDANALYSISIMG" jdbcType="VARCHAR" property="undetectedanalysisimg"/>
    </resultMap>
    <sql id="Base_Column_List">
    ID, TENANTID, ROPTION, DELETED, REMARK, CREATEDATE, CREATEUSERID, MODIFYDATE, MODIFYUSERID, 
    CREATEUSERNAME, MODIFYUSERNAME, TRIGGERLEVEL, FACTORY, WORKSHOPORDEPARTMENT, WORKSHOPSECTION, 
    FAILUREFREQUENCY, FAILUREDATE, TEAMMEMBERS, PSINITIATOR, QUESTIONTITLE, INTERIMMEASURES, 
    STANDARDORTARGETCONDITIONORLIFEEXPECTANCY, FAULTTYPE, IMPACTCATEGORY, LOSTTIME, PROBLEMANALYSISTOOLS, 
    ISSUESTATUS, QUESTIONNO, ASSETOREQUIPMENTNO, GUARANTOR, WORKORDER, PROBLEMDESCRIPTION, 
    AMOUNTOFDAMAGES, FOLLOWUPDAYS, ATTACHMENTS, SPA, LEVLE, STOPMINUTES, DISQUALIFICATION, 
    DEPPSRESPERSON, LEADER, PROCESSINSTID, PROCESSINSTSTATUS, SPAUSERID, ISCLOSEWF, CLOSECAUSE, 
    PSSPAUSERIDS, CAUSEANALYSISIMG, UNDETECTEDANALYSISIMG
  </sql>
    <insert id="insertFile">
        INSERT INTO `sys_file_item` (
	`ID`,
	`FILENAME`,
	`FILESIZE`,
	`FILETYPE`,
	`STORAGETYPE`,
	`FILEFOLDERID`,
	`RELATIONID`,
	`PATH`,
	`FULLPATH`,
	`URLPATH`,
	`MD5`,
	`EXTENDS1`,
	`EXTENDS2`,
	`EXTENDS3`,
	`TENANTID`,
	`ROPTION`,
	`DELETED`,
	`REMARK`,
	`CREATEDATE`,
	`CREATEUSERID`,
	`MODIFYDATE`,
	`MODIFYUSERID`,
	`CREATEUSERNAME`,
	`MODIFYUSERNAME`
)
VALUES
	(
		#{model.id},
		#{model.filename},
		#{model.filesize},
		#{model.filetype},
		#{model.storagetype},
		#{model.filefolderid},
		#{model.relationid},
		NULL,
		#{model.fullpath},
		#{model.urlpath},
		NULL,
		#{model.extends1},
		NULL,
		NULL,
		NULL,
		NULL,
		#{model.deleted},
		NULL,
		#{model.createdate},
		#{model.createuserid},
		#{model.modifydate},
		#{model.modifyuserid},
		#{model.createusername},
	    #{model.modifyusername}
	)
    </insert>
    <update id="updateNoticeByProduct">
        UPDATE sys_notify_announce set NOTIFYTYPE = 'issuetracking'
        <where>
            DELETED = 0
            AND ID in
            <foreach collection="idList" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </update>

    <select id="getProblemByTaskRun" resultType="com.rongwei.bsentity.domin.SrmIssuesListDo">
        SELECT
            DISTINCT sil.ID,sil.TASKSPASUBMISSIONTIME,sil.FOLLOWUPDAYS
        FROM
            srm_issues_list sil LEFT JOIN wf_run_task wft ON sil.PROCESSINSTID = wft.PROCESSINSTID
        WHERE  wft.TASKNAME = 'SPA跟踪' and wft.TASKSTATUS = "20"
    </select>

    <select id="getMaxRealEdDate" resultType="com.rongwei.bsentity.vo.SrmActionPlanVo">
        SELECT
            il.ID , MAX(ap.COMDATE) as REALEDDATE , il.WORKSHOPORDEPARTMENT AS FULLNAMEID , so.FULLNAME
        FROM
            srm_action_plan ap
            LEFT JOIN srm_issues_list il ON ap.MAINID = il.ID
            LEFT JOIN sys_organization so ON il.WORKSHOPORDEPARTMENT = so.ID
        <where>
            ap.DELETED = '0'
            AND il.DELETED = '0'
            AND ap.ISSTATUS = '20'
            AND ap.COMDATE 	&lt;&gt; NOW()
            <if test="issuesReportRequest.type != null  and issuesReportRequest.type == 'PS'">
                AND TRIGGERLEVEL = 'PS'
            </if>
            <if test="issuesReportRequest.type != null and issuesReportRequest.type == 'RCA'">
                AND TRIGGERLEVEL = 'RCA'
            </if>
            <if test= 'issuesReportRequest.cOrj != null and issuesReportRequest.cOrj != ""  and issuesReportRequest.cOrj == "c"'>
                AND WORKSHOPORDEPARTMENT = #{issuesReportRequest.orgId}
            </if>
            <if test='issuesReportRequest.cOrj != null  and issuesReportRequest.cOrj != "" and issuesReportRequest.cOrj == "j"'>
                AND WORKSHOPSECTION = #{issuesReportRequest.orgId}
            </if>
            <if test="issuesReportRequest.staDate != null and issuesReportRequest.staDate != ''">
                AND il.CREATEDATE >= concat(#{issuesReportRequest.staDate},' 00:00:00')
            </if>
            <if test="issuesReportRequest.endDate != null and issuesReportRequest.endDate != ''">
                AND il.CREATEDATE &lt; concat(#{issuesReportRequest.endDate}, ' 00:00:00')
            </if>
        </where>
        GROUP BY il.ID
    </select>
    <select id="getMaxTargetCompletionDate" resultType="com.rongwei.bsentity.vo.SrmActionPlanVo">
        SELECT
            il.ID , MAX(ap.TARGETCOMPLETIONDATE) AS TARGETCOMPLETIONDATE , il.WORKSHOPORDEPARTMENT AS FULLNAMEID ,so.FULLNAME
        FROM
            srm_action_plan ap
            LEFT JOIN srm_issues_list il ON ap.MAINID = il.ID
            LEFT JOIN sys_organization so ON il.WORKSHOPORDEPARTMENT = so.ID
        <where>
            il.DELETED = '0'
            AND ap.DELETED = '0'
            AND ap.ISDEF = '1'
            AND ap.ISSTATUS = '20'
            <if test="issuesReportRequest.type != null  and issuesReportRequest.type == 'PS'">
                AND TRIGGERLEVEL = 'PS'
            </if>
            <if test="issuesReportRequest.type != null and issuesReportRequest.type == 'RCA'">
                AND TRIGGERLEVEL = 'RCA'
            </if>
            <if test='issuesReportRequest.cOrj != null  and issuesReportRequest.cOrj != ""  and issuesReportRequest.cOrj == "c"'>
                AND WORKSHOPORDEPARTMENT = #{issuesReportRequest.orgId}
            </if>
            <if test='issuesReportRequest.cOrj != null  and issuesReportRequest.cOrj != ""  and issuesReportRequest.cOrj == "j"'>
                AND WORKSHOPSECTION = #{issuesReportRequest.orgId}
            </if>
            <if test="issuesReportRequest.staDate != null and issuesReportRequest.staDate != ''">
                AND il.CREATEDATE >= concat(#{issuesReportRequest.staDate},' 00:00:00')
            </if>
            <if test="issuesReportRequest.endDate != null and issuesReportRequest.endDate != ''">
                AND il.CREATEDATE &lt; concat(#{issuesReportRequest.endDate}, ' 00:00:00')
            </if>
        </where>
        GROUP BY il.ID
    </select>
    <select id="getFormCount" resultType="com.rongwei.bsentity.vo.SrmFormCountVo">
        SELECT
            COUNT(DISTINCT il.ID) as COUNT, il.WORKSHOPORDEPARTMENT as orgId , so.FULLNAME
        FROM
            srm_issues_list il
            LEFT JOIN sys_organization so ON il.WORKSHOPORDEPARTMENT = so.ID
        <where>
            il.DELETED = '0'
            <if test="issuesReportRequest.type != null  and issuesReportRequest.type == 'PS'">
                AND TRIGGERLEVEL = 'PS'
            </if>
            <if test="issuesReportRequest.type != null and issuesReportRequest.type == 'RCA'">
                AND TRIGGERLEVEL = 'RCA'
            </if>
            <if test='issuesReportRequest.cOrj != null and issuesReportRequest.cOrj != "" and issuesReportRequest.cOrj == "c"'>
                AND WORKSHOPORDEPARTMENT = #{issuesReportRequest.orgId}
            </if>
            <if test='issuesReportRequest.cOrj != null and issuesReportRequest.cOrj != "" and issuesReportRequest.cOrj == "j"'>
                AND WORKSHOPSECTION = #{issuesReportRequest.orgId}
            </if>
            <if test="issuesReportRequest.staDate != null and issuesReportRequest.staDate != ''">
                AND il.CREATEDATE >= concat(#{issuesReportRequest.staDate},' 00:00:00')
            </if>
            <if test="issuesReportRequest.endDate != null and issuesReportRequest.endDate != ''">
                AND il.CREATEDATE &lt; concat(#{issuesReportRequest.endDate}, ' 00:00:00')
            </if>
        </where>
        GROUP BY il.WORKSHOPORDEPARTMENT
    </select>
    <select id="getListByMesId" resultType="com.rongwei.bsentity.domin.SrmIssuesListDo">
        select * from srm_issues_list where MESID IN
        <foreach collection="mesIds" item="mesId" index="0" open="(" close=")" separator=",">
            #{mesId}
        </foreach>
    </select>
</mapper>
