package com.engine.kq.cmd.group;

import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.biz.KQLoactionComInfo;
import com.engine.kq.biz.KQWifiComInfo;
import org.apache.poi.ss.usermodel.*;
import weaver.conn.RecordSet;
import weaver.file.ImageFileManager;
import weaver.general.Util;
import weaver.hrm.HrmUserVarify;
import weaver.hrm.User;
import weaver.systeminfo.SystemEnv;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ImportExcelCmd extends AbstractCommonCommand<Map<String, Object>> {

  public ImportExcelCmd(Map<String, Object> params, User user) {
    this.user = user;
    this.params = params;
  }

  @Override
  public Map<String, Object> execute(CommandContext commandContext) {
    Map<String, Object> retmap = new HashMap<String, Object>();
    RecordSet rs = new RecordSet();
    String sql = "";
    try {
      //必要的权限判断
      if (!HrmUserVarify.checkUserRight("HrmKQGroup:Add", user)) {
        retmap.put("status", "-1");
        retmap.put("message", SystemEnv.getHtmlLabelName(2012, user.getLanguage()));
        return retmap;
      }
      List lsDatas = null;
      List datas = null;

      String groupId = Util.null2String(params.get("groupId"));
      String filename = Util.null2String(params.get("filename"));
      int startRow = 3;
      ImageFileManager manager = new ImageFileManager();
      manager.getImageFileInfoById(Util.getIntValue(filename));
      Workbook workbook = WorkbookFactory.create(manager.getInputStream());
      for (int idx = 0; idx < workbook.getNumberOfSheets(); idx++) {
        Sheet sheet = workbook.getSheetAt(idx);
        Row row = null;
        Cell cell = null;
        lsDatas = new ArrayList();
        for (int i = startRow; startRow<=sheet.getLastRowNum() && i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row == null) {
            continue;
          }
          datas = new ArrayList();
          for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
            cell = row.getCell((short) cellIndex);
            if (cell == null) continue;
            datas.add(getCellValue(cell).trim());
          }
          datas.add(groupId);
          if(Util.null2String(datas.get(0)).length()>0){
            lsDatas.add(datas);
          }
        }
        if (idx == 0) {
          sql = "insert into kq_location (longitude,latitude,checkscope,address,locationname,groupid) values (?,?,?,?,?,?) ";
        } else {
          sql = "insert into kq_wifi (wifiname,mac,groupid) values (?,?,?) ";
        }
        rs.executeBatchSql(sql, lsDatas);
      }
      new KQLoactionComInfo().removeCache();
      new KQWifiComInfo().removeCache();
      retmap.put("status", "1");
    } catch (Exception e) {
      retmap.put("status", "-1");
      retmap.put("message", SystemEnv.getHtmlLabelName(382661, user.getLanguage()));
      writeLog(e);
    }
    return retmap;
  }

  @Override
  public BizLogContext getLogContext() {
    return null;
  }

  /**
   * /**
   * 获取excel单元格值
   *
   * @param cell 要读取的单元格对象
   * @return
   */
  public String getCellValue(Cell cell) {
    String cellValue = "";
    if (cell == null)
      return "";
    switch (cell.getCellType()) {
      case BOOLEAN: // 得到Boolean对象的方法
        cellValue = String.valueOf(cell.getBooleanCellValue());
        break;
      case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {// 先看是否是日期格式
          SimpleDateFormat sft = new SimpleDateFormat("yyyy-MM-dd");
          cellValue = String.valueOf(sft.format(cell.getDateCellValue())); // 读取日期格式
        } else {
          cellValue = String.valueOf(new Double(cell.getNumericCellValue())); // 读取数字
          if (cellValue.endsWith(".0"))
            cellValue = cellValue.substring(0, cellValue.indexOf("."));
        }
        break;
      case FORMULA: // 读取公式
        cellValue = cell.getCellFormula();
        break;
      case STRING: // 读取String
        cellValue = cell.getStringCellValue();
        break;
    }

    return cellValue;
  }
}
