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

        string sql = @"insert Stock_InOut(io_flag,order_type,order_number,wh_id,iot_id,vd_id,cus_id,
                                    pt_id,st_id,dep_id,ele_id,tax_rate,remark,
                                    sw_id,ship_address,contact,phone,
                                    new_uid,new_user,new_date,
                                    update_uid,update_user,update_date,
                                    verify_uid,verify_user,
                                    from_id,from_type,bus_type,
                                    sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                                    ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select  @io_flag,@order_type,@order_number,@wh_id,@iot_id,@vd_id,@cus_id,
                                @pt_id,@st_id,@dep_id,@ele_id,@tax_rate,@remark,
                                @sw_id,@ship_address,@contact,@phone,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user,
                                @from_id,@from_type,@bus_type,
                                @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                                @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@io_flag",m.io_flag),
            new SqlParameter("@order_type",m.order_type),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@iot_id",XTools.IntToDBNull(m.iot_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@pt_id",XTools.IntToDBNull(m.pt_id)),
            new SqlParameter("@st_id",XTools.IntToDBNull(m.st_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@sw_id",XTools.IntToDBNull(m.sw_id)),
            new SqlParameter("@ship_address",XTools.StringToDBNull(m.ship_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            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("@from_id",XTools.IntToDBNull(m.from_id)),
            new SqlParameter("@from_type",XTools.StringToDBNull(m.from_type)),
            new SqlParameter("@bus_type",XTools.StringToDBNull(m.bus_type)),
            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">io_id</param>
    /// <returns>InOut</returns>
    public InOut Get(int io_id)
    {
        InOut m = new InOut();
        string sql = @"select t.*,convert(char(10),t.new_date,23) as new_date_format,
                        wh.wh_code,wh.wh_name,d.dep_code,
                        d.dep_name,it.iot_name,cus.cus_name,vd.vd_name,
                        ele.ele_name,st.st_name,pt.pt_name,sw.sw_name
                        from Stock_InOut 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_Customer cus on t.cus_id=cus.cus_id
                        left join Base_Vendor vd on t.vd_id=vd.vd_id
                        left join Base_IOType it on t.iot_id=it.iot_id
                        left join Base_Employee ele on t.ele_id=ele.ele_id
                        left join Base_ShipWay sw on t.sw_id=sw.sw_id
                        left join Base_SaleType st on t.st_id=st.st_id
                        left join Base_PurchaseType pt on t.pt_id=pt.pt_id
                        where io_id=@io_id";
        sql = sql.Replace("@io_id", io_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.io_id = Convert.ToInt32(r["io_id"]);
                m.io_flag = Convert.ToInt32(r["io_flag"]);
                m.order_type = Convert.ToInt32(r["order_type"]);
                m.order_number = r["order_number"].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.vd_id = XTools.DBNullToInt(r["vd_id"]);
                if (r["vd_id"] != DBNull.Value)
                    m.vendor = new VendorDAL().Get(Convert.ToInt32(m.vd_id));

                m.cus_id = XTools.DBNullToInt(r["cus_id"]);
                if (r["cus_id"] != DBNull.Value)
                    m.customer = new CustomerDAL().Get(Convert.ToInt32(m.cus_id));

                m.iot_id = XTools.DBNullToInt(r["iot_id"]);
                if(r["iot_id"] != DBNull.Value)
                    m.io_type = new IOTypeDAL().Get(Convert.ToInt32(m.iot_id));
                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.pt_id = XTools.DBNullToInt(r["pt_id"]);
                if (r["pt_id"] != DBNull.Value)
                    m.purchasetype = new PurchaseTypeDAL().Get(Convert.ToInt32(m.pt_id));
                m.st_id = XTools.DBNullToInt(r["st_id"]);
                if (r["st_id"] != DBNull.Value)
                    m.saletype = new SaleTypeDAL().Get(Convert.ToInt32(m.st_id));
                m.sw_id = XTools.DBNullToInt(r["sw_id"]);
                if (r["sw_id"] != DBNull.Value)
                    m.ship_way = new ShipWayDAL().Get(Convert.ToInt32(m.sw_id));
                m.tax_rate = XTools.DBNullToDecimal(r["tax_rate"]);
                m.ship_address = r["ship_address"].ToString();
                m.contact = r["contact"].ToString();
                m.phone = r["phone"].ToString();
                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.from_id = XTools.DBNullToInt(r["from_id"]);
                m.from_type = r["from_type"].ToString();
                m.bus_type = r["bus_type"].ToString();
                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(InOut m)
    {
        string sql = @"update Stock_InOut set 
                        io_flag=@io_flag,
                        order_number=@order_number,
                        wh_id=@wh_id,
                        vd_id=@vd_id,
                        cus_id=@cus_id,
                        iot_id=@iot_id,
                        pt_id=@pt_id,
                        st_id=@st_id,
                        dep_id=@dep_id,
                        ele_id=@ele_id,
                        tax_rate=@tax_rate,
                        sw_id=@sw_id,
                        ship_address=@ship_address,
                        contact=@contact,
                        phone=@phone,
                        remark=@remark,
                        from_id=@from_id,
                        from_type=@from_type,
                        bus_type=@bus_type,
                        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 io_id=@io_id";
        SqlParameter[] par = {
            new SqlParameter("@io_id",m.io_id),
            new SqlParameter("@io_flag",m.io_flag),
            new SqlParameter("@order_type",m.order_type),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@iot_id",XTools.IntToDBNull(m.iot_id)),
            new SqlParameter("@pt_id",XTools.IntToDBNull(m.pt_id)),
            new SqlParameter("@st_id",XTools.IntToDBNull(m.st_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@sw_id",XTools.IntToDBNull(m.sw_id)),
            new SqlParameter("@ship_address",XTools.StringToDBNull(m.ship_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            new SqlParameter("@from_id",XTools.IntToDBNull(m.from_id)),
            new SqlParameter("@from_type",XTools.StringToDBNull(m.from_type)),
            new SqlParameter("@bus_type",XTools.StringToDBNull(m.bus_type)),
            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 io_id, int order_type)
    {
        InOutDAL dal = new InOutDAL();
        SqlUtil su = new SqlUtil();
        SqlParameter[] par = {
                new SqlParameter("@io_id",io_id)
            };
         //删除子表
        new InOutsDAL().DeleteByOrder(io_id);
        //删除主表
        string sql = "delete Stock_InOut where io_id=@io_id";
        su.ExecuteNonQuery(sql, par);
        return "success";
    }
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">编码</param>
    /// <param name="order_type">单据类型</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number, int order_type)
    {
        string sql = "select count(1) from Stock_InOut where order_type=@order_type and order_number=@order_number";
        SqlParameter[] par ={
                new SqlParameter("@order_type", order_type),
                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(io_id),0) from Stock_InOut";
        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, int order_type)
    {
        string sql = "select isnull(max(io_id),0) from Stock_InOut where (io_id<@current_id or isnull(@current_id,0)=0) and order_type=@order_type";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(io_id),0) from Stock_InOut where io_id>@current_id and order_type=@order_type";
        }
        //当前用户是否管理员
        bool is_admin = new AuthUI().CheckLoginUserIsAdmin();
        if (!is_admin)
        {
            //3,采购入库单 6,销售出库单
            if (order_type == 3 || order_type == 6)
            {
                //值为1代表启用业务员权限控制
                string sale_employee_auth = new ConfigReader().GetConfig("sale_employee_auth");
                if (sale_employee_auth.Equals("1"))
                {
                    //只有客户专管业务员或业务主管才有权限看到
                    sql += " and cus.ele_id in (" + new AuthEx().GetSqlForMyEmployee() + ")";
                }
            }
            //值为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),
                new SqlParameter("@order_type", order_type)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="io_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int io_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.ios_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
	                    i.on_batch=1 and isnull(d.batch,'')=''
	                    then '未填批次'
                        when
	                    isnull(d.quantity,0)=0
	                    then '未填数量'
	                    else 'success' end as msg
	                    from Stock_InOuts d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.io_id=@io_id
                    )t where msg!='success'
                    order by ios_id";
        SqlParameter[] par ={
                new SqlParameter("@io_id", io_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="io_id">单据ID</param>
    /// <param name="action_type">1审核操作,-1弃审操作</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckOverStock(int io_id, int action_type)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select wh_name + ' ' + i.i_code+' ' +i.i_name + ' ' + x.free1 + ' ' + x.free2 + ' ' + x.batch
                        +' 数量:'+cast(x.quantity as varchar(50))+' 超现存量:'+cast(isnull(oh.quantity,0) as varchar(50)) as info
                        from 
                        (
	                        select wh.wh_name,t.io_id,t.io_flag,d.d_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_InOuts d
	                        left join Stock_InOut t on t.io_id=d.io_id
                            left join Base_Warehouse wh on wh.wh_id=d.d_wh_id
                            where d.io_id=@io_id and wh.is_control_stock=1
	                        group by wh.wh_name,t.io_id,t.io_flag,d.d_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.d_wh_id
                        and isnull(oh.free1,'')=isnull(x.free1,'') 
                        and isnull(oh.free2,'')=isnull(x.free2,'')
                        and isnull(oh.batch,'')=isnull(x.batch,'')
                        where (io_flag=1 and isnull(x.quantity,0) [action_type_1] 0 and abs(x.quantity)>isnull(oh.quantity,0))
                        or (io_flag=0 and isnull(x.quantity,0) [action_type_0] 0 and abs(x.quantity)>isnull(oh.quantity,0))
                    )t";
        SqlParameter[] par ={
                new SqlParameter("@io_id", io_id)
            };
        if (action_type == 1)
        {
            //审核时，入库单数量小于零(负数) 或 出库单数量大于零(正数)，则判断现存量
            sql = sql.Replace("[action_type_1]", "<");
            sql = sql.Replace("[action_type_0]", ">");
        }
        else
        {
            //弃审时，入库单数量大于零(正数) 或 出库单数量小于零(负数)，则判断现存量
            sql = sql.Replace("[action_type_1]", ">");
            sql = sql.Replace("[action_type_0]", "<");
        }
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 根据整张出入库单更新现存量
    /// </summary>
    /// <param name="io_id">进出单主表id</param>
    /// <param name="direction">方向：1,加现存量;0,减现存量</param>
    public void UpdateOhHandByOrder(int io_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_InOut t 
            left join Stock_InOuts d on t.io_id=d.io_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.io_id=@io_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("@io_id", io_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.io_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_InOuts d
	            left join Stock_InOut t on t.io_id=d.io_id
                where d.io_id=@io_id
	            group by t.io_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("@io_id", io_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);

    }
    /// <summary>
    /// 更新采购订单累计入库数
    /// </summary>
    /// <param name="io_id">进出单主表id</param>
    /// <param name="direction">方向：1,加累入库数;-1,减累入库数</param>
    public void UpdatePurchaseInQuantityByOrder(int io_id, int direction)
    {
        string sql = @"
                update Purchase_PurchaseOrders set
                acc_in_quantity=isnull(acc_in_quantity,0) @plus isnull(t.quantity,0),
                acc_in_num=isnull(acc_in_num,0) @plus isnull(t.num,0)
                from(
                    select pos_id,sum(d.quantity) as quantity,sum(d.num) as num
                    from Stock_InOuts d 
                    where d.io_id=@io_id and isnull(pos_id,0)!=0
                    group by pos_id 
                )t where Purchase_PurchaseOrders.pos_id=t.pos_id";
        if (direction == 1)
        {
            //加现存量
            sql = sql.Replace("@plus", "+");
        }
        else
        {
            //减现存量
            sql = sql.Replace("@plus", "-");
        }
        sql = sql.Replace("@io_id", io_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);
    }

    /// <summary>
    /// 检查是否超采购订单入库
    /// </summary>
    /// <param name="io_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckPurchaseInOverOrder(int io_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select i.i_code+' '+i.i_name
                        +' 入库数量:'+cast(t.quantity+isnull(pos.acc_in_quantity,0) as varchar(50))+' 超订单数量:'+cast(isnull(pos.quantity,0) as varchar(50)) as info
                        from 
                        (
                            select pos_id,sum(d.quantity) as quantity
                            from Stock_InOuts d 
                            where d.io_id=@io_id and isnull(ios_id,0)!=0 and isnull(pos_id,0)<>0
                            group by pos_id
                        ) t left join Purchase_PurchaseOrders pos on pos.pos_id=t.pos_id
                        left join Base_Inventory i on i.i_id=pos.i_id
                        where round(isnull(t.quantity,0)+isnull(pos.acc_in_quantity,0),10)>round(isnull(pos.quantity,0),10)
                        and t.quantity>0 and pos.quantity>0
                    )x";
        SqlParameter[] par ={
                new SqlParameter("@io_id", io_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="io_id">进出单主表id</param>
    /// <param name="direction">方向：1,加累出库数;0,减累出库数</param>
    public void UpdateSalesOutQuantityByOrder(int io_id, int direction)
    {
        string sql = @"
                update Sale_DeliveryOrders set
                acc_out_quantity=isnull(acc_out_quantity,0) @plus isnull(t.quantity,0),
                acc_out_num=isnull(acc_out_num,0) @plus isnull(t.num,0)
                from(
                    select dos_id,sum(d.quantity) as quantity,sum(d.num) as num
                    from Stock_InOuts d 
                    where d.io_id=@io_id and isnull(dos_id,0)!=0
                    group by dos_id 
                )t where Sale_DeliveryOrders.dos_id=t.dos_id";
        if (direction == 1)
        {
            //加累计出库数
            sql = sql.Replace("@plus", "+");
        }
        else
        {
            //减累计出库数
            sql = sql.Replace("@plus", "-");
        }
        sql = sql.Replace("@io_id", io_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);
    }

    /// <summary>
    /// 检查是否超发货单出库
    /// </summary>
    /// <param name="io_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckSalesOutOverDeliveryOrder(int io_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select i.i_code+' '+i.i_name
                        +' 出库数量:'+cast(t.quantity as varchar(50))+' 超发货单数量:'+cast(isnull(dos.quantity,0) as varchar(50)) as info
                        from 
                        (
                            select dos_id,sum(d.quantity) as quantity
                            from Stock_InOuts d 
                            where d.io_id=@io_id and isnull(ios_id,0)!=0 and isnull(dos_id,0)<>0
                            group by dos_id
                        ) t left join Sale_DeliveryOrders dos on dos.dos_id=t.dos_id
                        left join Base_Inventory i on i.i_id=dos.i_id
                        where round(isnull(t.quantity,0),10)>round(isnull(dos.quantity,0),10)
                        and t.quantity>0 and dos.quantity>0
                    )x";
        SqlParameter[] par ={
                new SqlParameter("@io_id", io_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 io_id, int wh_id)
    {
        string sql = @"
                update Stock_InOuts set
                d_wh_id=@wh_id where io_id=@io_id";
        SqlParameter[] par = {
            new SqlParameter("@wh_id", wh_id),
            new SqlParameter("@io_id", io_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    /// <summary>
    /// 批量设置表体部门
    /// </summary>
    /// <param name="id">主表id</param>
    /// <param name="dep_id">部门id</param>
    public void BatchSetDepartment(int io_id, int dep_id)
    {
        string sql = @"
                update Stock_InOuts set
                d_dep_id=@dep_id where io_id=@io_id";
        SqlParameter[] par = {
            new SqlParameter("@dep_id", dep_id),
            new SqlParameter("@io_id", io_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 判断是否已发生对账业务
    /// </summary>
    /// <param name="io_id"></param>
    /// <returns>bool</returns>
    public bool CheckIsDuiZhang(int io_id)
    {
        string sql = @"
                    select count(1) from Stock_InOuts
                    where io_id=@io_id and 
                    (
                        isnull(dz_quantity,0)<>0
                        or
                        ios_id in
                        (
	                        select distinct ios_id from ARAP_CheckAccounts where ios_id is not null
                        )
                    )";
        SqlParameter[] par = {
            new SqlParameter("@io_id", io_id)
        };
        if (Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par)) > 0)
            return true;
        return false;
    }

    /// <summary>
    /// 通过销售出库单或采购入库单生成对账单
    /// </summary>
    /// <param name="io_id">进出单主表ID</param>
    /// <returns>返回success代表成功</returns>
    public string InOutToCheckAccount(int io_id, int action_type)
    {
        InOutDAL io_dal = new InOutDAL();
        InOutsDAL ios_dal = new InOutsDAL();
        CheckAccount ca = new CheckAccount();
        CheckAccountsDAL cas_dal = new CheckAccountsDAL();
        CheckAccountDAL ca_dal = new CheckAccountDAL();
        SqlUtil su = new SqlUtil();
        InOut m = Get(io_id);
        string sql_crc = "";
        //如果是入库
        if (m.io_flag == 1)
        {
            ca.rp_flag = "AP";//应付
            ca.from_type = "采购入库单";
            sql_crc = @"
            select po.crc_id,po.exchange_rate,po.tax_rate
            from Stock_InOut t
            left join Stock_InOuts d on t.io_id=d.io_id
            left join Purchase_PurchaseOrders pos on pos.pos_id=d.pos_id
            left join Purchase_PurchaseOrder po on po.po_id=pos.po_id
            where t.io_id = @io_id;";
        }
        else
        {
            ca.rp_flag = "AR";//应收
            ca.from_type = "销售出库单";
            sql_crc = @"
            select do.crc_id,do.exchange_rate,do.tax_rate
            from Stock_InOut t
            left join Stock_InOuts d on t.io_id=d.io_id
            left join Sale_DeliveryOrders dos on dos.dos_id=d.dos_id
            left join Sale_DeliveryOrder do on do.do_id=dos.do_id
            where t.io_id = @io_id;";

        }
        sql_crc = sql_crc.Replace("@io_id", io_id.ToString());
        DataRow dr_crc = su.GetDataTable(sql_crc, null).Rows[0];
        
        ca.order_number = m.order_number;//对账单号=进出单号
        ca.from_id = m.io_id;
        ca.cus_id = m.cus_id;
        ca.vd_id = m.vd_id;
        ca.ele_id = m.ele_id;
        ca.wh_id = m.wh_id;
        ca.dep_id = m.dep_id;
        if (dr_crc["crc_id"] != DBNull.Value)
        {
            ca.crc_id = XTools.DBNullToInt(dr_crc["crc_id"]);
            ca.exchange_rate = XTools.DBNullToDecimal(dr_crc["exchange_rate"]);
            ca.tax_rate = XTools.DBNullToDecimal(dr_crc["tax_rate"]);
        }
        else
        {
            //没币种时使用本位币
            ca.crc_id = new CurrencyDAL().GetStandard().crc_id;
            ca.exchange_rate = 1;
            ca.tax_rate = 0;
        }
        ca.new_date = m.new_date;
        ca.new_uid = m.new_uid;
        ca.new_user = m.new_user;
        ca.verify_uid = m.verify_uid;
        ca.verify_user = m.verify_user;
        ca.remark = m.remark;
        ca.sdefine1 = m.sdefine1;
        ca.sdefine2 = m.sdefine2;
        ca.sdefine3 = m.sdefine3;
        ca.sdefine4 = m.sdefine4;
        ca.sdefine5 = m.sdefine5;
        ca.ndefine1 = m.ndefine1;
        ca.ndefine2 = m.ndefine2;
        ca.ndefine3 = m.ndefine3;
        ca.ndefine4 = m.ndefine4;
        ca.ndefine5 = m.ndefine5;
        ca_dal.Add(ca);
        int ca_id = ca_dal.GetMaxID();
        DataTable dt = ios_dal.GetDetails(io_id);
        if (dt.Rows.Count == 0)
        {
            return "表体不可为空！";
        }
        foreach (DataRow dr in dt.Rows)
        {

            int ios_id = Convert.ToInt32(dr["ios_id"]);
            InOuts ios = ios_dal.Get(ios_id, 0);
            CheckAccounts cas = new CheckAccounts();

            cas.ca_id = ca_id;
            cas.ios_id = ios_id;
            cas.d_wh_id = ios.d_wh_id;
            cas.i_id = ios.i_id;
            cas.free1 = ios.free1;
            cas.free2 = ios.free2;
            cas.batch = ios.batch;
            cas.quantity = ios.quantity;
            if (ca.rp_flag.Equals("AR"))
            {
                //如果有发货单 金额取发货单
                if (ios.delivery_orders != null)
                {
                    cas.d_tax_rate = ios.delivery_orders.d_tax_rate;

                    cas.unit_price = ios.delivery_orders.unit_price;
                    cas.tax_unit_price = ios.delivery_orders.tax_unit_price;
                    cas.total_price = ios.delivery_orders.total_price;
                    cas.tax_total_price = ios.delivery_orders.tax_total_price;
                    cas.tax = ios.delivery_orders.tax;

                    cas.nat_unit_price = ios.delivery_orders.nat_unit_price;
                    cas.nat_tax_unit_price = ios.delivery_orders.nat_tax_unit_price;
                    cas.nat_total_price = ios.delivery_orders.nat_total_price;
                    cas.nat_tax_total_price = ios.delivery_orders.nat_tax_total_price;
                    cas.nat_tax = ios.delivery_orders.nat_tax;
                }
                else
                {
                    //没发货单直接取销售出库单
                    cas.d_tax_rate = ios.d_tax_rate;
                    cas.unit_price = ios.unit_price;
                    cas.tax_unit_price = ios.tax_unit_price;
                    cas.total_price = ios.total_price;
                    cas.tax_total_price = ios.tax_total_price;
                    cas.tax = ios.tax;
                    cas.nat_unit_price = ios.unit_price;
                    cas.nat_tax_unit_price = ios.tax_unit_price;
                    cas.nat_total_price = ios.total_price;
                    cas.nat_tax_total_price = ios.tax_total_price;
                    cas.nat_tax = ios.tax;
                }
            }
            else
            {
                //如果有采购订单
                if (ios.purchase_orders != null)
                {
                    cas.d_tax_rate = ios.purchase_orders.d_tax_rate;

                    cas.unit_price = ios.purchase_orders.unit_price;
                    cas.tax_unit_price = ios.purchase_orders.tax_unit_price;
                    cas.total_price = ios.purchase_orders.total_price;
                    cas.tax_total_price = ios.purchase_orders.tax_total_price;
                    cas.tax = ios.purchase_orders.tax;

                    cas.nat_unit_price = ios.purchase_orders.nat_unit_price;
                    cas.nat_tax_unit_price = ios.purchase_orders.nat_tax_unit_price;
                    cas.nat_total_price = ios.purchase_orders.nat_total_price;
                    cas.nat_tax_total_price = ios.purchase_orders.nat_tax_total_price;
                    cas.nat_tax = ios.purchase_orders.nat_tax;
                }
                else
                {
                    //没采购订单时读采购入库单的价格
                    cas.d_tax_rate = ios.d_tax_rate;
                    cas.unit_price = ios.unit_price;
                    cas.tax_unit_price = ios.tax_unit_price;
                    cas.total_price = ios.total_price;
                    cas.tax_total_price = ios.tax_total_price;
                    cas.tax = ios.tax;
                    cas.nat_unit_price = ios.unit_price;
                    cas.nat_tax_unit_price = ios.tax_unit_price;
                    cas.nat_total_price = ios.total_price;
                    cas.nat_tax_total_price = ios.tax_total_price;
                    cas.nat_tax = ios.tax;
                }
            }
            cas.num = ios.num;
            cas.convert_rate = ios.convert_rate;
            cas.d_sdefine1 = ios.d_sdefine1;
            cas.d_sdefine2 = ios.d_sdefine2;
            cas.d_sdefine3 = ios.d_sdefine3;
            cas.d_sdefine4 = ios.d_sdefine4;
            cas.d_sdefine5 = ios.d_sdefine5;
            cas.d_ndefine1 = ios.d_ndefine1;
            cas.d_ndefine2 = ios.d_ndefine2;
            cas.d_ndefine3 = ios.d_ndefine3;
            cas.d_ndefine4 = ios.d_ndefine4;
            cas.d_ndefine5 = ios.d_ndefine5;
            cas.ios_id = ios.ios_id;//存储子表ID
            cas_dal.Add(cas);
        }
        //更新累计对账数
        ca_dal.UpdateDuiZhangQuantityByOrder(ca_id, action_type);
        //通过对账单生成应收应付账
        string msg_ap = ca_dal.DuiZhangToAPDetail(ca_id);
        if (!msg_ap.Equals("success"))
        {
            return msg_ap;
        }
        return "success";
    }
    /// <summary>
    /// 删除销售出库单或采购入库单生成的对账单
    /// </summary>
    /// <param name="io_id">进出单主表ID</param>
    /// <returns>返回success代表成功</returns>
    public string DeleteCheckAccountByInOut(int io_id, int action_type)
    {
        InOut io = new InOut();
        InOutDAL io_dal = new InOutDAL();
        InOutsDAL ios_dal = new InOutsDAL();
        CheckAccount ca = new CheckAccount();
        CheckAccountsDAL cas_dal = new CheckAccountsDAL();
        CheckAccountDAL ca_dal = new CheckAccountDAL();
        SqlUtil su = new SqlUtil();
        InOut m = Get(io_id);
        //得到出库单生成的对账单主表ID
        foreach (DataRow dr in GetCheckAccountID(io_id).Rows)
        {
            int ca_id = Convert.ToInt32(dr["ca_id"]);
            if (ca_id != 0)
            {
                //删除应收应付明细
                string msg_apd = ca_dal.DeleteAPDetail(ca_id);
                if (!msg_apd.Equals("success"))
                {
                    return msg_apd;
                }
                //更新累计对账数
                ca_dal.UpdateDuiZhangQuantityByOrder(ca_id, action_type);
                //删除整个对账单子表
                cas_dal.DeleteByOrder(ca_id);
                //删除对账单主表
                ca_dal.Delete(ca_id);

            }
        }
        return "success";
    }
    /// <summary>
    /// 得到出库单生成的对账单主表ID集合
    /// </summary>
    public DataTable GetCheckAccountID(int io_id)
    {
        string sql = @"
            select ca.ca_id from ARAP_CheckAccount ca left join
            ARAP_CheckAccounts cas on ca.ca_id=cas.ca_id
            where cas.ios_id in (select ios_id from Stock_InOuts where io_id=@io_id)
            group by ca.ca_id";
        SqlParameter[] par ={
            new SqlParameter("@io_id", io_id)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    /// <summary>
    /// 得到单据总金额
    /// </summary>
    /// <returns>decimal</returns>
    public decimal GetTotalPrice(int io_id)
    {
        string sql = "select isnull(sum(tax_total_price),0) from Stock_InOuts where io_id=@io_id ";
        SqlParameter[] par ={
                new SqlParameter("@io_id", io_id)
            };
        decimal sum_price = Convert.ToDecimal(new SqlUtil().ExecuteScalar(sql, par));
        return sum_price;
    }
}
