wsdb.pas

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

  finally
    MemStream.Free;
    StrStream.Free;
  end;
end;

constructor TCustomWorkflowDB.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FWorkInsBindary := TWorkInsBindary.Create(Self);
  FWorkDefBindary := TWorkDefBindary.Create(Self);
  FTaskInsBindary := TTaskInsBindary.Create(Self);
  FAttachmentBindary := TAttachmentBindary.Create(Self);
  FTaskLogBindary := TTaskLogBindary.Create(Self);
  FParams := TParams.Create;
  //FAutoIncFields := false;
end;

destructor TCustomWorkflowDB.Destroy;
begin
  FParams.Free;
  FTaskLogBindary.Free;
  FWorkInsBindary.Free;
  FWorkDefBindary.Free;
  FTaskInsBindary.Free;
  FAttachmentBindary.Free;
  inherited;
end;

procedure TCustomWorkflowDB.ExecuteQuery(SQL: string; Params: TParams);
var
  Done: boolean;
  DS: TDataset;
begin
  DS := nil;

  //Create the query
  Done := false;
  if Assigned(FOnCreateQuery) then
    FOnCreateQuery(Self, SQL, DS, Done);
  if not Done then
    DS := DoCreateQuery(SQL);

  //Assign the params
  Done := false;
  if Assigned(FOnAssignSQLParams) then
    FOnAssignSQLParams(Self, DS, Params, Done);
  if not Done then
    DoAssignSQLParams(DS, Params);

  //Execute the query
  Done := false;
  if Assigned(FOnExecuteQuery) then
    FOnExecuteQuery(Self, DS, Done);
  if not Done then
    DoExecuteQuery(DS);

  if DS <> nil then
    DS.Free;
end;

procedure TCustomWorkflowDB.DoExecuteQuery(Dataset: TDataset);
begin
end;

function TCustomWorkflowDB.OpenQuery(SQL: string; Params: TParams): TDataset;
var
  Done: boolean;
begin
  result := nil;

  //Create the query
  Done := false;
  if Assigned(FOnCreateQuery) then
    FOnCreateQuery(Self, SQL, result, Done);
  if not Done then
    result := DoCreateQuery(SQL);

  //Assign the params
  Done := false;
  if Assigned(FOnAssignSQLParams) then
    FOnAssignSQLParams(Self, result, Params, Done);
  if not Done then
    DoAssignSQLParams(result, Params);

  if result <> nil then
    result.Open;
end;

function TCustomWorkflowDB.DoCreateQuery(SQL: string): TDataset;
begin
  result := nil;
end;

procedure TCustomWorkflowDB.TaskInstanceInsert(TaskIns: TTaskInstance);
var
  SQL: string;
  CreatedOn: TDateTime;
