using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Control;

public class VendorDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Vendor m)
    {
        string sql = @"insert Base_Vendor(vd_code,vd_name,vc_id,vd_full_name,is_stop,ele_id,
                        arrive_address,contact,legal_person,phone,
                        cellphone,fax,email,qq,address,bank_of_deposit,banking_account,
                        sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                        ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select @vd_code,@vd_name,@vc_id,@vd_full_name,@is_stop,@ele_id,
                        @arrive_address,@contact,@legal_person,@phone,
                        @cellphone,@fax,@email,@qq,@address,@bank_of_deposit,@banking_account,
                        @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                        @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@vd_code",m.vd_code),
            new SqlParameter("@vd_name",m.vd_name),
            new SqlParameter("@vc_id",m.vc_id),
            new SqlParameter("@vd_full_name",XTools.StringToDBNull(m.vd_full_name)),
            new SqlParameter("@is_stop",XTools.IntToDBNull(m.is_stop)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@arrive_address",XTools.StringToDBNull(m.arrive_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@legal_person",XTools.StringToDBNull(m.legal_person)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            new SqlParameter("@cellphone",XTools.StringToDBNull(m.cellphone)),
            new SqlParameter("@fax",XTools.StringToDBNull(m.fax)),
            new SqlParameter("@email",XTools.StringToDBNull(m.email)),
            new SqlParameter("@qq",XTools.StringToDBNull(m.qq)),
            new SqlParameter("@bank_of_deposit",XTools.StringToDBNull(m.bank_of_deposit)),
            new SqlParameter("@banking_account",XTools.StringToDBNull(m.banking_account)),
            new SqlParameter("@address",XTools.StringToDBNull(m.address)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">vd_id</param>
    /// <returns>Vendor</returns>
    public Vendor Get(int vd_id)
    {
        Vendor m = new Vendor();
        string sql = @"select *
                      from Base_Vendor where vd_id=@vd_id";
        SqlParameter[] par = {
                new SqlParameter("@vd_id",vd_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            VendorClassDAL vc_dal = new VendorClassDAL();
            EmployeeDAL ele_dal = new EmployeeDAL();
            foreach(DataRow r in dt.Tables[0].Rows)
            {
                m.vd_id = Convert.ToInt32(r["vd_id"]);
                m.vd_code = r["vd_code"].ToString();
                m.vd_name = r["vd_name"].ToString();
                m.vc_id = Convert.ToInt32(r["vc_id"]);
                if (r["vc_id"] != DBNull.Value)
                    m.vendor_class = vc_dal.Get(Convert.ToInt32(r["vc_id"]));
                m.ele_id = XTools.DBNullToInt(r["ele_id"]);
                if (r["ele_id"] != DBNull.Value)
                {
                    m.employee = ele_dal.Get(Convert.ToInt32(r["ele_id"]));
                }
                m.vd_full_name = r["vd_full_name"].ToString();
                m.is_stop = XTools.DBNullToInt(r["is_stop"]);
                m.arrive_address = r["arrive_address"].ToString();
                m.contact = r["contact"].ToString();
                m.legal_person = r["legal_person"].ToString();
                m.phone = r["phone"].ToString();
                m.cellphone = r["cellphone"].ToString();
                m.fax = r["fax"].ToString();
                m.email = r["email"].ToString();
                m.qq = r["qq"].ToString();
                m.bank_of_deposit = r["bank_of_deposit"].ToString();
                m.banking_account = r["banking_account"].ToString();
                m.address = r["address"].ToString();
                m.sdefine1 = r["sdefine1"].ToString();
                m.sdefine2 = r["sdefine2"].ToString();
                m.sdefine3 = r["sdefine3"].ToString();
                m.sdefine4 = r["sdefine4"].ToString();
                m.sdefine5 = r["sdefine5"].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(r["ndefine1"]);
                m.ndefine2 = XTools.DBNullToDecimal(r["ndefine2"]);
                m.ndefine3 = XTools.DBNullToDecimal(r["ndefine3"]);
                m.ndefine4 = XTools.DBNullToDecimal(r["ndefine4"]);
                m.ndefine5 = XTools.DBNullToDecimal(r["ndefine5"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 通过编码得到ID
    /// </summary>
    /// <param name="编码">vd_code</param>
    /// <returns>int</returns>
    public int GetIdByCode(string vd_code)
    {
        InventoryClass m = new InventoryClass();
        string sql = @"select vd_id
                      from Base_Vendor where vd_code=@vd_code";
        SqlParameter[] par = {
                new SqlParameter("@vd_code",vd_code)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Vendor m)
    {
        string sql = @"update Base_Vendor set 
                        vd_code=@vd_code,
                        vd_name=@vd_name,
                        vd_full_name=@vd_full_name,
                        vc_id=@vc_id,
                        is_stop=@is_stop,
                        ele_id=@ele_id,
                        arrive_address=@arrive_address,
                        contact=@contact,
                        legal_person=@legal_person,
                        phone=@phone,
                        cellphone=@cellphone,
                        fax=@fax,
                        email=@email,
                        qq=@qq,
                        bank_of_deposit=@bank_of_deposit,
                        banking_account=@banking_account,
                        address=@address,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where vd_id=@vd_id";
        SqlParameter[] par = {
            new SqlParameter("@vd_id",m.vd_id),
            new SqlParameter("@vd_code",m.vd_code),
            new SqlParameter("@vd_name",m.vd_name),
            new SqlParameter("@vc_id",m.vc_id),
            new SqlParameter("@vd_full_name",XTools.StringToDBNull(m.vd_full_name)),
            new SqlParameter("@is_stop",m.is_stop),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@arrive_address",XTools.StringToDBNull(m.arrive_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@legal_person",XTools.StringToDBNull(m.legal_person)),
            new SqlParameter("@cellphone",XTools.StringToDBNull(m.cellphone)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            new SqlParameter("@fax",XTools.StringToDBNull(m.fax)),
            new SqlParameter("@email",XTools.StringToDBNull(m.email)),
            new SqlParameter("@qq",XTools.StringToDBNull(m.qq)),
            new SqlParameter("@bank_of_deposit",XTools.StringToDBNull(m.bank_of_deposit)),
            new SqlParameter("@banking_account",XTools.StringToDBNull(m.banking_account)),
            new SqlParameter("@address",XTools.StringToDBNull(m.address)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int vd_id)
    {
        Vendor m = new Vendor();
        string sql = "delete Base_Vendor where vd_id in (@vd_id)";
        SqlParameter[] par = {
                new SqlParameter("@vd_id",vd_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有部门
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Vendor order by vd_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 快速下拉查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_FastSearch(string keyword)
    {
        string sql = @"select top 10 vd_id,vd_code,vd_name from Base_Vendor where (vd_code like '%'+@keyword+'%' or vd_name like '%'+@keyword+'%') and isnull(is_stop,0)=0 order by vd_code";
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    //打印用的sql
    public string sql_print = "";
    //列表查询用的sql
    string sql_list = @"
                    select vd.*,vc.vc_code,vc.vc_name,ele.ele_name,
                    case when isnull(vd.is_stop,0)=1 then '是' else '否' end as is_stop_name
                    from Base_Vendor vd
                    left join Base_VendorClass vc on vd.vc_id=vc.vc_id
                    left join Base_Employee ele on ele.ele_id=vd.ele_id";
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,@sql_select_field 
                from(@sql_list)t
                where 1=1 ";
        sql = sql.Replace("@sql_list", sql_list)
                 .Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);
        //打印是则不需要分页,只需要加上筛选条件
        sql_print = "select top 1000000000 * from (" + sql + ") t where 1=1 " + pg["sql_end"] + " " + pg["order_field"].ToString();
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from (" + sql_list + ") t where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
    /// <summary>
    /// 验证编码是否存在
    /// </summary>
    /// <param name="vd_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string vd_code)
    {
        string sql = "select count(1) from Base_Vendor where vd_code=@vd_code";
        SqlParameter[] par ={
                new SqlParameter("@vd_code", vd_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 验证名称是否存在
    /// </summary>
    /// <param name="vd_name">供应商名称</param>
    /// <returns>bool</returns>
    public bool CheckNameExist(string vd_name)
    {
        string sql = "select count(1) from Base_Vendor where vd_name=@vd_name";
        SqlParameter[] par ={
                new SqlParameter("@vd_name", vd_name)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
}
