ag_report.pas

亚惠快餐管理信息系统 包括亚惠快餐管理的各项功能

  end; //对应外层while
  Main_Table_Str.Add(Main_Tab_temp);
  if ((MultiFacts = True) and (Union_Flag = False)) then
  begin
    Union_Flag := True;
    Change_To_Function(Measures_Temp, Dimension);
    goto Jump_Flag;
  end;
  AG_ADOQUERY1.Free;
  Result := Main_Table_Str;
  //不能释放Main_Table_Str，因为外边Main_Tab_Str指向这个。

end;

{---------------------------------此函数递归查找所选项的连接方法----------------------}

function Find_Connect_Condition(TableName, Connect_Col: string; var i: integer;
  Dimension: Tstrings): Boolean;
var
  j, Property_Count: integer;
  // i用来表示每一个维属性经过的点数目；j用来表示可能连接的维吗数目;property_count表示一个维码可以找到几个有相应维属性的表
  ss, alias: string;
  TableString, ConString, Multi_Tables: Tstrings;
  //分别表示连接到的表，连接字段，连接过程中的中间表变量（用于找到不止一个唯属性的情况）
  AG_ADOQUERY2: TADOQuery;
begin

  Result := False;
  AG_ADOQUERY2 := TADOQuery.Create(Application);
  ss := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password=' +
    PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser + ';Initial Catalog=';
  ss := ss + PublicUnit.DbName + ';Data Source=' + PublicUnit.ServerName;

  alias := Trim(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName);
  if (TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldType <> '指标') and
    (Change_To_Function(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName,
    Dimension) = True) then
    //相应字段找到，但是需要做一定转换。比如日期-->Convert(varchar(10),日期,120),时间-->Convert(varchar(10),日期,108),以及聚合函数
  begin
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[0] :=
      TableName; //表名应该为主表名
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[0] :=
      Connect_Col; //连接字段应该为空
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_Out[0]
      :=
      'NullCol';

    if Judge_Aggregate(Dem_Count) = True then //如果是聚合字段，则需要加上别名
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Aliase := alias;

    if
      CompareText(Copy(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName, 1, 7), 'convert') = 0 then
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Aliase := alias;

    if
      CompareText(Copy(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName, 1, 2), '((') = 0 then
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Aliase := alias;

    i := i + 1;
    Result := True;
    Exit;
  end;

  if Find_Connect_Self(TableName) = True then
    //如果本表内可以找到维属性（维码） ，则进行下一个的查找
  begin
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[0] :=
      TableName;
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[0] :=
      Connect_Col;
    TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_Out[0]
      :=
      'NullCol';
    i := i + 1;
    Result := True;
    Exit;
  end;

  if Find_With_correspondingTab(TableName, Connect_Col, i) = True then
    //如果按照对照表可以找到维属性（维码），即只需和相应表作一次连接，如GLXX_DZYXX,则进行下一个的查找
  begin
    Result := True;
    Exit;
  end;

  j := 0;
  TableString := TStringList.Create;
  ConString := TStringList.Create;
  Multi_Tables := TStringList.Create;

  with AG_ADOQuery2 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From TableDemention Where 字段类型 = ' + '''' + '维码' +
      '''' + ' And 表名 = ''' + TableName + '''');
    try
      Active := true;
    except
      ShowMessage('无法打开库表管理表！');
      Free;
      Exit;
    end;
    while not Eof do //可能由维码连接到别的表的情况
    begin
      if Find_Col_Pos(Trim(FieldByName('字段名').AsString), Multi_Tables) = False
        then
      begin
        //ShowMessage('在为维码寻找连接表时出现错误！');
{          ShowMessage(FieldByName('字段名').AsString);
        TableString.Free;
        ConString.Free;
        Multi_Tables.Free;
        Free;
        Exit;
}

      end;
      property_count := 0;
      while Property_Count < Multi_Tables.Count do
      begin
        TableString.Add(Multi_Tables[property_count]);
        ConString.Add(Trim(FieldByName('字段名').AsString));
        property_count := property_count + 1;
      end;
      Multi_Tables.Clear;
      next;
    end;
  end;

  while j < TableString.Count do
  begin
    if Find_Connect_Condition(TableString[j], ConString[j], i, Dimension) = True
      then
    begin
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[i]
        := TableName;
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[i]
        := Connect_Col;
      TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_Out[i]
        :=
        TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[i
        - 1];
      Check_If_Need_Modified(i);
      i := i + 1;
      Result := True;
      TableString.Free;
      ConString.Free;
      AG_ADOQuery2.Free;
      Exit;
    end;
    j := j + 1;
  end;
  Result := False;
  TableString.Free;
  ConString.Free;
  AG_ADOQuery2.Free;
end;

{---------------------------------查找当前字段是否要替换成带函数的字段-------------------------}
{------------------------------------------重载函数--------------------------------------------}

function Change_To_Function(var Replace_Thing: string; Dimension: Tstrings):
  Boolean; overload;
var
  ss: string;
  AG_ADOQUERY3: TADOQuery;
