|
namespace Dao
|
{
|
using Common;
|
using Model;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Runtime.InteropServices;
|
using System.Text;
|
using System.Text.RegularExpressions;
|
|
public class GwAuditCacheDao : IDisposable
|
{
|
private int CalcDeductCount(int count, int deductPercent)
|
{
|
return ((count * deductPercent) / 100);
|
}
|
|
public void Dispose()
|
{
|
}
|
|
private string GenerateDeductRownumString(int totalCount, int count)
|
{
|
Dictionary<int, int> dictionary = new Dictionary<int, int>();
|
Random random = new Random();
|
while ((dictionary.Count < count) && (dictionary.Count < totalCount))
|
{
|
int key = random.Next(totalCount);
|
if (!dictionary.ContainsKey(key))
|
{
|
dictionary.Add(key, key);
|
}
|
}
|
StringBuilder builder = new StringBuilder();
|
foreach (int num2 in dictionary.Keys)
|
{
|
builder.AppendFormat("{0},", num2);
|
}
|
if (builder.Length > 0)
|
{
|
builder.Remove(builder.Length - 1, 1);
|
}
|
return builder.ToString();
|
}
|
|
//处理账号集合
|
private string GenerateOpIDsString(string selectedOpIDs)
|
{
|
if (string.IsNullOrEmpty(selectedOpIDs))
|
{
|
return string.Empty;
|
}
|
return string.Format("ca.SP_ID IN ({0}) AND ", Regex.Replace(selectedOpIDs, @"(\d+)", (MatchEvaluator)(m => ("'" + m.Groups[1].Value + "'"))));
|
}
|
|
public GwAuditCache GetAuditCache(string hashCode)
|
{
|
string str = "SELECT COUNT(*) AS CNT, HASH_CODE,MSG_CONTENT,STRATEGY_ID,DEDUCT_PERCENT,sp_id,client_id FROM GW_AUDIT_CACHE WHERE HASH_CODE=:HASH_CODE GROUP BY HASH_CODE,MSG_CONTENT,STRATEGY_ID,DEDUCT_PERCENT,sp_id,client_id";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":HASH_CODE", hashCode) };
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(str, OracleHelper.Connection, parameterArray))
|
{
|
while ((reader != null) && reader.Read())
|
{
|
GwAuditCache cache = new GwAuditCache();
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
cache.StrategyID = wrapper.GetInt("STRATEGY_ID", 0);
|
cache.Content = wrapper.GetString("MSG_CONTENT", "");
|
cache.Cnt = wrapper.GetInt("CNT", 0);
|
cache.DeductPercent = wrapper.GetInt("DEDUCT_PERCENT", 0);
|
cache.SpID = wrapper.GetString("sp_id", "");
|
cache.ClientID = wrapper.GetString("client_id", "");
|
return cache;
|
}
|
}
|
return null;
|
}
|
|
/// <summary>
|
/// 加载待审核数据
|
/// </summary>
|
/// <param name="begin"></param>
|
/// <param name="end"></param>
|
/// <param name="status"></param>
|
/// <param name="content"></param>
|
/// <param name="spids"></param>
|
/// <param name="recordCount"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="pageIndex"></param>
|
/// <returns></returns>
|
public DataTable LoadCache(DateTime begin, DateTime end, int status, string content, string spids, out int recordCount, int pageSize, int pageIndex)
|
{
|
string str = string.Format("SELECT op.op_name,ca.SP_ID,ca.OP_ID,ca.MSG_CONTENT,ca.KEYWORD,ca.HASH_CODE,COUNT(*) CNT FROM GW_AUDIT_CACHE ca left join gw_op op on ca.op_id=op.op_id WHERE {0} ca.AP_SUBMIT_TIME BETWEEN :START_TIME AND :END_TIME AND (ca.MSG_CONTENT LIKE :CONTENT OR :CONTENT='%%') AND ca.AUDITING_STATUS=:AUDITING_STATUS \r\n GROUP BY ca.SP_ID,ca.OP_ID,ca.MSG_CONTENT,ca.KEYWORD,ca.HASH_CODE,op.op_name", this.GenerateOpIDsString(spids));
|
List<GwAuditCache> list = new List<GwAuditCache>();
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":START_TIME", begin), new OracleParameter(":END_TIME", end), new OracleParameter(":CONTENT", "%" + content + "%"), new OracleParameter(":AUDITING_STATUS", status) };
|
recordCount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.ToCountSql(str), OracleHelper.Connection, parameterArray));
|
return OracleHelper.Execute(OracleHelper.ToPagerSql(str, pageIndex, pageSize, recordCount), OracleHelper.Connection, parameterArray);
|
}
|
|
/// <summary>
|
/// 加载详情
|
/// </summary>
|
/// <param name="startTime"></param>
|
/// <param name="endTime"></param>
|
/// <param name="hashCode"></param>
|
/// <returns></returns>
|
public DataTable LoadDetail(DateTime startTime, DateTime endTime, string hashCode)
|
{
|
return OracleHelper.Execute("SELECT AP_SUBMIT_TIME,MSG_CONTENT,MOBILE,SP_ID,OP_ID FROM GW_AUDIT_CACHE WHERE HASH_CODE=:HASH_CODE AND AP_SUBMIT_TIME BETWEEN :START_TIME AND :END_TIME AND ROWNUM < 20", OracleHelper.Connection, new OracleParameter[] { new OracleParameter(":HASH_CODE", hashCode), new OracleParameter(":START_TIME", startTime), new OracleParameter(":END_TIME", endTime) });
|
}
|
|
public List<GwAuditCache> LoadInfoList(string spID, string pattern)
|
{
|
List<GwAuditCache> list = new List<GwAuditCache>();
|
string str = "SELECT * FROM GW_AUDIT_CACHE WHERE SP_ID=:SP_ID AND regexp_like(replace(msg_content,CHR(10),'*'),:REGEXP) ORDER BY ID ASC";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":SP_ID", spID), new OracleParameter(":REGEXP", pattern) };
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(str, OracleHelper.Connection, parameterArray))
|
{
|
while ((reader != null) && reader.Read())
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
GwAuditCache item = new GwAuditCache();
|
item.StrategyID = wrapper.GetInt("STRATEGY_ID", 0);
|
item.CreateTime = wrapper.GetDateTime("AP_SUBMIT_TIME");
|
item.Content = wrapper.GetString("MSG_CONTENT", "");
|
item.Cnt = wrapper.GetInt("CNT", 0);
|
list.Add(item);
|
}
|
}
|
return list;
|
}
|
|
public List<GwAuditCache> LoadInfoList(DateTime startTime, DateTime endTime, int auditingStatus, string content, string selectedOpIDs, out int recordcount, int pageSize, int pageIndex)
|
{
|
string str = string.Format("SELECT * FROM (SELECT TO_CHAR(AP_SUBMIT_TIME,'YYYY-MM-DD HH24:\"00\":\"00\"') as CREATE_TIME,COUNT(*) AS CNT,SP_ID,OP_ID,HASH_CODE, STRATEGY_ID,MSG_CONTENT, AUDITING_MODE,DEDUCT_PERCENT FROM GW_AUDIT_CACHE ca WHERE {0} (MSG_CONTENT LIKE :CONTENT OR :CONTENT='%%') AND AP_SUBMIT_TIME BETWEEN :START_TIME AND :END_TIME AND AUDITING_MODE IN (1,2) and AUDITING_STATUS=:AUDITING_STATUS GROUP BY TO_CHAR(AP_SUBMIT_TIME,'YYYY-MM-DD HH24:\"00\":\"00\"'),HASH_CODE,OP_ID, STRATEGY_ID,MSG_CONTENT,SP_ID,AUDITING_MODE,DEDUCT_PERCENT ORDER BY 1 ASC)", this.GenerateOpIDsString(selectedOpIDs));
|
List<GwAuditCache> list = new List<GwAuditCache>();
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":START_TIME", startTime), new OracleParameter(":END_TIME", endTime), new OracleParameter(":CONTENT", "%" + content + "%"), new OracleParameter(":AUDITING_STATUS", auditingStatus) };
|
recordcount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.ToCountSql(str), OracleHelper.Connection, parameterArray));
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(OracleHelper.ToPagerSql(str, pageIndex, pageSize, recordcount), OracleHelper.Connection, parameterArray))
|
{
|
while ((reader != null) && reader.Read())
|
{
|
GwAuditCache o = new GwAuditCache();
|
if (this.ReadInfo(reader, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
return list;
|
}
|
|
public bool MakeTest(string apID, int opID, string extNo, string extParams, string mobile, string content)
|
{
|
string str = "INSERT INTO GW_AUDIT_CACHE(ID,AP_MID, AP_ID, OP_ID, SP_ID, CLIENT_ID, STRATEGY_ID, EXT_NO,EXT_PARAMS, MOBILE, PK_TOTAL, PK_NUMBER, TPPID, TPUDHI, PRIORITY, RD_FLAG, MSG_FORMAT, MSG_LENGTH, MSG_CONTENT, AP_RESULT, AP_SUBMIT_TIME, AUDITING_STATUS, AUDITING_MODE, DEDUCT_PERCENT, HASH_CODE, PRICE,ROUTER_TYPE,MCPM) \r\nVALUES(:ID,:AP_MID,:AP_ID,:OP_ID, 0, 0, 0,:EXT_NO,:EXT_PARAMS, :MOBILE, 1, 1, 0, 0, 0, 1, 8, :MSG_LENGTH, :MSG_CONTENT, 0, SYSDATE, :AUDITING_STATUS, :AUDITING_MODE, 0, 0, 0,1,99999)";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":ID", DateTime.Now.AddYears(-2010).Ticks * 0x3e8L), new OracleParameter(":AP_MID", DateTime.Now.Ticks.ToString()), new OracleParameter(":AP_ID", apID), new OracleParameter(":OP_ID", opID), new OracleParameter(":MOBILE", mobile), new OracleParameter(":EXT_NO", extNo), new OracleParameter(":EXT_PARAMS", extParams), new OracleParameter(":MSG_LENGTH", content.Length * 2), new OracleParameter(":MSG_CONTENT", content), new OracleParameter(":AUDITING_STATUS", Convert.ToInt32((AuditingStatus)4)), new OracleParameter(":AUDITING_MODE", Convert.ToInt32((AuditingMode)1)) };
|
return (OracleHelper.ExecuteSql(str, OracleHelper.Connection, parameterArray) > 0);
|
}
|
|
private bool ReadInfo(OracleDataReader reader, GwAuditCache o)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
o.HashCode = wrapper.GetString("HASH_CODE", "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF");
|
o.StrategyID = wrapper.GetInt("STRATEGY_ID", 0);
|
o.Content = wrapper.GetString("MSG_CONTENT", "");
|
o.Cnt = wrapper.GetInt("CNT", 0);
|
o.DeductPercent = wrapper.GetInt("DEDUCT_PERCENT", 0);
|
o.AuditingMode = (AuditingMode)wrapper.GetInt("AUDITING_MODE", 0);
|
o.CreateTime = wrapper.GetDateTime("CREATE_TIME");
|
o.SpID = wrapper.GetString("SP_ID", "");
|
o.OpID = wrapper.GetString("OP_ID", "");
|
return true;
|
}
|
|
public bool UpdateCacheStatus(string hashCode, AuditingStatus status)
|
{
|
string str = "UPDATE GW_AUDIT_CACHE SET AP_SUBMIT_TIME=SYSDATE,AUDITING_STATUS=:AUDITING_STATUS WHERE HASH_CODE=:HASH_CODE";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":AUDITING_STATUS", (int)status), new OracleParameter(":HASH_CODE", hashCode) };
|
return (OracleHelper.ExecuteSql(str, OracleHelper.Connection, parameterArray) > 0);
|
}
|
|
public bool UpdateCacheStatus(string hashCode, AuditingStatus status, int opID)
|
{
|
string str = "UPDATE GW_AUDIT_CACHE SET AP_SUBMIT_TIME=SYSDATE, AUDITING_STATUS=:AUDITING_STATUS,OP_ID=:OP_ID,ROUTER_TYPE=1 WHERE HASH_CODE=:HASH_CODE";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":AUDITING_STATUS", (int)status), new OracleParameter(":HASH_CODE", hashCode), new OracleParameter(":OP_ID", opID) };
|
return (OracleHelper.ExecuteSql(str, OracleHelper.Connection, parameterArray) > 0);
|
}
|
}
|
}
|
