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 Customer_FollowUpDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Customer_FollowUp m)
    {
        string sql = @"insert Base_Customer_FollowUp(cus_id,fu_date,fu_uid,fu_user,remark)
                        select @cus_id,@fu_date,@fu_uid,@fu_user,@remark";
        SqlParameter[] par = {
            new SqlParameter("@cus_id",m.cus_id),
            new SqlParameter("@fu_date",m.fu_date),
            new SqlParameter("@fu_uid",m.fu_uid),
            new SqlParameter("@fu_user",m.fu_user),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">cus_id</param>
    /// <returns>Customer_FollowUp</returns>
    public Customer_FollowUp Get(int cfu_id)
    {
        Customer_FollowUp m = new Customer_FollowUp();
        string sql = @"select t.*,cus.cus_name
                      from Base_Customer_FollowUp t
                      left join Base_Customer cus on t.cus_id=cus.cus_id
                      where t.cfu_id=@cfu_id";
        SqlParameter[] par = {
                new SqlParameter("@cfu_id",cfu_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {            
            CustomerDAL cus_dal = new CustomerDAL();
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.cfu_id = Convert.ToInt32(r["cfu_id"]);
                m.cus_id = Convert.ToInt32(r["cus_id"]);
                if (r["cus_id"] != DBNull.Value)
                {
                    m.customer = cus_dal.Get(Convert.ToInt32(r["cus_id"]));
                }
                m.fu_date = XTools.DBNullToTime(r["fu_date"]);
                m.fu_uid = XTools.DBNullToInt(r["fu_uid"]);
                m.fu_user = r["fu_user"].ToString();
                m.remark = r["remark"].ToString();
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Customer_FollowUp m)
    {
        string sql = @"update Base_Customer_FollowUp set 
                        fu_date=@fu_date,
                        remark=@remark
                       where cfu_id=@cfu_id";
        SqlParameter[] par = {
            new SqlParameter("@cfu_id",m.cfu_id),
            new SqlParameter("@cus_id",m.cus_id),
            new SqlParameter("@fu_date",m.fu_date),
            new SqlParameter("@fu_uid",m.fu_uid),
            new SqlParameter("@fu_user",m.fu_user),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int cfu_id)
    {
        Customer_FollowUp m = new Customer_FollowUp();
        string sql = "delete Base_Customer_FollowUp where cfu_id in (@cfu_id)";
        SqlParameter[] par = {
                new SqlParameter("@cfu_id",cfu_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Customer_FollowUp order by cfu_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    //打印用的sql
    public string sql_print = "";
    string sql_list = @"
                    select t.cfu_id,convert(char(10),t.fu_date,23) as fu_date,t.cus_id,cus.cus_name,
                    t.fu_uid,t.fu_user,t.remark
                    from Base_Customer_FollowUp t
                    left join Base_Customer cus on t.cus_id=cus.cus_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("@order_field", order_field)
                 .Replace("@sql_list", sql_list)
                 .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 ";
        sql = sql.Replace("@sql_list", sql_list);
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
}
