/// <summary>
/// 第一次加载报表对象时将报表列头信息缓存起来
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static string GetColumnsInfoString(object obj)
{
var rowBuilder = new StringBuilder();
var cellList = new List <Cell>();
var mobileReport = obj as MobileReport;
if (mobileReport != null)
{
Report report = mobileReport.Report;
// 这里取pageTitle的一个对象来组成报表标题区域的值
Section section = report.Sections[SectionType.PageTitle];
if (DslTransfer.IsMutiReport(report))
{
section = report.Sections[SectionType.Detail];
}
foreach (object gridDetailCell in section.Cells)
{
var cell = gridDetailCell as Cell;
if (cell != null && cell.Visible)
{
cellList.Add(gridDetailCell as Cell);
}
}
}
foreach (Cell cell in cellList)
{
rowBuilder.Append(cell.Name);
rowBuilder.Append(",");
}
rowBuilder.Remove(rowBuilder.Length - 1, 1);
return(rowBuilder.ToString());
}
/// <summary>
/// 将报表对象转换为DSL串
/// </summary>
/// <param name="report"></param>
/// <returns></returns>
private string Parse(MobileReport report)
{
string tableSchema = string.Empty;
string reportStr;
string rowData;
if (report.Page == 0) //第一页
{
tableSchema = DslTransfer.TransferToTableSchema(report);
//Logger logger = Logger.GetLogger("DSLTransfer");
//logger.Info(tableSchema);
//logger.Close();
byte[] bytes = Encoding.Default.GetBytes(tableSchema);
tableSchema = Convert.ToBase64String(bytes);
rowData = RetrieveRowData.TransferToRowData(report);
reportStr = string.Format("<report><tableSchema>{0}</tableSchema>{1}</report>", tableSchema, rowData);
}
else
{
if (report.SemiRows != null)
{
string columnstr = this.GetCacheInfoFromSession(this._responseXml, "columnsString");
byte[] bytes = Convert.FromBase64String(columnstr);
columnstr = Encoding.Default.GetString(bytes);
rowData = RetrieveRowData.TransferToRowData(columnstr, report);
reportStr = string.Format("<report>{0}</report>", rowData);
}
else
{
reportStr = null;
}
}
return(reportStr);
}
internal static void SaveMobileReport(MobileReport mobileReport, ref string filePath)
{
#region 1.将报表格式信息存入数据库中
string tableSchema = DslTransfer.TransferToTableSchema(mobileReport);
string root = GetU8Path();
var directoryPath = Path.Combine(root, @"U8AuditWebSite/MobileReport");
string guid = Guid.NewGuid().ToString();
string fileName = string.Format("ReportData{0}.db", guid);
var cachePath = Path.Combine(root, string.Format(@"U8AuditWebSite/MobileReport/ReportData{0}.db", guid));
Directory.CreateDirectory(directoryPath);
SQLiteConnection.CreateFile(cachePath);
//连接数据库
var conn = new SQLiteConnection();
var connstr = new SQLiteConnectionStringBuilder
{
DataSource = cachePath
};
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
var cmd = new SQLiteCommand();
string sql = "drop table if exists TableSchema";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "CREATE TABLE TableSchema(tableSchema varchar(10000))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = string.Format("INSERT INTO TableSchema VALUES('{0}')", tableSchema);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
#endregion
#region 2.报表数据存入数据库中
// 创建报表格式表
var cellList = new List <Cell>();
bool isMutiReport = false;
Hashtable ht = new Hashtable();
Report report = mobileReport.Report;
var supperCellList = new List <Cell>();
var childCellList = new List <Cell>();
if (mobileReport != null)
{
Section section = report.Sections[SectionType.PageTitle];
foreach (object gridDetailCell in section.Cells)
{
var cell = gridDetailCell as Cell;
if (cell == null || !cell.Visible)
{
continue;
}
if (cell is SuperLabel)
{
isMutiReport = true; //如果列头存在superLable类型，则为多列头格式报表
supperCellList.Add(cell);
}
cellList.Add(gridDetailCell as Cell);
}
if (isMutiReport)
{
cellList = new List <Cell>();
section = report.Sections[SectionType.Detail];
foreach (object gridDetailCell in section.Cells)
{
var cell = gridDetailCell as Cell;
if (cell != null && cell.Visible)
{
cellList.Add(gridDetailCell as Cell);
childCellList.AddRange(from superCell in supperCellList
where (cell.Super != null && cell.Super.Name == superCell.Name)
select cell);
}
}
}
// 这里取pageTitle的一个对象来组成报表标题区域的值
//var section = report.Sections[SectionType.PageTitle];
//foreach (var gridDetailCell in section.Cells)
//{
// var cell = gridDetailCell as Cell;
// if (cell != null && cell.Visible)
// {
// cellList.Add(gridDetailCell as Cell);
// }
//}
}
// 应手机端开发人员要求，这里要通过后台设置各个列的次序等信息，采用f0,f1,f2,f3...等定位列头。
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cellList.Count; i++)
{
sb.Append("f" + i);
sb.Append(" varchar(50)");
sb.Append(",");
}
//foreach (var cell in cellList)
//{
// sb.Append(cell.Name);
// sb.Append(" varchar(50)");
// sb.Append(",");
//}
sb.Remove(sb.Length - 1, 1);
sql = "drop table if exists ReportData";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = string.Format("CREATE TABLE ReportData({0})", sb);
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
if (!isMutiReport)
{
// 将reportData第一行添加报表中文名称等信息。
sb = new StringBuilder();
sb.Append("insert into ReportData VALUES(");
foreach (var cell in cellList)
{
sb.Append(string.Format("'{0}',", cell.Caption));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
sql = sb.ToString();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
else //如果该报表属于多列头报表，首先从Detail中取得子列，并对列头进行特殊处理
{
// 将reportData前两行添加报表中文名称等信息。
sb = new StringBuilder();
sb.Append("insert into ReportData VALUES(");
foreach (var cell in cellList)
{
sb.Append(string.Format("'{0}',", childCellList.Contains(cell) ? cell.Super.Caption : cell.Caption));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
sql = sb.ToString();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sb = new StringBuilder();
sb.Append("insert into ReportData VALUES(");
foreach (var cell in cellList)
{
sb.Append(string.Format("'{0}',", cell.Caption));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
sql = sb.ToString();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
// 插入报表数据
DbTransaction trans = conn.BeginTransaction(); // <--加入事务控制
try
{
foreach (SemiRow semiRow in mobileReport.SemiRows)
{
sb = new StringBuilder();
sb.Append("insert into ReportData VALUES(");
foreach (var cell in cellList)
{
string value;
if (semiRow[cell.Name] == null)
{
value = "";
}
else
{
value = semiRow[cell.Name].ToString();
}
if (value == "'")
{
value = "''";
}
sb.Append(string.Format("'{0}',", value));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
sql = sb.ToString();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
trans.Commit(); // <--提交事务
}
catch (Exception exception)
{
trans.Rollback(); // <--失败则回滚
throw exception;
}
filePath = Path.ChangeExtension(fileName, ".txt");
var filePath1 = Path.ChangeExtension(cachePath, ".txt");
File.Copy(cachePath, filePath1);
//File.Move(cachePath, filePath1);
#endregion
}