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

#include "stdafx.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()
{
	
}

CUsers::~CUsers()
{

}

int CUsers::GetUID()
{
	return UID;
}

void CUsers::SetUID(int iUID)
{
	UID=iUID;
}

CString CUsers::GetLoginName()
{
	return LoginName;
}

CString CUsers::GetName()
{
	return Name;
}

int CUsers::GetChannelID()
{
	return ChannelID;
}

int CUsers::GetColumnID()
{
	return ColumnID;
}


void CUsers::SetLoginName(CString cLoginName)
{
	LoginName = cLoginName;
}
void CUsers::SetName(CString cName)
{
	Name = cName;
}

CString CUsers::GetPwd()
{
	return Pwd;
}

CString CUsers::GetSex()
{
	return Sex;
}

void CUsers::SetPwd(CString cPwd)
{
	Pwd = cPwd;
}

int CUsers::GetRoleID()
{
	return RoleID;
}

void CUsers::SetRoleID(int iRoleID)
{
	RoleID = iRoleID;
}
void CUsers::SetSex(CString cSex)
{
	Sex = cSex;
}

void CUsers::SetChannelID(int iChannelID)
{
	ChannelID = iChannelID;
}

void CUsers::SetColumnID(int iColumnID)
{
	ColumnID = iColumnID;
}


//數據庫操作
int CUsers::HaveLoginName(CString cLoginName)
{	
	
	//設置SELECT語句

	vSQL = "SELECT * FROM Users WHERE LoginName='" + cLoginName + "'";
	
	//執行SELECT語句
	m_pRecordset = GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
		return -1;
	else
		return 1;
}
	
void CUsers::sql_insert(CString cLoginName,CString cPwd,CString cName,CString cSex,int iRoleID)
{	
	//設置INSERT語句
	CString strID;
	strID.Format("%d", iRoleID);
	vSQL = "INSERT INTO Users VALUES('" + cLoginName + "','" +cPwd + "','" + cName + "','"  + cSex + "'," + strID + ")";
	//執行INSERT語句
	ExecuteSQL(vSQL);	
}

void CUsers::sql_update(int iUID,CString cLoginName,CString cPwd,CString cName,CString cSex,int iRoleID)
{
	//設置UPDATE語句
	CString strUID,sSql;
	strUID.Format("%d", iUID);
	CString strID;
	strID.Format("%d", iRoleID);

	vSQL = "UPDATE Users SET LoginName='" + cLoginName + "',Password='" + cPwd + "',Name='" + cName + "',Sex='" + cSex + "',RoleID=" + strID + " WHERE UID=" + strUID;
   
	//執行UPDATE語句
	ExecuteSQL(vSQL);	

}

void CUsers::sql_delete(long iUID,long& iFlg,CString& cMessage)
{
	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection=m_pConnection;
		m_pCommand->CommandType=adCmdStoredProc;
		m_pCommand->CommandText=_bstr_t("del_Users");
		
		_variant_t vvar1,vvar2,vvar3;
	
		vvar1=_variant_t(iUID);
		
		vvar2=_variant_t(iFlg);
		vvar3=_variant_t(_bstr_t(cMessage));

		_ParameterPtr mp_var1,mp_var2,mp_var3;
		mp_var1.CreateInstance(__uuidof(Parameter));
		mp_var2.CreateInstance(__uuidof(Parameter));
		mp_var3.CreateInstance(__uuidof(Parameter));
		
	
		mp_var1=m_pCommand->CreateParameter
		(
		_bstr_t("var1"),
		adVarChar,
		adParamInput,
		20,
		vvar1
		);
		m_pCommand->Parameters->Append(mp_var1); 
	
		mp_var2=m_pCommand->CreateParameter
		(
		_bstr_t("var2"),
		adBigInt,
		adParamOutput,
		5,
		vvar2
		);
		m_pCommand->Parameters->Append(mp_var2); 

		mp_var3=m_pCommand->CreateParameter
		(
		_bstr_t("var3"),
		adVarChar,
		adParamOutput,
		200,
		vvar3
		);
		m_pCommand->Parameters->Append(mp_var3); 


		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
		m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);

		iFlg=mp_var2->Value;
		cMessage=mp_var3->Value.bstrVal;
		}
		catch(_com_error &error)
		{
			AfxMessageBox(error.ErrorMessage(),MB_OK,0);
			AfxMessageBox(error.Description(),MB_OK,0);
			AfxMessageBox("ADO錯誤!",MB_OK,0);
		}


}
	
void CUsers::sql_query(CString cWhere)
{
	
	vSQL = "SELECT * FROM Users" + cWhere;
	//執行SELETE語句
	m_pRecordset = GetRecordSet(vSQL);

}

