using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Business.Supply.Entity;
using Common;

namespace Business.Supply.Vo
{
    public class SupplyVo
    {
        /// <summary>
        /// 参数赋值
        /// </summary>
        /// <param name="supply">实体类SupplyEty</param>
        /// <returns>参数数组</returns>
        private SqlParameter[] SetPara(SupplyEty supply)
        {
            return new[]
                {
                    new SqlParameter("@id", supply.Id),
                    new SqlParameter("@name", supply.Name),
                    new SqlParameter("@depid", supply.DepId),
                    new SqlParameter("@time", supply.Time),
                    new SqlParameter("@remark", supply.Remark),
                    new SqlParameter("@delflag", supply.DelFlag),
                    new SqlParameter("@addUserID", supply.AddUserId),
                    new SqlParameter("@addtime", supply.AddTime),
                    new SqlParameter("@updateUserID", supply.UpdateUserId),
                    new SqlParameter("@lastTime", supply.LastTime),
                    new SqlParameter("@quantity",supply.Quantity)
                };
        }

        /// <summary>
        /// 新增supply记录
        /// </summary>
        /// <param name="supply">实体类SupplyEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string InsertSupply(SupplyEty supply)
        {
            var sql =
                "insert into supply(id,depid,name,time,remark,delflag,addUserID,addtime,updateUserID,lastTime,quantity) values(@id,@depid,@name,@time,@remark,@delflag,@addUserID,@addtime,@updateUserID,@lastTime,@quantity)";
            return SqlHelper.ExecSql(sql, SetPara(supply));
        }

        /// <summary>
        /// 获取全部供应信息
        /// </summary>
        /// <param name="searchText">查询字段</param>
        /// <returns>数据表</returns>
        public DataTable AllSupply(string searchText)
        {
            var sql =
                "select p.id,p.name,p.depid,sd.name as depname, p.quantity,p.remark,p.time,p.lastTime,s.username as addusername ,ss.username as updateusername from supply p left join SysDep sd on sd.depid=p.depid left join SysUser s on s.userid=p.addUserID left join SysUser ss on ss.userid=p.updateUserID where p.delflag ='false' ";

            if (!string.IsNullOrEmpty(searchText))
            {
                //根据查询字段 进行查询
                sql +=
                    string.Format(
                        "and( p.name like '%{0}%' or sd.name like '%{0}%' or p.remark like '%{0}%' or p.quantity like '%{0}%' or s.username like '%{0}%' or ss.username like '%{0}%')",
                        searchText);
            }
            sql += " order by p.lastTime DESC";

            return SqlHelper.ExecSqlDateTable(sql);
        }

        /// <summary>
        /// 更新供应信息
        /// </summary>
        /// <param name="supply">实体类SupplyEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string UpdateSupply(SupplyEty supply)
        {
            var sq1 =
                "update supply set name=@name,depid=@depid,quantity=@quantity,remark=@remark,lastTime=@lastTime,updateUserID=@updateUserID,time=@time where id=@id";
            return SqlHelper.ExecSql(sq1, SetPara(supply));
        }

        /// <summary>
        /// 根据id删除供应信息
        /// </summary>
        /// <param name="id">供应id</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string DelSupply(string id)
        {
            var sql = "update supply set delflag='true' where id in (" + id + ")";
            return SqlHelper.ExecSql(sql);
        }
    }
}