unitmatenumchg.pas

仓储系统

    sql.add('select * from MateClass order by MClass_No');
    open;
  end;
  setlength(listLb,dm.qry_MateClass.recordcount);
  setlength(listLbmc,dm.qry_MateClass.recordcount);
  cbbLb.Items.clear;
  cbbLb.Items.add('全部');
  for i:=0 to dm.qry_MateClass.recordcount - 1 do
  begin
    listLb[i]:=trim(dm.qry_MateClass.fieldbyname('MClass_No').asstring);
    listLbmc[i]:=trim(dm.qry_MateClass.fieldbyname('MClass_Name').asstring);
    cbbLb.items.Add(listLb[i]+' '+listLbmc[i]);
    dm.qry_MateClass.next;
  end;
end;

procedure TfrmMateNumChg.btnQryClick(Sender: TObject);
var
  str:string;
  str1,str2:string;
begin
  str:=' select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,                                                                                                '+
       '         (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初数量,                                                                          '+
       '         (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初总额,                                                                                      '+
       '         (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,                                                                                                                                    '+
       '         (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库总额,                                                                                                                                            '+
       '         (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,                                                                                                                                  '+
       '         (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库总额,                                                                                                                                          '+
       '         a.stoc_amount as 库存数量,a.mate_totalprice as 库存总额                                                                                                                                         '+
       '     from mate_basic a                                                                                                                                                                                   '+
       '     left outer join                                                                                                                                                                                     '+
       '     (select mate_code,sum(in_amount) as in_amount,sum(in_zj) as in_zj from mate_CgRk where (In_Date between :dtp1 and :dtp2) group by mate_code) b on a.mate_code=b.mate_code                           '+
       '     left outer join                                                                                                                                                                                     '+
       '     (select mate_code,sum(TL_amount) as TL_amount,sum(TL_zj) as TL_zj from TuiLiao_Rk where (mate_code=:bm) and (TL_Date between :dtp1 and :dtp2) group by mate_code) c on a.mate_code=c.mate_code      '+
       '     left outer join                                                                                                                                                                                     '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Ly where (Out_Date between :dtp1 and :dtp2) group by mate_code) d on a.mate_code=d.mate_code                        '+
       '     left outer join                                                                                                                                                                                     '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Bf where (Out_Date between :dtp1 and :dtp2) group by mate_code) e on a.mate_code=e.mate_code                        '+
       '     where (a.mate_class between ''101'' and ''606'')    ' ;


    str1:=formatdatetime('yyyy-mm-dd',dtpfrom.Date);
    str2:=formatdatetime('yyyy-mm-dd',dtpto.Date);

    //with ADOQuery_sql do
    //begin
      ADOQuery_sql.disablecontrols;
      ADOQuery_sql.close;
      ADOQuery_sql.sql.Clear;

      ADOQuery_sql.Parameters.ParamByName('dtp1').Value:=str1;
      ADOQuery_sql.Parameters.ParamByName('dtp2').Value:=str2;

//      sql.Add(str);
      ADOQuery_sql.open;
      ADOQuery_sql.enablecontrols;
//    end;
//    dsHuiZong.DataSet:=nil;
//    dsHuiZong.DataSet:=ADOQuery_sql;
end;

procedure TfrmMateNumChg.dtptoExit(Sender: TObject);
begin
  if btnClose.focused   then
  begin
     btnClose.OnClick(Sender);
     exit;
  end;
  //
  if dtpto.date<dtpfrom.date then
  begin
    application.MessageBox('终止日期不能小于起始日期','提示',MB_OK+MB_DEFBUTTON1);
    dtpto.SetFocus;
    exit;
  end;
end;

procedure TfrmMateNumChg.dtptoEnter(Sender: TObject);
begin
  dtpto.Date:=IncMonth(dtpfrom.Date,1);
end;

