billquery.pas

进销存·完整的·有数据库的·非常完整·只得参考

unit BillQuery;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ListForm, ImgList, ExtCtrls, dxCntner, dxTL, ExistBillEdit,
  dxDBCtrl, dxDBGrid, DBData, Db, BillEdit, ADODB, Menus, dxDBTL, dxBar,
  dxBarExtItems, KsSkinForms, KsSkinPanels, KsSkinMessages, dxExEdtr,
  dxDBELib, dxEditor, dxEdLib, StdCtrls, Variants, se_controls;

type
  TfrmBillQuery = class(TfrmListForm)
    daBase2Info: TDataSource;
    ADOSetBillQuery: TADODataSet;
    MainGrid: TdxDBGrid;
    procedure bbAddClick(Sender: TObject);
    procedure bbEditClick(Sender: TObject);
    procedure bbDelClick(Sender: TObject);
    procedure bbTypeClick(Sender: TObject);
    procedure bbFindClick(Sender: TObject);
    procedure bbFilterClick(Sender: TObject);
    procedure bbSetColClick(Sender: TObject);
    procedure bbRefreshClick(Sender: TObject);
    procedure bbSelectClick(Sender: TObject);
    procedure MainGridDblClick(Sender: TObject);
    procedure MainGridCustomDrawCell(Sender: TObject; ACanvas: TCanvas;
      ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxTreeListColumn;
      ASelected, AFocused, ANewItemRow: Boolean; var AText: string;
      var AColor: TColor; AFont: TFont; var AAlignment: TAlignment;
      var ADone: Boolean);
    procedure bbCopyClick(Sender: TObject);
    procedure bbBlankoutClick(Sender: TObject);
    procedure MainGridCustomDraw(Sender: TObject; ACanvas: TCanvas;
      ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn;
      const AText: string; AFont: TFont; var AColor: TColor; ASelected,
      AFocused: Boolean; var ADone: Boolean);
  private
    { Private declarations }
    sReturn: string;
    lMode, lMainMode: Integer;
    lSelect: Integer;
    bRead: Boolean;
    function GetRadioBill(l1Mode: Integer): Integer;
    function QueryToEdit(lQMode: Integer): Integer;
    procedure ToolShow;
    procedure LoadGrid;
    function ReturnSQL: string;
    procedure MainShow;
    procedure LoadData;

  public
    { Public declarations }
  end;

function BillQueryShow(l1Mode, l1Select: Integer; b1Read: Boolean): string;

implementation

uses SysPublic;

{$R *.DFM}

function BillQueryShow(l1Mode, l1Select: Integer; b1Read: Boolean): string;
var
  frmBillQuery: TfrmBillQuery;
begin
  frmBillQuery := TfrmBillQuery.Create(Application);
  
  with frmBillQuery do
  begin
    lMainMode := l1Mode;
    lMode := l1Mode;
    lSelect := l1Select;
    bRead := b1Read;
    sReturn := '';
    MainShow;
    Result := sReturn;
    Free;
  end;
end;

procedure TfrmBillQuery.MainShow;
begin
  if (lMainMode = ALL_STOCK_BILL) or (lMainMode = ALL_SALE_BILL) or
    (lMainMode = ALL_EXIST_BILL) or (lMainMode = ALL_MONEY_BILL) or (lMainMode = 0) then
    lMode := GetRadioBill(lMainMode);
  if lMode = 0 then
    Exit;
  ToolShow;
  LoadGrid;
  LoadData;
  ShowModal;
  SetColWidth(Caption, MainGrid);
end;

procedure TfrmBillQuery.LoadGrid;
begin
  case lMode of
    ALL_STOCK_BILL:
      begin
        Caption := '单据查询-所有进货单据';
      end;
    ALL_SALE_BILL:
      begin
        Caption := '单据查询-所有销售单据';
      end;
    ALL_EXIST_BILL:
      begin
        Caption := '单据查询-库存单据查询';
      end;
    STOCK_ORDER_BILL:
      begin
        Caption := '单据查询-进货订单查询';
      end;
    SALE_ORDER_BILL:
      begin
        Caption := '单据查询-销售订单查询';
      end;
  else
    begin
      Caption := '单据查询-所有单据';
    end;
  end;

  StrToGridField(MainGrid, 'ID,BillDate,UserCode,Name,EName,UName,Memo,Type',
    'ID,开单日期,单据编号,单据类型,经手人,相关单位,备注,类型',
    '30,80,110,80,70,120,120,90');
  MainGrid.ColumnByFieldName('ID').Visible := false;
  MainGrid.ColumnByFieldName('UserCode').SummaryFooterType := cstCount;
  MainGrid.ColumnByFieldName('UserCode').SummaryFooterFormat := '记录条数: 0';
  SetCol(Caption, MainGrid, 1);
  MainGrid.ColumnByFieldName('Type').Visible := false;
end;

function TfrmBillQuery.ReturnSQL: string;
var
  sSql: string;
