﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using System.Web.Script.Serialization;

namespace lotteryWCF
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

    public class Service1 : IService1
    {

        private string sqlConnection = ConfigurationManager.ConnectionStrings["lotteryDb"].ConnectionString;
        private string selectMember = "SELECT * FROM dbo.member";
        private string selectPrize = "SELECT * FROM dbo.prize";
        private string selectNoWinners = "SELECT * FROM dbo.member WHERE winner=0";

        /// <summary>
        /// Получение всех участников
        /// </summary>
        /// <returns>Сериализованная строка JSON</returns>
        public string GetAllCustomers()
        {
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();

            using (SqlConnection connection = new SqlConnection(sqlConnection))
            {

                connection.Open();
                SqlCommand command = new SqlCommand();
                command.CommandText = selectMember;
                command.Connection = connection;
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Dictionary<string, string> row = new Dictionary<string, string>();
                    row.Add("ID", GetValue(reader["id"]));
                    row.Add("Name", GetValue(reader["name"]));
                    row.Add("Surname", GetValue(reader["surname"]));
                    row.Add("Winner", GetValue(reader["winner"]));
                    row.Add("Prize", GetValue(reader["prize"]));
                    row.Add("Money", GetValue(reader["money"]));
                    rows.Add(row);
                }

                reader.Close();
            }
            return serializer.Serialize(rows);
        }

        /// <summary>
        /// Сохранение данных в базу
        /// </summary>
        /// <param name="saveData">двумерный list где:
        /// [i] - список id
        /// [i][0] - id
        /// [i][1] - признак победителя
        /// [i][2] - id приза
        /// [i][3] - деньги</param>
        public void MakeWinners(List<List<string>> saveData)
        {
            using (SqlConnection connection = new SqlConnection(sqlConnection))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.CommandText = "UPDATE dbo.member SET winner=@win, prize=@prize, money=@money WHERE id=@id";
                command.Connection = connection;
                foreach (var i in saveData)
                {

                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@id", i[0]);
                    command.Parameters.AddWithValue("@win", i[1]);
                    if (i[2] == "NULL")
                    {
                        command.Parameters.AddWithValue("@prize", DBNull.Value);
                    }
                    else {
                        command.Parameters.AddWithValue("@prize", i[2]);
                    }
                    if (i[3] == "NULL")
                    {
                        command.Parameters.AddWithValue("@money", DBNull.Value);
                    }
                    else {
                        command.Parameters.AddWithValue("@money", i[3]);
                    }
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Получение участников лотереи которые не являются победителями
        /// </summary>
        /// <returns>Сериализованная строка JSON</returns>
        public string GetCustomers()
        {
            {
                JavaScriptSerializer serializer = new JavaScriptSerializer();
                List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();

                using (SqlConnection connection = new SqlConnection(sqlConnection))
                {

                    connection.Open();
                    SqlCommand command = new SqlCommand();
                    command.CommandText = selectNoWinners;
                    command.Connection = connection;
                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        Dictionary<string, string> row = new Dictionary<string, string>();
                        row.Add("ID", GetValue(reader["id"]));
                        row.Add("Name", GetValue(reader["name"]));
                        row.Add("Surname", GetValue(reader["surname"]));
                        row.Add("Winner", GetValue(reader["winner"]));
                        row.Add("Prize", GetValue(reader["prize"]));
                        row.Add("Money", GetValue(reader["money"]));
                        rows.Add(row);
                    }

                    reader.Close();
                }

                return serializer.Serialize(rows);
            }
        }

        /// <summary>
        /// Получение списка призов из таблицы prize
        /// </summary>
        /// <returns>Сериализованная строка JSON</returns>
        public string GetPrize()
        {
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();

            using (SqlConnection connection = new SqlConnection(sqlConnection))
            {

                connection.Open();
                SqlCommand command = new SqlCommand();
                command.CommandText = selectPrize;
                command.Connection = connection;
                SqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
                    Dictionary<string, string> row = new Dictionary<string, string>();
                    row.Add("ID", GetValue(reader["id"]));
                    row.Add("Name", GetValue(reader["name"]));
                    rows.Add(row);

                }

                reader.Close();
            }

            return serializer.Serialize(rows);
        }

        /// <summary>
        /// Если обьект пустой то возвращает "null" иначе object.ToString
        /// </summary>
        /// <param name="value">обрабатываемый объект</param>
        /// <returns>"null" или object.ToString</returns>
        private static string GetValue(object value)
        {
            return value == DBNull.Value ? "null" : value.ToString();
        }

    }
}
