trialbalance.h

Visual C++.net数据库开发经典案例

#pragma once
#include "LinkDataBase.h"
#include "DataGridNoActiveCellColumn.h"

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;


namespace My
{
	/// <summary> 
	/// TrialBalance 摘要
	///
	/// 警告: 如果您更改该类的名称，则将需要更改 
	///          与该类所依赖的所有 .resx 文件关联的托管资源编译器工具的 
	///          “资源文件名”属性。  否则，
	///          设计器将不能与此窗体关联的
	///          本地化资源正确交互。
	/// </summary>
	public __gc class TrialBalance : public System::Windows::Forms::Form
	{
	//--------------------成员声明------------------------
	private:
		LinkDataBase* MyDataBase;	//数据库连接类
		DataSet* ds;		
		String* strSQL;
		SqlDataAdapter* DetailAdapter;
		DataTable* IsBalanceTable;
		
	//-------------------成员声明结束-----------------------
	public: 
		TrialBalance(bool isNewAccount)
		{
			InitializeComponent();
			this->MyDataBase = new LinkDataBase();
			this->DetailAdapter = new SqlDataAdapter();			
			this->ds = new DataSet();
			this->IsBalanceTable = new DataTable();			
			if(isNewAccount)
			{
				this->strSQL = S"SELECT 科目代码,科目名称,(case when 余额方向 = '借方' then 期初余额 else 0 end - 累计借方)"
					S" as 期初借方, (case when 余额方向 = '贷方' then 期初余额 else 0 end - 累计贷方) as 期初贷方,累计借方"
					S" as 本期发生借方,累计贷方 as 本期发生贷方,(case when 余额方向 = '借方' then 期初余额 else 0 end) as "
					S"期末借方,(case when 余额方向 = '贷方' then 期初余额 else 0 end) as 期末贷方 from 帐簿初始化表 where"
					S" 累计借方 <> 0 or 累计贷方<> 0 or 期初余额 <> 0 or 期初余额 <> 0";
					
			}
			else
			{
				this->strSQL = S"SELECT 本期汇总帐簿.科目代码,科目表.科目名称,(case when 余额方向 = '借方' then 期初余额 else 0 end)"
					S" as 期初借方, (case when 余额方向 = '贷方' then 期初余额 else 0 end) as 期初贷方,本期借方合计"
					S" as 本期发生借方,本期贷方合计 as 本期发生贷方,(case when 余额方向 = '借方' then 期初余额 else 0 end) as "
					S"期末借方,(case when 余额方向 = '贷方' then 期初余额 else 0 end) as 期末贷方 from 本期汇总帐簿,科目表 where"
					S"本期汇总帐簿.科目代码 = 科目表.科目代码 and (本期借方合计 <> 0 or 本期贷方合计 <> 0 or 期初余额 <> 0 or 余额 <> 0)";
			}
			this->MyDataBase->SelectDataBase(this->DetailAdapter,this->ds,this->strSQL,S"试算平衡明细表");			
		}
        
	protected: 
		void Dispose(Boolean disposing)
		{
			if (disposing && components)
			{
				components->Dispose();
			}
			__super::Dispose(disposing);
		}
	private: System::Windows::Forms::GroupBox *  groupBox1;
	private: System::Windows::Forms::DataGrid *  dgrdDetails;
	private: System::Windows::Forms::DataGrid *  dgrdIsBalance;




	private:
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		System::ComponentModel::Container* components;

		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		void InitializeComponent(void)
		{
			this->groupBox1 = new System::Windows::Forms::GroupBox();
			this->dgrdIsBalance = new System::Windows::Forms::DataGrid();
			this->dgrdDetails = new System::Windows::Forms::DataGrid();
			this->groupBox1->SuspendLayout();
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dgrdIsBalance))->BeginInit();
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dgrdDetails))->BeginInit();
			this->SuspendLayout();
			// 
			// groupBox1
			// 
			this->groupBox1->Controls->Add(this->dgrdIsBalance);
			this->groupBox1->Dock = System::Windows::Forms::DockStyle::Bottom;
			this->groupBox1->Location = System::Drawing::Point(0, 229);
			this->groupBox1->Name = S"groupBox1";
			this->groupBox1->Size = System::Drawing::Size(704, 152);
			this->groupBox1->TabIndex = 0;
			this->groupBox1->TabStop = false;
			this->groupBox1->Text = S"试算平衡结果";
			// 
			// dgrdIsBalance
			// 
			this->dgrdIsBalance->DataMember = S"";
			this->dgrdIsBalance->Dock = System::Windows::Forms::DockStyle::Fill;
			this->dgrdIsBalance->HeaderForeColor = System::Drawing::SystemColors::ControlText;
			this->dgrdIsBalance->Location = System::Drawing::Point(3, 17);
			this->dgrdIsBalance->Name = S"dgrdIsBalance";
			this->dgrdIsBalance->Size = System::Drawing::Size(698, 132);
			this->dgrdIsBalance->TabIndex = 0;
			// 
			// dgrdDetails
			// 
			this->dgrdDetails->DataMember = S"";
			this->dgrdDetails->Dock = System::Windows::Forms::DockStyle::Fill;
			this->dgrdDetails->HeaderForeColor = System::Drawing::SystemColors::ControlText;
			this->dgrdDetails->Location = System::Drawing::Point(0, 0);
			this->dgrdDetails->Name = S"dgrdDetails";
			this->dgrdDetails->Size = System::Drawing::Size(704, 229);
			this->dgrdDetails->TabIndex = 1;
			// 
			// TrialBalance
			// 
			this->AutoScaleBaseSize = System::Drawing::Size(6, 14);
			this->ClientSize = System::Drawing::Size(704, 381);
			this->Controls->Add(this->dgrdDetails);
			this->Controls->Add(this->groupBox1);
			this->Name = S"TrialBalance";
			this->Text = S"试算平衡";
			this->Load += new System::EventHandler(this, TrialBalance_Load);
			this->groupBox1->ResumeLayout(false);
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dgrdIsBalance))->EndInit();
			(__try_cast<System::ComponentModel::ISupportInitialize *  >(this->dgrdDetails))->EndInit();
			this->ResumeLayout(false);

		}		
