style24.doc/산출물/3.설계/03.인터페이스/네이버/네이버_최저가/java/NaverPriceInfo.java

package com.exrkorea.batch.biz.affiliate.naver.search;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.json.JSONArray;
import org.json.JSONObject;

import com.exrkorea.batch.framework.base.DataBatch;
import com.exrkorea.batch.framework.var.Var;
import com.nogdooframework.web.parameter.NGMap;

public class NaverPriceInfo extends DataBatch	{

	private static final String requestUrl = "https://openapi.naver.com/v1/search/shop.json"; 	//request value 를 제외한 요청 URL
	private static final String clientId = "p9BcE9JoeZEUNuYnpqUC";		//NAVER CLIENT ID
	private static final String clientSecret = "ALsYw0LUvo";	//NAVER SECRET

	//private static List<NGMap> lowestPriceProdList = new ArrayList<NGMap>(); // NAVER SEARCH API로 가져온 데이터리스트

	private static int successCnt = 0;			// 성공
	private static int failCnt = 0;				// 성공했지만 가져올 상품이 존재하지 않는 경우
	private static int errorCnt = 0;			// ERROR인 경우 

	private static int prodCnt = 0;				//api key 변경 가격 비교 상품수


	long startTime = System.currentTimeMillis(); // LOG 출력을 위한 시작시간

	/**
	 * 상품정보 (재고여부, 전시여부)
	 * @param conn
	 * @return List<NGMap>
	 * @throws Exception
	 */
	private List<NGMap> getProductInfo(Connection conn) throws Exception {

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;

		//System.out.println("getProductInfo======>");

		//List<NGMap> prodList = new LinkedList<NGMap>();
		List<NGMap> prodList = new ArrayList<NGMap>();

		try {
			int dataCnt = 0;

			sql = super.getSqlStatement("/biz/affiliate/" + Var.NAVER + "/search/getProductInfo.sql");
			log.debug("== SQL statement ===============================================================");
			log.debug("\n{}", sql);
			log.debug("================================================================================");

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();


			while (rs.next()) {
				if (dataCnt == 0)
					log.info("================================DB 조회 시작================================");

				NGMap resultMap = new NGMap();

				//resultMap.setString("PROD_CD", rs.getString("STYLECD")); // 상품코드 (ERP 스타일 코드)

				resultMap.setString("TEXT", rs.getString("TEXT")); // 검색어
				resultMap.setString("PROD_CD", rs.getString("PROD_CD")); // 상품코드

				//resultMap.setString("LIST_PRICE", rs.getString("LIST_PRICE")); // 최초소비자가
				//resultMap.setString("PROD_PRICE", rs.getString("PROD_PRICE")); // 현재판매가

				//System.out.println("resultMap==========>"+resultMap);

				prodList.add(resultMap);

				dataCnt++;
			}

			log.info("================================DB 조회 완료================================");

		} catch (Exception e) {
			//e.printStackTrace();
			throw e;
		} finally {
			if(pstmt != null) pstmt.close();
			if(rs != null) rs.close();
		}

		return prodList;
	}


