package com.webclient.beans.udjobstand;

import com.alibaba.fastjson.JSONObject;
import com.webclient.beans.udbudgetTask.AssetCFImportData;
import com.webclient.beans.udpr.ImpExcelTool;
import org.apache.poi.ss.usermodel.*;
import psdi.mbo.MboRemote;
import psdi.mbo.MboSetRemote;
import psdi.server.MXServer;
import psdi.util.MXApplicationException;
import psdi.util.MXException;
import psdi.webclient.system.beans.DataBean;
import psdi.webclient.system.controller.MPFormData;
import psdi.webclient.system.session.WebClientSession;

import javax.servlet.http.HttpSession;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.rmi.RemoteException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Logger;

/**
 * 批量导入作业标准
 * edit 2023-11-16
 */
public class UdJobstandImportDataBean extends DataBean{


    static Logger logger = Logger.getLogger(AssetCFImportData.class.getName());
    public static WebClientSession wcs;
    public static HttpSession mxSession;
    public static MboRemote mainMbo;
    public static MPFormData mpData;

    public void importExecute() throws MXException, RemoteException {
        wcs = this.clientSession;
        mpData = ImpExcelTool.getInstance().checkExcelType(wcs); // 校验文件正确性
        mxSession = wcs.getRequest().getSession();
        if (null == mxSession.getAttribute("mxClientSession")) {
            mxSession.setAttribute("mxClientSession", wcs);
        }
        if (this.app != null && this.app.getAppBean() != null) {
            mxSession.setAttribute("appMainMbo", this.app.getAppBean().getMbo());
        }
        mxSession.setAttribute("mpData", mpData);
        wcs.getResponse().setContentType("text/html; charset=UTF-8"); // 转码
        try {
            // 读取文件
            byte[] b = mpData.getFileOutputStream().toByteArray();
            InputStream ips = new ByteArrayInputStream(b);
            Workbook workBook= WorkbookFactory.create(ips);
            Sheet sheet = workBook.getSheetAt(0);
            int beginRowIndex = 3;// 处理数据的开始行，默认从0开始
            int beginColIndex = 0;// 处理数据开始列，默认从0开始
            // 验证EXCEL中的数据
            String result=validDataAndInsert(sheet, beginRowIndex,beginColIndex);
            JSONObject resObj = (JSONObject) JSONObject.parse(result);
            String code = resObj.getString("code");
            if(resObj!=null && code !=null && !"".equals(code)){
                if("10000".equals(code)){
                    app.getAppBean().refreshTable();
                    app.getAppBean().reloadTable();
                    app.getAppBean().fireDataChangedEvent();
                    app.getAppBean().fireStructureChangedEvent();
                    this.app.getDataBean("results_showlist").reloadTable();
                    this.app.getDataBean("results_showlist").refreshTable();
                    wcs.showMessageBox("commoninfo", "commoninfo", new String[]{"导入成功!"});
//                    app.getAppBean().save();

                }else{
                    wcs.showMessageBox("commoninfo", "commoninfo_err", new String[]{resObj.getString("msg")});
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            wcs.showMessageBox("commoninfo", "commoninfo_err2", new String[]{e.getMessage()});
        } finally {
            System.gc();
        }

    }

    public String validDataAndInsert(Sheet sheet, int beginRowIndex, int beginColIndex) throws RemoteException, MXException, ParseException {
        JSONObject result = new JSONObject();
        int rowi=1;
        try {
            // 主单编号
            int totalRows = sheet.getLastRowNum(); // 该excel表的总行数
            //主表
            MboSetRemote udjobstandSet = MXServer.getMXServer().getMboSet("UDJOBSTAND",MXServer.getMXServer().getSystemUserInfo());
            MboSetRemote l_udjobstandSet = MXServer.getMXServer().getMboSet("L_UDJOBSTAND",MXServer.getMXServer().getSystemUserInfo());
            //作业标准任务子表
            MboSetRemote udjobstandlineSet = MXServer.getMXServer().getMboSet("UDJOBSTANDLINE",MXServer.getMXServer().getSystemUserInfo());
            MboSetRemote l_udjobstandlineSet = MXServer.getMXServer().getMboSet("L_UDJOBSTANDLINE",MXServer.getMXServer().getSystemUserInfo());
            //执行组 物料 子表
            MboSetRemote udjobzigfSet = MXServer.getMXServer().getMboSet("UDJOBZIGF",MXServer.getMXServer().getSystemUserInfo());
            String num="";//序号
            String zybzh="";//作业标准号
            Date curdate=new Date();
            for (int i = beginRowIndex; i <= totalRows; i++) {
//                System.out.println("当前插入行：：" + i + "/" + totalRows);
                Row row = sheet.getRow(i);
                // 判断何时结束循环
                if (row == null || null==row.getCell(2)) {
                    break;
                }
                rowi=row.getRowNum()+1;
                try{
                //序号和作业标准号都为空时，则不允许导入
                if(("".equalsIgnoreCase(getCellString(row, 0)) || null==getCellString(row, 0) ) &&
                        ("".equalsIgnoreCase(getCellString(row, 1)) || null==getCellString(row, 1)) ){
                    result.put("code", "10001");
                    result.put("msg", "In row "+rowi+" of excel.The serial number and job standard number cannot be empty at the same time. ");
                    return result.toString();
                }else if(("".equalsIgnoreCase(getCellString(row, 0)) || null==getCellString(row, 0)) &&
                        (!"".equalsIgnoreCase(getCellString(row, 1)) || null!=getCellString(row, 1))){
                    //更新作业标准，依据芦占占要求更新时先清除子表所有数据后，再覆盖
                    String sql="UDJOBSTANDNUM='"+getCellString(row, 1)+"' AND ORGID='"+getCellString(row, 5)+"' ";
                    udjobstandSet.setWhere(sql);
                    udjobstandSet.reset();
                    if(null!=udjobstandSet && udjobstandSet.count()>0 && !udjobstandSet.isEmpty()){
                        MboRemote udjob2Mbo=udjobstandSet.getMbo(0);
                        int udjobstandid=udjob2Mbo.getInt("UDJOBSTANDID");

                        if(!zybzh.equalsIgnoreCase(getCellString(row, 1))){
                            zybzh=udjob2Mbo.getString("UDJOBSTANDNUM");//作业标准号

                            if(!"".equalsIgnoreCase(getCellString(row, 13)) && null!=getCellString(row, 13)){
                                MboSetRemote zybzSet=udjob2Mbo.getMboSet("$UDJOBSTANDLINE","UDJOBSTANDLINE","PARENTID="+udjobstandid+"");
                                if(null!=zybzSet && zybzSet.count()>0){
                                    zybzSet.deleteAll(2L);
                                    zybzSet.save();
                                }
                            }
                            if(!"".equalsIgnoreCase(getCellString(row, 18)) && null!=getCellString(row, 18)){
                                MboSetRemote zxzSet=udjob2Mbo.getMboSet("$UDJOBZIGF_ZXZ","UDJOBZIGF","type=1 and parentid="+udjobstandid+"");
                                if(null!=zxzSet && zxzSet.count()>0){
                                    zxzSet.deleteAll(2L);
                                    zxzSet.save();
                                }
                            }
                            if(!"".equalsIgnoreCase(getCellString(row, 19)) && null!=getCellString(row, 19)){
                                MboSetRemote wlSet=udjob2Mbo.getMboSet("$UDJOBZIGF_WL","UDJOBZIGF","type=2 and parentid="+udjobstandid+"");
                                if(null!=wlSet && wlSet.count()>0){
                                    wlSet.deleteAll(2L);
                                    wlSet.save();
                                }
                            }
                        }

                        //作业标准任务子表
                        if(!"".equalsIgnoreCase(getCellString(row, 13)) && null!=getCellString(row, 13)){

                            MboRemote udjobsLineMbo=udjobstandlineSet.add();
                            udjobsLineMbo.setValue("PARENTID", udjobstandid, 2L);
                            udjobsLineMbo.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobsLineMbo.setValue("NUM", getCellString(row, 13), 2L);
                            udjobsLineMbo.setValue("TASKID", getCellString(row, 14), 2L);
                            udjobsLineMbo.setValue("LANGCODE", "EN", 2L);
                            udjobsLineMbo.setValue("DESCRIPTION", getCellString(row, 16), 2L);
                            udjobsLineMbo.getThisMboSet().save();
                            udjobsLineMbo.getThisMboSet().close();

                            MboRemote l_fcMbo=l_udjobstandlineSet.add();
                            l_fcMbo.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                            l_fcMbo.setValue("LANGCODE", "ZH", 2L);
                            l_fcMbo.setValue("DESCRIPTION", getCellString(row, 15), 2L);
                            l_fcMbo.getThisMboSet().save();
                            l_fcMbo.getThisMboSet().close();

                            MboRemote l_fcMboFr=l_udjobstandlineSet.add();
                            l_fcMboFr.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                            l_fcMboFr.setValue("LANGCODE", "FR", 2L);
                            l_fcMboFr.setValue("DESCRIPTION", getCellString(row, 17), 2L);
                            l_fcMboFr.getThisMboSet().save();
                            l_fcMboFr.getThisMboSet().close();
                        }

                        //执行组
                        if(!"".equalsIgnoreCase(getCellString(row, 18)) && null!=getCellString(row, 18)){

                            MboRemote udjobzMbo1=udjobzigfSet.add();
                            udjobzMbo1.setValue("TYPE", 1, 2L);
                            udjobzMbo1.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobzMbo1.setValue("PARENTID", udjobstandid, 2L);
                            udjobzMbo1.setValue("ZXZ", getCellString(row, 18), 2L);
                            udjobzMbo1.getThisMboSet().save();
                            udjobzMbo1.getThisMboSet().close();
                        }

                        //物料
                        if(!"".equalsIgnoreCase(getCellString(row, 19)) && null!=getCellString(row, 19)){

                            MboRemote udjobzMbo2=udjobzigfSet.add();
                            udjobzMbo2.setValue("TYPE", 2, 2L);
                            udjobzMbo2.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobzMbo2.setValue("PARENTID", udjobstandid, 2L);
                            udjobzMbo2.setValue("NUM", getCellString(row, 19), 2L);
                            udjobzMbo2.setValue("ITEMNUM", getCellString(row, 20), 2L);
                            udjobzMbo2.setValue("QUANTITY", getCellString(row, 21), 2L);
                            udjobzMbo2.getThisMboSet().save();
                            udjobzMbo2.getThisMboSet().close();
                        }

                        result.put("code", "10000");
                        result.put("msg", "success");
                    }

                }else if((!"".equalsIgnoreCase(getCellString(row, 0)) || null!=getCellString(row, 0)) &&
                        ("".equalsIgnoreCase(getCellString(row, 1)) || null==getCellString(row, 1))){
                    //新增作业标准
                    if(("".equalsIgnoreCase(zybzh) || null==zybzh) || (!num.equalsIgnoreCase(getCellString(row, 0)))){
                        //作业标准主表新增
                        MboRemote udjobMbo=udjobstandSet.add();
                        udjobMbo.setValue("ORGID", getCellString(row, 5), 2L);
                        udjobMbo.setValue("DESCRIPTION", getCellString(row, 3), 2L);
                        udjobMbo.setValue("LANGCODE", "EN", 2L);
                        udjobMbo.setValue("CREATEBY", getCellString(row, 6), 2L);
                        udjobMbo.setValue("CREATEDATE", curdate, 2L);
                        udjobMbo.setValue("TEMPLATEID", getCellString(row, 7), 2L);
                        udjobMbo.setValue("CFTYPE", getCellString(row, 8), 2L);
                        udjobMbo.setValue("FREQUENCY", getCellString(row, 9), 2L);//计量频率
                        udjobMbo.setValue("FREQUNIT", getCellString(row, 10), 2L);//计量频率单位
                        udjobMbo.setValue("TIMEFRECY", getCellString(row, 11), 2L);//时间频率
                        udjobMbo.setValue("TIMEFRECYUNIT", getCellString(row, 12), 2L);//时间频率单位
                        udjobMbo.setValue("STATUS", "ACTIVE", 2L);//状态根据芦占占要求20240603导入即活动状态
                        udjobMbo.getThisMboSet().save();
                        udjobMbo.getThisMboSet().close();

                        num=getCellString(row, 0);//序号
                        zybzh=udjobMbo.getString("UDJOBSTANDNUM");//作业标准号

                        MboRemote l_fcMbo=l_udjobstandSet.add();
                        l_fcMbo.setValue("OWNERID", udjobMbo.getInt("UDJOBSTANDID"), 2L);
                        l_fcMbo.setValue("LANGCODE", "ZH", 2L);
                        l_fcMbo.setValue("DESCRIPTION", getCellString(row, 2), 2L);
                        l_fcMbo.getThisMboSet().save();
                        l_fcMbo.getThisMboSet().close();

                        MboRemote l_fcMboFr=l_udjobstandSet.add();
                        l_fcMboFr.setValue("OWNERID", udjobMbo.getInt("UDJOBSTANDID"), 2L);
                        l_fcMboFr.setValue("LANGCODE", "FR", 2L);
                        l_fcMboFr.setValue("DESCRIPTION", getCellString(row, 4), 2L);
                        l_fcMboFr.getThisMboSet().save();
                        l_fcMboFr.getThisMboSet().close();

                        //作业标准任务子表新增
                        if(!"".equalsIgnoreCase(getCellString(row, 13)) && null!=getCellString(row, 13)){
                            MboRemote udjobsLineMbo=udjobstandlineSet.add();
                            udjobsLineMbo.setValue("PARENTID", udjobMbo.getInt("UDJOBSTANDID"), 2L);
                            udjobsLineMbo.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobsLineMbo.setValue("NUM", getCellString(row, 13), 2L);
                            udjobsLineMbo.setValue("TASKID", getCellString(row, 14), 2L);
                            udjobsLineMbo.setValue("LANGCODE", "EN", 2L);
                            udjobsLineMbo.setValue("DESCRIPTION", getCellString(row, 16), 2L);
                            udjobsLineMbo.getThisMboSet().save();
                            udjobsLineMbo.getThisMboSet().close();

                            MboRemote l_fclineMbo=l_udjobstandlineSet.add();
                            l_fclineMbo.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                            l_fclineMbo.setValue("LANGCODE", "ZH", 2L);
                            l_fclineMbo.setValue("DESCRIPTION", getCellString(row, 15), 2L);
                            l_fclineMbo.getThisMboSet().save();
                            l_fclineMbo.getThisMboSet().close();

                            MboRemote l_fclineMboFr=l_udjobstandlineSet.add();
                            l_fclineMboFr.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                            l_fclineMboFr.setValue("LANGCODE", "FR", 2L);
                            l_fclineMboFr.setValue("DESCRIPTION", getCellString(row, 17), 2L);
                            l_fclineMboFr.getThisMboSet().save();
                            l_fclineMboFr.getThisMboSet().close();
                        }

                        if(!"".equalsIgnoreCase(getCellString(row, 18)) && null!=getCellString(row, 18)){
                            //执行组
                            MboRemote udjobzMbo1=udjobzigfSet.add();
                            udjobzMbo1.setValue("TYPE", 1, 2L);
                            udjobzMbo1.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobzMbo1.setValue("PARENTID", udjobMbo.getInt("UDJOBSTANDID"), 2L);
                            udjobzMbo1.setValue("ZXZ", getCellString(row, 18), 2L);
                            udjobzMbo1.getThisMboSet().save();
                            udjobzMbo1.getThisMboSet().close();
                        }

                        if(!"".equalsIgnoreCase(getCellString(row, 19)) && null!=getCellString(row, 19)){
                            //物料
                            MboRemote udjobzMbo2=udjobzigfSet.add();
                            udjobzMbo2.setValue("TYPE", 2, 2L);
                            udjobzMbo2.setValue("ORGID", getCellString(row, 5), 2L);
                            udjobzMbo2.setValue("PARENTID", udjobMbo.getInt("UDJOBSTANDID"), 2L);
                            udjobzMbo2.setValue("NUM", getCellString(row, 19), 2L);
                            udjobzMbo2.setValue("ITEMNUM", getCellString(row, 20), 2L);
                            udjobzMbo2.setValue("QUANTITY", getCellString(row, 21), 2L);
                            udjobzMbo2.getThisMboSet().save();
                            udjobzMbo2.getThisMboSet().close();
                        }

                        result.put("code", "10000");
                        result.put("msg", "success");
                    }else if(num.equalsIgnoreCase(getCellString(row, 0))){
                        String sql="UDJOBSTANDNUM='"+zybzh+"' AND ORGID='"+getCellString(row, 5)+"' ";
                        udjobstandSet.setWhere(sql);
                        udjobstandSet.reset();
                        if(null!=udjobstandSet && udjobstandSet.count()>0){
                            MboRemote udjob2Mbo=udjobstandSet.getMbo(0);
                            int udjobstandid=udjob2Mbo.getInt("UDJOBSTANDID");

                            //作业子表任务新增
                            if(!"".equalsIgnoreCase(getCellString(row, 13)) && null!=getCellString(row, 13)){
                                MboRemote udjobsLineMbo=udjobstandlineSet.add();
                                udjobsLineMbo.setValue("PARENTID", udjobstandid, 2L);
                                udjobsLineMbo.setValue("ORGID", getCellString(row, 5), 2L);
                                udjobsLineMbo.setValue("NUM", getCellString(row, 13), 2L);
                                udjobsLineMbo.setValue("TASKID", getCellString(row, 14), 2L);
                                udjobsLineMbo.setValue("LANGCODE", "EN", 2L);
                                udjobsLineMbo.setValue("DESCRIPTION", getCellString(row, 16), 2L);
                                udjobsLineMbo.getThisMboSet().save();
                                udjobsLineMbo.getThisMboSet().close();

                                MboRemote l_fcMbo=l_udjobstandlineSet.add();
                                l_fcMbo.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                                l_fcMbo.setValue("LANGCODE", "ZH", 2L);
                                l_fcMbo.setValue("DESCRIPTION", getCellString(row, 15), 2L);
                                l_fcMbo.getThisMboSet().save();
                                l_fcMbo.getThisMboSet().close();

                                MboRemote l_fcMboFr=l_udjobstandlineSet.add();
                                l_fcMboFr.setValue("OWNERID", udjobsLineMbo.getInt("UDJOBSTANDLINEID"), 2L);
                                l_fcMboFr.setValue("LANGCODE", "FR", 2L);
                                l_fcMboFr.setValue("DESCRIPTION", getCellString(row, 17), 2L);
                                l_fcMboFr.getThisMboSet().save();
                                l_fcMboFr.getThisMboSet().close();
                            }

                            if(!"".equalsIgnoreCase(getCellString(row, 18)) && null!=getCellString(row, 18)){
                                //执行组
                                MboRemote udjobzMbo1=udjobzigfSet.add();
                                udjobzMbo1.setValue("TYPE", 1, 2L);
                                udjobzMbo1.setValue("ORGID", getCellString(row, 5), 2L);
                                udjobzMbo1.setValue("PARENTID", udjobstandid, 2L);
                                udjobzMbo1.setValue("ZXZ", getCellString(row, 18), 2L);
                                udjobzMbo1.getThisMboSet().save();
                                udjobzMbo1.getThisMboSet().close();
                            }

                            if(!"".equalsIgnoreCase(getCellString(row, 19)) && null!=getCellString(row, 19)){
                                //物料
                                MboRemote udjobzMbo2=udjobzigfSet.add();
                                udjobzMbo2.setValue("TYPE", 2, 2L);
                                udjobzMbo2.setValue("ORGID", getCellString(row, 5), 2L);
                                udjobzMbo2.setValue("PARENTID", udjobstandid, 2L);
                                udjobzMbo2.setValue("NUM", getCellString(row, 19), 2L);
                                udjobzMbo2.setValue("ITEMNUM", getCellString(row, 20), 2L);
                                udjobzMbo2.setValue("QUANTITY", getCellString(row, 21), 2L);
                                udjobzMbo2.getThisMboSet().save();
                                udjobzMbo2.getThisMboSet().close();
                            }

                            result.put("code", "10000");
                            result.put("msg", "success");
                        }

                    }

                }else{
                    result.put("code", "10001");
                    result.put("msg", "In row "+rowi+" of excel.Serial number and work standards cannot be filled in at the same time. ");
                    return result.toString();
                }

            }catch (Exception e){
                    result.put("code", "10001");
                    result.put("msg", "In row "+rowi+" of excel."+e.getMessage());
                }
            }
        }catch (Exception e){
            e.getMessage();
            e.getCause();
            result.put("code", "10001");
            result.put("msg", "In row "+rowi+" of excel."+e.getMessage());
        }
        return result.toString();
    }


    private String getCellString(Row row, int i) throws MXApplicationException {
        Cell cell = row.getCell(i);
        String cellStr = "";
        if (cell == null) {
            return cellStr;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cellStr = cell.getRichStringCellValue().getString().trim();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) { // 自定义的日期类型
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date date = cell.getDateCellValue();
                cellStr = sdf.format(date).trim();
            } else {
                NumberFormat nf = NumberFormat.getInstance();
                nf.setGroupingUsed(false); // 设置数字格式，不自动用科学计数法
                if (i == 15 || i == 18 || i == 19 || i == 21 || i == 23 || i == 47) {
                    Integer value = (int) cell.getNumericCellValue(); // 整数
                    cellStr = nf.format(value).trim();
                } else {
                    double value = cell.getNumericCellValue(); // 小数
                    cellStr = nf.format(value).trim();
                }
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { // 公式
            cellStr = cell.getCellFormula().trim();
        } else {
            cellStr = cell.getRichStringCellValue().getString().trim();
        }
        return cellStr;
    }

}