package com.bcxin.survey.dao.report;

import com.bcxin.survey.domain.survey.SurveyTag;
import com.bcxin.survey.domain.survey.Survey_Info;
import com.bcxin.survey.enums.report.QuestionType;
import com.bcxin.survey.enums.report.SurveyStep;
import com.bcxin.survey.enums.report.TagType;
import com.bcxin.survey.utils.DictConst;
import com.bcxin.survey.utils.StringUtil;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository("cmsDao")
public class CMSDaoImpl extends BaseDaoImpl implements CMSDao {

	@Override
	public List<Survey_Info> getSurvey_InfoFromCMSForFix(SurveyStep surveyStep,String activityType,String addressType){
		StringBuffer sql = new StringBuffer("select * from dede_addon41 addon where 1=1 ");
		sql.append(" and takanjieduan = '第"+surveyStep.getValue()+"阶段' ");

		int count = getSurveyInfoCountFromCMSForFix(surveyStep,activityType,addressType);
		if (count == 0) {
			sql.append(" and gm = '1' ");
		} else {
			/* 其他类型默认用室内场地 #吴艺林需求*/
			if (DictConst.ADDRESSTYPE_SNLT.equals(addressType)
					|| DictConst.ADDRESSTYPE_SNSW.equals(addressType)) {
				addressType = DictConst.ADDRESSTYPE_SN;
			}
			sql.append(" and activityType = '"+activityType+"' ");
			sql.append(" and addressType = '"+addressType+"' ");
		}
		sql.append(" order by bianhao asc ");
		System.out.println(sql.toString());
		DraftDBConnection connection = new DraftDBConnection();
		List<Survey_Info> surveyInfos = new ArrayList<Survey_Info>();
		try {
			Statement st = connection.getDraftConnection().createStatement();
			ResultSet rs = st.executeQuery(sql.toString());
	        while(rs.next()){
	        	Survey_Info surveyInfo = new Survey_Info();
	        	surveyInfo.setAid(rs.getString("aid"));
	        	surveyInfo.setAnswerTip(rs.getString("takantishi"));
	        	surveyInfo.setCreateOn(new Date());
	        	String paixubianhao = rs.getString("bianhao");
	        	if(StringUtil.isNotEmpty(paixubianhao)) {
					surveyInfo.setSort(Long.parseLong(paixubianhao));
				}
	        	
	        	surveyInfo.setQuestion(rs.getString("takanxiangmu"));
	        	surveyInfo.setSurveyStep(SurveyStep.convertName(rs.getString("takanjieduan")));
	        	String shifouguding= rs.getString("shifouguding");
	        	if(shifouguding.equals("是")) {
					surveyInfo.setQuestionType(QuestionType.FIXED);
				}
	        	else {
					surveyInfo.setQuestionType(QuestionType.UNFIXED);
				}
	        	surveyInfos.add(surveyInfo);
	        }
		} catch (Exception e) {
			System.out.println(e);
		}
		return surveyInfos;
	}


