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


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


        public int CountByCGId(int CGId)
        {
            string sql = "select COUNT(*) from dbo.CarCompanyAndTouristGuide where CGId=@CGId";
            SqlParameter[] parmet = new SqlParameter[] {
           new SqlParameter("@CGId",CGId)
           };

            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parmet));
            if (obj > 0)
            {
                return obj;
            }
            else
            {
                return 0;
            }

        }


        public List<CarCompanyAndTouristGuide> CarCompanyAndTouristGuideList(int CGId)
        {
            string sql = "select * from CarCompanyAndTouristGuide where CGId =@CGId and isdel=0 order by id desc";
            SqlParameter[] parameter = new SqlParameter[]{
                                new SqlParameter("@CGId",CGId)
            };
            return excuteSql(sql, parameter);
        }

        public CarCompanyAndTouristGuide CarCompanyAndTouristGuide(int CGId)
        {
            string sql = "select * from CarCompanyAndTouristGuide where CGId =@CGId and isdel=0 order by id desc";
            SqlParameter[] parameter = new SqlParameter[]{
                                new SqlParameter("@CGId",CGId)
            };
            return excuteType(sql, parameter);
        }

        public CarCompanyAndTouristGuide CarCompanyAndTouristGuideById(int id)
        {
            string sql = "select * from CarCompanyAndTouristGuide where id =@id and isdel=0 order by id desc";
            SqlParameter[] parameter = new SqlParameter[]{
                                new SqlParameter("@id",id)
            };
            return excuteType(sql, parameter);
        }

        public CarCompanyAndTouristGuide GetCCATG(int CGId)
        {
            string sql = "select * from CarCompanyAndTouristGuide where CGId =@CGId and isdel=0 order by id desc";
            SqlParameter[] parameter = new SqlParameter[]{
                                new SqlParameter("@CGId",CGId)
            };

            return excuteType(sql, parameter);

            //return excuteType(sql, parameter);
        }



        /// <summary>
        /// 新增
        /// 20210828 贾文滔
        /// </summary>
        /// <param name="CarCompanyAndTouristGuide"></param>
        /// <returns></returns>
        public bool Add(CarCompanyAndTouristGuide ctggd)
        {
            string sql = "insert into CarCompanyAndTouristGuide values(@CGId,@CarType,@Price,@Unit,@CompanyOrGuide,@Operators,@OperatorDate,@Remark,@Currency,@IsDel)";


            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@CGId",ctggd.CGId),
                new SqlParameter("@CarType",ctggd.CarType),
                new SqlParameter("@Price",ctggd.Price),
                new SqlParameter("@Unit",ctggd.Unit),
                new SqlParameter("@CompanyOrGuide",ctggd.CompanyOrGuide),
                new SqlParameter("@Operators",ctggd.Operators),
                new SqlParameter("@OperatorDate",ctggd.OperatorDate),
                new SqlParameter("@Remark",ctggd.Remark),
                new SqlParameter("@Currency",ctggd.Currency),
                new SqlParameter("@IsDel",ctggd.IsDel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 批量添加方法
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool AddCarCompanyAndTouristGuide(List<CarCompanyAndTouristGuide> list)
        {
            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            // SqlTransaction trans = cmd.Connection.BeginTransaction();

            try
            {
                foreach (CarCompanyAndTouristGuide ctg in list)
                {
                    if (ctg.CarType != null)
                    {
                        cmd.CommandText = "insert into CarCompanyAndTouristGuide values(" + ctg.CGId + ",'" + ctg.CarType + "'," + ctg.Price + ",'" + ctg.Unit + "'," + ctg.CompanyOrGuide + "," + ctg.Operators + ",'" + ctg.OperatorDate + "','" + ctg.Remark + ",'" + ctg.Currency + "'," + ctg.IsDel + "')";
                    }
                    else
                    {
                        cmd.CommandText = "insert into CarCompanyAndTouristGuide values(" + ctg.CGId + ",' '," + ctg.Price + ",'" + ctg.Unit + "'," + ctg.CompanyOrGuide + "," + ctg.Operators + ",'" + ctg.OperatorDate + "','" + ctg.Remark + ",'" + ctg.Currency + "'," + ctg.IsDel + "')";
                    }
                    cmd.ExecuteNonQuery();
                }
                //trans.Commit();
                cmd.Connection.Close();
                return true;
            }
            catch (Exception ex)
            {
                string error = ex.Message.ToString();
                // trans.Rollback();
                cmd.Connection.Close();

                return false;
            }
        }


        /// <summary>
        /// 新增
        /// 20210828 贾文滔
        /// </summary>
        /// <param name="CarCompanyAndTouristGuide"></param>
        /// <returns></returns>
        public bool Edit(CarCompanyAndTouristGuide ctggd)
        {
            string sql = "update CarCompanyAndTouristGuide set CGId=@CGId,CarType=@CarType,Price=@Price,Unit=@Unit," +
                          "CompanyOrGuide=@CompanyOrGuide,Operators=@Operators,OperatorDate=@OperatorDate,Remark=@Remark" +
                          "Currency=@Currency,IsDel=@IsDel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@CGId",ctggd.CGId),
                new SqlParameter("@CarType",ctggd.CarType),
                new SqlParameter("@Price",ctggd.Price),
                new SqlParameter("@Unit",ctggd.Unit),
                new SqlParameter("@CompanyOrGuide",ctggd.CompanyOrGuide),
                new SqlParameter("@Operators",ctggd.Operators),
                new SqlParameter("@OperatorDate",ctggd.OperatorDate),
                new SqlParameter("@Remark",ctggd.Remark),
                new SqlParameter("@Currency",ctggd.Currency),
                new SqlParameter("@IsDel",ctggd.IsDel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }


        /// <summary>
        /// 批量更新方法
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdateCarCompanyAndTouristGuide(List<CarCompanyAndTouristGuide> list)
        {
            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            // SqlTransaction trans = cmd.Connection.BeginTransaction();

            try
            {
                foreach (CarCompanyAndTouristGuide ctg in list)
                {
                    if (ctg.CarType != null)
                    {
                        cmd.CommandText = "update CarCompanyAndTouristGuide set CarType= '" + ctg.CarType + "', Price = " + ctg.Price + ", Unit = '" + ctg.Unit + "',CompanyOrGuide = " + ctg.CompanyOrGuide + ",Operators = " + ctg.Operators + ",OperatorDate = '" + ctg.OperatorDate + "',Remark = '" + ctg.Remark + "',Currency = '" + ctg.Currency + "' where id = " + ctg.Id + "";
                    }
                    else
                    {
                        cmd.CommandText = "update CarCompanyAndTouristGuide set  CarType= '" + ctg.CarType + "', Price = " + ctg.Price + ", Unit = '" + ctg.Unit + "',CompanyOrGuide = " + ctg.CompanyOrGuide + ",Operators = " + ctg.Operators + ",OperatorDate = '" + ctg.OperatorDate + "',Remark = '" + ctg.Remark + "',Currency = '" + ctg.Currency + "' where Cgid = " + ctg.Id + "";
                    }
                    cmd.ExecuteNonQuery();
                }
                //trans.Commit();
                cmd.Connection.Close();
                return true;
            }
            catch
            {
                // trans.Rollback();
                cmd.Connection.Close();
                return false;
            }
        }

        /// <summary>
        /// 批量更新方法
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdateCarCompanyAndTouristGuide2(List<CarCompanyAndTouristGuide> list)
        {
            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            // SqlTransaction trans = cmd.Connection.BeginTransaction();

            try
            {
                foreach (CarCompanyAndTouristGuide ctg in list)
                {
                    if (ctg.CarType != null)
                    {
                        cmd.CommandText = "update CarCompanyAndTouristGuide set CarType= '" + ctg.CarType + "', Price = " + ctg.Price + ", Unit = '" + ctg.Unit + "',CompanyOrGuide = " + ctg.CompanyOrGuide + ",Operators = " + ctg.Operators + ",OperatorDate = '" + ctg.OperatorDate + "',Remark = '" + ctg.Remark + "',Currency = '" + ctg.Currency + "' where cgid = " + ctg.Id + "";
                    }
                    else
                    {
                        cmd.CommandText = "update CarCompanyAndTouristGuide set  CarType= '" + ctg.CarType + "', Price = " + ctg.Price + ", Unit = '" + ctg.Unit + "',CompanyOrGuide = " + ctg.CompanyOrGuide + ",Operators = " + ctg.Operators + ",OperatorDate = '" + ctg.OperatorDate + "',Remark = '" + ctg.Remark + "',Currency = '" + ctg.Currency + "' where cgid = " + ctg.Id + "";
                    }
                    cmd.ExecuteNonQuery();
                }
                //trans.Commit();
                cmd.Connection.Close();
                return true;
            }
            catch
            {
                // trans.Rollback();
                cmd.Connection.Close();
                return false;
            }
        }
    }
}