同样在 在 协同系统\计划\计划中心资源
应该是跟下面的函数有关系
function TPlanFilterManage.GetSQLFilter: string;
const
SOrg = 'FPERSONID = ''%s''';
SOrg_Pos = '(FDEPTID = ''%s'') AND (FPOSITIONID = ''%s'') AND (FPERSONID = ''%s'')';
var
I: Integer;
lWhere: string;
lSQLFilter: string;
// lOperatorPosition: TOperatorPosition;
lDeptIDs, lPerIDs: TStringList;
lDepts, lPers: string;
lDeptURLs: TStringList;
lDeptLikes: string;
lSubLikes: string;
lDeptFilter: string;
lPlanState: TPlanState;
lOperatorID, lDept, lPos, lPer: string;
begin
// lOperatorPosition :=
// PlanLib.TPlanSpecialLogic.GetOperatorPosition(Context);
{
我的工作
SELECT * FROM TPLAN
WHERE FPLANROOTID IN (SELECT DISTINCT FPLANROOTID FROM TPLAN WHERE --显示整棵树
FAFFIRMLEVEL >= 自己的“审批范围” AND --排除审批范围属于下级审批的计划
FPLANID IN (SELECT DISTINCT FPLANID FROM TPLANEXECUTOR --
WHERE FPERSONID = 自己 OR --与自己相关的计划
FDEPTID = 自己部门 OR --自己部门内的计划
FORGURL LIKE '上级部门%' OR --上级部门的计划
(FTYPE = 'petEditor' AND FORGURL LIKE '下级%') --我的下属创建的
))
我的管理
SELECT * FROM TPLAN
WHERE FPLANROOTID IN (SELECT DISTINCT FPLANROOTID FROM TPLAN WHERE --显示整棵树
FAFFIRMLEVEL >= 自己的“审批范围” AND --排除审批范围属于下级审批的计划
FPLANID IN (SELECT DISTINCT FPLANID FROM TPLANEXECUTOR --
WHERE FPERSONID = 自己 OR --与自己相关的计划
FORGURL LIKE '下级%' --我的下属创建的
))
}
{ 只列出与任务相关的计划 }
if AllowPlanIDs then
begin
lWhere := '';
for I := 0 to PlanIDs.Count - 1 do
begin
if I = 0 then
lWhere := SysUtils.Format('''%s''', [SysUtils.Trim(PlanIDs[I])])
else
lWhere := lWhere + SysUtils.Format(', ''%s''', [SysUtils.Trim(PlanIDs[I])]);
end;
if lWhere <> '' then
lSQLFilter := SysUtils.Format('FPLANID IN (%s)', [lWhere]) else
lSQLFilter := '1 <> 1';
end
else
begin
{ 获取显示在望远镜中的过滤条件 }
lSQLFilter := CustomFilter;
{ 排除审批范围属于下级审批的计划 }
{ 暂时干掉
lWhere := SysUtils.Format(
'FAFFIRMLEVEL >= %d',
[PlanLib.TPlanSpecialLogic.GetDeptLevel(
lOperatorPosition.PositionMember.Parent)]);
lSQLFilter := GetMergeSQLWhereAND(lSQLFilter, lWhere);}
lWhere := '';
{ 从执行表中获取与组织机构相关的范围权限 }
lPerIDs := TStringList.Create;
lDeptIDs := TStringList.Create;
try
lPerIDs.Duplicates := TDuplicates.dupIgnore;
lPerIDs.Sorted := True;
lDeptIDs.Duplicates := TDuplicates.dupIgnore;
lDeptIDs.Sorted := True;
TPlanSpecialLogic.GetPersonIDsAndDeptIDs(Context, lPerIDs, lDeptIDs);
lPers := '';
for I := 0 to lPerIDs.Count - 1 do
if I = 0 then
lPers := SysUtils.Format('''%s''', [lPerIDs[I]]) else
lPers := lPers + ', ' + SysUtils.Format('''%s''', [lPerIDs[I]]);
Assert(lPers <> '', '人员不存在');
{ lDepts := '';
for I := 0 to lDeptIDs.Count - 1 do
if I = 0 then
lDepts := SysUtils.Format('''%s''', [lDeptIDs[I]]) else
lDepts := lDepts + ', ' + SysUtils.Format('''%s''', [lDeptIDs[I]]);
Assert(lDepts <> '', '部门不存在');}
//与自己相关
//与自己部门相关
{ lWhere := GetMergeSQLWhereOR(lWhere,
SysUtils.Format('FDEPTID IN (%s)', [lDepts]));}
finally
lPerIDs.Free;
lDeptIDs.Free;
end;
{ 1. 与自己相关的计划 }
if FIncludePerson then
begin
lWhere := GetMergeSQLWhereOR(lWhere, SysUtils.Format('FPERSONID IN (%s)', [lPers]));
end;
{ 2. 与部门相关的计划 }
if FIncludeDept then
begin
lDeptURLs := TStringList.Create;
try
lDeptURLs.Duplicates := TDuplicates.dupIgnore;
lDeptURLs.Sorted := True;
TPlanSpecialLogic.GetSuperiorDeptURLs(Context, lDeptURLs);
lDeptLikes := '';
for I := 0 to lDeptURLs.Count - 1 do
begin
lDeptLikes := GetMergeSQLWhereOR(lDeptLikes,
SysUtils.Format('(FORGURL LIKE ''%s%%'')',
[TPlanSpecialLogic.GetSaveOrgURL(lDeptURLs[I])]),
False);
end;
Assert(lDeptLikes <> '', '获取上级部门URL失败');
finally
lDeptURLs.Free;
end;
if not FIncludePerson then
lDeptLikes := GetMergeSQLWhereAND(lDeptLikes, SysUtils.Format('FPERSONID NOT IN (%s)', [lPers]));
lWhere := GetMergeSQLWhereOR(lWhere, lDeptLikes);
end;
{ 3. 与指定的组织机构（管理的部门）相关的 }
lDeptFilter := '';
for I := 0 to DeptURLs.Count - 1 do
begin
lDeptFilter := GetMergeSQLWhereOR(lDeptFilter,
SysUtils.Format('(FORGURL LIKE ''%s%%'')',
[TPlanSpecialLogic.GetSaveOrgURL(DeptURLs[I])]), False);
end;
lWhere := GetMergeSQLWhereOR(lWhere, lDeptFilter);
{ 4. 组合上述执行表过滤条件 }
Assert(SysUtils.Trim(lWhere) <> '', 'Error: TPlanFilterManage.GetSQLFilter');
lWhere := SysUtils.Format(
'FPLANID IN (SELECT DISTINCT TPLANEXECUTOR.FPLANID FROM TPLANEXECUTOR, TPLAN WHERE (TPLAN.FPLANID = TPLANEXECUTOR.FPLANID) AND (%s))',
[lWhere]);
lSQLFilter := GetMergeSQLWhereAND(lSQLFilter, lWhere);
end;
{ 获取计划的完整树 }
if ShowEntireTree then
begin
lSQLFilter := SysUtils.Format(
'TPLAN.FPLANROOTID IN (SELECT DISTINCT FPLANROOTID FROM TPLAN WHERE %s)',
[lSQLFilter]);
end;
{ 即使是显示完整的数，计划类型中的已删除和已调整的计划也不显示出来 }
if PlanStates <> [] then
begin
lWhere := '';
lPlanState := TPlanState.psPlanDeleted;
if not(lPlanState in PlanStates) then
if lWhere = '' then
lWhere := SysUtils.Format('''%s''', [TPlanSpecialLogic.PlanStateToStr(lPlanState)]) else
lWhere := lWhere + SysUtils.Format(', ''%s''', [TPlanSpecialLogic.PlanStateToStr(lPlanState)]);
if lWhere <> '' then
begin
lWhere := SysUtils.Format('NOT(TPLAN.FPLANSTATE IN (%s))', [lWhere]);
lSQLFilter := GetMergeSQLWhereAND(lSQLFilter, lWhere);
end;
end;
{ 与数据集中的原始SQLFilter合并 }
Result := GetMergeSQLWhereAND(lSQLFilter, FSQLFilter);
end; |
发表于 2007-1-23 10:00:16
楼主