using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using ApServerProvider;
using DbCommon;
using System.Collections;

namespace Estsh.Core.Repositories
{

    /**
     * 客户零件号关系管理
     * 
     * Fred
     * 
     */
    public class SysPartExtGroupDal : BaseApp
	{
        public SysPartExtGroupDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { 
        }
		#region  成员方法
		/// <summary>
		/// 获得菜单列表数据
		/// </summary>
		public DataTable getList(string strWhere,string filedOrder)
		{
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT a.ruid,a.part_id,b.part_no,b.part_spec,a.torque_job_no,a.torque_robot_no,a.robot_offline_no FROM dbo.sys_part_ext_group a LEFT JOIN dbo.sys_part b ON a.part_id =b.part_id");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                return this._remotingProxy.GetDataTable(strSql.ToString());
            }
		}

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public DataTable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                if (strWhere.Trim() != "")
                {
                    strWhere = strWhere.Remove(0, 6);//因为存储过程中已经有where，这里需要把之前加入的where去掉，这里截除前六个字符
                }
                if (OrderBy.Trim() != "")
                {
                    OrderBy = " " + OrderBy;  //排序
                }
                Hashtable Params = new Hashtable(6);

                Params.Add("@TotalCount", 100);
                Params.Add("@TotalPage", 100);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", "dbo.sys_part_ext_group a LEFT JOIN dbo.sys_part b ON a.part_id =b.part_id");
                Params.Add("@Column", "a.ruid,a.part_id,b.part_no,b.part_spec,a.torque_job_no,a.torque_robot_no,a.robot_offline_no");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

