ag_report.pas

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

    end;
    i := i + 1;
  end;

  if (Sub_Str_Flag = True) then
  begin
    Insert('Order By ', Sql_Str, 1); //如果存在排序
    Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 2);
  end;
  Sql_Result.Add(Sql_Str);

  NoOrder:
  Result := True;
end;

{---------------------------------为Union查询产生的视图添加可能的排序语句-------------------------------------}

function Union_OrderBy(): string;
var
  Sub_Str_Flag: Boolean;
  i: integer;
  Sql_Str: string;
begin
  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
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase)
      else
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName);
      Sql_Str := Sql_Str + ' ' +
        Trim(TReportFieldMessage(ItemArray.Objects[i]).OrderType);
      Sql_Str := Sql_Str + ', ';
      Sub_Str_Flag := True;
    end;
    i := i + 1;
  end;
  if (Sub_Str_Flag = True) then
  begin
    Insert('Order By ', Sql_Str, 1); //如果存在排序
    Sql_Str := Copy(Sql_Str, 1, Length(Sql_Str) - 2);
  end;
  Result := Sql_Str
end;

{--------------------------------为union查询生成视图查询语句--------------------------------------}

function Union_View_Sql(var Sql_Result: Tstrings; Dimension: Tstrings): Boolean;
var
  Sub_Str_Flag, Grouping_Flag: Boolean;
  i: integer;
  Sql_Str: string;
begin

  Sql_Result.clear;
  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
      //如果别名不为空，则把别名作为选择字段名
      Sql_Str := Sql_Str + Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase)
    else
      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 <> '') and
      (TReportFieldMessage(ItemArray.Objects[i]).FieldType = '指标') 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 }

  {-----------------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 ) then
    begin
      Grouping_Flag:=True;
      Sql_Str:=Sql_Str+', Grouping(';
      if (TReportFieldMessage(ItemArray.Objects[i]).Aliase = '') then //如果别名不为空，则把别名作为选择字段名，反之用字段名
        Sql_Str:=Sql_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName)
      else
        Sql_Str:=Sql_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase);
      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;
}
  Sql_Result.Add(Sql_Str);

  Sql_Result.Add('From Union_Query_View');

  {----------------------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
        //如果别名为空，把别名作为选择字段名，反之用字段名
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName)
      else
        Sql_Str := Sql_Str +
          Trim(TReportFieldMessage(ItemArray.Objects[i]).Aliase);
      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);

  Sql_Result.Add(Union_OrderBy());

end;

{----------------------------------处理卡片状态的约束------------------------------------------}

function Get_Kp_constrains(Dimension: Tstrings; var Kpzt_Constrains, Kpzt_Table,
  Invariable_Constrains: string): Boolean;
var
  i: integer;
begin
  i := 0;
  while (i < Dimension.Count) do
  begin
    if (TReportFieldMessage(Dimension.Objects[i]).FieldName = '交易方式') and
      (TReportFieldMessage(Dimension.Objects[i]).Constraints = 'In (' + '''' +
      'IC'
      + '''' + ')') then
    begin
      i := i + 1;
      break;
    end;
    i := i + 1;
  end;

  //i未清零，注意report表中顺序一定要正确
  while (i < Dimension.Count) do
  begin { while }
    if (TReportFieldMessage(Dimension.Objects[i]).FieldName = '卡片状态所在表')
      then
    begin
      Kpzt_Table := TReportFieldMessage(Dimension.Objects[i]).Constraints;
      break;
    end;
    Kpzt_Constrains := Kpzt_Constrains +
      TReportFieldMessage(Dimension.Objects[i]).FieldName + ' ' +
      TReportFieldMessage(Dimension.Objects[i]).Constraints + ' ';
    Kpzt_Constrains := Kpzt_Constrains + ' and ';
    i := i + 1;
  end; { while }

  i := 0;
  while (i < Dimension.Count) do
  begin
    if (TReportFieldMessage(Dimension.Objects[i]).FieldName = '不变约束') then
    begin
      Invariable_Constrains :=
        TReportFieldMessage(Dimension.Objects[i]).Constraints;
      break;
    end;
    i := i + 1;
  end;

end;