begin
  sSql := ' SELECT bi.ID,bi.BillDate,bi.UserCode,bi.Name,bi.Mode,e.Name as EName,u.Name as UName,bi.Memo,bi.type'
    + ' FROM BillIndex bi,Employe e,Unit u' +
    ' WHERE bi.EmployeID = e.ID and bi.UnitID = u.ID and bi.ID>0';

  if lMode = ALL_STOCK_BILL then
    sSql := sSql + ' and bi.Mode>=' + IntToStr(STOCK_ORDER_EDIT) +
      ' and bi.Mode<=' + IntToStr(STOCK_BACK_EDIT)
  else
    if lMode = ALL_SALE_BILL then
      sSql := sSql + ' and bi.Mode>=' + IntToStr(SALE_ORDER_EDIT) +
        ' and bi.Mode<=' + IntToStr(SALE_BACK_EDIT)
    else
      sSql := sSql + ' and bi.Mode=' + IntToStr(QueryToEdit(lMode));
  if lMode = ALL_EXIST_BILL then
    sSql := 'SELECT I.*,E.Name as EName' +
      ' FROM Employe AS E RIGHT JOIN ExistIndex AS I ON E.ID = I.EmployeID';
  Result := sSql;
end;

procedure TfrmBillQuery.LoadData;
begin
  OpenDataSet(ADOSetBillQuery, ReturnSQL);
end;

function TfrmBillQuery.GetRadioBill(l1Mode: Integer): Integer;
var
  sStock, sSale, sExist, sMoney, sAll: string;
  lIndex: Integer;
begin
  sStock := '全部进货单' + #13 + '  进货订单' + #13 + '  进货单' + #13 + '  进货付款单' + #13 + '  进货退货单';
  sSale := '全部销售单' + #13 + '  销售订单' + #13 + '  销售单' +
    #13 + '  销售收款单' + #13 + '  现款销售单' + #13 + '  销售退货单' + #13 + '  零售单(POS)';
  sExist := '全部库存单' + #13 + '  领料单' + #13 + '  退料单' + #13 + '  报损单' +
    #13 + '  报溢单' + #13 + '  赠送单' + #13 + '  获赠单' + #13 + '  产品进仓' + #13 + '  库存盘点' + #13 + '  存货调价' +
    #13 + '  仓库调拨' + #13 + '  变价调拨' + #13 + '  组装拆卸' + #13 + '  库存变动';
  sMoney := '全部钱流单' + #13 + ' 其它费用单' + #13 + ' 其它收入单' + #13 + ' 银行存取款';

  sAll := sStock + #13 + sSale + #13 + sExist + #13 + sMoney;
  case l1Mode of
    ALL_STOCK_BILL:
      begin
        lIndex := GetListBox('单据类型选择', sStock, 0);
        case lIndex of
          0: Result := ALL_STOCK_BILL;
          1: Result := STOCK_ORDER_BILL;
          2: Result := STOCK_FORMAL_BILL;
          3: Result := STOCK_MONEY_BILL;
          4: Result := STOCK_BACK_BILL;
        else
          Result := 0;
        end;
      end;
    ALL_SALE_BILL:
      begin
        lIndex := GetListBox('单据类型选择', sSale, 0);
        case lIndex of
          0: Result := ALL_SALE_BILL;
          1: Result := SALE_ORDER_BILL;
          2: Result := SALE_FORMAL_BILL;
          3: Result := SALE_MONEY_BILL;
          4: Result := SALE_READY_BILL;
          5: Result := SALE_BACK_BILL;
          6: Result := SALE_POS_BILL;
        else
          Result := 0;
        end;
      end;
    ALL_EXIST_BILL:
      begin
        lIndex := GetListBox('单据类型选择', sExist, 0);
        case lIndex of
          0: Result := ALL_EXIST_BILL;
          1: Result := EXIST_DRAW_BILL;
          2: Result := EXIST_RETURN_BILL;
          3: Result := EXIST_LOSING_BILL;
          4: Result := EXIST_INCREASE_BILL;
          5: Result := EXIST_PRESENT_BILL;
          6: Result := EXIST_GAIN_BILL;
          7: Result := EXIST_ENTER_DEPOT_BILL;
          8: Result := EXIST_CHECK_LIST_BILL;
          9: Result := EXIST_CHANGE_PRICE_BILL;
          10: Result := EXIST_EXCHANGE_BILL;
          11: Result := EXIST_PRICE_EXCHANGE_BILL;
          12: Result := EXIST_ASSEMBLY_BILL;
          13: Result := EXIST_OTHER_BILL;
        else
          Result := 0;
        end;
      end;
    ALL_MONEY_BILL: //钱流单据查询
      begin
        lIndex := GetListBox('单据类型选择', sMoney, 0);
        case lIndex of
          0: Result := MONEY_EXPENSES_BILL;
          1: Result := MONEY_INCOME_BILL;
          2: Result := MONEY_DEPOSIT_BILL;
        else
          Result := 0;
        end;
      end;
  else
    begin
      Result := l1Mode;
      Exit;
    end;
  end;
end;

function TfrmBillQuery.QueryToEdit(lQMode: Integer): Integer;
var
  lEditMode: Integer;
begin
  case lQMode of
    {进货}
    STOCK_ORDER_BILL: lEditMode := STOCK_ORDER_EDIT;
    STOCK_FORMAL_BILL: lEditMode := STOCK_FORMAL_EDIT;
    STOCK_MONEY_BILL: lEditMode := STOCK_MONEY_EDIT;
    STOCK_BACK_BILL: lEditMode := STOCK_BACK_EDIT;
    {销售}
    SALE_ORDER_BILL: lEditMode := SALE_ORDER_EDIT;
    SALE_FORMAL_BILL: lEditMode := SALE_FORMAL_EDIT;
    SALE_MONEY_BILL: lEditMode := SALE_MONEY_EDIT;
    SALE_READY_BILL: lEditMode := SALE_READY_EDIT;
    SALE_BACK_BILL: lEditMode := SALE_BACK_EDIT;