private: System::Void TrialBalance_Load(System::Object *  sender, System::EventArgs *  e)
		 {
			 //期初借方
			 Decimal iniDebit = 0;
			 //期初贷方
			 Decimal iniLoan = 0;
			 //本期发生借方
			 Decimal thisDebit = 0;
			 //本期发生贷方
			 Decimal thisLoan = 0;
			 //期末借方
			 Decimal finDebit = 0;
			 //期末贷方
			 Decimal finLoan = 0;
			 //试算平衡
			 String* isBalance = S"试算平衡";

			 this->dgrdDetails->DataSource = this->ds->Tables->Item[S"试算平衡明细表"];
			 this->DataGridStateDetail();

			 for(int i = 0;i < this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Count;i++)
			 {
				 iniDebit = iniDebit + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"期初借方"]);
				 iniLoan = iniLoan + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"期初贷方"]);
				 thisDebit = thisDebit + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"本期发生借方"]);
				 thisLoan = thisLoan + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"本期发生贷方"]);
				 finDebit = finDebit + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"期末借方"]);
				 finLoan = finLoan + System::Convert::ToDecimal(this->ds->Tables->Item[S"试算平衡明细表"]->Rows->Item[i]->Item[S"期末贷方"]);
			 }
			 if(iniDebit != iniLoan || thisDebit != thisLoan || finDebit != finLoan)
			 {
				 isBalance = S"试算不平衡";
			 }
			 this->IsBalanceTable->Columns->Add(S"是否平衡",System::Type::GetType(S"System.String"));
			 this->IsBalanceTable->Columns->Add(S"期初借方",System::Type::GetType(S"System.Decimal"));
			 this->IsBalanceTable->Columns->Add(S"期初贷方",System::Type::GetType(S"System.Decimal"));
			 this->IsBalanceTable->Columns->Add(S"本期发生借方",System::Type::GetType(S"System.Decimal"));
			 this->IsBalanceTable->Columns->Add(S"本期发生贷方",System::Type::GetType(S"System.Decimal"));
			 this->IsBalanceTable->Columns->Add(S"期末借方",System::Type::GetType(S"System.Decimal"));
			 this->IsBalanceTable->Columns->Add(S"期末贷方",System::Type::GetType(S"System.Decimal"));
			 this->dgrdIsBalance->DataSource = this->IsBalanceTable;
			 this->DataGridStateIsBalance();
			 DataRow* row = this->IsBalanceTable->NewRow();
			 row->set_Item(S"是否平衡",isBalance);
			 row->set_Item(S"期初借方",__box(iniDebit));
			 row->set_Item(S"期初贷方",__box(iniLoan));
			 row->set_Item(S"本期发生借方",__box(thisDebit));
			 row->set_Item(S"本期发生贷方",__box(thisLoan));
			 row->set_Item(S"期末借方",__box(finDebit));
			 row->set_Item(S"期末贷方",__box(finLoan));
			 this->IsBalanceTable->Rows->Add(row);
		 }
		 //------------------设置表格样式----------------------
		 void DataGridStateDetail()
		 {
			 DataGridTableStyle* ts = new DataGridTableStyle();
			 DataGridNoActiveCellColumn* aTextColumn;
			 ts->AlternatingBackColor = Color::LightGray;
			 ts->MappingName = this->ds->Tables->Item[S"试算平衡明细表"]->TableName;

			 int numCols = this->ds->Tables->Item[S"试算平衡明细表"]->Columns->Count;
			 for(int i = 0;i < numCols;i++)
			 {
				 aTextColumn = new DataGridNoActiveCellColumn();
				 aTextColumn->get_TextBox()->Enabled = false;
				 aTextColumn->MappingName = this->ds->Tables->Item[S"试算平衡明细表"]->Columns->Item[i]->ColumnName;
				 aTextColumn->HeaderText = this->ds->Tables->Item[S"试算平衡明细表"]->Columns->Item[i]->ColumnName;
				 aTextColumn->NullText = S"";
				 aTextColumn->Format = S"D";
				 ts->GridColumnStyles->Add(aTextColumn);
			 }
			 this->dgrdDetails->TableStyles->Add(ts);
			 this->dgrdDetails->Select(0);
		 }
		  //------------------设置表格样式----------------------
		 void DataGridStateIsBalance()
		 {
			 DataGridTableStyle* ts = new DataGridTableStyle();
			 DataGridNoActiveCellColumn* aTextColumn;
			 ts->AlternatingBackColor = Color::LightGray;
			 ts->MappingName = this->IsBalanceTable->TableName;

			 int numCols = this->IsBalanceTable->Columns->Count;
			 for(int i = 0;i < numCols;i++)
			 {
				 aTextColumn = new DataGridNoActiveCellColumn();
				 aTextColumn->get_TextBox()->Enabled = false;
				 aTextColumn->MappingName = this->IsBalanceTable->Columns->Item[i]->ColumnName;
				 aTextColumn->HeaderText = this->IsBalanceTable->Columns->Item[i]->ColumnName;
				 aTextColumn->NullText = S"";
				 aTextColumn->Format = S"D";
				 ts->GridColumnStyles->Add(aTextColumn);
			 }
			 this->dgrdIsBalance->TableStyles->Add(ts);
			 this->dgrdIsBalance->Select(0);
		 }

};
}