czx20241211接口更新

chenzx

2024-12-11 4043a2fe2103c7960b74acbccdcc876d11f83f0f

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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 ChatHistoryDAL : BaseMySQLDAL
    {
        public List<ChatHistoryDataPage> GetChatHistoryList(int UserId, int AgentId, string Keyword, int PageNum, int PageSize)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT * FROM  chathistorydata WHERE userid = '{UserId}' AND agentid = '{AgentId}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and content like '%{Keyword.Trim()}%' ";
                }
                int offset = (PageNum - 1) * PageSize;
                sql += $" ORDER BY id DESC  LIMIT {PageSize} OFFSET {offset}";
 
                return connection.Query<ChatHistoryDataPage>(sql).ToList();
            }
        }
 
        public int GetChatHistoryCount(int UserId, int AgentId, string Keyword)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT COUNT(*) FROM  chathistorydata WHERE userid = '{UserId}' AND agentid = '{AgentId}' ";
                if (!string.IsNullOrEmpty(Keyword))
                {
                    sql += $" and content like '%{Keyword.Trim()}%' ";
                }
                sql += $"  ORDER BY id DESC ";
                int Count = connection.Query<int>(sql).FirstOrDefault();
                return Count;
            }
        }
 
        public List<ChatHistoryDataPage> GetChatHistory6List(int UserId, int AgentId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"SELECT content,role FROM  chathistorydata WHERE userid ='{UserId}' AND agentid = '{AgentId}' ORDER BY id DESC LIMIT 6";
                return connection.Query<ChatHistoryDataPage>(sql).ToList();
            }
        }
 
        public int AddChatHistoryData(ChatHistoryDataPage chatHistoryDataPage)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = @"
            INSERT INTO chathistorydata (content, createtime, totaltokens, model, created, chatid, object, userid, agentid, role)
            VALUES (@Content, NOW(), @TotalTokens, @Model, @Created, @Chatid, @Object, @UserId, @AgentId, @Role)";
 
                var parameters = new
                {
                    Content = chatHistoryDataPage.Content,
                    TotalTokens = chatHistoryDataPage.TotalTokens,
                    Model = chatHistoryDataPage.Model,
                    Created = chatHistoryDataPage.Created,
                    Chatid = chatHistoryDataPage.Chatid,
                    Object = chatHistoryDataPage.Object,
                    UserId = chatHistoryDataPage.UserId,
                    AgentId = chatHistoryDataPage.AgentId,
                    Role = chatHistoryDataPage.Role,
                };
                connection.Open(); // 打开数据库连接
 
                // 执行插入操作
                int affectedRows = connection.Execute(sql, parameters);
                if (affectedRows > 0)
                {
                    // 获取最后插入的ID
                    var lastInsertIdSql = "SELECT LAST_INSERT_ID()";
                    var lastInsertId = connection.Query<int>(lastInsertIdSql).FirstOrDefault();
                    return lastInsertId;
                }
                return -1; // 如果没有行受影响，返回-1或其他错误代码
            }
        }
 
        public bool DelChatHistoryDataById(int Id)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                var sql = $"delete from chathistorydata where id={Id}";
                connection.Open();
                return connection.Execute(sql) > 0;
            }
        }
        public string GetPromptData(int id)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                return connection.QueryFirstOrDefault<string>($"SELECT prompt FROM agentdata WHERE id={id}");
            }
        }
 
        public bool UpdataUserNoById(int AgentId, int UserId)
        {
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                string sqlUpdateQuestion = $"UPDATE agentdata SET questionno = COALESCE(questionno, 0) + 1 WHERE id = {AgentId}";
                string sqlCount = $"SELECT COUNT(*) FROM chathistorydata WHERE userid = {UserId} and agentid={AgentId}";
                string sqlUpdateUser = $"UPDATE agentdata SET userno = COALESCE(userno, 0) + 1 WHERE id = {AgentId}";
                int count = 0;
                connection.Open();
                using (IDataReader reader = connection.ExecuteReader(sqlCount))
                {
                    if (reader.Read())
                    {
                        count = reader.GetInt32(0);
                    }
                }
                if (count == 0)
                {
                    connection.Execute(sqlUpdateUser);
                }
                return connection.Execute(sqlUpdateQuestion) > 0;
            }
        }
    }
}