/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sikd.ikonsol.ctrl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.Serializable;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.ejb.EJB;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.view.ViewScoped;
import javax.inject.Named;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.primefaces.PrimeFaces;
import org.primefaces.event.FileUploadEvent;
import sikd.ikonsol.ejb.session.DwEjbHome;
import sikd.ikonsol.ejb.session.HeaderDataKonsolidasiEjbHome;
import sikd.ikonsol.ejb.session.JenisBasEjbHome;
import sikd.ikonsol.ejb.session.JenisLaporanEjbHome;
import sikd.ikonsol.ejb.session.LingkupDataEjbHome;
import sikd.ikonsol.ejb.session.PeriodeEjbHome;
import sikd.ikonsol.ejb.session.RefPemdaEjbHome;
import sikd.ikonsol.ejb.session.SusunKonsolidasiEjbHome;
import sikd.ikonsol.model.HeaderDataKonsolidasi;
import sikd.ikonsol.model.JenisBas;
import sikd.ikonsol.model.JenisLaporan;
import sikd.ikonsol.model.LingkupData;
import sikd.ikonsol.model.Periode;
import sikd.ikonsol.model.RawData;
import sikd.ikonsol.model.RawDataLak;
import sikd.ikonsol.model.RawDataLpe;
import sikd.ikonsol.model.RawDataLpsal;
import sikd.ikonsol.model.ReferensiPemda;
import sikd.ikonsol.model.SusunKonsolidasi;
import sikd.sikd5.sikd5ejb.session.util.MyBatisConnectionFactory;

/**
 *
 * @author Imam Bhoim
 */
@ViewScoped
@Named(value = "headerDataKonsolidasiBeanCtrl")
public class HeaderDataKonsolidasiBeanCtrl implements Serializable {

    HeaderDataKonsolidasi hddatakons, hd;
    HeaderDataKonsolidasi obj;

    private SqlSessionFactory sqlSessionFactory;
    SusunKonsolidasi skons;
    SusunKonsolidasi obj2;

    int record = 0;

    int headerdata = 0;

    private List<HeaderDataKonsolidasi> list, list2;
    private List<JenisLaporan> listjlapor;
    private List<LingkupData> listldata;
    private List<Periode> listperiode;
    private List<JenisBas> listjenisbas;
    private List<ReferensiPemda> listProv;
    private List<RawData> listDw;

    private List<RawData> listNeraca;

    private List<RawData> listLo;

    private List<RawDataLak> listDwLak;
    private List<RawDataLpe> listDwLpe;

    private List<RawDataLpsal> listDwLpsal;

    private String stKonek = "";

    private String stKonekLo = "";

    private String stKonekNeraca = "";

    private String stKonekLpsal = "";

    private String stKonekLak = "";

    private String stKonekLpe = "";

    RawData rwdata, rawdatatmp, rwcount;
    RawData rwcountNeraca, rwcountLo;
    RawDataLak rwdatalak, rwcountLak;
    RawDataLpe rwdatalpe, rwcountLpe;
    RawDataLpsal rwdatalpsal, rwcountLpsal;

    private List<RawData> listcsv;
    private List<RawDataLak> listcsvlak;
    private List<RawDataLpe> listcsvlpe;
    private List<RawDataLpsal> listcsvlpsal;

    @EJB
    HeaderDataKonsolidasiEjbHome headerDataKonsEjbHome;

    @EJB
    JenisLaporanEjbHome jLaporEjbHome;

    @EJB
    LingkupDataEjbHome ldataEjbHome;

    @EJB
    PeriodeEjbHome periodeEjbHome;

    @EJB
    JenisBasEjbHome jenisbasEjbHome;

    @EJB
    SusunKonsolidasiEjbHome susunkonsolidasiEjbHome;

    @EJB
    RefPemdaEjbHome pemdaEjbHome;

    @EJB
    DwEjbHome dWEjbHome;

    @PostConstruct
    public void init() {

        try {
            list = headerDataKonsEjbHome.findAll();
        } catch (Exception xx) {
        }
        rwdata = new RawData();
        rwdatalak = new RawDataLak();
        rwdatalpe = new RawDataLpe();
        rwdatalpsal = new RawDataLpsal();
        //obj = periodeEjbHome.findByKode("22");
        //periode = periodeEjbHome.createNew();
        try {
            listjlapor = jLaporEjbHome.findAll();
        } catch (Exception ex) {
        }
        try {
            listldata = ldataEjbHome.findAll();
        } catch (Exception ex) {
        }

        try {
            listperiode = periodeEjbHome.findAllAktif();
        } catch (Exception ex) {
        }
        try {
            listjenisbas = jenisbasEjbHome.findAllAktif();
        } catch (Exception ex) {
        }
        try {
            listProv = pemdaEjbHome.findAllProvinsi("__.00");
        } catch (Exception ex) {
        }

        try {
            FacesContext context = FacesContext.getCurrentInstance();
            listcsv = (List<RawData>) context.getExternalContext().getSessionMap().get("IDITEM");
        } catch (Exception e) {
            // System.out.println(e);
        }

        try {
            FacesContext context = FacesContext.getCurrentInstance();
            listcsvlak = (List<RawDataLak>) context.getExternalContext().getSessionMap().get("IDITEMLAK");
        } catch (Exception e) {
            // System.out.println(e);
        }

        try {
            FacesContext context = FacesContext.getCurrentInstance();
            listcsvlpe = (List<RawDataLpe>) context.getExternalContext().getSessionMap().get("IDITEMLPE");
        } catch (Exception e) {
            // System.out.println(e);
        }

        try {
            FacesContext context = FacesContext.getCurrentInstance();
            listcsvlpsal = (List<RawDataLpsal>) context.getExternalContext().getSessionMap().get("IDITEMLPSAL");
        } catch (Exception e) {
            // System.out.println(e);
        }

    }

    public void add() {

        hddatakons = headerDataKonsEjbHome.createNew();

        skons = susunkonsolidasiEjbHome.createNew();

    }

