ad_qry_valuestructanalyse.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 291 行

{$A+,B-,C+,D+,E-,F-,G+,H+,I+,J+,K-,L+,M-,N+,O+,P+,Q-,R-,S-,T-,U-,V+,W-,X+,Y+,Z1}
{$MINSTACKSIZE $00004000}
{$MAXSTACKSIZE $00100000}
{$IMAGEBase $00400000}
{$ApPType GUI}
unit Ad_Qry_ValueStructAnalyse;


Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg;

Type
  TFrm_Ad_Qry_ValueStructAnalyse = Class(TFrm_Base_Qry)
    AdoQry_tmp1: TAdoQuery;
    AdoQry_MainAssetTypeCode: TStringField;
    AdoQry_MainAssetTypeName: TStringField;
    AdoQry_MainAssetCode: TStringField;
    AdoQry_MainAssetName: TStringField;
    AdoQry_MainAssetB: TStringField;
    AdoQry_MainUomCode: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainSomeFirstAmount: TFloatField;
    AdoQry_MainDepreciationValue: TFloatField;
    AdoQry_MainNetValue: TFloatField;
    AdoQry_MainOldRate: TFloatField;
    AdoQry_MainNetRate: TFloatField;
    AdoQry_MainSort: TIntegerField;
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
    function GetSomeAssetAmount(AssetTypeCode:string):Double;//某一类别或所有类别的固定资产原值之和
    procedure  CreateFirstTmp;                           //创建第一个临时表(#Tmp_Firsttable2);
    procedure UpdateSomeAmount;                          //修改各部门物料的百分比占有率;
    procedure CreateSecondTmp;                           //创建第二个临时表(#Tmp_SecondTable2);
    procedure UpdateSecondTmp;                          //修改第二个临时表百分比占有率;
    procedure DeleteTmpTable;                           //删除临时表
    procedure CreateThreeTmp;                           //创建第三个临时表
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
  end;

var
  Frm_Ad_Qry_ValueStructAnalyse: TFrm_Ad_Qry_ValueStructAnalyse;

implementation

{$R *.DFM}
procedure TFrm_Ad_Qry_ValueStructAnalyse.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
begin
  Application.ProcessMessages;
  inherited;
//  FreeFields:='SomeFirstAmount,DeptRate,TotalRate';
  AdoQry_tmp1.Connection:=AdOConnection;
  CreateFirstTmp;
  UpdateSomeAmount;
  CreateSecondTmp;
  UpdateSecondTmp;
  CreateThreeTmp;
  SelectFromSql:='select * from #Tmp_ThreeTable_Value ';
  OrderByFields:='AssetTypeCode ,Sort,AssetCode';
  Getdata;
end;

function TFrm_Ad_Qry_ValueStructAnalyse.GetSomeAssetAmount(AssetTypeCode:string):Double;
var
  sSQL:string;
begin
  if AssetTypeCode<>'' then
  begin
    //假如参数为all时求出所有类别所有固定资产的值　
    if AssetTypeCode='all' then
      sSQL:=' select sum(FirstAmount) as TotalAmount'+
            ' from Ad_AssetCard'
    //否则求出某一类别所有固定资产的值
    else
      sSQL:=' select sum(FirstAmount) as TotalAmount'+
            ' from Ad_AssetCard'+
            ' where AssetTypeCode='+QuotedStr(AssetTypeCode);
  end;
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQL.Add(sSQL);
    Open;
    Result:=AdoQry_tmp.fieldbyname('TotalAmount').AsFloat;
  end;
end;

procedure  TFrm_Ad_Qry_ValueStructAnalyse.CreateFirstTmp;//创建第一个临时表(#Tmp_SecondTable2);
var SSQL:string;
begin
  sSQL:='select  Ad_AssetCard.AssetTypeCode,'+
                ' Ad_AssetType.AssetTypeName,'+
                ' Ad_AssetCard.AssetCode,'+
                ' Ad_AssetCard.AssetName,'+
                ' Ad_AssetCard.AssetCode+'' ''+Ad_AssetCard.AssetName as AssetB,'+
                ' Ad_AssetCard.UomCode,'+
                ' Uom.UomName,'+
                ' Ad_AssetCard.FirstAmount as SomeFirstAmount,'+
                ' Ad_AssetCard.DepreciationValue,'+
                ' Ad_AssetCard.NetValue,'+
                ' case when Ad_AssetCard.FirstAmount=0 then 0 else Ad_AssetCard.DepreciationValue/Ad_AssetCard.FirstAmount*100 end as OldRate,'+
                ' case when Ad_AssetCard.FirstAmount=0 then 0 else Ad_AssetCard.NetValue/Ad_AssetCard.FirstAmount*100 end as NetRate,'+
                ' Sort=0'+
        ' into #Tmp_Firsttable2'+
        ' from  Ad_AssetCard'+
        ' join Ad_AssetType on Ad_AssetCard.AssetTypeCode=Ad_AssetType.AssetTypeCode'+
        ' join Uom on Ad_AssetCard.UomCode=Uom.UomCode';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.UpdateSomeAmount;//修改第一个临时表各部门物料的百分比占有率;
