ad_qry_deptstructanalyse.pas

一个MRPII系统源代码版本

unit Ad_Qry_DeptStructAnalyse;


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_DeptStructAnalyse = Class(TFrm_Base_Qry)
    AdoQry_tmp1: TAdoQuery;
    AdoQry_MainDeptCode: TStringField;
    AdoQry_MainAssetCode: TStringField;
    AdoQry_MainAssetName: TStringField;
    AdoQry_MainAssetB: TStringField;
    AdoQry_MainUomCode: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainSomeFirstAmount: TFloatField;
    AdoQry_MainDeptRate: 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(DeptCode:string):Double;//某一部门或所有部门的固定资产原值之和
    procedure  CreateFirstTmp;                           //创建第一个临时表(#Tmp_Firsttable1);
    procedure UpdateSomeAmount;                          //修改各部门物料的百分比占有率;
    procedure CreateSecondTmp;                           //创建第二个临时表(#Tmp_SecondTable1);
    procedure UpdateSecondTmp;                          //修改第二个临时表百分比占有率;
    function FindDeptName(DeptCode:string):string;     //查找部门名称
    procedure CreateThreeTmp;                           //创建第三个临时表
    procedure DeleteTmpTable;                           //删除临时表    
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
  end;

var
  Frm_Ad_Qry_DeptStructAnalyse: TFrm_Ad_Qry_DeptStructAnalyse;

implementation

{$R *.DFM}
procedure TFrm_Ad_Qry_DeptStructAnalyse.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
begin
  Application.ProcessMessages;
  inherited;
  
  AdoQry_tmp1.Connection:=AdOConnection;
  CreateFirstTmp;
  UpdateSomeAmount;
  CreateSecondTmp;
  UpdateSecondTmp;
  CreateThreeTmp;
  SelectFromSql:='select * from #Tmp_ThreeTable_Dept ';
  OrderByFields:='DeptCode ,Sort,AssetCode';
  Getdata;
end;

function TFrm_Ad_Qry_DeptStructAnalyse.GetSomeAssetAmount(DeptCode:string):Double;
var
  sSQL:string;
begin
  if DeptCode<>'' then
  begin
    //假如参数为all时求出所有部门所有固定资产的值　
    if DeptCode='all' then
      sSQL:=' select sum(Ad_useDept.Assignrate*Ad_AssetCard.FirstAmount)/100 as TotalAmount'+
            ' from Ad_useDept'+
            ' join Ad_AssetCard on Ad_useDept.assetCode=Ad_AssetCard.assetCode'
    //否则求出某一部门所有固定资产的值
    else
      sSQL:=' select sum(Ad_useDept.Assignrate*Ad_AssetCard.FirstAmount)/100 as TotalAmount'+
            ' from Ad_useDept'+
            ' join Ad_AssetCard on Ad_useDept.assetCode=Ad_AssetCard.assetCode'+
            ' where Ad_useDept.DeptCode='+QuotedStr(DeptCode);
  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_DeptStructAnalyse.CreateFirstTmp;//创建第一个临时表(#Tmp_SecondTable1);
var
  sSQL:string;
begin
  sSQL:=' select Ad_useDept.DeptCode,'+
                ' Ad_AssetCard.AssetCode,'+
                ' Ad_AssetCard.AssetName,'+
                ' Ad_AssetCard.AssetCode+'' ''+Ad_AssetCard.AssetName as AssetB,'+
                ' Ad_AssetCard.UomCode,'+
                ' Uom.UomName,'+
                ' (Ad_AssetCard.FirstAmount*Ad_useDept.AssignRate)/100 as SomeFirstAmount,'+
                ' Ad_useDept.AssignRate as DeptRate,'+  //DeptRate和TotalRate只确定数据类型,
                ' Ad_useDept.AssignRate as TotalRate,'+ //不用管数据，后面UpdateSomeAmount会修改
                ' Sort=0'+
        ' into #Tmp_Firsttable1'+
        ' from Ad_useDept'+
        ' join Ad_AssetCard on Ad_useDept.AssetCode=Ad_AssetCard.AssetCode'+
        ' 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_DeptStructAnalyse.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_Firsttable1');
    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_Firsttable1 set '+
               ' DeptRate='+FloatToStr(DeptRateAmount)+
               ','+
               ' TotalRate='+FloatToStr(TotalRateAmount)+
               ' 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_DeptStructAnalyse.CreateSecondTmp; //创建第二个临时表(#Tmp_SecondTable1);
var
  sSQL:string;
begin
  sSQL:=' select Ad_useDept.DeptCode,'+
                ' AssetCode=''                '','+
                ' AssetName=''                                                 '','+
                ' AssetB=''                                                                                                        '','+
                ' UomCode=''        '','+
                ' UomName=''        '','+
                ' sum((Ad_AssetCard.FirstAmount*Ad_useDept.AssignRate)/100) as SomeFirstAmount,'+
                ' sum((Ad_AssetCard.FirstAmount*Ad_useDept.AssignRate)/100) as DeptRate,'+  //DeptRate和TotalRate只确定数据类型,
                ' sum((Ad_AssetCard.FirstAmount*Ad_useDept.AssignRate)/100) as TotalRate,'+ //不用管数据，后面UpdateSomeAmount会修改
                ' Sort=1'+
        ' into #Tmp_SecondTable1'+
        ' from Ad_useDept'+
        ' join Ad_AssetCard on Ad_useDept.AssetCode=Ad_AssetCard.AssetCode'+
        ' Group by DeptCode';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_DeptStructAnalyse.UpdateSecondTmp;  //修改第二个临时表百分比占有率;
var
  updateSQL:string;
  DeptRateAmount,TotalRateAmount,SomeTotalAmount,TotalAmount:double;
begin
  with AdoQry_tmp1 do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from #Tmp_SecondTable1');
    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_SecondTable1 set '+
               ' AssetB='+ QuotedStr(AdoQry_tmp1.fieldbyname('DeptCode').asstring+' '+FindDeptName(AdoQry_tmp1.fieldbyname('DeptCode').asstring))+
               ','+
               ' DeptRate='+FloatToStr(100.00)+
               ','+
               ' TotalRate='+FloatToStr(TotalRateAmount)+
               ' where DeptCode='+QuotedStr(AdoQry_tmp1.fieldbyname('DeptCode').asstring);
    AdoQry_tmp1.Next;
  end;

  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(updateSQL);
    Prepared;
    ExecSQL;
  end;
end;
procedure TFrm_Ad_Qry_DeptStructAnalyse.DeleteTmpTable;
var
  DelSQL:string;
begin
  DelSQL:='drop table #Tmp_Firsttable1 drop table #Tmp_SecondTable1 drop table #Tmp_ThreeTable_Dept';
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(DelSQL);
    Prepared;
    ExecSQL;
  end;
end;

procedure TFrm_Ad_Qry_DeptStructAnalyse.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;

function TFrm_Ad_Qry_DeptStructAnalyse.FindDeptName(DeptCode:string):string; //查找部门名称
var
  sSQL:string;
begin
  sSQL:='select DeptName from Dept where DeptCode='+QuotedStr(DeptCode);
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQl.Add(sSQL);
    open;
  end;
  Result:= AdoQry_tmp.fieldbyname('DeptName').AsString;
end;

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


procedure TFrm_Ad_Qry_DeptStructAnalyse.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ad_Qry_DeptStructAnalyse :=nil;
end;

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

end;

end.