package cn.com.capcloud.core.execl.xls;

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlCol implements ColFace {

	private int colIndex;
	private int keyColIndex;
	private JdbcTemplate jdbcTemplate;
	private String sql;
	private int cellType;
	private boolean isWriteNulltoZero=true;
	
	public boolean isWriteNulltoZero() {
		return isWriteNulltoZero;
	}

	public void setWriteNulltoZero(boolean isWriteNulltoZero) {
		this.isWriteNulltoZero = isWriteNulltoZero;
	}

	public int getCellType() {
		return cellType;
	}

	public void setCellType(int cellType) {
		this.cellType = cellType;
	}

	private int startRow;
	private int endRow;

	public int getStartRow() {
		return startRow;
	}

	public void setStartRow(int startRow) {
		this.startRow = startRow;
	}

	public int getEndRow() {
		return endRow;
	}

	public void setEndRow(int endRow) {
		this.endRow = endRow;
	}

	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public String getSql() {
		return sql;
	}
	/**
	 * 暂时规定sql只能有两列，第一个字段是key值，第二个字段是value值
	 * 第一个字段用key做别名，第二个字段用value做别名
	 * @param sql
	 */
	public void setSql(String sql) {
		this.sql = sql;
	}

	public void setColIndex(int colIndex) {
		this.colIndex = colIndex;
	}

	public void setKeyColIndex(int keyColIndex) {
		this.keyColIndex = keyColIndex;
	}

	@Override
	public List<CellForm> getCellFormList(jxl.write.WritableSheet sheet,JdbcTemplate jdbcTemplate) {
		List<CellForm> list=new ArrayList<CellForm>();
		List<Map<String, Object>> queryList=jdbcTemplate.queryForList(sql);

		Map<String,String> map=new HashMap<String,String>();
		for(int i=0;i<queryList.size();i++){
			map.put(String.valueOf(queryList.get(i).get("MAPKEY")),String.valueOf(queryList.get(i).get("VALUE")));
		}

		for(int i=this.getStartRow();i<=this.getEndRow();i++){
			String keyValue=sheet.getCell(this.getKeyColIndex(), i).getContents();
			if(map.get(keyValue)!=null){
				CellForm cellForm=new CellForm();
				cellForm.setCol(this.getColIndex());
				cellForm.setRow(i);
				cellForm.setHeaderformat(CellFormat.BODY_CHAR_FLOAT);
				cellForm.setType(this.getCellType());
				cellForm.setCellValue(map.get(keyValue));
				list.add(cellForm);
			}else if(keyValue.length()>0&&this.isWriteNulltoZero()){
				CellForm cellForm=new CellForm();
				cellForm.setCol(this.getColIndex());
				cellForm.setRow(i);
				cellForm.setHeaderformat(CellFormat.BODY_CHAR_FLOAT);
				cellForm.setType(this.getCellType());
				cellForm.setCellValue("0");
				list.add(cellForm);
			}
			
		}
		return list;
	}

	@Override
	public int getColIndex() {
		return this.colIndex;
	}

	@Override
	public int getKeyColIndex() {
		return this.keyColIndex;
	}
	

}
