prodanalysisunit.pas

用友系统二次开发 存货管理 客户管理 订货审核 退货审核 铺货计划 调货计划

unit ProdAnalysisUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dxExEdtr, Menus, DB, ADODB, RzPanel, cxDBLabel, cxControls,
  cxContainer, cxEdit, cxLabel, dxDBTLCl, dxGrClms, dxDBCtrl, dxDBGrid,
  RzEdit, StdCtrls, Mask, Buttons, RzLabel, dxTL, dxCntner, ExtCtrls, ComObj,
  DateUtils, RzButton, RzRadChk, cxGraphics, cxRadioGroup, cxCheckBox,
  cxTextEdit, cxMaskEdit, cxDropDownEdit, cxCheckComboBox, cxCheckListBox,
  cxSpinEdit, shellapi;
                    
type
  TfrmProdAnalysis = class(TForm)
    RzPanel1: TRzPanel;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridColumn;
    dxDBGrid1Column2: TdxDBGridColumn;
    RzPanel2: TRzPanel;
    RzLabel4: TRzLabel;
    RzLabel3: TRzLabel;
    RzLabel1: TRzLabel;
    Bt_Query: TBitBtn;
    sDate: TRzDateTimeEdit;
    eDate: TRzDateTimeEdit;
    Inventory: TRzEdit;
    Vendor: TRzEdit;
    RzPanel6: TRzPanel;
    RzPanel3: TRzPanel;
    dxDBGrid2: TdxDBGrid;
    dxDBGrid2Column1: TdxDBGridColumn;
    dxDBGrid2Column2: TdxDBGridColumn;
    dxDBGrid2Column3: TdxDBGridColumn;
    dxDBGrid2Column4: TdxDBGridColumn;
    dxDBGrid2Column5: TdxDBGridMaskColumn;
    dxDBGrid2Column6: TdxDBGridMaskColumn;
    dxDBGrid2Column7: TdxDBGridCurrencyColumn;
    dxDBGrid2Column8: TdxDBGridColumn;
    dxDBGrid2Column9: TdxDBGridColumn;
    dxDBGrid2Column10: TdxDBGridMaskColumn;
    dxDBGrid2Column11: TdxDBGridMaskColumn;
    dxDBGrid2Column12: TdxDBGridMaskColumn;
    dxDBGrid2Column13: TdxDBGridMaskColumn;
    dxDBGrid2Column14: TdxDBGridMaskColumn;
    dxDBGrid2Column15: TdxDBGridMaskColumn;
    dxDBGrid2Column16: TdxDBGridMaskColumn;
    dxDBGrid2Column17: TdxDBGridCurrencyColumn;
    dxDBGrid2Column18: TdxDBGridCurrencyColumn;
    RzPanel4: TRzPanel;
    RzLabel5: TRzLabel;
    RzPanel5: TRzPanel;
    cxLabel5: TcxLabel;
    cxDBLabel8: TcxDBLabel;
    cxLabel3: TcxLabel;
    cxDBLabel3: TcxDBLabel;
    cxLabel11: TcxLabel;
    cxDBLabel6: TcxDBLabel;
    cxLabel8: TcxLabel;
    cxDBLabel11: TcxDBLabel;
    cxDBLabel10: TcxDBLabel;
    cxLabel7: TcxLabel;
    cxDBLabel9: TcxDBLabel;
    cxLabel10: TcxLabel;
    cxDBLabel5: TcxDBLabel;
    cxLabel1: TcxLabel;
    cxDBLabel4: TcxDBLabel;
    cxLabel4: TcxLabel;
    cxLabel2: TcxLabel;
    cxDBLabel2: TcxDBLabel;
    cxLabel9: TcxLabel;
    cxDBLabel7: TcxDBLabel;
    cxLabel6: TcxLabel;
    cxDBLabel1: TcxDBLabel;
    cxDBLabel12: TcxDBLabel;
    cxLabel12: TcxLabel;
    RzStatusBar1: TRzStatusBar;
    DS_ProdAnalysis: TDataSource;
    DS_ProdList: TDataSource;
    ProdAnalysis: TADODataSet;
    ProdList: TADODataSet;
    PopupMenu1: TPopupMenu;
    Export_Excel: TMenuItem;
    Export_Excel_Current: TMenuItem;
    Export_Excel_All: TMenuItem;
    chkCombox_ForbidCust: TcxCheckComboBox;
    cxCheckBox1: TcxCheckBox;
    cxCheckBox2: TcxCheckBox;
    RB_PF: TcxRadioButton;
    RB_LS: TcxRadioButton;
    cxCheckBox3: TcxCheckBox;
    cxSpinEdit1: TcxSpinEdit;
    Bt_LoadAdvice: TBitBtn;
    chkComBox_ReturnCust: TcxCheckComboBox;

    Function  GetFieldsVauleList(returnfield,tremfield:Tfield;tremvaule:string):TStringList;

    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure ExportExcel;
    procedure Export_Excel_CurrentClick(Sender: TObject);
    procedure Export_Excel_AllClick(Sender: TObject);
    procedure Bt_QueryClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Bt_LoadAdviceClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmProdAnalysis: TfrmProdAnalysis;

