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 InOutsDAL
{
    // 单据类型(1,其它出库单 2,其它出库单 3,采购出库单 4,材料出库单 5,成品出库单 6,销售出库单)
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(InOuts m)
    {
        string sql = @"insert Stock_InOuts(io_id,d_wh_id,d_dep_id,i_id,dos_id,pos_id,free1,free2,batch,
                                unit_price,tax_unit_price,
                                quantity,num,convert_rate,
                                total_price,tax_total_price,tax,d_tax_rate,d_remark,
                                dz_quantity,dz_num,tss_id,scs_id,
                                d_sdefine1,d_sdefine2,d_sdefine3,d_sdefine4,d_sdefine5,
                                d_ndefine1,d_ndefine2,d_ndefine3,d_ndefine4,d_ndefine5)
                        select  @io_id,@d_wh_id,@d_dep_id,@i_id,@dos_id,@pos_id,@free1,@free2,@batch,
                                @unit_price,@tax_unit_price,
                                @quantity,@num,@convert_rate,
                                @total_price,@tax_total_price,@tax,@d_tax_rate,@d_remark,
                                @dz_quantity,@dz_num,@tss_id,@scs_id,
                                @d_sdefine1,@d_sdefine2,@d_sdefine3,@d_sdefine4,@d_sdefine5,
                                @d_ndefine1,@d_ndefine2,@d_ndefine3,@d_ndefine4,@d_ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@io_id",m.io_id),
            new SqlParameter("@dos_id",XTools.IntToDBNull(m.dos_id)),
            new SqlParameter("@pos_id",XTools.IntToDBNull(m.pos_id)),
            new SqlParameter("@d_wh_id",XTools.IntToDBNull(m.d_wh_id)),
            new SqlParameter("@d_dep_id",XTools.IntToDBNull(m.d_dep_id)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@batch",XTools.StringToDBNull(m.batch)),
            new SqlParameter("@unit_price",XTools.DecimalToDBNull(m.unit_price)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@tax",XTools.DecimalToDBNull(m.tax)),
            new SqlParameter("@d_tax_rate",XTools.DecimalToDBNull(m.d_tax_rate)),
            new SqlParameter("@d_remark",XTools.StringToDBNull(m.d_remark)),
            new SqlParameter("@dz_quantity",XTools.DecimalToDBNull(m.dz_quantity)),
            new SqlParameter("@dz_num",XTools.DecimalToDBNull(m.dz_num)),
            new SqlParameter("@tss_id",XTools.IntToDBNull(m.tss_id)),
            new SqlParameter("@scs_id",XTools.IntToDBNull(m.scs_id)),
            new SqlParameter("@d_sdefine1",XTools.StringToDBNull(m.d_sdefine1)),
            new SqlParameter("@d_sdefine2",XTools.StringToDBNull(m.d_sdefine2)),
            new SqlParameter("@d_sdefine3",XTools.StringToDBNull(m.d_sdefine3)),
            new SqlParameter("@d_sdefine4",XTools.StringToDBNull(m.d_sdefine4)),
            new SqlParameter("@d_sdefine5",XTools.StringToDBNull(m.d_sdefine5)),
            new SqlParameter("@d_ndefine1",XTools.DecimalToDBNull(m.d_ndefine1)),
            new SqlParameter("@d_ndefine2",XTools.DecimalToDBNull(m.d_ndefine2)),
            new SqlParameter("@d_ndefine3",XTools.DecimalToDBNull(m.d_ndefine3)),
            new SqlParameter("@d_ndefine4",XTools.DecimalToDBNull(m.d_ndefine4)),
            new SqlParameter("@d_ndefine5",XTools.DecimalToDBNull(m.d_ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">ios_id</param>
    /// <returns>InOuts</returns>
    public InOuts Get(int ios_id,int order_type)
    {
        InOuts m = new InOuts();
        //0代表不清楚order_type
        if (order_type == 0)
        {
            string sql_ot = @"select order_type from Stock_InOuts d
                             left join Stock_InOut t on d.io_id=t.io_id
                             where d.ios_id=" + ios_id.ToString();
            order_type = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql_ot, null));
        }
        string sql_select = "";
        string sql_join = "";
        //采购入库
        if (order_type == 3)
        {
            sql_select = ",po.order_number as po_order_number,pos.quantity as po_order_quantity";
            sql_join = @"left join Purchase_PurchaseOrders pos on pos.pos_id=d.pos_id
                         left join Purchase_PurchaseOrder po on po.po_id=pos.po_id";
        }
        //销售出库
        if (order_type == 6)
        {
            sql_select = @",do.order_number as do_order_number,dos.quantity as do_order_quantity
                           ,so.order_number as so_order_number,sos.quantity as so_order_quantity,sos.sos_id";
            sql_join = @"left join Sale_DeliveryOrders dos on dos.dos_id=d.dos_id
                        left join Sale_DeliveryOrder do on do.do_id=dos.do_id
                        left join Sale_SalesOrders sos on sos.sos_id=dos.sos_id
                        left join Sale_SalesOrder so on so.so_id=sos.so_id";
        }
        string sql = @"select d.*,
                    i.sdefine1 as d_inv_sdefine1,i.sdefine2 as d_inv_sdefine2,i.sdefine3 as d_inv_sdefine3,
                    i.sdefine4 as d_inv_sdefine4,i.sdefine5 as d_inv_sdefine5,
                    i.ndefine1 as d_inv_ndefine1,i.ndefine2 as d_inv_ndefine2,i.ndefine3 as d_inv_ndefine3,
                    i.ndefine4 as d_inv_ndefine4,i.ndefine5 as d_inv_ndefine5
                    @sql_select
                    from Stock_InOuts d
                    left join Base_Inventory i on d.i_id=i.i_id
                    @sql_join
                    where d.ios_id=@ios_id";
        sql = sql.Replace("@ios_id", ios_id.ToString());
        sql = sql.Replace("@sql_select", sql_select);
        sql = sql.Replace("@sql_join", sql_join);
        InOutDAL parent_dal = new InOutDAL();
        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"]);//主表id
                m.parent_object = parent_dal.Get(m.io_id);//父对象
                m.ios_id = Convert.ToInt32(r["ios_id"]);//子表id
                m.pos_id = XTools.DBNullToInt(r["pos_id"]);//采购订单子表id
                m.dos_id = XTools.DBNullToInt(r["dos_id"]);//发货单子表id
                m.d_wh_id = XTools.DBNullToInt(r["d_wh_id"]);//仓库id
                if (r["d_wh_id"] != DBNull.Value)
                    m.warehouse = new WarehouseDAL().Get((int)m.d_wh_id);
                m.d_dep_id = XTools.DBNullToInt(r["d_dep_id"]);//部门id
                if (r["d_dep_id"] != DBNull.Value)
                    m.department = new DepartmentDAL().Get((int)m.d_dep_id);
                m.i_id = Convert.ToInt32(r["i_id"]);//存货id
                if (r["i_id"] != DBNull.Value)
                    m.inventory = new InventoryDAL().Get(m.i_id);
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.batch = r["batch"].ToString();
                //采购入库
                if (order_type == 3)
                {
                    if (r["pos_id"] != DBNull.Value)
                    {
                        m.pos_id = XTools.DBNullToInt(r["pos_id"]);//采购订单子表id
                        m.purchase_orders = new PurchaseOrdersDAL().Get((int)m.pos_id);//采购订单实体
                        m.po_order_number = r["po_order_number"].ToString();
                    }
                }
                //销售出库
                if (order_type == 6)
                {
                    if (r["dos_id"] != DBNull.Value)
                    {
                        m.dos_id = XTools.DBNullToInt(r["dos_id"]);//发货单子表id
                        m.delivery_orders = new DeliveryOrdersDAL().Get((int)m.dos_id);//发货订单实体
                        m.do_order_number = r["do_order_number"].ToString();////发货单号
                        if (m.delivery_orders.sos_id != null)
                        {
                            m.sales_orders = new SalesOrdersDAL().Get((int)m.delivery_orders.sos_id);//销售订单实体
                            m.so_order_number = r["so_order_number"].ToString();//销售订单号
                            m.sos_id = m.sales_orders.sos_id;//销售订单子表id
                        }
                    }
                }
                m.unit_price = XTools.DBNullToDecimal(r["unit_price"]);//无税单价
                m.tax_unit_price = XTools.DBNullToDecimal(r["tax_unit_price"]);//含税单价
                m.quantity = XTools.DBNullToDecimal(r["quantity"]);//数量
                m.num = XTools.DBNullToDecimal(r["num"]);//件数
                m.convert_rate = XTools.DBNullToDecimal(r["convert_rate"]);//换算率
                m.total_price = XTools.DBNullToDecimal(r["total_price"]);//无税金额
                m.tax_total_price = XTools.DBNullToDecimal(r["tax_total_price"]);//含税金额
                m.tax = XTools.DBNullToDecimal(r["tax"]);//税额
                m.d_tax_rate = XTools.DBNullToDecimal(r["d_tax_rate"]);//税率
                m.dz_quantity = XTools.DBNullToDecimal(r["dz_quantity"]);//累计对账数量
                m.dz_num = XTools.DBNullToDecimal(r["dz_num"]);//累计对账件数
                m.tss_id = XTools.DBNullToInt(r["tss_id"]);//调拨单子表ID
                m.scs_id = XTools.DBNullToInt(r["scs_id"]);//盘点单子表ID
                m.d_remark = r["d_remark"].ToString();//备注
                m.d_sdefine1 = r["d_sdefine1"].ToString();
                m.d_sdefine2 = r["d_sdefine2"].ToString();
                m.d_sdefine3 = r["d_sdefine3"].ToString();
                m.d_sdefine4 = r["d_sdefine4"].ToString();
                m.d_sdefine5 = r["d_sdefine5"].ToString();
                m.d_ndefine1 = XTools.DBNullToDecimal(r["d_ndefine1"]);
                m.d_ndefine2 = XTools.DBNullToDecimal(r["d_ndefine2"]);
                m.d_ndefine3 = XTools.DBNullToDecimal(r["d_ndefine3"]);
                m.d_ndefine4 = XTools.DBNullToDecimal(r["d_ndefine4"]);
                m.d_ndefine5 = XTools.DBNullToDecimal(r["d_ndefine5"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(InOuts m)
    {
        string sql = @"update Stock_InOuts set
                        io_id=@io_id,dos_id=@dos_id,pos_id=@pos_id,d_wh_id=@d_wh_id,d_dep_id=@d_dep_id,
                        i_id=@i_id,free1=@free1,free2=@free2,batch=@batch,
                        unit_price=@unit_price,tax_unit_price=@tax_unit_price,
                        total_price=@total_price,tax_total_price=@tax_total_price,
                        tax=@tax,d_tax_rate=@d_tax_rate,
                        quantity=@quantity,num=@num,convert_rate=@convert_rate,d_remark=@d_remark,
                        dz_quantity=@dz_quantity,dz_num=@dz_num,
                        tss_id=@tss_id,scs_id=@scs_id,
                        d_sdefine1=@d_sdefine1,d_sdefine2=@d_sdefine2,d_sdefine3=@d_sdefine3,
                        d_sdefine4=@d_sdefine4,d_sdefine5=@d_sdefine5,
                        d_ndefine1=@d_ndefine1,d_ndefine2=@d_ndefine2,d_ndefine3=@d_ndefine3,
                        d_ndefine4=@d_ndefine4,d_ndefine5=@d_ndefine5
                        where ios_id=@ios_id";
        SqlParameter[] par = {
            new SqlParameter("@ios_id",m.ios_id),
            new SqlParameter("@io_id",m.io_id),
            new SqlParameter("@pos_id",XTools.IntToDBNull(m.pos_id)),
            new SqlParameter("@dos_id",XTools.IntToDBNull(m.dos_id)),
            new SqlParameter("@d_wh_id",XTools.IntToDBNull(m.d_wh_id)),
            new SqlParameter("@d_dep_id",XTools.IntToDBNull(m.d_dep_id)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@batch",XTools.StringToDBNull(m.batch)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@unit_price",XTools.DecimalToDBNull(m.unit_price)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@tax",XTools.DecimalToDBNull(m.tax)),
            new SqlParameter("@d_tax_rate",XTools.DecimalToDBNull(m.d_tax_rate)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@dz_quantity",XTools.DecimalToDBNull(m.dz_quantity)),
            new SqlParameter("@dz_num",XTools.DecimalToDBNull(m.dz_num)),
            new SqlParameter("@d_remark",XTools.StringToDBNull(m.d_remark)),
            new SqlParameter("@tss_id",XTools.IntToDBNull(m.tss_id)),
            new SqlParameter("@scs_id",XTools.IntToDBNull(m.scs_id)),
            new SqlParameter("@d_sdefine1",XTools.StringToDBNull(m.d_sdefine1)),
            new SqlParameter("@d_sdefine2",XTools.StringToDBNull(m.d_sdefine2)),
            new SqlParameter("@d_sdefine3",XTools.StringToDBNull(m.d_sdefine3)),
            new SqlParameter("@d_sdefine4",XTools.StringToDBNull(m.d_sdefine4)),
            new SqlParameter("@d_sdefine5",XTools.StringToDBNull(m.d_sdefine5)),
            new SqlParameter("@d_ndefine1",XTools.DecimalToDBNull(m.d_ndefine1)),
            new SqlParameter("@d_ndefine2",XTools.DecimalToDBNull(m.d_ndefine2)),
            new SqlParameter("@d_ndefine3",XTools.DecimalToDBNull(m.d_ndefine3)),
            new SqlParameter("@d_ndefine4",XTools.DecimalToDBNull(m.d_ndefine4)),
            new SqlParameter("@d_ndefine5",XTools.DecimalToDBNull(m.d_ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除子表
    /// </summary>
    /// <param name="ios_id">子表ID</param>
    public void Delete(int ios_id)
    {
        InOuts m = new InOuts();
        string sql = "delete Stock_InOuts where ios_id=@ios_id";
        SqlParameter[] par = {
                new SqlParameter("@ios_id",ios_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除整单子表
    /// </summary>
    /// <param name="io_id">主表ID</param>
    public void DeleteByOrder(int io_id)
    {
        InOuts m = new InOuts();
        string sql = "delete Stock_InOuts where io_id=@io_id";
        SqlParameter[] par = {
                new SqlParameter("@io_id",io_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到单据表体
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field, int order_type)
    {
        //列表查询用的sql
        string sql_list = @"
	                select d.io_id,d.ios_id,
                    d.d_wh_id,wh.wh_code as d_wh_code,wh.wh_name as d_wh_name,
                    d.d_dep_id,dep.dep_code as d_dep_code,dep.dep_name as d_dep_name,
                    d.i_id,d.free1,d.free2,d.batch,
                    i.i_code,i.i_name,i.standard,i.munit,i.aunit,
	                d.quantity,d.num,d.convert_rate,d.d_remark,
                    d.unit_price,d.tax_unit_price,d.total_price,
                    d.tax_total_price,d.tax,d.d_tax_rate,
	                d.d_sdefine1,d.d_sdefine2,d.d_sdefine3,d.d_sdefine4,d.d_sdefine5,
	                d.d_ndefine1,d.d_ndefine2,d.d_ndefine3,d.d_ndefine4,d.d_ndefine5,
                    i.sdefine1 as d_inv_sdefine1,i.sdefine2 as d_inv_sdefine2,i.sdefine3 as d_inv_sdefine3,
                    i.sdefine4 as d_inv_sdefine4,i.sdefine5 as d_inv_sdefine5,
                    i.ndefine1 as d_inv_ndefine1,i.ndefine2 as d_inv_ndefine2,i.ndefine3 as d_inv_ndefine3,
                    i.ndefine4 as d_inv_ndefine4,i.ndefine5 as d_inv_ndefine5,
                    d.pos_id,d.dos_id
                    @sql_select
	                from Stock_InOuts d
	                left join Base_Inventory i on d.i_id=i.i_id
                    left join Base_Warehouse wh on d.d_wh_id=wh.wh_id
                    left join Base_Department dep on d.d_dep_id=dep.dep_id
                    @sql_join";
        string sql_select = "";
        string sql_join = "";
        //采购入库
        if (order_type == 3)
        {
            sql_select = ",po.order_number as po_order_number,pos.quantity as po_order_quantity";
            sql_join = @"left join Purchase_PurchaseOrders pos on pos.pos_id=d.pos_id
                    left join Purchase_PurchaseOrder po on po.po_id=pos.po_id";
        }
        //销售出库
        if (order_type == 6)
        {
            sql_select = @",do.order_number as do_order_number,dos.quantity as do_order_quantity
                           ,so.order_number as so_order_number,sos.quantity as so_order_quantity";
            sql_join = @"left join Sale_DeliveryOrders dos on dos.dos_id=d.dos_id
                        left join Sale_DeliveryOrder do on do.do_id=dos.do_id
                        left join Sale_SalesOrders sos on sos.sos_id=dos.sos_id
                        left join Sale_SalesOrder so on so.so_id=sos.so_id";
        }
        sql_list = sql_list.Replace("@sql_select", sql_select);
        sql_list = sql_list.Replace("@sql_join", sql_join);

        string order_field = pg["order_field"].ToString();
        string sql = @"select top 1000000000 ROW_NUMBER() OVER(@order_field) AS row_num,@sql_select_field 
                from(@sql_list)t
                where 1=1 ";
        sql = sql.Replace("@sql_list", sql_list)
            .Replace("@order_field", order_field)
            .Replace("@sql_select_field", sql_select_field);
        //打印是则不需要分页,只需要加上筛选条件
        sql_print = "select top 1000000000 * from (" + sql_list + ") t where 1=1 " + pg["sql_end"] + " " + pg["order_field"].ToString();
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from Stock_InOuts where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
    /// <summary>
    /// 得到某张单所有明细
    /// </summary>
    /// <param name="io_id"></param>
    /// <returns></returns>
    public DataTable GetDetails(int io_id)
    {
        string sql = "select * from Stock_InOuts where io_id=@io_id";
        SqlParameter[] par = {
            new SqlParameter("@io_id", io_id)
        };
        return new SqlUtil().GetDataTable(sql, par);
    }
    /// <summary>
    /// 更新采购订单累计入库数
    /// </summary>
    /// <param name="pos_id">采购订单子表id</param>
    /// <param name="quantity">入库数量</param>
    /// <param name="num">入库件数</param>
    public void UpdatePurchaseInQuantity(int? pos_id, decimal? quantity, decimal? num)
    {
        string sql = @"
                update Purchase_PurchaseOrders set
                acc_in_quantity=isnull(acc_in_quantity,0)+@quantity,
                acc_in_num=isnull(acc_in_num,0)+@num
                where pos_id=@pos_id";
        SqlParameter[] par = {
            new SqlParameter("@pos_id", pos_id),
            new SqlParameter("@quantity", quantity),
            new SqlParameter("@num", XTools.DecimalToDBNull(num))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    /// <summary>
    /// 得到某张单据表体总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetOneOrder_TotalNumbers(int io_id)
    {
        string sql = "select count(1) from Stock_InOuts where io_id=@io_id ";
        SqlParameter[] par = {
                new SqlParameter("@io_id",io_id)
            };
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, par);
        return total_numbers;
    }

}
