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();
|
}
|
}
|
}
|
}
|
