Estsh-Yiri/Estsh.Core.Services/Repositories/YYReWorkQueryDal.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 YYReWorkQueryDal : BaseApp
    {
        public YYReWorkQueryDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
            this.TABLE_NAME = "";
            this.PK_NAME = "";
        }

        /// <summary>
        /// 一次通过率
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable YYReWorkData(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                string where="";

                where = aWhere.Replace("a.create_ymd+' '+a.create_hms", "a.out_process_time");


                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("(SELECT bbb.type_name,bbb.model_name,bbb.stage_name,qty,cqty, SUBSTRING(CONVERT(VARCHAR(20), ROUND(qty/cast(cqty as decimal(10,2))*100,2)),0,6)+'%'  AS  per  ");
                SqlStringBuilder.Append(" FROM  ");
                SqlStringBuilder.Append("(SELECT type_name,model_name,stage_name,qty FROM  ");
                SqlStringBuilder.Append("(SELECT c.type_name,b.model_name,e.stage_name,COUNT(DISTINCT a.serial_number) qty  ");
                SqlStringBuilder.Append("FROM dbo.g_sn_travel(NOLOCK) a  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id=b.model_id   ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id=b.model_type_id   ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_terminal d ON a.terminal_id=d.terminal_id ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_stage(NOLOCK) e ON d.stage_id=e.stage_id ");
                SqlStringBuilder.Append("WHERE  a.work_flag=1 AND a.current_status=0  AND a.prod_type IN  ('S','E','C') and  a.terminal_id IN (100061,100065,100066)  " + aWhere + " ");
                SqlStringBuilder.Append("GROUP BY  c.type_name,b.model_name,e.stage_name) aa)aaa ");
                SqlStringBuilder.Append("LEFT JOIN  ");
                SqlStringBuilder.Append("(SELECT c.type_name,b.model_name,e.stage_name,COUNT(DISTINCT a.serial_number) cqty  ");
                SqlStringBuilder.Append("FROM dbo.g_sn_travel(NOLOCK) a ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id=b.model_id   ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id=b.model_type_id   ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_terminal d ON a.terminal_id=d.terminal_id ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_stage(NOLOCK) e ON d.stage_id=e.stage_id ");
                SqlStringBuilder.Append("WHERE   a.prod_type IN  ('S','E','C') AND  a.work_flag IN (0,1) AND  c.type_name<>''  and a.terminal_id IN (100061,100065,100066)   " + aWhere + " ");
                SqlStringBuilder.Append("GROUP BY  c.type_name,b.model_name,e.stage_name) bbb ");
                SqlStringBuilder.Append("ON aaa.type_name=bbb.type_name AND bbb.model_name = aaa.model_name AND bbb.stage_name = aaa.stage_name) aaaa ");
               


                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", SqlStringBuilder.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " * "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " type_name,model_name,stage_name "));
                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", " 1=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());
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;

             
            }
        }

        /// <summary>
        /// 不良率
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable YYReWorkDataDeatil(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                 StringBuilder SqlStringBuilder = new StringBuilder();

                 SqlStringBuilder.Append("select * from (SELECT aa.type_name , aa.model_name ,aa.stage_name ,aa.defect_desc ,aa.item_defect_desc ,aa.ncqty ,aa.qty ,SUBSTRING(CONVERT(VARCHAR(20), ROUND(aa.ncqty / CAST(aa.qty AS DECIMAL(10, 2)) * 100, 2)),0, 6) + '%' AS per FROM  (SELECT c.type_name , ");
                 SqlStringBuilder.Append("        b.model_name , ");
                 SqlStringBuilder.Append("        e.stage_name , ");
                 SqlStringBuilder.Append("        f.defect_desc , ");
                 SqlStringBuilder.Append("        g.item_defect_desc , ");
                 SqlStringBuilder.Append("        COUNT(a.serial_number) AS ncqty , ");
                 SqlStringBuilder.Append("        ( SELECT    COUNT(a.serial_number) AS ncqty ");
                 SqlStringBuilder.Append("          FROM      dbo.g_sn_defect (NOLOCK) a ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_defect f ON a.defect_id = f.defect_id ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_defect_detail g ON a.item_defect_id = g.ruid ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.g_sn_status (NOLOCK) i ON a.serial_number = i.serial_number ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_model (NOLOCK) b ON i.model_id = b.model_id ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_model_type (NOLOCK) c ON c.type_id = b.model_type_id ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_terminal d ON a.terminal_id = d.terminal_id ");
                 SqlStringBuilder.Append("                    LEFT JOIN dbo.sys_stage (NOLOCK) e ON d.stage_id = e.stage_id ");
                 SqlStringBuilder.Append("          WHERE     1 = 1 ");
                 SqlStringBuilder.Append("                    AND a.terminal_id NOT IN ( 100044, 100055, 100069 ) ");
                 SqlStringBuilder.Append("                     " + aWhere + " ");
                 SqlStringBuilder.Append("        ) AS qty ");
                 SqlStringBuilder.Append(" FROM   dbo.g_sn_defect (NOLOCK) a ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_defect f ON a.defect_id = f.defect_id ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_defect_detail g ON a.item_defect_id = g.ruid ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status (NOLOCK) i ON a.serial_number = i.serial_number ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model (NOLOCK) b ON i.model_id = b.model_id ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model_type (NOLOCK) c ON c.type_id = b.model_type_id ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_terminal d ON a.terminal_id = d.terminal_id ");
                 SqlStringBuilder.Append("        LEFT JOIN dbo.sys_stage (NOLOCK) e ON d.stage_id = e.stage_id ");
                 SqlStringBuilder.Append(" WHERE  1 = 1 ");
                 SqlStringBuilder.Append("        AND a.terminal_id NOT IN ( 100044, 100055, 100069 ) ");
                 SqlStringBuilder.Append("        "+aWhere+" ");
                 SqlStringBuilder.Append(" GROUP BY c.type_name , ");
                 SqlStringBuilder.Append("        b.model_name , ");
                 SqlStringBuilder.Append("        e.stage_name , ");
                 SqlStringBuilder.Append("        f.defect_desc , ");
                 SqlStringBuilder.Append("        g.item_defect_desc  )  aa ) bb  ");


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

                //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", SqlStringBuilder.ToString()));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " bb.* "));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " "));
                //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", ""));
                //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());
                //totalCount = Convert.ToInt32(values["@TotalCount"]);
                //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);
            }
        }
    }
}