메모하는 습관

Oracle COUNT() OVER(PARTITION BY)

 - COUNT() OVER() -- 1201~1210 일 기준 주문데이터 중 PAY_FINISH 를 기준으로 카운트 반환 
 SELECT COUNT(DISTINCT CASE WHEN ood.ord_no >= OOD.PAY_FINISH THEN ood.ord_no END) OVER(PARTITION BY OOD.PAY_FINISH) CNT_ORD    
   FROM ORD OOD   
  WHERE OOD.PAY_FINISH BETWEEN '20151201' AND '20151210'

Oracle 9i 부터 지원되는 Pipelined Table Function 에 대한 예제 참고

예제 1

예제 2

테이블 자동증가값 초기화

DBCC CHECKIDENT(Table명,RESEED,초기값) 

ex) DBCC CHECKIDENT(TBL_BOARD, RESEED, 1)

GMT/UTC 표준 시간 변환

글로벌 프로젝트 중 PG사로부터 결제완료 일자를 아래와 같은 생소한 format 으로 리턴 받은적이 있는데 컨버트 과정을 거쳐 해결함.

• Response date : 31/3/2015 4:44:30 PM
• Malay : GMT +8
• Korea : GMT +9 

/* 작성일 : 2013.11.27
 * 내 용 : GMT/UTC 일시 변환
 */
public void getConvert(ParameterMap parameterMap) throws Exception {
    //31/3/2015 4:01:59 PM ---> yyyy-MM-dd HH:mm:ss 변환
    logger.debug("TIMES " + convertToDate("31/3/2015 4:44:30 PM"));
    logger.debug("TIMES " + utcToLocaltime(convertToDate("31/3/2015 4:44:30 PM")));
}

/* convertToDate
 * @param datetime
 * SimpleDateFormat("yyyy-MM-dd HH:mm:ss") HH 대문자 : 24 Hour
 * 소문자 : 12 Hour  
 */
public static String convertToDate(String inputDate) throws Exception{
    String dateTime = inputDate;
    SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss a", Locale.US);
    Date parseDate = null;
    String convertedDate = null;
	 
    try {
        parseDate = format.parse(dateTime);
        DateFormat dateFormatNeeded = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        convertedDate = dateFormatNeeded.format(parseDate);  
    } catch (ParseException e) {
        logger.error("error " + e);
        throw new Exception(e);
    }
	
    return convertedDate;
}


/* UTC time to local time
 */
public static String utcToLocaltime(String datetime) throws Exception {
    String locTime = null;
    //TimeZone tz = TimeZone.getTimeZone("GMT+08:00"); 해당 국가 일시 확인 할 때, 한쿸은 +9
    TimeZone tz = TimeZone.getDefault();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
	
    try {
        Date parseDate = sdf.parse(datetime);
        long milliseconds = parseDate.getTime();
        int offset = tz.getOffset(milliseconds);
        locTime = sdf.format(milliseconds + offset);
        locTime = locTime.replace("+0000", "");
    } catch(Exception e) {
        e.printStackTrace();
        throw new Exception(e);
    }
	
    return locTime;
}
 
/**
 * local time to UTC time
 */
public static String localtimeToUTC(String inputdatetime) throws Exception {
    String utcTime = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
    TimeZone tz = TimeZone.getDefault();
	 
    try {
        Date parseDate = sdf.parse(inputdatetime);
        long milliseconds = parseDate.getTime();
        int offset = tz.getOffset(milliseconds);
        utcTime = sdf.format(milliseconds - offset);
        utcTime = utcTime.replace("+0000", "");
    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception(e);
    }
	 
    return utcTime;
}

