/// <summary>
/// 仕入先一覧の取得
///
/// 使用画面：MstVendorMnt, SrchVendor
/// </summary>
public DataSet GetVendorList()
{
ComDB db = new ComDB(_db_type);
ComLibrary comlibrary = new ComLibrary();
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
//-->ADD BY UBIQ-SUO 2010/09/25
strSQL.Append("SELECT COUNT(*) ");
//strSQL.Append("VENDOR_CD, BEG_EFF_DATE, END_EFF_DATE, PO_STOP_FLAG, ");
//strSQL.Append("VENDOR_NAME, VENDOR_DESC, VENDOR_NAME2, VENDOR_DESC2, ");
//strSQL.Append("COUNTRY_CD, FOREIGN_FLAG, CUR_CD, SUPPLY_WHS, ");
//strSQL.Append("BOND_SUPPLY_WHS, PO_SLIPLESS_FLAG, PO_SLIP_TYPE, FCST_FLAG, ");
//strSQL.Append("PAY_TERMS, VENDOR_ATTN, TELNO, FAXNO, ");
//strSQL.Append("ZIP_CD, STATE, ADDRESS1, ADDRESS2, ");
//strSQL.Append("ADDRESS2, ADDRESS4, STATE2, ADDRESS2_1, ");
//strSQL.Append("ADDRESS2_2, ADDRESS2_3, ADDRESS2_4, ");
//strSQL.Append("DATA_CHAR1, DATA_CHAR2, ");
//strSQL.Append("DATA_CHAR3, DATA_CHAR4, DATA_CHAR5, DATA_CHAR6, ");
//strSQL.Append("DATA_CHAR7, DATA_CHAR8, DATA_CHAR9, DATA_CHAR10, ");
//strSQL.Append("DATA_NUM1, DATA_NUM2, DATA_NUM3, DATA_NUM4, ");
//strSQL.Append("DATA_NUM5, DATA_NUM6, DATA_NUM7, DATA_NUM8, ");
//strSQL.Append("DATA_NUM9, DATA_NUM10, DATA_FLAG1, DATA_FLAG2, ");
//strSQL.Append("DATA_FLAG3, DATA_FLAG4, DATA_FLAG5, DATA_FLAG6, ");
//strSQL.Append("DATA_FLAG7, DATA_FLAG8, DATA_FLAG9, DATA_FLAG10, ");
//strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_PGM, CHG_USERID, ");
//strSQL.Append("UPDATE_CNTR ");
strSQL.Append(" FROM VENDOR_MASTER ");
if (_vendor_cd != "" || _vendor_name != "")
{
strSQL.Append(" WHERE ");
if (_vendor_cd != "")
{
strSQL.Append(" VENDOR_CD LIKE '" + _vendor_cd + "%'");
if (_vendor_name != "")
{
strSQL.Append(" AND ");
}
}
if (_vendor_name != "")
{
strSQL.Append(" VENDOR_NAME LIKE '%" + _vendor_name + "%'");
}
}
//strSQL.Append(" ORDER BY VENDOR_CD");
db.DbParametersClear();
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
return(null);
}
//if (db.State() == ConnectionState.Open)
// db.DbClose();
if (comlibrary.StringToInt(ds.Tables[0].Rows[0][0].ToString()) > 2000)
{
_range = true;
}
else
{
_range = false;
}
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
strSQL = new StringBuilder();
//<--ADD BY UBIQ-SUO 2010/09/25
strSQL.Append("SELECT TOP 2000 ");
strSQL.Append("VENDOR_CD, BEG_EFF_DATE, END_EFF_DATE, PO_STOP_FLAG, ");
strSQL.Append("VENDOR_NAME, VENDOR_DESC, VENDOR_NAME2, VENDOR_DESC2, ");
strSQL.Append("COUNTRY_CD, FOREIGN_FLAG, CUR_CD, SUPPLY_WHS, ");
strSQL.Append("BOND_SUPPLY_WHS, PO_SLIPLESS_FLAG, PO_SLIP_TYPE, FCST_FLAG, ");
strSQL.Append("PAY_TERMS, VENDOR_ATTN, TELNO, FAXNO, ");
strSQL.Append("ZIP_CD, STATE, ADDRESS1, ADDRESS2, ");
strSQL.Append("ADDRESS2, ADDRESS4, STATE2, ADDRESS2_1, ");
strSQL.Append("ADDRESS2_2, ADDRESS2_3, ADDRESS2_4, REJ_WHS, "); //ADD BY UBIQ-LIU 2010/11/23 add [REJ_WHS]
strSQL.Append("DATA_CHAR1, DATA_CHAR2, ");
strSQL.Append("DATA_CHAR3, DATA_CHAR4, DATA_CHAR5, DATA_CHAR6, ");
strSQL.Append("DATA_CHAR7, DATA_CHAR8, DATA_CHAR9, DATA_CHAR10, ");
strSQL.Append("DATA_NUM1, DATA_NUM2, DATA_NUM3, DATA_NUM4, ");
strSQL.Append("DATA_NUM5, DATA_NUM6, DATA_NUM7, DATA_NUM8, ");
strSQL.Append("DATA_NUM9, DATA_NUM10, DATA_FLAG1, DATA_FLAG2, ");
strSQL.Append("DATA_FLAG3, DATA_FLAG4, DATA_FLAG5, DATA_FLAG6, ");
strSQL.Append("DATA_FLAG7, DATA_FLAG8, DATA_FLAG9, DATA_FLAG10, ");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_PGM, CHG_USERID, ");
strSQL.Append("UPDATE_CNTR ");
strSQL.Append(" FROM VENDOR_MASTER ");
if (_vendor_cd != "" || _vendor_name != "")
{
strSQL.Append(" WHERE ");
if (_vendor_cd != "")
{
strSQL.Append(" VENDOR_CD LIKE '" + _vendor_cd + "%'");
if (_vendor_name != "")
{
strSQL.Append(" AND ");
}
}
if (_vendor_name != "")
{
strSQL.Append(" VENDOR_NAME LIKE '%" + _vendor_name + "%'");
}
}
strSQL.Append(" ORDER BY VENDOR_CD");
db.DbParametersClear();
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(ds);
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
}
/// <summary>
/// JOCの取得
///
/// <para>使用画面：SearchJoc</para>
/// </summary>
public DataSet GetJOCList()//UPD UBIQ-LIU 2010/07/01
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
ComLibrary comlibrary = new ComLibrary();
//--- sql
//--> ADD BY UBIQ-SUO 2010/09/25
strSQL.Append(" SELECT COUNT(*) ");
//strSQL.Append(" J.JOC_CD, J.BEG_EFF_DATE, J.END_EFF_DATE, ");
//strSQL.Append(" J.JOC_STATUS, J.JOC_TYPE, J.JOC_DESC, ");
//strSQL.Append(" J.JOC_DESC2, J.COST_CENTER, J.WS_CD, ");
//strSQL.Append(" J.PROCESS_CD, J.CHG_PGM, J.CHG_USERID, ");
//strSQL.Append(" J.UPDATE_CNTR, W.WS_DESC AS WS_DESC, ");
//strSQL.Append(" S.DATA_CHAR AS COST_CENTER_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM JOC_MASTER J ");
//strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON J.WS_CD = W.WS_CD ");
//strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S ON S.KEY01 ='COST_CENTER'");
//strSQL.Append(" AND J.COST_CENTER =S.KEY02 ");
strSQL.Append(" WHERE J.JOC_TYPE = @JOC_TYPE ");
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
db.DbPsetInt("@JOC_TYPE", _joc_type);
}
else
{
strSQL.Append(" FROM JOC_MASTER J ");
//strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON J.WS_CD = W.WS_CD ");
//strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S ON S.KEY01 ='COST_CENTER'");
//strSQL.Append(" AND J.COST_CENTER =S.KEY02 ");
strSQL.Append(" WHERE J.JOC_TYPE = @JOC_TYPE ");
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
db.DbPsetInt("@JOC_TYPE", _joc_type);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
return(null);
}
//if (db.State() == ConnectionState.Open)
// db.DbClose();
if (comlibrary.StringToInt(ds.Tables[0].Rows[0][0].ToString()) > 2000)
{
_range = true;
}
else
{
_range = false;
}
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
strSQL = new StringBuilder();
//<-- ADD BY UBIQ-SUO 2010/09/25
strSQL.Append(" SELECT TOP 2000 "); //UPD BY UBIQ-SUO 2010/09/25 ADD TOP 2000
strSQL.Append(" J.JOC_CD, J.BEG_EFF_DATE, J.END_EFF_DATE, ");
strSQL.Append(" J.JOC_STATUS, J.JOC_TYPE, J.JOC_DESC, ");
strSQL.Append(" J.JOC_DESC2, J.COST_CENTER, J.WS_CD, ");
strSQL.Append(" J.PROCESS_CD, J.CHG_PGM, J.CHG_USERID, ");
strSQL.Append(" J.UPDATE_CNTR, W.WS_DESC AS WS_DESC, ");
strSQL.Append(" S.DATA_CHAR AS COST_CENTER_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM JOC_MASTER J ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON J.WS_CD = W.WS_CD ");
strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S ON S.KEY01 ='COST_CENTER'");
strSQL.Append(" AND J.COST_CENTER =S.KEY02 ");
strSQL.Append(" WHERE J.JOC_TYPE = @JOC_TYPE ");
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
db.DbPsetInt("@JOC_TYPE", _joc_type);
}
else
{
strSQL.Append(" FROM JOC_MASTER J ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON J.WS_CD = W.WS_CD ");
strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S ON S.KEY01 ='COST_CENTER'");
strSQL.Append(" AND J.COST_CENTER =S.KEY02 ");
strSQL.Append(" WHERE J.JOC_TYPE = @JOC_TYPE ");
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
db.DbPsetInt("@JOC_TYPE", _joc_type);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(ds);
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
}
/// <summary>
/// 仕入先一覧の取得
///
/// 使用画面：MstVendorMnt, SrchVendor
/// </summary>
public DataSet GetCustomerList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
ComLibrary comlibrary = new ComLibrary();
//--- sql
//-->ADD BY UBIQ-SUO 2010/09/25
strSQL.Append("SELECT COUNT(*)");
//strSQL.Append("CUST_CD, CUST_SUB_CD, ");
//strSQL.Append("CUST_NAME, CUST_DESC, CUST_NAME2, CUST_DESC2, ");
//strSQL.Append("COUNTRY_CD, FOREIGN_FLAG, CUR_CD, SHIP_WHS, ");
//strSQL.Append("TRANS_LT, SHIP_SLIP_TYPE, PAY_TERMS, ");
//strSQL.Append("EVAL_RATIO, CUST_ATTN, TELNO, FAXNO, ");
//strSQL.Append("ZIP_CD, STATE, ADDRESS1, ADDRESS2, ");
//strSQL.Append("ADDRESS3, ADDRESS4, STATE2, ADDRESS2_1, ");
//strSQL.Append("ADDRESS2_2, ADDRESS2_3, ADDRESS2_4, ");
//strSQL.Append("DATA_CHAR1, DATA_CHAR2, ");
//strSQL.Append("DATA_CHAR3, DATA_CHAR4, DATA_CHAR5, DATA_CHAR6, ");
//strSQL.Append("DATA_CHAR7, DATA_CHAR8, DATA_CHAR9, DATA_CHAR10, ");
//strSQL.Append("DATA_NUM1, DATA_NUM2, DATA_NUM3, DATA_NUM4, ");
//strSQL.Append("DATA_NUM5, DATA_NUM6, DATA_NUM7, DATA_NUM8, ");
//strSQL.Append("DATA_NUM9, DATA_NUM10, DATA_FLAG1, DATA_FLAG2, ");
//strSQL.Append("DATA_FLAG3, DATA_FLAG4, DATA_FLAG5, DATA_FLAG6, ");
//strSQL.Append("DATA_FLAG7, DATA_FLAG8, DATA_FLAG9, DATA_FLAG10, ");
//strSQL.Append("CHG_PGM, CHG_USERID, ");
//strSQL.Append("UPDATE_CNTR");
strSQL.Append(" FROM CUSTOMER_MASTER ");
db.DbParametersClear();
if (_cust_cd != "" || _cust_name != "")
{
strSQL.Append(" WHERE ");
if (_cust_cd != "")
{
strSQL.Append(" CUST_CD LIKE '" + _cust_cd + "%'");
if (_cust_name != "")
{
strSQL.Append(" AND ");
}
}
if (_cust_name != "")
{
strSQL.Append(" CUST_NAME LIKE '%" + _cust_name + "%'");
}
}
//strSQL.Append(" ORDER BY CUST_CD");
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
return(null);
}
//if (db.State() == ConnectionState.Open)
// db.DbClose();
if (comlibrary.StringToInt(ds.Tables[0].Rows[0][0].ToString()) > 2000)
{
_range = true;
}
else
{
_range = false;
}
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
strSQL = new StringBuilder();
//<--ADD BY UBIQ-SUO 2010/09/25
strSQL.Append("SELECT TOP 2000");
strSQL.Append("CUST_CD, CUST_SUB_CD, ");
strSQL.Append("CUST_NAME, CUST_DESC, CUST_NAME2, CUST_DESC2, ");
strSQL.Append("COUNTRY_CD, FOREIGN_FLAG, CUR_CD, SHIP_WHS, ");
strSQL.Append("TRANS_LT, SHIP_SLIP_TYPE, PAY_TERMS, ");
strSQL.Append("EVAL_RATIO, CUST_ATTN, TELNO, FAXNO, ");
strSQL.Append("ZIP_CD, STATE, ADDRESS1, ADDRESS2, ");
strSQL.Append("ADDRESS3, ADDRESS4, STATE2, ADDRESS2_1, ");
strSQL.Append("ADDRESS2_2, ADDRESS2_3, ADDRESS2_4, ");
strSQL.Append("DATA_CHAR1, DATA_CHAR2, ");
strSQL.Append("DATA_CHAR3, DATA_CHAR4, DATA_CHAR5, DATA_CHAR6, ");
strSQL.Append("DATA_CHAR7, DATA_CHAR8, DATA_CHAR9, DATA_CHAR10, ");
strSQL.Append("DATA_NUM1, DATA_NUM2, DATA_NUM3, DATA_NUM4, ");
strSQL.Append("DATA_NUM5, DATA_NUM6, DATA_NUM7, DATA_NUM8, ");
strSQL.Append("DATA_NUM9, DATA_NUM10, DATA_FLAG1, DATA_FLAG2, ");
strSQL.Append("DATA_FLAG3, DATA_FLAG4, DATA_FLAG5, DATA_FLAG6, ");
strSQL.Append("DATA_FLAG7, DATA_FLAG8, DATA_FLAG9, DATA_FLAG10, ");
strSQL.Append("CHG_PGM, CHG_USERID, ");
strSQL.Append("UPDATE_CNTR");
strSQL.Append(" FROM CUSTOMER_MASTER ");
db.DbParametersClear();
if (_cust_cd != "" || _cust_name != "")
{
strSQL.Append(" WHERE ");
if (_cust_cd != "")
{
strSQL.Append(" CUST_CD LIKE '" + _cust_cd + "%'");
if (_cust_name != "")
{
strSQL.Append(" AND ");
}
}
if (_cust_name != "")
{
strSQL.Append(" CUST_NAME LIKE '%" + _cust_name + "%'");
}
}
strSQL.Append(" ORDER BY CUST_CD");
try
{
ds = db.DbDataSet(strSQL.ToString(), "TABLE");
if (ds == null)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(ds);
}
catch
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(null);
}
}