    public String save() {

        int thn = hddatakons.getTahun();
        int idjnslap = hddatakons.getIdjnslaporan();
        String kdper = hddatakons.getKodeperiode();

        HeaderDataKonsolidasi h = new HeaderDataKonsolidasi();

        h = headerDataKonsEjbHome.findByThnIdlapKdper(thn, idjnslap, kdper);
        try {
            if (h == null) {

                hddatakons.setLingkup_id(4);

                hd = headerDataKonsEjbHome.save(hddatakons);

                skons.setHeader_data_id(hd.getId());
                skons.setTahun(hd.getTahun());
                skons.setJenis_laporan_id(hd.getIdjnslaporan());
                skons.setLingkup_id(4);
                skons.setLevel(hd.getLevel());
                skons.setIdbas(hd.getIdbas());

                susunkonsolidasiEjbHome.save(skons);

                PrimeFaces.current().executeScript("PF('wg_entryform').hide()");
                PrimeFaces.current().ajax().update(":master:dthddatakons");

                list = headerDataKonsEjbHome.findAll();

            } else {
                FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO,
                        "Info", "Data Laporan Sudah Ada");
                PrimeFaces.current().dialog().showMessageDynamic(message);
            }

        } catch (Exception ex) {
            System.out.println("GAGAL!");
            FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO,
                    "Info", "Data Laporan Sudah Ada");
            PrimeFaces.current().dialog().showMessageDynamic(message);
        }

        // simpan
        return "index?faces-redirect=true";
    }

    public String update() {

        int thn = obj.getTahun();
        int idjnslap = obj.getIdjnslaporan();
        int idsumber = obj.getSumber_id();
        int ibas = obj.getIdbas();
        int level = obj.getLevel();
        int status = obj.getStatus();

        String kdper = obj.getKodeperiode();

        HeaderDataKonsolidasi h = new HeaderDataKonsolidasi();

        h = headerDataKonsEjbHome.findByThnIdlapKdper(thn, idjnslap, kdper);

        HeaderDataKonsolidasi h2 = new HeaderDataKonsolidasi();

        h2 = headerDataKonsEjbHome.findByAllKriteria(thn, idjnslap, kdper, idsumber, ibas, level, status);

        try {
            if (h == null) {
                headerDataKonsEjbHome.update(obj);

                obj2.setHeader_data_id(obj.getId());
                obj2.setTahun(obj.getTahun());
                obj2.setJenis_laporan_id(obj.getIdjnslaporan());
                //obj2.setLingkup_id(obj.getLingkup_id());
                obj2.setLevel(obj.getLevel());
                obj2.setIdbas(obj.getIdbas());

                susunkonsolidasiEjbHome.update(obj2);

                PrimeFaces.current().executeScript("PF('wg_entryform_ubah').hide()");
                PrimeFaces.current().ajax().update(":master:dthddatakons");

                list = headerDataKonsEjbHome.findAll();
            } else {

                if (h2 == null) {
                    headerDataKonsEjbHome.update(obj);

                    obj2.setHeader_data_id(obj.getId());
                    obj2.setTahun(obj.getTahun());
                    obj2.setJenis_laporan_id(obj.getIdjnslaporan());
                    //obj2.setLingkup_id(obj.getLingkup_id());
                    obj2.setLevel(obj.getLevel());
                    obj2.setIdbas(obj.getIdbas());

                    susunkonsolidasiEjbHome.update(obj2);

                    PrimeFaces.current().executeScript("PF('wg_entryform_ubah').hide()");
                    PrimeFaces.current().ajax().update(":master:dthddatakons");

                    list = headerDataKonsEjbHome.findAll();
                } else {
                    FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO,
                            "Info", "Data Laporan Sudah Ada");
                    PrimeFaces.current().dialog().showMessageDynamic(message);
                }
            }

        } catch (Exception ex) {
            System.out.println("GAGAL!");
            FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO,
                    "Info", "Data Laporan Sudah Ada");
            PrimeFaces.current().dialog().showMessageDynamic(message);
        }

        // this.list = service.findAll();from_entryForm
        return "index?faces-redirect=true";
    }

    public void edit(int id) {
        obj = headerDataKonsEjbHome.findByID(id);

        obj2 = new SusunKonsolidasi();

        listjlapor = jLaporEjbHome.findAll();
        listldata = ldataEjbHome.findAll();
        listjenisbas = jenisbasEjbHome.findAllAktif();
        listperiode = periodeEjbHome.findAllAktif();
    }

    public void download(int id) {

        String term = ";";
        try {
            String namaFile = "";//gson.toJson(listProvinsiEksport2);
            //listProvinsiEksport2 = akeliminasiEjbHome.findByListProvEksport2(kdPeriode, idbas, idjnslaporan, tahun, kdPemda);
            FacesContext facesContext = FacesContext.getCurrentInstance();

            HttpServletResponse response = (HttpServletResponse) facesContext.getExternalContext().getResponse();

            obj = headerDataKonsEjbHome.findByID(id);

            if (obj.getIdjnslaporan() == 1 || obj.getIdjnslaporan() == 2 || obj.getIdjnslaporan() == 3 || obj.getIdjnslaporan() == 7) {

                listcsv = headerDataKonsEjbHome.findRawData(id);
                namaFile = this.exportCSVRawData(namaFile, listcsv, term);
            } else if (obj.getIdjnslaporan() == 4) {

            } else if (obj.getIdjnslaporan() == 5) {

            } else if (obj.getIdjnslaporan() == 6) {

            } else {
                System.out.println("KOSONG");
            }
//            GsonBuilder gsonBuilder = new GsonBuilder();
//            gsonBuilder.setLongSerializationPolicy(LongSerializationPolicy.STRING);
//
//            Gson gson = gsonBuilder.create();

            //Gson gson = builder.create();
            PrintWriter out = response.getWriter();
            response.setHeader("Content-Disposition", "attachment;filename=data.csv");
            response.setContentType("application/csv");
            out.print(namaFile);
            out.flush();
            facesContext.responseComplete();
        } catch (IOException ex) {
            //Logger.getLogger(AkunEliminasiBeanCtrl.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }

    }

    public static String exportCSVRawData(String nama, List<RawData> lc, String term) {

        try {

            Workbook wb = new HSSFWorkbook();
            Sheet cs = wb.createSheet("data");
            Row headerRow = cs.createRow(0);

            Cell _h1 = headerRow.createCell(0);
            _h1.setCellValue("tahun");
            Cell _h2 = headerRow.createCell(1);
            _h2.setCellValue("kd_provinsi");
            Cell _h3 = headerRow.createCell(2);
            _h3.setCellValue("kd_pemda");
            Cell _h4 = headerRow.createCell(3);
            _h4.setCellValue("kd_satker");
            Cell _h5 = headerRow.createCell(4);
            _h5.setCellValue("periode_id");
            Cell _h6 = headerRow.createCell(5);
            _h6.setCellValue("perubahan_id");
            Cell _h7 = headerRow.createCell(6);
            _h7.setCellValue("kd_rek_1");
            Cell _h8 = headerRow.createCell(7);
            _h8.setCellValue("kd_rek_2");
            Cell _h9 = headerRow.createCell(8);
            _h9.setCellValue("kd_rek_3");
            Cell _h10 = headerRow.createCell(9);
            _h10.setCellValue("kd_rek_4");
            Cell _h11 = headerRow.createCell(10);
            _h11.setCellValue("kd_rek_5");
            Cell _h12 = headerRow.createCell(11);
            _h12.setCellValue("nm_rek_4");
            Cell _h13 = headerRow.createCell(12);
            _h13.setCellValue("nm_rek_5");
            Cell _h14 = headerRow.createCell(13);
            _h14.setCellValue("anggaran");
            Cell _h15 = headerRow.createCell(14);
            _h15.setCellValue("realisasi");
            Cell _h16 = headerRow.createCell(15);
            _h16.setCellValue("d_k");
            Cell _h17 = headerRow.createCell(16);
            _h17.setCellValue("jenis_transaksi_id");

            int row = 1;

            for (int i = 0; i < lc.size(); i++) {
                Row dataRow = cs.createRow((short) row);
//                dataRow.setRowStyle(unlockedCellStyle);

                dataRow.createCell(0).setCellValue(lc.get(i).getTahun());
                dataRow.createCell(1).setCellValue(lc.get(i).getKdprov());
                dataRow.createCell(2).setCellValue(lc.get(i).getKdpemda());
                dataRow.createCell(3).setCellValue(lc.get(i).getKdsatker());
                dataRow.createCell(4).setCellValue(lc.get(i).getPeriodeid());
                dataRow.createCell(5).setCellValue(lc.get(i).getPerubahanid());
                dataRow.createCell(6).setCellValue(lc.get(i).getKdrek1());
                dataRow.createCell(7).setCellValue(lc.get(i).getKdrek2());
                dataRow.createCell(8).setCellValue(lc.get(i).getKdrek3());
                dataRow.createCell(9).setCellValue(lc.get(i).getKdrek4());
                dataRow.createCell(10).setCellValue(lc.get(i).getKdrek5());
                dataRow.createCell(11).setCellValue(lc.get(i).getNmrek4());
                dataRow.createCell(12).setCellValue(lc.get(i).getNmrek5());
                dataRow.createCell(13).setCellValue(lc.get(i).getAnggaran());
                dataRow.createCell(14).setCellValue(lc.get(i).getRealisasi());
                dataRow.createCell(15).setCellValue(lc.get(i).getDk());
                dataRow.createCell(16).setCellValue(lc.get(i).getJenis_transaksi_id());

                row += 1;

            }

            ArrayList<List<HSSFCell>> cellGrid;
            cellGrid = new ArrayList<List<HSSFCell>>();

            Sheet mySheet = wb.getSheetAt(0);
            Iterator<?> rowIter = mySheet.rowIterator();

            while (rowIter.hasNext()) {
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<?> cellIter = myRow.cellIterator();
                List<HSSFCell> cellRowList = new ArrayList<HSSFCell>();
                while (cellIter.hasNext()) {
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    cellRowList.add(myCell);
                }
                cellGrid.add(cellRowList);
            }

            File file = new File(nama);
            PrintStream stream = new PrintStream(file);
            for (int i = 0; i < cellGrid.size(); i++) {
                List<HSSFCell> cellRowList = cellGrid.get(i);
                for (int j = 0; j < cellRowList.size(); j++) {
                    HSSFCell myCell = (HSSFCell) cellRowList.get(j);
                    String stringCellValue = null;
                    if (myCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        double db = myCell.getNumericCellValue();
                        DecimalFormat df = new DecimalFormat("###.##");
                        stringCellValue = "" + df.format(db);
                    } else {
                        stringCellValue = myCell.toString();
                    }
                    stream.print(stringCellValue + term);

                }
                stream.println("");
            }

            stream.close();

        } catch (Exception e) {
            e.printStackTrace();

        }
        return nama;
    }

    public String load(int id) {

        headerdata = id;
        obj = headerDataKonsEjbHome.findByID(id);
        try {
            if (obj.getIdjnslaporan() == 1 || obj.getIdjnslaporan() == 2 || obj.getIdjnslaporan() == 3 || obj.getIdjnslaporan() == 7) {

                listcsv = headerDataKonsEjbHome.findRawData(id);

                this.saveToSession(listcsv);

                FacesContext context = FacesContext.getCurrentInstance();
                //System.out.println("Bas " + id);
                HttpServletRequest oriquest
                        = (HttpServletRequest) context.getExternalContext().getRequest();
                String nexurl = "/ikonsol/proseskonsolidasi/eksportdata.sikd";
                try {
                    context.getExternalContext()
                            .redirect(oriquest.getContextPath() + nexurl);// "/logbook/view.xhtml");
                } catch (IOException e) {
                    e.printStackTrace();
                    System.out.println("AAA");
                }

            } else if (obj.getIdjnslaporan() == 4) {

                listcsvlak = headerDataKonsEjbHome.findRawDataLak(id);

                this.saveToSession2(listcsvlak);

                FacesContext context = FacesContext.getCurrentInstance();
                //System.out.println("Bas " + id);
                HttpServletRequest oriquest
                        = (HttpServletRequest) context.getExternalContext().getRequest();
                String nexurl = "/ikonsol/proseskonsolidasi/eksportdatalak.sikd";
                try {
                    context.getExternalContext()
                            .redirect(oriquest.getContextPath() + nexurl);// "/logbook/view.xhtml");
                } catch (IOException e) {
                    e.printStackTrace();
                    System.out.println("AAA");
                }

            } else if (obj.getIdjnslaporan() == 5) {

                listcsvlpe = headerDataKonsEjbHome.findRawDataLpe(id);

                this.saveToSession3(listcsvlpe);

                FacesContext context = FacesContext.getCurrentInstance();
                //System.out.println("Bas " + id);
                HttpServletRequest oriquest
                        = (HttpServletRequest) context.getExternalContext().getRequest();
                String nexurl = "/ikonsol/proseskonsolidasi/eksportdatalpe.sikd";
                try {
                    context.getExternalContext()
                            .redirect(oriquest.getContextPath() + nexurl);// "/logbook/view.xhtml");
                } catch (IOException e) {
                    e.printStackTrace();
                    System.out.println("AAA");
                }

            } else if (obj.getIdjnslaporan() == 6) {
                listcsvlpsal = headerDataKonsEjbHome.findRawDataLpsal(id);

                this.saveToSession4(listcsvlpsal);

                FacesContext context = FacesContext.getCurrentInstance();
                //System.out.println("Bas " + id);
                HttpServletRequest oriquest
                        = (HttpServletRequest) context.getExternalContext().getRequest();
                String nexurl = "/ikonsol/proseskonsolidasi/eksportdatalpsal.sikd";
                try {
                    context.getExternalContext()
                            .redirect(oriquest.getContextPath() + nexurl);// "/logbook/view.xhtml");
                } catch (IOException e) {
                    e.printStackTrace();
                    System.out.println("AAA");
                }
            } else {
                System.out.println("KOSONG");
            }
        } catch (Exception r) {
            r.printStackTrace();
            System.out.println("BBB");

            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR,
                    r + " Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded "
                            + "( data tidak bisa ditampilkan ) " , null));
        }

        return "view?faces-redirect=true";

    }

    private void saveToSession(List<RawData> idx) {
        FacesContext context = FacesContext.getCurrentInstance();
        context.getExternalContext().getSessionMap()
                .put("IDITEM", idx);
        System.out.println("Session id " + idx);

    }
    
     private void saveToSession2(List<RawDataLak> idx) {
        FacesContext context = FacesContext.getCurrentInstance();
        context.getExternalContext().getSessionMap()
                .put("IDITEMLAK", idx);
        System.out.println("Session id " + idx);

    }

     
      private void saveToSession3(List<RawDataLpe> idx) {
        FacesContext context = FacesContext.getCurrentInstance();
        context.getExternalContext().getSessionMap()
                .put("IDITEMLPE", idx);
        System.out.println("Session id " + idx);

    }

      
       private void saveToSession4(List<RawDataLpsal> idx) {
        FacesContext context = FacesContext.getCurrentInstance();
        context.getExternalContext().getSessionMap()
                .put("IDITEMLPSAL", idx);
        System.out.println("Session id " + idx);

    }


    public void upload(int id) {
        obj = headerDataKonsEjbHome.findByID(id);

    }

    public void delete(int id) {
        headerDataKonsEjbHome.delete(id);

        susunkonsolidasiEjbHome.delete(id);
        PrimeFaces.current().ajax().update(":master:dthddatakons");
        list = headerDataKonsEjbHome.findAll();
    }

    public ArrayList<Integer> myNumbers() {

        ArrayList<Integer> numbers = new ArrayList<Integer>();
        for (int years = 2015; years <= Calendar.getInstance().get(Calendar.YEAR); years++) {
            numbers.add(years);
        }
        return (numbers);
    }

    public ArrayList<Integer> levelData() {

        ArrayList<Integer> numbers = new ArrayList<Integer>();

        numbers.add(1);
        numbers.add(2);
        numbers.add(3);
        numbers.add(4);
        numbers.add(5);
        return (numbers);
    }

    public void handleFileUpload(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {
            System.out.println("XXPath2 " + event.getFile().getFileName());
            String a = event.getFile().getFileName();
            System.out.println("XXPath " + a.toString());
            //workbook = new XSSFWorkbook(file);
            Path pathToFile = Paths.get(a).toAbsolutePath();

            InputStream file;
            file = event.getFile().getInputstream();

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "perubahan_id", "kd_rek_1", "kd_rek_2",
                            "kd_rek_3", "kd_rek_4", "kd_rek_5", "nm_rek_4", "nm_rek_5",
                            "realisasi", "anggaran", "d_k", "jenis_transaksi_id")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index

                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");

                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String perubahanid = csvRecord.get("perubahan_id");
                    String kdrek1 = csvRecord.get("kd_rek_1");
                    String kdrek2 = csvRecord.get("kd_rek_2");
                    String kdrek3 = csvRecord.get("kd_rek_3");
                    String kdrek4 = csvRecord.get("kd_rek_4");
                    String kdrek5 = csvRecord.get("kd_rek_5");

                    if (kdrek1.equalsIgnoreCase("")) {
                        kdrek1 = "0";
                    }

                    if (kdrek2.equalsIgnoreCase("")) {
                        kdrek2 = "0";
                    }

                    if (kdrek3.equalsIgnoreCase("")) {
                        kdrek3 = "0";
                    }

                    if (kdrek4.equalsIgnoreCase("")) {
                        kdrek4 = "0";
                    } else if (kdrek4.equalsIgnoreCase("1")) {
                        kdrek4 = "01";
                    } else if (kdrek4.equalsIgnoreCase("2")) {
                        kdrek4 = "02";
                    } else if (kdrek4.equalsIgnoreCase("3")) {
                        kdrek4 = "03";
                    } else if (kdrek4.equalsIgnoreCase("4")) {
                        kdrek4 = "04";
                    } else if (kdrek4.equalsIgnoreCase("5")) {
                        kdrek4 = "05";
                    } else if (kdrek4.equalsIgnoreCase("6")) {
                        kdrek4 = "06";
                    } else if (kdrek4.equalsIgnoreCase("7")) {
                        kdrek4 = "07";
                    } else if (kdrek4.equalsIgnoreCase("8")) {
                        kdrek4 = "08";
                    } else if (kdrek4.equalsIgnoreCase("9")) {
                        kdrek4 = "09";
                    } else {

                    }

                    if (kdrek5.equalsIgnoreCase("")) {
                        kdrek5 = "0";
                    } else if (kdrek5.equalsIgnoreCase("1")) {
                        kdrek5 = "01";
                    } else if (kdrek5.equalsIgnoreCase("2")) {
                        kdrek5 = "02";
                    } else if (kdrek5.equalsIgnoreCase("3")) {
                        kdrek5 = "03";
                    } else if (kdrek5.equalsIgnoreCase("4")) {
                        kdrek5 = "04";
                    } else if (kdrek5.equalsIgnoreCase("5")) {
                        kdrek5 = "05";
                    } else if (kdrek5.equalsIgnoreCase("6")) {
                        kdrek5 = "06";
                    } else if (kdrek5.equalsIgnoreCase("7")) {
                        kdrek5 = "07";
                    } else if (kdrek5.equalsIgnoreCase("8")) {
                        kdrek5 = "08";
                    } else if (kdrek5.equalsIgnoreCase("9")) {
                        kdrek5 = "09";
                    } else {

                    }

                    if (kdprov.equalsIgnoreCase("1")) {
                        kdprov = "01";
                    } else if (kdprov.equalsIgnoreCase("2")) {
                        kdprov = "02";
                    } else if (kdprov.equalsIgnoreCase("3")) {
                        kdprov = "03";
                    } else if (kdprov.equalsIgnoreCase("4")) {
                        kdprov = "04";
                    } else if (kdprov.equalsIgnoreCase("5")) {
                        kdprov = "05";
                    } else if (kdprov.equalsIgnoreCase("6")) {
                        kdprov = "06";
                    } else if (kdprov.equalsIgnoreCase("7")) {
                        kdprov = "07";
                    } else if (kdprov.equalsIgnoreCase("8")) {
                        kdprov = "08";
                    } else if (kdprov.equalsIgnoreCase("9")) {
                        kdprov = "09";
                    } else {

                    }

                    String nmrek4 = csvRecord.get("nm_rek_4");
                    String nmrek5 = csvRecord.get("nm_rek_5");
                    String realisasi = csvRecord.get("realisasi");
                    if (realisasi.equalsIgnoreCase("")) {
                        realisasi = "0";
                    }
                    String anggaran = csvRecord.get("anggaran");
                    if (anggaran.equalsIgnoreCase("")) {
                        anggaran = "0";
                    }
                    String dk = csvRecord.get("d_k");
                    String jenistransaksiid = csvRecord.get("jenis_transaksi_id");

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdata.setTahun(Integer.valueOf(tahun));
                        rwdata.setKdprov(kdprov);
                        rwdata.setKdpemda(kdpemda);
                        rwdata.setKdsatker(kdsatker);
                        rwdata.setPeriodeid(Integer.valueOf(periodeid));
                        rwdata.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdata.setKdrek1(Integer.valueOf(kdrek1));
                        rwdata.setKdrek2(Integer.valueOf(kdrek2));
                        rwdata.setKdrek3(Integer.valueOf(kdrek3));
                        rwdata.setKdrek4(kdrek4.trim());
                        rwdata.setKdrek5(kdrek5.trim());
                        rwdata.setNmrek4(nmrek4);
                        rwdata.setNmrek5(nmrek5);
                        rwdata.setRealisasi(Double.valueOf(realisasi));
                        rwdata.setAnggaran(Double.valueOf(anggaran));
                        rwdata.setDk(dk);
                        rwdata.setHeader_data_id(obj.getId());
                        rwdata.setJenis_transaksi_id(Integer.valueOf(jenistransaksiid));

                        headerDataKonsEjbHome.saveImportRawdata(rwdata);
                    }
                }

                List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCount(obj.getId());

                HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProv(obj.getId());
                List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemda(obj.getId());

                if (jmlprov.size() > 1) {
                    hdatakon.setLingkup_id(1);
                } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                    hdatakon.setLingkup_id(2);
                } else {
                    hdatakon.setLingkup_id(3);
                }

                hdatakon.setId(obj.getId());

                SusunKonsolidasi objSusun = new SusunKonsolidasi();
                objSusun.setLingkup_id(hdatakon.getLingkup_id());
                objSusun.setJml_pemda(hd.size());
                objSusun.setHeader_data_id(hdatakon.getId());
                try {
                    headerDataKonsEjbHome.updateLingkup(hdatakon);
                    susunkonsolidasiEjbHome.updatePemda(objSusun);
                } catch (Exception e) {
                    System.out.println(e);
                }

                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public boolean statusFileCSV(int id, int jenis) {

        HeaderDataKonsolidasi ab = headerDataKonsEjbHome.findByID(id);
        HeaderDataKonsolidasi ac = headerDataKonsEjbHome.findCountRawData(id);
        HeaderDataKonsolidasi lak = headerDataKonsEjbHome.findCountRawDataLak(id);
        HeaderDataKonsolidasi lpe = headerDataKonsEjbHome.findCountRawDataLpe(id);
        HeaderDataKonsolidasi lpsal = headerDataKonsEjbHome.findCountRawDataLpsal(id);

        if (ab.getSumber_id() == 2) {
            if (jenis == 1 || jenis == 2 || jenis == 3 || jenis == 7) {
                if (ac.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 4) {
                if (lak.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 5) {
                if (lpe.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 6) {
                if (lpsal.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }

    }

    public boolean statusFileDB(int id, int jenis) {

        HeaderDataKonsolidasi ab = headerDataKonsEjbHome.findByID(id);
        HeaderDataKonsolidasi ac = headerDataKonsEjbHome.findCountRawData(id);
        HeaderDataKonsolidasi lak = headerDataKonsEjbHome.findCountRawDataLak(id);
        HeaderDataKonsolidasi lpe = headerDataKonsEjbHome.findCountRawDataLpe(id);
        HeaderDataKonsolidasi lpsal = headerDataKonsEjbHome.findCountRawDataLpsal(id);

        if (ab.getSumber_id() == 1) {
            if (jenis == 1 || jenis == 2 || jenis == 3 || jenis == 7) {
                if (ac.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 4) {
                if (lak.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 5) {
                if (lpe.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else if (jenis == 6) {
                if (lpsal.getId() == 0) {
                    return false;
                } else {
                    return true;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }

    }

    public void handleFileUploadUpdate(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));

        }

        try {

            String a = event.getFile().getFileName();
            InputStream file;
            file = event.getFile().getInputstream();

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "perubahan_id", "kd_rek_1", "kd_rek_2",
                            "kd_rek_3", "kd_rek_4", "kd_rek_5", "nm_rek_4", "nm_rek_5",
                            "realisasi", "anggaran", "d_k")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");

                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String perubahanid = csvRecord.get("perubahan_id");
                    String kdrek1 = csvRecord.get("kd_rek_1");
                    String kdrek2 = csvRecord.get("kd_rek_2");
                    String kdrek3 = csvRecord.get("kd_rek_3");
                    String kdrek4 = csvRecord.get("kd_rek_4");
                    String kdrek5 = csvRecord.get("kd_rek_5");

                    String nmrek4 = csvRecord.get("nm_rek_4");
                    String nmrek5 = csvRecord.get("nm_rek_5");
                    String realisasi = csvRecord.get("realisasi");
                    if (realisasi.equalsIgnoreCase("")) {
                        realisasi = "0";
                    }
                    String anggaran = csvRecord.get("anggaran");
                    if (anggaran.equalsIgnoreCase("")) {
                        anggaran = "0";
                    }
                    String dk = csvRecord.get("d_k");

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdata.setTahun(Integer.valueOf(tahun));
                        rwdata.setKdprov(kdprov);
                        rwdata.setKdpemda(kdpemda);
                        rwdata.setKdsatker(kdsatker);
                        rwdata.setPeriodeid(Integer.valueOf(periodeid));
                        rwdata.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdata.setKdrek1(Integer.valueOf(kdrek1));
                        rwdata.setKdrek2(Integer.valueOf(kdrek2));
                        rwdata.setKdrek3(Integer.valueOf(kdrek3));
                        rwdata.setKdrek4(kdrek4.trim());
                        rwdata.setKdrek5(kdrek5.trim());
                        rwdata.setNmrek4(nmrek4);
                        rwdata.setNmrek5(nmrek5);
                        rwdata.setRealisasi(Double.valueOf(realisasi));
                        rwdata.setAnggaran(Double.valueOf(anggaran));
                        rwdata.setDk(dk);

                        headerDataKonsEjbHome.saveImportRawdataUpdate(rwdata);
                    }
                }

            } catch (Exception ex) {
                ex.printStackTrace();
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        list2 = headerDataKonsEjbHome.findKdpemda();

        for (HeaderDataKonsolidasi rrw : list2) {

            headerDataKonsEjbHome.deleteData(rrw.getNamaperiode(), obj.getId());
        }

        headerDataKonsEjbHome.deleteRawTemp();

        this.handleFileUpload(event);

    }

    public void handleFileUploadCopy(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }
        InputStream inputStream = null;

        try {
            Map<String, String> map = MyBatisConnectionFactory.urlConnection();
            Connection connection = DriverManager.getConnection(
                    map.get("dbUrl"), map.get("username"), map.get("password"));
            String sql = "COPY ikon.raw_data_temp FROM stdin CSV HEADER DELIMITER ';'";

            BaseConnection pgcon = (BaseConnection) connection;
            CopyManager mgr = new CopyManager(pgcon);

            try {
                inputStream = event.getFile().getInputstream();
                Reader in = new BufferedReader(new InputStreamReader(inputStream));
                long rowsaffected = mgr.copyIn(sql, in);

                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));

            } catch (SQLException ex) {
                System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
                System.exit(0);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null));

            }
        } catch (IOException ex) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null));

        } catch (SQLException ex) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null));

        }

    }

    public static void exeData(String inputDataFile)
            throws FileNotFoundException, IOException, SQLException {
        Map<String, String> map = MyBatisConnectionFactory.urlConnection();
        Connection connection = DriverManager.getConnection(
                map.get("dbUrl"), map.get("username"), map.get("password"));
        String sql = "COPY ikon.raw_data_temp FROM stdin CSV HEADER DELIMITER ';'";
//        Connection conn = null;
//        try {
//            conn = MyBatisConnectionFactory.getSqlSessionFactoryIkonsol().openSession().getConnection();
//       
//        
//        } catch (Exception ex) {
//            ex.printStackTrace();
//        }

        // Connection ct =         sqlSessionFactory.openSession().getConnection();
        BaseConnection pgcon = (BaseConnection) connection;
        CopyManager mgr = new CopyManager(pgcon);
        try {
            Reader in = new BufferedReader(new FileReader(new File(inputDataFile)));
            long rowsaffected = mgr.copyIn(sql, in);
        } catch (SQLException ex) {
            System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
            System.exit(0);
        }
    }

    public void handleFileUploadLak(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {
            System.out.println("XXPath2 " + event.getFile().getFileName());
            String a = event.getFile().getFileName();
            System.out.println("XXPath " + a.toString());
            //workbook = new XSSFWorkbook(file);
            Path pathToFile = Paths.get(a).toAbsolutePath();

            InputStream file;
            file = event.getFile().getInputstream();

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "perubahan_id", "kd_rek_1", "kd_rek_2",
                            "kd_rek_3", "nm_rek_1", "nm_rek_2", "nm_rek_3",
                            "nilai", "d_k")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            System.out.println("importDapetfile " + a);
            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index
                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");

                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String perubahanid = csvRecord.get("perubahan_id");
                    String kdrek1 = csvRecord.get("kd_rek_1");
                    String kdrek2 = csvRecord.get("kd_rek_2");
                    String kdrek3 = csvRecord.get("kd_rek_3");
                    String nmrek1 = csvRecord.get("nm_rek_1");
                    String nmrek2 = csvRecord.get("nm_rek_2");
                    String nmrek3 = csvRecord.get("nm_rek_3");
                    String nilai = csvRecord.get("nilai");
                    String dk = csvRecord.get("d_k");
                    // String header_data_id = csvRecord.get(16);
                    // String jenis_transaksi_id = csvRecord.get(17);
                    System.out.println("Name : " + tahun);
                    System.out.println("Email : " + kdpemda);
                    System.out.println("Phone : " + kdrek1);
                    System.out.println("---------------\n\n");

                    if (kdprov.equalsIgnoreCase("1")) {
                        kdprov = "01";
                    } else if (kdprov.equalsIgnoreCase("2")) {
                        kdprov = "02";
                    } else if (kdprov.equalsIgnoreCase("3")) {
                        kdprov = "03";
                    } else if (kdprov.equalsIgnoreCase("4")) {
                        kdprov = "04";
                    } else if (kdprov.equalsIgnoreCase("5")) {
                        kdprov = "05";
                    } else if (kdprov.equalsIgnoreCase("6")) {
                        kdprov = "06";
                    } else if (kdprov.equalsIgnoreCase("7")) {
                        kdprov = "07";
                    } else if (kdprov.equalsIgnoreCase("8")) {
                        kdprov = "08";
                    } else if (kdprov.equalsIgnoreCase("9")) {
                        kdprov = "09";
                    } else {

                    }

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalak.setTahun(Integer.valueOf(tahun));
                        rwdatalak.setKdprov(kdprov);
                        rwdatalak.setKdpemda(kdpemda);
                        rwdatalak.setKdsatker(kdsatker);
                        rwdatalak.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalak.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdatalak.setKdrek1(Integer.valueOf(kdrek1));
                        rwdatalak.setKdrek2(Integer.valueOf(kdrek2));
                        rwdatalak.setKdrek3(Integer.valueOf(kdrek3));
                        rwdatalak.setNmrek1(nmrek1);
                        rwdatalak.setNmrek2(nmrek2);
                        rwdatalak.setNmrek3(nmrek3);
                        rwdatalak.setNilai(Double.valueOf(nilai));
                        rwdatalak.setDk(dk);
                        rwdatalak.setHeader_data_id(obj.getId());
                        rwdatalak.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLak(rwdatalak);
                    }
                    //  System.out.print("xxxn "+obj.getId());

