bas_qry_bomcomparesheet.pas

一个MRPII系统源代码版本

unit Bas_Qry_BomCompareSheet;

Interface

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

Type
  TFrm_Bas_Qry_BomCompareSheet = Class(TFrm_Base_Panel)
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    ToolButton8: TToolButton;
    ExtPrintReport1: TExtPrintReport;
    DBGridEh1: TDBGridEh;
    DataSource1: TDataSource;
    AdoQry_Main: TAdoQuery;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    procedure Act_ExcelExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Act_QuitExecute(Sender: TObject);
    procedure DBGridEh1MouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure DBGridEh1KeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure AdoQry_MainAfterOpen(DataSet: TDataSet);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    { Private declarations }
    flag:integer;
    colorflag:integer;
    Tmp_Name:string;
    object1,ite_ItemCode1str,ItemCode1str,BomItemType1str,Bomqty1str,BomScrAp_Percent1str,BomStatus1str,DeptName1str:string;
    object2,ite_ItemCode2str,ItemCode2str,BomItemType2str,Bomqty2str,BomScrAp_Percent2str,BomStatus2str,DeptName2str:string;
    procedure setReport;
    //procedure Compare;
    procedure inivar;
    procedure displayCodeName;                         //显示父,子项代码和名称;
   // function getItemName(ItemCode:string);
   function getItemCodeName(ItemCode:string):string;

  public
    { Public declarations }
    procedure RunBomCompare(ite_ItemCode1,ite_ItemCode2:string;AdoConnection:TAdoConnection);
    //procedure Initform(AdoConnection:TAdoConnection);
  end;

var
  Frm_Bas_Qry_BomCompareSheet: TFrm_Bas_Qry_BomCompareSheet;

implementation

uses Sys_Global;

{$R *.DFM}

{ TFrm_Bas_Qry_BomCompareSheet }

procedure TFrm_Bas_Qry_BomCompareSheet.RunBomCompare(ite_ItemCode1,
  ite_ItemCode2: string;AdoConnection:TAdoConnection);