//선택한 주문상품 배열 생성 2015.03.19 -add lee.jong.pil
var fnChkGoodsParam = function(){
	//주문상품 재계산 배열
	var goodsArray = new Array();	

	$("input[name^='cart_chk']").each(function(){
		var $parent = $(this).parent();
		var goodsParam = {};

		if($(this).is(":checked")){
			goodsParam.GOODS_NO 			 = $parent.find("#goods_no").val(); 
			goodsParam.GOODS_CUST_SALE_PRICE = $parent.find("#goods_Cust_Sale_Price").val(); 
			goodsParam.GOODS_SALE_PRICE 	 = $parent.find("#goods_Sale_Price").val(); 

			goodsParam.GOODS_DC_PRICE_TOT 	 = $parent.find("#goods_Dc_Price_Tot").val(); 
			goodsParam.GOODS_DC_PRICE 		 = $parent.find("#goods_dc_price").val(); 
			goodsParam.GOODS_DC_PRICE1   	 = $parent.find("#goods_dc_price1").val(); 
			goodsParam.GOODS_DC_PRICE2       = $parent.find("#goods_dc_price2").val(); 
			goodsParam.GOODS_DC_PRICE_STAFF  = $parent.find("#goods_dc_priceStaff").val(); 

			goodsParam.GOODS_DLVF_ST             = $parent.find("#goods_Dlvf_St").val(); 
			goodsParam.GOODS_DLVF_COST        = $parent.find("#goods_Dlvf_Cost").val(); 
			goodsParam.GOODS_DLVF_POLICY_NO  = $parent.find("#goods_Dlvf_Policy_No").val(); 
			goodsParam.GOODS_VIR_VEND_NO     = $parent.find("#vir_vend_no").val(); 
			
			goodsArray.push(goodsParam);
		}
		//배송비 노출영역 초기화(벤더별)
		$("div.calcul-content").find('#'+$parent.find("#vir_vend_no").val()).find('.price').text("0");
	});

	//(2)선택된 상품 기준 주문금액 재계산
	fnGoodsReCalcAmt(goodsArray);
}


