upurreturn.~pas

在打沙场用的最简单

     sql.add(' and ck=:ck');
     Parameters.ParamByName('ck').Value:=wwDBLookupCombo1.Text;
     end;
     if edit1.text<>'' then
     begin
     sql.add(' and fcode like :fcode');
     Parameters.ParamByName('fcode').Value:= '%'+edit1.text+'%';
     end;
     if combobox1.text<>'' then
     begin
     sql.add(' and fcode like :fname');
     Parameters.ParamByName('fname').Value:='%'+combobox1.text+'%';
     end;
     if edit3.text<>'' then
     begin
     sql.add(' and colorno like :colorno');
     Parameters.ParamByName('colorno').Value:='%'+edit3.text+'%';
     end;
     if edit4.text<>'' then
     begin
     sql.add(' and zs like :zs');
     Parameters.ParamByName('zs').Value:='%'+edit4.text+'%';
     end;
     if edit5.text<>'' then
     begin
     sql.add(' and gno like :gno');
     Parameters.ParamByName('gno').Value:='%'+edit5.text+'%';
     end;
     if combobox2.text<>'' then
     begin
     sql.add(' and ftype like :color');
     Parameters.ParamByName('color').Value:='%'+combobox2.text+'%';
     end;
     if edit6.text<>'' then
     begin
     sql.add(' and pno= :pno');
     Parameters.ParamByName('pno').Value:=edit6.text;
     end;
     if wwDBDateTimePicker2.Text<>'' then
     begin
     sql.add(' and years=:years and months=:months');
     Parameters.ParamByName('years').Value:=inttostr(yy);
     Parameters.ParamByName('months').Value:=inttostr(mm);
     end;
     Open;
    end;  

end;

