/// <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); } }