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