using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Console_SendMail
{
    public static class DatabaseManager
    {
        public static System.Data.SqlClient.SqlConnection getCn()
        {
            string cs = Properties.Settings.Default.gwcs;
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
            cn.ConnectionString = cs;
            return cn;
        }

        public static bool GetGroupEnableMail(string GroupColumn)
        {
            List<string> retval = new List<string>();
            var cn = getCn();
            cn.Open();
            var sql = "select isnull(usemail,'False') " +
                " from  UserGroup WITH (nolock) " +
                $" where gcode='{GroupColumn}' ";

     
            var cmd = new SqlCommand(sql, cn);
            var value = cmd.ExecuteScalar().ToString().ToUpper();
    
            cmd.Dispose();
            cn.Close();
            cn.Dispose();
            return value == "TRUE";
        }



        public static List<String> getGroupListWithoutGcode(string GroupColumn, string table, string where = "", Boolean desc = false, Boolean useColumncover = true)
        {
            List<string> retval = new List<string>();
            var cn = getCn();
            cn.Open();
            var sql = "select {0} " +
                " from {1}  WITH (nolock) " +
                " where isnull({0},'') != '' ";

            if (table.ToUpper() == "USERS")
                sql = "select {0} " +
                " from {1}  WITH (nolock) " +
                " where isnull({0},'') != '' ";


            if (where != "") sql += " and " + where;

            sql += " group by {0} " +
                   " order by {0} ";
            if (desc) sql += " desc";

            if (useColumncover)
                sql = string.Format(sql, "[" + GroupColumn + "]", table);
            else
                sql = string.Format(sql, GroupColumn, table);
            var cmd = new SqlCommand(sql, cn);
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                retval.Add(rdr[0].ToString());
            }
            cmd.Dispose();
            cn.Close();
            cn.Dispose();
            return retval;
        }
        public static string GetUserName(string gcode, string uid)
        {
            var slq = "select isnull(name,'') from vGroupUser" +
                    $" where gcode = '{gcode}'" +
                    $" and id = '{uid}'";
            try
            {
                return ExecuteScalar(slq);
            }
            catch
            {
                return string.Empty;
            }

        }
        public static string GetUserTel(string gcode, string uid)
        {
            var slq = "select isnull(tel,'') from vGroupUser" +
                    $" where gcode = '{gcode}'" +
                    $" and id = '{uid}'";
            try
            {
                return ExecuteScalar(slq);
            }
            catch
            {
                return string.Empty;
            }

        }
        public static int ExecuteNonQuery(string sql)
        {
            var cn = getCn();
            cn.Open();
            var cmd = new SqlCommand(sql, cn);
            var retval = cmd.ExecuteNonQuery();
            cn.Close();
            cn.Dispose();
            return retval;
        }
        public static int ExecuteScalarI(string sql)
        {
            var cn = getCn();
            cn.Open();
            var cmd = new SqlCommand(sql, cn);
            var retval = (int)(cmd.ExecuteScalar());
            cn.Close();
            cn.Dispose();
            return retval;
        }
        public static string ExecuteScalar(string sql)
        {
            var cn = getCn();
            cn.Open();
            var cmd = new SqlCommand(sql, cn);
            var retval = cmd.ExecuteScalar().ToString();
            cn.Close();
            cn.Dispose();
            return retval;
        }

        public static List<String> getGroupList(string gcode, string GroupColumn, string table, string where = "", Boolean desc = false, Boolean useColumncover = true)
        {
            List<string> retval = new List<string>();
            var cn = getCn();
            cn.Open();
            var sql = "select {0} " +
                " from {1}  WITH (nolock) " +
                $" where gcode='{gcode}' and isnull({0},'') != '' ";

            if (table.ToUpper() == "USERS")
                sql = "select {0} " +
                " from {1}   WITH (nolock) " +
                " where isnull({0},'') != '' ";


            if (where != "") sql += " and " + where;

            sql += " group by {0} " +
                   " order by {0} ";
            if (desc) sql += " desc";

            if (useColumncover)
                sql = string.Format(sql, "[" + GroupColumn + "]", table);
            else
                sql = string.Format(sql, GroupColumn, table);
            var cmd = new SqlCommand(sql, cn);
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                retval.Add(rdr[0].ToString());
            }
            cmd.Dispose();
            cn.Close();
            cn.Dispose();
            return retval;
        }


        public static List<String> getDateList(string table,string where="")
        {
            return getGroupList("pdate", table,where);
        }
    }
}