/// <summary>
/// 用户取消关注时调用的程序
/// </summary>
/// <param name="vipID"></param>
public void UnSubscribe(string vipID)
{
ISQLHelper sqlHelper = new DefaultSQLHelper(loggingSessionInfo.Conn);
sqlHelper.ExecuteNonQuery("DELETE FROM TimingPushMessage WHERE MemberID='" + vipID + "'");
sqlHelper.ExecuteNonQuery("DELETE FROM TimingPushMessageVipLastRecord WHERE VIPID='" + vipID + "'");
}
/// <summary>
/// 根据CustomerId获取商户信息
/// </summary>
/// <param name="pID">标识符的值</param>
public t_customerEntity GetByCustomerID(object pID)
{
//参数检查
if (pID == null)
{
return(null);
}
string id = pID.ToString();
//组织SQL
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select * from [t_customer] where customer_id='{0}' ", id.ToString());
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
//读取数据
t_customerEntity m = null;
using (SqlDataReader rdr = sqlHelper.ExecuteReader(sql.ToString()))
{
while (rdr.Read())
{
this.Load(rdr, out m);
break;
}
}
//返回
return(m);
}
public static string GetCustomerByAppId(string AppId)
{
string sql = "select top 1 CustomerId From cpos_ap..TCustomerWeiXinMapping where AppId = 'wx8a8c3152a369751a' and IsDelete = '0' ";
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
/// <summary>
/// 根据Customer_ID获取CustomerCode
/// </summary>
/// <param name="customerID"></param>
/// <returns></returns>
public string GetCustomerCodeByCustomerID(string customerID)
{
string sql = string.Format("SELECT customer_code FROM dbo.t_customer WHERE customer_id = '{0}'",
customerID);
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
public string GetCustomerIdByWx(string weixinID)
{
string sql = string.Format(
"select CustomerId FROM dbo.TCustomerWeiXinMapping WHERE WeiXinId = '{0}' AND IsDelete = 0 ", weixinID);
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
/// <summary>
/// 获取商户编码
/// </summary>
/// <param name="customerId"></param>
/// <returns></returns>
public static string GetCustomerCode(string customerId)
{
string sql = string.Format(
"select a.customer_code from t_customer a where a.customer_id='{0}' ",
customerId);
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
public static string GetCustomerConn(string customerId)
{
string sql = string.Format(
"select 'user id='+a.db_user+';password='******';data source='+a.db_server+';database='+a.db_name+';' conn " +
" from t_customer_connect a where a.customer_id='{0}' ",
customerId);
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
/// <summary>
/// 根据微信公众账号获取数据库连接字符串
/// </summary>
/// <param name="weixinID"></param>
/// <returns></returns>
public string GetCustomerConn(string weixinID)
{
string sql = string.Format(
"select 'user id='+a.db_user+';password='******';data source='+a.db_server+';database='+a.db_name+';' conn "
+ " from t_customer_connect a where a.customer_id = "
+ " (SELECT CustomerId FROM dbo.TCustomerWeiXinMapping WHERE WeiXinId = '{0}' AND IsDelete = 0) ",
weixinID);
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteScalar(sql);
return(result == null || result == DBNull.Value ? string.Empty : result.ToString());
}
public CommonResponse Process(CommandResponse pRequest)
{
CommonResponse response = new CommonResponse();
try
{
response.ResultCode = 0;
DefaultSQLHelper _sqlHelper = new DefaultSQLHelper(ConfigurationManager.AppSettings["MobileLogConn"]);
MobileCommandRecordDAO DAO = new MobileCommandRecordDAO(new Base.MobileDeviceManagementUserInfo(), _sqlHelper);
var entity = DAO.GetByID(pRequest.CommandID);
if (entity != null)
{
entity.CommandResponseCount++;
entity.ResponseJson = pRequest.ToJSON();
if (pRequest.ResponseCode < 100)
{
entity.Status = 100;
entity.ResponseCode = 0;
}
else
{
entity.Status = 2;
entity.ResponseCode = 1;
if (entity.CommandResponseCount >= 3)
{
entity.Status = 3;
}
}
if (pRequest.NeedRepeat)
{
entity.LastUpdateTime = DateTime.Now;
}
DAO.Update(entity);
}
else
{
throw new Exception("未找到命令");
}
}
catch (Exception ex)
{
Loggers.Exception(new ExceptionLogInfo(ex));
response.Message = ex.Message;
response.ResultCode = 200;
}
return(response);
}
public t_customerEntity GetCustomer(string customerCode)
{
string sql = string.Format("select * from dbo.t_customer where customer_code='{0}'"
, customerCode.Replace("'", "''"));
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
using (SqlDataReader rdr = sqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
t_customerEntity m;
this.Load(rdr, out m);
return(m);
}
}
return(null);
}
/// <summary>
/// 获取所有商户ID
/// </summary>
/// <returns></returns>
private Dictionary <string, string> GetALLCustomerInfo_List()
{
var Result = new Dictionary <string, string>();
string sql = @"select a.customer_id as CustomerID,'user id='+b.db_user+';password='******';data source='+b.db_server+';database='+b.db_name+';' as conn
from cpos_ap..t_customer as a left join cpos_ap..t_customer_connect as b on a.customer_id=b.customer_id
where a.customer_status=1" ;
string conn = ConfigurationManager.AppSettings["Conn_ap"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
DataSet ds = sqlHelper.ExecuteDataset(sql);
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Result.Add(ds.Tables[0].Rows[i]["CustomerID"].ToString(), ds.Tables[0].Rows[i]["conn"].ToString());
}
}
return(Result);
}
public DataSet GetMarketingGroupType(LoggingSessionInfo loggingSessionInfo)
{
List <SqlParameter> ls = new List <SqlParameter>();
ls.Add(new SqlParameter("@CustomerID", loggingSessionInfo.ClientID));
string sql = @"select ActivityGroupId,Name,ActivityGroupCode,Remark,CreateTime,CreateBy,LastUpdateTime,LastUpdateBy,IsDelete,DisplayIndex
,( select count(1) from T_CTW_LEvent b where b.ActivityGroupId = a.ActivityGroupId and status=10 and customerid=@CustomerID ) as WaitPublishEvent
,( select count(1) from T_CTW_LEvent c where c.ActivityGroupId = a.ActivityGroupId and status=20 and customerid=@CustomerID) as RuningEvent
,( select count(1) from T_CTW_LEvent d where d.ActivityGroupId = a.ActivityGroupId and status=40 and customerid=@CustomerID) as EndEvent
from SysMarketingGroupType a
where isdelete=0
order by DisplayIndex ";
string conn = loggingSessionInfo.CurrentLoggingManager.Connection_String;
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
var result = sqlHelper.ExecuteDataset(CommandType.Text, sql, ls.ToArray());
return(result);
}
public BaseCPOSDAO(LoggingSessionInfo userInfo)
: base(userInfo, new DirectConnectionStringManager(null))
{
bool pIsLoggingTSQL = true; // Base.ConfigInfo.IsLoggingTSQL;
BasicUserInfo pUserInfo = new BasicUserInfo();
pUserInfo.ClientID = null;
pUserInfo.UserID = null;
var connectionString = userInfo.CurrentLoggingManager.Connection_String;
var sqlHelper = new DefaultSQLHelper(connectionString);
sqlHelper.CurrentUserInfo = pUserInfo;
this.SQLHelper = sqlHelper;
if (pIsLoggingTSQL)
{
//给数据访问助手挂载执行完毕事件，以记录所有执行的SQL
this.SQLHelper.OnExecuted += new EventHandler <SqlCommandExecutionEventArgs>(SQLHelper_OnExecuted);
}
}
/// <summary>
/// Trade-获取平台微信公众号信息 add by Henry 2014-12-11
/// </summary>
/// <param name="pWhereConditions"></param>
/// <param name="pOrderBys"></param>
/// <returns></returns>
public WApplicationInterfaceEntity[] GetCloudWAppInterface(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys)
{
//组织SQL
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select * from [WApplicationInterface] where isdelete=0 ");
if (pWhereConditions != null)
{
foreach (var item in pWhereConditions)
{
sql.AppendFormat(" and {0}", item.GetExpression());
}
}
if (pOrderBys != null && pOrderBys.Length > 0)
{
sql.AppendFormat(" order by ");
foreach (var item in pOrderBys)
{
sql.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc");
}
sql.Remove(sql.Length - 1, 1);
}
//执行SQL
List <WApplicationInterfaceEntity> list = new List <WApplicationInterfaceEntity>();
string conn = ConfigurationManager.AppSettings["Conn_alading"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
using (SqlDataReader rdr = sqlHelper.ExecuteReader(sql.ToString()))
{
while (rdr.Read())
{
WApplicationInterfaceEntity m;
this.Load(rdr, out m);
list.Add(m);
}
}
//返回结果
return(list.ToArray());
}
public string[] Process(CommandRequest pRequest)
{
try
{
DefaultSQLHelper _sqlHelper = new DefaultSQLHelper(ConfigurationManager.AppSettings["MobileLogConn"]);
MobileCommandRecordDAO DAO = new MobileCommandRecordDAO(new Base.MobileDeviceManagementUserInfo(), _sqlHelper);
var entitys = DAO.GetCommand(pRequest.ClientID, pRequest.UserID);
if (entitys.Length > 0)
{
if (DAO.UpdateIsDelete(_sqlHelper.CreateTransaction(), entitys) > 0)
{
return(entitys.Aggregate(new List <string>()
{
}, (i, j) =>
{
i.Add(j.RecordID + " " + j.CommandText);
return i;
}).ToArray());
}
else
{
return(null);
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
Loggers.Exception(new ExceptionLogInfo(ex));
return(new List <string>()
{
ex.Message
}.ToArray());
}
}
public static string GetVipIDByALDVipID(string customerId, string aldVipId)
{
var parm = new SqlParameter[2];
parm[0] = new SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar)
{
Value = customerId
};
parm[1] = new SqlParameter("@ALDVipId", System.Data.SqlDbType.NVarChar)
{
Value = aldVipId
};
string conn = ConfigurationManager.AppSettings["ALDMemberConn"];
DefaultSQLHelper sqlHelper = new DefaultSQLHelper(conn);
DataSet dsVipId = sqlHelper.ExecuteDataset(CommandType.StoredProcedure, "ProcGetCustomerVipIdByALDVipId", parm);
string userId = "1";
if (dsVipId.Tables[0].Rows.Count > 0)
{
userId = dsVipId.Tables[0].Rows[0]["CustomerVipId"].ToString();
}
return(userId);
}
private void DoWork()
{
while (true)
{
try
{
ConfigurationManager.RefreshSection("appSettings");
var time = ConfigurationManager.AppSettings["Timing"];
TimeSpan.TryParse(time, out _timeSpan);
if (DateTime.Now.TimeOfDay.Hours == _timeSpan.Hours && DateTime.Now.TimeOfDay.Minutes == _timeSpan.Minutes)
{
var sql = @"
declare @Date Date = DATEADD({1}, {2}, GETDATE());
select
VIPID,
VIPName,
Phone,
Weixin,
op1.OptionText,
op2.OptionText,
Col5,
Col6
from Vip v
left join Options as op1 on convert(nvarchar(10),op1.OptionValue)=v.Col1 and op1.OptionName='VipSchool' and isnull(op1.ClientID,v.ClientID)=v.ClientID
and op1.IsDelete=v.IsDelete
left join Options as op2 on convert(nvarchar(10),op2.OptionValue)=v.Col2 and op2.OptionName='VipCourse' and isnull(op2.ClientID,v.ClientID)=v.ClientID
and op2.IsDelete=v.IsDelete
where v.Isdelete = 0 and DATEDIFF(day, v.CreateTime, @Date) = 0 and v.ClientID = '{0}'
and v.Status = 2
declare @DailyCount int = 0;
declare @MonthlyCount int = 0;
declare @WeeklyCount int = 0;
select @DailyCount = COUNT(1) from Vip where Isdelete = 0 and Status = 2 and DATEDIFF(DAY, CreateTime, @Date) = 0 and ClientID = '{0}'
select @WeeklyCount = COUNT(1) from Vip where Isdelete = 0 and Status = 2 and DATEDIFF(WEEK, CreateTime, @Date) = 0 and ClientID = '{0}'
select @MonthlyCount = COUNT(1) from Vip where Isdelete = 0 and Status = 2 and DATEDIFF(MONTH, CreateTime, @Date) = 0 and ClientID = '{0}'
select @DailyCount DailyCount, @WeeklyCount WeeklyCount, @MonthlyCount MonthlyCount;
";
sql = string.Format(sql,
ConfigurationManager.AppSettings["CustomerID"],
ConfigurationManager.AppSettings["Interval"] ?? "DAY",
ConfigurationManager.AppSettings["Increment"] ?? "0");
var sqlHlper = new DefaultSQLHelper(connStr);
var ds = sqlHlper.ExecuteDataset(sql);
var result = NoticeMailSender <MailListTemplate> .SendMail(ConfigurationManager.AppSettings["MailTo"], ConfigurationManager.AppSettings["Subject"], ds);
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} EMBA通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, result ? "成功" : "失败")
});
Loggers.Database(new DatabaseLogInfo
{
TSQL = new TSQL
{
CommandText = sql
}
});
}
}
catch (Exception ex)
{
Loggers.Exception(new BasicUserInfo(), ex);
}
Thread.Sleep(new TimeSpan(0, 0, 1, 0));
//Loggers.Debug(new DebugLogInfo
//{
// Message = DateTime.Now.ToString()
// +"\r\n" + DateTime.Now.TimeOfDay.Hours.ToString() + "\t" + _timeSpan.Hours
// +"\r\n" + DateTime.Now.TimeOfDay.Minutes +"\t" + _timeSpan.Minutes
//});
}
}
public CommonResponse Process(LogRequest[] pRequests)
{
CommonResponse response = new CommonResponse();
try
{
DefaultSQLHelper _sqlHelper = new DefaultSQLHelper(ConfigurationManager.AppSettings["MobileLogConn"]);
MobileLogRecordBLL MobileLogRecordBLL = new MobileLogRecordBLL(new Base.MobileDeviceManagementUserInfo()
{
ClientID = pRequests[0].ClientID, UserID = pRequests[0].UserID
}, _sqlHelper);
List <MobileLogRecordEntity> entityList = new List <MobileLogRecordEntity> {
};
foreach (var pRequest in pRequests)
{
MobileLogRecordEntity entity = new MobileLogRecordEntity();
entity.ClientID = pRequest.ClientID;
entity.UserID = pRequest.UserID;
entity.LogType = pRequest.LogType;
entity.LogTime = pRequest.GetLogTime();
entity.AppCode = pRequest.AppCode;
entity.ResultCode = "0";
entity.Message = pRequest.Message;
entity.CreateTime = DateTime.Now;
entity.IsDelete = 0;
entity.Tag = pRequest.Tag;
entityList.Add(entity);
}
response.ResultCode = 0;
//存数据库
//发送邮件
var transaction = _sqlHelper.CreateTransaction();
using (transaction.Connection)
{
try
{
foreach (var item in entityList)
{
MobileLogRecordBLL.Create(item, transaction);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
if (MailHelper.MailUserRegex != null)
{
var list = MailHelper.MailUsers.ToList().FindAll(t =>
{
var reg = MailHelper.MailUserRegex.IsMatch(t.AppCode + "|" + t.ClientID);
return(reg);
}).ToArray();
MailHelper.SendMail(list, pRequests);
}
else
{
foreach (var item in MailHelper.MailUsers)
{
if (item.Type == 0)
{
MailHelper.SendMail(item, pRequests.ToList().FindAll(t => t.AppCode == item.AppCode).ToArray());
}
else
{
MailHelper.SendMail(item, pRequests.ToList().FindAll(t => t.AppCode == item.AppCode && t.ClientID == item.ClientID).ToArray());
}
}
}
}
catch (Exception ex)
{
response.ResultCode = 100;
response.Message = ex.Message;
Loggers.Exception(new ExceptionLogInfo(ex));
return(response);
}
return(response);
}