                String strProcedure = "EXEC Com_Pagination @TotalCount, @TotalPage, @Table, @Column,@OrderColumn,@GroupColumn,@PageSize, @CurrentPage,@Group,@Condition";
                return _remotingProxy.GetDataTable(strProcedure, Params);
            }
        }

        /// <summary>
        /// 获取分页记录总数
        /// </summary>
        public int getCountWhere(string strWhere)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(*) as Total FROM dbo.sys_part_ext_group a LEFT JOIN dbo.sys_part b ON a.part_id =b.part_id");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" " + strWhere);
                }
                int i = 0;
                DataTable dt = _remotingProxy.GetDataTable(strSql.ToString());
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Total"].ToString() != "")
                    {
                        i = int.Parse(dt.Rows[0]["Total"].ToString());
                    }
                }
                return i;
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveData(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_part_ext_group(part_id,torque_job_no,torque_robot_no,robot_offline_no,create_userid) ");
                SqlStringBuilder.Append("VALUES(@part_id,@torque_job_no,@torque_robot_no,@robot_offline_no,@create_userid) ");

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

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateData(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE dbo.sys_part_ext_group SET  ");
                SqlStringBuilder.Append("torque_job_no=@torque_job_no,torque_robot_no=@torque_robot_no,robot_offline_no=@robot_offline_no,update_userid=@update_userid,update_ymd=Convert(varchar(10),getdate(),23),update_hms=Convert(varchar(10),getdate(),108) ");
                SqlStringBuilder.Append("WHERE part_id=@part_id ");

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

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

		#endregion  成员方法






        /// <summary>
        /// 获取 站点 信息
        /// BY NOAH
        /// </summary>
        /// <returns></returns>
        public DataTable getTerminalName()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT DISTINCT keydata_id as [value],terminal_name as [key] from sys_terminal WHERE keydata_id IN (SELECT keydata_id FROM dbo.sys_terminal EXCEPT SELECT keydata_id FROM dbo.sys_terminal_control) and enabled = 'Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }
        /// <summary>
        /// 获取 看板 信息
        /// BY NOAH
        /// </summary>
        /// <returns></returns>
        public DataTable getBoardName()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT DISTINCT view_board_id as [value],view_board_name as [key] from g_view_board WHERE enabled = 'Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }
        /// <summary>
        /// 判断用户输入的零件号是否存在
        /// BY NOAH
        /// </summary>
        /// <param name="part_no"></param>
        /// <returns></returns>
        public String isExsitPart_no(String part_no)
        {
            lock (_remotingProxy)
            {
                string part_id = null;
                string SqlString = "select top 1 part_id from sys_part where enabled='Y' and part_no=@part_no";
                Hashtable Params = new Hashtable(1);
                Params.Add("@part_no", part_no);
                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);
                if (dt.Rows.Count < 1)
                {
                    return part_id;
                }
                else
                {
                    part_id = dt.Rows[0][0].ToString();
                    return part_id;
                }
            }
        }

        /// <summary>
        /// 导入数据方法
        /// </summary>
        /// <param name="dt">需要导入的数据表</param>
        /// <returns>true or false</returns>
        public bool InsertData(DataTable dt)
        {
            lock (_remotingProxy)
            {
                List<String> SqlStrings = new List<string>(dt.Rows.Count);

                List<Hashtable> hs = new List<Hashtable>(dt.Rows.Count);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string partNo = dt.Rows[i][0].ToString().Trim();
                    string torque_job_no = dt.Rows[i][2].ToString().Trim();
                    string torque_robot_no = dt.Rows[i][3].ToString().Trim();
                    string robot_offline_no = dt.Rows[i][4].ToString().Trim();

                    int partID = GetPartID(partNo);

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);

                    DataTable existInfo = checkExist(partID);
                    if (existInfo.Rows.Count >= 1)
                    {
                        SqlStringBuilder.Append("UPDATE dbo.sys_part_ext_group SET torque_job_no=@torque_job_no ,torque_robot_no=@torque_robot_no, ");
                        SqlStringBuilder.Append("robot_offline_no=@robot_offline_no,update_userid=@emp_id, ");
                        SqlStringBuilder.Append("update_ymd=Convert(varchar(10),getdate(),23),update_hms=Convert(varchar(10),getdate(),108) ");
                        SqlStringBuilder.Append("WHERE part_id=@part_id");
                    }
                    else
                    {
                        SqlStringBuilder.Append("INSERT INTO sys_part_ext_group(part_id,torque_job_no,torque_robot_no,robot_offline_no,create_userid) ");
                        SqlStringBuilder.Append("VALUES(@part_id,@torque_job_no,@torque_robot_no,@robot_offline_no,@emp_id) ");
                    }

                    Hashtable values = new Hashtable(4);

                    values.Add("@emp_id", _remotingProxy.UserID);
                    values.Add("@part_id", partID);
                    values.Add("@torque_job_no", torque_job_no);
                    values.Add("@torque_robot_no", torque_robot_no);
                    values.Add("@robot_offline_no", robot_offline_no);

                    SqlStrings.Add(SqlStringBuilder.ToString());

                    hs.Add(values);
                }

                return _remotingProxy.ExecuteSqlTransaction(SqlStrings, hs);

            }
        }

        /// <summary>
        /// 获取配置ID
        /// </summary>
        /// <param name="modelName"></param>
        /// <returns></returns>
        public int GetModelID(string modelName)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT model_id FROM dbo.sys_model WHERE model_name=@modelName";

                Hashtable Params = new Hashtable(1);

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

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
        }

        /// <summary>
        /// 获取总成零件ID
        /// </summary>
        /// <param name="modelName"></param>
        /// <returns></returns>
        public int GetPartID(string partNo)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT part_id FROM sys_part WHERE part_no=@partNo";

                Hashtable Params = new Hashtable(1);

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

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
        }

        /// <summary>
        /// 查看信息是否存在
        /// </summary>
        /// <param name="modelID"></param>
        /// <param name="partID"></param>
        /// <param name="custPartNo"></param>
        /// <returns></returns>
        public DataTable checkExist(int partID)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT * FROM sys_part_ext_group WHERE part_id=@partID");

                Hashtable ht = new Hashtable(1);

                ht.Add("@partID", partID);

                return _remotingProxy.GetDataTable(strSql.ToString(),ht);
            }
        }
	}
}