using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Control;

public class DeliveryOrderDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(DeliveryOrder m)
    {
        string sql = @"insert Sale_DeliveryOrder(order_number,cus_id,st_id,
			                        wh_id,iot_id,dep_id,ele_id,crc_id,exchange_rate,remark,
                                    tax_rate,zdkl,sw_id,ship_address,contact,phone,
                                    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,@cus_id,@st_id,
			                    @wh_id,@iot_id,@dep_id,@ele_id,@crc_id,@exchange_rate,@remark,
                                @tax_rate,@zdkl,@sw_id,@ship_address,@contact,@phone,
                                @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("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@st_id",XTools.IntToDBNull(m.st_id)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@iot_id",XTools.IntToDBNull(m.iot_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@zdkl",XTools.DecimalToDBNull(m.zdkl)),
            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("@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">do_id</param>
    /// <returns>DeliveryOrder</returns>
    public DeliveryOrder Get(int do_id)
    {
        DeliveryOrder m = new DeliveryOrder();
        string sql = @"select do.*,convert(char(10),do.new_date,23) as new_date_format,
                    dep.dep_name,cus.cus_name,crc.crc_name,it.iot_name,
                    ele.ele_name,st.st_name,sw.sw_name,wh.wh_name,
                    cus.sdefine1 as cus_sdefine1,cus.sdefine2 as cus_sdefine2,cus.sdefine3 as cus_sdefine3,
                    cus.sdefine4 as cus_sdefine4,cus.sdefine5 as cus_sdefine5,
                    cus.ndefine1 as cus_ndefine1,cus.ndefine2 as cus_ndefine2,cus.ndefine3 as cus_ndefine3,
                    cus.ndefine4 as cus_ndefine4,cus.ndefine5 as cus_ndefine5
                    from Sale_DeliveryOrder do
                    left join Base_Customer cus on do.cus_id=cus.cus_id
                    left join Base_Warehouse wh on wh.wh_id=do.wh_id
                    left join Base_Department dep on do.dep_id=dep.dep_id
                    left join Base_Employee ele on do.ele_id=ele.ele_id
                    left join Base_SaleType st on do.st_id=st.st_id
                    left join Base_Currency crc on do.crc_id=crc.crc_id
                    left join Base_ShipWay sw on do.sw_id=sw.sw_id
                    left join Base_IOType it on do.iot_id=it.iot_id
                    where do.do_id=@do_id";
        sql = sql.Replace("@do_id", do_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.do_id = Convert.ToInt32(r["do_id"]);
                m.order_number = r["order_number"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                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.st_id = XTools.DBNullToInt(r["st_id"]);
                if(r["st_id"] != DBNull.Value)
                    m.saletype = new SaleTypeDAL().Get(Convert.ToInt32(m.st_id));

                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.ele_id = XTools.DBNullToInt(r["ele_id"]);
                if (r["ele_id"] != DBNull.Value)
                    m.employee = new EmployeeDAL().Get(Convert.ToInt32(m.ele_id));
                m.crc_id = XTools.DBNullToInt(r["crc_id"]);
                if (r["crc_id"] != DBNull.Value)
                    m.currency = new CurrencyDAL().Get(Convert.ToInt32(m.crc_id));
                m.exchange_rate = XTools.DBNullToDecimal(r["exchange_rate"]);
                m.tax_rate = XTools.DBNullToDecimal(r["tax_rate"]);
                m.zdkl = XTools.DBNullToDecimal(r["zdkl"]);
                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.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.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(DeliveryOrder m)
    {
        string sql = @"update Sale_DeliveryOrder set 
                        order_number=@order_number,
                        cus_id=@cus_id,
                        st_id=@st_id,
                        iot_id=@iot_id,
                        wh_id=@wh_id,
                        dep_id=@dep_id,
                        ele_id=@ele_id,
                        crc_id=@crc_id,
                        exchange_rate=@exchange_rate,
                        tax_rate=@tax_rate,
                        zdkl=@zdkl,
                        sw_id=@sw_id,
                        ship_address=@ship_address,
                        contact=@contact,
                        phone=@phone,
                        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 do_id=@do_id";
        SqlParameter[] par = {
            new SqlParameter("@do_id",m.do_id),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@st_id",XTools.IntToDBNull(m.st_id)),
            new SqlParameter("@iot_id",XTools.IntToDBNull(m.iot_id)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@zdkl",XTools.DecimalToDBNull(m.zdkl)),
            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",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 do_id)
    {

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

        return "success";
    }
   
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">单据号</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from Sale_DeliveryOrder 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(do_id),0) from Sale_DeliveryOrder";
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, null));
    }
    /// <summary>
    /// 得到上一个ID
    /// </summary>
    public int GetPrevNextID(int current_id, string type)
    {
        string sql = @"select isnull(max(t.do_id),0) from Sale_DeliveryOrder t
                       left join Base_Customer cus on t.cus_id=cus.cus_id
                       where (t.do_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = @"select isnull(min(t.do_id),0) from Sale_DeliveryOrder t
                    left join Base_Customer cus on t.cus_id=cus.cus_id
                    where t.do_id>@current_id";
        }
        //当前用户是否管理员
        bool is_admin = new AuthUI().CheckLoginUserIsAdmin();
        if (!is_admin)
        {
            //值为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)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="do_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int do_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.dos_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 Sale_DeliveryOrders d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.do_id=@do_id and isnull(i.is_service,0)=0
                    )t where msg!='success'
                    order by dos_id";
        SqlParameter[] par ={
                new SqlParameter("@do_id", do_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="do_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckOverStock(int do_id, int action_type)
    {
        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.do_id,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 Sale_DeliveryOrders d
	                        left join Sale_DeliveryOrder t on t.do_id=d.do_id
                            where d.do_id=@do_id
	                        group by t.do_id,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 isnull(i.is_service,0)=0 
                        and isnull(x.quantity,0) [action_type] 0 and abs(x.quantity)>isnull(oh.quantity,0)
                    )t";
        SqlParameter[] par ={
                new SqlParameter("@do_id", do_id)
            };
        if (action_type == 1)
        {
            //审核时，入库单数量小于零(负数) 或 出库单数量大于零(正数)，则判断现存量
            sql = sql.Replace("[action_type]", ">");
        }
        else
        {
            //弃审时，入库单数量大于零(正数) 或 出库单数量小于零(负数)，则判断现存量
            sql = sql.Replace("[action_type]", "<");
        }
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 更新销售订单累计发货数
    /// </summary>
    /// <param name="do_id">进出单主表id</param>
    /// <param name="direction">方向：1,加累发货数;0,减累发货数</param>
    public void UpdateFaHuoQuantityByOrder(int do_id, int direction)
    {
        string sql = @"
                update Sale_SalesOrders set
                acc_fh_quantity=isnull(acc_fh_quantity,0) @plus isnull(t.quantity,0),
                acc_fh_num=isnull(acc_fh_num,0) @plus isnull(t.num,0)
                from(
                    select sos_id,sum(d.quantity) as quantity,sum(d.num) as num
                    from Sale_DeliveryOrders d 
                    where d.do_id=@do_id and isnull(dos_id,0)!=0
                    group by sos_id 
                )t where Sale_SalesOrders.sos_id=t.sos_id";
        if (direction == 1)
        {
            //加现存量
            sql = sql.Replace("@plus", "+");
        }
        else
        {
            //减现存量
            sql = sql.Replace("@plus", "-");
        }
        sql = sql.Replace("@do_id", do_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);
    }

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

    /// <summary>
    /// 批量设置表体部门
    /// </summary>
    /// <param name="id">主表id</param>
    /// <param name="dep_id">部门id</param>
    public void BatchSetDepartment(int do_id, int dep_id)
    {
        string sql = @"
                update Sale_DeliveryOrders set
                d_dep_id=@dep_id where do_id=@do_id";
        SqlParameter[] par = {
            new SqlParameter("@dep_id", dep_id),
            new SqlParameter("@do_id", do_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 判断发货单是否已出库
    /// </summary>
    /// <param name="do_id">发货单主表ID</param>
    /// <returns>bool</returns>
    public bool CheckHasChuKu(int do_id)
    {
        string sql = @"
                    select count(1) from Sale_DeliveryOrders
                    where do_id=@do_id and 
                    (
                        isnull(acc_out_quantity,0)<>0
                        or
                        dos_id in
                        (
	                        select distinct dos_id from Stock_InOuts where dos_id is not null
                        )
                    )";
        SqlParameter[] par ={
                new SqlParameter("@do_id", do_id)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }

    /// <summary>
    /// 得到发货单生成的出库单主表ID集合
    /// </summary>
    public DataTable GetSalesOutID(int do_id)
    {
        string sql = @"
            select io.io_id from Stock_InOut io left join
            Stock_InOuts ios on io.io_id=ios.io_id
            where io.order_type=6
            and ios.dos_id in (select dos_id from Sale_DeliveryOrders where do_id=@do_id)
            group by io.io_id";
        SqlParameter[] par ={
            new SqlParameter("@do_id", do_id)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    /// <summary>
    /// 发货单生成销售出库单
    /// </summary>
    /// <param name="do_id">发货单主表ID</param>
    /// <returns>返回success代表成功</returns>
    public string DeliveryOrderToSalesOut(int do_id, int action_type)
    {
        InOut io = new InOut();
        InOutDAL io_dal = new InOutDAL();
        InOutsDAL ios_dal = new InOutsDAL();
        DeliveryOrdersDAL dos_dal = new DeliveryOrdersDAL();
        DeliveryOrderDAL do_dal = new DeliveryOrderDAL();
        DeliveryOrder m = do_dal.Get(do_id);
        io.io_flag = 0;//进出类型:出库
        io.order_type = 6;//单据类型:销售出库
        io.order_number = m.order_number;//出库单号=发货单号
        io.from_id = m.do_id;
        io.from_type = "发货单";
        io.cus_id = m.cus_id;
        io.ele_id = m.ele_id;
        io.wh_id = m.wh_id;
        io.dep_id = m.dep_id;
        io.contact = m.contact;
        io.ship_address = m.ship_address;
        io.phone = m.phone;
        io.st_id = m.st_id;
        io.iot_id = m.iot_id;
        io.sw_id = m.sw_id;
        io.new_date = m.new_date;
        io.new_uid = m.new_uid;
        io.new_user = m.new_user;
        io.verify_uid = m.verify_uid;
        io.verify_user = m.verify_user;
        io.remark = m.remark;
        io.sdefine1 = m.sdefine1;
        io.sdefine2 = m.sdefine2;
        io.sdefine3 = m.sdefine3;
        io.sdefine4 = m.sdefine4;
        io.sdefine5 = m.sdefine5;
        io.ndefine1 = m.ndefine1;
        io.ndefine2 = m.ndefine2;
        io.ndefine3 = m.ndefine3;
        io.ndefine4 = m.ndefine4;
        io.ndefine5 = m.ndefine5;

        io_dal.Add(io);
        int io_id = io_dal.GetMaxID();

        DataTable dt = dos_dal.GetDetails(m.do_id);
        if (dt.Rows.Count==0)
        {
            return "表体不可为空！";
        }
        foreach (DataRow dr in dt.Rows)
        {
            int dos_id = Convert.ToInt32(dr["dos_id"]);
            DeliveryOrders dos = dos_dal.Get(dos_id);
            //劳务费不用生成出库
            if (dos.inventory.is_service == 1)
                continue;
            InOuts os = new InOuts();
            os.io_id = io_id;
            os.d_wh_id = dos.d_wh_id;
            os.d_dep_id = m.dep_id;
            os.i_id = dos.i_id;
            os.free1 = dos.free1;
            os.free2 = dos.free2;
            os.batch = dos.batch;
            os.quantity = dos.quantity;
            os.num = dos.num;
            os.convert_rate = dos.convert_rate;
            os.d_sdefine1 = dos.d_sdefine1;
            os.d_sdefine2 = dos.d_sdefine2;
            os.d_sdefine3 = dos.d_sdefine3;
            os.d_sdefine4 = dos.d_sdefine4;
            os.d_sdefine5 = dos.d_sdefine5;
            os.d_ndefine1 = dos.d_ndefine1;
            os.d_ndefine2 = dos.d_ndefine2;
            os.d_ndefine3 = dos.d_ndefine3;
            os.d_ndefine4 = dos.d_ndefine4;
            os.d_ndefine5 = dos.d_ndefine5;
            os.dos_id = dos.dos_id;//存储子表ID
            ios_dal.Add(os);
        }
        //更新累计出库数
        io_dal.UpdateSalesOutQuantityByOrder(io_id, action_type);
        //更新现存量
        io_dal.UpdateOhHandByOrder(io_id, -action_type);
        //值为1代表销售出库单审核自动生成对账单
        string sales_out_verify_check_account = new ConfigReader().GetConfig("sales_out_verify_check_account");
        if (sales_out_verify_check_account.Equals("1"))
        {
            //通过销售出库单生成对账单
            string msg_ac = io_dal.InOutToCheckAccount(io_id, action_type);
            if (!msg_ac.Equals("success"))
            {
                return msg_ac;
            }
        }
        return "success";
    }
    /// <summary>
    /// 删除发货单所生成的销售出库单
    /// </summary>
    /// <param name="do_id">发货单主表ID</param>
    /// <returns>返回success代表成功</returns>
    public string DeleteSalesOutFromDeliveryOrder(int do_id, int action_type)
    {
        InOutDAL io_dal = new InOutDAL();
        CheckAccountDAL ca_dal = new CheckAccountDAL();
        //得到发货单生成的出库单主表ID集合
        foreach (DataRow dr in GetSalesOutID(do_id).Rows)
        {
            
            int io_id = Convert.ToInt32(dr["io_id"]);
            if (io_id != 0)
            {
                //值为1代表销售出库单审核自动生成对账单
                string sales_out_verify_check_account = new ConfigReader().GetConfig("sales_out_verify_check_account");
                if (sales_out_verify_check_account.Equals("1"))
                {
                    //得到出库单生成的对账单主表ID集合
                    foreach (DataRow dr2 in io_dal.GetCheckAccountID(io_id).Rows)
                    {
                        int ca_id = Convert.ToInt32(dr2["ca_id"]);
                        //删除对账单生成的应收应付明细账
                        string msg_ap = ca_dal.DeleteAPDetail(ca_id);
                        if (!msg_ap.Equals("success"))
                        {
                            return msg_ap;
                        }
                    }
                    //删除对账单
                    string msg_dz = io_dal.DeleteCheckAccountByInOut(io_id, action_type);
                    if (!msg_dz.Equals("success"))
                    {
                        return msg_dz;
                    }
                }
                else
                {
                    if (io_dal.CheckIsDuiZhang(io_id))
                    {
                        return "销售出库单已作对账，不允许弃审！";
                    }
                }
                //更新累计出库数
                io_dal.UpdateSalesOutQuantityByOrder(io_id, action_type);
                //更新现存量
                io_dal.UpdateOhHandByOrder(io_id, -action_type);
                //删除出库单
                string delete_msg = io_dal.Delete(io_id, 6);
                if (!delete_msg.Equals("success"))
                {
                    return delete_msg;
                }
            }
        }
        return "success";
    }
    /// <summary>
    /// 得到单据总金额
    /// </summary>
    /// <returns>decimal</returns>
    public decimal GetTotalPrice(int do_id)
    {
        string sql = "select isnull(sum(tax_total_price),0) from Sale_DeliveryOrders where do_id=@do_id ";
        SqlParameter[] par ={
                new SqlParameter("@do_id", do_id)
            };
        decimal sum_price = Convert.ToDecimal(new SqlUtil().ExecuteScalar(sql, par));
        return sum_price;
    }
}