implementation

{$R *.dfm}

Function TfrmProdAnalysis.GetFieldsVauleList(returnfield,tremfield:Tfield;tremvaule:string):TStringList;
var
    tmpList:TstringList;
begin
    tmpList:=TstringList.Create;
    tmpList.Clear ;
    with tremfield.DataSet do begin
         First;
         while Not tremfield.DataSet.Eof do begin
             if tremfield.AsString = tremvaule then begin
                  tmpList.Add(returnfield.AsString);
             end;
             next ;
         end;
    end;
end;



procedure TfrmProdAnalysis.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
   Action:=caFree;
end;

Procedure TfrmProdAnalysis.Export_Excel_CurrentClick(Sender: TObject);
begin
  Try
      ExportExcel;
      Showmessage('导出成功!');
  except
      Showmessage('导出至 EXCEL 不成功!');
  end;
end;

procedure TfrmProdAnalysis.Export_Excel_AllClick(Sender: TObject);
begin
  IF ProdList.RecordCount>64 Then Begin
      Showmessage('款号不得超过64个!');
      Exit ;
  end ;

  Try
      ProdAnalysis.DataSource := Nil ;
      ExportExcel;
      ProdAnalysis.DataSource := DS_ProdList ;
      ProdList.First ;
      Showmessage('导出成功!');
  except
      Showmessage('导出至 EXCEL 不成功!');
  end;
end;

procedure TfrmProdAnalysis.Bt_QueryClick(Sender: TObject);
var
  ProdGroup:String;
