/// <summary>
/// 预估下载的文件大小
/// </summary>
/// <param name="strSql">SQL</param>
/// <param name="ingDbServerId">服务器ID</param>
/// <returns>返回是否offline下载</returns>
private int IntDataSize(string strSql, long?ingDbServerId)
{
strSql = "select count(0) from (" + strSql + ")td";
//获得参数
//string code = Request["code"].Trim();
//string queryParams = Request["queryParams"];//查询条件
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
int esitSize = 0; //获得文件预估大小
#region 得到执行sql语句
try
{
object objSize = 0;
if (ingDbServerId == null)
{
string dbType = DbHelper.GetCurConnection().DbType.ToString();
objSize = DbHelper.ExecuteScalar(strSql);
}
else
{
objSize = _dbServerAppService.ExecuteScalar(ingDbServerId.Value, strSql, ref err);//获取数据总长度
}
esitSize = (objSize == null || objSize.ToString().Trim() == "" ? 0 : Convert.ToInt32(objSize));
if (err.IsError)
{
throw new Exception(err.Message);
}
}
catch (Exception ex)
{
//throw new Abp.UI.UserFriendlyException(ex.Message);
throw new Exception(ex.Message);
}
#endregion
return(esitSize);
}
public void Add(ImportLogInput input)
{
//用户ID
var userId = AbpSession.UserId;
//获取外导表
var impTb = _impTbRepository.FirstOrDefault(a => a.Code == input.Code);
if (impTb == null)
{
"找不到表信息".ErrorMsg();
}
//获取上传文件
var file = _filesRepository.FirstOrDefault(a => a.Id == input.FileId.Value);
if (file == null || (file != null && !File.Exists(file.Path)))
{
"文件不存在!".ErrorMsg();
}
var dbServer = impTb.DbServer;//得到数据库对像
//设置实例名称
string caseTableName = impTb.EnTableName;
switch (impTb.Rule)
{
case "1":
caseTableName = impTb.EnTableName + "_" + DateTime.Now.ToString("yyMMdd");
break;
case "3":
caseTableName = impTb.EnTableName + "_" + dbServer.User.ToString();
break;
case "4":
caseTableName = impTb.EnTableName + "_" + input.Suffix.ToString();
break;
}
//创建外导表实例
var impTbCase = new ImpTbCase()
{
CaseTableName = caseTableName,
ImpTbId = impTb.Id
};
var impTbCaseId = _impTbCaseRepository.InsertAndGetId(impTbCase);
//批次代码和批次名
var module = _moduleRepository.FirstOrDefault(a => a.Code == input.ModuleCode);
var batchName = module != null ? module.Name : file.Name;
var batchHeader = DateTime.Now.ToString("yyMMdd");
var batchNumber = 1;
var implogNext = _importLogRepository.GetAll().OrderByDescending(a => a.Id)
.FirstOrDefault(a => a.UserId == userId && a.Code.Contains(batchHeader));
if (implogNext != null)
{
var batchArr = implogNext.Code.Split('-');
if (batchArr.Count() > 0)
{
if (batchArr[0] == batchHeader)
{
batchNumber = Int32.Parse(batchArr[1]) + 1;
}
}
}
batchName += "-" + batchHeader;
var batchCode = string.Format("{0}-{1}", batchHeader, batchNumber);
//创建外导信息日志
var impLog = new ImportLog()
{
FileId = input.FileId.Value,
FileName = file.Name,
ImpMode = input.ImportMode,
ImpTbId = impTb.Id,
ImpTbCaseId = impTbCaseId,
CaseTableName = caseTableName,
Duration = 0,
Code = batchCode,
Name = batchName,
UserId = userId.Value
};
var result = _importLogRepository.Insert(impLog);
//数据导入操作
if (result != null)
{
//创建表执行脚本
var sqlScript = string.Format(impTb.Sql, result.CaseTableName);
try
{
#region 判断表是否存在
string strSql = Easyman.Common.DatabaseHelper.GetIsDataBaseTableSql(dbServer.DataCase, caseTableName, dbServer.User);
ErrorInfo er = new ErrorInfo();
object objReValu = _dbServerAppService.ExecuteScalar(dbServer.Id, strSql, ref er);
if (Convert.ToInt32(objReValu) <= 0)
{
_dbServerAppService.Execute(dbServer.Id, sqlScript);
}
#endregion
}
catch (Exception ex)
{
if (ex.Message.IndexOf("名称已由现有对象使用") <= -1)
{
//throw new ArgumentNullException(ex.Message);
throw new Exception("sql执行失败：" + ex.Message);
}
}
if (result.ImpMode == "离线导入") //0.判断导入模式是否离线，离线则进度任务调度中去，否则继续执行
{
//进入离线模式
return;
}
//输入导入操作
var importReult = ImportData(file.Path, impTb, batchCode, result.CaseTableName, dbServer.Id);
if (importReult)
{
File.Delete(file.Path);
}
//old code
//var connectionString = string.Empty;
//DatabaseType databaseType = DatabaseType.Oracle;
//switch (dbType)
//{
// default://默认oracle数据库
// connectionString = string.Format("Data Source={0}:{1}/{2};User Id={3};Password={4};Connect Timeout =200",
// dbServer.Ip, dbServer.Port, dbServer.DataCase, dbServer.User, dbServer.Password);
// break;
// case "db2":
// connectionString = dbServer.Port == null ?
// string.Format("Driver={IBM DB2 ODBC DRIVER};Server={0}:{1};DSN={2};UID={3};PWD={4};Protocol=TCPIP",
// dbServer.Ip, dbServer.Port, dbServer.DataCase, dbServer.User, dbServer.Password)
// : string.Format("Driver={IBM DB2 ODBC DRIVER};Server={0};DSN={1};UID={2};PWD={3};Protocol=TCPIP",
// dbServer.Ip, dbServer.DataCase, dbServer.User, dbServer.Password);
// databaseType = DatabaseType.Db2;
// break;
// case "mysql":
// connectionString = string.Format("Server={0};Port={1};Database={2];Uid={3};Pwd={4];",
// dbServer.Ip, dbServer.Port, dbServer.DataCase, dbServer.User, dbServer.Password);
// databaseType = DatabaseType.MySql;
// break;
// case "sqlserver":
// connectionString = dbServer.Port == null ?
// string.Format("server={0};uid={2};pwd={3};database={4}", dbServer.Ip, dbServer.User,
// dbServer.Password, dbServer.DataCase)
// : string.Format("server={0}:{1};uid={2};pwd={3};database={4}", dbServer.Ip, dbServer.Port,
// dbServer.User, dbServer.Password, dbServer.DataCase);
// databaseType = DatabaseType.SqlServer;
// break;
//}
//if (!string.IsNullOrEmpty(connectionString))
//{
// //创建表执行脚本
// var sqlScript = string.Format(impTb.Sql, result.CaseTableName);
// //创建表操作
// CreateTable(connectionString, sqlScript, databaseType);
// //执行数据导入操作
// if (result.ImpMode == "离线导入") //0.判断导入模式是否离线，离线则进度任务调度中去，否则继续执行
// {
// //进入离线模式
// return;
// }
// //输入导入操作
// var importReult = ImportData(file.Path, impTb, batchCode, result.CaseTableName,
// connectionString, dbType);
// if (importReult) File.Delete(file.Path);
//}
}
}
/// <summary>
/// 根据code代码及传入条件拼凑和执行sql
/// </summary>
/// <param name="code"></param>
/// <param name="rows"></param>
/// <param name="page"></param>
/// <param name="queryParams"></param>
/// <param name="sidx"></param>
/// <param name="sord"></param>
/// <param name="err"></param>
/// <returns></returns>
public string ExcuteReportSql(string code, int rows, int page, string queryParams, string sidx, string sord, ref ErrorInfo err)
{
if (!string.IsNullOrEmpty(code))
{
var ent = _reportRepository.FirstOrDefault(p => p.Code == code);
if (ent != null)
{
string dbType = ""; //数据库种类
int records = 0; //总数量
DataTable endTable = new DataTable(); //查询结果
#region 获取数据库
DbServerOutput dbServer = new DbServerOutput();
//中心库
if (ent.DbServerId == null)
{
var connection = ConnectionMsg.GetCurConnection();//获取当前承载库连接信息
dbType = connection.DbType.ToString();
dbServer = null;
}
else
{
dbServer = _dbServerApp.GetDbServer(ent.DbServerId.Value);
dbType = dbServer.DbTypeName;
if (dbServer == null)
{
err.IsError = true;
err.Message = "未找到当前code对应的数据库编号[" + ent.DbServerId.Value + "]！";
return("");
}
}
#endregion
//替换全局变量
string sql = ReplaceGlobalVar(ent.Sql);
//替换变量（内置或自定义、外置）
sql = ReplaceDefaultValue(sql, queryParams);
//当IsPlaceholder==true时,拼凑查询筛选条件
if (ent.IsPlaceholder == null || !ent.IsPlaceholder.Value)
{
sql = AnalysisParam(dbType, sql, queryParams);
}
sql = SqlForOrder(sql, sidx, sord); //生成排序sql
string sqlPage = SqlForPage(dbType, sql, page, rows, ref err); //拼凑分页sql
//获取总数
string sqlC = string.Format(@"select count(1) from ({0})", sql);
if (dbServer != null)
{
//记录总数
object num = _dbServerApp.ExecuteScalar(ent.DbServerId.Value, sqlC, ref err);
records = Convert.ToInt32(num);
endTable = _dbServerApp.ExecuteGetTable(ent.DbServerId.Value, sqlPage);
}
else//根据承载库查询
{
try
{
object num = DbHelper.ExecuteScalar(sqlC);
records = Convert.ToInt32(num);
endTable = DbHelper.ExecuteGetTable(sqlPage);
}
catch (Exception ex)
{
err.IsError = true;
err.Message = "查询数据错误：" + ex.Message;
return("");
}
}
//拼凑json串
string result = "{\"records\":" + records + ",\"page\":" + page + ",\"total\":" + Math.Ceiling(decimal.Divide(records, rows)) + ",\"rows\":" + JSON.DecodeToStr(endTable) + "}";
return(result);
}
else
{
err.IsError = true;
err.Message = "未找到编号为[" + code + "]的报表！";
}
}
else
{
err.IsError = true;
err.Message = "传入的代码不能为空！";
}
return("");
}