DataControl.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace DBAccess
{
    public class DataControl
    {
        public DataSet GetEquip()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT e.ID, e.WatchNo, e.WatchPara, e.WatchUnit, e.WatchMax, e.WatchMin ");
            sb.Append(" FROM Equipment e ORDER BY e.ID DESC");
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString());
        }


        public DataSet GetEquipDataByDate(string strID, string strDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT t.TimeValue,w.EID,w.WatchDate,w.WatchTime,w.WatchValue  ");
            sb.Append(" FROM TimeDict t LEFT JOIN WatchData w ON t.TimeValue=w.WatchTime  ");
            sb.Append(" AND w.WatchDate=@WatchDate AND w.EID=@EID  ORDER BY t.TimeValue ");
            SqlParameter[] p ={
                                 new SqlParameter("@WatchDate",strDate),
                                 new SqlParameter("@EID",strID)
                             };
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }








        public int SetData(string strID, string strNum, string strDate, string strTime)
        {
            DeleteData(strID, strDate, strTime);
            StringBuilder sb = new StringBuilder();
            sb.Append(" INSERT INTO WatchData (EID,WatchDate,WatchTime,WatchValue,WatchDateTime) ");
            sb.Append(" VALUES(@EID,@WatchDate,@WatchTime,@WatchValue,@WatchDateTime) ");




            SqlParameter[] p ={
                                 new SqlParameter("@EID",strID),
                                 new SqlParameter("@WatchDate",strDate),
                                 new SqlParameter("@WatchValue",strNum),
                                 new SqlParameter("@WatchTime",strTime),
                                 new SqlParameter("@WatchDateTime",Convert.ToDateTime(strDate).AddHours(Convert.ToInt32(strTime)))
                             };
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }


        public int DeleteData(string strID,  string strDate, string strTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" DELETE FROM WatchData WHERE WatchDate =@WatchDate AND EID=@EID AND WatchTime=@WatchTime ");
            SqlParameter[] p ={
                                 new SqlParameter("@EID",strID),
                                 new SqlParameter("@WatchDate",strDate),
                                 new SqlParameter("@WatchTime",strTime)
                                          };
           return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }












        public DataSet GetViewData(string strStart,string strEnd)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select e.ID,e.WatchNo,e.WatchPara,e.WatchUnit,e.WatchMax,e.WatchMin, ");
            sb.Append(" round(AVG(w.WatchValue),2)as AVGValue, ");
            sb.Append(" CASE WHEN MAX(w.WatchValue)> e.WatchMax THEN MAX(w.WatchValue) ELSE NULL END AS MAXOverValue, ");
            sb.Append(" CASE WHEN Min(w.WatchValue)< e.WatchMin THEN Min(w.WatchValue) ELSE NULL END AS MinOverValue, ");
            sb.Append(" (select count(WatchValue) from WatchData where (WatchValue>e.WatchMax or WatchValue<e.WatchMin) and EID=e.ID ");
            if (!string.IsNullOrEmpty(strStart))
            {
                sb.Append(" and WatchDate>= '" + strStart + "' ");
            }
            if (!string.IsNullOrEmpty(strEnd))
            {
                sb.Append(" and WatchDate<= '" + strEnd + "' ");
            }
            
            sb.Append(" ) as OutCount ");
            sb.Append(" FROM Equipment e left join WatchData w ");
            sb.Append(" ON e.ID=w.EID ");
         
            if (!string.IsNullOrEmpty(strStart))
            {
                sb.Append(" and w.WatchDate>= '" + strStart + "' ");
            }
            if (!string.IsNullOrEmpty(strEnd))
            {
                 sb.Append(" and w.WatchDate<= '" + strEnd + "' ");
            }


            sb.Append(" group by e.ID,e.WatchNo,e.WatchPara,e.WatchUnit,e.WatchMax,e.WatchMin ");


            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString());
        }


        public DataSet GetDailyChart(string strID,string strDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT e.ID,e.WatchNo,e.WatchUnit,e.WatchMax,e.WatchMin, ");
            sb.Append(" w.WatchDate,w.WatchValue,w.WatchTime ");
            sb.Append(" FROM Equipment e JOIN WatchData w ON e.ID=w.EID ");
            sb.Append(" WHERE e.ID=@ID AND w.WatchDate=@WatchDate AND w.WatchDateTime<GETDATE() ");
            sb.Append(" order by WatchDate, WatchTime ASC ");


            SqlParameter[] p ={
                                 new SqlParameter("@ID",strID),
                                 new SqlParameter("@WatchDate",strDate)
                             };
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);




        }
        public DataSet GetWeeklyChart(string strID, string strStart,string strEnd)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT e.ID,e.WatchNo,e.WatchUnit,e.WatchMax,e.WatchMin, ");
            sb.Append(" w.WatchDate,w.WatchValue,w.WatchTime, w.WatchDateTime  ");
            sb.Append(" FROM Equipment e JOIN WatchData w ON e.ID=w.EID ");
            sb.Append(" WHERE e.ID=@ID AND w.WatchDate BETWEEN @StartDate AND @EndDate");
            sb.Append(" order by WatchDate, WatchTime ASC ");


            SqlParameter[] p ={
                                 new SqlParameter("@ID",strID),
                                 new SqlParameter("@StartDate",strStart),
                                 new SqlParameter("@EndDate",strEnd)
                             };
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }


        public DataSet GetMonthlyChart(string strID, DateTime watchDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT e.ID,e.WatchNo,e.WatchUnit,e.WatchMax,e.WatchMin, ");
            sb.Append(" w.WatchDate,w.WatchValue,w.WatchTime, w.WatchDateTime ");
            sb.Append(" FROM Equipment e JOIN WatchData w ON e.ID=w.EID ");
            sb.Append(" WHERE e.ID=@ID AND YEAR(w.WatchDate)=@Year AND MONTH(w.WatchDate)=@Month ");
            sb.Append(" order by WatchDate, WatchTime ASC ");


            SqlParameter[] p ={
                                 new SqlParameter("@ID",strID),
                                 new SqlParameter("@Year",watchDate.Year.ToString()),
                                 new SqlParameter("@Month",watchDate.Month.ToString())
                             };
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }


        public DataSet GetYearChart(string strID, DateTime watchDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT e.ID,e.WatchNo,e.WatchUnit,e.WatchMax,e.WatchMin, ");
            sb.Append(" w.WatchDate,w.WatchValue,w.WatchTime, w.WatchDateTime ");
            sb.Append(" FROM Equipment e JOIN WatchData w ON e.ID=w.EID ");
            sb.Append(" WHERE e.ID=@ID AND YEAR(w.WatchDate)=@Year ");
            sb.Append(" order by WatchDate, WatchTime ASC ");


            SqlParameter[] p ={
                                 new SqlParameter("@ID",strID),
                                 new SqlParameter("@Year",watchDate.Year.ToString())
                             };
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sb.ToString(), p);
        }






        //SELECT e.ID,e.WatchNo,e.WatchUnit,e.WatchMax,e.WatchMin,
        //w.WatchDate,w.WatchValue,w.WatchTime
        //FROM Equipment e join WatchData w on e.ID=w.EID
        //where e.ID=@ID and w.WatchDate=@WatchDate


        //order by WatchDate, WatchTime ASC








        //select e.ID,e.WatchNo,e.WatchPara,e.WatchUnit,e.WatchMax,e.WatchMin,
        //AVG(w.WatchValue)as AVGValue,
        //CASE WHEN MAX(w.WatchValue)> e.WatchMax THEN MAX(w.WatchValue) ELSE NULL END AS MAXOverValue,
        //CASE WHEN Min(w.WatchValue)< e.WatchMin THEN Min(w.WatchValue) ELSE NULL END AS MinOverValue,
        //(select count(WatchValue) from WatchData where WatchValue>e.WatchMax or WatchValue<e.WatchMin and EID=e.ID) as OutCount


        //from Equipment e left join WatchData w 
        //on e.ID=w.EID and w.WatchDate between '2012-10-1' and '2012-10-31'


        //group by e.ID,e.WatchNo,e.WatchPara,e.WatchUnit,e.WatchMax,e.WatchMin


    }
}