readquery.pas

进销存管理 编译环境Delphi7+Win2000 用到的控件 ReportMachine2.6 InfoPower4000Pro_vcl7 RxLib2.7 SkinEngine 3

unit ReadQuery;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, ListForm,
  ImgList, ExtCtrls, dxCntner, dxTL, GridRowEdit, dxDBCtrl, dxDBGrid, DBData, Db,
  Base2InfoUnit, Base2InfoEmp, Base2InfoWare, Base2InfoFixAdd, Base2InfoFixDec, ADODB,
  Base2InfoDepot, Menus, dxDBTL, dxEditor, dxExEdtr, dxEdLib, StdCtrls, dxBar,
  dxBarExtItems, KsSkinLabels, KsSkinForms, KsSkinPanels, Grids, DBGrids,
  dxDBTLCl, dxGrClms, Buttons, se_controls, KsSkinSpeedButtons;

type
  TfrmReadQuery = class(TfrmListForm)
    daBase2Info: TDataSource;
    DataSetQuery: TADODataSet;
    TreeMain: TdxDBTreeList;
    MainGrid: TdxDBGrid;
    pnlDate: TSeSkinPanel;
    Label1: TSeSkinLabel;
    Label2: TSeSkinLabel;
    edtStartDate: TdxEdit;
    edtEndDate: TdxEdit;
    bbDepot: TdxBarLargeButton;
    pnlDepot: TSeSkinPanel;
    SeSkinLabel2: TSeSkinLabel;
    edtDepot: TdxEdit;
    lblEditDepot: TSeSkinLabel;
    sbDateEx: TSeSkinSpeedButton;
    sbSelectEx: TSeSkinSpeedButton;
    procedure bbExitClick(Sender: TObject);
    procedure bbFindClick(Sender: TObject);
    procedure bbFilterClick(Sender: TObject);
    procedure TreeMainKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure TreeMainChangeNodeEx(Sender: TObject);
    procedure TreeMainGetImageIndex(Sender: TObject; Node: TdxTreeListNode;
      var Index: Integer);
    procedure TreeMainGetSelectedIndex(Sender: TObject;
      Node: TdxTreeListNode; var Index: Integer);
    procedure MainGridKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure bbTypeClick(Sender: TObject);
    procedure bbDateClick(Sender: TObject);
    procedure bbSetColClick(Sender: TObject);
    procedure bbRefreshClick(Sender: TObject);
    procedure bbSaveClick(Sender: TObject);
    procedure MainGridCustomDrawColumnHeader(Sender: TObject;
      AColumn: TdxTreeListColumn; ACanvas: TCanvas; ARect: TRect;
      var AText: string; var AColor: TColor; AFont: TFont;
      var AAlignment: TAlignment; var ASorted: TdxTreeListColumnSort;
      var ADone: Boolean);
    procedure MainGridCustomDrawBand(Sender: TObject;
      ABand: TdxTreeListBand; ACanvas: TCanvas; ARect: TRect;
      var AText: string; var AColor: TColor; AFont: TFont;
      var AAlignment: TAlignment; var ADone: Boolean);
    procedure MainGridChangeColumn(Sender: TObject; Node: TdxTreeListNode;
      Column: Integer);
    procedure bbSaveAsClick(Sender: TObject);
    procedure pbbChartSetupClick(Sender: TObject);
    procedure bbBandsClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure MainGridDblClick(Sender: TObject);
    procedure bbAlldelClick(Sender: TObject);
    procedure bbDepotClick(Sender: TObject);
    procedure DataSetQueryCalcFields(DataSet: TDataSet);
    procedure MainGridEdited(Sender: TObject; Node: TdxTreeListNode);
    procedure DataSetQueryAfterScroll(DataSet: TDataSet);
    procedure bbDetaledClick(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 }
    bVisibleBand, bReportGroup: Boolean;
    lMode, lTree, lReturn, lDepotID, lOutOrder: Integer;
    tStartDate, tEndDate: TDateTime;
    sStartDate, sEndDate, sUniqueTable, sPubMask, sPubCalcField, sFilterID: string;
    cPubFieldMode: Char;
    sWrite, sExpCaptionSave, sExpFieldSave, sSaveDataField, sExpSave,
      sIsString: string;
    function FilterData: Integer;
    procedure CalcExp;
    procedure BillRowSummary;
    procedure GetDataField(var sName, sField, sWidth, sRead, sExp: string);
    procedure SaveData;
    procedure ToolShow;
    procedure LoadGrid;
    function LoadDate: Boolean;
    procedure MainShow;
    procedure LoadData;
    procedure LoadCalcField; //读计算字段
    procedure CalcAllField(DataSet: TDataSet); //计算计算字段
  public
    { Public declarations }
  end;

function ReadQueryShow(l1Mode, l1Tree: Integer; s1FilterID: string = ''): string;
implementation

uses SysPublic,
  ExpressionForm,
  //GridSaveAsForm,
  PrintChartSetup;

{$R *.DFM}

function ReadQueryShow(l1Mode, l1Tree: Integer; s1FilterID: string = ''): string;
var
  frmReadQuery: TfrmReadQuery;
begin
  frmReadQuery := TfrmReadQuery.Create(Application);
  with frmReadQuery do
  begin
    lMode := l1Mode;
    lTree := l1Tree;
    sFilterID := s1FilterID;
    MainShow;
    Result := IntToStr(lReturn);
    Free;
  end;
end;

