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;

namespace WMS.SqlServerData.BaseData
{
    public class StorageOrderData : IStorageOrder
    {
        /// <summary>
        /// 获取仓库订单发货区域
        /// </summary>
        /// <returns>仓库订单关联Model</returns>
        public DataTable GetStorOrderDT(StorageOrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("select 							");
            strSql.AppendLine("	a.STORAGE_ID,                   ");
            strSql.AppendLine("	a.PROVINCE_ID,                  ");
            strSql.AppendLine("	a.CITY_ID,                      ");
            strSql.AppendLine("	a.TOWN_ID,                      ");
            strSql.AppendLine("	a.REMARK,                       ");
            strSql.AppendLine("	a.OPERATORID,                   ");
            strSql.AppendLine("	a.OPERATEDATE,                  ");
            strSql.AppendLine("	b.add_name PROVINCE_NAME,       ");
            strSql.AppendLine("	c.add_name CITY_NAME,           ");
            strSql.AppendLine("	d.add_name TOWN_NAME            ");
            strSql.AppendLine("FROM                             ");
            strSql.AppendLine("	T_BASE_STORAGE_ORDER a          ");
            strSql.AppendLine("	left join T_BASE_ADDRESSAREA b  ");
            strSql.AppendLine("	on a.PROVINCE_ID = b.add_id     ");
            strSql.AppendLine("	left join T_BASE_ADDRESSAREA c  ");
            strSql.AppendLine("	on a.CITY_ID = c.add_id         ");
            strSql.AppendLine("	left join T_BASE_ADDRESSAREA d  ");
            strSql.AppendLine("	on a.TOWN_ID = d.add_id         ");
            strSql.AppendLine("where 1=1                        ");
            if (!string.IsNullOrEmpty(model.PROVINCE_ID))
            {
                strSql.AppendLine(" and a.PROVINCE_ID = '" + model.PROVINCE_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.CITY_ID))
            {
                strSql.AppendLine(" and a.CITY_ID = '" + model.CITY_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.TOWN_ID))
            {
                strSql.AppendLine(" and a.TOWN_ID = '" + model.TOWN_ID + "' ");
            }
            if (!string.IsNullOrEmpty(model.STORAGE_ID))
            {
                strSql.AppendLine(" and a.STORAGE_ID = '" + model.STORAGE_ID + "' ");
            }
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 插入仓库订单范围单明细信息
        /// </summary>
        /// <param name="StorageOrderModel">仓库订单范围单明细Model</param>
        public void InsertStorageOrder(StorageOrderModel model)
        {
            StringBuilder strSql = new StringBuilder();

            //if (string.IsNullOrEmpty(model.STORAGE_ID))
            //{
            //    strSql.AppendLine("insert into T_BASE_STORAGE_ORDER(");
            //    strSql.AppendLine("STORAGE_ID,PROVINCE_ID,CITY_ID,TOWN_ID,REMARK,OPERATORID,OPERATEDATE)");
            //    strSql.AppendLine(" values (");
            //    strSql.AppendLine("'CK'||seq_BASE_STORAGEINFO.Currval,:PROVINCE_ID,:CITY_ID,:TOWN_ID,:REMARK,:OPERATORID,getdate())");
            //    OracleParameter[] parameters = {
            //       new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":TOWN_ID",OracleDbType.Varchar2),
            //       new OracleParameter(":REMARK",OracleDbType.Varchar2),
            //       new OracleParameter(":OPERATORID",OracleDbType.Varchar2)};
            //    parameters[0].Value = model.PROVINCE_ID;
            //    parameters[1].Value = model.CITY_ID;
            //    parameters[2].Value = model.TOWN_ID;
            //    parameters[3].Value = model.REMARK;
            //    parameters[4].Value = model.OPERATORID;

            //    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
            //}
            //else
            //{
                strSql.AppendLine("insert into T_BASE_STORAGE_ORDER(");
                strSql.AppendLine("STORAGE_ID,PROVINCE_ID,CITY_ID,TOWN_ID,REMARK,OPERATORID,OPERATEDATE)");
                strSql.AppendLine(" values (");
                strSql.AppendLine(":STORAGE_ID,:PROVINCE_ID,:CITY_ID,:TOWN_ID,:REMARK,:OPERATORID,getdate())");
                OracleParameter[] parameters = {
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
					new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
					new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
					new OracleParameter(":TOWN_ID",OracleDbType.Varchar2),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":OPERATORID",OracleDbType.Varchar2)};
                parameters[0].Value = model.STORAGE_ID;
                parameters[1].Value = model.PROVINCE_ID;
                parameters[2].Value = model.CITY_ID;
                parameters[3].Value = model.TOWN_ID;
                parameters[4].Value = model.REMARK;
                parameters[5].Value = model.OPERATORID;

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

        }

        /// <summary>
        /// 通过省市区编号删除仓库订单范围单明细
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteStorageOrderByAreaID(StorageOrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_BASE_STORAGE_ORDER ");
            strSql.AppendLine(" where PROVINCE_ID=:PROVINCE_ID and CITY_ID=:CITY_ID and TOWN_ID=:TOWN_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
					new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
					new OracleParameter(":TOWN_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = model.PROVINCE_ID;
            parameters[1].Value = model.CITY_ID;
            parameters[2].Value = model.TOWN_ID;

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

        /// <summary>
        /// 通过仓库编号删除仓库订单范围单明细
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteStorageOrderByStorageID(StorageOrderModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_BASE_STORAGE_ORDER ");
            strSql.AppendLine(" where STORAGE_ID=:STORAGE_ID");
            OracleParameter[] parameters = {
					new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)		};
            parameters[0].Value = model.STORAGE_ID;

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