wsdb.pas

Workflow Studio是一款专为商业进程管理（BPM）设计的Delphi VCL框架。通过Workflow Studio你可以轻易地将工作流与BPM功能添加到你的应用程序里。这样能使你或你的最

    {Insert attachment unique key}
    (*SQL := Format('INSERT INTO %s (%s%s, %s, %s, %s) Values (%s:workkey, :createdon, :objecttype, :strid)',
      [TableName, SQLKeyField(KeyField), WorkKeyField, CreatedOnField, ObjectTypeField, StrIdField, SQLKeyParam(KeyField)]);
    FParams.Clear;
    //if not FAutoIncFields then
      FParams.CreateParam(ftInteger, KeyField, ptInput).AsInteger := FindNextID(TableName, KeyField);
    FParams.CreateParam(ftInteger, 'workkey', ptInput).AsInteger := StrToInt(AWorkKey);
    FParams.CreateParam(ftDateTime, 'createdon', ptInput).AsDateTime := CreatedOn;
    FParams.CreateParam(ftInteger, 'objecttype', ptInput).AsInteger := Ord(AType);
    StrId := AItem.BuildStrId;
    FParams.CreateParam(ftString, 'strid', ptInput).AsString := StrId;
    ExecuteQuery(SQL, FParams);

    {retrieve the generated key}
    SQL := Format('SELECT %s FROM %s WHERE %s = :workkey AND %s = :createdon and %s = :objecttype and %s = :strid',
      [KeyField, TableName, WorkKeyField, CreatedOnField, ObjectTypeField, StrIdField]);
    DS := OpenQuery(SQL, FParams);
    try
      AItem.Key := DS.FieldByName(KeyField).AsString;
      if AItem.Key = '' then
        wsDBError(Format(_str(SErrorInsertCannotGetKey), [TableName]));
    finally
      DS.Close;
      if DestroyQueries then
        DS.Free;
    end;*)

    AItem.Key := IntToStr(FindNextID(TableName, KeyField));
    SQL := Format('INSERT INTO %s (%s, %s) Values (:id, :createdon)',
      [TableName, KeyField, CreatedOnField]);
    FParams.Clear;
    FParams.CreateParam(ftInteger, 'id', ptInput).AsInteger := StrToInt(AItem.Key);
    FParams.CreateParam(ftDateTime, 'createdon', ptInput).AsDateTime := CreatedOn;
    ExecuteQuery(SQL, FParams);

    {Save other attachment fields}
    AttachmentItemUpdate(AItem, AType, AWorkKey);
  end;
end;

procedure TCustomWorkflowDB.AttachmentItemUpdate(AItem: TAttachmentItem;
  AType: TAttachmentParentType; AWorkKey: string);
var
  SQL: string;
begin
  CheckDB;
  With AttachmentBindary do
  begin
    if AItem.Key <> '' then
    begin
      SQL := Format('UPDATE %s SET '+
        '%s = :objecttype, '+
        '%s = :workkey, '+
        '%s = :content '+
        'WHERE %s = :id',
        [TableName, ObjectTypeField, WorkKeyField, ContentField, KeyField]);
      FParams.Clear;
      FParams.CreateParam(ftInteger, 'id', ptInput).AsInteger := StrToInt(AItem.Key);
      FParams.CreateParam(ftInteger, 'workkey', ptInput).AsInteger := StrToInt(AWorkKey);
      FParams.CreateParam(ftInteger, 'objecttype', ptInput).AsInteger := Ord(AType);


      SetBlobParam(FParams.CreateParam(ftBlob, 'content', ptInput), AItem.Content);

      ExecuteQuery(SQL, FParams);
    end else
      wsDBError(Format(_str(SErrorUpdateEmptyKey), [TableName]));
  end;
end;

function TCustomWorkflowDB.AttachmentItemLoad(AItem: TAttachmentItem): boolean;
var
  SQL: string;
  DS: TDataset;
begin
  result := false;
  if Trim(AItem.Key) = '' then
    exit;

  CheckDB;
  SQL := Format('SELECT * from %s WHERE %s = :id',
    [AttachmentBindary.TableName, AttachmentBindary.KeyField]);
  FParams.Clear;
  FParams.CreateParam(ftInteger, 'id', ptInput).AsInteger := StrToInt(AItem.Key);
  DS := OpenQuery(SQL, FParams);
  try
    if not DS.IsEmpty then
    begin
      AttachmentItemLoadRecord(DS, AItem);
      result := true;
    end;
  finally
    DS.Close;
    if DestroyQueries then
      DS.Free;
  end;