//선택 상품 주문금액 재계산 
var fnGoodsReCalcAmt = function(goodsArray){
	//배송비 정책번호 기준으로 배열 정렬
	goodsArray.sort(function(a, b){
		return a.GOODS_DLVF_POLICY_NO-b.GOODS_DLVF_POLICY_NO;
	});

	//console.dir("goodsArrays ===> " + JSON.stringify(goodsArray));
	
	var goodsCustSalePriceAmt = 0;		//고객판매가 총금액
	var goodsSalePriceAmt = 0;			//상품 판매가 총금액
	var goodsDcPriceAmt	= 0;			//프로모션 총할인 금액
	var goodsDcPrice = 0;				//회원할인
	var goodsDcPrice1 = 0;				//월령할인
	var goodsDcPrice2 = 0;				//프리미엄할인
	var goodsDcPriceStaff = 0;			//임직원할인
	var goodsReCalcDlvfCostAmt = 0;		//재계산 배송비 합계
		
	var tmpDlvfPolicyNo = 0; 			//직전 배송비정책
	var tmpGoodsCustSalePrice = 0;		//직전 고객판매가

	//배송비 재계산
	$.each(goodsArray, function(i) {
		var goodsDlvfPolicyNo 		= goodsArray[i].GOODS_DLVF_POLICY_NO;
		var goodsCustSalePrice 		= goodsArray[i].GOODS_CUST_SALE_PRICE;
		
		//1.배송비 정책 동일한 경우
		if(tmpDlvfPolicyNo == goodsDlvfPolicyNo){
			//재계산된 배송비 초기화
			if(goodsArray[i-1]["GOODS_RECALC_DLVF_COST"] > 0){
				$.each(goodsArray, function(j) {
					if(goodsArray[j].GOODS_DLVF_POLICY_NO == goodsDlvfPolicyNo){
						goodsArray[j]["GOODS_RECALC_DLVF_COST"] = "0";
					}								
				});
			}
			
			//직전 정책번호의 고객판매가 금액 조회
			if(tmpGoodsCustSalePrice == 0){
				tmpGoodsCustSalePrice = goodsArray[i-1].GOODS_CUST_SALE_PRICE;
			}
			
			var sumGoodsCustSalePrice = cmmCalc(tmpGoodsCustSalePrice, "+", goodsCustSalePrice);
						
			//1.동일정책 고객판매가 합산 : 기준정책금액 비교
			if(parseInt(sumGoodsCustSalePrice) >= parseInt(goodsArray[i].GOODS_DLVF_ST)){
				//1-1.동일정책 고객판매가 합산금액 >= 기준정책금액
				goodsArray[i]["GOODS_RECALC_DLVF_COST"] = "0";
			}else{
				//1-2.동일정책 고객판매가 합산금액 < 기준정책금액
				goodsArray[i]["GOODS_RECALC_DLVF_COST"] = goodsArray[i].GOODS_DLVF_COST;
			};

			tmpGoodsCustSalePrice = sumGoodsCustSalePrice;		//동일정책 고객판매가 합산
			
		}else{ //배송비정책 동일하지 않은 경우 (최초에도 실행)
			
			//2.고객판매가  : 기준정책금액 비교
			if(parseInt(goodsArray[i].GOODS_CUST_SALE_PRICE) < parseInt(goodsArray[i].GOODS_DLVF_ST)){
				//2-1.고객판매가  < 기준정책금액 (기준배송비 부과)
				goodsArray[i]["GOODS_RECALC_DLVF_COST"] = goodsArray[i].GOODS_DLVF_COST;
			}else{
				//2-2.고객판매가 > 기준정책금액 (배송비 0)
				goodsArray[i]["GOODS_RECALC_DLVF_COST"] = "0";
			};

			tmpGoodsCustSalePrice = 0;							//동일정책 고객판매가 초기화
		}

		tmpDlvfPolicyNo  =  goodsDlvfPolicyNo;		//배송비정책
	});
		
	//console.dir("goodsArrays ===> " + JSON.stringify(goodsArray));

	//주문금액합산	
	$.each(goodsArray, function(k){
		goodsCustSalePriceAmt 	= cmmCalc(goodsCustSalePriceAmt, "+", goodsArray[k].GOODS_CUST_SALE_PRICE);
		goodsSalePriceAmt 		= cmmCalc(goodsSalePriceAmt, 	 "+", goodsArray[k].GOODS_SALE_PRICE);
		goodsDcPriceAmt 		= cmmCalc(goodsDcPriceAmt, 		 "+", goodsArray[k].GOODS_DC_PRICE_TOT);
		goodsDcPrice 			= cmmCalc(goodsDcPrice, 		 "+", goodsArray[k].GOODS_DC_PRICE);
		goodsDcPrice1 			= cmmCalc(goodsDcPrice1, 		 "+", goodsArray[k].GOODS_DC_PRICE1);
		goodsDcPrice2 			= cmmCalc(goodsDcPrice2, 		 "+", goodsArray[k].GOODS_DC_PRICE2);
		goodsDcPriceStaff 		= cmmCalc(goodsDcPriceStaff, 	 "+", goodsArray[k].GOODS_DC_PRICE_STAFE);
		goodsReCalcDlvfCostAmt 	= cmmCalc(goodsReCalcDlvfCostAmt,"+", goodsArray[k].GOODS_RECALC_DLVF_COST);		
		
		var goodsVirVendNo		= goodsArray[k].GOODS_VIR_VEND_NO;
		var goodsDlvfRCostName	= goodsArray[k].GOODS_RECALC_DLVF_COST == 0 ? "무료배송" : toCurrency(goodsArray[k].GOODS_RECALC_DLVF_COST)+"원";
		
		//이전 벤더사별 배송비 
		var prevGoodsVirVendPrice = cmmCalc($("div.calcul-content").find('#'+goodsVirVendNo).find('.price').text(), "+", goodsArray[k].GOODS_RECALC_DLVF_COST);			
		$("div.calcul-content").find('#'+goodsVirVendNo).find('.price').text(toCurrency(prevGoodsVirVendPrice));
		$("div.shipping-area."+goodsArray[k].GOODS_NO).text(goodsDlvfRCostName);			
		
	});	

	//재계산 주문금액 노출
	$("em.goodsCustSalePriceAmt").text(toCurrency(cmmCalc(goodsCustSalePriceAmt, "+", goodsReCalcDlvfCostAmt)));
	$("em.goodsSalePriceAmt").text(toCurrency(goodsSalePriceAmt));			
	$("em.goodsDcPriceAmt").text(toCurrency(goodsDcPriceAmt));				
	$("em.goodsDcPrice").text(toCurrency(goodsDcPrice));						
	$("em.goodsDcPrice1").text(toCurrency(goodsDcPrice1));
	$("em.goodsDcPrice2").text(toCurrency(goodsDcPrice2));
	$("em.goodsDcPriceStaff").text(toCurrency(goodsDcPriceStaff));
	$("em.goodsReCalcDlvfCostAmt").text(toCurrency(goodsReCalcDlvfCostAmt));
}

$("[name^=cart_chk]").bind("click", function(){
 //(1)선택 상품 기준 배열생성 호출
 fnChkGoodsParam();
});


   총 상품 판매가 원 
   총 할인 금액 원
   총 배송비 원
   프리미엄할인 원
   회원할인 원
    .
    .(생략)