package com.yizhi.application.customProject.contoller;


import com.yizhi.core.application.context.RequestContext;
import com.yizhi.core.application.context.TaskContext;
import com.yizhi.core.application.file.task.AbstractDefaultTask;
import com.yizhi.custom.api.feign.StudentExperienceClient;
import com.yizhi.custom.api.vo.custom.ExperienceImportSheetRowVO;
import com.yizhi.custom.api.vo.domain.StudentExperienceVo;
import com.yizhi.system.application.model.AccountNameForIdsModel;
import com.yizhi.system.application.system.remote.AccountClient;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author Ding
 * @className ExternalExperienceImport
 * @description TODO
 * @date 2018/11/9
 **/
@Component
public class ExternalExperienceImport extends AbstractDefaultTask<String, Map<String, Object>> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExternalExperienceImport.class);

    @Autowired
    private AccountClient accountClient;

    @Autowired
    private StudentExperienceClient studentExperienceClient;

    @Override
    protected String execute(Map<String, Object> map) {
        Long taskId = (Long) map.get("taskId");
        String serialNo = (String) map.get("serialNo");
        String taskName = (String) map.get("taskName");
        String ossUrl = (String) map.get("url");
        URL url = null;
        HttpURLConnection conn = null;
        InputStream inputStream = null;
        Workbook book = null;

        RequestContext res = (RequestContext) map.get("res");
        Long poolId = (Long) map.get("poolId");
        Long accountId = res.getAccountId();
        Long companyId = res.getCompanyId();
        Long siteId = res.getSiteId();
        Date submitTime = new Date();
        TaskContext taskContext = new TaskContext(taskId, serialNo, taskName, accountId, submitTime, siteId, companyId);
        working(taskContext);
        List<ExperienceImportSheetRowVO> rowVOS = new ArrayList<>();
        ExperienceImportSheetRowVO rowVO = null;
        List<String> names = new ArrayList<>();
        List<Integer> errorList = new ArrayList<>();
        List<Integer> noIdList = new ArrayList<>();
        try {
            //解析url
            String fileName = ossUrl.substring(ossUrl.lastIndexOf("/") + 1);
            String ossUrlPrefix = ossUrl.substring(0, ossUrl.lastIndexOf("/") + 1);
            ossUrl = ossUrlPrefix + URLEncoder.encode(fileName, "utf-8");

            url = new URL(ossUrl);
            conn = (HttpURLConnection) url.openConnection();
            //防止屏蔽程序抓取而返回403错误
            conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
            inputStream = conn.getInputStream();
            book = WorkbookFactory.create(inputStream);
        } catch (MalformedURLException e) {
            LOGGER.error("创建ossurl 连接错误", e);
            fail(taskContext, "创建ossurl 连接错误");
        } catch (IOException e) {
            LOGGER.error("创建ossurl 连接错误", e);
            fail(taskContext, "创建ossurl 连接错误");
        } catch (InvalidFormatException e) {
            LOGGER.error("创建Workbook 对象错误", e);
            fail(taskContext, "创建ossurl 连接错误");
        }

        try {
            if (null == book) {
                fail(taskContext, "创建Workbook 对象失败");
            }
            Sheet sheet = book.getSheetAt(0);
            Row row;
            Cell cell0;
            Cell cell1;
            int lastRowNum = sheet.getLastRowNum();
            //获取每行值
            if (lastRowNum > 0) {
                for (int i = 0; i < lastRowNum; i++) {
                    String name = null;
                    Long experience = null;
                    row = sheet.getRow(i + 1);
                    if (null != row) {
                        cell0 = row.getCell(0);
                        cell1 = row.getCell(1);
                        if (null != cell0) {
                            if (cell0.getCellTypeEnum().equals(CellType.STRING)) {
                                name = cell0.getStringCellValue();
                            } else if (cell0.getCellTypeEnum().equals(CellType.NUMERIC)) {
                                cell0.setCellType(CellType.STRING);
                                name = cell0.getStringCellValue();
                            }
                        }
                        try {
                            if (null != cell1) {
                                if (cell1.getCellTypeEnum().equals(CellType.STRING)) {
                                    experience = Long.valueOf(cell1.getStringCellValue());
                                } else if (cell1.getCellTypeEnum().equals(CellType.NUMERIC)) {
                                    experience = (long) cell1.getNumericCellValue();
                                }
                            }
                        } catch (NumberFormatException e2) {
                            int n = i + 2;
                            LOGGER.error("第" + n + "行经验值必须是数字格式", e2);
                        }
                        if (null != name && null != experience) {
                            rowVO = new ExperienceImportSheetRowVO();
                            rowVO.setName(name.trim());
                            rowVO.setExperience(experience);
                            rowVO.setRowNumber(i + 2);
                            rowVOS.add(rowVO);
                            names.add(name.trim());
                        } else {
                            errorList.add(i + 2);
                        }
                    }
                }
            } else {
                success(taskContext, "执行成功,未检索到数据", "");
            }

            //通过用户名找出对应的用户id
            Map<String, Map<String, Object>> nameMap = new HashMap<>();
            if (CollectionUtils.isNotEmpty(names)) {
                AccountNameForIdsModel model = new AccountNameForIdsModel(names, res);
                nameMap = accountClient.getAccountByNames(model, companyId, siteId, "customProject");
            }

            List<ExperienceImportSheetRowVO> list = new ArrayList<>();
            if (null != nameMap && nameMap.size() > 0) {
                for (ExperienceImportSheetRowVO sheetRowVO : rowVOS) {
                    if (nameMap.containsKey(sheetRowVO.getName())) {
                        Map<String, Object> accountMap = new HashMap<>(3);
                        accountMap = nameMap.get(sheetRowVO.getName());
                        sheetRowVO.setAccountId(Long.valueOf(accountMap.get("accountId").toString()));
//                        Integer accountId1 = (Integer) accountMap.get("accountId");
//                        sheetRowVO.setAccountId(Long.valueOf(accountId1));
                        sheetRowVO.setOrgId(Long.valueOf(accountMap.get("orgId").toString()));
                        sheetRowVO.setOrgName((String) accountMap.get("orgName"));
                        list.add(sheetRowVO);
                    } else {
                        noIdList.add(sheetRowVO.getRowNumber());
                    }
                }
            }
            List<StudentExperienceVo> insertList = new ArrayList<>();
            StudentExperienceVo studentExperience = null;
            //建立对象,批量插入对应用户Id的经验
            if (CollectionUtils.isNotEmpty(list)) {
                for (ExperienceImportSheetRowVO rowVO1 : list) {
                    studentExperience = new StudentExperienceVo();
                    studentExperience.setExperiencePoolId(poolId);
                    studentExperience.setAccountId(rowVO1.getAccountId());
                    studentExperience.setAccountName(rowVO1.getName());
                    studentExperience.setCompanyId(companyId);
                    studentExperience.setSiteId(siteId);
                    studentExperience.setOrgId(rowVO1.getOrgId());
                    studentExperience.setOrgName(rowVO1.getOrgName());
                    studentExperience.setCreateById(res.getAccountId());
                    studentExperience.setCreateByName(res.getAccountName());
                    studentExperience.setType(5);
                    studentExperience.setExperience(rowVO1.getExperience());
                    studentExperience.setActivityName("外部导入");
                    insertList.add(studentExperience);
                }
            }
            boolean isOk = false;
            if (CollectionUtils.isNotEmpty(insertList)) {
                if (insertList.size() > 2000) {
                    int n = insertList.size() / 2000;
                    for (int i = 0; i < n; i++) {
                        isOk = studentExperienceClient.insertStudentExperienceList(insertList.subList(2000 * i, 2000 * i + 2000));
                    }
                    if ((insertList.size() % 2000) > 0) {
                        isOk = studentExperienceClient.insertStudentExperienceList(insertList.subList(2000 * n, insertList.size()));
                    }
                } else {
                    isOk = studentExperienceClient.insertStudentExperienceList(insertList);
                }

            }
            if (isOk) {
                Integer n = insertList.size();
                if (CollectionUtils.isNotEmpty(errorList) && (CollectionUtils.isNotEmpty(noIdList))) {
                    success(taskContext, "成功导入" + n + "条,第" + errorList.toString() + "行导入格式有误;第" + noIdList.toString() + "行未找到相应用户", ossUrl);
                } else if (CollectionUtils.isNotEmpty(errorList)) {
                    success(taskContext, "成功导入" + n + "条,第" + errorList.toString() + "行导入格式有误", ossUrl);
                } else if (CollectionUtils.isNotEmpty(noIdList)) {
                    success(taskContext, "成功导入" + n + "条,第" + noIdList.toString() + "行未找到相应用户", ossUrl);
                } else if (CollectionUtils.isEmpty(errorList) && CollectionUtils.isEmpty(noIdList)) {
                    success(taskContext, "成功导入" + n + "条,失败0条", ossUrl);
                }
            } else {
                if (CollectionUtils.isNotEmpty(errorList) && (CollectionUtils.isNotEmpty(noIdList))) {
                    fail(taskContext, "成功导入" + 0 + "条,第" + errorList.toString() + "行导入格式有误;第" + noIdList.toString() + "行未找到相应用户");
                } else if (CollectionUtils.isNotEmpty(errorList)) {
                    fail(taskContext, "成功导入" + 0 + "条,第" + errorList.toString() + "行导入格式有误");
                } else if (CollectionUtils.isNotEmpty(noIdList)) {
                    fail(taskContext, "成功导入" + 0 + "条,第" + noIdList.toString() + "行未找到相应用户");
                } else if (CollectionUtils.isEmpty(errorList) && CollectionUtils.isEmpty(noIdList)) {
                    fail(taskContext, "成功导入0条,未检索到相关数据 ");
                }
            }
        } catch (Exception e1) {
            LOGGER.error("外部经验导入过程出现错误:", e1);
            fail(taskContext, "外部经验导入过程出现错误");
        }
        return "ok";
    }
}
