Exemple #1
0
        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));
            }
        }
Exemple #2
0
        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));
            }
        }