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);
}
/// <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 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
//});
}
}