begin
  flag:=0;
  Tmp_Name:=FormatDateTime('yyyymmddhhmmssnnn',now());
  Tmp_Name:='Tmp'+Tmp_Name;
  Screen.Cursor:=CrSQLWait;
  AdoQry_Tmp.Connection:=AdoConnection;
  ite_ItemCode1str:=ite_ItemCode1+' '+getItemCodeName(ite_ItemCode1);
  ite_ItemCode2str:=ite_ItemCode2+' '+getItemCodeName(ite_ItemCode2);
  with AdoQry_Main do
  begin
    Connection:=AdoConnection;
    Close;
    sql.clear;
    sql.Add('set noCount on '+
              ' if OBJECT_ID('+''''+Tmp_Name+''''+') is not null '+
              '  drop table '+Tmp_Name+'  '+
              'create table '+Tmp_Name+' '+
              '  ( ItemCodeName varchAr(77) null,'+
              '    ite_ItemCode varchAr(16) null,'+
              '    BomItemType varchAr(10) null,'+
              '    Bomqty varchAr(8) null,'+
              '    BomScrAp_Percent varchAr(8) null, '+
              '    BomStatus  varchAr(8) null, '+
              '    DeptCodeName varchAr(49) null,'+
              '    colorflag  varchAr(2) null  )  '+
              'select * from '+Tmp_Name+' ');

      open;
    try
      with AdoQry_Tmp do
      begin

        Close;
        sql.clear;
        sql.Add('select a.ItemCodeName1 as ItemCodeName1'
                        +' ,a.BomItemType1 as BomItemType1'
                        +' ,a.Bomqty1 as Bomqty1 '
                        +' ,a.BomScrAp_Percent1 as BomScrAp_Percent1'
                        +' ,a.BomStatus1 as BomStatus1'
                        +' ,a.DeptName1 as DeptName1'
                        +' ,b.ItemCodeName2 as ItemCodeName2'
                        +' ,b.BomItemType2 as BomItemType2'
                        +' ,b.Bomqty2 as Bomqty2'
                        +' ,b.BomScrAp_Percent2 as BomScrAp_Percent2'
                        +' ,b.BomStatus2 as BomStatus2'
                        +' ,b.DeptName2 as DeptName2'
             +' from '
                        +' (select Bom.ItemCode as ItemCode'
                                   +' , Bom.ItemCode+'' ''+Item.ItemName as ItemCodeName1 '
                                   +' ,case Bom.BomItemType'
                                   +'    when 0 then ''普通物料 '''
                                   +'    when 1 then ''工作中心'''
                                   +'    when 2 then ''工具'''
                                   +'    when 3 then ''结构虚项'''
                                   +'    when 4 then ''副产品'''
                                   +'    when 5 then ''成本中心'''
                                   +'    when 6 then ''替代虚项'' end as BomItemType1'
                                   +' ,Bom.BomQty as BomQty1'
                                   +' ,Bom.BomScrAp_Percent as BomScrAp_Percent1'
                                   +' , case Bom.BomStatus'
                                   +'     when 0 then ''正常'''
                                   +'     when 1 then '' 未生效'''
                                   +'     when 2 then ''暂定'''
                                   +'     when 3 then ''暂停使用'' end as BomStatus1 '
                                   +' ,Dept.DeptName as DeptName1 '
                         +' from Bom left join (select ItemCode,ItemName from Item where ItemUsable=1) Item on Bom.ItemCode=Item.ItemCode '
                         +' left join Dept on Bom.DeptCode=Dept.DeptCode'
                         +' where ite_ItemCode='''+ite_ItemCode1+''' '
                         +' and Bom.ItemCode not in (select ItemCode from Item where ItemUsable=0) ) a '
              +' full outer join'
                        +' (select  Bom.ItemCode as ItemCode'
                                   +' , Bom.ItemCode+'' ''+Item.ItemName as ItemCodeName2 '
                                   +' ,case Bom.BomItemType'
                                   +'    when 0 then ''普通物料 '''
                                   +'    when 1 then ''工作中心'''
                                   +'    when 2 then ''工具'''
                                   +'    when 3 then ''结构虚项'''
                                   +'    when 4 then ''副产品'''
                                   +'    when 5 then ''成本中心'''
                                   +'    when 6 then ''替代虚项'' end as BomItemType2'
                                   +' ,Bom.BomQty as BomQty2'
                                   +' ,Bom.BomScrAp_Percent as BomScrAp_Percent2'
                                   +' , case Bom.BomStatus'
                                   +'     when 0 then ''正常'''
                                   +'     when 1 then '' 未生效'''
                                   +'     when 2 then ''暂定'''
                                   +'     when 3 then ''暂停使用'' end as BomStatus2 '
                                   +' ,Dept.DeptName as DeptName2 '
                         +' from Bom left join (select ItemCode,ItemName from Item where ItemUsable=1) Item on Bom.ItemCode=Item.ItemCode '
                         +' left join Dept on Bom.DeptCode=Dept.DeptCode'
                         +' where ite_ItemCode='''+ite_ItemCode2+''' '
                         +' and Bom.ItemCode not in (select ItemCode from Item where ItemUsable=0) ) b '
             +'  on b.ItemCode=a.ItemCode '
             +' Order by a.ItemCode,b.ItemCode');
        open;
        if AdoQry_Tmp.active=False  then AdoQry_Tmp.active:=True;
        AdoQry_Tmp.First;
        colorflag:=0 ;
        while not AdoQry_Tmp.eof do
        begin
          inivar;
          ItemCode1str:=AdoQry_Tmp.fieldbyname('ItemCodeName1').asstring;
          BomItemType1str:=AdoQry_Tmp.fieldbyname('BomItemType1').asstring;
          //Bomqty1str:=formatfloat('0.#####',fieldbyname('BomQty1').asfloat);
          //BomScrAp_Percent1str:=formatfloat('0.#####',fieldbyname('BomScrAp_Percent1').asfloat);
          Bomqty1str:=AdoQry_Tmp.fieldbyname('BomQty1').asstring;
          BomScrAp_Percent1str:=AdoQry_Tmp.fieldbyname('BomScrAp_Percent1').asstring;
          BomStatus1str:=AdoQry_Tmp.fieldbyname('BomStatus1').asstring;
          DeptName1str:=AdoQry_Tmp.fieldbyname('DeptName1').asstring;
          ItemCode2str:=AdoQry_Tmp.fieldbyname('ItemCodeName2').asstring;
          BomItemType2str:=AdoQry_Tmp.fieldbyname('BomItemType2').asstring;
          //Bomqty2str:=formatfloat('0.#####',fieldbyname('BomQty2').asfloat);
          //BomScrAp_Percent2str:=formatfloat('0.#####',fieldbyname('BomScrAp_Percent2').asfloat);
          Bomqty2str:=AdoQry_Tmp.fieldbyname('BomQty2').asstring;
          BomScrAp_Percent2str:=AdoQry_Tmp.fieldbyname('BomScrAp_Percent2').asstring;
          BomStatus2str:=AdoQry_Tmp.fieldbyname('BomStatus2').asstring;
          DeptName2str:=AdoQry_Tmp.fieldbyname('DeptName2').asstring;
          object1:=ItemCode1str+BomItemType1str+Bomqty1str+BomScrAp_Percent1str+BomStatus1str+DeptName1str;
          object2:=ItemCode2str+BomItemType2str+Bomqty2str+BomScrAp_Percent2str+BomStatus2str+DeptName2str;
          if object1=object2 then
          begin
          
          end
          else
          begin
            AdoQry_Main.insert;
            AdoQry_Main.fieldbyname ('ite_ItemCode').asstring:= ite_ItemCode1str;
            AdoQry_Main.fieldbyname ('ItemCodeName').asstring:= ItemCode1str;
            AdoQry_Main.fieldbyname ('BomItemType').asstring:= BomItemType1str;
            AdoQry_Main.fieldbyname ('Bomqty').asstring:= Bomqty1str;
            AdoQry_Main.fieldbyname ('BomScrAp_Percent').asstring:= BomScrAp_Percent1str;
            AdoQry_Main.fieldbyname ('BomStatus').asstring:= BomStatus1str;
            AdoQry_Main.fieldbyname ('DeptCodeName').asstring:= DeptName1str;
            AdoQry_Main.fieldbyname ('colorflag').asstring:= inttostr(colorflag);
            AdoQry_Main.post;
            AdoQry_Main.insert;
            AdoQry_Main.fieldbyname ('ite_ItemCode').asstring:=ite_ItemCode2str;
            AdoQry_Main.fieldbyname ('ItemCodeName').asstring:= ItemCode2str;
            AdoQry_Main.fieldbyname ('BomItemType').asstring:= BomItemType2str;
            AdoQry_Main.fieldbyname ('Bomqty').asstring:= Bomqty2str;
            AdoQry_Main.fieldbyname ('BomScrAp_Percent').asstring:= BomScrAp_Percent2str;
            AdoQry_Main.fieldbyname ('BomStatus').asstring:= BomStatus2str;
            AdoQry_Main.fieldbyname ('DeptCodeName').asstring:= DeptName2str;
            AdoQry_Main.fieldbyname ('colorflag').asstring:= inttostr(colorflag);
            AdoQry_Main.post;
            if  (ItemCode1str=null ) or (ItemCode2str=null )  then
            begin
              AdoQry_Main.insert;
              AdoQry_Main.fieldbyname ('ite_ItemCode').asstring:= '对比是否有差异';
              AdoQry_Main.fieldbyname ('ItemCodeName').asstring:= '';
              AdoQry_Main.fieldbyname ('BomItemType').asstring:= 'Y';
              AdoQry_Main.fieldbyname ('Bomqty').asstring:= 'Y';
              AdoQry_Main.fieldbyname ('BomScrAp_Percent').asstring:='Y';
              AdoQry_Main.fieldbyname ('BomStatus').asstring:='Y';
              AdoQry_Main.fieldbyname ('DeptCodeName').asstring:= 'Y';
              AdoQry_Main.fieldbyname ('colorflag').asstring:=inttostr(colorflag);
              AdoQry_Main.post;
            end
            else
            begin
               if BomItemType1str=BomItemType2str then
                 BomItemType1str:='N'
               else
                 BomItemType1str:='Y';
              if Bomqty1str=Bomqty2str then