using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using System.Data;

using WMS.DBUtility;
using WMS.Model.Base;
using WMS.IData.IBase;
using System.Text.RegularExpressions;

namespace WMS.SqlServerData.BaseData
{
    public class StorageAreaData : IStorageArea
    {
        /// <summary>
        /// 获取库区信息结果集
        /// </summary>
        /// <param name="areaInfoModel">库区信息</param>
        /// <returns>库区信息结果集</returns>
        public DataTable GetAreaInfoDS(StorageAreaModel areaInfoModel, string parentAreaName)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("select									     ");
            sqlBuilder.AppendLine("  a.storage_area_id,         --库区编号       ");
            sqlBuilder.AppendLine("  a.storage_area_name,       --库区名称       ");
            sqlBuilder.AppendLine("  a.storage_area_short_name, --库区简称       ");
            sqlBuilder.AppendLine("  a.py_name,                 --拼音简称       ");
            sqlBuilder.AppendLine("  a.storage_id,              --所属仓库编号   ");
            sqlBuilder.AppendLine("  a.sort,                    --排序           ");
            sqlBuilder.AppendLine("  ( case a.status when '1' then  '停用' when '0'  then '正常' else'其他' end) status, --Status  0：正常 1：停用	");
            sqlBuilder.AppendLine("   ISNULL(a.remark,' ') remark,  --备注           ");
            sqlBuilder.AppendLine("  a.version,                 --版本号         ");
            sqlBuilder.AppendLine("  b.STORAGE_NAME,            --所属仓库       ");
            sqlBuilder.AppendLine("  a.PARENT_ID,               --父编号       ");
            sqlBuilder.AppendLine("  a.operatorid,              --操作人         ");
            sqlBuilder.AppendLine("  a.operatedate,             --操作日期       ");
            sqlBuilder.AppendLine("  a.edit                     --可否编辑       ");
            sqlBuilder.AppendLine("from                                          ");
            sqlBuilder.AppendLine("  T_BASE_STORAGE_AREA a left join t_base_storageinfo b  ");
            sqlBuilder.AppendLine("  on a.STORAGE_id = b.STORAGE_id              ");
            sqlBuilder.AppendLine("where 1 = 1                                   ");

