586 lines
18 KiB
Java
586 lines
18 KiB
Java
|
|
import java.io.BufferedReader;
|
|
import java.io.IOException;
|
|
import java.io.InputStreamReader;
|
|
import java.io.OutputStream;
|
|
import java.lang.management.ManagementFactory;
|
|
import java.net.HttpURLConnection;
|
|
import java.net.URL;
|
|
import java.sql.Connection;
|
|
import java.sql.DriverManager;
|
|
import java.sql.PreparedStatement;
|
|
import java.sql.ResultSet;
|
|
import java.sql.SQLException;
|
|
import java.text.SimpleDateFormat;
|
|
import java.util.Date;
|
|
import java.util.HashMap;
|
|
import java.util.Iterator;
|
|
import java.util.Map;
|
|
|
|
import com.google.gson.Gson;
|
|
import com.google.gson.GsonBuilder;
|
|
import com.google.gson.JsonObject;
|
|
|
|
public class CallHpordRunOutOfStock {
|
|
/* 개발
|
|
private static String jdbcUrl = "jdbc:oracle:thin:@10.0.50.147:1521:spcdev01";
|
|
private static String jdbcId = "spp";//jdbc id
|
|
private static String jdbcPw = "spp";//jdbc pw
|
|
*/
|
|
|
|
/* 운영 */
|
|
private static String jdbcUrl = "jdbc:oracle:thin:@10.0.50.152:1521/PCPOS";
|
|
private static String jdbcId = "SPP";//jdbc id
|
|
private static String jdbcPw = "Spc_$pp!23";//jdbc pw
|
|
|
|
|
|
private static Connection dbConn = null;
|
|
private static String chk = "";
|
|
private static SimpleDateFormat formatter;
|
|
|
|
public static void main(String[] args) {
|
|
|
|
try {
|
|
SimpleDateFormat formatTime = new SimpleDateFormat("HHmmss");
|
|
chk = formatTime.format(new Date());
|
|
|
|
|
|
formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] ==== START ====" );
|
|
|
|
startCallApi();
|
|
|
|
} catch (SQLException se) {
|
|
se.printStackTrace();
|
|
|
|
} finally{
|
|
|
|
if(dbConn != null){
|
|
try{
|
|
dbConn.close();
|
|
}catch(SQLException e){
|
|
|
|
}
|
|
}
|
|
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] ==== END ====" );
|
|
}
|
|
}
|
|
|
|
public static void startCallApi() throws SQLException{
|
|
|
|
String strOSName = System.getProperty("os.name");
|
|
String strPID = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];
|
|
String strClassName = "CallHpordRunOutOfStock";
|
|
|
|
Boolean isRunnable = false;
|
|
|
|
try {
|
|
Process p = Runtime.getRuntime().exec( new String[]{
|
|
"/bin/sh"
|
|
, "-c"
|
|
, "ps -ef | grep [" + strClassName.substring(0, 1) + "]" + strClassName.substring(1, strClassName.length()) + " | awk '{ print $2 }'"
|
|
} );
|
|
|
|
BufferedReader in = new BufferedReader(new InputStreamReader( p.getInputStream() ) );
|
|
|
|
String s = null;
|
|
while( ( s = in.readLine() ) != null ){
|
|
|
|
if( strPID.equals(s) ){
|
|
continue;
|
|
}
|
|
isRunnable = true;
|
|
|
|
break;
|
|
}
|
|
|
|
if( in != null ) in.close();
|
|
|
|
} catch( IOException e ){
|
|
e.printStackTrace();
|
|
isRunnable = true;
|
|
}
|
|
|
|
|
|
if( !isRunnable ){
|
|
selectData();
|
|
} else {
|
|
/*isRunnable = searchAction();
|
|
|
|
if(isRunnable){*/
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] " + strClassName + " Daemon이 이미 실행중 입니다" );
|
|
/*} else {
|
|
selectData();
|
|
}*/
|
|
}
|
|
}
|
|
|
|
public static void selectData() throws SQLException{
|
|
|
|
try {
|
|
Class.forName("oracle.jdbc.driver.OracleDriver");
|
|
dbConn = DriverManager.getConnection(jdbcUrl, jdbcId, jdbcPw);
|
|
dbConn.setAutoCommit(false);
|
|
|
|
} catch (ClassNotFoundException ce) {
|
|
ce.printStackTrace();
|
|
}
|
|
|
|
updateAction("1");
|
|
|
|
PreparedStatement pstmt = null;
|
|
ResultSet rs = null;
|
|
String sql = "";
|
|
|
|
HashMap mp = null;
|
|
String storCd = "";
|
|
String itemCd = "";
|
|
String updStatus = "";
|
|
|
|
sql = " SELECT * FROM ( \n"; //점포코드
|
|
sql = sql + " SELECT A.STOR_CD \n"; //점포코드
|
|
sql = sql + " , A.ITEM_CD \n"; //상품코드
|
|
sql = sql + " , CASE WHEN A.STATUS_DIV = '0' THEN '01' \n";
|
|
sql = sql + " WHEN A.STATUS_DIV = '2' THEN '00' \n";
|
|
sql = sql + " ELSE '' END SHORTAGE_CD \n"; //품점여부 00:판매(비결품) , 01:결품
|
|
sql = sql + " , NVL( A.BASE_QTY , 0) \n";
|
|
sql = sql + " - NVL(A.SALE_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.ORD_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.MV_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.ADD_STOCK_QTY , 0) \n";
|
|
sql = sql + " AS STOCK_QTY \n"; //재고수량
|
|
sql = sql + " , CASE WHEN A.STATUS_DIV = '0' THEN '1' \n";
|
|
sql = sql + " WHEN A.STATUS_DIV = '2' THEN '3' \n";
|
|
sql = sql + " ELSE '' END AS UPD_STATUS \n";
|
|
sql = sql + " FROM SPL_DAY_STOCK_HP A \n";
|
|
sql = sql + " , MST_STOR B \n";
|
|
sql = sql + " , MST_SALESORG_ITEM C \n";
|
|
sql = sql + " WHERE A.STOR_CD = B.STOR_CD \n";
|
|
sql = sql + " AND B.CMP_CD = C.CMP_CD \n";
|
|
sql = sql + " AND B.SALES_ORG_CD = C.SALES_ORG_CD \n";
|
|
sql = sql + " AND B.DRT_FRCS_TYPE = C.DRT_FRCS_TYPE \n";
|
|
sql = sql + " AND A.ITEM_CD = C.ITEM_CD \n";
|
|
sql = sql + " AND A.STATUS_DIV IN ('0', '2') \n";
|
|
sql = sql + " AND B.CMP_CD = 'PCKR' \n";
|
|
sql = sql + " AND C.CMP_CD = 'PCKR' \n";
|
|
sql = sql + " AND NVL(C.ITEM_PROD_TYPE, '1') <> '3' \n";
|
|
sql = sql + " UNION ALL \n";
|
|
sql = sql + " SELECT A.STOR_CD \n"; //점포코드
|
|
sql = sql + " , A.ITEM_CD \n"; //상품코드
|
|
sql = sql + " , CASE WHEN A.STATUS_DIV = '0' THEN '01' \n";
|
|
sql = sql + " WHEN A.STATUS_DIV = '2' THEN '00' \n";
|
|
sql = sql + " ELSE '' END SHORTAGE_CD \n"; //품점여부 00:판매(비결품) , 01:결품
|
|
sql = sql + " , NVL( A.BASE_QTY , 0) \n";
|
|
sql = sql + " - NVL(A.SALE_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.ORD_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.MV_QTY , 0) \n";
|
|
sql = sql + " + NVL(A.ADD_STOCK_QTY , 0) \n";
|
|
sql = sql + " AS STOCK_QTY \n"; //재고수량
|
|
sql = sql + " , CASE WHEN A.STATUS_DIV = '0' THEN '1' \n";
|
|
sql = sql + " WHEN A.STATUS_DIV = '2' THEN '3' \n";
|
|
sql = sql + " ELSE '' END AS UPD_STATUS \n";
|
|
sql = sql + " FROM SPL_DAY_STOCK_HP A \n";
|
|
sql = sql + " , SPL_HPYORD_ITEMVIEW_YN D \n";
|
|
sql = sql + " WHERE A.STOR_CD = D.STOR_CD \n";
|
|
sql = sql + " AND A.ITEM_CD = D.ITEM_CD \n";
|
|
sql = sql + " AND A.STATUS_DIV IN ('0', '2') \n";
|
|
sql = sql + " AND D.CMP_CD = 'PCKR' \n";
|
|
sql = sql + " ) \n";
|
|
sql = sql + " WHERE 1=1 \n";
|
|
sql = sql + " AND ROWNUM <= ( SELECT TO_NUMBER(CHAR_VAL_TITLE_VAL_01) AS NUM "
|
|
+ " FROM MST_CMM_CD_DTL "
|
|
+ " WHERE CMP_CD = 'PCKR' "
|
|
+ " AND CMM_GRP_CD = 'S0102' "
|
|
+ " AND CMM_CD = '1' "
|
|
+ " AND ROWNUM = 1 ) \n";
|
|
sql = sql + " \r\n";
|
|
|
|
|
|
try{
|
|
|
|
pstmt = dbConn.prepareStatement(sql) ;
|
|
|
|
rs = pstmt.executeQuery();
|
|
|
|
String sendJson = "";
|
|
int i = 0;
|
|
while(rs.next()){
|
|
|
|
storCd = rs.getString("STOR_CD");
|
|
itemCd = rs.getString("ITEM_CD");
|
|
updStatus = rs.getString("UPD_STATUS");
|
|
|
|
sendJson = "{\"deviceType\"" + ":\"POS\","
|
|
+ "\"brandCode\"" + ":\"PBparis\","
|
|
+ "\"storeCode\"" + ":\"" + storCd + "\","
|
|
+ "\"itemCode\"" + ":\"" + itemCd + "\","
|
|
+ "\"shortageCode\"" + ":\"" + rs.getString("SHORTAGE_CD") + "\","
|
|
+ "\"shortageMemo\"" + ":\"\","
|
|
+ "\"stockQty\"" + ":\"" + rs.getString("STOCK_QTY") + "\"}";
|
|
|
|
mp = new HashMap();
|
|
|
|
mp.put("STOR_CD" , storCd);
|
|
mp.put("ITEM_CD" , itemCd);
|
|
mp.put("UPD_STATUS" , updStatus);
|
|
|
|
// API 통신
|
|
callHappyOrdApi(sendJson, mp);
|
|
|
|
i++;
|
|
}
|
|
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] Send Count ==> " + i );
|
|
|
|
}catch(Exception e){
|
|
e.printStackTrace();
|
|
|
|
}finally{
|
|
|
|
if(rs != null){
|
|
try{
|
|
rs.close();
|
|
}catch(SQLException e){
|
|
|
|
}
|
|
}
|
|
if(pstmt != null){
|
|
try{
|
|
pstmt.close();
|
|
}catch(SQLException e){
|
|
|
|
}
|
|
}
|
|
|
|
updateAction("0");
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* 해피오더 제고 테이블 상태 업데이트
|
|
*
|
|
* @param dsm 조회조건 데이터
|
|
* @return DataSet 조회 결과 데이터셋
|
|
*
|
|
* @throws Exception
|
|
*/
|
|
public static int updateStatus(HashMap param) {
|
|
|
|
PreparedStatement pstmt = null;
|
|
int rs = -1;
|
|
|
|
String sql = "";
|
|
sql = " UPDATE SPL_DAY_STOCK_HP \n";
|
|
sql = sql + " SET STATUS_DIV = ? \n"; //상태 업데이트
|
|
sql = sql + " , UPD_DATE = SYSDATE \n"; //업데이트 일자
|
|
sql = sql + " WHERE STOR_CD = ? \n";
|
|
sql = sql + " AND ITEM_CD = ? \n";
|
|
sql = sql + " \r\n";
|
|
|
|
|
|
try {
|
|
//System.out.println("Start updateStatus");
|
|
|
|
pstmt = dbConn.prepareStatement(sql) ;
|
|
pstmt.setString(1, (String) param.get("UPD_STATUS") );
|
|
pstmt.setString(2, (String) param.get("STOR_CD") );
|
|
pstmt.setString(3, (String) param.get("ITEM_CD") );
|
|
|
|
rs = pstmt.executeUpdate();
|
|
|
|
}catch(Exception e){
|
|
|
|
try {
|
|
dbConn.rollback();
|
|
|
|
} catch (SQLException e1) {
|
|
|
|
}
|
|
|
|
e.printStackTrace();
|
|
|
|
}finally{
|
|
|
|
try{
|
|
dbConn.commit();
|
|
|
|
if(pstmt != null){
|
|
pstmt.close();
|
|
}
|
|
|
|
}catch(SQLException e){
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
return rs;
|
|
}
|
|
|
|
|
|
public static boolean searchAction() {
|
|
ResultSet rs = null;
|
|
int intRs = 0;
|
|
|
|
PreparedStatement pstmt = null;
|
|
|
|
String sql = "";
|
|
sql = " SELECT NVL(CHAR_VAL_TITLE_VAL_01, 0) AS PRESS_YN \n";
|
|
sql = sql + " FROM MST_CMM_CD_DTL \n"; //상태 업데이트
|
|
sql = sql + " WHERE CMP_CD = 'PCKR' \n";
|
|
sql = sql + " AND CMM_GRP_CD = 'S0101' \n";
|
|
sql = sql + " AND CMM_CD = 'P' \n";
|
|
sql = sql + " \r\n";
|
|
|
|
try {
|
|
|
|
pstmt = dbConn.prepareStatement(sql) ;
|
|
|
|
rs = pstmt.executeQuery();
|
|
|
|
while(rs.next()){
|
|
intRs = rs.getInt("PRESS_YN");
|
|
}
|
|
|
|
|
|
}catch(Exception e){
|
|
|
|
e.printStackTrace();
|
|
|
|
}
|
|
|
|
return intRs==1?true:false;
|
|
}
|
|
|
|
public static int updateAction(String param) {
|
|
|
|
PreparedStatement pstmt = null;
|
|
int rs = -1;
|
|
|
|
String sql = "";
|
|
|
|
sql = " UPDATE MST_CMM_CD_DTL \n";
|
|
sql = sql + " SET CHAR_VAL_TITLE_VAL_01 = ? \n"; //상태 업데이트
|
|
sql = sql + " WHERE CMP_CD = 'PCKR' \n";
|
|
sql = sql + " AND CMM_GRP_CD = 'S0101' \n";
|
|
sql = sql + " AND CMM_CD = 'P' \n";
|
|
sql = sql + " \r\n";
|
|
|
|
try {
|
|
|
|
pstmt = dbConn.prepareStatement(sql) ;
|
|
pstmt.setString(1, param );
|
|
|
|
rs = pstmt.executeUpdate();
|
|
|
|
|
|
}catch(Exception e){
|
|
try{
|
|
dbConn.rollback();
|
|
|
|
}catch(SQLException se){
|
|
|
|
}
|
|
|
|
e.printStackTrace();
|
|
|
|
}finally{
|
|
|
|
try{
|
|
dbConn.commit();
|
|
|
|
if(pstmt != null){
|
|
pstmt.close();
|
|
}
|
|
|
|
}catch(SQLException e){
|
|
|
|
}
|
|
}
|
|
|
|
|
|
return rs;
|
|
}
|
|
|
|
|
|
/**
|
|
* 해피오더 점포 재고 JSON POST 통신
|
|
*
|
|
* @param dsm 조회조건 데이터
|
|
* @return DataSet 조회 결과 데이터셋
|
|
*
|
|
* @throws Exception
|
|
*/
|
|
public static void callHappyOrdApi(String sendJson, HashMap param) {
|
|
|
|
HttpURLConnection con = null;
|
|
BufferedReader in = null;
|
|
OutputStream os = null;
|
|
|
|
//JSON API URL(IP) 정보 -개발
|
|
String HP_IP = "http://apex.happypointcard.co.kr";
|
|
String URL = "/ho/soldout"; //매장 상품 결품 처리
|
|
//String sendJson = "";
|
|
//System.out.println("[ JSon Data ] =============================> " + sendJson);
|
|
|
|
try {
|
|
URL url = new URL( HP_IP + URL ); //웹 화면 MST409 승인 업체 관리에서 55 해피오더로 관리 됨
|
|
|
|
con = (HttpURLConnection) url.openConnection();
|
|
con.setConnectTimeout(10000); //1000 millisecond 가 1초
|
|
con.setRequestProperty("Content-Type", "application/json; charset=UTF-8");
|
|
con.setDoOutput(true); //POST 방식은 스트림 기반의 데이터 전송 방식이기 때문에 setDoOutput(true)를 실행
|
|
con.setDoInput(true); //Server 통신에서 입력 가능한 상태로 만듬
|
|
con.setRequestMethod("POST");
|
|
con.connect();
|
|
|
|
//System.out.println( "[ JSON SEND ] " + sendJson );
|
|
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] " + sendJson);
|
|
|
|
os = con.getOutputStream();
|
|
os.write(sendJson.getBytes("UTF-8"));
|
|
os.close();
|
|
|
|
// 응답값 처리
|
|
in = new BufferedReader(new InputStreamReader(con.getInputStream(), "UTF-8"));
|
|
|
|
String receiveJson = "";
|
|
String strMap = "";
|
|
|
|
while((receiveJson = in.readLine()) != null) { // response를 차례대로 출력
|
|
//System.out.println( "[ JSON RECEIVE ] " + receiveJson );
|
|
|
|
//System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [INFO] " + receiveJson);
|
|
|
|
HashMap hm = new HashMap();
|
|
|
|
strMap = jSonToMapParsing(receiveJson).get("status").toString();
|
|
|
|
//System.out.println(strMap);
|
|
hm = jSonToMapParsing(strMap);
|
|
|
|
String strStatus = hm.get("status").toString().replace("\"", "");
|
|
String strCode = hm.get("code").toString().replace("\"", "");
|
|
String strMsg = hm.get("message").toString().replace("\"", "");
|
|
|
|
// 정상처리시 상태값 변경
|
|
if("200".equals(strStatus) && "20000".equals(strCode)){
|
|
updateStatus(param);
|
|
}else if("507".equals(strStatus) && "91005".equals(strCode)){
|
|
updateStatus(param);
|
|
}else{
|
|
String errMsg = "[ERROR] 점포코드/상품 [" + param.get("STOR_CD") + "/" + param.get("ITEM_CD") + "] status:" + strStatus + ",code:" + strCode + ",msg:" + strMsg ;
|
|
//System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [ERROR] "+ "status : " + strStatus.replace("\"", "") + ", code : " + strCode.replace("\"", "") );
|
|
errLog(param, errMsg);
|
|
}
|
|
}
|
|
|
|
in.close();
|
|
con.disconnect();
|
|
|
|
}catch (Exception e){
|
|
|
|
System.out.println( "[" + formatter.format(new Date()) + "] [" + chk + "] [ERROR] " + e.toString() );
|
|
}
|
|
|
|
}
|
|
|
|
public static int errLog(HashMap param, String errMsg) {
|
|
|
|
PreparedStatement pstmt = null;
|
|
PreparedStatement pstmt2 = null;
|
|
int rs = -1;
|
|
|
|
try {
|
|
String errSql = "";
|
|
|
|
errSql = " INSERT INTO SYS_BATCH_LOG (BATCH_ID ,BATCH_NM ,BATCH_TYPE ,START_DATE ,END_DATE ,SUCC_YN ,ERR_DESC ,REG_DATE ,REG_USER_ID ,UPD_DATE ,UPD_USER_ID) "
|
|
+ " VALUES ('YGY' ,'요기요 결품/재판매 API' ,'005' ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') ,TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ,1 ,'"+errMsg+"' ,SYSDATE ,'java_run' ,SYSDATE ,'java_run' ) " ;
|
|
|
|
|
|
String updSql = "";
|
|
updSql = " UPDATE SPL_DAY_STOCK_HP \n";
|
|
updSql = updSql + " SET STATUS_DIV = ? \n"; //상태 업데이트
|
|
updSql = updSql + " , UPD_DATE = SYSDATE \n"; //업데이트 일자
|
|
updSql = updSql + " WHERE STOR_CD = ? \n";
|
|
updSql = updSql + " AND ITEM_CD = ? \n";
|
|
updSql = updSql + " \r\n";
|
|
|
|
|
|
pstmt = dbConn.prepareStatement(errSql) ;
|
|
rs = pstmt.executeUpdate();
|
|
|
|
//System.out.println("Start updateStatus");
|
|
|
|
pstmt2 = dbConn.prepareStatement(updSql) ;
|
|
pstmt2.setString(1, (String) param.get("UPD_STATUS") );
|
|
pstmt2.setString(2, (String) param.get("STOR_CD") );
|
|
pstmt2.setString(3, (String) param.get("ITEM_CD") );
|
|
|
|
rs = pstmt2.executeUpdate();
|
|
|
|
}catch(Exception e){
|
|
|
|
e.printStackTrace();
|
|
|
|
try {
|
|
dbConn.rollback();
|
|
|
|
} catch (SQLException e1) {
|
|
e1.printStackTrace();
|
|
}
|
|
|
|
}finally{
|
|
|
|
try{
|
|
dbConn.commit();
|
|
|
|
if(pstmt != null){
|
|
pstmt.close();
|
|
}
|
|
|
|
if(pstmt2 != null){
|
|
pstmt2.close();
|
|
}
|
|
|
|
}catch(SQLException e){
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
return rs;
|
|
}
|
|
|
|
|
|
public static String isNull(String source, String value) {
|
|
String retVal;
|
|
|
|
if(source == null || source.trim().equals("") || source.trim().equals("null")) {
|
|
retVal = value;
|
|
} else {
|
|
retVal = source.trim();
|
|
}
|
|
return retVal;
|
|
}
|
|
|
|
public static String MapTojSonParsing(Map hmData) {
|
|
Gson gson = new GsonBuilder().disableHtmlEscaping().serializeNulls().create();
|
|
String sb = gson.toJson(hmData);
|
|
return sb.toString();
|
|
}
|
|
|
|
public static HashMap jSonToMapParsing(String sjson) {
|
|
JsonObject obj =(JsonObject) new com.google.gson.JsonParser().parse(sjson);
|
|
//new JsonParser().parse(sjson).getAsJsonObject(); //
|
|
Iterator i = obj.entrySet().iterator();
|
|
|
|
HashMap map = new HashMap();
|
|
while (i.hasNext()) {
|
|
Map.Entry e = (Map.Entry)i.next();
|
|
map.put((String)e.getKey(), e.getValue());
|
|
}
|
|
return map;
|
|
}
|
|
|
|
}
|