using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Bowin;
using Bowin.Web;
using Bowin.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Data.OleDb;

public partial class EroPost : System.Web.UI.Page {

  public sysUtils sys;
  string sql, sql1, sql2b, Dx;
  DataTable DT1, DT2, DT3, DT4, DT, DT1a, DT2b;

  protected void Page_Load(object sender, EventArgs e) {
    sys = new sysUtils(Page, true);
    sql = "select * from TELERROR where STATE=0 order by TEDATE";
    DT1 = bwDBUtility.bwGetDataTable(sys.connTelErr, sql, "DT1");
    for (int j = 0; j < DT1.Rows.Count; j++) {
	  // Response.Write(DT1.Rows[j]["TEDATE"].ToString());
	  // Response.Write("<br/>");
      if (DT1.Rows[j]["CAREA"].ToString() != "*") {
        sql = "select * from streetLight where SLSN='" + DT1.Rows[j]["SLSN"].ToString() + "'  and RECSTATE=0 and SLKIND=0 and CAREA=N'" + DT1.Rows[j]["CAREA"].ToString() + "'";
      } else {
        sql = "select * from streetLight where SLSN='" + DT1.Rows[j]["SLSN"].ToString() + "' and RECSTATE=0 and SLKIND=0 and LEN(SLSN)=6";
      }
      DT2 = bwDBUtility.bwGetDataTable(sys.conn, sql, "DT2");
      if (DT2.Rows.Count > 0) {
        SqlTransaction trans = sys.conn.BeginTransaction();
        SqlCommand cmd = new SqlCommand();
        SqlCommand cmd1 = new SqlCommand();
        cmd.Connection = sys.conn;
        cmd.Transaction = trans;
        cmd1.Connection = sys.conn;
        cmd1.Transaction = trans;
        Boolean Ispt = false;
        sys.Asp.SetParam("SLID_", DT2.Rows[0]["SLID"].ToString());
        DateTime G = Convert.ToDateTime(DT1.Rows[j]["TEDATE"]);
        string CD_BREAKDOWN = GetBreakdown(DT1.Rows[j]["CD_BREAKDOWN"].ToString());



        Boolean isDup = false;
        String ERRORID_2b = null;
        SqlCommand cmd2b = new SqlCommand();
        cmd2b.Connection = sys.conn;
        cmd2b.Transaction = trans;
        sql2b = @"
            select distinct top 1 [e].[ERRORID]
            from [ERROR] as [e]
            left join [STREETLIGHT] as [s] on [e].[SLID] = [s].[SLID]
            left join [EORDER] as [ee] on [e].[EORDERID] = [ee].[EORDERID] and [ee].[RECSTATE] = 0
            where [s].[SLSN] = @SLSN and not ([e].[ERSTATE] = 5 or ([e].[ERSTATE] = 2 and [ee].[EORDERID] is null) or ([e].[ERSTATE] in (1, 2) and [ee].[ORDFINISH] = 1 and [ee].[RECSTATE] = 0)) and [s].[RECSTATE] = 0 and [e].[RECSTATE] = 0
        ";

        /*
        DataTable DT2b = bwDBUtility.bwGetDataTable(sys.conn, trans, sql2b.Replace("@SLSN", DT2.Rows[0]["SLSN"].ToString()), "DT2b");
        if (DT2b.Rows.Count > 0) {
            ERRORID_2b = DT2b.Rows[0]["ERRORID"].ToString();    // Convert.ToString(DT2b.Rows[0]["ERRORID"])
        }
        */

        // cmd2b.Parameters.Add("@SLID", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(DT2.Rows[0]["SLID"].ToString());
        cmd2b.Parameters.Add("@SLSN", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["SLSN"].ToString();
        cmd2b.CommandText = sql2b;
        Object obj2b = cmd2b.ExecuteScalar();
        if (obj2b != null) {
            ERRORID_2b = obj2b.ToString();    // Convert.ToString(obj2b)
        }

        if (ERRORID_2b != null) {
            isDup = true;
        }


        sql = "Select MAX(ERRSN) as ERSN, COUNT(ERRSN) as CON From ERROR where ERRSN LIKE '" + Convert.ToString(G.Year - 1911) + "%'";

        DT3 = bwDBUtility.bwGetDataTable(sys.conn, trans, sql, "DT3");
        Application.Lock();
        if (DT3.Rows[0]["CON"].ToString() == "0") {
          Application["ERSN"] = (G.Year - 1911) * 1000000;
        } else {
          Application["ERSN"] = Convert.ToInt64(DT3.Rows[0]["ERSN"]);
        }
        Application["ERSN"] = Convert.ToInt64(Application["ERSN"]) + 1;
        String ERSN = Convert.ToString(Application["ERSN"]);
        Application.UnLock();
        sys.Asp.SetParam("ERRSN_", ERSN);
        sql = "Insert into ERROR (SLSNTEMP,SLID,CD_BREAKDOWN,ERRDATE,ERRLDATE,ERRSN,ERRPLACE,PROPOSERNAME,PROPOSERTEL,RTEL,REMAIL,AREAID,X,Y,STREET,VILLAGE,ERRTYPE,EORDERID,ERRKIND,ISPROTECT,PHONELANG,UPDDATE,BREAKDOWN_DESC,ERRPFI,CAREA,OSLID,OPLACE,ERSTATE,ERRCANCELDESC,IS_AI_LAMP) " +
                          " values (@SLSNTEMP,@SLID,@CD_BREAKDOWN,@ERRDATE,@ERRLDATE,@ERRSN,@ERRPLACE,@PROPOSERNAME,@PROPOSERTEL,@RTEL,@REMAIL,@AREAID,@X,@Y,@STREET,@VILLAGE,@ERRTYPE,@EORDERID,@ERRKIND,@ISPROTECT,@PHONELANG,@UPDDATE,@BREAKDOWN_DESC,@ERRPFI,@CAREA,@OSLID,@OPLACE,@ERSTATE,@ERRCANCELDESC,@IS_AI_LAMP);SELECT @@IDENTITY AS NewID;";

        cmd.Parameters.Add("@SLID", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(DT2.Rows[0]["SLID"].ToString());
        cmd.Parameters.Add("@IS_AI_LAMP", SqlDbType.TinyInt, 0).Value = Convert.ToBoolean(DT2.Rows[0]["IS_AI_CONTROL"]) == true ? 1 : 0;
        cmd.Parameters.Add("@OSLID", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(DT2.Rows[0]["SLID"].ToString());

        cmd.Parameters.Add("@CAREA", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["CAREA"].ToString();

        cmd.Parameters.Add("@AREAID", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["AREAID"].ToString();
        cmd.Parameters.Add("@STREET", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["CD_STREET"].ToString();
        cmd.Parameters.Add("@VILLAGE", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["CD_VILLAGE"].ToString();
        cmd.Parameters.Add("@ERRPLACE", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["SLADD"].ToString();
        cmd.Parameters.Add("@OPLACE", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["SLADD"].ToString();

        cmd.Parameters.Add("@CD_BREAKDOWN", SqlDbType.NVarChar, 50).Value = CD_BREAKDOWN;
        cmd.Parameters.Add("@X", SqlDbType.Float, 0).Value = bwDBUtility.strToDBFloat(DT2.Rows[0]["TWD97X"].ToString());
        cmd.Parameters.Add("@Y", SqlDbType.Float, 0).Value = bwDBUtility.strToDBFloat(DT2.Rows[0]["TWD97Y"].ToString());
        cmd.Parameters.Add("@ERRSN ", SqlDbType.NVarChar, 20).Value = ERSN;
        cmd.Parameters.Add("@PROPOSERNAME", SqlDbType.NVarChar, 20).Value = "IVR";
        cmd.Parameters.Add("@PROPOSERTEL", SqlDbType.NVarChar, 20).Value = DT1.Rows[j]["PHONE"].ToString();
        cmd.Parameters.Add("@RTEL", SqlDbType.SmallInt, 0).Value = 1;
        cmd.Parameters.Add("@ERRKIND", SqlDbType.SmallInt, 0).Value = 1;
        cmd.Parameters.Add("@ERRTYPE", SqlDbType.SmallInt, 0).Value = 1;
        cmd.Parameters.Add("@REMAIL", SqlDbType.SmallInt, 0).Value = 0;
        cmd.Parameters.Add("@ERRDATE", SqlDbType.DateTime, 0).Value = G;
        DateTime G1;
        G1 = G;
        if (CD_BREAKDOWN == "全天亮") {
          G1 = G1.AddHours(4);
        } else {
          G1 = G1.AddDays(1);
        }
        cmd.Parameters.Add("@ERRLDATE", SqlDbType.DateTime, 0).Value = G1;
        cmd.Parameters.Add("@UPDDATE", SqlDbType.DateTime, 0).Value = G;
        cmd.Parameters.Add("@PHONELANG", SqlDbType.NVarChar, 3).Value = "";
        cmd.Parameters.Add("@EORDERID", SqlDbType.Int, 0).Value = -1;
        if (DT2.Rows[0]["SLTYPE"].ToString() == "1") {
          if (DT2.Rows[0]["SLEDATE"].ToString() != "") {
            DateTime SSDate = Convert.ToDateTime(DT2.Rows[0]["SLEDATE"]);
            if (SSDate.CompareTo(DateTime.Today) >= 0) {
              Ispt = true;
            }

          }
          if (Ispt) {
            cmd.Parameters.Add("@ISPROTECT", SqlDbType.SmallInt, 0).Value = 1;
          } else {
            cmd.Parameters.Add("@ISPROTECT", SqlDbType.SmallInt, 0).Value = 0;
          }
          cmd.Parameters.Add("@ERRPFI", SqlDbType.SmallInt, 0).Value = 0;
        } else {
          cmd.Parameters.Add("@ISPROTECT", SqlDbType.SmallInt, 0).Value = 0;
          cmd.Parameters.Add("@ERRPFI", SqlDbType.SmallInt, 0).Value = 1;
        }
        cmd.Parameters.Add("@SLSNTEMP", SqlDbType.NVarChar, 30).Value = "";
        cmd.Parameters.Add("@SLSN", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["SLSN"].ToString();

        cmd.Parameters.Add("@BREAKDOWN_DESC", SqlDbType.NVarChar, 100).Value = "";

        if (isDup) {
            // cmd.Parameters["@ERSTATE"].Value = 5;
            cmd.Parameters.Add("@ERSTATE", SqlDbType.SmallInt, 0).Value = 5;
            cmd.Parameters.Add("@ERRCANCELDESC", SqlDbType.NVarChar, 200).Value = "重覆報修";
        } else {
            cmd.Parameters.Add("@ERSTATE", SqlDbType.SmallInt, 0).Value = 0;
            cmd.Parameters.Add("@ERRCANCELDESC", SqlDbType.NVarChar, 200).Value = DBNull.Value;
        }

        cmd.CommandText = sql;
        ArrayList DataSetNew, DataSetOld;
        DataTable dtLog;
        int LogOperate;

        dtLog = bwDBUtility.bwGetDataTable(sys.conn, trans, "select * from ERROR where ERRORID=-99999999", "ERROR");
        DataSetOld = bwDBUtility.GetRecordValues(dtLog);
        LogOperate = 401;

        String ERRORID = cmd.ExecuteScalar().ToString();
        dtLog = bwDBUtility.bwGetDataTable(sys.conn, trans, "select * from ERROR where ERRORID=" + ERRORID, "ERROR1");
        DataSetNew = bwDBUtility.GetRecordValues(dtLog);
        int AUID = bwDBUtility.AddLogUser(sys.conn, trans, 0, LogOperate, Request.ServerVariables.Get("REMOTE_ADDR"));
        bwDBUtility.AddLogDB(sys.conn, trans, AUID, int.Parse(ERRORID), "ERROR", "ERRORID", bwDBUtility.ArrayListToB64Str(DataSetOld), bwDBUtility.ArrayListToB64Str(DataSetNew));
        sys.AddErrLog(sys.conn, trans, ERRORID, "故障報修", G, "");
        String CID = "-1";
        String PID = "-1";
        if (DT2.Rows[0]["SLTYPE"].ToString() == "1") {

          int intWorkDays;

          string sql4 = "select a.* from PRJAREA a,PROJECT b where a.PRJID=b.PRJID and a.Recstate=0 and b.RECSTATE=0 and a.FIXAREA='" + DT2.Rows[0]["FIXAREA"].ToString() + "' and b.PRJSDATE<='" + G.ToShortDateString() + "' and b.PRJEDATE>='" + G.ToShortDateString() + "' and b.CAREA='" + DT2.Rows[0]["CAREA"].ToString() + "'";
          DT4 = bwDBUtility.bwGetDataTable(sys.conn, trans, sql4, "DT4");
          if (DT4.Rows.Count > 0) {
            PID = DT4.Rows[0]["PRJID"].ToString();
            CID = DT4.Rows[0]["COMPID"].ToString();
          }
          sys.Asp.SetParam("PRJID_", PID);
          sys.Asp.SetParam("COMPID_", CID);
          if ((sys.Asp.GetParam("PRJID_") != "-1") && (sys.Asp.GetParam("COMPID_") != "-1") && (!Ispt)) {
            sql1 = "select WORKDAYS from PROJECT where PRJID=" + sys.Asp.GetParam("PRJID_");
            DT3 = bwDBUtility.bwGetDataTable(sys.conn, trans, sql1, "DT3");
            intWorkDays = Convert.ToInt32(DT3.Rows[0]["WORKDAYS"]);
            //G = new DateTime(G.Year, G.Month, G.Day);

            DateTime GX = sysUtils.bwAddWorkDate(G, intWorkDays);
            DateTime GB = G;
            DateTime GA = GX;
            Double x = 0, y = 0;
            do {
              sql1 = "select * from plans where WDate>'" + G.ToShortDateString() + "' and WDate <='" + GX.ToShortDateString() + "' and CAREA='" + DT2.Rows[0]["CAREA"].ToString() + "'";
              DT3 = bwDBUtility.bwGetDataTable(sys.conn, trans, sql1, "DT3");
              x = 0;
              for (Int32 i = 0; i < DT3.Rows.Count; i++) {
                if ((DT3.Rows[i]["s_weather"].ToString() == "3") || (DT3.Rows[i]["s_weather"].ToString() == "4") || (DT3.Rows[i]["s_weather"].ToString() == "5") || (DT3.Rows[i]["s_weather"].ToString() == "6")) {
                  x = x + 1;
                }

              }
              y = y + x;

              if (x != 0) {
                G = GX;
                GX = sysUtils.bwAddWorkDate(G, Convert.ToInt32(x));
              }
            } while (x != 0);

            //y = Math.Round(y + 0.1);
            if (y != 0) {
              GX = sysUtils.bwAddWorkDate(GA, Convert.ToInt32(y));
            }
            cmd1.Parameters.Add("@ORDYEAR ", SqlDbType.Int, 0).Value = Convert.ToString(G.Year - 1911);
            cmd1.Parameters.Add("@ORDFINISH ", SqlDbType.Int, 0).Value = 0;
            cmd1.Parameters.Add("@ORDPRESSSN ", SqlDbType.Int, 0).Value = 0;
            cmd1.Parameters.Add("@WORKDAYS ", SqlDbType.Int, 0).Value = Convert.ToString(intWorkDays);
            cmd1.Parameters.Add("@ORDODATE ", SqlDbType.DateTime, 0).Value = GB;
            //mailMessage = mailMessage + "派工日期:" + sysUtils.DateTimeTw(GB) + "\n";
            Dx = sysUtils.DateTimeTw(GB);
            cmd1.Parameters.Add("@ORDLDATE ", SqlDbType.DateTime, 0).Value = GX;
            cmd1.Parameters.Add("@ERRORID ", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(ERRORID);
            cmd1.Parameters.Add("@SLID ", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(sys.Asp.GetParam("SLID_"));
            cmd1.Parameters.Add("@ORDERSN ", SqlDbType.NVarChar, 20).Value = ERSN;
            cmd1.Parameters.Add("@SLSNTEMP", SqlDbType.NVarChar, 30).Value = "";
            cmd1.Parameters.Add("@PRJID ", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(sys.Asp.GetParam("PRJID_"));
            cmd1.Parameters.Add("@COMPID ", SqlDbType.Int, 0).Value = bwDBUtility.strToDBInt(sys.Asp.GetParam("COMPID_"));
            cmd1.Parameters.Add("@CAREA", SqlDbType.NVarChar, 50).Value = DT2.Rows[0]["CAREA"].ToString();

            string sqli2 = "Insert into EORDER (ORDYEAR,ORDFINISH,ORDPRESSSN,WORKDAYS,ORDODATE,ORDLDATE,ERRORID,ORDERSN,PRJID,COMPID,SLID,SLSNTEMP,CAREA) " +
                  " values (@ORDYEAR,@ORDFINISH,@ORDPRESSSN,@WORKDAYS,@ORDODATE,@ORDLDATE,@ERRORID,@ORDERSN,@PRJID,@COMPID,@SLID,@SLSNTEMP,@CAREA);SELECT @@IDENTITY AS NewID;";
            cmd1.CommandText = sqli2;
            String EORDERID = cmd1.ExecuteScalar().ToString();

            dtLog = bwDBUtility.bwGetDataTable(sys.conn, trans, "select * from EORDER where EORDERID=-99999999", "EORDER");
            DataSetOld = bwDBUtility.GetRecordValues(dtLog);
            LogOperate = 411;


            dtLog = bwDBUtility.bwGetDataTable(sys.conn, trans, "select * from EORDER where EORDERID=" + EORDERID, "EORDER1");
            DataSetNew = bwDBUtility.GetRecordValues(dtLog);
            AUID = bwDBUtility.AddLogUser(sys.conn, trans, 0, LogOperate, Request.ServerVariables.Get("REMOTE_ADDR"));
            bwDBUtility.AddLogDB(sys.conn, trans, AUID, int.Parse(EORDERID), "EORDER", "EORDERID", bwDBUtility.ArrayListToB64Str(DataSetOld), bwDBUtility.ArrayListToB64Str(DataSetNew));


            sys.Asp.SetParam("ERSTATE_", "1");
            sql1 = "UPDATE ERROR SET ERSTATE=1,EORDERID=" + EORDERID + ",ERRLDATE='" + sysUtils.DTToSqlDateTime(GX) + "' where ERRORID=" + ERRORID;
            bwDBUtility.bwExecSql(sys.conn, trans, sql1);


          }
        }

        if ((sys.Asp.GetParam("SLID_") != "-1") && (PID != "-1") && (CID != "-1") && (!Ispt)) {
          sql1 = "update STREETLIGHT set STATE=2 where SLID=" + sys.Asp.GetParam("SLID_");
        } else {
          sql1 = "update STREETLIGHT set STATE=1 where SLID=" + sys.Asp.GetParam("SLID_");
        }
        bwDBUtility.bwExecSql(sys.conn, trans, sql1);

		if (isDup) {
			bwDBUtility.bwExecSql(sys.connTelErr, "UPDATE TELERROR set STATE=3 where TEID=" + DT1.Rows[j]["TEID"].ToString());
        } else {
			bwDBUtility.bwExecSql(sys.connTelErr, "UPDATE TELERROR set STATE=1 where TEID=" + DT1.Rows[j]["TEID"].ToString());
		}
        trans.Commit();
      } else {
        bwDBUtility.bwExecSql(sys.connTelErr, "UPDATE TELERROR set STATE=2 where TEID=" + DT1.Rows[j]["TEID"].ToString());
      }
    }

    return;

    /*
    string ServerName = "api.hiair.hinet.net";
    string smsPort = "8000";
    string smsUser = "89936207";
    string smsPass = "7256d159";
    sql = "select * from ERRTEL where PHONESTATE=0 and PHONETIMES<3";
    DT1 = bwDBUtility.bwGetDataTable(sys.conn, sql, "DT1");
    for (int j = 0; j < DT1.Rows.Count; j++) {
      try {
        HiAirLib.HiNetSMS hnSMS = new HiAirLib.HiNetSMS();
        int ret_code = hnSMS.StartCon(ServerName, smsPort, smsUser, smsPass);
        if (ret_code == 0) {
          string ESLSN = DT1.Rows[j]["SLSN"].ToString();
          if (ESLSN == "") {
            ESLSN = "不知道編號";
          }
          string DTIME = "";
          if (DT1.Rows[j]["SBDATE"].ToString() != "") {
            DTIME = "於" + Convert.ToDateTime(DT1.Rows[j]["SBDATE"]).ToString("yyyy/MM/dd HH:mm");
          }
          string MSG = "";
          if (DT1.Rows[j]["FSTATE"].ToString() == "1") {
            MSG = "路燈編號" + ESLSN + "經查修,須轉派相關單位繼續處理中,詳情請上網 http://streetlight.ntpc.gov.tw/SLNTPC/ErrorListMain.aspx 查詢,謝謝您的通報!";
          } else if (DT1.Rows[j]["FSTATE"].ToString() == "5") {
            MSG = "路燈編號" + ESLSN + "報修已取消!取消原因:" + DT1.Rows[j]["CANCELDESC"].ToString();
          } else {
            MSG = "路燈編號:" + ESLSN + "已" + DTIME + "維修完成!謝謝您的通報!";
          }
          ret_code = hnSMS.SendMsg(DT1.Rows[j]["ERRPHONE"].ToString(), MSG);
          if (ret_code == 0) {
            bwDBUtility.bwExecSql(sys.conn, "UPDATE ERRTEL set PHONESTATE=1,LASTTIME=getdate() where ERRID=" + DT1.Rows[j]["ERRID"].ToString());
          } else {
            bwDBUtility.bwExecSql(sys.conn, "UPDATE ERRTEL set PHONETIMES=PHONETIMES+1,LASTTIME=getdate() where ERRID=" + DT1.Rows[j]["ERRID"].ToString());
          }
        }
        hnSMS.EndCon();
        hnSMS = null;
      } catch (Exception ex) {
        Response.Write(ex.ToString());
      }
    }
    */

  }
  public static string GetBreakdown(string strState) {
    if (strState == "01") {
      return "路燈不亮";
    } else if (strState == "02") {
      return "全天亮";
    } else if (strState == "03") {
      return "時亮時不亮";
    } else if (strState == "04") {
      return "整排不亮";
    } else if (strState == "05") {
      return "漏電";
    } else if (strState == "06") {
      return "傾斜或折斷";
    } else {
      return "其他";
    }
  }



}