package com.cnd3b.restcontroller.system.system;

import com.alibaba.fastjson.JSONObject;
import com.cnd3b.common.Controller;
import com.cnd3b.common.D3bException;
import com.cnd3b.common.parameter.parameter;
import jxl.Cell;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.glassfish.jersey.media.multipart.FormDataContentDisposition;
import p2.pao.PaoRemote;
import p2.pao.PaoSetRemote;
import p2.util.P2Exception;

import java.io.File;
import java.io.InputStream;
import java.util.Calendar;
import java.util.HashMap;

public class uploadExcelData extends Controller {

    /**
     * excel临时文件夹存放地
     */
    //public static String excel_file = "/Users/shenjingwei/Documents/代码开发/idea/DSB_CRM/p2-4.1.0_201907241548/deploy/build/pic.war/";
    public uploadExcelData(JSONObject content) {
        super(content);
    }

    public String upLoadExcel(InputStream uploadfileInputStream, FormDataContentDisposition uploadfile, String fdatatype) {
        // 获取文件名称
        JSONObject resultObject = new JSONObject();
        if (uploadfile == null) {
            return getErrReturnObject().setErrMsg("文件为空").toString();
        }
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            workbook = Workbook.getWorkbook(uploadfileInputStream);
            String filename = fdatatype + Calendar.getInstance().getTimeInMillis() + "_err.xls";
            File errfile = new File(parameter.UpLoadExcelErrFilePath() + "\\" + filename);
            writableWorkbook = Workbook.createWorkbook(errfile, workbook);
            /**
             * 如果返回结果不为空，则表示导入错误，需生成excel供用户下载
             */
            if (!uploadData(fdatatype, writableWorkbook)) {
                writableWorkbook.write();
                errfile.exists();
                return getErrReturnObject().setErrMsg("导入错误，是否下载错误信息").addKeyValue("errfileurl", "https://cnd3b.com/uploadexcelerrfile/" + filename).toString();
            } else {
                errfile.delete();
                errfile.exists();
                return getSucReturnObject().toString();
            }

        } catch (Exception e) {
            e.printStackTrace();
            return getErrReturnObject().setErrMsg(e.getMessage()).toString();
        } finally {
            try {
                workbook.close();
                writableWorkbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private boolean uploadData(String fdatatype, WritableWorkbook workbook) {
        switch (fdatatype) {
            case "uploadagents": {
                return uploadData_agents(workbook);
            }
            case "uploadproducts": {
                return uploadData_products(workbook);
            }
            default:
                return false;
        }
    }

    /**
     * 经销商导入
     *
     * @param workbook
     */
    private boolean uploadData_agents(WritableWorkbook workbook) {
        PaoSetRemote tagents = null;
        boolean iserr = false;
        try {
            tagents = getP2ServerSystemPaoSet("tagents", "siteid='" + siteid + "'");
            /**
             * 将现有经销商信息遍历并存入map
             */
            HashMap<String, PaoRemote> paomap = new HashMap<>();
            int i = 0;
            while (tagents.getPao(i) != null) {
                paomap.put(tagents.getPao(i).getString("fagentnum"), tagents.getPao(i));
                i++;
            }
            /**
             *遍历excel，并将数据存入经销商表
             */
            WritableSheet sheet = workbook.getSheet(0);
            for (int rowno = 1; rowno < sheet.getRows(); rowno++) {
                Cell[] cells = sheet.getRow(rowno);
                int celcount = cells.length;
                if (celcount == 0) {
                    break;
                }
                int colno = 0;
                try {
                    PaoRemote agent = null;
                    String fagentnum = cells[colno++].getContents();

                    if (fagentnum.equals("")) {
                        throw new D3bException("经销商编号不可为空");
                    }
                    /**
                     * 判断经销商编号是否已存在，如果已存在获取数据，如不存在则新增数据
                     */
                    if (paomap.containsKey(fagentnum)) {
                        agent = paomap.get(fagentnum);
                    } else {
                        agent = tagents.addAtEnd();
                        paomap.put(fagentnum, agent);
                        /**
                         * 更新系统信息
                         */
                        agent.setValue("siteid", siteid);
                        agent.setValue("createby", username);
                        agent.setValue("createdate", getDateTime());
                    }
                    /**
                     * 更新系统信息
                     */
                    agent.setValue("changeby", username);
                    agent.setValue("changedate", getDateTime());

                    /**
                     * 开始更新字段信息
                     */
                    if (celcount > 0) {
                        agent.setValue("fagentnum", fagentnum);
                    }
                    if (celcount > 1) {
                        agent.setValue("fagentshortname", cells[colno++].getContents());
                    }
                    if (celcount > 2) {
                        agent.setValue("fagentname", cells[colno++].getContents());
                    }
                    if (celcount > 3) {
                        agent.setValue("fprovince", cells[colno++].getContents());
                    }
                    if (celcount > 4) {
                        agent.setValue("fcity", cells[colno++].getContents());
                    }
                    if (celcount > 5) {
                        agent.setValue("fcounty", cells[colno++].getContents());
                    }
                    if (celcount > 6) {
                        agent.setValue("faddress", cells[colno++].getContents());
                    }
                    if (celcount > 7) {
                        agent.setValue("fcooperationstatus", cells[colno++].getContents());
                    }
                    if (celcount > 8) {
                        agent.setValue("fcontact", cells[colno++].getContents());
                    }
                    if (celcount > 9) {
                        agent.setValue("fphonenumber", cells[colno++].getContents());
                    }
                    if (celcount > 10) {
                        agent.setValue("fsaleruserid", cells[colno++].getContents());
                    }
                    if (celcount > 11) {
                        agent.setValue("ftype", cells[colno++].getContents());
                    }
                } catch (P2Exception e) {
                    e.printStackTrace();
                    /**
                     * 如果插入遇到错误，则在行最末尾插入错误信息
                     */
                    iserr = true;
                    Label label = new Label(colno++, rowno, e.getMessage());
                    WritableFont titleWf = new WritableFont(WritableFont.createFont("宋体"),// 字体
                            12,//WritableFont.DEFAULT_POINT_SIZE,   // 字号
                            WritableFont.NO_BOLD,                  // 粗体
                            false,                                 // 斜体
                            UnderlineStyle.NO_UNDERLINE,            // 下划线
                            Colour.RED,                       // 字体颜色
                            ScriptStyle.NORMAL_SCRIPT);
                    label.setCellFormat(new WritableCellFormat(titleWf));
                    sheet.addCell(label);
                }
            }
            /**
             * 如果更新期间没有遇到错误，则进行数据保存,否则清除数据
             */
            if (!iserr) {
                tagents.save();
            } else {
                tagents.clear();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                tagents.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return !iserr;
    }

    /**
     * 经销商商品导入
     *
     * @param workbook
     */
    private boolean uploadData_products(WritableWorkbook workbook) {
        PaoSetRemote tagents_productSet = null;
        boolean iserr = false;
        try {
            tagents_productSet = getP2ServerSystemPaoSet("tagents_product", "siteid='" + siteid + "' and tagentsid=" + tagentsid);
            /**
             * 将现有商品信息遍历并存入map
             */
            HashMap<String, PaoRemote> paomap = new HashMap<>();
            /**
             *遍历excel，并将数据存入经销商表
             */
            WritableSheet sheet = workbook.getSheet(0);
            for (int rowno = 1; rowno < sheet.getRows(); rowno++) {
                Cell[] cells = sheet.getRow(rowno);
                int celcount = cells.length;
                if (celcount == 0) {
                    break;
                }
                int colno = 0;
                try {
                    PaoRemote tagents_product = null;
                    String fprodnum = cells[colno++].getContents();
                    String fprodname = cells[colno++].getContents();

                    if (fprodnum.equals("") && fprodname.equals("")) {
                        throw new D3bException("商品编号、商品名称不可同时为空");
                    }
                    /**
                     * 判断商品编号是否已存在，如果已存在获取数据，如不存在则新增数据
                     */
                    if (paomap.containsKey(fprodnum)) {
                        tagents_product = paomap.get(fprodnum);
                    } else {
                        tagents_product = tagents_productSet.addAtEnd();
                        paomap.put(fprodnum, tagents_product);
                        /**
                         * 更新系统信息
                         */
                        tagents_product.setValue("siteid", siteid);
                        tagents_product.setValue("createby", username);
                        tagents_product.setValue("createdate", getDateTime());
                        tagents_product.setValue("tagentsid", tagentsid);
                    }
                    tagents_product.setValue("changeby", username);
                    tagents_product.setValue("changedate", getDateTime());
                    /**
                     * 开始更新字段信息
                     */
                    if (celcount > 0) {
                        tagents_product.setValue("fprodnum", fprodnum);
                    }
                    if (celcount > 1) {
                        tagents_product.setValue("fprodname", fprodname);
                    }
                    if (celcount > 2) {
                        tagents_product.setValue("fprice", cells[colno++].getContents());
                    }
                    if (celcount > 3) {
                        tagents_product.setValue("fnotes", cells[colno++].getContents());
                    }
                } catch (P2Exception e) {
                    e.printStackTrace();
                    /**
                     * 如果插入遇到错误，则在行最末尾插入错误信息
                     */
                    iserr = true;
                    Label label = new Label(colno++, rowno, e.getMessage());
                    WritableFont titleWf = new WritableFont(WritableFont.createFont("宋体"),// 字体
                            12,//WritableFont.DEFAULT_POINT_SIZE,   // 字号
                            WritableFont.NO_BOLD,                  // 粗体
                            false,                                 // 斜体
                            UnderlineStyle.NO_UNDERLINE,            // 下划线
                            Colour.RED,                       // 字体颜色
                            ScriptStyle.NORMAL_SCRIPT);
                    label.setCellFormat(new WritableCellFormat(titleWf));
                    sheet.addCell(label);
                }
            }
            /**
             * 如果更新期间没有遇到错误，则进行数据保存,否则清除数据
             */
            if (!iserr) {
                tagents_productSet.save();
            } else {
                tagents_productSet.clear();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                tagents_productSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return !iserr;
    }
}