end;

procedure TCustomWorkflowDB.AttachmentItemLoadRecord(DS: TDataset;
  AItem: TAttachmentItem);
begin
  With AttachmentBindary do
  begin
    AItem.Key := DS.FieldByName(KeyField).AsString;
    AItem.Content := DS.FieldByName(ContentField).AsString;
    AItem.DirtyContent := false;
  end;
end;

procedure TCustomWorkflowDB.SetBlobParam(AParam: TParam; BlobStream: string);
var
  SS: TStringStream;
begin
  SS := TStringStream.Create(BlobStream);
  try
    SS.Position := 0;
    AParam.LoadFromStream(SS, ftBlob);
  finally
    SS.Free;
  end;
end;

function TCustomWorkflowDB.MapOperationToStr(AOperation: TTaskLogOperation): string;
begin
  Case AOperation of
    tlStatusChange: result := 'S';
    tlUpdate: result := 'U';
    tlCreate: result := 'C';
  else
    result := 'X';
  end;
end;

procedure TCustomWorkflowDB.LogTaskOperation(TaskIns: TTaskInstance;
  Operation: TTaskLogOperation; Info1: string = ''; Info2: string = '');
var
  SQL: string;
begin
  CheckDB;
  With TaskLogBindary do
  begin
    {Insert tasklog unique key}
    SQL := Format('INSERT INTO %s (%s, %s, %s, %s, %s, %s) Values '+
      '(:taskinskey, :eventdate, :operation, :userid, :info, :info2)',
        [TableName, TaskInsKeyField, EventDateField, OperationField,
         UserIdField, InfoField, Info2Field]);
    FParams.Clear;
    FParams.CreateParam(ftinteger, 'taskinskey', ptInput).AsInteger := StrToInt(TaskIns.Key);
    FParams.CreateParam(ftDateTime, 'eventdate', ptInput).AsDateTime := Now;
    FParams.CreateParam(ftString, 'operation', ptInput).AsString := MapOperationToStr(Operation);
    FParams.CreateParam(ftString, 'userid', ptInput).AsString := WorkflowStudio.UserManager.LoggedUserId;
    FParams.CreateParam(ftString, 'info', ptInput).AsString := Info1;
    FParams.CreateParam(ftString, 'info2', ptInput).AsString := Info2;
    ExecuteQuery(SQL, FParams);
  end;
end;

procedure TCustomWorkflowDB.TaskLogLoadList(ATaskInsKey: string;
  ALogItems: TTaskLogItems);
var
  SQL: string;
  DS: TDataset;
begin
  ALogItems.Clear;
  if ATaskInsKey <> '' then
  begin
    CheckDB;
    FParams.Clear;
    SQL := Format('SELECT * from %s where %s = :taskinskey order by %s',
      [TaskLogBindary.TableName, TaskLogBindary.TaskInsKeyField, TaskLogBindary.EventDateField]);

    FParams.Clear;
    FParams.CreateParam(ftInteger, 'taskinskey', ptInput).AsInteger := StrToInt(ATaskInsKey);

    DS := OpenQuery(SQL, FParams);
    try
      DS.First;
      while not DS.EOF do
      begin
        TaskLogLoadRecord(DS, ALogItems.Add);
        DS.Next;
      end;
    finally
      //DS.Close;
      if DestroyQueries then
        DS.Free;
    end;
  end;
end;

procedure TCustomWorkflowDB.TaskLogLoadRecord(DS: TDataset;
  LogItem: TTaskLogItem);
begin
  With TaskLogBindary do
  begin
    LogItem.Operation := MapStrToOperation(DS.FieldByName(OperationField).AsString);
    LogItem.TaskInsKey := DS.FieldByName(TaskInsKeyField).AsString;
    LogItem.EventDate := DS.FieldByName(EventDateField).AsDateTime;
    LogItem.UserId := DS.FieldByName(UserIdField).AsString;
    LogItem.Info := DS.FieldByName(InfoField).AsString;
    LogItem.Info2 := DS.FieldByName(Info2Field).AsString;
  end;
end;

function TCustomWorkflowDB.MapStrToOperation(AStr: string): TTaskLogOperation;
Var
  AChar: string;
