OA2021/DAL/DailyFeePaymentService.cs

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

namespace DAL
{
    /// <summary>
    /// 日常费用付款申请数据访问类
    /// </summary>
    public class DailyFeePaymentService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<DailyFeePayment> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<DailyFeePayment>.excuteSql(new DailyFeePayment(), "DailyFeePayment", sql, CommandType.Text, param);
        }

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

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

        /// <summary>
        /// 获取全部 - 分页
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetDailyFeePayment(int pageIndex, out int sumPage, out int totalRecord, int audit, int auditGM, int auditRM, string instructions, string UniversiadePriceType, int operators, string idCard)
        {
            string sqlwhere = "IsDel = 0 and Instructions like '%" + instructions + "%'";

            if (operators != 0)
            {
                sqlwhere += " and Operator = " + operators;
            }
            if (idCard == "2")
                sqlwhere += " and FAudit = 1 and MAudit = " + auditGM;
            if (idCard == "3" || idCard == "")
                sqlwhere += " and FAudit = " + audit + " and MAudit = " + auditGM;
            if (idCard == "4")
                sqlwhere += " and (PriceType=288 or PriceType = 301 or operator=" + operators + ") and RAudit=" + auditRM;

            if ((idCard == "2" || idCard == "3") && UniversiadePriceType == "741")
                sqlwhere = sqlwhere + "and PriceType IN (select Id from setdata where STID = 55)";
            else if ((idCard == "2" || idCard == "3") && UniversiadePriceType != "--未选择--" && !string.IsNullOrEmpty(UniversiadePriceType.ToString()))
                sqlwhere = sqlwhere + "and PriceType IN (" + Convert.ToInt32(UniversiadePriceType) + ")";




            return PageBase<DailyFeePayment>.excutePageSql(new DailyFeePayment(), "DailyFeePayment", "DailyFeePayment", "*", "OperatorDate desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }
        /// <summary>
        /// 获取全部 - 分页
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePaymentDesc> GetDailyFeePaymentDesc(int pageIndex, out int sumPage, out int totalRecord, int audit, int auditGM, int auditRM, string instructions, string UniversiadePriceType, int operators, string idCard)
        {
            string sqlwhere = "where  IsDel = 0 and Instructions like '%" + instructions + "%'";

            if (operators != 0)
            {
                sqlwhere += " and Operator = " + operators;
            }
            if (idCard == "2")
                sqlwhere += " and FAudit = 1 and MAudit = " + auditGM;
            if (idCard == "3" || idCard == "")
                sqlwhere += " and FAudit = " + audit + " and MAudit = " + auditGM;
            if (idCard == "4")
                sqlwhere += " and (PriceType=288 or PriceType = 301 or operator=" + operators + ") and RAudit=" + auditRM;

            if ((idCard == "2" || idCard == "3") && UniversiadePriceType == "741")
                sqlwhere = sqlwhere + "and PriceType IN (select Id from setdata where STID = 55)";
            else if ((idCard == "2" || idCard == "3") && UniversiadePriceType != "--未选择--" && !string.IsNullOrEmpty(UniversiadePriceType.ToString()))
                sqlwhere = sqlwhere + "and PriceType IN (" + Convert.ToInt32(UniversiadePriceType) + ")";

            int startIndex = (pageIndex - 1) * 10 + 1;
            int endIndex = startIndex + 10 - 1;
            string sql = string.Format(@"Select * From (Select row_number() over (order by OperatorDate desc) as RowNumber,* From DailyFeePayment {0}) 
                                        temp Where RowNumber Between {1} and {2}", sqlwhere, startIndex, endIndex);


            string CountSql = string.Format(@"Select COUNT(1) as Count From (Select * From DailyFeePayment {0}) temp", sqlwhere);
            List<CountData> CountList= excuteSqlCount(CountSql);
            float totalPage = (float)CountList[0].Count / 10;//总页数
            if (totalPage == 0) totalPage = 1;
            else totalPage = (int)Math.Ceiling((double)totalPage);
            sumPage = Convert.ToInt32(totalPage);
            totalRecord = CountList[0].Count;


            return excuteSqlDesc(sql);
        }
        /// <summary>
        /// 获取全部 - 不分页
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetDailyFeePaymentNoPage(int pageIndex, int sumPage, int totalRecord, int audit, int auditGM, int auditRM, string instructions, int operators, string idCard)
        {
            string sqlwhere = "select * from DailyFeePayment where IsDel = 0 and Instructions like '%" + instructions + "%'";

            if (operators != 0 && operators != 41 && operators != 82)
                sqlwhere += " and Operator = " + operators;

            if (idCard == "2")
                sqlwhere += " and FAudit = 1 and MAudit = " + auditGM;
            else
                sqlwhere += " and FAudit = " + audit + " and MAudit = " + auditGM;

            if (idCard == "4")
                sqlwhere += " and (PriceType=288 or PriceType = 301 or operator=" + operators + ") and RAudit=" + auditRM;

            return excuteSql(sqlwhere);

            //return PageBase<DailyFeePayment>.excutePageSql(new DailyFeePayment(), "DailyFeePayment", "DailyFeePayment", "*", "OperatorDate desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 获取全部 - 分页
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetDailyFeePayment(int pageIndex, out int sumPage, out int totalRecord, int audit, int auditGM, string instructions, int operators, string idCard, string w)
        {
            string sqlwhere = "IsDel = 0 and Instructions like '%" + instructions + "%'";

            if (operators != 0)
                sqlwhere += " and Operator = " + operators;

            if (idCard == "2")
                sqlwhere += " and FAudit = 1 and MAudit = " + auditGM;
            else
                sqlwhere += " and FAudit = " + audit + " and MAudit = " + auditGM;
            sqlwhere += w;

            return PageBase<DailyFeePayment>.excutePageSql(new DailyFeePayment(), "DailyFeePayment", "DailyFeePayment", "*", "id desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 获取全部 
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetAllDailyFeePayment(int FAudit, int MAudit, string instructions, int LoginUserId, string SelectUserId)
        {
            string sqlwhere = "select * from DailyFeePayment where IsDel = 0  and FAudit = " + FAudit + " and MAudit = " + MAudit;

            if (!string.IsNullOrEmpty(instructions))
            {
                sqlwhere += " and Instructions like '%" + instructions + "%' ";
            }

            if (LoginUserId == 21 || LoginUserId == 22)
            {
                if (SelectUserId != "-1")
                {
                    sqlwhere += " and Operator = " + LoginUserId;
                }
            }
            else
            {
                sqlwhere += " and Operator = " + LoginUserId;
            }

            return excuteSql(sqlwhere);
        }

        public List<DailyFeePayment> GetAll(string OperType)
        {
            string sql = "select * from DailyFeePayment where IsDel = 0  and Operators > 0 and Operators is not null  ";
            if (OperType == "finance")
            {
                sql += " and FAudit = 0 and MAudit = 0 ";
            }
            else if (OperType == "GM")
            {
                sql += " and FAudit = 1 and MAudit = 0 ";
            }
            return excuteSql(sql);
        }

        public List<DailyFeePayment> GetAll(int page,int size,out int total,string OperType,string QueryName,string PriceRemake)
        {
            string sqlWhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(QueryName))
            {
                UsersService usersService = new UsersService();
                var queryUserId = usersService.GetAll().FindAll(x => x.CnName.Contains(QueryName)).Select(x => x.Id);
                if (queryUserId.Count() > 0)
                {
                    sqlWhere += $"   and Operator  in ({string.Join(",", queryUserId).TrimEnd(',')})    ";
                }
                else
                {
                    sqlWhere += $"   and Operator  in (0)  ";
                }
            }

            if (!string.IsNullOrWhiteSpace(PriceRemake))
            {
                sqlWhere += $"   and Instructions  like '%{PriceRemake}%'  ";
            }

            string sql = $@" select top {size} *  from DailyFeePayment where isdel = 0 and  Operator > 0 and Operator is not null  " + sqlWhere;
            total = 0;
            if (OperType == "finance")
            {
                sql += $@" and FAudit = 0 and MAudit = 0  and Id not in (
		             select top {(page - 1) * size} Id from DailyFeePayment where isdel = 0 and  Operator > 0 and Operator is not null 
		             and FAudit = 0 and MAudit = 0  {sqlWhere}
		             order by id desc 
	                )";

                total = (int)SqlHelper.ExecuteScalar($@"select count(*) from DailyFeePayment where isdel = 0 and  Operator > 0 
                        and Operator is not null and FAudit = 0 and MAudit = 0 " + sqlWhere, CommandType.Text);
            }
            else if (OperType == "GM")
            {
                sql += $@" and FAudit = 1 and MAudit = 0 and Id not in (
		             select top {(page - 1) * size} Id from DailyFeePayment where isdel = 0 and  Operator > 0 and Operator is not null 
		              and FAudit = 1 and MAudit = 0  {sqlWhere}
		             order by id desc 
	                )";

                total = (int)SqlHelper.ExecuteScalar($@"select count(*) from DailyFeePayment where isdel = 0 and  Operator > 0 
                        and Operator is not null and FAudit = 1 and MAudit = 0 " + sqlWhere, CommandType.Text);
            }

            sql += "order by id desc ";
            return excuteSql(sql);
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="dfp"></param>
        /// <returns></returns>
        public bool AddDailyFeePayment(DailyFeePayment dfp, out int id)
        {
            string sql = "insert into DailyFeePayment values(@Instructions,@SumPrice,@Operator,@OperatorDate,@FAudit,@FAuditDate,@MAudit,@MAuditDate,@Reason,@IsDel,@IsPay,@OrbitalPrivateTransfer,@PriceType,@CId,@CName,@RAudit,@RAuditDate,@AuditOperator);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Instructions",dfp.Instructions),
                new SqlParameter("@SumPrice",dfp.SumPrice),
                new SqlParameter("@Operator",dfp.Operators),
                new SqlParameter("@OperatorDate",dfp.OperatorsDate),
                new SqlParameter("@FAudit",dfp.FAudit),
                new SqlParameter("@FAuditDate",dfp.FAuditDate),
                new SqlParameter("@MAudit",dfp.MAudit),
                new SqlParameter("@MAuditDate",dfp.MAuditDate),
                new SqlParameter("@Reason",dfp.Reason),
                new SqlParameter("@IsDel",dfp.IsDel),
                new SqlParameter("@IsPay",dfp.IsPay),
                new SqlParameter("@OrbitalPrivateTransfer",dfp.OrbitalPrivateTransfer),
                new SqlParameter("@PriceType",dfp.PriceType),
                new SqlParameter("@CId",dfp.CId),
                new SqlParameter("@CName",dfp.CName),
                new SqlParameter("@RAudit",dfp.RAudit),
                new SqlParameter("@RAuditDate",dfp.RAuditDate),
                new SqlParameter("@AuditOperator",dfp.AuditOperator)
            };

            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter));

            if (obj > 0)
            {
                id = obj;
                return true;
            }

            id = 0;
            return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditDailyFeePayment(DailyFeePayment dfp)
        {
            string sql = "update DailyFeePayment set Instructions = @Instructions,SumPrice = @SumPrice,OrbitalPrivateTransfer = @OrbitalPrivateTransfer,PriceType = @PriceType,Operator = @Operator,OperatorDate = @OperatorDate where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@Instructions",dfp.Instructions),
                new SqlParameter("@SumPrice",dfp.SumPrice),
                new SqlParameter("@OrbitalPrivateTransfer",dfp.OrbitalPrivateTransfer),
                new SqlParameter("@PriceType",dfp.PriceType),
                new SqlParameter("@Operator",dfp.Operators),
                new SqlParameter("@OperatorDate",dfp.OperatorsDate),
                new SqlParameter("@Id",dfp.Id)
            };

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

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditDailyFeePaymentSumPrice(float sumPrice, int id)
        {
            string sql = "update DailyFeePayment set SumPrice = @SumPrice where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@SumPrice",sumPrice),
                new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelDailyFeePayment(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DailyFeePayment set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelDailyFeePaymentById(int id)
        {
            if (SqlHelper.ExecuteNonQuery("delete DailyFeePayment where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 财务审核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateDailyFeePaymentByFAudit(int FAudit, string FAuditDate, string Reason, int id)
        {
            string sql = "update DailyFeePayment set FAudit = @FAudit,FAuditDate=@FAuditDate,Reason = @Reason where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@FAudit",FAudit),
                new SqlParameter("@FAuditDate",FAuditDate),
                new SqlParameter("@Reason",Reason),
                new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 人事审核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateDailyFeePaymentByRAudit(int FAudit, int MAudit, int RAudit, string Date, string Reason, int id)
        {
            string sql = "update DailyFeePayment set FAudit = @FAudit,FAuditDate=@FAuditDate,MAudit = @MAudit,MAuditDate=@MAuditDate,RAudit = @RAudit,RAuditDate=@RAuditDate,Reason = @Reason where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@FAudit",FAudit),
                new SqlParameter("@FAuditDate",Date),
                new SqlParameter("@MAudit",MAudit),
                new SqlParameter("@MAuditDate",Date),
                new SqlParameter("@RAudit",RAudit),
                new SqlParameter("@RAuditDate",Date),
                new SqlParameter("@Reason",Reason),
                new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 总经理审核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateDailyFeePaymentByMAudit(int FAudit, int MAudit, string MAuditDate, string Reason, int id, int AuditOperator)
        {
            string sql = "update DailyFeePayment set FAudit = @FAudit,MAudit = @MAudit,MAuditDate=@MAuditDate,Reason = @Reason,AuditOperator=@AuditOperator where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@FAudit",FAudit),
                new SqlParameter("@MAudit",MAudit),
                new SqlParameter("@MAuditDate",MAuditDate),
                new SqlParameter("@Reason",Reason),
                new SqlParameter("@AuditOperator",AuditOperator),
                new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 付款确认
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateDailyFeePaymentByPay(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DailyFeePayment set IsPay = 1 where Id = " + id + "", CommandType.Text) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 获取全部未付款数据 - 分页
        /// MAudit=1
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetDailyFeePaymentByIsPay(int pageIndex, out int sumPage, out int totalRecord, int isPay, string instructions)
        {
            string sqlwhere = "IsDel = 0 and IsPay =" + isPay + " and MAudit = 1 and Instructions like '%" + instructions + "%'";

            return PageBase<DailyFeePayment>.excutePageSql(new DailyFeePayment(), "DailyFeePayment", "DailyFeePayment", "*", "id desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 获取全部未付款数据 - 分页
        /// MAudit=0
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetDailyFeePaymentMaByIsPay(int pageIndex, out int sumPage, out int totalRecord, int isPay, string priceType, string startTime, string endTime)
        {
            string sqlwhere = "IsDel = 0 and IsPay =" + isPay + " and  PriceType = " + priceType;
            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                sqlwhere += "  OperatorDate between '" + startTime + "' and '" + endTime + "'";

            return PageBase<DailyFeePayment>.excutePageSql(new DailyFeePayment(), "DailyFeePayment", "DailyFeePayment", "*", " OperatorDate asc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }
        /// <summary>
        /// 报表
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<DailyFeePayment> GetStatements(string startTime, string endTime)
        {
            return excuteSql("select * from DailyFeePayment where (MAuditDate >= '" + startTime + "' and MAuditDate <= '" + endTime + "') and IsPay = 1 and IsDel = 0 and PriceType not in( 686 ,687 , 688 , 689)");
        }

        public List<DailyFeePayment> GetById(string idlist)
        {
            return excuteSql("select * from DailyFeePayment where IsDel = 0 and Id in (" + idlist + ")");
        }


        public List<DailyFeePayment> GetByPaymentApplicationReport(string startTime, string endTime)
        {

            return excuteSql("select * from DailyFeePayment where  IsDel = 0 and IsPay = 0 and MAudit = 1 and (MAuditDate between '" + startTime + "' and '" + endTime + "') and PriceType <> 306"); //and PriceType <> 307
            //return excuteSql("select * from DailyFeePayment where  IsDel = 0 and IsPay = 0 and (MAuditDate between '" + startTime + "' and '" + endTime + "')");
        }

        public double GetSum(string startTime, string endTime, int CId)
        {
            try
            {
                return (double)SqlHelper.ExecuteScalar("select sum(itemSumprice) from dailyFeePaymentContent dfpc join dailyFeePayment dfp on dfp.id = dfpc.DFPID where dfp.isdel = 0 and dfp.maudit = 1 and (dfp.operatordate between '" + startTime + "' and '" + endTime + "') and dfp.CId=" + CId, CommandType.Text);
            }
            catch
            {
                return 0;
            }

        }

        /// <summary>
        ///  日常费用 不含 信用卡还款(686) 张总私人费用(687) 代报销社保生育补贴(688) 团组签证保险(689)
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="CId"></param>
        /// <returns></returns>
        public double GetSum1(string startTime, string endTime, int CId)
        {
            try
            {
                return (double)SqlHelper.ExecuteScalar("select sum(SumPrice) from dailyFeePayment where isdel = 0 and IsPay = 1 and(MAuditDate between '" + startTime + "' and '" + endTime + "') and Cid = " + CId + " and PriceType not in(686, 687, 688, 689)", CommandType.Text);
            }
            catch
            {
                return 0;
            }
        }


        /// <summary>
        /// 日常费用
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="CId">公司编号id</param>
        /// <returns></returns>
        public double GetCorrosion(string startTime, string endTime, string[] CIds)
        {
            try
            {
                string strIds = "";
                for (int i = 0; i < CIds.Length; i++)
                {
                    if (i == CIds.Length - 1)
                        strIds += i;
                    else
                        strIds += i + ",";
                }
                return (double)SqlHelper.ExecuteScalar("select sum(itemSumprice) from dailyFeePaymentContent dfpc join dailyFeePayment dfp on dfp.id = dfpc.DFPID where dfp.isdel = 0 and dfp.maudit = 1 and (dfp.operatordate between '" + startTime + "' and '" + endTime + "') and dfp.CId in (" + strIds + ") ", CommandType.Text);
            }
            catch { return 0; }
        }

        /// <summary>
        /// 日常费用
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="PriceId">费用发类型id</param>
        /// <param name="CId">公司编号id</param>
        /// <returns></returns>
        public double GetCorrosion(string startTime, string endTime, int PriceId, string[] CIds)
        {
            try
            {
                string strIds = "";
                for (int i = 0; i < CIds.Length; i++)
                {
                    if (i == CIds.Length - 1)
                        strIds += i;
                    else
                        strIds += i + ",";
                }
                return (double)SqlHelper.ExecuteScalar("select sum(itemSumprice) from dailyFeePaymentContent dfpc join dailyFeePayment dfp on dfp.id = dfpc.DFPID where dfp.isdel = 0 and dfp.maudit = 1 and (dfp.operatordate between '" + startTime + "' and '" + endTime + "') and dfp.CId IN (" + strIds + ") and dfp.PriceType =" + PriceId, CommandType.Text);
            }
            catch { return 0; }
        }

        /// <summary>
        /// 日常费用(setdata 人员费用,办公费用 )
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="CIds"></param>
        /// <returns></returns>
        public decimal GetNewOverhead(string startTime, string endTime, string[] CIds)
        {
            string cidsStr = string.Empty;
            foreach (var item in CIds)
            {
                cidsStr += item + ",";
            }
            cidsStr = cidsStr.TrimEnd(',');

            string sql = $@"	select sum(itemSumprice) from dailyFeePaymentContent 
	                dfpc join dailyFeePayment dfp on dfp.id = dfpc.DFPID
	                where dfp.isdel = 0 and dfp.maudit = 1 and dfp.CId in ({cidsStr}) 
	                and (dfp.operatordate between '{startTime}' and '{endTime}') and dfp.PriceType in(
		                select Id from SetData where IsDel = 0 And STid = 49 or STid = 48  and Name <> '其他款项'
	                )  ";
            return  Convert.ToDecimal((SqlHelper.ExecuteScalar(sql, CommandType.Text).ToString()));
        }

        /// <summary>
        /// 日常费用详细数据
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="PriceId">费用发类型id</param>
        /// <param name="CId">公司编号id</param>
        /// <returns></returns>

        public List<DailyFeePayment> GetCostData(string startTime, string endTime, int PriceId, string[] CIds)
        {
            string strIds = "";
            for (int i = 0; i < CIds.Length; i++)
            {
                if (i == CIds.Length - 1)
                    strIds += i;
                else
                    strIds += i + ",";
            }

            return excuteSql("select * from DailyFeePayment where  IsDel = 0 and IsPay = 1  and MAudit = 1 and (MAuditDate between '" + startTime + "' and '" + endTime + "') and PriceType = " + PriceId + " and CId IN  (" + strIds + ")"); //and PriceType <> 307
            //return excuteSql("select * from DailyFeePayment where  IsDel = 0 and IsPay = 0 and (MAuditDate between '" + startTime + "' and '" + endTime + "')");
        }

        /// <summary>
        /// 20220323 1107  贾文滔
        /// 大运会专属查询函数
        /// </summary>
        /// <returns></returns>
        public List<DailyFeePayment> GetForUniversiade(string PriceType)
        {
            string sql = "select * from DailyFeePayment d where d.IsDel=0 ";

            if (PriceType == "741")
                sql = sql + "and d.PriceType IN (select Id from setdata where STID = 55) order by PriceType";
            else
                sql = sql + "and d.PriceType IN (" + PriceType + ") order by PriceType";

            return excuteSql(sql);
        }


    }
}