/// <summary>
/// 查询数据
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="page">页码</param>
/// <param name="rows">页量</param>
/// <param name="sort">排序字段</param>
/// <param name="order">排序方式</param>
/// <param name="listFieldName">查询列，默认为 *</param>
/// <param name="whereSql">条件</param>
/// <param name="total">返回总条数</param>
/// <returns></returns>
public DataTable GetData(string TableName, int page, int rows, string sort, string order, string listFieldName, string whereSql, out int total)
{
if (string.IsNullOrWhiteSpace(whereSql))
{
whereSql = "";
}
else
{
whereSql = "WHERE " + whereSql;
}
var sql = $@"
SELECT
{listFieldName}
FROM
{TableName} {whereSql}
ORDER BY
{sort} {order}
LIMIT
{rows} OFFSET {(page - 1) * rows}";
sql += $";select count(1) as total from {TableName} {whereSql}";
var ds = new Data.SQLite.SQLiteHelper(connectionString).Query(sql);
var dt = ds.Tables[0];
int.TryParse(ds.Tables[1].Rows[0][0].ToString(), out total);
return(dt);
}
/// <summary>
/// 获取所有表
/// </summary>
/// <returns></returns>
public List <Model.DkTableName> GetTable()
{
var ds = new Data.SQLite.SQLiteHelper(connectionString).Query(GetTableSQL());
var list = ds.Tables[0].ToModel <Model.DkTableName>();
return(list);
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="page">页码</param>
/// <param name="rows">页量</param>
/// <param name="sort">排序字段</param>
/// <param name="order">排序方式</param>
/// <param name="listFieldName">查询列，默认为 *</param>
/// <param name="total">返回总条数</param>
/// <returns></returns>
public DataTable GetData(string TableName, int page, int rows, string sort, string order, string listFieldName, out int total)
{
var sql = @"
SELECT
" + listFieldName + @"
FROM
" + TableName + @"
ORDER BY
" + sort + " " + order + @"
LIMIT
" + rows + " OFFSET " + (page - 1) * rows;
sql += ";select count(1) as total from " + TableName;
var ds = new Data.SQLite.SQLiteHelper(connectionString).Query(sql);
var dt = ds.Tables[0];
int.TryParse(ds.Tables[1].Rows[0][0].ToString(), out total);
return(dt);
}
/// <summary>
/// 获取所有列
/// </summary>
/// <param name="listTableName">表名</param>
/// <returns></returns>
public List <Model.DkTableColumn> GetColumn(List <string> listTableName = null)
{
if (listTableName == null)
{
listTableName = GetTable().Select(x => x.TableName).ToList();
}
else
{
listTableName.ForEach(x => x.Replace("'", ""));
}
var listSql = new List <string>();
foreach (var tableName in listTableName)
{
listSql.Add(GetColumnSQL(tableName));
}
//自增信息
var aasql = "SELECT name, sql from SQLITE_MASTER WHERE 1=1";
if (listTableName != null && listTableName.Count > 0)
{
aasql += " AND name IN('" + string.Join("','", listTableName) + "')";
}
listSql.Add(aasql);
var sql = string.Join(";", listSql);
var ds = new Data.SQLite.SQLiteHelper(connectionString).Query(sql);
var aadt = ds.Tables[ds.Tables.Count - 1].Select();
var listColumn = new List <Model.DkTableColumn>();
for (int i = 0; i < listTableName.Count; i++)
{
var dt = ds.Tables[i];
var tableName = listTableName[i];
//表创建SQL （分析该SQL语句获取自增列信息）
var aacreate = aadt.FirstOrDefault(x => x["name"].ToString() == tableName)["sql"].ToString();
var aasi = aacreate.IndexOf('(');
var aaei = aacreate.LastIndexOf(')');
aacreate = aacreate.Substring(aasi, aaei - aasi);
//有自增
bool hasaa = aacreate.ToUpper().Contains("AUTOINCREMENT");
int ti = 1;
foreach (DataRow dr in dt.Rows)
{
var colmo = new Model.DkTableColumn()
{
TableName = tableName,
TableComment = "",
FieldName = dr["name"].ToString(),
DataTypeLength = dr["type"].ToString(),
FieldOrder = ti++,
PrimaryKey = dr["pk"].ToString() == "1" ? "YES" : "",
NotNull = dr["notnull"].ToString() == "1" ? "YES" : "",
DefaultValue = dr["dflt_value"]?.ToString(),
FieldComment = ""
};
if (colmo.DataTypeLength.Contains("("))
{
var tlarr = colmo.DataTypeLength.TrimEnd(')').Split('(').ToList();
colmo.DataType = tlarr[0];
var dls = tlarr[1].Split(',').ToList();
colmo.DataLength = dls.FirstOrDefault().Trim();
colmo.DataScale = dls.Count == 2 ? dls.LastOrDefault().Trim() : null;
}
else
{
colmo.DataType = colmo.DataTypeLength;
}
if (hasaa)
{
var aais = aacreate.ToUpper().Split(',').ToList().Any(x => x.Contains("AUTOINCREMENT") && x.Contains(colmo.FieldName.ToUpper()));
colmo.AutoAdd = aais ? "YES" : string.Empty;
}
else
{
colmo.AutoAdd = string.Empty;
}
listColumn.Add(colmo);
}
}
return(listColumn);
}
/// <summary>
/// 检测未发送短信
/// </summary>
/// <returns></returns>
public static ActionResultVM SmsNotSent()
{
var vm = new ActionResultVM();
var listPrimaryKey = new List <string>();
try
{
string connectionString = string.Empty;
//查询SQL
var querySql = GlobalTo.GetValue("Task:QuerySql");
DataTable dt = null;
//数据源连接字符串
if (Enum.TryParse(GlobalTo.GetValue("TypeDB"), true, out TypeDB TDB))
{
connectionString = GlobalTo.Configuration.GetConnectionString(TDB.ToString());
switch (TDB)
{
case TypeDB.MySQL:
dt = new Data.MySQL.MySQLHelper(connectionString).Query(querySql).Tables[0];
break;
case TypeDB.SQLite:
dt = new Data.SQLite.SQLiteHelper(connectionString).Query(querySql).Tables[0];
break;
case TypeDB.Oracle:
dt = new Data.Oracle.OracleHelper(connectionString).Query(querySql).Tables[0];
break;
case TypeDB.SQLServer:
dt = new Data.SQLServer.SQLServerHelper(connectionString).Query(querySql).Tables[0];
break;
case TypeDB.PostgreSQL:
dt = new Data.PostgreSQL.PostgreSQLHelper(connectionString).Query(querySql).Tables[0];
break;
}
}
if (dt.Rows.Count > 0)
{
var listPhoneNumber = new List <string>();
var listSignName = new List <string>();
var TemplateCode = string.Empty;
var listTemplateParamJson = new List <string>();
//表列名
var colPrimaryKey = GlobalTo.GetValue("Task:TableColumn:PrimaryKey");
var colPhoneNumber = GlobalTo.GetValue("Task:TableColumn:PhoneNumber");
var colSignName = GlobalTo.GetValue("Task:TableColumn:SignName");
var colTemplateCode = GlobalTo.GetValue("Task:TableColumn:TemplateCode");
var colTemplateParam = GlobalTo.GetValue("Task:TableColumn:TemplateParam");
foreach (DataRow dr in dt.Rows)
{
listPrimaryKey.Add(dr[colPrimaryKey].ToString());
listPhoneNumber.Add(dr[colPhoneNumber].ToString());
listSignName.Add(dr[colSignName].ToString());
if (string.IsNullOrWhiteSpace(TemplateCode))
{
TemplateCode = dr[colTemplateCode].ToString();
}
listTemplateParamJson.Add(dr[colTemplateParam].ToString());
}
//发送参数
var dicAddQuery = new Dictionary <string, string>
{
{ "PhoneNumberJson", listPhoneNumber.ToJson() },
{ "SignNameJson", listSignName.ToJson() },
{ "TemplateCode", TemplateCode },
{ "TemplateParamJson", listTemplateParamJson.ToJson() }
};
//调用发送方法
vm = SmsService.SendBatchSms(dicAddQuery);
//发送成功
if (vm.Code == 200)
{
//修改状态
var updateSql = GlobalTo.GetValue("Task:UpdateSql");
updateSql = string.Format(updateSql, "'" + string.Join("','", listPrimaryKey) + "'");
int num = 0;
switch (TDB)
{
case TypeDB.MySQL:
num = new Data.MySQL.MySQLHelper(connectionString).ExecuteNonQuery(updateSql);
break;
case TypeDB.SQLite:
num = new Data.SQLite.SQLiteHelper(connectionString).ExecuteNonQuery(updateSql);
break;
case TypeDB.Oracle:
num = new Data.Oracle.OracleHelper(connectionString).ExecuteNonQuery(updateSql);
break;
case TypeDB.SQLServer:
num = new Data.SQLServer.SQLServerHelper(connectionString).ExecuteNonQuery(updateSql);
break;
case TypeDB.PostgreSQL:
num = new Data.PostgreSQL.PostgreSQLHelper(connectionString).ExecuteNonQuery(updateSql);
break;
}
if (num <= 0)
{
vm.Msg += "（修改发送状态失败）";
}
}
}
else
{
vm.Set(ARTag.lack);
}
}
catch (Exception ex)
{
vm.Set(ex);
}
//日志记录
if (GlobalTo.GetValue <bool>("Logs:Enable"))
{
Core.ConsoleTo.Log(new { vm, listPrimaryKey }.ToJson() + Environment.NewLine);
}
return(vm);
}