110,499
社区成员
发帖
与我相关
我的任务
分享
create table users
(
Id int identity(1,1) primary key,
UserName nvarchar(20) null,
CreateTime datetime default(1900-01-01)
)
insert into users (UserName,CreateTime)
select '张三',getdate()
union
select '李四','2009-05-06'
union
select '王五', '2009-05-01'
private const string conString = "Server=SZSOFT-SZZB\\SQLEXPRESS;database=office;uid=sa;pwd=abc_!123456;";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BuildUserTree();
}
}
/// <summary>
/// 生成树
/// </summary>
private void BuildUserTree()
{
trvUser.Nodes.Clear();
TreeNode root = new TreeNode("新增用户");
CreateSubNode(root);
trvUser.Nodes.Add(root);
}
/// <summary>
/// 加载子节点
/// </summary>
/// <param name="parentNode"></param>
private void CreateSubNode(TreeNode parentNode)
{
TreeNode subNode = null;
DataTable dt = GetAllUsers();
DateTime now=DateTime.Now;
int dow=(int)now.DayOfWeek;
//星期天为第一天
int di=-dow;
DateTime startWeek=now.AddDays(di);
int ei = 6 - dow ;
DateTime endWeek = now.AddDays(ei);
DateTime startMonth = Convert.ToDateTime(string.Format("{0}-{1}-01",now.Year,now.Month));
DateTime endMonth = startMonth.AddMonths(1).AddDays(-1);
// 今天新增用户
DataRow[] drFind = dt.Select(string.Format("CreateTime='{0}'", now.ToString("yyyy-MM-dd")));
TreeNode todayNode = new TreeNode("今天新增");
foreach(DataRow dr in drFind)
{
subNode = new TreeNode(dr["UserName"].ToString());
todayNode.ChildNodes.Add(subNode);
}
parentNode.ChildNodes.Add(todayNode);
// 本周新增用户
drFind = dt.Select(string.Format("CreateTime>='{0}' and CreateTime <='{1}' ", startWeek.ToString("yyyy-MM-dd"),endWeek.ToString("yyyy-MM-dd")));
TreeNode weekNode = new TreeNode("本周新增");
foreach(DataRow dr in drFind)
{
subNode = new TreeNode(dr["UserName"].ToString());
weekNode.ChildNodes.Add(subNode);
}
parentNode.ChildNodes.Add(weekNode);
// 月新增用户
drFind = dt.Select(string.Format("CreateTime>='{0}' and CreateTime <='{1}' ", startMonth.ToString("yyyy-MM-dd"), endMonth.ToString("yyyy-MM-dd")));
TreeNode monthNode = new TreeNode("本月新增");
foreach (DataRow dr in drFind)
{
subNode = new TreeNode(dr["UserName"].ToString());
monthNode.ChildNodes.Add(subNode);
}
parentNode.ChildNodes.Add(monthNode);
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns></returns>
private DataTable GetAllUsers()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = conString;
con.Open();
string sqlSelect = "select Id,UserName,convert(varchar(10), CreateTime,120) as CreateTime From Users";
SqlDataAdapter adapter = new SqlDataAdapter(sqlSelect, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
select * from tb where addDate>= convert(varchar(7), Getdate(),120) + '-01'
and adddate< convert(varchar(7),dateadd(month,1,Getdate()),120) + '-01'
int i = (int)DateTime.Now.DayOfWeek;
DateTime sd = DateTime.Now.AddDays(0 - i);
DateTime ed = DateTime.Now.AddDays(6 - i);