package com.sxit.wap.termuser;import java.sql.*;import java.util.*;import com.sxit.wap.bookcard.*;import com.sxit.wap.common.*;import com.sxit.wap.exception.*;public class BookTermUserBean extends BookTermUserDao{  public static Collection getTermUser(int termId)throws SysException{    String sql = "SELECT C.* FROM WAP_BOOK_TERM_USER U, WAP_BOOK_CARD C WHERE U.TERM_ID =" + termId + " AND C.ID = U.CARD_ID";    return queryBySql(sql);   }   public static void newTermUser(String userMdn,int termId,int cardId)throws SysException,UpdateException,AppException{     BookTermUserModel termuser =new BookTermUserModel();     termuser.setCardId(cardId);     termuser.setUserMdn(userMdn);     termuser.setTermId(termId);     insert(termuser);   }   public static Collection getAllUser(String userMdn)throws SysException{  String sql = "SELECT C.*,T.ID,T.TERM_ATTR FROM WAP_BOOK_TERM_USER U, WAP_BOOK_CARD C,WAP_BOOK_TERM T WHERE C.ID = U.CARD_ID AND U.TERM_ID = T.ID AND T.USER_MDN = '" + userMdn + "'";  return queryBySql(sql); } public static Collection getUserByName(int type,String userMdn,String name)throws SysException{  String sql = "SELECT C.*,T.ID,T.TERM_ATTR FROM WAP_BOOK_TERM_USER U, WAP_BOOK_CARD C,WAP_BOOK_TERM T WHERE C.ID = U.CARD_ID AND U.TERM_ID = T.ID AND T.USER_MDN = '" + userMdn + "' AND ";  sql += (type==2)?(" C.USER_MDN LIKE '" + name + "%'"):(" C.USER_NAME LIKE '" + name + "%'");  System.out.println(sql);  return queryBySql(sql); } public static boolean update(int oldid,int newid,int cid)throws SysException,UpdateException{  String sql = "UPDATE WAP_BOOK_TERM_USER SET TERM_ID = " +  newid + " WHERE TERM_ID = " + oldid + " AND CARD_ID = " + cid;    System.out.println(sql);  return updateBySql(sql) == 1; } public static boolean isExist(String from,String to)throws SysException{   String sql = "SELECT COUNT(*) FROM WAP_BOOK_TERM_USER U,WAP_BOOK_CARD C WHERE U.USER_MDN ='" + to + "' AND U.CARD_ID = C.ID AND C.USER_MDN = '" + from + "'";   return getRowCountBySql(sql)==1; } public static boolean sendcard(String from,String to)throws SysException{   //String sql ="INSERT INTO WAP_BOOK_TERM_USER (SELECT T.ID TID,C.ID CID,T.USER_MDN FROM WAP_BOOK_USER U,WAP_BOOK_CARD C,WAP_BOOK_TERM T WHERE U.CARD_ID = C.ID AND U.USER_MDN = '" + from + "' AND T.TERM_ATTR = 0 AND T.USER_MDN = '" + to + "')";   //return updateBySql(sql) == 1;   Connection conn = null;   boolean flag = false; try {   conn = Database.getConnection();   conn.setAutoCommit(false);   int id = BookCardBean.getSequenceNextValue();   String sql = "INSERT INTO WAP_BOOK_CARD (SELECT " + id +",C.USER_MDN,C.USER_NAME,C.USER_JOB,C.USER_COM,C.USER_TEL,C.USER_FAX,C.USER_EMAIL,C.USER_ADRESS,'' FROM WAP_BOOK_USER U,WAP_BOOK_CARD C WHERE U.CARD_ID = C.ID AND U.USER_MDN = '" + from + "')";   System.out.println("1:" + sql);   updateBySql(sql);   sql = "INSERT INTO WAP_BOOK_TERM_USER (SELECT T.ID," +id+ ",'" + to + "' FROM WAP_BOOK_TERM T WHERE T.term_ATTR = 0 AND T.USER_MDN = '" +to+ "')";   System.out.println("2:" + sql);   updateBySql(sql);   flag = true;   conn.commit(); } 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 boolean delete(int tid,int cid)throws SysException{//  String sql = "DELETE FROM WAP_BOOK_TERM_USER WHERE TERM_ID = " + tid + " AND CARD_ID = " + cid;//  return updateBySql(sql) == 1;//}public static boolean deleteAll(int tid)throws SysException{  String sql = "DELETE FROM WAP_BOOK_TERM_USER WHERE TERM_ID = " + tid;  return updateBySql(sql) >= 0;}public static int getRowCountOfAll(int tid)throws SysException{  String sql = "SELECT COUNT(*) FROM WAP_BOOK_TERM_USER U, WAP_BOOK_CARD C WHERE U.TERM_ID =" + tid + " AND C.ID = U.CARD_ID";  return getRowCountBySql(sql);}public static Collection queryUserByTid(int tid,int beginRow, int endRow)throws SysException{  String sql = "SELECT C.* FROM WAP_BOOK_TERM_USER U, WAP_BOOK_CARD C WHERE U.TERM_ID =" + tid + " AND C.ID = U.CARD_ID";  return queryBySql(sql,beginRow,endRow);}public static boolean delete(int tid,int cid)throws SysException{  Connection conn = null;boolean flag = false;try {  conn = Database.getConnection();  conn.setAutoCommit(false);  String sql = "DELETE FROM WAP_BOOK_TERM_USER WHERE TERM_ID =" + tid + " AND CARD_ID = " + cid;  int i = updateBySql(sql);  BookCardBean.delete(cid);  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;}}