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

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

namespace Estsh.Core.Repositories
{
    /***************************************************************************************************
* 
* 作者：任华松
* 创建时间：2013.07.08
* 描述：产品下线未发运查询DAL层
* 修改日志：
* 
* 
* *************************************************************************************************/
    public class XTReWorkQueryDal : BaseApp
    {
        public XTReWorkQueryDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
            this.TABLE_NAME = "";
            this.PK_NAME = "";
        }

        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable XTReWorkData(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("SELECT aaa.type_name, aaa.model_name,aaa.stage_name,aaa.defect_desc,ncqty,qty,qty+ncqty AS couqty,CONCAT(round(cast(isnull(cast(qty*1.0/(qty+ncqty) as decimal(10,2)),0)*100 as int),2),'%') AS  per ");
                sb.Append("FROM ( SELECT type_name,model_name,stage_name,defect_desc,ncqty FROM  ( ");
                sb.Append("SELECT t.type_name, f.model_name,e.stage_name,a.defect_desc,COUNT(b.defect_id) AS  ncqty  ");
                sb.Append("FROM sys_defect  a ");
                sb.Append("LEFT JOIN dbo.g_sn_defect b ON a.defect_id=b.defect_id ");
                sb.Append("LEFT JOIN dbo.g_sn_status c ON b.serial_number=c.serial_number ");
                sb.Append("LEFT JOIN dbo.sys_terminal d ON b.terminal_id=d.terminal_id ");
                sb.Append("LEFT JOIN dbo.sys_stage e ON d.stage_id=e.stage_id ");
                sb.Append("LEFT JOIN dbo.sys_model f ON c.model_id=f.model_id ");
                sb.Append("LEFT JOIN dbo.sys_model_type t ON t.type_id=f.model_type_id ");
                sb.Append("WHERE 1=1 " + aWhere + " ");
                sb.Append("GROUP BY t.type_name, f.model_name,e.stage_name, a.defect_desc ) AS aa )aaa LEFT JOIN ( ");
                sb.Append("SELECT t.type_name, f.model_name,e.stage_name,COUNT(1) AS  qty ");
                sb.Append("FROM  dbo.g_out_pdline  b ");
                sb.Append("INNER JOIN dbo.g_sn_status i ON b.serial_number=i.serial_number ");
                sb.Append("LEFT JOIN dbo.sys_terminal j ON i.terminal_id=j.terminal_id ");
                sb.Append("LEFT JOIN dbo.sys_stage e ON e.stage_id=j.stage_id ");
                sb.Append("LEFT JOIN dbo.sys_model f ON f.model_id=i.model_id ");
                sb.Append("LEFT JOIN dbo.sys_model_type t ON t.type_id=f.model_type_id ");
                sb.Append("WHERE 1=1 " + aWhere + " ");
                sb.Append("GROUP BY  t.type_name,f.model_name,e.stage_name ) bbb ON  bbb.type_name = aaa.type_name AND bbb.model_name = aaa.model_name AND aaa.stage_name=bbb.stage_name   ORDER BY aaa.type_name ,  aaa.model_name ,aaa.stage_name  ");


                Hashtable ht = new Hashtable();
                DataTable dt = _remotingProxy.GetDataTable(sb.ToString());
                return dt;


            }
        }


        /// <summary>
        /// 区段集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetStageName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select stage_name as [value],stage_name as [key] from sys_stage where enabled='Y' ");

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

        /// <summary>
        /// 不良类型集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetDefectName(string stageName)
        {
            lock (_remotingProxy)
            {
                string quernSen = "";
                if (string.IsNullOrEmpty(stageName))
                {
                    quernSen = @" SELECT  defect_desc  as [value],defect_desc as [key] FROM dbo.sys_defect WHERE enabled = 'Y' ";
                }
                else
                {
                    quernSen = @" SELECT  defect_desc  as [value],defect_desc as [key] FROM  dbo.sys_defect WHERE stage_id IN (SELECT stage_id FROM  dbo.sys_stage WHERE stage_name='" + stageName + "' AND enabled='Y') AND  enabled = 'Y'  ";
                }
                DataTable dt = _remotingProxy.GetDataTable(quernSen);
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }

     

        /// <summary>
        ///车型集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetTypeName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select type_name as [value],type_name as [key] from sys_model_type where enabled='Y' ");

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

        /// <summary>
        /// 配置集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetModelList(string typeName)
        {
            lock (_remotingProxy)
            {
                string quernSen = "";
                if (string.IsNullOrEmpty(typeName))
                {
                    quernSen = @" SELECT  model_name  as [value],model_name as [key] FROM dbo.sys_model WHERE enabled = 'Y' ";
                }
                else
                {
                    quernSen = @" SELECT  model_name as [value],model_name as [key] FROM dbo.sys_model a JOIN dbo.sys_model_type b ON a.model_type_id = b.type_id WHERE a.enabled = 'Y' AND b.type_name = '" + typeName + "' ";
                }
                DataTable dt = _remotingProxy.GetDataTable(quernSen);
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }
    }
}