public DropDomainModelWebApiPaged GetFilteredDropingDomainsPaged(MatchCriteriaWithPatternModel mc, List <string> TLDs, char DropOrLastDrop, char SortBy, string SearchStr, int NumOfRecPerPage, int WhichPage, string dropDate, bool IsExcel) //,SqlParamModel SqlParams)
{
int NumOfRecord = 0;
List <DropDomainModelWebApi> ret = new List <DropDomainModelWebApi>();
string SqlPartChars = GetSqlPartForChar(mc);
string SqlTLDsPart = GetSqlPartForTLDs(TLDs);
string SqlPatternsPart = GetSqlPartForPatterns(mc);
List <DropDatesType> DropDates = new List <DropDatesType>();
string _table = (DropOrLastDrop == 'D') ? "DomainsDroping" : "DomainsDropedLastWeek";
string Sql = "select * from " + _table + " with (nolock) ";
Sql += "where uzunluk between @MinLength and @MaxLength ";
Sql += SqlTLDsPart;
Sql += SqlPartChars;
if (mc.HasSlash == 0)
{
Sql += " and HasHyphens = 0"; //HasHyphens=1 durumunda sql'e bişi ekleme eklersen sadece slash'lı olanları getirir.
}
Sql += SqlPatternsPart;
GetSqlForSearchSortAndPaging(ref Sql, SortBy, SearchStr, dropDate, NumOfRecPerPage, WhichPage, IsExcel);
string SqlForNumOfRec = Sql.Substring(0, Sql.IndexOf("order by") - 1).Replace("select *", "select count(Id)");
string SqlLog = "insert into SqlLog(Sql,FromWhere,SqlPatternsPart) values (@Sql,'GetFilteredDropingDomainsPaged',@SqlPatternsPart)";
using (IDbConnection conn = new SqlConnection(myConn))
{
conn.Open();
//try
//{
conn.Execute(SqlLog, new { @Sql = Sql, @SqlPatternsPart = SqlForNumOfRec });
ret = conn.Query <DropDomainModelWebApi>(Sql, mc).ToList();
NumOfRecord = conn.Query <int>(SqlForNumOfRec, mc).FirstOrDefault();
if (WhichPage == 1 && SortBy == '9' && SearchStr == "" && dropDate == "A") //filtreli sorgularda drop date listesine ihtiyaç yok, zaten ilk objeyi oluştururken ilk sayfa sorgusunda almıştı
{
DropDates = conn.Query <DropDatesType>("DropAuctionOp.GetDropDates", commandType: CommandType.StoredProcedure).ToList();
}
// }
// catch (Exception ex) { int a = 1; }
}
//OrganizeDate(ref ret);
DropDomainModelWebApiPaged retPaged = new DropDomainModelWebApiPaged();
retPaged.DomainList = ret;
retPaged.NumOfRecord = NumOfRecord;
retPaged.DropDates = DropDates.Where(x => TLDs.Contains(x.Ext) && x.DropOrLastDrop == DropOrLastDrop).Select(y => y.DropDate).Distinct().ToList();
return(retPaged);
}
public DropDomainModelWebApiPaged GetDropingDomainsByKeywordsPaged(string IncludeHyphens, string IncludeNumbers, List <KeywordModel> Keywords, List <string> TLDs, char DropOrLastDrop, char SortBy, string SearchStr, int NumOfRecPerPage, int WhichPage, string dropDate, bool IsExcel) //, SqlParamModel SqlParams)
{
//paging, text search, order by kısımlarını clientte yapacağız-bunun için full data alınacak, IsChanged parametresi değişmediği sürece servere tekrar gelinmeyecek
int NumOfRecord = 0;
string _table = (DropOrLastDrop == 'D') ? "DomainsDroping" : "DomainsDropedLastWeek";
List <DropDomainModelWebApi> ret = new List <DropDomainModelWebApi>();
List <DropDatesType> DropDates = new List <DropDatesType>();
string SqlTLDsPart = GetSqlPartForTLDs(TLDs);
string SqlKeywordsPart = GetSqlPartForKeywords(Keywords);
string Sql = "select * from " + _table + " with (nolock) ";
Sql += "where ";
Sql += SqlTLDsPart.Substring(4); //baştaki and'i atmak için
if (IncludeHyphens == "0")
{
Sql += " and HasHyphens = 0";
}
if (IncludeNumbers == "0")
{
Sql += " and HasNumbers = 0";
}
Sql += SqlKeywordsPart;
GetSqlForSearchSortAndPaging(ref Sql, SortBy, SearchStr, dropDate, NumOfRecPerPage, WhichPage, IsExcel);
string SqlForNumOfRec = Sql.Substring(0, Sql.IndexOf("order by") - 1).Replace("select *", "select count(Id)");
string SqlLog = "insert into SqlLog(Sql,FromWhere) values (@Sql,'GetDropingDomainsByKeywordsPaged')";
using (IDbConnection conn = new SqlConnection(myConn))
{
conn.Open();
//try
//{
conn.Execute(SqlLog, new { @Sql = Sql });
ret = conn.Query <DropDomainModelWebApi>(Sql).ToList();
NumOfRecord = conn.Query <int>(SqlForNumOfRec).FirstOrDefault();
if (WhichPage == 1 && SortBy == '9' && SearchStr == "" && dropDate == "A") //filtreli sorgularda drop date listesine ihtiyaç yok, zaten ilk objeyi oluştururken ilk sayfa sorgusunda almıştı
{
DropDates = conn.Query <DropDatesType>("DropAuctionOp.GetDropDates", commandType: CommandType.StoredProcedure).ToList();
}
// }
// catch (Exception ex) { int a = 1; }
}
DropDomainModelWebApiPaged retPaged = new DropDomainModelWebApiPaged();
retPaged.DomainList = ret;
retPaged.NumOfRecord = NumOfRecord;
retPaged.DropDates = DropDates.Where(x => TLDs.Contains(x.Ext) && x.DropOrLastDrop == DropOrLastDrop).Select(y => y.DropDate).Distinct().ToList();
return(retPaged);
}
public HttpResponseMessage GetDropsPaged()
{
//Data subscription'ın geçerliliği kontrolü
if (!IsSubscribedDataService())
{
return(Request.CreateResponse(HttpStatusCode.UpgradeRequired, "Renew your data subscription!"));
}
//****
DomainData dd = new DomainData();
DropDomainModelWebApiPaged DropDomainList = new DropDomainModelWebApiPaged();
var ListOfTLDs = Request.Headers.GetValues("ListOfTLDs").FirstOrDefault();
List <string> TLDs = JsonConvert.DeserializeObject <List <string> >(ListOfTLDs);
string IsKeyword = Request.Headers.GetValues("IsKeyword").FirstOrDefault();
char DropOrLastDrop = Convert.ToChar(Request.Headers.GetValues("DropOrLastDrop").FirstOrDefault());
//for paging+filters
int NumOfRecPerPage = Convert.ToInt32(Request.Headers.GetValues("NumOfRecPerPage").FirstOrDefault());
int WhichPage = Convert.ToInt32(Request.Headers.GetValues("WhichPage").FirstOrDefault());
char SortBy = Convert.ToChar(Request.Headers.GetValues("SortBy").FirstOrDefault());
string SearchStr = Request.Headers.GetValues("SearchStr").FirstOrDefault();
//string KeyWord = Request.Headers.GetValues("KeyWord").FirstOrDefault();
string dropDate = Request.Headers.GetValues("dropDate").FirstOrDefault();
//****
//for excel
bool IsExcel = (Request.Headers.GetValues("IsExcel").FirstOrDefault() == "1")?true:false;
if (IsKeyword == "0")
{
var MatchCriteria = Request.Headers.GetValues("MatchCriteria").FirstOrDefault();
MatchCriteriaWithPatternModel objMC = JsonConvert.DeserializeObject <MatchCriteriaWithPatternModel>(MatchCriteria);
DropDomainList = dd.GetFilteredDropingDomainsPaged(objMC, TLDs, DropOrLastDrop, SortBy, SearchStr, NumOfRecPerPage, WhichPage, dropDate, IsExcel);
}
if (IsKeyword == "1")
{
string IncludeHyphens = Request.Headers.GetValues("IncludeHyphens").FirstOrDefault();
string IncludeNumbers = Request.Headers.GetValues("IncludeNumbers").FirstOrDefault();
var Keywords = Request.Headers.GetValues("Keywords").FirstOrDefault();
List <KeywordModel> objKeywords = JsonConvert.DeserializeObject <List <KeywordModel> >(Keywords);
DropDomainList = dd.GetDropingDomainsByKeywordsPaged(IncludeHyphens, IncludeNumbers, objKeywords, TLDs, DropOrLastDrop, SortBy, SearchStr, NumOfRecPerPage, WhichPage, dropDate, IsExcel);
}
return(Request.CreateResponse(HttpStatusCode.OK, DropDomainList));
//return Request.CreateResponse(HttpStatusCode.OK, HttpContext.Current.User.Identity.Name);
}
