package com.webclient.beans.udpr;
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;

import javax.servlet.http.HttpSession;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


import org.python.antlr.ast.Break;
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;

/**
 * 车间需求申请导入pr行
 * @author lip
 * @date 2023-09-12 21:35
 *
 */
public class PrlineImportData extends DataBean{
    static Logger logger = Logger.getLogger(PrlineImportData.class.getName());
    public static WebClientSession wcs;
    public static HttpSession mxSession;
    public static MboRemote mainMbo;
    public static MPFormData mpData;

    public void importExecute() throws MXException, RemoteException {
        logger.info("开始执行导入数据操作!");
        wcs = this.clientSession;
        mpData = ImpExcelTool.getInstance().checkExcelType(wcs); // 校验文件正确性
        mxSession = wcs.getRequest().getSession();
        String str[]={"导入成功!"};
        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 {
            // MboRemote mainMbo = this.app.getAppBean().getMbo();
            mainMbo = (MboRemote) mxSession.getAttribute("appMainMbo");
            String status=mainMbo.getString("STATUS");
            if(!"WAPPR".equalsIgnoreCase(status)){
                wcs.showMessageBox("prline", "prline_statusno", str);
                return;
            }

            //以上代码校验是否是文件通过

            //以下验证数据及导入数据

            // 读取文件
            byte[] b = mpData.getFileOutputStream().toByteArray();
            InputStream ips = new ByteArrayInputStream(b);
            Workbook workBook=WorkbookFactory.create(ips);
            Sheet sheet = workBook.getSheetAt(0);
            int beginRowIndex = 2;// 处理数据的开始行，默认从0开始
            int beginColIndex = 0;// 处理数据开始列，默认从0开始
            String tableName = "PRLINE"; //要处理的表名 需求计划明细项子表
            // 验证EXCEL中的数据
            boolean vailFlag=validDataAndInsert(sheet, beginRowIndex,mainMbo, wcs);
            if(vailFlag){
                wcs.showMessageBox("commoninfo", "commoninfo", str);
                String tableId = this.creatingEvent.getTargetId();
                DataBean tableBean = this.app.getDataBean(tableId);
                tableBean.reset();
                tableBean.refreshTable();
                app.getAppBean().save();
                app.getAppBean().refreshTable();
                app.getAppBean().reloadTable();
                app.getAppBean().fireDataChangedEvent();
                app.getAppBean().fireStructureChangedEvent();

            }else {
                String str1[]={"导入失败,请检查数据真实性或联系管理员核实!"};
                wcs.showMessageBox("commoninfo", "commoninfo_err", str1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            System.gc();
        }

    }
    public boolean validDataAndInsert(Sheet sheet, int beginRowIndex,
                                      MboRemote mainMbo, WebClientSession wcs) throws RemoteException, MXException, ParseException {
        boolean vailFlag=false;
        try {
            // 主单编号
            int totalRows = sheet.getLastRowNum(); // 该excel表的总行数
            MboSetRemote udAchievlineSet = mainMbo.getMboSet("&UDACHIEVELINE","PRLINE","1=2");
            for (int i = beginRowIndex; i <= totalRows; i++) {
//                System.out.println("当前插入行：：" + i + "/" + totalRows);
                Row row = sheet.getRow(i);
                // 判断何时结束循环
                if (row == null || null==row.getCell(0)) {
                    break;
                }
                String itemnum=getCellString(row, 0);
                int iCount=mainMbo.getMboSet("$ITEM", "ITEM", "ITEMNUM='"+itemnum+"'").count();
                if(iCount<=0){
                    wcs.showMessageBox("prlineimp", "prlineimp_item", new String[]{itemnum});
                    return false;
                }
                String orderqtyCell=getCellString(row, 2);
                if(!orderqtyCell.matches("-?\\d+") || !orderqtyCell.matches("-?\\d+(\\.\\d+)?")){
                    String str1[]={orderqtyCell};
                    wcs.showMessageBox("prlineimp", "prlineimp_orde", str1);
                    return false;
                }
                String remarks=getCellString(row, 4);
                if(!"".equalsIgnoreCase(remarks) && null!=remarks){
                    if(remarks.length()>=1300){
                        String str1[]={remarks};
                        wcs.showMessageBox("prlineimp", "prlineimp_len", str1);
                        return false;
                    }
                }

                String sqlstr="itemnum='"+itemnum+"' and prnum='"+mainMbo.getString("prnum")+"' and siteid='"+mainMbo.getString("SITEID")+"' " +
                        "and orgid='"+mainMbo.getString("ORGID")+"'";
                MboSetRemote prlineSet = MXServer.getMXServer().getMboSet("PRLINE", MXServer.getMXServer().getSystemUserInfo());
                prlineSet.setWhere(sqlstr);
                prlineSet.reset();
                if(null!=prlineSet && !prlineSet.isEmpty()){
                    prlineSet.getMbo(0).setValue("ORDERQTY", getCellString(row, 2),2L);
                    prlineSet.getMbo(0).setValue("APPRQUANTITY", getCellString(row, 2),2L);
                    prlineSet.save();
                }else{
                    int prlinenum=0;
                    String sql2str="prnum='"+mainMbo.getString("prnum")+"' and siteid='"+mainMbo.getString("SITEID")+"' " +
                            "and orgid='"+mainMbo.getString("ORGID")+"'";
                    MboSetRemote prlineSet2 = MXServer.getMXServer().getMboSet("PRLINE", MXServer.getMXServer().getSystemUserInfo());
                    prlineSet2.setWhere(sql2str);
                    prlineSet2.reset();
                    if(null!=prlineSet2 && !prlineSet2.isEmpty()){
                        prlinenum= (int) prlineSet2.max("PRLINENUM");
                    }
                    MboRemote prlineMbo=udAchievlineSet.add();
                    if(null!=prlineMbo){
                        //关联值
                        //prnum=:prnum and siteid=:siteid
                        prlineMbo.setValue("prnum", mainMbo.getString("prnum"),2L);
                        prlineMbo.setValue("SITEID", mainMbo.getString("SITEID"),2L);
                        prlineMbo.setValue("ORGID", mainMbo.getString("ORGID"),2L);
                        prlineMbo.setValue("PRLINENUM", prlinenum+1,2L);//序号
                        prlineMbo.setValue("ITEMNUM", getCellString(row, 0),2L);
                        prlineMbo.setValue("ORDERQTY", getCellString(row, 2),2L);
                        prlineMbo.setValue("APPRQUANTITY", getCellString(row, 2),2L);
                        prlineMbo.setValue("CONVERSION",1,2L);
                        prlineMbo.setValue("REMARKS",getCellString(row, 4),2L);
                        prlineMbo.getThisMboSet().save();
                    }
                }
                vailFlag=true;
            }
        }catch (Exception e){
            e.getMessage();
            e.getCause();
            vailFlag=false;
        }
        return vailFlag;
    }
    /**
     *
     * @param row
     * @param i
     * @return
     * @throws MXApplicationException
     */
    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;
    }

}