begin
   Try
       if RB_PF.Checked then ProdGroup:= RB_PF.Hint ;
       if RB_LS.Checked then ProdGroup:= RB_LS.Hint ;
       ProdAnalysis.Close ;
       ProdAnalysis.CommandText:= 'ExeCute _PM_P_ProductAnalysis '+ ProdGroup +','''+
                                    FormatDatetime('YYYY-MM-DD',sDate.Date)+''','''+
                                    FormatDatetime('YYYY-MM-DD',eDate.Date)+''','''+
                                    Inventory.Text +''','''+ Vendor.Text +'''' ;
       ProdAnalysis.Open ;

       ProdList.Close ;
       ProdList.CommandText:= 'ExeCute _PM_P_ProductList '+ ProdGroup +','''+
                                    FormatDatetime('YYYY-MM-DD',sDate.Date)+''','''+
                                    FormatDatetime('YYYY-MM-DD',eDate.Date)+''','''+
                                    Inventory.Text +''','''+ Vendor.Text +'''';
       ProdList.Open ;

       Showmessage('查询成功! ')
    except
       Showmessage('查询失败! ')
    end ;


end;

procedure TfrmProdAnalysis.FormCreate(Sender: TObject);
Var
  TempDate: TDate ;
begin
  TempDate:= Now();
  While Not DayOfWeek(TempDate)=2 do
    Begin
      Showmessage(IntTOStr(DayOfWeek(TempDate)));
      TempDate:=TempDate-1;
    End ;
  if MonthOf(TempDate)<3 Then
    begin
      sDate.Date:=Encodedate(YearOf(TempDate)-1,MonthOf(TempDate)+10,DayOf(TempDate))-7;
      eDate.Date:=Encodedate(YearOf(TempDate)-1,MonthOf(TempDate)+10,DayOf(TempDate));
    end
    Else
    begin
      sDate.Date:=Encodedate(YearOf(TempDate),  MonthOf(TempDate) -2,DayOf(TempDate))-7;
      eDate.Date:=Encodedate(YearOf(TempDate),  MonthOf(TempDate) -2,DayOf(TempDate));
    end ;
end;


procedure TfrmProdAnalysis.ExportExcel;
Var
    Excel: OleVariant;
    i,iGs,iGh: Integer ;
    sInvCode,sTemp: String ;
    OldFile : String;
//    NewFile : String;
begin
   IF ProdAnalysis.RecordCount <> 0 then Begin
      try
        Excel := CreateOLEObject('Excel.Application');
      except
        Application.MessageBox('Excel is Not Install! ', 'Wait!', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
        Exit;
      end;
      Excel.Visible := False;
      sTemp:=FormatDateTime('YYYYMMDDHHMMSS',Now());
      OldFile:= GetCurrentDir+'\NPReport.dll';
//      NewFile:= GetCurrentDir+'\新款分析报表'+sTemp+'.XLS';
//      CopyFile(PChar(OldFile),PChar(NewFile));
      Excel.WorkBooks.open(OldFile);

      i:=1;
      iGh:=1;
      iGs:=9;
      ProdAnalysis.First ;
      Excel.WorkSheets[i].name:= ProdAnalysis.FieldValues['货品编码'];
      Excel.WorkSheets[i].Cells[3,3].Value := ProdAnalysis.FieldValues['新款日期'];
      Excel.WorkSheets[i].Cells[3,10].Value := ProdAnalysis.FieldValues['类别'];
      Excel.WorkSheets[i].Cells[4,3].Value := ''''+ProdAnalysis.FieldValues['货品编码'];
      Excel.WorkSheets[i].Cells[4,5].Value := ProdAnalysis.FieldValues['货品名称'];
      Excel.WorkSheets[i].Cells[5,3].Value := ProdAnalysis.FieldValues['成本价'];
      Excel.WorkSheets[i].Cells[5,5].Value := ProdAnalysis.FieldValues['批发价'];
      Excel.WorkSheets[i].Cells[5,10].Value := ProdAnalysis.FieldValues['库存数量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+0].Value := iGs-8;
      Excel.WorkSheets[i].Cells[iGs,iGh+1].Value := ProdAnalysis.FieldValues['客户'];
      Excel.WorkSheets[i].Cells[iGs,iGh+2].Value := ProdAnalysis.FieldValues['首铺日期'];
      Excel.WorkSheets[i].Cells[iGs,iGh+3].Value := ProdAnalysis.FieldValues['首铺数量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+4].Value := ProdAnalysis.FieldValues['补货数量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+5].Value := ProdAnalysis.FieldValues['补次'];
      Excel.WorkSheets[i].Cells[iGs,iGh+6].Value := ProdAnalysis.FieldValues['退货数量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+7].Value := ProdAnalysis.FieldValues['退次品量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+8].Value := ProdAnalysis.FieldValues['净销售量'];
      Excel.WorkSheets[i].Cells[iGs,iGh+9].Value := ProdAnalysis.FieldValues['净销售额'];
      Excel.WorkSheets[i].Cells[iGs,iGh+10].Value := ProdAnalysis.FieldValues['利润'];
      sInvCode:= ProdAnalysis.FieldValues['货品编码'];
      ProdAnalysis.Next;
      while NOT ProdAnalysis.Eof do begin
        If ProdAnalysis.FieldValues['货品编码']=sInvCode then Begin
          Excel.WorkSheets[i].Rows [iGs+1].Insert;
          Excel.WorkSheets[i].Cells[iGs+1,iGh+0].Value := iGs-9;
          Excel.WorkSheets[i].Cells[iGs+1,iGh+1].Value := ProdAnalysis.FieldValues['客户'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+2].Value := ProdAnalysis.FieldValues['首铺日期'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+3].Value := ProdAnalysis.FieldValues['首铺数量'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+4].Value := ProdAnalysis.FieldValues['补货数量'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+5].Value := ProdAnalysis.FieldValues['补次'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+6].Value := ProdAnalysis.FieldValues['退货数量'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+7].Value := ProdAnalysis.FieldValues['退次品量'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+8].Value := ProdAnalysis.FieldValues['净销售量'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+9].Value := ProdAnalysis.FieldValues['净销售额'];
          Excel.WorkSheets[i].Cells[iGs+1,iGh+10].Value := ProdAnalysis.FieldValues['利润'];
          iGs:=iGs+1;
      end Else begin
          i:=i+1;
          Excel.WorkSheets[i].Visible := True;
          iGs:=9;
          Excel.WorkSheets[i].name:= ProdAnalysis.FieldValues['货品编码'];
          Excel.WorkSheets[i].Cells[3,3].Value := ProdAnalysis.FieldValues['新款日期'];
          Excel.WorkSheets[i].Cells[3,10].Value := ProdAnalysis.FieldValues['类别'];
          Excel.WorkSheets[i].Cells[4,3].Value := ''''+ProdAnalysis.FieldValues['货品编码'];
          Excel.WorkSheets[i].Cells[4,5].Value := ProdAnalysis.FieldValues['货品名称'];
          Excel.WorkSheets[i].Cells[5,3].Value := ProdAnalysis.FieldValues['成本价'];
          Excel.WorkSheets[i].Cells[5,5].Value := ProdAnalysis.FieldValues['批发价'];
          Excel.WorkSheets[i].Cells[5,10].Value := ProdAnalysis.FieldValues['库存数量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+0].Value := iGs-8;
          Excel.WorkSheets[i].Cells[iGs,iGh+1].Value := ProdAnalysis.FieldValues['客户'];
          Excel.WorkSheets[i].Cells[iGs,iGh+2].Value := ProdAnalysis.FieldValues['首铺日期'];
          Excel.WorkSheets[i].Cells[iGs,iGh+3].Value := ProdAnalysis.FieldValues['首铺数量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+4].Value := ProdAnalysis.FieldValues['补货数量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+5].Value := ProdAnalysis.FieldValues['补次'];
          Excel.WorkSheets[i].Cells[iGs,iGh+6].Value := ProdAnalysis.FieldValues['退货数量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+7].Value := ProdAnalysis.FieldValues['退次品量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+8].Value := ProdAnalysis.FieldValues['净销售量'];
          Excel.WorkSheets[i].Cells[iGs,iGh+9].Value := ProdAnalysis.FieldValues['净销售额'];
          Excel.WorkSheets[i].Cells[iGs,iGh+10].Value := ProdAnalysis.FieldValues['利润'];
      end;
      sInvCode:= ProdAnalysis.FieldValues['货品编码'];
      ProdAnalysis.Next;
    end;
    Excel.Visible := True;
  end Else begin
    Showmessage('没有资料供可以操作!')
  end;
end;























procedure TfrmProdAnalysis.Bt_LoadAdviceClick(Sender: TObject);
var
  i : integer;
  s : String;
begin
     for i:=0 to GetFieldsVauleList(prodAnalysis.FieldByName('客户'),prodAnalysis.FieldByName('净销售量'),'0').Count-1 do begin;
          s := GetFieldsVauleList(prodAnalysis.FieldByName('客户'),prodAnalysis.FieldByName('净销售量'),'0').Strings[i];
          chkCombox_ForbidCust.Properties.Items.AddCheckItem(s);
     end;

     for i:=0 to GetFieldsVauleList(prodAnalysis.FieldByName('客户'),prodAnalysis.FieldByName('补次'),'0').Count-1 do begin;
          s := GetFieldsVauleList(prodAnalysis.FieldByName('客户'),prodAnalysis.FieldByName('补次'),'0').Strings[i];
          chkCombox_ReturnCust.Properties.Items.AddCheckItem(s);
     end;
end;

end.