package org.wap.jilin;
import java.sql.SQLException;
import java.util.Date;

import org.wap.util.DateSwitch;
;

/**
 * Title:       用戶登錄信息檢查
 * Description:
 * Copyright:    
 * Company:      
 * @author:	李哲
 * @version 1.0
 */

public class Controller extends DataBase {

	public Controller() throws Exception {
		super();

	}

	private LoginVO loginvo = null;

	public boolean insertLogin(String userName, String passWord) {

		boolean flag = false;
		try {
			loginvo = executeLoginFirst(userName.trim(), passWord.trim());
			if (loginvo != null) {
				String sqlStr = "";
				sqlStr =
					"update acct_login set gspwd='"
						+ passWord.trim()
						+ "',do_type='N',remarks='N' where gsphone='"
						+ userName.trim()
						+ "'";
				System.out.println(sqlStr);
				try {
					stmt.execute(sqlStr);
					flag = true;

				} catch (SQLException sqle) {
					flag = false;
				}

			} else {

				String sqlStr = "";
				sqlStr =
					"insert into acct_login(gsphone,gspwd,do_type,remarks) values "
						+ "('"
						+ userName.trim()
						+ "','"
						+ passWord.trim()
						+ "','N','N')";
				System.out.println(sqlStr);
				try {
					stmt.execute(sqlStr);
					flag = true;

				} catch (SQLException sqle) {
					flag = false;
				}

			}
			close();
			return flag;
		} catch (Exception e1) {
			// TODO 自動(dòng)生成 catch 塊
			e1.printStackTrace();
			return flag;
		}

	}

	public LoginVO executeLogin(String userName, String passWord)
		throws Exception {

		String strSql =
			"select gsPhone,gspwd,remarks from acct_login where gsPhone = '"
				+ userName.trim()
				+ "'"
				+ " and gspwd = '"
				+ passWord.trim()
				+ "' and do_type='Y'";
		System.out.println(strSql);
		rs = stmt.executeQuery(strSql);
		LoginVO lvo = null;
		if (rs.next()) {
			lvo = new LoginVO();
			lvo.setGsPhone(rs.getString("gsphone"));
			lvo.setGspwd(rs.getString("gspwd"));
			lvo.setRemark(rs.getString("remarks"));
		}
		//close();
		return lvo;

	}

	public LoginVO executeLoginFirst(String userName, String passWord)
		throws Exception {

		String strSql =
			"select gsPhone,gspwd,remarks from acct_login where gsPhone = '"
				+ userName.trim()
				+ "'";
		System.out.println(strSql);
		rs = stmt.executeQuery(strSql);
		LoginVO lvo = null;
		if (rs.next()) {
			lvo = new LoginVO();
			lvo.setGsPhone(rs.getString("gsphone"));
			lvo.setGspwd(rs.getString("gspwd"));
			lvo.setRemark(rs.getString("remarks"));
		}
		//close();
		return lvo;

	}

	public InfoVO executeInfo(String userName) throws Exception {

		String strSql =
			"select gsPhone, UserName,usergroup,localnet,cardid,callgrand,dinnername,phonestate,"
				+ "operdate,do_type,remarks,dinner_type from acct_info where gsPhone = '"
				+ userName.trim()
				+ "' ";

		rs = stmt.executeQuery(strSql);
		//rs = stmt.executeQuery(getSql());
		InfoVO l = null;
		if (rs.next()) {
			l = new InfoVO();
			l.setGsPhone(rs.getString("gsPhone"));
			l.setUserName(rs.getString("UserName"));
			l.setUserGroup(rs.getString("usergroup")); //獲取類型
			l.setLocalnet(rs.getString("localnet"));
			l.setDinnerName(rs.getString("dinnername"));
			l.setPhonestate(rs.getString("phonestate"));
			l.setRemark(rs.getString("remarks"));
			l.setDinnerType(rs.getString("dinner_type"));

		}
		//if(rs == null)rs.close();
		//close();
		return l;

	}

	public BalanceVO executeBalance(String userName) throws Exception {

		String strSql =
			"select gsPhone,fundbalance,monthfee,fee,sumbalance,enddate,do_type,remarks "
				+ "from acct_balance  where gsPhone = '"
				+ userName.trim()
				+ "' ";

		rs = stmt.executeQuery(strSql);
		//rs = stmt.executeQuery(getSql());
		BalanceVO l = null;
		if (rs.next()) {
			l = new BalanceVO();
			l.setGsPhone(rs.getString("gsPhone"));
			l.setFundbalance(rs.getString("fundbalance"));
			l.setMonthfee(rs.getString("monthfee"));
			l.setFee(rs.getString("fee"));
			l.setSumbalance(rs.getString("sumbalance"));
			l.setEnddate(rs.getString("enddate"));
			l.setDoType(rs.getString("do_type"));
			l.setRemark(rs.getString("remarks"));

		}
		//if(rs == null)rs.close();
		close();
		return l;

	}

