package id.co.hanoman.service.as400;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import id.co.hanoman.service.PersistanceServiceInterface;
import id.co.hanoman.service.ServiceUtil;
import id.co.hanoman.service.ePayment.AppSetting;
import id.co.hanoman.service.mwapp.SysParam;


public class AS400ServiceInterface {
	protected Log log = LogFactory.getLog(this.getClass());
	public Branch[] getBranchList(String mwappDs, String branchCode) {
		return new Branch[] {};
	}
	
	public Branch[] getBranchList(String mwappDs, BigDecimal branchCodeStart, BigDecimal branchCodeEnd) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Branch[]  result = null;

		try {
			System.out.println("before connect");
			conn = initialAS400Connection();
			System.out.println("after connect");

			String as400Schema = getAppSettingValue(mwappDs, "EPAYMENT_AS400_SCHEMA", "I");			
			String sql = "SELECT JHDATA.JDBR, JHDATA.JDNAME FROM PAR" + as400Schema + "SIBS.JHDATA JHDATA WHERE JHDATA.JDBR >= ? AND JHDATA.JDBR <= ?";
			ps = conn.prepareStatement(sql);
			ps.setBigDecimal(1, branchCodeStart);
			ps.setBigDecimal(2, branchCodeEnd);
			rs = ps.executeQuery();
			System.out.println("BRANCH");
			List<Branch> list = new ArrayList<Branch>();
			while (rs.next()) {
				String code = rs.getString(1);
				String name = rs.getString(2);
				Branch branch = new Branch();
				branch.setCode(code);
				branch.setName(name);
				list.add(branch);
			}
			
			result = new Branch[list.size()];
			result = list.toArray(result);

		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					System.out.println("-------------- Closing Connection AS400 ---------------");
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		
		return result;
	}

	public KeperluanBayar[] getKeperluanBayar(String mwappDs, String univCode) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		KeperluanBayar[]  result = null;

		try {
			System.out.println("before connect");
			conn = initialAS400Connection();
			System.out.println("after connect");

			String as400Schema = getAppSettingValue(mwappDs, "EPAYMENT_AS400_SCHEMA", "I");			
			String sql = "SELECT UPLPAR.UNPYTP, UPLPAR.UNPYDS FROM BW" + as400Schema + "DAT.UPLPAR AS UPLPAR WHERE UPLPAR.UNVCOD = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, univCode);
			rs = ps.executeQuery();

			List<KeperluanBayar> list = new ArrayList<KeperluanBayar>();
			while (rs.next()) {
				String code = rs.getString(1);
				String name = rs.getString(2);
				KeperluanBayar keperluanBayar = new KeperluanBayar();
				keperluanBayar.setCode(code);
				keperluanBayar.setName(name);
				list.add(keperluanBayar);
			}
			
			result = new KeperluanBayar[list.size()];
			result = list.toArray(result);

		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					System.out.println("-------------- Closing Connection AS400 ---------------");
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		return result;
	}
	
