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

        string sql = @"insert Stock_Transfer(order_number,
                                    wh_id_out,wh_id_in,
                                    iot_id_out,iot_id_in,
                                    dep_id_out,dep_id_in,
                                    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_out,@wh_id_in,
                                @iot_id_out,@iot_id_in,
                                @dep_id_out,@dep_id_in,
                                @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_out",XTools.IntToDBNull(m.wh_id_out)),
            new SqlParameter("@wh_id_in",XTools.IntToDBNull(m.wh_id_in)),
            new SqlParameter("@iot_id_out",XTools.IntToDBNull(m.iot_id_out)),
            new SqlParameter("@iot_id_in",XTools.IntToDBNull(m.iot_id_in)),
            new SqlParameter("@dep_id_out",XTools.IntToDBNull(m.dep_id_out)),
            new SqlParameter("@dep_id_in",XTools.IntToDBNull(m.dep_id_in)),
            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">ts_id</param>
    /// <returns>Transfer</returns>
    public Transfer Get(int ts_id)
    {
        Transfer m = new Transfer();
        string sql = @"select ts.*,
                    convert(char(10),ts.new_date,23) as new_date_format,
                    wh_out.wh_name as wh_name_out,wh_in.wh_name as wh_name_in,
                    dep_out.dep_name as dep_name_out,dep_in.dep_name as dep_name_in,
                    iot_out.iot_name as iot_name_out,iot_in.iot_name as iot_name_in
                    from Stock_Transfer ts 
                    left join Base_Warehouse wh_out on ts.wh_id_out=wh_out.wh_id
                    left join Base_Warehouse wh_in on ts.wh_id_in=wh_in.wh_id
                    left join Base_Department dep_out on ts.dep_id_out=dep_out.dep_id
                    left join Base_Department dep_in on ts.dep_id_in=dep_in.dep_id
                    left join Base_IOType iot_out on ts.iot_id_out=iot_out.iot_id
                    left join Base_IOType iot_in on ts.iot_id_in=iot_in.iot_id
                    where ts.ts_id=@ts_id";
        sql = sql.Replace("@ts_id", ts_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.ts_id = Convert.ToInt32(r["ts_id"]);
                m.order_number = r["order_number"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                m.wh_id_out = Convert.ToInt32(r["wh_id_out"]);
                if (r["wh_id_out"] != DBNull.Value)
                    m.warehouse_out = new WarehouseDAL().Get(Convert.ToInt32(m.wh_id_out));
                m.wh_id_in = Convert.ToInt32(r["wh_id_in"]);
                if (r["wh_id_in"] != DBNull.Value)
                    m.warehouse_in = new WarehouseDAL().Get(Convert.ToInt32(m.wh_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.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.dep_id_out = XTools.DBNullToInt(r["dep_id_out"]);
                if (r["dep_id_out"] != DBNull.Value)
                    m.department_out = new DepartmentDAL().Get(Convert.ToInt32(m.dep_id_out));
                m.dep_id_in = XTools.DBNullToInt(r["dep_id_in"]);
                if (r["dep_id_in"] != DBNull.Value)
                    m.department_in = new DepartmentDAL().Get(Convert.ToInt32(m.dep_id_in));
                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(Transfer m)
    {
        string sql = @"update Stock_Transfer set 
                        order_number=@order_number,
                        wh_id_out=@wh_id_out,wh_id_in=@wh_id_in,
                        iot_id_out=@iot_id_out,iot_id_in=@iot_id_in,
                        dep_id_out=@dep_id_out,dep_id_in=@dep_id_in,
                        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 ts_id=@ts_id";
        SqlParameter[] par = {
            new SqlParameter("@ts_id",m.ts_id),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@wh_id_out",XTools.IntToDBNull(m.wh_id_out)),
            new SqlParameter("@wh_id_in",XTools.IntToDBNull(m.wh_id_in)),
            new SqlParameter("@iot_id_out",XTools.IntToDBNull(m.iot_id_out)),
            new SqlParameter("@iot_id_in",XTools.IntToDBNull(m.iot_id_in)),
            new SqlParameter("@dep_id_out",XTools.IntToDBNull(m.dep_id_out)),
            new SqlParameter("@dep_id_in",XTools.IntToDBNull(m.dep_id_in)),
            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 ts_id)
    {
        TransferDAL dal = new TransferDAL();
        SqlUtil su = new SqlUtil();
        SqlParameter[] par = {
                new SqlParameter("@ts_id",ts_id)
            };
        //启用事务
        using (TransactionScope scope = new TransactionScope())
        {
            //删除子表
            new TransfersDAL().DeleteByOrder(ts_id);
            //删除主表
            string sql = "delete Stock_Transfer where ts_id=@ts_id";
            su.ExecuteNonQuery(sql, par);
            //提交事务
            scope.Complete();
        }
        return "success";
    }
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from Stock_Transfer 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(ts_id),0) from Stock_Transfer";
        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(ts_id),0) from Stock_Transfer where (ts_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(ts_id),0) from Stock_Transfer where ts_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="ts_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int ts_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.tss_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
	                    isnull(d.quantity,0)=0
	                    then '未填数量'
	                    else 'success' end as msg
	                    from Stock_Transfers d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.ts_id=@ts_id
                    )t where msg!='success'
                    order by tss_id";
        SqlParameter[] par ={
                new SqlParameter("@ts_id", ts_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="ts_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckOverStock(int ts_id)
    {
        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.ts_id,t.wh_id_out 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
	                        from Stock_Transfers d
	                        left join Stock_Transfer t on t.ts_id=d.ts_id
                            where d.ts_id=@ts_id
	                        group by t.ts_id,t.wh_id_out,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("@ts_id", ts_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="id">主表id</param>
    /// <param name="wh_id">仓库id</param>
    public void BatchSetWarehouse(int ts_id, int wh_id)
    {
        string sql = @"
                update Stock_Transfers set
                d_wh_id=@wh_id where ts_id=@ts_id";
        SqlParameter[] par = {
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@ts_id", ts_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

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

}
