unitinoutdjqry.pas

仓储系统

      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid4DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid5DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid6DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid7DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
  private
    { Private declarations }
  public
    { Public declarations }
    listLb,listLbmc:array of string;
    XLApp: Variant;
  end;

var
  frmInOutDjQry: TfrmInOutDjQry;

implementation

uses ComObj, Excel_TLB, UnitDm;
{$R *.dfm}

procedure TfrmInOutDjQry.BitBtn3Click(Sender: TObject);
begin
  close;
end;

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

procedure TfrmInOutDjQry.Clear(form:TForm);
var
  i:integer;
begin
  for i:=0 to TForm(form).ComponentCount-1 do
  begin
    if form.Components[i] is TEdit then
      TEdit(form.Components[i]).Text:=''
    else if form.Components[i] is TComboBox then
    begin
      TComboBox(form.Components[i]).ItemIndex:=-1;
      TComboBox(form.Components[i]).text:='';
    end
    else if form.Components[i] is TMemo then
      TMemo(form.Components[i]).Lines.Text:='';
  end;
end;


procedure TfrmInOutDjQry.BitBtnCgClick(Sender: TObject);
var
  str,strTemp:string;
  str1,str2,str3,strStart,strEnd:string;

  i,j:integer;
  MaxWidth: integer;
  DC:HDC;
  savefont:Hfont;
  Textsize:Tsize;
  maxlength:integer;
  strmax:string;
begin
  if PageControl2.ActivePage=TabSheetCg then
  begin
    TabSheetCg_1.TabVisible:=true;
    TabSheetZz_1.TabVisible:=false;
    TabSheetProductIn_1.TabVisible:=false;
    TabSheetTL_1.TabVisible:=false;
    TabSheetProductOut_1.TabVisible:=false;
    TabSheetLy_1.TabVisible:=false;
    TabSheetBf_1.TabVisible:=false;
  end;

  strTemp:='  select a.In_Bill as 入库单号,a.Pr_No as 合同编号,a.Plan_id as 计划编号,   '+
           '     a.Mate_Code as 物料编码,b.Mate_Name as 物料名称,b.Mate_Type as 规格型号,b.Mate_Class as 类别代码,  '+
           '     a.Gys as 供应商,a.Dept_id as 部门,a.In_Date as 入库日期,a.KfNo as 库房,a.KwNo as 库位,  '+
           '     a.In_Type as 入库类别,a.In_Amount as 入库数量,a.In_Price as 入库单价,a.In_Zj as 入库总价,   '+
           '     a.In_Post as 过帐标志,a.In_man as 经手人,a.Exam_man as 检验人,a.Oper_id as 制单人,a.Rk_memo as 备注  '+
           ' from mate_CgRk a      '+
           ' left outer join Mate_Basic b on a.Mate_Code=b.Mate_Code where';
  //入库单号
  if edtRkdh.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Bill='''+trim(edtRkdh.text)+''') and ';
  end;
  //计划编号
  if edtJhbh.text<>'' then
  begin
    strTemp:=strTemp+'(a.Plan_id='''+trim(edtJhbh.text)+''') and ';
  end;
  //合同编号
  if edtHtbh.text<>'' then
  begin
    strTemp:=strTemp+'(a.Pr_No='''+trim(edtHtbh.text)+''') and ';
  end;
  //入库数量
  if edtNum.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Amount='''+trim(edtNum.text)+''') and';
  end;
  //入库单价
  if edtPrice.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Price='''+trim(edtPrice.text)+''') and';
  end;
  //入库总价
  if edtTotalPrice.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Zj='''+trim(edtTotalPrice.text)+''') and';
  end;
  //物料编码
  if edtWlbm.text<>'' then
  begin
    strTemp:=strTemp+'(a.Mate_Code='''+trim(edtWlbm.text)+''') and';
  end;
  //物料名称
  if edtWlmc.text<>'' then
  begin
    strTemp:=strTemp+'(b.Mate_Name='''+trim(edtWlmc.text)+''') and';
  end;
  //规格型号
  if edtType.text<>'' then
  begin
    strTemp:=strTemp+'(b.Mate_Type='''+trim(edtType.text)+''') and';
  end;
  //经手人
  if edtJsr.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Man='''+trim(edtJsr.text)+''') and';
  end;
  //检验人
  if edtJyr.text<>'' then
  begin
    strTemp:=strTemp+'(a.Exam_Man='''+trim(edtJyr.text)+''') and';
  end;
  //制单人
  if edtZdr_1.text<>'' then
  begin
    strTemp:=strTemp+'(a.Oper_id='''+trim(edtZdr_1.text)+''') and';
  end;
  //物料类别
  if cbbLb_1.text<>'' then
  begin
    i:=cbbLb_1.Items.IndexOf(cbbLb_1.text);
    if i=-1 then
    begin
      for j:=0 to high(listLb) do
      if listLb[j]=cbbLb_1.text then
      begin
         i:=j;
        break;
      end;
    end;
    if i<>-1 then
    begin
      strtemp:=strtemp+' (b.Mate_Class='''+listLb[i]+''') and';
    end;
  end;
  //起始日期
  str1:=copy(meStart_1.text,1,4);                       //2003
  str2:=copy(meStart_1.text,5,2);                       //07
  str3:=copy(meStart_1.text,7,2);                       //28
  strStart:=trim(str1)+trim(str2)+trim(str3);           //2003年07月28日
  //截止日期
  str1:=copy(meEnd_1.text,1,4);                         //2003
  str2:=copy(meEnd_1.text,5,2);                         //07
  str3:=copy(meEnd_1.text,7,2);                         //28
  strEnd:=trim(str1)+trim(str2)+trim(str3);             //2003年07月28日

  if (meStart_1.text<>'') and (meEnd_1.text<>'') then   //
  begin
    insert('-',strStart,5);
    insert('-',strStart,8);
    insert('-',strEnd,5);
    insert('-',strEnd,8);
    //strtemp:=strtemp+' convert(varchar(10),a.In_Date,21)='''+strStart+''' and';
    strtemp:=strtemp+' (convert(varchar(10),a.In_Date,21)>='''+strStart+
                     ''' and convert(varchar(10),a.In_Date,21)<='''+strEnd+''') and '
  end
  else if meStart_1.text<>'' then
  begin
    insert('-',strStart,5);
    insert('-',strStart,8);
    strtemp:=strtemp+' convert(varchar(10),a.In_Date,21)>='''+strStart+''' and';
  end
  else if meEnd_1.text<>'' then
  begin
    insert('-',strEnd,5);
    insert('-',strEnd,8);
    strtemp:=strtemp+' convert(varchar(10),a.In_Date,21)<='''+strEnd+''' and'
  end;
  //
  if (edtRkdh.text='') and (edtJhbh.text='') and (edtHtbh.text='') and (edtNum.text='')
     and (edtPrice.text='') and (edtTotalPrice.text='')
     and (edtWlbm.text='') and (edtWlmc.text='') and (edtType.text='')
     and (edtJsr.text='') and (edtJyr.text='') and (meStart_1.text='')
     and (meEnd_1.text='') and (cbbLb_1.text='') then
  begin
    //application.messagebox('请至少输入一个查询条件','提示',mb_ok+mb_iconwarning);
    //edtRkdh.setfocus;
    strTemp:=copy(strtemp,1,length(strtemp)-6);    //-6是减去where字符

    with qry_1 do
    begin
      close;
      sql.Clear;
      sql.add(strTemp);
      open;
    end;
    lbl_1.Caption:=inttostr(qry_1.RecordCount)+' 条';
  end else
  begin
    str:=copy(strtemp,1,length(strtemp)-4);

    with qry_1 do
    begin
      close;
      sql.Clear;
      sql.add(str);
      open;
    end;
    lbl_1.Caption:=inttostr(qry_1.RecordCount)+' 条';
  end;
  //
  clistbox1.clear;
  clistbox1.Items:=qry_1.FieldDefList;
  for i:=0 to clistbox1.items.Count - 1 do
  begin
    clistbox1.Checked[i]:=true;
  end;
  //向采购入库添加内容--------------------------------
  maxlength:=0;
  for i:=0 to clistbox1.items.count - 1 do
  begin
    if maxlength < length(clistbox1.Items.Strings[i]) then
    begin
      maxlength:=length(clistbox1.Items.Strings[i]);
      strmax:=clistbox1.Items.Strings[i];
    end;
  end;
  DC:=getDC(0);
  savefont:=selectobject(DC,font.handle);
  gettextextentpoint32(DC,pchar(strmax),maxlength,Textsize);
  MaxWidth:=Textsize.cx+getsystemmetrics(SM_CXBORDER)*4+50;
  selectobject(Dc,savefont);
  releaseDC(0,DC);
  SendMessage(cListBox1.Handle, LB_SETHORIZONTALEXTENT, MaxWidth, 0);
end;

procedure TfrmInOutDjQry.meStart_1Exit(Sender: TObject);
var
  str:string;
begin
  str:=meStart_1.text;
  if str='' then exit;
  insert('-',str,5);
  insert('-',str,8);
  try
    strtodatetime(str);
  except
    application.MessageBox('日期输入错误！','警告',mb_ok+mb_iconwarning);
    meStart_1.Text:='';
    meStart_1.setfocus;
    exit;
  end;
end;

procedure TfrmInOutDjQry.BitBtn20Click(Sender: TObject);
begin
  Clear(frmInOutDjQry);
  meStart_1.Text:='';
  meEnd_1.Text:='';
end;

procedure TfrmInOutDjQry.BitBtnZzClick(Sender: TObject);
var
  str,strTemp:string;
  str1,str2,str3,strStart,strEnd:string;

  i,j:integer;
  MaxWidth: integer;
  DC:HDC;
  savefont:Hfont;
  Textsize:Tsize;
  maxlength:integer;
  strmax:string;
begin
  if PageControl2.ActivePage=TabSheetZz then
  begin
    TabSheetCg_1.TabVisible:=false;
    TabSheetZz_1.TabVisible:=true;
    TabSheetProductIn_1.TabVisible:=false;
    TabSheetTL_1.TabVisible:=false;
    TabSheetProductOut_1.TabVisible:=false;
    TabSheetLy_1.TabVisible:=false;
    TabSheetBf_1.TabVisible:=false;
  end;

  strTemp:=' select a.In_Bill as 入库单号,a.Pr_No as 合同编号,a.Plan_id as 计划编号,                '+
       '     a.Mate_Code as 物料编码,b.Mate_Name as 物料名称,                                       '+
       '     b.Mate_Type as 规格型号,b.Mate_Class as 物料类别,                                      '+
       '     a.Gys as 供应商,a.Dept_id as 部门,a.KfNo as 库房,a.KwNo as 库位,                       '+
       '     a.In_Date as 入库日期 ,a.In_Type as 入库类别,                                          '+
       '     a.In_Price as 入库单价,a.In_Amount as 入库数量,a.In_Zj as 总价,a.In_Post as 过帐标志,  '+
       '     a.In_man as 经手人,a.Exam_man as 检验人,a.Oper_id as 制单人,a.Rk_memo as 备注          '+
       '  from Mate_ZzRk a                                                                          '+
       '  left outer join Mate_Basic b on a.Mate_Code=b.Mate_Code where' ;
  //入库单号
  if edtRkdh_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Bill='''+trim(edtRkdh_2.text)+''') and ';
  end;
  //计划编号
  if edtJhbh_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.Plan_id='''+trim(edtJhbh_2.text)+''') and ';
  end;
  //合同编号
  if edtHtbh_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.Pr_No='''+trim(edtHtbh_2.text)+''') and ';
  end;
  //入库数量
  if edtNum_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Amount='''+trim(edtNum_2.text)+''') and';
  end;
  //入库单价
  if edtPrice_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Price='''+trim(edtPrice_2.text)+''') and';
  end;
  //入库总价
  if edtTotalPrice_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Zj='''+trim(edtTotalPrice_2.text)+''') and';
  end;
  //物料编码
  if edtWlbm_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.Mate_Code='''+trim(edtWlbm_2.text)+''') and';
  end;
  //物料名称
  if edtWlmc_2.text<>'' then
  begin
    strTemp:=strTemp+'(b.Mate_Name='''+trim(edtWlmc_2.text)+''') and';
  end;
  //规格型号
  if edtType_2.text<>'' then
  begin
    strTemp:=strTemp+'(b.Mate_Type='''+trim(edtType_2.text)+''') and';
  end;
  //经手人
  if edtJsr_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.In_Man='''+trim(edtJsr_2.text)+''') and';
  end;
  //检验人
  if edtJyr_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.Exam_Man='''+trim(edtJyr_2.text)+''') and';
  end;
  //制单人
  if edtZdr_2.text<>'' then
  begin
    strTemp:=strTemp+'(a.Oper_id='''+trim(edtZdr_1.text)+''') and';
  end;
  //物料类别
  if cbbLb_2.text<>'' then
  begin
    i:=cbbLb_2.Items.IndexOf(cbbLb_2.text);
    if i=-1 then
    begin
      for j:=0 to high(listLb) do
      if listLb[j]=cbbLb_2.text then
      begin
         i:=j;
        break;
      end;
    end;
    if i<>-1 then
    begin
      strtemp:=strtemp+' (b.Mate_Class='''+listLb[i]+''') and';
    end;
  end;
  //起始日期
  str1:=copy(meStart_2.text,1,4);                       //2003
  str2:=copy(meStart_2.text,5,2);                       //07
  str3:=copy(meStart_2.text,7,2);                       //28
  strStart:=trim(str1)+trim(str2)+trim(str3);           //2003年07月28日
  //截止日期
  str1:=copy(meEnd_2.text,1,4);                         //2003
  str2:=copy(meEnd_2.text,5,2);                         //07
  str3:=copy(meEnd_2.text,7,2);                         //28
  strEnd:=trim(str1)+trim(str2)+trim(str3);             //2003年07月28日

  if (meStart_2.text<>'') and (meEnd_2.text<>'') then   //
  begin
    insert('-',strStart,5);
    insert('-',strStart,8);
    insert('-',strEnd,5);
    insert('-',strEnd,8);
    //strtemp:=strtemp+' convert(varchar(10),a.In_Date,21)='''+strStart+''' and';
    strtemp:=strtemp+' (convert(varchar(10),a.In_Date,21)>='''+strStart+
                     ''' and convert(varchar(10),a.In_Date,21)<='''+strEnd+''') and '
  end
  else if meStart_2.text<>'' then
  begin
    insert('-',strStart,5);
    insert('-',strStart,8);
    strtemp:=strtemp+' convert(varchar(10),a.In_Date,21)>='''+strStart+''' and';
  end
  else if meEnd_2.text<>''   then