everlight.eap/backend/DllEapDal/MesDal.cs

using Cksoft.Data;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.OracleClient;
namespace DllEapDal
{
    public class MesDal
    {
        ///// <summary>
        ///// 根据lot编号和机台编号，读取程序名称
        ///// </summary>
        ///// <param name="lot"></param>
        ///// <param name="maccode"></param>
        ///// <param name="errorinfo"></param>
        ///// <returns></returns>
        //public  string GetLotProgramName(string lot, ref string errorinfo)
        //{
        //    try
        //    {
        //        //return "12NE152-P-0-205-TP47";
        //        //读取程序名称
        //        string sqlstr = $@"select a.simtype,a.value,c.dt_description from FWCATNS_SIMS_PID a
        //            inner join FWCATNS_STG_CONTROLLOT b on a.pid=b.sourcedevice and a.diepart=b.diepart
        //            left outer join FWCATNS_AS_DEVICETOOLING c on a.pid=c.dt_device and a.diepart=c.dt_diepart and a.simtype=c.dt_tooltype
        //            where   b.controllotid='{lot}' and a.simtype in('MTP','TTP','BackGrindProgram')";
        //        DataTable tempdt = QueryFromMes(sqlstr, ref errorinfo);
        //        //读取
        //        DataRow[] temprows = tempdt.Select("simtype='BackGrindProgram'");
        //        if(temprows.Length<=0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的程序名称。";
        //            return "";
        //        }
        //        string programname = temprows[0]["value"].ToString();
        //        temprows = tempdt.Select("simtype='TTP'");
        //        if (temprows.Length <= 0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的TTP信息。";
        //            return "";
        //        }
        //        programname = $"{programname}-{temprows[0]["dt_description"].ToString()}";
        //        temprows = tempdt.Select("simtype='MTP'");
        //        if (temprows.Length <= 0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的MTP信息。";
        //            return "";
        //        }

        //        if(temprows[0]["value"].ToString().Substring(0,1)=="1")
        //        {
        //            programname = $"{programname}-F";
        //        }
        //        else
        //        {
        //            programname = $"{programname}-TP";
        //        }
        //        string[] tts = temprows[0]["value"].ToString().Split('-');
        //        programname = $"{programname}{int.Parse(tts[tts.Length-1])}";
        //        return programname;
        //    }
        //    catch (Exception ex)
        //    {
        //        errorinfo = ex.Message.ToString();
        //        return "";
        //    }
        //}
        /// <summary>
        /// 根据lot编号和机台编号，读取程序名称
        /// </summary>
        /// <param name="lot"></param>
        /// <param name="maccode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        //public string GetLotProgramName(string lot, ref string errorinfo)
        //{
        //    try
        //    {
        //        //return "12NE250-P-1-0059-0048";
        //        //读取程序名称
        //        //string sqlstr = $@"select a.simtype,a.value from FWCATNS_SIMS_PID a
        //        //    inner join FWCATNS_STG_CONTROLLOT b on a.pid=b.sourcedevice and a.diepart=b.diepart
        //        //    where   b.controllotid='{lot}' and a.simtype in('MTP','TTP','BackGrindProgram')";
        //        string sqlstr = $@"select a.simtype,a.value,b.diepart,b.sourcedevice from FWCATNS_SIMS_PID a
        //            inner join FWCATNS_STG_CONTROLLOT b on a.pid=b.sourcedevice and a.diepart=b.diepart
        //            where   b.controllotid='{lot}' and a.simtype ='BackGrindProgram'";
        //        DataTable tempdt = QueryFromMes(sqlstr, ref errorinfo);
        //        if(tempdt==null)
        //        {
        //            return "";
        //        }
        //        //读取
        //        DataRow[] temprows = tempdt.Select("simtype='BackGrindProgram'");
        //        if (temprows.Length <= 0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的程序名称。";
        //            return "";
        //        }
        //        string programname = temprows[0]["value"].ToString();
        //        string diepart= temprows[0]["diepart"].ToString();
        //        string sourcedevice = temprows[0]["sourcedevice"].ToString();

        //        //sqlstr = $@"select a.dt_toolmodel value,a.dt_tooltype simtype from FWCATNS_AS_DEVICETOOLING a
        //        //    inner join FWCATNS_STG_CONTROLLOT b on a.dt_device=b.sourcedevice and a.dt_diepart=b.diepart
        //        //    where b.controllotid='{lot}' and a.dt_tooltype in('MTP','TTP')";
        //        sqlstr = $@"select a.dt_toolmodel value,a.dt_tooltype simtype from FWCATNS_AS_DEVICETOOLING a
        //            inner join FWCATNS_STG_CONTROLLOT b on a.dt_device=b.sourcedevice and a.dt_diepart=b.diepart
        //            where b.controllotid='{lot}' and a.dt_tooltype ='TTP'";
        //        tempdt = QueryFromMes(sqlstr, ref errorinfo);
        //        if (tempdt == null)
        //        {
        //            return "";
        //        }
        //        temprows = tempdt.Select("simtype='TTP'");
        //        if (temprows.Length <= 0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的TTP信息。";
        //            return "";
        //        }
        //        programname = $"{programname}-{temprows[0]["value"].ToString().Substring(temprows[0]["value"].ToString().Length-4, 4)}";

