public void WaterThisPeriodValue()
        {
            //DataTable dtStartDay = GetThisPeriodStartDay();
            //DateTime tmp_StartDate = Convert.ToDateTime(dtStartDay.Rows[0]["VariableValue"]);
            //string sStartDay = tmp_StartDate.ToString("yyyy-MM-dd HH:mm:ss");

            //DataTable dtEndDay = GetThisPeriodEndDay();
            //DateTime tmp_EndDate = Convert.ToDateTime(dtEndDay.Rows[0]["VariableValue"]);
            //string sEndDay = tmp_EndDate.ToString("yyyy-MM-dd HH:mm:ss");

            DataTable dtStartDay = GetThisPeriodStartDay();
            string sStartDay = dtStartDay.Rows[0]["VariableValue"].ToString() + " 00:00:00";
            DataTable dtEndDay = GetThisPeriodEndDay();
            string sEndDay = dtEndDay.Rows[0]["VariableValue"].ToString() + " 23:59:59";

            clsDBComm commDB = new clsDBComm();
            //DataTable DT = new DataTable();
            bool IsSuccess = false;

            string sSelect = "(select  CASE MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) when 0 then MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) else CAST(( (MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm)) ) AS decimal(18, 3)) END   AS CumulateValueAlarm from vwPowerMeter1HourLog where (1=1)and ERID != 4  and  (Timestamp) >= '";
            for (int ExeCount = 0; ExeCount < dt.Rows.Count; ExeCount++)
            {
                try
                {
                    string text = "UPDATE tblPowerMeter SET  ";
                    text += " SysThisPeriodWater  = " + sSelect + sStartDay + "' and (Timestamp) <= '" + sEndDay + "' and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                    text += " WHERE [ERID] = " + dt.Rows[ExeCount]["ERID"].ToString();

                    IsSuccess = commDB.ModifyDBData(text);
                }
                catch (Exception ex)
                {
                    var message = ex.Message + "," + ex.StackTrace;
                    throw ex;
                }
            }
        }
         //public void Exchange(string Memo, int ERID, string ABA, string Name, DateTime StartDate, DateTime EndDate)
         //{
         //    int RoleID=0;

         //    clsDBComm commDB = new clsDBComm();

         //    DataTable dt = new DataTable();
         //    DataTable dtSelect = new DataTable();
         //    string cmd = "";
         //    bool IsSuccess = false;


         //    string tempABA = "";
         //    tempABA = (Convert.ToUInt32(ABA)).ToString("0000000000");

         //    DateTime tmp_StartDate = StartDate;
         //    DateTime tmp_EndDate = EndDate;

         //    string s_StartDate = tmp_StartDate.ToShortDateString() + " 00:00:00";
         //    string s_EndDate = tmp_EndDate.ToShortDateString() + " 23:59:59";

         //    //先判斷磁卡是否已存在
         //    cmd = string.Format("select * from tblMagneticCard where ABA='{0}';", tempABA);
         //    dt = commDB.SelectDBData(cmd);
         //    if (dt.Rows.Count > 0)
         //    {
         //        RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());

         //        ////0330刪除已存在,再加的
         //        //cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0};", RoleID);
         //        //dtSelect = commDB.SelectDBData(cmd);
         //        //if (dtSelect.Rows.Count > 0)
         //        //{
         //        //    cmd = string.Format("delete from tblSysRoleAuthority where RoleID={0};", RoleID);
         //        //    IsSuccess = commDB.ModifyDBData(cmd);
         //        //}

         //        //查詢已加的權限不重複加入
         //        cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
         //        dt = commDB.SelectDBData(cmd);
         //        foreach (DataRow row in dt.Rows)
         //        {
         //            cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
         //            dtSelect = commDB.SelectDBData(cmd);
         //            if (dtSelect.Rows.Count == 0)
         //            {
         //                cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
         //                IsSuccess = commDB.ModifyDBData(cmd);

         //            }
         //        }
         //    }
         //    else
         //    {
         //        //1.取得磁卡ABA後,加入tblSysRole磁卡權限群組表
         //        cmd = string.Format("INSERT INTO tblSysRole(RoleName,UpdateDate) VALUES ('{0}','{1}');", tempABA, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
         //        IsSuccess = commDB.ModifyDBData(cmd);

         //        if (IsSuccess)
         //        {
         //            cmd = string.Format("select * from tblSysRole where RoleName='{0}';", tempABA);

         //            dt = commDB.SelectDBData(cmd);
         //            if (dt.Rows.Count > 0)
         //            {
         //                RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());
         //            }

         //            //2.取得磁卡對應的RoleID, 加入tblMagneticCard磁卡資料表
         //            cmd = string.Format("INSERT INTO tblMagneticCard(ABA,Name,StartDate,EndDate,Type,RoleID,Timestamp,Memo,NormalID) VALUES " +
         //                                "('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}',{8});",
         //                                 tempABA, Name, s_StartDate, s_EndDate, 2, RoleID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "施工單號:" + Memo, 0);
         //            IsSuccess = commDB.ModifyDBData(cmd);
         //            if (IsSuccess)
         //            {
         //                //3.取得此機房的各讀卡機的ControlID及磁卡對應的RoleID,加入tblSysRoleAuthority磁卡權限表,查詢已加的權限不重複加入       
         //                cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
         //                dt = commDB.SelectDBData(cmd);

         //                foreach (DataRow row in dt.Rows)
         //                {
         //                    cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
         //                    dtSelect = commDB.SelectDBData(cmd);
         //                    if (dtSelect.Rows.Count == 0)
         //                    {
         //                        cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
         //                        IsSuccess = commDB.ModifyDBData(cmd);
         //                    }
         //                }
         //            }
         //        }
         //    }
         //}

         public void Exchange(string MCNSID, int ERID, string ABA, string Name, DateTime StartDate, DateTime EndDate)
         {
             int RoleID = 0;
             clsDBComm commDB = new clsDBComm();

             DataTable dt = new DataTable();
             DataTable dtSelect = new DataTable();
             string cmd = "";
             bool IsSuccess = false;

             string tempABA = "";
             tempABA = (Convert.ToUInt32(ABA)).ToString("0000000000");

             DateTime tmp_StartDate = StartDate;
             DateTime tmp_EndDate = EndDate;

             string s_StartDate = tmp_StartDate.ToString("yyyy-MM-dd HH:mm:ss");
             string s_EndDate = tmp_EndDate.ToString("yyyy-MM-dd HH:mm:ss");

             //先判斷(1)磁卡是否已存在,(2)是否已啟用,(3)磁卡實際可進機房的時間是否在磁卡有效期限的範圍
             cmd = string.Format("select * from tblMagneticCard where ABA='{0}' and Enable='Y'and '{1}' between CardStartDate and CardEndDate and '{2}' between CardStartDate and CardEndDate;", tempABA, s_StartDate, s_EndDate);
             dt = commDB.SelectDBData(cmd);
             if (dt.Rows.Count > 0)
             {
                 //已存在,先更新-可進入機房時間起迄
                 cmd = string.Format("UPDATE tblMagneticCard set StartDate ='{0}',EndDate='{1}',Type=2,MCNSID='{2}',NormalID=0,Timestamp='{3}' where ABA='{4}';", s_StartDate, s_EndDate, MCNSID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), tempABA);
                 IsSuccess = commDB.ModifyDBData(cmd);

                 RoleID = int.Parse(dt.Rows[0]["RoleID"].ToString());

                 //查詢已加的權限不重複加入
                 cmd = string.Format("select * from vwEntranceGuardDetail where ERID={0};", ERID);
                 dt = commDB.SelectDBData(cmd);
                 foreach (DataRow row in dt.Rows)
                 {
                     cmd = string.Format("select * from tblSysRoleAuthority where RoleID={0} and ControlID='{1}';", RoleID, row["ControlID"].ToString());
                     dtSelect = commDB.SelectDBData(cmd);
                     if (dtSelect.Rows.Count == 0)
                     {
                         cmd = string.Format("INSERT INTO tblSysRoleAuthority(RoleID,ControlID) VALUES ({0},'{1}');", RoleID, row["ControlID"].ToString());
                         IsSuccess = commDB.ModifyDBData(cmd);

                     }
                 }
                 //寫入CardCommandLog,ABA,*,C
                 cmd = string.Format("INSERT INTO [SecureDB].[dbo].[tblCardCommandLog]([ABA],[ControlID],[CommandType],[CardType])VALUES('{0}','*','*','C')", tempABA);
                 IsSuccess = commDB.ModifyDBData(cmd);

             }
             else
             {
                 throw new System.Exception("(1)磁卡不存在(2)磁卡未啟用(3)磁卡實際可進機房的時間超過磁卡有效期限的範圍");
             }
         }
        public void WaterBaseValueDailyAvg(string s_year)
        {
            clsDBComm commDB = new clsDBComm();
            DataTable dtYear = new DataTable();
            bool IsSuccess = false;
            string cmd = "";

            //YearData();
            string sSelect = "(select  CASE MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) when 0 then MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm) else CAST(( (MAX(CumulateValueAlarm) - MIN(CumulateValueAlarm))/30 ) AS decimal(18, 3)) END   AS CumulateValueAlarm from vwPowerMeter1HourLog where (1=1)and ERID != 4 and year(Timestamp)= ";
            for (int ExeCount = 0; ExeCount < dt.Rows.Count; ExeCount++)
            {
                //先判別是否為新的年份
                cmd = string.Format("select * from tblPowerAndWaterMeterYearMonthDailyAvg where YearsNo='{0}' and ERID={1};", s_year, dt.Rows[ExeCount]["ERID"].ToString());
                dtYear = commDB.SelectDBData(cmd);
                if (dtYear.Rows.Count > 0)
                {
                    try
                    {
                        string text = "UPDATE tblPowerAndWaterMeterYearMonthDailyAvg SET  ";
                        text += " Water1MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=1 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water2MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=2 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water3MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=3 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water4MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=4 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water5MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=5 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water6MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=6 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water7MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=7 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water8MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=8 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water9MonthDailyAvg  = " + sSelect + s_year + " and month(Timestamp)=9 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water10MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=10 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water11MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=11 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += ",Water12MonthDailyAvg = " + sSelect + s_year + " and month(Timestamp)=12 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";

                        text += ",[WaterUpdateDate] = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        text += ",[Memo] = 'SYS' ";
                        text += " WHERE [YearsNo] = " + s_year + " and [ERID] = " + dt.Rows[ExeCount]["ERID"].ToString();

                        IsSuccess = commDB.ModifyDBData(text);
                    }
                    catch (Exception ex)
                    {
                        var message = ex.Message + "," + ex.StackTrace;
                        throw ex;
                    }
                }
                else
                {
                    try
                    {
                        string text = "INSERT INTO tblPowerAndWaterMeterYearMonthDailyAvg (YearsNo,ERID,Water1MonthDailyAvg,Water2MonthDailyAvg,Water3MonthDailyAvg,Water4MonthDailyAvg,Water5MonthDailyAvg,Water6MonthDailyAvg,Water7MonthDailyAvg,Water8MonthDailyAvg,Water9MonthDailyAvg,Water10MonthDailyAvg,Water11MonthDailyAvg,Water12MonthDailyAvg,WaterUpdateDate,Memo)VALUES  ";

                        text += "(" + s_year + "," + dt.Rows[ExeCount]["ERID"].ToString();
                        text += "," + sSelect + s_year + " and month(Timestamp)=1 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=2 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=3 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=4 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=5 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=6 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=7 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=8 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=9 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=10 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=11 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";
                        text += "," + sSelect + s_year + " and month(Timestamp)=12 and ERID=" + dt.Rows[ExeCount]["ERID"].ToString() + " GROUP BY ERID,ERName )";

                        text += ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        text += ",'SYS') ";


                        IsSuccess = commDB.ModifyDBData(text);
                    }
                    catch (Exception ex)
                    {
                        var message = ex.Message + "," + ex.StackTrace;
                        throw ex;
                    }
                }


            }
        }