FeatureHistoryController

package pl.lc.ekartanalyst.basic.controller;

import java.io.FileOutputStream;
import java.lang.reflect.Array;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.geotools.data.ows.HTTPResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import pl.kg.ut.postgres.spatial.json.GeoJson;
import pl.lc.common.core.domain.FileResponse;
import pl.lc.ekartanalyst.basic.dao.proc.BasicProcedures;
import pl.lc.ekartanalyst.basic.dao.proc.FeatureClassesHistory;
import pl.lc.ekartanalyst.basic.dao.proc.FeatureCountProc.CountByFeatureClassNameParams;
import pl.lc.ekartanalyst.basic.dao.proc.FeatureFindProc;
import pl.lc.ekartanalyst.basic.dao.proc.FeatureFindProc.FindByFeatureClassNameParams;
import pl.lc.ekartanalyst.dispatch.dao.proc.DispatchProc;
import pl.lc.ekartanalyst.dispatch.domain.LaneOccupancy;
import pl.lc.ekartanalyst.mapadm.domain.FeatureClass;
import pl.kg.ut.postgres.spatial.json.GeoJsonFeature;

import com.google.common.base.Preconditions;
import pl.lc.ut.portal.criterion.sql.ISpatialCriterion;
import pl.lc.ut.portal.http.ResponseUtils;
import pl.lc.ut.portal.web.dao.GeoConjunctionCriterion;
import pl.lc.ut.portal.web.dao.ISpatialComparison;
import pl.lc.ut.portal.web.dao.ISpatialFindCriterion;
import pl.lc.ut.portal.web.dao.ISpatialFindOrder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.xml.bind.annotation.XmlTransient;

@Controller
@RequestMapping("/basic/feature/history")
public class FeatureHistoryController {
    @RequestMapping(value = "/readHistoryLayer")
    @ResponseBody
    public FeatureClass readHistoryLayer(@RequestParam String featureClassName) {
        Preconditions.checkNotNull(featureClassName);
        return BasicProcedures.getHistoryTablesProc().getHistoryLayer(featureClassName);
    }

    @RequestMapping(value = "/countByName")
    @ResponseBody
    public Integer countByName(@RequestBody CountByFeatureClassNameParams params) {
        Preconditions.checkNotNull(params.featureClassName);
        return BasicProcedures.getHistoryTablesProc().countByFeatureClassName(params.featureClassName, params.criterion);
    }

    @RequestMapping(value = "/findByName")
    @ResponseBody
    public List<GeoJsonFeature> findByName(
            @RequestBody FindByFeatureClassNameParams params) throws Exception {
        return BasicProcedures.getHistoryTablesProc().findByFeatureClassName(params);
    }

    @RequestMapping(value = "/findDeletedByName")
    @ResponseBody
    public List<GeoJsonFeature> findDeletedByName(
            @RequestBody FindByFeatureClassNameParams params) throws Exception {
        return BasicProcedures.getDeletedFeatureHistoryFindProc().findByFeatureClassName(params);
    }

    @RequestMapping(value = "/readHistoryForLayers")
    @ResponseBody
    public Map<String, FeatureClassesHistory.ChangesMonit> readHistoryForLayers(@RequestBody FindByFeatureClassNameParams f){
        return new FeatureClassesHistory().temp(f);
    }


    @RequestMapping(value = "/writeXlsFile")
    @ResponseBody
    public void writeXlsFile(@RequestBody ISpatialFindCriterion criterion, HTTPResponse response) throws Exception{



//        Map<String, FeatureClassesHistory.ChangesMonit> zakladki = new FeatureClassesHistory().temp(f);
//
//        for zakladka :
//        FeatureClass fc = BasicProcedures.getHistoryTablesProc().getHistoryLayer(featureClassName);
//          list<geojson> wiersze = BasicProcedures.getHistoryTablesProc().findByFeatureClassName(params);



        SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy_HH-mm-ss");
        String dateString = format.format( new Date()   );
        String excelFileName = "Zmiany_Olsztyn_"+dateString+".xlsx";//name of excel file

        String sheetName = "Sheet1";//name of sheet

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);

        //iterating r number of rows
        for (int r=0;r < 5; r++ )
        {
            HSSFRow row = sheet.createRow(r);

            //iterating c number of columns
            for (int c=0;c < 5; c++ )
            {
                HSSFCell cell = row.createCell(c);

                cell.setCellValue("Cell "+r+" "+c);
            }
        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        //write this workbook to an Outputstream.
        wb.write(fileOut);


    }