        //        sqlstr = $@"SELECT   MAX (A.MATPRODUCTID) mtp
        //               FROM   MACMATERIALPRODUCT A, MACMATERIALPRODUCT_PN2M B
        //              WHERE       A.SYSID = B.FROMID
        //                      AND B.KEYDATA = 'IsTooling'
        //                      AND B.VALDATA = 'Y'
        //                      AND A.MATPRODUCTID IN
        //                               (SELECT   B.CHILDMATPRODUCT
        //                                  FROM   MACMATERIALPRODUCT A,
        //                                         MACMBOMLINK B,
        //                                         FWCATNS_AS_SPEC C
        //                                 WHERE       A.TYPE = 'EPXY'
        //                                         AND A.MATPRODUCTID = B.CHILDMATPRODUCT
        //                                         AND B.MATPRODUCTID = C.SPE_BOM
        //                                         AND C.SPE_DEVICE = '{sourcedevice}'
        //                                         AND C.SPE_DIEPART = '{diepart}')";

        //        tempdt = QueryFromMes(sqlstr, ref errorinfo);
        //        if (tempdt == null)
        //        {
        //            return "";
        //        }
        //        if (tempdt.Rows.Count <= 0)
        //        {
        //            errorinfo = $"未找到lot【{lot}】对应的MTP信息。";
        //            return "";
        //        }
        //        programname = $"{programname}-{tempdt.Rows[0]["mtp"].ToString().Substring(tempdt.Rows[0]["mtp"].ToString().Length - 4, 4)}";
        //        //if (temprows[0]["value"].ToString().Substring(0, 1) == "1")
        //        //{
        //        //    programname = $"{programname}-F";
        //        //}
        //        //else
        //        //{
        //        //    programname = $"{programname}-TP";
        //        //}
        //        //string[] tts = temprows[0]["value"].ToString().Split('-');
        //        //programname = $"{programname}{int.Parse(tts[tts.Length - 1])}";
        //        return programname;
        //    }
        //    catch (Exception ex)
        //    {
        //        errorinfo = "函数名称[GetLotProgramName01]" + ex.Message.ToString();
        //        return "";
        //    }
        //}
        public string GetLotProgramName(string lot, ref string errorinfo)
        {
            try
            {
                //return "12NE250-P-1-0059-0048";
                string sqlstr = $@"select a.simtype,a.value,b.diepart,b.sourcedevice from FWCATNS_SIMS_PID a
                    inner join FWCATNS_STG_CONTROLLOT b on a.pid=b.sourcedevice and a.diepart=b.diepart
                    where   b.controllotid='{lot}' and a.simtype  in('BackGrindProgram','MTP','TTP')";
                DataTable tempdt = QueryFromMes(sqlstr, ref errorinfo);
                if (tempdt == null)
                {
                    return "";
                }
                //读取
                DataRow[] temprows = tempdt.Select("simtype='BackGrindProgram'");
                if (temprows.Length <= 0)
                {
                    errorinfo = $"未找到lot【{lot}】对应的程序名称。";
                    return "";
                }
                string programname = temprows[0]["value"].ToString();
                string diepart = temprows[0]["diepart"].ToString();
                string sourcedevice = temprows[0]["sourcedevice"].ToString();
                temprows = tempdt.Select("simtype='TTP'");
                if (temprows.Length > 0&&temprows[0]["value"].ToString().Length>=4)
                {
                    programname = $"{programname}-{temprows[0]["value"].ToString().Substring(temprows[0]["value"].ToString().Length - 4, 4)}";
                }
                else
                {
                    sqlstr = $@"select a.dt_toolmodel value,a.dt_tooltype simtype from FWCATNS_AS_DEVICETOOLING a
                    inner join FWCATNS_STG_CONTROLLOT b on a.dt_device=b.sourcedevice and a.dt_diepart=b.diepart
                    where b.controllotid='{lot}' and a.dt_tooltype ='TTP'";
                    DataTable subtempdt = QueryFromMes(sqlstr, ref errorinfo);
                    if (subtempdt == null)
                    {
                        return "";
                    }
                    if (subtempdt.Rows.Count <= 0)
                    {
                        errorinfo = $"未找到lot【{lot}】对应的TTP信息。";
                        return "";
                    }
                    programname = $"{programname}-{subtempdt.Rows[0]["value"].ToString().Substring(subtempdt.Rows[0]["value"].ToString().Length - 4, 4)}";
                }
                temprows = tempdt.Select("simtype='MTP'");
                if (temprows.Length > 0 && temprows[0]["value"].ToString().Length >= 4)
                {
                    programname = $"{programname}-{temprows[0]["value"].ToString().Substring(temprows[0]["value"].ToString().Length - 4, 4)}";
                }
                else
                {
                    sqlstr = $@"SELECT   MAX (A.MATPRODUCTID) mtp
                       FROM   MACMATERIALPRODUCT A, MACMATERIALPRODUCT_PN2M B
                      WHERE       A.SYSID = B.FROMID
                              AND B.KEYDATA = 'IsTooling'
                              AND B.VALDATA = 'Y'
                              AND A.MATPRODUCTID IN
                                       (SELECT   B.CHILDMATPRODUCT
                                          FROM   MACMATERIALPRODUCT A,
                                                 MACMBOMLINK B,
                                                 FWCATNS_AS_SPEC C
                                         WHERE       A.TYPE = 'EPXY'
                                                 AND A.MATPRODUCTID = B.CHILDMATPRODUCT
                                                 AND B.MATPRODUCTID = C.SPE_BOM
                                                 AND C.SPE_DEVICE = '{sourcedevice}'
                                                 AND C.SPE_DIEPART = '{diepart}')";

                    DataTable subtempdt = QueryFromMes(sqlstr, ref errorinfo);
                    if (subtempdt == null)
                    {
                        return "";
                    }
                    if (subtempdt.Rows.Count <= 0)
                    {
                        errorinfo = $"未找到lot【{lot}】对应的MTP信息。";
                        return "";
                    }
                    programname = $"{programname}-{subtempdt.Rows[0]["mtp"].ToString().Substring(subtempdt.Rows[0]["mtp"].ToString().Length - 4, 4)}";
                }
                return programname;
            }
            catch (Exception ex)
            {
                errorinfo = "函数名称[GetLotProgramName01]" + ex.Message.ToString();
                return "";
            }
        }
        public string GetLotProgramNameForMac(string maccode, ref string errorinfo)
        {
            try
            {
                //return "12NE152-P-0-205-TP47";
                //读取程序名称
                string sqlstr = $@"select a.appid from FWLOT a
                    where a.lastlocation='{maccode}' and a.processingstatus='Active'
                    order by a.startdate desc,a.starttime desc";
                DataTable tempdt = QueryFromMes(sqlstr, ref errorinfo);
                if(tempdt.Rows.Count<=0)
                {
                    errorinfo = "未找到机台对应的批号。";
                    return "";
                }
                string controlid = tempdt.Rows[0]["appid"].ToString();
                return GetLotProgramName(controlid,ref errorinfo);
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return "";
            }
        }
        /// <summary>
        /// 根据lot编号，获取lot对应的机台编号和程序名称
        /// </summary>
        /// <param name="lot"></param>
        /// <param name="maccode">机台编号</param>
        /// <param name="errorinfo"></param>
        /// <returns>程序名称</returns>

