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 InventoryDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(Inventory m)
    {
        string sql = @"insert Base_Inventory(i_code,i_name,ic_id,standard,
                        munit,aunit,convert_rate,valuation,add_date,update_date,
                        stop_date,updater_id,on_free1,on_free2,on_batch,
                        is_sale,is_purchase,is_self,is_outsource,is_service,safety_stock,
                        min_stock,max_stock,purchase_batch,vd_id,wh_id,
                        new_tax_unit_price,new_nat_unit_price,
                        sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                        ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select @i_code,@i_name,@ic_id,@standard,
                        @munit,@aunit,@convert_rate,@valuation,@add_date,@update_date,
                        @stop_date,@updater_id,@on_free1,@on_free2,@on_batch,
                        @is_sale,@is_purchase,@is_self,@is_outsource,@is_service,@safety_stock,
                        @min_stock,@max_stock,@purchase_batch,@vd_id,@wh_id,
                        @new_tax_unit_price,@new_nat_unit_price,
                        @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                        @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@i_code",m.i_code),
            new SqlParameter("@i_name",m.i_name),
            new SqlParameter("@ic_id",m.ic_id),
            new SqlParameter("@standard",XTools.StringToDBNull(m.standard)),
            new SqlParameter("@munit",XTools.StringToDBNull(m.munit)),
            new SqlParameter("@aunit",XTools.StringToDBNull(m.aunit)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@valuation",XTools.StringToDBNull(m.valuation)),
            new SqlParameter("@add_date",XTools.DateTimeToDBNull(m.add_date)),
            new SqlParameter("@update_date",XTools.DateTimeToDBNull(m.update_date)),
            new SqlParameter("@stop_date",XTools.DateTimeToDBNull(m.stop_date)),
            new SqlParameter("@updater_id",XTools.IntToDBNull(m.updater_id)),
            new SqlParameter("@on_free1",Convert.ToInt32(m.on_free1)),
            new SqlParameter("@on_free2",Convert.ToInt32(m.on_free2)),
            new SqlParameter("@on_batch",Convert.ToInt32(m.on_batch)),
            new SqlParameter("@is_sale",Convert.ToInt32(m.is_sale)),
            new SqlParameter("@is_purchase",Convert.ToInt32(m.is_purchase)),
            new SqlParameter("@is_self",Convert.ToInt32(m.is_self)),
            new SqlParameter("@is_outsource",Convert.ToInt32(m.is_outsource)),
            new SqlParameter("@is_service",Convert.ToInt32(m.is_service)),
            new SqlParameter("@safety_stock",XTools.DecimalToDBNull(m.safety_stock)),
            new SqlParameter("@min_stock",XTools.DecimalToDBNull(m.min_stock)),
            new SqlParameter("@max_stock",XTools.DecimalToDBNull(m.max_stock)),
            new SqlParameter("@purchase_batch",XTools.DecimalToDBNull(m.purchase_batch)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            new SqlParameter("@new_tax_unit_price",XTools.DecimalToDBNull(m.new_tax_unit_price)),
            new SqlParameter("@new_nat_unit_price",XTools.DecimalToDBNull(m.new_nat_unit_price)),
            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">i_id</param>
    /// <returns>Inventory</returns>
    public Inventory Get(int i_id)
    {
        Inventory m = new Inventory();
        string sql = @"select *
                      from Base_Inventory where i_id=@i_id";
        SqlParameter[] par = {
                new SqlParameter("@i_id",i_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            InventoryClassDAL ic_dal = new InventoryClassDAL();
            InventoryPhotoDAL ip_dal = new InventoryPhotoDAL();
            foreach (DataRow r in dt.Tables[0].Rows)
            {
            
                m.i_id = Convert.ToInt32(r["i_id"]);
                m.i_code = r["i_code"].ToString();
                m.i_name = r["i_name"].ToString();
                m.ic_id = Convert.ToInt32(r["ic_id"]);
                if (r["ic_id"] != null)
                {
                    m.inventory_class = ic_dal.Get(Convert.ToInt32(r["ic_id"]));
                }
                if (ip_dal.GetByInventory(i_id) != null)
                {
                    m.inventory_photo = ip_dal.GetByInventory(i_id);
                }
                
                m.standard = r["standard"].ToString();
                m.munit = r["munit"].ToString();
                m.aunit = r["aunit"].ToString();
                m.convert_rate = XTools.DBNullToDecimal(r["convert_rate"]);
                m.valuation = r["valuation"].ToString();
                m.add_date = XTools.DBNullToTime(r["add_date"]);
                m.update_date = XTools.DBNullToTime(r["update_date"]);
                m.stop_date = XTools.DBNullToTime(r["stop_date"]);
                m.updater_id = XTools.DBNullToInt(r["updater_id"]);
                SysCore.DAL.MemberDAL m_dal = new SysCore.DAL.MemberDAL();
                if (m.updater_id != null)
                {
                    m.updater_user = m_dal.Get(m.updater_id).nickname;
                }
                m.on_free1 = Convert.ToInt32(r["on_free1"]);
                m.on_free2 = Convert.ToInt32(r["on_free2"]);
                m.on_batch = Convert.ToInt32(r["on_batch"]);
                m.is_sale = XTools.DBNullToInt(r["is_sale"]);
                m.is_purchase = XTools.DBNullToInt(r["is_purchase"]);
                m.is_self = XTools.DBNullToInt(r["is_self"]);
                m.is_outsource = XTools.DBNullToInt(r["is_outsource"]);
                m.is_service = XTools.DBNullToInt(r["is_service"]);
                m.safety_stock = XTools.DBNullToDecimal(r["safety_stock"]);
                m.min_stock = XTools.DBNullToDecimal(r["min_stock"]);
                m.max_stock = XTools.DBNullToDecimal(r["max_stock"]);
                m.purchase_batch = XTools.DBNullToDecimal(r["purchase_batch"]);
                m.new_tax_unit_price = XTools.DBNullToDecimal(r["new_tax_unit_price"]);
                m.new_nat_unit_price = XTools.DBNullToDecimal(r["new_nat_unit_price"]);
                m.vd_id = XTools.DBNullToInt(r["vd_id"]);
                if (r["vd_id"] != DBNull.Value)
                    m.vendor = new VendorDAL().Get(Convert.ToInt32(r["vd_id"]));
                m.wh_id = XTools.DBNullToInt(r["wh_id"]);
                if (r["wh_id"] != DBNull.Value)
                m.warehouse = new WarehouseDAL().Get(Convert.ToInt32(r["wh_id"]));
                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>
    /// 通过编码得到ID
    /// </summary>
    /// <param name="编码">vc_code</param>
    /// <returns>int</returns>
    public int GetIdByCode(string i_code)
    {
        Department m = new Department();
        string sql = @"select i_id
                      from Base_Inventory where i_code=@i_code";
        SqlParameter[] par = {
                new SqlParameter("@i_code",i_code)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(Inventory m)
    {
        string sql = @"update Base_Inventory set 
                        i_code=@i_code,
                        i_name=@i_name,
                        ic_id=@ic_id,
                        standard=@standard,
                        munit=@munit,
                        aunit=@aunit,
                        convert_rate=@convert_rate,
                        valuation=@valuation,
                        add_date=@add_date,
                        update_date=@update_date,
                        stop_date=@stop_date,
                        updater_id=@updater_id,
                        on_free1=@on_free1,
                        on_free2=@on_free2,
                        on_batch=@on_batch,
                        is_sale=@is_sale,
                        is_purchase=@is_purchase,
                        is_self=@is_self,
                        is_outsource=@is_outsource,
                        is_service=@is_service,
                        safety_stock=@safety_stock,
                        min_stock=@min_stock,
                        max_stock=@max_stock,
                        purchase_batch=@purchase_batch,
                        new_tax_unit_price=@new_tax_unit_price,
                        new_nat_unit_price=@new_nat_unit_price,
                        vd_id=@vd_id,
                        wh_id=@wh_id,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where i_id=@i_id";
        SqlParameter[] par = {
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@i_code",m.i_code),
            new SqlParameter("@i_name",m.i_name),
            new SqlParameter("@ic_id",m.ic_id),
            new SqlParameter("@standard",XTools.StringToDBNull(m.standard)),
            new SqlParameter("@munit",XTools.StringToDBNull(m.munit)),
            new SqlParameter("@aunit",XTools.StringToDBNull(m.aunit)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@valuation",XTools.StringToDBNull(m.valuation)),
            new SqlParameter("@add_date",XTools.DateTimeToDBNull(m.add_date)),
            new SqlParameter("@stop_date",XTools.DateTimeToDBNull(m.stop_date)),
            new SqlParameter("@update_date",XTools.DateTimeToDBNull(m.update_date)),
            new SqlParameter("@updater_id",XTools.IntToDBNull(m.updater_id)),
            new SqlParameter("@on_free1",m.on_free1),
            new SqlParameter("@on_free2",m.on_free2),
            new SqlParameter("@on_batch",m.on_batch),
            new SqlParameter("@is_sale",Convert.ToInt32(m.is_sale)),
            new SqlParameter("@is_purchase",Convert.ToInt32(m.is_purchase)),
            new SqlParameter("@is_self",Convert.ToInt32(m.is_self)),
            new SqlParameter("@is_outsource",Convert.ToInt32(m.is_outsource)),
            new SqlParameter("@is_service",Convert.ToInt32(m.is_service)),
            new SqlParameter("@safety_stock",XTools.DecimalToDBNull(m.safety_stock)),
            new SqlParameter("@min_stock",XTools.DecimalToDBNull(m.min_stock)),
            new SqlParameter("@max_stock",XTools.DecimalToDBNull(m.max_stock)),
            new SqlParameter("@new_tax_unit_price",XTools.DecimalToDBNull(m.new_tax_unit_price)),
            new SqlParameter("@new_nat_unit_price",XTools.DecimalToDBNull(m.new_nat_unit_price)),
            new SqlParameter("@purchase_batch",XTools.DecimalToDBNull(m.purchase_batch)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@wh_id",XTools.IntToDBNull(m.wh_id)),
            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 void Delete(int i_id)
    {
        Inventory m = new Inventory();
        string sql = "delete Base_Inventory where i_id in (@i_id)";
        SqlParameter[] par = {
                new SqlParameter("@i_id",i_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有存货
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_Inventory order by i_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 快速下拉查询
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetList_FastSearch(string keyword, string where)
    {
        //'<img width=100 src=' + ip.small_pic_url + '>' as small_pic_url
        string sql = @"select top 5 i.i_id,i.i_code,i.i_name,i.standard,i.munit,
                       '<img width=100 src=' + ip.small_pic_url + '>' as small_pic_url
                       from Base_Inventory i left join
                       Base_InventoryPhoto ip on i.i_id=ip.i_id
                       where stop_date is null and (i.i_code like '%'+@keyword+'%' or i.i_name like '%'+@keyword+'%') 
                       @where
                       order by i.i_code";
        sql = sql.Replace("@where", where);
        SqlParameter[] par ={
            new SqlParameter("@keyword",keyword)
        };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        return dt;
    }
    //打印用的sql
    public string sql_print = "";
    //列表查询用的sql
    string sql_list = @"
                    select i.*,ic.ic_code,ic.ic_name,v.vd_name,wh.wh_name,vm.vm_name,
                    case when on_free1=1 then '已启用' else '' end on_free1_name,
                    case when on_free2=1 then '已启用' else '' end on_free2_name,
                    case when on_batch=1 then '已启用' else '' end on_batch_name,
                    case when is_sale=1 then '是' else '' end is_sale_name,
                    case when is_purchase=1 then '是' else '' end is_purchase_name,
                    case when is_self=1 then '是' else '' end is_self_name,
                    case when is_outsource=1 then '是' else '' end is_outsource_name,
                    case when is_service=1 then '是' else '' end is_service_name
                    from Base_Inventory i
                    left join Base_Vendor v on v.vd_id=i.vd_id
                    left join Base_Warehouse wh on wh.wh_id=i.vd_id
                    left join Base_InventoryClass ic on ic.ic_id=i.ic_id
                    left join Base_ValuationMethod vm on vm.vm_id=i.valuation";
    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select 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 + ") 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 (" + sql_list + ") t 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="i_code">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string i_code)
    {
        string sql = "select count(1) from Base_Inventory where i_code=@i_code";
        SqlParameter[] par ={
                new SqlParameter("@i_code", i_code)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
}