    @RequestMapping(value = "/writeXlsFileFlash")
    @ResponseBody
    @XmlTransient
    public void writeXlsFile(@RequestBody CritObject crit, HttpServletResponse response) throws Exception{

//        SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy_HH-mm-ss");
//        String dateString = format.format( new Date()   );
//        String excelFileName = "Zmiany_Olsztyn_"+dateString+".xlsx";//name of excel file

        ResponseUtils.excelResponse(response, "Zmiany_Olsztyn.xls");
        ServletOutputStream out = response.getOutputStream();

        ISpatialFindCriterion criterion = crit.criterion;

        String sheetName = "Sheet1";//name of sheet

        HSSFWorkbook wb = new HSSFWorkbook();

        FindByFeatureClassNameParams f = new FindByFeatureClassNameParams(null,0,null,criterion,null);

        Map<String, FeatureClassesHistory.ChangesMonit> zakladki = new FeatureClassesHistory().temp(f);

        for (FeatureClassesHistory.ChangesMonit zakladka : zakladki.values())
        {

            f.featureClassName = zakladka.featureClassName;
            sheetName = f.featureClassName.toString().replace("."," ");

            FeatureClass fc = BasicProcedures.getHistoryTablesProc().getHistoryLayer(zakladka.featureClassName);

            List<GeoJsonFeature> wiersze = BasicProcedures.getHistoryTablesProc().findByFeatureClassName(f);

            HSSFSheet sheet = wb.createSheet(sheetName);

            HSSFRow rowH = sheet.createRow(0);
            HSSFCell cellH;

            String[] columnNames = { "hst_last_update",
                    "hst_operation_type",
                    "hst_username",
                    "geosecma_src",
                    "geosecma_src_id",
                    "stan",
                    "rodzaj",
                    "numer",
                    "uwagi",
                    "city_id",
                    "street_id",
                    "address_point_id",
                    "city_name",
                    "street_name",
                    "address_point_number",
                    "glebokosc_poczatku",
                    "rzedna_poczatku",
                    "glebokosc_konca",
                    "rzedna_konca",
                    "wlasnosc",
                    "kategoria",
                    "rok_budowy",
                    "wlasciciel",
                    "kaskada",
                    "gid_powiazany_z_geosecma_src_id",
                    "glebokosc_przylacza",
                    "rzedna_przylacza",
                    "glebokosc_wlaczenia",
                    "rzedna_wlaczenia",
                    "protokol_odbioru_koncowego",
                    "data_odbioru_koncowego",
                    "nr_dok_geo_powyk",
                    "dane_inwestora",
                    "ocena_stanu_tech",
                    "f_out_type",
                    "f_level_type_pt1",
                    "f_level_type_pt2",
                    "f_level_type_service",
                    "f_level_type_connection",
                    "ulice",
                    "material",
                    "srednica",
                    "gid",
                    "stan_z_ibak",
                    "the_geom_length",
                    "" };

            for (int y = 0; y < 45; y++){
                    cellH = rowH.createCell(y);
                    cellH.setCellValue(columnNames[y]);
            }

            //iterating r number of rows
            for (int r=0;r < wiersze.size(); r++ ) {
                HSSFRow row = sheet.createRow(r+1);

                for (int c = 0; c < 45; c++) {

                        HSSFCell cell = row.createCell(c);
                        cell.setCellValue(wiersze.get(r).getProperties().get("hst_last_update").toString());

                }
            }

//                Iterator it = wiersze.iterator();
//                    while(it.hasNext())
//                    {
//                        HSSFCell cell = row.createCell();
//                        cell.setCellValue(wiersze.toString());
//                    }

        }

        FileOutputStream fileOut = new FileOutputStream("Zmiany_Olsztyn.xlsx");

        //write this workbook to an Outputstream.
        wb.write(out);
        fileOut.flush();
        fileOut.close();
//        return fileOut;
    }

    static class CritObject {
        public ISpatialFindCriterion criterion;
    }

//    public class FindByFeatureClassNameParamsHist {
//        public String featureClassName;
//        public Integer start;
//        public Integer limit;
//        public ISpatialFindCriterion criterion;
//        public List<ISpatialFindOrder> order;
//
//        public FindByFeatureClassNameParamsHist() {}
//
//        public FindByFeatureClassNameParamsHist(String featureClassName,
//                                            Integer start, Integer limit,
//                                            ISpatialFindCriterion criterion,
//                                            List<ISpatialFindOrder> order)
//        {
//            this.featureClassName = featureClassName;
//            this.start = start;
//            this.limit = limit;
//            this.criterion = criterion;
//            this.order = order;
//        }
//    }
//
//    public static class FindByLayerIdsParamsHist {
//        public List<Integer> layerIds;
//        public Integer start;
//        public Integer limit;
//        public ISpatialFindCriterion criterion;
//        public List<ISpatialFindOrder> order;
//
//        public FindByLayerIdsParamsHist() {}
//
//        public FindByLayerIdsParamsHist(List<Integer> layerIds,
//                                    Integer start, Integer limit,
//                                    ISpatialFindCriterion criterion,
//                                    List<ISpatialFindOrder> order)
//        {
//            this.layerIds = layerIds;
//            this.start = start;
//            this.limit = limit;
//            this.criterion = criterion;
//            this.order = order;
//        }
//    }
//
//    public ChangesMonit createChangesMonits(FeatureClass fc, int size){
//        ChangesMonit chm = new ChangesMonit();
//        chm.featureClassChanges = size;
//        chm.featureClassLabel = fc.getLabel();
//        chm.featureClassName = fc.getName();
//
//        return chm;
//    }
//
//    public class ChangesMonit {
//
//        public String featureClassLabel;
//        public String featureClassName;
//        public int featureClassChanges;
//
//    }

}