begin
  CheckDB;
  CreatedOn := Now;
  With TaskInsBindary do
  begin
    {Insert taskinstance unique key}
    (*SQL := Format('INSERT INTO %s (%s%s, %s) Values (%s:workinskey, :createdon, :name)',
      [TableName, SQLKeyField(KeyField), WorkInsKeyField, CreatedOnField, NameField, SQLKeyParam(KeyField)]);
    FParams.Clear;
    if not FAutoIncFields then
      FParams.CreateParam(ftInteger, KeyField, ptInput).AsInteger := FindNextID(TableName, KeyField);
    FParams.CreateParam(ftInteger, 'workinskey', ptInput).AsInteger := StrToInt(TaskIns.WorkInsKey);
    FParams.CreateParam(ftDateTime, 'createdon', ptInput).Value := CreatedOn;
    FParams.CreateParam(ftString, 'name', ptInput).AsString := TaskIns.TaskDef.Name; wsclasses
    ExecuteQuery(SQL, FParams);

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

    TaskIns.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(TaskIns.Key);
    FParams.CreateParam(ftDateTime, 'createdon', ptInput).Value := CreatedOn;
    ExecuteQuery(SQL, FParams);

    {Save a log for the task creation}
    LogTaskOperation(TaskIns, tlCreate);

    {Save other taskinstance fields}
    TaskInstanceUpdate(TaskIns, true);
  end;
end;

procedure TCustomWorkflowDB.TaskInstanceLoad(TaskIns: TTaskInstance);
var
  SQL: string;
  DS: TDataset;
begin
  CheckDB;
  SQL := Format('SELECT * from %s WHERE %s = :id',
    [TaskInsBindary.TableName, TaskInsBindary.KeyField]);
  FParams.Clear;
  FParams.CreateParam(ftInteger, 'id', ptInput).AsInteger := StrToInt(TaskIns.Key);
  DS := OpenQuery(SQL, FParams);
  try
    if not DS.IsEmpty then
      TaskInstanceLoadRecord(DS, TaskIns)
    else
      wsDBError(Format(_str(SErrorRecordNotFoundKey), [TaskInsBindary.TableName, TaskIns.Key]));
  finally
    DS.Close;
    if DestroyQueries then
      DS.Free;
  end;
end;

procedure TCustomWorkflowDB.TaskInstanceLoadList(ATasks: TTaskInstanceList;
  AFilterType: TTaskFilterType; AFilterKey: string; OnlyIncomplete: boolean);
var
  SQL: string;
  DS: TDataset;
  UserKeys: TStringList;
  UserCondition: string;
  c: integer;
  CompletedCondition: string;
  GroupIds: TStringList;
  AUser: TWorkflowUser;

begin
  CheckDB;
  DS := nil;

  {build incomplete condition}
  if OnlyIncomplete then
    CompletedCondition := Format('%s = :completed', [TaskInsBindary.CompletedField])
  else
    CompletedCondition := '0=0';

  Case AFilterType of
    tfWorkIns:
      begin
        SQL := Format('SELECT * FROM %s WHERE %s = :workinskey AND (%s)',
          [TaskInsBindary.TableName, TaskInsBindary.WorkInsKeyField, CompletedCondition]);
        FParams.Clear;
        FParams.CreateParam(ftInteger, 'workinskey', ptInput).AsInteger := StrToInt(AFilterKey);
        if OnlyIncomplete then
          FParams.CreateParam(ftString, 'completed', ptInput).AsString := 'F';
        DS := OpenQuery(SQL, FParams);
      end;
    tfUser, tfUserList:
      begin
        {Get users key values and build condition for users}
        UserKeys := TStringList.Create;
        try
          {Put the correct keys in the userkeys}
          Case AFilterType of
            tfUser: UserKeys.Add(AFilterKey);
            tfUserList: UserKeys.CommaText := AFilterKey;
          end;

          {For each user id, include the ids of the groups for which the user id belongs to.
           Do that only for GroupAssignmentMode = gamSingleTask}
          if WorkflowStudio.GroupAssignmentMode = gamSingleTask then
          begin
            GroupIds := TStringList.Create;
            try
              for c := 0 to UserKeys.Count - 1 do
              begin
                AUser := WorkflowStudio.UserManager.Users.FindById(UserKeys[c]);
                if AUser <> nil then
                begin
                  GroupIds.Clear;
                  AUser.FillGroupIds(GroupIds);
                  UserKeys.AddStrings(GroupIds);
                end;
              end;
            finally
              GroupIds.Free;
            end;
          end;

          {Fill in the condition for the user keys, in the format
           (UserId = :userid0) OR (UserId = :userid1) ....}
          UserCondition := '';
          for c := 0 to UserKeys.Count - 1 do
          begin
            if UserCondition <> '' then
              UserCondition := UserCondition + ' OR ';
            UserCondition := UserCondition + Format('(%s = :userid%d)',
              [TaskInsBindary.UserIdField, c]);
          end;
          if UserCondition = '' then
            UserCondition := '0=0';

          {Build the SQL statement}
          SQL := Format('SELECT * FROM %s WHERE (%s) AND (%s)',
            [TaskInsBindary.TableName, UserCondition, CompletedCondition]);

          {Fill in the param values, including the values of user keys in params userid0, userid1, etc.}
          FParams.Clear;
          for c := 0 to UserKeys.Count - 1 do
            FParams.CreateParam(ftString, Format('userid%d', [c]), ptInput).AsString := UserKeys[c];
          if OnlyIncomplete then
            FParams.CreateParam(ftString, 'completed', ptInput).AsString := 'F';
          DS := OpenQuery(SQL, FParams);
        finally
          UserKeys.Free;
        end;
      end;
  end;

  if DS = nil then Exit;
  try
    DS.First;

    ATasks.Clear;
    while not DS.EOF do
    begin
      TaskInstanceLoadRecord(DS, ATasks.Add.Task);
      DS.Next;
    end;
  finally
    DS.Close;
    if FDestroyQueries then
      DS.Free;
  end;
end;

procedure TCustomWorkflowDB.TaskInstanceLoadRecord(DS: TDataset; TaskIns: TTaskInstance);
begin
  With TaskInsBindary do
  begin
    ComponentFromString(TaskIns, DS.FieldByName(TaskField).AsString);
    TaskIns.Key := DS.FieldByName(KeyField).AsString;
    TaskIns.UserID := DS.FieldByName(UserIdField).AsString;
    TaskIns.TaskDef.Name := DS.FieldByName(NameField).AsString;
    (*TaskIns.Comments := DS.FieldByName(CommentsField).AsString;
    TaskIns.TaskDef.Subject := DS.FieldByName(SubjectField).AsString;;
    TaskIns.TaskDef.Description := DS.FieldByName(DescriptionField).AsString;*)
    TaskIns.WorkInsKey := IntToStr(Ds.FieldByName(WorkInsKeyField).AsInteger);
    TaskIns.DefinitionKey := IntToStr(Ds.FieldByName(WorkDefKeyField).AsInteger);
    TaskIns.CreatedOn := DS.FieldByName(CreatedOnField).AsDateTime;
  end;
end;

procedure TCustomWorkflowDB.TaskInstancesDeleteByWorkIns(AWorkInsKey: string);
var
  SQL: string;
begin
  SQL := Format('DELETE FROM %s WHERE %s = :workinskey',
    [TaskInsBindary.TableName, TaskInsBindary.WorkInsKeyField]);
  FParams.Clear;
  FParams.CreateParam(ftInteger, 'workinskey', ptInput).AsInteger := StrToInt(AWorkInsKey);
  ExecuteQuery(SQL, FParams);
end;

procedure TCustomWorkflowDB.TaskInstanceUpdate(TaskIns: TTaskInstance; Inserting: boolean = false);
var
  SQL: string;
  TaskInsOld: TTaskInstance;
  NowDate: TDateTime;
begin
  CheckDB;
  With TaskInsBindary do
  begin
    if TaskIns.Key <> '' then
    begin
      TaskInsOld := TTaskInstance.Create(nil);
      try
        {Load the current record into a temporary instance for comparing}
        if not Inserting then
        begin
          TaskInsOld.Key := TaskIns.Key;
          TaskInstanceLoad(TaskInsOld);
        end;

        NowDate := Now;
        {Save the current taskinstance in the database}
        SQL := Format('UPDATE %s SET '+
          '%s = :userid, '+
          '%s = :name, '+

          {'%s = :comments, '+
          '%s = :subject, '+
          '%s = :description, '+}

          '%s = :task, '+
          '%s = :workinskey, '+
          '%s = :workdefkey, '+
          '%s = :completed, '+
          '%s = :modifiedon, '+
          '%s = :modifieduserid '+
          'WHERE %s = :id',
          [TableName, UserIdField, NameField, {CommentsField, SubjectField, DescriptionField,}
           TaskField, WorkInsKeyField, WorkDefKeyField, CompletedField,
           ModifiedOnField, ModifiedUserIdField,
           KeyField]);
        FParams.Clear;
        FParams.CreateParam(ftInteger, 'id', ptInput).AsInteger := StrToInt(TaskIns.Key);
        FParams.CreateParam(ftString, 'userid', ptInput).AsString := TaskIns.UserID;
        FParams.CreateParam(ftString, 'name', ptInput).AsString := TaskIns.TaskDef.Name;
        {FParams.CreateParam(ftString, 'comments', ptInput).AsString := TaskIns.Comments;
        FParams.CreateParam(ftString, 'subject', ptInput).AsString := TaskIns.TaskDef.Subject;
        FParams.CreateParam(ftString, 'description', ptInput).AsString := TaskIns.TaskDef.Description;}
        FParams.CreateParam(ftMemo, 'task', ptInput).AsMemo := ComponentToString(TaskIns);
        FParams.CreateParam(ftInteger, 'workinskey', ptInput).AsInteger := StrToInt(TaskIns.WorkInsKey);
        FParams.CreateParam(ftInteger, 'workdefkey', ptInput).AsInteger := StrToint(TaskIns.DefinitionKey);
        if TaskIns.Completed then
          FParams.CreateParam(ftString, 'completed', ptInput).AsString := 'T'
        else
          FParams.CreateParam(ftString, 'completed', ptInput).AsString := 'F';

        if not Inserting then
        begin
          FParams.CreateParam(ftDateTime, 'modifiedon', ptInput).AsDateTime := NowDate;
          FParams.CreateParam(ftString, 'modifieduserid', ptInput).AsString := WorkflowStudio.UserManager.LoggedUserId;
        end else
        begin
          FParams.CreateParam(ftDateTime, 'modifiedon', ptInput).Clear;
          FParams.CreateParam(ftString, 'modifieduserid', ptInput).Clear;