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 OnHandDAL
{
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">oh_id</param>
    /// <returns>OnHand</returns>
    public OnHand Get(int wh_id, int i_id, string free1, string free2, string batch)
    {
        OnHand m = new OnHand();
        string sql = @"select *
                        from Stock_OnHand 
                        where wh_id=@wh_id
                        and i_id=@i_id
                        and isnull(free1,'')=isnull(@free1,'')
                        and isnull(free2,'')=isnull(@free2,'')
                        and isnull(batch,'')=isnull(@batch,'')";
        SqlParameter[] par = {
                new SqlParameter("@wh_id",wh_id),
                new SqlParameter("@i_id",i_id),
                new SqlParameter("@free1",free1),
                new SqlParameter("@free2",free2),
                new SqlParameter("@batch",batch)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.oh_id = Convert.ToInt32(r["oh_id"]);
                m.i_id = Convert.ToInt32(r["i_id"]);
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.batch = r["batch"].ToString();
                m.quantity = XTools.DBNullToDecimal(r["quantity"]);
                m.num = XTools.DBNullToDecimal(r["num"]);
            }
        }
        if (m.quantity == null)
        {
            m.wh_id = wh_id;
            m.i_id = i_id;
            m.free1 = free1;
            m.free2 = free2;
            m.batch = batch;
            m.quantity = 0;
            m.num = 0;
        }
        return m;
    }
    /// <summary>
    /// 更新现存量
    /// </summary>
    /// <param name="m">实体</param>
    public void UpdateQuantity(int wh_id, int i_id, string free1, string free2, string batch, decimal? quantity, decimal? num)
    {
        string sql = @"
            if(select count(1) from Stock_OnHand
            where wh_id=@wh_id and i_id=@i_id
            and isnull(free1,'')=isnull(@free1,'')
            and isnull(free2,'')=isnull(@free2,'')
            and isnull(batch,'')=isnull(@batch,''))=0
            begin
                insert Stock_OnHand(wh_id,i_id,free1,free2,batch,quantity,num)
                select @wh_id,@i_id,@free1,@free2,@batch,@quantity,@num;
            end 
            else
            begin
                update Stock_OnHand set
                quantity=isnull(quantity,0)+isnull(@quantity,0),
                num=isnull(num,0)+isnull(@num,0)
                where wh_id=@wh_id and i_id=@i_id
                and isnull(free1,'')=isnull(@free1,'')
                and isnull(free2,'')=isnull(@free2,'')
                and isnull(batch,'')=isnull(@batch,'')
            end";
        SqlParameter[] par = {
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@i_id", i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(free2)),
            new SqlParameter("@batch",XTools.StringToDBNull(batch)),
            new SqlParameter("@quantity", quantity),
            new SqlParameter("@num", XTools.DecimalToDBNull(num))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    /// <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(
                    select *
                    from Stock_OnHand
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .Replace("@sql_select_field", sql_select_field);

        //分页处理与查询条件
        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 Stock_OnHand 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;
    }

}
