nancy ConvertDB

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 hsENM_Type;
private Dictionary hsENM_Col_Num;
private Dictionary hsENM_Num_Col;
private Dictionary 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 hhss";
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();
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;
    }
}

}