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

namespace Estsh.Core.Repositories
{
    public class XTWorkplaceDefineDal : BaseApp
    {
        public XTWorkplaceDefineDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }


        /// <summary>
        /// 获取配置名称
        /// </summary>
        /// <returns></returns>
        public ArrayList GetTearmialName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select terminal_name as [value],terminal_name as [key] from dbo.sys_terminal where enabled='Y' ");

                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }

        public DataTable getList(string strWhere, string filedOrder)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("		SELECT a.*,b.model_name FROM dbo.g_edi_model_relationship AS a  ");
                SqlStringBuilder.Append("		LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id ");

                if (!strWhere.Trim().Equals(""))
                {
                    SqlStringBuilder.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    SqlStringBuilder.Append(" order by " + filedOrder);
                }
                return this._remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
        }

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getTerminalListByPage(int PageSize, int PageIndex, int TerminalID, string OrderBy)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT TOP 15   ");
                SqlStringBuilder.Append("                         a.ruid ,   ");
                SqlStringBuilder.Append("                         l.seq ,   ");
                SqlStringBuilder.Append("                         b.car_no ,   ");
                SqlStringBuilder.Append("                         h.type_id ,   ");
                SqlStringBuilder.Append("                         h.type_code ,   ");
                SqlStringBuilder.Append("                         h.type_name ,   ");
                SqlStringBuilder.Append("                         g.model_id ,   ");
                SqlStringBuilder.Append("                         g.model_name ,   ");
                SqlStringBuilder.Append("                         g.model_desc ,   ");
                SqlStringBuilder.Append("                         i.enum_desc ,   ");
                SqlStringBuilder.Append("                         d.part_spec ,   ");
                SqlStringBuilder.Append("                         a.part_id ,   ");
                SqlStringBuilder.Append("                         b.type AS wo_type ,   ");
                SqlStringBuilder.Append("                         CASE a.type  WHEN 'S' THEN '订单生产'  WHEN 'E' THEN '插单生产'  WHEN 'Z' THEN '试制生产' ELSE '其他' END  AS  wo_detail_type,   ");
                SqlStringBuilder.Append("                         c.serial_number ,   ");
                SqlStringBuilder.Append("                         j.csn ,   ");
                SqlStringBuilder.Append("                         a.create_ymd + ' ' + a.create_hms AS ctime ,   ");
                SqlStringBuilder.Append("                         d.part_no ,   ");
                SqlStringBuilder.Append("                         a.ruid AS wo_detail_id ,   ");
                SqlStringBuilder.Append("                         0 AS row_select ,   ");
                SqlStringBuilder.Append("                         d.option10 ,   ");
                SqlStringBuilder.Append("                         d.part_location,   ");
                SqlStringBuilder.Append("                         RIGHT(c.serial_number,10) AS number ");
                SqlStringBuilder.Append("                 FROM    dbo.g_workorder_detail a   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.sys_model g ON j.model_id = g.model_id   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location'   ");
                SqlStringBuilder.Append("                                                     AND i.enum_value = d.part_location   ");
                SqlStringBuilder.Append("                         LEFT JOIN dbo.g_stock_order AS k ON k.order_no = b.order_no ");
                SqlStringBuilder.Append("						 LEFT JOIN dbo.g_workorder_seq AS l ON a.ruid=l.workorder_id   ");
                SqlStringBuilder.Append("                 WHERE   RIGHT('00' + d.part_location, 2) IN (   ");
                SqlStringBuilder.Append("                         SELECT  RIGHT('00' + col, 2)   ");
                SqlStringBuilder.Append("                         FROM    dbo.f_split(( SELECT    ISNULL(view_part_location, '01,02')   ");
                SqlStringBuilder.Append("                                               FROM      dbo.sys_terminal_control   ");
                SqlStringBuilder.Append("                                               WHERE     terminal_id = @terminal_id   ");
                SqlStringBuilder.Append("                                             ), ',') )   ");
                SqlStringBuilder.Append("                         AND l.seq > ( SELECT    wo_detail_seq   ");
                SqlStringBuilder.Append("                                       FROM      dbo.sys_terminal_point   ");
                SqlStringBuilder.Append("                                       WHERE     terminal_id = @terminal_id   ");
                SqlStringBuilder.Append("                                     )   ");
                SqlStringBuilder.Append("                         AND j.current_status NOT IN ('9','1','8')    ");
                SqlStringBuilder.Append("                         AND j.work_flag <> 1   ");
                SqlStringBuilder.Append("                         AND a.enabled = 'Y'  ");
                SqlStringBuilder.Append("						 AND l.stage_id=(SELECT stage_id FROM dbo.sys_terminal WHERE terminal_id=@terminal_id)  ");
                SqlStringBuilder.Append("                 ORDER BY l.seq    ");

                //Hashtable Params = new Hashtable(1);
                //Params.Add("@terminal_id ", TerminalID);
                //DataTable dt = new DataTable();
                //dt= _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), Params);
                //return dt;
                Hashtable values = new Hashtable(1);
                values.Add("@terminal_id", TerminalID);
                DataTable dt = new DataTable();
                dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", dt.Rows.Count);

                return result;

            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveModelType(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT  INTO dbo.g_edi_model_relationship ");
                SqlStringBuilder.Append("        ( model_id, edi_model_name ) ");
                SqlStringBuilder.Append("VALUES  ( @model_id, @edi_model_name ) ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateModelType(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE  dbo.g_edi_model_relationship ");
                SqlStringBuilder.Append("SET     model_id = @model_id , ");
                SqlStringBuilder.Append("        edi_model_name = @edi_model_name  ");
                SqlStringBuilder.Append("WHERE   ruid = @ruid ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="type_id"></param>
        /// <returns></returns>
        public int deleteModelType(String ruid)
        {
            lock (_remotingProxy)
            {
                String delStr = "DELETE dbo.g_edi_model_relationship WHERE ruid=@ruid";
                Hashtable htparams = new Hashtable();
                htparams.Add("@ruid", ruid);
                return _remotingProxy.ExecuteNonQuery(delStr, htparams);
            }
        }

        public int GetModelID(string model_name)
        {
            lock (_remotingProxy)
            {
                string SqlString = "select model_id from dbo.sys_model where model_name=@model_name";

                Hashtable Params = new Hashtable(1);

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

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj != null ? Convert.ToInt32(obj) : 0;
            }
        }

        /// <summary>
        /// 根据帐号名称查找对应编号
        /// </summary>
        /// <param name="custPDLineDesc">客户产线描述</param>
        /// <returns>客户产线编号</returns>
        public int GetTermianlId(string terminal_name)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT terminal_id FROM dbo.sys_terminal WHERE terminal_name=@terminal_name";

                Hashtable Params = new Hashtable(1);

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

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj != null ? Convert.ToInt32(obj) : 0;
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表(导出时使用)
        /// </summary>
        public DataTable getShippingExport(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            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", @" g_edi_model_relationship AS a 
                LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a.ruid,b.model_name,a.edi_model_name "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("desc", "")));
                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", strWhere));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                return dt;
            }
        }
    }
}