|
using Common;
|
using Model;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace Dao
|
{
|
public class GwClientDao : IDisposable
|
{
|
|
/// <summary>
|
/// 加载代理商客户列表
|
/// </summary>
|
/// <param name="recordCount">总计</param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSizestring"></param>
|
/// <param name="clientID">客户账号名称</param>
|
/// <param name="clientName">联系人</param>
|
/// <param name="telphone">联系电话</param>
|
/// <param name="agent">业务员</param>
|
/// <returns></returns>
|
public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telphone, string agent)
|
{
|
|
List<GwClient> list = new List<GwClient>();
|
recordCount = 0;
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("from GW_CLIENT where 1=1");
|
OracleParameter[] oracleParameterArray = new OracleParameter[4];
|
if (clientID != null && clientID != "")
|
{
|
oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
|
stringBuilder.Append(" and CLIENT_ID like :ClientID");
|
}
|
if (clientName != null && clientName != "")
|
{
|
oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
|
stringBuilder.Append(" and CLIENT_NAME like :ClientName");
|
}
|
if (telphone != null && telphone != "")
|
{
|
oracleParameterArray[2] = new OracleParameter(":Telphone", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telphone + '%');
|
stringBuilder.Append(" and Telphone like :Company");
|
}
|
if (agent != null && agent != "")
|
{
|
oracleParameterArray[3] = new OracleParameter(":Agent", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%");
|
stringBuilder.Append(" and Agent like :Agent");
|
}
|
stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
|
{
|
while (((DbDataReader)reader).Read())
|
recordCount = this.ReadCount(reader);
|
}
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSizestring, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwClient o = new GwClient();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return list;
|
}
|
return list;
|
}
|
public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent)
|
{
|
|
|
List<GwClient> list = new List<GwClient>();
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("from GW_CLIENT where 1=1");
|
OracleParameter[] oracleParameterArray = new OracleParameter[4];
|
if (clientID != null && clientID != "")
|
{
|
oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
|
stringBuilder.Append(" and CLIENT_ID like :ClientID");
|
}
|
if (clientName != null && clientName != "")
|
{
|
oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
|
stringBuilder.Append(" and CLIENT_NAME like :ClientName");
|
}
|
if (company != null && company != "")
|
{
|
oracleParameterArray[2] = new OracleParameter(":Company", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[2]).Value = (object)('%' + company + '%');
|
stringBuilder.Append(" and COMPANY like :Company");
|
}
|
if (agent != null && agent != "")
|
{
|
oracleParameterArray[3] = new OracleParameter(":Agent", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%");
|
stringBuilder.Append(" and Agent like :Agent");
|
}
|
stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwClient o = new GwClient();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return list;
|
}
|
return list;
|
}
|
|
public bool UpdatePassword(string clientID, string password)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET PASSWORD=:PASSWORD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":PASSWORD", (object)password)) > 0;
|
}
|
|
public bool UpdatePassword(string clientID, string oldPassword, string newPassword)
|
{
|
string str = DataHelper.MD5Hex(oldPassword);
|
return OracleHelper.ExecuteSql("update GW_CLIENT SET PASSWORD=:NEWPASSWORD WHERE CLIENT_ID=:CLIENT_ID AND ((PASSWORD=:OLDPASSWORD) OR (PASSWORD=:MD5Password) )", OracleHelper.Connection, new OracleParameter(":NEWPASSWORD", (object)DataHelper.MD5Hex(newPassword)), new OracleParameter(":OLDPASSWORD", (object)oldPassword), new OracleParameter(":MD5Password", (object)str), new OracleParameter(":CLIENT_ID", (object)clientID)) >= 1;
|
}
|
|
public bool UpdateClientInfo(GwClient client)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENTNAME", (object)client.ClientName), new OracleParameter(":TELEPHONE", (object)client.Telephone), new OracleParameter(":ADDRESS", (object)client.Address), new OracleParameter(":COMPANY", (object)client.Company), new OracleParameter(":REMARK", (object)client.Remark), new OracleParameter(":ALARM_MOBILE", (object)client.AlarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)client.BalanceThreshold), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0 && this.UpdateClientSp(client);
|
}
|
|
public bool UpdateClientSp(GwClient client)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_SP SET ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":ALARM_MOBILE", (object)client.AlarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)client.BalanceThreshold), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0;
|
}
|
|
//更新充值金额AMOUNT
|
public bool UpdateClientAmount(GwClient client)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET BALANCE=:BALANCE, TOP_UP_AMOUNT_TOTAL=:TOP_UP_AMOUNT_TOTAL WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":BALANCE", (object)client.Balance), new OracleParameter(":TOP_UP_AMOUNT_TOTAL", (object)client.Top_up_Amount_Total), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0;
|
}
|
|
/// <summary>
|
/// 更新账号的信息
|
/// </summary>
|
/// <param name="item"></param>
|
/// <returns></returns>
|
public bool Update(GwClient item)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_ID=:CLIENT_ID,CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,PASSWORD=:PASSWORD,AGENT=:AGENT,PERMISSION_DATA=:PERMISSION_DATA,CREATE_TIME=SYSDATE WHERE CLIENT_ID=:Id", OracleHelper.Connection, new OracleParameter(":Id",(object)item.ClientID), new OracleParameter(":CLIENT_ID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData)) > 0;
|
}
|
|
public bool Add(GwClient item)
|
{
|
return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,COMPANY,REMARK,PASSWORD,PERMISSION_DATA,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:COMPANY,:REMARK,:PASSWORD,:PERMISSION_DATA,SYSDATE,:PARENTID,:Certification_Status)", OracleHelper.Connection, new OracleParameter(":CLIENTID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":PARENTID", (object)item.ParentId), new OracleParameter(":Certification_Status", (object)item.Certification_Status)) > 0;
|
}
|
|
public GwClient Get(string clientID)
|
{
|
GwClient o = new GwClient();
|
if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
public GwClient GetClientName(string clientName)
|
{
|
GwClient o = new GwClient();
|
if (string.IsNullOrEmpty(clientName) || string.Equals("0", clientName))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_NAME=:CLIENT_NAME"), OracleHelper.Connection, new OracleParameter(":CLIENT_NAME", (object)clientName)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
public List<GwClient> Clientlist()
|
{
|
List<GwClient> list = new List<GwClient>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT", OracleHelper.Connection))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwClient o = new GwClient();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
return list;
|
}
|
|
/// <summary>
|
/// 判断账户是否存在
|
/// </summary>
|
/// <param name="clientID">账户</param>
|
/// <returns></returns>
|
public bool IsIDExists(string clientID)
|
{
|
if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
|
return true;
|
using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)))
|
{
|
if (((DbDataReader)oracleDataReader).Read())
|
return true;
|
}
|
return false;
|
}
|
|
public bool IsNameExists(string clientName)
|
{
|
if (string.IsNullOrEmpty(clientName) || string.Equals("0", clientName))
|
return true;
|
using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_NAME=:CLIENT_NAME"), OracleHelper.Connection, new OracleParameter(":CLIENT_NAME", (object)clientName)))
|
{
|
if (((DbDataReader)oracleDataReader).Read())
|
return true;
|
}
|
return false;
|
}
|
|
public bool Delete(string clientID)
|
{
|
if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
|
return false;
|
return OracleHelper.ExecuteSql(string.Format("delete from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)) > 0;
|
}
|
|
private bool ReadInfo(OracleDataReader reader, GwClient o)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
o.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
|
o.ClientName = oracleReaderWrapper.GetString("CLIENT_NAME", "");
|
o.Telephone = oracleReaderWrapper.GetString("TELEPHONE", "");
|
o.Address = oracleReaderWrapper.GetString("ADDRESS", "");
|
o.Company = oracleReaderWrapper.GetString("COMPANY", "");
|
o.Remark = oracleReaderWrapper.GetString("REMARK", "");
|
o.Password = oracleReaderWrapper.GetString("PASSWORD", "");
|
o.Agent = oracleReaderWrapper.GetString("AGENT", "");
|
o.AlarmMobile = oracleReaderWrapper.GetString("ALARM_MOBILE", "");
|
o.BalanceThreshold = oracleReaderWrapper.GetInt("BALANCE_THRESHOLD", 0);
|
o.Balance = oracleReaderWrapper.GetInt("BALANCE", 0);
|
o.PermissionData = oracleReaderWrapper.GetString("PERMISSION_DATA", "");
|
o.Id = oracleReaderWrapper.GetInt("ID", 0);
|
o.Certification_Status = oracleReaderWrapper.GetInt("CERTIFICATION_STATUS", 0);
|
o.Client_Classes = oracleReaderWrapper.GetInt("CLIENT_CLASSES",0);
|
o.Client_Parentid = oracleReaderWrapper.GetString("CLIENT_PARENTID", "");
|
o.Client_Type = oracleReaderWrapper.GetInt("CLIENT_TYPE", 0);
|
o.Is_Enable = oracleReaderWrapper.GetInt("IS_ENABLE", 0);
|
o.Top_up_Amount_Total = (decimal)oracleReaderWrapper.GetInt("TOP_UP_AMOUNT_TOTAL", 0);
|
o.Salesman = oracleReaderWrapper.GetString("SALESMAN", "");
|
return true;
|
}
|
|
private int ReadCount(OracleDataReader reader)
|
{
|
return new OracleReaderWrapper(reader).GetInt("count", 0);
|
}
|
|
public void Dispose()
|
{
|
}
|
|
public bool UpdateWhiteList(string clientID, string mobileList)
|
{
|
OracleHelper.ExecuteSql("BEGIN\r\n UPDATE GW_CLIENT_WHITE SET MOBILE_LIST=:MOBILE_LIST WHERE CLIENT_ID=:CLIENT_ID;\r\n IF SQL%ROWCOUNT =0 THEN\r\n INSERT INTO GW_CLIENT_WHITE(CLIENT_ID,MOBILE_LIST) VALUES(:CLIENT_ID,:MOBILE_LIST);\r\n END IF;\r\nEND;", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":MOBILE_LIST", (OracleDbType)105, (object)mobileList, ParameterDirection.Input));
|
return true;
|
}
|
|
public bool UpdateBlackList(string clientID, string mobileList)
|
{
|
OracleHelper.ExecuteSql("BEGIN\r\n UPDATE GW_CLIENT_BLACK SET MOBILE_LIST=:MOBILE_LIST WHERE CLIENT_ID=:CLIENT_ID;\r\n IF SQL%ROWCOUNT =0 THEN\r\n INSERT INTO GW_CLIENT_BLACK(CLIENT_ID,MOBILE_LIST) VALUES(:CLIENT_ID,:MOBILE_LIST);\r\n END IF;\r\nEND;", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":MOBILE_LIST", (OracleDbType)105, (object)mobileList, ParameterDirection.Input));
|
return true;
|
}
|
|
public string GetBlackList(string clientId)
|
{
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT_BLACK WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
|
{
|
if (reader != null && ((DbDataReader)reader).Read())
|
return new OracleReaderWrapper(reader).GetString("MOBILE_LIST", string.Empty);
|
}
|
return string.Empty;
|
}
|
|
public string GetWhiteList(string clientId)
|
{
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT_WHITE WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
|
{
|
if (reader != null && ((DbDataReader)reader).Read())
|
return new OracleReaderWrapper(reader).GetString("MOBILE_LIST", string.Empty);
|
}
|
return string.Empty;
|
}
|
|
public bool Login(string account, string password, out GwClient client)
|
{
|
client = new GwClient();
|
string str = DataHelper.MD5Hex(password);
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT WHERE CLIENT_ID=:ACCOUNT AND ((PASSWORD=:PASSWORD) OR (PASSWORD=:MD5Password)) ", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password), new OracleParameter(":MD5Password", (object)str)))
|
{
|
if (reader == null || !((DbDataReader)reader).Read())
|
return false;
|
this.ReadInfo(reader, client);
|
}
|
return true;
|
}
|
}
|
}
|
|
|
