using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.SceneManagement;
using UnityEngine.UI;
using UnityEngine.Networking;
using UnityEngine.EventSystems;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Text;
using TMPro;
using System;
using System.IO;
using System.Net;
using System.Data;
using SimpleJSON;
using System.Linq;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Globalization;
using static System.Net.WebRequestMethods;


public class Main : MonoBehaviour
{
    [SerializeField] GameObject 面板1;
    [SerializeField] GameObject 面板2;
    [SerializeField] GameObject 讀取面板;
    [SerializeField] TextMeshProUGUI 讀取;
    [SerializeField] GameObject 股東帳1;
    [SerializeField] Toggle 股東帳;
    [SerializeField] TextMeshProUGUI 天;
    [SerializeField] TextMeshProUGUI 周;
    [SerializeField] TextMeshProUGUI 月;
    [SerializeField] TextMeshProUGUI 天1;
    [SerializeField] TextMeshProUGUI 周1;
    [SerializeField] TextMeshProUGUI 月1;

    public GameObject buttonPrefab;
    public ScrollRect buttonscroll;
    private List<GameObject> copy = new List<GameObject>();

    public GameObject buttonPrefab1;
    public ScrollRect buttonscroll1;
    private List<GameObject> copy1 = new List<GameObject>();

    public CanvasScaler canvasScaler;

    private float timer = 0f;
    private float interval = 60f;

    float screenWidth;
    float screenHeight;

    private string loadingText = "";
    private int dotCount = 0;

    string 選擇日期;
    string 本周一;
    string 月初;
    string 年初;
    string 開始日期;
    string 結束日期;
    string PA2;
    JSONArray 彙總1;
    JSONNode 彙總2;
    JSONNode 彙總3;
    JSONNode 彙總4;
    JSONNode 彙總5;
    JSONNode 彙總6;
    JSONNode 彙總7;

    string 路徑1;
    // Start is called before the first frame update
    void Start()
    {
        Screen.autorotateToPortrait = false;
        Screen.orientation = ScreenOrientation.Portrait;
        screenWidth = Screen.width;
        screenHeight = Screen.height;
        float rate = 900.0f / 1900.0f;
        if (canvasScaler == null)
        {
            canvasScaler = GetComponent<CanvasScaler>();
        }
        Main.Global.rate = screenWidth/screenHeight;
        if (Main.Global.rate>rate)
        {
            canvasScaler.matchWidthOrHeight = 1f;
        }
        else
        {
            canvasScaler.matchWidthOrHeight = 0f;
        }

        StartCoroutine(AnimateText());
        讀取面板.SetActive(true);
        面板1.SetActive(true);
        面板2.SetActive(false);
        if (Main.Global.股東)
        {
            股東帳1.SetActive(true);
        }
        else 
        {
            股東帳1.SetActive(false);
        }
        AdManager.Instance.ShowInterstitialAd();
        StartCoroutine(讀取IP());
    }
    private void 輸入日期(string date)
    {
        DateTime today = DateTime.ParseExact(date, "yyyy/MM/dd", null);
        DateTime yearStart = new DateTime(today.Year, 1, 1);
        DateTime monthStart = new DateTime(today.Year, today.Month, 1);
        int daysUntilMonday = ((int)DayOfWeek.Monday - (int)today.DayOfWeek + 7) % 7;
        if (daysUntilMonday == 0)
        {
            daysUntilMonday = 7; // 如果差距為 0，則禮拜一仍在本周
        }
        DateTime monday = today.AddDays(daysUntilMonday-7);

        選擇日期 = today.ToString("yyyy/MM/dd");
        年初 = yearStart.ToString("yyyy/MM/dd");
        月初 = monthStart.ToString("yyyy/MM/dd");
        本周一= monday.ToString("yyyy/MM/dd");
    }
    IEnumerator AnimateText()
    {
        string qqq = 讀取.text.ToString();
        while (true)
        {
            yield return new WaitForSeconds(0.5f);

            if (dotCount < 3)
            {
                loadingText += ".";
                dotCount++;
            }
            else
            {
                loadingText = "";
                dotCount = 0;
            }

            讀取.text = qqq+loadingText;
        }
    }

