OA2021/DAL/GroupCostParaService.cs

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


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

        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public GroupCostParameter GetById(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from GroupCostParameter where Id = @id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public GroupCostParameter GetByDiid(int diid)
        {
            //调用获取单个对象的方法
            return excuteType("select * from GroupCostParameter where Diid = @diid", new SqlParameter("@diid", diid));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public GroupCostParameter GetByDiidAndCostTypeA(int diid)
        {
            return excuteType("select * from GroupCostParameter where Diid = @diid and (CostType = 'A' OR CostType IS NULL) ", new SqlParameter("@diid", diid));
        }

        public GroupCostParameter GetByDiidAndCostTypeB(int diid)
        {
            return excuteType("select * from GroupCostParameter where Diid = @diid and (CostType =  'B')", new SqlParameter("@diid", diid));
        }


        public List<GroupCostParameter> GetAllByDiid(int diid)
        {
            return excuteSql("select * from GroupCostParameter where Diid = @diid", new SqlParameter("@diid", diid));
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<GroupCostParameter> GetAll()
        {
            return excuteSql("select * from GroupCostParameter where IsDel=0");
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public bool AddItaliaTran(GroupCostParameter hd)
        {
            string sql = "insert into GroupCostParameter values(@Diid,@Currency,@Rate,@Tax,@FFYS,@HotelXS,@JJCCB,@JJCXS,@JJCRS,@GWCCB,@GWCXS,@GWCRS,@HCPCB,@HCPRS,@HCPXS," +
                         "@CPCB,@CPRS,@CPXS,@BXCB,@BXRS,@BXXS,@HSCB,@HSRS,@HSXS,@VisaCB,@VisaXS,@VisaRS,@GWCB,@GWXS,@GWRS,@DJCB,@DJXS,@LYJCB,@LYJXS,@LYJRS,@SGRCB,@SGRNumber,@SGRXS" +
                         ",@TBRCB,@TBRNumber,@TBRXS,@JSESCB,@JSESNumber,@JSESXS,@SUITECB,@SUITENumber,@SUITEXS,@IsShare,@Oper,@OpTime,@CostType,@AstartTime,@AendTime,@Anumber,@BstartTime,@BendTime,@Bnumber)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",hd.Diid),
                new SqlParameter("@Currency",hd.Currency),
                new SqlParameter("@Rate",hd.Rate),
                new SqlParameter("@Tax",hd.Tax),
                new SqlParameter("@FFYS",hd.FFYS),
                new SqlParameter("@HotelXS",hd.HotelXS),
                new SqlParameter("@JJCCB",hd.JJCCB),
                new SqlParameter("@JJCXS",hd.JJCXS),
                new SqlParameter("@JJCRS",hd.JJCRS),
                new SqlParameter("@GWCCB",hd.GWCCB),
                new SqlParameter("@GWCXS",hd.GWCXS),
                new SqlParameter("@GWCRS",hd.GWCRS),
                new SqlParameter("@VisaCB",hd.VisaCB),
                new SqlParameter("@VisaXS",hd.VisaXS),
                new SqlParameter("@VisaRS",hd.VisaRS),
                new SqlParameter("@GWCB",hd.GWCB),
                new SqlParameter("@GWXS",hd.GWXS),
                new SqlParameter("@GWRS",hd.GWRS),
                new SqlParameter("@HCPCB",hd.HCPCB),
                new SqlParameter("@HCPRS",hd.HCPRS),
                new SqlParameter("@HCPXS",hd.HCPXS),
                new SqlParameter("@CPCB",hd.CPCB),
                new SqlParameter("@CPRS",hd.CPRS),
                new SqlParameter("@CPXS",hd.CPXS),
                new SqlParameter("@BXCB",hd.BXCB),
                new SqlParameter("@BXRS",hd.BXRS),
                new SqlParameter("@BXXS",hd.BXXS),
                new SqlParameter("@HSCB",hd.HSCB),
                new SqlParameter("@HSRS",hd.HSRS),
                new SqlParameter("@HSXS",hd.HSXS),
                new SqlParameter("@DJCB",hd.DJCB),
                new SqlParameter("@DJXS",hd.DJXS),
                new SqlParameter("@LYJCB",hd.LYJCB),
                new SqlParameter("@LYJXS",hd.LYJXS),
                new SqlParameter("@LYJRS",hd.LYJRS),
                new SqlParameter("@SGRCB",hd.SGRCB),
                new SqlParameter("@SGRNumber",hd.SGRNumber),
                new SqlParameter("@SGRXS",hd.SGRXS),
                new SqlParameter("@TBRCB",hd.TBRCB),
                new SqlParameter("@TBRNumber",hd.TBRNumber),
                new SqlParameter("@TBRXS",hd.TBRXS),
                new SqlParameter("@JSESCB",hd.JSESCB),
                new SqlParameter("@JSESNumber",hd.JSESNumber),
                new SqlParameter("@JSESXS",hd.JSESXS),
                new SqlParameter("@SUITECB",hd.SUITECB),
                new SqlParameter("@SUITENumber",hd.SUITENumber),
                new SqlParameter("@SUITEXS",hd.SUITEXS),
                new SqlParameter("@IsShare",hd.IsShare),
                new SqlParameter("@Oper",hd.Oper),
                new SqlParameter("@OpTime",hd.OpTime),

                new SqlParameter("@CostType",hd.CostType)  ,
                new SqlParameter("@AstartTime",hd.AstartTime)  ,
                new SqlParameter("@AendTime",hd.AendTime)  ,
                new SqlParameter("@Anumber",hd.Anumber)  ,
                new SqlParameter("@BstartTime",hd.BstartTime)  ,
                new SqlParameter("@BendTime",hd.BendTime)  ,
                new SqlParameter("@Bnumber",hd.Bnumber)
            };

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

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditItaliaTran(GroupCostParameter hd)
        {
            string sql = "update GroupCostParameter set Diid=@Diid,Currency=@Currency,Rate=@Rate,Tax=@Tax,FFYS=@FFYS,HotelXS=@HotelXS,JJCCB=@JJCCB,JJCXS=@JJCXS," +
                         "JJCRS=@JJCRS,GWCCB=@GWCCB,GWCXS=@GWCXS,GWCRS=@GWCRS,HCPCB=@HCPCB,VisaCB=@VisaCB,HCPRS=@HCPRS,HCPXS=@HCPXS,CPCB=@CPCB,CPRS=@CPRS,CPXS=@CPXS," +
                         "BXCB=@BXCB,BXRS=@BXRS,BXXS=@BXXS,HSCB=@HSCB,HSRS=@HSRS,HSXS=@HSXS,VisaXS=@VisaXS,VisaRS=@VisaRS,GWCB=@GWCB,GWXS=@GWXS,GWRS=@GWRS,DJCB=@DJCB,DJXS=@DJXS,SGRCB=@SGRCB,SGRNumber=@SGRNumber,SGRXS=@SGRXS," +
                         "TBRCB=@TBRCB,TBRNumber=@TBRNumber,TBRXS=@TBRXS,LYJCB=@LYJCB,LYJXS=@LYJXS,LYJRS=@LYJRS,JSESCB=@JSESCB,JSESNumber=@JSESNumber,JSESXS=@JSESXS," +
                         "SUITECB=@SUITECB,SUITENumber=@SUITENumber,SUITEXS=@SUITEXS,Oper=@Oper,OpTime=@OpTime,CostType=@CostType,AstartTime=@AstartTime,AendTime=@AendTime,Anumber=@Anumber,BstartTime=@BstartTime,BendTime=@BendTime,Bnumber=@Bnumber where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",hd.Diid),
                new SqlParameter("@Currency",hd.Currency),
                new SqlParameter("@Rate",hd.Rate),
                new SqlParameter("@Tax",hd.Tax),
                new SqlParameter("@FFYS",hd.FFYS),
                new SqlParameter("@HotelXS",hd.HotelXS),
                new SqlParameter("@JJCCB",hd.JJCCB),
                new SqlParameter("@JJCXS",hd.JJCXS),
                new SqlParameter("@JJCRS",hd.JJCRS),
                new SqlParameter("@GWCCB",hd.GWCCB),
                new SqlParameter("@GWCXS",hd.GWCXS),
                new SqlParameter("@GWCRS",hd.GWCRS),
                new SqlParameter("@HCPCB",hd.HCPCB),
                new SqlParameter("@HCPRS",hd.HCPRS),
                new SqlParameter("@HCPXS",hd.HCPXS),
                new SqlParameter("@CPCB",hd.CPCB),
                new SqlParameter("@CPRS",hd.CPRS),
                new SqlParameter("@CPXS",hd.CPXS),
                new SqlParameter("@BXCB",hd.BXCB),
                new SqlParameter("@BXRS",hd.BXRS),
                new SqlParameter("@BXXS",hd.BXXS),
                new SqlParameter("@HSCB",hd.HSCB),
                new SqlParameter("@HSRS",hd.HSRS),
                new SqlParameter("@HSXS",hd.HSXS),
                new SqlParameter("@VisaCB",hd.VisaCB),
                new SqlParameter("@VisaXS",hd.VisaXS),
                new SqlParameter("@VisaRS",hd.VisaRS),
                new SqlParameter("@GWCB",hd.GWCB),
                new SqlParameter("@GWXS",hd.GWXS),
                new SqlParameter("@GWRS",hd.GWRS),
                new SqlParameter("@DJCB",hd.DJCB),
                new SqlParameter("@DJXS",hd.DJXS),
                new SqlParameter("@LYJCB",hd.LYJCB),
                new SqlParameter("@LYJXS",hd.LYJXS),
                new SqlParameter("@LYJRS",hd.LYJRS),
                new SqlParameter("@SGRCB",hd.SGRCB),
                new SqlParameter("@SGRNumber",hd.SGRNumber),
                new SqlParameter("@SGRXS",hd.SGRXS),
                new SqlParameter("@TBRCB",hd.TBRCB),
                new SqlParameter("@TBRNumber",hd.TBRNumber),
                new SqlParameter("@TBRXS",hd.TBRXS),
                new SqlParameter("@JSESCB",hd.JSESCB),
                new SqlParameter("@JSESNumber",hd.JSESNumber),
                new SqlParameter("@JSESXS",hd.JSESXS),
                new SqlParameter("@SUITECB",hd.SUITECB),
                new SqlParameter("@SUITENumber",hd.SUITENumber),
                new SqlParameter("@SUITEXS",hd.SUITEXS),
                new SqlParameter("@Oper",hd.Oper),
                new SqlParameter("@OpTime",hd.OpTime),
                new SqlParameter("@Id",hd.Id),
                new SqlParameter("@CostType",hd.CostType)  ,
                new SqlParameter("@AstartTime",hd.AstartTime)  ,
                new SqlParameter("@AendTime",hd.AendTime)  ,
                new SqlParameter("@Anumber",hd.Anumber)  ,
                new SqlParameter("@BstartTime",hd.BstartTime)  ,
                new SqlParameter("@BendTime",hd.BendTime)  ,
                new SqlParameter("@Bnumber",hd.Bnumber)
            };

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

        /// <summary>
        /// 成本预算各项指标 
        /// </summary>
        /// <param name="diid">团组编号</param>
        /// <param name="type">(0:签证 1:机票 2:酒店 3:地接(含火车票 船票) 4:公务 5:零用金 6:团组总金额)</param>
        /// <param name="price">金额</param>
        /// <returns>
        ///  "-1" : 团组预算系数表没有查到该数据      
        ///  "0/其他小数"  : 传入金额小于预算金额正常 返回 "0" / 传入金额超过预算金额 返回 "超出金额占预算金额的百分比(小数显示)"
        /// </returns>
        public string GetBudgetIndicators(int diid, string type, float price)
        {
            string ReturnCode = "";
            GroupCostParameter gcp = GetByDiid(diid);
            if (gcp != null)
            {
                //团组汇率
                float Rate = gcp.Rate;
                //签证
                float VisaPrice = gcp.VisaCB * gcp.VisaRS * gcp.VisaXS * Rate;
                //机票
                float JJCPrice = gcp.JJCCB * gcp.JJCRS * gcp.JJCXS * Rate;  //经济舱
                float GWCPrice = gcp.GWCCB * gcp.GWCRS * gcp.GWCXS * Rate;  //公务舱
                float JPPrice = JJCPrice + GWCPrice;
                //酒店
                float SGRPrice = gcp.SGRCB * gcp.SGRNumber * gcp.SGRXS * Rate;  // 单人间
                float TBRPrice = gcp.TBRCB * gcp.TBRNumber * gcp.TBRXS * Rate;  //双人间
                float JSESPrice = gcp.JSESCB * gcp.JSESNumber * gcp.JSESXS * Rate; //小套房
                float SuitePrice = gcp.SUITECB * gcp.SUITENumber * gcp.SUITEXS * Rate; //套房
                float HotelPrice = SGRPrice + TBRPrice + JSESPrice + SuitePrice;
                //地接
                float DJPrice = gcp.DJCB * gcp.VisaRS * gcp.DJXS * Rate;
                float HCPPrice = gcp.HCPCB * gcp.HCPRS * gcp.HCPXS * Rate;  //火车票
                float CPPrice = gcp.CPCB * gcp.CPRS * gcp.CPXS * Rate;   // 船票
                DJPrice = DJPrice + HCPPrice + CPPrice;
                //公务
                float GWPrice = gcp.GWCB * gcp.GWRS * gcp.GWXS * Rate;
                //零用金
                float LYJPrice = gcp.LYJCB * gcp.LYJRS * gcp.LYJXS * Rate;
                //团组预算
                float TzPrice = VisaPrice + JPPrice + HotelPrice + DJPrice + GWPrice + LYJPrice;

                try
                {
                    switch (type)
                    {
                        case "0":
                            if (price <= VisaPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - VisaPrice) / VisaPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "1":
                            if (price <= JPPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - JPPrice) / JPPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "2":
                            if (price <= HotelPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - HotelPrice) / HotelPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "3":
                            if (price <= DJPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - DJPrice) / DJPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "4":
                            if (price <= GWPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - GWPrice) / GWPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "5":
                            if (price <= LYJPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - LYJPrice) / LYJPrice).ToString("0.000").Substring(0, 5);
                            break;
                        case "6":
                            if (price <= TzPrice)
                                ReturnCode = "0";
                            else
                                ReturnCode = ((price - TzPrice) / TzPrice).ToString("0.000").Substring(0, 5);
                            break;

                    }
                }
                catch (Exception)
                {
                    ReturnCode = "-1";
                }
                
            }
            else
                ReturnCode = "-1";

            return ReturnCode;
        }

        /// <summary>
        /// 雷怡 2021-08-06 15：08
        /// 修改 团组系数 通知表示  0：不允许通知 1：允许通知
        /// </summary>
        /// <param name="Diid">团组diid</param>
        /// <param name="IsShare">0/1</param>
        /// <returns></returns>
        public bool EditIsShare(int id, int IsShare)
        {
            string sql = "update GroupCostParameter set IsShare=@IsShare where Id = @id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@IsShare",IsShare),
                new SqlParameter("@id",id)
            };

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