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