procedure TfrmMateNumChg.btnPrintClick(Sender: TObject);
Const
  ConstStr='''';
var
  my:Tdbgrid;
  i,j,total,rows: Integer;
  temp:string;
  Sheet: Variant;
  num:integer;
  isfind:boolean;
  query:Tquery;
begin
  my:=dbgrid1;
  temp:='收发料查询结果';

  dsHuiZong.DataSet:=TADOQuery(ADOQuery_sql);     //注意此处的用法

  if not VarIsEmpty(XLApp) then
  begin
   if  XLApp.Visible=false then
   begin
    XLApp.Visible := True;
    XLAPP.WindowState := xlMaximized;
    XLApp.ScreenUpdating := True;
    XLApp.Workbooks.Add(xlWBatWorkSheet);
    XLApp.Workbooks[1].WorkSheets[1].Name := temp;
    XLApp.Workbooks[1].WorkSheets[1].activate;
    Sheet := XLApp.Workbooks[1].WorkSheets[temp];
   end;
   if XLApp.Visible=true then
   begin
    num:=XLAPP.Workbooks.count;
    if num=0 then //没有工作薄
    begin
     XLApp.Visible := True;
     XLAPP.WindowState := xlMaximized;
     XLApp.Workbooks.Add(xlWBatWorkSheet);
     XLApp.Workbooks[1].WorkSheets[1].Name := temp;
     XLApp.Workbooks[1].WorkSheets[1].activate;
     Sheet := XLApp.Workbooks[1].WorkSheets[temp];
    end
    else if num=1 then //有工作薄
    begin
     num:=XLApp.Workbooks[1].WorkSheets.count;
     isfind:=false;
     for i:=1 to num do
     begin
      if XLApp.Workbooks[1].WorkSheets[i].Name= temp then
      begin
       XLApp.Workbooks[1].WorkSheets[i].Cells.ClearContents;
       isfind:=true;
       break;
      end;
     end;//end for
     if isfind then //找到
     begin
      XLApp.Visible := True;
      XLAPP.WindowState := xlMaximized;
      XLApp.Workbooks[1].WorkSheets[temp].Activate;
      Sheet := XLApp.Workbooks[1].WorkSheets[temp];
     end
     else //没有找到
     begin
      XLApp.Visible := True;
      XLAPP.WindowState := xlMaximized;
      XLApp.Workbooks[1].sheets.Add(XLApp.Workbooks[1].WorkSheets[1],,1,xlWorkSheet);
      XLApp.Workbooks[1].WorkSheets[1].Name :=temp;
      XLApp.Workbooks[1].WorkSheets[1].activate;
      Sheet := XLApp.Workbooks[1].WorkSheets[temp];
     end;
    end;
   end;
  end
  else //没有ole对象时创建实例
  begin
   XLApp:= CreateOleObject('Excel.Application');
   if VarIsEmpty(XLApp) then
   begin
    application.MessageBox('请在打印查询结果前先安装<Excel>程序!','警告',mb_ok+mb_iconwarning);
    exit;
   end;
   XLApp.Visible := True;
   XLAPP.WindowState := xlMaximized;
   XLApp.Workbooks.Add(xlWBatWorkSheet);
   XLApp.Workbooks[1].WorkSheets[1].Name := temp;
   Sheet := XLApp.Workbooks[1].WorkSheets[temp];
  end;
//-----------填充查询结果------------------------------------
  ADOQuery_sql.First;
  total:=(my as Tdbgrid).fieldCount;
  rows:=ADOQuery_sql.RecordCount;
  //填充标题
  for i := 0 to total - 1 do
  begin
   Sheet.Cells[1,i+1]:=(my as Tdbgrid).Fields[i].fieldname;
  end;
  //填充内容
  for i:=0 to rows - 1 do
  begin
   for j:=0 to total - 1 do
    sheet.cells[i+2,j+1]:=ConstStr+ADOQuery_sql.Fieldbyname((my as Tdbgrid).Fields[j].fieldname).asstring;
    ADOQuery_sql.next;
  end;
end;

end.