using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DataLinkMesWork;
using SystemFramework;

namespace bizFacade
{
    public partial class BaseDataSystemMES
    {
        /// <summary>
        /// 根据工位号
        /// </summary>
        /// <param name="positionID"></param>
        /// <returns></returns>
        public static string  GetAllWorkPositionNameForMIS(string positionID)
        {
            string positionIDName = "";
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT top 1  工位名称 FROM MES_计划BOM_工位与名称 "
                + "where  工位号='" + positionID + "'";
            if (!SQLCommon.SqlServerConnectionStatus)
            {
                SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            }
            else
            {
                DataAccess.ExecuteDataTable(sql, out  dt);
            }
            if (dt == null) return positionIDName;
            if (dt.Rows.Count > 0)
            {
                positionIDName = dt.Rows[0]["工位名称"].ToString();
            }
            return positionIDName;
            
        }
        /// <summary>
        /// 根据工位号获得工位名称
        /// </summary>
        /// <param name="positionID"></param>
        /// <returns></returns>
        public static string GetWorkPositionNameByWorkPositionID(string positionID)
        {
            string positionIDName = "";
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT top 1  工位名称 FROM MES_计划BOM_工位与名称 "
                + "where  工位号='" + positionID + "'";
            if (!SQLCommon.SqlServerConnectionStatus)
            {
                SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            }
            else
            {
                DataAccess.ExecuteDataTable(sql, out  dt);
            }
            if (dt == null) return positionIDName;
            if (dt.Rows.Count > 0)
            {
                positionIDName = dt.Rows[0]["工位名称"].ToString();
            }
            return positionIDName;

        }
        /// <summary>
        /// 根据订单号获得订单信息
        /// </summary>
        /// <param name="OrderForm">订单号</param>
        /// <returns></returns>
        public static DataTable GetOrderFormPlanByOrderForm(string OrderForm)
        {
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT TOP 1 * FROM [MES_计划BOM_订单计划下达] "
                + "where 订单号='" + OrderForm + "'";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            MaterialData.dt_OrderFormPlan = dt;
            return dt;
        }     
        /// <summary>
        /// 根据订单号获得工位基本操作指导信息
        /// </summary>
        /// <param name="OpName">工位号</param>
        /// <param name="OrderForm">订单号</param>
        /// <returns></returns>
        public static DataTable GetWorkPositionGuidByOrderForm(string OrderForm,string OpName)
        {
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT TOP 1 工位操作指导,工位指导图片 FROM [MES_计划BOM_订单计划下达] "
                + "where 工位号='" + OpName + "'"
                + " and 订单号='" + OrderForm + "'"
               + " order by 导入时间 desc ";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);

            return dt;
        }
        /// <summary>
        /// 根据发动机号获得装配指南信息
        /// </summary>
        /// <param name="orderForm"></param>
        /// <param name="OpName"></param>
        /// <returns></returns>
        public static DataTable GetAssemblyListByEngineNumber(string orderForm, string OpName)
        {
            string errorMessage;
            DataTable dt;
            string sql;
            //sql = "SELECT  * "
            sql = "SELECT [ID],[订单号], [SAP物料号], [发动机型号],  [发动机型号代码], [发动机机型]," +
               "[发动机数量], [厂牌编号起始号], [客户名称], [投料日期], [文件编号], [集团名称], [通知号], " +
               "[功率], [指南文件编号], [装机状态], [适用状态], [物料描述],[计划下达日期], [计划完工日期]," +
               "[主配厂家],[工位号], [工位名称], [物料号], [零件图号], [零件名称], [零件数量],[是否扫描],d.扫描方式代码,d.扫描方式,[厂家代码], [生产厂家], " +
               "[操作指导],'' as 发动机号,0 as 物料号表索引号,0 as 零件数量验证,"
            + "'' as 物料条码,'' as 物料条码物料号,'' as 物料条码批次,0 as 验证结果,"
            + "'' as 操作者工号,'' as 操作者姓名,'' as 班次代码,"
            + "b.物料来源代码, b.物料来源, a.是否禁用代码, a.是否禁用,被强制验证物料号,更改代码,更改原因 "
                + " FROM MES_计划BOM_订单计划下达_扫描方式 AS d INNER JOIN (MES_计划BOM_订单计划下达_是否禁用 AS a INNER JOIN (MES_计划BOM_订单计划下达_物料来源 AS b INNER JOIN MES_计划BOM_订单计划下达 AS c ON b.物料来源代码 = c.物料来源代码) ON a.是否禁用代码 = c.是否禁用代码) ON c.扫描方式=d.扫描方式代码 "
                + "where 订单号='" + orderForm + "'"
                + "and 工位号='" + OpName + "' and a.是否禁用代码 = 0 "
                + " ORDER BY 物料号 ";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            return dt;
        }
        /// <summary>
        /// 根据发动机号获得装配指南信息
        /// </summary>
        /// <param name="orderForm"></param>
        /// <param name="OpName"></param>
        /// <returns></returns>
        public static DataTable GetAssemblyListForVerifyByEngineNumber(string orderForm, string OpName)
        {
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT [ID], [订单号], [SAP物料号], [发动机型号],  [发动机型号代码], [发动机机型]," +
                  "[发动机数量], [厂牌编号起始号], [客户名称], [投料日期], [文件编号], [集团名称], [通知号], " +
                  "[功率], [指南文件编号], [装机状态], [适用状态], [物料描述],[计划下达日期], [计划完工日期]," +
                  "[主配厂家],[工位号], [工位名称], [物料号], [零件图号], [零件名称], [零件数量],[是否扫描],d.扫描方式代码,d.扫描方式,[厂家代码], [生产厂家], " +
                  "[操作指导],'' as 发动机号,'' as 追踪代码,0 as 物料号表索引号,0 as 零件数量验证,"
               + "'' as 物料条码,'' as 物料条码物料号,'' as 物料条码批次,0 as 验证结果,"
               + "'' as 操作者工号,'' as 操作者姓名,'' as 班次代码,"
               + "b.物料来源代码, b.物料来源, a.是否禁用代码, a.是否禁用,被强制验证物料号,更改代码,更改原因 "
                   + " FROM MES_计划BOM_订单计划下达_扫描方式 AS d INNER JOIN (MES_计划BOM_订单计划下达_是否禁用 AS a INNER JOIN (MES_计划BOM_订单计划下达_物料来源 AS b INNER JOIN MES_计划BOM_订单计划下达 AS c ON b.物料来源代码 = c.物料来源代码) ON a.是否禁用代码 = c.是否禁用代码) ON c.扫描方式=d.扫描方式代码 "
                   + "where 订单号='" + orderForm + "'"
                   + "and 工位号='" + OpName + "' and a.是否禁用代码 = 0 and 是否扫描 = 1 "
                   + " ORDER BY 物料号 ";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            return dt;
        }

