ad_qry_typestructanalyse.pas

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

unit Ad_Qry_TypeStructAnalyse;


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_TypeStructAnalyse = 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_MainTypeRate: TFloatField;
    AdoQry_MainTotalRate: TFloatField;
    AdoQry_MainSort: TIntegerField;
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
    function GetSomeAssetAmount(AssetTypeCode:string):Double;//某一类别或所有类别的固定资产原值之和
    procedure  CreateFirstTmp;                           //创建第一个临时表(#Tmp_FirstTable);
    procedure UpdateSomeAmount;                          //修改各部门物料的百分比占有率;
    procedure CreateSecondTmp;                           //创建第二个临时表(#Tmp_SecondTable);
    procedure UpdateSecondTmp;                          //修改第二个临时表百分比占有率;
    procedure DeleteTmpTable;                           //删除临时表
    procedure CreateThreeTmp;                           //创建第三个临时表
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
  end;

var
  Frm_Ad_Qry_TypeStructAnalyse: TFrm_Ad_Qry_TypeStructAnalyse;

implementation

{$R *.DFM}
procedure TFrm_Ad_Qry_TypeStructAnalyse.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_Type ';
  OrderByFields:='AssetTypeCode ,Sort,AssetCode';
  Getdata;
end;

function TFrm_Ad_Qry_TypeStructAnalyse.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_TypeStructAnalyse.CreateFirstTmp;//创建第一个临时表(#Tmp_SecondTable);
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,'+
//                ' TypeRate=0,'+
//                ' TotalRate=0,'+
                ' Ad_AssetCard.FirstAmount as TypeRate,'+  //DeptRate和TotalRate只确定数据类型,
                ' Ad_AssetCard.FirstAmount as TotalRate,'+ //不用管数据，后面UpdateSomeAmount会修改
                ' Sort=0'+
        ' into #Tmp_FirstTable'+
        ' 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_TypeStructAnalyse.UpdateSomeAmount;//修改第一个临时表各部门物料的百分比占有率;
var
  updateSQL:string;
  TypeRateAmount,TotalRateAmount:double;
  test,test1,test2:double;
begin
  with AdoQry_tmp1 do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from #Tmp_FirstTable');
    Open;
  end;

  AdoQry_tmp1.First;
  updateSQL:='';
  TypeRateAmount:=0;
  TotalRateAmount:=0;
  while not AdoQry_tmp1.Eof do
  begin
    TypeRateAmount:=AdoQry_tmp1.fieldbyname('SomeFirstAmount').AsFloat*100/GetSomeAssetAmount(AdoQry_tmp1.fieldbyname('AssetTypeCode').AsString);
    TotalRateAmount:=AdoQry_tmp1.fieldbyname('SomeFirstAmount').AsFloat*100/GetSomeAssetAmount('all');
    updateSQL:=updateSQL+' update #Tmp_FirstTable set '+
               ' TypeRate='+FloatToStr(TypeRateAmount)+
               ','+
               ' TotalRate='+FloatToStr(TotalRateAmount)+
               ' where 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_TypeStructAnalyse.CreateSecondTmp; //创建第二个临时表(#Tmp_SecondTable);
var
  sSQL:string;
begin
  sSQL:=' select Ad_AssetCard.AssetTypeCode,'+
                ' Ad_AssetType.AssetTypeName,'+
                ' AssetCode=''       '','+
                ' AssetName=''       '','+
                ' AssetB=''                                     '','+
                ' UomCode=''       '','+
                ' UomName=''                  '','+
                ' Sum(Ad_AssetCard.FirstAmount) as SomeFirstAmount,'+
//                ' TypeRate=100,'+
//                ' TotalRate=100,'+
                ' Sum(Ad_AssetCard.FirstAmount) as TypeRate,'+  //DeptRate和TotalRate只确定数据类型,
                ' Sum(Ad_AssetCard.FirstAmount) as TotalRate,'+ //不用管数据，后面UpdateSomeAmount会修改
                ' Sort=1'+
        ' into #Tmp_SecondTable'+
        ' 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_TypeStructAnalyse.UpdateSecondTmp;  //修改第二个临时表百分比占有率;
var
  updateSQL:string;
  DeptRateAmount,TotalRateAmount,SomeTotalAmount,TotalAmount:double;
begin
  with AdoQry_tmp1 do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from #Tmp_SecondTable');
    Open;
  end;

  AdoQry_tmp1.First;
  updateSQL:='';
  TotalRateAmount:=0;
  TotalAmount:=GetSomeAssetAmount('all');
  while not AdoQry_tmp1.Eof do
  begin
    SomeTotalAmount:=AdoQry_tmp1.fieldbyname('SomeFirstAmount').AsFloat*100;
    TotalRateAmount:=SomeTotalAmount/TotalAmount;
    updateSQL:=updateSQL+' update #Tmp_SecondTable set '+
               ' AssetB='+ QuotedStr(AdoQry_tmp1.fieldbyname('AssetTypeCode').asstring+' '+AdoQry_tmp1.fieldbyname('AssetTypeName').asstring)+
               ','+
               ' TypeRate='+FloatToStr(100.00)+
               ','+
               ' TotalRate='+FloatToStr(TotalRateAmount)+
               ' 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_TypeStructAnalyse.DeleteTmpTable;
var
  DelSQL:string;
begin
  DelSQL:='drop table #Tmp_FirstTable drop table #Tmp_SecondTable drop table #tmp_threetable_Type' ;
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(DelSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_TypeStructAnalyse.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_TypeStructAnalyse.CreateThreeTmp;     //创建第三个临时表
var
  sSQL:string;
begin
  sSQL:='select * into #Tmp_ThreeTable_Type from #Tmp_FirstTable union select * from #Tmp_SecondTable ';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_TypeStructAnalyse.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ad_Qry_TypeStructAnalyse:=nil;
end;

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

end;

end.