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