using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ApServerProvider;
using System.Collections;
using System.Text;
using System.Data;
using DbCommon;
using Estsh.Web.Util;

/***************************************************************************************************
 * 
 * 作者：贾文涛
 * 创建时间：2014.07.17
 * 描述：成品发运（JIS）数据库访问类
 * 修改日志：
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理成品发运（JIS）的业务数据
    /// </summary>
    public class ShippingJISDal : BaseApp
    {
        public ShippingJISDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { 
        }

        #region 成品发运（JIS）

        /// <summary>
        /// 
        /// </summary>
        /// <param name="endOrderNo"></param>
        /// <param name="endSeq"></param>
        /// <returns></returns>
        public String getCarNobyOrderNo(string endOrderNo,string endSeq)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();
                String strSql = "SELECT TOP 1 car_no FROM dbo.vw_orders_info WHERE order_no = @orderNo AND slot_no = @slotNo ";

                if (endOrderNo.Length == 10)
                {
                    endOrderNo = "2200JIT027" + endOrderNo;
                }

                Hashtable values = new Hashtable(2);
                values.Add("@orderNo", endOrderNo);
                values.Add("@slotNo", endSeq);

                object objRes = _remotingProxy.GetScalar(strSql, values);
                if (null == objRes || objRes is DBNull)
                {
                    return "";
                }
                else
                {
                    return Convert.ToString(objRes);
                }
            }
        }

        /// <summary>
        /// 查询发运数据
        /// </summary>
        /// <param name="endCarNO"></param>
        /// <param name="htRes"></param>
        /// <returns></returns>
        public DataTable GetShippingJIS(string endCarNO, out Hashtable htRes)
        {
            lock (_remotingProxy)
            {
                htRes = new Hashtable();
                List<StoreProcedureParameter> spParas = new List<StoreProcedureParameter>();
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@endCarNO", endCarNO));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@endOrderNO", ""));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@shippingModel", "JIS"));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@startCarNO", 20));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@factoryCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingWarehouseCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@custCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingDate", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingFormNO", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retCode", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retMsg", 512));

                DataTable tmpDT = null;
                try
                {
                    tmpDT = _remotingProxy.ExecuteSotreProcedure("sys_shipping_data_query", spParas, ref htRes);
                }
                catch (System.Exception ex)
                {
                    htRes.Add("@retMsg", ex.Message);
                    return null;
                }
                if (!htRes.Contains("@retCode"))
                {
                    htRes.Add("@retMsg", "Unknown Error When Execute Store Procedure.");
                    return null;
                }

                if (htRes["@retCode"].ToString().ToUpper() == "PASSED")
                {
                    return tmpDT;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 取回排序单号
        /// </summary>
        /// <param name="startCarNO"></param>
        /// <returns></returns>
        public DataTable GetOrderNOViaCarNO(string startCarNO)
        {
            lock (_remotingProxy)
            {
                string querySen = " SELECT TOP 1 order_no,slot_no,car_no FROM dbo.vw_orders_info WHERE car_no = @carNO ";

                Hashtable htPara = new Hashtable();
                htPara.Add("@carNO", startCarNO);
                try
                {
                    DataTable tmpDT = _remotingProxy.GetDataTable(querySen, htPara);
                    return tmpDT;
                }
                catch (System.Exception ex)
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 查询车身件的订单
        /// </summary>
        /// <returns></returns>
        public DataTable QueryCSPartOrder()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT DISTINCT ");
                SqlStringBuilder.Append("        cpm.order_no AS [value], cpm.order_no AS [key] ");
                SqlStringBuilder.Append("FROM    dbo.g_cspart_master cpm ");
                SqlStringBuilder.Append("        JOIN dbo.g_cspart_detail cpd ON cpm.order_no = cpd.order_no ");
                SqlStringBuilder.Append("WHERE   1 = 1 ");
                SqlStringBuilder.Append("        AND cpm.type = 'LXJ' ");
                SqlStringBuilder.Append("        AND cpm.order_status = 'S' ");
                SqlStringBuilder.Append("ORDER BY cpm.order_no ");

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
        }

        public DataTable GetShippingCSJ(string orderNO, out Hashtable htRes)
        {
            lock (_remotingProxy)
            {
                htRes = new Hashtable();
                List<StoreProcedureParameter> spParas = new List<StoreProcedureParameter>();
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderNO", orderNO));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@factoryCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingWarehouseCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@custCode", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingDate", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@shippingFormNO", 50));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retCode", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retMsg", 512));

                DataTable tmpDT = null;
                try
                {
                    tmpDT = _remotingProxy.ExecuteSotreProcedure("sys_shipping_addition", spParas, ref htRes);
                }
                catch (System.Exception ex)
                {
                    htRes.Add("@retMsg", ex.Message);
                    return null;
                }
                if (!htRes.Contains("@retCode"))
                {
                    htRes.Add("@retMsg", "Unknown Error When Execute Store Procedure.");
                    return null;
                }
                if (htRes["@retCode"].ToString().ToUpper().StartsWith("PASSED"))
                {
                    return tmpDT;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 检查Factory Code, Warehouse Code, Customer Code Invalid or not.
        /// </summary>
        /// <param name="factoryCode"> Factory Code</param>
        /// <param name="warehouseCode">SAP warehouse Code.</param>
        /// <param name="custCode">Customer Code</param>
        /// <returns> -99:Query Exception.
        ///            0 : Code Invalid.
        ///            1 : Code Valid.
        /// </returns>
        public String CheckParamsValid(string factoryCode, string warehouseCode, string custCode)
        {
            lock (_remotingProxy)
            {
                string querySen = " SELECT TOP 1 factory_code From sys_factory WHERE factory_code = '" + factoryCode + "';";

                try
                {
                    object outFC = _remotingProxy.GetScalar(querySen);
                    if (outFC is DBNull || null == outFC)
                    {
                        return "[" + factoryCode + "] 为无效的工厂代码！";
                    }
                }
                catch (System.Exception ex)
                {
                    return "查询工厂代码异常！";
                }
                querySen = " SELECT TOP 1 erp_warehouse FROM dbo.sys_warehouse WHERE erp_warehouse = '" + warehouseCode + "';";
                try
                {
                    object outFC = _remotingProxy.GetScalar(querySen);
                    if (outFC is DBNull || null == outFC)
                    {
                        return "[" + warehouseCode + "] 为无效的ERP库存地代码！";
                    }
                }
                catch (System.Exception ex)
                {
                    return "查询ERP库存地异常！";
                }

                querySen = " SELECT TOP 1 customer_code FROM dbo.sys_customer WHERE customer_code =  '" + custCode + "';";
                try
                {
                    object outFC = _remotingProxy.GetScalar(querySen);
                    if (outFC is DBNull || null == outFC)
                    {
                        return "[" + custCode + "] 为无效的客户代码！";
                    }
                }
                catch (System.Exception ex)
                {
                    return "查询客户代码异常！";
                }
                return "PASSED";
            }
        }

        /// <summary>
        /// 类似存储过程的方式运行一批语句
        /// </summary>
        /// <param name="sqlSen"></param>
        /// <returns></returns>
        public Hashtable ExecMutilSentenceInBatch(string sqlSen, out DataTable resDT)
        {
            lock (_remotingProxy)
            {
                resDT = null;

                Hashtable htRes = new Hashtable();
                List<StoreProcedureParameter> spParas = new List<StoreProcedureParameter>();
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@tsqlSen", sqlSen));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retCode", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retMsg", 512));
                try
                {
                    resDT = _remotingProxy.ExecuteSotreProcedure("sys_exec_tsql_batch", spParas, ref htRes);
                }
                catch (System.Exception ex)
                {
                    htRes.Add("@retCode", "Fail");
                    htRes.Add("@retMsg", ex.Message);
                }
                return htRes;
            }
        }

        /// <summary>
        /// 更新车身件的状态(单号，旧状态，新状态，更新人ID = 登录人ID)
        /// </summary>
        /// <param name="orderNO">车身件发运时的单号</param>
        /// <param name="oldSta">旧状态</param>
        /// <param name="newSta">新状态</param>
        /// <returns></returns>
        public Hashtable UpdateCSJStatus(string orderNO, string oldSta, string newSta)
        {
            lock (_remotingProxy)
            {
                Hashtable htRes = new Hashtable();
                List<StoreProcedureParameter> spParas = new List<StoreProcedureParameter>();
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderNO", orderNO));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@oldSta", oldSta));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@newSta", newSta));
                spParas.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@userID", _remotingProxy.UserID));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retCode", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retMsg", 512));

                try
                {
                    _remotingProxy.ExecuteSotreProcedure("sys_shipping_updcs", spParas, ref htRes);
                }
                catch (System.Exception ex)
                {
                    htRes.Add("@retCode", "Fail");
                    htRes.Add("@retMsg", ex.Message);
                }
                if (!htRes.Contains("@retCode"))
                {
                    htRes.Add("@retCode", "Fail");
                    htRes.Add("@retMsg", "Unknown Error When Execute Store Procedure.");
                }

                if (!htRes.Contains("@retMsg"))
                {
                    htRes.Add("@retMsg", "Fail");
                }
                return htRes;
            }
        }

        /// <summary>
        /// 更新总成数据状态
        /// </summary>
        /// <param name="startCarNO"></param>
        /// <param name="endCarNO"></param>
        /// <param name="zcOldSta"></param>
        /// <param name="zcNewSta"></param>
        /// <returns></returns>
        public Hashtable UpdateZCStatus(string startCarNO, string endCarNO, string zcOldSta, string zcNewSta)
        {
            lock (_remotingProxy)
            {
                //return UpdateZCStatus(startCarNO, endCarNO, zcOldSta, zcOldSta, "JISUPD");
                Hashtable htRes = new Hashtable();
                /*
                    CREATE PROCEDURE [dbo].[sys_upd_shippingdata]
                     @startCarNO VARCHAR(50) = ''	--- 开始车号
                    ,@endCarNO VARCHAR(50)	= ''	---	结束车号		
                    ,@oldSta VARCHAR(10)	= ''	-- 只将状态为 oldSta 的更新为newSta
                    ,@newSta VARCHAR(10)	= ''	--	更新为newSta		
                    ,@action VARCHAR(512)	= ''	--	执行的指令		
                    ,@userID INT			= 0		--	
                    ,@retCode VARCHAR(10) OUTPUT	-- 成功执行返回PASSED, 错误返回FAILED
                    ,@retMsg VARCHAR(512) OUTPUT	-- 执行的结果信息
                 */
                List<StoreProcedureParameter> spParas = new List<StoreProcedureParameter>();
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@startCarNO", startCarNO));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@endCarNO", endCarNO));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@oldSta", zcOldSta));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@newSta", zcNewSta));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@action", "JISUPD"));
                spParas.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@userID", _remotingProxy.UserID));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retCode", 10));
                spParas.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@retMsg", 512));

                try
                {
                    _remotingProxy.ExecuteSotreProcedure("sys_upd_shippingdata", spParas, ref htRes);
                }
                catch (System.Exception ex)
                {
                    htRes.Add("@retCode", "Fail");
                    htRes.Add("@retMsg", ex.Message);
                }
                if (!htRes.Contains("@retCode"))
                {
                    htRes.Add("@retCode", "Fail");
                    htRes.Add("@retMsg", "Unknown Error When Execute Store Procedure.");
                }

                if (!htRes.Contains("@retMsg"))
                {
                    htRes.Add("@retMsg", "Fail");
                }
                return htRes;
            }
        }
        #endregion
    }
}