<?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="WebStockCloseDetail">
	
	<select id="selectTotalStockCloseDetailCount" parameterType="hashmap" resultType="hashmap">
		SELECT COUNT(*) AS "totalCount"
		FROM (
			SELECT MSCD.MU_TREATMENT_ID
				  ,MSCD.TREATMENT_NAME
				  ,MSCD.MU_COMPANY_ID
				  ,MSCD.COMPANY_NAME
				  ,MSCD.MU_PRODUCT_ID
				  ,MSCD.PRODUCT_CODE
				  ,MSCD.PRODUCT_NAME
			  FROM MU_STOCK_CLOSE_DETAIL AS MSCD
			 WHERE MSCD.USE_YN = 'Y'
			   AND MSCD.MU_STOCK_CLOSE_ID = #{muStockCloseId}
			   <if test="stockCloseDetailTreatmentId != null and stockCloseDetailTreatmentId != ''">
				   AND MSCD.MU_TREATMENT_ID = (
						SELECT MU_CATEGORY_ITEM_ID
						FROM MU_CATEGORY_ITEM mci
						JOIN MU_CATEGORY mc
						ON mci.mu_category_id = mc.mu_category_id
						WHERE mc.CATEGORY_NAME = '재고구분'
						AND mci.CATEGORY_ITEM_CODE = #{stockCloseDetailTreatmentId}
					)
			   </if>
			   <if test="stockCloseDetailCompanyId != null and stockCloseDetailCompanyId != ''">
				   AND MSCD.MU_COMPANY_ID = #{stockCloseDetailCompanyId}
			   </if>
			   <if test="stockCloseDetailSearchKeywordParam != null and stockCloseDetailSearchKeywordParam != ''">
				   AND (
					   MSCD.COMPANY_NAME LIKE CONCAT('%', TRIM(#{stockCloseDetailSearchKeywordParam}), '%')
					   OR MSCD.PRODUCT_NAME LIKE CONCAT('%', TRIM(#{stockCloseDetailSearchKeywordParam}), '%')
				   )
			   </if>
		  GROUP BY MSCD.MU_TREATMENT_ID
				  ,MSCD.TREATMENT_NAME
				  ,MSCD.MU_COMPANY_ID
				  ,MSCD.COMPANY_NAME
				  ,MSCD.MU_PRODUCT_ID
				  ,MSCD.PRODUCT_CODE
				  ,MSCD.PRODUCT_NAME
		   ) AS MSCD
	</select>
	
	<select id="selectListStockCloseDetail" parameterType="hashmap" resultType="hashmap">
		SELECT MSS.*
			  FROM (
						SELECT MSS.*
							  ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
						  FROM (
									SELECT 
										 MSS.MU_TREATMENT_ID			          			AS "muTreatmentId"
										  ,MSS.TREATMENT_NAME                     			AS "treatmentName"
										  ,MSS.MU_COMPANY_ID                      			AS "muCompanyId"
										  ,MSS.COMPANY_NAME                       			AS "companyName"
										  ,MSS.MU_PRODUCT_ID                      			AS "muProductId"
										  ,MSS.PRODUCT_CODE                       			AS "productCode"
										  ,MSS.PRODUCT_NAME                       			AS "productName"										  
										  ,IFNULL(FORMAT(AVG(MSCD.PRICE), 0), 0)           AS "r_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSCD.QUANTITY)) = SUM(MSCD.QUANTITY) THEN FORMAT(IFNULL(SUM(MSCD.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSCD.QUANTITY), 2) AS FLOAT)), 0)
										   END                                     			AS "r_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSCD.QUANTITY), 0)       AS "r_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSH_I.PRICE), 0), 0)   		AS "i_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSH_I.QUANTITY)) = SUM(MSH_I.QUANTITY) THEN FORMAT(IFNULL(SUM(MSH_I.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSH_I.QUANTITY), 2) AS FLOAT)), 0)
										   END                                    			AS "i_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSH_I.QUANTITY), 0)       AS "i_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSH_U.PRICE), 0), 0)           AS "u_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSH_U.QUANTITY)) = SUM(MSH_U.QUANTITY) THEN FORMAT(IFNULL(SUM(MSH_U.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSH_U.QUANTITY), 2) AS FLOAT)), 0)
										   END                                     			AS "u_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSH_U.QUANTITY), 0)       AS "u_totalAmount"
										  ,0            									AS "m_price"
										  ,0                                     			AS "m_quantity"
										  ,0       											AS "m_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSS.PRICE), 0), 0)   			AS "price"
										  ,CASE
											  WHEN FLOOR(SUM(MSS.QUANTITY)) = SUM(MSS.QUANTITY) THEN FORMAT(IFNULL(SUM(MSS.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSS.QUANTITY), 2) AS FLOAT)), 0)
										   END                                    			AS "quantity"
										  ,MSS.PRICE * SUM(MSS.QUANTITY)      	  			AS "totalAmount"
										  ,MSS.ETC											AS "ETC"
									  FROM MU_STOCK_SUM AS MSS
									  LEFT OUTER JOIN MU_STOCK_HISTORY AS MSH_I ON MSS.MU_STOCK_HISTORY_ID = MSH_I.MU_STOCK_HISTORY_ID AND (MSH_I.TYPE = 'I' OR MSH_I.TYPE = 'O')
									  LEFT OUTER JOIN MU_STOCK_HISTORY AS MSH_U ON MSS.MU_STOCK_HISTORY_ID = MSH_U.MU_STOCK_HISTORY_ID AND MSH_U.TYPE = 'U'
									  LEFT OUTER JOIN MU_STOCK_CLOSE_DETAIL AS MSCD ON MSS.MU_STOCK_HISTORY_ID = MSCD.MU_STOCK_HISTORY_ID AND LEFT(MSS.IN_DATE, 7) = #{R_YEAR_MONTH}
									 WHERE MSS.USE_YN = 'Y'
									 AND LEFT(MSS.IN_DATE, 7) = #{YEAR_MONTH}
									 <if test="stockCloseDetailTreatmentId != null and stockCloseDetailTreatmentId != ''">
											AND MSS.MU_TREATMENT_ID = (SELECT MU_CATEGORY_ITEM_ID
											FROM MU_CATEGORY_ITEM mci
											JOIN MU_CATEGORY mc
											ON mci.mu_category_id = mc.mu_category_id
											WHERE mc.CATEGORY_NAME = '재고구분'
											AND mci.CATEGORY_ITEM_CODE =#{stockCloseDetailTreatmentId})
										</if>
										<if test="stockCloseDetailCompanyId != null and stockCloseDetailCompanyId != ''">
											AND MSS.MU_COMPANY_ID   = #{stockCloseDetailCompanyId}
										</if>
										<if test="stockCloseDetailSearchKeywordParam != null and stockCloseDetailSearchKeywordParam != ''">
											AND ((MSS.COMPANY_NAME LIKE CONCAT('%',TRIM(#{stockCloseDetailSearchKeywordParam}),'%'))
											OR (MSS.PRODUCT_NAME LIKE CONCAT('%',TRIM(#{stockCloseDetailSearchKeywordParam}),'%')))
										</if>									 
								  GROUP BY MSS.MU_TREATMENT_ID
										  ,MSS.TREATMENT_NAME
										  ,MSS.MU_COMPANY_ID
										  ,MSS.COMPANY_NAME
										  ,MSS.MU_PRODUCT_ID
										  ,MSS.PRODUCT_CODE
										  ,MSS.PRODUCT_NAME
									  
								  ORDER BY MSS.MU_TREATMENT_ID ASC
									  
									 LIMIT 18446744073709551615
							   ) MSS, (SELECT @RNUM:=0) R
						 WHERE 1 = 1
				   ) MSS
			 WHERE 1 = 1
	
-- 		 LIMIT ${stockCloseDetailStart}, ${stockCloseDetailLimit}
	</select>
	
	<select id="selectListStockCloseDetailExcel" parameterType="hashmap" resultType="hashmap">
		SELECT MSS.*
			  FROM (
						SELECT MSS.*
							  ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
						  FROM (
									SELECT 
										 MSS.MU_TREATMENT_ID			          			AS "muTreatmentId"
										  ,MSS.TREATMENT_NAME                     			AS "treatmentName"
										  ,MSS.MU_COMPANY_ID                      			AS "muCompanyId"
										  ,MSS.COMPANY_NAME                       			AS "companyName"
										  ,MSS.MU_PRODUCT_ID                      			AS "muProductId"
										  ,MSS.PRODUCT_CODE                       			AS "productCode"
										  ,MSS.PRODUCT_NAME                       			AS "productName"										  
										  ,IFNULL(FORMAT(AVG(MSCD.PRICE), 0), 0)           AS "r_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSCD.QUANTITY)) = SUM(MSCD.QUANTITY) THEN FORMAT(IFNULL(SUM(MSCD.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSCD.QUANTITY), 2) AS FLOAT)), 0)
										   END                                     			AS "r_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSCD.QUANTITY), 0)       AS "r_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSH_I.PRICE), 0), 0)   		AS "i_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSH_I.QUANTITY)) = SUM(MSH_I.QUANTITY) THEN FORMAT(IFNULL(SUM(MSH_I.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSH_I.QUANTITY), 2) AS FLOAT)), 0)
										   END                                    			AS "i_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSH_I.QUANTITY), 0)       AS "i_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSH_U.PRICE), 0), 0)           AS "u_price"
										  ,CASE
											  WHEN FLOOR(SUM(MSH_U.QUANTITY)) = SUM(MSH_U.QUANTITY) THEN FORMAT(IFNULL(SUM(MSH_U.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSH_U.QUANTITY), 2) AS FLOAT)), 0)
										   END                                     			AS "u_quantity"
										  ,IFNULL(MSS.PRICE * SUM(MSH_U.QUANTITY), 0)       AS "u_totalAmount"
										  ,0            									AS "m_price"
										  ,0                                     			AS "m_quantity"
										  ,0       											AS "m_totalAmount"
										  ,IFNULL(FORMAT(AVG(MSS.PRICE), 0), 0)   			AS "price"
										  ,CASE
											  WHEN FLOOR(SUM(MSS.QUANTITY)) = SUM(MSS.QUANTITY) THEN FORMAT(IFNULL(SUM(MSS.QUANTITY),0),0)
											  ELSE IFNULL(TRIM(TRAILING '0' FROM CAST(FORMAT(SUM(MSS.QUANTITY), 2) AS FLOAT)), 0)
										   END                                    			AS "quantity"
										  ,MSS.PRICE * SUM(MSS.QUANTITY)      	  			AS "totalAmount"
										  ,MSS.ETC											AS "ETC"
									  FROM MU_STOCK_SUM AS MSS
									  LEFT OUTER JOIN MU_STOCK_HISTORY AS MSH_I ON MSS.MU_STOCK_HISTORY_ID = MSH_I.MU_STOCK_HISTORY_ID AND (MSH_I.TYPE = 'I' OR MSH_I.TYPE = 'O')
									  LEFT OUTER JOIN MU_STOCK_HISTORY AS MSH_U ON MSS.MU_STOCK_HISTORY_ID = MSH_U.MU_STOCK_HISTORY_ID AND MSH_U.TYPE = 'U'
									  LEFT OUTER JOIN MU_STOCK_CLOSE_DETAIL AS MSCD ON MSS.MU_STOCK_HISTORY_ID = MSCD.MU_STOCK_HISTORY_ID AND LEFT(MSS.IN_DATE, 7) = #{R_YEAR_MONTH}							  
									 WHERE MSS.USE_YN = 'Y'           
									 AND LEFT(MSS.IN_DATE, 7) = #{YEAR_MONTH}
									 <if test="stockCloseDetailTreatmentId != null and stockCloseDetailTreatmentId != ''">
											AND MSS.MU_TREATMENT_ID = (SELECT MU_CATEGORY_ITEM_ID
											FROM MU_CATEGORY_ITEM mci
											JOIN MU_CATEGORY mc
											ON mci.mu_category_id = mc.mu_category_id
											WHERE mc.CATEGORY_NAME = '재고구분'
											AND mci.CATEGORY_ITEM_CODE =#{stockCloseDetailTreatmentId})
										</if>
										<if test="stockCloseDetailCompanyId != null and stockCloseDetailCompanyId != ''">
											AND MSS.MU_COMPANY_ID   = #{stockCloseDetailCompanyId}
										</if>
										<if test="stockCloseDetailSearchKeywordParam != null and stockCloseDetailSearchKeywordParam != ''">
											AND ((MSS.COMPANY_NAME LIKE CONCAT('%',TRIM(#{stockCloseDetailSearchKeywordParam}),'%'))
											OR (MSS.PRODUCT_NAME LIKE CONCAT('%',TRIM(#{stockCloseDetailSearchKeywordParam}),'%')))
										</if>									 
								  GROUP BY MSS.MU_TREATMENT_ID
										  ,MSS.TREATMENT_NAME
										  ,MSS.MU_COMPANY_ID
										  ,MSS.COMPANY_NAME
										  ,MSS.MU_PRODUCT_ID
										  ,MSS.PRODUCT_CODE
										  ,MSS.PRODUCT_NAME
								  ORDER BY MSS.MU_TREATMENT_ID ASC
									  
									 LIMIT 18446744073709551615
							   ) MSS, (SELECT @RNUM:=0) R
						 WHERE 1 = 1
				   ) MSS
			 WHERE 1 = 1
	
	</select>
	
	<insert id="insertStockCloseDetail" parameterType="hashmap">
		<selectKey resultType="string" keyProperty="id" order="BEFORE">
			SELECT CONCAT(#{muStockCloseDetailId},LPAD((SELECT NEXTVAL(MU_STOCK_CLOSE_DETAIL_SEQ)), 9, 0))
		</selectKey>
		INSERT INTO MU_STOCK_CLOSE_DETAIL(
					 MU_STOCK_CLOSE_DETAIL_ID
					,MU_STOCK_CLOSE_ID
					,MU_TREATMENT_ID
					,TREATMENT_NAME
					,MU_PRODUCT_ID
					,PRODUCT_CODE
					,PRODUCT_NAME
					,MU_COMPANY_ID
					,COMPANY_NAME
					,MU_STOCK_HISTORY_ID
					,IN_DATE
					,PRICE
					,QUANTITY
					,TOTAL_AMOUNT
					,WRITE_DATE
					,WRITE_TIME
					,CUD_FLAG
					,USE_YN
					,REG_ID
					,REG_DATE
					,MOD_ID
					,MOD_DATE
					,T_ID
					,T_DATE
		)VALUES(
					 #{id}
					,#{muStockCloseId}
					,#{muTreatmentId}
					,#{treatmentName}
					,#{muProductId}
					,#{productCode}
					,#{productName}
					,#{muCompanyId}
					,#{companyName}
					,#{muStockHistoryId}
					,#{inDate}
					,#{price}
					,#{quantity}
					,#{totalAmount}
					,CURDATE()
					,CURTIME()
					,'C'
					,'Y'
					,#{regId}
					,NOW()
					,#{modId}
					,NOW()
					,#{tId}
					,#{tDate}
		)
	</insert>
	<select id="selectTotalStockCloseDetail" parameterType="hashmap" resultType="hashmap">
		SELECT
			COUNT(*) AS treatmentCount,
			IFNULL(
					(
						SELECT GROUP_CONCAT(CONCAT(MCI.CATEGORY_ITEM_NAME, IFNULL(SumAmounts.totalQuantity, 0)))
						FROM MU_CATEGORY_ITEM AS MCI
								 JOIN (
							SELECT MCI2.CATEGORY_ITEM_NAME,
								   MCI2.MU_CATEGORY_ITEM_ID,
								   SUM(MSCD.QUANTITY) AS totalQuantity
							FROM MU_CATEGORY_ITEM MCI2
									 left JOIN MU_STOCK_CLOSE_DETAIL MSCD
											   ON MCI2.MU_CATEGORY_ITEM_ID = MSCD.MU_TREATMENT_ID
												   AND MSCD.USE_YN = 'Y'
												   AND MSCD.MU_STOCK_CLOSE_ID = #{muStockCloseId}
									 JOIN MU_CATEGORY
										  ON MU_CATEGORY.MU_CATEGORY_ID = MCI2.MU_CATEGORY_ID
											  AND MU_CATEGORY.CATEGORY_NAME = '재고구분'
							GROUP BY MCI2.MU_CATEGORY_ITEM_ID, MSCD.MU_TREATMENT_ID
						) AS SumAmounts ON MCI.MU_CATEGORY_ITEM_ID = SumAmounts.MU_CATEGORY_ITEM_ID
						WHERE MCI.USE_YN = 'Y'
					), ''
			) AS groupTreatmentCount,
			COUNT(DISTINCT MSCD.MU_COMPANY_ID) AS companyCount,
			FORMAT(SUM(MSCD.totalAmount), 0) AS stockAmount
		FROM (
				 SELECT
					 MSCD.MU_TREATMENT_ID,
					 MSCD.MU_COMPANY_ID,
					 SUM(MSCD.QUANTITY) AS totalQuantity,
					 SUM(MSCD.TOTAL_AMOUNT) AS totalAmount
				 FROM MU_STOCK_CLOSE_DETAIL AS MSCD
				 WHERE MSCD.USE_YN = 'Y'
				   AND MSCD.MU_STOCK_CLOSE_ID = #{muStockCloseId}
				 GROUP BY MSCD.MU_TREATMENT_ID, MSCD.MU_COMPANY_ID,MSCD.MU_PRODUCT_ID
			 ) AS MSCD;
	</select>
	
	<select id="selectDate" parameterType="hashmap" resultType="hashmap">
		SELECT LEFT(MSC.END_DATE, 4) AS YEAR, SUBSTRING(MSC.END_DATE, 6, 2) AS MONTH 
			FROM MU_STOCK_CLOSE AS MSC
			WHERE  MSC.MU_STOCK_CLOSE_ID = #{muStockCloseId}
	</select>
</mapper>