rmpurreturn01.pas

在打沙场用的最简单

unit rmpurreturn01;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, DB, DBTables, Grids, DBGridEh, ExtCtrls,
  comobj, Excel2000,Mask, wwdbedit, Wwdotdot, Wwdbcomb, wwdbdatetimepicker;

type
  Trmpurreturn = class(TForm)
    DBGridEh1: TDBGridEh;
    DataSource1: TDataSource;
    Query1: TQuery;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    Panel1: TPanel;
    Query2: TQuery;
    wwDBDateTimePicker1: TwwDBDateTimePicker;
    wwDBComboBox1: TwwDBComboBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Edit1: TEdit;
    Label4: TLabel;
    Edit2: TEdit;
    Label5: TLabel;
    Label6: TLabel;
    Edit3: TEdit;
    Edit4: TEdit;
    Label7: TLabel;
    Label8: TLabel;
    Edit5: TEdit;
    Edit6: TEdit;
    Label9: TLabel;
    Edit7: TEdit;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  rmpurreturn: Trmpurreturn;

implementation

uses main;

{$R *.dfm}

procedure Trmpurreturn.BitBtn1Click(Sender: TObject);
var
yy,mm,dd:word;
i:integer;
begin
 decodedate(wwDBDateTimePicker1.Date,yy,mm,dd);
   with query2 do
   begin
     sql.clear;
     sql.add('update  tpurreturnsub set  syqty=isnull(fqty,0)+isnull(fjqty,0)-isnull(fcqty,0)');
     execsql;
   end;
    with query1 do
    begin
     sql.Clear;
     sql.add('select ck,years,months,fitemid,fcode,fname,zs,color,colorno,gno,pno,funit,ftype, fjqty, fcqty');
     sql.add(',  fqty ,  syqty from tpurreturnsub  where fid=fid');
     if trim(wwDBCombobox1.Text)<>'' then
     begin
     sql.add(' and ck=:ck');
     ParamByName('ck').Value:=wwDBCombobox1.Text;
     end;
     if edit1.text<>'' then
     begin
     sql.add(' and fcode like :fcode');
     ParamByName('fcode').Value:= '%'+edit1.text+'%';
     end;
     if edit3.text<>'' then
     begin
     sql.add(' and fname like :fname');
     ParamByName('fname').Value:='%'+edit3.text+'%';
     end;
     if edit4.text<>'' then
     begin
     sql.add(' and colorno like :colorno');
     ParamByName('colorno').Value:='%'+edit4.text+'%';
     end;
     if edit2.text<>'' then
     begin
     sql.add(' and zs like :zs');
     ParamByName('zs').Value:='%'+edit2.text+'%';
     end;
     if edit5.text<>'' then
     begin
     sql.add(' and gno like :gno');
     ParamByName('gno').Value:='%'+edit5.text+'%';
     end;
     if edit6.text<>'' then
     begin
     sql.add(' and color like :color');
     ParamByName('color').Value:='%'+edit6.text+'%';
     end;
     if edit7.text<>'' then
     begin
     sql.add(' and pno like :pno');
     ParamByName('pno').Value:='%'+edit7.text+'%';
     end;
     if wwDBDateTimePicker1.Text<>'' then
     begin
     sql.add(' and years=:years and months=:months');
     ParamByName('years').Value:=inttostr(yy);
     ParamByName('months').Value:=inttostr(mm);
     end;

     open;
   end;
  for i:=0 to 14 do
  dbgrideh1.Columns[i].Width:=64;
end;

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

procedure Trmpurreturn.FormDestroy(Sender: TObject);
begin
  frmmain.delformname(self.Caption);
end;

procedure Trmpurreturn.BitBtn2Click(Sender: TObject);
var
yy,mm,dd:word;
begin
  decodedate(wwDBDateTimePicker1.Date,yy,mm,dd);
  inherited;
  with   query2 do
  begin
    sql.clear;
    sql.add('select years,months from tpurreturnsub where years=:years and months=:months');
     ParamByName('years').Value:=inttostr(yy);
     ParamByName('months').Value:=inttostr(mm);
    open;
    if recordcount>0 then
    begin
       if messagedlg('本月已盘点，是否要重算? ',mtConfirmation,[mbok,mbcancel],0)=mrok then
       begin
         query2.SQL.Clear;
         query2.SQL.Add('update tpurreturnsub set fqty=isnull(fjqty,0)-isnull(fcqty,0)') ;
         query2.sql.add('where years=:years and months=:months');
         query2.ParamByName('years').Value:=inttostr(yy);
         query2.ParamByName('months').Value:=inttostr(mm);
         query2.ExecSQL;
         messagedlg('盘点更新完毕!请核对',mtConfirmation,[mbok,mbcancel],0);
       end;
    end else
    begin
         query2.SQL.Clear;
         query2.SQL.Add('update tpurreturnsub set fqty=isnull(fjqty,0)-isnull(fcqty,0)') ;
         query2.sql.add('where years=:years and months=:months');
         query2.ParamByName('years').Value:=inttostr(yy);
         query2.ParamByName('months').Value:=inttostr(mm);
         query2.ExecSQL;
         messagedlg('盘点更新完毕!请核对',mtConfirmation,[mbok,mbcancel],0);
    end;

  end;
end;

procedure Trmpurreturn.FormCreate(Sender: TObject);
begin
frmmain.setformname(self.Caption);
wwdbcombobox1.Text:='亿溢';
wwDBDateTimePicker1.Date:=date;
end;

procedure Trmpurreturn.BitBtn3Click(Sender: TObject);
var
  excelid, mybook: variant;
  row, col, i, j, colcount, frow: integer;
  excelflg: boolean;
  rpstr: string;
  per_count,  datasum, dsum1, dsum2: integer;
  datas1, datas2, datas3: string;
begin
 // inherited;

  query1.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 := query1.fieldcount-2;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] :=  '基本报表';
      mybook.worksheets[1].cells.item[row, col].font.size := 18;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
      row := row + 1;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
        datetostr(date);
      row := row + 1;
      frow := row;
      query1.First;
      for i := 1 to colcount do
      begin
        excelid.columns[i].ColumnWidth := dbGrideh1.Columns[i - 1].Width /
          8;
        excelid.columns[i].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, i] := dbgrideh1.Columns[i -
          1].Title.Caption;
        mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
      end;
      row := row + 1;
      while not query1.Eof do
      begin
        for i := 1 to colcount do
          mybook.worksheets[1].cells.item[row, i] := query1.Fields[i -
            1].AsString;
        row := row + 1;
         query1.Next;
      end;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      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(query1.RecordCount) + '条';
      row := row + 1;
      rpstr := '报表条件:';
     { if grid.Cells[0, 0] <> '' then
        for i := 0 to grid.RowCount - 1 do
          for j := 0 to 3 do
            rpstr := rpstr + ' ' + grid.Cells[j, i];    }
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] := rpstr;
      row := row + 1;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[1].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[2].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[3].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[4].LineStyle := xlContinuous;

//    end;

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


end;

end.