	public OldBalanceVO executeOldBalance(String userName, String date)
		throws Exception {


		String strSql =
			"select gsPhone,odd_year,fee_local,fee_roam,fee_route,fee_ip,fee_193,fee_special,"
				+ "fee_rent,fee_other,fee_sum,remarks from acct_old_balance  where gsPhone = '"
				+ userName.trim()
				+ "' and odd_year = '"
				+ date.trim()
				+ "' ";

		System.out.println(strSql);
		rs = stmt.executeQuery(strSql);
		//rs = stmt.executeQuery(getSql());
		OldBalanceVO l = null;
		if (rs.next()) {
			l = new OldBalanceVO();
			l.setGsPhone(rs.getString("gsPhone"));
			l.setOddYear(rs.getString("odd_year"));
			l.setFeeLocal(rs.getString("fee_local"));
			l.setFeeRoam(rs.getString("fee_roam"));
			l.setFeeRoute(rs.getString("fee_route"));
			l.setFeeIp(rs.getString("fee_ip"));
			l.setFee193(rs.getString("fee_193"));
			l.setFeeSpecial(rs.getString("fee_special"));
			l.setFeeRent(rs.getString("fee_rent"));
			l.setFeeOther(rs.getString("fee_other"));
			l.setFeeSum(rs.getString("fee_sum"));
			l.setRemark(rs.getString("remarks"));
			//l.setBillYn(rs.getString("bill_yn")); 
			//l.setSendYn(rs.getString("send_yn"));

		}
		//if(rs == null)rs.close();
		close();
		return l;

	}

	public DinnerVO executeDinner(String type) throws Exception {

		String strSql =
			"SELECT dinner_type, dinnername, remarks FROM acct_dinner "
				+ "where dinner_type = '"
				+ type.trim()
				+ "'";

		System.out.println(strSql);
		rs = stmt.executeQuery(strSql);
		//rs = stmt.executeQuery(getSql());
		DinnerVO l = null;
		if (rs.next()) {
			l = new DinnerVO();
			l.setDinnerType(rs.getString("dinner_type"));
			l.setDinnerName(rs.getString("dinnername"));
			l.setDinnerDetail(rs.getString("remarks"));

			//l.setBillYn(rs.getString("bill_yn")); 
			//l.setSendYn(rs.getString("send_yn"));

		}
		//if(rs == null)rs.close();
		close();
		return l;

	}

	//	public boolean updatePassword(String userNumber,String newPassword) throws Exception {
	//		
	//		String strSql = "update acct_password set password = '"+newPassword.trim()+"'" +
	//			",password_new='"+newPassword.trim()+"' where user_number = '"+userNumber.trim()+"'";
	//		try
	//				{
	//					stmt.execute(strSql);
	//					return true;
	//				
	//				}catch (SQLException sqle)
	//				{
	//					return false;
	//				}finally{
	//					close();
	//					}
	//	}

	/**
	 * @return
	 */
	//	public Info getL() {
	//		return l;
	//	}
	//
	//	/**
	//	 * @param login
	//	 */
	//	public void setL(Info login) {
	//		l = login;
	//	}

	public String selectMonth() {
		String year = DateSwitch.DateToString(new Date());
		String month = DateSwitch.DateMonthToString(new Date());
		long lyear = Long.parseLong(year);
		long lmonth = Long.parseLong(month);
		long lmonth1 = 0;
		long lmonth2 = 0;
		String month1 = "";
		String month2 = "";
		String selectYear = "";
		String option = "";
		String date = "";
		String date1 = "";
		String date2 = "";
		if (lmonth == 1) {
			lyear = lyear - 1;
			selectYear = Long.toString(lyear);
			option =
				"<option value = '"
					+ year
					+ "01'>01月</option>"
					+ "<option value = '"
					+ selectYear
					+ "12'>12月</option>"
					+ "<option value = '"
					+ selectYear
					+ "11'>11月</option>";
		} else if (lmonth == 2) {
			lyear = lyear - 1;
			selectYear = Long.toString(lyear);
			option =
				"<option value = '"
					+ year
					+ "02'>02月</option>"
					+ "<option value = '"
					+ year
					+ "01'>01月</option>"
					+ "<option value = '"
					+ selectYear
					+ "12'>12月</option>";
		} else {
			lmonth1 = lmonth - 1;
			lmonth2 = lmonth - 2;
			month1 = "0" + Long.toString(lmonth1);
			month2 = "0" + Long.toString(lmonth2);
			month1 = month1.substring(month1.length()-2,month1.length());
			month2 = month2.substring(month2.length()-2,month2.length());
			date = year + month;
			date1 = year + month1;
			date2 = year + month2;
			option =
				"<option value = '"
					+ date
					+ "'>"
					+ month
					+ "月</option>"
					+ "<option value = '"
					+ date1
					+ "'>"
					+ month1
					+ "月</option>"
					+ "<option value = '"
					+ date2
					+ "'>"
					+ month2
					+ "月</option>";
		}
		return option;

	}

};