nancy ConvertDB


![スクリーンショット 2020-04-02 0.16.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/588727/18fcf0e2-280c-bd51-44ae-d44910cfbc05.png)


using System;
using System.Collections.Generic;

namespace ConvertDB
{
    public class ExColumn
    {
        public enum COLUMN_TYPE
        {
            UNDEFINED = 0,
            DATETYPE = 0,
            MEMO = 1,
            NUMERIC = 2,
        }
        private Dictionary<string, COLUMN_TYPE> hsENM_Type;
        private Dictionary<int, string> hsENM_Col_Num;
        private Dictionary<string, int> hsENM_Num_Col;
        private Dictionary<string, object> hsENM_ColNM_SUM;

        public ExColumn()
        {
            hsENM_Type = new Dictionary<string, COLUMN_TYPE>();
            hsENM_Col_Num = new Dictionary<int, string>();
            hsENM_Num_Col = new Dictionary<string, int>();
            hsENM_ColNM_SUM = new Dictionary<string, object>();

            init_hsENM_ColNM_SUM();
        }

        public void SetColumn(int id, string name, COLUMN_TYPE type)
        {

            hsENM_Num_Col.Add(name, id);
            hsENM_Type.Add(name, type);
            hsENM_Col_Num.Add(id, name);
        }
        private void init_hsENM_ColNM_SUM()
        {
            try
            {
                foreach (var key in hsENM_Col_Num.Keys)
                {
                    hsENM_ColNM_SUM.Add(hsENM_Col_Num[key].ToString(), 0);
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void ColumnSumPlus(string key, double val)
        {
            try
            {
                if (hsENM_ColNM_SUM.ContainsKey(key))
                {
                    double tempVal = (double)hsENM_ColNM_SUM[key];
                    hsENM_ColNM_SUM.Remove(key);
                    hsENM_ColNM_SUM.Add(key, tempVal + val);
                }
                else
                {
                    hsENM_ColNM_SUM.Remove(key);
                    hsENM_ColNM_SUM.Add(key, val);
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public double GetColumnSum(string key)
        {
            try
            {
                if (hsENM_ColNM_SUM.ContainsKey(key))
                {
                    return (double)hsENM_ColNM_SUM[key];
                }

                return 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void ResetColumnSum(string key)
        {
            try
            {
                if (hsENM_ColNM_SUM.ContainsKey(key))
                {
                    double tempVal = 0;
                    hsENM_ColNM_SUM.Remove(key);
                    hsENM_ColNM_SUM.Add(key, tempVal);
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public COLUMN_TYPE GetColType(string ColumnName)
        {
            return hsENM_Type[ColumnName];
        }
        public COLUMN_TYPE GetColType(int ColumnID)
        {
            if (hsENM_Col_Num.ContainsKey(ColumnID))
            {
                return hsENM_Type[hsENM_Col_Num[ColumnID].ToString()];
            }
            return COLUMN_TYPE.UNDEFINED;
        }
        public string ColID2Name(int key)
        {
            return hsENM_Col_Num[key];
        }
        public int ColName2ColID(string key)
        {
            return hsENM_Num_Col[key];
        }
        public int GetMAX_COLMNID()
        {
            int Max = 0;
            foreach (var val in hsENM_Col_Num.Keys)
            {
                if (Max < val) { Max = val; }
            }
            return Max;
        }
        public List<string> GetColumnNames()
        {
            List<string> arrNames = new List<string>();
            foreach (string item in hsENM_Num_Col.Keys)
            {
                arrNames.Add(item);
            }
            return arrNames;
        }
    }
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace ConvertDB
{
    public class ExcelConnect : IDisposable
    {
        private const string DB_DATE_STYLE = "yyyy/MM/dd hh:mm:ss";
        public const string EXCELVARCHAR = "memo";
        public const string EXCEL_EXT_2007 = ".xlsx";
        public const string EXCEL_EXT_200X = ".xls";
       
        private DataSet m_dataSet = new DataSet();

        public void Dispose()
        {
            //実装予定
        }
        public DataTable LoadTableFromExcel(string sql, string newTableName, string filename)
        {

            using (OleDbConnection conn = GetOledbconnet(filename))
            {
                using (OleDbCommand command = conn.CreateCommand())
                {
                    var table = new DataTable();
                    command.CommandText = sql;
                    var ds = new DataSet();

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        table.Load(reader);
                        table.TableName = newTableName;
                    }
                    return table;
                }
            }
        }
        private OleDbConnection GetOledbconnet(string strFileName)
        {
            string strExt;
            string strProvider;
            string strDataSource;
            string strExtProperties;

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            DbConnectionStringBuilder builder = factory.CreateConnectionStringBuilder();

            strExt = Path.GetExtension(strFileName);

            if (strExt == EXCEL_EXT_2007)
            {
                strProvider = @"Microsoft.ACE.OLEDB.12.0";
                strDataSource = strFileName;
                strExtProperties = "Excel 12.0 Xml;HDR=YES";
            }
            else if (strExt == EXCEL_EXT_200X)
            {
                strProvider = "Microsoft.Jet.OLEDB.4.0";
                strDataSource = strFileName;
                strExtProperties = "Excel 8.0;HDR=Yes";
            }
            else if (strExt == ".accdb")
            {
                strProvider = " Microsoft.ACE.OLEDB.12.0";
                strDataSource = strFileName;
                strExtProperties = "Persist Security Info = False;";
            }
            else if (strExt == ".mdb")
            {
                strProvider = " Microsoft.ACE.OLEDB.12.0";
                strDataSource = strFileName;
                strExtProperties = "Persist Security Info = False;";
            }
            else
            {
                strProvider = @"Microsoft.ACE.OLEDB.12.0";
                strDataSource = Path.GetDirectoryName(strFileName);
                strExtProperties = "Text;FMT=Delimited;HDR=Yes";
            }

            builder["Provider"] = strProvider;
            builder["Data Source"] = strDataSource;
            builder["Extended Properties"] = strExtProperties;

            OleDbConnection conn = new OleDbConnection(builder.ToString());

            conn.Open();

            return conn;

            //Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\myFolder\myAccessFile.accdb;
            //Jet OLEDB:Database Password = MyDbPassword;
            //Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\myFolder\myAccessFile.mdb;
            //Jet OLEDB:Database Password = MyDbPassword;
            //Persist Security Info = False;
        }

        public void ExcelOut(string strFileName, DataSet ds)
        {

            foreach (DataTable dt in ds.Tables)
            {
                ExcelOut(strFileName, dt);
            }
        }
        public void ExcelOut(string strFileName, DataTable dt)
        {
            try
            {
                int shtCnt = 0;
                List<string> copyTableNames = new List<string>();

                if (dt.Rows.Count > 1000)
                {
                    shtCnt = (dt.Rows.Count / 1000);
                    for (int i = 0; i < shtCnt; i++)
                    {
                        copyTableNames.Add(dt.TableName + "_" + i.ToString());
                    }
                }
                else
                {
                    copyTableNames.Add(dt.TableName);
                }

                for (int shtNo = 0; shtNo < copyTableNames.Count; shtNo++)
                {

                    using (OleDbConnection conn = GetOledbconnet(strFileName))
                    {

                        OleDbCommand command = conn.CreateCommand();
                        //string strTable = dt.TableName;
                        string strTable = copyTableNames[shtNo];
                        try
                        {
                            command.CommandText = "Drop table [" + strTable + "]";
                            command.ExecuteNonQuery();
                        }
                        catch
                        {
                            //存在しない場合がある
                        }
                        string strCommand = " create table ";

                        strCommand += "[" + strTable + "]" + "(";

                        for (Int32 i = 0; i < dt.Columns.Count; i++)
                        {
                            string strTypeName = dt.Columns[i].DataType.Name;
                            if (strTypeName == "Integer" | strTypeName == "Int32" || strTypeName == "Decimal" |
                               strTypeName == "Long" | strTypeName == "Double" | strTypeName == "Short")
                            {
                                strCommand += "[" + dt.Columns[i].ColumnName + "] int ";
                            }
                            else if (strTypeName == "DateTime")
                            {
                                strCommand += "[" + dt.Columns[i].ColumnName + "] memo ";
                            }
                            else
                            {
                                strCommand += "[" + dt.Columns[i].ColumnName + "] memo ";
                            }
                            //最後じゃなければ「，」をつける
                            if (i != dt.Columns.Count - 1)
                            {
                                strCommand += ",";
                            }
                        }
                        strCommand += ")";
                        command.CommandText = strCommand;
                        command.ExecuteNonQuery();


                        //データ入力(パラメータマッチング)
                        strCommand = " INSERT INTO " + "[" + strTable + "]" + " VALUES (";
                        for (Int32 i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].DataType == null)
                            {
                                continue;
                            }
                            strCommand += "?";

                            if (i != dt.Columns.Count - 1)
                            {
                                strCommand += ",";
                            }
                        }
                        strCommand += ")";

                        command.CommandText = strCommand;

                        for (int rowno = 0 + (60000 * (shtNo)); rowno < 60000 * (shtNo + 1); rowno++)
                        {
                            //foreach (DataRow dtRow in dt.Rows)
                            //{
                            command.Parameters.Clear();

                            for (Int32 i = 0; i < dt.Columns.Count; i++)
                            {
                                if (dt.Columns[i].DataType.Name == "DateTime")
                                {
                                    try
                                    {
                                        DateTime dTime = (DateTime)dt.Rows[rowno][i];
                                        command.Parameters.AddWithValue(
                                            "@" + i.ToString(), EXCELVARCHAR).Value = dTime.ToString(DB_DATE_STYLE);
                                    }
                                    catch
                                    {
                                        command.Parameters.AddWithValue(
                                            "@" + i.ToString(), EXCELVARCHAR).Value = string.Empty;
                                    }

                                }
                                else if (dt.Columns[i].DataType.Name == "Integer" |
                                         dt.Columns[i].DataType.Name == "Int32" |
                                         dt.Columns[i].DataType.Name == "Decimal" |
                                         dt.Columns[i].DataType.Name == "Long" |
                                         dt.Columns[i].DataType.Name == "Double" |
                                         dt.Columns[i].DataType.Name == "Short")
                                {
                                    command.Parameters.AddWithValue("@" + i.ToString(), "int").Value = dt.Rows[rowno][i];
                                }
                                else
                                {
                                    command.Parameters.AddWithValue("@" + i.ToString(), "text").Value = dt.Rows[rowno][i];
                                }
                            }
                            command.ExecuteNonQuery();

                        }
                    }
                }
            }
            catch
            {
                // 書き込みエラー
            }
        }
    
    }

}

using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Collections.Generic;


namespace ConvertDB
{
    public class LockOracleConn : OracleConn
    {
        private OracleTransaction m_transaction = null;

        public void Commit()
        {
            m_transaction.Commit();

        }
        public void Rollback()
        {
            m_transaction.Rollback();
        }

        public override void Connect()
        {
            base.Connect();
            m_transaction = m_conn.BeginTransaction();
        }
    }
    public class OracleConn : IDisposable
    {

        public OracleConnection m_conn = null;


        private bool disposedValue = false; // 重複する呼び出しを検出するには

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    // TODO: マネージド状態を破棄します (マネージド オブジェクト)。
                    m_conn.Close();

                }

                // TODO: アンマネージド リソース (アンマネージド オブジェクト) を解放し、下のファイナライザーをオーバーライドします。
                // TODO: 大きなフィールドを null に設定します。

                disposedValue = true;
            }
        }

        // TODO: 上の Dispose(bool disposing) にアンマネージド リソースを解放するコードが含まれる場合にのみ、ファイナライザーをオーバーライドします。
        // ~OracleConn() {
        //   // このコードを変更しないでください。クリーンアップ コードを上の Dispose(bool disposing) に記述します。
        //   Dispose(false);
        // }

        // このコードは、破棄可能なパターンを正しく実装できるように追加されました。
        public void Dispose()
        {
            // このコードを変更しないでください。クリーンアップ コードを上の Dispose(bool disposing) に記述します。
            Dispose(true);
            // TODO: 上のファイナライザーがオーバーライドされる場合は、次の行のコメントを解除してください。
            // GC.SuppressFinalize(this);
        }



        public virtual void Connect()
        {
            string strDataSource = "Data Source = " + "NPOPS" + "; User Id = EAST; Password = EAST;";
            m_conn = new OracleConnection(strDataSource);
            m_conn.Open();
        }

        public DataTable ExcuteString(string strSqr)
        {
            try
            {
                var oraCmd = m_conn.CreateCommand();
                oraCmd.CommandText = strSqr;
                oraCmd.CommandType = CommandType.Text;

                var reader = oraCmd.ExecuteReader();

                //Create DataTable from Datareader
                var datatable = new DataTable();
                datatable.Load(reader);

                return datatable;


            }
            catch (OracleException oraEx)
            {
                throw oraEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // (Override) ExcuteString 2/2
        public DataTable ExcuteString(String strSQL, List<OracleParameter> listParam)
        {
            try
            {
                var oraCmd = m_conn.CreateCommand();
                oraCmd.CommandText = strSQL;
                oraCmd.CommandType = System.Data.CommandType.Text;

                foreach (var val in listParam)
                {
                    oraCmd.Parameters.Add(val);
                }

                var reader = oraCmd.ExecuteReader();

                //Create DataTable from Datareader
                DataTable datatable = new DataTable();
                datatable.Load(reader);

                return datatable;


            }
            catch (OracleException oraEx)
            {
                throw oraEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //after creating procedure, we will complete the following: 
        public int ExcuteStoredProcedure(String strProd, List<OracleParameter> param)
        {
            try
            {
                var oraCmd = m_conn.CreateCommand();
                oraCmd.CommandText = strProd;
                oraCmd.CommandType = System.Data.CommandType.StoredProcedure;

                foreach (var item in param)
                {
                    oraCmd.Parameters.Add(item);

                }
                var datatable = new DataTable();
                int ret = oraCmd.ExecuteNonQuery();


                return ret;

            }
            catch (OracleException oraEx)
            {
                throw oraEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConvertDB
{
    class CUtil
    {
        public const string XML_SCH = ".xsd";
        public static string GetSqlFromTXT(string argfileName)
        {
            string text = string.Empty;

            using (FileStream fs = new FileStream(Path.GetFullPath(argfileName), FileMode.Open))
            {
                using (StreamReader sr = new StreamReader(fs, Encoding.GetEncoding("shift-jis")))
                {
                    text += sr.ReadToEnd();
                }
            }

            return text;
        }
        public static void CSVOut(string strFileName, DataTable dt)
        {
            using (StreamWriter sw = new StreamWriter(@strFileName, false, Encoding.GetEncoding("utf-8")))
            {
                string delim = ",";
                string coloumn = string.Empty;
                foreach (var col in dt.Columns)
                {
                    coloumn += "\"" + col.ToString() + "\"";
                }
                sw.WriteLine(coloumn.Replace("\"\"", "\"" + delim + "\""));

                foreach (var rows in dt.AsEnumerable())
                {
                    string oneRow = string.Empty;
                    foreach (var col in dt.Columns)
                    {
                        oneRow += "\"" + rows[col.ToString()].ToString() + "\"";
                    }
                    sw.WriteLine(oneRow.Replace("\"\"", "\"" + delim + "\""));
                }

            }
        }
        public static void XMLOut(string strFileName, DataSet ds)
        {

            using (StreamWriter sr = new StreamWriter(strFileName))
            {
                string schFileName = Path.GetFileNameWithoutExtension(strFileName) + XML_SCH;
                using (StreamWriter sch = new StreamWriter(schFileName))
                {
                    ds.WriteXmlSchema(sch);
                    ds.WriteXml(sr);
                }
            }

        }
        public static DataSet XMLLoad(string strFileName)
        {
            using (StreamReader sr = new StreamReader(@strFileName, Encoding.GetEncoding("utf-8")))
            {
                string schFileName = Path.GetFileNameWithoutExtension(strFileName) + XML_SCH;
                using (StreamReader sch = new StreamReader(schFileName, Encoding.GetEncoding("utf-8")))
                {
                    var ds = new DataSet();
                    ds.ReadXmlSchema(sch);
                    ds.ReadXml(sr);

                    return ds;
                }
            }
        }
    }
}

using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Data;

namespace ConvertDB
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConn oracon = new OracleConn())
            {
                oracon.Connect();
                string strSql = string.Empty;
                var param = new List<Oracle.DataAccess.Client.OracleParameter>();
                param.Add(new Oracle.DataAccess.Client.OracleParameter
                    ("inLOT_CD", Oracle.DataAccess.Client.OracleDbType.Varchar2, "18011483", ParameterDirection.Input));
                param.Add(new Oracle.DataAccess.Client.OracleParameter
                    ("outPos", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Output));
                param.Add(new Oracle.DataAccess.Client.OracleParameter
                    ("outErrCD", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Output));
                param.Add(new Oracle.DataAccess.Client.OracleParameter
                 ("outMsg", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.Output));
                //  inLOT_CD            in  VARCHAR2,
                //outPos            out VARCHAR2,
                //outErrCD          out VARCHAR2,
                //outMsg            out VARCHAR2



                oracon.ExcuteStoredProcedure("ES_W_SPEC_DETAIL.MAIN", param);
                var dt = oracon.ExcuteString(" select * from W_SPEC_DETAIL");
                using (ExcelConnect exConn = new ExcelConnect())
                {
                    exConn.ExcelOut("a.xls", dt);

                }
            }
            ////ロック
            //using (LockOracleConn lockOra = new LockOracleConn())
            //{
            //    lockOra.Connect();             
            //}

            //using (ExcelConnect exConn = new ExcelConnect())
            //{
            //    var t = exConn.LoadTableFromExcel("select * from [m_bumon] bumon ", "M_BUMON", "bumon.xls");

            //    DataSet ds = new DataSet();
            //    ds.Tables.Add(t);                
            //    CUtil.XMLOut("M_BUMON.xml", ds);
            //    var ds2 = CUtil.XMLLoad("M_BUMON.xml");
            //    exConn.ExcelOut("M_BUMON.xls", ds2);
            //}

        }
        static string GetTestResult(  )
        {
            //        {
            //            抹消 SKANRYO_KBN 生産入力マスタ
            //              1   0           Minusしない
            //              1   0           Minusする
            //              1   1           Minusしない
            //              1   1           Minusする
            //              1   設定しない Minusしない
            //              1   設定しない Minusする
            //              0   0           Minusしない
            //              0   0           Minusする
            //              0   1           Minusしない
            //              0   1           Minusする
            //              0   設定しない Minusしない
            //              0   設定しない Minusする
            CUtil.GetSqlFromTXT("test.sql");

            return "";
        }

        static string GetLotList()
        {


            string[] MessageList = new string[1];



            bool gai = true;
            bool nai = true;
            bool Rironchi = true;

            string SQL = string.Empty;
            string SQLSELECT = "SELECT ";
            SQLSELECT = SQLSELECT + "\n" + "A.TR_CD, ";
            // 2018/03/13 f_yajima AddStart
            SQLSELECT = SQLSELECT + "\n" + "E.TR_NAME, ";
            // AddEnd

            // 2018/04/12 a_fushimi Mod
            // SQLSELECT = SQLSELECT & vbCrLf & "DECODE(NVL(A.STOP_KBN, '0'), 0, '', '*') STOP_KBN,"
            SQLSELECT = SQLSELECT + "\n" + "(CASE WHEN NVL(A.STOP_KBN, '0') <> '0' THEN '*' ELSE ";
            SQLSELECT = SQLSELECT + "\n" + "(CASE WHEN NVL(HL.LOT_CD, '0') <> '0' THEN '#' ELSE '' END) END) STOP_KBN,";

            SQLSELECT = SQLSELECT + "\n" + "A.LOT_CD, ";

            // 2013/12/16 Tsuruki ADD
            SQLSELECT = SQLSELECT + "\n" + "D.SIKLOT_CD, ";

            SQLSELECT = SQLSELECT + "\n" + "A.HIN_CD, ";
            SQLSELECT = SQLSELECT + "\n" + "LPAD(NVL(A.HAN_CD, '      '), 6, ' ') || ' ' || NVL(A.SHAN_CD, '   ') HAN_CD, ";
            if (gai == true)
            {
                // 外層
                SQLSELECT = SQLSELECT + "\n" + "A.DEN_SHU, ";
                SQLSELECT = SQLSELECT + "\n" + "'' SOBETU, ";
            }
            else if (nai == true)
            {
                // 内層
                SQLSELECT = SQLSELECT + "\n" + "A.DENSHU DEN_SHU, ";
                SQLSELECT = SQLSELECT + "\n" + "A.SOBETU, ";
            }
            SQLSELECT = SQLSELECT + "\n" + "NVL(A.SOSU, 0) SOSU, ";
            SQLSELECT = SQLSELECT + "\n" + "DECODE(A.SHU_KBN, '1', '', NVL(C.NAM_NAME, ' ')) SHU_MEI, ";
            // 2018/03/13 f_yajima AddStart
            if (gai == true)
            {
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SOSU(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1') GAI_SOSU,";         // 外層層数
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G0030','M0050') SHU_HIN,";          // 製品種類
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G3081','M0150') CATEGORY,";         // 集計製品群
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G3300','S2140') KOZO,";             // 構造
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G3340','S2145') DOU_KOHO,";         // 銅メッキ工法
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G3310','S2141') KAIRO_KOHO,";       // 回路工法
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,NULL,'1','G3320','S2142') SURFACE,";          // 表面処理
            }
            else
            {
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SOSU(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0') GAI_SOSU,";         // 外層層数
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G0030','M0050') SHU_HIN,";          // 製品種類
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G3081','M0150') CATEGORY,";         // 集計製品群
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G3300','S2140') KOZO,";             // 構造
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G3340','S2145') DOU_KOHO,";         // 銅メッキ工法
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G3310','S2141') KAIRO_KOHO,";       // 回路工法
                SQLSELECT = SQLSELECT + "\n" + "ES_NGKOSE.GET_SPDT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0','G3320','S2142') SURFACE,";          // 表面処理
            }
            SQLSELECT = SQLSELECT + "\n" + "'' DEMAND,";           // 試作・量産
            SQLSELECT = SQLSELECT + "\n" + "B.MAEDKT_CD MAE_DKT,";         // 前大工程CD
            SQLSELECT = SQLSELECT + "\n" + "F.SKT_CD MAE_SKT,";            // 前小工程CD
            SQLSELECT = SQLSELECT + "\n" + "B.DKT_CD GEN_DKT,";            // 現大工程CD
            SQLSELECT = SQLSELECT + "\n" + "G.SKT_CD GEN_SKT,";            // 現小工程CD
            SQLSELECT = SQLSELECT + "\n" + "TO_CHAR(A.SH_DATE, 'YY/MM/DD') SH_DATE,";            // 出荷予想日
                                                                                                 // AddEnd
            SQLSELECT = SQLSELECT + "\n" + "NVL(B.ZAIKO_P, 0) ZAIKO_P, ";


            if (Rironchi == true)
            {
                // 理論値
                SQLSELECT = SQLSELECT + "\n" + "DECODE(SP.SPEC_KBN06,'2',CEIL(NVL(B.ZAIKO_P, 0) / DECODE(NVL(SP.SPEC_NUM08,1),0,1,NVL(SP.SPEC_NUM08,1))),0) ZAIKO_F, ";  // フレーム（良品数ピース / フレーム内ピース）
                SQLSELECT = SQLSELECT + "\n" + "CEIL(NVL(B.ZAIKO_P, 0) / DECODE(NVL(A.MENTUKE,1),0,1,NVL(A.MENTUKE,1))) ZAIKO_W, ";         // ワーク（良品数ピース/面付数）
            }
            else
            {
                // 実績値
                SQLSELECT = SQLSELECT + "\n" + "NVL(B.ZAIKO_F, 0) ZAIKO_F, ";
                SQLSELECT = SQLSELECT + "\n" + "NVL(B.ZAIKO_W, 0) ZAIKO_W, ";
            }


            SQLSELECT = SQLSELECT + "\n" + "NVL(A.TOUNYU_P, 0) TOUNYU_P, ";
            // ** Mod 2008/02/04 shiozawa
            // ** Add 2007/01/15 shiozawa
            SQLSELECT += "\n" + "ROUND(NVL(B.ZAIKO_P, 0) / NVL(A.TOUNYU_P, 0), 8) CHOKU, ";

            // ** Add End
            // 2018/03/13 f_yajima ModStart コメントアウト
            // SQLSELECT = SQLSELECT & vbCrLf & "NVL(TO_CHAR(TO_NUMBER(B.DKT_CD)),' ') DKT_CD, "
            // SQLSELECT = SQLSELECT & vbCrLf & "NVL(LPAD(TO_CHAR(TO_NUMBER(B.MAEDKT_CD)),5,' '),' ') MAEDKT_CD, "
            // ModEnd
            SQLSELECT = SQLSELECT + "\n" + "TO_CHAR(A.TONYU_DATE, 'YY/MM/DD') TONYU_DATE, ";

            // 2019/02/06 Hirokazu Ushiyama mod START
            // ** Mod 2007/08/29 shiozawa
            // SQLSELECT = SQLSELECT & vbCrLf & "TO_CHAR(B.U_DATE, 'YY/MM/DD') TUKA_DATE, "
            // SQLSELECT = SQLSELECT & vbCrLf & "TO_CHAR(B.JISSEKI_DATE, 'YY/MM/DD') TUKA_DATE, "
            // ** Mod End
            SQLSELECT = SQLSELECT + "\n" + "TO_CHAR(B.JISSEKI_DATE, 'YY/MM/DD HH24:MI:SS') TUKA_DATE, ";
            // mod END

            SQLSELECT = SQLSELECT + "\n" + "TO_CHAR(A.NOKI_DATE, 'YY/MM/DD') NOKI_DATE, ";
            SQLSELECT = SQLSELECT + "\n" + "DECODE(A.SENI, 1, '*' || LTRIM(TO_CHAR(A.ZAISUN_X, '9,990')), TO_CHAR(A.ZAISUN_X, '9,990')) ZAISUN_X, ";
            SQLSELECT = SQLSELECT + "\n" + "DECODE(A.SENI, 2, '*' || LTRIM(TO_CHAR(A.ZAISUN_Y, '9,990')), TO_CHAR(A.ZAISUN_Y, '9,990')) ZAISUN_Y, ";
            SQLSELECT = SQLSELECT + "\n" + "NVL(A.ITAATU, 0) ITAATU, ";
            SQLSELECT = SQLSELECT + "\n" + "NVL(A.BIKO, ' ') BIKO, ";
            // 2018/03/13 f_yajima ModStart コメントアウト
            // SQLSELECT = SQLSELECT & vbCrLf & "NVL(A.PLOT_CD, ' ') PLOT_CD, "
            // ModEnd
            SQLSELECT = SQLSELECT + "\n" + "ROUND(NVL(A.TANKA, 0), 2) TANKA, ";
            SQLSELECT = SQLSELECT + "\n" + "MENTUKE, ";

            // 2017/03/07 Togashi add
            if (gai == true)
            {
                // 外層
                SQLSELECT = SQLSELECT + "\n" + "ES_SQM.GETSQM(A.HIN_CD, A.HAN_CD, A.SHAN_CD, NULL, '1', NVL(B.ZAIKO_P, 0)) INV_SQM, ";
                SQLSELECT = SQLSELECT + "\n" + "ES_SQM.GETSQM(A.HIN_CD, A.HAN_CD, A.SHAN_CD, NULL, '1', NVL(A.TOUNYU_P, 0)) TONYU_SQM, ";
            }
            else
            {
                // 内層
                SQLSELECT = SQLSELECT + "\n" + "ES_SQM.GETSQM(A.HIN_CD, A.HAN_CD, A.SHAN_CD, A.SOBETU, '0', NVL(B.ZAIKO_P, 0)) INV_SQM, ";
                SQLSELECT = SQLSELECT + "\n" + "ES_SQM.GETSQM(A.HIN_CD, A.HAN_CD, A.SHAN_CD, A.SOBETU, '0', NVL(A.TOUNYU_P, 0)) TONYU_SQM, ";
            }
            SQLSELECT = SQLSELECT + "\n" + "ES_RATE.GETUSD(SYSDATE, ROUND(NVL(A.TANKA, 0), 2)) DOL_TANKA ";
            // 2017/03/07 Togashi add end

            // 2017/04/06 a_fushimi add
            SQLSELECT = SQLSELECT + "\n" + ", A.RISC RISC ";

            // 2017/04/26 a_fushimi add
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '01', A.LOT_CD) DFSR ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '02', A.LOT_CD) FLAT ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '03', A.LOT_CD) PET ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '04', A.LOT_CD) SRDI ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '05', A.LOT_CD) SRCT ";

            // 2017/10/13 f_yajima AddStart
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETGAICHUJDG(A.LOT_CD) TEHAI ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '06', A.LOT_CD) GREEN ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '07', A.LOT_CD) CO2 ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '08', A.LOT_CD) WHITEINK ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '09', A.LOT_CD) RINSING ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '10', A.LOT_CD) BAKING ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '11', A.LOT_CD) MAGIC ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '12', A.LOT_CD) WARPAGE ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '13', A.LOT_CD) DRY ";
            SQLSELECT = SQLSELECT + "\n" + ", ES_KTKBN.GETKTJDG(A.NGS_KBN, '14', A.LOT_CD) SORTER ";
            // AddEnd

