// Copyright (c) 2008-2019 schick Informatik
// Description: Erzeugt eine CSV Datei aus dem Query
//

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

namespace puls200.AIS2Excel
{
    public static class AISVessel
    {
        const string ExportQuery = "SELECT ap.MMSI, ap.timestamp, ap.latitude, ap.longitude, ap.ROT, ap.COG, ap.SOG, ap.Accuracy, ap.heading, ap.NavStatus, ap.CommState, ap.Raim, " +
            "sd.aisversion, sd.imoNumber, sd.callsign, sd.name, sd.shiptype, sd.dimension, sd.typeofdevice, sd.maxpresetstaticdraught, sd.dte, sd.classb, sd.breadth, sd.length " +
            "FROM aisstaticdata sd JOIN aisposreport ap ON ap.mmsi = sd.mmsi " +
            "WHERE ap.Latitude > @LRLAT AND ap.Latitude < @ULLAT AND ap.Longitude > @ULLON AND ap.Longitude < @LRLON AND ap.Timestamp > @FROM AND ap.Timestamp < @TO " +
            "ORDER BY ap.Timestamp";


        public static void Export(DateTime from, DateTime to, double ulLat, double ulLon, double lrLat, double lrLon, string filename)
        {
            using (StreamWriter sw = new StreamWriter(filename))
            {

                SqlConnection con = new SqlConnection(Properties.Settings.Default.ConnectionString);
                con.Open();

                SqlCommand cmd = new SqlCommand(ExportQuery, con);
                cmd.Parameters.AddWithValue("FROM", from);
                cmd.Parameters.AddWithValue("TO", to);
                cmd.Parameters.AddWithValue("ULLAT", ulLat * 600000);
                cmd.Parameters.AddWithValue("ULLON", ulLon * 600000);
                cmd.Parameters.AddWithValue("LRLAT", lrLat * 600000);
                cmd.Parameters.AddWithValue("LRLON", lrLon * 600000);

                IDataReader reader = cmd.ExecuteReader();
                createCsvFile(reader, sw);
                reader.Close();
                con.Close();
            }
        }

        public static void createCsvFile(IDataReader reader, StreamWriter writer)
        {
            const string Delimiter = "\"";
            const string Separator = ";";

            // write header row
            for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++)
            {
                if (columnCounter > 0)
                {
                    writer.Write(Separator);
                }
                writer.Write(Delimiter + reader.GetName(columnCounter) + Delimiter);
            }
            writer.WriteLine(string.Empty);

            // data loop
            while (reader.Read())
            {
                // column loop
                for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++)
                {
                    if (columnCounter > 0)
                    {
                        writer.Write(Separator);
                    }
                    writer.Write(Delimiter + reader.GetValue(columnCounter).ToString().Replace('"', '\'') + Delimiter);
                }   // end of column loop
                writer.WriteLine(string.Empty);
            }   // data loop

            writer.Flush();
        }

    }
}