ranJianKang/src/WebCyPaiBan/Controller/SQLService/zhiHui_Daily_eport_SQL.cs

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace WebCyPaiBan.Controller.SQLService
{
    public class zhiHui_Daily_eport_SQL
    {
        /// <summary>
        /// 新增数据
        /// </summary>
        /// <returns></returns>
        public bool Add(string param)
        {
            Guid guid = Guid.NewGuid();
            //{
            //"F0000036":"然会所（佳兆业店）",              //预约门店
            //"F0000021":"全息健康评估",                    //调理名称
            //"F0000024":"肖然",                            //疗愈师
            //"F0000022":"2022/6/6 15:35:00",               //开始时间
            //"F0000023":"2022/6/6 15:55:00",               //结束时间
            //"F0000027":"豁然","DataList":[{"F0000021":"全息健康评估","F0000024":"77be6a06-97ba-4a91-a7f9-49310d65366f","F0000022":"2022/6/6 15:35:00","F0000023":"2022/6/6 15:55:00","F0000027":"豁然"}]}
            //书写调用第三方接口方法
            JObject ResultJson = JsonConvert.DeserializeObject<JObject>(param.ToString());
            string id = guid.ToString();
            string Stores = ResultJson["F0000036"].ToString();      //预约门店
            string Room = ResultJson["F0000027"].ToString();        //房间号
            string TLName = ResultJson["F0000021"].ToString();      //调理名称
            string Name = ResultJson["F0000024"].ToString();        //疗愈师
            string StartDate = ResultJson["F0000022"].ToString();   //开始时间
            string EndDate = ResultJson["F0000023"].ToString();     //结束时间

            string sql = "insert into Daily_report values ('"+id+"','"+ Stores + "','" + Room + "','" + TLName + "','" + Name + "','"+ StartDate + "','"+ EndDate + "')";
            bool _bool = DB_Helper.Excute1(sql);
            return _bool;
        }

        /// <summary>
        /// 查询（默认）
        /// </summary>
        /// <param name="StartDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <returns></returns>
        public DataTable SetDate(DateTime StartDate, DateTime EndDate)
        {
            string sql = "";
            if (StartDate == null && EndDate == null)
            {
                string StartDates = DateTime.Now.ToString("yyyy-MM-dd");
                string EndDates = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
                sql = "select Stores,TLName,Name,StartDate,EndDate from Daily_report where StartDate >= '#{StartDates}'and EndDate < '#{EndDates}'";
            }
            if(StartDate == null && EndDate != null)
            {
                sql = "select Stores,TLName,Name,StartDate,EndDate from Daily_report where EndDate < '#{EndDate}'";
            }
            if (StartDate != null && EndDate == null)
            {
                sql = "select Stores,TLName,Name,StartDate,EndDate from Daily_report where StartDate >= '#{StartDate}'";
            }
            if (StartDate != null && EndDate != null)
            {
                sql = "select Stores,TLName,Name,StartDate,EndDate from Daily_report where StartDate >= '#{StartDate}'and EndDate < '#{EndDate}'";
            }
            DataTable dt = DB_Helper.Select1(sql);
            return dt;
        }

        /// <summary>
        /// 查询门店条件
        /// </summary>
        /// <param name="Stores">门店</param>
        /// <returns></returns>
        public DataTable SetStores(string Room,string Stores = "",string name="", string StartDate = "")
        {
            string sql = "";
            if(string.IsNullOrEmpty(StartDate))
            StartDate = DateTime.Now.ToString("yyyy-MM-dd");

            if (string.IsNullOrEmpty(Stores))
            {
                if (string.IsNullOrEmpty(name))
                {
                    sql = "select * from Daily_report where Room = '" + Room + "' and  StartDate >='" + StartDate + "'and EndDate < '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "'and Room is not null";
                }
                else
                {
                    sql = "select * from Daily_report where Room = '" + Room + "' and Name = '" + name + "'  and  StartDate >='" + StartDate + "'and EndDate < '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "'and Room is not null";
                }
            }
            else
            {
                if (string.IsNullOrEmpty(name))
                {
                    sql = "select * from Daily_report where Stores = '" + Stores + "'and Room = '" + Room + "' and  StartDate >='" + StartDate + "'and EndDate < '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "'and Room is not null";
                }
                else
                {
                    sql = "select * from Daily_report where Stores = '" + Stores + "'and Room = '" + Room + "' and Name = '" + name + "' and  StartDate >='" + StartDate + "'and EndDate < '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "'and Room is not null";
                }
                    
            }
            
            DataTable dt = DB_Helper.Select1(sql);
            return dt;
        }





        /// <summary>
        /// 查询所有条件
        /// </summary>
        /// <param name="Stores">门店名称</param>
        /// <param name="TLName">调理名称</param>
        /// <param name="Name">调理师</param>
        /// <param name="StartDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <returns></returns>
        public DataTable SetStartDate(string Stores,string TLName, string Name, DateTime StartDate,DateTime EndDate)
        {
            string sql = "select Stores,TLName,Name,StartDate,EndDate from Daily_report where Stores='#{Stores}'and TLName = '#{TLName}'and Name = '#{Name}'and StartDate = '#{StartDate}'and EndDate = '#{EndDate}'";
            DataTable dt = DB_Helper.Select1(sql);
            return dt;
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <returns></returns>
        public DataTable SetRoom(string Stores = "",string StartDate = "")
        {
            string sql = "";
            if (string.IsNullOrEmpty(StartDate))
            StartDate = DateTime.Now.ToString("yyyy-MM-dd");

            if (string.IsNullOrEmpty(Stores))
            {
                sql = "select distinct Room from Daily_report where StartDate >='" + StartDate + "' and EndDate <= '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "' and Room is not null";

            }
            else
            {
                sql = "select distinct Room from Daily_report where StartDate >='" + StartDate + "' and EndDate <= '" + (Convert.ToDateTime(StartDate).AddDays(+1)).ToString("yyyy-MM-dd") + "' and Stores = '" + Stores + "'and Room is not null";

            }

            DataTable dt = DB_Helper.Select1(sql);
            return dt;
        }

        /// <summary>
        /// 获取人员信息
        /// </summary>
        /// <param name="Stores">门店</param>
        /// <returns></returns>
        public DataTable SetName()
        {
            string sql = "select distinct Name from Daily_report";
            DataTable dt = DB_Helper.Select1(sql);
            return dt;
        }

    }
}