ustockstates.pas

适合行业为眼镜业

unit Ustockstates;

{最后修改于6-14，可以支持多级仓库使用}
interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ubase, Buttons, Grids, DBGrids, DB, StdCtrls, ExtCtrls, Menus,shellapi,
  DBClient;

type
  Tfrmstockstates = class(Tfrmbase)
    Panel1: TPanel;
    Label1: TLabel;
    Panel2: TPanel;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    btnpagedown: TBitBtn;
    BitBtn7: TBitBtn;
    Label2: TLabel;
    cmbtop: TComboBox;
    StringGrid1: TStringGrid;
    Label3: TLabel;
    cdssort: TClientDataSet;
    dssort: TDataSource;
    Label4: TLabel;
    PopupMenu2: TPopupMenu;
    N3: TMenuItem;
    N4: TMenuItem;
    Panel4: TPanel;
    CBType: TComboBox;
    cbBrand: TComboBox;
    CbBreed: TComboBox;
    ckbtype: TCheckBox;
    ckbbreed: TCheckBox;
    ckbbrand: TCheckBox;
    cbamount: TCheckBox;
    edtamount: TEdit;
    cdspart: TClientDataSet;
    cbball: TCheckBox;
    edtBbegin: TEdit;
    Label5: TLabel;
    edtBEND: TEdit;
    cbpoll: TCheckBox;
    EDTPBEGIN: TEdit;
    Label6: TLabel;
    EDTPEND: TEdit;
    cbnroms: TCheckBox;
    EDTNORMS: TEdit;
    Cbmstorage: TCheckBox;
    cmbmstorage: TComboBox;
    cbstorages: TCheckBox;
    cmbstorages: TComboBox;
    btnprint: TBitBtn;
    procedure cmbtopChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure btnpagedownClick(Sender: TObject);
    procedure spbexitClick(Sender: TObject);
    procedure StringGrid1DrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure N3Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn3MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure ckbtypeClick(Sender: TObject);
    procedure ckbbrandClick(Sender: TObject);
    procedure ckbbreedClick(Sender: TObject);
    procedure cbamountClick(Sender: TObject);
    procedure CBTypeChange(Sender: TObject);
    procedure cbBrandChange(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure cbballClick(Sender: TObject);
    procedure cbpollClick(Sender: TObject);
    procedure cbnromsClick(Sender: TObject);
    procedure CbmstorageClick(Sender: TObject);
    procedure cbstoragesClick(Sender: TObject);
    procedure cmbmstorageChange(Sender: TObject);
    procedure btnprintClick(Sender: TObject);
  private
    { Private declarations }
    flag,LocalCount:integer;
    ps:boolean;
    procedure inistringgrid(visible:boolean);
    procedure redata;
    procedure cleardata;
  public
    { Public declarations }
    tj:string;
    tempsql,tables:string;
  end;

var
  frmstockstates: Tfrmstockstates;

implementation

uses func, untdatadm, fm_SetColumn, Udlgstock, Udlgselectdate, Unitreportxf;

{$R *.dfm}
//////////////////////////自定义函数
procedure Tfrmstockstates.redata;
var
  i,p,t,m,j,d,c:integer;
  f:boolean;
begin
  m:=stringgrid1.RowCount-1;
  stringgrid1.RowCount:=cdssort.RecordCount+1;
  t:=stringgrid1.RowCount-1;
  i:=1;
  cdssort.First;
  while not cdssort.Eof do
  begin
    f:=true;
    if  trim(stringgrid1.Cells[1,m-1])=trim(cdssort.fieldbyname('goods_no').AsString) then
    begin
      c:=0;
      j:= stringgrid1.ColCount;
      for d:=1 to j do
      begin
        if trim(stringgrid1.Cells[d,0])=trim(cdssort.fieldbyname('storage_no').AsString+'仓库') then
        begin
          c:=d;
        end;
      end;
      if c=0 then
      begin
        stringgrid1.ColCount:=stringgrid1.ColCount+1;
        stringgrid1.ColWidths[stringgrid1.ColCount-1]:=60;
        stringgrid1.Cols[stringgrid1.ColCount-1].Add(trim(cdssort.fieldbyname('storage_no').AsString+'仓库'));
        stringgrid1.Cells[stringgrid1.ColCount-1,m-1]:=trim(cdssort.fieldbyname('stock_amount').AsString );
      end else
      begin
        stringgrid1.Cells[c,m-1]:=trim(cdssort.fieldbyname('stock_amount').AsString );
      end;
      f:=false;
    end;
    if f then
    begin
      for p:=1 to dbgrid1.FieldCount-1 do
      begin
        j:=stringgrid1.ColCount-1;
        c:=0;
        if (trim(stringgrid1.Cells[1,m])<>'') and (trim(stringgrid1.Cells[2,m])<>'') and (trim(stringgrid1.Cells[3,m])<>'') and (trim(stringgrid1.Cells[4,m])<>'') and (trim(stringgrid1.Cells[5,m])<>'') and (trim(stringgrid1.Cells[6,m])<>'') and (trim(stringgrid1.Cells[7,m])<>'') and (trim(stringgrid1.Cells[8,m])<>'') and (trim(stringgrid1.Cells[9,m])<>'') then
        begin
          for d:=1 to j do
          begin
            if trim(stringgrid1.Cells[d,0])=trim(cdssort.fieldbyname('storage_no').AsString+'仓库') then
            begin
              c:=d;
            end;
          end;
        end;
        if c=0 then
        begin
         c:=p;
        end ;
        stringgrid1.Cells[c,m]:=trim(dbgrid1.Fields[p].AsString);
      end;
      inc(m);
    end;
   cdssort.Next;
  end;
  //////////////////////////////
  m:=0; //清除空白项
  for i:=1 to stringgrid1.RowCount-1 do
  begin
    if trim(stringgrid1.Cells[1,i])='' then inc(m);
  end;
  stringgrid1.RowCount:=stringgrid1.RowCount-m;
  label4.Caption:=inttostr(strtoint(label4.Caption)+stringgrid1.RowCount-1);
  //showmessage(inttostr(stringgrid1.RowCount-1));
  ////////////////////////////////
end;
//////////////////////////////////////
procedure Tfrmstockstates.inistringgrid(visible:boolean);
var
  i:integer;
begin
  if visible then
  begin
    cleardata;
  end;
  stringgrid1.Visible:=visible;
  cdssort.First;
  if stringgrid1.ColCount<= dbgrid1.Columns.Count then
  begin
    stringgrid1.ColCount:=dbgrid1.Columns.Count;
  end;
  stringgrid1.ColWidths[0]:=-1;
  for i:=1 to dbgrid1.Columns.Count-1 do
  begin
    if  dbgrid1.Columns[i].Visible then
    begin
      stringgrid1.ColWidths[i]:=dbgrid1.Columns[i].Width;
    end else
    begin
      stringgrid1.ColWidths[i]:=-1;
    end;
    if trim(dbgrid1.Columns.Items[i].Title.Caption)<>'库存数量' then
    begin
      stringgrid1.cells[i,0]:=trim(dbgrid1.Columns[i].Title.Caption);
    end else
    begin
      stringgrid1.cells[i,0]:=trim(cdssort.fieldbyname('storage_no').AsString)+'仓库';
    end;
  end;
  redata;
end;

procedure Tfrmstockstates.cleardata;
var
  i:integer;
begin
  for i:=1 to stringgrid1.RowCount-1 do
  begin
    stringgrid1.Rows[i].Clear;
  end;
  stringgrid1.RowCount:=2;
   for i:=0 to stringgrid1.ColCount-1 do
  begin
    stringgrid1.cols[i].Clear;
  end;
  stringgrid1.ColCount:=2;
end;
/////////////////////////////////////////
procedure Tfrmstockstates.cmbtopChange(Sender: TObject);
begin
  inherited;
  tempsql:='select top '+trim(cmbtop.Text);
end;

procedure Tfrmstockstates.FormShow(Sender: TObject);
var
  t_sql:string;
  datas:olevariant;
begin
  inherited;
 ///////调类别
  t_sql:='select * from  [goods_type]';
  GetDataToComBoX(cbtype,t_sql,'type');
  cbtype.ItemIndex := -1;
  ////////////////仓库
   ///////////////////////////////
  cmbmstorage.Items.Add('本仓库'+shopid);
   ////////////////是否为总仓库

  t_sql:='select managerid from stock_manager where storageid='+''''+trim(shopid)+'''';
  datas:= null;
  ipubtemp.ty_query(t_sql,datas);
  if varisnull(datas) then
  begin
    ps:=true;
  end else
  begin
    ps:=false;
  end;
  ///////////////////////////
  if ps then
  begin
    t_sql:='select storage_no from  [storage] where storage_type<>'+''''+'门店仓库'+''''+' and storage_no<>'+''''+trim(shopid)+'''';
    cdspart.Close;
    cdspart.Data:=null;
    datas:=null;
    ipubtemp.ty_query(t_sql,datas);
    if not varisnull(datas) then
    begin
      cdspart.Data:=datas;
      cdspart.Open;
      datas:=null;
      while not cdspart.Eof do
      begin
        cmbmstorage.Items.Add('配送仓库'+trim(cdspart.fieldbyname('storage_no').AsString));
        cdspart.Next;
      end;
      cdspart.Close;
      cdspart.Data:=null;
    end;
  end;
  cmbmstorage.ItemIndex := 0;
  cmbmstorageChange(cmbmstorage);
  cmbmstorage.ItemIndex := -1;
  ////////////////////////////
  dbgCur:=dbgrid1;
  cmbtop.ItemIndex:=0;
  tempsql:='select top '+trim(cmbtop.Text);
  tables:=' * from vSelectGoods';
  cdssort.Close;
  cdssort.Data:=null;
  stringgrid1.FixedCols:=2;
end;

procedure Tfrmstockstates.BitBtn2Click(Sender: TObject);
begin
  inherited;
  //if dmmain.CDSquery.Active  then  exit;
  with TfmSetColumn.Create(Self) do
  try
    showmodal;
  finally
    free;
  end;
  if stringgrid1.Visible then
  inistringgrid(true);
end;

procedure Tfrmstockstates.BitBtn1Click(Sender: TObject);
var
  temp:widestring;
  datas:olevariant;
  str,str1:string;
  i,j:integer;
  zt:boolean;
begin
  inherited;
    cdssort.Close;
    cdssort.Data:=null;
    tj:=' where 1=1 ';
    str1:='';
    zt:=false;
    if not ps then
    begin
      str1:=' (';
      for j:=0 to cmbstorages.Items.Count-2 do
      begin
        i:=pos('仓库', trim(cmbstorages.Items.Strings[j]));
        if i>0 then
        begin
          str1:=str1+''''+copy(trim(cmbstorages.Items.Strings[j]),i+4,length(trim(cmbstorages.Items.Strings[j])))+''''+',';
        end else
        begin
          str1:=str1+''''+trim(cmbstorages.Items.Strings[j])+''''+',';
        end;
      end;
      str1:=str1+''''+trim(shopid)+''''+',';
    end;
     if cbmstorage.Checked then
    begin