using Dapper;
using DataDAL.DBContext;
using Models.Models;
using SamplePre.Common;
using SamplePre.DAL.DBContext;
using SamplePre.Models.Ext;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Dapper.SqlMapper;

namespace SamplePre.DAL
{
    public class SOPDal
    {

        /// <summary>
        /// 获取标准下的动作，动作中包含参数
        /// </summary>
        /// <param name="standId"></param>
        /// <returns></returns>
        public List<sys_standard_action_ext> QueryStandardProcessByStandardId(string standId)
        {
            using (IDbConnection dbConnection = new DapperDBContext().Connection)
            {
                dbConnection.Open();

                string querySql = @"
                                    SELECT
	                                        a.*,
	                                        b.action_name,
	                                        b.action_unit,
	                                        e.data_value AS action_unit_name,
	                                        b.remark,
	                                        c.all_param 
                                        FROM
	                                        sys_standard_action a
	                                        LEFT JOIN sys_action b ON a.process_id = b.id
	                                        LEFT JOIN sys_dict e ON b.action_unit = e.id
	                                        LEFT JOIN (
	                                        SELECT
		                                        x.action_id,
		                                        x.action_seqno,
		                                        GROUP_CONCAT( CONCAT( y.title, '：', x.data_value, y.unit ) SEPARATOR '，' ) AS all_param 
	                                        FROM
		                                        sys_standard_action_parm x
		                                        LEFT JOIN sys_parm y ON x.parm_id = y.id 
	                                        WHERE
		                                        x.standard_id = @standId 
	                                        GROUP BY
		                                        x.action_id,
		                                        x.action_seqno 
	                                        ) c ON a.process_id = c.action_id 
	                                        AND a.process_no = c.action_seqno 
                                        WHERE
	                                        a.standard_id = @standId
                                        ORDER BY a.process_no
                                    ";

                List<sys_standard_action_ext> data = dbConnection.Query<sys_standard_action_ext>(querySql, new { standId = standId }).ToList();

                return data;

            }
        }

    }
}