/// <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(""); }