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 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 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 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 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 Lock Select(long Id)
{
var sql = SqlFacade.SqlSelect(TableName, "*", "id=:Id");
return(SqlFacade.Connection.Query <Lock>(sql, new { Id }).FirstOrDefault());
}
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 UnitMeasure Select(long Id)
{
var sql = SqlFacade.SqlSelect(TableName, "*", "id = :id");
return(SqlFacade.Connection.Query <UnitMeasure>(sql, new { Id }).FirstOrDefault());
}