ufz.pas

在打沙场用的最简单

           if wwdbedit20.Text='' then
           CDSsub[sw]:=0 else
           CDSsub[sw]:=wwdbedit20.Text;
         end;
         CDSsub['qtyunit']:=16;
         CDSsub['per1']:=0;
        //  CDSsub['fprice'] :=    adoquery1.fieldbyname('fprice').Value;
         // CDSsub['total'] :=adoquery1.fieldbyname('fqty').Value*adoquery1.fieldbyname('fprice').Value;
        end;

    adoquery1.Next;
 end;
    if adoquery1.RecordCount>0 then
     BEGIN
    GetsqlData(CDSselectsub,'Vstore','frefno','frefno='+vartosql(adoquery1.fieldbyname('frefno').asstring),1);
    if cdsselectsub.RecordCount>0 then
    begin
    if  CDSselectsub['fpurstore']<>NULL then
    CDSmaster['fpurstore']:=CDSselectsub['fpurstore'];//searq.fieldbyname('fpurstore').asstring;//'仓库';
    if CDSselectsub['fpurer']<>NULL then
    CDSmaster['fpurer']:=CDSselectsub['fpurer'];//searq.fieldbyname('fpurer').asstring;//'负责人';
    if  CDSselectsub['fprovider']<>null then
    CDSmaster['fprovider']:=CDSselectsub['fprovider'];//searq.fieldbyname('fprovider').asstring;//'供应商';
    if  CDSselectsub['fnote']<>null then
    CDSmaster['fnote']:=CDSselectsub['fnote'];//searq.fieldbyname('fnote').asstring;//'备注';
    if CDSselectsub['fdelivdate']<>null then
    CDSmaster['fdelivdate']:=CDSselectsub['fdelivdate'];//searq.fieldbyname('fdelivdate').asstring;//'交货日期';
    if  CDSselectsub['fcreauser']<>null then
    CDSmaster['fcreauser']:=CDSselectsub['fcreauser'];//searq.fieldbyname('fcreauser').asstring;//'创建人';
    if CDSselectsub['fcreadate'] <>null then
    CDSmaster['fcreadate']:=CDSselectsub['fcreadate'];//searq.fieldbyname('fcreadate').asstring;//'订货日期';
    if CDSselectsub['frefno'] <>null then
    CDSmaster['frefno']:=CDSselectsub['frefno'];//searq.fieldbyname('fcreadate').asstring;//'订货日期';
    end;
     END;
 //  Grid.SelectedIndex:=7;
 end;
end;

procedure Tffz.BitBtn1Click(Sender: TObject);
var
  s,str1,str2,str3,str4:string;
i,  ii,jj:integer;
  ini:textfile;
 Searcht, usercode:string;
begin
  inherited;
    s:=OpenDialog1.FileName;
    if not FileExists(s) then    //判断此文件是否存在
    showmessage('该文件不存在')
    else
    begin
    AssignFile(ini,s);
    reset(ini);
    CDSmaster.Append;
   // i:=1;
    while not eof(ini) do
    begin
    Readln(ini,usercode);
    ii:=length(usercode);
    jj:=Pos(',',usercode);
    str1:=copy(usercode,1,jj-1);
    ///////////////////   str1
    usercode:= copy(usercode,jj+1,ii-jj);
    ii:=length(usercode);
    jj:=Pos(',',usercode) ;
    str2:=copy(usercode,1,jj-1);
    //////////////////////    str2

    usercode:= copy(usercode,jj+1,ii-jj);
    ii:=length(usercode);
    jj:=Pos(',',usercode) ;
    str3:=copy(usercode,1,jj-1);
    /////////////////////////////str3
    str4:= copy(usercode,jj+1,ii-jj);
    ///////////////////////////////////str4
    spbtnadd.Click;
    CdsSub.Append;
    GetsqlData(CDSselectsub,'Vitem','fid','fcode='+vartosql(str1),1);
    if CDSselectsub.RecordCount > 0 then
     begin
     //  CDSsub['fid']:=i;
       CDSsub['fitemid']:=CDSselectsub['fid'] ;
       CDSsub['fname']:=CDSselectsub['fname'];       
       CDSsub['fcode']:=str1;
       CDSsub['fqty'] := str2;       
       CDSsub['sdate']:=str3;
       CDSsub['stime']:=str4;

//       CDSsub['total'] :=strtofloat( str2)*CDSsub['fqty'];
     end;
       Grid.SelectedIndex:=7;
     //i:=i+1;
 // CdsSub.FieldByName('fcode').AsString:=str1;
//  CdsSub.FieldByName('fqty').AsString:=str2;
 end;
     CloseFile(ini);
end;

end;

procedure Tffz.acsaveExecute(Sender: TObject);
var
ini:textfile;
s,  cmdStr:string;
yy,mm,dd:word;
begin
  decodedate(wwDBDateTimePicker2.Date,yy,mm,dd);
   cdssub.DisableControls;
   CDSsub.First;
   while not CDSsub.eof do
   begin

