pm_qry_analyzeritemvendor.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerItemVendor;

Interface

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

Type
  TFrm_Pm_Qry_AnalyzerItemVendor = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MaInvendorName: TStringField;
    AdoQry_MainCountBillqty: TBCDField;
    AdoQry_MainCountBillAmount: TBCDField;
    AdoQry_MainavgBillPrice: TBCDField;
    AdoQry_MainPricesec: TBCDField;
    AdoQry_Mainsecrate: TStringField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MaInvendorflag: TStringField;
    Label2: TLabel;
    dbtxtVendorflag: TDBText;
    dbtxtItemflag: TDBText;
    Label1: TLabel;
    AdoQry_Mainflag: TIntegerField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Pm_Qry_AnalyzerItemVendor: TFrm_Pm_Qry_AnalyzerItemVendor;

implementation
uses  Pm_Qry_AnalyzerItemVendor_C;

{$R *.DFM}

{ TFrm_Pm_Qry_AnalyzerItemVendor }

procedure TFrm_Pm_Qry_AnalyzerItemVendor.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4:string; date1,date2:string;
    tmpItemCode:string;
    tmpflag:integer;
begin
  inherited;
Pricefields:='avgBillPrice,Pricesec,';
tmpItemCode:='tmpItemCode';
tmpflag:=0;
try
  with TFrm_Pm_Qry_AnalyzerItemVendor_C.Create(self) do
    begin
      showmodal;
      if modalResult<>mrok then exit;
      date1:=medts.Text;
      date2:=medte.Text;
    end;
finally
  Frm_Pm_Qry_AnalyzerItemVendor_C.free;
