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;
using System.Transactions;

public class BOMDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(BOM m)
    {

        string sql = @"insert Base_BOM(p_i_id,p_free1,p_free2,wh_id,dep_id,remark,
                                    new_uid,new_user,new_date,
                                    sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                                    ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select  @p_i_id,@p_free1,@p_free2,@wh_id,@dep_id,@remark,
                                @new_uid,@new_user,@new_date,
                                @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                                @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@p_i_id",m.p_i_id),
            new SqlParameter("@p_free1",XTools.StringToDBNull(m.p_free1)),
            new SqlParameter("@p_free2",XTools.StringToDBNull(m.p_free2)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@new_uid",XTools.IntToDBNull(m.new_uid)),
            new SqlParameter("@new_user",XTools.StringToDBNull(m.new_user)),
            new SqlParameter("@new_date",m.new_date),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">bo_id</param>
    /// <returns>BOM</returns>
    public BOM Get(int bo_id)
    {
        BOM m = new BOM();
        string sql = @"select bo.*,
                        convert(char(10),bo.new_date,23) as new_date_format,
                        wh.wh_name,dep.dep_name,
                        i.i_code as p_i_code,i.i_name as p_i_name,
                        i.standard as p_standard,i.munit as p_munit,
                        i.sdefine1 as inv_sdefine1,i.sdefine2 as inv_sdefine2,i.sdefine3 as inv_sdefine3,
                        i.sdefine4 as inv_sdefine4,i.sdefine5 as inv_sdefine5,
                        i.ndefine1 as inv_ndefine1,i.ndefine2 as inv_ndefine2,i.ndefine3 as inv_ndefine3,
                        i.ndefine4 as inv_ndefine4,i.ndefine5 as inv_ndefine5
                        from Base_BOM bo 
                        left join Base_Warehouse wh on wh.wh_id=bo.wh_id
                        left join Base_Department dep on dep.dep_id=bo.dep_id
                        left join Base_Inventory i on i.i_id=bo.p_i_id
                        where bo_id=@bo_id";
        sql = sql.Replace("@bo_id", bo_id.ToString());
        //提供sql给打印用
        sql_print = sql;
        using (SqlDataReader r = new SqlUtil().GetDataReader(sql, null))
        {
            while (r.Read())
            {
                m.bo_id = Convert.ToInt32(r["bo_id"]);
                m.p_i_id = Convert.ToInt32(r["p_i_id"]);//存货id
                if (r["p_i_id"] != DBNull.Value)
                    m.p_inventory = new InventoryDAL().Get(m.p_i_id);
                m.p_free1 = r["p_free1"].ToString();
                m.p_free2 = r["p_free2"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                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.dep_id = XTools.DBNullToInt(r["dep_id"]);
                if (r["dep_id"] != DBNull.Value)
                    m.department = new DepartmentDAL().Get(Convert.ToInt32(m.dep_id));
                m.remark = r["remark"].ToString();
                m.new_uid = XTools.DBNullToInt(r["new_uid"]);
                m.new_user = r["new_user"].ToString();
                m.verify_uid = XTools.DBNullToInt(r["verify_uid"]);
                m.verify_user = r["verify_user"].ToString();
                m.update_uid = XTools.DBNullToInt(r["update_uid"]);
                m.update_user = r["update_user"].ToString();
                m.update_date = XTools.DBNullToTime(r["update_date"]);
                //单据自定义项
                m.sdefine1 = r["sdefine1"].ToString();
                m.sdefine2 = r["sdefine2"].ToString();
                m.sdefine3 = r["sdefine3"].ToString();
                m.sdefine4 = r["sdefine4"].ToString();
                m.sdefine5 = r["sdefine5"].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(r["ndefine1"]);
                m.ndefine2 = XTools.DBNullToDecimal(r["ndefine2"]);
                m.ndefine3 = XTools.DBNullToDecimal(r["ndefine3"]);
                m.ndefine4 = XTools.DBNullToDecimal(r["ndefine4"]);
                m.ndefine5 = XTools.DBNullToDecimal(r["ndefine5"]);
                //存货自定义项
                m.inv_sdefine1 = r["inv_sdefine1"].ToString();
                m.inv_sdefine2 = r["inv_sdefine2"].ToString();
                m.inv_sdefine3 = r["inv_sdefine3"].ToString();
                m.inv_sdefine4 = r["inv_sdefine4"].ToString();
                m.inv_sdefine5 = r["inv_sdefine5"].ToString();
                m.inv_ndefine1 = XTools.DBNullToDecimal(r["inv_ndefine1"]);
                m.inv_ndefine2 = XTools.DBNullToDecimal(r["inv_ndefine2"]);
                m.inv_ndefine3 = XTools.DBNullToDecimal(r["inv_ndefine3"]);
                m.inv_ndefine4 = XTools.DBNullToDecimal(r["inv_ndefine4"]);
                m.inv_ndefine5 = XTools.DBNullToDecimal(r["inv_ndefine5"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(BOM m)
    {

        string sql = @"update Base_BOM set 
                        p_i_id=@p_i_id,
                        p_free1=@p_free1,
                        p_free2=@p_free2,
                        wh_id=@wh_id,
                        dep_id=@dep_id,
                        remark=@remark,
                        new_uid=@new_uid,
                        new_date=@new_date,
                        update_uid=@update_uid,
                        update_date=@update_date,
                        verify_uid=@verify_uid,
                        verify_user=@verify_user,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where bo_id=@bo_id";
        SqlParameter[] par = {
            new SqlParameter("@bo_id",m.bo_id),
            new SqlParameter("@p_i_id",m.p_i_id),
            new SqlParameter("@p_free1",XTools.StringToDBNull(m.p_free1)),
            new SqlParameter("@p_free2",XTools.StringToDBNull(m.p_free2)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@new_uid",XTools.IntToDBNull(m.new_uid)),
            new SqlParameter("@new_user",XTools.StringToDBNull(m.new_user)),
            new SqlParameter("@new_date",XTools.DateTimeToDBNull(m.new_date)),
            new SqlParameter("@update_uid",XTools.IntToDBNull(m.update_uid)),
            new SqlParameter("@update_user",XTools.StringToDBNull(m.update_user)),
            new SqlParameter("@update_date",XTools.DateTimeToDBNull(m.update_date)),
            new SqlParameter("@verify_uid",XTools.IntToDBNull(m.verify_uid)),
            new SqlParameter("@verify_user",XTools.StringToDBNull(m.verify_user)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public string Delete(int bo_id)
    {
        BOMDAL dal = new BOMDAL();
        SqlUtil su = new SqlUtil();
        SqlParameter[] par = {
                new SqlParameter("@bo_id",bo_id)
            };

        //启用事务
        using (TransactionScope scope = new TransactionScope())
        {
            try
            {
                //删除子表
                new BOMsDAL().DeleteByOrder(bo_id);
                //删除主表
                string sql = "delete Base_BOM where bo_id=@bo_id";
                su.ExecuteNonQuery(sql, par);
            }
            catch (Exception err)
            {
                return err.Message;
            }
            //提交事务
            scope.Complete();
        }
        return "success";
    }
    /// <summary>
    /// 验证父件是否存在
    /// </summary>
    /// <param name="p_i_id">存货ID</param>
    /// <returns>bool</returns>
    public bool CheckExist(string p_i_id)
    {
        string sql = "select count(1) from Base_BOM where p_i_id=@p_i_id";
        SqlParameter[] par ={
                new SqlParameter("@p_i_id", p_i_id)
            };
        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(bo_id),0) from Base_BOM";
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, null));
    }
    /// <summary>
    /// 得到上一个ID
    /// </summary>
    /// <param name="current_id">当前单据ID</param>
    /// <param name="type">上翻还是下翻</param>
    /// <param name="order_type">单据类型</param>
    /// <returns>单据ID</returns>
    public int GetPrevNextID(int current_id, string type)
    {
        string sql = "select isnull(max(bo_id),0) from Base_BOM where (bo_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(bo_id),0) from Base_BOM where bo_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="bo_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int bo_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.bos_id,i.i_code+' ' +i.i_name as info,
	                    case when
                        isnull(d.d_wh_id,'')='' 
                        then '未填仓库'
	                    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.bo_quantity,0)=0
	                    then '未填单件用量'
	                    else 'success' end as msg
	                    from Base_BOMs d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.bo_id=@bo_id
                    )t where msg!='success'
                    order by bos_id";
        SqlParameter[] par ={
                new SqlParameter("@bo_id", bo_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="id">主表id</param>
    /// <param name="wh_id">仓库id</param>
    public void BatchSetWarehouse(int bo_id, int wh_id)
    {
        string sql = @"
                update Base_BOMs set
                d_wh_id=@wh_id where bo_id=@bo_id";
        SqlParameter[] par = {
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@bo_id", bo_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    /// <summary>
    /// 批量设置表体部门
    /// </summary>
    /// <param name="id">主表id</param>
    /// <param name="dep_id">部门id</param>
    public void BatchSetDepartment(int bo_id, int dep_id)
    {
        string sql = @"
                update Base_BOMs set
                d_dep_id=@dep_id where bo_id=@bo_id";
        SqlParameter[] par = {
            new SqlParameter("@dep_id", dep_id),
            new SqlParameter("@bo_id", bo_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

}
