// ChargeItem.cpp: implementation of the CChargeItem class.
//
// 1 ItemId int identity(1,1) 項目編號
// 2 SpeId int 專業編號 Allow Null = False
// 3 iYear int 年級 Allow Null = False
// 4 Tuition float 學費 Allow Null = Yes
// 5 Incidental float 雜費 Allow Null = Yes
// 6 MacTimeFee float 機時費 Allow Null = Yes
// 7 Insurance float 保險費 Allow Null = Yes
// 8 DormFee float 住宿費 Allow Null = Yes
// 9 BicycleFee float 存車費 Allow Null = Yes
// 10 BookFee float 書費 Allow Null = Yes
// 11 FileFee float 資料費 Allow Null = Yes
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "ChargeManage.h"
#include "ChargeItem.h"
#include "ADOConn.h"

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

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

CChargeItem::CChargeItem()
{
	ItemId = 0;
	SpeId = 0;
	iYear = 2006;
	Tuition = 0.0;
	Incidental = 0.0;

	MacTimeFee = 0.0;
	Insurance = 0.0;
	DormFee = 0.0;
	BicycleFee = 0.0;

	BookFee = 0.0;
	FileFee = 0.0;
}

CChargeItem::~CChargeItem()
{

}

// 判斷此專業\此年度是否有收費標準
bool CChargeItem::HaveSpe(CString paraId,CString cYear)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ChargeItem WHERE SpeId=" + paraId + " And iYear=" + cYear;

	//執行SELETE語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回值
	if (m_pRecordset->adoEOF)
		return false;
	else
		return true;

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

// 根據收費項目編號得到其他信息
void CChargeItem::GetInfo(CString paraId)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT * FROM ChargeItem WHERE ItemId = " + paraId;

	//執行SELETE語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);

	//返回各列的值
	if (m_pRecordset->adoEOF)
		CChargeItem();
	else
	{
		ItemId = atol(paraId);
		SpeId = atol((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ItemId"));
		iYear = atoi((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("iYear"));
		Tuition = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Tuition"));
		Incidental = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Incidental"));

		MacTimeFee = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("MacTimeFee"));
		Insurance = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("Insurance"));
		DormFee = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("DormFee"));
		BicycleFee = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("BicycleFee"));

		BookFee = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("BookFee"));
		FileFee = atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("FileFee"));
	}

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

// 插入操作
void CChargeItem::SqlInsert()
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	//設置INSERT語句
	CString strSpeId;
	strSpeId.Format("%ld", SpeId);
	CString cYear;
	cYear.Format("%d", iYear);
	CString fTuition;
	fTuition.Format("%lf", Tuition);
	CString fIncidental;
	fIncidental.Format("%lf", Incidental);
	CString fMacTimeFee;
	fMacTimeFee.Format("%lf", MacTimeFee);
	CString fInsurance;
	fInsurance.Format("%lf", Insurance);
	CString fDormFee;
	fDormFee.Format("%lf", DormFee);
	CString fBicycleFee;
	fBicycleFee.Format("%lf", BicycleFee);
	CString fBookFee;
	fBookFee.Format("%lf", BookFee);
	CString fFileFee;
	fFileFee.Format("%lf", FileFee);
	_bstr_t vSQL;
	vSQL = "INSERT INTO ChargeItem (SpeId,iYear,Tuition, Incidental,"
		"  MacTimeFee,Insurance, DormFee, BicycleFee, BookFee, FileFee) VALUES ("
		+ strSpeId + ", " + cYear + "," + fTuition + ", "
		+ fIncidental + ", " + fMacTimeFee + ", " + fInsurance + ", " + fDormFee + ", "
		+ fBicycleFee + ", " + fBookFee + ", " + fFileFee + ")";

	//執行INSERT語句
	m_AdoConn.ExecuteSQL(vSQL);	

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

// 修改操作
void CChargeItem::SqlUpdate(CString paraId)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	//設置UPDATE語句
	CString strSpeId;
	strSpeId.Format("%ld", SpeId);
	CString cYear;
	cYear.Format("%d", iYear);
	CString fTuition;
	fTuition.Format("%lf", Tuition);
	CString fIncidental;
	fIncidental.Format("%lf", Incidental);
	CString fMacTimeFee;
	fMacTimeFee.Format("%lf", MacTimeFee);
	CString fInsurance;
	fInsurance.Format("%lf", Insurance);
	CString fDormFee;
	fDormFee.Format("%lf", DormFee);
	CString fBicycleFee;
	fBicycleFee.Format("%lf", BicycleFee);
	CString fBookFee;
	fBookFee.Format("%lf", BookFee);
	CString fFileFee;
	fFileFee.Format("%lf", FileFee);
	
	_bstr_t vSQL;
	vSQL = "UPDATE ChargeItem SET Tuition = " + fTuition + ", Incidental = " + fIncidental
		+ ", MacTimeFee = " + fMacTimeFee + ", Insurance = " + fInsurance
		+ ", DormFee=" + fDormFee + ",BicycleFee = " + fBicycleFee
		+ ", BookFee=" + fBookFee + ",FileFee=" + fFileFee + ",iYear=" + cYear
		+ ",SpeId=" + strSpeId + " WHERE ItemId = " + paraId;

	//執行UPDATE語句
	m_AdoConn.ExecuteSQL(vSQL);

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

// 刪除指定收費信息
void CChargeItem::SqlDelete(CString paraId)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	//設置DELETE語句
	_bstr_t vSQL;
	vSQL = "DELETE FROM ChargeItem WHERE ItemId = " + paraId;

	//執行DELETE語句
	m_AdoConn.ExecuteSQL(vSQL);	

	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
// 返回指定編號的收費總額
float CChargeItem::GetSum(CString paraId,CString cYear)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT (Tuition+Incidental+MacTimeFee+Insurance+DormFee+"
		"BicycleFee+BookFee+FileFee) AS cSum FROM ChargeItem "
		" WHERE SpeId = " + paraId + " And iYear = " + cYear;
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	
	if (m_pRecordset->adoEOF)
		return 0;
	else
		return atof((LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("cSum"));
	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}
// 返回項目編號
CString CChargeItem::GetItemId(CString paraId,CString cYear)
{
	//連接數據庫
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	
	//設置SELECT語句
	_bstr_t vSQL;
	vSQL = "SELECT ItemId FROM ChargeItem "
		" WHERE SpeId = " + paraId + " And iYear = " + cYear;
	
	//執行SELECT語句
	_RecordsetPtr m_pRecordset;
	m_pRecordset = m_AdoConn.GetRecordSet(vSQL);
	
	if (m_pRecordset->adoEOF)
		return "";
	else
		return (LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("ItemId");
	
	//斷開與數據庫的連接
	m_AdoConn.ExitConnect();
}