QZTLApi/QZWebApi/Models/SqlSugarHelper.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;

namespace QZWebApi.Models
{
    public class SqlSugarHelper
    {
        public static string Sqlconn = ConfigurationManager.AppSettings["ConnectionString"].ToString();

        public DataTable GetPeisInfo(string planUserId)
        {
            SqlSugarClient WebDb = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Sqlconn,
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true
            });

            var dt = WebDb.Ado.GetDataTable($"select a.patient_register_id,b.id_no,a.summary_date from patient_register as a left join patient as b on a.patient_id=b.patient_id  where a.third_register_id='{planUserId}' and complete_flag='3'");

            return dt;

        }

        /// <summary>
        /// 获取总检综述
        /// </summary>
        /// <param name="planUserId"></param>
        /// <returns></returns>
        public string GetSumSummary(string planUserId)
        {
            StringBuilder sb = new StringBuilder();

            SqlSugarClient WebDb = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Sqlconn,
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true
            });


            var dt = WebDb.Ado.GetDataTable($"select b.sum_summary_header_id,b.summary_title from patient_register as a inner join sum_summary_header as b " +
               $"on a.patient_register_id=b.patient_register_id where a.third_register_id='{planUserId}' and complete_flag='3'  order by b.display_order");

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    sb.Append($"  *  {row["summary_title"].ToString()}：");
                    var dt2 = WebDb.Ado.GetDataTable($"select b.summary_content from  sum_summary_header as a " +
            $"left join sum_summary_content as b on a.sum_summary_header_id=b.sum_summary_header_id  where a.sum_summary_header_id='{row["sum_summary_header_id"].ToString()}' order by b.display_order");
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow row2 in dt2.Rows)
                        {
                            sb.Append($"{row2["summary_content"].ToString()}");
                            //if (dt2.Rows.IndexOf(row2) == dt2.Rows.Count - 1)
                            //{
                            //    sb.Append($"{row2["summary_content"].ToString()}");
                            //}
                            //else
                            //{
                            //    sb.Append($"{row2["summary_content"].ToString()}");
                            //}

                        }
                    }
                }

            }
            return sb.ToString();
        }


        /// <summary>
        /// 获取总检建议
        /// </summary>
        /// <param name="planUserId"></param>
        /// <returns></returns>
        public string GetSumSuggestion(string planUserId)
        {
            StringBuilder sb = new StringBuilder();

            SqlSugarClient WebDb = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Sqlconn,
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true
            });


            var dt = WebDb.Ado.GetDataTable($"select b.sum_suggestion_header_id,b.suggestion_title from patient_register as a inner join sum_suggestion_header as b " +
               $"on a.patient_register_id=b.patient_register_id where a.third_register_id='{planUserId}' and complete_flag='3'  order by b.display_order");

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    sb.Append($"  *  {row["suggestion_title"].ToString()}：");
                    var dt2 = WebDb.Ado.GetDataTable($"select b.suggestion_content from  sum_suggestion_header as a " +
            $"inner join sum_suggestion_content as b on a.sum_suggestion_header_id=b.sum_suggestion_header_id where a.sum_suggestion_header_id='{row["sum_suggestion_header_id"].ToString()}' order by b.display_order");
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow row2 in dt2.Rows)
                        {
                            sb.Append($"{row2["suggestion_content"].ToString()}");
                            //if (dt2.Rows.IndexOf(row2) == dt2.Rows.Count - 1)
                            //{
                            //    sb.Append($"{row2["suggestion_content"].ToString()}");
                            //}
                            //else
                            //{
                            //    sb.Append($"{row2["suggestion_content"].ToString()}");
                            //}

                        }
                    }
                }

            }
            return sb.ToString();
        }



        /// <summary>
        /// 获取项目、结果、以及明细
        /// </summary>
        /// <param name="planUserId"></param>
        /// <returns></returns>
        public List<TjjlPackages> GetRegisterCheck(string planUserId)
        {

            List<TjjlPackages> tjjlPackagesList = new List<TjjlPackages>();

            SqlSugarClient WebDb = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Sqlconn,
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true
            });


            var dt = WebDb.Ado.GetDataTable($"select b.register_check_id from patient_register as a left join register_check as b on a.patient_register_id=b.patient_register_id  " +
                $" where a.third_register_id='{planUserId}' and  a.complete_flag='3' ");

            if (dt.Rows.Count > 0)
            {

                foreach (DataRow row in dt.Rows)
                {
                    TjjlPackages tjjlPackages = new TjjlPackages();

                    var dt2 = WebDb.Ado.GetDataTable($"select sum(a.charge_price) as charge_price,STRING_AGG(b.asbitem_name, ',' ORDER BY b.asbitem_name) AS asbitem_name from register_check_asbitem as a " +
                        $"left join asbitem as b on a.asbitem_id = b.asbitem_id where a.register_check_id='{row["register_check_id"].ToString()}' group by register_check_id ");
                    if (dt2.Rows.Count > 0)
                    {
                        tjjlPackages.packageName = dt2.Rows[0]["asbitem_name"].ToString();
                        tjjlPackages.fee = Convert.ToDouble(Convert.ToDecimal(dt2.Rows[0]["charge_price"].ToString()) * 100);
                    }


                    var dt3 = WebDb.Ado.GetDataTable($"select summary from register_check_summary where register_check_id='{row["register_check_id"].ToString()}' ");
                    if (dt3.Rows.Count > 0)
                    {
                        string summary = "";

                        foreach (DataRow row3 in dt3.Rows)
                        {
                            if (dt3.Rows.IndexOf(row3) == dt3.Rows.Count - 1)
                            {
                                summary += $"{row3["summary"].ToString()},";
                            }
                            else
                            {
                                summary += $"{row3["summary"].ToString()}";
                            }
                        }
                        tjjlPackages.summary = summary;
                    }
                    else
                    {
                        tjjlPackages.summary = "";
                    }


                    //明细结果
                    var dt4 = WebDb.Ado.GetDataTable($"select b.price,b.item_name,a.result,a.reference_range_value,a.unit,c.data_input_prompt from register_check_item as a  " +
                        $"left join item as b on a.item_id=b.item_id  left join result_status as c on a.result_status_id=c.result_status_id " +
                        $"where a.register_check_id='{row["register_check_id"].ToString()}'");
                    if (dt4.Rows.Count > 0)
                    {
                        List<JkTjxxBean> jkTjxxBeanList = new List<JkTjxxBean>();
                        foreach (DataRow row4 in dt4.Rows)
                        {
                            JkTjxxBean jkTjxxBean = new JkTjxxBean();
                            jkTjxxBean.price = Convert.ToDouble(Convert.ToDecimal(row4["price"].ToString()) * 100);
                            jkTjxxBean.ysValue = row4["result"].ToString();
                            jkTjxxBean.tjxmMc = row4["item_name"].ToString();
                            jkTjxxBean.tjUnit = row4["unit"].ToString();
                            jkTjxxBean.reference = row4["reference_range_value"].ToString();

                            string remark = "-1";

                            if (row4["data_input_prompt"] != DBNull.Value)
                            {
                                if (row4["data_input_prompt"].ToString() == "")
                                {
                                    remark = "0";
                                }
                                else if (row4["data_input_prompt"].ToString() == "＋" || row4["data_input_prompt"].ToString() == "↑" || row4["data_input_prompt"].ToString() == "±")
                                {
                                    remark = "2";
                                }
                                else
                                {
                                    remark = "1";
                                }
                            }
                            jkTjxxBean.remark = remark;

                            jkTjxxBeanList.Add(jkTjxxBean);
                        }

                        tjjlPackages.items = jkTjxxBeanList;
                    }
                    else
                    {
                        tjjlPackages.items = new List<JkTjxxBean>();
                    }

                    if (tjjlPackages.items != null && tjjlPackages.items.Any() && string.IsNullOrWhiteSpace(tjjlPackages.summary))
                    {
                        tjjlPackagesList.Add(tjjlPackages);
                    }

                }
            }

            return tjjlPackagesList;
        }

        public DataTable GetPeisReport(string planUserId)
        {
            SqlSugarClient WebDb = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Sqlconn,
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true
            });

            var dt = WebDb.Ado.GetDataTable($"select report_file from patient_register   where third_register_id='{planUserId}' and complete_flag='3'");

            return dt;
        }

        public string GetImageBase64StringAsync(string pdfUrl)
        {
            string result = "";
            try
            {
                pdfUrl = pdfUrl.Replace(@"\", "/");

                WebClient webClient = new WebClient();
                byte[] byteArray = webClient.DownloadData(pdfUrl);
                result = Convert.ToBase64String(byteArray);

            }
            catch
            {
                result = "";
            }

            //if (!string.IsNullOrEmpty(result))
            //    result = "data:image/jpeg;base64," + result;
            return result;
        }
    }
}