EEP範例資料

訂、出貨系統流程架構

訂、出貨系統流程表格

訂、出貨系統資料結構

客戶基本檔(CUSTOMER)

商品基本檔(PRODUCT)

訂單主檔(ORDER_MASTER)

訂單明細檔(ORDER_DETAIL)

出貨單主檔(SALE_MASTER)

出貨單明細檔(SALE_DETAIL)

月庫存記錄檔(MONTH_STOCK)

使用者基本檔(USERS)

客戶基本資料

執行畫面

程式碼

產品基本資料

執行畫面

程式碼

訂單資料維護

執行畫面

前端程式碼

後端程式碼

訂單查詢作業（報表）

設計畫面

查詢畫面程式碼

報表程式碼

訂、出貨系統資料結構

客戶基本檔(CUSTOMER)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
CUST_NO | 客戶編號 | VARCHAR | 8 | Y
CUST_NAME | 客戶名稱 | VARCHAR | 20 | N

商品基本檔(PRODUCT)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
PROD_NO | 商品編號 | VARCHAR | 15 | Y
PROD_NAME | 商品名稱 | VARCHAR | 20 | N
PRICE | 單價 | NUMERIC | 8 | N
QTY | 現有量 | NUMERIC | 8 | N

訂單主檔(ORDER_MASTER)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
ORDER_NO | 訂單編號 | VARCHAR | 10 | Y
ORDER_DATE | 訂單日期 | VARCHAR | 8 | N
CUST_NO | 客戶編號 | VARCHAR | 8 | N
TOTAL_AMOUNT | 訂單總額 | NUMERIC | 8 | N

訂單明細檔(ORDER_DETAIL)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
ORDER_NO | 訂單編號 | VARCHAR | 10 | Y
SEQ_NO | 序號 | VARCHAR | 2 | Y
PROD_NO | 商品編號 | VARCHAR | 15 | N
PRICE | 單價 | NUMERIC | 8 | N
QTY | 數量 | NUMERIC | 8 | N
AMOUNT | 金額 | NUMERIC | 8 | N

出貨單主檔(SALE_MASTER)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
SALE_NO | 出貨單編號 | VARCHAR | 10 | Y
SALE_DATE | 出貨單日期 | VARCHAR | 8 | N
CUST_NO | 客戶編號 | VARCHAR | 8 | N
TOTAL_AMOUNT | 出貨單總額 | NUMERIC | 8 | N

出貨單明細檔(SALE_DETAIL)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
SALE_NO | 出貨單編號 | VARCHAR | 10 | Y
SEQ_NO | 序號 | VARCHAR | 2 | Y
PROD_NO | 商品編號 | VARCHAR | 15 | N
PRICE | 單價 | NUMERIC | 8 | N
QTY | 數量 | NUMERIC | 8 | N
AMOUNT | 金額 | NUMERIC | 8 | N

月庫存記錄檔(MONTH_STOCK)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
MON | 年月 | VARCHAR | 6 | Y
PROD_NO | 商品編號 | VARCHAR | 15 | Y
QTY1 | 訂貨量 | NUMERIC | 8 | N
QTY2 | 出貨量 | NUMERIC | 8 | N
AMOUNT1 | 訂貨金額 | NUMERIC | 8 | N
AMOUNT2 | 出貨金額 | NUMERIC | 8 | N

使用者基本檔(USERS)

欄位名稱 | 中文名稱 | 欄位型態 | 欄位長度 | 鍵值
USERID | 使用者編號 | VARCHAR | 8 | Y
USERNAME | 使用者名稱 | VARCHAR | 20 | N
PWD | 密碼 | VARCHAR | 10 | N

客戶基本資料

執行畫面

程式碼

unit FmcInv01010u;

{ 程式代號：sInv01010

程式名稱：客戶基本資料

}

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

AFM2BaseFM1, AuditFlowClient, SimpRep, Menus, Db, DBClient, InfoClient,

ComCtrls, StdCtrls, Buttons, InfoBut, ExtCtrls, ButtonLP2, Grids,

AdvDBGrids, InfoCtrls, Mask, RepCommonLayout, RepLayoutComp, BatchClient;

type

TFmcInv01010 = class(TAFM2BaseForm1)

InfoDBGrid1: TInfoDBGrid;

Splitter1: TSplitter;

Panel1: TPanel;

cdsMainCUST_NO: TStringField;

cdsMainCUST_NAME: TStringField;

illCUST_NO: TInfoLabel;

idtCUST_NO: TInfoDBEdit;

illCUST_NAME: TInfoLabel;

idtCUST_NAME: TInfoDBEdit;

ListRepLayout1: TListRepLayout;

