Estsh-Yiri/Estsh.Core.Services/Repositories/OffLine.cs

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

namespace Estsh.Core.Repositories
{
    public class OffLine:BaseApp
    {
        /***************************************************************************************************
         * 
         * 作者：张茂忠
         * 创建时间：2013.04.24
         * 描述：下线回冲查询模块Dal层
         * 修改日志：
         * 
         * 
         * *************************************************************************************************/
        public OffLine(RemotingProxy _remotingproxy)
            : base(_remotingproxy)
        { }

        /// <summary>
        /// 根据查询条件得出结果集
        /// </summary>
        /// <param name="aWhere">查询条件</param>
        /// <returns>符合条件的结果集</returns>
        public DataTable GetQuery(string aWhere, string partno, string carno)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT TOP 1000 ");
                SqlStringBuilder.Append("        a.* , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        d.part_spec AS spec, ");
                SqlStringBuilder.Append("        e.enum_desc  ");
                SqlStringBuilder.Append("FROM    g_move_trans a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_pdline b ON a.pdline_name = b.pdline_name ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_no = c.part_no ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part d ON a.item_part_no = d.part_no ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum e ON c.part_location =e.enum_value AND e.enum_type='sys_part_location' ");
                SqlStringBuilder.Append("WHERE   a.enabled = 'Y' and a.part_no=@part_no and a.car_no=@car_no");


                if (!string.IsNullOrEmpty(aWhere))
                {
                    SqlStringBuilder.Append(aWhere);
                }

                Hashtable values = new Hashtable(1);

                values.Add("@part_no", partno);
                values.Add("@car_no", carno);

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

