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