{-----------------------------------统计返还数量--------------------------------------------}
{function FHSL_Stat(Dimension:Tstrings):String;
var
  i:integer;
  Constrain_Str,Result_Str:string;
  Sub_Str_Flag:Boolean;
begin
  i:=0;
  Constrain_Str:='';
  Sub_Str_Flag:=False;
  while (i < Dimension.Count) and (TReportFieldMessage(ItemArray.Objects[i]).FieldName <> '返还数量') do
  begin
    if (TReportFieldMessage(ItemArray.Objects[i]).Constraints = '') then
    begin
      i:=i+1;
      continue;
    end;
    Constrain_Str:=Constrain_Str+' and ';
    Constrain_Str:=Constrain_Str+Trim(TReportFieldMessage(ItemArray.Objects[i]).FieldName)+' '+Trim(TReportFieldMessage(ItemArray.Objects[i]).Constraints);
    i:=i+1;
    Sub_Str_Flag:=True;
  end;

  Result_Str:='(';

  ---------------------统计退卡数量--------------------------
  Result_Str:=Result_Str+'(select count(*) from SCSJ_TSKXX where 卡片状态='+''''+'退卡'+'''';
  if (Constrain_Str <> '') then Result_Str:=Result_Str+Constrain_Str;
  Result_Str:=Result_Str+')';

  ---------------------统计已销售卡数量---------------------------
  Result_Str:=Result_Str+' -(select count(*) from SCSJ_TSKXX where 卡片状态='+''''+'使用'+'''';
  if (Constrain_Str <> '') then Result_Str:=Result_Str+Constrain_Str;
  Result_Str:=Result_Str+')';

  {----------------------统计领用数量---------------------------
  Result_Str:=Result_Str+' +(select count(*) from SCSJ_TLYXX';
  if (Sub_Str_Flag = True) then  Result_Str:=Result_Str+' where ';
  if (Constrain_Str <> '') then
  begin
    Constrain_Str:=Copy(Constrain_Str,5,Length(Constrain_Str));
    Result_Str:=Result_Str+Constrain_Str;
  end;
  Result_Str:=Result_Str+')';

  Result_Str:=Result_Str+')';
  Result:=Result_Str;

end;
}

{-----------------将生成的语句存成视图，并取出明细行和小计行-----------------------}

function Process_Summation(ItemArray, Dimension: Tstrings; Grouping_Flag:
  Boolean; var Sql_Result: TStrings): Boolean;
var
  i: integer;
  Sub_Str_Flag: Boolean;
  Sql_Str, Add_Top_Str: string;
  AG_ADOQUERY_Summation: TADOQuery;
begin
  Sub_Str_Flag := False;
  AG_ADOQUERY_Summation := TADOQuery.Create(Application);
  Add_Top_Str := Sql_Result[0];
  Insert(' top 4294967295 ', Add_Top_Str, 8);
  Sql_Result[0] := Add_Top_Str;
  {--------------------------创建视图----------------------------}
  Sql_Result.Insert(0, 'create view Summation_View as');
  with AG_ADOQUERY_Summation 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('IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ' + '''' + 'Summation_View' + '''' + ')');
    Sql.Add(' DROP VIEW Summation_View ');
    try
      Execsql;
    except
      ShowMessage('无法删除上次产生的Summation_View视图');
      Result := False;
      Free;
      Exit;
    end;
    Total_Sql_Result.Add('IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ' + '''' + 'Summation_View' + '''' + ')');
    Total_Sql_Result.Add(' DROP VIEW Summation_View ');
    Total_Sql_Result.Add(' Go ');
    Total_Sql_Result.AddStrings(Sql_Result);
    Total_Sql_Result.Add(' Go ');

    if Active then
      Close;
    Sql.Clear;
    Sql.Assign(Sql_Result);
    //Sql.SaveToFile('c:\sql1.sql');
    try
      Execsql;
    except
      ShowMessage('无法创建Summation_View视图，有可能是卡统计中统计条件不足或其他原因。');
      Result := False;
      Free;
      Exit;
    end; //try
  end; //with

  AG_ADOQUERY_Summation.Free;
  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_View');
  {----------------------Where------------------------------------}
  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);
  end;
  Sql_Result.Add(Sql_Str);

  Result := True;
end;

{-----------------将生成的语句存成临时表，并取出明细行和小计行-----------------------}

function Process_Summation_Tmptable(ItemArray, Dimension: Tstrings;
  Grouping_Flag: Boolean; var Sql_Result: TStrings): Boolean;
var
  i: integer;
  Sub_Str_Flag: Boolean;
  Sql_Str, Add_Top_Str, Tmp_Str: string;
  Tmp_Sql_Result: Tstrings;
begin
  Result := False;