	@Override
	public int getSurveyInfoCountFromCMSForFix(SurveyStep surveyStep,String activityType,String addressType){
		StringBuffer sql = new StringBuffer("select count(*) as shuliang from dede_addon41 addon where 1=1 ");
		sql.append(" and takanjieduan = '第"+surveyStep.getValue()+"阶段' ");

		/* 第一阶段的 */
		if (SurveyStep.FIRST.equals(surveyStep)) {
			/* 其他类型默认用室内场地 #吴艺林需求*/
			if (DictConst.ADDRESSTYPE_SNLT.equals(addressType)
					|| DictConst.ADDRESSTYPE_SNSW.equals(addressType)) {
				addressType = DictConst.ADDRESSTYPE_SN;
			}
			sql.append(" and activityType = '"+activityType+"' ");
			sql.append(" and addressType = '"+addressType+"' ");
		} else {
			return 0;
		}
		DraftDBConnection connection = new DraftDBConnection();
		try {
			Statement st = connection.getDraftConnection().createStatement();
			ResultSet rs = st.executeQuery(sql.toString());
			while(rs.next()){
				return Integer.parseInt(rs.getString("shuliang"));
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return 0;
	}

	@Override
	public void findSurveyTagsFromCMS(){
		StringBuffer sql = new StringBuffer("select addon.biaoqianfenlei,addon.biaoqian1,addon.biaoqian2,addon.biaoqian3,addon.biaoqian4,addon.biaoqian5,addon.biaoqian6 ");
		sql.append(" from dede_archives archives join dede_addon32 addon on archives.id = addon.aid ");
		sql.append(" and archives.arcrank !='-2' and addon.wentitimu !=''and addon.biaoqianfenlei!='' ");
	
		System.out.println(sql);
		DraftDBConnection connection = new DraftDBConnection();
		try {
			Statement st = connection.getDraftConnection().createStatement();
			ResultSet rs = st.executeQuery(sql.toString());
	        while(rs.next()){
	        	SurveyTag surveyTag = findSurveyTagByNameAndTypeAndLevel(rs.getString("biaoqianfenlei"),TagType.LABEL,1);
				if(surveyTag == null){
					surveyTag =new SurveyTag();
		        	surveyTag.setCreateOn(new Date());
		        	surveyTag.setName(rs.getString("biaoqianfenlei"));
		        	surveyTag.setLevel(1);
		        	surveyTag.setTagType(TagType.LABEL);
		        	saveOrUpdate(surveyTag);
				}
	        	
	        	for(int i = 1; i<= 6; i++){
	        		if(StringUtil.isNotEmpty(rs.getString("biaoqian"+i))){
	        			SurveyTag tag = findSurveyTagByNameAndTypeAndLevel(rs.getString("biaoqian"+i),TagType.LABEL,2);
	        			if(tag == null){
		        			tag= new SurveyTag();
		        			tag.setCreateOn(new Date());
		        			tag.setName(rs.getString("biaoqian"+i));
		        			tag.setLevel(2);
		        			tag.setParentId(surveyTag.getOid());
		        			tag.setTagType(TagType.LABEL);
		        			saveOrUpdate(tag);
	        			}
	        		}
	        	}
	        } 
		}catch (Exception e) {
			System.out.println(e);
		}
	}
	
	@SuppressWarnings("unchecked")
	private SurveyTag findSurveyTagByNameAndTypeAndLevel(String name, TagType type, int level) {
		List<Criterion> criterionList = new ArrayList<Criterion>();
		if(StringUtil.isNotEmpty(name)) {
			criterionList.add(Restrictions.eq("name", name));
		}
		else {
			return null;
		}
		if(type != null) {
			criterionList.add(Restrictions.eq("tagType", type));
		}
		else {
			return null;
		}
		criterionList.add(Restrictions.eq("level", level));
		List<SurveyTag> surveyTags = findByCriterion(SurveyTag.class,criterionList);
		if(surveyTags != null && surveyTags.size()>0) {
			return surveyTags.get(0);
		}
		
		return null;
	}


	@Override
	public List<Survey_Info> findSurveyInfoByTags(String tags) {
		StringBuffer sql = new StringBuffer("select addon.* from dede_archives archives join dede_addon32 addon on archives.id = addon.aid ");
		sql.append(" and archives.arcrank !='-2' and addon.wentitimu !='' ");
		sql.append(" and addon.shifouguding='否' ");
		if(StringUtil.isNotEmpty(tags)){
			for(String tag:tags.split(",")){
				String label="";
				for(int i = 1; i<= 6; i++){
					if(i==1) {
						label = " addon.biaoqian" + i + " =" + tag + "";
					}else {
						label = label + " or addon.biaoqian" + i + " =" + tag + "";
					}
				}
				if(StringUtil.isNotEmpty(label)) {
					sql.append(" and (" + label + ") ");
				}
			}
		}
		System.out.println(sql);		
		DraftDBConnection connection = new DraftDBConnection();
		List<Survey_Info> surveyInfos = new ArrayList<Survey_Info>();
		try {
			Statement st = connection.getDraftConnection().createStatement();
			ResultSet rs = st.executeQuery(sql.toString());
	        while(rs.next()){
	        	Survey_Info surveyInfo = new Survey_Info();
	        	surveyInfo.setAid(rs.getString("aid"));
	        	surveyInfo.setAnswerTip(rs.getString("takanjieguotishi"));
	        	surveyInfo.setCreateOn(new Date());
	        	
	        	surveyInfo.setQuestion(rs.getString("wentitimu"));
	        	surveyInfo.setSurveyStep(SurveyStep.convertName(rs.getString("takancishu")));
	        	String shifouguding= rs.getString("shifouguding");
	        	if(shifouguding.equals("是")) {
					surveyInfo.setQuestionType(QuestionType.FIXED);
				}
	        	else {
					surveyInfo.setQuestionType(QuestionType.UNFIXED);
				}
	        	surveyInfos.add(surveyInfo);
	        }
		} catch (Exception e) {
			System.out.println(e);
		}
		return surveyInfos;
	}

	@Override
	public Survey_Info findSurveyInfoByAid(String aid) {
		StringBuffer sql = new StringBuffer("select addon.* from dede_archives archives join dede_addon32 addon on archives.id = addon.aid ");
		sql.append(" and archives.arcrank !='-2' ");
		if(StringUtil.isNotEmpty(aid)){
        	sql.append(" and addon.aid='"+ aid +"' ");
		}
		System.out.println(sql.toString());
		DraftDBConnection connection = new DraftDBConnection();
    	Survey_Info surveyInfo = new Survey_Info();
		try {
			Statement st = connection.getDraftConnection().createStatement();
			ResultSet rs = st.executeQuery(sql.toString());
	        while(rs.next()){
	        	surveyInfo.setAid(rs.getString("aid"));
	        	surveyInfo.setAnswerTip(rs.getString("takanjieguotishi"));
	        	surveyInfo.setCreateOn(new Date());

	        	
	        	surveyInfo.setQuestion(rs.getString("wentitimu"));
	        	surveyInfo.setSurveyStep(SurveyStep.convertName(rs.getString("takancishu")));
	        	
	        	String label="";
	        	for(int i = 1; i<= 6; i++){
	        		if(StringUtil.isNotEmpty(rs.getString("biaoqian"+i))){
	        			if(StringUtil.isEmpty(label)) {
							label = rs.getString("biaoqian" + i);
						}else {
							label = label + "," + rs.getString("biaoqian" + i);
						}
	        		}
	        	}
	        	String options= "";
	        	for(int j=1;j<=6;j++){
	        		if(StringUtil.isNotEmpty(rs.getString("xuanxiang"+j))){
	        			if(StringUtil.isEmpty(options)) {
							options = rs.getString("xuanxiang" + j);
						}else {
							options = options + "#" + rs.getString("xuanxiang" + j);
						}
	        		}
	        	}
	        }
		} catch (Exception e) {
			System.out.println(e);
		}
		return surveyInfo;
	}

}