using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;


public partial class StoredProcedures
{
    [SqlProcedure]
    public static void GetSalesPerTerritoryByMonth(SqlDateTime StartDate,
                                                    SqlDateTime EndDate)
    {
        // utworzenie obiektu SqlCommand
        SqlCommand command = new SqlCommand();

        // użycie kontekstu połączenia
        command.Connection = new SqlConnection("Context connection=true");
        command.Connection.Open();

        // kod T-SQL, który należy wykonać
        string sql =
            "SELECT DISTINCT " +
                "CONVERT(CHAR(7), h.OrderDate, 120) AS YYYY_MM " +
            "FROM Sales.SalesOrderHeader h " +
            "WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
            "ORDER BY YYYY_MM";
        command.CommandText = sql.ToString();

        // przypisanie parametrów StartDate i EndDate
        SqlParameter param =
            command.Parameters.Add("@StartDate", SqlDbType.DateTime);
        param.Value = StartDate;
        param = command.Parameters.Add("@EndDate", SqlDbType.DateTime);
        param.Value = EndDate;

        // odczytanie danych
        SqlDataReader reader = command.ExecuteReader();

        // utworzenie obiektu StringBuilder
        System.Text.StringBuilder yearsMonths = new System.Text.StringBuilder();

        // przejście przez każdy wiersz w obiekcie czytającym i dodanie wartości do StringBuilder
        while (reader.Read())
        {
            yearsMonths.Append("[" + (string)reader["YYYY_MM"] + "], ");
        }

        // zamknięcie obiektu czytającego
        reader.Close();

        if (yearsMonths.Length > 0)
        {
            // usunięcie ostatniego przecinka na liście
            yearsMonths.Remove(yearsMonths.Length - 2, 1);
        }
        else
        {
            command.CommandText =
                "RAISERROR('W podanym okresie nie zawarto żadnej transakcji sprzedaży.', 16, 1)";
            try
            {
                SqlContext.Pipe.ExecuteAndSend(command);
            }
            catch
            {
                return;
            }
        }

        // definicja zapytania zwracającego tabelę skrośną
        sql =
            "SELECT TerritoryId, " +
                    yearsMonths.ToString() +
            "FROM " +
            "(" +
                "SELECT " +
                    "TerritoryId, " +
                    "CONVERT(CHAR(7), h.OrderDate, 120) AS YYYY_MM, " +
                    "d.LineTotal " +
                "FROM Sales.SalesOrderHeader h " +
                "JOIN Sales.SalesOrderDetail d " +
                    "ON h.SalesOrderID = d.SalesOrderID " +
                "WHERE h.OrderDate BETWEEN @StartDate AND @EndDate " +
            ") p " +
            "PIVOT " +
            "( " +
                "SUM (LineTotal) " +
                "FOR YYYY_MM IN " +
                "( " +
                    yearsMonths.ToString() +
                ") " +
            ") AS pvt " +
            "ORDER BY TerritoryId";

        // ustawienie CommandText
        command.CommandText = sql.ToString();

        // wykonanie zapytania przez metodę wywołującą
        SqlContext.Pipe.ExecuteAndSend(command);

        // zamknięcie połączenia
        command.Connection.Close();
    }
};
