식단 관리 - 열량 차트

구글차트에 넣어줄 데이터 요청

function graph(){
	$.ajax({
		url : '${pageContext.request.contextPath}/user/diet/dietMemGraphList',
		error : function(result){
			alert('dietMemGraphList 에러');
		},
		success : function(result){
			

// 			$('#chart_chart').empty();
			
			google.charts.load('current', {'packages':['line','controls']});
			chartDrowFun.chartDrow(result);		
		}
	})	
}

 

요청을 받는 컨트롤러

@RequestMapping("dietMemGraphList")
	public ModelAndView dietMemGraph(ModelAndView andView, Map<String, String> params, HttpServletRequest request,
			HttpSession session) throws Exception {

		session = request.getSession();

		MemberVO memberInfo = (MemberVO) session.getAttribute("LOGIN_MEMBERINFO");

		params.put("mem_no", memberInfo.getMem_no());

		List<Diet_dayVO> dietDayMemList = dietService.dietMemGraphList(params);

		andView.addObject("dietDayMemList", dietDayMemList);

		andView.setViewName("jsonConvertView");
		
		return andView;
	}

 

실행되는 쿼리

SELECT *
from(
SELECT c.MEM_NO MEM_NO, d.DD_DATE DD_DATE, d.DD_KCAL DD_KCAL, c.DAY_KCAL DAY_KCAL
		FROM (
			SELECT MEM_NO, dm_no, DAY_KCAL, TO_CHAR(DM_DATE, 'YYYY-MM-DD') dm_date
			FROM DIET_MEM
			WHERE dm_no IN (
				SELECT max(a.dm_no) dm_no
				FROM diet_mem a, diet_day b
				WHERE a.MEM_NO = b.MEM_NO
				AND b.dd_date = TO_CHAR(a.DM_DATE, 'YYYY-MM-DD')
				GROUP BY dd_date)
			AND TO_CHAR(DM_DATE, 'YYYY-MM-DD') IN (
													SELECT dd_date
													FROM diet_mem a, diet_day b
													WHERE a.MEM_NO = b.MEM_NO
													AND b.dd_date = TO_CHAR(a.DM_DATE, 'YYYY-MM-DD')
													GROUP BY dd_date
													)
			)c , DIET_DAY d
		WHERE c.dm_date = d.dd_date
		AND d.mem_no = #{mem_no}
		ORDER BY dd_date)
		WHERE mem_no = #{mem_no}

 

//차트
var chartDrowFun = {

 chartDrow : function(e){

     // 보낼 데이터 - mem_no, 

     // 필요한거 dietMem의 dm_date, day_kcal
     
     // diet_day의 dd_kcal 
     var chartData = '';
     //날짜형식 변경하고 싶으시면 이 부분 수정하세요.
     var chartDateformat     = 'MM월dd일';
     //라인차트의 라인 수
     var chartLineCount    = 10;
     //컨트롤러 바 차트의 라인 수
     var controlLineCount    = 10;


     function drawDashboard() {

       var data = new google.visualization.DataTable();
       //그래프에 표시할 컬럼 추가
       data.addColumn('datetime' , '날짜');
       data.addColumn('number'   , '섭취 열량');
       data.addColumn('number'   , '목표 열량');
       

       //그래프에 표시할 데이터
       var dataRow = [];

       for(var i = 0; i < e.dietDayMemList.length; i++){ //데이터 삽입
           
     	  var dd_kcal = e.dietDayMemList[i].dd_kcal;
          var day_kcal = e.dietDayMemList[i].day_kcal;

           dataRow = [new Date(e.dietDayMemList[i].dd_date.split('-')[0], e.dietDayMemList[i].dd_date.split('-')[1] -1,e.dietDayMemList[i].dd_date.split('-')[2], '00'), Number(dd_kcal), Number(day_kcal)];
           data.addRow(dataRow);
       }
         var chart = new google.visualization.ChartWrapper({
           chartType   : 'LineChart',
           containerId : 'lineChartArea', //라인 차트 생성할 영역
           options     : {
                           isStacked   : 'percent',
                           focusTarget : 'category',
                           height          : 500,
                           width           : 1150,
							  chartArea: {left:30,top:20, bottom:50, top : 100},
                           legend          : { position: "top", textStyle: {fontSize: 13}},
                           pointSize        : 5,
                           tooltip          : {textStyle : {fontSize:12}, showColorCode : true,trigger: 'both'},
                           hAxis              : {format: chartDateformat, gridlines:{count:chartLineCount,units: {
                                                               years : {format: ['yyyy년']},
                                                               months: {format: ['MM월']},
                                                               days  : {format: ['dd일']},
                                                               hours : {format: ['HH시']}}
                                                             },textStyle: {fontSize:12}},
             vAxis              : {minValue: 100,viewWindow:{min:0},gridlines:{count:-1},textStyle:{fontSize:12}},
             animation        : {startup: true,duration: 1000,easing: 'in' },
             annotations    : {pattern: chartDateformat,
                             textStyle: {
                             fontSize: 15,
                             bold: true,
                             italic: true,
                             color: '#871b47',
                             auraColor: '#d799ae',
                             opacity: 0.8,
                             pattern: chartDateformat
                           }
                         }
           }
         });

         var control = new google.visualization.ControlWrapper({
           controlType: 'ChartRangeFilter',
           containerId: 'controlsArea',  //control bar를 생성할 영역
           options: {
               ui:{
                     chartType: 'LineChart',
                     chartOptions: {
                     chartArea: {'width': '70%','height' : 130},
                       hAxis: {'baselineColor': 'none', format: chartDateformat, textStyle: {fontSize:12},
                         gridlines:{count:controlLineCount,units: {
                               years : {format: ['yyyy년']},
                               months: {format: ['MM월']},
                               days  : {format: ['dd일']},
                               hours : {format: ['HH시']}}
                         }}
                     }
               },
                 filterColumnIndex: 0
             }
         });

         var date_formatter = new google.visualization.DateFormat({ pattern: chartDateformat});
         date_formatter.format(data, 0);

         var dashboard = new google.visualization.Dashboard(document.getElementById('Line_Controls_Chart'));
         window.addEventListener('resize', function() { dashboard.draw(data); }, false); //화면 크기에 따라 그래프 크기 변경
         dashboard.bind([control], [chart]);
         dashboard.draw(data);

     }
       google.charts.setOnLoadCallback(drawDashboard);

   }
 }