var
  updateSQL:string;
  DeptRateAmount,TotalRateAmount:double;
  test,test1,test2:double;
begin
  {with AdoQry_tmp1 do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from #Tmp_Firsttable2');
    Open;
  end;

  AdoQry_tmp1.First;
  updateSQL:='';
  DeptRateAmount:=0;
  TotalRateAmount:=0;
  while not AdoQry_tmp1.Eof do
  begin
    DeptRateAmount:=AdoQry_tmp1.fieldbyname('SomeFirstAmount').AsFloat*100/GetSomeAssetAmount(AdoQry_tmp1.fieldbyname('DeptCode').AsString);
    TotalRateAmount:=AdoQry_tmp1.fieldbyname('SomeFirstAmount').AsFloat*100/GetSomeAssetAmount('all');
    updateSQL:=updateSQL+' update #Tmp_Firsttable2 set '+
               ' DeptRate='+FloatToStr(DeptRateAmount)+
               ','+
               ' TotalRate='+FloatToStr(DeptRateAmount)+
               ' where DeptCode='+QuotedStr(AdoQry_tmp1.fieldbyname('DeptCode').asstring)+' and '+
               ' AssetCode= '+QuotedStr(AdoQry_tmp1.fieldbyname('AssetCode').asstring);
    AdoQry_tmp1.Next;
  end;

  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(updateSQL);
    Prepared;
    ExecSQL;
  end;}
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.CreateSecondTmp; //创建第二个临时表(#Tmp_SecondTable2);
var
  sSQL:string;
begin
  sSQL:=' select  Ad_AssetCard.AssetTypeCode,'+
                ' Ad_AssetType.AssetTypeName,'+
                ' AssetCode=''         '','+
                ' AssetName=''         '','+
                ' AssetB=''                                              '','+
                ' UomCode=''              '','+
                ' UomName=''              '','+
                ' sum(Ad_AssetCard.FirstAmount) as SomeFirstAmount,'+
                ' sum(Ad_AssetCard.DepreciationValue) as DepreciationValue,'+
                ' sum(Ad_AssetCard.NetValue) as NetValue,'+
                ' case when sum(Ad_AssetCard.FirstAmount)=0 then 0 else sum(Ad_AssetCard.DepreciationValue)/sum(Ad_AssetCard.FirstAmount)*100 end as OldRate,'+
                ' case when sum(Ad_AssetCard.FirstAmount)=0 then 0 else sum(Ad_AssetCard.NetValue)/sum(Ad_AssetCard.FirstAmount)*100 end as NetRate,'+
                ' Sort=1 '+
        ' into #Tmp_SecondTable2'+
        ' from  Ad_AssetCard'+
        ' join Ad_AssetType on Ad_AssetCard.AssetTypeCode=Ad_AssetType.AssetTypeCode'+
        ' Group by Ad_AssetCard.AssetTypeCode,Ad_AssetType.AssetTypeName';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.UpdateSecondTmp;  //修改第二个临时表;
var
  updateSQL:string;
begin
  with AdoQry_tmp1 do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from #Tmp_SecondTable2');
    Open;
  end;

  AdoQry_tmp1.First;
  updateSQL:='';
  while not AdoQry_tmp1.Eof do
  begin
    updateSQL:=updateSQL+' update #Tmp_SecondTable2 set '+
               ' AssetB='+ QuotedStr(AdoQry_tmp1.fieldbyname('AssetTypeCode').asstring+' '+AdoQry_tmp1.fieldbyname('AssetTypeName').asstring)+
               ' where AssetTypeCode='+QuotedStr(AdoQry_tmp1.fieldbyname('AssetTypeCode').asstring);
    AdoQry_tmp1.Next;
  end;

  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(updateSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.DeleteTmpTable;
var
  DelSQL:string;
begin
  DelSQL:='drop table #Tmp_Firsttable2 drop table #Tmp_SecondTable2 drop table #tmp_threetable_Value';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(DelSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.DBGridEhGetCellParams(
  Sender: TObject; Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if AdoQry_Main.fieldbyname('Sort').AsInteger=1 then
  begin
    AFont.Color:=clred;
    Afont.Style:=[fsbold];
  end;  
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.CreateThreeTmp;     //创建第三个临时表
var
  sSQL:string;
begin
  sSQL:='select * into #Tmp_ThreeTable_Value from #Tmp_Firsttable2 union select * from #Tmp_SecondTable2 ';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  DeleteTmpTable;

end;

procedure TFrm_Ad_Qry_ValueStructAnalyse.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ad_Qry_ValueStructAnalyse:=nil;
  
end;

end.