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

namespace Estsh.Core.Repositories
{
    public class ProductionOrderPdlineFirstEmergencyReleaseDal : BaseApp
    {
        public ProductionOrderPdlineFirstEmergencyReleaseDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
        }

        /// <summary>
        /// 获取客户产线列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetCustPDLine()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  cust_pdline_name as [value], ");
                SqlStringBuilder.Append("cust_pdline_name + ' : ' + cust_pdline_desc as [key] ");
                SqlStringBuilder.Append("FROM    dbo.sys_cust_pdline ");
                SqlStringBuilder.Append("WHERE   enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY cust_pdline_name ");

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

        /// <summary>
        /// 根据客户产线查询车型
        /// </summary>
        /// <returns></returns>
        public DataTable GetMode_type(string CustPDLine)
        {
            lock (_remotingProxy)
            {
                string sql = "select type_id as [value], type_name as [key] from dbo.sys_model_type WHERE enabled='Y' and cust_pdline_id= (select cust_pdline_id from sys_cust_pdline where cust_pdline_name='" + CustPDLine + "')";
                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 产线
        /// </summary>
        /// <returns></returns>
        public DataTable GetPdline(string CustPDLine)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.pdline_id AS [value] , ");
                SqlStringBuilder.Append("        a.pdline_name AS [key] ");
                SqlStringBuilder.Append("FROM    dbo.sys_pdline AS a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_pdline_relation AS b ON a.pdline_id = b.pdline_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_cust_pdline AS c ON b.cust_pdline_id = c.cust_pdline_id ");
                SqlStringBuilder.Append("WHERE   c.cust_pdline_name = @CustPDLine ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@CustPDLine", CustPDLine);

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

        /// <summary>
        /// 区段
        /// </summary>
        /// <returns></returns>
        public DataTable GetStage(string pdlineId)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  stage_id AS [value] , ");
                SqlStringBuilder.Append("        stage_name AS [key] ");
                SqlStringBuilder.Append("FROM    dbo.sys_stage ");
                SqlStringBuilder.Append("WHERE   pdline_id = @pdlineId ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@pdlineId", pdlineId);

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

        /// <summary>
        /// 根据车型编号查询座椅配置
        /// </summary>
        /// <param name="modelTypeID">车型编号</param>
        /// <returns>配置</returns>
        public DataTable GetModelList(int ModelType)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT model_id as [value],model_name as [key] FROM dbo.sys_model WHERE enabled='Y' and model_type_id=@type_id  ORDER BY model_name";

                Hashtable Params = new Hashtable(1);

                Params.Add("@type_id", ModelType);

                return _remotingProxy.GetDataTable(SqlString, Params);
            }
        }

