<?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.ekp.mapper.QwExcelApiMapper">

	<select id="selectInvoiceList" resultType="java.util.Map">
		SELECT
		a.fd_3afa7c19036b66 AS "发票号",
		a.fd_3b05ae02ff41ae AS "合同号",
		a.fd_3b05ade863b120 AS "开票金额",
		date_format(a.fd_3afa7c1b153ff8, '%Y-%m-%d') AS "开票时间",
		date_format(a.fd_3afa7c1b153ff8, '%Y-%m') AS "开票月份",
		a.fd_3afac373e38ec4 AS "购买方名称",
		a.fd_3b0f037ecc0ed0 AS "发票状态",
		a.fd_3b1bb0ccc14760 AS "红冲状态",
		concat(
		a.fd_3afa7c19036b66,
		'&amp;',
		ifnull(a.fd_3afac373e38ec4, ''),
		'&amp;',
		ifnull(date_format(a.fd_3afa7c1b153ff8, '%Y-%m'), '')
		) AS "发票号&amp;购买方名称&amp;开票月份组合",
		date_format(b.doc_create_time, '%Y-%m-%d') AS "创建时间"
		FROM ekp_bacdf3a32d242279ceb6 a
		LEFT JOIN modeling_simple_main b ON a.fd_id = b.fd_id
		<where>
			<!-- 基础时间条件
			b.doc_create_time > (curdate() - INTERVAL 15 MONTH)
			AND b.doc_create_time < (curdate() - INTERVAL 12 MONTH)
			-->
			<!-- 动态条件 -->
		   1 = 1
			<if test="startDate != null">
				AND b.doc_create_time <![CDATA[ >= ]]> #{startDate}
			</if>
			<if test="endDate != null">
				AND b.doc_create_time  <![CDATA[ <= ]]> #{endDate}
			</if>
		</where>
		ORDER BY a.fd_3afa7c1b153ff8 DESC
	</select>
	<!-- 基础查询：返回合同信息列表 -->
	<select id="selectContractList" resultType="java.util.Map">
		SELECT
		a.fd_3ade33763baa0e AS "合同号",
		a.fd_3ade334bac0016 AS "合同名称",
		a.fd_3ade3353d31f6c AS "客户名称",
		a.fd_3aeae8ad0c9e62_text AS "客户编码",
		a.fd_division AS "事业部",
		a.fd_new_line AS "事业部条线",
		b.fd_name AS "业务收入归属部门",
		a.fd_3b107c2e2546a6 AS "合同状态"
		FROM
		ekp_0103c453b2848c3bb31c a
		LEFT JOIN sys_org_element b ON a.fd_3b1141597b4894 = b.fd_id
		<where>  1=1 ORDER BY a.fd_3ade33763baa0e DESC
			<if test="limitStart != null and limitEnd != null">
				limit #{limitStart},#{limitEnd}
			</if>
		</where>

	</select>
	<select id="selectContractListCount" resultType="java.lang.Integer">
		SELECT
		count(1)
		FROM
		ekp_0103c453b2848c3bb31c a
	</select>
</mapper>