end;
  lbl_Condition.Caption:='统计日期从'+date1+'到'+date2;
 sqltext1:='select t1.InvBillid,    '
          +'        t1.InvBilldate,  '
          +'        t1.VendorCode,   '
          +'        t2.ItemCode,     '
          +'        t2.InvBillqty,   '
          +'        t2.InvBillAmount '
          +' into #tmpInvBill        '
         +' from InvInBill t1(nolock)'
        +' join  InvInBillline t2(nolock) on t1.InvBillid=t2.InvBillid  '
        +'   and t2.ItemCode<>''9999999999'' '
        +' where t1.InvBillStkchck=1                                    '
        +'   and t1.realBillflag=1 '
        +#13+' and  t1.InvBilldate between '''+date1+''''+' and '''+date2+' 23:59:59'+''''
        +' select ItemCode,               '
            +'       VendorCode,               '
            +'       sum(InvBillqty) as CountBillqty,  '
            +'       sum(InvBillAmount) as CountBillAmount, '
            +'       case sum(InvBillqty)  '
            +'         when 0 then 0   '
           +'          else   convert(decimal(14,2),sum(InvBillAmount)/sum(InvBillqty)) '
            +'       end as avgBillPrice  '
           +' into #tmpCount                      '
           +' from   #tmpInvBill                  '
           +' group by ItemCode,VendorCode        ';

 sqltext2:=' select t1.ItemCode,                                             '
          +'        t2.ItemName,                                             '
          +'        Itemflag=t1.ItemCode+'' ''+t2.ItemName,'
          +'        Vendorflag=t1.VendorCode+'' ''+t3.VendorName,'
          +'        t1.VendorCode,                                           '
          +'        t3.VendorName,                                           '
          +'        t1.CountBillqty,                                         '
          +'        t1.CountBillAmount,                                      '
          +'        t1.avgBillPrice,                                         '
          +'        convert(float,0) as Pricesec,                            '
          +'        ''0%        '' as secrate,                              '
          +'        0  as flag'
          +'  into #tmpResult                                                '
       +'  from  #tmpCount t1                                               '
       +'  left outer join Item t2(nolock) on t1.ItemCode=t2.ItemCode        '
       +'  left outer join Vendor t3(nolock) on t1.VendorCode=t3.VendorCode  '
       +'  Order by t1.ItemCode,t1.avgBillPrice    ';

  sqltext3:='select top 0 ItemCode,           '
           +'              ItemName,            '
           +'              Itemflag,           '
           +'               Vendorflag,         '
           +'               VendorCode,        '
           +'              VendorName,         '
           +'              CountBillqty,       '
           +'              CountBillAmount,    '
           +'              avgBillPrice,       '
           +'              Pricesec,           '
           +'              secrate,             '
           +'              flag  '
           +'    into #tmpres                 '
           +'  from #tmpResult                '
            +'  declAre @Count int,            '
            +'          @ItemCode varchAr(16), '
            +'          @tmpCode  varchAr(16),'
            +'          @tmpflag  int,         '
            +'          @Price    float        '
            +'  set rowCount 1                 '
            +'  insert into #tmpres            '
            +'  select * from #tmpResult       '
           // +'  update #tmpres '
           // +'     set flag=1  '
           // +'  from #tmpres  '
            +'  select @ItemCode=(select ItemCode from #tmpres)    '
            +'  select @Price=(select avgBillPrice from #tmpres)    '
            +'  select @tmpflag=(select flag from #tmpres)          '
            +'  delete from #tmpResult                              '
            +'  select @Count=(select Count(*) from #tmpResult)     '
            +'  select @Count                                       '
            +'  while @Count>0                                      '
            +'  begin                                               '
            +'    select @tmpCode=ItemCode from #tmpResult       '
            +'   if @tmpCode=@ItemCode  '
            +'     begin                                            '
            +'       update #tmpResult                              '
            +'          set Pricesec=avgBillPrice-@Price,           '
            +'              flag=@tmpflag,                          '
            +'               secrate= case @Price     '
            +'                        when 0 then null              '
            +'                           else convert(varchAr,convert(decimal(14,0),(avgBillPrice-@Price)*100/@Price))+''%''   '
            +'                         end '
            +'       insert into #tmpres                            '
            +'       select * from #tmpResult                       '
            +'    end                                                '
            +'   else                                                '
            +'     begin                                             '
            +'         select @tmpflag=@tmpflag+1     '
            +'          update #tmpResult '
            +'          set    flag=@tmpflag '
           // +'           from #tmpResult '
            +'          insert into #tmpres                          '
            +'          select * from #tmpResult                     '
            +'          select @ItemCode=ItemCode from #tmpResult    '
            +'          select @Price=avgBillPrice from #tmpResult   '
            +'     end                                               '
            +'   delete from #tmpResult                              '
            +'   select @Count=@Count-1                              '
            +' end                                                   '
            +' set rowCount 999999999                               ';

 sqltext4:='select ItemCode,VendorCount=Count(VendorCode) into #tmpItemVendor from #tmpres group by ItemCode';

 selectfromsql:='select * from #tmpres';
 with  AdoQry_Main do
   begin
     Close;
     sql.clear;
     sql.Add(sqltext1);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext2);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext3);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext4);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add('select * from #tmpres where ItemCode in(select ItemCode from #tmpItemVendor where VendorCount>1)');
     Prepared;
     try
     open;
     First;
     while not eof do
       begin
         if tmpItemCode<>fieldbyname('ItemCode').asstring then
            begin
              edit;
              fieldbyname('flag').asinteger:=tmpflag+1;
              post;
              tmpflag:=tmpflag+1;
              tmpItemCode:=fieldbyname('ItemCode').asstring;
            end
         else
            begin
              edit;
              fieldbyname('flag').asinteger:=tmpflag;
              post;
            end;
         next;
       end;
     First;
     except
     end;


   end;


end;

procedure TFrm_Pm_Qry_AnalyzerItemVendor.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Pm_Qry_AnalyzerItemVendor:=nil;
end;

procedure TFrm_Pm_Qry_AnalyzerItemVendor.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('drop table #tmpInvBill,#tmpItemVendor,#tmpCount,#tmpResult,#tmpres');
    try
    execsql;
    except
    end;
  end;
end;

procedure TFrm_Pm_Qry_AnalyzerItemVendor.DBGridEhGetCellParams(
  Sender: TObject; Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
var ItemCode,VendorCode:string;
begin
  inherited;
with AdoQry_Main do
  begin
    ItemCode:=fieldbyname('ItemCode').asstring;
    VendorCode:=fieldbyname('VendorCode').asstring;
   //if (fieldbyname('Pricesec').asfloat=0) and (Trim(fieldbyname('secrate').asstring)='0%')  then
     if  fieldbyname('flag').asinteger mod 2=0 then
        begin
        Background:=$ffccee;
       afont.Color:=clblack;
       end;
  end;

end;

end.