ufzpayment.~pas

在打沙场用的最简单

  inherited;
 with adoquery1 do
 begin
  sql.Clear;
  sql.Add('select * from jsb where years=:years and months=:months');
  Parameters.ParamByName('years').Value:=pbspinedit1.Text;
  Parameters.ParamByName('months').Value:=pbspinedit2.Text;
  open;
 end;
 if adoquery1.RecordCount>0 then
 begin
   if application.MessageBox('本月已有资料，是否重算','系统提示',MB_YESNO)=IDYES then
   begin
    adoquery1.sql.Clear;
    adoquery1.sql.Add('delete from jsb where years=:years and months=:months');
    adoquery1.Parameters.ParamByName('years').Value:=pbspinedit1.Text;
    adoquery1.Parameters.ParamByName('months').Value:=pbspinedit2.Text;
    adoquery1.ExecSQL;
    cojsb;

   end;
 end else
  cojsb;

end;

procedure Tfzpayment.cojsb;
begin
  adoquery1.sql.Clear;
  adoquery1.sql.Add('insert into jsb(yj,years,months,company,pno3,gx,fzqty,fzweight ) ');
  adoquery1.sql.Add('  select 0,:yy1,:mm1,fprovider,pno,fgx,sum(tfzsub.total) as total,sum(tfzsub.fqty) as fqty from tfz,tfzsub  ');
  adoquery1.SQL.add(' where  year(fcreadate)=:yy and month(fcreadate)=:mm and  tfz.fresid=tfzsub.fresid ');
  adoquery1.SQL.Add(' group by fprovider,pno,fgx');
  adoquery1.Parameters.ParamByName('yy').Value:=pbspinedit1.Text;
  adoquery1.Parameters.ParamByName('mm').Value:=pbspinedit2.Text;
  adoquery1.Parameters.ParamByName('yy1').Value:=pbspinedit1.Text;
  adoquery1.Parameters.ParamByName('mm1').Value:=pbspinedit2.Text;
  adoquery1.ExecSQL;

  with  adoquery1 do   /////////////////////以上是发制统计，以下是收片统计
  begin
  sql.clear;
  sql.Add('  select fprovider,pno,fgx,sum(tspsub.total) as total,sum(tspsub.fqty) as fqty from tsp,tspsub  ');
  sql.Add(' where  year(fdelivdate)=:yy and month(fdelivdate)=:mm and  tsp.fresid=tspsub.fresid ');
  SQL.Add(' group by fprovider,pno,fgx');
  adoquery1.Parameters.ParamByName('yy').Value:=pbspinedit1.Text;
  adoquery1.Parameters.ParamByName('mm').Value:=pbspinedit2.Text;
