pingheng.~cpp

全面剖析财务管理的内容

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "pingheng.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TfmPingheng *fmPingheng;
//---------------------------------------------------------------------------
__fastcall TfmPingheng::TfmPingheng(TComponent* Owner)
    : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TfmPingheng::FormCreate(TObject *Sender)
{
    StringGrid1->Cells[0][1] = "合计";
    StringGrid1->ColWidths[0] = DBGrid1->Columns->Items[0]->Width + 15;
    StringGrid1->Cells[1][0] = "是否平衡";
    StringGrid1->ColWidths[1] = DBGrid1->Columns->Items[1]->Width;
    StringGrid1->Cells[2][0] = "期初借方";
    StringGrid1->ColWidths[2] = DBGrid1->Columns->Items[2]->Width;;
    StringGrid1->Cells[3][0] = "期初贷方";
    StringGrid1->ColWidths[3] = DBGrid1->Columns->Items[3]->Width;
    StringGrid1->Cells[4][0] = "本期发生借方";
    StringGrid1->ColWidths[4] = DBGrid1->Columns->Items[4]->Width;
    StringGrid1->Cells[5][0] = "本期发生贷方";
    StringGrid1->ColWidths[5] = DBGrid1->Columns->Items[5]->Width;
    StringGrid1->Cells[6][0] = "期末借方";
    StringGrid1->ColWidths[6] = DBGrid1->Columns->Items[6]->Width;;
    StringGrid1->Cells[7][0] = "期末贷方";
    StringGrid1->ColWidths[7] = DBGrid1->Columns->Items[7]->Width;;

}
//---------------------------------------------------------------------------
bool TfmPingheng::CalPingHeng(int nStatus)
{
    // 根据不同的状态计算平衡情况
    AnsiString sql;
    // 初始化帐簿的试算平衡,需要根据初始化的信息反算期初
    if(nStatus == 0)
    {
        sql = "select 科目代码, 科目名称,";
        sql += "(case when 余额方向='借方' then 期初余额 else 0 end - 累计借方)";
        sql += " as 期初借方,";
        sql += "(case when 余额方向='贷方' then 期初余额 else 0 end - 累计贷方)";
        sql += " as 期初贷方,";
        sql += "累计借方 as 本期发生借方,累计贷方 as 本期发生贷方,";
        sql += "case when 余额方向='借方' then 期初余额 else 0 end as 期末借方,";
        sql += "case when 余额方向='贷方' then 期初余额 else 0 end as 期末贷方";
        sql += " from 帐簿初始化表 ";
        sql += " where 累计借方<>0 or 累计贷方<>0 or 期初余额<>0";
        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add(sql);
        Query1->Open();
        // 计算合计金额,用于判断是否平衡
        sql = "select ";
    	sql += "sum(case when 余额方向='借方' then 期初余额 else 0 end - 累计借方)";
        sql += " as 期初借方合计,";
        sql += "sum(case when 余额方向='贷方' then 期初余额 else 0 end - 累计贷方)";
        sql += " as 期初贷方合计,";
    	sql += "sum(累计借方) as 本期借方合计,sum(累计贷方) as 本期贷方合计,";
        sql += "sum(case when 余额方向='借方' then 期初余额 else 0 end) ";
        sql += "as 期末借方合计,";
    	sql += "sum(case when 余额方向='贷方' then 期初余额 else 0 end) as ";
        sql += "期末贷方合计";
        sql += " from 帐簿初始化表 ";
    	sql += " where 累计借方<>0 or 累计贷方<>0 or 期初余额<>0";
        Query2->Close();
        Query2->SQL->Clear();
        Query2->SQL->Add(sql);
        Query2->Open();
        StringGrid1->Cells[2][1] = Query2->FieldByName
            ("期初借方合计")->AsString;
        StringGrid1->Cells[3][1] = Query2->FieldByName
            ("期初贷方合计")->AsString;
        StringGrid1->Cells[4][1] = Query2->FieldByName
            ("本期借方合计")->AsString;
        StringGrid1->Cells[5][1] = Query2->FieldByName
            ("本期贷方合计")->AsString;
        StringGrid1->Cells[6][1] = Query2->FieldByName
            ("期末借方合计")->AsString;
        StringGrid1->Cells[7][1] = Query2->FieldByName
            ("期末贷方合计")->AsString;
    }
    else
    {
        sql = "select a.科目代码, b.科目名称,";
        sql += "(case when a.余额方向='借方' then 期初余额 else 0 end )";
        sql += " as 期初借方,";
        sql += "(case when a.余额方向='贷方' then 期初余额 else 0 end)";
        sql += " as 期初贷方,";
        sql += "本期借方合计 as 本期发生借方, 本期贷方合计 as 本期发生贷方,";
        sql += "case when a.余额方向='借方' then 余额 else 0 end as 期末借方,";
	    sql += "case when a.余额方向='贷方' then 余额 else 0 end as 期末贷方";
    	sql += " from 本期汇总账簿 as a, 科目表 as b  ";
	    sql += " where a.科目代码 = b.科目代码 and (本期借方合计<> 0";
        sql += " or 本期贷方合计<>0 or 期初余额<>0 or 余额<>0)";
        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add(sql);
        Query1->Open();
        // 计算合计金额,用于判断是否平衡
        sql = "select ";
    	sql += "sum(case when 余额方向='借方' then 期初余额 else 0 end)";
        sql += " as 期初借方合计,";
        sql += "sum(case when 余额方向='贷方' then 期初余额 else 0 end)";
        sql += " as 期初贷方合计,";
    	sql += "sum(本期借方合计) as 本期借方合计,sum(本期贷方合计) ";
        sql += "as 本期贷方合计,";
        sql += "sum(case when 余额方向='借方' then 余额 else 0 end) ";
        sql += "as 期末借方合计,";
        sql += "sum(case when 余额方向='贷方' then 余额 else 0 end) ";
        sql += "as 期末贷方合计";
        sql += " from 本期汇总账簿 ";
        sql += " where 本期借方合计<> 0 or 本期贷方合计<>0 or ";
        sql += "期初余额<>0 or 余额<>0";
        Query2->Close();
        Query2->SQL->Clear();
        Query2->SQL->Add(sql);
        Query2->Open();
        StringGrid1->Cells[2][1] = Query2->FieldByName
            ("期初借方合计")->AsString;
        StringGrid1->Cells[3][1] = Query2->FieldByName
            ("期初贷方合计")->AsString;
        StringGrid1->Cells[4][1] = Query2->FieldByName
            ("本期借方合计")->AsString;
        StringGrid1->Cells[5][1] = Query2->FieldByName
            ("本期贷方合计")->AsString;
        StringGrid1->Cells[6][1] = Query2->FieldByName
            ("期末借方合计")->AsString;
        StringGrid1->Cells[7][1] = Query2->FieldByName
            ("期末贷方合计")->AsString;
    }
    // 判断数据是否平衡
    {
        if(StringGrid1->Cells[2][1] != StringGrid1->Cells[3][1] ||
           StringGrid1->Cells[4][1] != StringGrid1->Cells[5][1] ||
           StringGrid1->Cells[6][1] != StringGrid1->Cells[7][1] )
        // 结果不平衡
        {
            StringGrid1->Cells[1][1] = "不平衡";
            StringGrid1->Font->Color = clRed;
            return false;
        }
        else
        {
            StringGrid1->Cells[1][1] = "平衡";
            StringGrid1->Font->Color = clWindowText;
            return true;
        }
    }
}
//---------------------------------------------------------------------------