/// <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)
{
if (listFieldName == "*")
{
listFieldName = "t.*";
}
var sql = @"
SELECT
*
FROM
(
SELECT
ROWNUM AS rowno," + listFieldName + @"
FROM
" + TableName + @" t
WHERE
ROWNUM <= " + (page * rows) + @"
ORDER BY " + sort + " " + order + @"
) table_alias
WHERE
table_alias.rowno >= " + ((page - 1) * rows + 1);
var sqlTotal = "select count(1) as total from " + TableName;
var dt = new Data.Oracle.OracleHelper(connectionString).Query(sql).Tables[0];
var dtTotal = new Data.Oracle.OracleHelper(connectionString).Query(sqlTotal).Tables[0].Rows[0][0].ToString();
int.TryParse(dtTotal, out total);
return(dt);
}
/// <summary>
/// 获取所有表
/// </summary>
/// <returns></returns>
public List <Model.DkTableName> GetTable()
{
var ds = new Data.Oracle.OracleHelper(connectionString).Query(GetTableSQL());
var list = ds.Tables[0].ToModel <Model.DkTableName>();
return(list);
}
/// <summary>
/// 获取所有列
/// </summary>
/// <param name="listTableName">表名</param>
/// <returns></returns>
public List <Model.DkTableColumn> GetColumn(List <string> listTableName = null)
{
var whereSql = string.Empty;
if (listTableName?.Count > 0)
{
listTableName.ForEach(x => x = x.Replace("'", ""));
whereSql = "AND A.TABLE_NAME IN ('" + string.Join("','", listTableName) + "')";
}
var sql = GetColumnSQL(whereSql);
var ds = new Data.Oracle.OracleHelper(connectionString).Query(sql);
var list = ds.Tables[0].ToModel <Model.DkTableColumn>();
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="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 (listFieldName == "*")
{
listFieldName = "t.*";
}
var countWhere = string.Empty;
if (string.IsNullOrWhiteSpace(whereSql))
{
whereSql = "";
}
else
{
countWhere = "WHERE " + whereSql;
whereSql = "AND " + whereSql;
}
var sql = $@"
SELECT
*
FROM
(
SELECT
ROWNUM AS rowno,{listFieldName}
FROM
{TableName} t
WHERE
ROWNUM <= {(page * rows)} {whereSql}
ORDER BY {sort} {order}
) table_alias
WHERE
table_alias.rowno >= {((page - 1) * rows + 1)}";
var sqlTotal = $"select count(1) as total from {TableName} {countWhere}";
var dt = new Data.Oracle.OracleHelper(connectionString).Query(sql).Tables[0];
var dtTotal = new Data.Oracle.OracleHelper(connectionString).Query(sqlTotal).Tables[0].Rows[0][0].ToString();
int.TryParse(dtTotal, out total);
return(dt);
}
/// <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);
}
