using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
    public class ViewCarDataAndGuideService
    {

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<ViewCarDataAndGuide> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<ViewCarDataAndGuide>.excuteSql(new ViewCarDataAndGuide(), "ViewCarDataAndGuide", sql, CommandType.Text, param);
        }


        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        ViewCarDataAndGuide excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<ViewCarDataAndGuide> cList = excuteSql(sql, param);
            //判断集合是否为空
            if (cList == null || cList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return cList[0];
        }

        /// <summary>
        /// 查询信息
        ///--TouristGuideGroundData导游地接表  comanyorguide=2
        /// </summary>
        /// <returns>返回空或者单个对象信息</returns>
        public List<ViewCarDataAndGuide> getBy(string unitArea, string unitName, string contact, string tel)
        {
            string sqlWhere = "";
            if (unitArea != "")
            {
                if (unitArea.Contains(','))
                    unitArea = unitArea.Replace(',', ' ');
                else if (unitArea.Contains('，'))
                    unitArea = unitArea.Replace('，', ' ');
                else if (unitArea.Contains('、'))
                    unitArea = unitArea.Replace('、', ' ');


                if (unitArea.Contains(" "))
                {
                    string sqlCity = " and (";
                    string[] sqlName = unitArea.Split(' ');
                    for (int i = 0, len = sqlName.Length; i < len; i++)
                    {
                        if (sqlName[i] != "")
                        {
                            if (i == len - 1)
                                sqlCity += " UnitArea like '%" + sqlName[i] + "%') ";
                            else
                                sqlCity += " UnitArea like '%" + sqlName[i] + "%' or";
                        }

                    }
                    sqlWhere += sqlCity;
                }
                else
                {
                    sqlWhere += " and UnitArea like '%" + unitArea + "%'";
                }
            }
               
            if (unitName != "")
                sqlWhere += " and UnitName like '%" + unitName + "%'";
            if (contact != "")
                sqlWhere += " and Contact like '%" + contact + "%'";
            if (tel != "")
                sqlWhere += " and Tel like '%" + tel + "%'";
            else
                sqlWhere += "";

            //调用获取单个对象的方法
            return excuteSql("select a.id,a.UnitArea,a.UnitName,a.Contact,a.Tel,a.Score,a.OperatorDate,b.Price,b.Currency from TouristGuideGroundData a join CarCompanyAndTouristGuide b on a.Id = b.CGID where a.IsDel = 0 " + sqlWhere + " order by a.OperatorDate desc");
        }



        /// <summary>
        /// 查询车公司
        /// --CarData车公司表  comanyorguide=1
        /// </summary>
        /// <returns>返回空或者单个对象信息</returns>
        public List<ViewCarDataAndGuide> getByCarType(string unitArea, string unitName, string contact, string tel,string cartype)
        {
            string sqlWhere = "";
            if (unitArea != "")
                sqlWhere += " and UnitArea like '%" + unitArea + "%'";
            if (unitName != "")
                sqlWhere += " and UnitName like '%" + unitName + "%'";
            if (contact != "")
                sqlWhere += " and Contact like '%" + contact + "%'";
            if (tel != "")
                sqlWhere += " and Tel like '%" + tel + "%'";
            if (cartype != "")
                sqlWhere += " and CarType like '%" + tel + "%'";
            else
                sqlWhere += "";

            //调用获取单个对象的方法
            return excuteSql("select a.id,a.UnitArea,a.UnitName,a.Contact,a.Tel,a.Score,a.OperatorDate,b.Price,b.Currency from CarData a join CarCompanyAndTouristGuide b on a.Id = b.CGID where a.IsDel = 0 " + sqlWhere + " order by a.OperatorDate desc");
        }



    }
}