public StaffJSON Delete(string staffCode)
{
StaffJSON result = new StaffJSON();
//StringBuilder sbSQL = new StringBuilder();
try
{
StringBuilder sbSQL = new StringBuilder();
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("DELETE FROM mstaff");
sbSQL.AppendLine("WHERE staffcode = :p_staffcode");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("p_staffcode", staffCode);
Debug.Write(staffCode);
npgDB.ExecuteNonQuery();
result.success = true;
//using (NpgsqlDataReader rec = npgDB.Query())
//{
// if (rec.Read())
// {
// string deletedStaff = NpgDB.getString(rec, "staffCode");
// result.staff = new Staff
// {
// staffCode = deletedStaff
// };
// result.success = true;
// }
//}
}
}
catch (Exception ex) { }
return(result);
}
public App GetApp(string id)
{
App app = new App();
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT app.appid, app.name, app.description, app.icon, installfile.vercd, installfile.vernm, installfile.filenm");
sbSQL.AppendLine("FROM mapp app");
sbSQL.AppendLine("LEFT JOIN(SELECT installfile.appid, installfile.vercd, installfile.vernm, installfile.filenm");
sbSQL.AppendLine(" FROM dinstallfile installfile");
sbSQL.AppendLine(" INNER JOIN (SELECT appid, max(vercd) maxvercd FROM dinstallfile GROUP BY appid) newleast");
sbSQL.AppendLine(" ON installfile.appid = newleast.appid AND installfile.vercd = newleast.maxvercd) installfile");
sbSQL.AppendLine("ON installfile.appid = app.appid");
sbSQL.AppendLine("WHERE app.appid = :p_id");
sbSQL.AppendLine("AND app.startstmp <= CURRENT_TIMESTAMP AND app.endstmp >= CURRENT_TIMESTAMP");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_id", id);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
app = new App(rec, true);
}
}
}
}
catch (Exception ex)
{
}
return(app);
}
public long PageCount()
{
long RowCount = 0;
try
{
StringBuilder sbSQL = new StringBuilder();
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT count(staffcode)::varchar AS count FROM mstaff");
// sbSQL.AppendLine("ORDER by mstaff.staffcode");
npgDB.Command = sbSQL.ToString();
Debug.Write(sbSQL.ToString());
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
string strPageCount = NpgDB.getString(rec, "count");
RowCount = long.Parse(strPageCount);
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
long count = (long)Math.Ceiling((float)RowCount / 10);
return(count);
}
public StaffJSON CheckLogin(string staffCode, string password)
{
StaffJSON result = new StaffJSON();
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT staff.staffcode, staff.kanjiname, staff.kananame, ward.wardcode, ward.wardname,staff.generationno ");
sbSQL.AppendLine("FROM mstaff staff");
sbSQL.AppendLine("LEFT JOIN mward ward ON ward.wardcode = ward.wardname");
sbSQL.AppendLine("WHERE staff.staffcode = :p_staffCode");
sbSQL.AppendLine("AND staff.password = :p_password");
sbSQL.AppendLine("AND staff.validstartdate <= CURRENT_DATE");
sbSQL.AppendLine("AND staff.validenddate >= CURRENT_DATE");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("p_staffCode", staffCode);
npgDB.SetParams("p_password", password);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
result.staff = new Staff(rec);
result.success = true;
}
}
}
}
catch (Exception ex)
{
}
return(result);
}
public List <Staff> GetListStaff(int pageCount)
{
List <Staff> staffs = new List <Staff>();
try
{
StringBuilder sbSQL = new StringBuilder();
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT mstaff.staffcode, mstaff.kananame, mstaff.kanjiname, mstaff.password, mward.wardcode,mward.wardname, mstaff.generationno");
sbSQL.AppendLine("FROM mstaff");
sbSQL.AppendLine("LEFT JOIN mward ON mward.wardcode = mstaff.wardcode");
sbSQL.AppendLine("ORDER BY mstaff.staffcode");
sbSQL.AppendLine("LIMIT 10");
sbSQL.AppendLine(" offset :p_pageCount");
pageCount = pageCount * 10;
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_pageCount", pageCount);
Debug.Write(sbSQL.ToString());
using (NpgsqlDataReader rec = npgDB.Query())
{
while (rec.Read())
{
staffs.Add(new Staff(rec));
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
return(staffs);
}
public String getDemo()
{
//
String rs = "";
//
StringBuilder sbSQL = new StringBuilder();
//
//DB接続
using (NpgDB npgDB = Connection.DBConnect())
{
// ﾃﾞｰﾀ取得
sbSQL.Clear();
sbSQL.AppendLine(" SELECT * ");
sbSQL.AppendLine(" FROM m_staff ");
sbSQL.AppendLine(" WHERE staffcd = :p_staffcd ");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("p_staffcd", "1001");
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
rs = NpgDB.getString(rec, "staffnm");
}
}
}
//EXIT
return(rs);
}
/////////////////////////////////////////////////////////////////////////
/// <summary> DBｵｰﾌﾟﾝ </summary>
/// <remarks>
/// 環境情報を元にDBをｵｰﾌﾟﾝ
/// </remarks>
/// <param name="evmt">環境情報</param>
internal static NpgDB DBConnect()
{
NpgDB npgDB = new NpgDB(System.Configuration.ConfigurationManager.ConnectionStrings, "DbPgSql");
npgDB.Open();
//
// Exit
return(npgDB);
}
public List <App> GetApps(string key)
{
string[] keys = null;
if (key != null)
{
keys = key.ToLower().Split(new string[] { " ", " " }, StringSplitOptions.None);
}
List <App> apps = new List <App>();
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT app.appid, app.name, app.description, app.icon");
sbSQL.AppendLine("FROM mapp app");
sbSQL.AppendLine("WHERE app.startstmp <= CURRENT_TIMESTAMP AND app.endstmp >= CURRENT_TIMESTAMP");
if (keys != null && keys.Length > 0)
{
sbSQL.AppendLine("AND (app.appid LIKE :p_key OR LOWER(app.name) LIKE :p_key");
if (keys.Length > 1)
{
for (int i = 1; i < keys.Length; i++)
{
sbSQL.AppendLine("OR app.appid LIKE :p_key_" + i + " OR LOWER(app.name) LIKE :p_key_" + i);
}
}
sbSQL.AppendLine(")");
}
npgDB.Command = sbSQL.ToString();
if (keys != null && keys.Length > 0)
{
npgDB.SetParams(":p_key", "%" + keys[0] + "%");
if (keys.Length > 1)
{
for (int i = 1; i < keys.Length; i++)
{
npgDB.SetParams(":p_key_" + i, "%" + keys[i] + "%");
}
}
}
using (NpgsqlDataReader rec = npgDB.Query())
{
while (rec.Read())
{
App app = new App(rec);
apps.Add(app);
}
}
}
}
catch (Exception ex)
{
}
return(apps);
}
/////////////////////////////////////////////////////////////////////////
/// <summary> ﾛｸﾞ出力 </summary>
/// <remarks>
/// ﾛｸﾞﾃｰﾌﾞﾙへﾛｸﾞを出力
/// </remarks>
/// <param name="sSts">ﾛｸﾞ種別</param>
/// <param name="sTtl">ﾛｸﾞﾀｲﾄﾙ</param>
/// <param name="sMsg">ﾛｸﾞﾒｯｾｰｼﾞ</param>
/// <param name="sMsgEx">ﾛｸﾞ負荷情報</param>
protected void LogWrite(string sSts, string sTtl, string sMsg, string sMsgEx)
{
StringBuilder sbSQL = new StringBuilder();
//
// ｴﾗｰﾄﾗｯﾌﾟ
try
{
// 環境存在判定
if (m_config != null)
{
using (NpgDB npgDB = TTCommon.DBConnect(m_config))
{
// SQLの作成 & 実行
sbSQL.AppendLine(" INSERT INTO " + m_sTbl);
sbSQL.AppendLine(" " + "(");
sbSQL.AppendLine(" " + "log_stamp" + ",");
sbSQL.AppendLine(" " + "log_kbn" + ",");
sbSQL.AppendLine(" " + "log_ttl" + ",");
sbSQL.AppendLine(" " + "log_memo" + ",");
sbSQL.AppendLine(" " + "log_ext" + ",");
sbSQL.AppendLine(" " + "log_prg_nm" + ",");
sbSQL.AppendLine(" " + "stf_cd" + ",");
sbSQL.AppendLine(" " + "stf_j_nm" + " ");
sbSQL.AppendLine(" " + ")" + " " + "VALUES" + " " + "(");
sbSQL.AppendLine(" " + "CURRENT_TIMESTAMP" + ",");
sbSQL.AppendLine(" " + ":log_kbn" + ",");
sbSQL.AppendLine(" " + ":log_ttl" + ",");
sbSQL.AppendLine(" " + ":log_memo" + ",");
sbSQL.AppendLine(" " + ":log_ext" + ",");
sbSQL.AppendLine(" " + ":log_prg_nm" + ",");
sbSQL.AppendLine(" " + ":stf_cd" + ",");
sbSQL.AppendLine(" " + ":stf_j_nm" + " ");
sbSQL.AppendLine(" " + ")");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("log_kbn", sSts);
npgDB.SetParams("log_ttl", sTtl);
npgDB.SetParams("log_memo", sMsg);
npgDB.SetParams("log_ext", sMsgEx);
npgDB.SetParams("log_prg_nm", m_sPgNm);
npgDB.SetParams("stf_cd", m_sStfCd);
npgDB.SetParams("stf_j_nm", m_sStfNm);
npgDB.ExecuteNonQuery();
}
}
else
{
LogfileWrite(sTtl, sMsg, sMsgEx);
}
}
catch (Exception ex)
{
DebugWrite("ﾛｸﾞ出力失敗:" + ex.Message);
LogfileWrite(sTtl, sMsg, ex.Source);
LogfileWrite(sTtl, sMsg, sMsgEx);
}
}
/////////////////////////////////////////////////////////////////////////
// <summary>
// ｲﾍﾞﾝﾄ処理
// </summary>
// <param name="delEventHandler">ｲﾍﾞﾝﾄ処理ﾃﾞﾘｹﾞｰﾄ</param>
// <remarks></remarks>
protected void OnEventExecute(string sEventName, OnEventHandler delEventHandler)
{
// ｴﾗｰﾄﾗｯﾌﾟ
try
{
// ﾛｸﾞ生成
m_log = new TTLog(m_sSysNm, m_sPgNm);
//
// 環境生成
m_config = new TTConfig(m_sConfigPath);
//
// ﾛｸﾞﾃｰﾌﾞﾙ設定
m_log.SetLogInfo(m_config, m_sLogTbl, m_sStfCd, m_sStfNm);
//
// DB接続
m_npgDB = TTCommon.DBConnect(m_config);
//
// 開始ﾛｸﾞ
m_log.EventStart(sEventName);
//
// ｲﾍﾞﾝﾄ処理実行
delEventHandler();
}
catch (TTConfig.TTConfigError ex)
{
m_log.Error("環境設定エラー", ex);
}
catch (IcelineExceptionNpgDBConnect ex)
{
m_log.Error("ＤＢ接続エラー", ex);
}
catch (System.Threading.ThreadAbortException)
{
// Redirectを実行するとこの例外が発生する為、Nop
}
catch (Exception ex)
{
m_log.Error("想定外エラー", ex);
}
finally
{
// 終了ﾛｸﾞ
m_log.EventEnd(sEventName);
//
// 後処理
m_log.Dispose();
//
if (m_npgDB != null)
{
m_npgDB.Dispose();
m_npgDB = null;
}
}
}
public List <Staff> GetListStaff(string code, int pageCount)
{
int offset = pageCount * 5;
List <Staff> staffs = new List <Staff>();
try
{
StringBuilder sbSQL = new StringBuilder();
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("SELECT mstaff.staffcode, mstaff.kananame, mstaff.kanjiname, mstaff.password, mward.wardcode, mward.wardname, mstaff.generationno FROM mstaff");
sbSQL.AppendLine("LEFT JOIN mward ON mward.wardcode = mstaff.wardcode");
sbSQL.AppendLine("WhERE (mstaff.staffcode LIKE :p_staffcode ");
sbSQL.AppendLine("OR mstaff.kananame LIKE :p_staffcode ");
sbSQL.AppendLine("OR mstaff.kanjiname LIKE :p_staffcode )");
//sbSQL.AppendLine(" offset :p_pageCount");
sbSQL.AppendLine("ORDER BY mstaff.staffcode");
sbSQL.AppendLine("LIMIT 5");
//sbSQL.AppendLine("OFFSET 5");
sbSQL.AppendLine("OFFSET :p_pageCount");
//sbSQL.AppendLine("LEFT join m_deptgroup ");
//sbSQL.AppendLine("ON m_department.deptgrpcd=m_deptgroup.deptgrpcd");
//sbSQL.AppendLine("ORDER by mstaff.staffcode");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("p_staffcode", "%" + code + "%");
npgDB.SetParams(":p_pageCount", offset);
Debug.Write(sbSQL.ToString());
using (NpgsqlDataReader rec = npgDB.Query())
{
while (rec.Read())
{
staffs.Add(new Staff(rec));
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
return(staffs);
}
public AppJSON Update(string staffCode, App app)
{
AppJSON result = new AppJSON();
if (app != null)
{
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("UPDATE mapp");
sbSQL.AppendLine("SET");
sbSQL.AppendLine("name = :p_name,");
sbSQL.AppendLine("description = :p_description,");
if (app.icon != "")
{
sbSQL.AppendLine("icon = :p_icon,");
}
sbSQL.AppendLine("upopr = :p_staff_code,");
sbSQL.AppendLine("upstmp = CURRENT_TIMESTAMP");
sbSQL.AppendLine("WHERE appid = :p_appid");
sbSQL.AppendLine("RETURNING appid");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_name", app.name);
npgDB.SetParams(":p_description", app.description);
if (app.icon != "")
{
npgDB.SetParams(":p_icon", app.icon);
}
npgDB.SetParams(":p_staff_code", staffCode);
npgDB.SetParams(":p_appid", app.appId);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
string updatedAppId = NpgDB.getString(rec, "appid");
result.app = new App
{
appId = updatedAppId
};
result.success = true;
}
}
}
}
catch (Exception ex) { }
}
return(result);
}
//////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// ｺﾝｽﾄﾗｸﾀ
/// </summary>
/// <param name="rec"></param>
public InstallFile(NpgsqlDataReader rec)
{
this.appId = NpgDB.getString(rec, "appid");
this.verNm = NpgDB.getString(rec, "vernm");
this.fileNm = NpgDB.getString(rec, "filenm");
this.upOpr = NpgDB.getString(rec, "upopr");
string verCdStr = NpgDB.getLongString(rec, "vercd");
try
{
this.verCd = int.Parse(verCdStr);
}
catch (Exception ex) { }
}
//////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// ｺﾝｽﾄﾗｸﾀ
/// </summary>
/// <param name="rec"></param>
public Staff(NpgsqlDataReader rec, bool isGetStaffInfo = false)
{
this.staffCode = NpgDB.getString(rec, "staffcode");
this.kanjiName = NpgDB.getString(rec, "kanjiname");
this.kanaName = NpgDB.getString(rec, "kananame");
this.generationno = NpgDB.getString(rec, "generationno");
this.password = NpgDB.getString(rec, "password");
this.staffWardCode = NpgDB.getString(rec, "wardcode");
if (isGetStaffInfo)
{
this.staffWardName = NpgDB.getString(rec, "wardname");
}
}
/////////////////////////////////////////////////////////////////////////
/// <summary> DBｵｰﾌﾟﾝ </summary>
/// <remarks>
/// 環境情報を元にDBをｵｰﾌﾟﾝ
/// </remarks>
/// <param name="evmt">環境情報</param>
internal static NpgDB DBConnect(TTConfig evmt)
{
NpgDB npgDB = new NpgDB(System.Configuration.ConfigurationManager.ConnectionStrings, "DbPgSql");
// NpgDB npgDB = new NpgDB( evmt.DBInfo.HOST,
// evmt.DBInfo.PORT,
// evmt.DBInfo.DBNAME,
// evmt.DBInfo.USER,
// evmt.DBInfo.PASS,
// evmt.DBInfo.TIMEOUT);
npgDB.Open();
//
// Exit
return(npgDB);
}
public Staff Getstaff(string code)
{
Staff staff = new Staff();
try
{
StringBuilder sbSQL = new StringBuilder();
using (NpgDB npgDB = Connection.DBConnect())
{ // siêu cấp mạnh mẽ
//sbSQL.AppendLine("SELECT * FROM mstaff");
sbSQL.AppendLine("SELECT mstaff.staffcode, mstaff.kananame, mstaff.kanjiname, mstaff.password, mward.wardcode,mward.wardname, mstaff.generationno");
sbSQL.AppendLine("FROM mstaff");
sbSQL.AppendLine("LEFT JOIN mward ON mward.wardcode = mstaff.wardcode");
sbSQL.AppendLine("WHERE mstaff.staffcode = :p_staffCode");
sbSQL.AppendLine("ORDER BY mstaff.staffcode");
//sbSQL.AppendLine("SELECT staff.staffcode, staff.kanjiname, staff.kananame, ward.wardcode, ward.wardname,staff.generationno ");
//sbSQL.AppendLine("FROM mstaff staff");
//sbSQL.AppendLine("LEFT JOIN mward ward ON ward.wardcode = ward.wardname");
//sbSQL.AppendLine("WHERE staff.staffcode = :p_staffCode");
//sbSQL.AppendLine("AND staff.password = :p_password");
//sbSQL.AppendLine("AND staff.validstartdate <= CURRENT_DATE");
//sbSQL.AppendLine("AND staff.validenddate >= CURRENT_DATE");
//sbSQL.AppendLine("LEFT join m_deptgroup ");
//sbSQL.AppendLine("ON m_department.deptgrpcd=m_deptgroup.deptgrpcd");
//sbSQL.AppendLine("ORDER by mstaff.staffcode");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams("p_staffcode", code);
Debug.Write(sbSQL.ToString());
using (NpgsqlDataReader rec = npgDB.Query())
{
while (rec.Read())
{
staff = new Staff(rec, true);
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
return(staff);
}
public StaffJSON Update(string staffCod, Staff staff)
{
StaffJSON result = new StaffJSON();
//result.success = false;
if (staff != null)
{
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("UPDATE mstaff");
sbSQL.AppendLine("SET");
sbSQL.AppendLine("kananame = :p_kananame,");
sbSQL.AppendLine("kanjiname = :p_kanjiname,");
sbSQL.AppendLine("generationno = :p_generationno,");
sbSQL.AppendLine("password = :p_password,");
sbSQL.AppendLine("wardcode = :p_wardcode");
sbSQL.AppendLine("WHERE staffcode = :p_staffcode");
sbSQL.AppendLine("RETURNING staffcode, kananame, kanjiname, password, generationno, wardcode");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_staffcode", staff.staffCode);
npgDB.SetParams(":p_kananame", staff.kanaName);
npgDB.SetParams(":p_kanjiname", staff.kanjiName);
npgDB.SetParams(":p_generationno", staff.generationno);
npgDB.SetParams(":p_password", staff.password);
npgDB.SetParams(":p_wardcode", staff.staffWardCode);
Debug.WriteLine(sbSQL.ToString());
//npgDB.ExecuteNonQuery();
//result.success = true;
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
result.staff = new Staff(rec);
result.success = true;
}
}
}
}
catch (Exception ex) { }
}
return(result);
}
public StaffJSON Create(Staff staff)
{
StaffJSON result = new StaffJSON();
if (staff != null)
{
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("INSERT INTO mstaff");
sbSQL.AppendLine("(");
sbSQL.AppendLine("staffcode, kananame, kanjiname, password, generationno");
sbSQL.AppendLine(")");
sbSQL.AppendLine("VALUES");
sbSQL.AppendLine("(");
sbSQL.AppendLine("nextval('staffcode_sequence'), :p_kananame, :p_kanjiname, :p_password, :p_generationno");
sbSQL.AppendLine(")");
sbSQL.AppendLine("RETURNING staffcode, kananame, kanjiname, password, generationno");
npgDB.Command = sbSQL.ToString();
//npgDB.SetParams(":p_staffcode", staff.staffCode);
npgDB.SetParams(":p_kananame", staff.kanaName);
npgDB.SetParams(":p_kanjiname", staff.kanjiName);
npgDB.SetParams(":p_generationno", staff.generationno);
npgDB.SetParams(":p_password", staff.password);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
result.staff = new Staff(rec);
result.success = true;
}
}
}
}
catch (Exception ex) { }
}
return(result);
}
//////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// ｺﾝｽﾄﾗｸﾀ
/// </summary>
/// <param name="rec"></param>
public App(NpgsqlDataReader rec, bool isGetFileInfo = false)
{
this.appId = NpgDB.getString(rec, "appid");
this.name = NpgDB.getString(rec, "name");
this.description = NpgDB.getString(rec, "description");
this.icon = NpgDB.getString(rec, "icon");
if (isGetFileInfo)
{
string verCdStr = NpgDB.getLongString(rec, "vercd");
try
{
this.verCd = int.Parse(verCdStr);
}
catch (Exception ex)
{
}
this.verNm = NpgDB.getString(rec, "vernm");
this.fileNm = NpgDB.getString(rec, "filenm");
}
}
public AppJSON Create(string staffCode, App app)
{
AppJSON result = new AppJSON();
if (app != null)
{
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("INSERT INTO mapp");
sbSQL.AppendLine("(");
sbSQL.AppendLine("appid, name, description, icon, upopr");
sbSQL.AppendLine(")");
sbSQL.AppendLine("VALUES");
sbSQL.AppendLine("(");
sbSQL.AppendLine(":p_appid, :p_name, :p_description, :p_icon, :p_upopr");
sbSQL.AppendLine(")");
sbSQL.AppendLine("RETURNING appid, name, description, icon, upopr");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_appid", app.appId);
npgDB.SetParams(":p_name", app.name);
npgDB.SetParams(":p_description", app.description);
npgDB.SetParams(":p_icon", app.icon);
npgDB.SetParams(":p_upopr", staffCode);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
result.app = new App(rec);
result.success = true;
}
}
}
}
catch (Exception ex) { }
}
return(result);
}
public InstallFileJSON UpdateFile(string staffCode, InstallFile installFile)
{
InstallFileJSON result = new InstallFileJSON();
if (installFile != null)
{
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("INSERT INTO dinstallfile");
sbSQL.AppendLine("(appid, vercd, vernm, filenm, upopr)");
sbSQL.AppendLine("VALUES (");
sbSQL.AppendLine(":p_appid, :p_vercd, :p_vernm, :p_filenm, :p_upopr");
sbSQL.AppendLine(")");
sbSQL.AppendLine("RETURNING appid, vercd, vernm, filenm, upopr");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_appid", installFile.appId);
npgDB.SetParamsLongString(":p_vercd", installFile.verCd + "");
npgDB.SetParams(":p_vernm", installFile.verNm);
npgDB.SetParams(":p_filenm", installFile.fileNm);
npgDB.SetParams(":p_upopr", staffCode);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
result.fileContent = new InstallFile(rec);
result.success = true;
}
}
}
}
catch (Exception ex) { }
}
return(result);
}
public AppJSON Delete(string staffCode, string appId)
{
AppJSON result = new AppJSON();
StringBuilder sbSQL = new StringBuilder();
try
{
using (NpgDB npgDB = Connection.DBConnect())
{
sbSQL.AppendLine("UPDATE mapp");
sbSQL.AppendLine("SET endstmp = CURRENT_TIMESTAMP,");
sbSQL.AppendLine("upopr = :p_staff_code,");
sbSQL.AppendLine("upstmp = CURRENT_TIMESTAMP");
sbSQL.AppendLine("WHERE appid = :p_appid");
sbSQL.AppendLine("RETURNING appid");
npgDB.Command = sbSQL.ToString();
npgDB.SetParams(":p_staff_code", staffCode);
npgDB.SetParams(":p_appid", appId);
using (NpgsqlDataReader rec = npgDB.Query())
{
if (rec.Read())
{
string deletedAppId = NpgDB.getString(rec, "appid");
result.app = new App
{
appId = deletedAppId
};
result.success = true;
}
}
}
}
catch (Exception ex) { }
return(result);
}