//  adoquery1.Parameters.ParamByName('yy1').Value:=pbspinedit1.Text;
//  adoquery1.Parameters.ParamByName('mm1').Value:=pbspinedit2.Text;
  open;
  end;
  while not  adoquery1.Eof do
  begin
    adoquery2.sql.Clear;                //////////////
    adoquery2.sql.Add('select * from jsb where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
    adoquery2.Parameters.ParamByName('years').Value:=pbspinedit1.Text;
    adoquery2.Parameters.ParamByName('months').Value:=pbspinedit2.Text;
    adoquery2.Parameters.ParamByName('company').Value:=adoquery1.fieldbyname('fprovider').AsString;
    adoquery2.Parameters.ParamByName('pno').Value:=adoquery1.fieldbyname('pno').AsString;
    adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
    adoquery2.open;
    if adoquery2.RecordCount>0 then
    begin
     adoquery2.sql.Clear;
     adoquery2.sql.Add('update jsb  set spqty=:spqty,spweight=:spweight where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
     adoquery2.Parameters.ParamByName('years').Value   :=pbspinedit1.Text;
     adoquery2.Parameters.ParamByName('months').Value  :=pbspinedit2.Text;
     adoquery2.Parameters.ParamByName('spqty').Value   :=adoquery1.fieldbyname('total').Asfloat;
     adoquery2.Parameters.ParamByName('spweight').Value:=adoquery1.fieldbyname('fqty').Asfloat;
     adoquery2.Parameters.ParamByName('company').Value :=adoquery1.fieldbyname('fprovider').AsString;
     adoquery2.Parameters.ParamByName('pno').Value:=adoquery1.fieldbyname('pno').AsString;
     adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
     adoquery2.ExecSQL;
    end else
    begin
     adoquery2.sql.clear;
     adoquery2.sql.Add('insert into jsb(yj,years,months,company,pno3,gx,spqty,spweight ) ');
     adoquery2.SQL.Add(' values(0,:years,:months,:company,:pno3,:gx,:spqty,:spweight  )' );
     adoquery2.Parameters.ParamByName('years').Value   :=pbspinedit1.Text;
     adoquery2.Parameters.ParamByName('months').Value  :=pbspinedit2.Text;
     adoquery2.Parameters.ParamByName('pno3').Value   :=adoquery1.fieldbyname('pno').AsString;
     adoquery2.Parameters.ParamByName('spqty').Value   :=adoquery1.fieldbyname('total').Asfloat;
     adoquery2.Parameters.ParamByName('spweight').Value:=adoquery1.fieldbyname('fqty').Asfloat;
     adoquery2.Parameters.ParamByName('company').Value :=adoquery1.fieldbyname('fprovider').AsString;
     adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
     adoquery2.ExecSQL;
    end;
  adoquery1.Next;
  end;   /////////////收片统计完毕
  with  adoquery1 do   /////////////////////以下是回毛统计
  begin
  sql.clear;
  sql.Add('  select fprovider,pno,fgx,sum(thmsub.fqty) as fqty from thm,thmsub  ');
  sql.Add(' where  year(fdelivdate)=:yy and month(fdelivdate)=:mm and  thm.fresid=thmsub.fresid ');
  SQL.Add(' group by fprovider,pno,fgx');
  adoquery1.Parameters.ParamByName('yy').Value:=pbspinedit1.Text;
  adoquery1.Parameters.ParamByName('mm').Value:=pbspinedit2.Text;
//  adoquery1.Parameters.ParamByName('yy1').Value:=pbspinedit1.Text;
//  adoquery1.Parameters.ParamByName('mm1').Value:=pbspinedit2.Text;
  open;
  end;
  while not  adoquery1.Eof do
  begin
    adoquery2.sql.Clear;
    adoquery2.sql.Add('select * from jsb where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
    adoquery2.Parameters.ParamByName('years').Value:=pbspinedit1.Text;
    adoquery2.Parameters.ParamByName('months').Value:=pbspinedit2.Text;
    adoquery2.Parameters.ParamByName('company').Value:=adoquery1.fieldbyname('fprovider').AsString;
    adoquery2.Parameters.ParamByName('pno').Value:=adoquery1.fieldbyname('pno').AsString;
    adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
    adoquery2.open;
    if adoquery2.RecordCount>0 then
    begin
     adoquery2.sql.Clear;
     adoquery2.sql.Add('update jsb  set hmweight=:hmweight where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
     adoquery2.Parameters.ParamByName('years').Value   :=pbspinedit1.Text;
     adoquery2.Parameters.ParamByName('months').Value  :=pbspinedit2.Text;
     adoquery2.Parameters.ParamByName('hmweight').Value:=adoquery1.fieldbyname('fqty').Asfloat;
     adoquery2.Parameters.ParamByName('company').Value :=adoquery1.fieldbyname('fprovider').AsString;
     adoquery2.Parameters.ParamByName('pno').Value:=adoquery1.fieldbyname('pno').AsString;
     adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
     adoquery2.ExecSQL;
    end else
    begin
     adoquery2.sql.clear;
     adoquery2.sql.Add('insert into jsb(yj,years,months,company,pno3,gx,hmweight ) ');
     adoquery2.SQL.Add(' values(0,:years,:months,:company,:pno3,:gx,:hmweight  )' );
     adoquery2.Parameters.ParamByName('years').Value   :=pbspinedit1.Text;
     adoquery2.Parameters.ParamByName('months').Value  :=pbspinedit2.Text;
     adoquery2.Parameters.ParamByName('pno3').Value   :=adoquery1.fieldbyname('pno').AsString;
     adoquery2.Parameters.ParamByName('hmweight').Value:=adoquery1.fieldbyname('fqty').Asfloat;
     adoquery2.Parameters.ParamByName('company').Value :=adoquery1.fieldbyname('fprovider').AsString;
     adoquery2.Parameters.ParamByName('gx').Value:=adoquery1.fieldbyname('fgx').AsString;
     adoquery2.ExecSQL;
    end;
  adoquery1.Next;
  end;   /////////////回毛统计完毕
 application.MessageBox('计算完毕！','系统提示',MB_OK);
end;
procedure Tfzpayment.BitBtn1Click(Sender: TObject);
var
str1:string;
begin
  inherited;
 with adoquery1 do
 begin
  sql.Clear;
  sql.Add('select company,pno3,gx,price1,fzqty,fzweight,spqty,spweight');
  sql.add(',jgf,hmweight,sh,price2,kmnk,chkk,yfk,years,months,yj from jsb ');
  open;
 end;

end;

procedure Tfzpayment.s03Execute(Sender: TObject);
begin
//  inherited;
query1.Edit;
end;

procedure Tfzpayment.BitBtn2Click(Sender: TObject);
begin
  inherited;

  if CDSbaseinfo.State in [dsinsert,dsedit] then
  CDSbaseinfo.Post;
  if CDSbaseinfo.ChangeCount > 0 then
    begin
      applyupdata(CDSbaseinfo,maintable,keyfield);
      CDSbaseinfo.MergeChangeLog;
    end;
end;

procedure Tfzpayment.GridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 // inherited;
  if Key=vk_return then
  begin
    if  query1.State in [dsEdit,dsinsert] then
       begin
        if grid.Col< grid.VisibleColumns.Count then
          grid.SelectedIndex:=grid.SelectedIndex+1 else
          grid.SelectedIndex:=0;

        end;
  end;
end;

procedure Tfzpayment.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-1;
      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 := 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 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;

procedure Tfzpayment.Query1BeforePost(DataSet: TDataSet);
begin
  inherited;
   if query1.State in [dsedit,dsinsert] then
   begin
    query1.FieldByName('jgf').AsFloat  := query1.FieldByName('price1').AsFloat *   query1.FieldByName('spqty').AsFloat/12; //加工费
    query1.FieldByName('kmnk').AsFloat := query1.FieldByName('price2').AsFloat * (query1.FieldByName('fzweight').AsFloat-query1.FieldByName('spweight').AsFloat-query1.FieldByName('hmweight').AsFloat-
    query1.FieldByName('spweight').AsFloat*query1.FieldByName('sh').AsFloat/100); //扣毛款
    query1.FieldByName('yfk').AsFloat :=  query1.FieldByName('jgf').AsFloat - query1.FieldByName('kmnk').AsFloat -query1.FieldByName('chkk').AsFloat ;
   end;
end;

end.