datacore.cpp

an Atl_com use to read or write an mdb file which use ado.Japanese software develop

	sqlstr=sqlstr+(_bstr_t)"' where id="+(_bstr_t)id; 
	try
	{
		cmd->ActiveConnection=ConPtr;
		cmd->CommandText=sqlstr;
		cmd->Execute(&vNull,&vNull,adCmdText);
		*succeed=VARIANT_TRUE;
	}
	catch(_com_error e)
	{
		*succeed=VARIANT_FALSE;
	}
	return S_OK;
}



STDMETHODIMP CDataCore::Delete_users(BSTR id, VARIANT_BOOL *succeed)
{
	AFX_MANAGE_STATE(AfxGetStaticModuleState())
	CoInitialize(NULL);
	_CommandPtr cmd(__uuidof(Command));
	_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
	_bstr_t sqlstr;
	*succeed=VARIANT_FALSE;
	sqlstr="delete from users where id="+(_bstr_t)id;
	try
	{
		cmd->ActiveConnection=ConPtr;
		cmd->CommandText=sqlstr;
		cmd->Execute(&vNull,&vNull,adCmdText);
		*succeed=VARIANT_TRUE;
	}
	catch(_com_error e)
	{
		*succeed=VARIANT_FALSE;
	}
	return S_OK;
}

STDMETHODIMP CDataCore::Updae_OutPutTwo(BSTR InvoiceName, BSTR Count, BSTR unit, BSTR Unitprice, BSTR TotalPrice, BSTR APS, BSTR AllowCode, BSTR ID, VARIANT_BOOL *succeed)
{
	AFX_MANAGE_STATE(AfxGetStaticModuleState())
	CoInitialize(NULL);
	_CommandPtr cmd(__uuidof(Command));
	_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
	_bstr_t sqlstr;
	sqlstr="update 桝弌 set 僀儞儃僀僗昞婰柤='"+(_bstr_t)InvoiceName;
	sqlstr=sqlstr+(_bstr_t)"',悢検="+(_bstr_t)Count+(_bstr_t)",扨埵='"+(_bstr_t)unit+(_bstr_t)"',扨壙=";
	sqlstr=sqlstr+(_bstr_t)Unitprice+(_bstr_t)",壙奿="+(_bstr_t)TotalPrice+(_bstr_t)",APS="+(_bstr_t)APS;
	sqlstr=sqlstr+(_bstr_t)",嫋壜Code="+(_bstr_t)AllowCode+(_bstr_t)" where 桝弌ID="+(_bstr_t)ID;
	*succeed=VARIANT_FALSE;
	try
	{
		cmd->ActiveConnection=ConPtr;
		cmd->CommandText=sqlstr;
		cmd->Execute(&vNull,&vNull,adCmdText);
		*succeed=VARIANT_TRUE;
	}
	catch(_com_error e)
	{
		*succeed=VARIANT_FALSE;
	}
	return S_OK;
}

