using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RDH.Data.BLL;
using RDH.Data.Models;
namespace RDH.Data
{
///
/// 表示具有快照表的持久层基础类
///
///
public abstract class BaseOrclSnapshotBLL : BaseOrclBLL, ISnapshotBLL
where T : BaseModel
{
public BaseOrclSnapshotBLL() : base()
{
}
protected virtual String BuildInsertSnapshotSql()
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat("INSERT INTO {0}(", this.TABLE_NAME + "_BASE");
foreach (var c in ColumnPropMaps)
{
builder.AppendFormat("{0},", c.Key);
}
//根据参数值添加数据
//builder.Append("SNAPSHOT_KEY) VALUES(");
//foreach (var c in ColumnPropMaps)
//{
// builder.AppendFormat(":{0},", c.Value);
//}
//builder.Append(":SnapshotKey)");
//根据表中数据添加
builder.Remove(builder.Length - 1, 1);
builder.Append(") SELECT ");
foreach (var c in ColumnPropMaps)
{
//if (c.Key == "CREATE_TIME")
//{
// builder.Append("SYSDATE,");
//}
//else
//{
// builder.AppendFormat("{0},", c.Key);
//}
builder.AppendFormat("{0},", c.Key);
}
builder.Remove(builder.Length - 1, 1);
builder.AppendFormat(" FROM {0} WHERE {1}=:{2}",
this.TABLE_NAME,
this.KEY_COLUMN,
this.ColumnPropMaps[this.KEY_COLUMN]);
return builder.ToString();
}
public virtual void UpdateCurrent(T v)
{
Object tempParam = GetParam(v);
SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(),
BuildInsertSnapshotSql(),
tempParam);
v.CreateTime = DateTime.Now;
tempParam = GetParam(v);
SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(),
BuildUpdateSql(),
tempParam);
}
public virtual void DeleteCurrent(T v)
{
Object tempParam = GetParam(v);
SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(),
BuildInsertSnapshotSql(),
tempParam);
SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(),
BuildDeleteSql(),
tempParam);
}
internal String BuildLastDataQueryByHistory(DateTime endDate, String partitionKey)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT ");
foreach (KeyValuePair pair in this.ColumnPropMaps)
{
builder.AppendFormat("t4.{0},", pair.Key, pair.Value);
}
builder.Remove(builder.Length - 1, 1);
builder.AppendFormat(" FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t3.{0} ORDER BY t3.create_time DESC) AS R,",
String.IsNullOrEmpty(partitionKey) ? "key" : partitionKey);
foreach (KeyValuePair pair in this.ColumnPropMaps)
{
builder.AppendFormat("t3.{0},", pair.Key);
}
builder.Remove(builder.Length - 1, 1);
builder.Append(" FROM (SELECT ");
foreach (KeyValuePair pair in this.ColumnPropMaps)
{
builder.AppendFormat("t1.{0},", pair.Key);
}
builder.Remove(builder.Length - 1, 1);
builder.AppendFormat(" FROM {0} t1 "
+ " WHERE t1.create_time pair in this.ColumnPropMaps)
{
builder.AppendFormat("t2.{0},", pair.Key);
}
builder.Remove(builder.Length - 1, 1);
builder.AppendFormat(" FROM {0}_BASE t2"
+ " WHERE t2.create_time