        /// <summary>
        /// 根据车号获取总成信息
        /// </summary>
        /// <param name="carno">车号</param>
        /// <returns></returns>
        public DataTable GetCount(ref Pager pager, string carno, string typename)
        {
            lock (_remotingProxy)
            {
                StringBuilder strColumn = new StringBuilder();
                strColumn.Append(" b.part_id , ");
                strColumn.Append(" b.part_no , ");
                strColumn.Append(" b.part_spec , ");
                strColumn.Append(" c.enum_desc , ");
                strColumn.Append(" a.ruid , ");
                strColumn.Append(" d.serial_number , ");
                strColumn.Append(" e.car_no ");

                StringBuilder strTable = new StringBuilder();
                strTable.Append(" dbo.g_workorder_detail (NOLOCK) a ");
                strTable.Append(" LEFT JOIN dbo.sys_part (NOLOCK) b ON a.part_id = b.part_id ");
                strTable.Append(" LEFT JOIN dbo.sys_enum (NOLOCK) c ON b.part_location = c.enum_value ");
                strTable.Append(" AND c.enum_type = 'sys_part_location' ");
                strTable.Append(" LEFT JOIN dbo.g_workorder_sn (NOLOCK) d ON a.ruid = d.wo_detail_id ");
                strTable.Append(" LEFT JOIN g_workorder e ON a.workorder_id = e.ruid ");

                StringBuilder strWhere = new StringBuilder();

                strWhere.Append(" a.workorder_id = ( SELECT TOP 1 ");
                strWhere.Append(" ruid ");
                strWhere.Append(" FROM     dbo.g_workorder (NOLOCK) a , ");
                strWhere.Append(" dbo.sys_part (NOLOCK) b , ");
                strWhere.Append(" dbo.sys_model (NOLOCK) c , ");
                strWhere.Append(" dbo.sys_model_type (NOLOCK) d ");
                strWhere.Append(" WHERE    a.part_id = b.part_id ");
                strWhere.Append(" AND b.model_id = c.model_id ");
                strWhere.Append(" AND c.model_type_id = d.type_id ");
                strWhere.AppendFormat(" AND d.type_name = '{0}' ", typename);
                strWhere.AppendFormat(" AND a.car_no = '{0}' ", carno);
                strWhere.Append(" ORDER BY ruid DESC ");
                strWhere.Append(" ) ");

                StringBuilder strOrder = new StringBuilder();
                strOrder.Append(" a.ruid , ");
                strOrder.Append(" d.serial_number , ");
                strOrder.Append(" e.car_no ");

                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", strTable.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", strColumn.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", strOrder.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));

                Hashtable values = new Hashtable(2);

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

                //totalCount = Convert.ToInt32(values["@TotalCount"]);
                pager.totalRows = Convert.ToInt32(values["@TotalCount"]);
                return dt;


                //StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.Append("SELECT  b.part_id , ");
                //SqlStringBuilder.Append("        b.part_no , ");
                //SqlStringBuilder.Append("        b.part_spec , ");
                //SqlStringBuilder.Append("        c.enum_desc , ");
                //SqlStringBuilder.Append("        a.ruid , ");
                //SqlStringBuilder.Append("        d.serial_number , ");
                //SqlStringBuilder.Append("        e.car_no ");
                //SqlStringBuilder.Append("FROM    dbo.g_workorder_detail (NOLOCK) a ");
                //SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part (NOLOCK) b ON a.part_id = b.part_id ");
                //SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum (NOLOCK) c ON b.part_location = c.enum_value ");
                //SqlStringBuilder.Append("                                             AND c.enum_type = 'sys_part_location' ");
                //SqlStringBuilder.Append("        LEFT JOIN dbo.g_workorder_sn (NOLOCK) d ON a.ruid = d.wo_detail_id ");
                //SqlStringBuilder.Append("        LEFT JOIN g_workorder e ON a.workorder_id = e.ruid ");
                //SqlStringBuilder.Append("WHERE   a.workorder_id = ( SELECT TOP 1 ");
                //SqlStringBuilder.Append("                                    ruid ");
                //SqlStringBuilder.Append("                           FROM     dbo.g_workorder (NOLOCK) a , ");
                //SqlStringBuilder.Append("                                    dbo.sys_part (NOLOCK) b , ");
                //SqlStringBuilder.Append("                                    dbo.sys_model (NOLOCK) c , ");
                //SqlStringBuilder.Append("                                    dbo.sys_model_type (NOLOCK) d ");
                //SqlStringBuilder.Append("                           WHERE    a.part_id = b.part_id ");
                //SqlStringBuilder.Append("                                    AND b.model_id = c.model_id ");
                //SqlStringBuilder.Append("                                    AND c.model_type_id = d.type_id ");
                //SqlStringBuilder.Append("                                    AND d.type_name = @typename ");
                //SqlStringBuilder.Append("                                    AND a.car_no = @carno ");
                //SqlStringBuilder.Append("                           ORDER BY ruid DESC ");
                //SqlStringBuilder.Append("                         ) ");


                //Hashtable values = new Hashtable(1);

                //values.Add("@carno", carno);
                //values.Add("@typename", typename);
                //dt=_remotingProxy.GetDataTable(SqlStringBuilder.ToString(), values);
                //return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }

        }

        /// <summary>
        /// 判断数据是否存在
        /// </summary>
        /// <param name="aPDLineName">客户产线名称</param>
        /// <returns>true or false</returns>
        public bool GetCustPDLineName(string aPDLineName)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select * from sys_cust_pdline where cust_pdline_name=@cust_pdline_name ");

                Hashtable values = new Hashtable();

                values.Add("@cust_pdline_name", aPDLineName);

                return _remotingProxy.GetScalar(SqlStringBuilder.ToString(), values) != null;
            }
        }

        public DataTable GetCustomerID()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("select * from sys_customer");


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


        public DataTable GetTrayID()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append(" select * from g_tray_type ");

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

        /// <summary>
        ///更改可用状态
        /// </summary>
        /// <param name="status">true or false</param>
        /// <param name="dr">当前行</param>
        /// <returns></returns>
        public bool ChangeDetailStatus(bool status, DataRow dr)
        {
            lock (_remotingProxy)
            {
                string guid = dr["cust_pdline_id"].ToString();

                string enabled = string.Empty;

                //string partID = GetPartMessage(partNo).Rows[0][0].ToString();

                string SqlString = "update dbo.sys_cust_pdline set enabled=@status where cust_pdline_id=@part_id";

                if (status)
                {
                    enabled = "Y";
                }
                else
                {
                    enabled = "N";
                }

                Hashtable values = new Hashtable();

                values.Add("@status", enabled);
                values.Add("@part_id", guid);

                return _remotingProxy.ExecuteNonQuery(SqlString, values) > 0;
            }
        }

        public bool DeleteDetail(DataRow dr)
        {
            lock (_remotingProxy)
            {
                //string partID = GetPartMessage(dr[0].ToString()).Rows[0][0].ToString();

                string guid = dr["cust_pdline_id"].ToString();

                string SqlString = "delete from sys_cust_pdline where cust_pdline_id = @guid";

                Hashtable values = new Hashtable();

                values.Add("@guid", guid);

                return _remotingProxy.ExecuteNonQuery(SqlString, values) > 0;
            }
        }

