package com.mvc.Beans;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.multiaction.MultiActionController;

public class countCondition extends MultiActionController {
	public JdbcTemplate jtl;

	public JdbcTemplate getJtl() {
		return jtl;
	}

	public void setJtl(JdbcTemplate jtl) {
		this.jtl = jtl;
	}
	
	public ModelAndView getCountCondition(HttpServletRequest request,HttpServletResponse response) throws Exception{
		request.setCharacterEncoding("GBK");
		String sname=request.getParameter("sname");
		String division=request.getParameter("division");
		String company=request.getParameter("company");
		String linename=request.getParameter("linename");
		String year=request.getParameter("year");
		String month=request.getParameter("month");
		
		String ctl=" ";
		int label=0;
		
		if(!sname.equals(" ")){
			ctl=" and station.sname like '%"+sname+"%' ";
			label=1;
		}
		
		if(!division.equals(" ") && label==1){
			ctl =ctl + " and station.division like '%"+division+"%' ";
		}
		if(!division.equals(" ") && label==0){
			ctl = " and station.division like '%"+division+"%' ";
			label=1;
		}
		
		if(!company.equals(" ") && label == 1){
			ctl =ctl + " and station.company like '%"+company+"%' ";
		}
		if(!company.equals(" ") && label==0){
			ctl = " and station.company like '%"+company+"%' ";
			label=1;
		}
		
		if(!linename.equals(" ") && label==1){
			ctl =ctl+" and counting.linename like '%"+linename+"%'";
		}
		if(!linename.equals(" ") && label==0){
			ctl=" and counting.linename like '%"+linename+"%'";
			label=1;
		}
		
		if(!year.equals(" ") && label == 1){
			ctl=ctl + " and year(counting.calendar) like '%"+year+"%' ";
		}
		if(!year.equals(" ") && label==0){
			ctl=" and year(counting.calendar) like '%"+year+"%' ";
			label=1;
		}
		
		if(!month.equals(" ") && label==1){
			ctl=ctl+" and month(counting.calendar) like '%"+month+"%' ";
		} 
		if(!month.equals(" ") && label==0){
			ctl=" and month(counting.calendar) like '%"+month+"%' ";
			label=1;
		}
		
		if(label==0){
			List clist=jtl.queryForList("select station.sname,station.division,station.company,counting.linename,counting.busnum,counting.money,counting.busnum*counting.money as total from station,counting where counting.linename=station.linename");
			return new ModelAndView("countingList","clist",clist);
		}
		else{
			List clist=jtl.queryForList("select station.sname,station.division,station.company,counting.linename,counting.busnum,counting.money,counting.busnum*counting.money as total from station,counting where counting.linename=station.linename"+ctl+" ");
			return new ModelAndView("countingList","clist",clist);
		}
	}
}