// Users.cpp: implementation of the CUsers class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "HrSys.h"
#include "Users.h"
#include "ADOConn.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CUsers::CUsers()
{
	UserName = "";
	EmpId = 0;
	UserPwd = "";
	UserType = "";
}

CUsers::~CUsers()
{

}
//數據庫操作
void CUsers::sql_insert()
{	
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置INSERT語句
	CString strEmpId;
	strEmpId.Format("%d", EmpId);

	_bstr_t vSQL;
	vSQL = "INSERT INTO Users VALUES('" + UserName + "'," 
		+ strEmpId + ",'111111','" + UserType + "')";	
	//執行INSERT語句
	m_AdoConn.ExecuteSQL(vSQL);	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//更新用戶信息，但不能更新用戶名和用戶密碼
void CUsers::sql_update(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置UPDATE語句
	CString strEmpId;
	strEmpId.Format("%d", EmpId);
	_bstr_t vSQL;
	vSQL = "UPDATE Users SET EmpId=" + strEmpId 
		+ ",UserType='" + UserType + "' WHERE UserName='" + cUserName + "'";
	//執行UPDATE語句
	m_AdoConn.ExecuteSQL(vSQL);	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//刪除用戶信息
void CUsers::sql_delete(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置DELETE語句
	_bstr_t vSQL;
	vSQL = "DELETE FROM Users WHERE UserName='" + cUserName	+ "'";
	//執行DELETE語句
	m_AdoConn.ExecuteSQL(vSQL);	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//更新指定用戶密碼
void CUsers::sql_updatePassword(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置UPDATE語句
	_bstr_t vSQL;
	vSQL = "UPDATE Users SET UserPwd='" + UserPwd 
		+ "' WHERE UserName='" + cUserName + "'";
	//執行UPDATE語句
	m_AdoConn.ExecuteSQL(vSQL);	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//根據用戶名讀取所有字段值
void CUsers::GetData(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM Users WHERE UserName='" + cUserName + "'";
	//執行SELETE語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CUsers();
	else
	{
		UserName = cUserName;
		EmpId = atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("EmpId"));
		UserPwd = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UserPwd");
		UserType = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UserType");
	}
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}

//判斷指定用戶名是否存在
bool CUsers::HaveRecord(CString cUserName)
{	
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM Users WHERE UserName='" + cUserName + "'";
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return false;
	else
		return true;

	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//判斷指定員工是否存在用戶名
bool CUsers::EmpIn_DB(CString cEmpId)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM Users WHERE EmpId=" + cEmpId;
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return false;
	else
		return true;

	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//將用戶密碼初始化為111111
void CUsers::ResetPassword(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置UPDATE語句
	_bstr_t vSQL;
	vSQL = "UPDATE Users SET UserPwd='111111'" ;
	vSQL = vSQL	+ " WHERE UserName='" + cUserName + "'";
	//執行UPDATE語句
	m_AdoConn.ExecuteSQL(vSQL);	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//根據用戶名取得此員工的姓名
CString CUsers::GetEmpName(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT e.EmpName FROM Users u,Employees e";
	vSQL = vSQL	+ " WHERE u.UserName='" + cUserName + "'";
	vSQL = vSQL	+ " AND u.EmpId=e.EmpId";
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return "";
	else
		return (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("EmpName");

	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
//根據用戶名取得此員工的部門名稱
CString CUsers::GetDepName(CString cUserName)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT d.DepName FROM Users u,Employees e,Departments d";
	vSQL = vSQL	+ " WHERE u.UserName='" + cUserName + "'";
	vSQL = vSQL	+ " AND u.EmpId=e.EmpId AND e.DepId=d.DepId";
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return "";
	else
		return (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("DepName");

	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}