using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using WMS.DBUtility;
using WMS.IData.ICK;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Model.CK;
using WMS.Model.SC;
using WMS.IData.ISC;

namespace WMS.SqlServerData.SCData
{
    public class GoodsMaterialData : IGoodsMaterial
    {
        /// <summary>
        /// 获取商品原材料对应关系
        /// </summary>
        /// <returns>商品原材料对应关系</returns>
        public DataTable GetGoodsMaterialDT(GoodsMaterialModel model)
        {
            string strSql= string.Format(@"select 						  
	                                        a.GOODS_ID,                       
	                                        a.MATERIAL_ID,                    
	                                        a.STAND_NUM,                      
	                                        a.MAX_NUM,                        
	                                        a.LOSS_PERCENT,                   
	                                        a.OPERAT_MAN,                     
	                                        a.OPERAT_DATE,                    
	                                        a.REMARK,                         
	                                        a.UNIT,
	                                        t.VC_DICTIONARY_NAME UNIT_NAME,                           
	                                        a.PRICE,                          
	                                        b.goods_name,                     
	                                        c.goods_name MATERIAL_NAME ,a.GOODS_NUM,a.SCALING, a.WIP_ENTITY    
                                        FROM T_SC_GOODS_MATERIAL a         
	                                        left join t_base_goods b          
	                                        on a.goods_id = b.goods_id        
	                                        left join t_base_goods c          
	                                        on a.MATERIAL_ID = c.goods_id 
	                                        left join  T_SYS_DICTIONARY_TAB t
                                           on  a.UNIT = t.VC_CHAR_VALUE and t.Vc_Dictionary_Type = 'vc_uom'
                                        where 1 = 1  ");
           
            if (!string.IsNullOrEmpty(model.GOODS_ID))
            {
                strSql+=string.Format("and a.GOODS_ID ='{0}'", model.GOODS_ID );
            }

            if (!string.IsNullOrEmpty(model.WIP_ENTITY))
            {
                strSql += string.Format("and a.WIP_ENTITY_ID ='{0}'", model.WIP_ENTITY);
            }
            strSql +=" order by a.GOODS_ID";

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 插入商品原材料对应关系信息
        /// </summary>
        /// <param name="GoodsMaterialModel">商品原材料对应关系Model</param>
        public void InsertGoodsMaterial(GoodsMaterialModel model)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("insert into T_SC_GOODS_MATERIAL(");
            strSql.AppendLine("GOODS_ID,MATERIAL_ID,STAND_NUM,MAX_NUM,LOSS_PERCENT,OPERAT_MAN,REMARK,UNIT,PRICE,GOODS_UNIT,GOODS_NUM,SCALING)");
            strSql.AppendLine(" values (");
            strSql.AppendLine(":GOODS_ID,:MATERIAL_ID,:STAND_NUM,:MAX_NUM,:LOSS_PERCENT,:OPERAT_MAN,:REMARK,:UNIT,:PRICE,:GOODS_UNIT,:GOODS_NUM,:SCALING)");
            OracleParameter[] parameters = {
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,50),
					new OracleParameter(":MATERIAL_ID",OracleDbType.Varchar2,50),
					new OracleParameter(":STAND_NUM",OracleDbType.Decimal),
					new OracleParameter(":MAX_NUM",OracleDbType.Decimal),
					new OracleParameter(":LOSS_PERCENT",OracleDbType.Decimal),
					new OracleParameter(":OPERAT_MAN",OracleDbType.Varchar2,50), 
					new OracleParameter(":REMARK",OracleDbType.Varchar2,50),
					new OracleParameter(":UNIT",OracleDbType.Char,10),
					new OracleParameter(":PRICE",OracleDbType.Decimal), 
                    new OracleParameter(":GOODS_UNIT",OracleDbType.Varchar2,50),
                    new OracleParameter(":GOODS_NUM",OracleDbType.Decimal),
                    new OracleParameter(":SCALING",OracleDbType.Decimal)

            };
            parameters[0].Value = model.GOODS_ID;
            parameters[1].Value = model.MATERIAL_ID;
            parameters[2].Value = model.STAND_NUM;
            parameters[3].Value = model.MAX_NUM;
            parameters[4].Value = model.LOSS_PERCENT;
            parameters[5].Value = model.OPERAT_MAN; 
            parameters[6].Value = model.REMARK;
            parameters[7].Value = !string.IsNullOrEmpty(model.UNIT)?model.UNIT.Trim():"";
            parameters[8].Value = model.PRICE;
            parameters[9].Value = model.GOODS_UNIT; 
            parameters[10].Value = model.GOODS_NUM;
            parameters[11].Value = model.SCALING;
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 删除一个商品原材料对应关系信息
        /// </summary>
        /// <param name="ID">商品ID</param>
        public void DeleteGoodsMaterial(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_SC_GOODS_MATERIAL ");
            strSql.AppendLine(" where GOODS_ID=:GOODS_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters); 
        }
        /// <summary>
        /// 根据物料查询bOM
        /// </summary>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public DataTable GetGoodsMaterialList(string goodsId)
        {
            string sqlstr = "select t.GOODS_ID as FGOODSID,t.MATERIAL_ID,t.STAND_NUM,a.GOODS_NAME,a.SEMISTS,"
             + " isnull(b.SHELVES_NUM, 0)SHELVES_NUM,isnull(b.OCCUPY_NUM, 0) as OCCUPY,isnull (c.AMOUNT, 0) as BUY_NUM ,"
             + " e.GOODS_NAME as FGOODSNAMAE from T_SC_GOODS_MATERIAL t"
              + " left join T_BASE_GOODS a on t.MATERIAL_ID = a.GOODS_ID"
              + " left join T_BASE_GOODS e on t.GOODS_ID = e.GOODS_ID"
              + " left join T_MI_STOCK b on t.MATERIAL_ID = b.GOODS_ID"
              + " left join T_RK_WARE_NOTICE_TAB c on c.GOODS_ID = t.MATERIAL_ID"
              + " where t.GOODS_ID='" + goodsId + "'"; 

 
            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }


        public DataTable GetGoodsMaterialNew()
        {
            string sqlstr = "select *  from T_SC_GOODS_MATERIAL t";

            return SystemDataObject.Instance.GetDataTable(sqlstr);

        }



    }
}