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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.Collections;
using ApServerProvider;

namespace Estsh.Core.Repositories
{
    public class ProduceQuery:BaseApp
    {
        /***************************************************************************************************
         * 
         * 作者：张茂忠
         * 创建时间：2013.04.22
         * 描述：照片查询模块Dal层
         * 修改日志：
         * 
         * 
         * *************************************************************************************************/
        public ProduceQuery(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }
        /// <summary>
        /// 汇总信息
        /// </summary> 
        /// <returns></returns>
        public DataTable GetSummary(string _where)
        {
            lock (_remotingProxy)
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT  type_id , ");
                sql.Append("        c.model_id , ");
                sql.Append("        type_name , ");
                sql.Append("        model_desc , ");
                sql.Append("        e.number ");
                sql.Append("FROM    g_workorder AS a ");
                sql.Append("        INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                sql.Append("        INNER JOIN sys_model AS c ON c.model_id = b.model_id ");
                sql.Append("        INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id ");
                sql.Append("        INNER JOIN ( SELECT c.model_id , ");
                sql.Append("                            COUNT(c.model_id) AS number ");
                sql.Append("                     FROM   g_workorder AS a ");
                sql.Append("                            INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                sql.Append("                            INNER JOIN sys_model AS c ON c.model_id = b.model_id ");
                sql.Append("                            INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id ");
                sql.Append("                            LEFT JOIN g_wo_relation AS e ON e.workorder_id = a.ruid ");
                sql.Append("                            LEFT JOIN g_vw_order AS f ON f.order_id = e.order_id ");
                sql.Append("                     WHERE 1=1  " + _where + " ");
                sql.Append("                     GROUP BY c.model_id ");
                sql.Append("                   ) AS e ON e.model_id = c.model_id ");
                sql.Append("                     WHERE 1=1  " + _where + " ");
                sql.Append("GROUP BY type_id , ");
                sql.Append("        c.model_id , ");
                sql.Append("        type_name , ");
                sql.Append("        model_desc , ");
                sql.Append("        e.number  ");

                return _remotingProxy.GetDataTable(sql.ToString());
            }
        }
        /// <summary>
        /// 查询车型
        /// </summary> 
        /// <returns></returns>
        public DataTable GetModelType()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select type_id as [value],type_name as [key] from dbo.sys_model_type");

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

        /// <summary>
        /// 查询生产类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetProduceType()
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT enum_value as [value] ,enum_desc as [key] FROM sys_enum WHERE enum_type = 'sys_prod_type' ";
                return _remotingProxy.GetDataTable(sql);
            }
        }

        public string GetDisplay(string produceValue)
        {
            lock (_remotingProxy)
            {
                string stringSql = "SELECT enum_value as [value],enum_value as [key] FROM sys_enum WHERE enum_type = 'sys_prod_type' AND enum_desc = '" + produceValue + "'";

                return _remotingProxy.GetScalar(stringSql).ToString();
            }
        }

        public DataTable GetSummaryDtail(string _where, string typeID)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT 	d.type_name,	 ");
                SqlStringBuilder.Append("		g.part_id, ");
                SqlStringBuilder.Append("		h.part_no,         ");
                SqlStringBuilder.Append("		h.part_spec,                              ");
                SqlStringBuilder.Append("		COUNT(h.part_no) AS number, ");
                SqlStringBuilder.Append("		CASE ISNULL(NULLIF(g.type, 'N'), a.type)            ");
                SqlStringBuilder.Append("		WHEN 'N' THEN '正常'            ");
                SqlStringBuilder.Append("		WHEN 'P' THEN '领料'            ");
                SqlStringBuilder.Append("		WHEN 'X' THEN '试制'            ");
                SqlStringBuilder.Append("		WHEN 'D' THEN '加拨'            ");
                SqlStringBuilder.Append("		WHEN 'S' THEN '库存'          ");
                SqlStringBuilder.Append("		END AS type                       ");
                SqlStringBuilder.Append("		FROM   g_workorder AS a                              ");
                SqlStringBuilder.Append("		INNER JOIN sys_part AS b ON a.part_id = b.part_id                              ");
                SqlStringBuilder.Append("		INNER JOIN sys_model AS c ON c.model_id = b.model_id                              ");
                SqlStringBuilder.Append("		INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id                              ");
                SqlStringBuilder.Append("		LEFT JOIN g_wo_relation AS e ON e.workorder_id = a.ruid                              ");
                SqlStringBuilder.Append("		LEFT JOIN g_vw_order AS f ON f.order_id = e.order_id   ");
                SqlStringBuilder.Append("		inner join g_workorder_detail as g on a.ruid=g.workorder_id  ");
                SqlStringBuilder.Append("		inner join sys_part as h on g.part_id=h.part_id                   ");
                SqlStringBuilder.Append("		WHERE 1=1  " + _where);
                SqlStringBuilder.Append("		 GROUP BY  ");
                SqlStringBuilder.Append("		 g.part_id, ");
                SqlStringBuilder.Append("		 h.part_no, ");
                SqlStringBuilder.Append("		 h.part_spec,                             ");
                SqlStringBuilder.Append("		 d.type_name ,  ");
                SqlStringBuilder.Append("		 ISNULL(NULLIF(g.type, 'N'), a.type)    ORDER BY  type_name asc,type asc, h.part_no  asc");

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