package com.sml.eplatform.admin.ui.action;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.sml.eplatform.admin.mdl.User;
import com.sml.eplatform.admin.util.AuditLogHelper;
import com.sml.eplatform.admin.util.EncryptionUtil;
import com.sml.eplatform.admin.util.ImportAWIUserAccountUtil;
import com.sml.eplatform.admin.util.ImportExternalUserUtil;
import com.sml.eplatform.base.dao.DbManager;
import com.sml.eplatform.common.util.ExcelUtil;
import com.sml.eplatform.common.util.JsonString;
import com.sml.eplatform.common.util.StringUtils;

public class ImportUserAccountAction extends Action {

	private final static String[] exlColumnTtile = new String[]{
		"loginId", "userName", "passWord", "defaultLanguage", "phoneNumber", "email",
		"companyAcountLoginId"};

	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws IOException, Exception {
		request.setCharacterEncoding("UTF-8");
		try {
			User user = AdminSessionHelper.getUser(request);
			Map<String, Object> result = new HashMap<String, Object>();
			FileItemFactory factory = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			List item = upload.parseRequest(request);
			Iterator it = item.iterator();
			FileItem fileItem = (FileItem) it.next();
			InputStream in = fileItem.getInputStream();
            String message = importUserAccount(in);
            System.out.println("Excel Error Message: "+message);
            result.put("result", message);
            AuditLogHelper.log(user, AuditLogHelper.CREATE,
    				"Import User Account User", user.getUserName(), user
    						.getIpAddress());
    		JsonString.writeJsonString(response, result);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}

	public String importUserAccount(InputStream in){
		String errorMsg = null;
		List<Map> list = new ArrayList<Map>();
		errorMsg = readExcelRowColumn(in,list);
		if (errorMsg != null) {
			return errorMsg;
		}
		insertUserProperty(list);
		return null;
	}

	private String readExcelRowColumn(InputStream in, List<Map> list) {
		Workbook workbook = null;
		try {
			workbook = Workbook.getWorkbook(in);
			Sheet sheet = workbook.getSheet(0);
			int excelRecordCount = ExcelUtil.getRightRows(sheet);
			if (excelRecordCount <= 1) {
				return "The excel is empty, please check.";
			}
			Cell[] cellHead = sheet.getRow(0);
			Map<String, Integer> headMap = new HashMap<String, Integer>();
			String errorMsg = ExcelUtil.checkColumnTitle(cellHead, headMap, exlColumnTtile);
			if (errorMsg != null && !"".equals(errorMsg)) {
				return errorMsg;
			}
			for (int i = 1; i < excelRecordCount; i++) {
				Map<String, String> map = new HashMap<String, String>();
				for (String columnValue : exlColumnTtile) {
					String exlContent = sheet.getCell(headMap.get(columnValue), i).getContents();
					map.put(columnValue, exlContent.trim());
				}
				list.add(map);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {