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 <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); }
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); }
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 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 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); }
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> /// <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); } }
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); }
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 long PageCountSearch(string staffCode) { 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("WHERE staffcode like :p_staffcode OR kananame like :p_staffcode OR kanjiname like :p_staffcode"); // sbSQL.AppendLine("ORDER by mstaff.staffcode"); npgDB.Command = sbSQL.ToString(); npgDB.SetParams("p_staffcode", "%" + staffCode + "%"); 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 / 5); return(count); }