            if (areaInfoModel != null)
            {
                if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
                {
                    sqlBuilder.AppendLine(" and a.STORAGE_ID = '" + areaInfoModel.STORAGE_ID + "'");
                }
                if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_ID))
                {
                    sqlBuilder.AppendLine(" and a.storage_area_id = '" + areaInfoModel.STORAGE_AREA_ID + "'");
                }
                if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_NAME))
                {
                    sqlBuilder.AppendLine(" and a.storage_area_name = '" + areaInfoModel.STORAGE_AREA_NAME + "'");
                }
                if (!string.IsNullOrEmpty(areaInfoModel.PARENT_ID))
                {
                    sqlBuilder.AppendLine(" and a.PARENT_ID = '" + areaInfoModel.PARENT_ID + "'");
                }
                if (!string.IsNullOrEmpty(areaInfoModel.EDIT))
                {
                    sqlBuilder.AppendLine(" and a.edit = '" + areaInfoModel.EDIT + "'");
                }
                if (!string.IsNullOrEmpty(areaInfoModel.STATUS))
                {
                    sqlBuilder.AppendLine(" and a.STATUS = '" + areaInfoModel.STATUS + "'");
                }
            }
            //sqlBuilder.AppendLine(" order by a.SORT,PARENT_ID,a.storage_area_id");
            return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
        }

        /// <summary>
        /// 获取库区信息结果集(编号、名称)
        /// </summary>
        /// <param name="areaInfoModel">库区信息</param>
        /// <returns>库区信息结果集(编号、名称)</returns>
        public DataTable GetAreaInfoDSIdName(StorageAreaModel areaInfoModel)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("select									   ");
            sqlBuilder.AppendLine("  storage_area_id,         --库区编号       ");
            sqlBuilder.AppendLine("  storage_area_name,       --库区名称       ");
            sqlBuilder.AppendLine("from                                        ");
            sqlBuilder.AppendLine("  T_BASE_STORAGE_AREA                       ");
            sqlBuilder.AppendLine("where 1=1                                   ");

            if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
            {
                sqlBuilder.AppendLine(" and STORAGE_ID = '" + areaInfoModel.STORAGE_ID + "'");
            }
            sqlBuilder.AppendLine(" order by SORT");
            return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
        }

        /// <summary>
        /// 新增或修改库区信息
        /// </summary>
        /// <param name="areaInfoModel"></param>
        /// <param name="flag">flag 操作标示符 0：新增  1:修改</param>
        public string InsOrUpdAreaInfo(StorageAreaModel areaInfoModel, int flag)
        {
            string str = "select NEXT VALUE FOR  seq_BASE_STORAGE_AREA ";
            DataSet ds = SystemDataObject.Instance.GetDataSet(str);
            string seq = ds.Tables[0].Rows[0][0].ToString();


            //新增
            if (0 == flag)
            {
                if (string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_ID))
                {

                    if (string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
                    {
                        areaInfoModel.STORAGE_AREA_ID = "KQ" + areaInfoModel.STORAGE_ID + seq;
                    }
                    else
                    {
                        areaInfoModel.STORAGE_AREA_ID = "KQ" + Regex.Replace(areaInfoModel.STORAGE_ID, "[a-z,A-Z]", "", RegexOptions.IgnoreCase) + seq;
                    }

                    string sqlstr =@"insert into 
	                            T_BASE_STORAGE_AREA (STORAGE_AREA_ID,STORAGE_AREA_NAME,STORAGE_AREA_SHORT_NAME,PY_NAME,STORAGE_ID,SORT,REMARK,PARENT_ID,OPERATORID)
                            values
                            ('" + areaInfoModel.STORAGE_AREA_ID + "', '" + areaInfoModel.STORAGE_AREA_NAME + "','" + areaInfoModel.STORAGE_AREA_SHORT_NAME + "',"
                           + " '" + areaInfoModel.PY_NAME + "','" + areaInfoModel.STORAGE_ID + "' ,'" + areaInfoModel.SORT + "','" + areaInfoModel.REMARK + "',"
                           + " '" + areaInfoModel.PARENT_ID + "','" + areaInfoModel.OPERATORID + "')";


                    SystemDataObject.Instance.ExecuteSql(sqlstr);
                }
            }
            //修改
            else if (1 == flag)
            {
                string sqlstr =@"
                                update
	                                T_BASE_STORAGE_AREA
                                set                                                      
	                                storage_area_name       =:storage_area_name,       	--库区名称    		
	                                storage_area_short_name =:storage_area_short_name, 	--库区简称    		
	                                py_name                 =:py_name,                 	--拼音简称    		
	                                storage_id              =:storage_id,              	--所属仓库编号		
	                                sort                   	=:sort,                    	--排序        	
	                                status 					=:status, 					--Status        	
	                                remark 					=:remark, 					--备注        	
	                                version                 =:version,  				--版本号      
	                                PARENT_ID               =:PARENT_ID,                --父编号 
                                    operatorid              =:operatorid,               --操作人
                                    operatedate             =getdate(),                   --操作日期
                                    edit                    =:edit                      --可否编辑
                                where
	                                storage_area_id			=:storage_area_id			--库区编号 
                                ";
                OracleParameter[] parameter = new OracleParameter[]
                    {
                       new OracleParameter(":storage_area_name",OracleDbType.Varchar2),
                       new OracleParameter(":storage_area_short_name",OracleDbType.Varchar2),
                       new OracleParameter(":py_name",OracleDbType.Varchar2),
                       new OracleParameter(":storage_id",OracleDbType.Varchar2),
                       new OracleParameter(":sort",OracleDbType.Decimal),
                       new OracleParameter(":status",OracleDbType.Varchar2),
                       new OracleParameter(":remark",OracleDbType.Varchar2),
                       new OracleParameter(":version",OracleDbType.Varchar2),
                       new OracleParameter(":PARENT_ID",OracleDbType.Varchar2),
                       new OracleParameter(":operatorid",OracleDbType.Varchar2),
                       new OracleParameter(":edit",OracleDbType.Varchar2),
                       new OracleParameter(":storage_area_id",OracleDbType.Varchar2),
                    };
                parameter[0].Value = areaInfoModel.STORAGE_AREA_NAME;
                parameter[1].Value = areaInfoModel.STORAGE_AREA_SHORT_NAME;
                parameter[2].Value = areaInfoModel.PY_NAME;
                parameter[3].Value = areaInfoModel.STORAGE_ID;
                parameter[4].Value = areaInfoModel.SORT;
                parameter[5].Value = areaInfoModel.STATUS;
                parameter[6].Value = areaInfoModel.REMARK;
                parameter[7].Value = areaInfoModel.VERSION;
                parameter[8].Value = areaInfoModel.PARENT_ID;
                parameter[9].Value = areaInfoModel.OPERATORID;
                parameter[10].Value = areaInfoModel.EDIT;
                parameter[11].Value = areaInfoModel.STORAGE_AREA_ID;

                SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
            }

            return areaInfoModel.STORAGE_AREA_ID;
        }

        /// <summary>
        /// 删除库区信息
        /// </summary>
        /// <param name="strAreaInfoID"></param>
        public void DeleteAreaInfo(string strAreaInfoID)
        {
            string sqlstr =@"delete from T_BASE_STORAGE_AREA where storage_area_id =:storage_area_id";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":storage_area_id",OracleDbType.Varchar2), 
            };
            parameter[0].Value = strAreaInfoID;
            SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
        }

        /// <summary>
        /// 删除属于某仓库的库区信息
        /// </summary>
        /// <param name="strStorageInfoID">某仓库编号</param>
        public void DeleteAreaInfoByStorageInfoID(string strStorageInfoID)
        {
            string sqlstr =@"delete from T_BASE_STORAGE_AREA where storage_id =:storage_id";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":storage_id",OracleDbType.Varchar2), 
            };
            parameter[0].Value = strStorageInfoID;
            SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
        }

        /// <summary>
        /// 删除属于某仓库分类的库区信息
        /// </summary>
        /// <param name="strStorageTypeID">某仓库分类</param>
        public void DeleteAreaInfoByStorageTypeID(string strStorageTypeID)
        {
            string sqlstr =@"delete 
                            from 
                                T_BASE_STORAGE_AREA 
                            where 
                                storage_id 
                            in 
                            (
                            select 
                                STORAGE_ID 
                            from 
                                T_BASE_STORAGEINFO
                            where 
                                STORAGE_TYPE_ID =:STORAGE_TYPE_ID
                            )";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":STORAGE_TYPE_ID",OracleDbType.Varchar2), 
            };
            parameter[0].Value = strStorageTypeID;
            SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
        }

        /// <summary>
        /// 根据仓库编号停用或启用某库区
        /// </summary>
        /// <param name="strStorageInfoID">某仓库编号</param>
        /// <param name="flag">0停用1正常</param>
        public void CloseAreaInfoByStorageInfoID(string strStorageInfoID, int flag)
        {
            string sqlstr =@"update T_BASE_STORAGE_AREA set status = '" + flag + "' where storage_id =:storage_id";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":storage_id",OracleDbType.Varchar2), 
            };
            parameter[0].Value = strStorageInfoID;
            SystemDataObject.Instance.ExecuteSqlCommit(sqlstr, parameter);
        }

        /// <summary>
        /// 通过用户信息 获得用户所属库区
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetStorageAreaByUserIDandStorageID(UserDataModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select t.*,
                               a.storage_area_name  AREA_NAME,
                               s.storage_id,
                               s.storage_name,
                               d.vc_dictionary_name WORKKIND_NAME
                          from T_BASE_USERAREA t
                          left join t_Base_Storage_Area a
                            on t.area_id = a.storage_area_id
                          left join t_Base_Userinfo u
                            on t.user_id = u.user_id
                          left join t_Base_Storageinfo s
                            on u.storage_id = s.storage_id
                          left join t_Sys_Dictionary_Tab d
                            on t.workkind_id = d.vc_char_value
               where d.vc_dictionary_type = 'workkind_type'");
            strSql.Append("    and t.user_id = '" + model.USER_ID + "' ");
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());

        }

        /// <summary>
        /// 更新仓库的名称
        /// </summary>
        /// <param name="stroAge_id"></param>
        /// <param name="STORAGE_AREA_NAME"></param>
        public void UpdateAreaDataName(string stroAge_id,string STORAGE_AREA_NAME)
        {
            string sqlStr = "update T_BASE_STORAGE_AREA set STORAGE_AREA_NAME='"+ STORAGE_AREA_NAME + "' where  STORAGE_AREA_ID='" + stroAge_id + "'";
            SystemDataObject.Instance.ExecuteSql(sqlStr);

        }

    }
}