procedure TfrmReadQuery.MainShow;
begin
  ToolShow;
  if lMode = 0 then
    Exit;
  case lMode of
    WARE_STOCK_ORDER_COLLECT, EMPLOYE_STOCK_ORDER_COLLECT, CLIENT_STOCK_ORDER_COLLECT,
      WARE_SALE_ORDER_COLLECT, EMPLOYE_SALE_ORDER_COLLECT, CLIENT_SALE_ORDER_COLLECT,
      EMPLOYE_STOCK_COLLECT, WARE_STOCK_COLLECT, PROVIDE_STOCK_COLLECT,
      EMPLOYE_STOCK_DETAILED, WARE_STOCK_DETAILED, PROVIDE_STOCK_DETAILED,
      WARE_SALE_COLLECT, EMPLOYE_SALE_COLLECT, CLIENT_SALE_COLLECT,
      WARE_SALE_DETAILED, EMPLOYE_SALE_DETAILED, CLIENT_SALE_DETAILED,
      WARE_SALE_PROFIT, EMPLOYE_SALE_PROFIT, CLIENT_SALE_PROFIT,
      QUERY_FIXED_BILL, QUERY_FIXED_DEPRECIATE, QUERY_FIXED_PILE_DEPRECIATE,
      QUERY_FIXED_WORK, OPERATE_LOG,
      CLIENT_RECEIPT_DETAILED, PROVIDE_PAYABLE_DETAILED:
      if not LoadDate then
        Exit;
    BASE_CLIENT, BASE_PROVIDE, BASE_EMPLOYE, BASE_WARE, BASE_DEPOT,
      BASE_FIXED_ADD, WARE_STOCK_QUERY, WARE_STOCK_PRICE_EDIT, WARE_STOCK_DISTRIBUTE:
      bbType.Visible := ivAlways;
  end;
  LoadGrid;
  LoadData;
  LoadCalcField; //增加计算字段
  FilterData;
  ShowModal;
  if MainGrid.Visible then
    SetColWidth(Caption, MainGrid)
  else
    SetColWidth(Caption, TdxDbGrid(TreeMain));
end;

function TfrmReadQuery.FilterData: Integer;
var
  s1: string;
begin
  Result := 0;
  if sFilterID = '' then
    DataSetQuery.Filtered := false
  else
    DataSetQuery.Filtered := True;
  if sFilterID <> '' then
    s1 := 'ID = ' + sFilterID
  else
    s1 := '';
  try
    DataSetQuery.Filter := s1;
    Result := DataSetQuery.RecordCount;
  except
    Exit;
  end;
end;

function TfrmReadQuery.LoadDate: Boolean;
begin
  Result := GetDate(tStartDate, tEndDate);
  bbDate.Visible := ivAlways;
  pnlDate.Visible := True;
  sStartDate := FormatDateTime('yyyy/mm/dd', tStartDate);
  sEndDate := FormatDateTime('yyyy/mm/dd', tEndDate);
  edtStartDate.Text := sStartDate;
  edtEndDate.Text := sEndDate;
end;

procedure TfrmReadQuery.GetDataField(var sName, sField, sWidth, sRead, sExp:
  string);
var
  sSql: string;
  ADOSetTmp: TADODataSet;
  sNameS, sFieldS, sWidthS, sReadS, sExpS: string;
begin
  ADOSetTmp := nil;
  sNameS := '';
  sFieldS := '';
  sWidthS := '';
  sReadS := '';
  sSql := 'SELECT * FROM WageItem WHERE state=''使用''';

  ADOSetTmp := TADODataSet.Create(ADOSetTmp);
  ADOSetTmp.LockType := ltReadOnly;
  OpenDataSet(ADOSetTmp, sSql);
  if not ADOSetTmp.IsEmpty then
    while not ADOSetTmp.Eof do
    begin
      if sName <> '' then
        sNameS := sNameS + ADOSetTmp.FieldByName(sName).AsString + ',';
      if sField <> '' then
        sFieldS := sFieldS + 'A' + ADOSetTmp.FieldByName(sField).AsString + ',';
      sWidthS := sWidthS + '80' + ',';
      if Trim(ADOSetTmp.FieldByName('CanExp').AsString) <> '使用' then
      begin
        sReadS := sReadS + 'A' + ADOSetTmp.FieldByName(sField).AsString + ',';
        if sExp <> '' then
          sExpS := sExpS + '' + ',';
      end
      else
      begin
        if sExp <> '' then
          sExpS := sExpS + ADOSetTmp.FieldByName(sExp).AsString + ',';
      end;
      ADOSetTmp.Next;
    end
  else
  begin
  end;

  sName := sNameS;
  sField := sFieldS;
  sWidth := sWidthS;
  sRead := sReadS;
  sExp := sExpS;
  ADOSetTmp.Close;
  ADOSetTmp.Free;
end;

procedure TfrmReadQuery.LoadGrid;
var
  sField, sCaption, sWidth, sReadChart, sGroupField, sExp, sBandCaption,
    sBandIndex, sTwoField, sTwoCaption, sTwoWidth, sTwoMask, sSql, sColSum: string;
begin
  lReturn := 0;
  lDepotID := 0;
  edtDepot.Text := '全部仓库';
  sGroupField := '';
  sWrite := '';
  MainGrid.Tag := 0;
  sIsString := '';
  bVisibleBand := False;
  sPrintChart := '';
  sBandCaption := '';
  sBandIndex := '';
  sUniqueTable := '';
  sPubMask := '';
  sPubCalcField := '';
  cPubFieldMode := 'D';
  sTwoField := '';
  sTwoCaption := '';
  sTwoWidth := '';
  sTwoMask := '';
  sColSum := '';
  lOutOrder := StrToInt2(GetIniValue(frmData.ADOConnet, 'OutOrder')); //读成本算法
  bReportGroup := StrToBool2(GetIniValue(frmData.ADOConnet, 'ReportGroup'));
  if not DataSetQuery.Active then
    DataSetQuery.LockType := ltReadOnly;
  case lMode of
    BASE_CLIENT: