package frame.file.office.excel;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.slf4j.Logger;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import sun.awt.geom.AreaOp.AddOp;

import frame.file.AppendMode;
import frame.file.DeleteMode;
import frame.file.FileIO;
import frame.file.FileIOContainer;
import frame.file.FileIOContext;
import frame.file.FileIOItem;
import frame.file.UploadResult;
import frame.file.office.FileRecord;
import frame.file.office.IOMappingItemRuntime;
import frame.persist.NamedSQL;
import frame.persist.SQLRunner;
import frame.upload.FileItem;
import frame.util.Util;

public class ExcelLoader {
	
	private Logger logger;
    private static int Count_Batch = 5000;
    private UploadResult result;
    private long count;

    private XMLReader parser;  
    private InputSource sheetSource;  
    private int index = 0;
	private SheetHandler handler;  
	ArrayList<String> cloumnList;
    public ExcelLoader(Logger logger) {
        this.logger = logger;
    }
    
    public boolean loadFiles(UploadResult result,String ioCode, List<FileItem> fiList) throws Exception {
    	//TODO   多sheet 或者说 多file 组成一次task
    	for (FileItem fileItem : fiList) {
    		loadOneFile(result,ioCode, fileItem);
			InputStream inputStream = fileItem.getInputStream();
			OPCPackage pkg = OPCPackage.open(inputStream);  
			XSSFReader xssfReader = new XSSFReader(pkg);  
			SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();  
	        StylesTable stylesTable = xssfReader.getStylesTable();  
	        sheet = xssfReader.getSheet("rId1");  
	        parser = fetchSheetParser(sharedStringsTable, stylesTable);  
	        sheetSource = new InputSource(sheet); 
		}
		return true;
	}
    
    private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {  
        XMLReader parser =  XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");  
        handler = new SheetHandler(sharedStringsTable, stylesTable){

			@Override
			protected void outputRow(String[] datas, int[] rowTypes, int rowIndex, FileIOItem fileIOItem) {
				if (rowIndex == 0) {
					cloumnList = new ArrayList<String>();
				}
				else if (fileIOItem.getFromRowNo() > (rowIndex - 1)) {
					
					for(int i = 0; i < datas.length; i++) {
						String oneCloumn = datas[i];
						
						if(cloumnList == null) {
							cloumnList = new ArrayList<String>();
						}
						
						cloumnList.set(i, oneCloumn);
					}
				}
				else {
					//add one  row
				}
				
			}
        };
        parser.setContentHandler(handler);  
        
        return parser;  
    }  
    
    public void loadOneFile(UploadResult result, String ioCode, FileItem fileItem) throws Exception {
        this.result = result;
        
        //FileRecord fileRecord = new FileRecord(fileItem);
        //1. repository
        //2
        FileIO fileIO = FileIOContainer.get(ioCode);
        
        if (fileIO == null) {
            logger.error("can not find fileIO: " + ioCode);
            result.fail("error_IONotExist", "上载定义没有配置");
            return;
        }

        //1. fire before process
        fileIO.execHandlers(result, "begin");

        //2. import file
        doLoadOneFile(result, fileItem, fileIO);

        //3. move to standard table
        processData(result, fileIO);

        //4. after save
        fileIO.execHandlers(result, "end");

        //5. backup
        //fileRecord.moveToBackup();
    }

    public void testDataProcess(UploadResult result, String ioCode) {
        FileIO fileIO = FileIOContainer.get(ioCode);

        if (fileIO == null) {
            logger.error("can not find fileIO: " + ioCode);
            result.fail("error_IONotExist", "上载定义没有配置");
            return;
        }

        processData(result, fileIO);
    }

    private void doLoadOneFile(UploadResult result, FileItem fileItem, FileIO fileIO) {
        logger.info("正在导入文件: " + fileItem.getName() + "...");

        List<FileIOItem> ioItemList = fileIO.getItemList(FileIOItem.TypeCode_Import);

        //1. delete temp table data
        for (FileIOItem ioItem : ioItemList) {
            String toName = ioItem.getToName();

            NamedSQL namedSql = NamedSQL.getInstance("emptyTable");
            namedSql.setParam("tablename", toName);
            SQLRunner.execSQL(namedSql);
        }
        
        //2.
        
        try {
        	
            for (FileIOItem ioItem : ioItemList) {
                readOneSheet(fileItem, ioItemList);
                ioItem.execHandlers(result);
            }
        }
        catch (Exception e) {
        	logger.error(e.getMessage());
            result.fail("error_parseData", "文件解析错误： " + e.getMessage());
        }
        finally {
            excelConn.Close();
        }
    }

    private void readOneSheet(FileItem fileItem, List<FileIOItem> ioItemList) {
    	InputStream inputStream = fileItem.getInputStream();
		OPCPackage pkg = OPCPackage.open(inputStream);  
		XSSFReader xssfReader = new XSSFReader(pkg);  
		SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();  
        StylesTable stylesTable = xssfReader.getStylesTable();  
        parser = fetchSheetParser(sharedStringsTable, stylesTable);  
        XSSFReader.SheetIterator sheetsDataIterator = (SheetIterator) xssfReader.getSheetsData();
        
        while (sheetsDataIterator.hasNext()) {
			InputStream sheetData = sheetsDataIterator.next();
			String sheetName = sheetsDataIterator.getSheetName();
			
			for (FileIOItem fileIOItem : ioItemList) {	
				String fromName = fileIOItem.getFromName();
				//TODO  多sheet 问题 1.多sheet 同一张表  不同字段 一条数据    2.多sheet 不同表 相同临时表   （不同字段  不同数据）3.多sheet不同表不同临时表 1对1
				if (fromName.equalsIgnoreCase(sheetName)) {
					
					InputSource inputSource = new InputSource(sheetData);
					parser.parse(inputSource);
					sheetData.close();
				}
			}
			
		}
        

        //2. create mapping runtime
        List<String> excelColumns = getExcelFields(sheetname);
        ioItemList.createMappingRuntime(excelColumns, new FileIOContext(result, ioItemList));
        
        //3. create select sql
        String excelDataString = createSelectSql(sheetname, ioItemList);
        OleDbCommand command = new OleDbCommand(excelDataString, excelConn);
        
        //4. read data
        OleDbDataReader dataReader = command.ExecuteReader();
        try {
            SqlBulkCopy bulkCopy = null;
            try {
                bulkCopy = new SqlBulkCopy(SQLRunner.connStr);

                bulkCopy.DestinationTableName = ioItemList.getToName();

                List<IOMappingItemRuntime> insertMappingRuntime = ioItemList.getInsertMappingRuntime();
                for (IOMappingItemRuntime itemRuntime : insertMappingRuntime) {
                    bulkCopy.ColumnMappings.Add(itemRuntime.getFromField(), itemRuntime.getToField());
                }

                bulkCopy.BatchSize = Count_Batch;
                bulkCopy.NotifyAfter = Count_Batch;
                bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(onBatchCopyedEvent);

                bulkCopy.WriteToServer(dataReader);
            }
            finally {
                if (bulkCopy != null) {
                    bulkCopy.Close();
                }
            }
        }
        finally {
            dataReader.Close();
        }
    }

    private void processData(UploadResult result, FileIO fileIO) {
        try {
            List<FileIOItem> itemList = fileIO.getItemList(FileIOItem.TypeCode_DB);

            //0. init 
            for (FileIOItem item : itemList) {
                FileIOContext context = (FileIOContext)(item.createContext(result));
                item.createMappingRuntime(null, context);
            }
            
            //1. process temple data
            for (FileIOItem ioItem : itemList) {
                ioItem.execHandlers(result, "beforemove");
            }

            if (!result.isSuccess()) {
                return;
            }

            //2. move data to standard table
            for (FileIOItem ioItem : itemList) {
                if (ioItem.isStandardMove()) {
                    transferData(result, ioItem);
                    continue;
                }
                else {
                    ioItem.execHandlers(result, "move");
                }
            }

            if (!result.isSuccess()) {
                return;
            }

            //3. process standard table
            for (FileIOItem ioItem : itemList) {
                ioItem.execHandlers(result, "aftermove");
            }

            if (!result.isSuccess()) {
                return;
            }
        }
        catch (Exception e) {
            logger.error(result.workingSQL.getSQLString());
            
            e.printStackTrace();
            throw e;
        }
    }

