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 GoodsCombosDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(GoodsCombos m)
    {
        string sql = @"insert Sale_GoodsCombos(gc_id,i_id,
                                free1,free2,tax_unit_price,quantity,num,convert_rate,tax_total_price,is_main,d_remark)
                                select @gc_id,@i_id,
                                @free1,@free2,@tax_unit_price,@quantity,@num,@convert_rate,@tax_total_price,@is_main,@d_remark";
        SqlParameter[] par = {
            new SqlParameter("@gc_id",m.gc_id),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@is_main",XTools.IntToDBNull(m.is_main)),
            new SqlParameter("@d_remark",m.d_remark.ToString())
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">gcs_id</param>
    /// <returns>GoodsCombos</returns>
    public GoodsCombos Get(int gcs_id)
    {
        GoodsCombos m = new GoodsCombos();
        string sql = @"select gcs.*
                    from Sale_GoodsCombos gcs 
                    left join Base_Inventory i on gcs.i_id=i.i_id
                    where gcs.gcs_id=@gcs_id";
        SqlParameter[] par = {
                new SqlParameter("@gcs_id",gcs_id)
            };
        DeliveryOrderDAL parent_dal = new DeliveryOrderDAL();
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.gc_id = Convert.ToInt32(r["gc_id"]);//主表id
                m.parent_object = parent_dal.Get(m.gc_id);//父对象
                m.gcs_id = Convert.ToInt32(r["gcs_id"]);//子表id
                m.i_id = Convert.ToInt32(r["i_id"]);//存货id
                if(r["i_id"] != null)
                    m.inventory = new InventoryDAL().Get(m.i_id);
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.tax_unit_price = XTools.DBNullToDecimal(r["tax_unit_price"]);//含税单价
                m.quantity = XTools.DBNullToDecimal(r["quantity"]);//数量
                m.num = XTools.DBNullToDecimal(r["num"]);//件数
                m.convert_rate = XTools.DBNullToDecimal(r["convert_rate"]);//换算率
                m.tax_total_price = XTools.DBNullToDecimal(r["tax_total_price"]);//含税金额
                m.is_main = XTools.DBNullToInt(r["is_main"]);//主商品
                m.d_remark = r["d_remark"].ToString();//备注
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(GoodsCombos m)
    {
        string sql = @"update Sale_GoodsCombos set
                        gc_id=@gc_id,i_id=@i_id,free1=@free1,free2=@free2,
                        tax_unit_price=@tax_unit_price,quantity=@quantity,
                        num=@num,convert_rate=@convert_rate,tax_total_price=@tax_total_price,
                        is_main=@is_main,d_remark=@d_remark
                        where gcs_id=@gcs_id";
        SqlParameter[] par = {
            new SqlParameter("@gc_id",m.gc_id),
            new SqlParameter("@gcs_id",XTools.IntToDBNull(m.gcs_id)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@is_main",XTools.IntToDBNull(m.is_main)),
            new SqlParameter("@d_remark",XTools.StringToDBNull(m.d_remark))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除子表
    /// </summary>
    /// <param name="gcs_id">子表ID</param>
    public void Delete(int gcs_id)
    {
        GoodsCombos m = new GoodsCombos();
        string sql = "delete Sale_GoodsCombos where gcs_id=@gcs_id";
        SqlParameter[] par = {
                new SqlParameter("@gcs_id",gcs_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除整单子表
    /// </summary>
    /// <param name="gc_id">主表ID</param>
    public void DeleteByOrder(int gc_id)
    {
        GoodsCombos m = new GoodsCombos();
        string sql = "delete Sale_GoodsCombos where gc_id=@gc_id";
        SqlParameter[] par = {
                new SqlParameter("@gc_id",gc_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <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 d.*,
                    case when isnull(d.is_main,0)=1 then '是' end as is_main_name,
                    i.i_code,i.i_name,i.standard,i.munit,i.aunit
	                from Sale_GoodsCombos d
	                left join Base_Inventory i on d.i_id=i.i_id
                )t
                where 1=1 ";
        sql = sql.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 Sale_GoodsCombos 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>
    /// <returns>int</returns>
    public int GetOneOrder_TotalNumbers(int gc_id)
    {
        string sql = "select count(1) from Sale_GoodsCombos where gc_id=@gc_id ";
        SqlParameter[] par = {
                new SqlParameter("@gc_id",gc_id)
            };
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, par);
        return total_numbers;
    }
    /// <summary>
    /// 得到某张单所有明细
    /// </summary>
    /// <param name="ts_id"></param>
    /// <returns></returns>
    public DataTable GetDetails(int gc_id)
    {
        string sql = "select * from Sale_GoodsCombos where gc_id=@gc_id";
        SqlParameter[] par = {
            new SqlParameter("@gc_id", gc_id)
        };
        return new SqlUtil().GetDataTable(sql, par);
    }
    /// <summary>
    /// 得到最新的子表ID
    /// </summary>
    /// <returns>int</returns>
    public int GetMaxID(int gc_id)
    {
        string sql = "select isnull(max(gcs_id),0) from Sale_GoodsCombos where gc_id=@gc_id ";
        SqlParameter[] par = {
                new SqlParameter("@gc_id",gc_id)
            };
        int max_id = (int)new SqlUtil().ExecuteScalar(sql, par);
        return max_id;
    }
    /// <summary>
    /// 设置除当前记录之外的记录的“主商品”属性为否
    /// </summary>
    /// <param name="gc_id">主表ID</param>
    /// <param name="gcs_id">子表ID</param>
    public void UpdateIsMainOnlyOne(int gc_id, int gcs_id)
    {
        GoodsCombos m = new GoodsCombos();
        string sql = "update Sale_GoodsCombos set is_main=0 where gc_id=@gc_id and gcs_id!=@gcs_id";
        SqlParameter[] par = {
                new SqlParameter("@gc_id",gc_id),
                new SqlParameter("@gcs_id",gcs_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
}
