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 GoodsComboDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(GoodsCombo m)
    {
        string sql = @"insert Sale_GoodsCombo(order_number,gc_name,wh_id,
			                        begin_date,end_date,is_stop,remark,verify_uid,verify_user)
                                    select @order_number,@gc_name,@wh_id,
			                        @begin_date,@end_date,@is_stop,@remark,@verify_uid,@verify_user";
        SqlParameter[] par = {
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@gc_name",XTools.StringToDBNull(m.gc_name)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@begin_date",m.begin_date),
            new SqlParameter("@end_date",XTools.DateTimeToDBNull(m.end_date)),
            new SqlParameter("@is_stop",XTools.IntToDBNull(m.is_stop)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@verify_uid",XTools.IntToDBNull(m.verify_uid)),
            new SqlParameter("@verify_user",XTools.StringToDBNull(m.verify_user))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">gc_id</param>
    /// <returns>GoodsCombo</returns>
    public GoodsCombo Get(int gc_id)
    {
        GoodsCombo m = new GoodsCombo();
        string sql = @"select gc.*,wh.wh_name
                    from Sale_GoodsCombo gc
                    left join Base_Warehouse wh on wh.wh_id=gc.wh_id

                    where gc.gc_id=@gc_id";
        sql = sql.Replace("@gc_id", gc_id.ToString());
        //提供sql给打印用
        sql_print = sql;
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, null))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.gc_id = Convert.ToInt32(r["gc_id"]);
                m.gc_name = r["gc_name"].ToString();
                m.order_number = r["order_number"].ToString();
                m.begin_date = XTools.DBNullToTime(r["begin_date"]);
                m.end_date = XTools.DBNullToTime(r["end_date"]);
                m.is_stop = XTools.DBNullToInt(r["is_stop"]);
                m.wh_id = XTools.DBNullToInt(r["wh_id"]);
                if (r["wh_id"] != DBNull.Value)
                    m.warehouse = new WarehouseDAL().Get(Convert.ToInt32(m.wh_id));
                m.remark = r["remark"].ToString();
                m.info = r["info"].ToString();
                m.verify_uid = XTools.DBNullToInt(r["verify_uid"]);
                m.verify_user = r["verify_user"].ToString();
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(GoodsCombo m)
    {
        string sql = @"update Sale_GoodsCombo set 
                        order_number=@order_number,
                        is_stop=@is_stop,
                        wh_id=@wh_id,
                        gc_name=@gc_name,
                        remark=@remark,
                        verify_uid=@verify_uid,
                        verify_user=@verify_user,
                        begin_date=@begin_date,
                        end_date=@end_date
                       where gc_id=@gc_id";
        SqlParameter[] par = {
            new SqlParameter("@gc_id",m.gc_id),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@gc_name",XTools.StringToDBNull(m.gc_name)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@begin_date",m.begin_date),
            new SqlParameter("@end_date",XTools.DateTimeToDBNull(m.end_date)),
            new SqlParameter("@is_stop",XTools.IntToDBNull(m.is_stop)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@verify_uid",XTools.IntToDBNull(m.verify_uid)),
            new SqlParameter("@verify_user",XTools.StringToDBNull(m.verify_user))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public string Delete(int gc_id)
    {

        //删除子表
        new GoodsCombosDAL().DeleteByOrder(gc_id);
        //删除主表
        string sql = "delete Sale_GoodsCombo where gc_id=@gc_id";
        SqlParameter[] par = {
            new SqlParameter("@gc_id",gc_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);

        return "success";
    }
   
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">单据号</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from Sale_GoodsCombo where order_number=@order_number";
        SqlParameter[] par ={
                new SqlParameter("@order_number", order_number)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 得到最大ID号
    /// </summary>
    public int GetMaxID()
    {
        string sql = "select isnull(max(gc_id),0) from Sale_GoodsCombo";
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, null));
    }
    /// <summary>
    /// 得到上一个ID
    /// </summary>
    public int GetPrevNextID(int current_id, string type)
    {
        string sql = @"select isnull(max(t.gc_id),0) from Sale_GoodsCombo t
                       left join Base_Customer cus on t.is_stop=cus.is_stop
                       where (t.gc_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = @"select isnull(min(t.gc_id),0) from Sale_GoodsCombo t
                    left join Base_Customer cus on t.is_stop=cus.is_stop
                    where t.gc_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="gc_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int gc_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.gcs_id,i.i_code+' ' +i.i_name as info,
	                    case when
                        i.on_free1=1 and isnull(d.free1,'')=''
	                    then '未填自由项1'
	                    when
	                    i.on_free2=1 and isnull(d.free2,'')=''
	                    then '未填自由项2'
                        when
	                    isnull(d.quantity,0)=0
	                    then '未填数量'
	                    else 'success' end as msg
	                    from Sale_GoodsCombos d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.gc_id=@gc_id
                    )t where msg!='success'
                    order by gcs_id";
        SqlParameter[] par ={
                new SqlParameter("@gc_id", gc_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString() + " " + dt.Rows[0]["msg"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 更新表头套餐描述
    /// </summary>
    /// <param name="gc_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string UpdateInfo(int gc_id)
    {
		string sql = @"
								select i.i_name+' '+cast(isnull(gcs.tax_unit_price,0) as varchar(50))+'元x'+
								+cast(gcs.quantity as varchar(50))+' '+i.munit
								+'='+CAST(isnull(gcs.tax_total_price,0) as varchar(50))+'元' as msg
								from Sale_GoodsCombos gcs
								left join Base_Inventory i on gcs.i_id=i.i_id
                                where gcs.gc_id=@gc_id
								order by gcs.gcs_id";
        SqlParameter[] par ={
                new SqlParameter("@gc_id", gc_id)
				};
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        string info = "";
        if (dt.Rows.Count > 0)
        {
		    foreach(DataRow dr in dt.Rows)
		    {
                string msg = dr["msg"].ToString();
                info += msg + " | ";
		    }
        }
        string sql2 = "update Sale_GoodsCombo set info=@info where gc_id=@gc_id";
        if (info.Length > 500)
        {
            info = info.Substring(0, 500);
        }
        SqlParameter[] par2 ={
                new SqlParameter("@gc_id", gc_id),
                new SqlParameter("@info", info)
				};
        try
        {
            new SqlUtil().ExecuteNonQuery(sql2, par2);
        }
        catch (Exception err)
        {
            return err.Message;
        }
        return "success";
    }
    /// <summary>
    /// 判断单据是否存在主商品
    /// </summary>
    /// <param name="gc_id">主表ID</param>
    public bool CheckOrderHasIsMain(int gc_id)
    {
        GoodsCombos m = new GoodsCombos();
        string sql = "select count(*) from Sale_GoodsCombos where isnull(is_main,0)=1 and gc_id=@gc_id";
        SqlParameter[] par = {
                new SqlParameter("@gc_id",gc_id)
            };
        if (Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par)) >= 1)
            return true;
        return false;
    }
}
