public JsonResult ExportSql(string database, string tableName, string tableCategory, bool isAllTable, bool includCreate, bool includInsert)
{
DatabaseDialectEnum dialect = (DatabaseDialectEnum)Enum.Parse(typeof(DatabaseDialectEnum), database);
//优先级最高
if (isAllTable)
{
var dics = _dbContext.Dictionarys("TableCategory").OrderBy(d => d.SortBy);
StringBuilder sbALLSql = new StringBuilder();
sbALLSql.AppendLine("use FapCore30;");
foreach (var dic in dics)
{
sbALLSql.Append(_dbMetadataContext.ExportSql(dialect, string.Empty, dic.Code, includCreate, includInsert));
}
string allFileName = $"{database}.sql";
string allFilePath = Path.Combine(Environment.CurrentDirectory, FapPlatformConstants.TemporaryFolder, allFileName);
return(ZipSql(sbALLSql.ToString(), allFileName, allFilePath));
}
if (tableName.IsMissing() && tableCategory.IsMissing())
{
return(Json(ResponseViewModelUtils.Failure("请选择导出的表或分类")));
}
string sql = _dbMetadataContext.ExportSql(dialect, tableName, tableCategory, includCreate, includInsert);
string fileName = (tableName.IsPresent() ? tableName : tableCategory) + $"{database}.sql";
string filePath = Path.Combine(Environment.CurrentDirectory, FapPlatformConstants.TemporaryFolder, fileName);
if (tableName.IsPresent())
{
return(Json(new ResponseViewModel {
success = true, data = sql
}));
}
return(ZipSql(sql, fileName, filePath));
}
private static string ReplaceFunc(string sqlDesc, DatabaseDialectEnum dialect)
{
if (dialect == DatabaseDialectEnum.MSSQL)
{
sqlDesc = sqlDesc
.ReplaceIgnoreCase("[小时](", " DATEDIFF(hh,")
.ReplaceIgnoreCase("[天](", " DATEDIFF(dd,")
.ReplaceIgnoreCase("[星期](", " TimeStampDiff(wk,")
.ReplaceIgnoreCase("[月](", " TimeStampDiff(mm,")
.ReplaceIgnoreCase("[季度](", " TimeStampDiff(qq,")
.ReplaceIgnoreCase("[年](", " DATEDIFF(yy,")
.ReplaceIgnoreCase("[空](", " ISNULL(")
;
}
else if (dialect == DatabaseDialectEnum.MYSQL)
{
sqlDesc = sqlDesc
.ReplaceIgnoreCase("[小时](", " TimeStampDiff(HOUR,")
.ReplaceIgnoreCase("[天](", " TimeStampDiff(DAY,")
.ReplaceIgnoreCase("[星期](", " TimeStampDiff(WEEK,")
.ReplaceIgnoreCase("[月](", " TimeStampDiff(MONTH,")
.ReplaceIgnoreCase("[季度](", "TimeStampDiff(QUARTER,")
.ReplaceIgnoreCase("[年](", " TimeStampDiff(YEAR,")
.ReplaceIgnoreCase("[空](", " IFNULL(");
}
sqlDesc = sqlDesc.ReplaceIgnoreCase("[绝对值](", " ABS(")
.ReplaceIgnoreCase("[向上取整](", " CEILING(")
.ReplaceIgnoreCase("[向下取整](", " FLOOR(")
.ReplaceIgnoreCase("[四舍五入](", " ROUND(");
return(sqlDesc);
}
/// <summary>
/// 构造方法
/// </summary>
/// <param name="conn">连接</param>
public DbSession(ILoggerFactory loggerFactory, IConnectionFactory connectionFactory)
{
_logger = loggerFactory.CreateLogger <DbSession>();
ConnectionFactory = connectionFactory;
DatabaseDialect = connectionFactory.DatabaseDialect;
// CommandTimeout = commandTimeOut;
}
/// <summary>
/// 返回数据sql
/// </summary>
/// <param name="cols"></param>
/// <param name="data"></param>
/// <param name="sqlDesc"></param>
/// <param name="dialect"></param>
/// <returns></returns>
public static string ParsingConditionSql(IEnumerable <FapColumn> cols, IDictionary <string, object> data, string sqlDesc, DatabaseDialectEnum dialect)
{
Regex rgx = new Regex(MatchBigParantheses);
MatchCollection matchs = rgx.Matches(sqlDesc);
foreach (var mtch in matchs)
{
var colLabel = mtch.ToString().TrimStart('{').TrimEnd('}').Trim();
FapColumn fcol = cols.FirstOrDefault(c => c.ColComment == colLabel);
if (fcol != null)
{
if (fcol.ColType == FapColumn.COL_TYPE_INT ||
fcol.ColType == FapColumn.COL_TYPE_LONG ||
fcol.ColType == FapColumn.COL_TYPE_DOUBLE ||
fcol.ColType == FapColumn.COL_TYPE_BOOL)
{
sqlDesc = sqlDesc.Replace(mtch.ToString(), data[fcol.ColName].ToStringOrEmpty());
}
else
{
sqlDesc = sqlDesc.Replace(mtch.ToString(), $"'{data[fcol.ColName].ToStringOrEmpty()}'");
}
}
}
sqlDesc = ReplaceFunc(sqlDesc, dialect);
sqlDesc = ReplaceConstant(sqlDesc);
string sql = $"select 1 where {sqlDesc}";
if (dialect != DatabaseDialectEnum.MSSQL)
{
sql = $"select 1 from dual where {sqlDesc}";
}
return(sql);
}
/// <summary>
/// 返回sql where
/// </summary>
/// <param name="cols"></param>
/// <param name="sqlDesc"></param>
/// <returns></returns>
public static string ParsingSql(IEnumerable <FapColumn> cols, string sqlDesc, DatabaseDialectEnum dialect)
{
Regex rgx = new Regex(MatchBigParantheses);
MatchCollection matchs = rgx.Matches(sqlDesc);
foreach (var mtch in matchs)
{
var colLabel = mtch.ToString().TrimStart('{').TrimEnd('}').Trim();
sqlDesc = sqlDesc.ReplaceIgnoreCase(mtch.ToString(), cols.FirstOrDefault(c => c.ColComment == colLabel)?.ColName ?? "");
}
sqlDesc = ReplaceFunc(sqlDesc, dialect);
sqlDesc = ReplaceConstant(sqlDesc);
return(sqlDesc);
}
public static string ParsingFormulaCheckSql(string tableName, string checkSql, DatabaseDialectEnum dialect)
{
if (dialect == DatabaseDialectEnum.MSSQL)
{
string sql = checkSql.ReplaceIgnoreCase(tableName, "#FmuValideTemp");
return(string.Format(@"
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#FmuValideTemp'))
begin
drop table #FmuValideTemp
end
select top 1 * into #FmuValideTemp from {0} where 1 = 2;
{1};
drop table #FmuValideTemp;", tableName, sql));
}
else if (dialect == DatabaseDialectEnum.MYSQL)
{
string sql = checkSql.ReplaceIgnoreCase(tableName, "FmuValideTemp");
return(string.Format(@"CREATE TEMPORARY TABLE FmuValideTemp(SELECT * FROM {0} where 1=2 );
{1};
drop table FmuValideTemp;", tableName, sql));
}
return(string.Empty);
}
/// <summary>
///
/// </summary>
/// <param name="cols"></param>
/// <param name="colName"></param>
/// <param name="grandTableName">汇总表名</param>
/// <param name="sqlDesc"></param>
/// <param name="dialect"></param>
/// <returns></returns>
public static string ParsingFormulaGrandTotalSql(IEnumerable <FapColumn> cols, string colName, string grandTableName, string sqlDesc, DatabaseDialectEnum databaseDialect)
{
Regex rgx = new Regex(MatchBigParantheses);
MatchCollection matchs = rgx.Matches(sqlDesc);
if (matchs.Any())
{
var colLabel = matchs.First().Value.TrimStart('{').TrimEnd('}').Trim();
FapColumn fcol = cols.FirstOrDefault(c => c.ColComment == colLabel);
if (fcol != null)
{
string associate = string.Empty;
if (grandTableName.IsPresent() && !grandTableName.EqualsWithIgnoreCase(fcol.TableName))
{
if (grandTableName.EqualsWithIgnoreCase("PayCenter"))
{
associate = $"{grandTableName}.PayCaseUid = {fcol.TableName}.PayCaseUid and substring({grandTableName}.PayYM,1,4)= substring({fcol.TableName}.PayYM, 1, 4) and {grandTableName}.EmpUid = {fcol.TableName}.EmpUid";
}
else if (grandTableName.EqualsWithIgnoreCase("InsCenter"))
{
associate = $"{grandTableName}.InsCaseUid = {fcol.TableName}.InsCaseUid and substring({grandTableName}.InsYM,1,4)= substring({fcol.TableName}.InsYM, 1, 4) and {grandTableName}.EmpUid = {fcol.TableName}.EmpUid";
}
else
{
associate = $"{grandTableName}.Fid={fcol.TableName}.Fid";
}
string sqlSum = string.Empty;
if (databaseDialect == DatabaseDialectEnum.MSSQL)
{
sqlSum = $" ISNULL((select sum({fcol.ColName}) from {grandTableName} where {associate}),0.0)";
}
else if (databaseDialect == DatabaseDialectEnum.MYSQL)
{
sqlSum = $" IFNULL((select sum({fcol.ColName}) from {grandTableName} where {associate}),0.0)";
}
return($"update {fcol.TableName} set {colName} = {fcol.ColName} +" + sqlSum);
}
else
{
return(string.Empty);//$"update {fcol.TableName} set {colName}=sum({fcol.ColName}) where {associate}";
}
}
}
return(string.Empty);
}
public static string ParsingFormulaVariableSql(IEnumerable <FapColumn> cols, string colName, string sqlDesc, DatabaseDialectEnum dialect)
{
string tableName = cols.First().TableName;
Regex rgx = new Regex(MatchBigParantheses);
MatchCollection matchs = rgx.Matches(sqlDesc);
foreach (var mtch in matchs)
{
var colLabel = mtch.ToString().TrimStart('{').TrimEnd('}').Trim();
FapColumn fcol = cols.FirstOrDefault(c => c.ColComment == colLabel);
if (fcol != null)
{
sqlDesc = sqlDesc.Replace(mtch.ToString(), fcol.ColName);
}
}
sqlDesc = ReplaceFunc(sqlDesc, dialect);
sqlDesc = ReplaceConstant(sqlDesc);
return($"update {tableName} set {colName}={sqlDesc} where {ValidWhere(tableName)}");
}
