package com.service.weihua.sign;

import com.service.weihua.sign.CustomerDTO;
import com.service.weihua.sign.customerDAOSysException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.service.weihua.sign.DataBaseUtil;
import com.service.weihua.sign.FormatUtil;
import com.service.weihua.sign.ConnectionManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.HashMap;
import com.service.weihua.sign.ConstanceList;
import org.apache.log4j.*;

public class MSSQLUserDAO implements UserDAO{

private Logger logger = Logger.getLogger(getClass());

public MSSQLUserDAO() {
}
public void updateCustomer(UserDTO dto) throws UserDAOSysException
{
long userID = dto.getUserID();
String sql_str = "";
Connection conn = null;
PreparedStatement pstmt = null;
DataBaseUtil dataBaseUtil = new DataBaseUtil();


public void deleteCustomer(long userID) throws UserDAOSysException
{
String sql_str = "";
Connection conn = null;
PreparedStatement pstmt = null;

sql_str = "delete from T_customers where userID=?";

try
{
conn = ConnectionManager.getConnection();
pstmt = conn.prepareStatement(sql_str);
pstmt.setLong(1,userID);
pstmt.executeUpdate();
}
catch(SQLException sqle)
{
throw new UserDAOSysException(sqle.getMessage());
}
finally
{
ConnectionManager.closeAll(conn,pstmt,null);
}
}



public UserDTO getCustomer(String loginName) throws UserDAOSysException
{
UserDTO dto = new UserDTO();
String sql_str = "";

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet result = null;

sql_str = "SELECT customerID,companyName,companyAddress,contactName,email,mobile,"
+"phone,loginName,passWold,description,registeredTime "
+"FROM T_customers where loginName=?";

try
{
conn = ConnectionManager.getConnection();
pstmt = conn.prepareStatement(sql_str);
pstmt.setString(1,loginName);
result = pstmt.executeQuery();
if(result.isBeforeFirst())
{
while(result.next())
{
dto.setCustomerID(result.getLong(1));
dto.setCompanyName(result.getString(2));
dto.setCompanyAddress(result.getString(3));
dto.setContactName(result.getString(4));
dto.setEmail(result.getString(5));
dto.setMobile(result.getString(6));
dto.setPhone(result.getString(7));
dto.setLoginName(result.getString(8));
dto.setPassWold(result.getString(9));
dto.setDescription(result.getString(10));
dto.setregisteredTime(result.getTimestamp(11));
}
}
}
catch(SQLException sqle)
{
throw new UserDAOSysException(sqle.getMessage());
}
finally
{
ConnectionManager.closeAll(conn,pstmt,null);
}
return dto;
}

public UserDTO getCustomer(long userID) throws UserDAOSysException
{
UserDTO dto = new UserDTO();
String sql_str = "";

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet result = null;

sql_str = "SELECT T_customerscustomerID,companyName,companyAddress,contactName,email,mobile,"
+"phone,loginName,passWold,description,registeredTime "
+"FROM T_customers where customerID=?";

try
{
conn = ConnectionManager.getConnection();
pstmt = conn.prepareStatement(sql_str);
pstmt.setLong(1,customerID);
result = pstmt.executeQuery();
if(result.isBeforeFirst())
{
while(result.next())
{
dto.setCustomerID(result.getLong(1));
dto.setCompanyName(result.getString(2));
dto.setCompanyAddress(result.getString(3));
dto.setContactName(result.getString(4));
dto.setEmail(result.getString(5));
dto.setMobile(result.getString(6));
dto.setPhone(result.getString(7));
dto.setLoginName(result.getString(8));
dto.setPassWold(result.getString(9));
dto.setDescription(result.getString(10));
dto.setRegisteredTime(result.getTimestamp(11));
}
}
}
catch(SQLException sqle)
{
throw new UserDAOSysException(sqle.getMessage());
}
finally
{
ConnectionManager.closeAll(conn,pstmt,null);
}
return dto;
}
}