        /// <summary>
        /// 获取车型名称
        /// </summary>
        /// <returns></returns>
        public ArrayList GetModelType()
        {
            lock (_remotingProxy)
            {
                string SqlString = "select type_name AS [value],type_name+':'+type_desc AS [key] from sys_model_type";

                return DataTypeConvert.NewObject.DataTableToArrayList(_remotingProxy.GetDataTable(SqlString));
            }
        }

        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="woDetailID"></param>
        /// <param name="carNo">车号</param>
        /// <param name="SerialNumber">条码</param>
        /// <param name="PartID">零件编号</param>
        /// <returns></returns>
        public DataTable GetDataSourse(string woDetailID, string carNo, string SerialNumber, string PartID)
        {
            lock (_remotingProxy)
            {
                Hashtable ht = new Hashtable();

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

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@wo_detail_id", woDetailID));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@car_no", carNo));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@serial_number", SerialNumber));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@part_id", PartID));
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("dbo.data_mend_off_line", Parameters, ref ht);
                return dt;
            }
        }

        public string isGetVirtualData()
        {
            lock (_remotingProxy)
            {
                object obj = _remotingProxy.GetScalar("SELECT a.param_value FROM sys_base a  where a.param_name='getvirtualData' AND a.enabled='Y'");
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                else
                {
                    return null;
                }
            }
        }

        public DataTable GetQuery(ref Pager pager, string part_no, string car_no)
        {
            lock (_remotingProxy)
            {
                StringBuilder strColumn = new StringBuilder();
                strColumn.Append(" a.* , ");
                strColumn.Append(" c.part_spec , ");
                strColumn.Append(" d.part_spec AS spec, ");
                strColumn.Append(" e.enum_desc  ");

                StringBuilder strTable = new StringBuilder();
                strTable.Append(" g_move_trans a ");
                strTable.Append(" LEFT JOIN dbo.sys_pdline b ON a.pdline_name = b.pdline_name ");
                strTable.Append(" LEFT JOIN dbo.sys_part c ON a.part_no = c.part_no ");
                strTable.Append(" LEFT JOIN dbo.sys_part d ON a.item_part_no = d.part_no ");
                strTable.Append(" LEFT JOIN sys_enum e ON c.part_location =e.enum_value AND e.enum_type='sys_part_location' ");

                StringBuilder strWhere = new StringBuilder();
                strWhere.AppendFormat("a.enabled = 'Y' and a.part_no='{0}' and a.car_no='{1}'", part_no, car_no);

                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", strTable.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", strColumn.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.create_ymd,a.create_hms"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));

                Hashtable values = new Hashtable(2);

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

                //totalCount = Convert.ToInt32(values["@TotalCount"]);
                pager.totalRows = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        public DataTable GetQueryHistory(ref Pager pager, string part_no, string car_no)
        {
            lock (_remotingProxy)
            {
                StringBuilder strColumn = new StringBuilder();
                strColumn.Append(" a.* , ");
                strColumn.Append(" c.part_spec , ");
                strColumn.Append(" d.part_spec AS spec, ");
                strColumn.Append(" e.enum_desc  ");

                StringBuilder strTable = new StringBuilder();
                strTable.Append(" g_ht_move_trans a ");
                strTable.Append(" LEFT JOIN dbo.sys_pdline b ON a.pdline_name = b.pdline_name ");
                strTable.Append(" LEFT JOIN dbo.sys_part c ON a.part_no = c.part_no ");
                strTable.Append(" LEFT JOIN dbo.sys_part d ON a.item_part_no = d.part_no ");
                strTable.Append(" LEFT JOIN sys_enum e ON c.part_location =e.enum_value AND e.enum_type='sys_part_location' ");

                StringBuilder strWhere = new StringBuilder();
                strWhere.AppendFormat("a.enabled = 'Y' and a.part_no='{0}' and a.car_no='{1}'", part_no, car_no);

                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", strTable.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", strColumn.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.create_ymd,a.create_hms"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere.ToString()));

                Hashtable values = new Hashtable(2);

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

                //totalCount = Convert.ToInt32(values["@TotalCount"]);
                pager.totalRows = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }
    }
}