public static DataTable GetDataTable(string filter = "", string status = "") { var sql = SqlFacade.SqlSelect(TableName, "id, branch_code, code, description, type, name, phone, fax, email, address", "1 = 1"); if (status.Length == 0) { sql += " and status <> '" + Type.RecordStatus_Deleted + "'"; } else { sql += " and status = '" + status + "'"; } if (filter.Length > 0) { sql += " and (" + SqlFacade.SqlILike("code, description, phone, fax, email, address, note") + ")"; } sql += "\norder by code\nlimit " + ConfigFacade.sy_select_limit; var cmd = new NpgsqlCommand(sql); if (filter.Length > 0) { cmd.Parameters.AddWithValue(":filter", "%" + filter + "%"); } return(SqlFacade.GetDataTable(cmd)); }
public static DataTable GetDataTable(string filter = "", string status = "") { var sql = "select id, code, description, name, phone, fax, email, address from " + TableName + "\nwhere 1 = 1"; if (status.Length == 0) { sql += " and status <> '" + Type.RecordStatus_Deleted + "'"; } else { sql += " and status = '" + status + "'"; } if (filter.Length > 0) { sql += " and (code ilike :filter or description ilike :filter or phone ilike :filter or fax ilike :filter or email ilike :filter or address ilike :filter or note ilike :filter)"; } sql += "\norder by code\nlimit " + ConfigFacade.sy_select_limit; var cmd = new NpgsqlCommand(sql); if (filter.Length > 0) { cmd.Parameters.AddWithValue(":filter", "%" + filter + "%"); } return(SqlFacade.GetDataTable(cmd)); }
private void Get() { var sWhere = "code ~* :code"; if (Username.Length > 0) { sWhere = "username ~* :username and " + sWhere; } var sql = SqlFacade.SqlSelect(TableName, "id, value as value", sWhere); Config result = null; if (Username.Length > 0) { result = SqlFacade.Connection.Query <Config>(sql, new { Username, Code }).FirstOrDefault(); } else { result = SqlFacade.Connection.Query <Config>(sql, new { Code }).FirstOrDefault(); } if (result == null) { Add(); } else { Id = result.Id; Value = result.Value; } }
public static void Export() { string sql = SqlFacade.SqlSelect(TableName, "id \"Id\", code \"Code\", description \"Description\", address \"Address\", name \"Contact Name\", phone \"Phone\", fax \"Fax\", " + "email \"Email\", note \"Note\", status \"Status\", insert_by \"Inserted By\", insert_at \"Inserted At\", change_by \"Changed By\", change_at \"Changed At\"", "status <> '" + Type.RecordStatus_Deleted + "'", "code"); SqlFacade.ExportToCSV(sql); }
//public static void SetStatus(long Id, string s) //{ // DateTime? ts = SqlFacade.GetCurrentTimeStamp(); // SqlFacade.Connection.UpdateOnly(new User { Status = s, ChangeBy = App.session.Username, ChangeAt = ts }, p => new { p.Status, p.ChangeBy, p.ChangeAt }, p => p.Id == Id); //} //public static bool IsLocked(long Id) //{ // return SqlFacade.Connection.Exists<User>("Id = @Id and Lock_By = @LockBy", new { Id = Id, LockBy = App.session.Username }); //} //public static LockInfo GetLockInfo(long Id) //{ // var m = Select(Id); // var l = new LockInfo(); // l.Id = Id; // l.LockBy = m.LockBy; // l.LockAt = m.LockAt; // return l; //} //public static void Lock(long Id) //{ // DateTime ts = SqlFacade.GetCurrentTimeStamp(); // SqlFacade.Connection.UpdateOnly(new User { LockBy = App.session.Username, LockAt = ts }, p => new { p.LockBy, p.LockAt }, p => p.Id == Id); //} //public static void ReleaseLock(long Id) //{ // if (Id == 0) return; // DateTime ts = SqlFacade.GetCurrentTimeStamp(); // SqlFacade.Connection.UpdateOnly(new User { LockBy = null }, p => p.LockBy, p => p.Id == Id); //} //public static bool IsExist(string Username, long Id = 0) //{ // return SqlFacade.Connection.Exists<User>("Id <> @Id and Username = @Username", new { Id = Id, Username = Username }); //} public static void UpdatePwd(User m) { string sqlPwd = "select crypt('" + m.Pwd + "', gen_salt('bf'))"; m.Pwd = SqlFacade.Connection.ExecuteScalar <string>(sqlPwd); var sql = SqlFacade.SqlUpdate(TableName, "Pwd = @Pwd", "Id = @Id"); SqlFacade.Connection.Execute(sql, m); }
public static long Save(Lock m) { m.Lock_By = App.session.Username; m.Branch_Code = App.session.Branch_Code; m.Machine_Name = App.session.Machine_Name; m.Machine_Username = App.session.Machine_User_Name; var sql = SqlFacade.SqlInsert(TableName, "table_name, branch_code, lock_id, ref, lock_by, machine_name, machine_username", "", true); m.Id = SqlFacade.Connection.ExecuteScalar <long>(sql, m); return(m.Id); }
public static long Save(Session m) { long seq = 0; // New inserted sequence if (m.Id == 0) { var sql = SqlFacade.SqlInsert(TableName, "username, branch_code, version, machine_name, machine_user_name", "", true); seq = SqlFacade.Connection.ExecuteScalar <long>(sql, m); } return(seq); }
public static string GetMessage(string code) { var language = ConfigFacade.sy_language; var sql = SqlFacade.SqlSelect(TableName, "value", "code = lower(:code) and language = :language"); var message = SqlFacade.Connection.ExecuteScalar <string>(sql, new { code, language }); if (message == null) { ErrorLogFacade.Log("Message: code=" + code + " not exist"); } return(message); }
public static bool Init() { SetVersion(); // Splash screen fSplash = new frmSplash(); fSplash.SetAppName(" v " + SYS.App.version); fSplash.Show(); fSplash.ShowMsg("Initializing the application..."); fSplash.TopMost = true; // Database connection string from setting.ini try { fSplash.ShowMsg("Loading settings..."); LoadSettings(); fSplash.ShowMsg("Connecting to the database ..."); SqlFacade.OpenConnection(); // Database connection: open/test fSplash.ShowMsg("Ensuring database setup ..."); SqlFacade.EnsureDBSetup(); // Create tables if not exist } catch (Exception ex) { fSplash.ShowError(ex.Message); fSplash.Visible = false; fSplash.ShowDialog(); ErrorLogFacade.LogToFile(ex); return(false); } //Session session.Username = "******"; //todo: logged in user (to be removed) session.Branch_Code = "000"; session.Machine_Name = Environment.MachineName; session.Machine_User_Name = Environment.UserName; session.Version = version; LabelFacade.LoadSystemLabel(); MessageFacade.LoadSystemMessage(); // Log ErrorLogFacade.logFile.FileName = Path.Combine(Application.StartupPath, "Error.log"); AccessLog.FileName = Path.Combine(Application.StartupPath, "Access.log"); AccessLog.Write(DateTime.Now.ToString("yyy-MM-dd ddd hh:mm:ss tt") + " Application started. Process Id: " + ProcessID + ", Machine: " + session.Machine_Name + ", machine's username: "******", version: " + session.Version); session.Id = SessionFacade.Save(session); SessionLogFacade.Log(Type.Priority_Information, "Application", Type.Log_Launch, "Application started"); return(true); }
public static Lock Select(string table, long lockId) { var sql = SqlFacade.SqlSelect(TableName, "*", "table_name = :Table and lock_id = :LockId", "lock_at desc", 1); var m = SqlFacade.Connection.Query <Lock>(sql, new { table, lockId }).FirstOrDefault(); if (m == null) { m = new Lock(); // Locked = false } else { m.Locked = true; } return(m); }
public static bool Exists(string Code, long Id = 0) { var sql = SqlFacade.SqlExists(TableName, "id <> :id and status <> :status and code = :code"); var bExists = false; try { bExists = SqlFacade.Connection.ExecuteScalar <bool>(sql, new { Id, Status = Type.RecordStatus_Deleted, Code }); } catch (Exception ex) { MessageFacade.Show(MessageFacade.error_query + "\r\n" + ex.Message, LabelFacade.sy_location, MessageBoxButtons.OK, MessageBoxIcon.Error); ErrorLogFacade.Log(ex, "Exists"); } return(bExists); }
public void Save() { if (!Changed) { return; } var sql = SqlFacade.SqlUpdate(TableName, "value", "", "id = :id"); try { SqlFacade.Connection.Execute(sql, new { Value, Id }); } catch (Exception ex) { ErrorLogFacade.LogToFile(ex, "sql='" + sql + "')"); } }
public static long Save(UnitMeasure m) { string sql = ""; if (m.Id == 0) { m.Insert_By = App.session.Username; sql = SqlFacade.SqlInsert(TableName, "code, default_factor, description, insert_by", "", true); m.Id = SqlFacade.Connection.ExecuteScalar <long>(sql, m); } else { m.Change_By = App.session.Username; sql = SqlFacade.SqlUpdate(TableName, "code, description, default_factor, note, change_by, change_at, change_no", "change_at = now(), change_no = change_no + 1", "id = :id"); SqlFacade.Connection.Execute(sql, m); ReleaseLock(m.Id); // Unlock } return(m.Id); }
public static long Save(Account m) { string sql = ""; m.Branch_Code = App.session.Branch_Code; if (m.Id == 0) { m.Insert_By = App.session.Username; sql = SqlFacade.SqlInsert(TableName, "branch_code, code, description, type, address, name, phone, fax, email, note, insert_by", "", true); m.Id = SqlFacade.Connection.ExecuteScalar <long>(sql, m); } else { m.Change_By = App.session.Username; sql = SqlFacade.SqlUpdate(TableName, "branch_code, code, description, type, address, name, phone, fax, email, note, change_by, change_at, change_no", "change_at = now(), change_no = change_no + 1", "id = :id"); SqlFacade.Connection.Execute(sql, m); ReleaseLock(m.Id); // Unlock } return(m.Id); }
public static void Delete(string table, long lockId) { var sql = SqlFacade.SqlDelete(TableName, "table_name = :table and lock_id = :LockId"); SqlFacade.Connection.Execute(sql, new { table, lockId }); }
public static Lock Select(long Id) { var sql = SqlFacade.SqlSelect(TableName, "*", "id=:Id"); return(SqlFacade.Connection.Query <Lock>(sql, new { Id }).FirstOrDefault()); }
public static UnitMeasure Select(long Id) { var sql = SqlFacade.SqlSelect(TableName, "*", "id = :id"); return(SqlFacade.Connection.Query <UnitMeasure>(sql, new { Id }).FirstOrDefault()); }
private void Add() { var sql = SqlFacade.SqlInsert(TableName, "username, code, value, note", "", true); Id = SqlFacade.Connection.ExecuteScalar <long>(sql, new { Username, Code, Value, Note }); }
public static void Export() { string sql = SqlFacade.SqlSelect(TableName, ConfigFacade.sy_sql_export_unit_measure, "status <> '" + Type.RecordStatus_Deleted + "'", "code"); SqlFacade.ExportToCSV(sql); }
public static void SetStatus(long Id, string status) { var sql = SqlFacade.SqlUpdate(TableName, "status, change_by, change_at", "change_at = now()", "id = :id"); SqlFacade.Connection.Execute(sql, new { status, Change_By = App.session.Username, Id }); }