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

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

namespace Estsh.Core.Repositories
{
    public class DeliveryOrderReport:BaseApp
    {
        /***************************************************************************************************
         * 
         * 作者：张茂忠
         * 创建时间：2013.04.23
         * 描述：出货单信息查询模块Dal层
         * 修改日志：
         * 
         * 
         * *************************************************************************************************/
        public DeliveryOrderReport(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }
         
        /// <summary>
        /// 查询车型
        /// </summary> 
        /// <returns></returns>
        public ArrayList GetModelType()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select type_id as [value],type_name+':'+type_desc  as [key] from dbo.sys_model_type");

                return DataTypeConvert.NewObject.DataTableToArrayList(_remotingProxy.GetDataTable(SqlStringBuilder.ToString()));
            }
        }
        /// <summary>
        /// 获取枚举表
        /// </summary>
        /// <param name="enum_type">枚举类型</param>
        /// <returns></returns>
        public ArrayList GetEnum(string enum_type)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select enum_value AS [value],enum_desc AS [key] from dbo.sys_enum where enum_type='" + enum_type + "'");
                return DataTypeConvert.NewObject.DataTableToArrayList(_remotingProxy.GetDataTable(SqlStringBuilder.ToString()));
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public ArrayList GetQuery(string value,ref DataTable dt)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select * from (  ");

                SqlStringBuilder.Append("SELECT     ");
                SqlStringBuilder.Append("                    a.Number , ");
                SqlStringBuilder.Append("                    a.cust_order , ");
                SqlStringBuilder.Append("                    a.order_no , ");
                SqlStringBuilder.Append("                    a.part_id , ");
                SqlStringBuilder.Append("                    e.print_type , ");
                SqlStringBuilder.Append("                    e.start_car_no , ");
                SqlStringBuilder.Append("                    e.end_car_no , ");
                SqlStringBuilder.Append("                    e.bs_car_no , ");
                SqlStringBuilder.Append("                    e.be_car_no , ");
                SqlStringBuilder.Append("                    ( e.update_ymd + ' ' + e.update_hms ) AS update_ymd , ");
                SqlStringBuilder.Append("                    e.pack_type , ");
                SqlStringBuilder.Append("                    b.part_no , ");
                SqlStringBuilder.Append("                    b.part_spec , ");
                SqlStringBuilder.Append("                    SUM(a.qty) AS qty , ");
                SqlStringBuilder.Append("                    c.ship_unit , ");
                SqlStringBuilder.Append("                    s.shift_name ,  ");
                SqlStringBuilder.Append("                    g.model_id , ");
                SqlStringBuilder.Append("                    g.model_desc , ");
                SqlStringBuilder.Append("                    e.memo , ");
                SqlStringBuilder.Append("                    g.model_type_id , ");
                SqlStringBuilder.Append("                    m.type_name , ");
                SqlStringBuilder.Append("                    e.create_ymd   ");
                SqlStringBuilder.Append("          FROM      g_ship_order AS e ");
                SqlStringBuilder.Append("                    INNER JOIN g_ship_order_detail AS a ON e.order_no = a.order_no ");
                SqlStringBuilder.Append("                    INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("                    INNER JOIN sys_part_cust_order AS c ON c.part_id = b.part_id ");
                SqlStringBuilder.Append("                    INNER JOIN dbo.sys_enum AS d ON d.enum_value = b.part_location ");
                SqlStringBuilder.Append("                                                    AND d.enum_type = 'sys_part_location' ");
                SqlStringBuilder.Append("                    INNER JOIN dbo.sys_model AS g ON g.model_id = b.model_id ");
                SqlStringBuilder.Append("                    INNER JOIN dbo.sys_model_type AS m ON m.type_id = g.model_type_id ");
                SqlStringBuilder.Append("                    INNER JOIN dbo.sys_shift AS s ON s.shift_id = e.shift_id ");
                SqlStringBuilder.Append("           GROUP BY  ");
                SqlStringBuilder.Append("					a.Number , ");
                SqlStringBuilder.Append("                    a.cust_order , ");
                SqlStringBuilder.Append("                    a.order_no , ");
                SqlStringBuilder.Append("                    a.part_id , ");
                SqlStringBuilder.Append("                    e.print_type , ");
                SqlStringBuilder.Append("                    e.start_car_no , ");
                SqlStringBuilder.Append("                    e.end_car_no , ");
                SqlStringBuilder.Append("                    e.bs_car_no , ");
                SqlStringBuilder.Append("                    e.be_car_no , ");
                SqlStringBuilder.Append("                    ( e.update_ymd + ' ' + e.update_hms ), ");
                SqlStringBuilder.Append("                    e.pack_type , ");
                SqlStringBuilder.Append("                    b.part_no , ");
                SqlStringBuilder.Append("                    b.part_spec , ");
                SqlStringBuilder.Append("                    c.ship_unit , ");
                SqlStringBuilder.Append("                    s.shift_name ,  ");
                SqlStringBuilder.Append("                    g.model_id , ");
                SqlStringBuilder.Append("                    g.model_desc , ");
                SqlStringBuilder.Append("                    e.memo , ");
                SqlStringBuilder.Append("                    g.model_type_id , ");
                SqlStringBuilder.Append("                    m.type_name , ");
                SqlStringBuilder.Append("                    e.create_ymd   ");
                SqlStringBuilder.Append(") as a ");
                SqlStringBuilder.Append("where 0=0 " + value + " order by Number,part_no ");

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