        /// <summary>
        /// 根据发动机号获得装配指南信息
        /// </summary>
        /// <param name="orderForm"></param>
        /// <param name="OpName"></param>
        /// <returns></returns>
        public static DataTable GetAssemblyListForVerifyByEngineNumber_F3(string orderForm, string OpName)
        {
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT [ID], [订单号], [SAP物料号], [发动机型号],  [发动机型号代码], [发动机机型]," +
                  "[发动机数量], [厂牌编号起始号], [客户名称], [投料日期], [文件编号], [集团名称], [通知号], " +
                  "[功率], [指南文件编号], [装机状态], [适用状态], [物料描述],[计划下达日期], [计划完工日期]," +
                  "[主配厂家],[工位号], [工位名称], [物料号], [零件图号], [零件名称], [零件数量],[是否扫描],d.扫描方式代码,d.扫描方式,[厂家代码], [生产厂家], " +
                  "[操作指导],'' as 发动机号,'' as 追踪代码,0 as 物料号表索引号,0 as 零件数量验证,"
               + "'' as 物料条码,'' as 物料条码物料号,'' as 物料条码批次,0 as 验证结果,"
               + "'' as 操作者工号,'' as 操作者姓名,'' as 班次代码,"
               + "b.物料来源代码, b.物料来源, a.是否禁用代码, a.是否禁用,被强制验证物料号,更改代码,更改原因 "
                   + " FROM MES_计划BOM_订单计划下达_扫描方式 AS d INNER JOIN (MES_计划BOM_订单计划下达_是否禁用 AS a INNER JOIN (MES_计划BOM_订单计划下达_物料来源 AS b INNER JOIN MES_计划BOM_订单计划下达 AS c ON b.物料来源代码 = c.物料来源代码) ON a.是否禁用代码 = c.是否禁用代码) ON c.扫描方式=d.扫描方式代码 "
                   + "where 订单号='" + orderForm + "'"
                   + "and 工位号='" + OpName + "' and a.是否禁用代码 = 0 and 是否扫描 = 2 "//活塞连杆
                   + " ORDER BY 物料号 ";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            return dt;
        }   
        ///// <summary>
        ///// =1  与Access数据库连接   =0 与SQL Server 连接
        ///// </summary>
        //static int OpcServerIsAccess = 1;
        /// <summary>
        /// 从计划中获得发动机信息
        /// </summary>
        public static DataTable PruducePlanGetEngineNumber()
        {
            string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT  top 1  * FROM [MES_计划BOM_发动机号] order by 投料日期,发动机号";
            SQLCommon.ExecuteDataTable_OleDb(sql, ApplicationConfig.ConnectionString_Access, out dt, out errorMessage);
            MaterialData.dt_EngineNumber = dt;

            return dt;
        }

        /// <summary>
        /// 工位基本指导图片
        /// </summary>
        /// <param name="OpName">工位号</param>
        /// <returns></returns>
        public static DataTable GetWorkPositionBasicGuidPictureByOpName(string OpName)
        {
            //string errorMessage;
            DataTable dt;
            string sql;
            sql = "SELECT TOP 1 工位基本指导图片 FROM [MES_计划BOM_工位基本指导] "
                + "where 工位号='" + OpName + "'";
            DataAccess.ExecuteDataTable(sql, out  dt);
            //BizDataAccess.DataAccess.ExecuteDataTable_OleDb_Access(sql, out  dt);

            return dt;
        }

    }
}