    private void transferData(UploadResult result, FileIOItem ioItem) throws Exception {
        FileIOContext context = (FileIOContext) (ioItem.getContext());

        AppendMode appendMode = ioItem.getAppendMode();
        DeleteMode deleteMode = ioItem.getDeleteMode();
        int cnt = 0;

        if (AppendMode.Append == appendMode) {
            cnt = execute("transferAll", context);
            result.addMessage("插入数据：" + cnt);
        }
        else if (AppendMode.ClearAndAppend == appendMode) {
            //1. clear data
            if (ioItem.existsFilterFieldValues()) {
                cnt = execute("deleteByFilter", context);
            }
            else {
                cnt = execute("deleteAll", context);
            }
            result.addMessage("删除数据：" + cnt);

            //2. transfer data
            cnt = execute("transferAll", context);
            result.addMessage("插入数据：" + cnt);
        }
        else if (AppendMode.UpdateChanged == appendMode) {
            //1. delete not exists data
            if (DeleteMode.HardDelete == deleteMode) {
                cnt = execute("hardDeleteNotExists", context);
                result.addMessage("删除数据：" + cnt);
            }
            else if (DeleteMode.SoftDelete == deleteMode) {
                cnt = execute("softDeleteNotExists", context);
                result.addMessage("软删除数据：" + cnt);
            }

            //2. update exists data
            cnt = execute("updateExists", context);
            result.addMessage("更新数据：" + cnt);

            //3. insert append data
            cnt = execute("insertAppend", context);
            result.addMessage("插入数据：" + cnt);
        }
        else if (AppendMode.InsertChanged == appendMode) {
            //1. update exists data
            cnt = execute("updateExists", context);
            result.addMessage("更新数据：" + cnt);

            //2. insert append data
            cnt = execute("insertAppend", context);
            result.addMessage("插入数据：" + cnt);
        }
    }

    private int execute(String sqlName, FileIOContext context) throws Exception {
        NamedSQL namedSQL = NamedSQL.getInstance(sqlName);
        context.setParametersTo(namedSQL);
        return SQLRunner.execSQL(namedSQL);
    }

    private void onBatchCopyedEvent(object sender, SqlRowsCopiedEventArgs e) {
        count = count + e.RowsCopied;
        logger.info("已导入" + count + "条...");
    }

    private String getSheetName(OleDbConnection excelConn, FileIOItem ioItem) {
        DataTable sheetNames = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new String[] { null, null, null, "Table" });

        String designname = FileIOItem.extractSheetName(ioItem.getFromName()).toLowerCase();
        List<String> sheetNameList = new ArrayList<String>(); 

        //1.
        for (int i = 0; i < sheetNames.Rows.Count; i++) {
            String sheetname = sheetNames.Rows[i][2].ToString();
           
            String lower = sheetname.trim().toLowerCase();
            if (designname.equals(lower)) {
                return sheetname;
            }

            sheetNameList.add(lower);
        }

        //2.
        if (designname.indexOf("$") >= 0) {
            designname = designname.replace("$", "sheet");
            String lower = designname.trim().toLowerCase();

            if (!"$".equals(lower.substring(lower.length() - 1))) {
                lower = lower + "$";
            }

            for (int i = sheetNameList.size() - 1; i >= 0; i--) {
                String sheetname = sheetNameList.get(i);



                if (sheetname.equals(lower)) {
                    return sheetname;
                }
            }
        }

        //3.
        for (int i = sheetNameList.size() - 1; i >= 0; i--) {
            String sheetname = sheetNameList.get(i);

            if ("$".equals(sheetname.substring(sheetname.length() - 1))) {
                return sheetname;
            }
        }

        return null;
    }

    private List<String> getExcelFields(String sheetname) {
        List<String> result = new ArrayList<String>();
        
        DataTable columns = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, sheetname, null });

        for (int i = 0; i < columns.Rows.Count; i++) {
            String columnname = columns.Rows[i].ItemArray[3].ToString();
            result.add(columnname);
        }

        return result;
    }

    private String createSelectSql(String sheetname, FileIOItem ioItem) {
        StringBuilder result = new StringBuilder();

        List<IOMappingItemRuntime> itemRuntimes = ioItem.getInsertMappingRuntime();

        result.append("Select ");

        Boolean empty = true;

        for (IOMappingItemRuntime itemRuntime : itemRuntimes) {
            if (!empty) {
                result.append(", ");
            }

            result.append("[");
            result.append(itemRuntime.getFromField());
            result.append("]");

            empty = false;
        }

        sheetname = Util.deleteQuoted(sheetname);
        result.append(" FROM [").append(sheetname).append("]");

        return result.toString();
    }

    public void getExcelMeta() {
        String excelConnString = String.Format(Configer.readExcelConnString, "D:/工作/root/local/NPT sales YTD 2014-2_A.xlsx");
        logger.write("正在导入文件...");

        OleDbConnection excelConn = new OleDbConnection(excelConnString);
        try {
            excelConn.Open();
            logger.write("正在打开文件，读取数据...");
            DataTable datatable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new String[] { null, null, null, "Table" });

            int cnt = datatable.Rows.Count;
            int fieldCnt = datatable.Columns.Count;

            for (int i = 0; i < cnt; i++) {
                DataRow row = datatable.Rows[i];

                String value = "";
                for (int j = 0; j < fieldCnt; j++) {
                    value = value + ", " + row[j].ToString();
                }

                logger.write(value);
            }
        }
        finally {
            excelConn.Close();
        }
    }

}