//Process three table 
STDMETHODIMP CDataCore::ProcessOutPutDetail(BSTR InVoiceName, BSTR Count, BSTR unit, BSTR UnitPrice, BSTR TotalPrice, 
											BSTR APS, BSTR AllowCode, BSTR AllowDate, BSTR id, VARIANT_BOOL NewGoods, 
											VARIANT_BOOL NewDoc, VARIANT_BOOL DocVaild, BSTR Information, BSTR author, 
											BSTR Date, BSTR RelationShip, BSTR DocID, VARIANT_BOOL GoodValid, BSTR Info, 
											BSTR attribute, BSTR JodgeDate,BSTR JudgeCode, BSTR GoodID,VARIANT_BOOL *Succeed)
{
	AFX_MANAGE_STATE(AfxGetStaticModuleState())
	CoInitialize(NULL);
	_CommandPtr cmd(__uuidof(Command));
	_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
	_bstr_t sqlstr;
	_bstr_t sqltxt;
	_bstr_t strsql;
	_bstr_t txtsql;
	_bstr_t sqltext;
	_bstr_t goodid;
	goodid=GoodID;
	//AfxMessageBox((_bstr_t)AllowDate);
	if((_bstr_t)AllowDate==(_bstr_t)"null")
	{
		//AfxMessageBox((_bstr_t)AllowDate);
		sqlstr="update 桝弌 set 僀儞儃僀僗昞婰柤='"+(_bstr_t)InVoiceName;
		sqlstr=sqlstr+(_bstr_t)"',悢検="+(_bstr_t)Count+(_bstr_t)",扨埵='"+(_bstr_t)unit+(_bstr_t)"',扨壙=";
		sqlstr=sqlstr+(_bstr_t)UnitPrice+(_bstr_t)",壙奿="+(_bstr_t)TotalPrice+(_bstr_t)",APS="+(_bstr_t)APS;
		sqlstr=sqlstr+(_bstr_t)",嫋壜Code="+(_bstr_t)AllowCode+(_bstr_t)",暔昳忣曬ID="+(_bstr_t)goodid+(_bstr_t)" where 桝弌ID="+(_bstr_t)id;
	}
	else
	{
		sqlstr="update 桝弌 set 僀儞儃僀僗昞婰柤='"+(_bstr_t)InVoiceName;
		sqlstr=sqlstr+(_bstr_t)"',悢検="+(_bstr_t)Count+(_bstr_t)",扨埵='"+(_bstr_t)unit+(_bstr_t)"',扨壙=";
		sqlstr=sqlstr+(_bstr_t)UnitPrice+(_bstr_t)",壙奿="+(_bstr_t)TotalPrice+(_bstr_t)",APS="+(_bstr_t)APS;
		sqlstr=sqlstr+(_bstr_t)",嫋壜Code="+(_bstr_t)AllowCode+(_bstr_t)",嫋壜擔=#"+(_bstr_t)AllowDate+(_bstr_t)"#"+(_bstr_t)",暔昳忣曬ID="+(_bstr_t)goodid+(_bstr_t)" where 桝弌ID="+(_bstr_t)id;
	}
	_bstr_t vaild;
	_bstr_t Vaild;
	if(NewGoods==VARIANT_TRUE&&NewDoc==VARIANT_TRUE)
	{
		
		if(GoodValid==VARIANT_TRUE)
		{
			vaild=(_bstr_t)"1";
		}
		else
		{
			vaild=(_bstr_t)"0";
		}
		if((_bstr_t)JodgeDate!=(_bstr_t)"null")
		{
			sqltxt="insert into 暔昳忣曬(暔昳忣曬桳岠,暔昳忣曬,儊乕僇乕,敾掕Code,敾掕擔) values(";
			sqltxt=sqltxt+(_bstr_t)vaild+(_bstr_t)",'"+(_bstr_t)Info+(_bstr_t)"','"+(_bstr_t)attribute+(_bstr_t)"',";
			sqltxt=sqltxt+(_bstr_t)JudgeCode+(_bstr_t)",#"+(_bstr_t)JodgeDate+(_bstr_t)"#"+(_bstr_t)")";
			//AfxMessageBox((_bstr_t)JodgeDate);
		}
		else
		{
			sqltxt="insert into 暔昳忣曬(暔昳忣曬桳岠,暔昳忣曬,儊乕僇乕,敾掕Code) values(";
				//sqltxt="insert into 物品情報(物品情報有効,物品情報,メーカー,判定Code) values(";
			sqltxt=sqltxt+(_bstr_t)vaild+(_bstr_t)",'"+(_bstr_t)Info+(_bstr_t)"','"+(_bstr_t)attribute+(_bstr_t)"',";
			sqltxt=sqltxt+(_bstr_t)JudgeCode+(_bstr_t)")";
		}
		if(DocVaild==VARIANT_TRUE)
		{
			Vaild=(_bstr_t)"1";
		}
		else
		{
			Vaild=(_bstr_t)"0";
		}
		if((_bstr_t)Date!=(_bstr_t)"null")
		{
			strsql="insert into 暥彂(暥彂桳岠,暥彂忣曬,敪峴幰,敪峴擔,娭學崁斣) values(";
			strsql=strsql+(_bstr_t)Vaild+(_bstr_t)",'"+(_bstr_t)Information+(_bstr_t)"','"+(_bstr_t)author;
			strsql=strsql+(_bstr_t)"',#"+(_bstr_t)Date+(_bstr_t)"#,'"+(_bstr_t)RelationShip+(_bstr_t)"')";
		}
		else
		{
			strsql="insert into 暥彂(暥彂桳岠,暥彂忣曬,敪峴幰,娭學崁斣) values(";
			strsql=strsql+(_bstr_t)Vaild+(_bstr_t)",'"+(_bstr_t)Information+(_bstr_t)"','"+(_bstr_t)author;
			strsql=strsql+(_bstr_t)"','"+(_bstr_t)RelationShip+(_bstr_t)"')";
		}
	}
	if(NewGoods==VARIANT_FALSE&&NewDoc==VARIANT_FALSE)
	{
		if(GoodValid==VARIANT_TRUE)
		{
			vaild=(_bstr_t)"1";
		}
		else
		{
			vaild=(_bstr_t)"0";
		}
		if((_bstr_t)JodgeDate!=(_bstr_t)"null")
		{
			if((_bstr_t)DocID!=(_bstr_t)"null")
			{
				sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
				sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
				sqltxt=sqltxt+(_bstr_t)",敾掕擔=#"+(_bstr_t)JodgeDate+(_bstr_t)"#,暥彂ID="+(_bstr_t)DocID+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
			}
			else
			{
				sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
				sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
				sqltxt=sqltxt+(_bstr_t)",敾掕擔=#"+(_bstr_t)JodgeDate+(_bstr_t)"#"+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
			}
		}
		else
		{
			if((_bstr_t)DocID!=(_bstr_t)"null")
			{
				sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
				sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
				sqltxt=sqltxt+(_bstr_t)",暥彂ID="+(_bstr_t)DocID+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
			}
			else
			{
				sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
				sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
				sqltxt=sqltxt+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
			}
		}
		if(DocVaild==VARIANT_TRUE)
		{
			Vaild=(_bstr_t)"1";
		}
		else
		{
			Vaild=(_bstr_t)"0";
		}
		if((_bstr_t)DocID!=(_bstr_t)"null")
		{
			if((_bstr_t)Date!=(_bstr_t)"null")
			{
				strsql="update 暥彂 set 暥彂桳岠="+(_bstr_t)Vaild+(_bstr_t)",暥彂忣曬='"+(_bstr_t)Information;
				strsql=strsql+(_bstr_t)"',敪峴幰='"+(_bstr_t)author+(_bstr_t)"',敪峴擔=#"+(_bstr_t)Date+(_bstr_t)"#,娭學崁斣='"+(_bstr_t)RelationShip+(_bstr_t)"' where 暥彂ID="+(_bstr_t)DocID;
			}
			else
			{
				strsql="update 暥彂 set 暥彂桳岠="+(_bstr_t)Vaild+(_bstr_t)",暥彂忣曬='"+(_bstr_t)Information;
				strsql=strsql+(_bstr_t)"',敪峴幰='"+(_bstr_t)author+(_bstr_t)"',娭學崁斣='"+(_bstr_t)RelationShip+(_bstr_t)"' where 暥彂ID="+(_bstr_t)DocID;
			}
		}
		else
		{
			strsql="";
		}
	}
	if(NewGoods==VARIANT_FALSE&&NewDoc==VARIANT_TRUE)
	{
		if(GoodValid==VARIANT_TRUE)
		{
			vaild=(_bstr_t)"1";
		}
		else
		{
			vaild=(_bstr_t)"0";
		}
		if((_bstr_t)JodgeDate!=(_bstr_t)"null")
		{
			sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
			sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
			sqltxt=sqltxt+(_bstr_t)",敾掕擔=#"+(_bstr_t)JodgeDate+(_bstr_t)"#,暥彂ID="+(_bstr_t)DocID+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
		}
		else
		{
			sqltxt="update 暔昳忣曬 set 暔昳忣曬桳岠="+(_bstr_t)vaild+(_bstr_t)",暔昳忣曬='"+(_bstr_t)Info;
			sqltxt=sqltxt+(_bstr_t)"',儊乕僇乕='"+(_bstr_t)attribute+(_bstr_t)"',敾掕Code="+(_bstr_t)JudgeCode;
			sqltxt=sqltxt+(_bstr_t)",暥彂ID="+(_bstr_t)DocID+(_bstr_t)" where 暔昳忣曬ID="+(_bstr_t)GoodID;
		}
		if(DocVaild==VARIANT_TRUE)
		{
			Vaild=(_bstr_t)"1";
		}
		else
		{
			Vaild=(_bstr_t)"0";
		}
		if((_bstr_t)Date!=(_bstr_t)"null")
		{
			strsql="insert into 暥彂(暥彂桳岠,暥彂忣曬,敪峴幰,敪峴擔,娭學崁斣) values(";
			strsql=strsql+(_bstr_t)Vaild+(_bstr_t)",'"+(_bstr_t)Information+(_bstr_t)"','"+(_bstr_t)author;
			strsql=strsql+(_bstr_t)"',#"+(_bstr_t)Date+(_bstr_t)"#,'"+(_bstr_t)RelationShip+(_bstr_t)"')";
		}
		else
		{
			strsql="insert into 暥彂(暥彂桳岠,暥彂忣曬,敪峴幰,娭學崁斣) values(";
			strsql=strsql+(_bstr_t)Vaild+(_bstr_t)",'"+(_bstr_t)Information+(_bstr_t)"','"+(_bstr_t)author;
			strsql=strsql+(_bstr_t)"','"+(_bstr_t)RelationShip+(_bstr_t)"')";
		}	
	}
	if(NewGoods==VARIANT_TRUE&&NewDoc==VARIANT_FALSE)
	{
		if(GoodValid==VARIANT_TRUE)
		{
			vaild=(_bstr_t)"1";
		}
		else
		{
			vaild=(_bstr_t)"0";
		}
		if((_bstr_t)JodgeDate!=(_bstr_t)"null")
		{
			sqltxt="insert into 暔昳忣曬(暔昳忣曬桳岠,暔昳忣曬,儊乕僇乕,敾掕Code,敾掕擔,暥彂ID) values(";
			sqltxt=sqltxt+(_bstr_t)vaild+(_bstr_t)",'"+(_bstr_t)Info+(_bstr_t)"','"+(_bstr_t)attribute+(_bstr_t)"',";
			sqltxt=sqltxt+(_bstr_t)JudgeCode+(_bstr_t)",#"+(_bstr_t)JodgeDate+(_bstr_t)"#,"+(_bstr_t)DocID+(_bstr_t)")";
		}
		else
		{
			sqltxt="insert into 暔昳忣曬(暔昳忣曬桳岠,暔昳忣曬,儊乕僇乕,敾掕Code,暥彂ID) values(";
			sqltxt=sqltxt+(_bstr_t)vaild+(_bstr_t)",'"+(_bstr_t)Info+(_bstr_t)"','"+(_bstr_t)attribute+(_bstr_t)"',";
			sqltxt=sqltxt+(_bstr_t)JudgeCode+(_bstr_t)","+(_bstr_t)DocID+(_bstr_t)")";
		}
		strsql="";
	}
	//AfxMessageBox(sqlstr);
//	AfxMessageBox(sqltxt);
//	AfxMessageBox(strsql);
	*Succeed=VARIANT_FALSE;
	try
	{
		cmd->ActiveConnection=ConPtr;
		ConPtr->BeginTrans();
		cmd->CommandText=sqlstr;
		cmd->Execute(&vNull,&vNull,adCmdText);
	//	AfxMessageBox(sqlstr);
		if(strsql!=(_bstr_t)"")
		{
			//AfxMessageBox("2");
			cmd->CommandText=strsql;
			cmd->Execute(&vNull,&vNull,adCmdText);
		//	AfxMessageBox(strsql);
		}
		else
		{
		//	AfxMessageBox("1");
		}
		cmd->CommandText=sqltxt;
		cmd->Execute(&vNull,&vNull,adCmdText);
	//	_bstr_t sqltxt;
		//sqltxt="select @@identity  as ID from  暥彂";
		sqltxt="select max(暥彂ID)  as ID from  暥彂";
		_CommandPtr command(__uuidof(Command));
		command->ActiveConnection=ConPtr;
		command->CommandText=sqltxt;
		_RecordsetPtr rs(__uuidof(Recordset));
		rs->PutRefSource(command);
		_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
		rs->Open(vNull,vNull,adOpenDynamic,adLockOptimistic,adCmdText);
		//AfxMessageBox("ss");
		rs->MoveFirst();
		_variant_t MaxId;
		MaxId=rs->GetCollect("ID");
		_bstr_t txtsql;
		//txtsql="select @@identity  as ID from  暔昳忣曬";
		txtsql="select max(暔昳忣曬ID)  as ID from  暔昳忣曬";
		_CommandPtr CMD(__uuidof(Command));
		CMD->ActiveConnection=ConPtr;
		CMD->CommandText=txtsql;
		_RecordsetPtr RS(__uuidof(Recordset));
		RS->PutRefSource(CMD);
		RS->Open(vNull,vNull,adOpenDynamic,adLockOptimistic,adCmdText);
		//AfxMessageBox("ss");
		RS->MoveFirst();
		_variant_t maxid; //max  暔昳忣曬ID
		maxid=RS->GetCollect("ID");
		//AfxMessageBox(sqltxt);
		if(NewDoc==VARIANT_TRUE)
		{
			if((_bstr_t)GoodID!=(_bstr_t)"null")
			{
				txtsql="Update 暔昳忣曬 set 暥彂ID="+(_bstr_t)MaxId+" where 暔昳忣曬ID="+(_bstr_t)GoodID;
			}
			else
			{
				txtsql="Update 暔昳忣曬 set 暥彂ID="+(_bstr_t)MaxId+" where 暔昳忣曬ID="+(_bstr_t)maxid;
				//AfxMessageBox(txtsql);
			}
			cmd->CommandText=txtsql;
			cmd->Execute(&vNull,&vNull,adCmdText);
		}
		if(NewGoods==VARIANT_TRUE)
		{
			sqltext="update 桝弌 set 暔昳忣曬ID="+(_bstr_t)maxid+" where 桝弌ID="+(_bstr_t)id;
			////AfxMessageBox(sqltext);
			cmd->CommandText=sqltext;
			cmd->Execute(&vNull,&vNull,adCmdText);
		}
		ConPtr->CommitTrans();
		*Succeed=VARIANT_TRUE;
	}
	catch(_com_error e)
	{
		ConPtr->RollbackTrans();
		*Succeed=VARIANT_FALSE;
	}
	return S_OK;
}

