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);
        // 讀取語音報修未處理資料(STATE=0)
        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]["SLSN"].ToString() + "<br/>");
            // Response.Write(DT1.Rows[j]["TEDATE"].ToString());

            // 判斷路燈資料是否存在
            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");
            // Response.Write(DT2.Rows.Count.ToString() + "<br/>");
            if (DT2.Rows.Count > 0) { // 路燈資料存在
                SqlTransaction trans = sys.conn.BeginTransaction();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sys.conn;
                cmd.Transaction = trans;

                SqlCommand cmd1 = new SqlCommand();
                cmd1.Connection = sys.conn;
                cmd1.Transaction = trans;

                SqlCommand cmd2b = new SqlCommand();
                cmd2b.Connection = sys.conn;
                cmd2b.Transaction = trans;

                Boolean Ispt = false;
                Boolean isDup = false;
                String ERRORID_2b = null;

                // SLID
                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());

                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;
                // Response.Write(DT2.Rows[0]["IS_AI_CONTROL"].ToString());
                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 "其他";
        }
    }

}