erp/SiteCore/Handler/app.xue.cs

using BizCom;
using SQLData;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Utils.ImageUtils;
using Utils.Serialization;

namespace SiteCore.Handler
{
    public partial class app
    {
        /*
        昨天
select * from tb where datediff(day, 时间字段 ,getdate()) = 1
今天
select * from tb where datediff(day, 时间字段 ,getdate()) = 0
本周
select * from tb where datediff(week, 时间字段 ,getdate()) = 0
上周
select * from tb where datediff(week, 时间字段 ,getdate()) = 1
下周
select * from tb where datediff(week, 时间字段 ,getdate()) = －1
 
上月
Select * From TableName Where DateDiff(month, DateTimCol, GetDate()) = 1
本月
Select * From TableName Where DateDiff(month, DateTimCol, GetDate()) = 0
下月
Select * From TableName Where DateDiff(month, GetDate(), DateTimCol ) = －1
本年
Select * From TableName Where DateDiff(year, GetDate(), DateTimCol ) = 0
 
昨天：dateadd(day,-1,getdate())
明天：dateadd(day,1,getdate())
上月：month(dateadd(month, -1, getdate()))
本月：month(getdate())
下月：month(dateadd(month, 1, getdate()))
 
昨天　
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) = 1
明天
Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) = －1
最近七天
Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) <= 7
随后七天
 
当前年
select  提出日期, datepart(year,getdate()) as  当前年 from 供方资料表
前一年
select  提出日期, datepart(year,getdate())-1 as  当前年 from 供方资料表
后一年
select  提出日期, datepart(year,getdate())+1 as  当前年 from 供方资料表
 
        */


        public void get_xue_dateperson()
        {
            string sTime = "2018-09-08";// DateTime.Now.ToString("yyyy-MM-dd");
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select ddate, COUNT(0) as dc from(select convert(varchar(10), ddate, 120) as ddate, cname from dbLists where (DateDiff(d,ddate,'{0}')=1 or DateDiff(d,ddate,'{0}')=0) group by convert(varchar(10), ddate, 120), cname) as c group by ddate order by ddate asc ;", sTime);
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate, count(distinct cCardID) as dc from dbLists where (DateDiff(d,ddate,'{0}')=1 or DateDiff(d,ddate,'{0}')=0) and nentry=0 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc ;", sTime);
            sql.AppendFormat("select COUNT(0) from ( (select cname, COUNT(0) as c1 from dbo.dbLists where DATEDIFF(d, ddate, '{0}') = 0 and nentry = 0 group by cname) as a left join(select cname, COUNT(0) as c2 from dbo.dbLists where DATEDIFF(d, ddate, '{0}') = 0 and nentry = 1 group by cname) as c on c.cname = a.cname ) where (c1-c2)>0",sTime);
            DataSet ds = XueSqlHelper.ExecuteDataSet(sql.ToString());
            DataTable dt = ds.Tables[0];
            DataTable dt3 = ds.Tables[2];
            string cur = "";
            if (dt3.Rows.Count > 0)
            {
                cur = dt3.Rows[0][0].ToString();
            }
            conWrite("{\"zrs\":" + JsonString.DataTable2MiniAjaxJson(dt) + ",\"jing\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[1]) + ",\"cur\":\""+cur+"\"}");
        }

        public void get_xue_weekperson()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select ddate, COUNT(0) as dc from(select convert(varchar(10), ddate, 120) as ddate, cname from dbLists where DateDiff(dd,ddate,GetDate())<=7 group by convert(varchar(10), ddate, 120), cname) as c group by ddate order by ddate asc ;");
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate, count(0) as dc from dbLists where DateDiff(dd,ddate,GetDate())<=7 and nentry=0 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc ;");
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate, count(0) as dc from dbLists where DateDiff(dd,ddate,GetDate())<=7 and nentry=1 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc;");
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate,count(0) as dc from dbLists where DateDiff(dd,ddate,GetDate())<=7 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc");
            
            DataSet ds= XueSqlHelper.ExecuteDataSet(sql.ToString());
            DataTable dt= ds.Tables[0];
            conWrite("{\"zrs\":" + JsonString.DataTable2MiniAjaxJson(dt) + ",\"jing\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[1]) + ",\"chu\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[2]) + ",\"zong\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[3]) + "}");
        }