	private List<NGMap> getPriceInfo(List<NGMap> prodList, boolean loopCnt) throws InterruptedException {
		//private void getPriceInfo(List<NGMap> prodList, boolean loopCnt) {

		List<NGMap> reRequestValueList = new ArrayList<NGMap>();	// 에러로 누락되어 다시 검색할 데이터 
		List<NGMap> lowestPriceProdList = new ArrayList<NGMap>();

		String prodCd = "";
		String text = "";
		String comRequestUrl = ""; //LOG 보여주기 위한 수정된 URL(request value 포함 전체 URL)

		for (NGMap prodMap : prodList) {
			TimeUnit.SECONDS.sleep(1);
			text = prodMap.getString("TEXT");
			prodCd = prodMap.getString("PROD_CD");
			log.info("text : " + text + "prodCd : " + prodCd);
			prodCnt++;


			try {
				text = URLEncoder.encode(text, "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				e1.printStackTrace();
			}

			String display = "5";
			String sort = "asc";
			String apiURL = "";
			// URL 보내는 형태로 replace
			comRequestUrl = text + "&display="+ display + "&sort=" + sort;
			apiURL = requestUrl +"?query="+ comRequestUrl;
			try {

				//System.out.println("clientId====>"+clientId);
				//System.out.println("clientSecret====>"+clientSecret);

				URL url = new URL(apiURL);
				HttpURLConnection con = (HttpURLConnection)url.openConnection();
				con.setRequestMethod("GET");
				con.setRequestProperty("X-Naver-Client-Id", clientId);
				con.setRequestProperty("X-Naver-Client-Secret", clientSecret);
				int responseCode = con.getResponseCode();

				BufferedReader br;
				if(responseCode==200) { // 정상 호출
					//br = new BufferedReader(new InputStreamReader(con.getInputStream()));
					br = new BufferedReader(new InputStreamReader(con.getInputStream(), "UTF-8"));                    
				} else {  // 에러 발생
					//br = new BufferedReader(new InputStreamReader(con.getErrorStream()));
					br = new BufferedReader(new InputStreamReader(con.getErrorStream(), "UTF-8"));
				}
				String inputLine;
				StringBuffer response = new StringBuffer();

				while ((inputLine = br.readLine()) != null) {
					log.info(inputLine);
					response.append(inputLine);

				}

				br.close();

				//JSONObject obj = new JSONObject(response.toString().getBytes("UTF-8"));
				JSONObject obj = new JSONObject(response.toString());
				log.info("OBJ : {}", obj);
				int total = obj.getInt("total");

				//System.out.println("total====>"+total);

				JSONArray arr = obj.getJSONArray("items");

				for (int i = 0; i < arr.length(); i++)
				{

					NGMap resultMap2 = new NGMap();

					if(total > 0){ //정상처리

						String lprice = arr.getJSONObject(i).getString("lprice");
						String mallName = arr.getJSONObject(i).getString("mallName");

						//mallName = URLEncoder.encode(mallName , "UTF-8");
						//mallName = URLDecoder.decode(mallName , "UTF-8");
						//URLDecoder.decode(name, "UTF-8");


						if (Integer.parseInt(lprice) > 0 && !mallName.equals("네이버")) {

							resultMap2.setString("PROD_CD", prodCd);
							resultMap2.setString("LOWEST_PRICE", lprice);
							resultMap2.setString("MALL_NM", mallName);

							lowestPriceProdList.add(resultMap2);

							successCnt++;

						}

					}else if(total < 0 ){ // ERROR페이지 처리

						errorCnt++;

						// ERROR LOG 출력
						log.info("=================================ERROR========================================");
						log.info("\n PROD_CD : "+prodCd);
						log.info("\n REQUEST_URL : "+ apiURL);
						//log.info("\n ERROR_CODE : "+ rtnMap.getString("ERROR_CD"));
						//log.info("\n ERROR_MESSAGE : "+ rtnMap.getString("ERROR_MSG"));
						log.info("==============================================================================");

					}else if(total == 0){ //TOTAL 0 인 상품 처리

						failCnt++;

						// ERROR LOG 출력
						log.info("============================PRODUCT DOSE NOT EXIST============================");
						log.info("\n PROD_CD : "+prodCd);
						log.info("\n REQUEST_URL : "+ apiURL);
						log.info("\n FAIL_MESSAGE : "+ "상품이 존재하지 않습니다.");
						log.info("==============================================================================");

					}else{
						log.info("=================================ERROR========================================");
						log.info("\n PROD_CD : "+prodCd);
						log.info("\n REQUEST_URL : "+ apiURL);
						//log.info("\n ERROR_CODE : "+ rtnMap.getString("ERROR_CD"));
						//log.info("\n ERROR_MESSAGE : "+ rtnMap.getString("ERROR_MSG"));
						log.info("==============================================================================");
					}

				}
				//for - end 

			} catch (Exception e) {
				// ERROR LOG 출력
				log.info("=================================TIMEOUT_ERROR========================================");
				log.info("\n PROD_CD : "+prodCd);
				log.info("\n REQUEST_URL : "+ apiURL);
				log.info("\n ERROR_CODE : "+ "998"); //임의 코드 (알 수 없는 에러)
				log.info("\n ERROR_MESSAGE : "+ e);
				log.info("==============================================================================");
				//System.out.println(e);

				reRequestValueList.add(prodMap); //알수 없는 에러가 생길경우 에러난 값을 따로 담음
				e.printStackTrace();

			}

		}

		log.info("========================================JSON 읽어오기 완료!!!");
		log.info("Success :" + successCnt + " Fail : "+ failCnt + " , Error : "+ errorCnt +" \n");

		// 누락된 데이터 저장을 위한 재귀호출의 무한루프를 방지하기 위한 값 : loopCnt
		if(loopCnt){
			return lowestPriceProdList;
		}

		// 알수 없는 에러로 누락된 데이터가 있을경우
		if(reRequestValueList.size() != 0){ 

			log.info("========================================TIMEOUT_ERROR 로 누락된 데이터 다시읽기");
			getPriceInfo(reRequestValueList, true);

		}

		return lowestPriceProdList;
	}


	/**
	 * 네이버에서 가져온 최저가 정보를 DB에 Batch 작업
	 * 
	 * @param conn - Connection
	 * @param lowestPriceProdList - 네이버에서 가져온 최저가 정보 List<LSMap>
	 * @return int[]
	 * @throws Exception
	 */
	private void insertPriceInfo(Connection conn,List<NGMap> lowestPriceProdList) throws Exception {
		PreparedStatement pstmt = null;
		NGMap lowestPriceProd = new NGMap();

		try {

			String sql = super.getSqlStatement("/biz/affiliate/" + Var.NAVER + "/search/insertLowestPrice.sql");
			pstmt = conn.prepareStatement(sql);

			log.debug("== SQL statement ===============================================================");
			log.debug("\n" + sql);
			log.debug("==============================================================================");

			int batchSize = 1000;

			for (int j = 0; j<lowestPriceProdList.size(); j++) {

				lowestPriceProd = lowestPriceProdList.get(j);

				String prod_cd = lowestPriceProd.getString("PROD_CD");
				String mall_nm = lowestPriceProd.getString("MALL_NM");
				int lowest_price = lowestPriceProd.getInt("LOWEST_PRICE");

				pstmt.clearParameters();
				pstmt.setString(1, prod_cd);
				pstmt.setString(2, prod_cd);
				pstmt.setString(3, mall_nm);
				pstmt.setInt(4, lowest_price);

				pstmt.addBatch();

				// OutOfMemory를 고려하여 batchSize 단위로 커밋
				if( (j % batchSize) == 0){

					// Batch 실행
					pstmt.executeBatch() ;

					// Batch 초기화
					pstmt.clearBatch();

					// 커밋
					//conn.commit() ;

				}

			}

			if(lowestPriceProdList.size() % batchSize != 0){
				pstmt.executeBatch();
			}

		} catch (SQLException sqle) {
			log.info("error:\n" + lowestPriceProdList);

			throw new Exception(sqle.getMessage(), sqle);
		} catch(Exception e) {
			throw e;
		} finally {
			if (pstmt != null) pstmt.close();
		}

	}


	@Override
	public void execute(Connection conn) throws Exception {
		//상품리스트
		List<NGMap> prodList = getProductInfo(conn);

		// 최저가 정보 리스트
		List<NGMap> lowestPriceProdList = getPriceInfo(prodList, false);
		//getPriceInfo(prodList, false);

		//System.out.println("lowestPriceProdList===>"+lowestPriceProdList);
		//System.out.println("lowestPriceProdList.size===>"+lowestPriceProdList.size());

		insertPriceInfo(conn, lowestPriceProdList);

	}

	@Override
	public void init() throws Exception {
		//clientId = props.getProperty("NAVER_API_CLIENT_ID");
		//clientSecret = props.getProperty("NAVER_API_CLIENT_SECRET");
		//requestUrl = props.getProperty("NAVER_PRICE_INFO_URL");
		//System.out.println("init======>");

	}


	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {

		NaverPriceInfo batch = new NaverPriceInfo();
		System.out.println("네이버 - 최저가 연동 시작");
		batch.run("네이버 - 최저가 연동");
	}

}