using System;
using System.Data.SqlClient;
using System.Data;

namespace buero
{
    public partial class JSON_Table : System.Web.UI.Page
    {
        public bool Unbekannt_Anzeigen = false;
        string GetQS(string txt)
        {
            string result = Request.QueryString[txt];
            if (result == null) result = "";
            return result;
        }
        protected string GetJSONRecord(DataColumnCollection dc, DataRow dr, string DELIMITER_CODE)
        {
            const string DELIMITER = "\"";
            const string SEPARATOR = ",";
            string s = "";
            for (int i = 0; i < dc.Count; i++)
            {
                string s1 = dr[i].ToString();
                string ColumnName = dc[i].ColumnName;
                switch (ColumnName)
                {
                    case "dt":
                        s1 = DateTime.Parse(s1).ToString("yyyy-MM-dd");
                        break;
                    case "rt":
                        s1 = s1 == "" ? ".:." : s1;
                        break;
                    case "val":
                        if ((Unbekannt_Anzeigen) && (s1 == ""))
                            return "";
                        else
                            break;
                    default:
                        break;
                }
                s += DELIMITER_CODE + dc[i].ColumnName + DELIMITER_CODE + ":" + DELIMITER + s1 + DELIMITER + (i < dc.Count - 1 ? SEPARATOR : "");
            }
            return "{" + s + "}";
        }
        protected string GetJSON(DataTable dt, string code)
        {
            if (dt == null) return "1";
            int RowsCount = dt.Rows.Count;
            if (RowsCount <= 0) return "2";
            string JSON = "";
            string DELIMITER_CODE = "";
            switch (code)
            {
                case "js":
                    DELIMITER_CODE = "";
                    break;
                case "json":
                    DELIMITER_CODE = "\"";
                    break;
            }

            for (int i = 0; i < RowsCount; i++)
            {
                DataRow dr = dt.Rows[i];
                string NewRecord = GetJSONRecord(dt.Columns, dr, DELIMITER_CODE);
                if (NewRecord != "")
                    JSON += (JSON == "" ? "" : ",") + NewRecord;
               // if (i == 40) break;
            }
            return "[" + JSON + "]";
        }
        protected string GetKmlFormat(string key, string value)
        {
            return "<" + key + ">" + value + "</" + key + ">\n";
        }
        protected string GetKMLRecord(string lng, string lat, string name, string description)
        {
            string kml = GetKmlFormat("name", name);
            kml += GetKmlFormat("description", "<![CDATA[" + description + "]]>");
            kml += GetKmlFormat("Point", GetKmlFormat("coordinates", lng+","+lat+",0"));
            kml = GetKmlFormat("Placemark", kml);
            return kml;
        }
        protected string GetDescription(DataRow dr)
        {
            string description = "";
            string x = dr["x"].ToString();
            switch (x)
            {
                case "0.":
                case "1.":
                case "2.":
                case "3.":
                case "4.":
                case "5.":
                case "6.":
                case "7.":
                case "8.":
                    x = x + "Klasse";
                    break;
                default:
                    x = (x[0] == '0' ? x.Substring(1) : x);
                    switch (x)
                    {
                        case "0":
                            x = "";
                            break;
                        case "1":
                            x = "Im Jahr danach<br>";
                            break;
                        default:
                            x = x + " Jahre danach<br>";
                            break;
                    }
                    break;
            }
            string jahr = dr["jahr"].ToString();
            string monat = dr["monat"].ToString();
            string tag = dr["tag"].ToString();
            string datum = jahr
                + (monat == "0" ? "" : "-" + monat.PadLeft(2, '0')
                + (tag == "0" ? "" : "-" + tag.PadLeft(2, '0')));
            string comment = dr["COMMENT"].ToString();
            comment = (comment == "" ? "" : "(" + comment + ")");
            description += (x == "" ? "" : x + " ") + datum + ": " + dr["WAS"].ToString() + " " + comment + "\n<br>";
            return description;
        }
        protected string GetKML(DataTable dt)
        {
            if (dt == null) return "1";
            int RowsCount = dt.Rows.Count;
            if (RowsCount <= 0) return "2";
            string OldPoint = "";
            string kml_name = "";
            string kml_description = "";
            string kml_lng = "";
            string kml_lat = "";
            string str_kml = "";
            for (int i = 0; i < RowsCount; i++)
            {
                DataRow dr = dt.Rows[i];
                string Point = dr["LNG"].ToString().Trim() + dr["LAT"].ToString().Trim();
                if (i == 0) OldPoint = Point;
                if (Point==OldPoint)
                {
                    if (Point == "")
                    {
                        ;
                    }
                    else
                    {
                        kml_description += GetDescription(dr);
                    }
                }
                else
                {
                    if (Point == "")
                    {
                        if (OldPoint != "")
                        {
                            str_kml += GetKMLRecord(kml_lng, kml_lat, kml_name, kml_description);
                            kml_lng = "";
                            kml_lat = "";
                            kml_name = "";
                            kml_description = "";
                        }
                    }
                    else
                    {
                        if (OldPoint == "")
                        {
                            kml_lng = dr["LNG"].ToString().Replace(",", ".").Trim();
                            kml_lat = dr["LAT"].ToString().Replace(",", ".").Trim();
                            kml_name = dr["WO"].ToString() + " " + dr["WO_ADR"].ToString();
                            kml_description = GetDescription(dr);
                            OldPoint = Point;
                        }
                        else
                        { 
                            str_kml += GetKMLRecord(kml_lng, kml_lat, kml_name, kml_description);
                            kml_lng = dr["LNG"].ToString().Replace(",", ".").Trim();
                            kml_lat = dr["LAT"].ToString().Replace(",", ".").Trim();
                            kml_name = dr["WO"].ToString() + " " + dr["WO_ADR"].ToString();
                            kml_description = GetDescription(dr);
                            OldPoint = Point;
                        }
                    }
                }
                if (i == RowsCount - 1) str_kml += GetKMLRecord(kml_lng, kml_lat, kml_name, kml_description);
            }
            str_kml = GetKmlFormat("Folder", GetKmlFormat("name", "Treffen") + str_kml);
            str_kml = GetKmlFormat("Document", GetKmlFormat("name", "Matura 8AB66") 
                + GetKmlFormat("description", "Unsere Treffen seit 1957") + str_kml);
            str_kml = "<kml xmlns=\"http://www.opengis.net/kml/2.2\">\n" + str_kml + "</kml>\n";
            return "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" + str_kml;
        }
        string GetHelp()
        {
            string s = "";
            s += "<br>Abruf von JSON- oder KML-Daten der Beiträge";
            s += "<br>---------------------------------";
            s += "<br>b Datenbank { 'mat' }";
            s += "<br>t Tabelle { n1 || n1,n2,n3...}";
            s += "<br>c Code { json || js || kml }";
            s += "<br>n Name { 1 Tablename, name }";
            s += "<br>h Hilfe, dieser Text";
            return "<pre>" + s + "</pre";
        }
        protected void WriteTextAndEnd(string Text, string code)
        {
            string mime = "";
            switch (code)
            {
                case "kml":
                    mime = "text/xml"; // .google-earth.kml+xml
                    break;
                case "js":
                case "json":
                default:
                    mime = "text/html";
                    break;
            }
            Response.Clear();
            Response.AddHeader("Content-Type", mime);
            Response.Write(Text);
            Response.End();
        }
        protected void Page_Load()
        {
            string ConnectionString = "";
            string str_dbase = GetQS("b");
            string str_table = GetQS("t");
            string str_code = GetQS("c");
            string str_name = GetQS("n");
            string str_help = GetQS("h");
            if (str_help == "1")
            {
                WriteTextAndEnd(GetHelp(),"");
            }
            switch (str_dbase)
            {
                case "mat":
                    ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionMatura"].ConnectionString;
                    break;
                default:
                    WriteTextAndEnd(GetHelp(),"");
                    return;
            }
            string[] tables = str_table.Split(new char[] { ',' });
            string s = "";
            switch (tables.Length)
            {
                case 0: return;
                case 1:
                    s = WriteData(ConnectionString, str_table, str_name, str_code);
                    WriteTextAndEnd(s,str_code);
                    break;
                default:
                    for (int i = 0; i < tables.Length; i++)
                    {
                        s += WriteData(ConnectionString, tables[i], tables[i], str_code);
                    }
                    WriteTextAndEnd(s,str_code);
                    break;
            }
        }
        string WriteData(string conn, string table, string name, string code)
        {
            string sql_Select = "";
            switch (code)
            {
                case "kml":
                    sql_Select = "SELECT * FROM " + table + " ORDER BY LAT, LNG, JAHR, MONAT, TAG, ORD";
                    break;
                default:
                    sql_Select = "SELECT * FROM " + table;
                    break;
            }
            SqlDataAdapter da = new SqlDataAdapter(sql_Select, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string s = "";
            switch (code)
            {
                case "kml":
                    s = GetKML(dt);
                    break;
                default:
                    s = GetJSON(dt, code);
                    break;
            }
            if (name != "") s = "const " + name + "=" + s;
            return s;
        }
    }
}