/////////////////////////////////////////以下是更新库存
     with query2 do
     begin
     SQL.Clear;
     SQL.Add('select ck from tpurreturnsub   where  colorno =:colorno and gno=:gno ');
     sql.add(' and   fcode=:fcode  and ck=:ck and years=:years and months=:months');
     ParamByName('colorno').asstring:=cdssub.fieldbyname('colorno').asstring;
     ParamByName('gno').asstring:=cdssub.fieldbyname('gno').asstring;
     ParamByName('fcode').asstring:=cdssub.fieldbyname('fcode').asstring;
     ParamByName('ck').asstring:=wwDBLookupCombo1.Text;
     ParamByName('years').asinteger:=yy;
     ParamByName('months').asinteger:=mm;
     Open;
     end;

     if query2.RecordCount>0 then
     begin
     with query1 do
     begin
      if editstate=1 then
      begin
      SQL.Clear;
      SQL.Add('update  tpurreturnsub set fcqty=fcqty+:qty where  colorno =:colorno and gno=:gno ');
      sql.add(' and   fcode=:fcode   and ck=:ck and years=:years and months=:months');
      ParamByName('colorno').asstring:=cdssub.fieldbyname('colorno').asstring;
      ParamByName('gno').asstring:=cdssub.fieldbyname('gno').asstring;
      ParamByName('fcode').asstring:=cdssub.fieldbyname('fcode').asstring;
      ParamByName('qty').asfloat:=cdssub.fieldbyname('fqty').asfloat;      
      ParamByName('ck').asstring:=wwDBLookupCombo1.Text;
      ParamByName('years').asinteger:=yy;
      ParamByName('months').asinteger:=mm;
      execsql;
      end;
      if editstate=2 then
      begin
      SQL.Clear;
      SQL.Add('update  tpurreturnsub set fcqty=fcqty+:qty where  colorno =:colorno and gno=:gno ');
      sql.add(' and  fcode=:fcode  and ck=:ck and years=:years and months=:months');
      ParamByName('colorno').asstring:=cdssub.fieldbyname('colorno').asstring;
      ParamByName('gno').asstring:=cdssub.fieldbyname('gno').asstring;
      ParamByName('fcode').asstring:=cdssub.fieldbyname('fcode').asstring;
      ParamByName('qty').asfloat:=cdssub.fieldbyname('fqty').asfloat-cdssub.fieldbyname('oldfqty').asfloat;
      ParamByName('ck').asstring:=wwDBLookupCombo1.Text;
      ParamByName('years').asinteger:=yy;
      ParamByName('months').asinteger:=mm;
      execsql;

      end;
     end;
     end else
     begin
     with  query1 do
     begin
      sql.clear;
      sql.Add('insert into tpurreturnsub(funit,ftype,fresid,color,zs,fcode,fname,fcqty,colorno,gno,ck,years,months,pno ) values (:funit,:ftype,1,:color,:zs,:fcode,:fname,:fcqty,:colorno,:gno,:ck,:years,:months,:pno)');
      ParamByName('funit').asstring:=cdssub.fieldbyname('funit').asstring;
      ParamByName('ftype').asstring:=cdssub.fieldbyname('ftype').asstring;      
      ParamByName('colorno').asstring:=cdssub.fieldbyname('colorno').asstring;
      ParamByName('gno').asstring:=cdssub.fieldbyname('gno').asstring;
      ParamByName('fcqty').asfloat:=cdssub.fieldbyname('fqty').asfloat;
      ParamByName('color').asstring:=cdssub.fieldbyname('color').asstring;
      ParamByName('zs').asstring:=cdssub.fieldbyname('zs').asstring;
      ParamByName('fcode').asstring:=cdssub.fieldbyname('fcode').asstring;
      ParamByName('fname').asstring:=cdssub.fieldbyname('fname').asstring;
      ParamByName('ck').asstring:=wwDBLookupCombo1.Text;
      ParamByName('pno').asstring:=billno.Text;      
      ParamByName('years').asinteger:=yy;
      ParamByName('months').asinteger:=mm;
      execsql;
     end;
     end;
     cdssub.next;
   end;
  cdssub.EnableControls;
  inherited;
  cmdStr:='Insert into tptotal(fsid) values('+vartosql(inNumber)+')';
  Execsql(cmdStr);
end;

procedure Tffz.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;
  CDSsub.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 := 5;
      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;
      CDSsub.First;

        excelid.columns[1].ColumnWidth := Grid.Columns[0].Width / 8;
        excelid.columns[1].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, 1] := '序号';

        excelid.columns[2].ColumnWidth := Grid.Columns[1].Width / 8;
        excelid.columns[2].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, 2] := '产品代码';

        excelid.columns[3].ColumnWidth := Grid.Columns[2].Width / 8;
        excelid.columns[3].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, 3] := '数量';

        excelid.columns[4].ColumnWidth := Grid.Columns[3].Width / 8;
        excelid.columns[4].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, 4] := '年/月/日';

        excelid.columns[5].ColumnWidth := Grid.Columns[4].Width / 8;
        excelid.columns[5].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, 5] := '时/分/秒';

      row := row + 1;
      i:=1;
      while not CDSsub.Eof do
      begin

          mybook.worksheets[1].cells.item[row, 1] :=i;
          mybook.worksheets[1].cells.item[row, 2] := CDSsub['fcode'];
          mybook.worksheets[1].cells.item[row, 3] := CDSsub['fqty'];
          mybook.worksheets[1].cells.item[row, 4] := CDSsub['sdate'];
          mybook.worksheets[1].cells.item[row, 5] := CDSsub['stime'];
          i:=i+1 ;  

          row := row + 1;
          CDSsub.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(CDssub.RecordCount) + '条';
      row := row + 1;
      rpstr := '报表条件:';
      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;
    excelid.visible := true;
  end;
  CDSsub.EnableControls;  
end;

procedure Tffz.BitBtn2Click(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
  with adoquery3 do
  begin
   sql.clear;
   sql.add('  select fname,fcode,sum(fqty) fqty from Tpurchasesub a,titem b,tpurchase c');
   sql.add('  where a.fitemid=b.fid and a.fresid=c.fresid');
   if wwDBDateTimePicker2.Text<>'' then