begin
  result := tlNone;
  AChar := Uppercase(AStr);
  if AChar = 'S' then
    result := tlStatusChange
  else
  if AChar = 'U' then
    result := tlUpdate
  else
  if AChar = 'C' then
    result := tlCreate;
end;

function TCustomWorkflowDB.FindNextID(ATableName, AFieldName: string): integer;
var
  DS: TDataset;
begin
  FParams.Clear;
  DS := OpenQuery(
    Format('Select Max(%s) from %s', [AFieldName, ATableName]),
    FParams);
  try
    result := DS.Fields[0].AsInteger + 1;
  finally
    DS.Close;
    if DestroyQueries then
      DS.Free;
  end;
end;

(*function TCustomWorkflowDB.SQLKeyField(AFieldName: string): string;
begin
  //if not FAutoIncFields then
    result := Format('%s, ', [AFieldName])
  //else
  //  result := '';
end;

function TCustomWorkflowDB.SQLKeyParam(AFieldName: string): string;
begin
  //if not FAutoIncFields then
    result := Format(':%s, ', [AFieldName])
  //else
  //  result := '';
end;*)

procedure TCustomWorkflowDB.DoAssignSQLParams(Dataset: TDataset;
  AParams: TParams);
begin
end;

{ TwsBindary }

constructor TwsBindary.Create(AWorkflowDB: TComponent);
begin
  inherited Create;
  FWorkflowDB := AWorkflowDB;
end;

destructor TwsBindary.Destroy;
begin
  FWorkflowDB := nil;
  inherited;
end;

(*function TwsBindary.FieldByName(FieldName: string): TField;
begin
  result := nil;
  if DataSource = nil then Exit;
  result := FDataSource.DataSet.FieldByName(FieldName);
end;*)


(*procedure TwsBindary.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  if (Operation = opRemove) and (AComponent = FDataSource) then
    FDataSource := nil;
end;*)

(*procedure TwsBindary.SetDataSource(newValue: TDataSource);
begin
  if (FDataSource <> newValue) then
  begin
    FDataSource := newValue;
    if newValue <> nil then
      newValue.FreeNotification(newValue);
  end;
end;*)

{ TTaskInsBindary }

constructor TTaskInsBindary.Create(AWorkflowDB: TComponent);
begin
  inherited Create(AWorkflowDB);
  FTableName := 'wstaskinstance';
  KeyField := 'id';
  TaskField := 'task';
  UserIdField := 'userid';
  NameField := 'name';
  {CommentsField := 'comments';
  SubjectField := 'subject';
  DescriptionField := 'description';}
  WorkInsKeyField := 'workflowinstancekey';
  WorkDefKeyField := 'workflowdefinitionkey';
  CompletedField := 'completed';
  CreatedOnField := 'createdon';
  ModifiedOnField := 'modifiedon';
  ModifiedUserIdField := 'modifieduserid';
end;

{ TWorkDefBindary }

constructor TWorkDefBindary.Create(AWorkflowDB: TComponent);
begin
  inherited Create(AWorkflowDB);
  FTableName := 'wsworkflowdefinition';
  KeyField := 'id';
  WorkflowField := 'workflow';
  NameField := 'name';
end;

{ TWorkInsBindary }

constructor TWorkInsBindary.Create(AWorkflowDB: TComponent);
begin
  inherited Create(AWorkflowDB);
  FTableName := 'wsworkflowinstance';
  KeyField := 'id';
  WorkflowField := 'workflow';
  WorkDefKeyField := 'workflowdefinitionkey';
  CreatedOnField := 'createdon';
end;

{ TAttachmentBindary }

constructor TAttachmentBindary.Create(AWorkflowDB: TComponent);
begin
  inherited Create(AWorkflowDB);
  FTableName := 'wsattachment';
  KeyField := 'id';
  ContentField := 'filecontent';
  WorkKeyField := 'workkey';
  CreatedOnField := 'createdon';
  ObjectTypeField := 'objecttype';
end;

{ TTaskLogBindary }

constructor TTaskLogBindary.Create(AWorkflowDB: TComponent);
begin
  inherited;
  FTableName := 'wstasklog';
  KeyField := 'id';
  TaskInsKeyField := 'taskinstancekey';
  EventDateField := 'eventdate';
  OperationField := 'operation';
  UserIdField := 'userid';
  InfoField := 'info';
  Info2Field := 'info2';
end;

end.