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 StockCountDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(StockCount m)
    {

        string sql = @"insert Stock_StockCount(order_number,wh_id,iot_id_in,iot_id_out,
                                    dep_id,ele_id,remark,
                                    new_uid,new_user,new_date,
                                    update_uid,update_user,update_date,
                                    verify_uid,verify_user,
                                    sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                                    ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select  @order_number,@wh_id,@iot_id_in,@iot_id_out,
                                @dep_id,@ele_id,@remark,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user,
                                @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                                @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@iot_id_in",XTools.IntToDBNull(m.iot_id_in)),
            new SqlParameter("@iot_id_out",XTools.IntToDBNull(m.iot_id_out)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_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("@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);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">sc_id</param>
    /// <returns>StockCount</returns>
    public StockCount Get(int sc_id)
    {
        StockCount m = new StockCount();
        string sql = @"select 
                        convert(char(10),t.new_date,23) as new_date_format,
                        wh.wh_code,wh.wh_name,d.dep_code,
                        d.dep_name,it1.iot_name as iot_name_in,it2.iot_name as iot_name_out,
                        t.*
                        from Stock_StockCount t
                        left join Base_Warehouse wh on t.wh_id=wh.wh_id
                        left join Base_Department d on t.dep_id=d.dep_id
                        left join Base_IOType it1 on t.iot_id_in=it1.iot_id
                        left join Base_IOType it2 on t.iot_id_out=it2.iot_id
                        where sc_id=@sc_id";
        sql = sql.Replace("@sc_id", sc_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.sc_id = Convert.ToInt32(r["sc_id"]);
                m.order_number = r["order_number"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                m.wh_id = Convert.ToInt32(r["wh_id"]);
                if (r["wh_id"] != DBNull.Value)
                    m.warehouse = new WarehouseDAL().Get(Convert.ToInt32(m.wh_id));
                m.iot_id_in = XTools.DBNullToInt(r["iot_id_in"]);
                if (r["iot_id_in"] != DBNull.Value)
                    m.io_type_in = new IOTypeDAL().Get(Convert.ToInt32(m.iot_id_in));
                m.iot_id_out = XTools.DBNullToInt(r["iot_id_out"]);
                if (r["iot_id_out"] != DBNull.Value)
                    m.io_type_out = new IOTypeDAL().Get(Convert.ToInt32(m.iot_id_out));
                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.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"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(StockCount m)
    {
        string sql = @"update Stock_StockCount set 
                        order_number=@order_number,
                        wh_id=@wh_id,
                        iot_id_in=@iot_id_in,
                        iot_id_out=@iot_id_out,
                        dep_id=@dep_id,
                        ele_id=@ele_id,
                        remark=@remark,
                        new_uid=@new_uid,
                        new_user=@new_user,
                        new_date=@new_date,
                        update_uid=@update_uid,
                        update_user=@update_user,
                        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 sc_id=@sc_id";
        SqlParameter[] par = {
            new SqlParameter("@sc_id",m.sc_id),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@iot_id_in",XTools.IntToDBNull(m.iot_id_in)),
            new SqlParameter("@iot_id_out",XTools.IntToDBNull(m.iot_id_out)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_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 sc_id)
    {
        StockCountDAL dal = new StockCountDAL();
        SqlUtil su = new SqlUtil();
        SqlParameter[] par = {
                new SqlParameter("@sc_id",sc_id)
            };
        //启用事务
        using (TransactionScope scope = new TransactionScope())
        {
            //删除子表
            new StockCountsDAL().DeleteByOrder(sc_id);
            //删除主表
            string sql = "delete Stock_StockCount where sc_id=@sc_id";
            su.ExecuteNonQuery(sql, par);
            //提交事务
            scope.Complete();
        }
        return "success";
    }
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">编码</param>
    /// <param name="order_type">单据类型</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from Stock_StockCount 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(sc_id),0) from Stock_StockCount";
        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(sc_id),0) from Stock_StockCount where (sc_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(sc_id),0) from Stock_StockCount where sc_id>@current_id";
        }
        //当前用户是否管理员
        bool is_admin = new AuthUI().CheckLoginUserIsAdmin();
        if (!is_admin)
        {
            //值为1代表启用部门权限控制
            string system_dep_auth = new ConfigReader().GetConfig("system_dep_auth");
            if (system_dep_auth.Equals("1"))
            {
                //只有客户专管业务员或业务主管才有权限看到
                sql += " and dep_id in (" + new AuthEx().GetSqlForMyDepartment() + ")";
            }
            //值为1代表启用仓库权限控制
            string system_wh_auth = new ConfigReader().GetConfig("system_wh_auth");
            if (system_wh_auth.Equals("1"))
            {
                sql += " and wh_id in (" + new AuthEx().GetSqlForMyWarehouse() + ")";
            }
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="sc_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int sc_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.scs_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
	                    i.on_batch=1 and isnull(d.batch,'')=''
	                    then '未填批次'
                        when
	                    d.quantity is null
	                    then '未填盘点数量'
	                    else 'success' end as msg
	                    from Stock_StockCounts d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.sc_id=@sc_id
                    )t where msg!='success'
                    order by scs_id";
        SqlParameter[] par ={
                new SqlParameter("@sc_id", sc_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="sc_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckOverStock(int sc_id)
    {
        //账面数-盘点数>0时，代表要出库，所以要判断现存量
        string sql = @"SELECT top 1 info FROM
                    (
                        select i.i_code+' ' +i.i_name
                        +' 数量:'+cast(x.quantity as varchar(50))+' 超现存量:'+cast(isnull(oh.quantity,0) as varchar(50)) as info
                        from 
                        (
	                        select t.sc_id,t.wh_id,d.i_id,
                            isnull(d.free1,'') as free1,isnull(d.free2,'') as free2,
	                        isnull(d.batch,'') as batch,
                            sum(isnull(d.quantity_zm,0)-isnull(d.quantity,0)) as quantity
	                        from Stock_StockCounts d
	                        left join Stock_StockCount t on t.sc_id=d.sc_id
                            where d.sc_id=@sc_id
	                        group by t.sc_id,t.wh_id,d.i_id,isnull(d.free1,''),isnull(d.free2,''),isnull(d.batch,'')
                        )x
                        left join Base_Inventory i on x.i_id=i.i_id
                        left join Stock_OnHand oh on oh.i_id=x.i_id
                        and oh.wh_id=x.wh_id
                        and isnull(oh.free1,'')=isnull(x.free1,'') 
                        and isnull(oh.free2,'')=isnull(x.free2,'')
                        and isnull(oh.batch,'')=isnull(x.batch,'')
                        where isnull(x.quantity,0)>0 and x.quantity>isnull(oh.quantity,0)
                    )t";
        SqlParameter[] par ={
                new SqlParameter("@sc_id", sc_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 根据整张出入库单更新现存量
    /// </summary>
    /// <param name="sc_id">进出单主表id</param>
    /// <param name="direction">方向：1,加现存量;0,减现存量</param>
    public void UpdateOhHandByOrder(int sc_id, int direction)
    {
        string sql = @"
            insert Stock_OnHand(wh_id,i_id,batch,free1,free2)
            select d.d_wh_id,d.i_id,d.batch,d.free1,d.free2
            from Stock_StockCount t 
            left join Stock_StockCounts d on t.sc_id=d.sc_id
            left join Stock_OnHand oh on oh.wh_id=d.d_wh_id
            and oh.i_id=d.i_id
            and isnull(oh.batch,'')=isnull(d.batch,'')
            and isnull(oh.free1,'')=isnull(d.free1,'')
            and isnull(oh.free2,'')=isnull(d.free2,'')
            where d.sc_id=@sc_id and d.i_id is not null and oh.i_id is null
            group by d.d_wh_id,d.i_id,d.batch,d.free1,d.free2";
        SqlParameter[] par = {
            new SqlParameter("@sc_id", sc_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
        sql = @"
            update Stock_OnHand set 
            quantity=isnull(Stock_OnHand.quantity,0) @plus isnull(x.quantity,0),
            num=isnull(Stock_OnHand.num,0) @plus isnull(x.num,0)
            from 
            (
	            select t.sc_id,t.io_flag,d.d_wh_id as wh_id,d.i_id,
                isnull(d.free1,'') as free1,isnull(d.free2,'') as free2,
	            isnull(d.batch,'') as batch,
                sum(d.quantity) as quantity,sum(d.num) as num
	            from Stock_StockCounts d
	            left join Stock_StockCount t on t.sc_id=d.sc_id
                where d.sc_id=@sc_id
	            group by t.sc_id,t.io_flag,d.d_wh_id,d.i_id,isnull(d.free1,''),isnull(d.free2,''),isnull(d.batch,'')
            )x where Stock_OnHand.wh_id=x.wh_id and Stock_OnHand.i_id=x.i_id
            and isnull(Stock_OnHand.batch,'')=isnull(x.batch,'')
            and isnull(Stock_OnHand.free1,'')=isnull(x.free1,'')
            and isnull(Stock_OnHand.free2,'')=isnull(x.free2,'')";
        
        if (direction == 1)
        {
            //加现存量
            sql = sql.Replace("@plus", "+");
        }
        else
        {
            //减现存量
            sql = sql.Replace("@plus", "-");
        }
        sql = sql.Replace("@sc_id", sc_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);

    }
    /// <summary>
    /// 批量设置表体仓库
    /// </summary>
    /// <param name="id">主表id</param>
    /// <param name="wh_id">仓库id</param>
    public void BatchSetWarehouse(int sc_id, int wh_id)
    {
        string sql = @"
                update Stock_StockCounts set
                d_wh_id=@wh_id where sc_id=@sc_id";
        SqlParameter[] par = {
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@sc_id", sc_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

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

    /// <summary>
    /// 盘库/校正
    /// </summary>
    /// <param name="sc_id">主表id</param>
    /// <param name="type">1 盘库,2较正</param>
    public void PanKu(int type,int sc_id,int wh_id, DateTime? pd_date)
    {
        string sql = "";
        if (type == 1)//盘库
        {
            sql = @"
                delete Stock_StockCounts where sc_id=@sc_id;
                insert into Stock_StockCounts(sc_id,i_id,free1,free2,batch,quantity_zm,num_zm,quantity,num,convert_rate)
                select @sc_id,i_id,isnull(free1,'') as free1,isnull(free2,'') as free2,isnull(batch,'') as batch,
                sum(quantity) as quantity_zm,sum(num) as num_zm,sum(quantity) as quantity,sum(num) as num,convert_rate
                from
                (
                    select d.i_id,d.free1,d.free2,d.batch,
                    case when io_flag=1 then d.quantity else -d.quantity end as quantity,
                    case when io_flag=1 then d.num else -d.num end as num,
                    case when isnull(i.aunit,'')<>'' then i.convert_rate else null end as convert_rate
                    from Stock_InOuts d
                    left join Stock_InOut t on t.io_id=d.io_id
                    left join Base_Inventory i on d.i_id=i.i_id
                    where isnull(t.verify_uid,0)!=0
	                and t.new_date<=@pd_date and d.d_wh_id=@wh_id
                )t group by i_id,isnull(free1,''),isnull(free2,''),isnull(batch,''),convert_rate;";
        }
        else//校正
        {
            sql = @"
                update Stock_StockCounts set quantity_zm=0,num_zm=0 where sc_id=@sc_id;
                update Stock_StockCounts set 
                quantity_zm=t.quantity_zm,num_zm=t.num_zm
                from(
	                select i_id,isnull(free1,'') as free1,isnull(free2,'') as free2,isnull(batch,'') as batch,
	                sum(quantity) as quantity_zm,sum(num) as num_zm
	                from
	                (
		                select d.i_id,d.free1,d.free2,d.batch,
		                case when io_flag=1 then d.quantity else -d.quantity end as quantity,
		                case when io_flag=1 then d.num else -d.num end as num
		                from Stock_InOuts d
		                left join Stock_InOut t on t.io_id=d.io_id
		                where isnull(t.verify_uid,0)!=0
		                and t.new_date<=@pd_date and d.d_wh_id=@wh_id
	                )x group by i_id,isnull(free1,''),isnull(free2,''),isnull(batch,'')
                )t
                where Stock_StockCounts.sc_id=@sc_id
                and Stock_StockCounts.i_id=t.i_id
                and isnull(Stock_StockCounts.free1,'')=isnull(t.free1,'')
                and isnull(Stock_StockCounts.free2,'')=isnull(t.free2,'')
                and isnull(Stock_StockCounts.batch,'')=isnull(t.batch,'');";

        }
        sql += @"
                update Stock_StockCounts set free1=null where sc_id=@sc_id and free1='';
                update Stock_StockCounts set free2=null where sc_id=@sc_id and free2='';
                update Stock_StockCounts set batch=null where sc_id=@sc_id and batch='';";
        SqlParameter[] par = {
            new SqlParameter("@sc_id", sc_id),
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@pd_date", pd_date)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

}
