|
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;
|
|
public class GwRmDao : IDisposable
|
{
|
public bool Add(string content)
|
{
|
return (OracleHelper.ExecuteSql("INSERT INTO GW_EVENT_LOG(EVENT_ID,ASSIGNED_TO,EVENT_TYPE,CONTENT,EVENT_TIME,HANDLE_FLAG)VALUES(GW_EVENT_LOG_SEQ.NEXTVAL,'SMEXC','RMRESEND',:CONTENT,SYSDATE,0)", OracleHelper.Connection, new OracleParameter[] { new OracleParameter(":CONTENT", content) }) > 0);
|
}
|
|
public void Dispose()
|
{
|
|
}
|
|
public int loadGwRmPushList(string sDate, string beginTime, string endTime, string ApID, string OpID, string SpID, string ClientID, string DestnationId, out int recordcount)
|
{
|
string str = sDate.Replace("-", "");
|
recordcount = 0;
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from LG_RM_").Append(str).Append(" where 1=1 ");
|
OracleParameter[] parameterArray = new OracleParameter[7];
|
if (!string.IsNullOrEmpty(ApID))
|
{
|
parameterArray[0] = new OracleParameter(":ApID", (OracleDbType)126);
|
parameterArray[0].Value = ApID;
|
builder.Append(" and AP_ID=:ApID");
|
|
}
|
if (!string.IsNullOrEmpty(OpID))
|
{
|
parameterArray[1] = new OracleParameter(":OpID", (OracleDbType)126);
|
parameterArray[1].Value = OpID;
|
builder.Append(" and OP_ID=:OpID");
|
}
|
if (!string.IsNullOrEmpty(SpID))
|
{
|
parameterArray[2] = new OracleParameter(":SpID", (OracleDbType)126);
|
parameterArray[2].Value = SpID;
|
builder.Append(" and SP_ID=:SpID");
|
}
|
if (!string.IsNullOrEmpty(DestnationId))
|
{
|
parameterArray[3] = new OracleParameter(":DestnationId", (OracleDbType)126);
|
parameterArray[3].Value = DestnationId;
|
builder.Append(" and MOBILE=:DestnationId");
|
}
|
if (!string.IsNullOrEmpty(ClientID))
|
{
|
parameterArray[4] = new OracleParameter(":CLIENT_ID", (OracleDbType)126);
|
parameterArray[4].Value = ClientID;
|
builder.Append(" and CLIENT_ID=:CLIENT_ID");
|
}
|
parameterArray[5] = new OracleParameter(":BEGIN_TIME", (OracleDbType)106);
|
parameterArray[5].Value = DateTime.Parse(sDate + " " + beginTime);
|
parameterArray[6] = new OracleParameter(":END_TIME", (OracleDbType)106);
|
parameterArray[6].Value = DateTime.Parse(sDate + " " + endTime);
|
builder.Append(" and REPORT_TIME BETWEEN :BEGIN_TIME AND :END_TIME");
|
builder.Append(" ORDER BY REPORT_TIME DESC");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection, parameterArray))
|
{
|
while (reader.Read())
|
{
|
recordcount = this.ReadCount(reader);
|
}
|
}
|
DataTable table = OracleHelper.Execute("select * " + builder.ToString(), OracleHelper.Connection, parameterArray);
|
int count = 0;
|
//using (OracleBulkCopy copy = new OracleBulkCopy(OracleHelper.OraclePrepareConnection, 1))
|
using (OracleBulkCopy copy = new OracleBulkCopy(OracleHelper.OraclePrepareConnection, (OracleBulkCopyOptions)1))
|
{
|
copy.DestinationTableName="GW_RM_RESEND";
|
copy.ColumnMappings.Add("AP_MID", "AP_MID");
|
copy.ColumnMappings.Add("OP_MID", "OP_MID");
|
copy.ColumnMappings.Add("AP_ID", "AP_ID");
|
copy.ColumnMappings.Add("OP_ID", "OP_ID");
|
copy.ColumnMappings.Add("SP_ID", "SP_ID");
|
copy.ColumnMappings.Add("CLIENT_ID", "CLIENT_ID");
|
copy.ColumnMappings.Add("ACCESS_CODE", "ACCESS_CODE");
|
copy.ColumnMappings.Add("MOBILE", "MOBILE");
|
copy.ColumnMappings.Add("STAT", "STAT");
|
copy.ColumnMappings.Add("SUBMIT_TIME", "SUBMIT_TIME");
|
copy.ColumnMappings.Add("DONE_TIME", "DONE_TIME");
|
copy.ColumnMappings.Add("REPORT_TIME", "REPORT_TIME");
|
copy.ColumnMappings.Add("EXT_MSG", "EXT_MSG");
|
copy.BatchSize=0xf4240;
|
try
|
{
|
if ((table != null) && (table.Rows.Count > 0))
|
{
|
copy.WriteToServer(table);
|
count = table.Rows.Count;
|
}
|
}
|
catch (Exception)
|
{
|
}
|
finally
|
{
|
if (copy != null)
|
{
|
copy.Close();
|
}
|
}
|
copy.Dispose();
|
}
|
return count;
|
}
|
|
public DataTable LoadInfoList(string ApID, string OpID, string SpID, string DestnationId, string sDate, string beginTime, string endTime, StatisOption so)
|
{
|
string str = sDate.Replace("-", "");
|
StringBuilder builder = new StringBuilder();
|
StringBuilder builder2 = new StringBuilder();
|
if ((so & StatisOption.APID) == StatisOption.APID)
|
{
|
builder.Append(" and a.ap_id=b.ap_id");
|
builder2.Append(" and a.ap_id=c.ap_id");
|
}
|
if ((so & StatisOption.OPID) == StatisOption.OPID)
|
{
|
builder.Append(" and a.op_id=b.op_id");
|
builder2.Append(" and a.op_id=c.op_id");
|
}
|
if ((so & StatisOption.SpID) == StatisOption.SpID)
|
{
|
builder.Append(" and a.sp_id=b.sp_id");
|
builder2.Append(" and a.sp_id=c.sp_id");
|
}
|
string str2 = string.Format("SELECT 1,\r\n (select count(*) from LG_RM_{5} b where STAT = 'DELIVRD' AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (MOBILE=:DestnationId OR :DestnationId IS NULL) AND REPORT_TIME BETWEEN :BEGIN_TIME AND :END_TIME {3}) as DELIVRD,\r\n (select count(*) from LG_RM_{5} c where STAT <> 'DELIVRD' AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (MOBILE=:DestnationId OR :DestnationId IS NULL) AND REPORT_TIME BETWEEN :BEGIN_TIME AND :END_TIME {4}) as FAIL\r\n {0}{1}{2}\r\n FROM LG_RM_{5} a WHERE (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (MOBILE=:DestnationId OR :DestnationId IS NULL) AND REPORT_TIME BETWEEN :BEGIN_TIME AND :END_TIME GROUP BY 1{0}{1}{2}", new object[] { ((so & StatisOption.APID) == StatisOption.APID) ? ",AP_ID" : "", ((so & StatisOption.OPID) == StatisOption.OPID) ? ",OP_ID" : "", ((so & StatisOption.SpID) == StatisOption.SpID) ? ",SP_ID" : "", builder.ToString(), builder2.ToString(), str });
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":AP_ID", ApID), new OracleParameter(":OP_ID", OpID), new OracleParameter(":SP_ID", SpID), new OracleParameter(":DestnationId", DestnationId), new OracleParameter(":BEGIN_TIME", Convert.ToDateTime(sDate + " " + beginTime)), new OracleParameter(":END_TIME", Convert.ToDateTime(sDate + " " + endTime)) };
|
return OracleHelper.Execute(str2.ToString(), OracleHelper.Connection, parameterArray);
|
}
|
|
public List<GwRm> LoadInfoList(string ApID, string ClientID, string OpID, string SpID, string DestnationId, string sDate, string beginTime, string endTime, out int recordcount, int pageSize, int PageIndex)
|
{
|
List<GwRm> list = new List<GwRm>();
|
recordcount = 0;
|
try
|
{
|
string str = sDate.Replace("-", "");
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from LG_RM_").Append(str).Append(" where 1=1");
|
OracleParameter[] parameterArray = new OracleParameter[7];
|
if ((ApID != null) && (ApID != ""))
|
{
|
parameterArray[0] = new OracleParameter(":ApID", (OracleDbType)126);
|
parameterArray[0].Value = ApID;
|
builder.Append(" and AP_ID=:ApID");
|
}
|
if ((OpID != null) && (OpID != ""))
|
{
|
parameterArray[1] = new OracleParameter(":OpID", (OracleDbType)126);
|
parameterArray[1].Value = OpID;
|
builder.Append(" and OP_ID=:OpID");
|
}
|
if (!string.IsNullOrEmpty(SpID))
|
{
|
parameterArray[2] = new OracleParameter(":SpID", (OracleDbType)126);
|
parameterArray[2].Value = SpID;
|
builder.Append(" and SP_ID=:SpID");
|
}
|
if ((DestnationId != null) && (DestnationId != ""))
|
{
|
parameterArray[3] = new OracleParameter(":DestnationId", (OracleDbType)126);
|
parameterArray[3].Value = DestnationId;
|
builder.Append(" and MOBILE=:DestnationId");
|
}
|
if (!string.IsNullOrEmpty(ClientID))
|
{
|
parameterArray[4] = new OracleParameter(":CLIENT_ID", (OracleDbType)126);
|
parameterArray[4].Value = ClientID;
|
builder.Append(" and CLIENT_ID=:CLIENT_ID");
|
}
|
parameterArray[5] = new OracleParameter(":BEGIN_TIME", (OracleDbType)106);
|
parameterArray[5].Value = DateTime.Parse(sDate + " " + beginTime);
|
parameterArray[6] = new OracleParameter(":END_TIME", (OracleDbType)106);
|
parameterArray[6].Value = DateTime.Parse(sDate + " " + endTime);
|
builder.Append(" and REPORT_TIME BETWEEN :BEGIN_TIME AND :END_TIME");
|
builder.Append(" ORDER BY REPORT_TIME DESC");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection, parameterArray))
|
{
|
while (reader.Read())
|
{
|
recordcount = this.ReadCount(reader);
|
}
|
}
|
using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString(), OracleHelper.Connection, parameterArray))
|
{
|
while (reader2.Read())
|
{
|
GwRm o = new GwRm();
|
if (this.ReadInfo(reader2, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
}
|
|
private int ReadCount(OracleDataReader reader)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
int @int = 0;
|
try
|
{
|
@int = wrapper.GetInt("count", 0);
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return 0;
|
}
|
return @int;
|
}
|
|
private bool ReadInfo(OracleDataReader reader, GwRm o)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
o.APMID=wrapper.GetString("AP_MID", string.Empty);
|
o.OPMID=wrapper.GetString("OP_MID", string.Empty);
|
o.ClientID=wrapper.GetString("CLIENT_ID", string.Empty);
|
o.SPID=wrapper.GetString("SP_ID", string.Empty);
|
o.ApID=wrapper.GetInt("AP_ID", 0);
|
o.OpID=wrapper.GetInt("OP_ID", 0);
|
o.AccessCode=wrapper.GetString("ACCESS_CODE", "");
|
o.Mobile=wrapper.GetString("MOBILE", "");
|
o.Stat=wrapper.GetString("STAT", "");
|
o.SubmitTime=wrapper.GetString("SUBMIT_TIME", "");
|
o.DoneTime=wrapper.GetString("DONE_TIME", "");
|
o.ReportTime=wrapper.GetDateTime("REPORT_TIME");
|
o.ExtMsg=wrapper.GetString("EXT_MSG", "");
|
return true;
|
}
|
}
|
}
|
|
|
