OA2021/DAL/OfferServices.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 OfferServices
    {

        /// <summary>
        /// 查询所有—分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="company"></param>
        /// <param name="Uname"></param>
        /// <param name="ProCode"></param>
        /// <returns></returns>
        public DataTable GetAll(int pageIndex, out int sumPage, out int totalRecord, int company,string Uname,string Projects,int Cid,string proCode)
        {
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@pageIndex", pageIndex),
                new SqlParameter("@pageSize", 10),
                new SqlParameter("@Uname", Uname),
                new SqlParameter("@Projects", Projects),
                new SqlParameter("@Cid", Cid),
                new SqlParameter("@proCode", proCode)
            };
            //查询总条数
            totalRecord = Convert.ToInt32(SqlHelper.ExecuteScalar("select count(1) from dbo.Offer T left join clientdata C on T.Uid=C.id left join setData SC on T.Cid=SC.id left join setData SP on T.Pid=SP.id left join OfferData O on SC.id=O.Cid and SP.id=O.Pid where T.IsDel <> 1 and C.IsDel<>1 and SC.IsDel<>1 and SP.IsDel<>1 and C.UnitName like '%"+Uname+"%' and Projects like '%"+Projects+"%' and T.Cid="+Cid+" and T.ProCode like '%"+proCode+"%'", CommandType.Text, null));
            //计算出总页数
            sumPage = totalRecord % 10 == 0 ? totalRecord / 10 : totalRecord / 10 + 1;
            return SqlHelper.TransferProcedure("[exec_Offer_Query]", CommandType.StoredProcedure, parameter);
        }

        /// <summary>
        /// 添加报价单
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public bool AddOffer(Offer o)
        {
            string sql = "insert into Offer values(@Uid,@Projects,@Cid,@Pid,@Discount,@SalePrice,@Profit,@Version,@IsNew,@IsDel,@InputTime,@ProCode)";
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@Uid",o.Uid),
                new SqlParameter("@Projects",o.Projects),
                new SqlParameter("@Cid",o.Cid),
                new SqlParameter("@Pid",o.Pid),
                new SqlParameter("@Discount",o.Discount),
                new SqlParameter("@SalePrice",o.SalePrice),
                new SqlParameter("@Profit",o.Profit),
                new SqlParameter("@Version",o.Version),
                new SqlParameter("@IsNew",o.IsNew),
                new SqlParameter("@IsDel",o.IsDel),
                new SqlParameter("@InputTime",o.InputTime),
                new SqlParameter("@ProCode",o.ProCode)
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 根据项目编号查询该企业该项目生成报价单数量
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetOfferCount(string ProCode)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar("select max(Version) from Offer where ProCode=@ProCode", CommandType.Text, new SqlParameter("@ProCode", ProCode)));
        }

        /// <summary>
        /// 查询最大项目编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxCode()
        {
            return SqlHelper.ExecuteScalar("select max(ProCode) from Offer", CommandType.Text, null).ToString();
        }

        /// <summary>
        /// 根据id查询offer实体
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public Offer GetOfferById(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Offer where Id = @id and IsDel = 0", new SqlParameter("@id", id));
        }


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

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

        /// <summary>
        /// 根据项目编号获得所有该项目的报价单
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public List<Offer> GetOfferByProCode(string ProCode)
        {
            return excuteSql("select * from Offer where ProCode=@ProCode", new SqlParameter("@ProCode", ProCode));
        }

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

        /// <summary>
        /// 根据项目编号和版本号查询信息
        /// </summary>
        /// <param name="proCode"></param>
        /// <param name="version"></param>
        /// <returns></returns>
        public DataTable GetOfferByProCodeAndVersion(string proCode, int version)
        {
            return SqlHelper.TransferProcedure("exec_Offer_QueryByProCodeAndVersion", CommandType.StoredProcedure, new SqlParameter("@ProCode", proCode), new SqlParameter("@Version", version));
        }

        public List<Offer> GetAll(string startTime, string endTime)
        {
            string sql="select * from Offer where 1=1 ";
            if(startTime!="")
                sql+=" and InputTime >='"+startTime+"'";
            if(endTime!="")
                sql+=" and InputTime <='"+endTime+"'";
            return excuteSql(sql, null);
        }
    }
}