begin
  AG_ADOQUERY3 := TADOQuery.Create(Application);
  ss := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password=' +
    PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser + ';Initial Catalog=';
  ss := ss + PublicUnit.DbName + ';Data Source=' + PublicUnit.ServerName;
  with AG_ADOQuery3 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From ZDBHGXB ');
    try
      Active := true;
    except
      ShowMessage('无法打开ZDBHGXB表！');
      Result := False;
      Free;
      Exit;
    end;
    while not Eof do //可能由维码连接到别的表的情况
    begin
      if Replace_Thing = Trim(FieldByName('源字段名').AsString) then
      begin
        Replace_Thing := Trim(FieldByName('目标字段名').AsString);
        if Trim(FieldByName('备注').AsString) = '返还统计' then
          FHTJ_Flag := True;
        Result := True;
        Exit;
      end;
      next;
    end;
  end;
  Result := False;
  AG_ADOQUERY3.Free;
end;

function Change_To_Function(var Replace_Thing: Tstrings; Dimension: Tstrings):
  Boolean; overload;
var
  i: integer;
  ss: string;
  AG_ADOQUERY3: TADOQuery;
begin
  i := 0;
  AG_ADOQUERY3 := TADOQuery.Create(Application);
  ss := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password=' +
    PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser + ';Initial Catalog=';
  ss := ss + PublicUnit.DbName + ';Data Source=' + PublicUnit.ServerName;
  while (i < Replace_Thing.Count) do
  begin
    with AG_ADOQuery3 do
    begin
      ConnectionString := ss;
      if Active then
        Close;
      Sql.Clear;
      Sql.Add('Select * From ZDBHGXB ');
      try
        Active := true;
      except
        ShowMessage('无法打开ZDBHGXB表！');
        Result := False;
        Free;
        Exit;
      end;
      while not Eof do //可能由维码连接到别的表的情况
      begin
        if Replace_Thing[i] = Trim(FieldByName('源字段名').AsString) then
        begin
          Replace_Thing[i] := Trim(FieldByName('目标字段名').AsString);
          if (i = (Replace_Thing.Count - 1)) then
          begin
            Result := True;
            Free;
            Exit;
          end;
          Break;
        end;
        next;
      end; //内层while
    end; //with
    i := i + 1;
  end; //外层while
  Result := False;
  AG_ADOQUERY3.Free;
end;

{---------------------------------------检查是否有需要加修正值的字段---------------------------------------}

function Check_If_Need_Modified(i: integer): Boolean;
var
  ss: string;
  AG_ADOQUERY4: TADOQuery;
begin
  AG_ADOQUERY4 := TADOQuery.Create(Application);
  ss := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password=' +
    PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser + ';Initial Catalog=';
  ss := ss + PublicUnit.DbName + ';Data Source=' + PublicUnit.ServerName;
  with AG_ADOQuery4 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From ZDDYGXB');
    try
      Active := true;
    except
      ShowMessage('无法打开对照表ZDDYGXB！');
      Result := False;
      Free;
      Exit;
    end;
    while not Eof do //可能由维码连接到别的表的情况
    begin
      if
        (TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[i
        - 1] = Trim(FieldByName('源字段名').AsString))
        and
        (TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[i
        - 1] = Trim(FieldByName('目标表名').AsString)) then
      begin
        TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[i
          - 1] := Trim(FieldByName('目标字段名').AsString);
        Result := True;
        Free;
        Exit;
      end;
      next;
    end; //对应while
  end; //对应with
  Result := False;
  AG_ADOQUERY4.Free;
end;

{-----------------------------------------从对照表里查找是否有所选项------------------------------------------------------------------}

function Find_With_correspondingTab(TableName, Connect_Col: string; var i:
  integer): Boolean;
var
  ss, ss1, Source_Col_Name, Aim_Tab_Name, Aim_Col_Name: string;
  AG_ADOQUERY5, AG_ADOQUERY8: TADOQuery;
begin
  AG_ADOQUERY5 := TADOQuery.Create(Application);
  AG_ADOQUERY8 := TADOQuery.Create(Application);
  ss := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password=' +
    PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser + ';Initial Catalog=';
  ss := ss + PublicUnit.DbName + ';Data Source=' + PublicUnit.ServerName;
  ss1 := ss;
  with AG_ADOQuery5 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From ZDDYGXB where 目标字段名 = ''' +
      Trim(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName) + '''');
    try
      Active := true;
    except
      ShowMessage('无法打开对照表！');
      Result := False;
      Free;
      AG_ADOQuery8.Free;
      Exit;
    end;
    while not Eof do
    begin
      Source_Col_Name := Trim(FieldByName('源字段名').AsString);
      Aim_Tab_Name := Trim(FieldByName('目标表名').AsString);
      Aim_Col_Name := Trim(FieldByName('目标字段名').AsString);
      with AG_ADOQuery8 do
      begin
        ConnectionString := ss1;
        if Active then
          Close;
        Sql.Clear;
        Sql.Add('Select * From TableDemention Where  表名 = ''' + TableName +
          '''');
        try
          Active := true;
        except
          ShowMessage('无法打开库表管理表！');
          Result := False;
          Free;
          AG_ADOQuery5.Free;
          Exit;
        end;
        while not Eof do
        begin
          if Source_Col_Name = Trim(FieldByName('字段名').AsString) then
          begin
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[0] := Aim_Tab_Name; //经过对照表找到的表名及表字段
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[0] := Aim_Col_Name;
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_Out[0] := 'NullCol';
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Table[1] := TableName;
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_In[1] := Connect_Col;
            TReportFieldMessage(ItemArray.Objects[Dem_Count]).Selected_Item_Con_Out[1] := Source_Col_Name;
            i := i + 2;
            Result := True;
            Free;
            AG_ADOQuery5.Free;
            Exit;
          end;
          Next;
        end; //对应的二个while