BSTR CDataCore::RtnMaxDocID()
{
	_bstr_t sqlstr="SELECT max(暥彂ID) as ID FROM 暥彂";
	CoInitialize(NULL);
	_CommandPtr cmd(__uuidof(Command));
	cmd->ActiveConnection=ConPtr;
	cmd->CommandText=sqlstr;
	_RecordsetPtr rs(__uuidof(Recordset));
	rs->PutRefSource(cmd);
	_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
	rs->Open(vNull,vNull,adOpenDynamic,adLockOptimistic,adCmdText);
	//AfxMessageBox("ss");
	rs->MoveFirst();
	_variant_t MaxId;
	MaxId=rs->GetCollect("ID");
	BSTR maxid;
	maxid=(_bstr_t)MaxId;
	CoUninitialize();
	return maxid;
}

BSTR CDataCore::RtnMaxGoodID()
{
	_bstr_t sqlstr="SELECT max(暔昳忣曬ID) as ID FROM 暔昳忣曬";
	CoInitialize(NULL);
	_CommandPtr cmd(__uuidof(Command));
	cmd->ActiveConnection=ConPtr;
	cmd->CommandText=sqlstr;
	_RecordsetPtr rs(__uuidof(Recordset));
	rs->PutRefSource(cmd);
	_variant_t vNull(DISP_E_PARAMNOTFOUND,VT_ERROR);
	rs->Open(vNull,vNull,adOpenDynamic,adLockOptimistic,adCmdText);
	//AfxMessageBox("ss");
	rs->MoveFirst();
	_variant_t MaxId;
	MaxId=rs->GetCollect("ID");
	BSTR maxid;
	maxid=(_bstr_t)MaxId;
	CoUninitialize();
	return maxid;
}

STDMETHODIMP CDataCore::InitionConect(BSTR Constr, BSTR pwd, BSTR username, VARIANT_BOOL *Succeed)
{
	AFX_MANAGE_STATE(AfxGetStaticModuleState())
	CoInitialize(NULL);
	ConPtr.CreateInstance(__uuidof(Connection));
	*Succeed=VARIANT_FALSE;
	_bstr_t Connection=(_bstr_t)"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+(_bstr_t)Constr+";Persist Security Info=False;Jet OLEDB:Database Password="+(_bstr_t)pwd;
//	AfxMessageBox(Connection);
	try
	{
		if(ConPtr->State)
		{
			ConPtr->Close();
		}
		//ConPtr->Open(Constr,"","",adModeUnknown);
		ConPtr->Open(Connection,"","",adModeUnknown);
		*Succeed=VARIANT_TRUE;
	}