private static string GetWhere(TableModel_M tm)
{
string where;
//if (!tm.IsFileTable)
//{
switch (tm.Type)
{
case enTypeReport.Table:
case enTypeReport.Object:
{
where = "@WhereQuestionSQL";
} break;
default:
{
where = "1 = 1";
} break;
}
if (tm.Table.TypeTable == Interfaces.TypeTable.LayerMap)
{
//where += string.Format(" AND ST_IsValid(\"{0}\")", tm.Table.geomFieldName);
}
//}
//else
//{
// where = "1 = 1";
//}
return(where);
}
public void OpenReport(IReportItem_M report, IFilterTable filter)
{
var reportVM = new Report_VM((report as ReportItem_M), filter);
switch (report.Type)
{
case enTypeReport.Table:
case enTypeReport.Object:
{
var ti = Program.app.getTableInfo((int)report.IdTable);
var tm = new TableModel_M(ti, report.Type);
tm.Filter = filter;
reportVM.Reports.Add(tm);
} break;
case enTypeReport.All:
{
foreach (var item in Program.app.tables_info)
{
var tm = new TableModel_M(item, enTypeReport.All);
reportVM.Reports.Add(tm);
}
} break;
}
reportVM.OpenReport();
}
internal void OpenDesignAll(ReportItem_M report, IFilterTable filter)
{
if (report.Type != enTypeReport.All)
{
throw new Exception(Resources.Report_M_ErrorDesign);
}
var reportVM = new Report_VM((ReportItem_M)report.Clone(), filter);
foreach (var item in Program.app.tables_info)
{
var tm = new TableModel_M(item, enTypeReport.All);
reportVM.Reports.Add(tm);
}
reportVM.OpenDesign();
}
public void OpenDesignTable(IReportItem_M report, IFilterTable filter)
{
if (report.Type != enTypeReport.Table)
{
throw new Exception(Resources.Report_M_ErrorDesign);
}
var reportVM = new Report_VM((report as ReportItem_M), filter);
var ti = Program.app.getTableInfo((int)report.IdTable);
var tm = new TableModel_M(ti, enTypeReport.Table);
tm.Filter = filter;
reportVM.Reports.Add(tm);
reportVM.OpenDesign();
}
private static string GetOrderby(TableModel_M tm)
{
string orderby = string.Empty;
if (!tm.IsFileTable)
{
switch (tm.Type)
{
case enTypeReport.Table:
case enTypeReport.Object:
{
orderby = "ORDER BY @OrderQuestionSQL";
} break;
}
}
return(orderby);
}
private static TableDataSource CreateTablePhotoDataSource(TableModel_M tmPhoto)
{
var reportTable = new TableDataSource()
{
Name = tmPhoto.Table.nameDB + "_photo",
TableName = tmPhoto.Table.nameDB + "_photo",
Alias = tmPhoto.Table.nameMap + "_photo"
};
var fileInfo = tmPhoto.Table.PhotoInfo;
string sqlStr;
using (SqlWork sqlCmd = new SqlWork())
{
sqlCmd.sql = string.Format("SELECT sys_scheme.get_sql_for_table({0});", tmPhoto.Table.idTable);
sqlStr = sqlCmd.ExecuteScalar <string>();
}
string fileSqlString = string.Format(@"
SELECT
""t!file"".id,
""t!file"".""{0}"" AS id_obj,
""t!file"".dataupd AS date_update,
""t!file"".img_preview AS preview,
""t!file"".""{1}"" AS file_body,
""t!file"".file_name AS file_name
FROM
(
SELECT *
FROM
(
{2}
) AS ""t!gen_sql""
WHERE {3}
) AS ""t!main""
RIGHT JOIN ""{4}"".""{5}"" AS ""t!file""
ON (""t!main"".{6} = ""t!file"".""{0}"")
WHERE ""t!file"".is_photo = true
",
fileInfo.namePhotoField,
fileInfo.namePhotoFile,
sqlStr,
"{0}",
tmPhoto.Table.nameSheme,
fileInfo.namePhotoTable,
tmPhoto.Table.pkField);
string where = GetWhere(tmPhoto);
reportTable.SelectCommand = string.Format(fileSqlString, where);
using (var sqlCmd = new SqlWork())
{
sqlCmd.sql = string.Format(fileSqlString, "1 = 1") + " LIMIT 0";
DataTable fileTable = sqlCmd.ExecuteGetTable();
foreach (DataColumn field in fileTable.Columns)
{
var ReportColumn = new Column();
ReportColumn.Name = field.Caption;
ReportColumn.DataType = field.DataType;
reportTable.Columns.Add(ReportColumn);
}
}
// Заполняем названия колонок
foreach (Column item in reportTable.Columns)
{
switch (item.Name)
{
case "id_obj": item.Alias = Resources.Report_VM_photoIdObj; break;
case "date_update": item.Alias = Resources.Report_VM_photoDateUpdate; break;
case "preview": item.Alias = Resources.Report_VM_photoPreview; break;
case "file_body": item.Alias = Resources.Report_VM_photoFileBody; break;
case "file_name": item.Alias = Resources.Report_VM_photoFileName; break;
}
}
#if DEBUG
//if (tmPhoto.Type != enTypeReport.All)
//UpdateDateTable(fileTable);
#endif
var paramWhere = new CommandParameter()
{
Name = "WhereQuestionSQL", DataType = 2, Value = true
};
reportTable.Parameters.Add(paramWhere);
return(reportTable);
}
private static TableDataSource CreateTableDataSource(TableModel_M tm)
{
var reportTable = new TableDataSource()
{
Name = tm.Table.nameDB,
TableName = tm.Table.nameDB,
Alias = tm.Table.nameMap.Replace(".", "_")
};
string sqlStr;
using (SqlWork sqlCmd = new SqlWork())
{
sqlCmd.sql = string.Format("SELECT sys_scheme.get_sql_for_table_with_geom({0}, {1});", tm.Table.idTable, Program.srid);
sqlStr = sqlCmd.ExecuteScalar <string>();
}
string tableSqlStr = string.Format("SELECT \n\t*\n FROM\n\t ({0}) as ss\nWHERE {2}\n{1}", sqlStr, "{0}", "{1}");
string where = GetWhere(tm);
string orderby = GetOrderby(tm);
reportTable.SelectCommand = string.Format(tableSqlStr, orderby, where);
var paramWhere = new CommandParameter()
{
Name = "WhereQuestionSQL",
DataType = 2,
Value = true
};
var paramOrder = new CommandParameter()
{
Name = "OrderQuestionSQL",
DataType = 19,
Value = tm.Table.pkField
};
reportTable.Parameters.Add(paramWhere);
reportTable.Parameters.Add(paramOrder);
using (SqlWork sqlCmd = new SqlWork(true))
{
DataTable table;
sqlCmd.sql = string.Format(tableSqlStr, "", "1 = 1") + " LIMIT 0";
try
{
table = sqlCmd.ExecuteGetTable();
foreach (DataColumn field in table.Columns)
{
var ReportColumn = new Column();
ReportColumn.Name = field.Caption;
ReportColumn.DataType = field.DataType;
reportTable.Columns.Add(ReportColumn);
}
}
catch (Exception)
{
throw;
}
}
// Заполняем названия колонок
foreach (Column item in reportTable.Columns)
{
var field = tm.Table.ListField.FirstOrDefault(f => f.nameDB == item.Name);
if (field != null)
{
item.Alias = field.nameMap;
continue;
}
else if (item.Name.StartsWith("id!"))
{
var fieldKey = tm.Table.ListField.FirstOrDefault(f => ("id!" + f.nameDB) == item.Name);
item.Alias = string.Format(Resources.Report_VM_KeyAliasFormat, fieldKey.nameMap);
}
else if (item.Name.StartsWith("geom!"))
{
switch (item.Name)
{
case "geom!type": item.Alias = Resources.Report_VM_geomType; break;
case "geom!area": item.Alias = Resources.Report_VM_geomArea; break;
case "geom!perimeter": item.Alias = Resources.Report_VM_geomPerimeter; break;
case "geom!length": item.Alias = Resources.Report_VM_geomLength; break;
case "geom!center_x": item.Alias = Resources.Report_VM_geomCenter_x; break;
case "geom!center_y": item.Alias = Resources.Report_VM_geomCenter_y; break;
}
}
}
//foreach (var field in )
//{
// var column = table.Columns[field.nameDB];
// if (column != null)
// column.Caption = field.nameMap;
// column = table.Columns["id!" + field.nameDB];
// if (column != null)
// column.Caption = field.nameMap + "(id)";
//}
return(reportTable);
}
private static TableDataSource FindTablePhotoDataSource(PostgresDataConnection pgConnFR, TableModel_M tm)
{
foreach (TableDataSource item in pgConnFR.Tables)
{
if (item.Name == tm.Table.nameDB + "_photo")
{
return(item);
}
}
return(null);
}
private FastReport.Report CreateReport()
{
var report = new Report();
if (!_reportItemM.IsNew)
{
report.LoadFromString(_reportItemM.Body);
}
var postgresConn = FindConnection(report, "Current data") as PostgresDataConnection;
if (postgresConn == null)
{
postgresConn = new PostgresDataConnection()
{
Name = "Current data",
Enabled = true
};
report.Dictionary.Connections.Add(postgresConn);
}
postgresConn.ConnectionString = Program.connString.ToString();
Interfaces.tablesInfo tableInfo = null;
if (_reportItemM.Type == enTypeReport.Table || _reportItemM.Type == enTypeReport.Object)
{
tableInfo = Program.app.getTableInfo(_filter.IdTable);
}
if (_isDesign)
{
cti.ThreadProgress.ShowWait("CreateReport");
foreach (var item in _reports)
{
try
{
var table = FindTableDataSource(postgresConn, item);
if (table == null)
{
table = CreateTableDataSource(item);
table.Enabled = (_reportItemM.Type == enTypeReport.Object || _reportItemM.Type == enTypeReport.Table);
postgresConn.Tables.Add(table);
}
if (item.Table.photo)
{
var tmPhoto = new TableModel_M(item.Table, item.Type, true);
var tablePhoto = FindTablePhotoDataSource(postgresConn, tmPhoto);
if (tablePhoto == null)
{
tablePhoto = CreateTablePhotoDataSource(tmPhoto);
tablePhoto.Enabled = false;
postgresConn.Tables.Add(tablePhoto);
}
//var pkColumn = table.Columns.FindByName(item.Table.pkField);
//var pkColumnPhoto = tablePhoto.Columns.FindByName("id_obj");
//var relName = table.TableName + "_" + item.Table.nameMap + "_photo";
//RelationCollection
//var ds = new DataSet();
//ds.Tables.Add(table);
//ds.Tables.Add(tablePhoto);
//var d = new DataRelation(relName, pkColumn, pkColumnPhoto);
//report.RegisterData(d, relName);
// Declare all the tables that will have relations
var nameRelation = table.TableName + "_" + item.Table.nameMap + "_photo";
Relation rel = report.Dictionary.Relations.FindByName(nameRelation);
if (rel == null)
{
var ParentTable = postgresConn.FindObject(item.Table.nameDB) as TableDataSource;
var PhotoTable = postgresConn.FindObject(item.Table.nameDB + "_photo") as TableDataSource;
// Declare the relations
rel = new Relation();
rel.Enabled = false;
rel.Name = nameRelation;
rel.ParentDataSource = ParentTable;
rel.ChildDataSource = PhotoTable;
rel.ParentColumns = new string[] { item.Table.pkField };
rel.ChildColumns = new string[] { Resources.Report_VM_photoIdObj };
report.Dictionary.Relations.Add(rel);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message, "NpgsqlException");
continue;
}
}
cti.ThreadProgress.Close("CreateReport");
}
//if (tableInfo != null)
//report.SetParameterValue("idCurrentObject", "\"" + tableInfo.pkField + "\" = " + _filter.IdObj);
return(report);
}
