// CreateSpringSeries.cpp : implementation file
//

#include "stdafx.h"
#include "dbconvert.h"
#include "CreateSpringSeries.h"

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

/////////////////////////////////////////////////////////////////////////////
// CreateSpringSeries dialog


CreateSpringSeries::CreateSpringSeries(CWnd* pParent /*=NULL*/)
	: CDialog(CreateSpringSeries::IDD, pParent)
{
	//{{AFX_DATA_INIT(CreateSpringSeries)
	m_pathName = _T("");
	//}}AFX_DATA_INIT
}


void CreateSpringSeries::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CreateSpringSeries)
	DDX_Text(pDX, IDC_EDIT1, m_pathName);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CreateSpringSeries, CDialog)
	//{{AFX_MSG_MAP(CreateSpringSeries)
	ON_BN_CLICKED(IDC_BUTTON2, OnCreateSeries)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CreateSpringSeries message handlers

bool CreateSpringSeries::ConnectionDatadb(_ConnectionPtr &pCon, CString strMdbName)
{
	
	UpdateData(true);
	pCon.CreateInstance(__uuidof(Connection));
	//CString conPRJDBSql = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source='"+m_pathName+"';PerSist Security Info=False";
	CString conPRJDBSql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+m_pathName+"';PerSist Security Info=False";
	try
	{
	   pCon->Open(_bstr_t(conPRJDBSql),"","",-1);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());

		return FALSE;
	}
	return TRUE;
	UpdateData(false);
}


//生成后缀名为Series的弹簧表
void CreateSpringSeries::OnCreateSeries() 
{
	deleteAllSeries();
	CString strTableName=_T("");
	_variant_t var;
	CString strSQL=_T("");
	try
	{
		m_SpringINFOset->MoveFirst();
		for(;!m_SpringINFOset->adoEOF;m_SpringINFOset->MoveNext())
		{
			var=m_SpringINFOset->GetCollect(_T("DataTableSeries"));
			strTableName=var.bstrVal;
			strSQL=_T("select * into "+strTableName+" from PshSPRING_LISEGA_Series  where SPRNo='xxxxxxx'");
			try
			{
				m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
			}
			catch(_com_error e)
			{
			}
			
		}
		WriteData();
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	AfxMessageBox("成功");
}



void CreateSpringSeries::deleteAllSeries()
{
	CString strSQL=_T("select * from SpringINFO where Spring_Crude is NOT NULL");
	_variant_t var;
	CString strTableName=_T("");
	try
	{
		m_pCon=NULL;
		ConnectionDatadb(m_pCon,_T(""));
		m_SpringINFOset.CreateInstance(__uuidof(Recordset));
		m_SpringINFOset->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		int iCount=m_SpringINFOset->GetRecordCount();
		m_SpringINFOset->MoveFirst();
		for(int i=0;i<iCount;i++,m_SpringINFOset->MoveNext())
		{
			try
			{
				var=m_SpringINFOset->GetCollect(_T("DataTableSeries"));	
				strTableName=var.bstrVal;
				strSQL="drop table "+strTableName+" ";
				m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
			}
			catch(_com_error e)
			{
			}
		}
	}
	catch (_com_error e)
	{
		AfxMessageBox(e.Description());
	}
}

void CreateSpringSeries::WriteData()
{
	m_SpringINFOset->MoveFirst();
	CString strNo=_T("");
	CString strTableName=_T("");
	CString strCrudeName=_T("");
	CString strName=_T("");
	_RecordsetPtr pRs=NULL;
	_RecordsetPtr pRs1=NULL;
	_variant_t var;
	CString strSQL=_T("");
	pRs.CreateInstance(__uuidof(Recordset));
	pRs1.CreateInstance(__uuidof(Recordset));
	try
	{
		for(;!m_SpringINFOset->adoEOF;m_SpringINFOset->MoveNext())
		{
			var=m_SpringINFOset->GetCollect(_T("DataTableName"));
			strTableName=var.bstrVal;
			try
			{
				strSQL=_T("select * from "+strTableName+" order by seq");
				pRs->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
				var=m_SpringINFOset->GetCollect(_T("DataTableSeries"));
				strTableName=var.bstrVal;
				strSQL=_T("select * from "+strTableName+"");
				pRs1->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
				long iNo=1;
				for(;!pRs->adoEOF;pRs->MoveNext(),iNo++)
				{
					for(long i=1;i<5;i++)
					{
						pRs1->AddNew();
						var=pRs->GetCollect(_T("seq"));
						pRs1->PutCollect(_T("SERIES_Index"),_variant_t(iNo));
						pRs1->PutCollect(_T("SERIES_NUMBER"),_variant_t(i));
						pRs1->PutCollect(_T("SPRNo"),pRs->GetCollect(_T("seq")));
						CString str;
						str.Format("%d%02d",i,(pRs->GetCollect(_T("seq"))).intVal);
						pRs1->PutCollect(_T("SPRSpecNo"),_variant_t(str));
						pRs1->Update();
					}
				}
			}
			catch(_com_error e)
			{
				AfxMessageBox(e.Description());
			}
			if(pRs->State==adStateOpen)
			{
				pRs->Close();
			}
			if(pRs1->State==adStateOpen)
			{
				pRs1->Close();
			}
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
}

void CreateSpringSeries::WriteBH()
{
	CString strCrude=_T("");
	CString strSeries=_T("");
	CString strID=_T("");
	_variant_t var;
	CString strSQL=_T("");

	try
	{
		m_SpringINFOset->MoveFirst();
		for(;!m_SpringINFOset->adoEOF;m_SpringINFOset->MoveNext())
		{
			var=m_SpringINFOset->GetCollect(_T("Spring_Crude"));
			strCrude=var.bstrVal;
			strCrude.TrimLeft();
			strCrude.TrimRight();
			if(strCrude!=""&&var.vt!=VT_NULL)
			{
				var=m_SpringINFOset->GetCollect(_T("DataTableSeries"));
				strSeries=var.bstrVal;
				strSeries.TrimLeft();
				strSeries.TrimRight();
				if(strSeries!=""&&var.vt!=VT_NULL)
				{
					var=m_SpringINFOset->GetCollect(_T("TypeCustomID"));
					strID=var.bstrVal;
					strID.TrimLeft();
					strID.TrimRight();
					if(strID!=""&&var.vt!=VT_NULL)
					{
						strSQL.Format("Update %s as a,%s as b set b.bh=a.bh where a.CustomId ='%s' and a.SPRSpecNo=b.SPRSpecNo",strCrude,strSeries,strID);
						m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
					}
				}
			}
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
}

void CreateSpringSeries::OnOK() 
{
	WriteBH();	
	CDialog::OnOK();
}