using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.IO;

namespace BeginScreen
{
    public static class PublicMethod
    {
        public static DateTime ServerTime()
        {
            string sqlStr = "select getdate() as SysDate";
            DataTable dt = DBHelper.GetDataTable(sqlStr);
            return DateTime.Parse(DateTime.Parse(dt.Rows[0]["SysDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
        }

        /// <summary>
        ///  根据时间查询排程公告 
        /// </summary>
        /// <param name="dtBegin"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public static DataTable GetPlanNoticeNew(DateTime dtBegin, DateTime dtEnd, string OpeTime, string DeptId)
        {
            string strSql = "select * from (select ApplyId ,OperationRoom,OperationRoomId,planorder,ApplyDepName,SickBed,PatientName,'等待手术' [State],SQState,SZState,ApplyOperationInfoName OperationInfo,OperationBeginTime,ApplyOperationDoctor OperationDoctor,applyAnesthesiaDoctor AnesthesiaDoctor,ApplyTourNurse TourNurse,ApplyInstrumentNurse InstrumentNurse  from [dbo].[V_OperationRecordALL] where sqstate <6 and ((OrderOperationTime >='" + dtBegin.ToString("yyyy-MM-dd 00:00:00") + "' and OrderOperationTime<='" + dtEnd.ToString("yyyy-MM-dd 23:59:59") + "')) union select ApplyId ,OperationRoom,OperationRoomId,planorder,ApplyDepName,SickBed,PatientName,'手术中'[State],SQState,SZState,OperationInfoName OperationInfo,OperationBeginTime,OperationDoctorx OperationDoctor,AnesthesiaDoctor,TourNurse,InstrumentNurse  from [V_OperationRecordALL]  where SZstate in(1) and  ((InRoomTime >='" + dtBegin.ToString("yyyy-MM-dd 00:00:00") + "' and InRoomTime<='" + dtEnd.ToString("yyyy-MM-dd 23:59:59") + "')) and OutRoomTime is null union select ApplyId ,OperationRoom,OperationRoomId,planorder,ApplyDepName,SickBed,PatientName,'手术结束'[State],SQState,SZState,OperationInfoName OperationInfo,OperationBeginTime,OperationDoctorx OperationDoctor,AnesthesiaDoctor,TourNurse,InstrumentNurse  from [V_OperationRecordALL]  where   datediff(minute,CONVERT(DATETIME,outRoomTime,120),GETDATE())<" + OpeTime + ") as a   order by  OperationRoomId,planorder "; 
            return DBHelper.GetDataTable(strSql); 
        }

        public static DataTable GetPlanNoticeNew1(DateTime dtBegin, DateTime dtEnd, string OpeTime, string DeptId)
        {
            string strSql = "select * from (select ApplyId ,OperationRoom,ApplyDepName,SickBed,PatientName,'等待手术' [State],SQState,SZState,InRoomTime,OutRoomTime,OperationBeginTime,OperationEndTime,Pulse  from [dbo].[V_OperationRecordALL] where sqstate <6 and ((OrderOperationTime >='" + dtBegin.ToString("yyyy-MM-dd 00:00:00") + "' and OrderOperationTime<='" + dtEnd.ToString("yyyy-MM-dd 23:59:59") + "')) union select ApplyId ,OperationRoom,ApplyDepName,SickBed,PatientName,'手术中'[State],SQState,SZState,InRoomTime,OutRoomTime,OperationBeginTime,OperationEndTime,Pulse  from [V_OperationRecordALL]  where SZstate in(1) and  ((InRoomTime >='" + dtBegin.ToString("yyyy-MM-dd 00:00:00") + "' and InRoomTime<='" + dtEnd.ToString("yyyy-MM-dd 23:59:59") + "')) and OutRoomTime is null union select ApplyId ,OperationRoom,ApplyDepName,SickBed,PatientName,'手术结束'[State],SQState,SZState,InRoomTime,OutRoomTime,OperationBeginTime,OperationEndTime,Pulse  from [V_OperationRecordALL]  where   datediff(minute,CONVERT(DATETIME,outRoomTime,120),GETDATE())<" + OpeTime + ") as a   order by a.ApplyDepName collate Chinese_PRC_CS_AS_KS_WS,a.SQState desc,a.SZState desc"; 
            return DBHelper.GetDataTable(strSql);
        }


        public static DataTable SelectPlanedOpeByRoom(string whereSql)
        {
            try
            {
                string sql = "select ApplyDepName 科室, OperationRoom.Name  as 术间, PlanOrder  as 台次,case CONVERT(VARCHAR(16), PlanOrder ) when 1 then '10:00' else '接台' end as 时间,PatientName as 姓名,Sex as 性别,Age as 年龄, MdrecNo as 住院号,SickBed as 床号,ApplyDiagnoseInfoName as 术前诊断,ApplyOperationInfoName as 拟施手术,OperationDoctor as 手术者,Assistant1 as 一助,Assistant2 as 二助,Assistant3 as 三助,AnaesthesiaMethodName as 拟施麻醉,AnesthesiaDoctor as 麻醉医生,InstrumentNurse as 洗手,TourNurse as 巡回 from [dbo].[V_OperationFront] left join OperationRoom on OperationRoom.Id = OperationRoomId  where " + whereSql + "   Order By  ApplyDepName,OperationRoom.Id,PlanOrder" ;
                return DBHelper.GetDataTable(sql);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public static DataRow GetMessigeData()
        {
            string strSql = "select top 1 * from NoticeContent where  datediff(second,CONVERT(DATETIME,OperatorTime,120),GETDATE())<ReleaseTime   order by ID desc ";//
            DataTable dt = DBHelper.GetDataTable(strSql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            else
            {
                return null;
            }
        } 
        /// <summary>
        /// 查询科室
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDepartments()
        {
            string sql = "SELECT Id,Name,Name+'('+HelpCode+')' as NameHCode FROM Department where IsValid=1";
            return DBHelper.GetDataTable(sql);
        }

        /// <summary>
        /// 查询术者组人员
        /// </summary>
        /// <returns></returns>
        public static DataTable GetWorkers()
        {
            string sql = "select top 20 Id,Name,Name+'('+No+')'+'('+HelpCode+')' as NameHCode FROM Person where IsValid = 1";
            return DBHelper.GetDataTable(sql);
        }

    }
}