    IEnumerator 讀取IP()
    {
        string strcon = "讀取IP";
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", strcon);
        Debug.Log(strUrl);
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = request.downloadHandler.text.ToString();
        using (SqlConnection connection = new SqlConnection(Main.Global.路徑))
        {
            try
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(query, connection))
                {

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string pa = reader.GetString("資料庫");
                            string pa1 = reader.GetString("IP位置");
                            string pa2 = reader.GetString("帳號");
                            string pa3 = reader.GetString("密碼");
                            路徑1 = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", pa1, pa, pa2, pa3);
                            Debug.Log(路徑1);
                            StartCoroutine(日期清單());
                        }
                        
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
    IEnumerator 日期清單()
    {
        string strcon = "日期清單";
        string strUrl = string.Format(Main.Global.阿帕契路徑+ "comm={0}", strcon);
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = request.downloadHandler.text.ToString();
        Debug.Log(query);
        Debug.Log(路徑1);
        JSONArray json = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            json.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    int sum = 0;
                    for (int i = 0; i < json.Count; i++)
                    {
                        GameObject buttonObj = Instantiate(buttonPrefab) as GameObject;
                        buttonObj.GetComponent<RectTransform>().anchoredPosition = new Vector2(3, -sum);
                        Transform tran = buttonscroll.content;
                        buttonObj.transform.SetParent(tran, false);
                        TextMeshProUGUI[] texts = buttonObj.GetComponentsInChildren<TextMeshProUGUI>();
                        texts[0].text = json[i][1].ToString().Trim('"');
                        texts[1].text = json[i][2].ToString().Trim('"');
                        Button bt = buttonObj.GetComponentInChildren<Button>();
                        int index = i;
                        bt.onClick.AddListener(() =>
                        {
                            if (股東帳.isOn == true)
                            {
                                PA2 = "And (後台商客基本資料表.自訂 = 1)";
                            }
                            else
                            {
                                PA2 = "";
                            }
                            開始日期 = json[index][1].ToString().Trim('"');
                            結束日期 = json[index][2].ToString().Trim('"');
                            DateTime parsedDate = DateTime.ParseExact(開始日期, "yyyy-MM-dd", null);
                            string formattedDate = parsedDate.ToString("yyyy/MM/dd");
                            輸入日期(formattedDate);
                            面板1.SetActive(false);
                            面板2.SetActive(true);
                            天.text = 選擇日期;
                            周.text = 本周一;
                            月.text = 月初;
                            讀取面板.SetActive(true);
                            StartCoroutine(後台彙總讀取2());
                        });
                        sum += 100;
                        copy.Add(buttonObj);
                    }
                    RectTransform contentTransform = buttonscroll.content;
                    contentTransform.anchoredPosition = new Vector2(contentTransform.anchoredPosition.x, 0);
                    contentTransform.sizeDelta = new Vector2(contentTransform.sizeDelta.x, sum);
                    if (sum > 1652)
                    {
                        buttonscroll.vertical = true;
                    }
                    else
                    {
                        buttonscroll.vertical = false;
                    }
                    讀取面板.SetActive(false);
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
    }

    IEnumerator 後台彙總讀取2()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取2");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = string.Format(request.downloadHandler.text.ToString(), 開始日期, 結束日期, PA2);
        Debug.Log(query);
        彙總1 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總1.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    for (int i = 0; i < 彙總1.Count; i++)
                    {
                        if (彙總1[i][14] == null || 彙總1[i][14].ToString() == "")
                        {
                            彙總1[i][14] = "0";
                        }
                        彙總1[i][11] = 彙總1[i][13].ToString().Trim('"');
                    }
                    StartCoroutine(後台彙總讀取2_1());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
    }
    IEnumerator 後台彙總讀取2_1()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取2");
        DateTime parsedDate = DateTime.ParseExact(本周一, "yyyy/MM/dd", null);
        string formattedDate = parsedDate.ToString("yyyy-MM-dd");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = string.Format(request.downloadHandler.text.ToString(), formattedDate, 結束日期, PA2);
        Debug.Log(query);
        彙總2 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總2.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    for (int i = 0; i < 彙總2.Count; i++)
                    {
                        if (彙總2[i][14] == null || 彙總2[i][14].ToString() == "")
                        {
                            彙總2[i][14] = "0";
                        }
                        彙總2[i][11] = 彙總2[i][13].ToString().Trim('"');
                    }
                    StartCoroutine(後台彙總讀取2_2());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
        
        
    }
    IEnumerator 後台彙總讀取2_2()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取2");
        DateTime parsedDate = DateTime.ParseExact(月初, "yyyy/MM/dd", null);
        string formattedDate = parsedDate.ToString("yyyy-MM-dd");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = string.Format(request.downloadHandler.text.ToString(), formattedDate, 結束日期, PA2);
        Debug.Log(query);
        彙總6 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總6.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    for (int i = 0; i < 彙總6.Count; i++)
                    {
                        if (彙總6[i][14] == null || 彙總6[i][14].ToString() == "")
                        {
                            彙總6[i][14] = "0";
                        }
                        彙總6[i][11] = 彙總6[i][13].ToString().Trim('"');
                    }
                    StartCoroutine(後台彙總讀取1());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
        
    }
    IEnumerator 後台彙總讀取1()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取1");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        string query = string.Format(request.downloadHandler.text.ToString(), 開始日期, 結束日期, PA2);
        Debug.Log(query);
        彙總3 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總3.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    int sum = 0;
                    for (int i = 0; i < 彙總3.Count; i++)
                    {
                        彙總3[i][5] = "0";
                        彙總3[i][6] = "0";
                        彙總3[i][7] = "0";
                        彙總3[i][8] = "0";
                        彙總3[i][9] = "0";
                        彙總3[i][10] = "0";
                        for (int j = 0; j < 彙總1.Count; j++)
                        {
                            string n1 = 彙總3[i][2].ToString().Trim('"');
                            string n2 = 彙總1[j][2].ToString().Trim('"');
                            string n3 = 彙總1[j][15].ToString().Trim('"');
                            if ((n1 == n2) && n3 == "全")
                            {
                                彙總3[i][5] = 彙總1[j][10].ToString().Trim('"');
                                彙總3[i][6] = 彙總1[j][11].ToString().Trim('"');
                            }
                        }
                        double num = double.Parse(彙總3[i][6]);
                        int roundedValue = (int)Math.Round(num / 10000);
                        sum = sum + roundedValue;
                    }
                    天1.text = sum.ToString("#,##0");
                    if (sum >= 0)
                    {
                        天1.color = Color.blue;
                    }
                    else
                    {
                        天1.color = Color.red;
                    }
                    //Debug.Log(sum);
                    StartCoroutine(後台彙總讀取3());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
        
    }
    IEnumerator 後台彙總讀取3()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取3");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        DateTime parsedDate = DateTime.ParseExact(本周一, "yyyy/MM/dd", null);
        string formattedDate = parsedDate.ToString("yyyy-MM-dd");
        string query = string.Format(request.downloadHandler.text.ToString(), formattedDate, 結束日期, PA2);
        Debug.Log(query);
        彙總4 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總4.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    int sum = 0;
                    for (int i = 0; i < 彙總4.Count; i++)
                    {
                        彙總4[i][3] = "0";
                        彙總4[i][4] = "0";
                        彙總4[i][5] = "0";
                        彙總4[i][6] = "0";
                        彙總4[i][7] = "0";
                        彙總4[i][8] = "0";
                        for (int j = 0; j < 彙總2.Count; j++)
                        {
                            string n1 = 彙總4[i][1].ToString().Trim('"');
                            string n2 = 彙總2[j][2].ToString().Trim('"');
                            string n3 = 彙總2[j][15].ToString().Trim('"');
                            if ((n1 == n2) && n3 == "全")
                            {
                                彙總4[i][3] = 彙總2[j][10].ToString().Trim('"');
                                彙總4[i][4] = 彙總2[j][11].ToString().Trim('"');
                            }
                        }
                        double num = double.Parse(彙總4[i][4]);
                        int roundedValue = (int)Math.Round(num / 10000);
                        sum = sum + roundedValue;
                    }
                    周1.text = sum.ToString("#,##0");
                    if (sum >= 0)
                    {
                        周1.color = Color.blue;
                    }
                    else
                    {
                        周1.color = Color.red;
                    }
                    // Debug.Log(sum);
                    StartCoroutine(後台彙總讀取4());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
        
    }
    IEnumerator 後台彙總讀取4()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取4");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        DateTime parsedDate = DateTime.ParseExact(月初, "yyyy/MM/dd", null);
        string formattedDate = parsedDate.ToString("yyyy-MM-dd");
        string query = string.Format(request.downloadHandler.text.ToString(), formattedDate, 結束日期, PA2);
        Debug.Log(query);
        彙總7 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總7.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    int sum = 0;
                    for (int i = 0; i < 彙總7.Count; i++)
                    {
                        彙總7[i][3] = "0";
                        彙總7[i][4] = "0";
                        彙總7[i][5] = "0";
                        彙總7[i][6] = "0";
                        彙總7[i][7] = "0";
                        彙總7[i][8] = "0";
                        for (int j = 0; j < 彙總6.Count; j++)
                        {
                            string n1 = 彙總7[i][1].ToString().Trim('"');
                            string n2 = 彙總6[j][2].ToString().Trim('"');
                            string n3 = 彙總6[j][15].ToString().Trim('"');
                            if ((n1 == n2) && n3 == "全")
                            {
                                彙總7[i][3] = 彙總6[j][10].ToString().Trim('"');
                                彙總7[i][4] = 彙總6[j][11].ToString().Trim('"');
                            }
                        }
                        double num = double.Parse(彙總7[i][4]);
                        int roundedValue = (int)Math.Round(num / 10000);
                        sum = sum + roundedValue;
                    }
                    月1.text = sum.ToString("#,##0");
                    if (sum >= 0)
                    {
                        月1.color = Color.blue;
                    }
                    else
                    {
                        月1.color = Color.red;
                    }
                    // Debug.Log(sum);
                    StartCoroutine(後台彙總讀取5());
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }
        
    }
    IEnumerator 後台彙總讀取5()
    {
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", "後台彙總讀取5");
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            //Debug.Log(request.error);
        }
        DateTime parsedDate = DateTime.ParseExact(年初, "yyyy/MM/dd", null);
        string formattedDate = parsedDate.ToString("yyyy-MM-dd");
        string query = string.Format(request.downloadHandler.text.ToString(), formattedDate, 結束日期, PA2);
        Debug.Log(query);
        彙總5 = new JSONArray();
        using (SqlConnection connection = new SqlConnection(路徑1))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read()) // 逐行讀取資料
                        {
                            JSONNode jsona = new JSONObject(); // 創建 JSON 物件來存放每一行的資料
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i); // 取得欄位名稱
                                string value = reader.GetValue(i).ToString(); // 取得欄位值，並轉換成字串形式
                                jsona[columnName] = value; // 將欄位名稱和值加入 JSON 物件中
                            }
                            彙總5.Add(jsona); // 將整個 JSON 物件加入 JSON Array 中
                        }
                    }
                    for (int i = 0; i < 彙總5.Count; i++)
                    {
                        彙總5[i][3] = "0";
                        彙總5[i][4] = "0";
                        彙總5[i][5] = "0";
                        彙總5[i][6] = "0";
                        彙總5[i][7] = "0";
                        彙總5[i][8] = "0";
                        彙總5[i][11] = "0";
                        彙總5[i][12] = "0";
                        彙總5[i][13] = "0";
                        彙總5[i][14] = "0";
                        彙總5[i][15] = "0";
                        彙總5[i][16] = "0";


                        for (int j1 = 0; j1 < 彙總3.Count; j1++)
                        {
                            string n1 = 彙總5[i][1].ToString().Trim('"');
                            string n2 = 彙總3[j1][2].ToString().Trim('"');
                            if (n1 == n2)
                            {
                                double num = double.Parse(彙總3[j1][5]);
                                int roundedValue = (int)Math.Round(num / 10000);
                                double num1 = double.Parse(彙總3[j1][6]);
                                int roundedValue1 = (int)Math.Round(num1 / 10000);

                                彙總5[i][3] = roundedValue.ToString("#,##0");
                                彙總5[i][4] = roundedValue1.ToString("#,##0");
                            }
                        }

                        for (int j2 = 0; j2 < 彙總4.Count; j2++)
                        {
                            string n1 = 彙總5[i][1].ToString().Trim('"');
                            string n2 = 彙總4[j2][1].ToString().Trim('"');
                            if (n1 == n2)
                            {
                                double num = double.Parse(彙總4[j2][3]);
                                int roundedValue = (int)Math.Round(num / 10000);
                                double num1 = double.Parse(彙總4[j2][4]);
                                int roundedValue1 = (int)Math.Round(num1 / 10000);

                                彙總5[i][5] = roundedValue.ToString("#,##0");
                                彙總5[i][6] = roundedValue1.ToString("#,##0");
                            }
                        }

                        for (int j3 = 0; j3 < 彙總7.Count; j3++)
                        {
                            string n1 = 彙總5[i][1].ToString().Trim('"');
                            string n2 = 彙總7[j3][1].ToString().Trim('"');
                            if (n1 == n2)
                            {
                                double num = double.Parse(彙總7[j3][3]);
                                int roundedValue = (int)Math.Round(num / 10000);
                                double num1 = double.Parse(彙總7[j3][4]);
                                int roundedValue1 = (int)Math.Round(num1 / 10000);

                                彙總5[i][7] = roundedValue.ToString("#,##0");
                                彙總5[i][8] = roundedValue1.ToString("#,##0");
                            }
                        }
                    }
                    降冪(彙總5, 4);
                    int sum = 0;
                    if (copy1 != null)
                    {
                        DeletetextCopy1();
                    }
                    for (int ii = 0; ii < 彙總5.Count; ii++)
                    {
                        GameObject buttonObj = Instantiate(buttonPrefab1) as GameObject;
                        buttonObj.GetComponent<RectTransform>().anchoredPosition = new Vector2(3, -sum);
                        Transform tran = buttonscroll1.content;
                        buttonObj.transform.SetParent(tran, false);
                        TextMeshProUGUI[] texts = buttonObj.GetComponentsInChildren<TextMeshProUGUI>();
                        texts[0].text = 彙總5[ii][1].ToString().Trim('"');
                        texts[1].text = 彙總5[ii][2].ToString().Trim('"');
                        texts[2].text = 彙總5[ii][3].ToString().Trim('"');
                        texts[3].text = 彙總5[ii][4].ToString().Trim('"');
                        if (double.Parse(彙總5[ii][4]) >= 0)
                        {
                            texts[3].color = Color.blue;
                        }
                        else
                        {
                            texts[3].color = Color.red;
                        }
                        texts[4].text = 彙總5[ii][5].ToString().Trim('"');
                        texts[5].text = 彙總5[ii][6].ToString().Trim('"');
                        if (double.Parse(彙總5[ii][6]) >= 0)
                        {
                            texts[5].color = Color.blue;
                        }
                        else
                        {
                            texts[5].color = Color.red;
                        }
                        texts[6].text = 彙總5[ii][7].ToString().Trim('"');
                        texts[7].text = 彙總5[ii][8].ToString().Trim('"');
                        if (double.Parse(彙總5[ii][8]) >= 0)
                        {
                            texts[7].color = Color.blue;
                        }
                        else
                        {
                            texts[7].color = Color.red;
                        }
                        sum += 50;
                        copy1.Add(buttonObj);
                    }
                    RectTransform contentTransform = buttonscroll1.content;
                    contentTransform.anchoredPosition = new Vector2(contentTransform.anchoredPosition.x, 0);
                    contentTransform.sizeDelta = new Vector2(contentTransform.sizeDelta.x, sum);
                    if (sum > 1602)
                    {
                        buttonscroll1.vertical = true;
                    }
                    else
                    {
                        buttonscroll1.vertical = false;
                    }
                    讀取面板.SetActive(false);
                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                讀取面板.SetActive(false);
                yield break;
            }
            finally
            {
                connection.Close();
            }
        }

    }


    private void 降冪(JSONNode json, int a)
    {
        List<List<string>> tlist = new List<List<string>>();
        for (int i = 0; i<json.Count; i++)
        {
            List<string> list = new List<string>();
            for (int j = 0; j < json[i].Count; j++)
            {
                list.Add(json[i][j].ToString().Trim('"'));
            }
            tlist.Add(list);
        }
        tlist = tlist.OrderByDescending(row => double.Parse(row[a])).ToList();


        for (int i = 0; i<json.Count; i++)
        {

            for (int j = 0; j < json[i].Count; j++)
            {
                json[i][j] =  tlist[i][j];
            }

        }

    }
    public  class Global
    {
        public static string 登入人IP, 年,日期, 職稱, 系統語言, 使用者名稱, 密碼, 進入, 離開, 資料庫版本號, 執行檔版本號,版本號比對,預設伺服器路徑,版本號,選擇組別,阿帕契路徑,阿帕契,CC判別,合約內容,合約編號,級別,ID,查詢日期,人員,連動客戶;
        public static bool 判斷是否連線, 股東,國外,啟動完畢,系統分區控制,登入位置,鏡頭啟動,有無清單 ,讀取完畢, 首次登入, 讀取年份, 快速登入, show1, show2, show3, show4, show5, show6, show7,展示帳號登入,qwe,印尼內網;
        public static int 部門編號,camfps;
        public static float rate, rate_;
       //public static TimeSpan 運行天數;
        public static string PA, PB, PC, PD, PE, PF, PG, PH, PI, PJ, PK, PA1, PA2, PA3, PA4, PA5, PA6, PA9, PA20;
        //public static bool[] 電表異常BL = new bool[999];
       // public static Sprite[] Wnum;
       // public static Sprite[] Bnum;
        public static string[] 系統語言資料;
       // public static string[] CC判斷=new string[999];
        public static Dictionary<string, string> 系統語言字典 = new Dictionary<string, string>() { };
        public static string[,] PHP = new string[99, 2];
        public static string 資料庫名稱, 資料庫IP, 資料庫帳, 資料庫密,路徑;
    }
    public static IEnumerator 路徑()
    {
        string strcon = "取得路徑";
        string strUrl = string.Format(Main.Global.阿帕契路徑 + "comm={0}", strcon);
        UnityWebRequest request = UnityWebRequest.Get(strUrl);
        yield return request.SendWebRequest();
        if (request.result == UnityWebRequest.Result.ConnectionError)
        {
            Debug.Log(request.error);
            yield break;
        }
        Main.Global.路徑 = request.downloadHandler.text.ToString();
    }
    public static void 讀取sql()
    {
        string connectionString = "Data Source=mssql-147629-0.cloudclusters.net,12711;Initial Catalog=GCM-IP-DATE01;User ID=GCMIP01;Password=Df85deS52w";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();

                string query = "SELECT 類別,位址 FROM PHP連線 Where 名稱=N'GA'";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        int row = 0;
                        while (reader.Read()) // 假設只取前兩筆資料
                        {
                            // 取得類別和位址資料
                            Main.Global.PHP[row, 0] = reader["類別"].ToString();
                            Main.Global.PHP[row, 1] = reader["位址"].ToString();

                            row++;
                        }
                        Main.Global.國外 = true;
                    }

                }
            }
            catch (Exception ex)
            {
                Debug.LogError("Error connecting to the database: " + ex.Message);
                Main.Global.國外 = false;
            }
            finally
            {
                connection.Close();
            }
        }
    }
    public static string GetAddressByCategory(string category)
    {
        for (int i = 0; i < Main.Global.PHP.GetLength(0); i++)
        {
            if (Main.Global.PHP[i, 0] == category)
            {
                return Main.Global.PHP[i, 1];
            }
        }
        return ""; // 或者返回一個默認值
    }
    void Update()
    {
        
    }
    public void DeletetextCopy1()
    {
        // 刪除複製出來的物件
        foreach (GameObject i in copy1)
        {
            Destroy(i);
        }
        copy1.Clear();
    }
    public void 回上頁 ()
    {
        面板1.SetActive(true);
        面板2.SetActive(false);
        天.text ="";
        周.text ="";
        月.text ="";
        天1.text ="";
        周1.text ="";
        月1.text ="";
        DeletetextCopy1();
    }
    public void 回登入()
    {
        change_Secen(0);
    }
    public void 個人帳號管理()
    {
        change_Secen(2);
    }     
    public void change_Secen(int _screenNum)
    {

        SceneManager.LoadScene(_screenNum);
    }
    public void 截圖()
    {
        string fileName = string.Format("{0}/screenshot_{1}.png", Application.persistentDataPath, System.DateTime.Now.ToString("yyyyMMdd_HHmmss"));
        ScreenCapture.CaptureScreenshot(fileName);
    }
   

}