using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
    /// <summary>
    /// DB.供货商信息表
    /// </summary>
   public  class DBFillShops: IDisposable 
    {
          string sql;
        SqlHelpers sqlh;
       // DataSet ds;
       SqlDataReader dr;
        public DBFillShops()
        {
            sqlh = new SqlHelpers();
        }
        public DBFillShops(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }

         /// <summary>
        ///DB 查询所有供货商信息的方法
        /// </summary>
        /// <returns></returns>
        public List<CFillShops>DBFillShopsQuery()
        {
            List<CFillShops> lfs = new List<CFillShops>();
            sql = "select * from FillShops";
            dr =sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CFillShops fs = new CFillShops();
                fs.CfsId1  = Convert.ToInt32(dr["fsId"]);
                fs.CfsName1  = dr["fsName"].ToString();
                fs.FsSpell  = dr["fsSpell"].ToString();
                fs.CfsLinkman1  = dr["fsLinkman"].ToString();
                fs.CfsLinkphone1  = dr["fsLinkphone"].ToString();
                fs.CfsAddress1  = dr["fsAddress"].ToString();
                fs.CfsRemark1  = dr["fsRemark"].ToString();
                lfs.Add(fs);
            }
            dr.Close();
            return lfs;
        }

        /// <summary>
        /// 根据拼音缩写查询供货商的方法
        /// </summary>
        /// <param name="spell">拼音缩写</param>
        /// <returns></returns>
       public List<CFillShops> DBFillShopsSpell(string spell)
        {
            List<CFillShops> lfs = new List<CFillShops>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@spell", SqlDbType.VarChar, 30));
            pars[0].Value = spell;
            sql = "select * from FillShops where fsSpell like @spell+'%'";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CFillShops fs = new CFillShops();
                fs.CfsId1 = Convert.ToInt32(dr["fsId"]);
                fs.CfsName1 = dr["fsName"].ToString();
                fs.FsSpell = dr["fsSpell"].ToString();
                fs.CfsLinkman1 = dr["fsLinkman"].ToString();
                fs.CfsLinkphone1 = dr["fsLinkphone"].ToString();
                fs.CfsAddress1 = dr["fsAddress"].ToString();
                fs.CfsRemark1 = dr["fsRemark"].ToString();
                lfs.Add(fs);
            }
            dr.Close();
            return lfs;
        }

        /// <summary>
        /// 根据联系人查询供货商信息的方法
        /// </summary>
        /// <param name="lName">联系人姓名</param>
        /// <returns></returns>
        public List<CFillShops> DBFillShopXX(string lName)
        {
            List<CFillShops> lfs = new List<CFillShops>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@lName", SqlDbType.VarChar, 20));
            pars[0].Value = lName;
            sql = "select * from FillShops where fsLinkman=@lName";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CFillShops fs = new CFillShops();
                fs.CfsId1 = Convert.ToInt32(dr["fsId"]);
                fs.CfsName1 = dr["fsName"].ToString();
                fs.FsSpell = dr["fsSpell"].ToString();
                fs.CfsLinkman1 = dr["fsLinkman"].ToString();
                fs.CfsLinkphone1 = dr["fsLinkphone"].ToString();
                fs.CfsAddress1 = dr["fsAddress"].ToString();
                fs.CfsRemark1 = dr["fsRemark"].ToString();
                lfs.Add(fs);
            }
            dr.Close();
            return lfs;
        }

        /// <summary>
        /// 根据供货商编号删除供货商的方法
        /// </summary>
        /// <param name="fsId">供货商编号</param>
        public void DBFillShopIdDelete(int fsId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@fsId", SqlDbType.Int));
            pars[0].Value = fsId;
            sql = "delete from FillShops where fsId=@fsId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 插入供货商信息的方法
        /// </summary>
        /// <param name="fs"></param>
        public void DBFillShopsInsert(CFillShops fs)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@fsName", SqlDbType.VarChar, 40));
            pars[0].Value = fs.CfsName1 ;
            pars.Add(new SqlParameter("@fsSpell", SqlDbType.VarChar, 30));
            pars[1].Value = fs.FsSpell ;
            pars.Add(new SqlParameter("@fsLinkman", SqlDbType.VarChar, 10));
            pars[2].Value = fs.CfsLinkman1 ;
            pars.Add(new SqlParameter("@fsLinkphone", SqlDbType.VarChar, 30));
            pars[3].Value = fs.CfsLinkphone1 ;
            pars.Add(new SqlParameter("@fsAddress", SqlDbType.VarChar, 50));
            pars[4].Value = fs.CfsAddress1 ;
            pars.Add(new SqlParameter("@fsRemark", SqlDbType.VarChar, 50));
            pars[5].Value = fs.CfsRemark1 ;
            sql = "insert into FillShops values(@fsName,@fsSpell,@fsLinkman,@fsLinkphone,@fsAddress,@fsRemark)";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 修改供货商信息的方法
        /// </summary>
        /// <param name="fs"></param>
        public void DBFillShopsUpdate(CFillShops fs)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@fsName", SqlDbType.VarChar, 40));
            pars[0].Value = fs.CfsName1;
            pars.Add(new SqlParameter("@fsSpell", SqlDbType.VarChar, 30));
            pars[1].Value = fs.FsSpell;
            pars.Add(new SqlParameter("@fsLinkman", SqlDbType.VarChar, 10));
            pars[2].Value = fs.CfsLinkman1;
            pars.Add(new SqlParameter("@fsLinkphone", SqlDbType.VarChar, 30));
            pars[3].Value = fs.CfsLinkphone1;
            pars.Add(new SqlParameter("@fsAddress", SqlDbType.VarChar, 50));
            pars[4].Value = fs.CfsAddress1;
            pars.Add(new SqlParameter("@fsRemark", SqlDbType.VarChar, 50));
            pars[5].Value = fs.CfsRemark1;
            sql = "update FillShops set fsName=@fsName,fsSpell=@fsSpell,fsLinkman=@fsLinkman,fsLinkphone=@fsLinkphone,fsAddress=@fsAddress,fsRemark=@fsRemark where fsId=@fsId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

    }
}