package com.eversec.kit.dev;

import com.eversec.common.ExcelKit;
import com.eversec.common.FileKit;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Random;

import static java.util.Arrays.asList;

public class DataKit {

    public static void main(String[] args) throws IOException {
        //createUpdateSql();
        //sheetNames();
        createChart();
    }

    public static void createUpdateSql() throws IOException {

        String[] ports = {"8080","8000","8012","8003","8801","5004","20004","60001","80","60001","10001","23","82","23","4410","8443","5000","5002","60001","9600","1935"};
        StringBuffer buf = new StringBuffer();
        Random random = new Random();
        for (int i = 1; i <= 61824; i++) {
            buf.append(String.format("update basic_goods set iPaddress='%s.%s.%s.%s',iPPort='%s' where id=%s;%n"
                    , random.nextInt(256)
                    , random.nextInt(256)
                    , random.nextInt(256)
                    , random.nextInt(256)
                    , ports[random.nextInt(ports.length)]
                    , i
            ));

            if (i> 0 && (i%20000 == 0 || i == 93863-1)){
                FileKit.strToFile(buf.toString(), new File("tmp/data/up_"+ i +".sql"), true);
                buf = new StringBuffer(buf.length()+100);
            }
        }

        /*String tpl = "update basic_goods g,basic_enterprise e set g.unitCompany=e.companyName where e.id=%s and g.id in (%s);%n";
        for (int i = 18; i < 129; i++) {
            String ids = "";
            for (int j = i; j <= 61824; j = j + (128-18)) {
                ids += j+",";
            }
            ids = ids.substring(0, ids.length()-1);
            buf.append(String.format(tpl, i, ids));
        }

        FileKit.strToFile(buf.toString(), new File("tmp/data/up_goods.sql"), true);*/
    }

    public static void sheetNames() throws IOException {
        ExcelKit.getSheetNames(new File("res/xls/table.xls")).forEach(x->{
            System.out.print(String.format(",%s%n", x));
        });
    }

    public static void createChart(){


    }

    @Test
    public void dealCompanyData() {
        File file = new File("C:\\Users\\eversec\\Documents\\WXWork\\1688854143550524\\Cache\\File\\2019-01\\basic_platform(2).xlsx");

        String[] heads = {
                "xh", "id", "platId", "platName", "platDomain", "ip", "platType", "companyId", "accessProvince",
                "isKey", "firstTime", "accessType", "serviceType", "userNum", "contact", "tel", "email"
        };

        String[] ks = {"platId", "platName", "platType", "companyId", "accessProvince",
                "isKey", "firstTime", "accessType", "serviceType", "userNum", "contact", "tel", "email"};

        try {
            List<Map> list = ExcelKit.readExcel(file, heads);

            StringBuilder buf = new StringBuilder();
            buf.append("insert basic_platform_1 (");
            asList(ks).forEach(k -> {
                buf.append(k).append(",");
            });
            buf.deleteCharAt(buf.length() - 1);
            buf.append(") values \n");

            list.forEach(r -> {
                buf.append("(");
                asList(ks).forEach(k -> {
                    buf.append("'").append(r.getOrDefault(k, "")).append("',");
                });
                buf.deleteCharAt(buf.length() - 1);
                buf.append("),\n");
            });

            buf.deleteCharAt(buf.length() - 1);
            buf.deleteCharAt(buf.length() - 1);

            FileKit.strToFile(buf.toString(), new File("tmp/xxx.sql"));
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