procedure Tfrmpurreturn.p05Execute(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;
  adoquery1.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 :=  adoquery1.fieldcount;
      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;
      adoquery1.First;
      for i := 1 to colcount do
      begin
        excelid.columns[i].ColumnWidth := Grid.Columns[i - 1].Width /
          8;
        excelid.columns[i].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, i] := grid.Columns[i -
          1].Title.Caption;
        mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
      end;
      row := row + 1;
      while not adoquery1.Eof do
      begin
        for i := 1 to colcount do
          mybook.worksheets[1].cells.item[row, i] :=  adoquery1.Fields[i -1].AsString;
        row := row + 1;
        adoquery1.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(adoquery1.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;
  adoquery1.EnableControls;
  
end;

procedure Tfrmpurreturn.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  con1.Close;
  adoquery1.Close;
  adoquery2.Close;
  adoquery3.Close;
end;

procedure Tfrmpurreturn.BitBtn2Click(Sender: TObject);
var
yy,mm,dd:word;
begin
  decodedate(wwDBDateTimePicker2.Date,yy,mm,dd);

  inherited;
  with   adoquery2 do
  begin
    sql.clear;
    sql.add('delete  from tpurreturnsub where years=:years and months=:months');
    Parameters.ParamByName('years').Value:=inttostr(yy);
    Parameters.ParamByName('months').Value:=inttostr(mm+1);
    execsql;
    sql.clear;
    sql.Clear;
    sql.add(' insert into tpurreturnsub(fcode,fname,funit,ftype,fqty,years,months)');
    sql.add(' select fcode,fname,funit,ftype,pdqty,:years1,:months1 from  tpurreturnsub ');
    sql.add(' where years=:years2 and months=:months2');
    Parameters.ParamByName('years1').Value:=inttostr(yy);
    Parameters.ParamByName('months1').Value:=inttostr(mm+1);
    Parameters.ParamByName('years2').Value:=inttostr(yy);
    Parameters.ParamByName('months2').Value:=inttostr(mm);
    execsql;
    messagedlg('盘点更新完毕!请核对',mtConfirmation,[mbok,mbcancel],0);
   end;
end;

procedure Tfrmpurreturn.p01Execute(Sender: TObject);
begin
{ inherited;
  frDBDataSet1.DataSet:= datam.Query1 ;
  datam.Query1.DisableControls;
  frMD.LoadFromFile(frname);
  frMD.ShowReport;
  datam.Query1.EnableControls;  }
end;

procedure Tfrmpurreturn.acpriorExecute(Sender: TObject);
begin
//  inherited;
     cdssub.DisableControls;
    CDSsub.First;
     while  not CDSsub.Eof do
     begin
      GetsqlData(CDSselectsub,'vitem','fcode','fcode='+vartosql(cdssub['fcode']),1);
      if CDSselectsub.RecordCount > 0 then
       begin
       cdssub.Edit;
       CDSsub['fname']:=CDSselectsub['fname'];
       CDSsub['ftype'] :=CDSselectsub['ftype'];
       CDSsub['funit']:=CDSselectsub['funit'];
       CDSsub['zs']:=CDSselectsub['zs'];
       end;
      GetsqlData(CDSselectsub,'vstoresub','colorno','colorno='+vartosql(cdssub['colorno']),1);
      if CDSselectsub.RecordCount > 0 then
       cdssub.Edit;
       CDSsub['color']:=CDSselectsub['color'];
       CDSsub.Next;
    end;
   cdssub.EnableControls;
end;

procedure Tfrmpurreturn.GridColExit(Sender: TObject);
begin
  if  adoquery1.State in [dsEdit,dsinsert] then
  begin
   with  adoquery2 do
     begin
     SQL.Clear;
     sql.add(' select  fcode,fname,funit,ftype ');
     sql.add('  from  titem ');
     sql.add(' where  fcode=:fcode ');
     Parameters.ParamByName('fcode').value:=adoquery1.fieldbyname('fcode').Value;
     Open;
     if recordcount>0 then
     begin
      adoquery1.fieldbyname('fname').value:=adoquery1.fieldbyname('fname').asstring;
      adoquery1.fieldbyname('funit').value:=adoquery1.fieldbyname('funit').asstring;
      adoquery1.fieldbyname('ftype').value:=adoquery1.fieldbyname('ftype').asstring;
     end;
   end;
end;
end; 

procedure Tfrmpurreturn.GridTitleBtnClick(Sender: TObject; ACol: Integer;
  Column: TColumnEh);
var

sortstring:string; //排序列

begin

//进行排序

with Column do

begin

if FieldName = '' then

Exit;


case Title.SortMarker of

smNoneEh:

begin

Title.SortMarker := smDownEh;

sortstring := Column.FieldName + ' ASC';

end;

smDownEh: sortstring := Column.FieldName + ' ASC';

smUpEh: sortstring := Column.FieldName + ' DESC';

end;

//进行排序

try

adoquery1.Sort := sortstring //dataset为实际数据集变量名

except

end;

end;

end;

procedure Tfrmpurreturn.acnextExecute(Sender: TObject);
var
yy,mm,dd:word;
str1:string;
begin
    decodedate(wwDBDateTimePicker2.Date,yy,mm,dd);
     with adoQuery2 do
     begin
       sql.clear;
       sql.add('update  tpurreturnsub set  pdqty=syqty,kcqty=isnull(syqty,0)-isnull(pdqty,0)');
       sql.add(' where years=:years and months=:months');
       Parameters.ParamByName('years').Value:=inttostr(yy);
       Parameters.ParamByName('months').Value:=inttostr(mm);
       execsql;
     end;
    spbtnprint.Click;     

end;

procedure Tfrmpurreturn.acdeleteExecute(Sender: TObject);
begin
//  inherited;
    with adoquery3 do
   begin
     sql.clear;
     sql.add('delete from tpurreturnsub where fid=:fid');
     Parameters.ParamByName('fid').Value:=adoquery1.fieldbyname('fid').asinteger;
     execsql;
     showmessage('删除成功');
   end;
      spbtnprint.Click;  
end;

end.