ag_report.pas

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

  Sub_Str_Flag := False;
  Tmp_Sql_Result := TStringList.Create;
  Add_Top_Str := Sql_Result[0];
  Insert(' top 4294967295  ' + '''' + '    ' + '''' + ' as 小计,', Add_Top_Str,
    8);
  Sql_Result[0] := Add_Top_Str;

  {-----------------------将原有Sql_Result添加指标约束，增加Into子句，并临时保存到Tmp_Sql_Str中--------------------------}
  i := 0;
  Tmp_Str := Sql_Result[2]; //有可能是Where子句
  if (CompareText(Copy(Tmp_Str, 1, 5), 'Where') = 0) then
    //如果已有where 子句则在后面添加指标约束
  begin
    while (i < ItemArray.Count) do
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标')
        or (CompareText(TReportFieldMessage(ItemArray.Objects[i]).FieldName,
        'count(*)') = 0) then
      begin
        if (TReportFieldMessage(ItemArray.Objects[i]).Constraints = '') then
        begin
          i := i + 1;
          continue;
        end;
        Tmp_Str := Tmp_Str + ' and ';
        Tmp_Str := Tmp_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase) + ' ' +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Constraints);
      end;
      i := i + 1;
    end;
    Sql_Result[2] := Tmp_Str;
  end
  else
  begin
    Sub_Str_Flag := False;
    i := 0;
    Sql_Str := '';
    while (i < ItemArray.Count) do
    begin
      if (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标')
        or (CompareText(TReportFieldMessage(ItemArray.Objects[i]).FieldName,
        'count(*)') = 0) then
      begin
        if (TReportFieldMessage(ItemArray.Objects[i]).Constraints = '') then
        begin
          i := i + 1;
          continue;
        end;
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase) + ' ' +
          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('Where ', Sql_Str, 1); //如果存在指标约束
      Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 5);
      Sql_Result.Insert(2, Sql_Str);
    end;
  end;
  Sql_Result.Insert(1, 'into Summation_Table');
  Tmp_Sql_Result.Clear;
  Tmp_Sql_Result.AddStrings(Sql_Result);

  {--------------------------创建临时表（包含总计行并通过一个主键保证其在表的最末）----------------------------}
  Sql_Result.Clear;
  Sql_Result.Add('IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Summation_Table' + '''' + ')');
  Sql_Result.Add(' DROP TABLE Summation_Table ');
  Sql_Result.Add(' Go ');
  Sql_Result.AddStrings(Tmp_Sql_Result);
  Sql_Result.Add(' Go ');
  Sql_Result.Add(' ALTER TABLE Summation_Table ADD 序号 int IDENTITY (1, 1) NOT NULL PRIMARY KEY ');
  Sql_Result.Add(' Go ');

  if (Alter_To_Null(Tmp_Sql_Result, ItemArray) = False) then
    ShowMessage('在转换非空约束时出现错误');
  ;
  Sql_Result.AddStrings(Tmp_Sql_Result);

  Sql_Result.Add(' Insert into Summation_Table ');

  Sql_Str := 'Select ';
  Sql_Str := Sql_Str + '''' + '总计' + '''' + ',' + ' ';
  i := 0;
  while (i < ItemArray.Count) do
  begin
    if (i = 0) and (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标')
      then
    begin
      ShowMessage('请至少选择一项条件');
      Result := False;
      Exit;
    end
      //      else if ((i = 0) and (TReportFieldMessage(ItemArray.Objects[i]).FieldType <> '指标')) then
      //        Sql_Str:=Sql_Str+''''+'总计'+''''
    else if (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标') or
      (TReportFieldMessage(ItemArray.Objects[i]).FieldName = 'count(*)') then
      //如果别名不为空，则把别名作为选择字段名
    begin
      Sql_Str := Sql_Str + 'Sum(';
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase);
      Sql_Str := Sql_Str + ')';
      Sql_Str := Sql_Str + ' ' +
        TReportFieldMessage(ItemArray.Objects[i]).Aliase;
    end
    else
      Sql_Str := Sql_Str + 'NUll';
    if (i <> ItemArray.Count - 1) then //如果以后还有字段需要加上逗号
      Sql_Str := Sql_Str + ',';
    i := i + 1;
  end;
  Sql_Result.Add(Sql_Str);
  Sql_Result.Add('From Summation_Table');
  Sql_Result.Add(' Go ');

  //  Sql_Result.Clear;
    {----------------------Select子句----------------------------}
  i := 0;
  Sql_Str := 'Select 小计,';
  while (i < ItemArray.Count) do
  begin { while }
    if (TReportFieldMessage(ItemArray.Objects[i]).Aliase <> '') then
      //如果别名不为空，则把别名作为选择字段名
    begin
      if (Judeg_Num(Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase)) =
        True) then
        Sql_Str := Sql_Str + 'Convert(Numeric(38,2),' +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase) + ')';
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase);
    end
    else
    begin
      if (Judeg_Num(Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName)) =
        True) then
        Sql_Str := Sql_Str + 'Convert(Numeric(38,2),' +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName) + ')';
      Sql_Str := Sql_Str +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
    end;
    if (i <> ItemArray.Count - 1) then //如果以后还有字段需要加上逗号
      Sql_Str := Sql_Str + ',';
    i := i + 1;
  end; { while }
  Sql_Result.Add(Sql_Str);
  {----------------------From子句-------------------------------}
  Sql_Result.Add('From Summation_Table');

  Tmp_Sql_Result.Free;
  Result := True;
end;

{-----------------------------判定是否需要两位小数显示--------------------------------------------}

function Judeg_Num(Str: string): Boolean;
begin
  Result := False;
  if (Str = '实销售额') then
    Result := True;
  if (Str = '盒饭销售额') then
    Result := True;
  if (Str = '预交押金额') then
    Result := True;
  if (Str = '结算额') then
    Result := True;
  if (Str = '退货额') then
    Result := True;
  if (Str = '原销售额') then
    Result := True;
  if (Str = '交易金额') then
    Result := True;
  if (Str = '退货商品金额') then
    Result := True;
  if (Str = '存款金额') then
    Result := True;
  if (Str = '押金') then
    Result := True;
  if (Str = '退款金额') then
    Result := True;
  if (Str = '领用金额') then
    Result := True;

end;

{---------------------此函数用来把临时表中非空约束去掉，便于插入总计行（带Null）------------------------------------------}

function Alter_To_Null(var Tmp_Sql_Result: Tstrings; ItemArray: Tstrings):
  Boolean;
var
  i, j: integer;
  Tmp_Str, Col_Name: string;
  Contrast_Array: array[0..45, 0..1] of string;
  AG_ADOQUERY_Contrast: TADOQuery;
begin
  i := 0;
  Tmp_Sql_Result.Clear;
  AG_ADOQUERY_Contrast := TADOQuery.Create(Application);

  {------------------------------把字段_类型对照表里的内容存到数组中-------------------------------------}
  with AG_ADOQuery_Contrast do
  begin
    ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=False;Password='
      + PublicUnit.DBPass + ';User ID=' + PublicUnit.DBUser +
      ';Initial Catalog=';
    ConnectionString := ConnectionString + PublicUnit.DbName + ';Data Source=' +
      PublicUnit.ServerName;
    if Active then
      Close;
    Sql.Clear;
    Sql.Add('Select * From  Column_Name');
    try
      Active := true;
    except
      ShowMessage('无法打开字段类型对照表！');
      Free;
      Result := False;
      Exit;
    end;

    while not EOF do
    begin
      Contrast_Array[i][0] := Trim(FieldByName('字段名').AsString);
      Contrast_Array[i][1] := Trim(FieldByName('类型').AsString);
      i := i + 1;
      next;
    end;
  end; //对应with
  AG_ADOQuery_Contrast.Free;

  i := 0;
  while (i < ItemArray.Count) do
  begin { while }
    j := 0;
    Tmp_Sql_Result.Add('Alter Table Summation_Table');
    Tmp_Str := 'Alter Column ';
    if (TReportFieldMessage(ItemArray.Objects[i]).Aliase <> '') then
      Col_Name := Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase)
    else
      Col_Name := Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
    Tmp_Str := Tmp_Str + Col_Name + ' ';
    while (j < 46) do
    begin { while }
      if (Col_Name = Contrast_Array[j][0]) then
      begin
        Tmp_Str := Tmp_Str + Contrast_Array[j][1] + ' ' + 'NULL';
        break;
      end;
      j := j + 1;
    end; { while }
    if (j = 46) then
    begin
      ShowMessage('无法在字段类型对照表找到对应字段' + Col_Name + '！');
      Result := False;
      Exit;
    end;
    Tmp_Sql_Result.Add(Tmp_Str);
    Tmp_Sql_Result.Add(' Go ');
    i := i + 1;
  end; { while }

  Result := True;
end;

{---------------------------把残余的连接字段，连接表名以及别名项目从Tstring中去掉--------------------------------}

function Clear_Some_Item(var Tstring_Str: Tstrings): Boolean;
var
  i, j: integer;
begin
  i := 0;
  while (i < Tstring_Str.count) do
  begin
    TReportFieldMessage(Tstring_Str.Objects[i]).Aliase := '';
    j := 0;
    while (j <= 5) do
    begin
      TReportFieldMessage(Tstring_Str.Objects[i]).Selected_Item_Table[j] := '';
      j := j + 1;
    end;
    while (j <= 5) do
    begin
      TReportFieldMessage(Tstring_Str.Objects[i]).Selected_Item_Con_In[j] := '';
      j := j + 1;
    end;
    while (j <= 5) do
    begin
      TReportFieldMessage(Tstring_Str.Objects[i]).Selected_Item_Con_Out[j] :=
        '';
      j := j + 1;
    end;
    i := i + 1;
  end;
end;

{---------------------------把没选上的项目从Tstring中去掉--------------------------------}

function Get_Selected_Item(var Tstring_Str: Tstrings): Boolean;
var
  i: integer;
begin
  i := Tstring_Str.Count - 1;
  while (i >= 0) do
  begin { while }
    if (TReportFieldMessage(Tstring_Str.Objects[i]).visible = False) then
      Tstring_Str.Delete(i);
    i := i - 1;
  end; { while }
  i := 0;
  while (i < Tstring_Str.Count) do
  begin
    if (TReportFieldMessage(Tstring_Str.Objects[i]).FieldType = '指标') then
      TReportFieldMessage(Tstring_Str.Objects[i]).Aliase :=
        TReportFieldMessage(Tstring_Str.Objects[i]).FieldName;
    i := i + 1;
  end;
  Result := True;
end;

{-------------------------------判断是否属于聚合函数------------------------------------}

function Judge_Aggregate(i: integer): Boolean;
begin
  Result := False;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName, 1,
    6),
    'count(') = 0 then
    Result := True;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName, 1,
    3),
    'sum') = 0 then
    Result := True;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName, 1,
    3),
    'max') = 0 then
    Result := True;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName, 1,
    3),
    'min') = 0 then
    Result := True;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName, 1,
    3),
    'avg') = 0 then
    Result := True;
end;

{-------------------------------判断是否属于复杂统计函数------------------------------------}
{function Judge_Complex_Stat(i:integer):Boolean;
begin
  Result:=False;
  if CompareText(Copy(TReportFieldMessage(ItemArray.Objects[i]).FieldName,1,2),'((')=0 then Result:=True;
end;
}
{-------------------------------把Source_Str存入Aim_Str中------------------------------------}

function Save_Temp_TString(var Source_Str, Aim_Str: Tstrings): Boolean;
var
  i, j: integer;
begin
  i := 0;
  while (i < Source_Str.Count) do
  begin
    TReportFieldMessage(Aim_Str.Objects[i]).FieldName :=
      TReportFieldMessage(Source_Str.Objects[i]).FieldName;
    TReportFieldMessage(Aim_Str.Objects[i]).FieldType :=
      TReportFieldMessage(Source_Str.Objects[i]).FieldType;
    TReportFieldMessage(Aim_Str.Objects[i]).DataType :=
      TReportFieldMessage(Source_Str.Objects[i]).DataType;
    TReportFieldMessa