        public void get_xue_monthperson()
        {
            string sTime = DateTime.Now.ToString("yyyy-MM-dd");
            int m = GetInt("m");
            if (m > 0)
            {
                DateTime tTime = new DateTime(DateTime.Now.Year, m, 1);
                sTime= tTime.ToString("yyyy-MM-dd");
            }
            
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select ddate, COUNT(0) as dc from(select convert(varchar(10), ddate, 120) as ddate, cname from dbLists where DateDiff(m,ddate,'{0}')=0 group by convert(varchar(10), ddate, 120), cname) as c group by ddate order by ddate asc ;",sTime);
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate, count(0) as dc from dbLists where DateDiff(m,ddate,'{0}')=0 and nentry=0 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc ;", sTime);
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate, count(0) as dc from dbLists where DateDiff(m,ddate,'{0}')=0 and nentry=1 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc;", sTime);
            sql.AppendFormat("select convert(varchar(10), ddate, 120) as ddate,count(0) as dc from dbLists where DateDiff(m,ddate,'{0}')=0 group by convert(varchar(10), ddate, 120) order by convert(varchar(10), ddate, 120) asc", sTime);

            DataSet ds = XueSqlHelper.ExecuteDataSet(sql.ToString());
            DataTable dt = ds.Tables[0];
            conWrite("{\"zrs\":" + JsonString.DataTable2MiniAjaxJson(dt) + ",\"jing\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[1]) + ",\"chu\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[2]) + ",\"zong\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[3]) + "}");
        }


        public void get_xue_dayperson()
        {
            StringBuilder sql = new StringBuilder();
            //sql.AppendFormat("select dtime,COUNT(0) as dc from (select DateName(HH,ddate) as dtime,cname from dbLists where DateDiff(dd,ddate,'2018-09-07')=0 and nentry=0 group by DateName(HH,ddate),cname ) as c group by dtime order by CONVERT(int,dtime) asc;");
            string sTime = "2018-09-10";// DateTime.Now.ToString("yyyy-MM-dd");
            sql.AppendFormat("select DateName(HH,ddate) as dtime, count(0) as dc from dbLists where DateDiff(dd,ddate,'{0}')=0 and nentry=0 group by DateName(HH,ddate) order by convert(int,DateName(HH,ddate)) asc;", sTime);
            //sql.AppendFormat("select DateName(HH,ddate) as dtime, count(0) as dc from dbLists where DateDiff(dd,ddate,'{0}')=0 and nentry=1 group by DateName(HH,ddate) order by convert(int,DateName(HH,ddate)) asc;", sTime);
            sql.AppendFormat("select isnull(db1.dtime,db2.dtime) as dtime,isnull(db2.dc,0) as dc from (select DateName(HH,ddate) as dtime from dbLists where DateDiff(dd,ddate,'{0}')=0 and nentry=0 group by DateName(HH,ddate)) as db1 left join(select DateName(HH, ddate) as dtime, count(0) as dc from dbLists where DateDiff(dd, ddate, '{0}') = 0 and nentry = 1 group by DateName(HH, ddate)) as db2 on db1.dtime = db2.dtime order by convert(integer, db1.dtime) asc ;", sTime);
            DataSet ds = XueSqlHelper.ExecuteDataSet(sql.ToString());
            DataTable dt = ds.Tables[0];
            conWrite("{\"zrs\":" + JsonString.DataTable2MiniAjaxJson(dt) + ",\"chu\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[1]) + "}");//,\"jing\":" + JsonString.DataTable2MiniAjaxJson(ds.Tables[1]) + "
        }

        public void get_xuedata()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select convert(varchar(10),ddate,120) as date,cname,nentry,COUNT(0) as c from dbLists where DateDiff(dd,ddate,GetDate())<=7 group by convert(varchar(10),ddate,120),cname,nentry");
            DataSet ds = DbHelper.DbConn.ExecuteDataset(sql.ToString());
            DataTable dt = ds.Tables[0];
            


            //conGridJson(dStruct.TotalCount, Utils.Serialization.JsonString.DataTable2LowerAjaxJson(dt));


        }

    }
}