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

public class YingFuDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(YingFu m)
    {
        string sql = @"insert Ling_YingFu(add_date,amount,
                        cus_id,vd_id,ele_id,cst_id,digest,remark)
                        select @add_date,@amount,
                        @cus_id,@vd_id,@ele_id,@cst_id,@digest,@remark";
        SqlParameter[] par = {
            new SqlParameter("@add_date",m.add_date),
            new SqlParameter("@amount",XTools.DecimalToDBNull(m.amount)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@cst_id",XTools.IntToDBNull(m.cst_id)),
            new SqlParameter("@digest",XTools.StringToDBNull(m.digest)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">yf_id</param>
    /// <returns>YingFu</returns>
    public YingFu Get(int yf_id)
    {
        YingFu m = new YingFu();
        string sql = @"select *
                      from Ling_YingFu where yf_id=@yf_id";
        SqlParameter[] par = {
                new SqlParameter("@yf_id",yf_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.yf_id = Convert.ToInt32(r["yf_id"]);
                m.add_date = XTools.DBNullToTime(r["add_date"]);
                m.amount = XTools.DBNullToDecimal(r["amount"]);
                m.cus_id = XTools.DBNullToInt(r["cus_id"]);
                if (r["cus_id"] != DBNull.Value)
                    m.customer = new CustomerDAL().Get(Convert.ToInt32(m.cus_id));
                m.vd_id = XTools.DBNullToInt(r["vd_id"]);
                if (r["vd_id"] != DBNull.Value)
                {
                    m.vendor = new VendorDAL().Get(Convert.ToInt32(m.vd_id));
                    m.digest = m.vendor.sdefine1;
                }
                m.ele_id = XTools.DBNullToInt(r["ele_id"]);
                if (r["ele_id"] != DBNull.Value)
                    m.employee = new EmployeeDAL().Get(Convert.ToInt32(m.ele_id));
                m.cst_id = XTools.DBNullToInt(r["cst_id"]);
                if (r["cst_id"] != DBNull.Value)
                    m.cost = new CostDAL().Get(Convert.ToInt32(m.cst_id));
                //m.digest = r["digest"].ToString();

                m.remark = r["remark"].ToString();
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(YingFu m)
    {
        string sql = @"update Ling_YingFu set 
                        add_date=@add_date,
                        amount=@amount,
                        cus_id=@cus_id,
                        vd_id=@vd_id,
                        ele_id=@ele_id,
                        cst_id=@cst_id,
                        digest=@digest,
                        remark=@remark
                       where yf_id=@yf_id";
        SqlParameter[] par = {
            new SqlParameter("@yf_id",m.yf_id),
            new SqlParameter("@add_date",m.add_date),
            new SqlParameter("@amount",XTools.DecimalToDBNull(m.amount)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@cst_id",XTools.IntToDBNull(m.cst_id)),
            new SqlParameter("@digest",XTools.StringToDBNull(m.digest)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark))
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public void Delete(int yf_id)
    {
        YingFu m = new YingFu();
        string sql = "delete Ling_YingFu where yf_id in (@yf_id)";
        SqlParameter[] par = {
                new SqlParameter("@yf_id",yf_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    //打印用的sql
    public string sql_print = "";
    string sql_list = @"
                    select yf.yf_id,convert(char(10),yf.add_date,23) as add_date,
                    yf.amount,yf.cus_id,yf.vd_id,yf.ele_id,yf.cst_id,vd.sdefine1 as digest,yf.remark,
                    cus.cus_name,vd.vd_name,ele.ele_name,cst.cst_name
                    from Ling_YingFu yf
                    left join Base_Customer cus on cus.cus_id=yf.cus_id
                    left join Base_Vendor vd on vd.vd_id=yf.vd_id
                    left join Base_Employee ele on ele.ele_id=yf.ele_id
                    left join Base_Cost cst on cst.cst_id=yf.cst_id 
                    where 1=1 ";
    /// <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_select_field", sql_select_field);
        sql_list = sql_list.Replace("@owner", pg["owner"].ToString());
        sql_print = sql_list + " " + pg["sql_end"];
        //分页处理与查询条件
        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)
    {
        var 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;
    }
}
