using System;
|
using System.Collections.Generic;
|
using System.Data;
|
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 AgentDataDAL : BaseMySQLDAL
|
{
|
public List<AgentDataPage> GetAgentDataList(string Keyword, int PageNum, int PageSize, string AgentType, string CreatorId, string FavoriteFlag)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = $"SELECT a.id,a.name,a.prompt,a.permission,a.creationtime,a.modificationtime,a.avatarlink,a.creatorid,a.agenttype,a.description,a.userno,a.questionno,a.modelapi,a.modelkey,a.systemrouting,CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END AS favoriteflag FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE 1=1";
|
if (!string.IsNullOrEmpty(Keyword)) {
|
sql += $" and a.name like '%{Keyword.Trim()}%' ";
|
}
|
if (!string.IsNullOrEmpty(FavoriteFlag)&& FavoriteFlag=="1")
|
{
|
sql += $" and s.userid = '{CreatorId.Trim()}' and (CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END) = 1 ";
|
}
|
else {
|
if (!string.IsNullOrEmpty(AgentType))
|
{
|
sql += $" and a.agenttype = '{AgentType.Trim()}' ";
|
}
|
sql += $" and a.permission = 1 ";
|
}
|
int offset = (PageNum - 1) * PageSize;
|
sql += $" ORDER BY a.userno DESC LIMIT {PageSize} OFFSET {offset}";
|
|
return connection.Query<AgentDataPage>(sql).ToList();
|
}
|
}
|
|
public int GetAgentDataCount(string Keyword, string AgentType, string CreatorId, string FavoriteFlag)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = $"SELECT COUNT(*) FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE 1=1 ";
|
if (!string.IsNullOrEmpty(Keyword))
|
{
|
sql += $" and a.name like '%{Keyword.Trim()}%' ";
|
}
|
if (!string.IsNullOrEmpty(FavoriteFlag) && FavoriteFlag == "1")
|
{
|
sql += $" and s.userid = '{CreatorId.Trim()}' and (CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END) = 1 ";
|
}
|
else
|
{
|
if (!string.IsNullOrEmpty(AgentType))
|
{
|
sql += $" and a.agenttype = '{AgentType.Trim()}' ";
|
}
|
sql += $" and a.permission = 1 ";
|
}
|
sql += $" ORDER BY modificationtime DESC ";
|
int Count = connection.Query<int>(sql).FirstOrDefault();
|
return Count;
|
}
|
}
|
|
public List<AgentDataPage> GetMyAgentDataList(string Keyword, int PageNum, int PageSize, string AgentType, string CreatorId)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = $"SELECT a.id,a.name,a.prompt,a.permission,a.creationtime,a.modificationtime,a.avatarlink,a.creatorid,a.agenttype,a.description,a.userno,a.questionno,a.modelapi,a.modelkey,a.systemrouting,CASE WHEN s.agentid IS NOT NULL THEN 1 ELSE 0 END AS favoriteflag FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE a.creatorid = '{CreatorId.Trim()}' ";
|
if (!string.IsNullOrEmpty(Keyword))
|
{
|
sql += $" and a.name like '%{Keyword.Trim()}%' ";
|
}
|
if (!string.IsNullOrEmpty(AgentType))
|
{
|
sql += $" and a.agenttype = '{AgentType.Trim()}' ";
|
}
|
|
int offset = (PageNum - 1) * PageSize;
|
sql += $" ORDER BY a.userno DESC LIMIT {PageSize} OFFSET {offset}";
|
|
return connection.Query<AgentDataPage>(sql).ToList();
|
}
|
}
|
public int GetMyAgentDataCount(string Keyword, string AgentType, string CreatorId)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = $"SELECT COUNT(*) FROM agentdata a LEFT JOIN staragent s ON a.id = s.agentid AND s.userid = '{CreatorId}' WHERE a.creatorid = '{CreatorId.Trim()}' ";
|
if (!string.IsNullOrEmpty(Keyword))
|
{
|
sql += $" and a.name like '%{Keyword.Trim()}%' ";
|
}
|
if (!string.IsNullOrEmpty(AgentType))
|
{
|
sql += $" and a.agenttype = '{AgentType.Trim()}' ";
|
}
|
sql += $" ORDER BY modificationtime DESC ";
|
int Count = connection.Query<int>(sql).FirstOrDefault();
|
return Count;
|
}
|
}
|
|
public bool DelAgentDataById(int id)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = $"delete from agentdata where id={id}";
|
connection.Open();
|
return connection.Execute(sql) > 0;
|
}
|
}
|
|
public bool UpdateAgentdata(AgentDataPage agentDataPage)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = @"
|
UPDATE agentdata
|
SET
|
name = @Name,
|
prompt = @Prompt,
|
permission = @Permission,
|
agenttype = @AgentType,
|
modificationTime = NOW(),
|
avatarLink = @AvatarLink,
|
description = @Description,
|
modelapi = @ModelApi,
|
modelkey = @ModelKey,
|
systemrouting = @SystemRouting
|
WHERE id = @Id";
|
|
var parameters = new
|
{
|
Name = agentDataPage.Name,
|
Prompt = agentDataPage.Prompt,
|
Permission = agentDataPage.Permission,
|
AvatarLink = agentDataPage.AvatarLink,
|
Id = agentDataPage.Id,
|
AgentType = agentDataPage.AgentType,
|
Description = agentDataPage.Description,
|
ModelApi = agentDataPage.ModelApi,
|
ModelKey = agentDataPage.ModelKey,
|
SystemRouting = agentDataPage.SystemRouting
|
};
|
|
connection.Open(); // 打开数据库连接
|
int affectedRows = connection.Execute(sql, parameters); // 执行更新操作
|
return affectedRows > 0;
|
}
|
}
|
|
public bool AddAgentData(AgentDataPage agentDataPage)
|
{
|
using (IDbConnection connection = new MySqlConnection(connectionString))
|
{
|
var sql = @"
|
INSERT INTO agentdata (name, prompt, permission, creationtime, modificationtime, avatarLink, creatorid,agenttype,description,modelapi,modelkey,systemrouting)
|
VALUES ( @Name, @Prompt, @Permission,NOW(), NOW(), @AvatarLink, @CreatorId,@AgentType,@Description,@ModelApi,@ModelKey,@SystemRouting)";
|
|
var parameters = new
|
{
|
Name = agentDataPage.Name,
|
Description = agentDataPage.Description,
|
Permission = agentDataPage.Permission,
|
AvatarLink = agentDataPage.AvatarLink,
|
CreatorId = agentDataPage.CreatorId,
|
AgentType = agentDataPage.AgentType,
|
Prompt = agentDataPage.Prompt,
|
ModelApi = agentDataPage.ModelApi,
|
ModelKey = agentDataPage.ModelKey,
|
SystemRouting = agentDataPage.SystemRouting
|
};
|
|
connection.Open(); // 打开数据库连接
|
int affectedRows = connection.Execute(sql, parameters); // 执行更新操作
|
return affectedRows > 0;
|
}
|
}
|
|
}
|
}
|
