public List <Dictionary <string, string> > getExtendResAttr(int restype)
{
string sql = string.Format(@"SELECT [Type],[Name]
FROM [StatDB].[dbo].[B_ResAttribute]
where restype={0}
order by Type desc ", restype);
using (IDataReader reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql))
{
return(RelationDBDataSetUtil.ParseDataSet(reader));
}
}
public List <Dictionary <string, string> > GetClusterStatLog(string startdate, string enddate, int modulutype)
{
string sql = string.Format(@" select ID,marktime,taskname,detail,modulutype
from Msg_Error
where modulutype={2} and marktime >='{0}' and marktime<'{1}' ", startdate,
enddate, modulutype);
using (IDataReader dataReader = MySqlHelper.ExecuteReader(Mysql_Statdb_Connstring, sql))
{
return(RelationDBDataSetUtil.ParseDataSet(dataReader));
}
}
public List <Dictionary <string, string> > GetD_StatDownCPAAndApiDailyByCacheMap(int restype, int softid, int platform,
DateTime begintime, DateTime endtime, int period, int countryid, int provinceid, int downtype, string positions, string adsource, string version)
{
string sql = string.Format(@"
select
StatDate,b.SoftName,b.Bid,appVersion,platform,areaid,position,a.adsource,
downsuccesscount,downCount,activationCount,setupSuccessCount,showCount,browseCount
into #tmp_log
from D_StatDownCPA a with(nolock) inner join Cfg_SynchroApiAdvertList b with(nolock)
on a.campaignID=b.campaignID and a.adsource=b.adsource
where a.restype=@restype and a.softid=@softid and statdate between @begindate and @enddate;
select
StatDate,min(softname) f_name,min(bid) bid,
sum(downsuccesscount) downsuccesscount,
sum(downCount) downCount,
sum(activationCount) activationCount,
sum(setupSuccessCount) setupSuccessCount,
sum(showCount) showCount,
sum(browseCount) browseCount
from #tmp_log
where 1=1 {0} {1} {2} {3} {4}
group by statdate
order by statdate",
countryid != -1 ? " and areaid=@countryid" : "",
platform != 0 ? " and platform=@platform" : "",
!string.IsNullOrEmpty(positions) ? string.Format(" and position in ({0})", positions) : "",
adsource == "-1" || string.IsNullOrEmpty(adsource) ? "" : string.Format(" and adsource in ({0})", adsource),
!string.IsNullOrEmpty(version) ? string.Format(" and appVersion=@version", version) : "");
SqlParameter[] parameters = new SqlParameter[]
{
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softid),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@downtype", SqlDbType.Int, 2, downtype),
SqlParamHelper.MakeInParam("@restype", SqlDbType.SmallInt, 2, restype),
SqlParamHelper.MakeInParam("@CountryID", SqlDbType.Int, 4, countryid),
SqlParamHelper.MakeInParam("@ProvinceID", SqlDbType.Int, 4, provinceid),
SqlParamHelper.MakeInParam("@version", SqlDbType.VarChar, 100, version),
SqlParamHelper.MakeInParam("@begindate", SqlDbType.Int, 4, int.Parse(begintime.ToString("yyyyMMdd"))),
SqlParamHelper.MakeInParam("@enddate", SqlDbType.Int, 4, int.Parse(endtime.ToString("yyyyMMdd")))
};
return(RelationDBDataSetUtil.ParseDataSet(SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, parameters)));
}
public List <Dictionary <string, string> > GetD_StatDownRankByClassMap(int restype, int softid, int platform,
DateTime begintime, DateTime endtime, int period, int pcid, int cid, int downtype)
{
string sql = string.Format(@"
select A.Rank lastrank,B.* from (
select top 200 A.SoftID,A.Platform,A.ResType,A.ResID,A.StatDate,
A.PCID,A.CID,A.RealDownCount,ResName,ResIdentifier,
ROW_NUMBER() over(order by RealDownCount desc) Rank
from D_StatDownRankByClass A with(nolock)
where A.SOftID=@softid and A.Platform=@platform
and StatDate = @enddate and restype=@restype
and PCID=@pcid and cid=@cid
and period=@period and downtype=@downtype
) B left join (
select top 200 A.SoftID,A.Platform,A.ResType,A.ResID,A.StatDate,
A.PCID,A.CID,A.RealDownCount,ResName,ResIdentifier,
ROW_NUMBER() over(order by RealDownCount desc) Rank
from D_StatDownRankByClass A with(nolock)
where A.SOftID=@softid and A.Platform=@platform
and StatDate = @begindate and restype=@restype
and PCID=@pcid and cid=@cid
and period=@period and downtype=@downtype
) A on A.ResID=B.ResID and A.SoftID=B.SoftID and A.ResType=B.ResType and A.PCID=B.PCID
and A.CID=B.CID
");
SqlParameter[] parameters = new SqlParameter[]
{
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softid),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@downtype", SqlDbType.Int, 2, downtype),
SqlParamHelper.MakeInParam("@restype", SqlDbType.SmallInt, 2, restype),
SqlParamHelper.MakeInParam("@pcid", SqlDbType.Int, 4, pcid),
SqlParamHelper.MakeInParam("@cid", SqlDbType.Int, 4, cid),
SqlParamHelper.MakeInParam("@begindate", SqlDbType.Int, 4, int.Parse(begintime.ToString("yyyyMMdd"))),
SqlParamHelper.MakeInParam("@enddate", SqlDbType.Int, 4, int.Parse(endtime.ToString("yyyyMMdd")))
,
};
using (var reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, parameters))
{
return(RelationDBDataSetUtil.ParseDataSet(reader));
}
}
public List <Dictionary <string, string> > GetD_StatDownCPAAndApiByCacheMap(int restype, int softid, int platform,
DateTime begintime, DateTime endtime, int period, int countryid, int provinceid, int downtype, string positions, string adsource)
{
string sql = string.Format(@"
select
b.identifier f_identifier,min(b.softname) f_name,a.campaignID,
min(b.bid) bid,
sum(downCount) downCount,
sum(activationCount) activationCount,
sum(setupSuccessCount) setupSuccessCount,
sum(showCount) showCount,
sum(browseCount) browseCount
from D_StatDownCPA a with(nolock) inner join Cfg_SynchroApiAdvertList b with(nolock)
on a.campaignID=b.campaignID and a.adsource=b.adsource
where a.restype=@restype and a.softid=@softid and statdate between @begindate and @enddate {0} {1} {2} {3}
group by b.identifier,a.campaignID",
countryid != -1 ? " and areaid=@countryid" : "",
platform != 0 ? " and a.platform=@platform" : "",
string.IsNullOrEmpty(positions) ? "" : string.Format(" and position in ({0}) ", positions),
adsource == "-1" || string.IsNullOrEmpty(adsource) ? "" : string.Format(" and a.adsource in ({0}) ", adsource));
SqlParameter[] parameters = new SqlParameter[]
{
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softid),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@downtype", SqlDbType.Int, 2, downtype),
SqlParamHelper.MakeInParam("@restype", SqlDbType.SmallInt, 2, restype),
SqlParamHelper.MakeInParam("@CountryID", SqlDbType.Int, 4, countryid),
SqlParamHelper.MakeInParam("@ProvinceID", SqlDbType.Int, 4, provinceid),
SqlParamHelper.MakeInParam("@begindate", SqlDbType.Int, 4, int.Parse(begintime.ToString("yyyyMMdd"))),
SqlParamHelper.MakeInParam("@enddate", SqlDbType.Int, 4, int.Parse(endtime.ToString("yyyyMMdd")))
};
using (var reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, parameters))
{
return(RelationDBDataSetUtil.ParseDataSet(reader));
}
}
public List <Dictionary <string, string> > GetD_StatDownCPAByCacheMap(int restype, int softid, int platform,
DateTime begintime, DateTime endtime, int period, int countryid, int provinceid, int downtype)
{
string sql = string.Format(@"
select
a.resid,
sum(a.downcount) downcount,
sum(a.usercount) usercount,
sum(a.usercount) * 1.0 / sum(a.downcount) as userrate,
sum(SetupSuccessCount) SetupSuccessCount,
sum(DownUserCount) DownUserCount,
b.f_name,
b.f_identifier
from D_StatDownCpaByArea a inner join Softs_CPA b on a.resid = b.resid
where b.softid = @softid and statdate between @begindate and @enddate and b.restype = @restype {0} {1}
group by a.resid,b.f_name,b.f_identifier
order by sum(a.downcount) desc",
countryid != -1 ? " and areaid = @countryid" : "",
platform != 0 ? " and b.platform=@platform" : "");
SqlParameter[] parameters = new SqlParameter[]
{
SqlParamHelper.MakeInParam("@softid", SqlDbType.Int, 4, softid),
SqlParamHelper.MakeInParam("@platform", SqlDbType.TinyInt, 1, platform),
SqlParamHelper.MakeInParam("@period", SqlDbType.TinyInt, 1, period),
SqlParamHelper.MakeInParam("@downtype", SqlDbType.Int, 2, downtype),
SqlParamHelper.MakeInParam("@restype", SqlDbType.SmallInt, 2, restype),
SqlParamHelper.MakeInParam("@CountryID", SqlDbType.Int, 4, countryid),
SqlParamHelper.MakeInParam("@ProvinceID", SqlDbType.Int, 4, provinceid),
SqlParamHelper.MakeInParam("@begindate", SqlDbType.Int, 4, int.Parse(begintime.ToString("yyyyMMdd"))),
SqlParamHelper.MakeInParam("@enddate", SqlDbType.Int, 4, int.Parse(endtime.ToString("yyyyMMdd")))
};
using (var reader = SqlHelper.ExecuteReader(StatConn, CommandType.Text, sql, parameters))
{
return(RelationDBDataSetUtil.ParseDataSet(reader));
}
}
