internal static string Collections(string fromDate, string endDate, string wherePart)
{
string sql = "with v_t as({0}) select count(myselectid) from v_t";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT MySelectID FROM [mysoufun].[dbo].[MySelect_" + i + @"]
WHERE " + wherePart + @"
AND (createtime BETWEEN @fromDate AND @endDate)
)
UNION ALL ");
}
var chars = "UNION ALL".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}
internal static string Total(string fromDate, string endDate)
{
string sql = @"declare @date datetime
declare @sum int
set @sum=0
set @date=@fromDate
while @date<@endDate
begin
with v_b as(({0}) union ({1}) union ({2}))
select @sum=@sum+COUNT(userid) from v_b
set @date=@date+1
end
select @sum/7";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND roomid !=''
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var sqlBuilder1 = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder1.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND FangYuanID!=''
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var sqlBuilder2 = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder2.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where type='new' AND houseid!='' AND (FangYuanID='' OR FangYuanID IS NULL) AND (roomid ='' OR roomid IS NULL)
AND (createtime BETWEEN @date and @date+1)
)
UNION ALL " );
}
var chars = "UNION ALL".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars), sqlBuilder1.ToString().TrimEnd(chars), sqlBuilder2.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}
static void Main(string[] args)
{
string myfang_test = ConfigurationManager.ConnectionStrings["myfang_test"].ConnectionString;
/*
* string myfang_w = ConfigurationManager.ConnectionStrings["myfang_w"].ConnectionString;
* try
* {
* List<MySelect> list = HandleData.GetData(myfang_test);
* Console.WriteLine("导入" + list.Count);
* int i = 0;
* list.ForEach(en => { if (HandleData.Insert(myfang_w, en)) { Console.WriteLine(++i); } });
* Console.Write("完成");
*
* Console.ReadKey();
* }
* catch (Exception e)
* {
* Console.WriteLine("error:" + e.ToString());
* }*/
string sql = @"select count(*) from myselect_0
where city in (@city1,@city2) and type in (@t1,@t2,@t3)";
var param = new List <SqlParameter>();
param.Add(new SqlParameter {
ParameterName = "@city1", Value = "北京"
});
param.Add(new SqlParameter {
ParameterName = "@city2", Value = "上海"
});
param.Add(new SqlParameter {
ParameterName = "@t1", Value = "esf"
});
param.Add(new SqlParameter {
ParameterName = "@t2", Value = "new"
});
param.Add(new SqlParameter {
ParameterName = "@t3", Value = "rent"
});
var count = SlDatabase.ExecuteScalar(myfang_test, sql, param.ToArray());
Console.WriteLine(count);
Console.ReadKey();
}
internal static string NewUser(string fromDate, string endDate, string wherePart)
{
string sql = @"declare @date datetime
declare @sum int
set @sum=0
set @date=@fromDate
while @date<@endDate
begin
with v_b as({0})
select @sum=@sum+COUNT(userid) from v_b
set @date=@date+1
end
select @sum/7";
var sqlBuilder = new StringBuilder();
for (int i = 0; i < 10; i++)
{
sqlBuilder.Append("(SELECT userid FROM [mysoufun].[dbo].[MySelect_" + i + @"]
with (nolock) where " + wherePart + @"
AND (createtime BETWEEN @date and @date+1)
)
UNION " );
}
var chars = "UNION ".ToCharArray();
sql = string.Format(sql, sqlBuilder.ToString().TrimEnd(chars));
var sqlparam = new List <SqlParameter>();
sqlparam.Add(new SqlParameter()
{
ParameterName = "@fromDate", Value = fromDate
});
sqlparam.Add(new SqlParameter()
{
ParameterName = "@endDate", Value = endDate
});
return(SlDatabase.ExecuteScalar(connection, sql, sqlparam.ToArray()).ToString());
}
public static List <MySelect> GetData(string conn)
{
//string tableName = "myselect_0";
string sql = @"select * from MySelect_9 t
where t.createtime between '2016-02-22 17:10:56' and '2016-02-23 09:43:00'";
try
{
var table = new DataTable();
SlDatabase.Fill(conn, sql, table);
if (table.Rows.Count > 0)
{
//Console.WriteLine("query...");
return(table.AsEnumerable().Select(row => new MySelect
{
UserID = SlConvert.TryToInt64(row["UserID"], -1),
Face = SlConvert.TryToString(row["Face"], string.Empty),
Name = SlConvert.TryToString(row["Name"], string.Empty),
Address = SlConvert.TryToString(row["Address"], string.Empty),
Price = SlConvert.TryToDouble(row["Price"], -1),
City = SlConvert.TryToString(row["City"], string.Empty),
Linkurl = SlConvert.TryToString(row["Linkurl"], string.Empty),
Type = SlConvert.TryToString(row["Type"], string.Empty),
Pricetype = SlConvert.TryToString(row["Pricetype"], string.Empty),
Createtime = SlConvert.TryToDateTime(row["Createtime"], Convert.ToDateTime("1900-01-01")),
HouseID = SlConvert.TryToString(row["HouseID"], string.Empty),
Roomnum = SlConvert.TryToString(row["Roomnum"], string.Empty),
ID = SlConvert.TryToString(row["ID"], string.Empty),
//MySelectID = SlConvert.TryToInt32(row["MySelectID"], -1),
PropertyType = SlConvert.TryToInt32(row["PropertyType"], -1),
Coordx = SlConvert.TryToString(row["Coordx"], string.Empty),
Coordy = SlConvert.TryToString(row["Coordy"], string.Empty),
Guapailiang = SlConvert.TryToInt32(row["Guapailiang"], -1),
Service = SlConvert.TryToString(row["Service"], string.Empty),
EsfSubType = SlConvert.TryToString(row["EsfSubType"], string.Empty),
ChannelType = SlConvert.TryToString(row["ChannelType"], string.Empty),
FangYuanID = SlConvert.TryToString(row["FangYuanID"], string.Empty),
Roomid = SlConvert.TryToString(row["Roomid"], string.Empty),
ServiceArea = SlConvert.TryToString(row["servicearea"], string.Empty),
Homeid = SlConvert.TryToString(row["homeid"], string.Empty),
//HousePrice = SlConvert.TryToDouble(row["HousePrice"], -1),
//HouseStatus = SlConvert.TryToInt32(row["HouseStatus"], 0),
PriceCategory = SlConvert.TryToString(row["PriceCategory"], string.Empty),
//NewHousePrice = SlConvert.TryToDouble(row["NewHousePrice"], -1),
//PriceUnit = SlConvert.TryToString(row["PriceUnit"], string.Empty),
KnowledgeTag = SlConvert.TryToString(row["knowledgetag"], string.Empty),
Area = SlConvert.TryToString(row["area"], string.Empty),
UserName = SlConvert.TryToString(row["username"], string.Empty),
District = SlConvert.TryToString(row["district"], string.Empty),
Huxing = SlConvert.TryToString(row["huxing"], string.Empty),
Mianji = SlConvert.TryToString(row["mianji"], string.Empty),
CaseCount = SlConvert.TryToInt32(row["casecount"])
}).ToList());
}
else
{
return(null);
}
}
catch (Exception e) {
Console.WriteLine(e.ToString());
return(null);
}
}
public static bool Insert(string conn, MySelect entity)
{
try
{
string sql = @"if not exists(select myselectid from myselect_9
where userid=@userid and houseid=@houseid and roomid=@roomid and type=@type
and fangyuanid=@fangyuanid and homeid=@homeid and city=@city and linkurl=@linkurl and propertytype=@propertytype)
begin
INSERT INTO myselect_9
(
[UserID]
,[Face]
,[Name]
,[Address]
,[Price]
,[City]
,[Linkurl]
,[Type]
,[Pricetype]
,[HouseID]
,[Roomnum]
,[PropertyType]
,[Coordx]
,[Coordy]
,[Guapailiang]
,[Service]
,[EsfSubType]
,[ChannelType]
,[FangYuanID]
,[Roomid]
,[UserName]
,[Homeid]
,[ServiceArea]
,[CreateTime]
,[Area]
,[District]
,[Huxing]
,[Mianji]
,[CaseCount]
,[PriceCategory]
,[KnowledgeTag]
)
VALUES
(
@UserID
,@Face
,@Name
,@Address
,@Price
,@City
,@Linkurl
,@Type
,@Pricetype
,@HouseID
,@Roomnum
,@PropertyType
,@Coordx
,@Coordy
,@Guapailiang
,@Service
,@EsfSubType
,@ChannelType
,@FangYuanID
,@Roomid
,@UserName
,@Homeid
,@ServiceArea
,@CreateTime
,@Area
,@District
,@Huxing
,@Mianji
,@CaseCount
,@PriceCategory
,@KnowledgeTag
)
end;";
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter()
{
ParameterName = "@UserID", Value = entity.UserID
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Face", Value = entity.Face
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Name", Value = entity.Name
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Address", Value = entity.Address
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Price", Value = entity.Price
});
parameters.Add(new SqlParameter()
{
ParameterName = "@City", Value = entity.City
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Linkurl", Value = entity.Linkurl
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Type", Value = entity.Type
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Pricetype", Value = entity.Pricetype
});
parameters.Add(new SqlParameter()
{
ParameterName = "@HouseID", Value = entity.HouseID
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Roomnum", Value = entity.Roomnum
});
parameters.Add(new SqlParameter()
{
ParameterName = "@PropertyType", Value = entity.PropertyType
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Coordx", Value = entity.Coordx
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Coordy", Value = entity.Coordy
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Guapailiang", Value = entity.Guapailiang
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Service", Value = entity.Service
});
parameters.Add(new SqlParameter()
{
ParameterName = "@EsfSubType", Value = entity.EsfSubType
});
parameters.Add(new SqlParameter()
{
ParameterName = "@ChannelType", Value = entity.ChannelType
});
parameters.Add(new SqlParameter()
{
ParameterName = "@FangYuanID", Value = entity.FangYuanID
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Roomid", Value = entity.Roomid
});
parameters.Add(new SqlParameter()
{
ParameterName = "@UserName", Value = entity.UserName
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Homeid", Value = entity.Homeid
});
parameters.Add(new SqlParameter()
{
ParameterName = "@ServiceArea", Value = entity.ServiceArea
});
parameters.Add(new SqlParameter()
{
ParameterName = "@CreateTime", Value = entity.Createtime
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Area", Value = entity.Area
});
parameters.Add(new SqlParameter()
{
ParameterName = "@District", Value = entity.District
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Huxing", Value = entity.Huxing
});
parameters.Add(new SqlParameter()
{
ParameterName = "@Mianji", Value = entity.Mianji
});
parameters.Add(new SqlParameter()
{
ParameterName = "@CaseCount", Value = entity.CaseCount
});
parameters.Add(new SqlParameter()
{
ParameterName = "@PriceCategory", Value = string.IsNullOrEmpty(entity.PriceCategory) ? "" : entity.PriceCategory
});
parameters.Add(new SqlParameter()
{
ParameterName = "@KnowledgeTag", Value = string.IsNullOrEmpty(entity.KnowledgeTag) ? "" : entity.KnowledgeTag
});
var obj = SlDatabase.ExecuteNonQuery(conn, sql, parameters.ToArray());
return(obj > 0 ? true : false);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(false);
}
}
