ag_report.pas

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

      end; //对应第二个with
      Next;
    end; //对应第一个while
  end; //对应第一个with
  Result := False;
  AG_ADOQuery5.Free;
  AG_ADOQuery8.Free;
end;

{---------------------------------------此函数查找该字段可以连接到的表----------------------------------------}

function Find_Col_Pos(ColName: string; var Multi_Tables: TStrings): Boolean;
//Tables用来保存存在维属性Name的表名
var
  ss: string;
  AG_ADOQUERY6: TADOQuery;
begin
  AG_ADOQUERY6 := 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_ADOQuery6 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From TableDemention where 字段类型 = ' + '''' + '维属性' +
      '''' + ' And 字段名 = ''' + ColName + '''');
    try
      Active := true;
    except
      ShowMessage('无法打开ZDDYGXB表！');
      Result := False;
      Free;
      Exit;
    end;
    while not Eof do //可能由维码连接到别的表的情况
    begin
      if (Trim(FieldByName('表类型').AsString) <> '事实表') then
        Multi_Tables.Add(Trim(FieldByName('表名').AsString));
      next;
    end;

    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 (Trim(FieldByName('源字段名').AsString) = ColName) then
        Multi_Tables.Add(Trim(FieldByName('目标表名').AsString));
      next;
    end;

    if Multi_tables.Count = 0 then
    begin
      //ShowMessage('在TableDemention表中没有找到相应维属性及其表，可能是这个表中数据与现实数据环境中的表不一致。');
      Result := False;
    end
    else
      Result := True;
  end;
  AG_ADOQUERY6.Free;
end;

{---------------------------------此函数查找所选项是否在当前表中-------------------------}

function Find_Connect_Self(TableName: string): Boolean;
var
  ss: string;
  AG_ADOQUERY7: TADOQuery;
begin
  Result := False;
  AG_ADOQUERY7 := 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_ADOQuery7 do
  begin
    ConnectionString := ss;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From TableDemention Where  表名 = ''' + TableName + '''');
    try
      Active := true;
    except
      ShowMessage('无法打开库表管理表！');
      Free;
      Exit;
    end;
    while not Eof do //可能由维码连接到别的表的情况
    begin
      if TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName =
        Trim(FieldByName('字段名').AsString) then
      begin
        Result := True;
        Free;
        Exit;
      end;
      next;
    end;
    Result := False;
  end;
  AG_ADOQUERY7.Free;
end;

{-------------------------------------------生成相应的SQL语句-------------------------------------------------------}

function Assemble_Sql(var Sql_Result: Tstrings; Main_Table: string; Dimension:
  Tstrings; MainTabCount: integer): Boolean;
var
  Sub_Str_Flag, Grouping_Flag, If_Connected: Boolean;
  i, j: integer;
  Sql_Str: string;
label
  NoOrder, NoGrouping;
