package com.sxit.wap.bookcard;
import java.sql.*;
import java.util.*;
import com.sxit.wap.termuser.*;
import com.sxit.wap.bookterm.*;
import com.sxit.wap.common.*;
import com.sxit.wap.exception.*;

public class BookCardBean extends BookCardDao{
  public static boolean isExist(String userMdn) throws SysException {
    String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE USER_MDN = '" +
        userMdn + "'";
    return getRowCountBySql(sql) > 0;
  }

  public static boolean regUserCard(BookCardModel bookcard) throws SysException,
      UpdateException, AppException {
    bookcard.setId(getSequenceNextValue());
    BookCardModel flag = insert(bookcard);
    if (flag == null) {
      return false;
    }
    else {
      return true;
    }
  }

  public static boolean newCard(BookCardModel bookcard) throws SysException {
    bookcard.setId(getSequenceNextValue());
    Connection conn = null;
    boolean flag = false;
    try {
      conn = Database.getConnection();
      conn.setAutoCommit(false);
      String sql = "UPDATE WAP_BOOK_USER SET CARD_ID =" + bookcard.getId() +
          " WHERE USER_MDN = '" + bookcard.getUserMdn() + "'";
      updateBySql(sql);
      insert(bookcard);
      flag = true;
      conn.commit();
    }
    catch (AppException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (UpdateException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SysException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SQLException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
      throw new SysException("SQLException while execute subscribe");
    }
    finally {
      try {
        conn.setAutoCommit(true);
      }
      catch (Exception ex) {}
      try {
        conn.close();
      }
      catch (Exception ex) {}
    }
    return flag;
  }
public static BookCardModel newCard(String userMdn,String userName,String userEmail)throws SysException{
  Connection conn = null;
  boolean flag = false;
  BookCardModel bookcard = new BookCardModel();
  try {
    bookcard.setId(getSequenceNextValue());
    bookcard.setUserMdn(userMdn);
    bookcard.setUserName(userName);
    bookcard.setUserEmail(userEmail);
    conn = Database.getConnection();
    conn.setAutoCommit(false);
    String sql = "UPDATE WAP_BOOK_USER SET CARD_ID =" + bookcard.getId() +
        " WHERE USER_MDN = '" + bookcard.getUserMdn() + "'";
    updateBySql(sql);
    insert(bookcard);
    flag = true;
    conn.commit();
  }
  catch (AppException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (UpdateException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (SysException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (SQLException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
    throw new SysException("SQLException while execute subscribe");
  }
  finally {
    try {
      conn.setAutoCommit(true);
    }
    catch (Exception ex) {}
    try {
      conn.close();
    }
    catch (Exception ex) {}
  }
  if(flag){
    return bookcard;
  }
    return null;
}
  public static boolean updateByColumnn(String cardId, String colName,
                                        String colValue) throws SysException {
    try {
      String sql = "UPDATE WAP_BOOK_CARD SET " + colName + " = '" + colValue +
          "' WHERE ID = " + cardId;
      if (updateBySql(sql) == 1) {
        return true;
      }
      else {
        return false;
      }
    }
    catch (Exception ex) {
      return false;
    }
  }

  public static BookCardModel newUserCard(String userMdn, String termId,
                                          String mdn, String userName,
                                          String userEmail) throws SysException,
      UpdateException, AppException {
    BookCardModel bookcard = new BookCardModel();
    bookcard.setId(getSequenceNextValue());
    bookcard.setUserMdn(mdn);
    bookcard.setUserName(userName);
    bookcard.setUserEmail(userEmail);
    Connection conn = null;
    boolean flag = false;
    try {
      conn = Database.getConnection();
      conn.setAutoCommit(false);
      insert(bookcard);
      BookTermUserBean.newTermUser(userMdn, Integer.parseInt(termId),
                                   (int) bookcard.getId());
      flag = true;
      conn.commit();
    }
    catch (AppException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (UpdateException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SysException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SQLException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
      throw new SysException("SQLException while execute subscribe");
    }
    finally {
      try {
        conn.setAutoCommit(true);
      }
      catch (Exception ex) {}
      try {
        conn.close();
      }
      catch (Exception ex) {}
    }
    if (flag) {
      return bookcard;
    }
    else {
      return null;
    }
  }

  public static BookCardModel updateCard(int id, String userJob, String userCom,
                                         String userTel, String userFax,
                                        String userAdress) throws SysException,UpdateException,FinderException, AppException {
    BookCardModel bookcard = new BookCardModel();
    Connection conn = null;
    boolean flag = false;
    try {
      conn = Database.getConnection();
      conn.setAutoCommit(false);
      bookcard = findByPK(id);
      bookcard.setUserJob(userJob);
      bookcard.setUserCom(userCom);
      bookcard.setUserTel(userTel);
      bookcard.setUserFax(userFax);
      bookcard.setUserAdress(userAdress);
      bookcard = update(bookcard);
      flag = true;
      conn.commit();
    }
    catch (AppException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (UpdateException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SysException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
    }
    catch (SQLException e) {
      e.printStackTrace();
      try {
        conn.rollback();
      }
      catch (Exception ex) {}
      throw new SysException("SQLException while execute subscribe");
    }
    finally {
      try {
        conn.setAutoCommit(true);
      }
      catch (Exception ex) {}
      try {
        conn.close();
      }
      catch (Exception ex) {}
    }
    if (flag) {
      return bookcard;
    }
    else {
      return null;
    }
  }
  public static Collection getBookCardByUserMdn(String userMdn)throws SysException{
    String sql = "SELECT C.* FROM WAP_BOOK_CARD C,WAP_BOOK_USER U WHERE U.USER_MDN ='" + userMdn + "' AND U.CARD_ID = C.ID";
    System.out.println(sql);
    return queryBySql(sql);
  }
  public static boolean newCard(BookCardModel bookcard,int tid,String userMdn) throws SysException,UpdateException,FinderException, AppException {
  Connection conn = null;
  boolean flag = false;
  try {
    conn = Database.getConnection();
    conn.setAutoCommit(false);
    insert(bookcard);
    BookTermUserModel termuser = new BookTermUserModel();
    termuser.setTermId(tid);
    termuser.setUserMdn(userMdn);
    termuser.setCardId(bookcard.getId());
    BookTermUserBean.insert(termuser);
    flag = true;
    conn.commit();
  }
  catch (AppException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (UpdateException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (SysException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
  }
  catch (SQLException e) {
    e.printStackTrace();
    try {
      conn.rollback();
    }
    catch (Exception ex) {}
    throw new SysException("SQLException while execute subscribe");
  }
  finally {
    try {
      conn.setAutoCommit(true);
    }
    catch (Exception ex) {}
    try {
      conn.close();
    }
    catch (Exception ex) {}
  }
return flag;
}
}