procedure btnPrintClick(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

FmcInv01010: TFmcInv01010;

implementation

{$R *.DFM}

//列印按鈕

procedure TFmcInv01010.btnPrintClick(Sender: TObject);

begin

//inherited;//移除此段->不繼承

ListRepLayout1.PrintWithDialog;

end;

initialization

RegisterClass(TFmcInv01010);//註冊類別

end.

產品基本資料

執行畫面

程式碼

unit FmcInv01020u;

{ 程式代號：sInv01020

程式名稱：產品基本資料

}

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

AFM2BaseFM1, AuditFlowClient, SimpRep, Menus, Db, DBClient, InfoClient,

ComCtrls, StdCtrls, Buttons, InfoBut, ExtCtrls, ButtonLP2, Grids,

AdvDBGrids, InfoCtrls, Mask;

type

TFmcInv01020 = class(TAFM2BaseForm1)

InfoDBGrid1: TInfoDBGrid;

Splitter1: TSplitter;

Panel1: TPanel;

cdsMainPROD_NO: TStringField;

cdsMainPROD_NAME: TStringField;

cdsMainPRICE: TFloatField;

cdsMainQTY: TFloatField;

illPROD_NO: TInfoLabel;

idtPROD_NO: TInfoDBEdit;

illPROD_NAME: TInfoLabel;

idtPROD_NAME: TInfoDBEdit;

illPRICE: TInfoLabel;

idtPRICE: TInfoDBEdit;

illQTY: TInfoLabel;

idtQTY: TInfoDBEdit;

private

{ Private declarations }

public

{ Public declarations }

end;

var

FmcInv01020: TFmcInv01020;

implementation

{$R *.DFM}

initialization

RegisterClass(TFmcInv01020);

end.

訂單資料維護

執行畫面

前端程式碼

unit FmcInv02010u;

{ 程式代號：sInv02010

程式名稱：訂單資料

}

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

AFM2BaseFM2, AuditFlowClient, SimpRep, Menus, Db, DBClient, InfoClient,

StdCtrls, Buttons, InfoBut, ButtonLP2, ExtCtrls, ComCtrls, Grids,

AdvDBGrids, InfoCtrls, Mask, RefVals, RepCommonLayout, RepLayoutComp;

type

TFmcInv02010 = class(TAFM2BaseForm2)

grdView: TInfoDBGrid;

Splitter1: TSplitter;

Panel2: TPanel;

Panel3: TPanel;

grdDetail: TInfoDBGrid;

Splitter2: TSplitter;

cdsViewORDER_NO: TStringField;

cdsViewORDER_DATE: TStringField;

cdsViewCUST_NO: TStringField;

cdsViewTOTAL_AMOUNT: TFloatField;

cdsMasterORDER_NO: TStringField;

cdsMasterORDER_DATE: TStringField;

cdsMasterCUST_NO: TStringField;

cdsMasterTOTAL_AMOUNT: TFloatField;

cdsMasterQryOrder_detail: TDataSetField;

cdsDetailORDER_NO: TStringField;

cdsDetailSEQ_NO: TStringField;

cdsDetailPROD_NO: TStringField;

cdsDetailPRICE: TFloatField;

cdsDetailQTY: TFloatField;

cdsDetailAMOUNT: TFloatField;

illORDER_NO: TInfoLabel;

idtORDER_NO: TInfoDBEdit;

illORDER_DATE: TInfoLabel;

idtORDER_DATE: TInfoDBEdit;

illCUST_NO: TInfoLabel;

idtCUST_NO: TInfoDBEdit;

illTOTAL_AMOUNT: TInfoLabel;

idtTOTAL_AMOUNT: TInfoDBEdit;

rfCustomer: TRefValComponent;

InfoDBEdit1: TInfoDBEdit;

rfDetail: TRefValComponent;

cdsSelect: TInfoClientDataSet;

dsSelect: TDataSource;

cdsDetailtotal_amount: TAggregateField;

cdsDetailmax_seqno: TAggregateField;

cdsDetailprod_name: TStringField;

ListRepLayout1: TListRepLayout;

procedure cdsMasterAfterInsert(DataSet: TDataSet);

procedure cdsMasterCUST_NOValidate(Sender: TField);

procedure cdsDetailAfterPost(DataSet: TDataSet);

procedure cdsDetailCalcFields(DataSet: TDataSet);

procedure cdsDetailAfterInsert(DataSet: TDataSet);

procedure cdsDetailAfterDelete(DataSet: TDataSet);

procedure cdsDetailPRICEChange(Sender: TField);

procedure btnMasterInsertClick(Sender: TObject);

procedure btnMasterModifyClick(Sender: TObject);

procedure btnMasterDeleteClick(Sender: TObject);

procedure btnMasterQueryClick(Sender: TObject);

procedure grdDetailEditButtonClick(Sender: TObject);

procedure cdsDetailCalcCacheDatas0CalcData(Dataset: TDataSet;

CalcDataName: String; KeyDatas: array of String; var Store: Boolean);

procedure idtCUST_NOKeyPress(Sender: TObject; var Key: Char);

procedure cdsMasterORDER_DATEValidate(Sender: TField);

procedure btnPrintClick(Sender: TObject);

private

{ Private declarations }

//刪除全部資料註記

FDeleteAll:Boolean;

//取得客戶名稱

function GetCust_name(cust_no:string):string;

//取得序號

function GetMaxSeq: String;

//計算總金額

procedure DoSumTotal;

//控制鍵值欄位

procedure EnableKeyEdit;

procedure DisableKeyEdit;

//檢查產品編號是否存在

function CheckProd_no(prod_no:string): Boolean;

public

{ Public declarations }

end;

var

FmcInv02010: TFmcInv02010;

implementation

uses DmcInv02010u,UserUtils{CallServerMethod 使用的單元},AFm2BaseFmUtils;

{$R *.DFM}

procedure TFmcInv02010.cdsMasterAfterInsert(DataSet: TDataSet);

begin

inherited;

//新增時自動給號

cdsMaster['order_no']:='自動編號';

//新增帶出訂單日期的預設值

cdsMaster['order_date']:=FormatDateTime('yyyymmdd',now);

end;

//客戶編號欄位檢查

procedure TFmcInv02010.cdsMasterCUST_NOValidate(Sender: TField);

var

vInput:Variant;

vOutput:Variant;

begin

//利用 CallServerMethod 作客戶編號是否存在的檢查

vInput:=VarArrayOf([cdsMaster.FieldByName('cust_no').AsString]);

if not CallServerMethod('sInv02010',

'CheckCustomer',

vInput) then

begin

//Raise Exception.Create('客戶編號不存在');

//將訊息寫至訊息控制區

addMemoMessage('客戶編號不存在');

abort;

end;

end;

//取得客戶名稱的函式

function TFmcInv02010.GetCust_name(cust_no: string): string;

begin

cdsSelect.Close;

cdsSelect.Sql.Clear;

cdsSelect.Sql.Add('SELECT * FROM CUSTOMER');

cdsSelect.Sql.Add('WHERE CUST_NO='''+cust_no+'''');

cdsSelect.Open;

Result:=cdsSelect.FieldByName('cust_no').AsString;

end;

procedure TFmcInv02010.cdsDetailAfterPost(DataSet: TDataSet);

var

Total_amount:integer;

bk:TBookmark;

begin

inherited;

{

//計算訂單總金額

//方法 1:使用迴圈

try

cdsDetail.DisableControls;

bk:=cdsDetail.GetBookmark;

cdsDetail.First;

Total_amount:=0;

while not cdsDetail.Eof do

begin

Total_amount:=Total_amount+cdsDetail.FieldByName('amount').AsInteger;

cdsDetail.Next;

end;

cdsMaster.Edit;

cdsMaster['Total_amount']:=Total_amount;

cdsMaster.Post;

finally

cdsDetail.EnableControls;

cdsDetail.GotoBookmark(bk);

cdsDetail.FreeBookmark(bk);

end;

}

//計算訂單總金額

//方法 2:使用加總欄位

DoSumTotal;

end;

procedure TFmcInv02010.cdsDetailCalcFields(DataSet: TDataSet);

begin

inherited;

//.........

cdsDetail.CalcCacheDatas.GetCalcData('ccProd_name',

[cdsDetail.FieldByName('prod_no').AsString]);

end;

//取得序號,使用加總欄位的用法

function TFmcInv02010.GetMaxSeq: String;

var

FSeq:string;

begin

FSeq:=cdsDetail.fieldbyname('max_seqno').asstring;

if FSeq='' then FSeq:='0';

Result:=Format('%.2d',[StrToInt(FSeq)+1]);

end;

//計算明細檔總金額

procedure TFmcInv02010.DoSumTotal;

var

Total_Amount:integer;

begin

//取得金額合計

try

Total_Amount:=StrToInt(cdsDetail.Fieldbyname('Total_Amount').AsString);

except

Total_Amount:=0;

end;

//合計金額

cdsMaster.Edit;

cdsMaster['Total_Amount']:=Total_Amount;

// cdsMaster.Post; //影響 Detail 的移動

end;

//給預設值

procedure TFmcInv02010.cdsDetailAfterInsert(DataSet: TDataSet);

begin

inherited;

cdsDetail['seq_no']:=GetMaxSeq;

end;

procedure TFmcInv02010.cdsDetailAfterDelete(DataSet: TDataSet);

begin

inherited;

if FDeleteAll then Exit;

DoSumTotal;

end;

//利用欄位改變來取得金額=單價*數量

procedure TFmcInv02010.cdsDetailPRICEChange(Sender: TField);

begin

inherited;

cdsDetail['amount']:=cdsDetail.FieldByName('price').AsInteger *

cdsDetail.FieldByName('qty').AsInteger;

end;

procedure TFmcInv02010.btnMasterInsertClick(Sender: TObject);

begin

inherited;

FDeleteAll:=False;

DisableKeyEdit;

end;

procedure TFmcInv02010.btnMasterModifyClick(Sender: TObject);

begin

inherited;

FDeleteAll:=False;

DisableKeyEdit;

end;

procedure TFmcInv02010.btnMasterDeleteClick(Sender: TObject);

begin

inherited;

FDeleteAll:=True;

end;

procedure TFmcInv02010.DisableKeyEdit;

begin

idtORDER_NO.Enabled:=False;

idtORDER_NO.Color:=clBtnFace;

idtORDER_NO.ReadOnly:=True;

end;

procedure TFmcInv02010.EnableKeyEdit;

begin

idtORDER_NO.Enabled:=True;

idtORDER_NO.Color:=clWindow;

idtORDER_NO.ReadOnly:=False;

end;

//按下查詢

procedure TFmcInv02010.btnMasterQueryClick(Sender: TObject);

begin

inherited;

EnableKeyEdit;

end;

procedure TFmcInv02010.grdDetailEditButtonClick(Sender: TObject);

begin

inherited;

//取得開窗的資料

if grdDetail.SelectedField.FieldName='PROD_NO' then

begin

if not DoDbGridButtonClick(Sender) then Exit;

end;

end;

//使用 CacheCalculdatedField 機制

procedure TFmcInv02010.cdsDetailCalcCacheDatas0CalcData(Dataset: TDataSet;

CalcDataName: String; KeyDatas: array of String; var Store: Boolean);

begin

inherited;

//取得產品名稱

cdsSelect.Close;

cdsSelect.Sql.Clear;

cdsSelect.Sql.Add('SELECT PROD_NAME FROM PRODUCT');

cdsSelect.Sql.Add('WHERE PROD_NO='''+cdsDetail.FieldByName('PROD_NO').AsString+'''');

cdsSelect.Open;

cdsDetail['prod_name']:=cdsSelect.FieldByName('PROD_NAME').AsString;

end;

//將游標移至 Grid

procedure TFmcInv02010.idtCUST_NOKeyPress(Sender: TObject; var Key: Char);

begin

inherited;

if key=#13 then

begin

grdDetail.SelectedIndex:=1;

grdDetail.SetFocus;

end;

end;

//將 yyyymmdd 字串格式轉成 日期格式

function StringToDate(sDate:String):TDateTime;

var

yyyy,mm,dd:word;

begin

yyyy:=StrToInt(Copy(sDate,1,4));

mm:=StrToInt(Copy(sDate,5,2));

dd:=StrToInt(Copy(sDate,7,2));

Result:=EncodeDate(yyyy,mm,dd);

end;

//檢查日期是否正確(yyyymmdd)

function ChkDate(sDate:String):Boolean;

begin

Result:=True;

try

StringToDate(sDate);

except

Result:=False;

end;

end;

//日期檢查

procedure TFmcInv02010.cdsMasterORDER_DATEValidate(Sender: TField);

begin

inherited;

if not ChkDate(Sender.AsString) then

begin

AddMemoMessage('日期輸入錯誤');

Sender.FocusControl;

Abort;

end;

end;

//清冊列印

procedure TFmcInv02010.btnPrintClick(Sender: TObject);

begin

//inherited;

ListRepLayout1.PrintWithDialog;

end;

//檢查產品編號是否存在

function TFmcInv02010.CheckProd_no(prod_no:string):Boolean;

var

cdsProduct:TInfoClientDataSet;

begin

try

//動態產生一個 InfoClientDataSet 元件

cdsProduct:=TInfoClientDataSet.Create(Self);

//設定資料提供者

cdsProduct.ProviderName:='GLModule.qrySelect';

//寫 SQL

cdsProduct.Sql.Add('SELECT PROD_NAME FROM PRODUCT');

cdsProduct.Sql.Add('WHERE PROD_NO='''+prod_no+'''');

//開啟資料表

cdsProduct.Open;

//判斷有無資料

Result:=not cdsProduct.Eof;

finally

//釋放元件

cdsProduct.Free;

end;

end;

initialization

RegisterClass(TFmcInv02010);

end.

後端程式碼

unit DmsInv02010u;

{ 程式代號：sInv02010

程式名稱：訂單資料維護

}

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

Db, DBTables, UpdateComp, AutoNumber, BatchComp;

type

TsInv02010_SEL = class(TDataModule)

Database1: TDatabase;

QryOrder_master: TQuery;

dsOrder_master: TDataSource;

QryOrder_detail: TQuery;

ServiceManager1: TServiceManager;

ucOrder_master: TUpdateComponent;

ucOrder_detail: TUpdateComponent;

AutoNumber1: TAutoNumber;

Qry: TQuery;

BatchProcess: TBatchProcess;

BatchLoop: TBatchLoop;

Qry1: TQuery;

Query1: TQuery;

BatchProcess1: TBatchProcess;

procedure ucOrder_masterBeforeInsert2(Sender: TUpdateComponent;

Params: TParams; ParamObj: TParamObject);

procedure GetDateCallMethod(Params: Variant; out ReturnVal: Variant);

procedure CheckCustomerCallMethod(Params: Variant;

out ReturnVal: Variant);

procedure ucOrder_detailAfterModify2(Sender: TUpdateComponent;

SQL: String; Params: TParams; ParamObj: TParamObject);

procedure CheckCustomer1CallMethod(Params: Variant;

out ReturnVal: Variant);

procedure ucOrder_detailTransactions1GetKeyValue(

TransKeyField: TTransKeyField; UpdateType: TUpdateType;

Params: TParams; ParamObj: TParamObject; var KeyValue: Variant);

procedure BatchLoopLoop(BatchLoop: TBatchLoop; DataSet: TDataSet;

Params: Variant; var ReturnVal: Variant; ParamObj: TParamObject);

procedure BatchProcessCallMethod(BatchProcess: TBatchProcess;

Params: Variant; var ReturnVal: Variant; ParamObj: TParamObject);

procedure PostStockCallMethod(Params: Variant; out ReturnVal: Variant);

private

{ Private declarations }

public

{ Public declarations }

end;

var

sInv02010_SEL: TsInv02010_SEL;

implementation

{$R *.DFM}

uses CommonUtils;

//後端自動編號寫法

procedure TsInv02010_SEL.ucOrder_masterBeforeInsert2(

Sender: TUpdateComponent; Params: TParams; ParamObj: TParamObject);

var

order_no:string;

order_date:string;

begin

//取得訂單日期

order_date:=Params.ParamByName('order_date').AsString;

order_date:=Copy(order_date,1,6);

Params.ParamByName('order_no').AsString:=

AutoNumber1.GetNewAutoNum('NUMBERNAME;CURRENTPREFIX',

VarArrayof(['訂單',order_date]));

{

//自動編號

Params.ParamByName('o1odno').AsString:=

AutoNumber1.GetNewAutoNum('NUMBERNAME;CURRENTPREFIX',

VarArrayof([fs_NUMBERNAME,fs_CURRENTPREFIX]));

//AutoNumber.GetCurrentAutoNum;

}

end;

//CallServerMethod

procedure TsInv02010_SEL.GetDateCallMethod(Params: Variant;

out ReturnVal: Variant);

begin

ReturnVal:=FormatDateTime('yyyymmdd',Now);

end;

//CallServerMethod

procedure TsInv02010_SEL.CheckCustomerCallMethod(Params: Variant;

out ReturnVal: Variant);

var

cust_no:string;

begin

//取得前端傳入參數值

cust_no:=Params[0];

ReturnVal:=True;

Qry.close;

Qry.Sql.Clear;

Qry.Sql.Add('SELECT CUST_NO');

Qry.Sql.Add('FROM CUSTOMER');

Qry.Sql.Add('WHERE CUST_NO='''+cust_no+'''');

Qry.Open;

if Qry.Eof then

begin

ReturnVal:=False;

end;

//ReturnVal:=VarArrayOf(['1','2','3']);

end;

//動態產生 TQuery 的用法

procedure TsInv02010_SEL.CheckCustomer1CallMethod(Params: Variant;

out ReturnVal: Variant);

var

cust_no:string;

aQuery:TQuery;

begin

cust_no:=Params[0];

ReturnVal:=True;

try

ServiceManager1.CreateQuery(Database1.AliasName, aQuery);

aQuery.close;

aQuery.Sql.Clear;

aQuery.Sql.Add('SELECT CUST_NO');

aQuery.Sql.Add('FROM CUSTOMER');

aQuery.Sql.Add('WHERE CUST_NO='''+cust_no+'''');

aQuery.Open;

if aQuery.Eof then

begin

ReturnVal:=False;

end;

finally

aQuery.Free;

end;

end;

//取得新值及舊值(請 uses CommonUtils)

procedure TsInv02010_SEL.ucOrder_detailAfterModify2(

Sender: TUpdateComponent; SQL: String; Params: TParams;

ParamObj: TParamObject);

var

prod_no,old_prod_no:string;

qty,old_qty:integer;

begin

{

//取得產品編號舊值

if not VarIsNull(GetFieldOldValue(Params,'prod_no')) then

begin

old_prod_no:=GetFieldOldValue(Params,'prod_no');

end;

//取數量舊值

old_qty:=GetFieldOldValue(Params,'qty');

//取得產品編號

if not VarIsNull(GetFieldCurrentValue(Params,'prod_no')) then

begin

prod_no:=GetFieldCurrentValue(Params,'prod_no');

end;

//取得數量

qty:=GetFieldCurrentValue(Params,'qty');

}

end;

procedure TsInv02010_SEL.ucOrder_detailTransactions1GetKeyValue(

TransKeyField: TTransKeyField; UpdateType: TUpdateType; Params: TParams;

ParamObj: TParamObject; var KeyValue: Variant);

begin

//當欄位的值無法從該來源檔取得的處理

//從來源檔取值:取得訂單明細檔的訂單單號

{

if CompareText(TransKeyField.SrcField,'ORDER_NO') = 0 then

KeyValue :=Copy(GetFieldCurrentValue(Params,'ORDER_NO'),1,6);

}

end;

procedure TsInv02010_SEL.BatchLoopLoop(BatchLoop: TBatchLoop;

DataSet: TDataSet; Params: Variant; var ReturnVal: Variant;

ParamObj: TParamObject);

begin

Raise Exception.Create(DataSet.FieldByName('ORDER_NO').AsString+'錯誤!');

end;

procedure TsInv02010_SEL.BatchProcessCallMethod(

BatchProcess: TBatchProcess; Params: Variant; var ReturnVal: Variant;

ParamObj: TParamObject);

begin

BatchLoop.Run(nil,Params,ReturnVal,ParamObj);

end;

//利用 BatchProcess 來作 Transaction 的控管

procedure TsInv02010_SEL.PostStockCallMethod(Params: Variant;

out ReturnVal: Variant);

var

Mon:string;

Prod_no:string;

Qty:integer;

begin

//取得月份

Mon:=Params[0];

//取得作業資料的範圍

Qry.Close;

Qry.SQL.Add('SELECT *');

Qry.SQL.Add('FROM ORDER_DETAIL');

Qry.SQL.Add('WHERE ORDER_NO >='''+Mon+'01'+'''');

Qry.SQL.Add('AND ORDER_NO <='''+Mon+'31'+'''');

Qry.Open;

//從第一筆作到最後一筆

while not Qry.Eof do

begin

Prod_no:=Qry.FieldByName('prod_no').AsString;

Qty:=Qry.FieldByName('qty').AsInteger;

Qry1.Close;

Qry1.Sql.Clear;

Qry1.Sql.Add('SELECT PROD_NO');

Qry1.Sql.Add('FROM MON_STOCK');

Qry1.Sql.Add('WHERE MON='''+Mon+'''');

Qry1.Sql.Add('AND PROD_NO='''+Prod_no+'''');

Qry1.Open;

if Qry1.Eof then

begin

Qry1.Close;

Qry1.Sql.Clear;

Qry1.Sql.Add('Insert...............');

Qry1.ExecSql;

end

else

begin

Qry1.Close;

Qry1.Sql.Clear;

Qry1.Sql.Add('Update.....................');

Qry1.ExecSql;

end;

Qry.Next;

end;

ReturnVal:=VarArrayOf(['1','作業完成','筆數:']);

end;

initialization

RegisterClass(TsInv02010_SEL);

end.

訂單查詢作業（報表）

設計畫面

查詢畫面程式碼

unit FmcInv04010u;

{ 程式代號：sInv04010

程式名稱：訂單查詢

}

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

AFM2BaseFM3, StdCtrls, ComCtrls, ExtCtrls, Grids, DBGrids, Db, DBClient,

InfoClient, RepClient;

type

TFmcInv04010 = class(TAFM2BaseForm3)

Panel1: TPanel;

Splitter1: TSplitter;

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

Label4: TLabel;

etOrder1: TEdit;

etOrder2: TEdit;

Button1: TButton;

Button2: TButton;

etCust1: TEdit;

etCust2: TEdit;

dsSelect: TDataSource;

cdsSelect: TInfoClientDataSet;

DBGrid1: TDBGrid;

RepClient1: TRepClient;

procedure Button1Click(Sender: TObject);

procedure FormCreate(Sender: TObject);

procedure Button2Click(Sender: TObject);

private

{ Private declarations }

Gs_user:String;

public

{ Public declarations }

end;

var

FmcInv04010: TFmcInv04010;

implementation

uses DmcInv04010u,RPcInv04010u,UserUtils;

{$R *.DFM}

procedure TFmcInv04010.Button1Click(Sender: TObject);

begin

inherited;

//組合 SQL 語法

cdsSelect.Close;

cdsSelect.Sql.Clear;

cdsSelect.Sql.Add('select m.*,d.*,c.cust_name,p.prod_name');

cdsSelect.Sql.Add('from order_master m,order_detail d, customer c, product p');

cdsSelect.Sql.Add('where m.order_no=d.order_no');

cdsSelect.Sql.Add('and c.cust_no=m.cust_no');

cdsSelect.Sql.Add('and p.prod_no=d.prod_no');

if (etOrder1.text <> '') and (etOrder2.text <> '') then

begin

cdsSelect.Sql.Add('and m.order_no >= '''+etOrder1.text+'''');

cdsSelect.Sql.Add('and m.order_no <= '''+etOrder2.text+'''');

end;

if (etCust1.text <> '') and (etCust2.text <> '') then

begin

cdsSelect.Sql.Add('and m.Cust_no >= '''+etCust1.text+'''');

cdsSelect.Sql.Add('and m.Cust_no <= '''+etCust2.text+'''');

end;

cdsSelect.Sql.Add('order by m.cust_no');

{

//將 SQL 語法傳給報表

sqlScript:=cdsSelect.sql.text;

RepClient1.Preview(null,

VarArrayOf(['sql','user']),

VarArrayOf([SqlScript,Gs_user]));

}

cdsSelect.Open;

{

//傳統開啟報表畫面的寫法

try

RPcInv04010:=TRPcInv04010.Create(Application);

RPcInv04010.QuickRep1.Preview;

finally

RPcInv04010.Free;

end;

}

end;

procedure TFmcInv04010.FormCreate(Sender: TObject);

var

aUserID:Variant;

begin

inherited;

//取得使用者編號

GetVariantProp('UserID', aUserID);

Gs_User:=VarToStr(aUserID);

end;

procedure TFmcInv04010.Button2Click(Sender: TObject);

begin

inherited;

//將查詢的資料傳給報表

RepClient1.Preview(cdsSelect.Data,

null,

null);

end;

Initialization

RegisterClass(TFmcInv04010);

end.

報表程式碼

unit RPcInv04010u;

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

Qrctrls, QuickRpt, ExtCtrls, Db, DBClient, InfoClient, ReportComp;

type

TRPcInv04010 = class(TForm)

QuickRep1: TQuickRep;

QRBand1: TQRBand;

QRLabel6: TQRLabel;

QRBand2: TQRBand;

QRDBText3: TQRDBText;

QRDBText4: TQRDBText;

QRDBText5: TQRDBText;

QRGroup1: TQRGroup;

QRLabel2: TQRLabel;

QRLabel3: TQRLabel;

QRDBText1: TQRDBText;

QRDBText2: TQRDBText;

QRLabel4: TQRLabel;

QRLabel5: TQRLabel;

QRLabel1: TQRLabel;

QRBand3: TQRBand;

QRExpr1: TQRExpr;

QRBand4: TQRBand;

QRExpr2: TQRExpr;

QRBand5: TQRBand;

QRLabel7: TQRLabel;

ReportAssistant1: TReportAssistant;

cdsPrint: TInfoClientDataSet;

procedure ReportAssistant1SetCondition(Conditions: TConditions);

procedure ReportAssistant1SetClientDataSetData(Sender: TObject;

Data: Variant);

private

{ Private declarations }

public

{ Public declarations }

end;

var

RPcInv04010: TRPcInv04010;

implementation

{$R *.DFM}

procedure TRPcInv04010.ReportAssistant1SetCondition(

Conditions: TConditions);

{

var

FCondition:TCondition;

sqlScript:string;

user:string;

}

begin

//取sql語法

{

FCondition:=Conditions.findCondition('sql');

SqlScript:=FCondition.value;

cdsPrint.SQL.Text:=SqlScript;

//取user id

FCondition:=Conditions.findCondition('user');

user:=FCondition.value;

}

end;

procedure TRPcInv04010.ReportAssistant1SetClientDataSetData(

Sender: TObject; Data: Variant);

begin

cdsPrint.Data:=Data;

end;

Initialization

RegisterClass(TRPcInv04010);

end.


出貨系統範例

出貨系統範例-產生資料表語法

CREATE TABLE [dbo].[CUSTOMER] (
[CUST_NO] [varchar] (8) NOT NULL ,
[CUST_NAME] [varchar] (20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ORDER_DETAIL] (
[ORDER_NO] [varchar] (10) NOT NULL ,
[SEQ_NO] [varchar] (2) NOT NULL ,
[PROD_NO] [varchar] (15) NULL ,
[PRICE] [numeric](8, 0) NULL ,
[QTY] [numeric](8, 0) NULL ,
[AMOUNT] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ORDER_MASTER] (
[ORDER_NO] [varchar] (10) NOT NULL ,
[ORDER_DATE] [varchar] (8) NULL ,
[CUST_NO] [varchar] (8) NULL ,
[TOTAL_AMOUNT] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PRODUCT] (
[PROD_NO] [varchar] (15) NOT NULL ,
[PROD_NAME] [varchar] (20) NULL ,
[PRICE] [numeric](8, 0) NULL ,
[QTY] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MONTH_STOCK] (
[MON] [varchar] (6) NOT NULL ,
[PROD_NO] [varchar] (15) NULL ,
[QTY1] [numeric](8, 0) NULL ,
[QTY2] [numeric](8, 0) NULL ,
[AMOUNT1] [numeric](8, 0) NULL ,
[AMOUNT2] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SALE_MASTER] (
[SALE_NO] [varchar] (10) NOT NULL ,
[SALE_DATE] [varchar] (8) NOT NULL ,
[CUST_NO] [varchar] (8) NULL ,
[TOTAL_AMOUNT] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SALE_DETAIL] (
[SALE_NO] [varchar] (10) NOT NULL ,
[SEQ_NO] [varchar] (2) NOT NULL ,
[PROD_NO] [varchar] (15) NULL ,
[PRICE] [numeric](8, 0) NULL ,
[QTY] [numeric](8, 0) NULL ,
[AMOUNT] [numeric](8, 0) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY NONCLUSTERED
(
[CUST_NO]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ORDER_DETAIL] WITH NOCHECK ADD
CONSTRAINT [DF_ORDER_DETAIL_PRICE] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF_ORDER_DETAIL_QTY] DEFAULT (0) FOR [QTY],
CONSTRAINT [PK_ORDER_DETAIL] PRIMARY KEY NONCLUSTERED
(
[ORDER_NO],
[SEQ_NO]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ORDER_MASTER] WITH NOCHECK ADD
CONSTRAINT [DF_ORDER_MASTER_TOTAL_AMOUNT] DEFAULT (0) FOR [TOTAL_AMOUNT],
CONSTRAINT [PK_ORDER_MASTER] PRIMARY KEY NONCLUSTERED
(
[ORDER_NO]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PRODUCT] WITH NOCHECK ADD
CONSTRAINT [DF_PRODUCT_PRICE] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF_PRODUCT_QYT] DEFAULT (0) FOR [QTY],
CONSTRAINT [PK_PRODUCT] PRIMARY KEY NONCLUSTERED
(
[PROD_NO]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MONTH_STOCK] WITH NOCHECK ADD
CONSTRAINT [DF_MONTH_STOCK_QTY1] DEFAULT (0) FOR [QTY1],
CONSTRAINT [DF_MONTH_STOCK_QTY2] DEFAULT (0) FOR [QTY2],
CONSTRAINT [DF_MONTH_STOCK_AMOUNT1] DEFAULT (0) FOR [AMOUNT1],
CONSTRAINT [DF_MONTH_STOCK_AMOUNT2] DEFAULT (0) FOR [AMOUNT2]
GO

ALTER TABLE [dbo].[SALE_MASTER] WITH NOCHECK ADD
CONSTRAINT [DF_SALE_MASTER_TOTAL_AMOUNT] DEFAULT (0) FOR [TOTAL_AMOUNT],
CONSTRAINT [PK_SALE_MASTER] PRIMARY KEY NONCLUSTERED
(
[SALE_NO]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SALE_DETAIL] WITH NOCHECK ADD
CONSTRAINT [DF_SALE_DETAIL_PRICE] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF_SALE_DETAIL_QTY] DEFAULT (0) FOR [QTY],
CONSTRAINT [DF_SALE_DETAIL_AMOUNT] DEFAULT (0) FOR [AMOUNT],
CONSTRAINT [PK_SALE_DETAIL] PRIMARY KEY NONCLUSTERED
(
[SALE_NO],
[SEQ_NO]
) ON [PRIMARY]
GO


出貨系統範例資料字典新增語法

INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('CUSTOMER','CUST_NAME','CHAR','N',20,'客戶名稱','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('CUSTOMER','CUST_NO','CHAR','Y',8,'客戶編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','AMOUNT1','DOUBLE','N',8,'訂貨金額','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','AMOUNT2','DOUBLE','N',8,'出貨金額','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','MON','CHAR','N',6,'年月','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','PROD_NO','CHAR','Y',15,'商品編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','QTY1','DOUBLE','N',8,'訂貨量','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('MONTH_STOCK','QTY2','DOUBLE','N',8,'出貨量','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','AMOUNT','DOUBLE','N',8,'金額','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','ORDER_NO','CHAR','Y',10,'訂單編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','PRICE','DOUBLE','N',8,'單價','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','PROD_NO','CHAR','N',15,'商品編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','QTY','DOUBLE','N',8,'數量','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_DETAIL','SEQ_NO','CHAR','Y',2,'序號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_MASTER','CUST_NO','CHAR','N',8,'客戶編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_MASTER','ORDER_DATE','CHAR','N',8,'訂單日期','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_MASTER','ORDER_NO','CHAR','Y',10,'訂單編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('ORDER_MASTER','TOTAL_AMOUNT','DOUBLE','N',8,'訂單總額','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('PRODUCT','PRICE','DOUBLE','N',8,'單價','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('PRODUCT','PROD_NAME','CHAR','N',20,'商品名稱','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('PRODUCT','PROD_NO','CHAR','Y',15,'商品編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('PRODUCT','QTY','CHAR','N',8,'現有量','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','AMOUNT','DOUBLE','N',8,'金額','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','PRICE','DOUBLE','N',8,'單價','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','PROD_NO','CHAR','N',15,'商品編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','QTY','DOUBLE','N',8,'數量','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','SALE_NO','CHAR','Y',10,'出貨單編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_DETAIL','SEQ_NO','CHAR','Y',2,'序號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_MASTER','CUST_NO','CHAR','N',8,'客戶編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_MASTER','SALE_DATE','CHAR','N',8,'出貨單日期','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_MASTER','SALE_NO','CHAR','Y',10,'出貨單編號','','','','')
INSERT INTO COLDEF (TABLE_NAME,FIELD_NAME,FIELD_TYPE,IS_KEY,FIELD_LENGTH,CAPTION,EDITMASK,NEEDBOX,CANREPORT,EXT_MENUID) VALUES ('SALE_MASTER','TOTAL_AMOUNT','DOUBLE','N',8,'訂單總額','','','','')


出貨系統範例-選單權限資料語法

INSERT INTO MENUITEMTYPE (ITEMTYPE,ITEMNAME) VALUES ('INV','出貨系統')

INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV01010','客戶基本資料','INV01','CINV01010','TDMCINV01010','','FMCINV01010','N','','INV','0101')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV01020','產品基本資料','INV01','CINV01020','TDMCINV01020','','FMCINV01020','N','','INV','0102')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV02010','訂單資料維護','INV02','CINV02010','TDMCINV02010','','FMCINV02010','N','','INV','0201')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV02020','出貨單資料維護','INV02','CINV02020','TDMCINV02020','','FMCINV02020','N','','INV','0202')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV03010','庫存重新計算','INV03','CINV03010','TDMCINV03010','','FMCINV03010','N','','INV','0301')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV04010','訂單查詢作業','INV04','CINV04010','TDMCINV04010','','FMCINV04010','N','','INV','0401')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('CINV04020','出貨單查詢作業','INV04','CINV04020','TDMCINV04020','','FMCINV04020','N','','INV','0402')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('INV01','基本資料維護','','','','','','N','','INV','01')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('INV02','每日作業維護','','','','','','N','','INV','02')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('INV03','每月作業維護','','','','','','N','','INV','03')
INSERT INTO MENUTABLE (MENUID,CAPTION,PARENT,PACKAGE,PACKAGECLA,ITEMPARAM,FORM,ISSHOWMODAL,FUNCTIONS,ITEMTYPE,SEQ_NO) VALUES ('INV04','報表查詢作業','','','','','','N','','INV','04')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV01010','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV01020','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV02010','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV02020','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV03010','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV04010','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','CINV04020','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','INV01','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','INV02','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','INV03','Y')
INSERT INTO USERMENUS (USERID,MENUID,ENABLED) VALUES ('EEP','INV04','Y')


批次作業語法

SELECT M.ORDER_DATE, M.CUST_NO, M.ORDER_NO,
D.SEQ_NO,D.PROD_NO, D.PRICE, D.QTY, D.AMOUNT
FROM ORDER_MASTER M, ORDER_DETAIL D
WHERE M.ORDER_NO = D.ORDER_NO
AND M.ORDER_DATE >= :MON1
AND M.ORDER_DATE <= :MON2


統計報表語法

SELECT C.CUST_NAME,P.PROD_NAME,D.QTY
FROM ORDER_MASTER M,ORDER_DETAIL D,PRODUCT P,CUSTOMER C
WHERE M.ORDER_NO=D.ORDER_NO
AND M.CUST_NO=C.CUST_NO
AND D.PROD_NO=P.PROD_NO


群組報表語法

SELECT M.ORDER_NO,M.ORDER_DATE,M.CUST_NO,C.CUST_NAME,M.TOTAL_AMOUNT,
D.SEQ_NO,D.PROD_NO,P.PROD_NAME,D.PRICE,D.QTY,D.AMOUNT
FROM ORDER_MASTER M,ORDER_DETAIL D,PRODUCT P,CUSTOMER C
WHERE M.ORDER_NO=D.ORDER_NO
AND M.CUST_NO=C.CUST_NO
AND D.PROD_NO=P.PROD_NO


Schedule範例參考

procedure TsInv03010_SEL.TestScheduleCallMethod(Params: Variant;
out ReturnVal: Variant);
var
cust_no:string;
begin
cust_no:=Params[0];
QrySelect.Close;
QrySelect.Sql.Clear;
QrySelect.Sql.Add('INSERT INTO CUSTOMER(CUST_NO,CUST_NAME)');
QrySelect.Sql.Add('VALUES(:CUST_NO,:CUST_NAME)');
QrySelect.ParamByName('CUST_NO').AsString:=Cust_no+FormatDateTime('hhnnss',Now);
QrySelect.ParamByName('CUST_name').AsString:='Cus_'+FormatDateTime('hhnnss',Now);
QrySelect.ExecSQL;
end;


Method練習1

[Client]

procedure TFmcInv02010.cdsMasterCUST_NOValidate(Sender: TField);

var

vInput,vOutput:Variant;

bFlag:Boolean;

Cust_name:string;

begin

inherited;

vInput:=VarArrayOf([cdsMasterCUST_NO.AsString]);

vOutput:=CallServerMethod('sInv02010',

'FindCust',

vInput);

if not vOutput[0] then

begin

Raise Exception.Create('客戶編號不存在');

end;

Cust_name:=vOutput[1];

{if not CallServerMethod('sInv02010',

'FindCust',

VarArrayOf([cdsMasterCUST_NO.AsString])) then

begin

Raise Exception.Create('客戶編號不存在');

end;

}

end;

[ApSrv]

procedure TsInv02010_SEL.FindCustCallMethod(Params: Variant;

out ReturnVal: Variant);

var

Cust_no:string;

Order_date:string;

begin

Cust_no:=Params[0];

//Order_date:=Params[1];

QrySelect.Close;

QrySelect.Sql.Clear;

QrySelect.Sql.Add('SELECT CUST_NO,CUST_NAME');

QrySelect.Sql.Add('FROM CUSTOMER');

// QrySelect.Sql.Add('WHERE CUST_NO='''+Cust_no+'''');

QrySelect.Sql.Add('WHERE CUST_NO='+QuotedStr(Cust_no));

QrySelect.Open;

ReturnVal:=VarArrayOf([not QrySelect.Eof,

QrySelect.FieldByName('cust_name').AsString]);

{

ReturnVal:=False;

if QrySelect.RecordCount > 0 then

begin

ReturnVal:=True;

end;

}

end;


Method練習2

[Server]

procedure TsInv02010_SEL.GetProductCallMethod(Params: Variant;

out ReturnVal: Variant);

var

prod_no:string;

begin

prod_no:=Params[0];

QrySelect.Close;

QrySelect.Sql.Clear;

QrySelect.Sql.Add('SELECT PROD_NAME,PRICE,QTY');

QrySelect.Sql.Add('FROM PRODUCT');

QrySelect.Sql.Add('WHERE PROD_NO='+QuotedStr(PROD_NO));

QrySelect.Open;

ReturnVal:=VarArrayOf([not QrySelect.Eof,

QrySelect.FieldByName('PROD_NAME').AsString,

QrySelect.FieldByName('PRICE').AsInteger,

QrySelect.FieldByName('Qty').AsInteger]);

end;

[Client]

procedure TFmcInv02010.cdsDetailPROD_NOChange(Sender: TField);

var

vInput,vOutput:Variant;

begin

inherited;

vInput:=VarArrayOf([Sender.AsString]);

vOutput:=CallServerMethod('sInv02010','GetProduct',vInput);

cdsDetail['price']:=vOutput[2];

end;

procedure TFmcInv02010.cdsDetailPROD_NOValidate(Sender: TField);

var

vInput,vOutput:Variant;

begin

inherited;

vInput:=VarArrayOf([Sender.AsString]);

vOutput:=CallServerMethod('sInv02010','GetProduct',vInput);

if not vOutput[0] then

begin

Raise Exception.Create('not found');

end;

end;