infosch.pas

飞恒进销存(超市批发)管理系统V5.1（含源程序） 语言：Delphi 6/7 相关控件：FastReport 2.4以上, Ehlib 3.4以上 1.数据库为fhe2db_V51.da

unit InfoSch;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGridEh, ImgList, Menus, ComCtrls, ToolWin, ExtCtrls,adoDb,
  db,EhlibAdo, StdCtrls, Mask, DBCtrlsEh, Buttons, FR_Class, FR_DSet, comobj,
  FR_DBSet, StrUtils;

type
  TInfoSchForm = class(TForm)
    Tbinfo: TToolBar;
    ToolButton4: TToolButton;
    TBsec: TToolButton;
    Tbprint: TToolButton;
    Tbsave: TToolButton;
    ToolButton7: TToolButton;
    Tbexit: TToolButton;
    PMprint: TPopupMenu;
    Mpreview: TMenuItem;
    ILinfo: TImageList;
    Panel1: TPanel;
    PanelCondition1: TPanel;
    tbSetColumn: TToolButton;
    tbSaveColumn: TToolButton;
    ToolButton3: TToolButton;
    PanelCondition2: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    dbeCardNo: TDBEditEh;
    dtStartDate: TDateTimePicker;
    dtEndDate: TDateTimePicker;
    Label4: TLabel;
    dbeClientName: TDBEditEh;
    lblDsct: TLabel;
    dbeDsct: TDBEditEh;
    spbDsct: TSpeedButton;
    frReport1: TfrReport;
    frDBDataSet1: TfrDBDataSet;
    SaveDialog1: TSaveDialog;
    DBGrid1: TDBGridEh;
    Splitter1: TSplitter;
    grdGoods: TDBGridEh;
    procedure TBsecClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TbexitClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure MpreviewClick(Sender: TObject);
    procedure tbSetColumnClick(Sender: TObject);
    procedure tbSaveColumnClick(Sender: TObject);
    procedure dbGrid1TitleBtnClick(Sender: TObject; ACol: Integer;
      Column: TColumnEh);
    procedure dtStartDateExit(Sender: TObject);
    procedure dbeCardNoKeyPress(Sender: TObject; var Key: Char);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure dbeCardNoEditButtons0Click(Sender: TObject;
      var Handled: Boolean);
    procedure dbeClientNameEditButtons0Click(Sender: TObject;
      var Handled: Boolean);
    procedure spbDsctClick(Sender: TObject);
    procedure FMemuItemClick(Sender: TObject);
    procedure TbsaveClick(Sender: TObject);
  private
    { Private declarations }
    FSql:string;
    FTableId:integer;
    sTablename:string;
    sQueryCode,sFrName:string;
    sCdnSql:string;
    FMemuItem:TMenuItem;
    Function GetConditionValue(sCondition,sParam:string):string;
    Function GetStoreIdValue(sCondition,sParam:string):integer;
    procedure secCondition;
  public
    { Public declarations }
    property  xSql:string read FSql write FSql;
    property  xTableId:integer read FTableId write FTableId;
  end;

var
  InfoSchForm: TInfoSchForm;

implementation

uses search, datamodule1, report2, setFields, selVC;

{$R *.dfm}
//由条件得出相应条件的传递值
Function TInfoSchForm.GetConditionValue(sCondition,sParam:string):string;
var
  n1,n2:integer;
