fpnojrpt.pas

实用的毛织生产管理系统

unit fpnojrpt;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Spin, ExtCtrls, DB, DBTables, constb, Grids, DBGridEh,
  Buttons,comobj, Excel2000;

type
  Tfpnojrptfm = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    SpinEdit1: TSpinEdit;
    SpinEdit2: TSpinEdit;
    Button2: TButton;
    fpnoqy: TQuery;
    Button1: TButton;
    GroupBox2: TGroupBox;
    DBGridEh1: TDBGridEh;
    fpnodb: TDataSource;
    Button3: TButton;
    BitBtn1: TBitBtn;
    saveqy: TQuery;
    sch2: TQuery;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure DBGridEh1DblClick(Sender: TObject);
    procedure fpnoqyAfterOpen(DataSet: TDataSet);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fpnojrptfm: Tfpnojrptfm;

implementation

uses finishpnomem, fpnojrpt1;

{$R *.dfm}

procedure Tfpnojrptfm.FormCreate(Sender: TObject);
var
  years, months, days: word;
begin
  decodedate(date, years, months, days);
  spinedit1.Value := years;
  spinedit2.Value := months;
end;

procedure Tfpnojrptfm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  action := cafree;
end;

procedure Tfpnojrptfm.FormDestroy(Sender: TObject);
begin
  fpnojrptfm := nil;
end;

procedure Tfpnojrptfm.Button2Click(Sender: TObject);
begin
  if not assigned(fpnojrptfm1) then
    fpnojrptfm1 := tfpnojrptfm1.create(application);

  with fpnojrptfm1 do
  begin
    quickrep1.dataset := fpnoqy;
    qrdbtext1.DataSet := fpnoqy;
    qrdbtext2.DataSet := fpnoqy;
    qrdbtext3.DataSet := fpnoqy;
    qrdbtext4.DataSet := fpnoqy;
    qrdbtext5.DataSet := fpnoqy;
    qrdbtext6.DataSet := fpnoqy;
    qrdbtext7.DataSet := fpnoqy;
    qrdbtext8.DataSet := fpnoqy;
    qrdbtext9.DataSet := fpnoqy;
    qrdbtext10.DataSet := fpnoqy;
    qrdbtext11.DataSet := fpnoqy;
    qrdbtext12.DataSet := fpnoqy;
    qrdbtext13.DataSet := fpnoqy;
    qrdbtext14.DataSet := fpnoqy;
    qrdbtext15.DataSet := fpnoqy;
    qrdbtext16.DataSet := fpnoqy;
    qrdbtext17.DataSet := fpnoqy;
    qrlabel1.Caption := company + '完工情况简表';
    qrlabel2.Caption := '日期' + spinedit1.Text + '年' + spinedit2.Text + '月';
    quickrep1.Preview;
    close;
  

  end;

end;

procedure Tfpnojrptfm.Button1Click(Sender: TObject);
begin
  close;
end;

procedure Tfpnojrptfm.Button3Click(Sender: TObject);
begin
  with fpnoqy do
  begin
      sql.Clear;
      sql.add('select * from scpno where finishflg=:finishflg ');
      sql.add(' and year(finishdate)=:years and month(finishdate)=:months ');
      sql.add(' order by  pno');
      parambyname('finishflg').AsBoolean := true;
      parambyname('years').Asinteger := spinedit1.Value;
      parambyname('months').Asinteger := spinedit2.Value;
      open;
  end;
end;

procedure Tfpnojrptfm.DBGridEh1DblClick(Sender: TObject);
var
  total, avgprice: double;
begin
  if not assigned(finishpnomemfm) then
    finishpnomemfm := tfinishpnomemfm.create(application);
  with finishpnomemfm.pnototalqy do
  begin
    sql.clear;
    sql.Add('select sum(totalin) from scinmx where pno=:pno ');
    parambyname('pno').AsString := fpnoqy.fieldbyname('pno').AsString;
    open;
    total := fields[0].asfloat;
    close;


    if fpnoqy.FieldByName('zxnum').AsInteger > 0 then
      avgprice := total / fpnoqy.FieldByName('zxnum').AsInteger*12
    else
      avgprice := 0;
    close;
  end;
  with finishpnomemfm do
  begin
  epnoqy.RequestLive:=true;
  epnoqy.SQL.clear;
  epnoqy.SQL.Add('select * from scpno where pno=:pno');
  epnoqy.ParamByName('pno').AsString:=fpnoqy.fieldbyname('pno').AsString;
  epnoqy.Open;
    epnoqy.edit;
  epnoqy.FieldByName('total').AsString := floattostr(total);
  epnoqy.FieldByName('avgprice').AsString := formatfloat('0.00', avgprice);
end;

