barcodequery.~pas

Barcode And LabelPrint

unit BarCodeQuery;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, NEOFORM, ExtCtrls, ToolWin, ComCtrls, StdCtrls, DBCtrls, Buttons,
  fcImager, fcdbtreeview, fcStatusBar, fcTreeView, GridsEh, DBGridEh, ADODB;

type
  TFrmBarCodeQuery = class(TGBKPForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    DateTimePickerPostDateSart: TDateTimePicker;
    DateTimePickerPostDateEnd: TDateTimePicker;
    BitBtn1: TBitBtn;
    CheckBoxOrderID: TCheckBox;
    CheckBoxPostDate: TCheckBox;
    CheckBoxCustomer: TCheckBox;
    CheckBoxDevtype: TCheckBox;
    EdtBarcode: TEdit;
    EdtOrderIDSart: TComboBox;
    EdtDevtype: TComboBox;
    CheckBoxIsOut: TCheckBox;
    EdtOrderIDEnd: TComboBox;
    fcStatusBar1: TfcStatusBar;
    PageControl1: TPageControl;
    TabSheetOrderID: TTabSheet;
    TabSheetBarcode: TTabSheet;
    GroupBox2: TGroupBox;
    MemoOrderDetail: TMemo;
    GroupBox3: TGroupBox;
    MemoOrderMemo: TMemo;
    GroupBox4: TGroupBox;
    Label11: TLabel;
    EdtOrderID: TEdit;
    Label3: TLabel;
    EdtCustomerName: TEdit;
    Label14: TLabel;
    EdtPostDate: TEdit;
    GroupBox5: TGroupBox;
    EdtDevName: TEdit;
    Label6: TLabel;
    Label7: TLabel;
    EdtNum: TEdit;
    DBGridEh1: TDBGridEh;
    Label8: TLabel;
    MemoBarcode: TMemo;
    CheckBoxBarCode: TCheckBox;
    EdtCustomer: TEdit;
    fcTreeView1: TTreeView;
    procedure CheckBoxOrderIDClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure fcTreeView1Click(Sender: TObject);
    procedure PageControl1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure EdtOrderIDSartChange(Sender: TObject);
    procedure fcTreeView1Change(Sender: TObject; Node: TTreeNode);
  private
    sqlstrOrderID: string;
    sqlstrGBarCodeID: string;
    sqlstrBarCode: string;
    { Private declarations }
    procedure ordersToTreeView;
    procedure loadOrderS(nodedataInt: integer);
    procedure loadBarCodeS(nodedataInt: integer);
  public
    { Public declarations }
  end;

var
  FrmBarCodeQuery: TFrmBarCodeQuery;

implementation

uses StockDataModel, DB, PublicFunction;

{$R *.dfm}

procedure TFrmBarCodeQuery.CheckBoxOrderIDClick(Sender: TObject);
begin
  inherited;
  EdtOrderIDSart.Enabled := CheckBoxOrderID.Checked;
  EdtOrderIDEnd.Enabled := CheckBoxOrderID.Checked;
  DateTimePickerPostDateSart.Enabled := CheckBoxPostDate.Checked;
  DateTimePickerPostDateEnd.Enabled := CheckBoxPostDate.Checked;
  EdtDevtype.Enabled := CheckBoxDevtype.Checked;
  EdtCustomer.Enabled := CheckBoxCustomer.Checked;
  EdtBarcode.Enabled := CheckBoxBarCode.Checked;

  if EdtOrderIDSart.Enabled = false then EdtOrderIDSart.Color := clSilver else EdtOrderIDSart.Color := clWindow;
  EdtOrderIDEnd.Color := EdtOrderIDSart.Color;
  if DateTimePickerPostDateSart.Enabled = false then DateTimePickerPostDateSart.Color := clSilver else DateTimePickerPostDateSart.Color := clWindow;
  DateTimePickerPostDateEnd.Color := DateTimePickerPostDateSart.Color;
  if EdtDevtype.Enabled = false then EdtDevtype.Color := clSilver else EdtDevtype.Color := clWindow;
  if EdtCustomer.Enabled = false then EdtCustomer.Color := clSilver else EdtCustomer.Color := clWindow;
  if EdtBarcode.Enabled = false then EdtBarcode.Color := clSilver else EdtBarcode.Color := clWindow;
end;

procedure TFrmBarCodeQuery.BitBtn1Click(Sender: TObject);
var sqlstr: string;
  sqlstrsub: string;
begin
  inherited;
  if CheckBoxOrderID.Checked then
  begin
  if (trim(EdtOrderIDSart.Text)='')or(trim(EdtOrderIDend.Text)='') then
  begin
  ShowMessage('请输入订单号');
  exit;
  end;
  end;
  sqlstr := '';
  sqlstrsub := '';
  sqlstrOrderID := '';
  sqlstrGBarCodeID := '';
  sqlstrsub := 'select a.*,b.*,c.DevName,d.CustomerName,d.postdate,d.OrderDetail,d.OrderMemo  from BarCode a,GBarCode b,DeviceType c,Orders d  ' +
    'where  a.GBarCodeID=b.GBarCodeID and b.Devnum=c.Devnum and b.OrderID=d.OrderID'; //

  sqlstrOrderID := 'select d.orderID  from BarCode a,GBarCode b,DeviceType c,Orders d  ' +
    'where  a.GBarCodeID=b.GBarCodeID and b.Devnum=c.Devnum and b.OrderID=d.OrderID'; //
  sqlstrGBarCodeID := 'select b.GBarCodeID  from BarCode a,GBarCode b,DeviceType c,Orders d  ' +
    'where  a.GBarCodeID=b.GBarCodeID and b.Devnum=c.Devnum and b.OrderID=d.OrderID'; //
  sqlstrBarCode := 'select a.BarCode  from BarCode a,GBarCode b,DeviceType c,Orders d  ' +
    'where  a.GBarCodeID=b.GBarCodeID and b.Devnum=c.Devnum and b.OrderID=d.OrderID'; //
  if CheckBoxOrderID.Checked then
  begin
    sqlstr := sqlstr + format(' and a.OrderId>=%d and a.OrderId<=%d', [strtoint(trim(EdtOrderIDSart.Text)),strtoint(trim(EdtOrderIDEnd.Text))]);
  end;
  if CheckBoxPostDate.Checked then
  begin
    sqlstr := sqlstr + format(' and PostDate >=''%s'' and PostDate <= ''%s''', [datetostr(DateTimePickerPostDateSart.Date), datetostr(DateTimePickerPostDateEnd.Date)]);
  end; //保证日期是datetime型
  if CheckBoxDevtype.Checked then
  begin //可模糊查询
    sqlstr := sqlstr + format(' and DevName like ''%%%s%%'' ', [EdtDevtype.Text]);
  end;

  if CheckBoxCustomer.Checked then
  begin //可模糊查询
    sqlstr := sqlstr + format(' and CustomerName like ''%%%s%%''', [EdtCustomer.Text]);
  end;

  if CheckBoxIsOut.State = cbChecked then
    sqlstr := sqlstr + format(' and a.IsOut=%d', [1])
  else if CheckBoxIsOut.State = cbunchecked then sqlstr := sqlstr + format(' and a.IsOut=%d', [0]);

  if CheckBoxBarCode.Checked then
  begin
     //可模糊查询
    sqlstr := sqlstr + format(' and BarCode like ''%%%s%%''', [EdtBarcode.Text]);
  end;
  //查询出所有符合条件的条码
  with StockDM.QBarCodefind do
  begin
    Close;
    SQL.Clear;
    SQL.Text := sqlstrsub + sqlstr + ' order by a.OrderID';
    Prepared := true;
    Open;
    sqlstrOrderID := sqlstrOrderID + sqlstr;
    sqlstrGBarCodeID := sqlstrGBarCodeID + sqlstr;
    sqlstrBarCode := sqlstrBarCode + sqlstr;
    //Parameters.Items[0].Value := strtodate(datetostr(DateTimePickerPostDateSart.Date)); //如果不转换oracle将提示数据类型不一致
    //Parameters.Items[1].Value := strtodate(datetostr(DateTimePickerPostDateEnd.Date));
    //sqlstr:=SQL.Text;
    //EdtSum.Text := inttostr(RecordCount);
    //EdtSum.Text :=sqlstr;
  end;
  ordersToTreeView;
end;

procedure TFrmBarCodeQuery.ordersToTreeView;
var QOrders, QGBarcode: TADOQuery;
  nodeOrderID, nodeGBarCode: TTreeNode;
begin
  fcTreeView1.Items.Clear;

  QOrders := TADOQuery.Create(self);
  QGBarcode := TADOQuery.Create(self);
  try
    with QOrders do
    begin
      Connection := StockDM.ADOConn;
      close;
      sql.Clear;
      sql.Add(format('select * from  Orders where OrderID in (%s)', [sqlstrOrderID]));
      open;
      QGBarcode.Connection := StockDM.ADOConn;
      QGBarcode.close;
      QGBarcode.sql.Clear;
      QGBarcode.sql.Add(format('select a.*,b.devname from GBarCode a,DeviceType b  where a.devnum=b.devnum and a.GBarCodeID in (%s)', [sqlstrGBarCodeID]));
      QGBarcode.open;
      first;
      while not eof do
      begin
        nodeOrderID := fcTreeView1.items.AddObject(nil, FieldByName('OrderID').AsString + ' ' + FieldByName('CustomerName').AsString + ' ' + FieldByName('postDate').AsString, pointer(FieldByName('OrderID').Asinteger));
        QGBarcode.Close;
        QGBarcode.Filter := 'OrderID=''' + Fieldbyname('OrderID').asstring + '''';
        QGBarcode.Filtered := true;
        QGBarcode.Active := true;
        QGBarcode.First;
        while not QGBarcode.Eof do
        begin
          nodeGBarCode := fcTreeView1.items.AddChildObject(nodeOrderID, QGBarcode.FieldByName('devName').AsString + ' 数量:' + QGBarcode.FieldByName('num').AsString, pointer(QGBarcode.FieldByName('GBarCodeID').Asinteger));
          QGBarcode.next;
        end;
        next;
      end;
    end;

  finally
    FreeAndNil(QOrders);
    FreeAndNil(QGBarcode);
  end;
  fcTreeView1.Repaint;

end;
//procedure TFrmBarCodeQuery.ordersToTreeView;

procedure TFrmBarCodeQuery.fcTreeView1Click(Sender: TObject);
//Node,
begin
  inherited;
 // fcTreeView1.Selected
 // if (Node.Level = 0)
end;

procedure TFrmBarCodeQuery.loadOrderS(nodedataInt: integer);
begin
  with StockDM.QBarCodefind do
  begin
    Locate('OrderID', nodedataInt, []);
    EdtOrderID.Text := FieldValues['OrderID'];
    EdtPostDate.Text := FieldValues['POSTDATE'];
    EdtCustomerName.Text := FieldValues['CustomerName'];
    MemoOrderDetail.Text := FieldValues['OrderDetail'];
    MemoOrderMemo.Text := FieldValues['OrderMemo'];
  end;
end;

procedure TFrmBarCodeQuery.loadBarCodeS(nodedataInt: integer);
begin
  with StockDM.QBarCodefind do
  begin
    Locate('GBarCodeID', nodedataInt, []);
    EdtDevName.Text := FieldValues['DevName'];
    EdtNum.Text := FieldValues['num'];
    MemoBarcode.Text := FieldValues['Memo'];
   //加载条码
    StockDM.QBarCodeQuery.Close;
    StockDM.QBarCodeQuery.SQL.Clear;
    StockDM.QBarCodeQuery.SQL.Add(Format('select a.* from barcode a where a.GBarCodeID=%d and a.barcode in (%s)', [nodedataInt, sqlstrBarCode]));
    StockDM.QBarCodeQuery.open;
  end;
end;

procedure TFrmBarCodeQuery.PageControl1Change(Sender: TObject);
var nodedataInt: integer;
begin
  inherited;
  if fcTreeView1.Selected=nil then fcTreeView1.Items[0].Selected:=true;
  if PageControl1.ActivePage = TabSheetOrderID then
  begin
    if fcTreeView1.Selected.Level = 0 then
      nodedataInt := integer(fcTreeView1.Selected.data)
    else nodedataInt := integer(fcTreeView1.Selected.Parent.Data);
    loadOrderS(nodedataInt);
  end;
  if PageControl1.ActivePage = TabSheetBarcode then
  begin
    if fcTreeView1.Selected.Level = 1 then
      nodedataInt := integer(fcTreeView1.Selected.data)
    else nodedataInt := integer(fcTreeView1.Selected.GetFirstChild.Data);
    loadBarCodeS(nodedataInt);
  end;
end;

procedure TFrmBarCodeQuery.FormShow(Sender: TObject);
begin
  inherited;
  DateTimePickerPostDateSart.Date:=date;
  DateTimePickerPostDateend.Date:=date;

  EdtOrderIDSart.Items.AddStrings(GetValueToCMB('Orders', 'OrderID', 'All'));
  EdtOrderIDend.Items.AddStrings(GetValueToCMB('Orders', 'OrderID', 'All'));

  EdtDevtype.Items.AddStrings(GetValueToCMB('DeviceType', 'DevName', 'All'));
end;

procedure TFrmBarCodeQuery.EdtOrderIDSartChange(Sender: TObject);
begin
  inherited;
  EdtOrderIDEnd.Text:=EdtOrderIDSart.Text;
end;

procedure TFrmBarCodeQuery.fcTreeView1Change(Sender: TObject;
  Node: TTreeNode);
var nodedataInt: integer;
begin
  inherited;
  nodedataInt := integer(node.Data);
  if node.Level = 0 then
  begin
    PageControl1.ActivePage := TabSheetOrderID;
    loadOrderS(nodedataInt);
  end;
  if node.Level = 1 then
  begin
    PageControl1.ActivePage := TabSheetBarcode;
    loadBarCodeS(nodedataInt);
  end;
end;

end.