	public Teller getTellerByGroupAndId(String mwappDs, String group, String telId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Teller teller = null;

		try {
			System.out.println("before connect");
			conn = initialAS400Connection();
			System.out.println("after connect");
					
			String as400Schema = getParamValue(mwappDs, "MDW_AS400_SCHEMA");
			String sql = "SELECT TLTEL.TLNUM, TLTEL.TLNAME, TLCTLU.TLCTL FROM SET" + as400Schema + "ENV.TLTEL AS TLTEL JOIN SET" + as400Schema + "ENV.TLCTLU AS TLCTLU ON TLTEL.TLTBRN = TLCTLU.TLBRN# WHERE TLTEL.TLTTYP = ? AND TLTEL.TLNUM = ?";
			

			ps = conn.prepareStatement(sql);
			ps.setString(1, group);
			ps.setString(2, telId);
			rs = ps.executeQuery();

			List<Teller> list = new ArrayList<Teller>();
			if (rs.next()) {
				teller = new Teller();
				String tellerId = rs.getString(1);
				String tellerName = rs.getString(2);
				String ctrlUnitId = rs.getString(3);				
				teller.setTellerName(tellerName);
				teller.setTellerId(tellerId);
				teller.setCtrlUnitId(ctrlUnitId);
				teller.setGroup(group);
			}
			

		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					System.out.println("-------------- Closing Connection AS400 ---------------");
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		return teller;
	}

	public Teller[] getTellerList(String mwappDs, String group) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Teller[] result = null;

		try {
			System.out.println("before connect");
			conn = initialAS400Connection();
			System.out.println("after connect");

			String as400Schema = getParamValue(mwappDs, "MDW_AS400_SCHEMA");	
			
			String sql = "SELECT TLTEL.TLNUM, TLTEL.TLNAME, TLCTLU.TLCTL FROM SET" + as400Schema + "ENV.TLTEL AS TLTEL JOIN SET" + as400Schema + "ENV.TLCTLU AS TLCTLU ON TLTEL.TLTBRN = TLCTLU.TLBRN# WHERE TLTEL.TLTTYP = ? ORDER BY TLTEL.TLNUM";
			ps = conn.prepareStatement(sql);
			ps.setString(1, group);
			rs = ps.executeQuery();

			List<Teller> list = new ArrayList<Teller>();
			while (rs.next()) {
				String tellerId = rs.getString(1);
				String tellerName = rs.getString(2);
				String ctrlUnitId = rs.getString(3);				
				Teller teller = new Teller();				
				teller.setTellerName(tellerName);
				teller.setTellerId(tellerId);
				teller.setCtrlUnitId(ctrlUnitId);
				teller.setGroup(group);
				list.add(teller);
			}
			
			result = new Teller[list.size()];
			result = list.toArray(result);

		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					System.out.println("-------------- Closing Connection AS400 ---------------");
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		return result;
	}

	public BillerQuery[] queryBiller(String mwappDs, String filter) {
		Connection xconn = null;
		Statement stmtg = null;
		ResultSet rsg = null;
		BillerQuery[]  rs = null;

		
		try {
			System.out.println("before connect");
			xconn = initialAS400Connection();
			System.out.println("after connect");

			String as400Schema = getAppSettingValue(mwappDs, "EPAYMENT_AS400_SCHEMA", "I");
			stmtg = xconn.createStatement();
			if(filter == null) filter = new String();
			String sql = "SELECT UNCODE, UNNAME FROM BW" + as400Schema + "DAT.unlist WHERE 1=1 " + filter;
			rsg = stmtg.executeQuery(sql);

			List<BillerQuery> list = new ArrayList<BillerQuery>();
			
			while (rsg.next()) {
				String code = rsg.getString(1).trim();
				String name = rsg.getString(2).trim();
				BillerQuery billerQuery = new BillerQuery();
				billerQuery.setCode(code);
				billerQuery.setName(name);
				list.add(billerQuery);
			}
			
			rs = new BillerQuery[list.size()];
			rs = list.toArray(rs);
			System.out.println("Total Biller Query ::::: "+rs.length);
			
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rsg != null) {
				try {
					rsg.close();
				} catch (Exception e) {
				}
			}
			if (stmtg != null) {
				try {
					stmtg.close();
				} catch (Exception e) {
				}
			}
			if (xconn != null) {
				try {
					System.out.println("------------------------- Closing Connection AS400 -------------------------");
					xconn.close();
				} catch (Exception e) {
				}
			}
		}
		return rs;
	}
	
	
	private Connection initialAS400Connection() {
		Connection conn = null;

		try {
						
//          ----------------- jdbc -----------------------			
//			System.out.println("connecting as400 jdbc");
//			Class.forName("com.ibm.as400.access.AS400JDBCDriver");
//			conn = DriverManager.getConnection("jdbc:as400://172.18.30.104/BWIDAT", "BTNSOA", "BTNSOA");			

			
//          ----------------- jndi -----------------------
			System.out.println("connecting as400 datasource");			
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("jdbc/as400/btn_ds");
			conn = ds.getConnection();
			
			System.out.println("connected as400");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		}

		return conn;
	}
	
	private String getAppSettingValue(String ds, String code, String defaultValue) throws Exception {
		String settingValue = defaultValue;

		try {
			if (code == null || "".equals(code)) {
			} else {				
				PersistanceServiceInterface psiEpay = ServiceUtil.getDaoClient(null);
				AppSetting appSetting = (AppSetting) psiEpay.queryById(ds, "AppSetting", code, null);	
				if (appSetting != null) {
					settingValue = appSetting.getSettingValue();
					if (log.isDebugEnabled())
						log.debug("settingValue[" + code + "] :: " + settingValue);
					if (settingValue == null || "".equals(settingValue))
						settingValue = defaultValue;
				}
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(), e);
		}

		return settingValue;
	}	
	
	private String getParamValue(String ds, String code) throws Exception {
		String paramValue = null;

		try {
			if (code == null || "".equals(code)) {
			} else {				
				PersistanceServiceInterface psiEpay = ServiceUtil.getDaoClient(null);
				SysParam sysParam = (SysParam) psiEpay.queryById(ds, "SysParam", code, null);	
				if (sysParam != null) {
					paramValue = sysParam.getParamValue();
					if (log.isDebugEnabled())
						log.debug("paramValue[" + code + "] :: " + paramValue);
					if (paramValue == null || "".equals(paramValue))
						paramValue = null;
				}
				
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(), e);
		}
		return paramValue;
		
	}	

}