if assigned(finishpnomemfm) then
  finishpnomemfm.ShowModal;
   with fpnoqy do
  begin
      sql.Clear;
      sql.add('select * from scpno where finishflg=:finishflg ');
      sql.add(' and year(finishdate)=:years and month(finishdate)=:months ');
      sql.add(' order by  pno');
      parambyname('finishflg').AsBoolean := true;
      parambyname('years').Asinteger := spinedit1.Value;
      parambyname('months').Asinteger := spinedit2.Value;
      open;
  
  end;
end;

procedure Tfpnojrptfm.fpnoqyAfterOpen(DataSet: TDataSet);
begin
button2.Enabled:=bool(dataset.RecordCount);
end;

procedure Tfpnojrptfm.BitBtn1Click(Sender: TObject);
var
  excelid, mybook: variant;
  row, col, i, j, colcount, frow: integer;
  excelflg: boolean;
  rpstr: string;
  datasum, dsum1, dsum2: integer;
  datas1, datas2, datas3: string;
begin
  {saveqy.SQL.Clear;
  saveqy.SQL.Add(' scdaysave :days ');
  saveqy.ParamByName('days').AsDatetime := datetimepicker1.Date;
  saveqy.ExecSQL;
  saveqy.Close;  }
  with sch2 do
  begin
    sql.Clear;
    sql.add('select pno 批号,kno 款号,pnonum 制单数,kfjz 客户简称,spnum 收片数,zxnum 落货数,outdate 出货日期,total 总金额,avgprice 均打价 from scpno where finishflg=:finishflg');
    sql.add('and year(finishdate)=:years and month(finishdate)=:months');
    sql.add('order by  pno');
    parambyname('finishflg').AsBoolean := true;
    parambyname('years').Asinteger := spinedit1.Value;
    parambyname('months').Asinteger := spinedit2.Value;
    open;
  end;
  sch2.DisableControls;
  try
    excelid := createoleobject('excel.application');
    mybook := createoleobject('excel.sheet');
    mybook := excelid.workbooks.add;
    row := 1;
    col := 1;

    excelflg := true;
  except
    excelflg := false;
    application.MessageBox('请确定EXCEL是否正确安装!', '提示信息', mb_ok);
  end;
  if excelflg then
  begin
      colcount := sch2.fieldcount;

      excelid.ActiveSheet.PageSetup.Orientation := xlPortrait ;
//    excelid.ActiveSheet.PageSetup.Orientation := xlLandscape;

      mybook.worksheets[1].cells.item[row, col] := company + '生产日报表';
      mybook.worksheets[1].cells.item[row, col].font.size := 9;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
      row := row + 1;
      mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
        datetostr(date);
      row := row + 1;
      frow := row;
      sch2.First;
      for i := 1 to colcount do
      begin
        mybook.worksheets[1].cells.item[row,i].font.size := 9;
        excelid.columns[i].ColumnWidth :=12;
        mybook.worksheets[1].cells.item[row, i] :=sch2.Fields[i-1].fieldname;
        mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
      end;
      row := row + 1;
      while not sch2.Eof do
      begin
        for i := 1 to colcount do
         mybook.worksheets[1].cells.item[row,i] := sch2.Fields[i-1].AsString;
         row := row + 1;
         sch2.Next;
      end;

 {   schscdayqy.SQL.clear;
    schscdayqy.SQL.add('select char(null),char(null),char(null),char(null),char(null),sum(fznum) 发织量,sum(sbdaynum) 日收片,sum(sbtolnum) 总收片,char(null),sum(ffnum) 发外数,sum(sfdaynum) 日收外,sum(sftolnum) 总收外,');
    schscdayqy.SQL.add(' sum(bfdaynum) 缝盘,sum(bftolnum) 厂缝,sum(btdaynum) 日挑,sum(bttolnum) 总挑,sum(xsdaynum),sum(xstolnum) 总洗,sum(dcdaynum) 日电,sum(dctolnum) 总电,sum(tsdaynum) 日烫,');
    schscdayqy.SQL.add(' sum(tstolnum) 总烫,sum(zmdaynum) 日车,sum(zmtolnum) 总车,sum(cbdaynum) 日查,sum(cbtolnum) 总查,sum(zxdaynum) 日装,sum(zxtolnum) 总装,sum(pnonum) 制单,sum(subzx) 装差,char(null) from scday a');
    schscdayqy.SQL.add('  inner join  dayscpno b on a.pno=b.pno');
    schscdayqy.Open; }

    {  for i := 1 to colcount do
         mybook.worksheets[1].cells.item[row,i] := schscdayqy.Fields[i-1].AsString; }
     row:=row+1;
      mybook.worksheets[1].cells.item[row, col].font.size := 12;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
      mybook.worksheets[1].cells.item[row, col] := '总记录数:' + inttostr(sch2.RecordCount) + '条';
      row := row + 1;
      rpstr := '报表条件:';

        for i := 0 to sch2.recordcount - 1 do
          for j := 0 to 3 do

    excelid.visible := true;
  end;    
  sch2.EnableControls;
end;

end.