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;

/***************************************************************************************************
 * 
 * 作者：王勇
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 目视单打印模块的业务数据
    /// </summary>
    public class OrderListDal : BaseApp
    {
        public OrderListDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
        }

        public Hashtable getStockOrderListByPage(int PageSize, int PageIndex, string str)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();

                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", " (SELECT *,ROW_NUMBER() OVER (partition BY ebeln,ZDATE order BY ebeln,ZDATE)AS group_idx  from dbo.SAP_DSPO )AS a LEFT JOIN dbo.sys_part AS b ON a.MATNR=b.part_no LEFT JOIN dbo.sys_vendor AS c ON a.LIFNR=c.vendor_code  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " EBELN, WERKS,LIFNR,vendor_name,'' AS PART_QTY,(SELECT  [dbo].[sap2wms_ymd](zdate))+' '+(SELECT  [dbo].[sap2wms_hms](zcjsj)) AS [TIME],(CASE STAFLG WHEN '1' THEN '已发布' WHEN '2' THEN '已确认' WHEN '3' THEN '已备货' WHEN '4' THEN '已发货' WHEN '5' THEN '已收货' ELSE '' END) as STAFLG,LGORT,COMMENTS "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.ebeln  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", str + " and a.group_idx=1  "));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", values["@TotalCount"].ToString());

                return result;
            }
        }

        public Hashtable getStockOrderDetailByPage(int PageSize, int PageIndex,string str)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();

                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", " dbo.SAP_DSPO AS a LEFT JOIN dbo.sys_part AS b ON a.matnr=b.part_no   "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a.WERKS,b.part_no,b.part_spec,a.ZDEV_NUM,b.default_box_qty "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.evrtp "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", str));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", values["@TotalCount"].ToString());

                return result;
            }
        }



        /// <summary>
        /// 获取工厂
        /// </summary>
        /// <returns></returns>
        public DataTable GetFactory()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT factory_code as [value],factory_name as [key] FROM dbo.sys_factory ");

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

        /// <summary>
        /// 获取供应商
        /// </summary>
        /// <returns></returns>
        public DataTable GetVendor()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT vendor_code as [value],vendor_name as [key] FROM dbo.sys_vendor ");

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


        public DataTable updatePrintData(string ebeln)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                string SqlStrings = "";
                Hashtable Parameters = new Hashtable();

                Hashtable Params = new Hashtable(1);

                SqlStrings = " SELECT * FROM dbo.SAP_DSPO WHERE ebeln='" + ebeln + "' ORDER BY evrtp ";
                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.GetDataTable(SqlStrings, Parameters);
                //ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                //result.Add("dataList", dataList);

                return dt;
            }
        }

        /// <summary>
        /// 订单确认
        /// </summary>
        /// <returns></returns>
        public bool dingDanQueRen(string ebeln)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET STAFLG=2 WHERE EBELN IN (" + ebeln + ") and STAFLG=1 ");

                int count = _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString());

                bool bool_1 = false;
                if (count > 0)
                {
                    bool_1 = true;
                }

                return bool_1;
            }
        }

        /// <summary>
        /// 发运确认
        /// </summary>
        /// <returns></returns>
        public bool shipQueRen(string ebeln)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET STAFLG=4 WHERE EBELN IN (" + ebeln + ") and STAFLG=3 ");

                int count = _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString());

                bool bool_1 = false;
                if (count > 0)
                {
                    bool_1 = true;
                }

                return bool_1;
            }
        }

        public bool deleteOrder(String ebeln)
        {
            lock (_remotingProxy)
            {
                List<string> SqlStrings = new List<string>(3);
                List<Hashtable> Parameters = new List<Hashtable>(3);

                Hashtable Params = new Hashtable(1);

                Params.Add("@ebeln", ebeln);

                SqlStrings.Add(" DELETE dbo.WMS_DSPOD WHERE ebeln in (" + ebeln + " )");
                Parameters.Add(Params);

                SqlStrings.Add(" DELETE dbo.SAP_DSPO WHERE ebeln in (" + ebeln + ") ");
                Parameters.Add(Params);

                return _remotingProxy.ExecuteSqlTransaction(SqlStrings, Parameters);
            }
        }
    }
}