begin
  result:='';
  n1:=pos(sParam,sCondition);
  if n1 = 0 then exit;
  n1:=pos('''',copy(scondition,n1,Length(sCondition)-n1)) + n1-1;
  n2:=pos('''',copy(scondition,n1+1,Length(sCondition)-n1)) + n1;
  result:=copy(scondition,n1+1,n2-n1-1);
end;

Function TInfoSchForm.GetStoreIdValue(sCondition,sParam:string):integer;
var
  n1,n2:integer;
  s:string;
begin
  result:=0;
  n1:=pos(sParam,sCondition);
  if n1 = 0 then exit;
  n1:=pos('=',copy(scondition,n1,Length(sCondition)-n1)) + n1;
  n2:=pos('and',copy(scondition,n1+1,Length(sCondition)-n1)) + n1;  //
  if n1 = 0 then exit;
  if n2 = n1 then n2:=length(sCondition);  //若为最后一条件，此时无and,则为该串长
  s:=trim(copy(scondition,n1+1,n2-n1-1));
  if s='' then exit else result:=strToint(s);
end;

procedure TInfoSchForm.TBsecClick(Sender: TObject);
var
  s:string;
  n:integer;
begin
  schConditionForm.xTableName:=dataE2.aTblname[FTableId];
  schConditionForm.xQueryCode:=sQueryCode;
  if schConditionForm.showmodal=mrOk then
  begin
     //动态字段
     sCdnSql:=schConditionForm.xSql;
     panelCondition1.caption:='  查询条件：['+ schConditionForm.xCondition +' ]';
{     //sql为空时，默认为>'2000-01-01';
     if sSql='' then sSql:='Billdate > '''+'2000-01-01''';
     //:Cdn 动态SQL代替此串
     sSql:=StringReplace(xsql,':Cdn',sSql,[rfIgnoreCase]);
}
    with dataE2 do
    begin
      if QueryRpt.active then QueryRpt.Close;
      if fTableid <> 92 then   //将条件和参数一起传递
      begin
        queryRpt.Parameters[0].value:=sCdnSql;
        if pos(':orderby',xSql) > 0 then
          queryRpt.Parameters[1].value:='';
      end
      else begin
        s:=GetConditionValue(sCdnSql,'BillDate');
        if s='' then s:='2000-01-01';
        queryRpt.Parameters[0].value:=s;

        s:=GetConditionValue(sCdnSql,'   and   ');
        if s='' then s:=DateTostr(date);
        queryRpt.Parameters[1].value:=s;

        s:=GetConditionValue(sCdnSql,'Code');
        if s='' then s:='%%';
        queryRpt.Parameters[2].value:=s;

        s:=GetConditionValue(sCdnSql,'Name');
        if s='' then s:='%%';
        queryRpt.Parameters[3].value:=s;

        s:=GetConditionValue(sCdnSql,'Type');
        if s='' then s:='%%';
        queryRpt.Parameters[4].value:='';

        n:=GetStoreIdValue(sCdnSql,'StoreId');
        queryRpt.Parameters[5].value:=n;

        s:=GetConditionValue(sCdnSql,'StoreType');
        if s='' then s:='%%';
        queryRpt.Parameters[6].value:='';
      end;

      QueryRpt.open;
    end;
  end;  //mrok
end;

procedure TInfoSchForm.FormShow(Sender: TObject);
var
  tmpStream:TADOBlobStream;
  dLastMonth:TDateTime;
begin
  Application.ProcessMessages;
  dbGrid1.DataSource :=nil;
  grdGoods.DataSource:=nil;
  dLastMonth:=now;//dataE2.GetLastMonth(date); 改为默认查找当天
  dtStartDate.Date:=now;
  dtEndDate.Date:=now;
  sTableName:=dataE2.aTblname[FTableid];
  //是POS管理就显示PanelCondition2，否则显示PanelCondition1
  PanelCondition1.Visible:=(FTableid<101);
  PanelCondition2.Visible:=(FTableid>101);
  Tbprint.Visible:=(FTableid<>100);
  lblDsct.Visible:=(FTableid=102);
  dbeDsct.Visible:=(FTableid=102);
  spbDsct.Visible:=(FTableid=102);
  //客户销售明细表
  grdGoods.Visible:=(FTableid=89);
  Splitter1.Visible:=(FTableid=89);
  //2005-05-18:增加打印盘点表
  if FTableid=90 then
  begin
    FMemuItem:=TMenuItem.Create(nil);
    FMemuItem.Caption:='盘点表';
    PMprint.Items.Add(FMemuItem);
    FMemuItem.OnClick:=FMemuItemClick;
  end;
  dbeCardNo.Text:='';
  dbeClientName.Text:='';
  dbeDsct.Text:='';
  panelCondition1.caption:='  查询条件：[ '+'开单日期: '+dateTostr(dLastMonth)+'至'+dateTostr(date)+' ]';

  // read blob field
  if dataE2.OpenTable('select * from queryModule where upper(ModuleCode)= :ModuleCode',sTableName) then
  begin
    tmpStream := TADOBlobStream.Create(DataE2.adoQuery1.FieldByName('GridColumns') as TBlobField, bmRead);
    xSql:=dataE2.ADOQuery1.fieldbyname('Sql1').asstring+' '+dataE2.ADOQuery1.fieldbyname('Sql2').asstring;
    sQueryCode:=dataE2.ADOQuery1.fieldbyname('queryCode').asstring;
    //相对应的报表文件名
    sFrName:=dataE2.ADOQuery1.fieldbyname('RptName').asstring;
    dataE2.ADOQuery1.close;  end
  else exit;

  try
    tmpStream.Position :=0;
    try
      dbGrid1.Columns.LoadFromStream(tmpStream);  //load columns from gridcolumns
    except
      dbGrid1.Columns.clear;
    end;
  finally
    tmpStream.Free ;
  end;

  if  fTableId < 90 then
    sCdnSql:=' where Billdate between '''+dateTostr(dLastMonth)+'''   and   '''+dateTostr(date)+'''' //dateTimeTostr(now)+''''
  else
    sCdnSql:='';

  with dataE2 do
  begin
    if QueryRpt.active then QueryRpt.Close;
    QueryRpt.sql.clear;
    QueryRpt.sql.add(xSql);

    if FTableID >100 then
      secCondition
    else
    if fTableid <> 92 then   //将条件和参数一起传递
    begin
      queryRpt.Parameters[0].value:=sCdnSql;
      if pos(':orderby',xSql) > 0 then
        queryRpt.Parameters[1].value:='';
    end
    else begin
      queryRpt.Parameters[0].value:=dLastMonth;
      queryRpt.Parameters[1].value:=date;
      queryRpt.Parameters[2].value:='';
      queryRpt.Parameters[3].value:='';
      queryRpt.Parameters[4].value:='';
      queryRpt.Parameters[5].value:=0;
      queryRpt.Parameters[6].value:='';
    end;
    QueryRpt.open;
  end;
  dbGrid1.DataSource :=dataE2.dsRpt;

  if FTableid=89 then
  begin
    with dataE2 do
    begin
      if QueryTmp.active then QueryTmp.Close;
      QueryTmp.sql.clear;
      QueryTmp.sql.add('select a.Units,a.qty,a.price,b.code,b.name from o_outstored a');
      QueryTmp.sql.add('left outer join goods b on a.goodsid=b.goodsid');
      QueryTmp.sql.add('where a.billid=:billid');
      QueryTmp.Parameters[0].Value:=QueryRpt.FieldByName('Billid').AsInteger;
      QueryTmp.Open;
    end;
    grdGoods.DataSource:=dataE2.dsTmp;
  end;
end;

procedure TInfoSchForm.TbexitClick(Sender: TObject);
begin
  modalResult:=mrNone;
  close;
end;

procedure TInfoSchForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  dbGrid1.DataSource :=nil;
  if FTableid=90 then
    FMemuItem.Free;
end;

procedure TInfoSchForm.MpreviewClick(Sender: TObject);
var
  v: TfrView;
  b: TfrBandView;
  Page: TfrPage;
  sMemo:string;
  i:integer;
begin
  dbGrid1.DataSource :=nil;
  if fTableid <> 91 then
    FormRpt1.PrintSchReport(PanelCondition1.Caption ,sFrName,false)
  else if fTableid = 91 then
    begin
      dbGrid1.DataSource :=dataE2.dsRpt;
      frDBDataSet1.DataSource:=dataE2.dsRpt;
      frReport1.Pages.Clear;
      frReport1.Pages.Add;              // create page
      Page := frReport1.Pages[0];

      b := TfrBandView.Create;             // create Title band
      b.SetBounds(0, 20, 0, 50);           // position and size in pixels
      b.BandType := btReportTitle;         // (only Top and Height are significant
      Page.Objects.Add(b);                 //  for the band)

      v := TfrMemoView.Create;             // create memo
      v.SetBounds(20, 20, 200, 30);
      v.BandAlign := baWidth;
      v.Prop['Alignment'] := frtaCenter;   // another way to access properties
      v.Prop['Font.Style'] := 2;
      v.Prop['Font.Size'] := 16;
      v.Memo.Add('商品货位报表');
      Page.Objects.Add(v);

      v := TfrMemoView.Create;             // create data field
      v.SetBounds(30, 50, 50, 20);
      v.Prop['Alignment'] := frtaCenter;
      v.Prop['FrameTyp'] := 15;
      v.Memo.Add('编码');
      Page.Objects.Add(v);

      v := TfrMemoView.Create;             // create data field
      v.SetBounds(80, 50, 200, 20);
      v.Prop['Alignment'] := frtaCenter;