        private DataTable QueryFromMes(string sqlstr, ref string errorinfo)
        {
            return QueryFromMes(sqlstr, "temp", ref errorinfo);
        }
        private DataTable QueryFromMes(string sqlstr, string tablename, ref string errorinfo)
        {
            OracleConnection conn = null;
            try
            {
                string connstr = AppConfigurtaionServices.Configuration["mesdb:ConnectionStrings"];
                conn = new OracleConnection(connstr);
                OracleDataAdapter oda = new OracleDataAdapter(sqlstr, conn);
                DataTable redt = new DataTable(tablename);
                oda.Fill(redt);
                return redt;
            }
            catch (Exception ex)
            {
                errorinfo = "函数名称[QueryFromMes]" + ex.Message.ToString();
                return null;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }

        public int DownloadProgram(string lot,string mac, ref string errorinfo)
        {
            IDatabase db = null;
            try
            {
                //调程
                //db = DbFactory.Base("eap");
                //db.BeginTrans();
                //string programname = GetLotProgramName(lot, ref errorinfo);
                //if (programname == "")
                //{
                //    errorinfo = $"从MES库中获取程序名称出错：{errorinfo}，lot={lot}";
                //    MacOrderSendDal senddal = new MacOrderSendDal(db);
                //    string temperrorinfo = "";
                //    senddal.SendS10F3(mac, $"Read recipe from MES error [{lot}]", ref temperrorinfo);
                //    return -1;
                //}
                //errorinfo = "";
                //ProgramDal dal = new ProgramDal(db);
                //int result = dal.DownloadProgram(mac, programname, lot, ref errorinfo);
                //if (result <= 0)
                //{
                //    db.Rollback();
                //    MacOrderSendDal senddal = new MacOrderSendDal(db);
                //    string temperrorinfo = "";
                //    senddal.SendS10F3(mac, $"Can not found recipe[{programname}]", ref temperrorinfo);
                //    return -1;
                //}
                //db.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                db.Rollback();
                errorinfo = ex.Message.ToString();
                return -1;
            }
            finally
            {
                if (db != null)
                    db.Close();
            }
        }
    }
}