            // 2018/04/12 a_fushimi Add Start
            SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN INSTR(A.HIN_CD, 'PP') > 0 OR INSTR(A.HIN_CD, 'NICU') > 0 OR INSTR(A.HIN_CD, 'BOCU') > 0 THEN '' ELSE SK.DEPARTMENT_CLASS_ENGNM END) DEPARTMENT_CLASS_ENGNM ";
            SQLSELECT = SQLSELECT + "\n" + ", SK.DEPARTMENT_ENGNM DEPARTMENT_ENGNM ";
            SQLSELECT = SQLSELECT + "\n" + ", SK.DETAIL_ENGNM DETAIL_ENGNM ";
            SQLSELECT = SQLSELECT + "\n" + ", DECODE(SK.DKT_JISSEKI_KBN, '1', '○') DKT_JISSEKI_KBN ";
            SQLSELECT = SQLSELECT + "\n" + ", DECODE(SK.SKT_JISSEKI_KBN, '1', '○') SKT_JISSEKI_KBN ";
            SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '○' ELSE '' END) HOLD ";
            if (gai == true)
            {
                // 外層
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DEN_SHU = 'H' THEN '○' ELSE '' END) END) REWORK ";    // REWORK
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DEN_SHU = 'H' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '398' OR A.DEN_SHU = 'K' OR INSTR(A.BIKO, 'さん') > 0 OR INSTR(A.BIKO, 'Ｐ') > 0 OR INSTR(A.BIKO, 'P') > 0 THEN '○' ELSE '' END) END) END) SAMPLE ";    // SAMPLE
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DEN_SHU = 'H' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '398' OR A.DEN_SHU = 'K' OR INSTR(A.BIKO, 'さん') > 0 OR INSTR(A.BIKO, 'Ｐ') > 0 OR INSTR(A.BIKO, 'P') > 0 THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '364' THEN '○' ELSE '' END) END) END) END) SUB_PRODUCT ";    // SUB_PRODUCT
                SQLSELECT = SQLSELECT + "\n" + ", 'Outer' LAYER ";    // LAYER
                SQLSELECT = SQLSELECT + "\n" + ", '' LAYER_COUNT ";    // LAYER_COUNT
            }
            else if (nai == true)
            {
                // 内層
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DENSHU = 'H' THEN '○' ELSE '' END) END) REWORK ";    // REWORK
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DENSHU = 'H' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '398' OR A.DENSHU = 'K' OR INSTR(A.BIKO, 'さん') > 0 OR INSTR(A.BIKO, 'Ｐ') > 0 OR INSTR(A.BIKO, 'P') > 0 THEN '○' ELSE '' END) END) END) SAMPLE ";    // SAMPLE
                SQLSELECT = SQLSELECT + "\n" + ", (CASE WHEN NVL(HL.LOT_CD, '0') <> '0' OR NVL(A.STOP_KBN, '0') <> '0' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.DENSHU = 'H' THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '398' OR A.DENSHU = 'K' OR INSTR(A.BIKO, 'さん') > 0 OR INSTR(A.BIKO, 'Ｐ') > 0 OR INSTR(A.BIKO, 'P') > 0 THEN '' ELSE ";
                SQLSELECT = SQLSELECT + "\n" + "  (CASE WHEN A.TR_CD = '364' THEN '○' ELSE '' END) END) END) END) SUB_PRODUCT ";    // SUB_PRODUCT
                SQLSELECT = SQLSELECT + "\n" + ", ES_NGKOSE.GET_LAYER(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0') LAYER ";    // LAYER
                SQLSELECT = SQLSELECT + "\n" + ", ES_NGKOSE.GET_LAYER_COUNT(A.HIN_CD,A.HAN_CD,A.SHAN_CD,A.SOBETU,'0') LAYER_COUNT ";    // LAYER_COUNT
            }
            SQLSELECT = SQLSELECT + "\n" + ", SK.DEPT_CLASS_SEQ DEPT_CLASS_SEQ ";
            SQLSELECT = SQLSELECT + "\n" + ", SK.DEPT_SEQ DEPT_SEQ ";
            SQLSELECT = SQLSELECT + "\n" + ", KG.GAITR_CD GAITR_CD ";
            SQLSELECT = SQLSELECT + "\n" + ", GT.TR_RYAKU GAITR_NM ";
            // 2018/04/12 a_fushimi Add End

            SQL = "FROM ";
            if (gai == true)
            {
                // 外層
                // 20040622 NISHIMURA MOD
                SQL = SQL + "\n" + "V_GAISOT_NOTMATSHO A, ";
                // SQL = SQL & vbCrLf & "T_GAISOT A, "

                // 2018/03/16 a_fushimi Add Start
                SQL = SQL + "\n" + "T_GAISOKT F, ";
                SQL = SQL + "\n" + "T_GAISOKT G, ";
            }
            else if (nai == true)
            {
                // 内層
                // 20040622 NISHIMURA MOD
                SQL = SQL + "\n" + "V_NAISOT_NOTMATSHO A, ";
                // SQL = SQL & vbCrLf & "T_NAISOT A, "

                // 2018/03/16 a_fushimi Add Start
                SQL = SQL + "\n" + "T_NAISOKT F, ";
                SQL = SQL + "\n" + "T_NAISOKT G, ";
            }

            // ワーク・フレーム表示区分追加 Add m_higuchi 05/10/28
            SQL = SQL + "\n" + "SPEC SP, ";

            // 2013/12/16 Tsuruki ADD
            SQL = SQL + "\n" + "T_SIYOLOTK D, ";
            // 2018/03/13 f_yajima AddStart
            SQL = SQL + "\n" + "M_TRI E, ";



            // 2018/04/12 a_fushimi Add Start
            SQL = SQL + "\n" + "T_HOLDLOT HL, ";
            SQL = SQL + "\n" + "M_SUMMARYKT SK, ";
            SQL = SQL + "\n" + "M_KTGHNOGP KG, ";
            SQL = SQL + "\n" + "M_TRI GT, ";
            // 2018/04/12 a_fushimi Add End

            SQL = SQL + "\n" + "T_ZAIKO B, ";
            SQL = SQL + "\n" + "M_NAME C ";

            SQL = SQL + "\n" + "WHERE ";
            SQL = SQL + "\n" + "A.LOT_CD = B.LOT_CD AND ";
            SQL = SQL + "\n" + "A.SHU_KBN = C.NAM_CD(+) AND ";
            SQL = SQL + "\n" + "C.NAM_BCD(+) = 'S0200' AND ";

            // 2004/08/11 takahashi コメントアウト
            // SQL = SQL & vbCrLf & "A.SHU_KBN IN ('1', '2', '3') AND "

            // ワーク・フレーム表示区分追加 m_higuchi Add 05/10/28
            if (gai == true)
            {
                // 外層　ロット 製品仕様の結合
                SQL = SQL + "\n" + "A.NGS_KBN = SP.NGS_KBN(+) AND ";
                SQL = SQL + "\n" + "A.HIN_CD = SP.HIN_CD(+) AND ";
                SQL = SQL + "\n" + "A.HAN_CD = SP.HAN_CD(+) AND ";
                SQL = SQL + "\n" + "NVL(A.SHAN_CD,'9999') = NVL(SP.SHAN_CD(+),'9999') AND ";
            }
            else
            {
                // 内層　ロット 製品仕様の結合
                SQL = SQL + "\n" + "A.NGS_KBN = SP.NGS_KBN(+) AND ";
                SQL = SQL + "\n" + "A.HIN_CD = SP.HIN_CD(+) AND ";
                SQL = SQL + "\n" + "A.HAN_CD = SP.HAN_CD(+) AND ";
                SQL = SQL + "\n" + "NVL(A.SHAN_CD,'9999') = NVL(SP.SHAN_CD(+),'9999') AND ";
                SQL = SQL + "\n" + "NVL(A.SOBETU,'999999') = NVL(SP.SOBETU(+),'999999') AND ";
            }

            // 2013/12/16 Tsuruki ADD
            SQL = SQL + "\n" + "A.LOT_CD = D.LOT_CD(+) AND ";

            SQL = SQL + "\n" + "A.SKANRYO_KBN <> '1' AND ";
            SQL = SQL + "\n" + "B.ZAIKO_P <> 0 AND ";
            SQL = SQL + "\n" + "(B.KNPN_KBN = 0 OR B.KNPN_KBN IS NULL) AND ";
            SQL = SQL + "\n" + "B.DKT_CD <> :dkt_cd ";


            // 2018/03/13 f_yajima AddSTart
            SQL = SQL + "\n" + " AND A.TR_CD = E.TR_CD ";

            // 2018/03/16 a_fushimi Mod Start
            // SQL = SQL & vbCrLf & " AND B.MAEDKT_CD = F.DKT_CD(+) "
            // SQL = SQL & vbCrLf & " AND B.DKT_CD = G.DKT_CD(+) "
            SQL = SQL + "\n" + " AND B.LOT_CD = F.LOT_CD(+) ";
            SQL = SQL + "\n" + " AND B.MAEDKT_CD = F.DKT_CD(+) ";
            SQL = SQL + "\n" + " AND B.LOT_CD = G.LOT_CD(+) ";
            SQL = SQL + "\n" + " AND B.DKT_CD = G.DKT_CD(+) ";
            // 2018/03/16 a_fushimi Mod End

            // 2018/04/12 a_fushimi Add Start
            SQL = SQL + "\n" + " AND B.LOT_CD = HL.LOT_CD(+) ";
            SQL = SQL + "\n" + " AND G.DKT_CD = SK.DKT_CD(+) ";
            SQL = SQL + "\n" + " AND G.SKT_CD LIKE DECODE(SK.SKT_CD(+),'88888','%',SK.SKT_CD(+)) ";
            SQL = SQL + "\n" + " AND DECODE(G.KTGP_CD,'00000','0','1') LIKE DECODE(SK.KTGP_CD(+),'00000','0','99999','%','1') ";
            if (gai == true)
            {
                SQL = SQL + "\n" + " AND ES_NGKOSE.GET_SPDT(G.HIN_CD,G.HAN_CD,G.SHAN_CD,NULL,'1','G3310','S2141') LIKE DECODE(SK.SUM_TM(+),'0','Tenting','1','MSAP','%') ";
                SQL = SQL + "\n" + " AND SK.SUM_IO(+) = '9' ";
            }
            else
            {
                SQL = SQL + "\n" + " AND ES_NGKOSE.GET_SPDT(G.HIN_CD,G.HAN_CD,G.SHAN_CD,G.SOBETU,'0','G3310','S2141') LIKE DECODE(SK.SUM_TM(+),'0','Tenting','1','MSAP','%') ";
                SQL = SQL + "\n" + " AND ES_NGKOSE.GET_LAYER(G.HIN_CD,G.HAN_CD,G.SHAN_CD,G.SOBETU,'0') LIKE DECODE(SK.SUM_IO(+),'0','Inner','1','Outer','%') ";
            }
            SQL = SQL + "\n" + " AND SK.JISSEKI_KBN(+) = '1' ";
            SQL = SQL + "\n" + " AND G.KTGP_CD = KG.KTGP_CD(+) ";
            SQL = SQL + "\n" + " AND KG.GAITR_CD = GT.TR_CD(+) ";
            // 2018/04/12 a_fushimi Add End

            // AddEnd


            //// 20050119 takahashi Mod
            //// 社名
            //if (!IsNothing(frmPSDR0080.PFormTr.PTr_cdCSV) && frmPSDR0080.PFormTr.PTr_cdCSV.Length > 0)
            //    SQL += " AND A.TR_CD IN (" + frmPSDR0080.PFormTr.PTr_cdCSV + ") ";
            //else if (ShameiFrom != "" & ShameiTo == "")
            //{
            //    SQL += "AND A.TR_CD >= :shameifrom ";
            //    SqlParameters.Add("shameifrom", OracleDbType.Varchar2, ShameiFrom, ParameterDirection.Input);
            //}
            //else if (ShameiFrom == "" & ShameiTo != "")
            //{
            //    SQL += "AND A.TR_CD <= :shameito ";
            //    SqlParameters.Add("shameito", OracleDbType.Varchar2, ShameiTo, ParameterDirection.Input);
            //}
            //else if (ShameiFrom != "" & ShameiTo != "")
            //{
            //    SQL += " AND A.TR_CD BETWEEN :shameifrom AND :shameito ";
            //    SqlParameters.Add("shameifrom", OracleDbType.Varchar2, ShameiFrom, ParameterDirection.Input);
            //    SqlParameters.Add("shameito", OracleDbType.Varchar2, ShameiTo, ParameterDirection.Input);
            //}

            //// 品名
            //if (!IsNothing(frmPSDR0080.PFormHin.PHin_cdCSV) && frmPSDR0080.PFormHin.PHin_cdCSV.Length > 0)
            //    SQL += " AND A.HIN_CD IN (" + frmPSDR0080.PFormHin.PHin_cdCSV + ") ";
            //else if (Hin_Cd != "")
            //{
            //    string strHin_cd = Hin_Cd;
            //    // 曖昧品名検索
            //    if (Strings.Mid(strHin_cd, strHin_cd.Length, 1) == "*")
            //    {
            //        strHin_cd = strHin_cd.Substring(0, strHin_cd.Length - 1) + "%";
            //        SQL += " AND A.HIN_CD LIKE :hin_cd ";
            //    }
            //    else
            //        SQL += "AND A.HIN_CD = :hin_cd ";

            //    SqlParameters.Add("hin_cd", OracleDbType.Varchar2, strHin_cd, ParameterDirection.Input);
            //}



            //// 工程
            //if (KouteiFrom != "" & KouteiTo == "")
            //{
            //    SQL = SQL + "\n" + "AND B.DKT_CD >= :kouteifrom ";
            //    SqlParameters.Add("kouteifrom", OracleDbType.Varchar2, KouteiFrom, ParameterDirection.Input);
            //}
            //else if (KouteiFrom == "" & KouteiTo != "")
            //{
            //    SQL = SQL + "\n" + "AND B.DKT_CD <= :kouteito ";
            //    SqlParameters.Add("kouteito", OracleDbType.Varchar2, KouteiTo, ParameterDirection.Input);
            //}
            //else if (KouteiFrom != "" & KouteiTo != "")
            //{
            //    SQL = SQL + "\n" + " AND B.DKT_CD BETWEEN :kouteifrom AND :kouteito ";
            //    SqlParameters.Add("kouteifrom", OracleDbType.Varchar2, KouteiFrom, ParameterDirection.Input);
            //    SqlParameters.Add("kouteito", OracleDbType.Varchar2, KouteiTo, ParameterDirection.Input);
            //}

            //// ロット№
            //if (LotFrom != "" & LotTo == "")
            //{
            //    SQL = SQL + "\n" + "AND B.LOT_CD >= :lotfrom ";
            //    SqlParameters.Add("lotfrom", OracleDbType.Varchar2, LotFrom, ParameterDirection.Input);
            //}
            //else if (LotFrom == "" & LotTo != "")
            //{
            //    SQL = SQL + "\n" + "AND B.LOT_CD <= :lotto ";
            //    SqlParameters.Add("lotto", OracleDbType.Varchar2, LotTo, ParameterDirection.Input);
            //}
            //else if (LotFrom != "" & LotTo != "")
            //{
            //    SQL = SQL + "\n" + " AND B.LOT_CD BETWEEN :lotfrom AND :lotto ";
            //    SqlParameters.Add("lotfrom", OracleDbType.Varchar2, LotFrom, ParameterDirection.Input);
            //    SqlParameters.Add("lotto", OracleDbType.Varchar2, LotTo, ParameterDirection.Input);
            //}

            //// ** Mod 2008/02/04 shiozawa
            //// ** Add 2008/01/15 shiozawa
            //// 直行率（下限）
            //if (withBlock.txtChokuFrom.Text != "")
            //{
            //    // SQL &= vbCrLf & "AND ROUND(NVL(B.ZAIKO_P, 0) / NVL(A.TOUNYU_P, 0) * 100, 2) >= :choku_from "
            //    SQL += "\n" + "AND NVL(B.ZAIKO_P, 0) / NVL(A.TOUNYU_P, 0) * 100 >= :choku_from ";
            //    SqlParameters.Add("choku_from", OracleDbType.Varchar2, withBlock.txtChokuFrom.Value, ParameterDirection.Input);
            //}
            //// 直行率（上限）
            //if (withBlock.txtChokuTo.Text != "")
            //{
            //    // SQL &= vbCrLf & "AND ROUND(NVL(B.ZAIKO_P, 0) / NVL(A.TOUNYU_P, 0) * 100, 2) <= :choku_to "
            //    SQL += "\n" + "AND NVL(B.ZAIKO_P, 0) / NVL(A.TOUNYU_P, 0) * 100 <= :choku_to ";
            //    SqlParameters.Add("choku_to", OracleDbType.Varchar2, withBlock.txtChokuTo.Value, ParameterDirection.Input);
            //}
            //// ** Add End


            //if (withBlock.rbTujo.Checked == true)
            //    SQL = SQL + "\n" + "AND A.STOP_KBN = '0' ";
            //else if (withBlock.rbStop.Checked == true)
            //    SQL = SQL + "\n" + "AND A.STOP_KBN <> '0' ";

            //if (Den_A != "1")
            //{
            //    if (withBlock.rbGaisou.Checked == true)
            //    {
            //        // 外層
            //        if (Den_T == "1")
            //        {
            //            SQL = SQL + "\n" + "AND (A.DEN_SHU IS NULL ";
            //            SQL = SQL + "\n" + "OR A.DEN_SHU IN ";
            //            SQL = SQL + "\n" + "(:den_k, :den_h, :den_x, :den_z)) ";
            //        }
            //        else
            //        {
            //            SQL = SQL + "\n" + "AND A.DEN_SHU IN ";
            //            SQL = SQL + "\n" + "(:den_k, :den_h, :den_x, :den_z) ";
            //        }
            //    }
            //    else if (withBlock.rbNaisou.Checked == true)
            //    {
            //        // 内層
            //        if (Den_T == "1")
            //        {
            //            SQL = SQL + "\n" + "AND (A.DENSHU IS NULL ";
            //            SQL = SQL + "\n" + "OR A.DENSHU IN ";
            //            SQL = SQL + "\n" + "(:den_k, :den_h, :den_x, :den_z)) ";
            //        }
            //        else
            //        {
            //            SQL = SQL + "\n" + "AND A.DENSHU IN ";
            //            SQL = SQL + "\n" + "(:den_k, :den_h, :den_x, :den_z) ";
            //        }
            //    }
            //    SqlParameters.Add("den_k", OracleDbType.Varchar2, Den_K, ParameterDirection.Input);
            //    SqlParameters.Add("den_h", OracleDbType.Varchar2, Den_H, ParameterDirection.Input);
            //    SqlParameters.Add("den_x", OracleDbType.Varchar2, Den_X, ParameterDirection.Input);
            //    SqlParameters.Add("den_z", OracleDbType.Varchar2, Den_Z, ParameterDirection.Input);
            //}

            //// 実績入力日
            //if (HidukeFrom != "" & HidukeTo == "")
            //{
            //    // ** Mod 2007/08/29 shiozawa
            //    // SQL = SQL & vbCrLf & "AND TO_CHAR(B.SYSU_DATE, 'YYYY/MM/DD') >= :hidukefrom "
            //    SQL = SQL + "\n" + "AND TO_CHAR(B.JISSEKI_DATE, 'YYYY/MM/DD') >= :hidukefrom ";
            //    SqlParameters.Add("hidukefrom", OracleDbType.Varchar2, HidukeFrom, ParameterDirection.Input);
            //}
            //else if (HidukeFrom == "" & HidukeTo != "")
            //{
            //    // ** Mod 2007/08/29 shiozawa
            //    // SQL = SQL & vbCrLf & "AND TO_CHAR(B.SYSU_DATE, 'YYYY/MM/DD') <= :hiduketo "
            //    SQL = SQL + "\n" + "AND TO_CHAR(B.JISSEKI_DATE, 'YYYY/MM/DD') <= :hiduketo ";
            //    SqlParameters.Add("hiduketo", OracleDbType.Varchar2, HidukeTo, ParameterDirection.Input);
            //}
            //else if (HidukeFrom != "" & HidukeTo != "")
            //{
            //    // ** Mod 2007/08/29 shiozawa
            //    // SQL = SQL & vbCrLf & "AND TO_CHAR(B.SYSU_DATE, 'YYYY/MM/DD') BETWEEN "
            //    SQL = SQL + "\n" + "AND TO_CHAR(B.JISSEKI_DATE, 'YYYY/MM/DD') BETWEEN ";
            //    SQL = SQL + "\n" + ":hidukefrom AND :hiduketo ";
            //    SqlParameters.Add("hidukefrom", OracleDbType.Varchar2, HidukeFrom, ParameterDirection.Input);
            //    SqlParameters.Add("hiduketo", OracleDbType.Varchar2, HidukeTo, ParameterDirection.Input);
            //}

            SQL = SQL + "\n" + "ORDER BY ";
            SQL = SQL + "\n" + "A.TR_CD, ";
            SQL = SQL + "\n" + "A.HIN_CD, ";
            SQL = SQL + "\n" + "A.HAN_CD, ";
            SQL = SQL + "\n" + "A.SHAN_CD, ";
            SQL = SQL + "\n" + "A.LOT_CD ";



            // カウント取得
            //        int ICNT;
            //        ExecuteQuery("SELECT COUNT(*) CNT " + SQL, SqlParameters);
            //        ICNT = System.Convert.ToInt32(this.ColmnObjects.Item("CNT")(0));



            //        frmPSDR0080.Cursor = Cursors.Default;
            //        frmPSDR0080.Refresh();

            //        if (ICNT == 0)
            //        {
            //            KICMessage.GetMessage("E1006", "");
            //            return false;
            //        }
            //        else if (ICNT >= KIC.Data.KICCommonConstants.KensakuKensu * 10)
            //        {
            //            MessageList[0] = System.Convert.ToString(ICNT);
            //            if (KIC.Util.KICMessage.GetMessage("Q0012", MessageList, "") == MsgBoxResult.No)
            //                return false;
            //        }

            //        frmPSDR0080.lbPtn1.Visible = true;
            //        frmPSDR0080.picPtn1.Visible = true;
            //        frmPSDR0080.Cursor = Cursors.WaitCursor;
            //        frmPSDR0080.Refresh();

            //        // 実行
            //        DS = ExecuteQueryDataSet(SQLSELECT + SQL, "A", SqlParameters, Conn);

            //        // 取得したデータをシートに表示する
            //        if (!(DS == null))
            //        {
            //            withBlock.shtLotList.Visible = false;
            //            withBlock.shtLotList.MaxRows = DS.Tables(0).Rows.Count();

            //            DspLotList(DS, frmPSDR0080);
            //            withBlock.shtLotList.Visible = true;
            //        }
            //        else
            //            // 明細無
            //            return false;

            //        // シートにデータが追加された場合のみClearFlgを1にする
            //        // (第２会話目でのクリア処理で、検索条件を初期化させない為）
            //        frmPSDR0080.ClearFlg = 1;
            //    }

            //    return true;
            //}
            //catch (OracleException OraExp)
            //{
            //    throw OraExp;
            //}
            //finally
            //{
            //    if (!(DS == null))
            //        DS.Dispose();
            //    if (!(Conn == null))
            //    {
            //        // コネクションを開放する
            //        KICPoolManager.FreeConnection(Conn);
            //        Conn = null/* TODO Change to default(_) if this is not a reference type */;
            //    
            return SQLSELECT + SQL;
        }
    }
}