public int?GetTaxTypeFromTaxCodeTypeID(string TaxCodeTypeID)
{
int?ImportTaxType = 0;
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("TaxCodeTypes", "ImportTaxType")
.From("TaxCodeTypes")
.Criteria
.IsEqual("TaxCodeTypes", "TaxCodeTypeID", TaxCodeTypeID);
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
if (_reader.Read())
{
ImportTaxType = GetInt32(_reader, ("ImportTaxType"));
}
_reader.Close();
_cmd.Dispose();
return(ImportTaxType);
}
public DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("TaxCodes", "TaxCode")
.SelectColumn("TaxCodes", "TaxCodeDescription")
.SelectColumn("TaxCodes", "TaxPercentageRate")
.From("TaxCodes");
DataTable table = new DataTable();
table.Columns.Add("Tax Code");
table.Columns.Add("Description");
table.Columns.Add("Percentage Rate");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["Tax Code"] = GetString(_reader, "TaxCode");
row["Description"] = GetString(_reader, "TaxCodeDescription");
row["Percentage Rate"] = GetDouble(_reader, "TaxPercentageRate");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public virtual DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Gender", "GenderID")
.SelectColumn("Gender", "Description")
.From("Gender");
DataTable table = new DataTable();
table.Columns.Add("GenderID");
table.Columns.Add("Description");
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["GenderID"] = GetString(_reader, "GenderID");
row["Description"] = GetString(_reader, "Description");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("GeneralJournals", "GeneralJournalNumber", "GeneralJournalNumber")
.From("GeneralJournals");
DataTable table = new DataTable();
table.Columns.Add("GeneralJournalNumber");
DbCommand _cmd = CreateDbCommand(GetQuery_SelectAll());
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["GeneralJournalNumber"] = GetInt32(_reader, "GeneralJournalNumber");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Currency", "CurrencyID", "ID")
.SelectColumn("Currency", "CurrencyCode", "Code")
.SelectColumn("Currency", "ExchangeRate", "ExchangeRate")
.From("Currency");
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Code");
table.Columns.Add("Exchange Rate");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Code"] = GetString(_reader, "Code");
row["Exchange Rate"] = GetDouble(_reader, "ExchangeRate");
table.Rows.Add(row);
}
return(table);
}
public DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("AuthItem", "ItemID", "ID")
.SelectColumn("AuthItem", "Description", "Description")
.From("AuthItem");
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Description");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Description"] = GetString(_reader, "Description");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public virtual DataTable Table(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Locations", "LocationID", "ID")
.SelectColumn("Locations", "LocationName", "Name")
.SelectColumn("Locations", "IsInactive", "Inactive")
.From("Locations")
.Criteria.And(criteria);
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Name");
table.Columns.Add("Active");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Name"] = GetString(_reader, "Name");
row["Active"] = (GetString(_reader, "Inactive") == "N");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public List <CustomerRow> ListRow()
{
List <CustomerRow> customers = new List <CustomerRow>();
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Customers", "CustomerID")
.SelectColumn("Customers", "Name")
.SelectColumn("Customers", "TermsID")
.SelectColumn("Customers", "SaleCommentID")
.From("Customers");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
CustomerRow _obj = new CustomerRow();
_obj.CustomerID = GetInt32(_reader, "CustomerID");
_obj.Name = GetString(_reader, "Name");
_obj.TermsID = GetInt32(_reader, "TermsID");
_obj.SaleCommentID = GetInt32(_reader, "SaleCommentID");
customers.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(customers);
}
public virtual Account GetLinkedAccount(string linkAccount)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("LinkedAccounts", string.Format("{0}ID", linkAccount))
.From("LinkedAccounts");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
int? AccountID = null;
if (_reader.Read())
{
AccountID = GetInt32(_reader, string.Format("{0}ID", linkAccount));
}
return(_FindByIntId(AccountID));
}
protected override AuthUser _FindByTextId(string username)
{
if (!HasUser(username))
{
return(null);
}
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("AuthUser", "AuthUserID")
.From("AuthUser")
.Criteria
.IsEqual("AuthUser", "AuthUsername", username);
int UserID = ExecuteScalarInt(clause);
return(FindByUserID(UserID));
}
public DataTable Table(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("RecurringPurchases", "RecurringPurchaseID", "ID")
.SelectColumn("Suppliers", "Name", "SupplierName")
.SelectColumn("RecurringPurchases", "PurchaseDate", "PurchaseDate")
.From("RecurringPurchases")
.From("Suppliers")
.Join("RecurringPurchases", "CardRecordID", "Suppliers", "SupplierID")
.Criteria.And(criteria);
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Supplier ID");
table.Columns.Add("Purchase Date");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Supplier ID"] = GetString(_reader, "SupplierName");
DateTime?purchase_date = GetDateTime(_reader, "PurchaseDate");
if (purchase_date.HasValue)
{
row["Purchase Date"] = purchase_date.Value.ToString("yyyy-MM-dd");
}
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public List <EmployeeRow> ListRow()
{
List <EmployeeRow> _employees = new List <EmployeeRow>();
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Employees", "CardRecordID")
.SelectColumn("Employees", "CardIdentification")
.SelectColumn("Employees", "FirstName")
.SelectColumn("Employees", "LastName")
.SelectColumn("Employees", "IsInactive")
.From("Employees");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
EmployeeRow _obj = new EmployeeRow();
_obj.CardIdentification = GetString(_reader, "CardIdentification");
_obj.EmployeeID = GetInt32(_reader, "CardRecordID");
_obj.FirstName = GetString(_reader, "FirstName");
_obj.LastName = GetString(_reader, "LastName");
string IsInactive = GetString(_reader, "IsInactive");
if (IsInactive.Equals("Y"))
{
_obj.IsInactive = true;
}
_employees.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(_employees);
}
public object Table(Card card, DateTime start_time, DateTime end_time)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("ContactLog", "Date")
.SelectColumn("ContactLog", "ElapsedTime")
.SelectColumn("ContactLog", "RecontactDate")
.SelectColumn("ContactLog", "Notes")
.From("ContactLog");
DataTable table = new DataTable();
table.Columns.Add("Date");
table.Columns.Add("Time");
table.Columns.Add("Recontact");
table.Columns.Add("Notes");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["Date"] = GetInt32(_reader, "Date");
row["Time"] = GetString(_reader, "ElapsedTime");
row["Recontact"] = GetString(_reader, "RecontactDate");
row["Notes"] = GetString(_reader, "Notes");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public string GetSalesTaxCalcBasis(string SalesTaxCalcBasisID)
{
string SalesTaxCalcBasis = "";
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("PriceLevels", "Description")
.From("PriceLevels")
.Criteria
.IsEqual("PriceLevels", "PriceLevelID", SalesTaxCalcBasisID);
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
if (_reader.Read())
{
SalesTaxCalcBasis = GetString(_reader, ("Description"));
}
_reader.Close();
_cmd.Dispose();
return(SalesTaxCalcBasis);
}
public List <Account> List(string AccountNumber)
{
DbSelectStatement clause_select_account_id = DbMgr.CreateSelectClause();
clause_select_account_id
.SelectColumn("Accounts", "AccountID")
.From("Accounts")
.Criteria.IsEqual("Accounts", "AccountNumber", AccountNumber);
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Accounts")
.OrderBy("Accounts", "AccountNumber")
.Criteria.IsEqual("Accounts", "ParentAccountID", clause_select_account_id);
List <Account> result = new List <Account>();
DbCommand cmd = CreateDbCommand(clause);
DbDataReader _reader = cmd.ExecuteReader();
while (_reader.Read())
{
Account _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
result.Add(_obj);
}
_reader.Close();
cmd.Dispose();
return(result);
}
private string GetADONETError(OdbcException ex)
{
//string sQuery;
long lNativeError = 0;
string sErrorDesc = "";
if (ex.Errors.Count == 0)
{
return(sErrorDesc);
}
foreach (OdbcError oErr in ex.Errors)
{
sErrorDesc = oErr.Message;
if (sErrorDesc.EndsWith("Transaction rollback"))
{
lNativeError = oErr.NativeError;
break;
}
}
DbSelectStatement clause = CreateSelectClause();
if ((lNativeError >= 10000) && (lNativeError < 20000)) // 'Error
{
sErrorDesc = "Error: " + lNativeError.ToString() + " ";
lNativeError = lNativeError - 10000;
clause
.SelectColumn("ImportErrors", "Description")
.From("ImportErrors")
.Criteria
.IsEqual("ImportErrors", "ImportErrorID", (int)lNativeError);
//sQuery = "Select Description from ImportErrors where ImportErrorID = " + lNativeError.ToString();
}
else if ((lNativeError >= 1) && (lNativeError < 10000)) // 'Warning
{
clause
.SelectColumn("ImportWarnings", "Description")
.From("ImportWarnings")
.Criteria
.IsEqual("ImportWarnings", "ImportWarningID", (int)lNativeError);
//sQuery = "Select Description from ImportWarnings where ImportWarningID = " + lNativeError.ToString();
sErrorDesc = "Warning: " + lNativeError.ToString() + " ";
}
else if (lNativeError >= 20000)
{
clause
.SelectColumn("InternalODBCErrors", "Description")
.From("InternalODBCErrors")
.Criteria
.IsEqual("InternalODBCErrors", "NativeErrorNumber", (int)lNativeError);
//sQuery = "Select Description from InternalODBCErrors where NativeErrorNumber = " + lNativeError.ToString();
sErrorDesc = lNativeError.ToString() + " ";
}
else
{
// return first error in the list
foreach (OdbcError oErr in ex.Errors)
{
sErrorDesc = oErr.Message;
break;
}
return(sErrorDesc);
}
DbDataAdapter adapter = CreateDbDataAdapter(clause);
DataSet ADORecordset = new DataSet();
adapter.Fill(ADORecordset, "ImportWarnings");
foreach (DataRow drow in ADORecordset.Tables["ImportWarnings"].Rows)
{
sErrorDesc += drow["Description"];
}
return(sErrorDesc);
}
protected override object GetDbProperty(AuthRole _obj, string property_name)
{
if (property_name.Equals("Children"))
{
List <int> child_role_ids = new List <int>();
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("AuthRoleRole", "ChildRoleID")
.From("AuthRoleRole")
.Criteria
.IsEqual("AuthRoleRole", "ParentRoleID", _obj.RoleID);
DbCommand cmd = DbMgr.CreateDbCommand(clause);
DbDataReader _reader = cmd.ExecuteReader();
while (_reader.Read())
{
int?child_role_id = GetInt32(_reader, "ChildRoleID");
if (child_role_id != null)
{
child_role_ids.Add(child_role_id.Value);
}
}
_reader.Close();
cmd.Dispose();
List <AuthRole> child_roles = new List <AuthRole>();
foreach (int child_role_id in child_role_ids)
{
AuthRole child_role = RepositoryMgr.AuthRoleMgr.FindByRoleID(child_role_id);
if (child_role != null)
{
child_roles.Add(child_role);
}
}
return(child_roles);
}
else if (property_name.Equals("Items"))
{
List <int> item_ids = new List <int>();
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("AuthRoleItem", "ItemID")
.From("AuthRoleItem")
.Criteria
.IsEqual("AuthRoleItem", "RoleID", _obj.RoleID);
DbCommand cmd = DbMgr.CreateDbCommand(clause);
DbDataReader _reader = cmd.ExecuteReader();
while (_reader.Read())
{
int?item_id = GetInt32(_reader, "ItemID");
if (item_id != null)
{
item_ids.Add(item_id.Value);
}
}
_reader.Close();
cmd.Dispose();
List <AuthItem> items = new List <AuthItem>();
foreach (int item_id in item_ids)
{
AuthItem item = RepositoryMgr.AuthItemMgr.FindById(item_id);
if (item != null)
{
items.Add(item);
}
}
return(items);
}
return(null);
}
