/// <summary>
/// ロール詳細の取得
///
/// 使用画面：MstRoleMnt
/// </summary>
public int GetRoleDetail()
{
ComDB db = new ComDB("", "COM");
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
//strSQL.Append("SELECT ROLE_DESC1, ROLE_DESC2, ROLE_DESC3, DEL_DISABLE_FLAG,");
//strSQL.Append("ROLE_DESC4, ");
//switch (_lang)
//{
// case 2:
// strSQL.Append("ROLE_DESC2 AS ROLE_DESC, ");
// break;
// case 3:
// strSQL.Append("ROLE_DESC3 AS ROLE_DESC, ");
// break;
// case 4:
// strSQL.Append("ROLE_DESC4 AS ROLE_DESC, ");
// break;
// default:
// strSQL.Append("ROLE_DESC1 AS ROLE_DESC, ");
// break;
//}
strSQL.Append("SELECT USER_GROUP_CODE, USER_GROUP_NAME, GROUP_ACTIVE,");
strSQL.Append("MANAGEMENT_LEVEL, DEPT_NO, PROCESS_NO,");
//strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
//strSQL.Append(" UPDATE_CNTR");
//strSQL.Append(" FROM ROLE_MASTER ");
strSQL.Append(" ENTRY_DATE, UPDATE_DATE, USER_CREATE, USER_UPDATE");
strSQL.Append(" FROM USER_ROLE_MASTER ");
//strSQL.Append(" WHERE ROLE = @ROLE");
strSQL.Append(" WHERE USER_GROUP_CODE = @ROLE");
db.DbParametersClear();
db.DbPsetString("@ROLE", _role);
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
//_role_desc1 = db.Row("ROLE_DESC1");
//_role_desc2 = db.Row("ROLE_DESC2");
//_role_desc3 = db.Row("ROLE_DESC3");
//_role_desc4 = db.Row("ROLE_DESC4");
//_role_desc = db.Row("ROLE_DESC");
//_del_disable_flag = int.Parse(db.Row("DEL_DISABLE_FLAG"));
//_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
//_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
//_chg_user_id = db.Row("CHG_USER_ID");
//_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
_role_desc = db.Row("USER_GROUP_NAME");
_mgmt_level = db.Row("MANAGEMENT_LEVEL");
_dept_no = db.Row("DEPT_NO");
_process_no = db.Row("PROCESS_NO");
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("UPDATE_DATE"));
_chg_user_id = db.Row("USER_UPDATE");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 辞書一覧の取得
///
/// 使用画面：MstDicMnt
/// </summary>
public DataSet GetDictionaryList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT STD_ITEM, ITEM_DESC1, ITEM_DESC2, ITEM_DESC3, ITEM_DESC4,");
strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
strSQL.Append(" UPDATE_CNTR");
strSQL.Append(" FROM DICTIONARY_MASTER ");
db.DbParametersClear();
bool condFlag = false;
if (_std_item != "")
{
if (condFlag)
{
strSQL.Append(" AND STD_ITEM LIKE '%" + _std_item + "%'");
}
else
{
strSQL.Append(" WHERE STD_ITEM LIKE '%" + _std_item + "%'");
condFlag = true;
}
//Del 20070518 T.Sawada
//db.DbPsetString("STD_ITEM", _std_item);
//End Del
}
if (_item_desc1 != "")
{
if (condFlag)
{
strSQL.Append(" AND ITEM_DESC1 LIKE '%" + _item_desc1 + "%'");
}
else
{
strSQL.Append(" WHERE ITEM_DESC1 LIKE '%" + _item_desc1 + "%'");
condFlag = true;
}
//Del 20070518 T.Sawada
//db.DbPsetString("ITEM_DESC1", _item_desc1);
//End Del
}
if (_item_desc2 != "")
{
if (condFlag)
{
strSQL.Append(" AND ITEM_DESC2 LIKE '%" + _item_desc2 + "%'");
}
else
{
strSQL.Append(" WHERE ITEM_DESC2 LIKE '%" + _item_desc2 + "%'");
condFlag = true;
}
//Del 20070518 T.Sawada
//db.DbPsetString("ITEM_DESC2", _item_desc2);
//End Del
}
if (_item_desc3 != "")
{
if (condFlag)
{
strSQL.Append(" AND ITEM_DESC3 LIKE '" + _item_desc3 + "%'");
}
else
{
strSQL.Append(" WHERE ITEM_DESC3 LIKE '" + _item_desc3 + "%'");
condFlag = true;
}
//Del 20070518 T.Sawada
//db.DbPsetString("ITEM_DESC3", _item_desc3);
//End Del
}
if (_item_desc4 != "")
{
if (condFlag)
{
strSQL.Append(" AND ITEM_DESC4 LIKE '" + _item_desc4 + "%'");
}
else
{
strSQL.Append(" WHERE ITEM_DESC4 LIKE '" + _item_desc4 + "%'");
condFlag = true;
}
//Del 20070518 T.Sawada
//db.DbPsetString("ITEM_DESC4", _item_desc4);
//End Del
}
strSQL.Append(" ORDER BY STD_ITEM");
try
{
ds = db.DbDataSet(strSQL.ToString(), "dic");
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>
/// 画面ラベルの追加
/// </summary>
public int InsertScreenLabel()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
if (_db_type == "ORACLE")
{
strSQL.Append("INSERT INTO SCREEN_LABEL_MASTER ");
strSQL.Append("(");
strSQL.Append("SCREEN_ID, CONTROL_ID, STD_ITEM, ");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append(":SCREEN_ID, :CONTROL_ID, :STD_ITEM,");
strSQL.Append(":ENTRY_DATE, :CHG_DATE, :CHG_USER_ID, :UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("SCREEN_ID", _screen_id);
db.DbPsetString("CONTROL_ID", _control_id);
db.DbPsetString("STD_ITEM", _std_item);
db.DbPsetDate("ENTRY_DATE", DateTime.Now);
db.DbPsetDate("CHG_DATE", DateTime.Now);
db.DbPsetString("CHG_USER_ID", _chg_user_id);
db.DbPsetInt("UPDATE_CNTR", _update_cntr);
}
else
{
strSQL.Append("INSERT INTO SCREEN_LABEL_MASTER ");
strSQL.Append("(");
strSQL.Append("SCREEN_ID, CONTROL_ID, STD_ITEM, ");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append("@SCREEN_ID, @CONTROL_ID, @STD_ITEM,");
strSQL.Append("@ENTRY_DATE, @CHG_DATE, @CHG_USER_ID, @UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("@SCREEN_ID", _screen_id);
db.DbPsetString("@CONTROL_ID", _control_id);
db.DbPsetString("@STD_ITEM", _std_item);
db.DbPsetDate("@ENTRY_DATE", DateTime.Now);
db.DbPsetDate("@CHG_DATE", DateTime.Now);
db.DbPsetString("@CHG_USER_ID", _chg_user_id);
db.DbPsetInt("@UPDATE_CNTR", _update_cntr);
}
db.DbBeginTrans();
rtn = db.DbExecute(strSQL.ToString());
if (rtn != ComConst.FAILED)
{
db.DbCommit();
}
else
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
db.DbRollback();
}
return(rtn);
}
/// <summary>
/// 倉庫の変更
///
/// 使用画面：MstWhsMnt
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
int rtn = 0;
db.DbParametersClear();
db.DbPsetInt("RET", rtn, ComConst.DB_RTN);
db.DbPsetString("@I_WHS_CD", _whs_cd, ComConst.DB_IN);
db.DbPsetString("@I_MRP_WHS", _mrp_whs, ComConst.DB_IN);
db.DbPsetString("@I_WHS_DESC", _whs_desc, ComConst.DB_IN);
db.DbPsetString("@I_WHS_DESC2", _whs_desc2, ComConst.DB_IN);
db.DbPsetString("@I_RECV_LOC", _recv_loc, ComConst.DB_IN);
db.DbPsetInt("@I_WHS_TYPE", _whs_type, ComConst.DB_IN);
db.DbPsetInt("@I_WHS_ITEM_TYPE", _whs_item_type, ComConst.DB_IN);
db.DbPsetInt("@I_LOCATION_CTRL_FLAG", _location_ctrl_flag, ComConst.DB_IN);
db.DbPsetInt("@I_AUTO_HOUSING_TYPE", _auto_housing_type, ComConst.DB_IN);
db.DbPsetInt("@I_DIRECT_SHIP_FLAG", _direct_ship_flag, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_CD", _vendor_cd, ComConst.DB_IN);
db.DbPsetInt("@I_SUPPLY_TYPE", _supply_type, ComConst.DB_IN);//ADD UBIQ-LIU 2010/05/26
db.DbPsetInt("@I_CONS_WHS_FLAG", _cons_whs_flag, ComConst.DB_IN);
db.DbPsetString("@I_CONS_VENDOR_CD", _cons_vendor_cd, ComConst.DB_IN);
db.DbPsetString("@I_PASS_THRU_WHS_CD", _pass_thru_whs_cd, ComConst.DB_IN);
db.DbPsetInt("@I_BOND_FLAG", _bond_flag, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR1", _data_char1, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR2", _data_char2, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR3", _data_char3, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR4", _data_char4, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR5", _data_char5, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR6", _data_char6, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR7", _data_char7, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR8", _data_char8, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR9", _data_char9, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR10", _data_char10, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM1", _data_num1, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM2", _data_num2, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM3", _data_num3, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM4", _data_num4, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM5", _data_num5, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM6", _data_num6, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM7", _data_num7, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM8", _data_num8, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM9", _data_num9, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM10", _data_num10, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG1", _data_flag1, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG2", _data_flag2, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG3", _data_flag3, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG4", _data_flag4, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG5", _data_flag5, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG6", _data_flag6, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG7", _data_flag7, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG8", _data_flag8, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG9", _data_flag9, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG10", _data_flag10, ComConst.DB_IN);
db.DbPsetString("@I_CHG_PGM", _chg_pgm, ComConst.DB_IN);
db.DbPsetString("@I_CHG_USERID", _chg_user_id, ComConst.DB_IN);
db.DbPsetInt("@I_LANG", _lang, ComConst.DB_IN);
db.DbPsetInt("@I_UPDATE_CNTR", _update_cntr, ComConst.DB_IN);
db.DbPsetInt("@O_ERRCODE", _errcode, ComConst.DB_OUT);
db.DbPsetString("@O_MSG", _errmsg, ComConst.DB_OUT);
db.DbPsetInt("@O_SQLCODE", _sqlcode, ComConst.DB_OUT);
db.DbPsetString("@O_SQLMSG", _dbmsg, ComConst.DB_OUT);
db.DbPsetString("@O_PROC_NAME", _proc_name, ComConst.DB_OUT);
db.DbBeginTrans();
rtn = db.DbStored("SP_WAREHOUSE_UPDATE");
rtn = db.DbPgetInt("RET");
if (rtn == ComConst.SUCCEED)
{
db.DbCommit();
}
else
{
_errcode = db.DbPgetInt("@O_ERRCODE");
_errmsg = db.DbPgetString("@O_MSG");
_sqlcode = db.DbPgetInt("@O_SQLCODE");
_dbmsg = db.DbPgetString("@O_SQLMSG");
_proc_name = db.DbPgetString("@O_PROC_NAME");
_strErr = _errcode + ":" + _errmsg + "[" + _sqlcode + ":" + _dbmsg + "(" + _proc_name + ")]";
db.DbRollback();
rtn = ComConst.FAILED;
}
return(rtn);
}
/// <summary>
/// 辞書の追加
///
/// 使用画面：MstDicMnt
/// </summary>
public int Insert()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
if (_db_type == "ORACLE")
{
strSQL.Append("INSERT INTO DICTIONARY_MASTER ");
strSQL.Append("(");
strSQL.Append("STD_ITEM, ITEM_DESC1, ITEM_DESC2, ITEM_DESC3, ITEM_DESC4,");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID, UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append(":STD_ITEM, :ITEM_DESC1, :ITEM_DESC2, :ITEM_DESC3, :ITEM_DESC4,");
strSQL.Append(":ENTRY_DATE, :CHG_DATE, :CHG_USER_ID, :UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("STD_ITEM", _std_item);
db.DbPsetString("ITEM_DESC1", _item_desc1);
db.DbPsetString("ITEM_DESC2", _item_desc2);
db.DbPsetString("ITEM_DESC3", _item_desc3);
db.DbPsetString("ITEM_DESC4", _item_desc4);
db.DbPsetDate("ENTRY_DATE", DateTime.Now);
db.DbPsetDate("CHG_DATE", DateTime.Now);
db.DbPsetString("CHG_USER_ID", _chg_user_id);
db.DbPsetInt("UPDATE_CNTR", _update_cntr);
}
else
{
strSQL.Append("INSERT INTO DICTIONARY_MASTER ");
strSQL.Append("(");
strSQL.Append("STD_ITEM, ITEM_DESC1, ITEM_DESC2, ITEM_DESC3, ITEM_DESC4,");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID, UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append("@STD_ITEM, @ITEM_DESC1, @ITEM_DESC2, @ITEM_DESC3, @ITEM_DESC4,");
strSQL.Append("@ENTRY_DATE, @CHG_DATE, @CHG_USER_ID, @UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("@STD_ITEM", _std_item);
db.DbPsetString("@ITEM_DESC1", _item_desc1);
db.DbPsetString("@ITEM_DESC2", _item_desc2);
db.DbPsetString("@ITEM_DESC3", _item_desc3);
db.DbPsetString("@ITEM_DESC4", _item_desc4);
db.DbPsetDate("@ENTRY_DATE", DateTime.Now);
db.DbPsetDate("@CHG_DATE", DateTime.Now);
db.DbPsetString("@CHG_USER_ID", _chg_user_id);
db.DbPsetInt("@UPDATE_CNTR", _update_cntr);
}
db.DbBeginTrans();
rtn = db.DbExecute(strSQL.ToString());
if (rtn != ComConst.FAILED)
{
db.DbCommit();
}
else
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
db.DbRollback();
}
return(rtn);
}
/// <summary>
/// メッセージの変更
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
if (_db_type == "ORACLE")
{
strSQL.Append("UPDATE MESSAGE_MASTER ");
strSQL.Append("SET ");
strSQL.Append("MSG_DESC = :MSG_DESC,");
strSQL.Append("RANK = :RANK,");
strSQL.Append("CHG_DATE = :CHG_DATE,");
strSQL.Append("CHG_USER_ID = :CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR = UPDATE_CNTR + 1");
strSQL.Append(" WHERE MSG_CD = :MSG_CD");
strSQL.Append(" AND LANG = :LANG");
db.DbParametersClear();
db.DbPsetString("MSG_DESC", _msg_desc);
db.DbPsetString("RANK", _rank);
db.DbPsetDate("CHG_DATE", DateTime.Now);
db.DbPsetString("CHG_USER_ID", _chg_user_id);
db.DbPsetString("MSG_CD", _msg_cd);
db.DbPsetInt("LANG", _lang);
}
else
{
strSQL.Append("UPDATE MESSAGE_MASTER ");
strSQL.Append("SET ");
strSQL.Append("MSG_DESC = @MSG_DESC,");
strSQL.Append("RANK = @RANK,");
strSQL.Append("CHG_DATE = @CHG_DATE,");
strSQL.Append("CHG_USERID = @CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR = UPDATE_CNTR + 1");
strSQL.Append(" WHERE MSG_CD = @MSG_CD");
strSQL.Append(" AND LANG = @LANG");
db.DbParametersClear();
db.DbPsetString("@MSG_DESC", _msg_desc);
db.DbPsetString("@RANK", _rank);
db.DbPsetDate("@CHG_DATE", DateTime.Now);
db.DbPsetString("@CHG_USER_ID", _chg_user_id);
db.DbPsetString("@MSG_CD", _msg_cd);
db.DbPsetInt("@LANG", _lang);
}
db.DbBeginTrans();
rtn = db.DbExecute(strSQL.ToString());
if (rtn != ComConst.FAILED)
{
rtn = ComConst.SUCCEED;
db.DbCommit();
}
else
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
db.DbRollback();
}
return(rtn);
}
/// <summary>
/// 品目別段取条件マスタを検索して明細部を編集する
///
/// 使用画面：MstItemSetupMnt
/// </summary>
public DataSet GetItemSetupList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append(" ISM.RESOURCE_CD, R.RESOURCE_DESC, ISM.ITEM_NO, ISM.PREV_ITEM_NO, ISM.SETUP_TIME,");
strSQL.Append(" I1.ITEM_DESC, I2.ITEM_DESC PREV_ITEM_DESC ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
strSQL.Append(" FROM ITEM_SETUP_MASTER ISM ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER R ON ISM.RESOURCE_CD = R.RESOURCE_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I1 ON ISM.ITEM_NO = I1.ITEM_NO ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I2 ON ISM.PREV_ITEM_NO = I2.ITEM_NO ");
strSQL.Append(" WHERE ");
strSQL.Append(" ISM.RESOURCE_CD = :RESOURCE_CD ");
db.DbPsetString("RESOURCE_CD", _resource_cd);
}
else
{
db.DbParametersClear();
strSQL.Append(" FROM ITEM_SETUP_MASTER ISM ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER R ON ISM.RESOURCE_CD = R.RESOURCE_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I1 ON ISM.ITEM_NO = I1.ITEM_NO ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I2 ON ISM.PREV_ITEM_NO = I2.ITEM_NO ");
strSQL.Append(" WHERE ");
strSQL.Append(" ISM.RESOURCE_CD = @RESOURCE_CD ");
db.DbPsetString("@RESOURCE_CD", _resource_cd);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "TBLSUB");
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
/// </summary>
public int GetVendorDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("V.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("ADDRESS3, ADDRESS4, STATE2, ADDRESS2_1, ");
strSQL.Append("ADDRESS2_2, ADDRESS2_3, ADDRESS2_4, ");
strSQL.Append("V.DATA_CHAR1, V.DATA_CHAR2, ");
strSQL.Append("V.DATA_CHAR3, V.DATA_CHAR4, V.DATA_CHAR5, V.DATA_CHAR6, ");
strSQL.Append("V.DATA_CHAR7, V.DATA_CHAR8, V.DATA_CHAR9, V.DATA_CHAR10, ");
strSQL.Append("V.DATA_NUM1, V.DATA_NUM2, V.DATA_NUM3, V.DATA_NUM4, ");
strSQL.Append("V.DATA_NUM5, V.DATA_NUM6, V.DATA_NUM7, V.DATA_NUM8, ");
strSQL.Append("V.DATA_NUM9, V.DATA_NUM10, V.DATA_FLAG1, V.DATA_FLAG2, ");
strSQL.Append("V.DATA_FLAG3, V.DATA_FLAG4, V.DATA_FLAG5, V.DATA_FLAG6, ");
strSQL.Append("V.DATA_FLAG7, V.DATA_FLAG8, V.DATA_FLAG9, V.DATA_FLAG10, ");
strSQL.Append("V.ENTRY_DATE, V.CHG_DATE, V.CHG_PGM, V.CHG_USERID, ");
strSQL.Append("V.UPDATE_CNTR, ");
strSQL.Append("V.REJ_WHS, W2.WHS_DESC REJ_WHS_DESC, ");
strSQL.Append("W.WHS_DESC AS SUPPLY_WHS_DESC, W1.WHS_DESC AS BOND_SUPPLY_WHS_DESC, ");
strSQL.Append("S1.DATA_CHAR AS CUR_DESC ");
//strSQL.Append(" FROM VENDOR_MASTER V");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM VENDOR_MASTER V, WAREHOUSE_MASTER W, WAREHOUSE_MASTER W1 ");
strSQL.Append(" WHERE V.VENDOR_CD = :VENDOR_CD ");
strSQL.Append(" AND W.SUPPLY_WHS = W.WHS_CD(+) ");
strSQL.Append(" AND W.BOND_SUPPLY_WHS = W1.WHS_CD(+) ");
db.DbParametersClear();
db.DbPsetString("VENDOR_CD", _vendor_cd);
}
else
{
strSQL.Append(" FROM VENDOR_MASTER V ");
strSQL.Append(" LEFT OUTER JOIN WAREHOUSE_MASTER W ON V.SUPPLY_WHS = W.WHS_CD ");
strSQL.Append(" LEFT OUTER JOIN WAREHOUSE_MASTER W1 ON V.BOND_SUPPLY_WHS = W1.WHS_CD");
strSQL.Append(" LEFT OUTER JOIN WAREHOUSE_MASTER W2 ON V.REJ_WHS = W2.WHS_CD ");
strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S1 ON S1.KEY01 = 'CURRENCY_CD' ");
strSQL.Append(" AND S1.KEY02 = V.CUR_CD ");
strSQL.Append(" WHERE V.VENDOR_CD = @VENDOR_CD ");
db.DbParametersClear();
db.DbPsetString("@VENDOR_CD", _vendor_cd);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_vendor_cd = db.Row("VENDOR_CD");
_beg_eff_date = int.Parse(db.Row("BEG_EFF_DATE"));
_end_eff_date = int.Parse(db.Row("END_EFF_DATE"));
_po_stop_flag = int.Parse(db.Row("PO_STOP_FLAG"));
_vendor_name = db.Row("VENDOR_NAME");
_vendor_desc = db.Row("VENDOR_DESC");
_vendor_name2 = db.Row("VENDOR_NAME2");
_vendor_desc2 = db.Row("VENDOR_DESC2");
_country_cd = db.Row("COUNTRY_CD");
_foreign_flag = int.Parse(db.Row("FOREIGN_FLAG"));
_cur_cd = db.Row("CUR_CD");
_supply_whs = db.Row("SUPPLY_WHS");
_bond_supply_whs = db.Row("BOND_SUPPLY_WHS");
_rej_whs = db.Row("REJ_WHS"); //ADD BY UBIQ-LIU 2010/11/23
_rej_whs_desc = db.Row("REJ_WHS_DESC"); //ADD BY UBIQ-LIU 2010/11/23
_po_slipless_flag = int.Parse(db.Row("PO_SLIPLESS_FLAG"));
_po_slip_type = db.Row("PO_SLIP_TYPE");
_fcst_flag = int.Parse(db.Row("FCST_FLAG"));
_pay_terms = db.Row("PAY_TERMS");
_vendor_attn = db.Row("VENDOR_ATTN");
_telno = db.Row("TELNO");
_faxno = db.Row("FAXNO");
_zip_cd = db.Row("ZIP_CD");
_state = db.Row("STATE");
_address1 = db.Row("ADDRESS1");
_address2 = db.Row("ADDRESS2");
_address3 = db.Row("ADDRESS3");
_address4 = db.Row("ADDRESS4");
_state2 = db.Row("STATE2");
_address2_1 = db.Row("ADDRESS2_1");
_address2_2 = db.Row("ADDRESS2_2");
_address2_3 = db.Row("ADDRESS2_3");
_address2_4 = db.Row("ADDRESS2_4");
_data_char1 = db.Row("DATA_CHAR1");
_data_char2 = db.Row("DATA_CHAR2");
_data_char3 = db.Row("DATA_CHAR3");
_data_char4 = db.Row("DATA_CHAR4");
_data_char5 = db.Row("DATA_CHAR5");
_data_char6 = db.Row("DATA_CHAR6");
_data_char7 = db.Row("DATA_CHAR7");
_data_char8 = db.Row("DATA_CHAR8");
_data_char9 = db.Row("DATA_CHAR9");
_data_char10 = db.Row("DATA_CHAR10");
_data_num1 = double.Parse(db.Row("DATA_NUM1"));
_data_num2 = double.Parse(db.Row("DATA_NUM2"));
_data_num3 = double.Parse(db.Row("DATA_NUM3"));
_data_num4 = double.Parse(db.Row("DATA_NUM4"));
_data_num5 = double.Parse(db.Row("DATA_NUM5"));
_data_num6 = double.Parse(db.Row("DATA_NUM6"));
_data_num7 = double.Parse(db.Row("DATA_NUM7"));
_data_num8 = double.Parse(db.Row("DATA_NUM8"));
_data_num9 = double.Parse(db.Row("DATA_NUM9"));
_data_num10 = double.Parse(db.Row("DATA_NUM10"));
_data_flag1 = int.Parse(db.Row("DATA_FLAG1"));
_data_flag2 = int.Parse(db.Row("DATA_FLAG2"));
_data_flag3 = int.Parse(db.Row("DATA_FLAG3"));
_data_flag4 = int.Parse(db.Row("DATA_FLAG4"));
_data_flag5 = int.Parse(db.Row("DATA_FLAG5"));
_data_flag6 = int.Parse(db.Row("DATA_FLAG6"));
_data_flag7 = int.Parse(db.Row("DATA_FLAG7"));
_data_flag8 = int.Parse(db.Row("DATA_FLAG8"));
_data_flag9 = int.Parse(db.Row("DATA_FLAG9"));
_data_flag10 = int.Parse(db.Row("DATA_FLAG10"));
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
_chg_pgm = db.Row("CHG_PGM");
_chg_user_id = db.Row("CHG_USERID");
_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
_supply_whs_desc = db.Row("SUPPLY_WHS_DESC");
_bond_supply_whs_desc = db.Row("BOND_SUPPLY_WHS_DESC");
//ADD BY LIU 2008.11.19
_cur_desc = db.Row("CUR_DESC");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 工程の変更
///
/// 使用画面：MstProcessMnt
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
int rtn = 0;
db.DbParametersClear();
db.DbPsetInt("RET", rtn, ComConst.DB_RTN);
db.DbPsetString("@I_WS_CD", _ws_cd, ComConst.DB_IN);
db.DbPsetString("@I_PROCESS_CD", _process_cd, ComConst.DB_IN);
db.DbPsetString("@I_PROCESS_DESC", _process_desc, ComConst.DB_IN);
db.DbPsetString("@I_PROCESS_DESC2", _process_desc2, ComConst.DB_IN);
db.DbPsetString("@I_COST_CENTER", _cost_center, ComConst.DB_IN);
db.DbPsetInt("@I_SC_FLAG", _sc_flag, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_CD", _vendor_cd, ComConst.DB_IN);
db.DbPsetDouble("@I_CAPA_MH", _capa_mh, ComConst.DB_IN);
db.DbPsetString("@I_TIME_CONSTRAINT", _time_constraint, ComConst.DB_IN); // IMV3
db.DbPsetString("@I_DATA_CHAR1", _data_char1, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR2", _data_char2, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR3", _data_char3, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR4", _data_char4, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR5", _data_char5, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR6", _data_char6, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR7", _data_char7, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR8", _data_char8, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR9", _data_char9, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR10", _data_char10, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM1", _data_num1, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM2", _data_num2, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM3", _data_num3, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM4", _data_num4, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM5", _data_num5, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM6", _data_num6, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM7", _data_num7, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM8", _data_num8, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM9", _data_num9, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM10", _data_num10, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG1", _data_flag1, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG2", _data_flag2, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG3", _data_flag3, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG4", _data_flag4, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG5", _data_flag5, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG6", _data_flag6, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG7", _data_flag7, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG8", _data_flag8, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG9", _data_flag9, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG10", _data_flag10, ComConst.DB_IN);
db.DbPsetString("@I_CHG_PGM", _chg_pgm, ComConst.DB_IN);
db.DbPsetString("@I_CHG_USERID", _chg_user_id, ComConst.DB_IN);
db.DbPsetInt("@I_LANG", _lang, ComConst.DB_IN);
db.DbPsetInt("@I_UPDATE_CNTR", _update_cntr, ComConst.DB_IN);
db.DbPsetInt("@O_ERRCODE", _errcode, ComConst.DB_OUT);
db.DbPsetString("@O_MSG", _errmsg, ComConst.DB_OUT);
db.DbPsetInt("@O_SQLCODE", _sqlcode, ComConst.DB_OUT);
db.DbPsetString("@O_SQLMSG", _dbmsg, ComConst.DB_OUT);
db.DbPsetString("@O_PROC_NAME", _proc_name, ComConst.DB_OUT);
db.DbBeginTrans();
rtn = db.DbStored("SP_PROCESS_UPDATE");
rtn = db.DbPgetInt("RET");
if (rtn == ComConst.SUCCEED)
{
db.DbCommit();
}
else
{
_errcode = db.DbPgetInt("@O_ERRCODE");
_errmsg = db.DbPgetString("@O_MSG");
_sqlcode = db.DbPgetInt("@O_SQLCODE");
_dbmsg = db.DbPgetString("@O_SQLMSG");
_proc_name = db.DbPgetString("@O_PROC_NAME");
_strErr = _errcode + ":" + _errmsg + "[" + _sqlcode + ":" + _dbmsg + "(" + _proc_name + ")]";
db.DbRollback();
rtn = ComConst.FAILED;
}
return(rtn);
}
/// <summary>
/// 仕入先の変更
///
/// 使用画面：MstVendorMnt
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
int rtn = 0;
db.DbParametersClear();
db.DbPsetInt("RET", rtn, ComConst.DB_RTN);
db.DbPsetString("@I_VENDOR_CD", _vendor_cd, ComConst.DB_IN);
db.DbPsetInt("@I_BEG_EFF_DATE", _beg_eff_date, ComConst.DB_IN);
db.DbPsetInt("@I_END_EFF_DATE", _end_eff_date, ComConst.DB_IN);
db.DbPsetInt("@I_PO_STOP_FLAG", _po_stop_flag, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_NAME", _vendor_name, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_DESC", _vendor_desc, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_NAME2", _vendor_name2, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_DESC2", _vendor_desc2, ComConst.DB_IN);
db.DbPsetString("@I_COUNTRY_CD", _country_cd, ComConst.DB_IN);
db.DbPsetInt("@I_FOREIGN_FLAG", _foreign_flag, ComConst.DB_IN);
db.DbPsetString("@I_CUR_CD", _cur_cd, ComConst.DB_IN);
db.DbPsetString("@I_SUPPLY_WHS", _supply_whs, ComConst.DB_IN);
db.DbPsetString("@I_BOND_SUPPLY_WHS", _bond_supply_whs, ComConst.DB_IN);
db.DbPsetString("@I_REJ_WHS", _rej_whs, ComConst.DB_IN); //ADD BY UBIQ-LIU 2010/11/23
db.DbPsetInt("@I_PO_SLIPLESS_FLAG", _po_slipless_flag, ComConst.DB_IN);
db.DbPsetString("@I_PO_SLIP_TYPE", _po_slip_type, ComConst.DB_IN);
db.DbPsetInt("@I_FCST_FLAG", _fcst_flag, ComConst.DB_IN);
db.DbPsetString("@I_PAY_TERMS", _pay_terms, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_ATTN", _vendor_attn, ComConst.DB_IN);
db.DbPsetString("@I_TELNO", _telno, ComConst.DB_IN);
db.DbPsetString("@I_FAXNO", _faxno, ComConst.DB_IN);
db.DbPsetString("@I_ZIP_CD", _zip_cd, ComConst.DB_IN);
db.DbPsetString("@I_STATE", _state, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS1", _address1, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS2", _address2, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS3", _address3, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS4", _address4, ComConst.DB_IN);
db.DbPsetString("@I_STATE2", _state2, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS2_1", _address2_1, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS2_2", _address2_2, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS2_3", _address2_3, ComConst.DB_IN);
db.DbPsetString("@I_ADDRESS2_4", _address2_4, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR1", _data_char1, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR2", _data_char2, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR3", _data_char3, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR4", _data_char4, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR5", _data_char5, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR6", _data_char6, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR7", _data_char7, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR8", _data_char8, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR9", _data_char9, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR10", _data_char10, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM1", _data_num1, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM2", _data_num2, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM3", _data_num3, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM4", _data_num4, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM5", _data_num5, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM6", _data_num6, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM7", _data_num7, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM8", _data_num8, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM9", _data_num9, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM10", _data_num10, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG1", _data_flag1, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG2", _data_flag2, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG3", _data_flag3, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG4", _data_flag4, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG5", _data_flag5, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG6", _data_flag6, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG7", _data_flag7, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG8", _data_flag8, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG9", _data_flag9, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG10", _data_flag10, ComConst.DB_IN);
db.DbPsetString("@I_CHG_PGM", _chg_pgm, ComConst.DB_IN);
db.DbPsetString("@I_CHG_USERID", _chg_user_id, ComConst.DB_IN);
db.DbPsetInt("@I_LANG", _lang, ComConst.DB_IN);
db.DbPsetInt("@I_UPDATE_CNTR", _update_cntr, ComConst.DB_IN);
db.DbPsetInt("@O_ERRCODE", _errcode, ComConst.DB_OUT);
db.DbPsetString("@O_MSG", _errmsg, ComConst.DB_OUT);
db.DbPsetInt("@O_SQLCODE", _sqlcode, ComConst.DB_OUT);
db.DbPsetString("@O_SQLMSG", _dbmsg, ComConst.DB_OUT);
db.DbPsetString("@O_PROC_NAME", _proc_name, ComConst.DB_OUT);
db.DbBeginTrans();
rtn = db.DbStored("SP_VENDOR_UPDATE");
rtn = db.DbPgetInt("RET");
if (rtn == ComConst.SUCCEED)
{
db.DbCommit();
}
else
{
_errcode = db.DbPgetInt("@O_ERRCODE");
_errmsg = db.DbPgetString("@O_MSG");
_sqlcode = db.DbPgetInt("@O_SQLCODE");
_dbmsg = db.DbPgetString("@O_SQLMSG");
_proc_name = db.DbPgetString("@O_PROC_NAME");
_strErr = _errcode + ":" + _errmsg + "[" + _sqlcode + ":" + _dbmsg + "(" + _proc_name + ")]";
db.DbRollback();
rtn = ComConst.FAILED;
}
return(rtn);
}
/// <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>
/// 部門詳細の取得
///
/// 使用画面：MstDeptMnt
/// </summary>
public DataSet GetDEPTList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
strSQL.Append(" SELECT ");
strSQL.Append(" D.DEPT_CD, D.DEPT_DESC1, D.DEPT_DESC2, D.DEPT_DESC3, ");
strSQL.Append(" D.DEPT_DESC4, D.DEPT_TYPE, D.NUMBER_DEPT, D.AREA_DEPT , ");
switch (_lang)
{
case 2:
strSQL.Append(" DEPT_DESC2 AS DEPT_DESC ");
break;
case 3:
strSQL.Append(" DEPT_DESC3 AS DEPT_DESC ");
break;
case 4:
strSQL.Append(" DEPT_DESC4 AS DEPT_DESC ");
break;
default:
strSQL.Append(" DEPT_DESC1 AS DEPT_DESC ");
break;
}
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM DEPT_MASTER D ");
strSQL.Append(" WHERE D.DEPT_CD = :DEPT_CD");
db.DbParametersClear();
db.DbPsetString("DEPT_CD", _dept_cd);
}
else
{
strSQL.Append(" FROM DEPT_MASTER D ");
strSQL.Append(" WHERE 1=1 ");
db.DbParametersClear();
if (_dept_type >= 0)
{
strSQL.Append(" AND D.DEPT_TYPE =@DEPT_TYPE");
db.DbPsetInt("@DEPT_TYPE", _dept_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>
/// 部門詳細の取得
///
/// 使用画面：MstDeptMnt
/// </summary>
public int GetDEPTDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
strSQL.Append(" SELECT ");
strSQL.Append(" D.DEPT_CD, D.DEPT_DESC1, D.DEPT_DESC2, D.DEPT_DESC3, ");
strSQL.Append(" D.DEPT_DESC4, D.DEPT_TYPE, D.NUMBER_DEPT, D.AREA_DEPT , ");
switch (_lang)
{
case 2:
strSQL.Append(" DEPT_DESC2 AS DEPT_DESC ");
break;
case 3:
strSQL.Append(" DEPT_DESC3 AS DEPT_DESC ");
break;
case 4:
strSQL.Append(" DEPT_DESC4 AS DEPT_DESC ");
break;
default:
strSQL.Append(" DEPT_DESC1 AS DEPT_DESC ");
break;
}
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM DEPT_MASTER D ");
strSQL.Append(" WHERE D.DEPT_CD = :DEPT_CD");
db.DbParametersClear();
db.DbPsetString("DEPT_CD", _dept_cd);
}
else
{
strSQL.Append(" FROM DEPT_MASTER D ");
strSQL.Append(" WHERE D.DEPT_CD = @DEPT_CD");
db.DbParametersClear();
db.DbPsetString("@DEPT_CD", _dept_cd);
if (_dept_type >= 0)
{
strSQL.Append(" AND D.DEPT_TYPE =@DEPT_TYPE");
db.DbPsetInt("@DEPT_TYPE", _dept_type);
}
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_dept_cd = db.Row("DEPT_CD");
_dept_desc = db.Row("DEPT_DESC");
_dept_desc1 = db.Row("DEPT_DESC1");
_dept_desc2 = db.Row("DEPT_DESC2");
_dept_desc3 = db.Row("DEPT_DESC3");
_dept_desc4 = db.Row("DEPT_DESC4");
_dept_type = int.Parse(db.Row("DEPT_TYPE"));
_number_dept = double.Parse(db.Row("NUMBER_DEPT"));
_area_dept = double.Parse(db.Row("AREA_DEPT"));
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 指定ユーザーのロール一覧
///
/// 使用画面：MstUserMnt
/// </summary>
public DataSet GetRoleListByUser()
{
ComDB db = new ComDB("", "COM");
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT UR.ROLE, R.ROLE_DESC1, R.ROLE_DESC2, R.ROLE_DESC3,");
strSQL.Append("R.ROLE_DESC4, ");
switch (_lang)
{
case 2:
strSQL.Append("R.ROLE_DESC2 AS ROLE_DESC, ");
break;
case 3:
strSQL.Append("R.ROLE_DESC3 AS ROLE_DESC, ");
break;
case 4:
strSQL.Append("R.ROLE_DESC4 AS ROLE_DESC, ");
break;
default:
strSQL.Append("R.ROLE_DESC1 AS ROLE_DESC, ");
break;
}
strSQL.Append(" UR.ENTRY_DATE, UR.CHG_DATE, UR.CHG_USER_ID,");
strSQL.Append(" UR.UPDATE_CNTR");
strSQL.Append(" FROM USER_ROLE_MASTER UR, ROLE_MASTER R");
strSQL.Append(" WHERE UR.USER_ID = @USER_ID");
strSQL.Append(" AND UR.ROLE = R.ROLE");
strSQL.Append(" ORDER BY UR.ROLE");
db.DbParametersClear();
db.DbPsetString("@USER_ID", _user_id);
try
{
ds = db.DbDataSet(strSQL.ToString(), "role");
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>
/// 製造資源詳細の取得
///
/// 使用画面：MstRoutineResourceMnt
/// </summary>
public int GetRoutineResourceDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("R.ITEM_NO, R.WS_CD, R.ROUTINE_SEQ, R.PROCESS_CD, ");
strSQL.Append("R.PTY_NO, R.RESOURCE_CD, R.SETUP_TIME, R.UNIT_TIME, ");
strSQL.Append("R.UNIT_QTY, R.TRANSFER_TIME, R.MFG_LOT_QTY, R.SPEC_CD, ");
strSQL.Append("R.JIGU, R.DIE, ");
strSQL.Append("I.ITEM_DESC AS ITEM_DESC,");
strSQL.Append("W.WS_DESC AS WS_DESC, P.PROCESS_DESC AS PROCESS_DESC, ");
strSQL.Append("RO.BEG_EFF_DATE AS BEG_EFF_DATE, RO.END_EFF_DATE AS END_EFF_DATE, ");
strSQL.Append("RE.RESOURCE_DESC AS RESOURCE_DESC, ");
strSQL.Append("SP.DATA_CHAR AS SPEC_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM ROUTINE_RESOURCE_MASTER R, ROUTINE_MASTER RO, ITEM_MASTER I, ");
strSQL.Append(" WORKSHOP_MASTER W, PROCESS_MASTER P, RESOURCE_MASTER RE, SYSTEM_PARAMETER SP");
strSQL.Append(" WHERE R.ITEM_NO = :ITEM_NO");
strSQL.Append(" AND R.WS_CD = :WS_CD");
strSQL.Append(" AND R.ROUTINE_SEQ = :ROUTINE_SEQ");
strSQL.Append(" AND R.PROCESS_CD = :PROCESS_CD");
//strSQL.Append(" AND R.PTY_NO = :PTY_NO");
strSQL.Append(" AND R.RESOURCE_CD = :RESOURCE_CD");
strSQL.Append(" AND R.ITEM_NO = RO.ITEM_NO(+)");
strSQL.Append(" AND R.WS_CD = RO.WS_CD(+)");
strSQL.Append(" AND R.ROUTINE_SEQ = RO.ROUTINE_SEQ(+)");
strSQL.Append(" AND R.ITEM_NO = I.ITEM_NO(+)");
strSQL.Append(" AND R.WS_CD = W.WS_CD(+)");
strSQL.Append(" AND R.WS_CD = P.WS_CD(+)");
strSQL.Append(" AND R.PROCESS_CD = P.PROCESS_CD(+)");
strSQL.Append(" AND R.RESOURCE_CD = RE.RESOURCE_CD(+)");
strSQL.Append(" AND SP.KEY01 = 'SPEC_CD'");
strSQL.Append(" AND SP.KEY02 = R.SPEC_CD(+)");
db.DbParametersClear();
db.DbPsetString("ITEM_NO", _item_no);
db.DbPsetString("WS_CD", _ws_cd);
db.DbPsetInt("ROUTINE_SEQ", _routine_seq);
db.DbPsetString("PROCESS_CD", _process_cd);
//db.DbPsetInt("PTY_NO", _pty_no);
db.DbPsetString("RESOURCE_CD", _resource_cd);
}
else
{
strSQL.Append(" FROM ROUTINE_RESOURCE_MASTER R ");
strSQL.Append(" LEFT OUTER JOIN ROUTINE_MASTER RO ON R.ITEM_NO=RO.ITEM_NO ");
strSQL.Append(" AND R.WS_CD=RO.WS_CD AND R.ROUTINE_SEQ=RO.ROUTINE_SEQ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON R.WS_CD = W.WS_CD");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I ON R.ITEM_NO = I.ITEM_NO");
strSQL.Append(" LEFT OUTER JOIN PROCESS_MASTER P ON R.WS_CD = P.WS_CD AND R.PROCESS_CD = P.PROCESS_CD");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER RE ON R.RESOURCE_CD=RE.RESOURCE_CD");
strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER SP ON SP.KEY01 = 'SPEC_CD' AND SP.KEY02=R.SPEC_CD");
strSQL.Append(" WHERE R.ITEM_NO = @ITEM_NO ");
strSQL.Append(" AND R.WS_CD = @WS_CD ");
strSQL.Append(" AND R.ROUTINE_SEQ = @ROUTINE_SEQ");
strSQL.Append(" AND R.PROCESS_CD = @PROCESS_CD");
//strSQL.Append(" AND R.PTY_NO = @PTY_NO");
strSQL.Append(" AND R.RESOURCE_CD = @RESOURCE_CD");
db.DbParametersClear();
db.DbPsetString("@ITEM_NO", _item_no);
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetInt("@ROUTINE_SEQ", _routine_seq);
db.DbPsetString("@PROCESS_CD", _process_cd);
//db.DbPsetInt("@PTY_NO", _pty_no);
db.DbPsetString("@RESOURCE_CD", _resource_cd);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_item_no = db.Row("ITEM_NO");
_ws_cd = db.Row("WS_CD");
_routine_seq = int.Parse(db.Row("ROUTINE_SEQ"));
_process_cd = db.Row("PROCESS_CD");
_pty_no = int.Parse(db.Row("PTY_NO"));
_resource_cd = db.Row("RESOURCE_CD");
_setup_time = double.Parse(db.Row("SETUP_TIME"));
_unit_time = double.Parse(db.Row("UNIT_TIME"));
_unit_qty = double.Parse(db.Row("UNIT_QTY"));
_transfer_time = double.Parse(db.Row("TRANSFER_TIME"));
_mfg_lot_qty = double.Parse(db.Row("MFG_LOT_QTY"));
_spec_cd = db.Row("SPEC_CD");
_jigu = db.Row("JIGU");
_die = db.Row("DIE");
_item_desc = db.Row("ITEM_DESC");
_ws_desc = db.Row("WS_DESC");
_process_desc = db.Row("PROCESS_DESC");
_resource_desc = db.Row("RESOURCE_DESC");
_spec_desc = db.Row("SPEC_DESC");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 工程一覧の取得
/// <para>使用画面：[MfgProcessComplCancel]</para>
/// </summary>
public DataSet GetProcessList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("WS_CD, PROCESS_CD, PROCESS_DESC, PROCESS_DESC2, COST_CENTER, ");
strSQL.Append("SC_FLAG, VENDOR_CD, CAPA_MH, TIME_CONSTRAINT, 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 PROCESS_MASTER ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
if (_process_cd != "")
{
strSQL.Append(" WHERE PROCESS_CD = :PROCESS_CD ");
db.DbPsetString("PROCESS_CD", _process_cd);
}
}
else
{
db.DbParametersClear();
if (_process_cd != "")
{
strSQL.Append(" WHERE PROCESS_CD = @PROCESS_CD ");
db.DbPsetString("@PROCESS_CD", _process_cd);
}
}
strSQL.Append(" ORDER BY WS_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);
}
}
/// <summary>
/// メッセージの追加
/// </summary>
public int Insert()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
if (_db_type == "ORACLE")
{
strSQL.Append("INSERT INTO MESSAGE_MASTER ");
strSQL.Append("(");
strSQL.Append("MSG_CD, LANG, MSG_DESC, RANK,");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID, UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append(":MSG_CD, :LANG, :MSG_DESC, :RANK,");
strSQL.Append(":ENTRY_DATE, :CHG_DATE, :CHG_USER_ID, :UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("MSG_CD", _msg_cd);
db.DbPsetInt("LANG", _lang);
db.DbPsetString("MSG_DESC", _msg_desc);
db.DbPsetString("RANK", _rank);
db.DbPsetDate("ENTRY_DATE", DateTime.Now);
db.DbPsetDate("CHG_DATE", DateTime.Now);
db.DbPsetString("CHG_USER_ID", _chg_user_id);
db.DbPsetInt("UPDATE_CNTR", _update_cntr);
}
else
{
strSQL.Append("INSERT INTO MESSAGE_MASTER ");
strSQL.Append("(");
strSQL.Append("MSG_CD, LANG, MSG_DESC, RANK,");
strSQL.Append("ENTRY_DATE, CHG_DATE, CHG_USER_ID, UPDATE_CNTR");
strSQL.Append(") VALUES (");
strSQL.Append("@MSG_CD, @LANG, @MSG_DESC, @RANK,");
strSQL.Append("@ENTRY_DATE, @CHG_DATE, @CHG_USER_ID, @UPDATE_CNTR)");
db.DbParametersClear();
db.DbPsetString("@MSG_CD", _msg_cd);
db.DbPsetInt("@LANG", _lang);
db.DbPsetString("@MSG_DESC", _msg_desc);
db.DbPsetString("@RANK", _rank);
db.DbPsetDate("@ENTRY_DATE", DateTime.Now);
db.DbPsetDate("@CHG_DATE", DateTime.Now);
db.DbPsetString("@CHG_USER_ID", _chg_user_id);
db.DbPsetInt("@UPDATE_CNTR", _update_cntr);
}
db.DbBeginTrans();
rtn = db.DbExecute(strSQL.ToString());
if (rtn != ComConst.FAILED)
{
db.DbCommit();
}
else
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
db.DbRollback();
}
return(rtn);
}
/// <summary>
/// 工程詳細の取得
///
/// 使用画面：MstProcessMnt
/// </summary>
public int GetProcessDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("P.WS_CD, P.PROCESS_CD, P.PROCESS_DESC, P.PROCESS_DESC2, P.COST_CENTER,");
strSQL.Append("P.SC_FLAG, P.VENDOR_CD, P.CAPA_MH, P.TIME_CONSTRAINT, P.DATA_CHAR1, P.DATA_CHAR2, ");
strSQL.Append("P.DATA_CHAR3, P.DATA_CHAR4, P.DATA_CHAR5, P.DATA_CHAR6, ");
strSQL.Append("P.DATA_CHAR7, P.DATA_CHAR8, P.DATA_CHAR9, P.DATA_CHAR10, ");
strSQL.Append("P.DATA_NUM1, P.DATA_NUM2, P.DATA_NUM3, P.DATA_NUM4, ");
strSQL.Append("P.DATA_NUM5, P.DATA_NUM6, P.DATA_NUM7, P.DATA_NUM8, ");
strSQL.Append("P.DATA_NUM9, P.DATA_NUM10, P.DATA_FLAG1, P.DATA_FLAG2, ");
strSQL.Append("P.DATA_FLAG3, P.DATA_FLAG4, P.DATA_FLAG5, P.DATA_FLAG6, ");
strSQL.Append("P.DATA_FLAG7, P.DATA_FLAG8, P.DATA_FLAG9, P.DATA_FLAG10, ");
strSQL.Append("P.ENTRY_DATE, P.CHG_DATE, P.CHG_PGM, P.CHG_USERID, ");
strSQL.Append("P.UPDATE_CNTR, V.VENDOR_NAME, ");
strSQL.Append("W.WS_DESC "); // IMV3
//strSQL.Append("S.DATA_CHAR AS COST_CENTER_NAME, V.VENDOR_NAME ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM PROCESS_MASTER P, SYSTEM_PARAMETER S, VENDOR_MASTER V");
strSQL.Append(" WHERE P.WS_CD = :WS_CD");
//strSQL.Append(" AND P.PROCESS_CD = :PROCESS_CD");
//strSQL.Append(" AND P.COST_CENTER = S.KEY02(+)");
strSQL.Append(" AND S.KEY01 = 'COST_CENTER'");
strSQL.Append(" AND P.VENDOR_CD = V.VENDOR_CD(+)");
db.DbParametersClear();
db.DbPsetString("WS_CD", _ws_cd);
db.DbPsetString("PROCESS_CD", _process_cd);
}
else
{
strSQL.Append(" FROM PROCESS_MASTER P ");
//strSQL.Append(" LEFT OUTER JOIN SYSTEM_PARAMETER S ON S.KEY01 = 'COST_CENTER' AND P.COST_CENTER = S.KEY02 ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ON P.VENDOR_CD = V.VENDOR_CD");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON W.WS_CD = P.WS_CD");
strSQL.Append(" WHERE P.WS_CD = @WS_CD AND P.PROCESS_CD = @PROCESS_CD ");
db.DbParametersClear();
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetString("@PROCESS_CD", _process_cd);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_ws_cd = db.Row("WS_CD");
_process_cd = db.Row("PROCESS_CD");
_process_desc = db.Row("PROCESS_DESC");
_process_desc2 = db.Row("PROCESS_DESC2");
_cost_center = db.Row("COST_CENTER");
_sc_flag = int.Parse(db.Row("SC_FLAG"));
_vendor_cd = db.Row("VENDOR_CD");
_capa_mh = double.Parse(db.Row("CAPA_MH"));
_time_constraint = db.Row("TIME_CONSTRAINT"); // IMV3
_data_char1 = db.Row("DATA_CHAR1");
_data_char2 = db.Row("DATA_CHAR2");
_data_char3 = db.Row("DATA_CHAR3");
_data_char4 = db.Row("DATA_CHAR4");
_data_char5 = db.Row("DATA_CHAR5");
_data_char6 = db.Row("DATA_CHAR6");
_data_char7 = db.Row("DATA_CHAR7");
_data_char8 = db.Row("DATA_CHAR8");
_data_char9 = db.Row("DATA_CHAR9");
_data_char10 = db.Row("DATA_CHAR10");
_data_num1 = double.Parse(db.Row("DATA_NUM1"));
_data_num2 = double.Parse(db.Row("DATA_NUM2"));
_data_num3 = double.Parse(db.Row("DATA_NUM3"));
_data_num4 = double.Parse(db.Row("DATA_NUM4"));
_data_num5 = double.Parse(db.Row("DATA_NUM5"));
_data_num6 = double.Parse(db.Row("DATA_NUM6"));
_data_num7 = double.Parse(db.Row("DATA_NUM7"));
_data_num8 = double.Parse(db.Row("DATA_NUM8"));
_data_num9 = double.Parse(db.Row("DATA_NUM9"));
_data_num10 = double.Parse(db.Row("DATA_NUM10"));
_data_flag1 = int.Parse(db.Row("DATA_FLAG1"));
_data_flag2 = int.Parse(db.Row("DATA_FLAG2"));
_data_flag3 = int.Parse(db.Row("DATA_FLAG3"));
_data_flag4 = int.Parse(db.Row("DATA_FLAG4"));
_data_flag5 = int.Parse(db.Row("DATA_FLAG5"));
_data_flag6 = int.Parse(db.Row("DATA_FLAG6"));
_data_flag7 = int.Parse(db.Row("DATA_FLAG7"));
_data_flag8 = int.Parse(db.Row("DATA_FLAG8"));
_data_flag9 = int.Parse(db.Row("DATA_FLAG9"));
_data_flag10 = int.Parse(db.Row("DATA_FLAG10"));
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
_chg_pgm = db.Row("CHG_PGM");
_chg_user_id = db.Row("CHG_USERID");
_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
//_cost_center_name = db.Row("COST_CENTER_NAME");
_vendor_name = db.Row("VENDOR_NAME");
_ws_desc = db.Row("WS_DESC");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// メッセージ一覧の取得
/// </summary>
public DataSet GetMessageEditList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
db.DbParametersClear();
strSQL.Append("SELECT M.MSG_CD, M1.MSG_DESC MSG_DESC1,M2.MSG_DESC MSG_DESC2,M3.MSG_DESC MSG_DESC3,M4.MSG_DESC MSG_DESC4, M.RANK ");
strSQL.Append(" FROM ( ");
strSQL.Append(" SELECT DISTINCT MSG_CD, RANK FROM MESSAGE_MASTER ");
if (_msg_cd != "" || _msg_desc1 != "" || _msg_desc2 != "" || _msg_desc3 != "" || _msg_desc4 != "")
{
strSQL.Append(" WHERE 1 = 0 ");
if (_msg_cd != "")
{
strSQL.Append(" OR MSG_CD LIKE @MSG_CD ");
db.DbPsetString("@MSG_CD", "%" + _msg_cd + "%");
}
if (_msg_desc1 != "")
{
strSQL.Append(" OR (MSG_DESC LIKE @MSG_DESC1 AND LANG = 1)");
db.DbPsetString("@MSG_DESC1", "%" + _msg_desc1 + "%");
}
if (_msg_desc2 != "")
{
strSQL.Append(" OR (MSG_DESC LIKE @MSG_DESC2 AND LANG = 2)");
db.DbPsetString("@MSG_DESC2", "%" + _msg_desc2 + "%");
}
if (_msg_desc3 != "")
{
strSQL.Append(" OR (MSG_DESC LIKE @MSG_DESC3 AND LANG = 3)");
db.DbPsetString("@MSG_DESC3", "%" + _msg_desc3 + "%");
}
if (_msg_desc4 != "")
{
strSQL.Append(" OR (MSG_DESC LIKE @MSG_DESC4 AND LANG = 4)");
db.DbPsetString("@MSG_DESC4", "%" + _msg_desc4 + "%");
}
}
strSQL.Append(" ) M ");
strSQL.Append(" LEFT JOIN MESSAGE_MASTER M1 ON M1.MSG_CD = M.MSG_CD AND M1.LANG = 1 ");
strSQL.Append(" LEFT JOIN MESSAGE_MASTER M2 ON M2.MSG_CD = M.MSG_CD AND M2.LANG = 2 ");
strSQL.Append(" LEFT JOIN MESSAGE_MASTER M3 ON M3.MSG_CD = M.MSG_CD AND M3.LANG = 3 ");
strSQL.Append(" LEFT JOIN MESSAGE_MASTER M4 ON M4.MSG_CD = M.MSG_CD AND M4.LANG = 4 ");
strSQL.Append(" ORDER BY 1 ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "msg");
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>
/// 購入単価の変更
///
/// 使用画面：MstPurPriceMnt
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
int rtn = 0;
db.DbParametersClear();
db.DbPsetInt("RET", rtn, ComConst.DB_RTN);
db.DbPsetString("@I_ITEM_NO", _item_no, ComConst.DB_IN);
db.DbPsetString("@I_WS_CD", _ws_cd, ComConst.DB_IN);
db.DbPsetString("@I_PROCESS_CD", _process_cd, ComConst.DB_IN);
db.DbPsetString("@I_VENDOR_CD", _vendor_cd, ComConst.DB_IN);
db.DbPsetInt("@I_BEG_EFF_DATE", _beg_eff_date, ComConst.DB_IN);
db.DbPsetInt("@I_END_EFF_DATE", _end_eff_date, ComConst.DB_IN);
db.DbPsetInt("@I_UNIT_PRC_TYPE", _unit_prc_type, ComConst.DB_IN);
db.DbPsetInt("@I_UNIT_PRC_BASE", _unit_prc_base, ComConst.DB_IN);
db.DbPsetInt("@I_COM_PRC_FLAG", _com_prc_flag, ComConst.DB_IN);
db.DbPsetDouble("@I_TENTATIVE_PRC", _tentative_prc, ComConst.DB_IN);
db.DbPsetString("@I_TENTATIVE_CUR", _tentative_cur, ComConst.DB_IN);
db.DbPsetDouble("@I_ACT_PRC", _act_prc, ComConst.DB_IN);
db.DbPsetString("@I_ACT_CUR", _act_cur, ComConst.DB_IN);
db.DbPsetDouble("@I_DUTY_PCT", _duty_pct, ComConst.DB_IN);
db.DbPsetInt("@I_TAX_TYPE", _tax_type, ComConst.DB_IN);
db.DbPsetString("@I_TAX_CD", _tax_cd, ComConst.DB_IN);
db.DbPsetDouble("@I_VALUE_ADD_TAX_PCT", _value_add_tax_pct, ComConst.DB_IN);
db.DbPsetString("@I_PAY_TERMS", _pay_terms, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR1", _data_char1, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR2", _data_char2, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR3", _data_char3, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR4", _data_char4, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR5", _data_char5, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR6", _data_char6, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR7", _data_char7, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR8", _data_char8, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR9", _data_char9, ComConst.DB_IN);
db.DbPsetString("@I_DATA_CHAR10", _data_char10, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM1", _data_num1, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM2", _data_num2, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM3", _data_num3, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM4", _data_num4, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM5", _data_num5, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM6", _data_num6, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM7", _data_num7, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM8", _data_num8, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM9", _data_num9, ComConst.DB_IN);
db.DbPsetDouble("@I_DATA_NUM10", _data_num10, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG1", _data_flag1, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG2", _data_flag2, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG3", _data_flag3, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG4", _data_flag4, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG5", _data_flag5, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG6", _data_flag6, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG7", _data_flag7, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG8", _data_flag8, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG9", _data_flag9, ComConst.DB_IN);
db.DbPsetInt("@I_DATA_FLAG10", _data_flag10, ComConst.DB_IN);
db.DbPsetString("@I_CHG_PGM", _chg_pgm, ComConst.DB_IN);
db.DbPsetString("@I_CHG_USERID", _chg_user_id, ComConst.DB_IN);
db.DbPsetInt("@I_LANG", _lang, ComConst.DB_IN);
db.DbPsetInt("@I_UPDATE_CNTR", _update_cntr, ComConst.DB_IN);
db.DbPsetInt("@O_ERRCODE", _errcode, ComConst.DB_OUT);
db.DbPsetString("@O_MSG", _errmsg, ComConst.DB_OUT);
db.DbPsetInt("@O_SQLCODE", _sqlcode, ComConst.DB_OUT);
db.DbPsetString("@O_SQLMSG", _dbmsg, ComConst.DB_OUT);
db.DbPsetString("@O_PROC_NAME", _proc_name, ComConst.DB_OUT);
db.DbBeginTrans();
rtn = db.DbStored("SP_PURCHASE_PRICE_UPDATE");
rtn = db.DbPgetInt("RET");
if (rtn == ComConst.SUCCEED)
{
db.DbCommit();
}
else
{
_errcode = db.DbPgetInt("@O_ERRCODE");
_errmsg = db.DbPgetString("@O_MSG");
_sqlcode = db.DbPgetInt("@O_SQLCODE");
_dbmsg = db.DbPgetString("@O_SQLMSG");
_proc_name = db.DbPgetString("@O_PROC_NAME");
_strErr = _errcode + ":" + _errmsg + "[" + _sqlcode + ":" + _dbmsg + "(" + _proc_name + ")]";
db.DbRollback();
rtn = ComConst.FAILED;
}
return(rtn);
}
/// <summary>
/// 品目別段取条件マスタ詳細の取得
///
/// 使用画面：MstItemSetupMnt
/// </summary>
public int GetItemSetupDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ");
strSQL.Append(" ISM.RESOURCE_CD, R.RESOURCE_DESC, ISM.ITEM_NO, ISM.PREV_ITEM_NO, ISM.SETUP_TIME,");
strSQL.Append(" I1.ITEM_DESC, I2.ITEM_DESC PREV_ITEM_DESC ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
strSQL.Append(" FROM ITEM_SETUP_MASTER ISM ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER R ON ISM.RESOURCE_CD = R.RESOURCE_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I1 ON ISM.ITEM_NO = I1.ITEM_NO ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I2 ON ISM.PREV_ITEM_NO = I2.ITEM_NO ");
strSQL.Append(" WHERE ");
strSQL.Append(" ISM.RESOURCE_CD = :RESOURCE_CD ");
strSQL.Append(" AND ISM.ITEM_NO = :ITEM_NO ");
strSQL.Append(" AND ISM.PREV_ITEM_NO = :PREV_ITEM_NO ");
db.DbPsetString("RESOURCE_CD", _resource_cd);
db.DbPsetString("ITEM_NO", _item_no);
db.DbPsetString("PREV_ITEM_NO", _prev_item_no);
}
else
{
db.DbParametersClear();
strSQL.Append(" FROM ITEM_SETUP_MASTER ISM ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER R ON ISM.RESOURCE_CD = R.RESOURCE_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I1 ON ISM.ITEM_NO = I1.ITEM_NO ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I2 ON ISM.PREV_ITEM_NO = I2.ITEM_NO ");
strSQL.Append(" WHERE ");
strSQL.Append(" ISM.RESOURCE_CD = @RESOURCE_CD ");
strSQL.Append(" AND ISM.ITEM_NO = @ITEM_NO ");
strSQL.Append(" AND ISM.PREV_ITEM_NO = @PREV_ITEM_NO ");
db.DbPsetString("@RESOURCE_CD", _resource_cd);
db.DbPsetString("@ITEM_NO", _item_no);
db.DbPsetString("@PREV_ITEM_NO", _prev_item_no);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_resource_cd = db.Row("RESOURCE_CD");
_resource_desc = db.Row("RESOURCE_DESC");
_item_desc = db.Row("ITEM_NO");
_prev_item_no = db.Row("PREV_ITEM_NO");
_setup_time = double.Parse(db.Row("SETUP_TIME"));
_item_desc = db.Row("ITEM_DESC");
_prev_item_desc = db.Row("PREV_ITEM_DESC");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 購入単価一覧の取得
///
/// 使用画面：MstPurPriceMnt
/// </summary>
public DataSet GetPurPriceList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("P1.ITEM_NO, I.ITEM_DESC, P.PROCESS_CD, ");
strSQL.Append("P.BEG_EFF_DATE, P.END_EFF_DATE, P.UNIT_PRC_TYPE, P.UNIT_PRC_BASE, P.COM_PRC_FLAG, ");
strSQL.Append("P.TENTATIVE_PRC, P.TENTATIVE_CUR, P.ACT_PRC, P.ACT_CUR, ");
strSQL.Append("P.DUTY_PCT, P.TAX_TYPE, P.TAX_CD, P.VALUE_ADD_TAX_PCT, ");
strSQL.Append("P.PAY_TERMS, P.DATA_CHAR1, P.DATA_CHAR2, ");
strSQL.Append("P.DATA_CHAR3, P.DATA_CHAR4, P.DATA_CHAR5, P.DATA_CHAR6, ");
strSQL.Append("P.DATA_CHAR7, P.DATA_CHAR8, P.DATA_CHAR9, P.DATA_CHAR10, ");
strSQL.Append("P.DATA_NUM1, P.DATA_NUM2, P.DATA_NUM3, P.DATA_NUM4, ");
strSQL.Append("P.DATA_NUM5, P.DATA_NUM6, P.DATA_NUM7, P.DATA_NUM8, ");
strSQL.Append("P.DATA_NUM9, P.DATA_NUM10, P.DATA_FLAG1, P.DATA_FLAG2, ");
strSQL.Append("P.DATA_FLAG3, P.DATA_FLAG4, P.DATA_FLAG5, P.DATA_FLAG6, ");
strSQL.Append("P.DATA_FLAG7, P.DATA_FLAG8, P.DATA_FLAG9, P.DATA_FLAG10, ");
strSQL.Append("P.ENTRY_DATE, P.CHG_DATE, P.CHG_PGM, P.CHG_USERID, ");
strSQL.Append("P.UPDATE_CNTR, ");
strSQL.Append(" '*' AS WS_CD, P1.VENDOR_CD, V.VENDOR_NAME, P1.PTY_NO, P.VENDOR_CD, P.BEG_EFF_DATE, ");
strSQL.Append(" '*' AS WS_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM PURCHASE_PRICE_MASTER P, PURCHASE_MASTER P1 ,VENDOR_MASTER V,ITEM_MASTER I ");
strSQL.Append(" WHERE P1.ITEM_NO = P.ITEM_NO(+) AND P1.BEG_EFF_DATE = P.BEG_EFF_DATE(+)");
strSQL.Append(" AND P1.VENDOR_CD = V.VENDOR_CD(+) ");
strSQL.Append(" AND I.ITEM_NO = P1.ITEM_NO ");
strSQL.Append(" AND I.ITEM_TYPE >= '52' ");
strSQL.Append(" AND P.ITEM_NO = :ITEM_NO");
db.DbParametersClear();
db.DbPsetString("ITEM_NO", _item_no);
}
else
{
strSQL.Append(" FROM PURCHASE_MASTER P1 ");
strSQL.Append(" LEFT OUTER JOIN PURCHASE_PRICE_MASTER P ");
strSQL.Append(" ON P1.ITEM_NO = P.ITEM_NO ");
strSQL.Append(" AND P1.VENDOR_CD = P.VENDOR_CD ");
strSQL.Append(" AND P.WS_CD = '*' ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ");
strSQL.Append(" ON P1.VENDOR_CD = V.VENDOR_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I ");
strSQL.Append(" ON P1.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE P1.ITEM_NO = @ITEM_NO ");
strSQL.Append(" AND I.ITEM_TYPE >= '52' ");
db.DbParametersClear();
db.DbPsetString("@ITEM_NO", _item_no);
}
strSQL.Append(" UNION ");
strSQL.Append(" SELECT ");
strSQL.Append("IW.ITEM_NO, I.ITEM_DESC, P.PROCESS_CD, ");
strSQL.Append("P.BEG_EFF_DATE, P.END_EFF_DATE, P.UNIT_PRC_TYPE, P.UNIT_PRC_BASE, P.COM_PRC_FLAG, ");
strSQL.Append("P.TENTATIVE_PRC, P.TENTATIVE_CUR, P.ACT_PRC, P.ACT_CUR, ");
strSQL.Append("P.DUTY_PCT, P.TAX_TYPE, P.TAX_CD, P.VALUE_ADD_TAX_PCT, ");
strSQL.Append("P.PAY_TERMS, P.DATA_CHAR1, P.DATA_CHAR2, ");
strSQL.Append("P.DATA_CHAR3, P.DATA_CHAR4, P.DATA_CHAR5, P.DATA_CHAR6, ");
strSQL.Append("P.DATA_CHAR7, P.DATA_CHAR8, P.DATA_CHAR9, P.DATA_CHAR10, ");
strSQL.Append("P.DATA_NUM1, P.DATA_NUM2, P.DATA_NUM3, P.DATA_NUM4, ");
strSQL.Append("P.DATA_NUM5, P.DATA_NUM6, P.DATA_NUM7, P.DATA_NUM8, ");
strSQL.Append("P.DATA_NUM9, P.DATA_NUM10, P.DATA_FLAG1, P.DATA_FLAG2, ");
strSQL.Append("P.DATA_FLAG3, P.DATA_FLAG4, P.DATA_FLAG5, P.DATA_FLAG6, ");
strSQL.Append("P.DATA_FLAG7, P.DATA_FLAG8, P.DATA_FLAG9, P.DATA_FLAG10, ");
strSQL.Append("P.ENTRY_DATE, P.CHG_DATE, P.CHG_PGM, P.CHG_USERID, ");
strSQL.Append("P.UPDATE_CNTR, ");
strSQL.Append("IW.WS_CD, P1.VENDOR_CD, V.VENDOR_NAME, P1.PTY_NO, ");
strSQL.Append("P.VENDOR_CD, P.BEG_EFF_DATE, WS.WS_DESC ");//UPD UBIQ-LIU 2010/05/25 [WS_DESC]ADD
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM ITEM_WS_MASTER IW, PURCHASE_PRICE_MASTER P, PURCHASE_MASTER P1 ,VENDOR_MASTER V,ITEM_MASTER I ");
strSQL.Append(" WHERE IW.ITEM_NO = P1.ITEM_NO AND AND IW.PTY_NO = P1.PTY_NO ");
strSQL.Append(" AND IW.BEG_EFF_DATE <= P1.BEG_EFF_DATE AND IW.END_EFF_DATE >= P1.BEG_EFF_DATE ");
strSQL.Append(" AND IW.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" AND IW.ITEM_NO = :ITEM_NO");
strSQL.Append(" AND I.ITEM_TYPE <= '51' ");
}
else
{
strSQL.Append(" FROM ITEM_WS_MASTER IW ");
strSQL.Append(" INNER JOIN PURCHASE_MASTER P1 ON IW.ITEM_NO = P1.ITEM_NO ");
strSQL.Append(" AND IW.PTY_NO = P1.PTY_NO ");
strSQL.Append(" AND IW.BEG_EFF_DATE <= P1.BEG_EFF_DATE ");
strSQL.Append(" AND IW.END_EFF_DATE >= P1.BEG_EFF_DATE ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ON P1.VENDOR_CD = V.VENDOR_CD ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I ON IW.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" LEFT OUTER JOIN PURCHASE_PRICE_MASTER P ON IW.ITEM_NO = P.ITEM_NO ");
strSQL.Append(" AND IW.WS_CD = P.WS_CD ");
strSQL.Append(" AND P1.VENDOR_CD = P.VENDOR_CD ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER WS ON WS.WS_CD = IW.WS_CD ");//ADD UBIQ-LIU 2010/05/25
strSQL.Append(" WHERE IW.ITEM_NO = @ITEM_NO ");
strSQL.Append(" AND I.ITEM_TYPE <= '51' ");
}
strSQL.Append(" ORDER BY P1.PTY_NO,P.VENDOR_CD,P.BEG_EFF_DATE ");//2009.03.04 add
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);
}
}
//<--ADD BY UBIQ-SUO 2010/09/19
/// <summary>
/// 倉庫詳細の取得
///
/// 使用画面：MstWhsMnt
/// </summary>
public int GetWarehouseDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append(" SELECT ");
strSQL.Append(" W.WHS_CD, W.MRP_WHS, W.WHS_DESC, W.WHS_DESC2, ");
strSQL.Append(" W.RECV_LOC, W.WHS_TYPE, W.WHS_ITEM_TYPE, W.LOCATION_CTRL_FLAG, W.AUTO_HOUSING_TYPE, ");
strSQL.Append(" W.DIRECT_SHIP_FLAG, W.VENDOR_CD, W.CONS_WHS_FLAG, W.CONS_VENDOR_CD, ");
strSQL.Append(" W.PASS_THRU_WHS_CD, W.BOND_FLAG, W.DATA_CHAR1, W.DATA_CHAR2, ");
strSQL.Append(" W.DATA_CHAR3, W.DATA_CHAR4, W.DATA_CHAR5, W.DATA_CHAR6, ");
strSQL.Append(" W.DATA_CHAR7, W.DATA_CHAR8, W.DATA_CHAR9, W.DATA_CHAR10, ");
strSQL.Append(" W.DATA_NUM1, W.DATA_NUM2, W.DATA_NUM3, W.DATA_NUM4, ");
strSQL.Append(" W.DATA_NUM5, W.DATA_NUM6, W.DATA_NUM7, W.DATA_NUM8, ");
strSQL.Append(" W.DATA_NUM9, W.DATA_NUM10, W.DATA_FLAG1, W.DATA_FLAG2, ");
strSQL.Append(" W.DATA_FLAG3, W.DATA_FLAG4, W.DATA_FLAG5, W.DATA_FLAG6, ");
strSQL.Append(" W.DATA_FLAG7, W.DATA_FLAG8, W.DATA_FLAG9, W.DATA_FLAG10, ");
strSQL.Append(" W.ENTRY_DATE, W.CHG_DATE, W.CHG_PGM, W.CHG_USERID, ");
strSQL.Append(" W.UPDATE_CNTR, W.SUPPLY_TYPE, ");
strSQL.Append(" W2.WHS_DESC AS MRP_WHS_DESC, W3.WHS_DESC AS PASS_THRU_WHS_DESC, V.VENDOR_NAME, ");
strSQL.Append(" V2.VENDOR_NAME AS CONS_VENDOR_NAME ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM WAREHOUSE_MASTER W, WAREHOUSE_MASTER W2, WAREHOUSE_MASTER W3,VENDOR_MASTER V, VENDOR_MASTER V2 ");
strSQL.Append(" WHERE W.WHS_CD = :WHS_CD ");
strSQL.Append(" AND W.MRP_WHS = W2.WHS_CD(+) ");
strSQL.Append(" AND W.PASS_THRU_WHS_CD = W3.WHS_CD(+) ");
strSQL.Append(" AND W.VENDOR_CD = V.VENDOR_CD(+) ");
strSQL.Append(" AND W.CONS_VENDOR_CD = V2.VENDOR_CD(+) ");
db.DbParametersClear();
db.DbPsetString("WHS_CD", _whs_cd);
}
else
{
strSQL.Append(" FROM WAREHOUSE_MASTER W ");
strSQL.Append(" LEFT OUTER JOIN WAREHOUSE_MASTER W2 ON W.MRP_WHS = W2.WHS_CD ");
strSQL.Append(" LEFT OUTER JOIN WAREHOUSE_MASTER W3 ON W.PASS_THRU_WHS_CD = W3.WHS_CD ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ON W.VENDOR_CD = V.VENDOR_CD ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V2 ON W.CONS_VENDOR_CD = V2.VENDOR_CD ");
strSQL.Append(" WHERE W.WHS_CD = @WHS_CD ");
db.DbParametersClear();
db.DbPsetString("@WHS_CD", _whs_cd);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_whs_cd = db.Row("WHS_CD");
_mrp_whs = db.Row("MRP_WHS");
_whs_desc = db.Row("WHS_DESC");
_whs_desc2 = db.Row("WHS_DESC2");
_recv_loc = db.Row("RECV_LOC");
_whs_type = int.Parse(db.Row("WHS_TYPE"));
_whs_item_type = int.Parse(db.Row("WHS_ITEM_TYPE"));
_location_ctrl_flag = int.Parse(db.Row("LOCATION_CTRL_FLAG"));
_auto_housing_type = int.Parse(db.Row("AUTO_HOUSING_TYPE"));
_direct_ship_flag = int.Parse(db.Row("DIRECT_SHIP_FLAG"));
_vendor_cd = db.Row("VENDOR_CD");
_cons_whs_flag = int.Parse(db.Row("CONS_WHS_FLAG"));
_cons_vendor_cd = db.Row("CONS_VENDOR_CD");
_pass_thru_whs_cd = db.Row("PASS_THRU_WHS_CD");
_bond_flag = int.Parse(db.Row("BOND_FLAG"));
_data_char1 = db.Row("DATA_CHAR1");
_data_char2 = db.Row("DATA_CHAR2");
_data_char3 = db.Row("DATA_CHAR3");
_data_char4 = db.Row("DATA_CHAR4");
_data_char5 = db.Row("DATA_CHAR5");
_data_char6 = db.Row("DATA_CHAR6");
_data_char7 = db.Row("DATA_CHAR7");
_data_char8 = db.Row("DATA_CHAR8");
_data_char9 = db.Row("DATA_CHAR9");
_data_char10 = db.Row("DATA_CHAR10");
_data_num1 = double.Parse(db.Row("DATA_NUM1"));
_data_num2 = double.Parse(db.Row("DATA_NUM2"));
_data_num3 = double.Parse(db.Row("DATA_NUM3"));
_data_num4 = double.Parse(db.Row("DATA_NUM4"));
_data_num5 = double.Parse(db.Row("DATA_NUM5"));
_data_num6 = double.Parse(db.Row("DATA_NUM6"));
_data_num7 = double.Parse(db.Row("DATA_NUM7"));
_data_num8 = double.Parse(db.Row("DATA_NUM8"));
_data_num9 = double.Parse(db.Row("DATA_NUM9"));
_data_num10 = double.Parse(db.Row("DATA_NUM10"));
_data_flag1 = int.Parse(db.Row("DATA_FLAG1"));
_data_flag2 = int.Parse(db.Row("DATA_FLAG2"));
_data_flag3 = int.Parse(db.Row("DATA_FLAG3"));
_data_flag4 = int.Parse(db.Row("DATA_FLAG4"));
_data_flag5 = int.Parse(db.Row("DATA_FLAG5"));
_data_flag6 = int.Parse(db.Row("DATA_FLAG6"));
_data_flag7 = int.Parse(db.Row("DATA_FLAG7"));
_data_flag8 = int.Parse(db.Row("DATA_FLAG8"));
_data_flag9 = int.Parse(db.Row("DATA_FLAG9"));
_data_flag10 = int.Parse(db.Row("DATA_FLAG10"));
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
_chg_pgm = db.Row("CHG_PGM");
_chg_user_id = db.Row("CHG_USERID");
_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
_mrp_whs_desc = db.Row("MRP_WHS_DESC");
_vendor_name = db.Row("VENDOR_NAME");
_cons_vendor_name = db.Row("CONS_VENDOR_NAME");
_pass_thru_whs_desc = db.Row("PASS_THRU_WHS_DESC");
_supply_type = int.Parse(db.Row("SUPPLY_TYPE"));
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 購入単価詳細の取得
///
/// 使用画面：MstPurPriceMnt
/// </summary>
public int GetPurPriceDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ");
//strSQL.Append("P.ITEM_NO, P.WS_CD, P.PROCESS_CD, P.VENDOR_CD, P.BEG_EFF_DATE,");
strSQL.Append("P.END_EFF_DATE, P.UNIT_PRC_TYPE, P.UNIT_PRC_BASE, P.COM_PRC_FLAG,");
strSQL.Append("P.TENTATIVE_PRC, P.TENTATIVE_CUR, P.ACT_PRC, P.ACT_CUR, ");
strSQL.Append("P.DUTY_PCT, P.TAX_TYPE, P.TAX_CD, P.VALUE_ADD_TAX_PCT, ");
strSQL.Append("P.PAY_TERMS, P.DATA_CHAR1, P.DATA_CHAR2, ");
strSQL.Append("P.DATA_CHAR3, P.DATA_CHAR4, P.DATA_CHAR5, P.DATA_CHAR6, ");
strSQL.Append("P.DATA_CHAR7, P.DATA_CHAR8, P.DATA_CHAR9, P.DATA_CHAR10, ");
strSQL.Append("P.DATA_NUM1, P.DATA_NUM2, P.DATA_NUM3, P.DATA_NUM4, ");
strSQL.Append("P.DATA_NUM5, P.DATA_NUM6, P.DATA_NUM7, P.DATA_NUM8, ");
strSQL.Append("P.DATA_NUM9, P.DATA_NUM10, P.DATA_FLAG1, P.DATA_FLAG2, ");
strSQL.Append("P.DATA_FLAG3, P.DATA_FLAG4, P.DATA_FLAG5, P.DATA_FLAG6, ");
strSQL.Append("P.DATA_FLAG7, P.DATA_FLAG8, P.DATA_FLAG9, P.DATA_FLAG10, ");
strSQL.Append("P.ENTRY_DATE, P.CHG_DATE, P.CHG_PGM, P.CHG_USERID, ");
strSQL.Append("P.UPDATE_CNTR,");
strSQL.Append("I.ITEM_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM PURCHASE_PRICE_MASTER P, ITEM_MASTER I ");
strSQL.Append(" WHERE P.ITEM_NO = :ITEM_NO");
strSQL.Append(" AND P.WS_CD = :WS_CD");
strSQL.Append(" AND P.PROCESS_CD = :PROCESS_CD");
strSQL.Append(" AND P.VENDOR_CD = :VENDOR_CD");
strSQL.Append(" AND P.BEG_EFF_DATE = :BEG_EFF_DATE");
strSQL.Append(" AND P.ITEM_NO = I.ITEM_NO");
db.DbParametersClear();
db.DbPsetString("ITEM_NO", _item_no);
db.DbPsetString("WS_CD", _ws_cd);
db.DbPsetString("PROCESS_CD", _process_cd);
db.DbPsetString("VENDOR_CD", _vendor_cd);
db.DbPsetInt("BEG_EFF_DATE", _beg_eff_date);
}
else
{
db.DbParametersClear();
strSQL.Append(" FROM PURCHASE_PRICE_MASTER P ");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I ON P.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE P.ITEM_NO = @ITEM_NO");
strSQL.Append(" AND P.WS_CD = @WS_CD");
strSQL.Append(" AND P.PROCESS_CD = @PROCESS_CD");
strSQL.Append(" AND P.VENDOR_CD = @VENDOR_CD");
strSQL.Append(" AND P.BEG_EFF_DATE = @BEG_EFF_DATE");
db.DbPsetString("@ITEM_NO", _item_no);
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetString("@PROCESS_CD", _process_cd);
db.DbPsetString("@VENDOR_CD", _vendor_cd);
db.DbPsetInt("@BEG_EFF_DATE", _beg_eff_date);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
//_item_no = db.Row("ITEM_NO");
//_ws_cd = db.Row("WS_CD");
//_process_cd = db.Row("PROCESS_CD");
//_vendor_cd = db.Row("VENDOR_CD");
//_beg_eff_date = int.Parse(db.Row("BEG_EFF_DATE"));
_end_eff_date = int.Parse(db.Row("END_EFF_DATE"));
_unit_prc_type = int.Parse(db.Row("UNIT_PRC_TYPE"));
_unit_prc_base = int.Parse(db.Row("UNIT_PRC_BASE"));
_com_prc_flag = int.Parse(db.Row("COM_PRC_FLAG"));
_tentative_prc = double.Parse(db.Row("TENTATIVE_PRC"));
_tentative_cur = db.Row("TENTATIVE_CUR");
_act_prc = double.Parse(db.Row("ACT_PRC"));
_act_cur = db.Row("ACT_CUR");
_duty_pct = double.Parse(db.Row("DUTY_PCT"));
_tax_type = int.Parse(db.Row("TAX_TYPE"));
_tax_cd = db.Row("TAX_CD");
_value_add_tax_pct = double.Parse(db.Row("VALUE_ADD_TAX_PCT"));
_pay_terms = db.Row("PAY_TERMS");
_data_char1 = db.Row("DATA_CHAR1");
_data_char2 = db.Row("DATA_CHAR2");
_data_char3 = db.Row("DATA_CHAR3");
_data_char4 = db.Row("DATA_CHAR4");
_data_char5 = db.Row("DATA_CHAR5");
_data_char6 = db.Row("DATA_CHAR6");
_data_char7 = db.Row("DATA_CHAR7");
_data_char8 = db.Row("DATA_CHAR8");
_data_char9 = db.Row("DATA_CHAR9");
_data_char10 = db.Row("DATA_CHAR10");
_data_num1 = double.Parse(db.Row("DATA_NUM1"));
_data_num2 = double.Parse(db.Row("DATA_NUM2"));
_data_num3 = double.Parse(db.Row("DATA_NUM3"));
_data_num4 = double.Parse(db.Row("DATA_NUM4"));
_data_num5 = double.Parse(db.Row("DATA_NUM5"));
_data_num6 = double.Parse(db.Row("DATA_NUM6"));
_data_num7 = double.Parse(db.Row("DATA_NUM7"));
_data_num8 = double.Parse(db.Row("DATA_NUM8"));
_data_num9 = double.Parse(db.Row("DATA_NUM9"));
_data_num10 = double.Parse(db.Row("DATA_NUM10"));
_data_flag1 = int.Parse(db.Row("DATA_FLAG1"));
_data_flag2 = int.Parse(db.Row("DATA_FLAG2"));
_data_flag3 = int.Parse(db.Row("DATA_FLAG3"));
_data_flag4 = int.Parse(db.Row("DATA_FLAG4"));
_data_flag5 = int.Parse(db.Row("DATA_FLAG5"));
_data_flag6 = int.Parse(db.Row("DATA_FLAG6"));
_data_flag7 = int.Parse(db.Row("DATA_FLAG7"));
_data_flag8 = int.Parse(db.Row("DATA_FLAG8"));
_data_flag9 = int.Parse(db.Row("DATA_FLAG9"));
_data_flag10 = int.Parse(db.Row("DATA_FLAG10"));
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
_chg_pgm = db.Row("CHG_PGM");
_chg_user_id = db.Row("CHG_USERID");
_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
_item_desc = db.Row("ITEM_DESC");
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 辞書の変更
///
/// 使用画面：MstDicMnt
/// </summary>
public int Update()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
if (_db_type == "ORACLE")
{
strSQL.Append("UPDATE DICTIONARY_MASTER ");
strSQL.Append("SET ");
strSQL.Append("ITEM_DESC1 = :ITEM_DESC1,");
strSQL.Append("ITEM_DESC2 = :ITEM_DESC2,");
strSQL.Append("ITEM_DESC3 = :ITEM_DESC3,");
strSQL.Append("ITEM_DESC4 = :ITEM_DESC4,");
strSQL.Append("CHG_DATE = :CHG_DATE,");
strSQL.Append("CHG_USER_ID = :CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR = UPDATE_CNTR + 1");
strSQL.Append(" WHERE STD_ITEM = :STD_ITEM");
db.DbParametersClear();
db.DbPsetString("ITEM_DESC1", _item_desc1);
db.DbPsetString("ITEM_DESC2", _item_desc2);
db.DbPsetString("ITEM_DESC3", _item_desc3);
db.DbPsetString("ITEM_DESC4", _item_desc4);
db.DbPsetDate("CHG_DATE", DateTime.Now);
db.DbPsetString("CHG_USER_ID", _chg_user_id);
db.DbPsetString("STD_ITEM", _std_item);
}
else
{
strSQL.Append("UPDATE DICTIONARY_MASTER ");
strSQL.Append("SET ");
strSQL.Append("ITEM_DESC1 = @ITEM_DESC1,");
strSQL.Append("ITEM_DESC2 = @ITEM_DESC2,");
strSQL.Append("ITEM_DESC3 = @ITEM_DESC3,");
strSQL.Append("ITEM_DESC4 = @ITEM_DESC4,");
strSQL.Append("CHG_DATE = @CHG_DATE,");
strSQL.Append("CHG_USER_ID = @CHG_USER_ID,");
strSQL.Append("UPDATE_CNTR = UPDATE_CNTR + 1");
strSQL.Append(" WHERE STD_ITEM = @STD_ITEM");
db.DbParametersClear();
db.DbPsetString("@ITEM_DESC1", _item_desc1);
db.DbPsetString("@ITEM_DESC2", _item_desc2);
db.DbPsetString("@ITEM_DESC3", _item_desc3);
db.DbPsetString("@ITEM_DESC4", _item_desc4);
db.DbPsetDate("@CHG_DATE", DateTime.Now);
db.DbPsetString("@CHG_USER_ID", _chg_user_id);
db.DbPsetString("@STD_ITEM", _std_item);
}
db.DbBeginTrans();
rtn = db.DbExecute(strSQL.ToString());
if (rtn != ComConst.FAILED)
{
db.DbCommit();
}
else
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
db.DbRollback();
}
return(rtn);
}
/// <summary>
/// 購入単価一覧の取得
///
/// 使用画面：MstItemMnt
/// </summary>
public DataSet GetPurPriceList_Item()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("P.ITEM_NO, P.PROCESS_CD, P.VENDOR_CD, P.WS_CD, ");
strSQL.Append("P.BEG_EFF_DATE, P.END_EFF_DATE, P.UNIT_PRC_TYPE, P.UNIT_PRC_BASE, ");
strSQL.Append("P.TENTATIVE_PRC, P.TENTATIVE_CUR, P.ACT_PRC, P.ACT_CUR, ");
strSQL.Append("P.DUTY_PCT, P.TAX_TYPE, P.TAX_CD, P.VALUE_ADD_TAX_PCT, ");
strSQL.Append("P.ENTRY_DATE, P.CHG_DATE, P.CHG_PGM, P.CHG_USERID, ");
strSQL.Append("P.UPDATE_CNTR, P.COM_PRC_FLAG, WS.WS_DESC, P.PAY_TERMS, ");
strSQL.Append("P.DATA_CHAR1, P.DATA_CHAR2, "); //ADD BY UBIQ-LIU 2010/5/24
strSQL.Append("P.DATA_CHAR3, P.DATA_CHAR4, P.DATA_CHAR5, P.DATA_CHAR6, ");
strSQL.Append("P.DATA_CHAR7, P.DATA_CHAR8, P.DATA_CHAR9, P.DATA_CHAR10, ");
strSQL.Append("P.DATA_NUM1, P.DATA_NUM2, P.DATA_NUM3, P.DATA_NUM4, ");
strSQL.Append("P.DATA_NUM5, P.DATA_NUM6, P.DATA_NUM7, P.DATA_NUM8, ");
strSQL.Append("P.DATA_NUM9, P.DATA_NUM10, P.DATA_FLAG1, P.DATA_FLAG2, ");
strSQL.Append("P.DATA_FLAG3, P.DATA_FLAG4, P.DATA_FLAG5, P.DATA_FLAG6, ");
strSQL.Append("P.DATA_FLAG7, P.DATA_FLAG8, P.DATA_FLAG9, P.DATA_FLAG10, "); //ADD BY UBIQ-LIU 2010/5/24
strSQL.Append("V.VENDOR_NAME, S.DATA_CHAR AS T_CUR_NAME, S1.DATA_CHAR AS ACT_CUR_NAME ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM PURCHASE_PRICE_MASTER P ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER WS ON P.WS_CD = WS.WS_CD ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ");
strSQL.Append(" ON P.VENDOR_CD = V.VENDOR_CD ");
strSQL.Append(" WHERE P.ITEM_NO = @ITEM_NO ");
db.DbParametersClear();
db.DbPsetString("@ITEM_NO", _item_no);
if (check_flag == "1")
{
strSQL.Append(" AND P.WS_CD = :WS_CD ");
strSQL.Append(" AND P.PROCESS_CD = :PROCESS_CD ");
strSQL.Append(" AND P.VENDOR_CD = :VENDOR_CD ");
strSQL.Append(" AND P.BEG_EFF_DATE <= :END_EFF_DATE ");
strSQL.Append(" AND P.END_EFF_DATE >= :BEG_EFF_DATE ");
strSQL.Append(" AND P.BEG_EFF_DATE <> :END_EFF_DATE ");
db.DbPsetString("WS_CD", _ws_cd);
db.DbPsetString("PROCESS_CD", _process_cd);
db.DbPsetString("VENDOR_CD", _vendor_cd);
db.DbPsetInt("BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("END_EFF_DATE", _end_eff_date);
}
else if (check_flag == "2")
{
strSQL.Append(" AND P.WS_CD = :WS_CD ");
strSQL.Append(" AND P.PROCESS_CD = :PROCESS_CD ");
strSQL.Append(" AND P.VENDOR_CD = :VENDOR_CD ");
strSQL.Append(" AND P.BEG_EFF_DATE <= :END_EFF_DATE ");
strSQL.Append(" AND P.END_EFF_DATE >= :BEG_EFF_DATE ");
db.DbPsetString("WS_CD", _ws_cd);
db.DbPsetString("PROCESS_CD", _process_cd);
db.DbPsetString("VENDOR_CD", _vendor_cd);
db.DbPsetInt("BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("END_EFF_DATE", _end_eff_date);
}
else
{
strSQL.Append(" AND P.BEG_EFF_DATE <= :BEG_EFF_DATE ");
strSQL.Append(" AND P.END_EFF_DATE >= :END_EFF_DATE ");
db.DbPsetInt("BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("END_EFF_DATE", _end_eff_date);
}
}
else
{
strSQL.Append(" FROM PURCHASE_PRICE_MASTER P ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER WS ON P.WS_CD = WS.WS_CD ");
strSQL.Append(" LEFT JOIN SYSTEM_PARAMETER S ON P.TENTATIVE_CUR=S.KEY02 AND S.KEY01='CURRENCY_CD' ");
strSQL.Append(" LEFT JOIN SYSTEM_PARAMETER S1 ON P.ACT_CUR=S1.KEY02 AND S1.KEY01='CURRENCY_CD' ");
strSQL.Append(" LEFT OUTER JOIN VENDOR_MASTER V ");
strSQL.Append(" ON P.VENDOR_CD = V.VENDOR_CD ");
strSQL.Append(" WHERE P.ITEM_NO = @ITEM_NO ");
db.DbParametersClear();
db.DbPsetString("@ITEM_NO", _item_no);
if (check_flag == "1")
{
strSQL.Append(" AND P.WS_CD = @WS_CD ");
strSQL.Append(" AND P.PROCESS_CD = @PROCESS_CD ");
strSQL.Append(" AND P.VENDOR_CD = @VENDOR_CD ");
strSQL.Append(" AND P.BEG_EFF_DATE < @BEG_EFF_DATE ");//UPD UBIQ-LIU 2010/05/25 "<="→"<"
strSQL.Append(" AND P.END_EFF_DATE >= @END_EFF_DATE ");
//strSQL.Append(" AND P.BEG_EFF_DATE <> @BEG_EFF_DATE ");//DEL BY UBIQ-LIU 2010/5/25
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetString("@PROCESS_CD", _process_cd);
db.DbPsetString("@VENDOR_CD", _vendor_cd);
db.DbPsetInt("@BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("@END_EFF_DATE", _end_eff_date);
}
else if (check_flag == "2")
{
strSQL.Append(" AND P.WS_CD = @WS_CD ");
strSQL.Append(" AND P.PROCESS_CD = @PROCESS_CD ");
strSQL.Append(" AND P.VENDOR_CD = @VENDOR_CD ");
strSQL.Append(" AND P.BEG_EFF_DATE <= @END_EFF_DATE ");
strSQL.Append(" AND P.END_EFF_DATE >= @BEG_EFF_DATE ");
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetString("@PROCESS_CD", _process_cd);
db.DbPsetString("@VENDOR_CD", _vendor_cd);
db.DbPsetInt("@BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("@END_EFF_DATE", _end_eff_date);
}
else if (check_flag == "3")
{
strSQL.Append(" AND P.BEG_EFF_DATE <= @BEG_EFF_DATE ");
strSQL.Append(" AND P.END_EFF_DATE >= @END_EFF_DATE ");
strSQL.Append(" AND P.VENDOR_CD = @VENDOR_CD ");
db.DbPsetInt("@BEG_EFF_DATE", _beg_eff_date);
db.DbPsetInt("@END_EFF_DATE", _end_eff_date);
db.DbPsetString("@VENDOR_CD", _vendor_cd);
}
else// 使用画面：[MstItemEntry]
{
if (_beg_eff_date != 0)
{
strSQL.Append(" AND P.BEG_EFF_DATE <= @BEG_EFF_DATE ");
db.DbPsetInt("@BEG_EFF_DATE", _beg_eff_date);
}
if (_end_eff_date != 0)
{
strSQL.Append(" AND P.END_EFF_DATE >= @END_EFF_DATE ");
db.DbPsetInt("@END_EFF_DATE", _end_eff_date);
}
}
}
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>
/// 辞書詳細の取得
///
/// 使用画面：AppUserMenu, MstDicMnt
/// </summary>
public int GetDictionaryDetail()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
int rtn = 0;
//--- sql
strSQL.Append("SELECT ITEM_DESC1, ITEM_DESC2, ITEM_DESC3, ITEM_DESC4,");
switch (_lang)
{
case 2:
strSQL.Append(" ITEM_DESC2 AS ITEM_DESC, ");
break;
case 3:
strSQL.Append(" ITEM_DESC3 AS ITEM_DESC, ");
break;
case 4:
strSQL.Append(" ITEM_DESC4 AS ITEM_DESC, ");
break;
default:
strSQL.Append(" ITEM_DESC1 AS ITEM_DESC, ");
break;
}
strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
strSQL.Append(" UPDATE_CNTR");
strSQL.Append(" FROM DICTIONARY_MASTER ");
if (_db_type == "ORACLE")
{
strSQL.Append(" WHERE STD_ITEM = :STD_ITEM");
db.DbParametersClear();
db.DbPsetString("STD_ITEM", _std_item);
}
else
{
strSQL.Append(" WHERE STD_ITEM = @STD_ITEM");
db.DbParametersClear();
db.DbPsetString("@STD_ITEM", _std_item);
}
rtn = db.DbExecuteReader(strSQL.ToString());
if (rtn == ComConst.FAILED)
{
_dbmsg = db.expmsg;
_strErr = db.strErr;
}
else
{
_item_desc1 = db.Row("ITEM_DESC1");
_item_desc2 = db.Row("ITEM_DESC2");
_item_desc3 = db.Row("ITEM_DESC3");
_item_desc4 = db.Row("ITEM_DESC4");
_item_desc = db.Row("ITEM_DESC");
_entry_date = DateTime.Parse(db.Row("ENTRY_DATE"));
_chg_date = DateTime.Parse(db.Row("CHG_DATE"));
_chg_user_id = db.Row("CHG_USER_ID");
_update_cntr = int.Parse(db.Row("UPDATE_CNTR"));
}
if (db.State() == ConnectionState.Open)
{
db.DbClose();
}
return(rtn);
}
/// <summary>
/// 製造資源一覧の取得
///
/// 使用画面：MstRoutineResourceMnt
/// </summary>
public DataSet GetItemRoutineResourceList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("R.ITEM_NO, R.WS_CD, R.ROUTINE_SEQ, R.PROCESS_CD, ");
strSQL.Append("R.BEG_EFF_DATE, R.END_EFF_DATE, ");
strSQL.Append("RR.PTY_NO, RR.RESOURCE_CD, ");
strSQL.Append("I.ITEM_DESC AS ITEM_DESC, W.WS_DESC AS WS_DESC, ");
strSQL.Append("P.PROCESS_DESC AS PROCESS_DESC,");
strSQL.Append("RE.RESOURCE_DESC AS RESOURCE_DESC ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM ROUTINE_MASTER R, ROUTINE_RESOURCE_MASTER RR, ITEM_MASTER I, ");
strSQL.Append(" WORKSHOP_MASTER W, PROCESS_MASTER P, RESOURCE_MASTER RE");
strSQL.Append(" WHERE R.ITEM_NO = :ITEM_NO");
strSQL.Append(" AND R.WS_CD = :WS_CD");
strSQL.Append(" AND R.ITEM_NO = RR.ITEM_NO(+)");
strSQL.Append(" AND R.WS_CD = RR.WS_CD(+)");
strSQL.Append(" AND R.ROUTINE_SEQ = RR.ROUTINE_SEQ(+)");
strSQL.Append(" AND R.PROCESS_CD = RR.PROCESS_CD(+)");
//strSQL.Append(" AND R.RESOURCE_CD = RR.RESOURCE_CD(+)");
strSQL.Append(" AND R.ITEM_NO = I.ITEM_NO(+)");
strSQL.Append(" AND R.WS_CD = W.WS_CD(+)");
strSQL.Append(" AND R.WS_CD = P.WS_CD(+)");
strSQL.Append(" AND R.PROCESS_CD = P.PROCESS_CD(+)");
strSQL.Append(" AND RR.RESOURCE_CD = RE.RESOURCE_CD(+)");
db.DbParametersClear();
db.DbPsetString("ITEM_NO", _item_no);
db.DbPsetString("WS_CD", _ws_cd);
}
else
{
strSQL.Append(" FROM ROUTINE_MASTER R ");
strSQL.Append(" LEFT OUTER JOIN ROUTINE_RESOURCE_MASTER RR ON R.ITEM_NO=RR.ITEM_NO ");
strSQL.Append(" AND R.WS_CD=RR.WS_CD AND R.ROUTINE_SEQ=RR.ROUTINE_SEQ");
strSQL.Append(" AND R.PROCESS_CD=RR.PROCESS_CD ");
strSQL.Append(" LEFT OUTER JOIN WORKSHOP_MASTER W ON R.WS_CD = W.WS_CD");
strSQL.Append(" LEFT OUTER JOIN ITEM_MASTER I ON R.ITEM_NO = I.ITEM_NO");
strSQL.Append(" LEFT OUTER JOIN PROCESS_MASTER P ON R.WS_CD = P.WS_CD AND R.PROCESS_CD = P.PROCESS_CD");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_MASTER RE ON RR.RESOURCE_CD=RE.RESOURCE_CD");
strSQL.Append(" WHERE R.ITEM_NO = @ITEM_NO");
strSQL.Append(" AND R.WS_CD = @WS_CD");
db.DbParametersClear();
db.DbPsetString("@ITEM_NO", _item_no);
db.DbPsetString("@WS_CD", _ws_cd);
}
strSQL.Append(" ORDER BY R.ROUTINE_SEQ");
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>
/// 指定画面のラベル項目名一覧
/// </summary>
public DataSet GetLabelOfScreenList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT SL.CONTROL_ID, ");
switch (_lang)
{
case 2:
strSQL.Append(" D.ITEM_DESC2 AS ITEM_DESC ");
break;
case 3:
strSQL.Append(" D.ITEM_DESC3 AS ITEM_DESC ");
break;
case 4:
strSQL.Append(" D.ITEM_DESC4 AS ITEM_DESC ");
break;
default:
strSQL.Append(" D.ITEM_DESC1 AS ITEM_DESC ");
break;
}
strSQL.Append(" FROM SCREEN_LABEL_MASTER SL, DICTIONARY_MASTER D");
if (_db_type == "ORACLE")
{
strSQL.Append(" WHERE SL.SCREEN_ID = :SCREEN_ID");
strSQL.Append(" AND SL.STD_ITEM = D.STD_ITEM");
strSQL.Append(" ORDER BY SL.CONTROL_ID");
db.DbParametersClear();
db.DbPsetString("SCREEN_ID", _screen_id);
}
else
{
strSQL.Append(" WHERE SL.SCREEN_ID = @SCREEN_ID");
strSQL.Append(" AND SL.STD_ITEM = D.STD_ITEM");
strSQL.Append(" ORDER BY SL.CONTROL_ID");
db.DbParametersClear();
db.DbPsetString("@SCREEN_ID", _screen_id);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "label");
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>
/// ロール一覧の取得
///
/// 使用画面：MstRoleMnt, MstUserMnt
/// </summary>
public DataSet GetRoleList()
{
ComDB db = new ComDB("", "COM");
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
//strSQL.Append("SELECT ROLE, ROLE_DESC1, ROLE_DESC2, ROLE_DESC3,");
//strSQL.Append("ROLE_DESC4, ");
//switch (_lang)
//{
// case 2:
// strSQL.Append("ROLE_DESC2 AS ROLE_DESC, ");
// break;
// case 3:
// strSQL.Append("ROLE_DESC3 AS ROLE_DESC, ");
// break;
// case 4:
// strSQL.Append("ROLE_DESC4 AS ROLE_DESC, ");
// break;
// default:
// strSQL.Append("ROLE_DESC1 AS ROLE_DESC, ");
// break;
//}
strSQL.Append("SELECT USER_GROUP_CODE, USER_GROUP_NAME, GROUP_ACTIVE,");
strSQL.Append("MANAGEMENT_LEVEL, DEPT_NO, PROCESS_NO,");
//strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
//strSQL.Append(" UPDATE_CNTR");
//strSQL.Append(" FROM ROLE_MASTER ");
//strSQL.Append(" ORDER BY ROLE");
strSQL.Append(" ENTRY_DATE, UPDATE_DATE, USER_CREATE, USER_UPDATE");
strSQL.Append(" FROM USER_ROLE_MASTER ");
db.DbParametersClear();
if (_role != "")
{
strSQL.Append(" WHERE USER_GROUP_CODE = @ROLE ");
db.DbPsetString("@ROLE", _role);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "role");
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);
}
}
