📄 ag_report.pas
字号:
unit AG_Report;
interface
uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB;
var
Dem_Count: integer;
FHTJ_Flag: Boolean;
TableStr, MainTable, Kpzt_Constrains, Kpzt_Table, Invariable_Constrains:
string; //TableStr用来保存需要扫描的表名,MainTable表示用来查询的主表,Kpzt_Constrains用来保存卡查询可能涉及的卡状态,Kpzt_Table表示卡片状态约束所在的表（为空则说明在主表里）。
ItemArray, Total_Sql_Result: Tstrings;
function GetSQLStrings(Dimension, Measures: Tstrings; MultiFacts: Boolean; var
Sql_Result: TStrings): Boolean;
function Find_Connect_Condition(TableName, Connect_Col: string; var i: integer;
Dimension: Tstrings): Boolean;
function Change_To_Function(var Replace_Thing: string; Dimension: Tstrings):
Boolean; Overload;
function Change_To_Function(var Replace_Thing: Tstrings; Dimension: Tstrings):
Boolean; Overload;
function Check_If_Need_Modified(i: integer): Boolean;
function Find_Connect_Self(TableName: string): Boolean;
function Find_Col_Pos(ColName: string; var Multi_Tables: TStrings): Boolean;
function Find_With_correspondingTab(TableName, Connect_Col: string; var i:
integer): Boolean;
function Find_MainTable(Dimension, Measures: Tstrings; MultiFacts: Boolean):
TStrings;
function Assemble_Sql(var Sql_Result: Tstrings; Main_Table: string; Dimension:
Tstrings; MainTabCount: integer): Boolean;
function Get_Selected_Item(var Tstring_Str: Tstrings): Boolean;
function Clear_Some_Item(var Tstring_Str: Tstrings): Boolean;
function Judge_Aggregate(i: integer): Boolean;
function Save_Temp_TString(var Source_Str, Aim_Str: Tstrings): Boolean;
function Union_View_Sql(var Sql_Result: Tstrings; Dimension: Tstrings): Boolean;
function Get_Kp_constrains(Dimension: Tstrings; var Kpzt_Constrains, Kpzt_Table,
Invariable_Constrains: string): Boolean;
// function FHSL_Stat(Dimension:Tstrings):String;
function Process_Summation(ItemArray, Dimension: Tstrings; Grouping_Flag:
Boolean; var Sql_Result: TStrings): Boolean;
function Process_Summation_TmpTable(ItemArray, Dimension: Tstrings;
Grouping_Flag: Boolean; var Sql_Result: TStrings): Boolean;
function Alter_To_Null(var Tmp_Sql_Result: Tstrings; ItemArray: Tstrings):
Boolean;
function Judeg_Num(Str: string): Boolean;
function Union_OrderBy(): string;
function Process_FHTJ(Dimension, Measures: Tstrings; var Sql_Result: TStrings):
Boolean;
function Generate_Clause(Tab1, Tab2: string; Dimension: Tstrings; var
Not_In_Clause, On_Clause, Select_Clause: string): Boolean;
function Compute_View(Tab1, Tab2, Compute_Flag: string; Dimension, Measures:
Tstrings; var Temp_Sql_Result: Tstrings): Boolean;
function Assign_Ts(Source_Ts: Tstrings; var Aim_Ts: Tstrings): Boolean;
implementation
uses PublicUnit, AG_Main;
function GetSQLStrings(Dimension, Measures: Tstrings; MultiFacts: Boolean; var
Sql_Result: TStrings): Boolean;
var
i, j, Assign_Count, Change_Count: integer;
Grouping_Flag: Boolean;
Temp_Dimension, Temp_Measures, Main_Tab_str: Tstrings;
//Main_Tab_Str 用来保存可能产生的主表
Temp_ReportField: TReportFieldMessage;
AG_ADOQUERY_Union: TADOQuery;
begin
{--------------------------------初始化------------------------------------------}
j := 0;
Sql_Result.Clear;
MainTable := 'NullTable'; //把主表名赋为空
Kpzt_Constrains := '';
Kpzt_Table := '';
Invariable_Constrains := '';
FHTJ_Flag := False; //是否返还统计
Grouping_Flag := False;
ItemArray := TStringList.Create;
Total_Sql_Result := TStringList.Create;
Main_Tab_str := TStringList.Create;
Temp_Dimension := TStringList.Create;
Temp_Measures := TStringList.Create;
Get_Kp_constrains(Dimension, Kpzt_Constrains, Kpzt_Table,
Invariable_Constrains);
//得到卡查询的卡片状态约束条件以及卡片状态约束所在的维表名
Clear_Some_Item(Dimension); //把上次查询残余的连接字段，连接表名以及别名去掉
Clear_Some_Item(Measures);
Get_Selected_Item(Dimension); //将没有选中的项目去掉
Get_Selected_Item(Measures);
Assign_Count := 0;
while (Assign_Count < Dimension.Count) do
begin
Temp_ReportField := TReportFieldMessage.Create;
Temp_Dimension.AddObject('', TObject(Temp_ReportField));
Assign_Count := Assign_Count + 1;
end;
Assign_Count := 0;
while (Assign_Count < Measures.Count) do
begin
Temp_ReportField := TReportFieldMessage.Create;
Temp_Measures.AddObject('', TObject(Temp_ReportField));
Assign_Count := Assign_Count + 1;
end;
Save_Temp_TString(Dimension, Temp_Dimension); //保存，如果用到union时会用的到
Save_Temp_TString(Measures, Temp_Measures);
ItemArray.AddStrings(Dimension);
ItemArray.AddStrings(Measures);
Main_Tab_str := Find_MainTable(Dimension, Measures, MultiFacts);
//将可能的主表值赋给 Main_Tab_Str
{------------------------------按照主表的数量生成相应的SQL语句-------------------------------------}
while (j < Main_Tab_Str.Count) do
begin
ItemArray.Clear;
ItemArray.AddStrings(Dimension);
ItemArray.AddStrings(Measures);
if (Main_Tab_Str[j] = 'NullTable') then
begin
ShowMessage('主表查找返回空值，请检查错误！');
Sql_Result.Clear;
Result := False;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
Exit;
end;
MainTable := Main_Tab_Str[j];
//ShowMessage('主表为'+MainTable);
Dem_Count := 0;
while (Dem_Count < ItemArray.Count) do //比较每一个选中项在表中有没有
begin
i := 0;
if Find_Connect_Condition(MainTable, 'NullCol', i, Dimension) = False then
begin
ShowMessage(TReportFieldMessage(ItemArray.Objects[Dem_Count]).FieldName
+
' 找不到连接路径 ');
Sql_Result.Clear;
Result := False;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
Exit;
end;
Dem_Count := Dem_Count + 1;
end;
if Assemble_Sql(Sql_Result, Main_Tab_str[j], Dimension, Main_Tab_Str.Count)
= False then //拼接生成SQL语句的代码
begin
ShowMessage('生成SQL语句时发生错误！');
Sql_Result.Clear;
Result := False;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
Exit;
end;
if (Main_Tab_Str.Count <> 1) and (j <> Main_Tab_Str.Count - 1) then
begin
Sql_Result.Add('Union'); //多表连接查询
Save_Temp_TString(Temp_Dimension, Dimension);
//取出原始的Dimension和Measures，进行下一个主表的查找
Save_Temp_TString(Temp_Measures, Measures);
Change_Count := 0;
while (Change_Count < Measures.Count) do //把指标名称换成第二次查询所需要的
begin
Change_To_Function(TReportFieldMessage(Measures.Objects[Change_Count]).FieldName, Dimension);
Change_Count := Change_Count + 1;
end;
end;
j := j + 1;
end;
//Sql_Result.SaveToFile('c:\kerim.sql');
{--------------------如果是多表Union查询，需要创建视图，然后才能排序---------------------}
if (Main_Tab_Str.Count > 1) then
begin
AG_ADOQUERY_Union := TADOQuery.Create(Application);
Sql_Result.Insert(0, 'create view Union_Query_View as');
with AG_ADOQUERY_Union 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 = ' + '''' + 'Union_Query_View' + '''' + ')');
Sql.Add(' DROP VIEW Union_Query_View ');
try
Execsql;
except
ShowMessage('无法删除上次Union查询视图');
Sql_Result.Clear;
Result := False;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
Free;
Exit;
end;
Total_Sql_Result.Add('IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ' + '''' + 'Union_Query_View' + '''' + ')');
Total_Sql_Result.Add(' DROP VIEW Union_Query_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);
try
Execsql;
except
ShowMessage('无法为Union查询创建视图');
Sql_Result.Clear;
Total_Sql_Result.Clear; //new
Result := False;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
Free;
Exit;
end; //try
end; //with
Union_View_Sql(Sql_Result, Dimension);
// Total_Sql_Result.AddStrings(Sql_Result);
// Total_Sql_Result.Add(' Go ');
end; //if
//Sql_Result.SaveToFile('c:\sql.sql');
if (FHTJ_Flag = True) then
begin
Process_FHTJ(Dimension, Measures, Sql_Result);
end;
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;
end;
i := i + 1;
end;
//ShowMessage(Trim(TReportFieldMessage(ItemArray.Objects[ItemArray.Count-2]).Aliase));
//ShowMessage(Trim(TReportFieldMessage(ItemArray.Objects[ItemArray.Count-2]).FieldName));
//这一测试说明别名肯定是一样的，FieldName在多事实表（主表）查询是不同的。
{----------这一部分将结果保存成视图或临时表，然后取出明细项和最后的总计项----------}
if (Grouping_Flag = True) then
begin
if Process_Summation_TmpTable(ItemArray, Dimension, Grouping_Flag,
Sql_Result) = False then
ShowMessage('在Summation临时表中产生错误');
end
else
begin
if Process_Summation(ItemArray, Dimension, Grouping_Flag, Sql_Result) = False
then
ShowMessage('在Summation视图中产生错误');
end;
Total_Sql_Result.AddStrings(Sql_Result);
Sql_Result.Clear;
Sql_Result.AddStrings(Total_Sql_Result);
Result := True;
Main_Tab_Str.Free;
ItemArray.Free;
Temp_Dimension.Free;
Temp_Measures.Free;
Temp_ReportField.Free;
end;
{----------------------------此函数确定主事实表--------------------------------}
function Find_MainTable(Dimension, Measures: Tstrings; MultiFacts: Boolean):
Tstrings;
var
i: integer;
Union_Flag: Boolean; //如为真，则表示已经确定了两个主表。
ss, Main_Tab_Temp: string;
Main_Table_Str, Measures_Temp: Tstrings;
AG_ADOQUERY1: TADOQuery;
label
Jump_Flag;
begin
i := 0;
Union_Flag := False;
Main_Table_Str := TStringList.Create;
Measures_Temp := TStringList.Create;
AG_ADOQUERY1 := 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 < Measures.Count) do
begin
Measures_Temp.Add(Trim(TReportFieldMessage(Measures.Objects[i]).FieldName));
i := i + 1;
end;
Jump_Flag:
i := 0;
Main_Tab_Temp := 'NullTable';
while (i < Measures.Count) do //比较每一个选中项在表中有没有
begin
with AG_ADOQuery1 do
begin
ConnectionString := ss;
if Active then
Close;
Sql.Clear;
Sql.Add('Select * from TableDemention where 字段名 = ' + '''' +
Measures_Temp[i] + '''' + ' and 字段类型 =' + '''' + '指标' + '''');
try
Active := true;
except
ShowMessage('无法打开库表管理表进行主表确定！');
Main_Table_Str.Clear;
Main_Table_Str.Add('NullTable');
Result := Main_Table_Str;
Free;
Exit;
end;
while not EOF do
begin
if (RecNo <> 1) then
begin
ShowMessage('查找一个指标时返回了两条记录，请检查错误！');
Main_Table_Str.Clear;
Main_Table_Str.Add('NullTable');
Result := Main_Table_Str;
Free;
Exit;
Exit;
end;
if ((Main_Tab_Temp <> 'NullTable') and (Main_Tab_Temp <>
Trim(FieldByName('表名').AsString))) then
begin
ShowMessage('不同指标确定的主表不同，请检查错误！');
Main_Table_Str.Clear;
Main_Table_Str.Add('NullTable');
Result := Main_Table_Str;
Free;
Exit;
end;
Main_Tab_Temp := Trim(FieldByName('表名').AsString);
next;
end; //对应while
end; //对应with
i := i + 1;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -