/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
protected virtual string FormatName(string name)
{
return(SqlParamHelper.FormatName(name));
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="field">字段名称</param>
/// <param name="sqlDbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="value">参数</param>
protected virtual void AddParameter(string field, int sqlDbType, int size, object value)
{
AddParameter(SqlParamHelper.MakeInParam(field, (SqlDbType)sqlDbType, size, value));
}
/// <summary>
/// 格式化Select语句中的列名
/// </summary>
/// <param name="splitChat"></param>
/// <param name="columns"></param>
/// <returns></returns>
protected virtual string FormatQueryColumn(string splitChat, ICollection <string> columns)
{
return(SqlParamHelper.FormatQueryColumn(splitChat, columns));
}
/// <summary>
/// 增加guid类型的参数
/// </summary>
/// <param name="paramName"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual void AddParameterByGuid(string paramName, object value)
{
AddParameter(SqlParamHelper.MakeInParam(paramName, SqlDbType.UniqueIdentifier, 0, value));
}
/// <summary>
/// 增加text类型的参数
/// </summary>
/// <param name="paramName"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual void AddParameterByText(string paramName, object value)
{
AddParameter(SqlParamHelper.MakeInParam(paramName, SqlDbType.Text, 0, value));
}
/// <summary>
/// 添加表达式语句
/// </summary>
/// <param name="paramName">不带@参数名</param>
/// <param name="dbType">字段的类型</param>
/// <param name="size">字段的大小</param>
/// <param name="value">参数值</param>
protected virtual void AddExpressParam(string paramName, int dbType, int size, object value)
{
AddExpressParam(SqlParamHelper.MakeInParam(paramName, (SqlDbType)dbType, size, value));
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="field">字段名称</param>
/// <param name="value">参数</param>
public virtual void AddParameter(string field, object value)
{
AddParameter(SqlParamHelper.MakeInParam(field, value));
}
/// <summary>
/// 格式化where表达式，针对不同数据库的参数关键词的处理
/// </summary>
/// <param name="fieldName"></param>
/// <param name="compareChar"></param>
/// <param name="paramName"></param>
public virtual string FormatExpression(string fieldName, string compareChar = "", string paramName = "")
{
return(SqlParamHelper.FormatFilterParam(fieldName, compareChar, paramName));
}
/// <summary>
/// 添加表达式语句
/// </summary>
/// <param name="paramName">不带@参数名</param>
/// <param name="value">参数值</param>
public virtual void AddExpressParam(string paramName, object value)
{
AddExpressParam(SqlParamHelper.MakeInParam(paramName, value));
}
public void AddParam(string paramName, SqlDbType dbType, int size, object value)
{
AddParam(SqlParamHelper.MakeInParam(paramName, dbType, size, value));
}
/// <summary>
/// 增加空密码处理
/// 修改：伍张发
/// </summary>
/// <returns></returns>
public int GetUserId()
{
RegType regType = RegType;
PwdType pwdType = PwdType.DES;
SetLoginType(ref regType, ref pwdType, PassportId);
List <SqlParameter> listTmp = new List <SqlParameter>();
string sGetSql = "select top 1 userid,PassportId,DeviceID,RegType,RetailID,RetailUser,WeixinCode from SnsUserInfo ";
string password = _PassportPwd;
if (regType == RegType.Normal)
{
if (pwdType == PwdType.MD5)
{
password = CryptoHelper.DES_Decrypt(password, GameEnvironment.ProductDesEnKey);
password = PasswordEncryptMd5(password);
}
sGetSql += "where PassportId=@aPassportId and PassportPwd=@PassportPwd";
listTmp.Add(SqlParamHelper.MakeInParam("@aPassportId", SqlDbType.VarChar, 0, _PassportId));
listTmp.Add(SqlParamHelper.MakeInParam("@PassportPwd", SqlDbType.VarChar, 0, password));
}
else if (regType == RegType.Guest)
{
if (pwdType == PwdType.MD5)
{
password = CryptoHelper.DES_Decrypt(password, GameEnvironment.ProductDesEnKey);
if (password.Length != 32)
{
//判断是否已经MD5加密
password = PasswordEncryptMd5(password);
}
}
sGetSql += "where (DeviceID=@DeviceID and PassportPwd=@PassportPwd ) and PassportId=@aPassportId and RegType=@RegType";
listTmp.Add(SqlParamHelper.MakeInParam("@aPassportId", SqlDbType.VarChar, 0, _PassportId));
listTmp.Add(SqlParamHelper.MakeInParam("@DeviceID", SqlDbType.VarChar, 0, _deviceID));
listTmp.Add(SqlParamHelper.MakeInParam("@PassportPwd", SqlDbType.VarChar, 0, password));
listTmp.Add(SqlParamHelper.MakeInParam("@RegType", SqlDbType.Int, 0, (int)regType));
}
else
{
sGetSql += "where RetailID=@RetailID and RetailUser=@RetailUser";
listTmp.Add(SqlParamHelper.MakeInParam("@RetailID", SqlDbType.VarChar, 0, RetailID));
listTmp.Add(SqlParamHelper.MakeInParam("@RetailUser", SqlDbType.VarChar, 0, RetailUser));
}
SqlParameter[] paramsGet = listTmp.ToArray();
using (SqlDataReader aReader = SqlHelper.ExecuteReader(config.connectionString, CommandType.Text, sGetSql, paramsGet))
{
SnsCenterUser user = new SnsCenterUser();
if (aReader.Read())
{
try
{
_userid = Convert.ToInt32(aReader["userid"]);
_PassportId = aReader["PassportId"].ToString();
_deviceID = aReader["DeviceID"].ToNotNullString();
RegType = aReader["RegType"].ToEnum <RegType>();
RetailID = aReader["RetailID"].ToNotNullString();
RetailUser = aReader["RetailUser"].ToNotNullString();
WeixinCode = aReader["WeixinCode"].ToNotNullString();
}
catch (Exception ex)
{
TraceLog.WriteError("GetUserId method error:{0}, sql:{0}", ex, sGetSql);
}
return(_userid);
}
else
{
return(0);
}
}
}
/// <summary>
/// 开放对外的留存用户数据
/// </summary>
/// <param name="softId"></param>
/// <param name="platform"></param>
/// <param name="channelIds"></param>
/// <param name="period"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="minTime">最小可查看时间</param>
/// <returns></returns>
public List <Sjqd_StatRetainedUsers> GetChannelRetainUsersForOut(int softId, MobileOption platform,
List <int> channelIds, int period,
DateTime beginDate, DateTime endDate,
DateTime minTime)
{
if (platform == 0)
{
return(GetChannelRetainUsersForOut(softId, channelIds, period, beginDate, endDate, minTime));
}
if (channelIds.Count == 0)
{
return(new List <Sjqd_StatRetainedUsers>());
}
string ids = string.Join(",", channelIds.Select(p => p.ToString()).ToArray());
string sql = string.Format(@"declare @statendtime datetime =@begintime;
declare @statbegindate datetime;
declare @begindateint int;
declare @enddateint int;
create table #retaintable(originaldate int,OriginalNewUserCount int)
while(@statendtime<=@endtime)
begin
set @enddateint=CAST( convert(varchar(8),@statendtime,112) as int);
if(@period=3)
begin
set @statbegindate=dateadd(day,-6,@statendtime);
set @begindateint=CAST( convert(varchar(8),@statbegindate,112) as int);
insert into #retaintable
select @enddateint originaldate, SUM( (NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate between @begindateint and @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(day,7,@statendtime);
end
else if(@period=5)
begin
set @statbegindate=dateadd(day,1,dateadd(MONTH,-1,@statendtime));
set @begindateint=CAST( convert(varchar(8),@statbegindate,112) as int);
insert into #retaintable
select @enddateint originaldate, SUM((NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate between @begindateint and @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(MONTH,1,@statendtime);
end
else
begin--period=1
insert into #retaintable
select @enddateint originaldate, SUM((NewUserCount-isnull(NewUserCount_Shualiang,0))*(case when Modulus=0 then 1 else Modulus end))
from dbo.Sjqd_StatChannelUsers
where softid=@softid and platform=@platform and Period=1
and StatDate= @enddateint
and ChannelID in({0}) and StatDate>=@mindateint
set @statendtime=dateadd(day,1,@statendtime);
end
print(@statendtime);
print(@begindateint);
print(@enddateint);
end
set @begindateint=CAST( convert(varchar(8),@begintime,112) as int);
set @enddateint=CAST( convert(varchar(8),@endtime,112) as int);
select B.StatDate,B.OriginalDate,B.Period,B.SoftID,B.Platform,A.OriginalNewUserCount,
A.OriginalNewUserCount*1.0/B.OriginalNewUserCount*B.RetainedUserCount RetainedUserCount
from #retaintable A
inner join
(
select
StatDate,OriginalDate,@period Period,@softid SoftID,@platform Platform,
SUM(OriginalNewUserCount) originalnewusercount,
SUM(RetainedUserCount) RetainedUserCount from dbo.U_StatRetainedUsers with(nolock)
where OriginalDate between @begindateint and @enddateint
and softid=@softid and platform=@platform and Period=@period and ChannelID in({0})
group by OriginalDate,StatDate
) B
on A.originaldate=B.OriginalDate
where A.OriginalNewUserCount!=0
order by B.OriginalDate desc,B.StatDate desc", ids);
SqlParameter[] paras =
{
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softId),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@begintime", SqlDbType.DateTime, 8, beginDate),
SqlParamHelper.MakeInParam("@endtime", SqlDbType.DateTime, 8, endDate),
SqlParamHelper.MakeInParam("@mindateint", SqlDbType.Int, 4, minTime.ToString("yyyyMMdd")),
};
List <Sjqd_StatRetainedUsers> list = new List <Sjqd_StatRetainedUsers>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, paras))
{
while (reader.Read())
{
list.Add(new Sjqd_StatRetainedUsers(reader));
}
}
return(list);
}
/// <summary>
/// 在表sjqd_statusers 获取平均时长和平均使用次数
/// </summary>
/// <param name="softid"></param>
/// <param name="platformid"></param>
/// <param name="period"></param>
/// <param name="begintime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
public List <Sjqd_ULSessionAvgUsers> GetULSessionAvgUsers(int softid, int platformid, int period,
DateTime begintime, DateTime endtime)
{
string sql = @"
select
a.StatDate,a.Period,a.SoftID,a.Platform,
NewUserCountFromCache+NewUserCount NewUserCount,
ActiveUserCountFromCache+ActiveUserCount ActiveUserCount,
#temp.AvgSessions, #temp.AvgSessionLength
from Sjqd_StatUsers a left join #temp
on a.StatDate = #temp.StatDate and a.Period = #temp.period and a.SoftID = #temp.softid and a.Platform = #temp.platform
where a.SoftID=@softid and a.platform=@platform
and a.StatDate between @begintime and @endtime and a.Period=@period";
SqlParameter[] parameters = new SqlParameter[]
{
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softid),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platformid),
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@begintime", SqlDbType.Int, 4, int.Parse(begintime.ToString("yyyyMMdd")))
,
SqlParamHelper.MakeInParam("@endtime", SqlDbType.Int, 4, int.Parse(endtime.ToString("yyyyMMdd")))
};
var sql_session = @"
select
StatDate,Period,SoftID,Platform,
AvgSessions,AvgSessionLength
from UL_SessionLengthAvg
where SoftID=?softid and platform=?platform
and StatDate between ?begintime and ?endtime and Period=?period";
var mysql_params = new MySqlParameter[]
{
new MySqlParameter("?softid", softid),
new MySqlParameter("?platform", platformid),
new MySqlParameter("?period", period),
new MySqlParameter("?begintime", int.Parse(begintime.ToString("yyyyMMdd"))),
new MySqlParameter("?endtime", int.Parse(endtime.ToString("yyyyMMdd")))
};
var ds = MySqlHelper.ExecuteDataset(StatDB_MySQL_ConnString, sql_session, mysql_params);
List <Sjqd_ULSessionAvgUsers> ulSessionsAvg = new List <Sjqd_ULSessionAvgUsers>();
using (var conn = new SqlConnection(statdbConn))
{
conn.Open();
var sql_temp =
"create table #temp (statDate int, period int, softid int, platform int, avgsessions decimal(10,2),AvgSessionLength decimal(10,2));";
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql_temp);
using (var sqlBulkCopy = new SqlBulkCopy(conn))
{
sqlBulkCopy.DestinationTableName = "#temp";
sqlBulkCopy.WriteToServer(ds.Tables[0]);
}
using (var dataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, parameters))
{
while (dataReader.Read())
{
Sjqd_ULSessionAvgUsers ulAvgUser = new Sjqd_ULSessionAvgUsers();
if (dataReader["StatDate"] != null && dataReader["StatDate"] != DBNull.Value)
{
int date = Convert.ToInt32(dataReader["StatDate"]);
ulAvgUser.StatDate = new DateTime(date / 10000, date % 10000 / 100, date % 100);
}
if (dataReader["Period"] != null && dataReader["Period"] != DBNull.Value)
{
ulAvgUser.Period = (net91com.Stat.Core.PeriodOptions)Convert.ToInt32(dataReader["Period"]);
}
if (dataReader["SoftID"] != null && dataReader["SoftID"] != DBNull.Value)
{
ulAvgUser.SoftID = Convert.ToInt32(dataReader["SoftID"]);
}
if (dataReader["Platform"] != null && dataReader["Platform"] != DBNull.Value)
{
ulAvgUser.Platform = (MobileOption)Convert.ToInt32(dataReader["Platform"]);
}
if (dataReader["NewUserCount"] != DBNull.Value && dataReader["ActiveUserCount"] != DBNull.Value)
{
ulAvgUser.UseUsers = Convert.ToInt32(dataReader["NewUserCount"]) +
Convert.ToInt32(dataReader["ActiveUserCount"]);
}
if (dataReader["AvgSessions"] != null && dataReader["AvgSessions"] != DBNull.Value)
{
ulAvgUser.AvgSessions = Convert.ToDecimal(dataReader["AvgSessions"]);
}
if (dataReader["AvgSessionLength"] != null && dataReader["AvgSessionLength"] != DBNull.Value)
{
ulAvgUser.AvgSessionLength = Convert.ToDecimal(dataReader["AvgSessionLength"]);
}
ulAvgUser.AllSessionLength = (long)(ulAvgUser.UseUsers * ulAvgUser.AvgSessionLength);
ulAvgUser.AllSessions = (int)(ulAvgUser.UseUsers * ulAvgUser.AvgSessions);
ulAvgUser.AvgLengthPerSession = ulAvgUser.AvgSessions == 0
? 0
: Math.Round(
ulAvgUser.AvgSessionLength / ulAvgUser.AvgSessions, 2);
ulSessionsAvg.Add(ulAvgUser);
}
}
}
return(ulSessionsAvg);
}
public int BatchEditPositionName(B_DownPositionEntity position, string strlist)
{
using (var conn = new SqlConnection(statdbConn))
{
conn.Open();
var list = strlist.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
string sql = @"create table #temp(name varchar(100),position int);";
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
var dt = new DataTable();
var dc = new DataColumn("name", typeof(string))
{
MaxLength = 100
};
dt.Columns.Add(dc);
dc = new DataColumn("position", typeof(int));
dt.Columns.Add(dc);
foreach (var item in list)
{
var data = item.Split(",，\t".ToCharArray());
if (data.Length < 2)
{
continue;
}
var row = dt.NewRow();
row["position"] = data[0];
row["name"] = data[1];
dt.Rows.Add(row);
}
if (dt.Rows.Count > 0)
{
using (var sbc = new SqlBulkCopy(conn))
{
sbc.BulkCopyTimeout = 600;
sbc.BatchSize = dt.Rows.Count;
sbc.DestinationTableName = "#temp";
sbc.WriteToServer(dt);
}
sql = @"
update a
set a.Name = b.name
from B_DownPositions a left join #temp b
on a.Position = b.position
where a.ResType=@restype and a.projectsource=@projectsource and a.projectsourcetype=@projectsourcetype and b.position is not null ";
var param = new[] {
SqlParamHelper.MakeInParam("@restype", SqlDbType.SmallInt, 2, position.ResType),
SqlParamHelper.MakeInParam("@projectsource", SqlDbType.SmallInt, 2, position.ProjectSource),
SqlParamHelper.MakeInParam("@projectsourcetype", SqlDbType.TinyInt, 1, position.ProjectSourceType)
};
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, param));
}
return(0);
}
}
