01-Siyuan-MesProject/DataMirrorTools_Simulator/BasicOperation.OSql/OSql/SqlProject.cs

using Global;
using njc = Newtonsoft.Json.JsonConvert;
using System;
using System.Collections.Generic;
using System.Data;
//using wfmt = System.Windows.Forms.MessageBox;
using System.IO;
using System.Text;
using static DtWebApiDT.DataAccess;
//using static SqlManage.ExecSql;
using Newtonsoft.Json;

/// <summary>
/// 
/// </summary>
namespace DTTest
{
    /// <summary>
    /// 对于数据库的操作
    /// </summary>
    public partial class SqlOperation
    {
        /// <summary>
        /// 项目的操作
        /// </summary>
        public class SqlProject
        {
            /// <summary>
            /// 查看所有项目
            /// </summary>
            /// <returns></returns>
            public static DataTable SelectProjectAll()
            {
                string sql = $"SELECT [CurrentProject],[ProjectName],[OperationTime],[ProjectCode] FROM [ProjectList]" +
                    $" ORDER BY [OperationTime] DESC";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return ds.Tables[0];
            }

            public static DataTable SelectProjectAll(List<String> projectList)
            {
                var projectListStr = ConvertToStr(projectList);
                string sql = $"SELECT [CurrentProject],[ProjectName],[OperationTime],[ProjectCode] FROM [ProjectList]" +
                    $" WHERE [ProjectCode] IN ({projectListStr})" +
                    $" ORDER BY [OperationTime] DESC";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return ds.Tables[0];
            }
            public static string ConvertToStr(List<String> craftCodeList)
            {
                var craftCodeListTemp = new List<String>();
                foreach (string craftCode in craftCodeList)
                {
                    craftCodeListTemp.Add("'" + craftCode + "'");
                }
                return string.Join(",", craftCodeListTemp.ToArray());
            }
            /// <summary>
            /// 添加项目
            /// </summary>
            /// <param name="projectName"></param>
            /// <returns></returns>
            public static DataTable AddProject(string projectName)
            {
                //1.[ProjectList]增加一条记录
                string getdate = " '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' ";

                string sql =
                    @" DECLARE @ProjectName NVARCHAR(50) = '" + projectName + "'" +

                    @" DECLARE @AddProjectCode NVARCHAR(50) = '" + OutGUID.Get_CS_GUID() + "'" +
                    @" INSERT INTO [ProjectList]([ProjectName],[OperationTime],[ProjectCode]) VALUES(@ProjectName,"+getdate+",@AddProjectCode)" +

                    @" UPDATE [ProjectList] SET [CurrentProject] = 'False'" +
                    @" UPDATE [ProjectList] SET [CurrentProject] = 'True' WHERE [ProjectCode] = @AddProjectCode";

                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return SelectProjectAll();
            }













            /// <summary>
            /// 查看项目是存在
            /// </summary>
            /// <param name="projectName"></param>
            /// <returns></returns>
            public static int SelectProjectExist(string projectName)
            {
                string sql =
                    @" DECLARE @ProjectName NVARCHAR(50) = '" + projectName + "'" +
                    @" SELECT COUNT(1) FROM [ProjectList] WHERE [ProjectName] = @ProjectName";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return int.Parse((ds.Tables[0]).Rows[0][0].ToString());
            }

            /// <summary>
            /// 修改成当前项目
            /// </summary>
            /// <param name="projectCode"></param>
            public static DataTable SetCurrProject(string projectCode)
            {
                string sql =
                    @" DECLARE @ProjectCode NVARCHAR(50) = '" + projectCode + "'" +

                    @" UPDATE [ProjectList] SET [CurrentProject] = 'False'" +
                    @" UPDATE [ProjectList] SET [CurrentProject] = 'True' WHERE [ProjectCode] = @ProjectCode";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return SelectProjectAll();
            }

            /// <summary>
            /// 修改选中的项目信息
            /// </summary>
            /// <param name="projectCode"></param>
            /// <param name="projectName"></param>
            /// <param name="url"></param>
            public static void Update_ProjectName(string projectCode, string projectName)
            {
                string sql =
                    @" DECLARE @ProjectCode NVARCHAR(50) = '" + projectCode + "'" +
                    @" DECLARE @ProjectName NVARCHAR(50) = '" + projectName + "'" +
                    @" UPDATE [ProjectList] SET [ProjectName] = @ProjectName WHERE [ProjectCode] = @ProjectCode";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
            }



            public static void UpdateProjectParamConfig(string projectCode, string paramConfig)
            {
                string sql =
                    @" DECLARE @ProjectCode NVARCHAR(50) = '" + projectCode + "'" +
                    @" DECLARE @ParamConfig NVARCHAR(MAX) = '" + paramConfig + "'" +
                    @" UPDATE [ProjectList] SET [ParamConfig] = @ParamConfig WHERE [ProjectCode] = @ProjectCode";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
            }

            /// <summary>
            /// 查看当前项目的参数配置
            /// 2021.2.24,改
            /// </summary>
            /// <param name="projectCode"></param>
            /// <returns></returns>
            public static void Refresh_Project_Para(string projectCode)
            {
                ExecuteDataset(
                    $" SELECT TOP 1" +
                    $"  [ProjectCode]" +
                    $" ,[ProjectName]" +
                    $" ,ISNULL([ParamConfig],'-1') AS [ParamConfig]" +
                    $" FROM [ProjectList] WHERE [ProjectCode] = '{projectCode}'"
                    , PP.ConnectionStr, out DataSet ds_Project, out string errMsg_Project);

                var dt_Project = ds_Project.Tables[0];
                if (dt_Project.Rows.Count < 1)
                    UpdateProjectName("-1", "空项目");
                else
                    UpdateProjectName(projectCode, dt_Project.Rows[0]["ProjectName"]);

                try
                {
                    var pcpStr = dt_Project.Rows[0]["ParamConfig"].ToString();
                    PP.PCP = njc.DeserializeObject<PCP_Json>(pcpStr);
                }
                catch (Exception err)
                {
                    LogNet.LogisTrac.WriteLog(typeof(SqlOperation), err);

                    var paramConfig = njc.SerializeObject(new PCP_Json());
                    UpdateProjectParamConfig(projectCode, paramConfig);
                    Refresh_Project_Para(projectCode);
                }

            }



            /// <summary>
            /// 2021.11.23.LLX
            /// </summary>
            /// <param name="logicalBlockData"></param>
            public static void Update_ProjectLogicalBlockData(string logicalBlockData)
            {
                string sql =
                    $" UPDATE [ProjectList] SET [LogicalBlockData] = '{logicalBlockData}' WHERE [ProjectCode] = '{PP.ProjectCode}'";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
            }

            /// <summary>
            /// 2021.11.23.LLX
            /// </summary>
            public static string Select_ProjectLogicalBlockData()
            {
                string sql =
                    $" SELECT [LogicalBlockData] FROM [ProjectList] WHERE [ProjectCode] = '{PP.ProjectCode}'";
                ExecuteDataset(sql, PP.ConnectionStr, out DataSet ds, out string errorMessage);
                return ds.Tables[0].Rows[0][0].ToString();
            }



        }


    }
}