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.Base;
using WMS.IData.IBase;

namespace WMS.SqlServerData.BaseData
{
    public class SKUData : ISKU
    {
        /// <summary>
        /// 获取规格
        /// </summary>
        /// <returns>规格</returns>
        public DataTable GetSKUDT(SKUModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select SKU_ID,SKU_NAME,GOODS_ID,BRAND,GMODEL,GSIZE,CONFIGURE,GCLASS,COLOR,DESIGN_COLOR,CAPACITY_PACKING,UNIT,GUSE,PRICE,ORIGIN,GLENGTH,GWIDTH,GHEIGHT,GVOLUME,GVOLUME_UNIT,GROSS_WEIGHT,NET_WEIGHT,SCALE_UNIT,PROVIDER_ID,HSNUMBER,ABC,HAZARDOUS,GSTORED,PERIOD,PERIODUNIT,ADDTAX,SEASON,TARIFFRATE,GIFT_FLAG,SC_FLAG,STATUS,REMARK,CREATE_MAN,CREATE_DATE,UPDATE_MAN,UPDATE_DATE ");
            strSql.Append(" FROM T_BASE_SKU a ");
            strSql.AppendLine("where 1 = 1");
            if (!string.IsNullOrEmpty(model.SKU_ID))
            {
                strSql.AppendLine(" and a.SKU_ID = '" + model.SKU_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.GOODS_ID))
            {
                strSql.AppendLine(" and a.GOODS_ID = '" + model.GOODS_ID + "' ");
            }
            strSql.AppendLine(" order by a.GOODS_ID,a.SKU_ID desc");

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

        /// <summary>
        /// 插入规格信息
        /// </summary>
        /// <param name="SKUModel">规格Model</param>
        /// <returns>规格编号</returns>
        public string InsertSKU(SKUModel model)
        {
            //string str = "select SEQ_BASE_SKU.nextval from dual ";
            //DataSet ds = SystemDataObject.Instance.GetDataSet(str);
            //model.SKU_ID = "SKU" + ds.Tables[0].Rows[0][0].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_BASE_SKU(");
            strSql.Append("SKU_ID,SKU_NAME,GOODS_ID,BRAND,GMODEL,GSIZE,CONFIGURE,GCLASS,COLOR,DESIGN_COLOR,CAPACITY_PACKING,UNIT,GUSE,PRICE,ORIGIN,GLENGTH,GWIDTH,GHEIGHT,GVOLUME,GVOLUME_UNIT,GROSS_WEIGHT,NET_WEIGHT,SCALE_UNIT,PROVIDER_ID,HSNUMBER,ABC,HAZARDOUS,GSTORED,PERIOD,PERIODUNIT,ADDTAX,SEASON,TARIFFRATE,GIFT_FLAG,SC_FLAG,STATUS,IMG,REMARK,CREATE_MAN,CREATE_DATE,UPDATE_MAN,UPDATE_DATE)");
            strSql.Append(" values (");
            strSql.Append(":SKU_ID,:SKU_NAME,:GOODS_ID,:BRAND,:GMODEL,:GSIZE,:CONFIGURE,:GCLASS,:COLOR,:DESIGN_COLOR,:CAPACITY_PACKING,:UNIT,:GUSE,:PRICE,:ORIGIN,:GLENGTH,:GWIDTH,:GHEIGHT,:GVOLUME,:GVOLUME_UNIT,:GROSS_WEIGHT,:NET_WEIGHT,:SCALE_UNIT,:PROVIDER_ID,:HSNUMBER,:ABC,:HAZARDOUS,:GSTORED,:PERIOD,:PERIODUNIT,:ADDTAX,:SEASON,:TARIFFRATE,:GIFT_FLAG,:SC_FLAG,:STATUS,:IMG,:REMARK,:CREATE_MAN,:CREATE_DATE,:UPDATE_MAN,:UPDATE_DATE)");
            OracleParameter[] parameters = {
					new OracleParameter(":SKU_ID",OracleDbType.Varchar2),
					new OracleParameter(":SKU_NAME",OracleDbType.Varchar2),
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
					new OracleParameter(":BRAND",OracleDbType.Varchar2),
					new OracleParameter(":GMODEL",OracleDbType.Varchar2),
					new OracleParameter(":GSIZE",OracleDbType.Varchar2),
					new OracleParameter(":CONFIGURE",OracleDbType.Varchar2),
					new OracleParameter(":GCLASS",OracleDbType.Varchar2),
					new OracleParameter(":COLOR",OracleDbType.Varchar2),
					new OracleParameter(":DESIGN_COLOR",OracleDbType.Varchar2),
					new OracleParameter(":CAPACITY_PACKING",OracleDbType.Varchar2),
					new OracleParameter(":UNIT",OracleDbType.Varchar2),
					new OracleParameter(":GUSE",OracleDbType.Varchar2),
					new OracleParameter(":PRICE", OracleDbType.Decimal),
					new OracleParameter(":ORIGIN",OracleDbType.Varchar2),
					new OracleParameter(":GLENGTH", OracleDbType.Decimal),
					new OracleParameter(":GWIDTH", OracleDbType.Decimal),
					new OracleParameter(":GHEIGHT", OracleDbType.Decimal),
					new OracleParameter(":GVOLUME", OracleDbType.Decimal),
					new OracleParameter(":GVOLUME_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":GROSS_WEIGHT", OracleDbType.Decimal),
					new OracleParameter(":NET_WEIGHT", OracleDbType.Decimal),
					new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
					new OracleParameter(":HSNUMBER",OracleDbType.Varchar2),
					new OracleParameter(":ABC",OracleDbType.Varchar2),
					new OracleParameter(":HAZARDOUS", OracleDbType.Char),
					new OracleParameter(":GSTORED",OracleDbType.Varchar2),
					new OracleParameter(":PERIOD", OracleDbType.Decimal),
					new OracleParameter(":PERIODUNIT",OracleDbType.Varchar2),
					new OracleParameter(":ADDTAX", OracleDbType.Decimal),
					new OracleParameter(":SEASON",OracleDbType.Varchar2),
					new OracleParameter(":TARIFFRATE", OracleDbType.Decimal),
					new OracleParameter(":GIFT_FLAG",OracleDbType.Varchar2),
					new OracleParameter(":SC_FLAG", OracleDbType.Char),
					new OracleParameter(":STATUS",OracleDbType.Varchar2),
					new OracleParameter(":IMG", OracleDbType.Blob),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":UPDATE_MAN",OracleDbType.Varchar2),
					new OracleParameter(":UPDATE_DATE", OracleDbType.Date)};
            parameters[0].Value = model.SKU_ID;
            parameters[1].Value = model.SKU_NAME;
            parameters[2].Value = model.GOODS_ID;
            parameters[3].Value = model.BRAND;
            parameters[4].Value = model.GMODEL;
            parameters[5].Value = model.GSIZE;
            parameters[6].Value = model.CONFIGURE;
            parameters[7].Value = model.GCLASS;
            parameters[8].Value = model.COLOR;
            parameters[9].Value = model.DESIGN_COLOR;
            parameters[10].Value = model.CAPACITY_PACKING;
            parameters[11].Value = model.UNIT;
            parameters[12].Value = model.GUSE;
            parameters[13].Value = model.PRICE;
            parameters[14].Value = model.ORIGIN;
            parameters[15].Value = model.GLENGTH;
            parameters[16].Value = model.GWIDTH;
            parameters[17].Value = model.GHEIGHT;
            parameters[18].Value = model.GVOLUME;
            parameters[19].Value = model.GVOLUME_UNIT;
            parameters[20].Value = model.GROSS_WEIGHT;
            parameters[21].Value = model.NET_WEIGHT;
            parameters[22].Value = model.SCALE_UNIT;
            parameters[23].Value = model.PROVIDER_ID;
            parameters[24].Value = model.HSNUMBER;
            parameters[25].Value = model.ABC;
            parameters[26].Value = model.HAZARDOUS;
            parameters[27].Value = model.GSTORED;
            parameters[28].Value = model.PERIOD;
            parameters[29].Value = model.PERIODUNIT;
            parameters[30].Value = model.ADDTAX;
            parameters[31].Value = model.SEASON;
            parameters[32].Value = model.TARIFFRATE;
            parameters[33].Value = model.GIFT_FLAG;
            parameters[34].Value = model.SC_FLAG;
            parameters[35].Value = model.STATUS;
            parameters[36].Value = model.IMG;
            parameters[37].Value = model.REMARK;
            parameters[38].Value = model.CREATE_MAN;
            parameters[39].Value = model.CREATE_DATE;
            parameters[40].Value = model.UPDATE_MAN;
            parameters[41].Value = model.UPDATE_DATE;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

            return model.SKU_ID;
        }

        /// <summary>
        /// 修改规格信息
        /// </summary>
        /// <param name="SKUModel">规格Model</param>
        public void UpdateSKU(SKUModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update T_BASE_SKU set ");
            strSql.Append("SKU_ID=:SKU_ID,");
            strSql.Append("SKU_NAME=:SKU_NAME,");
            strSql.Append("GOODS_ID=:GOODS_ID,");
            strSql.Append("BRAND=:BRAND,");
            strSql.Append("GMODEL=:GMODEL,");
            strSql.Append("GSIZE=:GSIZE,");
            strSql.Append("CONFIGURE=:CONFIGURE,");
            strSql.Append("GCLASS=:GCLASS,");
            strSql.Append("COLOR=:COLOR,");
            strSql.Append("DESIGN_COLOR=:DESIGN_COLOR,");
            strSql.Append("CAPACITY_PACKING=:CAPACITY_PACKING,");
            strSql.Append("UNIT=:UNIT,");
            strSql.Append("GUSE=:GUSE,");
            strSql.Append("PRICE=:PRICE,");
            strSql.Append("ORIGIN=:ORIGIN,");
            strSql.Append("GLENGTH=:GLENGTH,");
            strSql.Append("GWIDTH=:GWIDTH,");
            strSql.Append("GHEIGHT=:GHEIGHT,");
            strSql.Append("GVOLUME=:GVOLUME,");
            strSql.Append("GVOLUME_UNIT=:GVOLUME_UNIT,");
            strSql.Append("GROSS_WEIGHT=:GROSS_WEIGHT,");
            strSql.Append("NET_WEIGHT=:NET_WEIGHT,");
            strSql.Append("SCALE_UNIT=:SCALE_UNIT,");
            strSql.Append("PROVIDER_ID=:PROVIDER_ID,");
            strSql.Append("HSNUMBER=:HSNUMBER,");
            strSql.Append("ABC=:ABC,");
            strSql.Append("HAZARDOUS=:HAZARDOUS,");
            strSql.Append("GSTORED=:GSTORED,");
            strSql.Append("PERIOD=:PERIOD,");
            strSql.Append("PERIODUNIT=:PERIODUNIT,");
            strSql.Append("ADDTAX=:ADDTAX,");
            strSql.Append("SEASON=:SEASON,");
            strSql.Append("TARIFFRATE=:TARIFFRATE,");
            strSql.Append("GIFT_FLAG=:GIFT_FLAG,");
            strSql.Append("SC_FLAG=:SC_FLAG,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("IMG=:IMG,");
            strSql.Append("REMARK=:REMARK,");
            strSql.Append("CREATE_MAN=:CREATE_MAN,");
            strSql.Append("CREATE_DATE=:CREATE_DATE,");
            strSql.Append("UPDATE_MAN=:UPDATE_MAN,");
            strSql.Append("UPDATE_DATE=:UPDATE_DATE");
            strSql.Append(" where SKU_ID=:SKU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SKU_ID",OracleDbType.Varchar2),
					new OracleParameter(":SKU_NAME",OracleDbType.Varchar2),
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
					new OracleParameter(":BRAND",OracleDbType.Varchar2),
					new OracleParameter(":GMODEL",OracleDbType.Varchar2),
					new OracleParameter(":GSIZE",OracleDbType.Varchar2),
					new OracleParameter(":CONFIGURE",OracleDbType.Varchar2),
					new OracleParameter(":GCLASS",OracleDbType.Varchar2),
					new OracleParameter(":COLOR",OracleDbType.Varchar2),
					new OracleParameter(":DESIGN_COLOR",OracleDbType.Varchar2),
					new OracleParameter(":CAPACITY_PACKING",OracleDbType.Varchar2),
					new OracleParameter(":UNIT",OracleDbType.Varchar2),
					new OracleParameter(":GUSE",OracleDbType.Varchar2),
					new OracleParameter(":PRICE", OracleDbType.Decimal),
					new OracleParameter(":ORIGIN",OracleDbType.Varchar2),
					new OracleParameter(":GLENGTH", OracleDbType.Decimal),
					new OracleParameter(":GWIDTH", OracleDbType.Decimal),
					new OracleParameter(":GHEIGHT", OracleDbType.Decimal),
					new OracleParameter(":GVOLUME", OracleDbType.Decimal),
					new OracleParameter(":GVOLUME_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":GROSS_WEIGHT", OracleDbType.Decimal),
					new OracleParameter(":NET_WEIGHT", OracleDbType.Decimal),
					new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
					new OracleParameter(":HSNUMBER",OracleDbType.Varchar2),
					new OracleParameter(":ABC",OracleDbType.Varchar2),
					new OracleParameter(":HAZARDOUS", OracleDbType.Char),
					new OracleParameter(":GSTORED",OracleDbType.Varchar2),
					new OracleParameter(":PERIOD", OracleDbType.Decimal),
					new OracleParameter(":PERIODUNIT",OracleDbType.Varchar2),
					new OracleParameter(":ADDTAX", OracleDbType.Decimal),
					new OracleParameter(":SEASON",OracleDbType.Varchar2),
					new OracleParameter(":TARIFFRATE", OracleDbType.Decimal),
					new OracleParameter(":GIFT_FLAG",OracleDbType.Varchar2),
					new OracleParameter(":SC_FLAG", OracleDbType.Char),
					new OracleParameter(":STATUS",OracleDbType.Varchar2),
					new OracleParameter(":IMG", OracleDbType.Blob),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_DATE", OracleDbType.Date),
					new OracleParameter(":UPDATE_MAN",OracleDbType.Varchar2),
					new OracleParameter(":UPDATE_DATE", OracleDbType.Date)};
            parameters[0].Value = model.SKU_ID;
            parameters[1].Value = model.SKU_NAME;
            parameters[2].Value = model.GOODS_ID;
            parameters[3].Value = model.BRAND;
            parameters[4].Value = model.GMODEL;
            parameters[5].Value = model.GSIZE;
            parameters[6].Value = model.CONFIGURE;
            parameters[7].Value = model.GCLASS;
            parameters[8].Value = model.COLOR;
            parameters[9].Value = model.DESIGN_COLOR;
            parameters[10].Value = model.CAPACITY_PACKING;
            parameters[11].Value = model.UNIT;
            parameters[12].Value = model.GUSE;
            parameters[13].Value = model.PRICE;
            parameters[14].Value = model.ORIGIN;
            parameters[15].Value = model.GLENGTH;
            parameters[16].Value = model.GWIDTH;
            parameters[17].Value = model.GHEIGHT;
            parameters[18].Value = model.GVOLUME;
            parameters[19].Value = model.GVOLUME_UNIT;
            parameters[20].Value = model.GROSS_WEIGHT;
            parameters[21].Value = model.NET_WEIGHT;
            parameters[22].Value = model.SCALE_UNIT;
            parameters[23].Value = model.PROVIDER_ID;
            parameters[24].Value = model.HSNUMBER;
            parameters[25].Value = model.ABC;
            parameters[26].Value = model.HAZARDOUS;
            parameters[27].Value = model.GSTORED;
            parameters[28].Value = model.PERIOD;
            parameters[29].Value = model.PERIODUNIT;
            parameters[30].Value = model.ADDTAX;
            parameters[31].Value = model.SEASON;
            parameters[32].Value = model.TARIFFRATE;
            parameters[33].Value = model.GIFT_FLAG;
            parameters[34].Value = model.SC_FLAG;
            parameters[35].Value = model.STATUS;
            parameters[36].Value = model.IMG;
            parameters[37].Value = model.REMARK;
            parameters[38].Value = model.CREATE_MAN;
            parameters[39].Value = model.CREATE_DATE;
            parameters[40].Value = model.UPDATE_MAN;
            parameters[41].Value = model.UPDATE_DATE;


            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一个规格
        /// </summary>
        /// <param name="SKU_ID">规格ID</param>
        public void DeleteSKU(string SKU_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_BASE_SKU ");
            strSql.Append(" where SKU_ID=:SKU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":SKU_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = SKU_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters); 
        }


        /// <summary>
        /// 删除一个商品的规格
        /// </summary>
        /// <param name="GOODS_ID">商品ID</param>
        public void DeleteSKUByGoods(string GOODS_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_BASE_SKU ");
            strSql.Append(" where GOODS_ID=:GOODS_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = GOODS_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
    }
}