const mysql = require("../utils/mysql_db");
const dbhandle = require("../utils/handle_db");
const uuid = require("node-uuid");

module.exports = {
	async getNoTokenNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate FROM wj_news_details where isIndex = '1' order by type desc,str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc;`;
		return await mysql.query(sql);
	},

	async getTypeNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate FROM wj_news_details where type LIKE '%${params.type}%' and isIndex=1 order by str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc;`;
		return await mysql.query(sql);
	},


	async getNoTokenVideoNewsByType(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate,isIndex,keshi,department,sender,approvalUrl,advise,orderlevel,videoUrl FROM wj_news_details WHERE type LIKE '%视频新闻%' order by CONCAT(orderlevel,str_to_date(createDate,'%Y-%m-%d %H:%i:%s')) desc;`;
		return await mysql.query(sql);
	},
	async getNoTokenPaperNewsByType(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate,isIndex,keshi,department,sender,approvalUrl,advise,orderlevel,paperUrl FROM wj_news_details WHERE type LIKE '%网络公开课%' order by CONCAT(orderlevel,str_to_date(createDate,'%Y-%m-%d %H:%i:%s')) desc;`;
		return await mysql.query(sql);
	},


	async getNoTokenNewsMore(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate FROM wj_news_details where type = '${params.name}' and state=1 order by str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc limit ${parseInt(params.pageCount - 1) * 10},10;`;
		return await mysql.query(sql);
	},
	async getTotalNoTokenNewsMore(params) {
		let sql = `SELECT count(1) count FROM wj_news_details where type = '${params.name}' and state=1`;
		return await mysql.query(sql);
	},

	async getOneNoTokenNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate FROM wj_news_details WHERE id='${params.id}' ;`;
		return await mysql.query(sql);
	},

	async getTopNoTokenNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate FROM wj_news_details WHERE isIndex=1 order by str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc limit 0,5 ;`;
		return await mysql.query(sql);
	},
	async getLunbo(params) {
		let sql = `SELECT id,imgURL,updateTime,title FROM wj_lunbo order by id desc;`;
		return await mysql.query(sql);
	},
	async getOneLunbo(params) {
		let sql = `SELECT id,imgURL,updateTime,title,detail FROM wj_lunbo WHERE id='${params.id}' ;`;
		return await mysql.query(sql);
	},
	async getSearchNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate from wj_news_details
		where LOCATE('${params.keywords}',CONCAT_WS('#',title,keyword,abstract,editor,keshi,department,details,createDate))>0 order by str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc limit ${parseInt(params.pageCount - 1) * 10},10;`;
		return await mysql.query(sql);
	},
	async getTotalNews(params) {
		let sql =
			`SELECT count(1) count from wj_news_details
		where LOCATE('${params.keywords}',CONCAT_WS('#',title,keyword,abstract,editor,keshi,department,details,createDate))>0`;
		return await mysql.query(sql);
	},
	async getTougaoStat(params) {
		let sql =
			`select n.dept_name as department,
    sum(case when n.dept_name=d.department then 1 else 0 end) as counter 	
    from wj_dept_name n , wj_news_details d 
   where date_format(d.createDate,'%Y-%m') = DATE_FORMAT(now(),'%Y-%m') 
    group by n.dept_name order by (n.id+0) ASC`;
		return await mysql.query(sql);
	},
	async getTotalDeptNews(params) {
		let sql = `SELECT count(1) count from wj_news_details where department='${params.keywords}'`;
		return await mysql.query(sql);
	},
	async getDeptNews(params) {
		let sql =
			`SELECT id,state,title,keyword,abstract,details,type,sender as editor,reviser,createDate,updateDate from wj_news_details
		where department='${params.keywords}' order by str_to_date(createDate,'%Y-%m-%d %H:%i:%s') desc limit ${parseInt(params.pageCount - 1) * 10},10;`;
		return await mysql.query(sql);
	}


};