using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Printing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CommonHelper;
using Dapper;
using GasolineBlend.Entity;
using Google.Protobuf.WellKnownTypes;
using Microsoft.Office.Interop.Excel;
using MySql.Data.MySqlClient;

namespace GasolineBlend.DAL
{
    public class ArticleDataDAL : BaseMySQLDAL
    {
        public List<ArticleDataPage> GetArticleDataList(string Platform, string ArticleType, string Scope, string Keyword, int PageNumber, int PageSize,int UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT a.id,a.platformulr,a.platform,a.articletype,a.articletitle,a.articleurl,a.entertime,a.pubtime,CASE WHEN s.articleid IS NOT NULL THEN 1 ELSE 0 END AS favoriteflag FROM articledata a LEFT JOIN collectarticle s  ON a.id = s.articleid AND s.userid = '{UserId}'  WHERE 1=1 ";
               
               
                if(!string.IsNullOrEmpty(Platform) && Platform.Contains("全部"))
                {
                    string updatedPlatform = Platform.Replace("全部", "");
                    sql += $" AND (a.region = '{updatedPlatform}' or a.region='全国') ";

                }
                if (!string.IsNullOrEmpty(Platform) && Platform.Contains("国家部委") && string.IsNullOrEmpty(Scope))
                {
                    sql += $" AND a.region ='全国' ";

                }
                else if (!string.IsNullOrEmpty(Platform) && string.IsNullOrEmpty(Scope) && !Platform.Contains("全部"))
                {
                    sql += $" AND a.platform like'%{Platform}%' ";
                }
                else if (!string.IsNullOrEmpty(Scope))
                {
                    sql += $" AND a.platform like'%{Scope}%' ";
                }
                if (!string.IsNullOrEmpty(ArticleType)) {
                    sql += $" AND a.articletype = '{ArticleType}' ";
                }
                if (!string.IsNullOrEmpty(Keyword)) {
                    sql += $" AND a.articletitle like '%{Keyword.Trim()}%' ";
                }
                int offset = (PageNumber - 1) * PageSize;
                sql += $" AND a.showflag=1 ORDER BY a.pubtime DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<ArticleDataPage>(sql).ToList();
            }
        }

        public int GetArticleDataCount(string Platform, string ArticleType, string Scope, string Keyword)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT  COUNT(*)  from articledata WHERE 1=1 ";


                if (!string.IsNullOrEmpty(Platform) && Platform.Contains("全部"))
                {
                    string updatedPlatform = Platform.Replace("全部", "");
                    sql += $" AND (region = '{updatedPlatform}' or region='全国') ";
                }
                if (!string.IsNullOrEmpty(Platform) && Platform.Contains("国家部委") && string.IsNullOrEmpty(Scope))
                {
                    sql += $" AND region='全国' ";
                }
                else if (!string.IsNullOrEmpty(Platform) && string.IsNullOrEmpty(Scope) && !Platform.Contains("全部"))
                {
                    sql += $" and platform like'%{Platform}%' ";
                }
                else if (!string.IsNullOrEmpty(Scope))
                {
                    sql += $" and platform like'%{Scope}%' ";
                }
                if (!string.IsNullOrEmpty(ArticleType))
                {
                    sql += $" and articletype = '{ArticleType}' ";
                }
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and articletitle like '%{Keyword.Trim()}%' ";
                }
                sql += $"  and showflag=1 ORDER BY pubtime DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }
        public int GetDeclarationtimeCount(string Keyword, int Time, string Region)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT  COUNT(*)   from articledata a JOIN departmentdata d ON a.platform = d.department WHERE a.articletype='申报通知' AND a.declarationtime IS NOT NULL ";

                if (!string.IsNullOrEmpty(Region))
                {
                    sql += $" and a.region = '{Region}' ";
                }

                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" AND a.articletitle like '%{Keyword.Trim()}%' ";
                }
                if (Time != 0)
                {
                    if (Time == 7)
                    {
                        sql += $" AND a.declarationtime IS NOT NULL AND a.deadline BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY ";
                    }
                    else if(Time == 30){
                        sql += $" AND a.declarationtime IS NOT NULL AND a.deadline BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY ";
                    }    
                }
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }

        public List<ArticleDataPage> GetDeclarationtimeList( string Keyword,int Time, int PageNumber, int PageSize ,string Region)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT  *, d.region  from articledata a JOIN departmentdata d ON a.platform = d.department WHERE a.articletype='申报通知' AND a.declarationtime IS NOT NULL  ";
                if (!string.IsNullOrEmpty(Region))
                {
                    sql += $" and a.region = '{Region}' ";
                }

                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" AND a.articletitle like '%{Keyword.Trim()}%' ";
                }
                if (Time != 0)
                {
                    if (Time == 7)
                    {
                        sql += $" AND a.declarationtime IS NOT NULL AND a.deadline BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY ";
                    }
                    else if (Time == 30)
                    {
                        sql += $" AND a.declarationtime IS NOT NULL AND a.deadline BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY ";
                    }
                }
                int offset = (PageNumber - 1) * PageSize;
                sql += $" ORDER BY a.declarationtime DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<ArticleDataPage>(sql).ToList();
            }
        }


        public List<ArticleDataPage> GetArticleCollectionList(string Platform, string ArticleType, string Keyword, int PageNumber, int PageSize, int UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT a.id,a.platformulr,a.platform,a.articletype,a.articletitle,a.articleurl,a.entertime,a.pubtime FROM articledata a LEFT JOIN collectarticle s  ON a.id = s.articleid AND s.userid ='{UserId}'  WHERE a.id = s.articleid";

                if (!string.IsNullOrEmpty(Platform))
                {
                    sql += $" AND a.platform like'%{Platform}%' ";
                }
                if (!string.IsNullOrEmpty(ArticleType))
                {
                    sql += $" AND a.articletype = '{ArticleType}' ";
                }
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" AND a.articletitle like '%{Keyword.Trim()}%' ";
                }
                int offset = (PageNumber - 1) * PageSize;
                sql += $" AND a.showflag=1 ORDER BY a.pubtime DESC  LIMIT {PageSize} OFFSET {offset}";

                return connection.Query<ArticleDataPage>(sql).ToList();
            }
        }

        public int GetArticleCollectionCount(string Platform, string ArticleType, string Keyword,int UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT COUNT(*) FROM articledata a LEFT JOIN collectarticle s ON a.id = s.articleid AND s.userid = '{UserId}' WHERE a.id = s.articleid ";

                 if (!string.IsNullOrEmpty(Platform))
                {
                    sql += $" and a.platform like'%{Platform}%' ";
                }
                if (!string.IsNullOrEmpty(ArticleType))
                {
                    sql += $" and a.articletype = '{ArticleType}' ";
                }
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and a.articletitle like '%{Keyword.Trim()}%' ";
                }
                sql += $"  and a.showflag=1 ORDER BY a.pubtime DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }

        public List<ArticleDataPage> GetDepartmentCollectionList(string UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"select DISTINCT(a.platform) from  articledata a LEFT JOIN collectarticle s ON a.id = s.articleid AND s.userid = '{UserId}' WHERE a.id = s.articleid";
                return connection.Query<ArticleDataPage>(sql).ToList();
            }
        }
    }
}