        /// <summary>
        /// 获取班次
        /// </summary>
        /// <returns></returns>
        public DataTable Getshift()
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT shift_id as [value] ,shift_code as [key] from sys_shift ";
                return _remotingProxy.GetDataTable(SqlString);
            }
        }

        /// <summary>
        /// 获取生产类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetProdType()
        {
            lock (_remotingProxy)
            {
                string SqlString = "select  enum_value as [value] ,enum_desc as [key] from sys_enum where enum_type='sys_prod_type' AND enum_value='Z'";
                return _remotingProxy.GetDataTable(SqlString);
            }
        }



        /// <summary>
        /// 根据配置编号获取总成清单
        /// </summary>
        /// <param name="modelID">配置编号</param>
        /// <returns>总成清单</returns>
        public DataTable GetBomDetail(int modelID, string stageId)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT b.item_part_id , ");
                SqlStringBuilder.Append("        c.part_no AS modelNo , ");
                SqlStringBuilder.Append("        d.part_no , ");
                SqlStringBuilder.Append("        d.part_spec, ");
                SqlStringBuilder.Append("        e.enum_desc ");
                SqlStringBuilder.Append(" FROM   dbo.sys_bom a , ");
                SqlStringBuilder.Append("        dbo.sys_bom_detail b , ");
                SqlStringBuilder.Append("        dbo.sys_part c , ");
                SqlStringBuilder.Append("        dbo.sys_part d, ");
                SqlStringBuilder.Append("		dbo.sys_enum e, ");
                SqlStringBuilder.Append("		dbo.g_stage_location f ");
                SqlStringBuilder.Append(" WHERE  a.bom_id = b.bom_id ");
                SqlStringBuilder.Append("        AND a.bom_type = '物料' ");
                SqlStringBuilder.Append("        AND a.part_id = c.part_id ");
                SqlStringBuilder.Append("        AND b.item_part_id = d.part_id ");
                SqlStringBuilder.Append("        AND c.model_id = @model_id ");
                SqlStringBuilder.Append("		AND e.enum_value = d.part_location AND e.enum_type='sys_part_location' AND e.enabled='Y' ");
                SqlStringBuilder.Append("		AND f.enum_id=e.enum_id AND f.stage_id=@stageId ");
                SqlStringBuilder.Append(" ORDER BY c.part_no , ");
                SqlStringBuilder.Append("        d.part_location  ");


                Hashtable Params = new Hashtable(2);

                Params.Add("@model_id", modelID);
                Params.Add("@stageId", stageId);


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

        /// <summary>
        /// 根据配置编号获取总成清单
        /// </summary>
        /// <param name="modelID">配置编号</param>
        /// <returns>总成清单</returns>
        public DataTable GetBomDetailTable(int modelID)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT  b.item_part_id,c.part_no AS modelNo, d.part_no , ");
                SqlStringBuilder.Append("        d.part_spec ");
                SqlStringBuilder.Append("FROM    dbo.sys_bom a , ");
                SqlStringBuilder.Append("        dbo.sys_bom_detail b , ");
                SqlStringBuilder.Append("        dbo.sys_part c , ");
                SqlStringBuilder.Append("        dbo.sys_part d ");
                SqlStringBuilder.Append("WHERE   a.bom_id = b.bom_id AND a.bom_type='物料'");
                SqlStringBuilder.Append("        AND a.part_id = c.part_id ");
                SqlStringBuilder.Append("        AND b.item_part_id = d.part_id ");
                SqlStringBuilder.Append("        AND c.model_id = @model_id ORDER BY c.part_no, d.part_location");

                Hashtable Params = new Hashtable(1);

                Params.Add("@model_id", modelID);

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

        /// <summary>
        /// 查询单据是否生成
        /// </summary>
        /// <param name="order_no"></param>
        /// <returns></returns>
        public bool isExist(string order_no)
        {
            lock (_remotingProxy)
            {
                string strSql = "select top 1 1 from dbo.g_stock_order where order_no='" + order_no + "'";
                DataTable dt = _remotingProxy.GetDataTable(strSql);
                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        //加载单据编号
        public string GetOrderNo(string stockOrder, string p)
        {
            lock (_remotingProxy)
            {
                Com.Estsh.MES.App.CommonListData comm = new Com.Estsh.MES.App.CommonListData(this._remotingProxy);
                return comm.GetOrderNo(stockOrder, p);
            }
        }

        //插入数据 g_stock_order
        //int _orderStatus = 0;
        public bool Insert_g_stock_order(int model_type_id, int model_id, string order_no, string cust_pdline, int shift_id, int qty, string type, string document_data, int stageId)
        {
            lock (_remotingProxy)
            {
                Hashtable OrderValues = new Hashtable();

                //if (orderPlanType == EOrderPlanType.ByModel)
                //{
                OrderValues.Add("model_type_id", model_type_id);
                OrderValues.Add("model_id", model_id);
                //}
                //else
                //{
                //OrderValues.Add("model_type_id", 0);
                //OrderValues.Add("model_id", 0);
                //}
                OrderValues.Add("order_no", order_no);
                //OrderValues.Add("factory_id", _remotingProxy.OrgID);
                OrderValues.Add("factory_id", 1001);

                OrderValues.Add("cust_pdline", cust_pdline);
                OrderValues.Add("shift_id", shift_id);
                OrderValues.Add("qty", qty);
                //生产类型
                OrderValues.Add("type", type);
                OrderValues.Add("document_data", document_data);
                OrderValues.Add("stage_id", stageId);

                return _remotingProxy.Insert("dbo.g_stock_order", OrderValues);
            }
        }

        //插入数据 g_stock_order_detail
        public bool Insert_g_stock_order_detail(string order_no, string part_id)
        {
            lock (_remotingProxy)
            {
                Hashtable DetailValues = new Hashtable();

                DetailValues.Clear();
                DetailValues.Add("order_no", order_no);
                DetailValues.Add("part_id", part_id);
                return _remotingProxy.Insert("dbo.g_stock_order_detail", DetailValues);
            }

        }

        ///<summary>
        /// 调用存储过程 dbo.sys_release_stock_order 释放生产指令
        /// </summary>
        /// <returns></returns>
        public string ReleaseStockOrder(string orderNo,  string stageId)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                //Parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@ruid", ruid));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@order_no", orderNo));

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

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.sys_release_First_Emergency_stock_order", Parameters);

                if (!ht.ContainsKey("@tres"))
                    return string.Empty;

                return ht["@tres"].ToString();
            }
        }


        ///<summary>
        /// 导入生产指令
        /// </summary>
        /// <returns></returns>
        public string ReleaseStockOrderImport(string orderNo, string partNo, int shiftId, string documentData, int qty, string ProdType, string batchNumber, int userId)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@order_no", orderNo));

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

                Parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@shiftId", shiftId));

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

                Parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@qty", qty));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@woType", ProdType));

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

                Parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@userId", userId));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.sys_release_stock_order_import", Parameters);

                if (!ht.ContainsKey("@tres"))
                    return string.Empty;

                return ht["@tres"].ToString();
            }
        }

        ///<summary>
        /// 调用存储过程 dbo.sys_assign_stock_order 分配虚拟料架
        /// </summary>
        /// <returns></returns>
        public string AssignStockOrder(string custPDLine)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@cust_pdline", custPDLine));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.sys_assign_stock_order", Parameters);

                if (!ht.ContainsKey("@tres"))
                    return string.Empty;

                return ht["@tres"].ToString();
            }
        }

        /// <summary>
        /// 获取要打印的条码数据（前排）
        /// </summary>
        /// <returns></returns>
        public DataTable GetSN(string orderNo)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT  ");
                SqlStringBuilder.Append("        a.serial_number , ");
                SqlStringBuilder.Append("        a.create_ymd + ' ' + a.create_hms AS create_time , ");
                SqlStringBuilder.Append("        RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
                SqlStringBuilder.Append("                                           2) AS order_seq , ");
                SqlStringBuilder.Append("        b.slot_no , ");
                SqlStringBuilder.Append("        b.car_no , ");
                SqlStringBuilder.Append("        c.part_no , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        c.part_spec2 , ");
                SqlStringBuilder.Append("        c.part_location , ");
                SqlStringBuilder.Append("        c.cust_part_no , ");
                SqlStringBuilder.Append("        e.model_code , ");
                SqlStringBuilder.Append("        e.model_desc , ");
                SqlStringBuilder.Append("        e.model_alias , ");
                SqlStringBuilder.Append("        f.enum_desc , ");
                SqlStringBuilder.Append("        g.csn , ");
                SqlStringBuilder.Append("        g.prod_type ");
                SqlStringBuilder.Append("FROM    dbo.g_workorder_sn a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_workorder b ON a.workorder = b.ruid ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
                SqlStringBuilder.Append("                                    AND f.enum_type = 'sys_part_location' ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
                SqlStringBuilder.Append("WHERE   a.printed = 0 ");
                SqlStringBuilder.Append("        AND g.workorder='" + orderNo + "' and c.part_location in ('01','02') ");
                SqlStringBuilder.Append("ORDER BY a.ruid ");

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

        /// <summary>
        /// 更新条码的打印状态
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>是否成功</returns>
        //public bool UpdateSNPrintStatus(string serialNumber)
        //{
        //    string SqlString = "update dbo.g_workorder_sn set printed=1,update_userid=@user_id,update_ymd=dbo.get_ymd(),update_hms=dbo.get_hms() where serial_number=@sn";

        //    Hashtable Params = new Hashtable(2);

        //    Params.Add("@user_id", _remotingProxy.UserID);
        //    Params.Add("@sn", serialNumber);

        //    return _remotingProxy.ExecuteNonQuery(SqlString, Params) == 1;
        //}

        /// <summary>
        /// 获取要打印的条码数据（后排）
        /// </summary>
        /// <returns></returns>
        public DataTable GetBackSN(string orderNo)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT  a.serial_number , ");
                SqlStringBuilder.Append("        a.create_ymd + ' ' + a.create_hms AS create_time , ");
                SqlStringBuilder.Append("        RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
                SqlStringBuilder.Append("                                           2) AS order_seq , ");
                SqlStringBuilder.Append("        b.slot_no , ");
                SqlStringBuilder.Append("        b.car_no , ");
                SqlStringBuilder.Append("        c.part_no , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        c.part_spec2 , ");
                SqlStringBuilder.Append("        c.part_location , ");
                SqlStringBuilder.Append("        c.cust_part_no , ");
                SqlStringBuilder.Append("        e.model_code , ");
                SqlStringBuilder.Append("        e.model_desc , ");
                SqlStringBuilder.Append("        e.model_alias , ");
                SqlStringBuilder.Append("        f.enum_desc , ");
                SqlStringBuilder.Append("        g.csn , ");
                SqlStringBuilder.Append("        g.prod_type , ");
                SqlStringBuilder.Append("        h.qa_label_count, h.barcode_count ");
                SqlStringBuilder.Append("FROM    dbo.g_workorder_sn a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_workorder b ON a.workorder = b.ruid ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
                SqlStringBuilder.Append("                                    AND f.enum_type = 'sys_part_location' ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model_prodinfo h ON e.model_id = h.model_id ");
                SqlStringBuilder.Append("WHERE   a.printed = 0 ");
                SqlStringBuilder.Append("        AND g.workorder = @order_no ");
                SqlStringBuilder.Append("        AND c.part_location NOT IN ( '01', '02' ) ");
                SqlStringBuilder.Append("ORDER BY c.part_location, a.ruid ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@order_no", orderNo);

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


        /// <summary>
        /// 初始化全局的静态变量
        /// </summary>
        /// <param name="proxy"></param>
        public string TrayPagePrintCount(string param)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT param_value FROM sys_base  WHERE param_name='" + param + "' ");

                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());

                if (dt.Rows.Count == 0)
                {
                    return "系统参数表（SYS_BASE）中未定义 参数" + param;
                }
                else
                {
                    return dt.Rows[0]["param_value"].ToString();
                }
            }
        }


        ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        //条码补打


        /// <summary>
        /// 获取补打信息
        /// </summary>
        /// <param name="serialNumber">过程条码 OR 合格证条码</param>
        /// <returns></returns>
        public DataTable GetReprintDataBySN(string type, string serialNumber, int count, bool isOrderByInLineTime)
        {
            lock (_remotingProxy)
            {
                if (type == "1")
                {
                    StringBuilder SqlStringBuilder = new StringBuilder(1024);

                    SqlStringBuilder.Append("SELECT  f.car_no , ");
                    SqlStringBuilder.Append("        a.serial_number , ");
                    SqlStringBuilder.Append("        a.create_ymd , ");
                    SqlStringBuilder.Append("        a.create_hms , ");
                    SqlStringBuilder.Append("        c.type , ");
                    SqlStringBuilder.Append("        d.part_no , ");
                    SqlStringBuilder.Append("        d.part_spec , ");
                    SqlStringBuilder.Append("        d.part_location , ");
                    SqlStringBuilder.Append("        e.model_code , ");
                    SqlStringBuilder.Append("        e.model_type , ");
                    SqlStringBuilder.Append("        a.printed , ");
                    SqlStringBuilder.Append("        a.reprint_count ");
                    SqlStringBuilder.Append("FROM    g_workorder_sn a , ");
                    SqlStringBuilder.Append("        g_workorder_detail b , ");
                    SqlStringBuilder.Append("        g_workorder c , ");
                    SqlStringBuilder.Append("        sys_part d , ");
                    SqlStringBuilder.Append("        sys_model e , ");
                    SqlStringBuilder.Append("        g_sn_status f ");
                    SqlStringBuilder.Append("WHERE   a.wo_detail_id = b.ruid ");
                    SqlStringBuilder.Append("        AND b.workorder_id = c.ruid ");
                    SqlStringBuilder.Append("        AND a.part_id = d.part_id ");
                    SqlStringBuilder.Append("        AND d.model_id = e.model_id ");
                    SqlStringBuilder.Append("        AND a.serial_number = f.serial_number AND d.part_location in ('01','02') AND f.current_status=0 ");
                    SqlStringBuilder.Append("        AND ( a.serial_number = '" + serialNumber + "' ");
                    SqlStringBuilder.Append("              OR f.csn = '" + serialNumber + "' ");
                    SqlStringBuilder.Append("            ) ");

                    return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                }
                else
                {
                    StringBuilder SqlStringBuilder = new StringBuilder(1024);

                    SqlStringBuilder.Append("SELECT TOP ");
                    SqlStringBuilder.Append(count);
                    SqlStringBuilder.Append(" f.car_no, ");
                    SqlStringBuilder.Append(" a.serial_number , ");
                    SqlStringBuilder.Append("        a.create_ymd , ");
                    SqlStringBuilder.Append("        a.create_hms , ");
                    SqlStringBuilder.Append("        c.type , ");
                    SqlStringBuilder.Append("        d.part_no , ");
                    SqlStringBuilder.Append("        d.part_spec , ");
                    SqlStringBuilder.Append("        d.part_location , ");
                    SqlStringBuilder.Append("        e.model_code , ");
                    SqlStringBuilder.Append("        e.model_type , ");
                    SqlStringBuilder.Append("        a.printed , ");
                    SqlStringBuilder.Append("        a.reprint_count ");
                    SqlStringBuilder.Append("FROM    g_workorder_sn a , ");
                    SqlStringBuilder.Append("        g_workorder_detail b , ");
                    SqlStringBuilder.Append("        g_workorder c , ");
                    SqlStringBuilder.Append("        sys_part d , ");
                    SqlStringBuilder.Append("        sys_model e , ");
                    SqlStringBuilder.Append("        g_sn_status f ");
                    SqlStringBuilder.Append("WHERE   a.wo_detail_id = b.ruid ");
                    SqlStringBuilder.Append("        AND b.workorder_id = c.ruid ");
                    SqlStringBuilder.Append("        AND a.part_id = d.part_id ");
                    SqlStringBuilder.Append("        AND d.model_id = e.model_id ");
                    SqlStringBuilder.Append("        AND a.printed = 1 AND a.serial_number=f.serial_number AND f.current_status=0 ");
                    SqlStringBuilder.Append("        AND d.part_location IN ('01','02') ");

                    // 补打过程条码、后排合格证按照上线时间降序
                    if (isOrderByInLineTime)
                    {
                        SqlStringBuilder.Append("ORDER BY f.in_pdline_time DESC ");
                    }
                    else // 补打前排合格证按照下线时间降序
                    {
                        SqlStringBuilder.Append("ORDER BY f.out_pdline_time DESC ");
                    }

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

        /// <summary>
        /// 更新条码的补打状态
        /// </summary>
        /// <param name="sn">条码</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateSNReprintStatus(string serialNumber)
        {
            lock (_remotingProxy)
            {
                string SqlString = "update g_workorder_sn set reprint_count=reprint_count+1 where serial_number=@sn";

                Hashtable Params = new Hashtable(1);

                Params.Add("@sn", serialNumber);

                return _remotingProxy.ExecuteNonQuery(SqlString, Params) == 1;
            }
        }

        //打印过程条码

        /// <summary>
        /// 获取要 打印过程条码 信息
        /// </summary>
        /// <param name="serialNumber"></param>
        //public void PrintSerialNumber(string serialNumber)
        public DataTable PrintSerialNumber(string serialNumber)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT TOP 1 ");
                SqlStringBuilder.Append("        a.serial_number , ");
                SqlStringBuilder.Append("        a.create_ymd + ' ' + a.create_hms AS create_time , ");
                SqlStringBuilder.Append("        RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
                SqlStringBuilder.Append("                                           2) AS order_seq , ");
                SqlStringBuilder.Append("        b.slot_no , ");
                SqlStringBuilder.Append("        b.car_no , ");
                SqlStringBuilder.Append("        c.part_no , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        c.part_spec2 , ");
                SqlStringBuilder.Append("        c.part_location , ");
                SqlStringBuilder.Append("        c.cust_part_no , ");
                SqlStringBuilder.Append("        e.model_code , ");
                SqlStringBuilder.Append("        e.model_desc , ");
                SqlStringBuilder.Append("        e.model_alias , ");
                SqlStringBuilder.Append("        f.enum_desc , ");
                SqlStringBuilder.Append("        g.csn , ");
                SqlStringBuilder.Append("        g.prod_type ");
                SqlStringBuilder.Append("FROM    dbo.g_workorder_sn a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_workorder b ON a.workorder = b.ruid ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
                SqlStringBuilder.Append("                                    AND f.enum_type = 'sys_part_location' ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
                SqlStringBuilder.Append("WHERE   a.serial_number = @sn ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@sn", serialNumber);

                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), Params);

                return dt;
            }
        }

        /// <summary>
        /// 根据配置编号获取过程条码打印的张数
        /// </summary>
        public int GetModelBarcodeCount(string serialNumber)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT barcode_count FROM dbo.sys_model_prodinfo WHERE model_id=(select model_id from dbo.g_sn_status where serial_number=@serial_number)";

                Hashtable Params = new Hashtable(1);

                Params.Add("@serial_number", serialNumber);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 1 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新条码的打印状态
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>是否成功</returns>
        public bool UpdateSNPrintStatus(string serialNumber)
        {
            lock (_remotingProxy)
            {
                string SqlString = "update dbo.g_workorder_sn set printed=1,update_userid=@user_id,update_ymd=dbo.get_ymd(),update_hms=dbo.get_hms() where serial_number=@sn";

                Hashtable Params = new Hashtable(2);

                Params.Add("@user_id", _remotingProxy.UserID);
                Params.Add("@sn", serialNumber);

                return _remotingProxy.ExecuteNonQuery(SqlString, Params) == 1;
            }
        }

        //打印合格证

        /// <summary>
        /// 根据配置编号获取合格证条码打印的张数
        /// </summary>
        public int GetModelQACount(string serialNumber)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT qa_label_count FROM dbo.sys_model_prodinfo WHERE model_id=(select model_id from dbo.g_sn_status where serial_number=@serial_number)";

                Hashtable Params = new Hashtable(1);

                Params.Add("@serial_number", serialNumber);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 1 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据配置编号获取合格证条码打印的张数
        /// </summary>
        public int getPartId(string partNo)
        {
            lock (_remotingProxy)
            {
                string SqlString = " SELECT * FROM dbo.sys_part WHERE part_no=@partNo ";

                Hashtable Params = new Hashtable(1);

                Params.Add("@partNo", partNo);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        //打印后排的的合格证条码

        /// <summary>
        /// 根据条码获取对应后排条码的打印信息
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>后排条码的打印信息</returns>
        public DataTable GetBackSNs(string serialNumber)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT top 1 a.serial_number , ");
                SqlStringBuilder.Append("        a.create_ymd + ' ' + a.create_hms AS create_time , ");
                SqlStringBuilder.Append("        RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
                SqlStringBuilder.Append("                                           2) AS order_seq , ");
                SqlStringBuilder.Append("        b.slot_no , ");
                SqlStringBuilder.Append("        b.car_no , ");
                SqlStringBuilder.Append("        c.part_no , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        c.part_spec2 , ");
                SqlStringBuilder.Append("        c.part_location , ");
                SqlStringBuilder.Append("        c.cust_part_no , ");
                SqlStringBuilder.Append("        e.model_code , ");
                SqlStringBuilder.Append("        e.model_desc , ");
                SqlStringBuilder.Append("        e.model_alias , ");
                SqlStringBuilder.Append("        f.enum_desc , ");
                SqlStringBuilder.Append("        g.csn , ");
                SqlStringBuilder.Append("        g.prod_type ");
                SqlStringBuilder.Append("FROM    dbo.g_workorder_sn a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_workorder b ON a.workorder = b.ruid ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
                SqlStringBuilder.Append("                                    AND f.enum_type = 'sys_part_location' ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
                SqlStringBuilder.Append("WHERE   a.printed = 0 ");
                SqlStringBuilder.Append("        AND b.ruid = ( SELECT   workorder ");
                SqlStringBuilder.Append("                       FROM     dbo.g_workorder_sn ");
                SqlStringBuilder.Append("                       WHERE    serial_number = @serial_number ");
                SqlStringBuilder.Append("                     ) ");
                SqlStringBuilder.Append("        AND c.part_location NOT IN ('01','02') ");
                SqlStringBuilder.Append("ORDER BY a.ruid ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@serial_number", serialNumber);

                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), Params);

                return dt;
            }
        }

        /// <summary>
        /// 根据零件号 
        /// </summary>
        /// <param name="partNO">零件号 或 客户零件号</param>
        /// <returns>零件编号</returns>
        public int GetPartID(string partNO)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "SELECT  model_id FROM dbo.sys_part WHERE part_no=@part_no AND enabled='Y' AND part_type=2";

                Hashtable Params = new Hashtable(1);

                Params.Add("@part_no", partNO);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        ///// <summary>
        ///// 查询配置名称
        ///// </summary>
        ///// <param name="model_name"></param>
        ///// <returns></returns>
        //public int GetModelName(string model_name)
        //{
        //    lock (_remotingProxy)
        //    {
        //        string SqlString =
        //            "SELECT TOP 1 model_id FROM dbo.sys_model WHERE model_name=@model_name AND enabled='Y'";

        //        Hashtable Params = new Hashtable(1);

        //        Params.Add("@model_name", model_name);

        //        object obj = _remotingProxy.GetScalar(SqlString, Params);

        //        return obj == null ? 0 : Convert.ToInt32(obj);
        //    }
        //}

        /// <summary>
        ///查询客户产线
        /// </summary>
        /// <param name="partNO">零件号 或 客户零件号</param>
        /// <returns>零件编号</returns>
        public string GetCustPdline(string custPdline)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "SELECT TOP 1 cust_pdline_name FROM dbo.sys_cust_pdline WHERE cust_pdline_id=@custPdline AND enabled='Y'";

                Hashtable Params = new Hashtable(1);

                Params.Add("@custPdline", custPdline);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? "" : obj.ToString();
            }
        }

        /// <summary>
        ///查询班次
        /// </summary>
        /// <param name="partNO">零件号 或 客户零件号</param>
        /// <returns>零件编号</returns>
        public int GetShiftId(string shiftName)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "SELECT TOP 1 shift_id FROM dbo.sys_shift WHERE shift_code=@shiftName AND enabled='Y'";

                Hashtable Params = new Hashtable(1);

                Params.Add("@shiftName", shiftName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        ///生产类型查询
        /// </summary>
        /// <param name="partNO"></param>
        /// <returns></returns>
        public int GetProdTypeId(string ProdTypeName)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "SELECT TOP 1 enum_id FROM dbo.sys_enum WHERE enum_type='sys_prod_type' AND enum_value=@ProdTypeName";

                Hashtable Params = new Hashtable(1);

                Params.Add("@ProdTypeName", ProdTypeName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        ///生产类型查询
        /// </summary>
        /// <param name="partNO"></param>
        /// <returns></returns>
        public int GetCustPdlinePart(string partNo, string cust_pdline)
        {
            lock (_remotingProxy)
            {
                StringBuilder querySen = new StringBuilder(1024);
                querySen.AppendLine("SELECT TOP 1 1 FROM dbo.sys_part AS a  ");
                querySen.AppendLine("LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id  ");
                querySen.AppendLine("LEFT JOIN dbo.sys_model_type AS c ON b.model_type_id=c.type_id  ");
                querySen.AppendLine("LEFT JOIN dbo.sys_cust_pdline AS d ON c.cust_pdline_id=d.cust_pdline_id  ");
                querySen.AppendLine(" WHERE part_no=@partNo AND c.cust_pdline_id=@cust_pdline  ");


                Hashtable Params = new Hashtable(2);

                Params.Add("@partNo", partNo);
                Params.Add("@cust_pdline", cust_pdline);

                object obj = _remotingProxy.GetScalar(querySen.ToString(), Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }



        /// <summary>
        ///查询数据是否已经导入
        /// </summary>
        /// <param name="partNO"></param>
        /// <returns></returns>
        public int GetOrderNo(string partNo, string cust_pdline, string orderData, string batchNumber, string shiftName, string ProdType)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "SELECT TOP 1 1 FROM dbo.g_stock_order WHERE shift_id=@shiftName AND model_id=@partNo AND type=@ProdType AND batch_number=@batchNumber AND cust_pdline=@cust_pdline AND document_data=@orderData";

                Hashtable Params = new Hashtable(1);

                Params.Add("@partNo", partNo);
                Params.Add("@cust_pdline", cust_pdline);
                Params.Add("@orderData", orderData);
                Params.Add("@batchNumber", batchNumber);
                Params.Add("@shiftName", shiftName);
                Params.Add("@ProdType", ProdType);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getProductionOrderListByPage(int PageSize, int PageIndex, string OrderBy, string stageId)
        {
            lock (_remotingProxy)
            {
                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));

                StringBuilder sb = new StringBuilder();
                sb.Append(" dbo.g_workorder_detail AS a");
                sb.Append(" LEFT JOIN dbo.g_workorder AS d ON a.workorder_id=d.ruid LEFT JOIN dbo.sys_part AS e ON d.part_id=e.part_id");
                sb.Append(" LEFT JOIN dbo.g_workorder_sn AS f ON a.ruid=f.wo_detail_id");
                sb.Append(" LEFT JOIN dbo.g_sn_status AS g ON f.serial_number=g.serial_number");
                sb.Append(" LEFT JOIN dbo.g_workorder AS w ON w.ruid = a.workorder_id");
                sb.Append(" LEFT JOIN dbo.g_pdline_relation AS r ON r.pdline_id = w.pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_cust_pdline AS p ON p.cust_pdline_id = r.cust_pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_part AS b ON a.part_id = b.part_id   ");
                sb.Append(" LEFT JOIN dbo.g_workorder_seq AS c ON a.ruid = c.workorder_id   ");
                sb.Append("  LEFT JOIN dbo.sys_enum AS i ON i.enum_value = b.part_location   ");
                sb.Append("                                 AND i.enum_type = 'sys_part_location'   ");
                sb.Append("                                AND i.enabled = 'Y'   ");
                sb.Append(" LEFT JOIN dbo.g_stage_location AS h ON h.enum_id = i.enum_id   ");

                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", sb.ToString()));

                StringBuilder sbColumn = new StringBuilder();
                sbColumn.Append(" ROW_NUMBER() OVER ( ORDER BY a.seq ) AS a ,");
                sbColumn.Append(" a.ruid ,");
                sbColumn.Append(" c.seq ,");
                sbColumn.Append(" a.workorder_id ,");
                sbColumn.Append(" e.part_no AS modelNo,");
                sbColumn.Append(" b.part_no ,");
                sbColumn.Append(" b.part_spec ,");
                sbColumn.Append(" p.cust_pdline_desc ,");
                sbColumn.Append(" a.type ,");
                sbColumn.Append(" a.create_ymd ,");
                sbColumn.Append(" a.create_hms ,");
                sbColumn.Append(" g.serial_number ,");
                sbColumn.Append(" w.car_no,");
                sbColumn.Append(" g.work_flag");

                String strWhere = " c.stage_id = " + stageId ;
                strWhere += " AND h.stage_id = " + stageId;
                strWhere += " AND  a.create_ymd>(SELECT CONVERT(VARCHAR(10), GETDATE()- 15, 120))";
                strWhere += " AND a.enabled='Y'";
                strWhere += " AND c.seq > ( SELECT    MAX(wo_detail_seq)";
                strWhere += " FROM      dbo.sys_terminal AS a";
                strWhere += " LEFT JOIN dbo.sys_terminal_point AS b ON a.terminal_id = b.terminal_id";
                strWhere += " WHERE     stage_id = " + stageId + ")";

                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", sbColumn.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere));

                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());

                return result;
            }
        }
    }
}