querydlg.cpp

VC下通过DB-LIBRARYDE的API函数操作SQLSERVER数据库.

// QueryDlg.cpp : implementation file
//

#include "stdafx.h"
#include "PhysicM.h"
#include "QueryDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CQueryDlg dialog


CQueryDlg::CQueryDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CQueryDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQueryDlg)
	m_end_date = _T("");
	m_start_date = _T("");
	//}}AFX_DATA_INIT
}


void CQueryDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQueryDlg)
	DDX_Control(pDX, IDC_PHYSIC_COMB, m_PhysicComb);
	DDX_Text(pDX, IDC_ENDDATE_EDT, m_end_date);
	DDX_Text(pDX, IDC_STARTDATE_EDT, m_start_date);
	DDX_Control(pDX, IDC_MSFLEXGRID1, m_Grid);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQueryDlg, CDialog)
	//{{AFX_MSG_MAP(CQueryDlg)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQueryDlg message handlers

void CQueryDlg::OnOK() 
{
	// TODO: Add extra validation here
	UpdateData();
	if (m_start_date.IsEmpty() || m_end_date.IsEmpty() )
	{
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("查询日期有误");
		return;
	}
	CString phid;
	m_PhysicComb.GetWindowText(phid);
	if(phid.IsEmpty()) 
	{
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("请选择药品名称");
		return;
	}
	Query(m_start_date,m_end_date,phid.Mid(1,3));
	//CDialog::OnOK();
}
void CQueryDlg::DefineGrid()
{
	m_Grid.SetRows(2);
	m_Grid.SetCols(7);
	m_Grid.SetRowHeight(0,300);
	m_Grid.SetRowHeight(1,300);

	m_Grid.SetFixedRows(1);
	m_Grid.SetFixedCols(0);
	m_Grid.SetMergeCol(0,TRUE);
	m_Grid.SetMergeCol(1,TRUE);
	m_Grid.SetMergeCol(2,TRUE);
    m_Grid.SetMergeRow(0,TRUE);
	m_Grid.SetTextMatrix(0,0,"医院名称");
	
	m_Grid.SetTextMatrix(0,1,"科室名称");
	m_Grid.SetTextMatrix(0,2,"医生名称");
	char tmp[100];
	m_Grid.SetTextMatrix(0,3,"销售数量");
	m_Grid.SetColAlignment(3,7);
	m_Grid.SetTextMatrix(0,4,"应付金额");
	m_Grid.SetColAlignment(4,7);
	m_Grid.SetTextMatrix(0,5,"折扣金额");
	m_Grid.SetColAlignment(5,7);
	m_Grid.SetTextMatrix(0,6,"实付金额");
	m_Grid.SetColAlignment(6,7);

	try{
		DBLibrary DB(GetDB());
		DB.Open("SELECT physic_id,RTRIM(Physic_Name) pn from physic order by physic_id");
		while(!DB.isEof())
		{
			memset(tmp,0,100);
			sprintf(tmp,"[%03d]",DB.GetValue("physic_id"));
			DB.GetValue("pn",tmp+5);
			m_PhysicComb.AddString((LPCTSTR)tmp);
			DB.Next();
		}
	}catch(DBErr &err)
	{
		char *str;
		int code;
		err.GetLastErr(code,&str);
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("%s(%d)",str,code);
	}
	catch(...)
	{
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("unknow error (-1)");
	}
	m_Grid.SetColWidth(0,3000);
	m_Grid.SetColWidth(1,1000);
}

BOOL CQueryDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	DefineGrid();
	// TODO: Add extra initialization here
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

CDBSession CQueryDlg::GetDB()
{
	return ((CPhysicMApp *)AfxGetApp())->DBSession;
}
void CQueryDlg::Query(CString &sdt,CString &edt,CString &ph_id)
{
	DBLibrary phDB(GetDB());
	DBLibrary queryDB(GetDB());
	char tmp[4][100];
    double sum[4];
	double curval[4];

	curval[0]=0.0;
	curval[1]=0.0;
	curval[2]=0.0;
	curval[3]=0.0;
	sum[0]=0.0;
	sum[1]=0.0;
	sum[2]=0.0;
	sum[3]=0.0;
	int row=1,i=0;
	try{
		phDB.Open("select a.hospital_name hname,b.office_name ofname, "
			" c.doctor_name dname,sum(d.physic_num) num ,"
			" sum(d.doctor_rate * d.physic_num) sum_fee, sum(d.sub_fee) sub_fee, sum(d.real_fee) real_fee "
			" from hospital a,office b,doctor c, sale_number d,physic e "
			" where d.sale_date between '%s' and '%s' and d.physic_id = %s "
			" and a.hospital_id = d.hospital_id and b.office_id=c.office_id and c.doctor_id=d.doctor_id and d.physic_id=e.physic_id "
			" group by a.hospital_name ,b.office_name ,c.doctor_name ",
			sdt,edt,ph_id);
		while(!phDB.isEof())
		{
			m_Grid.SetRows(row+1);
			memset(tmp,0,sizeof(char)*4*100);
			phDB.GetValue("hname",tmp[0]);
			phDB.GetValue("ofname",tmp[1]);
			phDB.GetValue("dname",tmp[2]);
			//
			for(i=0;i<4;i++) curval[i]=0.00;

			phDB.GetValue("num",&curval[0]);
			phDB.GetValue("sum_fee",&curval[1]);
			phDB.GetValue("sub_fee",&curval[2]);
			phDB.GetValue("real_fee",&curval[3]);
			for(i=0;i<4;i++)
				sum[i] += curval[i];
			for(i=0;i<3;i++)
				m_Grid.SetTextMatrix(row,i,(LPCTSTR)tmp[i]);

			for(i=0;i<4;i++)
				sprintf(tmp[i],"%.2f",curval[i]);
			for(i=3;i<7;i++)
				m_Grid.SetTextMatrix(row,i,(LPCTSTR)tmp[i-3]);
			m_Grid.SetRowHeight(row,300);
			row++;
 			phDB.Next();
		}
		m_Grid.SetRows(row + 1);
		m_Grid.SetRowHeight(row,300);
		m_Grid.SetTextMatrix(row,0,"合 计");
		/*
		m_Grid.SetTextMatrix(row,1,"合 计");
		m_Grid.SetTextMatrix(row,2,"合 计");
		m_Grid.SetMergeCells(0);
		m_Grid.SetMergeCells(1);
		m_Grid.SetMergeCells(2);
		.SetMergeRow(row,true);
		m_Grid.SetCellAlignment(4);
		*/
		for(i=0;i<4;i++)
			sprintf(tmp[i],"%.2f",sum[i]);
		for(i=3;i<7;i++)
			m_Grid.SetTextMatrix(row,i,(LPCTSTR)tmp[i-3]);

	}catch(DBErr &err)
	{
		char *str;
		int code;
		err.GetLastErr(code,&str);
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("%s(%d)",str,code);
	}
	catch(...)
	{
		((CMainFrame *)AfxGetApp()->m_pMainWnd)->AddErr("unknow error (-1)");
	}
}