123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120 |
- 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
- {
- /// <summary>
- /// 表示具有快照表的持久层基础类
- /// </summary>
- /// <typeparam name="T"></typeparam>
- public abstract class BaseOrclSnapshotBLL<T> : BaseOrclBLL<T>, ISnapshotBLL<T>
- 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<String, String> 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<String, String> pair in this.ColumnPropMaps)
- {
- builder.AppendFormat("t3.{0},", pair.Key);
- }
- builder.Remove(builder.Length - 1, 1);
- builder.Append(" FROM (SELECT ");
- foreach (KeyValuePair<String, String> 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<TO_DATE('{1}', 'yyyy-mm-dd')"
- + " UNION SELECT ",
- this.TABLE_NAME, endDate.ToString("yyyy-MM-dd"));
- foreach (KeyValuePair<String, String> 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<TO_DATE('{1}', 'yyyy-mm-dd')) t3) t4"
- + " WHERE t4.R=1",
- this.TABLE_NAME, endDate.ToString("yyyy-MM-dd"));
- return builder.ToString();
- }
- }
- }
|