// rwdata.setHeader_data_id(Integer.parseInt(header_data_id));
                    // rwdata.setJenis_transaksi_id(Integer.parseInt(jenis_transaksi_id));
                }

                List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLak(obj.getId());

                HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLak(obj.getId());
                List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLak(obj.getId());

                if (jmlprov.size() > 1) {
                    hdatakon.setLingkup_id(1);
                } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                    hdatakon.setLingkup_id(2);
                } else {
                    hdatakon.setLingkup_id(3);
                }

                hdatakon.setId(obj.getId());

                SusunKonsolidasi objSusun = new SusunKonsolidasi();
                objSusun.setLingkup_id(hdatakon.getLingkup_id());
                objSusun.setJml_pemda(hd.size());
                objSusun.setHeader_data_id(obj.getId());
                try {
                    headerDataKonsEjbHome.updateLingkup(hdatakon);
                    susunkonsolidasiEjbHome.updatePemda(objSusun);
                } catch (Exception e) {
                    System.out.println(e);
                }

                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            System.out.print("xxxn ERROR");
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void handleFileUploadLakUpdate(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {
            System.out.println("XXPath2 " + event.getFile().getFileName());
            String a = event.getFile().getFileName();
            System.out.println("XXPath " + a.toString());
            //workbook = new XSSFWorkbook(file);
            Path pathToFile = Paths.get(a).toAbsolutePath();

            InputStream file;
            file = event.getFile().getInputstream();

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "perubahan_id", "kd_rek_1", "kd_rek_2",
                            "kd_rek_3", "nm_rek_1", "nm_rek_2", "nm_rek_3",
                            "nilai", "d_k")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            System.out.println("importDapetfile " + a);
            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index
                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");

                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String perubahanid = csvRecord.get("perubahan_id");
                    String kdrek1 = csvRecord.get("kd_rek_1");
                    String kdrek2 = csvRecord.get("kd_rek_2");
                    String kdrek3 = csvRecord.get("kd_rek_3");
                    String nmrek1 = csvRecord.get("nm_rek_1");
                    String nmrek2 = csvRecord.get("nm_rek_2");
                    String nmrek3 = csvRecord.get("nm_rek_3");
                    String nilai = csvRecord.get("nilai");
                    String dk = csvRecord.get("d_k");

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalak.setTahun(Integer.valueOf(tahun));
                        rwdatalak.setKdprov(kdprov);
                        rwdatalak.setKdpemda(kdpemda);
                        rwdatalak.setKdsatker(kdsatker);
                        rwdatalak.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalak.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdatalak.setKdrek1(Integer.valueOf(kdrek1));
                        rwdatalak.setKdrek2(Integer.valueOf(kdrek2));
                        rwdatalak.setKdrek3(Integer.valueOf(kdrek3));
                        rwdatalak.setNmrek1(nmrek1);
                        rwdatalak.setNmrek2(nmrek2);
                        rwdatalak.setNmrek3(nmrek3);
                        rwdatalak.setNilai(Double.valueOf(nilai));
                        rwdatalak.setDk(dk);

                        headerDataKonsEjbHome.saveImportRawdataLakUpdate(rwdatalak);
                    }

                }

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            System.out.print("xxxn ERROR");
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        list2 = headerDataKonsEjbHome.findKdpemdaLak();

        for (HeaderDataKonsolidasi rrw : list2) {

            headerDataKonsEjbHome.deleteDataLak(rrw.getNamaperiode(), obj.getId());
        }

        headerDataKonsEjbHome.deleteRawLakTemp();

        this.handleFileUploadLak(event);
    }

    public void handleFileUploadLpe(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {
            String a = event.getFile().getFileName();
            //workbook = new XSSFWorkbook(file);
            Path pathToFile = Paths.get(a).toAbsolutePath();

            InputStream file;
            file = event.getFile().getInputstream();

            System.out.println("XXPath3 " + file.toString());

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "ekuitasAwal", "surplusDefisit", "koreksiPersediaan",
                            "selisihRevaluasiAset", "lainlain")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            System.out.println("importDapetfile " + a);
            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index
                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");
                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String ekuitasawal = csvRecord.get("ekuitasAwal");
                    String surplusdefisit = csvRecord.get("surplusDefisit");
                    String koreksipersediaan = csvRecord.get("koreksiPersediaan");
                    String selisihrevaluasiaset = csvRecord.get("selisihRevaluasiAset");
                    String lainlain = csvRecord.get("lainlain");

                    if (kdprov.equalsIgnoreCase("1")) {
                        kdprov = "01";
                    } else if (kdprov.equalsIgnoreCase("2")) {
                        kdprov = "02";
                    } else if (kdprov.equalsIgnoreCase("3")) {
                        kdprov = "03";
                    } else if (kdprov.equalsIgnoreCase("4")) {
                        kdprov = "04";
                    } else if (kdprov.equalsIgnoreCase("5")) {
                        kdprov = "05";
                    } else if (kdprov.equalsIgnoreCase("6")) {
                        kdprov = "06";
                    } else if (kdprov.equalsIgnoreCase("7")) {
                        kdprov = "07";
                    } else if (kdprov.equalsIgnoreCase("8")) {
                        kdprov = "08";
                    } else if (kdprov.equalsIgnoreCase("9")) {
                        kdprov = "09";
                    } else {

                    }

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalpe.setTahun(Integer.valueOf(tahun));
                        rwdatalpe.setKdprov(kdprov);
                        rwdatalpe.setKdpemda(kdpemda);
                        rwdatalpe.setKdsatker(kdsatker);
                        rwdatalpe.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpe.setEkuitasAwal(Double.valueOf(ekuitasawal));
                        rwdatalpe.setSurplusDefisit(Double.valueOf(surplusdefisit));
                        rwdatalpe.setKoreksiPersediaan(Double.valueOf(koreksipersediaan));
                        rwdatalpe.setSelisihRevaluasiAsset(Double.valueOf(selisihrevaluasiaset));
                        rwdatalpe.setLainLain(Double.valueOf(lainlain));
                        rwdatalpe.setHeader_data_id(obj.getId());
                        rwdatalpe.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLpe(rwdatalpe);
                    }

                }

                List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLpe(obj.getId());

                HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLpe(obj.getId());
                List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLpe(obj.getId());

                if (jmlprov.size() > 1) {
                    hdatakon.setLingkup_id(1);
                } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                    hdatakon.setLingkup_id(2);
                } else {
                    hdatakon.setLingkup_id(3);
                }

                hdatakon.setId(obj.getId());

                SusunKonsolidasi objSusun = new SusunKonsolidasi();
                objSusun.setLingkup_id(hdatakon.getLingkup_id());
                objSusun.setJml_pemda(hd.size());
                objSusun.setHeader_data_id(obj.getId());
                try {
                    headerDataKonsEjbHome.updateLingkup(hdatakon);
                    susunkonsolidasiEjbHome.updatePemda(objSusun);
                } catch (Exception e) {
                    System.out.println(e);
                }

                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void handleFileUploadLpeUpdate(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {

            String a = event.getFile().getFileName();

            InputStream file;
            file = event.getFile().getInputstream();

            System.out.println("XXPath3 " + file.toString());

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "ekuitasAwal", "surplusDefisit", "koreksiPersediaan",
                            "selisihRevaluasiAset", "lainlain")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index

                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");
                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String ekuitasawal = csvRecord.get("ekuitasAwal");
                    String surplusdefisit = csvRecord.get("surplusDefisit");
                    String koreksipersediaan = csvRecord.get("koreksiPersediaan");
                    String selisihrevaluasiaset = csvRecord.get("selisihRevaluasiAset");
                    String lainlain = csvRecord.get("lainlain");

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalpe.setTahun(Integer.valueOf(tahun));
                        rwdatalpe.setKdprov(kdprov);
                        rwdatalpe.setKdpemda(kdpemda);
                        rwdatalpe.setKdsatker(kdsatker);
                        rwdatalpe.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpe.setEkuitasAwal(Double.valueOf(ekuitasawal));
                        rwdatalpe.setSurplusDefisit(Double.valueOf(surplusdefisit));
                        rwdatalpe.setKoreksiPersediaan(Double.valueOf(koreksipersediaan));
                        rwdatalpe.setSelisihRevaluasiAsset(Double.valueOf(selisihrevaluasiaset));
                        rwdatalpe.setLainLain(Double.valueOf(lainlain));

                        headerDataKonsEjbHome.saveImportRawdataLpeUpdate(rwdatalpe);
                    }

                }

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        list2 = headerDataKonsEjbHome.findKdpemdaLpe();

        for (HeaderDataKonsolidasi rrw : list2) {

            headerDataKonsEjbHome.deleteDataLpe(rrw.getNamaperiode(), obj.getId());
        }

        headerDataKonsEjbHome.deleteRawLpeTemp();

        this.handleFileUploadLpe(event);

    }

    public void startConn(int id) {

        obj = headerDataKonsEjbHome.findByID(id);
    }

    public void closeConn() {

        //  obj = headerDataKonsEjbHome.findByID(id);
        // dWEjbHome.closesCon(obj);
        this.listDw = new ArrayList();
    }

    public void cekConn(int id) {

        // obj.setVendorDb("mysql");
        Boolean cek = true;
        try {
            cek = dWEjbHome.cekCon(obj);

            if (cek) {

                if (id == 1) {
                    listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcount = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryForm:dthddatakons2");

                    stKonek = "Terkoneksi";
                } else if (id == 2) {
                    listNeraca = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcountNeraca = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryFormNeraca:dthddatakons3");
                    stKonekNeraca = "Terkoneksi";
                } else if (id == 3) {
                    listLo = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcountLo = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryFormLo:dthddatakons4");
                    stKonekLo = "Terkoneksi";
                }

            } else {
                stKonek = "Gagal Terkoneksi";

            }
        } catch (Exception ex) {
            stKonek = "Gagal Terkoneksi";

        }

    }

    public void prosesLpsal() {

        if (listDwLpsal != null) {
            // cari data dulu kode pemda daari dw    
            List<RawDataLpsal> rData = dWEjbHome.findCountPemdaByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawDataLpsal rrw : rData) {

                headerDataKonsEjbHome.deleteDataLpsal(rrw.getKdpemda(), obj.getId());
            }

            listDwLpsal = dWEjbHome.findAllByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

            if (listDwLpsal != null) {
                try {
                    for (RawDataLpsal rdata : listDwLpsal) {

                        int tahun = rdata.getTahun();//csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();//csvRecord.get("kd_provinsi");
                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();//csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid();//csvRecord.get("periode_id");
                        double salawal = rdata.getSaldoAwal();// csvRecord.get("salAwal");
                        double penggunaansal = rdata.getPenggunaanSal(); //csvRecord.get("penggunaanSal");
                        double silpa = rdata.getSilpa();//csvRecord.get("silpa");
                        double koreksi = rdata.getKoreksi();//csvRecord.get("koreksi");
                        double lainnya = rdata.getLainnya();//csvRecord.get("lainnya");

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        rwdatalpsal.setTahun(Integer.valueOf(tahun));
                        rwdatalpsal.setKdprov(kdprov);
                        rwdatalpsal.setKdpemda(kdpemda);
                        rwdatalpsal.setKdsatker(kdsatker);
                        rwdatalpsal.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpsal.setSaldoAwal(Double.valueOf(salawal));
                        rwdatalpsal.setPenggunaanSal(Double.valueOf(penggunaansal));
                        rwdatalpsal.setSilpa(Double.valueOf(silpa));
                        rwdatalpsal.setKoreksi(Double.valueOf(koreksi));
                        rwdatalpsal.setLainnya(Double.valueOf(lainnya));
                        rwdatalpsal.setHeader_data_id(obj.getId());
                        rwdatalpsal.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLpsal(rwdatalpsal);

                    }

                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLpsal(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLpsal(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLpsal(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msglpsal", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msglpsal", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();
    }

    public void prosesLra() {

        listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
        // rwcount = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan());
        if (listDw != null) {
            // cari data dulu kode pemda daari dw    
            List<RawData> rData = dWEjbHome.findCountPemdaByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawData rrw : rData) {

                headerDataKonsEjbHome.deleteData(rrw.getKdpemda(), obj.getId());
            }

            //JIKA SUDAH DI HAPUS INSERT DATA RAW DATA MENGGUNAKAN DATA DW    
            // Lakukan insert  
            listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            if (listDw != null) {
                try {
                    for (RawData rdata : listDw) {
                        // awal loop     
                        String kdrek4 = "";// csvRecord.get("kd_rek_4");
                        String kdrek5 = "";// csvRecord.get("kd_rek_5");
                        int tahun = rdata.getTahun();// csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();// csvRecord.get("kd_provinsi");
                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();// csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid();// csvRecord.get("periode_id");
                        int perubahanid = rdata.getPerubahanid();// csvRecord.get("perubahan_id");
                        int kdrek1 = rdata.getKdrek1();// csvRecord.get("kd_rek_1");
                        int kdrek2 = rdata.getKdrek2();// csvRecord.get("kd_rek_2");
                        int kdrek3 = rdata.getKdrek3();// csvRecord.get("kd_rek_3");

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }

                        try {
                            kdrek4 = rdata.getKdrek4();// csvRecord.get("kd_rek_4");
                            if (kdrek4 == null) {
                                kdrek4 = "";
                            }
                        } catch (Exception ex) {
                            kdrek4 = "";
                        }

                        try {
                            kdrek5 = rdata.getKdrek5();// csvRecord.get("kd_rek_5");
                            if (kdrek5 == null) {
                                kdrek5 = "";
                            }
                        } catch (Exception ex) {
                            kdrek5 = "";
                        }

                        if (kdrek4.equalsIgnoreCase("")) {
                            kdrek4 = "0";
                        } else if (kdrek4.equalsIgnoreCase("1")) {
                            kdrek4 = "01";
                        } else if (kdrek4.equalsIgnoreCase("2")) {
                            kdrek4 = "02";
                        } else if (kdrek4.equalsIgnoreCase("3")) {
                            kdrek4 = "03";
                        } else if (kdrek4.equalsIgnoreCase("4")) {
                            kdrek4 = "04";
                        } else if (kdrek4.equalsIgnoreCase("5")) {
                            kdrek4 = "05";
                        } else if (kdrek4.equalsIgnoreCase("6")) {
                            kdrek4 = "06";
                        } else if (kdrek4.equalsIgnoreCase("7")) {
                            kdrek4 = "07";
                        } else if (kdrek4.equalsIgnoreCase("8")) {
                            kdrek4 = "08";
                        } else if (kdrek4.equalsIgnoreCase("9")) {
                            kdrek4 = "09";
                        } else {

                        }

                        if (kdrek5.equalsIgnoreCase("")) {
                            kdrek5 = "0";
                        } else if (kdrek5.equalsIgnoreCase("1")) {
                            kdrek5 = "01";
                        } else if (kdrek5.equalsIgnoreCase("2")) {
                            kdrek5 = "02";
                        } else if (kdrek5.equalsIgnoreCase("3")) {
                            kdrek5 = "03";
                        } else if (kdrek5.equalsIgnoreCase("4")) {
                            kdrek5 = "04";
                        } else if (kdrek5.equalsIgnoreCase("5")) {
                            kdrek5 = "05";
                        } else if (kdrek5.equalsIgnoreCase("6")) {
                            kdrek5 = "06";
                        } else if (kdrek5.equalsIgnoreCase("7")) {
                            kdrek5 = "07";
                        } else if (kdrek5.equalsIgnoreCase("8")) {
                            kdrek5 = "08";
                        } else if (kdrek5.equalsIgnoreCase("9")) {
                            kdrek5 = "09";
                        } else {

                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        String nmrek4 = rdata.getNmrek4();// csvRecord.get("kd_rek_5");
                        String nmrek5 = rdata.getNmrek5();//csvRecord.get("nm_rek_5");
                        double realisasi = rdata.getRealisasi();

                        double anggaran = rdata.getAnggaran();
                        String dk = rdata.getDk();

                        if (nmrek4 == null) {
                            nmrek4 = "";
                        }
                        if (nmrek5 == null) {
                            nmrek5 = "";
                        }
                        if (dk == null) {
                            dk = "";
                        }

                        rwdata.setTahun(Integer.valueOf(tahun));
                        rwdata.setKdprov(kdprov);
                        rwdata.setKdpemda(kdpemda);
                        rwdata.setKdsatker(kdsatker);
                        rwdata.setPeriodeid(Integer.valueOf(periodeid));
                        rwdata.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdata.setKdrek1(Integer.valueOf(kdrek1));
                        rwdata.setKdrek2(Integer.valueOf(kdrek2));
                        rwdata.setKdrek3(Integer.valueOf(kdrek3));
                        rwdata.setKdrek4(kdrek4.trim());
                        rwdata.setKdrek5(kdrek5.trim());
                        rwdata.setNmrek4(nmrek4);
                        rwdata.setNmrek5(nmrek5);
                        rwdata.setRealisasi(Double.valueOf(realisasi));
                        rwdata.setAnggaran(Double.valueOf(anggaran));
                        rwdata.setDk(dk);
                        rwdata.setHeader_data_id(obj.getId());
                        rwdata.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdata(rwdata);

                        // akhir loop
                    }

                    // update data pemda
                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCount(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProv(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemda(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msglra", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msglra", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void prosesNeraca() {
        //listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan());
        // rwcount = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan());
        String kdrek4 = "";
        String kdrek5 = "";
        if (listNeraca != null) {
            // cari data dulu kode pemda daari dw    
            List<RawData> rData = dWEjbHome.findCountPemdaByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawData rrw : rData) {

                headerDataKonsEjbHome.deleteData(rrw.getKdpemda(), obj.getId());
            }

            //JIKA SUDAH DI HAPUS INSERT DATA RAW DATA MENGGUNAKAN DATA DW    
            // Lakukan insert  
            listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            if (listDw != null) {
                try {
                    for (RawData rdata : listDw) {
                        // awal loop     

                        int tahun = rdata.getTahun();// csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();// csvRecord.get("kd_provinsi");

                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();// csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid();// csvRecord.get("periode_id");
                        int perubahanid = rdata.getPerubahanid();// csvRecord.get("perubahan_id");
                        int kdrek1 = rdata.getKdrek1();// csvRecord.get("kd_rek_1");
                        int kdrek2 = rdata.getKdrek2();// csvRecord.get("kd_rek_2");
                        int kdrek3 = rdata.getKdrek3();// csvRecord.get("kd_rek_3");

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }

                        try {
                            kdrek4 = rdata.getKdrek4();// csvRecord.get("kd_rek_4");
                            if (kdrek4 == null) {
                                kdrek4 = "";
                            }
                        } catch (Exception ex) {
                            kdrek4 = "";
                        }

                        try {
                            kdrek5 = rdata.getKdrek5();// csvRecord.get("kd_rek_5");
                            if (kdrek5 == null) {
                                kdrek5 = "";
                            }
                        } catch (Exception ex) {
                            kdrek5 = "";
                        }

                        if (kdrek4.equalsIgnoreCase("")) {
                            kdrek4 = "0";
                        } else if (kdrek4.equalsIgnoreCase("1")) {
                            kdrek4 = "01";
                        } else if (kdrek4.equalsIgnoreCase("2")) {
                            kdrek4 = "02";
                        } else if (kdrek4.equalsIgnoreCase("3")) {
                            kdrek4 = "03";
                        } else if (kdrek4.equalsIgnoreCase("4")) {
                            kdrek4 = "04";
                        } else if (kdrek4.equalsIgnoreCase("5")) {
                            kdrek4 = "05";
                        } else if (kdrek4.equalsIgnoreCase("6")) {
                            kdrek4 = "06";
                        } else if (kdrek4.equalsIgnoreCase("7")) {
                            kdrek4 = "07";
                        } else if (kdrek4.equalsIgnoreCase("8")) {
                            kdrek4 = "08";
                        } else if (kdrek4.equalsIgnoreCase("9")) {
                            kdrek4 = "09";
                        } else {

                        }

                        if (kdrek5.equalsIgnoreCase("")) {
                            kdrek5 = "0";
                        } else if (kdrek5.equalsIgnoreCase("1")) {
                            kdrek5 = "01";
                        } else if (kdrek5.equalsIgnoreCase("2")) {
                            kdrek5 = "02";
                        } else if (kdrek5.equalsIgnoreCase("3")) {
                            kdrek5 = "03";
                        } else if (kdrek5.equalsIgnoreCase("4")) {
                            kdrek5 = "04";
                        } else if (kdrek5.equalsIgnoreCase("5")) {
                            kdrek5 = "05";
                        } else if (kdrek5.equalsIgnoreCase("6")) {
                            kdrek5 = "06";
                        } else if (kdrek5.equalsIgnoreCase("7")) {
                            kdrek5 = "07";
                        } else if (kdrek5.equalsIgnoreCase("8")) {
                            kdrek5 = "08";
                        } else if (kdrek5.equalsIgnoreCase("9")) {
                            kdrek5 = "09";
                        } else {

                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        String nmrek4 = rdata.getNmrek4();// csvRecord.get("kd_rek_5");
                        String nmrek5 = rdata.getNmrek5();//csvRecord.get("nm_rek_5");
                        double realisasi = rdata.getRealisasi();

                        double anggaran = rdata.getAnggaran();
                        String dk = rdata.getDk();

                        if (nmrek4 == null) {
                            nmrek4 = "";
                        }
                        if (nmrek5 == null) {
                            nmrek5 = "";
                        }
                        if (dk == null) {
                            dk = "";
                        }

                        rwdata.setTahun(Integer.valueOf(tahun));
                        rwdata.setKdprov(kdprov);
                        rwdata.setKdpemda(kdpemda);
                        rwdata.setKdsatker(kdsatker);
                        rwdata.setPeriodeid(Integer.valueOf(periodeid));
                        rwdata.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdata.setKdrek1(Integer.valueOf(kdrek1));
                        rwdata.setKdrek2(Integer.valueOf(kdrek2));
                        rwdata.setKdrek3(Integer.valueOf(kdrek3));
                        rwdata.setKdrek4(kdrek4.trim());
                        rwdata.setKdrek5(kdrek5.trim());
                        rwdata.setNmrek4(nmrek4);
                        rwdata.setNmrek5(nmrek5);
                        rwdata.setRealisasi(Double.valueOf(realisasi));
                        rwdata.setAnggaran(Double.valueOf(anggaran));
                        rwdata.setDk(dk);
                        rwdata.setHeader_data_id(obj.getId());
                        rwdata.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdata(rwdata);

                        // akhir loop
                    }

                    // update data pemda
                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCount(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProv(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemda(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msgneraca", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msgneraca", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void prosesLo() {
        //listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan());
        // rwcount = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan());
        String kdrek4 = "";// csvRecord.get("kd_rek_4");
        String kdrek5 = "";// csvRecord.get("kd_rek_5");
        if (listLo != null) {
            // cari data dulu kode pemda daari dw    
            List<RawData> rData = dWEjbHome.findCountPemdaByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawData rrw : rData) {

                headerDataKonsEjbHome.deleteData(rrw.getKdpemda(), obj.getId());
            }

            //JIKA SUDAH DI HAPUS INSERT DATA RAW DATA MENGGUNAKAN DATA DW    
            // Lakukan insert  
            listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            if (listDw != null) {
                try {
                    for (RawData rdata : listDw) {
                        // awal loop     

                        int tahun = rdata.getTahun();// csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();// csvRecord.get("kd_provinsi");

                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();// csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid();// csvRecord.get("periode_id");
                        int perubahanid = rdata.getPerubahanid();// csvRecord.get("perubahan_id");
                        int kdrek1 = rdata.getKdrek1();// csvRecord.get("kd_rek_1");
                        int kdrek2 = rdata.getKdrek2();// csvRecord.get("kd_rek_2");
                        int kdrek3 = rdata.getKdrek3();// csvRecord.get("kd_rek_3");

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }

                        try {
                            kdrek4 = rdata.getKdrek4();// csvRecord.get("kd_rek_4");
                            if (kdrek4 == null) {
                                kdrek4 = "";
                            }
                        } catch (Exception ex) {
                            kdrek4 = "";
                        }

                        try {
                            kdrek5 = rdata.getKdrek5();// csvRecord.get("kd_rek_5");
                            if (kdrek5 == null) {
                                kdrek5 = "";
                            }
                        } catch (Exception ex) {
                            kdrek5 = "";
                        }

                        if (kdrek4.equalsIgnoreCase("")) {
                            kdrek4 = "0";
                        } else if (kdrek4.equalsIgnoreCase("1")) {
                            kdrek4 = "01";
                        } else if (kdrek4.equalsIgnoreCase("2")) {
                            kdrek4 = "02";
                        } else if (kdrek4.equalsIgnoreCase("3")) {
                            kdrek4 = "03";
                        } else if (kdrek4.equalsIgnoreCase("4")) {
                            kdrek4 = "04";
                        } else if (kdrek4.equalsIgnoreCase("5")) {
                            kdrek4 = "05";
                        } else if (kdrek4.equalsIgnoreCase("6")) {
                            kdrek4 = "06";
                        } else if (kdrek4.equalsIgnoreCase("7")) {
                            kdrek4 = "07";
                        } else if (kdrek4.equalsIgnoreCase("8")) {
                            kdrek4 = "08";
                        } else if (kdrek4.equalsIgnoreCase("9")) {
                            kdrek4 = "09";
                        } else {

                        }

                        if (kdrek5.equalsIgnoreCase("")) {
                            kdrek5 = "0";
                        } else if (kdrek5.equalsIgnoreCase("1")) {
                            kdrek5 = "01";
                        } else if (kdrek5.equalsIgnoreCase("2")) {
                            kdrek5 = "02";
                        } else if (kdrek5.equalsIgnoreCase("3")) {
                            kdrek5 = "03";
                        } else if (kdrek5.equalsIgnoreCase("4")) {
                            kdrek5 = "04";
                        } else if (kdrek5.equalsIgnoreCase("5")) {
                            kdrek5 = "05";
                        } else if (kdrek5.equalsIgnoreCase("6")) {
                            kdrek5 = "06";
                        } else if (kdrek5.equalsIgnoreCase("7")) {
                            kdrek5 = "07";
                        } else if (kdrek5.equalsIgnoreCase("8")) {
                            kdrek5 = "08";
                        } else if (kdrek5.equalsIgnoreCase("9")) {
                            kdrek5 = "09";
                        } else {

                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        String nmrek4 = rdata.getNmrek4();// csvRecord.get("kd_rek_5");
                        String nmrek5 = rdata.getNmrek5();//csvRecord.get("nm_rek_5");
                        double realisasi = rdata.getRealisasi();

                        double anggaran = rdata.getAnggaran();
                        String dk = rdata.getDk();

                        if (nmrek4 == null) {
                            nmrek4 = "";
                        }
                        if (nmrek5 == null) {
                            nmrek5 = "";
                        }
                        if (dk == null) {
                            dk = "";
                        }

                        rwdata.setTahun(Integer.valueOf(tahun));
                        rwdata.setKdprov(kdprov);
                        rwdata.setKdpemda(kdpemda);
                        rwdata.setKdsatker(kdsatker);
                        rwdata.setPeriodeid(Integer.valueOf(periodeid));
                        rwdata.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdata.setKdrek1(Integer.valueOf(kdrek1));
                        rwdata.setKdrek2(Integer.valueOf(kdrek2));
                        rwdata.setKdrek3(Integer.valueOf(kdrek3));
                        rwdata.setKdrek4(kdrek4.trim());
                        rwdata.setKdrek5(kdrek5.trim());
                        rwdata.setNmrek4(nmrek4);
                        rwdata.setNmrek5(nmrek5);
                        rwdata.setRealisasi(Double.valueOf(realisasi));
                        rwdata.setAnggaran(Double.valueOf(anggaran));
                        rwdata.setDk(dk);
                        rwdata.setHeader_data_id(obj.getId());
                        rwdata.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdata(rwdata);

                        // akhir loop
                    }

                    // update data pemda
                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCount(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProv(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemda(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msglo", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msglo", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void prosesLak() {

        if (listDwLak != null) {
            // cari data dulu kode pemda daari dw    
            List<RawDataLak> rData = dWEjbHome.findCountPemdaByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawDataLak rrw : rData) {

                headerDataKonsEjbHome.deleteDataLak(rrw.getKdpemda(), obj.getId());
            }

            listDwLak = dWEjbHome.findAllByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

            if (listDwLak != null) {
                try {
                    for (RawDataLak rdata : listDwLak) {
                        // awal loop  

                        int tahun = rdata.getTahun();//csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();//svRecord.get("kd_provinsi");

                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();// //csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid();//csvRecord.get("periode_id");
                        int perubahanid = rdata.getPerubahanid();//svRecord.get("perubahan_id");
                        int kdrek1 = rdata.getKdrek1();//csvRecord.get("kd_rek_1");
                        int kdrek2 = rdata.getKdrek2();// csvRecord.get("kd_rek_2");
                        int kdrek3 = rdata.getKdrek3();//csvRecord.get("kd_rek_3");
                        String nmrek1 = rdata.getNmrek1();//csvRecord.get("nm_rek_1");
                        String nmrek2 = rdata.getNmrek2();//csvRecord.get("nm_rek_2");
                        String nmrek3 = rdata.getNmrek3();//csvRecord.get("nm_rek_3");
                        double nilai = rdata.getNilai();//csvRecord.get("nilai");
                        String dk = rdata.getDk();//csvRecord.get("d_k");

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }
                        if (nmrek1 == null) {
                            nmrek1 = "";
                        }
                        if (nmrek2 == null) {
                            nmrek2 = "";
                        }
                        if (nmrek3 == null) {
                            nmrek3 = "";
                        }
                        if (dk == null) {
                            dk = "";
                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        rwdatalak.setTahun(Integer.valueOf(tahun));
                        rwdatalak.setKdprov(kdprov);
                        rwdatalak.setKdpemda(kdpemda);
                        rwdatalak.setKdsatker(kdsatker);
                        rwdatalak.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalak.setPerubahanid(Integer.valueOf(perubahanid));
                        rwdatalak.setKdrek1(Integer.valueOf(kdrek1));
                        rwdatalak.setKdrek2(Integer.valueOf(kdrek2));
                        rwdatalak.setKdrek3(Integer.valueOf(kdrek3));
                        rwdatalak.setNmrek1(nmrek1);
                        rwdatalak.setNmrek2(nmrek2);
                        rwdatalak.setNmrek3(nmrek3);
                        rwdatalak.setNilai(Double.valueOf(nilai));
                        rwdatalak.setDk(dk);
                        rwdatalak.setHeader_data_id(obj.getId());
                        rwdatalak.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLak(rwdatalak);

                    }
                    // update data pemda
                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLak(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLak(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLak(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msglak", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msglak", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }
        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();
    }

    public void prosesLpe() {

        if (listDwLpe != null) {
            // cari data dulu kode pemda daari dw    
            List<RawDataLpe> rData = dWEjbHome.findCountPemdaByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            //lalu delete data di raw data berdasakan kdpemda di DW
            for (RawDataLpe rrw : rData) {

                headerDataKonsEjbHome.deleteDataLpe(rrw.getKdpemda(), obj.getId());
            }

            listDwLpe = dWEjbHome.findAllByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

            if (listDwLpe != null) {
                try {
                    for (RawDataLpe rdata : listDwLpe) {
                        // awal loop  

                        int tahun = rdata.getTahun();//csvRecord.get("tahun");
                        String kdprov = rdata.getKdprov();//csvRecord.get("kd_provinsi");
                        String kdpemda = rdata.getKdpemda();//csvRecord.get("kd_pemda");
                        String kdsatker = rdata.getKdsatker();//csvRecord.get("kd_satker");
                        int periodeid = rdata.getPeriodeid(); //csvRecord.get("periode_id");
                        double ekuitasawal = rdata.getEkuitasAwal();//csvRecord.get("ekuitasAwal");
                        double surplusdefisit = rdata.getSurplusDefisit(); //csvRecord.get("surplusDefisit");
                        double koreksipersediaan = rdata.getKoreksiPersediaan();//csvRecord.get("koreksiPersediaan");
                        double selisihrevaluasiaset = rdata.getSelisihRevaluasiAsset();//csvRecord.get("selisihRevaluasiAset");
                        double lainlain = rdata.getLainLain();

                        if (kdprov == null) {
                            kdprov = "";
                        }
                        if (kdpemda == null) {
                            kdpemda = "";
                        }
                        if (kdsatker == null) {
                            kdsatker = "";
                        }

                        if (kdprov.equalsIgnoreCase("1")) {
                            kdprov = "01";
                        } else if (kdprov.equalsIgnoreCase("2")) {
                            kdprov = "02";
                        } else if (kdprov.equalsIgnoreCase("3")) {
                            kdprov = "03";
                        } else if (kdprov.equalsIgnoreCase("4")) {
                            kdprov = "04";
                        } else if (kdprov.equalsIgnoreCase("5")) {
                            kdprov = "05";
                        } else if (kdprov.equalsIgnoreCase("6")) {
                            kdprov = "06";
                        } else if (kdprov.equalsIgnoreCase("7")) {
                            kdprov = "07";
                        } else if (kdprov.equalsIgnoreCase("8")) {
                            kdprov = "08";
                        } else if (kdprov.equalsIgnoreCase("9")) {
                            kdprov = "09";
                        } else {

                        }

                        rwdatalpe.setTahun(Integer.valueOf(tahun));
                        rwdatalpe.setKdprov(kdprov);
                        rwdatalpe.setKdpemda(kdpemda);
                        rwdatalpe.setKdsatker(kdsatker);
                        rwdatalpe.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpe.setEkuitasAwal(Double.valueOf(ekuitasawal));
                        rwdatalpe.setSurplusDefisit(Double.valueOf(surplusdefisit));
                        rwdatalpe.setKoreksiPersediaan(Double.valueOf(koreksipersediaan));
                        rwdatalpe.setSelisihRevaluasiAsset(Double.valueOf(selisihrevaluasiaset));
                        rwdatalpe.setLainLain(Double.valueOf(lainlain));
                        rwdatalpe.setHeader_data_id(obj.getId());
                        rwdatalpe.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLpe(rwdatalpe);

                    }
                    List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLpe(obj.getId());

                    HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                    List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLpe(obj.getId());
                    List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLpe(obj.getId());

                    if (jmlprov.size() > 1) {
                        hdatakon.setLingkup_id(1);
                    } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                        hdatakon.setLingkup_id(2);
                    } else {
                        hdatakon.setLingkup_id(3);
                    }

                    hdatakon.setId(obj.getId());

                    SusunKonsolidasi objSusun = new SusunKonsolidasi();
                    objSusun.setLingkup_id(hdatakon.getLingkup_id());
                    objSusun.setJml_pemda(hd.size());
                    objSusun.setHeader_data_id(obj.getId());
                    try {
                        headerDataKonsEjbHome.updateLingkup(hdatakon);
                        susunkonsolidasiEjbHome.updatePemda(objSusun);
                    } catch (Exception e) {
                        System.out.println(e);
                    }

                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    //FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Load Data Sukses", null);
                    FacesContext.getCurrentInstance().addMessage("msglpe", facesMsg);

                } catch (Exception ex) {
                    ex.printStackTrace();
                    //System.out.print("xxxn ERROR" + ex);
                    FacesMessage facesMsg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex, null);
                    FacesContext.getCurrentInstance().addMessage("msglpe", facesMsg);
                }
            }
        } else {

            System.out.println("gagal konek data");

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();
    }

    public void cekConnLak(int id) {

        //obj.setVendorDb("mysql");
        Boolean cek = true;
        try {
            cek = dWEjbHome.cekCon(obj);

            if (cek) {

                if (id == 4) {
                    listDwLak = dWEjbHome.findAllByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcountLak = dWEjbHome.findCountByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryFormLak:dthddatakonslak");
                    stKonekLak = "Terkoneksi";

                }

            } else {
                stKonekLak = "Gagal Terkoneksi";

            }
        } catch (Exception ex) {
            stKonekLak = "Gagal Terkoneksi";

        }

    }

    public void cekConnLpe(int id) {

        //obj.setVendorDb("mysql");
        Boolean cek = true;
        try {
            cek = dWEjbHome.cekCon(obj);

            if (cek) {

                if (id == 5) {
                    listDwLpe = dWEjbHome.findAllByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcountLpe = dWEjbHome.findCountByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryFormLpe:dthddatakonslpe");
                    stKonekLpe = "Terkoneksi";

                }

            } else {
                stKonekLpe = "Gagal Terkoneksi";

            }
        } catch (Exception ex) {
            stKonekLpe = "Gagal Terkoneksi";

        }

    }

    public void cekConnLpsal(int id) {

        //obj.setVendorDb("mysql");
        Boolean cek = true;
        try {
            cek = dWEjbHome.cekCon(obj);

            if (cek) {

                if (id == 6) {
                    listDwLpsal = dWEjbHome.findAllByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    rwcountLpsal = dWEjbHome.findCountByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
                    headerDataKonsEjbHome.updateDW(obj);
                    PrimeFaces.current().ajax().update(":from_entryFormLpsal:dthddatakonslpsal");
                    stKonekLpsal = "Terkoneksi";

                }

            } else {
                stKonekLpsal = "Gagal Terkoneksi";

            }
        } catch (Exception ex) {
            stKonekLpsal = "Gagal Terkoneksi";

        }

    }

    public void startDw(int id) {

        obj = headerDataKonsEjbHome.findByID(id);

        if (obj.getIdjnslaporan() == 1) {
            listDw = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcount = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        } else if (obj.getIdjnslaporan() == 2) {
            listNeraca = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcountNeraca = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        } else if (obj.getIdjnslaporan() == 3) {
            listLo = dWEjbHome.findAllByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcountLo = dWEjbHome.findCountByParam(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        } else if (obj.getIdjnslaporan() == 4) {
            listDwLak = dWEjbHome.findAllByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcountLak = dWEjbHome.findCountByParamLak(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        } else if (obj.getIdjnslaporan() == 5) {
            listDwLpe = dWEjbHome.findAllByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcountLpe = dWEjbHome.findCountByParamLpe(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        } else if (obj.getIdjnslaporan() == 6) {
            listDwLpsal = dWEjbHome.findAllByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());
            rwcountLpsal = dWEjbHome.findCountByParamLpsal(obj.getTahun(), obj.getKodeperiode(), obj.getIdjnslaporan(), obj.getNametblDb(), obj.getProv());

        }

    }

    public void handleFileUploadLpsal(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {
            System.out.println("XXPath2 " + event.getFile().getFileName());
            String a = event.getFile().getFileName();
            System.out.println("XXPath " + a.toString());
            //workbook = new XSSFWorkbook(file);
            Path pathToFile = Paths.get(a).toAbsolutePath();

            InputStream file;
            file = event.getFile().getInputstream();

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "salAwal", "penggunaanSal", "silpa",
                            "koreksi", "lainnya")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            System.out.println("importDapetfile " + a);
            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index

                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");
                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String salawal = csvRecord.get("salAwal");
                    String penggunaansal = csvRecord.get("penggunaanSal");
                    String silpa = csvRecord.get("silpa");
                    String koreksi = csvRecord.get("koreksi");
                    String lainnya = csvRecord.get("lainnya");

                    if (kdprov.equalsIgnoreCase("1")) {
                        kdprov = "01";
                    } else if (kdprov.equalsIgnoreCase("2")) {
                        kdprov = "02";
                    } else if (kdprov.equalsIgnoreCase("3")) {
                        kdprov = "03";
                    } else if (kdprov.equalsIgnoreCase("4")) {
                        kdprov = "04";
                    } else if (kdprov.equalsIgnoreCase("5")) {
                        kdprov = "05";
                    } else if (kdprov.equalsIgnoreCase("6")) {
                        kdprov = "06";
                    } else if (kdprov.equalsIgnoreCase("7")) {
                        kdprov = "07";
                    } else if (kdprov.equalsIgnoreCase("8")) {
                        kdprov = "08";
                    } else if (kdprov.equalsIgnoreCase("9")) {
                        kdprov = "09";
                    } else {

                    }

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalpsal.setTahun(Integer.valueOf(tahun));
                        rwdatalpsal.setKdprov(kdprov);
                        rwdatalpsal.setKdpemda(kdpemda);
                        rwdatalpsal.setKdsatker(kdsatker);
                        rwdatalpsal.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpsal.setSaldoAwal(Double.valueOf(salawal));
                        rwdatalpsal.setPenggunaanSal(Double.valueOf(penggunaansal));
                        rwdatalpsal.setSilpa(Double.valueOf(silpa));
                        rwdatalpsal.setKoreksi(Double.valueOf(koreksi));
                        rwdatalpsal.setLainnya(Double.valueOf(lainnya));
                        rwdatalpsal.setHeader_data_id(obj.getId());
                        rwdatalpsal.setJenis_transaksi_id(0);

                        headerDataKonsEjbHome.saveImportRawdataLpsal(rwdatalpsal);
                    }

                }

                List<HeaderDataKonsolidasi> hd = headerDataKonsEjbHome.findByPemdaCountLpsal(obj.getId());

                HeaderDataKonsolidasi hdatakon = new HeaderDataKonsolidasi();
                List<HeaderDataKonsolidasi> jmlprov = headerDataKonsEjbHome.findProvLpsal(obj.getId());
                List<HeaderDataKonsolidasi> jmlpemda = headerDataKonsEjbHome.findPemdaLpsal(obj.getId());

                if (jmlprov.size() > 1) {
                    hdatakon.setLingkup_id(1);
                } else if (jmlprov.size() == 1 && jmlpemda.size() > 1) {
                    hdatakon.setLingkup_id(2);
                } else {
                    hdatakon.setLingkup_id(3);
                }

                hdatakon.setId(obj.getId());

                SusunKonsolidasi objSusun = new SusunKonsolidasi();
                objSusun.setLingkup_id(hdatakon.getLingkup_id());
                objSusun.setJml_pemda(hd.size());
                objSusun.setHeader_data_id(obj.getId());
                try {
                    headerDataKonsEjbHome.updateLingkup(hdatakon);
                    susunkonsolidasiEjbHome.updatePemda(objSusun);
                } catch (Exception e) {
                    System.out.println(e);
                }

                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Import Sukses", null));

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();

    }

    public void handleFileUploadLpsalUpdate(FileUploadEvent event) {
        if (event.getFile().equals(null)) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "File is null", null));
            System.out.println("importKosong");
        }

        try {

            InputStream file;
            file = event.getFile().getInputstream();

            System.out.println("XXPath3 " + file.toString());

            Reader reader = new InputStreamReader(file);//Files.newBufferedReader((Path) file);

            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                    .withHeader("tahun", "kd_provinsi", "kd_pemda", "kd_satker",
                            "periode_id", "salAwal", "penggunaanSal", "silpa",
                            "koreksi", "lainnya")
                    .withFirstRecordAsHeader()
                    .withIgnoreHeaderCase()
                    .withDelimiter(';').withTrim()
            );

            try {
                for (CSVRecord csvRecord : csvParser) {
                    // Accessing Values by Column Index

                    record = (int) csvRecord.getRecordNumber();

                    String tahun = csvRecord.get("tahun");
                    String kdprov = csvRecord.get("kd_provinsi");
                    String kdpemda = csvRecord.get("kd_pemda");
                    String kdsatker = csvRecord.get("kd_satker");
                    String periodeid = csvRecord.get("periode_id");
                    String salawal = csvRecord.get("salAwal");
                    String penggunaansal = csvRecord.get("penggunaanSal");
                    String silpa = csvRecord.get("silpa");
                    String koreksi = csvRecord.get("koreksi");
                    String lainnya = csvRecord.get("lainnya");

                    if (csvRecord.getRecordNumber() > 0) {
                        rwdatalpsal.setTahun(Integer.valueOf(tahun));
                        rwdatalpsal.setKdprov(kdprov);
                        rwdatalpsal.setKdpemda(kdpemda);
                        rwdatalpsal.setKdsatker(kdsatker);
                        rwdatalpsal.setPeriodeid(Integer.valueOf(periodeid));
                        rwdatalpsal.setSaldoAwal(Double.valueOf(salawal));
                        rwdatalpsal.setPenggunaanSal(Double.valueOf(penggunaansal));
                        rwdatalpsal.setSilpa(Double.valueOf(silpa));
                        rwdatalpsal.setKoreksi(Double.valueOf(koreksi));
                        rwdatalpsal.setLainnya(Double.valueOf(lainnya));

                        headerDataKonsEjbHome.saveImportRawdataLpsalUpdate(rwdatalpsal);
                    }

                }

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.print("xxxn ERROR" + ex);
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + ex
                        + ", baris data " + record, null));

            }
        } catch (IOException e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null));
        }

        list2 = headerDataKonsEjbHome.findKdpemdaLpsal();

        for (HeaderDataKonsolidasi rrw : list2) {

            headerDataKonsEjbHome.deleteDataLpsal(rrw.getNamaperiode(), obj.getId());
        }

        headerDataKonsEjbHome.deleteRawLpsalTemp();

        this.handleFileUploadLpsal(event);

    }

    // function rawa data
    public String saveImportRawdata() {

        // simpan
        headerDataKonsEjbHome.save(hddatakons);

        PrimeFaces.current().executeScript("PF('wg_entryform').hide()");
        PrimeFaces.current().ajax().update(":master:dthddatakons");

        list = headerDataKonsEjbHome.findAll();
        //this.list = service.findAll();
        return "index?faces-redirect=true";
    }

    public void deleteAll(int id, int idjenislaporan) {
        //obj = service.findByID(Integer.parseInt(id));
        //obj = periodeEjbHome.findByKode(kode);
        if (idjenislaporan == 1 || idjenislaporan == 2 || idjenislaporan == 3) {
            //delete akun eliminasi,rawdata,susun dan header konsolidasi
            headerDataKonsEjbHome.deleteRawDataLRANeracaLO(id);

        } else if (idjenislaporan == 4) {
            headerDataKonsEjbHome.deleteRawDataLak(id);
        } else if (idjenislaporan == 5) {
            headerDataKonsEjbHome.deleteRawDataLpe(id);
        } else if (idjenislaporan == 6) {
            headerDataKonsEjbHome.deleteRawDataLpsal(id);
        } else {
            headerDataKonsEjbHome.deleteRawDataLRANeracaLO(id);

        }

        PrimeFaces.current().ajax().update(":master:dthddatakons");
        list = headerDataKonsEjbHome.findAll();
    }

    public HeaderDataKonsolidasi getHddatakons() {
        return hddatakons;
    }

    public void setHddatakons(HeaderDataKonsolidasi hddatakons) {
        this.hddatakons = hddatakons;
    }

    public HeaderDataKonsolidasi getObj() {
        return obj;
    }

    public void setObj(HeaderDataKonsolidasi obj) {
        this.obj = obj;
    }

    public List<HeaderDataKonsolidasi> getList() {
        return list;
    }

    public void setList(List<HeaderDataKonsolidasi> list) {
        this.list = list;
    }

    public HeaderDataKonsolidasiEjbHome getHeaderDataKonsEjbHome() {
        return headerDataKonsEjbHome;
    }

    public void setHeaderDataKonsEjbHome(HeaderDataKonsolidasiEjbHome headerDataKonsEjbHome) {
        this.headerDataKonsEjbHome = headerDataKonsEjbHome;
    }

    public List<JenisLaporan> getListjlapor() {
        return listjlapor;
    }

    public void setListjlapor(List<JenisLaporan> listjlapor) {
        this.listjlapor = listjlapor;
    }

    public List<LingkupData> getListldata() {
        return listldata;
    }

    public void setListldata(List<LingkupData> listldata) {
        this.listldata = listldata;
    }

    public JenisLaporanEjbHome getjLaporEjbHome() {
        return jLaporEjbHome;
    }

    public void setjLaporEjbHome(JenisLaporanEjbHome jLaporEjbHome) {
        this.jLaporEjbHome = jLaporEjbHome;
    }

    public LingkupDataEjbHome getLdataEjbHome() {
        return ldataEjbHome;
    }

    public void setLdataEjbHome(LingkupDataEjbHome ldataEjbHome) {
        this.ldataEjbHome = ldataEjbHome;
    }

    public List<Periode> getListperiode() {
        return listperiode;
    }

    public void setListperiode(List<Periode> listperiode) {
        this.listperiode = listperiode;
    }

    public List<JenisBas> getListjenisbas() {
        return listjenisbas;
    }

    public void setListjenisbas(List<JenisBas> listjenisbas) {
        this.listjenisbas = listjenisbas;
    }

    public HeaderDataKonsolidasi getHd() {
        return hd;
    }

    public void setHd(HeaderDataKonsolidasi hd) {
        this.hd = hd;
    }

    public SusunKonsolidasi getSkons() {
        return skons;
    }

    public void setSkons(SusunKonsolidasi skons) {
        this.skons = skons;
    }

    public SusunKonsolidasi getObj2() {
        return obj2;
    }

    public void setObj2(SusunKonsolidasi obj2) {
        this.obj2 = obj2;
    }

    public List<HeaderDataKonsolidasi> getList2() {
        return list2;
    }

    public void setList2(List<HeaderDataKonsolidasi> list2) {
        this.list2 = list2;
    }

    public List<RawData> getListDw() {
        return listDw;
    }

    public void setListDw(List<RawData> listDw) {
        this.listDw = listDw;
    }

    public RawData getRawdatatmp() {
        return rawdatatmp;
    }

    public void setRawdatatmp(RawData rawdatatmp) {
        this.rawdatatmp = rawdatatmp;
    }

    public RawData getRwcount() {
        return rwcount;
    }

    public void setRwcount(RawData rwcount) {
        this.rwcount = rwcount;
    }

    public List<RawDataLak> getListDwLak() {
        return listDwLak;
    }

    public void setListDwLak(List<RawDataLak> listDwLak) {
        this.listDwLak = listDwLak;
    }

    public List<RawDataLpe> getListDwLpe() {
        return listDwLpe;
    }

    public void setListDwLpe(List<RawDataLpe> listDwLpe) {
        this.listDwLpe = listDwLpe;
    }

    public List<RawDataLpsal> getListDwLpsal() {
        return listDwLpsal;
    }

    public void setListDwLpsal(List<RawDataLpsal> listDwLpsal) {
        this.listDwLpsal = listDwLpsal;
    }

    public RawData getRwdata() {
        return rwdata;
    }

    public void setRwdata(RawData rwdata) {
        this.rwdata = rwdata;
    }

    public RawDataLak getRwdatalak() {
        return rwdatalak;
    }

    public void setRwdatalak(RawDataLak rwdatalak) {
        this.rwdatalak = rwdatalak;
    }

    public RawDataLak getRwcountLak() {
        return rwcountLak;
    }

    public void setRwcountLak(RawDataLak rwcountLak) {
        this.rwcountLak = rwcountLak;
    }

    public RawDataLpe getRwdatalpe() {
        return rwdatalpe;
    }

    public void setRwdatalpe(RawDataLpe rwdatalpe) {
        this.rwdatalpe = rwdatalpe;
    }

    public RawDataLpe getRwcountLpe() {
        return rwcountLpe;
    }

    public void setRwcountLpe(RawDataLpe rwcountLpe) {
        this.rwcountLpe = rwcountLpe;
    }

    public RawDataLpsal getRwdatalpsal() {
        return rwdatalpsal;
    }

    public void setRwdatalpsal(RawDataLpsal rwdatalpsal) {
        this.rwdatalpsal = rwdatalpsal;
    }

    public RawDataLpsal getRwcountLpsal() {
        return rwcountLpsal;
    }

    public void setRwcountLpsal(RawDataLpsal rwcountLpsal) {
        this.rwcountLpsal = rwcountLpsal;
    }

    public String getStKonek() {
        return stKonek;
    }

    public void setStKonek(String stKonek) {
        this.stKonek = stKonek;
    }

    public List<RawData> getListNeraca() {
        return listNeraca;
    }

    public void setListNeraca(List<RawData> listNeraca) {
        this.listNeraca = listNeraca;
    }

    public List<RawData> getListLo() {
        return listLo;
    }

    public void setListLo(List<RawData> listLo) {
        this.listLo = listLo;
    }

    public RawData getRwcountNeraca() {
        return rwcountNeraca;
    }

    public void setRwcountNeraca(RawData rwcountNeraca) {
        this.rwcountNeraca = rwcountNeraca;
    }

    public RawData getRwcountLo() {
        return rwcountLo;
    }

    public void setRwcountLo(RawData rwcountLo) {
        this.rwcountLo = rwcountLo;
    }

    public String getStKonekLo() {
        return stKonekLo;
    }

    public void setStKonekLo(String stKonekLo) {
        this.stKonekLo = stKonekLo;
    }

    public String getStKonekNeraca() {
        return stKonekNeraca;
    }

    public void setStKonekNeraca(String stKonekNeraca) {
        this.stKonekNeraca = stKonekNeraca;
    }

    public String getStKonekLpsal() {
        return stKonekLpsal;
    }

    public void setStKonekLpsal(String stKonekLpsal) {
        this.stKonekLpsal = stKonekLpsal;
    }

    public String getStKonekLak() {
        return stKonekLak;
    }

    public void setStKonekLak(String stKonekLak) {
        this.stKonekLak = stKonekLak;
    }

    public String getStKonekLpe() {
        return stKonekLpe;
    }

    public void setStKonekLpe(String stKonekLpe) {
        this.stKonekLpe = stKonekLpe;
    }

    public List<ReferensiPemda> getListProv() {
        return listProv;
    }

    public void setListProv(List<ReferensiPemda> listProv) {
        this.listProv = listProv;
    }

    public List<RawData> getListcsv() {
        return listcsv;
    }

    public void setListcsv(List<RawData> listcsv) {
        this.listcsv = listcsv;
    }

    public List<RawDataLak> getListcsvlak() {
        return listcsvlak;
    }

    public void setListcsvlak(List<RawDataLak> listcsvlak) {
        this.listcsvlak = listcsvlak;
    }

    public List<RawDataLpe> getListcsvlpe() {
        return listcsvlpe;
    }

    public void setListcsvlpe(List<RawDataLpe> listcsvlpe) {
        this.listcsvlpe = listcsvlpe;
    }

    public List<RawDataLpsal> getListcsvlpsal() {
        return listcsvlpsal;
    }

    public void setListcsvlpsal(List<RawDataLpsal> listcsvlpsal) {
        this.listcsvlpsal = listcsvlpsal;
    }

}
