package com.quant.controller;

import java.io.File;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.quant.comm.DBhepler;
import com.quant.comm.StuService;
import com.quant.dao.ExcelMapper;
import com.quant.dao.ProductMapper;
import com.quant.entity.ProductBasic;
import com.quant.vo.ExcelVo;
@Controller
@RequestMapping("/operation/ExcelToDb")		
public class ExcelToDbController {
	@Resource
	private ProductMapper productMapper;
	
	@Resource
	private ExcelMapper excelMapper;
		
	@RequestMapping("/toGetFile.do")
		public String toGetFile(){
			return "product/excel_toFile";
		}
		
		@RequestMapping("/getFile.do")
		public String getFile(Model model,String fileName) throws Exception, Exception{
			Workbook rwb=Workbook.getWorkbook(new File(fileName));
			String[] sheetName = rwb.getSheetNames();
			model.addAttribute("sheetName",sheetName);//将sheetName传到页面上
			model.addAttribute("fileName", fileName);
			rwb.close();
			return "product/excel_toFile";
		}
		
		@RequestMapping("/toCheck.do")
		public String toCheck(Model model,String fileName,String sheetName) throws Exception, Exception{
			String sqls = "select table_name from information_schema.tables t "
					+ "where t.table_schema='work'";
			DBhepler db=new DBhepler();
			ResultSet rs = db.Search(sqls);
			List<String> tableName = new ArrayList<String>();
			while(rs.next()){
				tableName.add(rs.getString(1));//获得tableName
			}
			model.addAttribute("tableName",tableName);//将tableName传到页面上
			model.addAttribute("fileName",fileName);
			model.addAttribute("sheetName",sheetName);
			return "product/excel_toFile";
		}
		
		@RequestMapping("/getword.do")
		public String word(Model model,String tableName,String fileName,String sheetName) throws Exception{
			List<ExcelVo>listExcel = StuService.getAllByExcel(fileName,sheetName,false);//查询所有字段
			model.addAttribute("list", listExcel);//将excel的每个字段传送到页面
			String sql="select COLUMN_NAME from information_schema.COLUMNS where "
					+ "TABLE_SCHEMA='work' and table_name="+"'"+tableName+"'"+" AND COLUMN_KEY <>'PRI'";
			DBhepler db=new DBhepler();
			ResultSet rs = db.Search(sql);
			List<String> wordName = new ArrayList<String>();
			while(rs.next()){
				wordName.add(rs.getString(1));//获得数据库字段名
			}
			List<ProductBasic> product = productMapper.findSimpleProduct();
			model.addAttribute("product",product);
			
			model.addAttribute("list", listExcel);
			model.addAttribute("wordName", wordName);
			model.addAttribute("tableName",tableName);
			model.addAttribute("fileName",fileName);
			model.addAttribute("sheetName",sheetName);
			return "product/excel_toFile";
		}
		
		@RequestMapping("/insertSql.do")
		public String insertSql(Model model,String tableName,String fileName,Integer productId,
				String sheetName,int accumulativeValue,int expiryDate,int netValue) throws Exception{
			DBhepler db=new DBhepler();
			String sql="select COLUMN_NAME from information_schema.COLUMNS where "
					+ "TABLE_SCHEMA='work' and table_name="
					+ ""+"'"+tableName+"'"+" AND COLUMN_KEY <>'PRI'";//查询表中的字段
			ResultSet r = db.Search(sql);
			List<String> wordName = new ArrayList<String>();
			while(r.next()){
				wordName.add(r.getString(1));//获得数据库字段名
			}
			
			Workbook rwb=Workbook.getWorkbook(new File(fileName));
			Sheet rs=rwb.getSheet(sheetName);				
			int length = 3;
			int rows = rs.getRows();//得到所有行
			int a =0;
			int b =0;
			List<Date> date = excelMapper.findDateByProductId(productId);
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			
			//如果产品Id相同且日期相同则更新
			for(int i=1;i<rows;i++){
				if(rs.getCell(1,i).getContents()==null||rs.getCell(1,i).getContents().equals("")){
					break;
				}
				boolean flag =true;
				String str="";
				String dates="";
				int j=0;
				for(;j<length-1;j++){
					Date datea = new Date();
					if(rs.getCell(expiryDate,i).getType()==CellType.DATE){
						DateCell dc = (DateCell) rs.getCell(expiryDate,i);
						datea = dc.getDate();
					}
					String values=sdf.format(datea);
					for(Date d :date){
						String expiryDates = sdf.format(d);
						expiryDates=sdf.format(sdf.parse(expiryDates));
						
						if(values.equals(expiryDates)){
							sql="update "+tableName;
							flag = false;
							dates=values;
							break;
						}
					}
				   if(!flag){
					   break;
				   }	
				}
				
				String text="";
				String net="";
				if(rs.getCell(accumulativeValue,i).getType() == CellType.NUMBER_FORMULA){
					NumberCell numberCell = (NumberCell)rs.getCell(accumulativeValue,i); 
					double value =numberCell.getValue();
					text = value + "";
				}else{
					text = rs.getCell(accumulativeValue,i).getContents();
				}
				
				if(rs.getCell(netValue,i).getType() == CellType.NUMBER_FORMULA){
					NumberCell numberCell = (NumberCell)rs.getCell(netValue,i); 
					double value =numberCell.getValue();
					net = value + "";
				}else{
					net = rs.getCell(netValue,i).getContents();
				}
				if(flag){
					Date now = new Date(System.currentTimeMillis());
					String nows = sdf.format(now);
					
					
					for(j=0;j<length-1;j++){
					sql="insert into "+tableName+" (product_id,accumulative_value,net_value,expiry_date,insert_date) values ";
					 str="( "+productId+",";
						str+="'"+text+"'"+","+"'"+net+"'"+",";
						str+="'"+rs.getCell(expiryDate,i).getContents()+"'"+","+"'"+nows+"'"+" )";
					}
				}else{
					Date now = new Date(System.currentTimeMillis());
					String nows = sdf.format(now);
					   Date datea = new Date();
						if(rs.getCell(expiryDate,i).getType()==CellType.DATE){
							DateCell dc = (DateCell) rs.getCell(expiryDate,i);
							datea = dc.getDate();
						}
						String values=sdf.format(datea);
						sql+=" set accumulative_value="+text+","+
						"net_value="+net+","+"insert_date="+"'"+nows+"'"+
							" where product_id="+productId+" and expiry_date="+"'"+values+"'";
					}
				a = db.AddU(sql, str);
				b=b+a;
			}
			model.addAttribute("b", b);
			return "product/excel_B";
		}
	}