int CUsers::getUIDByLoginName(CString loginName)
{
	//設置SELECT語句
	
	vSQL = "SELECT UID FROM Users WHERE LoginName='"+loginName+"'";
	//執行SELETE語句

	m_pRecordset = GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
	{
		CUsers();
	}
	else
	{
		UID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID"));		
	}
	return UID;
}
int CUsers::getIDByName(CString cName)
{
	//設置SELECT語句
	int UID=0;
	vSQL = "SELECT UID FROM Users WHERE Name='"+cName+"'";
	//執行SELETE語句
	
	m_pRecordset = GetRecordSet(vSQL);
	if (m_pRecordset->adoEOF)
	{
		CUsers();
	}
	else
	{
		UID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("UID"));		
	}
	return UID;
}


//根據員工編號讀取所有字段值
void CUsers::GetData(int iUID)
{
	CString strID;
	strID.Format("%d", iUID);

	//設置SELECT語句
	vSQL = "SELECT * FROM Users,UserChannel WHERE Users.UID=UserChannel.UserID AND Users.UID=" + strID;
	//執行SELETE語句
	m_pRecordset = GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CUsers();
	else
	{
		LoginName = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("LoginName");
		Pwd = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Password");
		Name = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Name");
		Sex = (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Sex");
		RoleID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("RoleID"));
		ChannelID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ChannelID"));
		ColumnID = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ColumnID"));
	}

}

void CUsers::insert(CString cLoginName,CString cPwd,CString cName,CString cSex,long iRoleID,long iChannelID,long iColumnID,long& iFlg,CString& cMessage)
{	

	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection=m_pConnection;
		m_pCommand->CommandType=adCmdStoredProc;
		m_pCommand->CommandText=_bstr_t("ins_Users");
		
		_variant_t vvar1,vvar2,vvar3,vvar4,vvar5,vvar6,vvar7,vvar8,vvar9;
	
		vvar1=_variant_t(_bstr_t(cLoginName));
		vvar2=_variant_t(_bstr_t(cPwd));
		vvar3=_variant_t(_bstr_t(cName));
		vvar4=_variant_t(_bstr_t(cSex));
		vvar5=_variant_t(iRoleID);
		vvar6=_variant_t(iChannelID);
		vvar7=_variant_t(iColumnID);
		vvar8=_variant_t(iFlg);
		vvar9=_variant_t(_bstr_t(cMessage));

		_ParameterPtr mp_var1,mp_var2,mp_var3,mp_var4,mp_var5,mp_var6,mp_var7,mp_var8,mp_var9;
		mp_var1.CreateInstance(__uuidof(Parameter));
		mp_var2.CreateInstance(__uuidof(Parameter));
		mp_var3.CreateInstance(__uuidof(Parameter));
		mp_var4.CreateInstance(__uuidof(Parameter));
		mp_var5.CreateInstance(__uuidof(Parameter));
		mp_var6.CreateInstance(__uuidof(Parameter));
		mp_var7.CreateInstance(__uuidof(Parameter));
		mp_var8.CreateInstance(__uuidof(Parameter));
		mp_var9.CreateInstance(__uuidof(Parameter));
	
		mp_var1=m_pCommand->CreateParameter
		(
		_bstr_t("var1"),
		adVarChar,
		adParamInput,
		20,
		vvar1
		);
		m_pCommand->Parameters->Append(mp_var1); 

		mp_var2=m_pCommand->CreateParameter
		(
		_bstr_t("var2"),
		adVarChar,
		adParamInput,
		20,
		vvar2
		);
		m_pCommand->Parameters->Append(mp_var2); 

		mp_var3=m_pCommand->CreateParameter
		(
		_bstr_t("var3"),
		adVarChar,
		adParamInput,
		20,
		vvar3
		);
		m_pCommand->Parameters->Append(mp_var3); 

		mp_var4=m_pCommand->CreateParameter
		(
		_bstr_t("var4"),
		adVarChar,
		adParamInput,
		1,
		vvar4
		);
		m_pCommand->Parameters->Append(mp_var4); 
		
		mp_var5=m_pCommand->CreateParameter
		(
		_bstr_t("var5"),
		adBigInt,
		adParamInput,
		5,
		vvar5
		);
		m_pCommand->Parameters->Append(mp_var5); 
	
		mp_var6=m_pCommand->CreateParameter
		(
		_bstr_t("var6"),
		adBigInt,
		adParamInput,
		5,
		vvar6
		);
		m_pCommand->Parameters->Append(mp_var6); 

		mp_var7=m_pCommand->CreateParameter
		(
		_bstr_t("var7"),
		adBigInt,
		adParamInput,
		5,
		vvar7
		);
		m_pCommand->Parameters->Append(mp_var7); 
	
		mp_var8=m_pCommand->CreateParameter
		(
		_bstr_t("var8"),
		adBigInt,
		adParamOutput,
		5,
		vvar8
		);
		m_pCommand->Parameters->Append(mp_var8); 

		mp_var9=m_pCommand->CreateParameter
		(
		_bstr_t("var9"),
		adVarChar,
		adParamOutput,
		200,
		vvar9
		);