begin
  If_Connected := False;
  Grouping_Flag := False;
  {----------------------Select子句----------------------------}
  i := 0;
  Sql_Str := 'Select ';
  while (i < ItemArray.Count) do
  begin { while }
    if (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标') then
      Sql_Str := Sql_Str + 'Sum(';
    if (TReportFieldMessage(ItemArray.Objects[i]).Aliase = '') then
      //如果别名为空（不是统计字段，不是convert函数，前面需要表名）
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0]) +
        '.';
    Sql_Str := Sql_Str +
      Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
    if (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标') then
      Sql_Str := Sql_Str + ')';
    if (TReportFieldMessage(ItemArray.Objects[i]).Aliase <> '') then
      Sql_Str := Sql_Str + ' ' +
        TReportFieldMessage(ItemArray.Objects[i]).Aliase;
    //如果是统计字段(sum,count)或日期时间则加上别名
    if (i <> ItemArray.Count - 1) then //如果以后还有字段需要加上逗号
      Sql_Str := Sql_Str + ',';
    i := i + 1;
  end; { while }

  if (MainTabCount > 1) or (FHTJ_Flag = True) then
    goto NoGrouping; //如果为多表联合，则暂时不生成Grouping部分

  {-----------------Grouping------------------}
{  i:=0;
  while (i < Dimension.Count) do
  begin
    if (TReportFieldMessage(ItemArray.Objects[i]).Grouping = True)
       and ( CompareText(TReportFieldMessage(ItemArray.Objects[i]).FieldName,'count(*)') <>0 )
       and ( CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName,1,2),'((') <> 0 ) then
    begin
      Grouping_Flag:=True;
      Sql_Str:=Sql_Str+', Grouping(';
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase = '') then //如果别名为空（不是convert函数），需要表名
        Sql_Str:=Sql_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0])+'.';
      Sql_Str:=Sql_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
      Sql_Str:=Sql_Str+')';
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase <> '') then //如果是convert函数，则用别名加“小计”作为列名
        Sql_Str:=Sql_Str+' '+TReportFieldMessage(ItemArray.Objects[i]).Aliase+'小计'
      else
        Sql_Str:=Sql_Str+' '+TReportFieldMessage(ItemArray.Objects[i]).FieldName+'小计'; //否则用字段名加“小计”作列名
    end;
    i:=i+1;
  end;
}
  NoGrouping:
  Sql_Result.Add(Sql_Str);

  {----------------------From子句----------------------------}
  i := 0;
  Sql_Str := 'From ' + Main_Table + ', ';
  while (i < Dimension.Count) do
  begin { while }
    j := 0;
    while (Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[j])
      <> '') do
    begin
      if
        (Pos(Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[j]), Sql_Str) = 0) then
      begin
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[j]);
        Sql_Str := Sql_Str + ', ';
      end;
      j := j + 1;
    end;
    i := i + 1;
  end; { while }
  Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 2);
  Sql_Result.Add(Sql_Str);
  {----------------------Where子句----------------------------}
  {----------------------数值部分----------------------------}
  i := 0;
  Sub_Str_Flag := False;
  Sql_Str := '';
  while (i < Dimension.Count) do
  begin { while }
    if (TReportFieldMessage(ItemArray.Objects[i]).Constraints = '') then
    begin
      i := i + 1;
      continue;
    end;
    //where 约束的情况
    if Judge_Aggregate(i) = False then //如果不是聚合函数
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase = '') then
        //如果别名为空，则说明不包括转换函数（如convert），需要加上表名
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0])
          +
          '.';
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName) + ' ' +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).Constraints);
      Sql_Str := Sql_Str + ' and ';
      Sub_Str_Flag := True;
    end;
    i := i + 1;
  end; { while }

  {----------------------连接部分----------------------------}
  i := 0;
  while (i < Dimension.Count) do
  begin { while }
    j := 0;
    while (TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Con_In[j] <>
      'NullCol') and
      (TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Con_In[j] <> '')
        do
    begin { while }
      Sql_Str := Sql_Str +
        TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[j] + '.' +
        TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Con_In[j]; //a.b
      Sql_Str := Sql_Str + ' = ' +
        TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[j + 1] +
        '.'
        + TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Con_Out[j +
        1];
      //=c.d
      Sql_Str := Sql_Str + ' and ';
      Sub_Str_Flag := True;
      If_Connected := True;
      j := j + 1;
    end; { while }
    i := i + 1;
  end;

  {------------------------卡片状态约束--------------------------}
  if (Kpzt_Constrains <> '') then
  begin
    if (Kpzt_Table <> '') and (If_Connected = False) then
    else
    begin
      Sql_Str := Sql_Str + Kpzt_Constrains;
      Sub_Str_Flag := True;
    end;
  end;

  if (Invariable_Constrains <> '') then
  begin
    Sql_Str := Sql_Str + Invariable_Constrains + ' And ';
    Sub_Str_Flag := True;
    if (If_Connected = True) then
      Sql_Str := Sql_Str +
        ' scsj_tkpxx.创建日期 in (select max(创建日期) from scsj_tkpxx where scsj_tkpxx.创建日期<scsj_dsyjxsxx1.日期 and  scsj_dsyjxsxx1.IC卡号=scsj_tkpxx.IC卡号 and scsj_tkpxx.卡片状态=''' + '使用'
        + '''' + ')' + ' And ';
  end;

  if (Sub_Str_Flag = True) then
  begin
    Insert('Where ', Sql_Str, 1); //如果存在where约束，那么增加 'where'
    Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 5);
  end;
  Sql_Result.Add(Sql_Str);

  {----------------------Group By子句----------------------------}
  i := 0;
  Sql_Str := 'Group By ';
  while (i < ItemArray.Count) do
  begin { while }
    if ((Judge_Aggregate(i) = False)) and
      (TReportFieldMessage(ItemArray.Objects[i]).FieldType <> '指标') then
      //如果是统计字段，不需要Group By
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase = '') then
        //如果别名为空，则说明不包括转换函数（如convert），需要加上表名
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0])
          +
          '.';
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
      Sql_Str := Sql_Str + ',';
    end;
    i := i + 1;
  end; { while }
  Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 1);
  if (Grouping_Flag = True) then
    Sql_Str := Sql_Str + ' with rollup';
  Sql_Result.Add(Sql_Str);

  {----------------------Having子句----------------------------}
  {  i:=0;
    Sub_Str_Flag :=False;
    Sql_Str:=' ';
    while (i < ItemArray.Count) do
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).Constraints = '') then
      begin
        i:=i+1;
        continue;
      end;
      //where 约束的情况
      if Judge_Aggregate(i) = True then
      begin
        Sql_Str:=Sql_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName)+' '+Trim(TReportFieldMessage(ItemArray.Objects[i]).Constraints);
        Sql_Str:=Sql_Str+' and ';
        Sub_Str_Flag:=True;
      end;
      i:=i+1;
    end;
    if (Sub_Str_Flag = True) then
    begin
      Insert('Having ',Sql_Str,1); //如果存在Having约束，那么增加 'Having'
      Sql_Str:=Copy(Sql_Str,1,Length(Sql_Str)-5);
    end;
    Sql_Result.Add(Sql_Str);
  }
  {----------------------Order By子句----------------------------}
  if (MainTabCount > 1) or (FHTJ_Flag = True) then
    goto NoOrder;
  i := 0;
  Sql_Str := '';
  Sub_Str_Flag := False;
  while (i < ItemArray.Count) do
  begin
    if (TReportFieldMessage(ItemArray.Objects[i]).OrderType <> '') then
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase <> '') then
      begin
        if
          (CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName,
          1, 7), 'convert') <> 0) then
          Sql_Str := Sql_Str +
            Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0])
            + '.';
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase);
      end
      else
      begin
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Selected_Item_Table[0])
          +
          '.';
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
      end;
      Sql_Str := Sql_Str + ' ' +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).OrderType);
      Sql_Str := Sql_Str + ', ';
      Sub_Str_Flag := True;