/// <summary>
/// ロケーション一覧の取得
///
/// 使用画面：MstLocationLblPrt
/// </summary>
public DataSet GetLocationListAll()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("L.WHS_CD, W.WHS_DESC, ");
strSQL.Append("L.LOCATION, L.LOCATION_DESC ");
strSQL.Append(" FROM LOCATION_MASTER L ");
strSQL.Append(" INNER JOIN WAREHOUSE_MASTER W ON W.WHS_CD = L.WHS_CD ");
strSQL.Append(" WHERE 1 = 1 ");
if (_whs_cd != "")
{
strSQL.Append(" AND L.WHS_CD = @WHS_CD ");
}
if (_location != "")
{
strSQL.Append(" AND L.LOCATION = @LOCATION ");
}
strSQL.Append(" ORDER BY L.WHS_CD, L.LOCATION");
db.DbParametersClear();
if (_whs_cd != "")
{
db.DbPsetString("@WHS_CD", _whs_cd);
}
if (_location != "")
{
db.DbPsetString("@LOCATION", _location);
}
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>
/// MRP倉庫一覧の取得
///
/// 使用画面：SrchMrpWhs
/// </summary>
public DataSet GetMrpWarehouseList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("WHS_CD, MRP_WHS, WHS_DESC, WHS_DESC2, ");
strSQL.Append("RECV_LOC, WHS_TYPE, WHS_ITEM_TYPE, LOCATION_CTRL_FLAG, AUTO_HOUSING_TYPE,");
strSQL.Append("DIRECT_SHIP_FLAG, VENDOR_CD, CONS_WHS_FLAG, CONS_VENDOR_CD, ");
strSQL.Append("PASS_THRU_WHS_CD, BOND_FLAG, 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, SUPPLY_TYPE ");
strSQL.Append(" FROM WAREHOUSE_MASTER ");
strSQL.Append(" WHERE WHS_CD = MRP_WHS ");
if (_mrp_whs != "")
{
strSQL.Append(" AND MRP_WHS = @MRP_WHS ");
db.DbParametersClear();
db.DbPsetString("@MRP_WHS", _mrp_whs);
}
strSQL.Append(" ORDER BY WHS_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>
/// 工程一覧の取得
///
/// 使用画面：MstProcessMnt
/// </summary>
public DataSet GetProcessSearch()
{
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 (_ws_cd != "")
{
strSQL.Append(" WHERE WS_CD = @WS_CD ");
}
strSQL.Append(" ORDER BY PROCESS_CD ");
db.DbParametersClear();
if (_ws_cd != "")
{
db.DbPsetString("@WS_CD", _ws_cd, ComConst.DB_IN);
}
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>
/// 使用画面：PurManPOPlanRpt
/// </summary>
public DataSet GetDayOfWeek()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT DISTINCT ");
strSQL.Append("CALENDAR_CYM, DAY_OF_WEEK, TTL_WEEK ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID = :CALENDAR_ID ");
strSQL.Append(" AND CALENDAR_DATE = :CALENDAR_DATE ");
db.DbParametersClear();
db.DbPsetString("CALENDAR_ID", _calendar_id);
db.DbPsetInt("CALENDAR_DATE", _calendar_date);
}
else
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID = @CALENDAR_ID ");
strSQL.Append(" AND CALENDAR_DATE = @CALENDAR_DATE ");
db.DbParametersClear();
db.DbPsetString("@CALENDAR_ID", _calendar_id);
db.DbPsetInt("@CALENDAR_DATE", _calendar_date);
}
strSQL.Append(" ORDER BY CALENDAR_CYM ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "Tabel");
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/10/21 end
#region SetddlData ｺﾝﾎﾞﾎﾞｯｸｽに表示値をｾｯﾄする(SQL)
/// <summary>
/// ｺﾝﾎﾞﾎﾞｯｸｽに表示値をｾｯﾄする
/// </summary>
/// <param name="ddl">ｺﾝﾎﾞﾎﾞｯｸｽ</param>
/// <param name="SQL">SQL文</param>
/// <param name="blank">先頭に空白をｾｯﾄする</param>
/// <param name="Description">Descriptionをｾｯﾄする</param>
public void SetDdlData(object ddl, string SQL, bool blank)
{
ComDB db = new ComDB();
DataSet ds = db.DbDataSet(SQL, "ddlData");
db.DbClose(); //add by suo 2010/05/25
if (ds != null && ds.Tables["ddlData"] != null)
{
DataTable dt = ds.Tables["ddlData"];
dt.Columns[0].ColumnName = "VALUE";
dt.Columns[1].ColumnName = "DISP";
if (blank)
{
//ﾌﾞﾗﾝｸ行の追加
DataRow _dr = dt.NewRow();
dt.Rows.InsertAt(_dr, 0);
}
try
{
if (ddl is System.Web.UI.WebControls.DropDownList)
{
//ｺﾝﾎﾞﾎﾞｯｸｽにｾｯﾄ
((DropDownList)ddl).DataSource = dt;
((DropDownList)ddl).DataTextField = "DISP";
((DropDownList)ddl).DataValueField = "VALUE";
((DropDownList)ddl).DataBind();
}
//Add by Ubiq-Zhu 2010/04/20
else if (ddl is System.Web.UI.WebControls.ListBox)
{
((ListBox)ddl).DataSource = dt;
((ListBox)ddl).DataTextField = "DISP";
((ListBox)ddl).DataValueField = "VALUE";
((ListBox)ddl).DataBind();
}
//else if (ddl is AjaxDataControls.GridViewDropDownListColumn)
//{
// //datagridviewのｺﾝﾎﾞにｾｯﾄ
// ((GridViewDropDownListColumn)ddl).DisplayMember = "DISP";
// ((GridViewDropDownListColumn)ddl).ValueMember = "VALUE";
// ((GridViewDropDownListColumn)ddl).DataSource = dt;
// ((GridViewDropDownListColumn)ddl).DataBind();
//}
}
catch
{
//throw new ComException(e.Message, null, null, MethodInfo.GetCurrentMethod().Name);
return;
}
}
}
/// <summary>
/// 指定画面の画面ラベル一覧
/// </summary>
public DataSet GetScreenLabelList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT CONTROL_ID, STD_ITEM,");
strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID, UPDATE_CNTR");
strSQL.Append(" FROM SCREEN_LABEL_MASTER");
if (_db_type == "ORACLE")
{
strSQL.Append(" WHERE SCREEN_ID = :SCREEN_ID");
strSQL.Append(" ORDER BY CONTROL_ID");
db.DbParametersClear();
db.DbPsetString("SCREEN_ID", _screen_id);
}
else
{
strSQL.Append(" WHERE SCREEN_ID = @SCREEN_ID");
strSQL.Append(" ORDER BY CONTROL_ID");
db.DbParametersClear();
db.DbPsetString("@SCREEN_ID", _screen_id);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "screen");
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);
}
}
public DataSet GetOrderImportList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("SLIP_NO, DATE, TIME, PROC_LOC_FROM, ");
strSQL.Append("MAKER_NAME, SHIP_TO, PART_NO, PART_NAME, ");
strSQL.Append("COLOR, RECEIVE_CYCLE, INV_CATEGORY, KD_LOT_FROM, ");
strSQL.Append("KD_LOT_TO, PROC_LOC_TO, DELV_ADDR, DELV_SCDLL_QTY, ");
strSQL.Append("PART_UNLD_QTY, PROGRESS, INV_LOC_CD, ");
strSQL.Append("FLAG ");
strSQL.Append(" FROM PUR_ORDER_LOCAL ");
//if (_whs_type == 1)
// strSQL.Append(" WHERE WHS_TYPE = 1 ");
strSQL.Append(" ORDER BY SLIP_NO");
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>
/// 指定ロールを持つユーザーの一覧
///
/// 使用画面：MstRoleMnt
/// </summary>
public DataSet GetUserListByRole()
{
ComDB db = new ComDB("", "COM");
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT U.USER_ID, ");
strSQL.Append("U.USER_FAMILY_NAME + ' ' + U.USER_FIRST_NAME AS USER_NAME ");
strSQL.Append(" FROM USER_ROLE_MASTER UR, USER_MASTER U");
strSQL.Append(" WHERE UR.ROLE = @ROLE");
strSQL.Append(" AND U.USER_ID = UR.USER_ID");
strSQL.Append(" AND U.LOGICAL_DEL_FLAG = 0 ");
strSQL.Append(" ORDER BY U.USER_ID");
db.DbParametersClear();
db.DbPsetString("@ROLE", _role);
try
{
ds = db.DbDataSet(strSQL.ToString(), "user");
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>
/// 棚卸結果登録Calendar詳細の取得
///
/// 使用画面：InvStockTakingAct
/// </summary>
public DataSet GetCalendarYearMonth()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append(" SELECT ");
strSQL.Append(" DISTINCT(SUBSTRING(CONVERT(VARCHAR(6),CALENDAR_CYM),1,4) ) AS CALENDAR_CYM ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID = :00 ");
strSQL.Append(" ORDER BY CALENDAR_CYM ");
}
else
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID ='00' ");
strSQL.Append(" ORDER BY CALENDAR_CYM ");
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "Cal");
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);
}
}
public DataSet GetOrderImportList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("CONTENT_NO, PART_NO, PART_NAME, COLOR, ");
strSQL.Append("PROC_LOC_FROM, PROC_LOC_TO, INV_LOC_CD, DOCK_NO, ,");
strSQL.Append("KD_LOT_NO, PC_NO, DELV_SCDL_TIME, DELV_SCDL_QTY, ");
strSQL.Append("INV_CATEGORY, FLAG ");
strSQL.Append(" FROM PUR_ORDER_IMPORT ");
//if (_whs_type == 1)
// strSQL.Append(" WHERE WHS_TYPE = 1 ");
strSQL.Append(" ORDER BY CONTENT_NO");
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>
/// 工程NG理由の取得
///
/// 使用画面：MstProcessNGReasonMnt
/// </summary>
public DataSet GetList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
strSQL.Append("SELECT ");
strSQL.Append("D.WS_CD, R.WS_DESC, D.PROCESS_CD, P.PROCESS_DESC, D.NG_REASON_CD, ");
strSQL.Append("D.NG_REASON_DESC ");
strSQL.Append(" FROM PROCESS_NG_REASON_MASTER D ");
strSQL.Append(" LEFT JOIN WORKSHOP_MASTER R ON D.WS_CD = R.WS_CD ");
strSQL.Append(" LEFT JOIN PROCESS_MASTER P ON D.WS_CD = P.WS_CD AND D.PROCESS_CD = P.PROCESS_CD ");
strSQL.Append(" WHERE D.WS_CD = @WS_CD ");
strSQL.Append(" AND D.PROCESS_CD = @PROCESS_CD ");
strSQL.Append(" ORDER BY D.NG_REASON_CD ");
db.DbParametersClear();
db.DbPsetString("@WS_CD", _ws_cd);
db.DbPsetString("@PROCESS_CD", _process_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>
/// ロケーション一覧の取得
///
/// 使用画面：MstLocationMnt
/// </summary>
public DataSet GetLocationList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
//strSQL.Append("SELECT ");
//strSQL.Append("LOCATION, LOCATION_DESC ");
//strSQL.Append(" FROM LOCATION_MASTER ");
//strSQL.Append(" WHERE WHS_CD = @WHS_CD ");
//strSQL.Append(" ORDER BY LOCATION");
strSQL.Append("SELECT ");
strSQL.Append("LOCATION_NO, LOCATION_NAME, LOCATION_DESCRIPTION, DEPT_NO, ");
strSQL.Append("ENTRY_DATE, UPDATE_DATE, USER_CREATE, USER_UPDATE ");
strSQL.Append(" FROM LOCATION_MASTER ");
//db.DbParametersClear();
//db.DbPsetString("@WHS_CD", _whs_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>
/// 部門詳細の取得
///
/// 使用画面：MstMgmtLevelMnt
/// </summary>
public DataSet GetMGMTList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
strSQL.Append(" SELECT ");
strSQL.Append(" MANAGEMENT_LEVEL, MANAGEMENT_DESCRIPTION, ");
strSQL.Append(" ENTRY_DATE, UPDATE_DATE, USER_CREATE, USER_UPDATE ");
strSQL.Append(" FROM MANAGEMENT_MASTER ");
db.DbParametersClear();
if (_mgmt_cd != "")
{
strSQL.Append(" WHERE MANAGEMENT_LEVEL = @MGMT_CD ");
db.DbPsetString("@MGMT_CD", _mgmt_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>
/// 資源ダウン理由の取得
///
/// 使用画面：MstDownReasonMnt
/// </summary>
public DataSet GetResourceList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
strSQL.Append("SELECT ");
strSQL.Append("D.RESOURCE_CD, R.RESOURCE_DESC, D.DOWN_REASON_CD, ");
strSQL.Append("D.DOWN_REASON_DESC ");
strSQL.Append(" FROM DOWN_REASON_MASTER D ");
strSQL.Append(" LEFT JOIN RESOURCE_MASTER R ON D.RESOURCE_CD = R.RESOURCE_CD ");
strSQL.Append(" WHERE D.RESOURCE_CD = @RESOURCE_CD ");
strSQL.Append(" ORDER BY D.DOWN_REASON_CD ");
db.DbParametersClear();
db.DbPsetString("@RESOURCE_CD", _resource_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);
}
}
public DataSet GetPickingList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append(" SELECT DELH.MC_HUM_NO AS MC_HUM_NO, DELH.CREATE_DATE AS CREATE_DATE, DELH.CREATE_BY AS CREATE_BY, ");
strSQL.Append(" DELD.VENDOR AS VENDOR, DELD.DEST AS DEST, DELD.MODEL AS MODEL, DELD.MC_DEL_N_2 AS MC_DEL_N_2, ");
strSQL.Append(" DELD.PC_NO AS PC_NO, DELD.BLOCK_QTY AS BLOCK_QTY, DELD.HEAD_QTY AS HEAD_QTY ");
strSQL.Append(" FROM [dbo].[PLAN_DEL_MC_HEADER] AS DELH LEFT JOIN [dbo].[PLAN_DEL_MC_DETAIL] AS DELD ");
strSQL.Append(" ON DELH.MC_HUM_NO = DELD.MC_HUM_NO ");
//strSQL.Append(" ORDER BY HEAD_NO ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "Cal");
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>
/// 資源詳細の取得
///
/// 使用画面：SrchResource
/// </summary>
public DataSet GetResourceList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
strSQL.Append("SELECT ");
strSQL.Append("R.RESOURCE_CD, R.RESOURCE_DESC, R.RESOURCE_DESC2, ");
strSQL.Append("R.RESOURCE_TYPE, R.RESOURCE_GRP, R.WS_CD, ");
strSQL.Append("R.PROCESS_CD, R.DISPLAY_SEQ, R.STD_WORKING_TIME, ");
strSQL.Append("R.TIME_DESC ");
strSQL.Append(" FROM RESOURCE_MASTER R ");
strSQL.Append(" ORDER BY RESOURCE_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 DataSet GetMessageList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT MSG_CD, LANG, MSG_DESC, RANK, ");
strSQL.Append(" ENTRY_DATE, CHG_DATE, CHG_USER_ID,");
strSQL.Append(" UPDATE_CNTR");
strSQL.Append(" FROM MESSAGE_MASTER ");
strSQL.Append(" ORDER BY MSG_CD");
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);
}
}
public DataSet GetOrder(int syear, int smonth)
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append(" SELECT ");
strSQL.Append(" MC_PROD_NO AS HEAD_NO ");
strSQL.Append(" FROM PLAN_PROD_MC_HEADER ");
strSQL.Append(" WHERE [YEAR] = " + syear + " AND [MONTH] = " + smonth);
strSQL.Append(" ORDER BY HEAD_NO ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "Cal");
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);
}
}
public DataSet GetPickingOrder()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append(" SELECT ");
strSQL.Append(" DISTINCT(MC_HUM_NO) AS HEAD_NO ");
strSQL.Append(" FROM PLAN_DEL_MC_HEADER ");
strSQL.Append(" ORDER BY HEAD_NO ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "Cal");
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>
/// 営業倉庫一覧の取得
/// <para>使用画面：[SrchSDMWhs]</para>
/// </summary>
public DataSet GetSDMWarehosueList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append(" SELECT * ");
strSQL.Append(" FROM WHS_MST ");
strSQL.Append(" ORDER BY WHS_CD ");
try
{
ds = db.DbDataSet(strSQL.ToString(), "WHS_CD");
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>
/// 指定ユーザーのロール一覧
///
/// 使用画面：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>
/// ロール一覧の取得、空白行つき
///
/// 使用画面：AppRouteDef
/// </summary>
public DataSet GetRoleListWithBlank()
{
ComDB db = new ComDB("", "COM");
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT '0' AS SORT_KEY, NULL AS ROLE, NULL AS ROLE_DESC ");
strSQL.Append(" UNION ");
strSQL.Append("SELECT '1', ROLE, ");
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(" FROM ROLE_MASTER ");
strSQL.Append(" ORDER BY SORT_KEY, ROLE");
//db.DbParametersClear();
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>
/// 製番検索2のDATA取得
///
/// <para>使用画面：MstJOC2Mnt</para>
/// </summary>
public DataSet GetJOCList2()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
//-->ADD BY UBIQ-SUO 2010/09/25
if (_joc_type == 0)//製番区分=製造製番の場合
{
strSQL.Append(" SELECT DISTINCT ");
strSQL.Append(" J.JOC_CD, J.ITEM_NO JOC_DESC, 0 JOC_TYPE, J.ITEM_NO, ");
strSQL.Append(" I.ITEM_DESC ");
strSQL.Append(" FROM PRODUCTION_PLAN J ");
strSQL.Append(" LEFT JOIN ITEM_MASTER I ON J.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE ");
if (_srchflag == 0)//未完の場合
{
strSQL.Append(" ORDER_STATUS < 90 ");
}
else//完了の場合
{
strSQL.Append(" ORDER_STATUS >= 90 ");
}
if (_db_type == "ORACLE")
{
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.ITEM_NO = :ITEM_NO ");
db.DbPsetString("ITEM_NO", _item_no);
}
}
else
{
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
}
}
else//製番区分=個別製番の場合
{
strSQL.Append(" SELECT ");
strSQL.Append(" J.JOC_CD, J.JOC_DESC, 1 JOC_TYPE, J.JOC_ITEM_NO ITEM_NO, ");
strSQL.Append(" I.ITEM_DESC ");
strSQL.Append(" FROM JOC_MASTER J ");
strSQL.Append(" LEFT JOIN ITEM_MASTER I ON J.JOC_ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE JOC_TYPE = 1 ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
strSQL.Append(" AND JOC_STATUS = :JOC_STATUS ");
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = :ITEM_NO ");
db.DbPsetString("ITEM_NO", _item_no);
}
db.DbPsetInt("JOC_STATUS", _srchflag);
}
else
{
db.DbParametersClear();
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
}
}
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 (ds.Tables[0].Rows.Count > 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
if (_joc_type == 0)//製番区分=製造製番の場合
{
strSQL.Append(" SELECT TOP 2000 A.* FROM (SELECT DISTINCT "); //UPD BY UBIQ-SUO 2010/09/26
strSQL.Append(" J.JOC_CD, J.ITEM_NO JOC_DESC, 0 JOC_TYPE, J.ITEM_NO, ");
strSQL.Append(" I.ITEM_DESC ");
strSQL.Append(" FROM PRODUCTION_PLAN J ");
strSQL.Append(" LEFT JOIN ITEM_MASTER I ON J.ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE ");
if (_srchflag == 0)//未完の場合
{
strSQL.Append(" ORDER_STATUS < 90 ");
}
else//完了の場合
{
strSQL.Append(" ORDER_STATUS >= 90 ");
}
if (_db_type == "ORACLE")
{
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.ITEM_NO = :ITEM_NO )");
db.DbPsetString("ITEM_NO", _item_no);
}
}
else
{
db.DbParametersClear();
if (_item_no != "")
{
strSQL.Append(" AND J.ITEM_NO = @ITEM_NO ) A");
db.DbPsetString("@ITEM_NO", _item_no);
}
}
strSQL.Append(" ) A "); //Add Ubiq-Sai 2011.08.25
}
else//製番区分=個別製番の場合
{
strSQL.Append(" SELECT TOP 2000 "); //UPD BY UBIQ-SUO 2010/09/26 ADD TOP 2000
strSQL.Append(" J.JOC_CD, J.JOC_DESC, 1 JOC_TYPE, J.JOC_ITEM_NO ITEM_NO, ");
strSQL.Append(" I.ITEM_DESC ");
strSQL.Append(" FROM JOC_MASTER J ");
strSQL.Append(" LEFT JOIN ITEM_MASTER I ON J.JOC_ITEM_NO = I.ITEM_NO ");
strSQL.Append(" WHERE JOC_TYPE = 1 ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
strSQL.Append(" AND JOC_STATUS = :JOC_STATUS ");
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = :ITEM_NO ");
db.DbPsetString("ITEM_NO", _item_no);
}
db.DbPsetInt("JOC_STATUS", _srchflag);
}
else
{
db.DbParametersClear();
strSQL.Append(" AND JOC_STATUS = @JOC_STATUS ");
if (_item_no != "")
{
strSQL.Append(" AND J.JOC_ITEM_NO = @ITEM_NO ");
db.DbPsetString("@ITEM_NO", _item_no);
}
db.DbPsetInt("@JOC_STATUS", _srchflag);
}
}
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>
/// 作業区一覧の取得
///
/// 使用画面：MstWsMnt
/// </summary>
public DataSet GetWorkShopList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("WS_CD, WS_DESC, WS_DESC2, REP_WS_CD, ");
strSQL.Append("COST_CENTER, SC_FLAG, VENDOR_CD, INPUT_FLAG, REJ_WHS, "); //ADD UBIQ-LIU 2010/11/24 ADD[REJ_WHS]
strSQL.Append("LOT_CTRL_FLAG, NEG_BAL_FLAG, FRONT_END_FLAG, COMPL_FLAG, WS_PRC, ");
strSQL.Append("AUTO_DISB_FLAG, WORK_INST_TYPE, COMPL_SLIP_PATTERN, 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");
db.DbParametersClear();
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM WORKSHOP_MASTER ");
strSQL.Append(" WHERE 1=1 ");
if (_sc_flag >= 0)
{
strSQL.Append(" AND SC_FLAG =:SC_FLAG");
db.DbPsetInt("SC_FLAG", _sc_flag);
}
strSQL.Append(" ORDER BY WS_CD");
}
else
{
strSQL.Append(" FROM WORKSHOP_MASTER ");
strSQL.Append(" WHERE 1=1 ");
if (_sc_flag >= 0)
{
strSQL.Append(" AND SC_FLAG =@SC_FLAG");
db.DbPsetInt("@SC_FLAG", _sc_flag);
}
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);
}
}
//--> ADD BY UBIQ-SUO 2010/9/13
/// <summary>
/// input Week or Month and range
/// OUTPUT NEXT range Month and Week no
/// 使用画面：PurManPOPlanRpt
/// </summary>
public DataSet GetWeekOFNext(int range)
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
if (date_type == 0)
{
strSQL.Append("SELECT DISTINCT ");
strSQL.Append("TTL_WEEK , min(CALENDAR_DATE) MIN_DATE, max(CALENDAR_DATE) MAX_DATE ");
}
else
{
strSQL.Append("SELECT DISTINCT ");
strSQL.Append("CALENDAR_CYM , min(CALENDAR_DATE) MIN_DATE, max(CALENDAR_DATE) MAX_DATE ");
}
if (_db_type == "ORACLE")
{
if (date_type == 0)
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE TTL_WEEK >= :TTL_WEEK AND TTL_WEEK <= :TTL_WEEK + :RANGE - 1 ");
strSQL.Append(" AND CALENDAR_ID = @CALENDAR_ID ");
strSQL.Append(" GROUP BY TTL_WEEK ");
db.DbParametersClear();
db.DbPsetInt("RANGE", range);
db.DbPsetString("CALENDAR_ID", _calendar_id);
db.DbPsetInt("TTL_WEEK", _ttl_week);
}
else
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_DATE >= :CALENDAR_DATE AND CALENDAR_DATE <= :CALENDAR_CYM_END ");
strSQL.Append(" AND CALENDAR_ID = @CALENDAR_ID ");
strSQL.Append(" GROUP BY CALENDAR_CYM ");
db.DbParametersClear();
db.DbPsetString("CALENDAR_ID", _calendar_id);
db.DbPsetInt("CALENDAR_CYM", _calendar_cym);
}
}
else
{
if (date_type == 0)
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE TTL_WEEK >= @TTL_WEEK AND TTL_WEEK <= @TTL_WEEK + @RANGE - 1 AND CALENDAR_ID = @CALENDAR_ID ");
strSQL.Append(" GROUP BY TTL_WEEK ");
db.DbParametersClear();
db.DbPsetString("@CALENDAR_ID", _calendar_id);
db.DbPsetInt("@TTL_WEEK", _ttl_week);
db.DbPsetInt("@RANGE", range);
}
else
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_DATE >= @CALENDAR_DATE AND CALENDAR_DATE <= ");
strSQL.Append(" CONVERT(VARCHAR(8), DATEADD(DAY,-1,(DATEADD(MONTH,@RANGE,CONVERT(DATETIME,CONVERT(VARCHAR(8),@CALENDAR_DATE))))), 112) ");
strSQL.Append(" AND CALENDAR_ID = @CALENDAR_ID ");
strSQL.Append(" GROUP BY CALENDAR_CYM ");
db.DbParametersClear();
db.DbPsetString("@CALENDAR_ID", _calendar_id);
db.DbPsetInt("@CALENDAR_DATE", _calendar_date);
db.DbPsetInt("@RANGE", range);
}
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "Tabel");
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/9/13
/// <summary>
/// 稼働日Calendar詳細の取得
///
/// 使用画面：PlanProdPlan
/// </summary>
public DataSet GetWeekDayCalendarList()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
strSQL.Append("CALENDAR_DATE, CALENDAR_CYM, WEEK_NO, DAY_OF_WEEK, ");
strSQL.Append("HOLIDAY_FLAG, PO_RLS_FLAG, TTL_WORK_DAYS, CALENDAR_ID,");
strSQL.Append("TTL_DAYS, TTL_WEEK, STD_WORKING_HOUR, ");
strSQL.Append("CHG_PGM, CHG_USERID, UPDATE_CNTR ");
if (_db_type == "ORACLE")
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID = :CALENDAR_ID");
strSQL.Append(" AND CALENDAR_CYM = :CALENDAR_CYM");
strSQL.Append(" ORDER BY CALENDAR_DATE ");
db.DbParametersClear();
db.DbPsetString("CALENDAR_ID", _calendar_id);
db.DbPsetInt("CALENDAR_CYM", _calendar_cym);
}
else
{
strSQL.Append(" FROM CALENDAR_MASTER ");
strSQL.Append(" WHERE CALENDAR_ID = @CALENDAR_ID");
strSQL.Append(" AND HOLIDAY_FLAG = 0");
strSQL.Append(" AND CALENDAR_DATE BETWEEN @FROM_TTL_WORK_DAYS AND @TO_TTL_WORK_DAYS");
strSQL.Append(" ORDER BY CALENDAR_DATE ");
db.DbParametersClear();
db.DbPsetString("@CALENDAR_ID", _calendar_id);
db.DbPsetInt("@FROM_TTL_WORK_DAYS", _from_ttl_work_days);
db.DbPsetInt("@TO_TTL_WORK_DAYS", _to_ttl_work_days);
}
try
{
ds = db.DbDataSet(strSQL.ToString(), "Cal");
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>
/// 資源別カレンダマスタ、カレンダマスタを検索して明細部を編集する
///
/// 使用画面：MstResourceCalendarMnt
/// </summary>
public DataSet GetResourceCalendarListByMonth()
{
ComDB db = new ComDB(_db_type);
StringBuilder strSQL = new StringBuilder();
DataSet ds = new DataSet();
//--- sql
strSQL.Append("SELECT ");
//strSQL.Append(" C.CALENDAR_DATE, C.DAY_OF_WEEK, C.STD_WORKING_HOUR, C.HOLIDAY_FLAG,");
strSQL.Append(" C.CALENDAR_DATE, C.DAY_OF_WEEK, C.HOLIDAY_FLAG, ");
strSQL.Append(" STD_WORKING_HOUR = CASE WHEN RC.CALENDAR_DATE IS NULL THEN ");
strSQL.Append(" (CASE WHEN C.HOLIDAY_FLAG = 1 THEN 0 ELSE C.STD_WORKING_HOUR END ) ");
strSQL.Append(" ELSE RC.WORKING_TIME END, ");
strSQL.Append(" WORKING_TIME = CASE WHEN RC.CALENDAR_DATE IS NULL THEN ");
strSQL.Append(" (CASE WHEN C.HOLIDAY_FLAG = 1 THEN 0 ELSE (SELECT STD_WORKING_TIME FROM RESOURCE_MASTER WHERE RESOURCE_CD = @RESOURCE_CD) END ) ");
strSQL.Append(" ELSE RC.WORKING_TIME END, ");
strSQL.Append(" TIME_DESC = CASE WHEN RC.CALENDAR_DATE IS NULL THEN ");
strSQL.Append(" (CASE WHEN C.HOLIDAY_FLAG = 1 THEN '' ELSE (SELECT TIME_DESC FROM RESOURCE_MASTER WHERE RESOURCE_CD = @RESOURCE_CD) END ) ");
strSQL.Append(" ELSE RC.TIME_DESC END ");
if (_db_type == "ORACLE")
{
db.DbParametersClear();
strSQL.Append(" FROM CALENDAR_MASTER C ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_CALENDAR RC ON RC.CALENDAR_DATE = C.CALENDAR_DATE ");
strSQL.Append(" AND RC.RESOURCE_CD = :RESOURCE_CD ");
strSQL.Append(" WHERE ");
strSQL.Append(" C.CALENDAR_ID = '00' ");
strSQL.Append(" AND C.CALENDAR_DATE = :CALENDAR_DATE ");
db.DbPsetString("RESOURCE_CD", _resource_cd);
db.DbPsetInt("CALENDAR_DATE", _calendar_date);
}
else
{
db.DbParametersClear();
strSQL.Append(" FROM CALENDAR_MASTER C ");
strSQL.Append(" LEFT OUTER JOIN RESOURCE_CALENDAR RC ON RC.CALENDAR_DATE = C.CALENDAR_DATE ");
strSQL.Append(" AND RC.RESOURCE_CD = @RESOURCE_CD ");
strSQL.Append(" WHERE ");
strSQL.Append(" C.CALENDAR_ID = '00' ");
strSQL.Append(" AND C.CALENDAR_CYM = @CALENDAR_DATE ");
db.DbPsetString("@RESOURCE_CD", _resource_cd);
db.DbPsetInt("@CALENDAR_DATE", _calendar_date);
strSQL.Append(" ORDER BY C.CALENDAR_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>
/// 部門詳細の取得
///
/// 使用画面：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;
//}
strSQL.Append(" SELECT ");
strSQL.Append(" D.DEPT_NO, D.DEPT_NAME, D.DEPT_DESCRIPTION, ");
strSQL.Append(" D.ENTRY_DATE, D.UPDATE_DATE, D.USER_CREATE, D.USER_UPDATE ");
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_cd != "")
{
strSQL.Append(" AND D.DEPT_NO =@DEPT_TYPE");
db.DbPsetString("@DEPT_TYPE", _dept_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);
}
}