private void CreateInitialAccountCodes(IDbConnection connection)
{
string _path = Application.StartupPath + "\\Xml\\defaultaccounts.xml";
DataTable _defaultaccounts = SCMS.XmlToTable(_path);
if (_defaultaccounts != null)
{
Cache.SyncTable(connection, "accounts");
DataTable _accounts = Cache.GetCachedTable("accounts");
if (_accounts != null)
{
string _query = "";
for (int i = 0; i <= (_defaultaccounts.Rows.Count - 1); i++)
{
DataRow _row = _defaultaccounts.Rows[i];
DataRow[] _rows = _accounts.Select("[AccountCode] = " + _row["AccountCode"].ToString());
if (_rows.Length <= 0)
{
_query += "INSERT INTO `accounts`\n" +
"(`AccountCode`, `AccountName`, `AccountCategory`, `Active`, `DateCreated`)\n" +
"VALUES\n" +
"(" + _row["AccountCode"].ToString() + ", '" + _row["AccountName"].ToString().ToSqlValidString() + "', '" + _row["AccountCategory"].ToString().ToSqlValidString() + "', 1, NOW());\n";
object[] _values = new object[_accounts.Columns.Count];
DataColumnCollection _cols = _accounts.Columns;
_values[_cols["AccountCode"].Ordinal] = _row["AccountCode"];
_values[_cols["AccountName"].Ordinal] = _row["AccountName"];
_values[_cols["AccountCategory"].Ordinal] = _row["AccountCategory"];
_values[_cols["Active"].Ordinal] = 1;
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_accounts.Rows.Add(_values);
}
}
if (!String.IsNullOrEmpty(_query.RLTrim()))
{
QueResult _result = Que.Execute(connection, _query);
if (String.IsNullOrEmpty(_query.RLTrim()))
{
_accounts.AcceptChanges(); Cache.Save();
}
else
{
_accounts.RejectChanges();
}
_result.Dispose(); _result = null; Materia.RefreshAndManageCurrentProcess();
}
}
_defaultaccounts.Dispose(); _defaultaccounts = null;
Materia.RefreshAndManageCurrentProcess();
}
}
/// <summary>
/// Logs the current system user's action into the database and locally.
/// </summary>
/// <param name="action"></param>
/// <param name="description"></param>
/// <param name="refno"></param>
/// <param name="amount"></param>
/// <param name="currency"></param>
/// <param name="amountusd"></param>
public void LogAction(UserAction action, string description, string refno, decimal amount, string currency, decimal amountusd)
{
if (IsSignedIn)
{
string _path = Application.StartupPath + "\\Xml\\locallogs.xml";
DataTable _localtable = SCMS.XmlToTable(_path);
if (_localtable != null)
{
object[] _values = new object[_localtable.Columns.Count];
DataColumnCollection _cols = _localtable.Columns;
_values[_cols["Username"].Ordinal] = Username;
_values[_cols["DateAndTime"].Ordinal] = DateTime.Now;
_values[_cols["Action"].Ordinal] = (int)action;
_values[_cols["ReferenceNo"].Ordinal] = refno;
_values[_cols["Description"].Ordinal] = description;
_values[_cols["Amount"].Ordinal] = amount;
_values[_cols["Currency"].Ordinal] = currency;
_values[_cols["AmountUSD"].Ordinal] = amountusd;
_localtable.Rows.Add(_values);
try
{
_localtable.AcceptChanges();
_localtable.WriteXml(_path, XmlWriteMode.WriteSchema);
}
catch (Exception ex)
{ SCMS.LogError("UserLogs", ex); }
_localtable.Dispose(); _localtable = null;
Materia.RefreshAndManageCurrentProcess();
}
string _query = "INSERT INTO `userlogs`\n" +
"(`Username`, `Action`, `ReferenceNo`, `Description`, `Amount`, `Currency`, `AmountUSD`, `ComputerName`, `IPAddress`)\n" +
"VALUES\n" +
"('" + Username.ToSqlValidString() + "', " + ((int)action).ToString() + ", '" + refno.ToSqlValidString() + "', '" + description.ToSqlValidString() + "', " + amount.ToSqlValidString() + ", '" + currency.ToSqlValidString() + "', " + amountusd.ToSqlValidString() + ", '" + Environment.MachineName.ToSqlValidString() + "', '" + Materia.GetCurrentIPAddress().ToSqlValidString() + "');";
QueResult _result = Que.Execute(SCMS.Connection, _query);
if (String.IsNullOrEmpty(_result.Error.RLTrim()))
{
Cache.SyncTable(SCMS.Connection, "userlogs");
}
else
{
SCMS.LogError("UserLog", new Exception(_result.Error));
}
_result.Dispose(); Materia.RefreshAndManageCurrentProcess();
}
}
private void CreateInitialCompanies(IDbConnection connection)
{
string _path = Application.StartupPath + "\\Xml\\defaultcompanies.xml";
DataTable _table = SCMS.XmlToTable(_path);
if (_table != null)
{
Cache.SyncTable(connection, "companies");
DataTable _cachedtable = Cache.GetCachedTable("companies");
if (_cachedtable != null)
{
string _query = "";
for (int i = 0; i <= (_table.Rows.Count - 1); i++)
{
DataRow _row = _table.Rows[i];
DataRow[] _rows = _cachedtable.Select("[Company] LIKE '" + _row["Company"].ToString().ToSqlValidString(true) + "'");
if (_rows.Length <= 0)
{
_query += "INSERT INTO `companies`\n" +
"(`Company`, `Name`, `DateCreated`)\n" +
"VALUES\n" +
"('" + _row["Company"].ToString().ToSqlValidString() + "', '" + _row["Name"].ToString().ToSqlValidString() + "', NOW());\n";
DataColumnCollection _cols = _cachedtable.Columns;
object[] _values = new object[_cols.Count];
_values[_cols["Company"].Ordinal] = _row["Company"];
_values[_cols["Name"].Ordinal] = _row["Name"];
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_cachedtable.Rows.Add(_values);
}
}
if (!String.IsNullOrEmpty(_query.RLTrim()))
{
QueResult _result = Que.Execute(connection, _query);
if (String.IsNullOrEmpty(_result.Error.RLTrim()))
{
_cachedtable.AcceptChanges(); Cache.Save();
}
_result.Dispose(); _result = null; Materia.RefreshAndManageCurrentProcess();
}
}
_table.Dispose(); _table = null;
Materia.RefreshAndManageCurrentProcess();
}
}
private DataTable GetDataSource()
{
DataTable _datasource = null;
Cache.SyncTable(SCMS.Connection, "scripts");
DataTable _scripts = Cache.GetCachedTable("scripts");
if (_scripts != null)
{
var _query = from _scr in _scripts.AsEnumerable()
select new
{
ReferenceNo = _scr.Field <string>("ReferenceNo"),
SystemVersion = _scr.Field <string>("SystemVersion"),
Title = _scr.Field <string>("Title"),
Author = _scr.Field <string>("Author"),
Executed = _scr.Field <Int16>("Executed"),
AutoScript = _scr.Field <Int16>("AutoScript"),
DateCreated = _scr.Field <DateTime>("DateCreated"),
LastModified = _scr.Field <DateTime>("LastModified")
};
_datasource = new DataTable();
_datasource.TableName = "scripts";
DataColumn _pk = _datasource.Columns.Add("ReferenceNo", typeof(string));
_datasource.Columns.Add("SystemVersion", typeof(string));
_datasource.Columns.Add("Title", typeof(string));
_datasource.Columns.Add("Author", typeof(string));
_datasource.Columns.Add("Executed", typeof(bool));
_datasource.Columns.Add("AutoScript", typeof(bool));
_datasource.Columns.Add("DateCreated", typeof(DateTime));
_datasource.Columns.Add("LastModified", typeof(DateTime));
foreach (var _row in _query)
{
_datasource.Rows.Add(new object[] {
_row.ReferenceNo, _row.SystemVersion, _row.Title,
_row.Author, VisualBasic.CBool(_row.Executed), VisualBasic.CBool(_row.AutoScript),
_row.DateCreated, _row.LastModified
});
}
_datasource.AcceptChanges();
}
return(_datasource);
}
/// <summary>
/// Gathers a user information based on the supplied credentials.
/// </summary>
/// <returns></returns>
public bool GetInformation()
{
bool _withinfo = false; ClearInformation();
Cache.SyncTable(SCMS.Connection, "users");
DataTable _table = Cache.GetCachedTable("users");
if (_table != null)
{
_table.CaseSensitive = true;
DataRow[] _rows = _table.Select("[Username] = '" + _username.ToSqlValidString(true) + "' AND [Password] = '" + _password.Encrypt(SCMS.EncryptionKey).ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
if (!Materia.IsNullOrNothing(_row["Department"]))
{
_department = _row["Department"].ToString();
}
if (!Materia.IsNullOrNothing(_row["FirstName"]))
{
_firstname = _row["FirstName"].ToString();
}
if (!Materia.IsNullOrNothing(_row["LastName"]))
{
_lastname = _row["LastName"].ToString();
}
if (!Materia.IsNullOrNothing(_row["MiddleName"]))
{
_middlename = _row["MiddleName"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Position"]))
{
_position = _row["Position"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Active"]))
{
_isactive = VisualBasic.CBool(_row["Active"]);
}
if (!Materia.IsNullOrNothing(_row["Role"]))
{
_issuperuser = VisualBasic.CBool(_row["Role"].ToString() == SuperUserRole);
}
if (!Materia.IsNullOrNothing(_row["AllCustomers"]))
{
_isallowedallcustomers = VisualBasic.CBool(_row["AllCustomers"]);
}
if (!Materia.IsNullOrNothing(_row["AllCompanies"]))
{
_isallowedallcompanies = VisualBasic.CBool(_row["AllCompanies"]);
}
if (!_isallowedallcompanies)
{
DataTable _companies = null;
_companies = _companies.LoadData(SCMS.Connection, "SELECT `Company` FROM `usercompanies` WHERE (`Username` LIKE '" + _username.ToSqlValidString() + "')");
if (_companies != null)
{
foreach (DataRow _company in _companies.Rows)
{
_accessiblecompanies.Add(new CompanyInfo(_company["Company"].ToString()));
}
_companies.Dispose(); _companies = null;
Materia.RefreshAndManageCurrentProcess();
}
}
_isvaliduser = true; _withinfo = true;
}
}
return(_withinfo);
}
private void CreateInitialCurrencies(IDbConnection connection)
{
string _path = Application.StartupPath + "\\Xml\\defaultcurrencies.xml";
DataTable _defaultcurrencies = SCMS.XmlToTable(_path);
if (_defaultcurrencies != null)
{
Cache.SyncTable(connection, "currencies");
DataTable _currencies = Cache.GetCachedTable("currencies");
if (_currencies != null)
{
string _query = "";
long _cashathandac = 54005; long _exrateac = 34625;
DataTable _accounts = Cache.GetCachedTable("accounts");
if (_accounts != null)
{
DataRow[] _rows = _accounts.Select("[AccountName] LIKE 'Cash at hand'");
if (_rows.Length > 0)
{
_cashathandac = (long)_rows[0]["AccountCode"];
}
_rows = _accounts.Select("[AccountName] LIKE 'Exchange rate differences'");
if (_rows.Length > 0)
{
_exrateac = (long)_rows[0]["AccountCode"];
}
}
for (int i = 0; i <= (_defaultcurrencies.Rows.Count - 1); i++)
{
DataRow _row = _defaultcurrencies.Rows[i];
DataRow[] _rows = _currencies.Select("[Currency] LIKE '" + _row["Currency"].ToString().ToSqlValidString(true) + "'");
if (_rows.Length <= 0)
{
_query += "INSERT INTO `currencies`\n" +
"(`Currency`, `Description`, `AccountCode`, `ExchangeRateAccountCode`, `DateCreated`)\n" +
"VALUES\n" +
"('" + _row["Currency"].ToString().ToSqlValidString() + "', '" + _row["Description"].ToString().ToSqlValidString() + "', " + _cashathandac.ToString() + ", " + _exrateac.ToString() + ", NOW());";
DataColumnCollection _cols = _currencies.Columns;
object[] _values = new object[_cols.Count];
_values[_cols["Currency"].Ordinal] = _row["Currency"];
_values[_cols["Description"].Ordinal] = _row["Description"];
_values[_cols["AccountCode"].Ordinal] = _cashathandac;
_values[_cols["ExchangeRateAccountCode"].Ordinal] = _exrateac;
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_currencies.Rows.Add(_values);
}
}
if (!String.IsNullOrEmpty(_query.RLTrim()))
{
QueResult _result = Que.Execute(connection, _query);
if (String.IsNullOrEmpty(_result.Error.RLTrim()))
{
_currencies.AcceptChanges(); Cache.Save();
}
else
{
_currencies.RejectChanges();
}
_result.Dispose(); _result = null; Materia.RefreshAndManageCurrentProcess();
}
}
_defaultcurrencies.Dispose(); _defaultcurrencies = null;
Materia.RefreshAndManageCurrentProcess();
}
}
/// <summary>
/// Retrieves the specified company's application settings and cofigurations.
/// </summary>
/// <param name="companycode"></param>
public static void Refresh(string companycode)
{
ClearInformation(); _company = new CompanyInfo(companycode);
Cache.SyncTable(SCMS.Connection, "settings");
DataTable _settings = Cache.GetCachedTable("settings");
if (_settings != null)
{
DataRow[] _rows = _settings.Select("[Company] LIKE '" + companycode.ToSqlValidString(true) + "'");
if (_rows.Length <= 0)
{
object[] _values = new object[_settings.Columns.Count];
DataColumnCollection _cols = _settings.Columns;
_values[_cols["Company"].Ordinal] = companycode;
_values[_cols["Address"].Ordinal] = "";
_values[_cols["Country"].Ordinal] = "";
_values[_cols["Phone"].Ordinal] = "";
_values[_cols["Mobile"].Ordinal] = "";
_values[_cols["Fax"].Ordinal] = "";
_values[_cols["Email"].Ordinal] = "";
_values[_cols["CompanyLogo"].Ordinal] = Properties.Resources.CSPTColored.ToByteArray();
_values[_cols["ReportLogo"].Ordinal] = Properties.Resources.CSPTBlackAndWhite.ToByteArray();
Cache.SyncTable(SCMS.Connection, "accounts");
DataTable _accounts = Cache.GetCachedTable("accounts");
if (_accounts != null)
{
long _accountcode = 0; string _accountname = "Raw materials & consumables - spare parts";
DataRow[] _selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["RawMaterialAccountCode"].Ordinal] = _accountcode;
}
_accountname = "Cash advances";
_selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["CashAdvanceAccountCode"].Ordinal] = _accountcode;
}
_accountname = "Parts consumption";
_selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["StockConsumptionAccountCode"].Ordinal] = _accountcode;
}
_accountname = "Stock adjustments";
_selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["StockAdjustmentAccountCode"].Ordinal] = _accountcode;
}
_accountname = "Unallocated payments / cheques";
_selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["UnallocatedPaymentAccountCode"].Ordinal] = _accountcode;
}
_accountname = "Result carried forward previous years";
_selrows = _accounts.Select("[AccountName] LIKE '" + _accountname.ToSqlValidString(true) + "'");
if (_selrows.Length > 0)
{
_accountcode = (long)_selrows[0]["AccountCode"];
_values[_cols["RollForwardAccountCode"].Ordinal] = _accountcode;
}
}
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_settings.Rows.Add(_values);
QueryGenerator _generator = new QueryGenerator(_settings);
_generator.ExcludedFields.Add("LastModified");
string _query = _generator.ToString();
if (!String.IsNullOrEmpty(_query.RLTrim()))
{
QueResult _result = Que.Execute(SCMS.Connection, _query);
if (String.IsNullOrEmpty(_result.Error.RLTrim()))
{
_settings.AcceptChanges();
}
_result.Dispose(); _result = null; Materia.RefreshAndManageCurrentProcess();
}
}
_rows = _settings.Select("[Company] LIKE '" + companycode.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
if (!Materia.IsNullOrNothing(_row["Address"]))
{
_address = _row["Address"].ToString();
}
if (VisualBasic.IsNumeric(_row["CashAdvanceAccountCode"]))
{
_cashadvanceaccountcode = (long)_row["CashAdvanceAccountCode"];
}
if (!Materia.IsNullOrNothing(_row["CompanyLogo"]))
{
try { _companylogo = ((byte[])_row["CompanyLogo"]).ToImage(); }
catch { }
}
if (!Materia.IsNullOrNothing(_row["Country"]))
{
_country = _row["Country"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Email"]))
{
_email = _row["Email"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Fax"]))
{
_fax = _row["Fax"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Mobile"]))
{
_mobile = _row["Mobile"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Phone"]))
{
_phone = _row["Phone"].ToString();
}
if (VisualBasic.IsNumeric(_row["RawMaterialAccountCode"]))
{
_rawmaterialaccountcode = (long)_row["RawMaterialAccountCode"];
}
if (!Materia.IsNullOrNothing(_row["ReportLogo"]))
{
try { _reportlogo = ((byte[])_row["ReportLogo"]).ToImage(); }
catch { }
}
if (VisualBasic.IsNumeric(_row["RollForwardAccountCode"]))
{
_rollforwardaccountcode = (long)_row["RollForwardAccountCode"];
}
if (VisualBasic.IsNumeric(_row["StockAdjustmentAccountCode"]))
{
_stockadjustmentaccountcode = (long)_row["StockAdjustmentAccountCode"];
}
if (VisualBasic.IsNumeric(_row["StockConsumptionAccountCode"]))
{
_stockconsumptionaccountcode = (long)_row["StockConsumptionAccountCode"];
}
if (VisualBasic.IsNumeric(_row["UnallocatedPaymentAccountCode"]))
{
_unallocatedpaymentaccountcode = (long)_row["UnallocatedPaymentAccountCode"];
}
}
}
}
private DataTable GetDataSource()
{
DataTable _datasource = null;
Cache.SyncTable(SCMS.Connection, "stockadjustments");
Cache.SyncTable(SCMS.Connection, "stockadjustmentdetails");
Cache.SyncTable(SCMS.Connection, "users");
Cache.SyncTable(SCMS.Connection, "parts");
Cache.SyncTable(SCMS.Connection, "measurements");
Cache.SyncTable(SCMS.Connection, "stockledger");
DataTable _adjustments = Cache.GetCachedTable("stockadjustments");
DataTable _users = Cache.GetCachedTable("users");
if (_adjustments != null &&
_users != null)
{
DataTable _approver = _users.Replicate();
DataTable _canceller = _users.Replicate();
DataTable _closing = _users.Replicate();
var _query = from _adj in _adjustments.AsEnumerable()
join _creator in _users.AsEnumerable() on _adj.Field <string>("Username") equals _creator.Field <string>("Username")
join _app in _approver.AsEnumerable() on _adj.Field <string>("ApprovedBy") equals _app.Field <string>("Username") into _a
join _cancel in _canceller.AsEnumerable() on _adj.Field <string>("CancelledBy") equals _cancel.Field <string>("Username") into _c
join _close in _closing.AsEnumerable() on _adj.Field <string>("ClosedBy") equals _close.Field <string>("Username") into _cl
where _adj.Field <string>("Company") == SCMS.CurrentCompany.Company
from _app in _a.DefaultIfEmpty(_approver.NewRow())
from _cancel in _c.DefaultIfEmpty(_canceller.NewRow())
from _close in _cl.DefaultIfEmpty(_closing.NewRow())
select new
{
ReferenceNo = _adj.Field <string>("ReferenceNo"),
Date = _adj.Field <DateTime>("Dated"),
Summary = _adj.Field <string>("Summary"),
Approval = (_adj.Field <Int16>("Approved") == 1 || _adj.Field <Int16>("Cancelled") == 1 ? (_adj.Field <Int16>("Approved") == 1 ? "Approved" : "") + (_adj.Field <Int16>("Cancelled") == 1 ? "Cancelled" : "") : "For Approval"),
Status = (_adj.Field <Int16>("Closed") == 1? "Closed" : "Open"),
DateCreated = _adj.Field <DateTime>("DateCreated"),
CreatedBy = _creator.Field <string>("FirstName") + " " + _creator.Field <string>("LastName"),
RefDate = (_adj.Field <Int16>("Approved") == 1 || _adj.Field <Int16>("Cancelled") == 1 ? (_adj.Field <Int16>("Approved") == 1 ? _adj.Field <DateTime>("DateApproved") : _adj.Field <DateTime>("DateCancelled")) : VisualBasic.CDate("1/1/1900")),
RefName = (_adj.Field <Int16>("Approved") == 1 || _adj.Field <Int16>("Cancelled") == 1 ? (_adj.Field <Int16>("Approved") == 1 ? _app.Field <string>("FirstName") + " " + _app.Field <string>("LastName") : _cancel.Field <string>("FirstName") + " " + _cancel.Field <string>("LastName")) : ""),
DateClosed = _adj.Field <DateTime>("DateClosed"),
ClosedBy = _close.Field <string>("FirstName") + " " + _close.Field <string>("LastName")
};
_datasource = new DataTable();
_datasource.TableName = "stockadjustments";
DataColumnCollection _cols = _datasource.Columns;
DataColumn _pk = _cols.Add("ReferenceNo", typeof(string));
_cols.Add("Dated", typeof(DateTime));
_cols.Add("Summary", typeof(string));
_cols.Add("Approval", typeof(string));
_cols.Add("Status", typeof(string));
_cols.Add("DateCreated", typeof(DateTime));
DataColumn _refdatecol = _cols.Add("RefDate", typeof(DateTime));
_cols.Add("RefName", typeof(string));
DataColumn _dateclosedcol = _cols.Add("DateClosed", typeof(DateTime));
_cols.Add("ClosedBy", typeof(string));
_datasource.Constraints.Add("PK", _pk, true);
_refdatecol.AllowDBNull = true; _dateclosedcol.AllowDBNull = true;
try
{
foreach (var _row in _query)
{
object _refdate = DBNull.Value;
if (_row.RefDate != VisualBasic.CDate("1/1/1900"))
{
_refdate = _row.RefDate;
}
object _dateclosed = DBNull.Value;
if (_row.DateClosed != VisualBasic.CDate("1/1/1900"))
{
_dateclosed = _row.DateClosed;
}
_datasource.Rows.Add(new object[] {
_row.ReferenceNo, _row.Date, _row.Summary,
_row.Approval, _row.Status, _row.DateCreated,
_refdate, _row.RefName, _dateclosed,
_row.ClosedBy
});
}
}
catch { }
_datasource.AcceptChanges();
}
return(_datasource);
}
private DataTable GetPartInventory()
{
DataTable _datasource = null;
Cache.SyncTable(SCMS.Connection, "parts");
Cache.SyncTable(SCMS.Connection, "partnames");
Cache.SyncTable(SCMS.Connection, "partcategories");
Cache.SyncTable(SCMS.Connection, "brands");
Cache.SyncTable(SCMS.Connection, "models");
Cache.SyncTable(SCMS.Connection, "measurements");
Cache.SyncTable(SCMS.Connection, "stockledger");
Cache.SyncTable(SCMS.Connection, "customers");
Cache.SyncTable(SCMS.Connection, "suppliers");
Cache.SyncTable(SCMS.Connection, "locations");
Cache.SyncTable(SCMS.Connection, "users");
DataTable _parts = Cache.GetCachedTable("parts");
DataTable _partnames = Cache.GetCachedTable("partnames");
DataTable _partcategories = Cache.GetCachedTable("partcategories");
DataTable _brands = Cache.GetCachedTable("brands");
DataTable _models = Cache.GetCachedTable("models");
DataTable _measurements = Cache.GetCachedTable("measurements");
DataTable _ledger = Cache.GetCachedTable("stockledger");
if (_parts != null &&
_partnames != null &&
_partcategories != null &&
_brands != null &&
_models != null &&
_measurements != null &&
_ledger != null)
{
string _path = Application.StartupPath + "\\Xml\\stocktypes.xml";
DataTable _stocktypes = SCMS.XmlToTable(_path);
if (_stocktypes != null)
{
var _query = from _part in _parts.AsEnumerable()
join _uom in _measurements.AsEnumerable() on _part.Field <string>("Unit") equals _uom.Field <string>("Unit")
join _name in _partnames.AsEnumerable() on _part.Field <string>("PartName") equals _name.Field <string>("PartName")
join _brand in _brands.AsEnumerable() on _part.Field <string>("Brand") equals _brand.Field <string>("Brand")
join _type in _stocktypes.AsEnumerable() on _part.Field <int>("StockType") equals _type.Field <int>("Id")
join _model in _models.AsEnumerable() on _part.Field <string>("ModelCode") equals _model.Field <string>("ModelCode") into _pm
join _stockledger in _ledger.AsEnumerable() on _part.Field <string>("PartCode") equals _stockledger.Field <string>("PartCode") into _sl
where _part.Field <string>("Company") == SCMS.CurrentCompany.Company
from _model in _pm.DefaultIfEmpty(_models.NewRow())
from _stockledger in _sl.DefaultIfEmpty(_ledger.NewRow())
group _stockledger by new
{
PartCode = _part.Field <string>("PartCode"),
PartNo = _part.Field <string>("PartNo"),
PartName = _part.Field <string>("PartName"),
Description = _part.Field <string>("Description"),
Brand = _part.Field <string>("Brand"),
Model = _model.Field <string>("Model"),
Category = _name.Field <string>("PartCategory"),
Unit = _part.Field <string>("Unit"),
ReorderPoint = _part.Field <int>("ReorderPoint"),
ReorderQty = _part.Field <int>("ReorderQty"),
Type = _type.Field <string>("StockType"),
Status = (_part.Field <Int16>("Active") == 1 ? "Active" : "Inactive")
} into _group
select new
{
PartCode = _group.Key.PartCode,
PartNo = _group.Key.PartNo,
PartName = _group.Key.PartName,
Description = _group.Key.Description,
Brand = _group.Key.Brand,
Model = _group.Key.Model,
Category = _group.Key.Category,
Unit = _group.Key.Unit,
Quantity = _group.Sum(_stockledger => (_stockledger.Field <int>("In") - _stockledger.Field <int>("Out"))),
Incoming = _group.Sum(_stockledger => _stockledger.Field <int>("Incoming")),
Outgoing = _group.Sum(_stockledger => _stockledger.Field <int>("Outgoing")),
Balance = _group.Sum(_stockledger => (_stockledger.Field <int>("In") - _stockledger.Field <int>("Out") + _stockledger.Field <int>("Incoming") - _stockledger.Field <int>("Outgoing"))),
ReorderPoint = _group.Key.ReorderPoint,
ReorderQty = _group.Key.ReorderQty,
Type = _group.Key.Type,
Status = _group.Key.Status,
LastPurchased = _group.Max(_stockledger => _stockledger.Field <DateTime>("PurchaseDate")),
UnitCost = _group.Sum(_stockledger => ((_stockledger.Field <int>("In") > 0 || _stockledger.Field <int>("Incoming") > 0 ? 1 : -1) * _stockledger.Field <decimal>("TotalCostUSD")))
};
_datasource = new DataTable();
_datasource.TableName = "parts";
DataColumn _dpk = _datasource.Columns.Add("PartCode", typeof(string));
_datasource.Columns.Add("PartNo", typeof(string));
_datasource.Columns.Add("PartName", typeof(string));
_datasource.Columns.Add("Description", typeof(string));
_datasource.Columns.Add("Brand", typeof(string));
_datasource.Columns.Add("Model", typeof(string));
_datasource.Columns.Add("Category", typeof(string));
_datasource.Columns.Add("Unit", typeof(string));
_datasource.Columns.Add("Quantity", typeof(int));
_datasource.Columns.Add("Incoming", typeof(int));
_datasource.Columns.Add("Outgoing", typeof(int));
_datasource.Columns.Add("Balance", typeof(int));
_datasource.Columns.Add("UnitCost", typeof(decimal));
_datasource.Columns.Add("ReorderPoint", typeof(int));
_datasource.Columns.Add("ReorderQty", typeof(int));
_datasource.Columns.Add("Type", typeof(string));
_datasource.Columns.Add("Status", typeof(string));
DataColumn _lastpurchasecol = _datasource.Columns.Add("LastPurchased", typeof(DateTime));
_lastpurchasecol.AllowDBNull = true;
_datasource.Constraints.Add("PK", _dpk, true);
try
{
foreach (var _row in _query)
{
decimal _unitcost = 0;
if (_row.Balance > 0 &&
_row.UnitCost > 0)
{
_unitcost = _row.UnitCost / _row.Balance;
}
object _lastpurchased = _row.LastPurchased;
if (VisualBasic.IsDate(_lastpurchased))
{
if (VisualBasic.CDate(_lastpurchased) == VisualBasic.CDate("1/1/1900"))
{
_lastpurchased = DBNull.Value;
}
}
_datasource.Rows.Add(new object[] {
_row.PartCode, _row.PartNo, _row.PartName,
_row.Description, _row.Brand, _row.Model,
_row.Category, _row.Unit, _row.Quantity,
_row.Incoming, _row.Outgoing, _row.Balance,
_unitcost, _row.ReorderPoint, _row.ReorderQty,
_row.Type, _row.Status, _lastpurchased
});
}
}
catch { }
_datasource.AcceptChanges();
}
}
_datasource.DefaultView.Sort = "[PartNo]";
return(_datasource);
}
private DataTable GetCustomers()
{
DataTable _datasource = null;
Cache.SyncTable(SCMS.Connection, "customers");
Cache.SyncTable(SCMS.Connection, "customergroups");
Cache.SyncTable(SCMS.Connection, "locations");
Cache.SyncTable(SCMS.Connection, "bankaccounts");
Cache.SyncTable(SCMS.Connection, "accounts");
Cache.SyncTable(SCMS.Connection, "receivableledger");
DataTable _customers = Cache.GetCachedTable("customers");
DataTable _arledger = Cache.GetCachedTable("receivableledger");
if (_customers != null &&
_arledger != null)
{
var _query = from _cus in _customers.AsEnumerable()
join _ledger in _arledger.AsEnumerable() on _cus.Field <string>("CustomerCode") equals _ledger.Field <string>("CustomerCode") into _cl
where _cus.Field <string>("Company") == SCMS.CurrentCompany.Company
from _ledger in _cl.DefaultIfEmpty(_arledger.NewRow())
group _ledger by new
{
CustomerCode = _cus.Field <string>("CustomerCode"),
CustomerNo = _cus.Field <string>("CustomerNo"),
CustomerName = _cus.Field <string>("CustomerName"),
CustomerGroup = _cus.Field <string>("CustomerGroup"),
Active = _cus.Field <Int16>("Active"),
Address = _cus.Field <string>("Address") + ", " + _cus.Field <string>("Country"),
Phone = _cus.Field <string>("Phone"),
Mobile = _cus.Field <string>("Mobile"),
Fax = _cus.Field <string>("Fax"),
Email = _cus.Field <string>("Email"),
ContactPerson = _cus.Field <string>("POC"),
Notes = _cus.Field <string>("Notes")
} into _group
orderby _group.Key.CustomerNo
select new
{
CustomerCode = _group.Key.CustomerCode,
CustomerNo = _group.Key.CustomerNo,
CustomerName = _group.Key.CustomerName,
CustomerGroup = _group.Key.CustomerGroup,
Active = _group.Key.Active,
Address = _group.Key.Address,
Phone = _group.Key.Phone,
Mobile = _group.Key.Mobile,
Fax = _group.Key.Fax,
Email = _group.Key.Email,
ContactPerson = _group.Key.ContactPerson,
OutstandingBalance = _group.Sum(_ledger => (_ledger.Field <decimal>("Receivable") - _ledger.Field <decimal>("Credited") - _ledger.Field <decimal>("Paid") - _ledger.Field <decimal>("Prepayment"))),
Notes = _group.Key.Notes
};
_datasource = new DataTable();
_datasource.TableName = "customers";
DataColumnCollection _cols = _datasource.Columns;
DataColumn _pk = _cols.Add("CustomerCode", typeof(string));
_cols.Add("CustomerNo", typeof(string));
_cols.Add("CustomerName", typeof(string));
_cols.Add("CustomerGroup", typeof(string));
_cols.Add("Active", typeof(Int16));
_cols.Add("Address", typeof(string));
_cols.Add("Phone", typeof(string));
_cols.Add("Mobile", typeof(string));
_cols.Add("Fax", typeof(string));
_cols.Add("Email", typeof(string));
_cols.Add("ContactPerson", typeof(string));
_cols.Add("Balance", typeof(decimal));
_cols.Add("Notes", typeof(string));
_datasource.Constraints.Add("PK", _pk, true);
try
{
foreach (var _row in _query)
{
_datasource.Rows.Add(new object[] {
_row.CustomerCode, _row.CustomerNo, _row.CustomerName,
_row.CustomerGroup, _row.Active, _row.Address,
_row.Phone, _row.Mobile, _row.Fax,
_row.Email, _row.ContactPerson, _row.OutstandingBalance,
_row.Notes
});
}
_datasource.AcceptChanges();
}
catch { }
}
return(_datasource);
}
private DataTable GetBankAccounts()
{
DataTable _datasource = null;
Cache.SyncTable(SCMS.Connection, "bankaccounts");
Cache.SyncTable(SCMS.Connection, "banks");
Cache.SyncTable(SCMS.Connection, "bankledger");
Cache.SyncTable(SCMS.Connection, "accounts");
Cache.SyncTable(SCMS.Connection, "currencies");
DataTable _bankaccounts = Cache.GetCachedTable("bankaccounts");
DataTable _bankledger = Cache.GetCachedTable("bankledger");
DataTable _accounts = Cache.GetCachedTable("accounts");
if (_bankaccounts != null &&
_bankledger != null &&
_accounts != null)
{
var _query = from _bankaccount in _bankaccounts.AsEnumerable()
join _account in _accounts.AsEnumerable() on _bankaccount.Field <long>("AccountCode") equals _account.Field <long>("AccountCode")
join _ledger in _bankledger.AsEnumerable() on _bankaccount.Field <string>("BankAccountCode") equals _ledger.Field <string>("BankAccountCode") into _bl
where _bankaccount.Field <string>("Company") == SCMS.CurrentCompany.Company
from _ledger in _bl.DefaultIfEmpty(_bankledger.NewRow())
group _ledger by new
{
BankAccountCode = _bankaccount.Field <string>("BankAccountCode"),
BankAccountNo = _bankaccount.Field <string>("AccountNo"),
BankAccountName = _bankaccount.Field <string>("AccountName"),
Currency = _bankaccount.Field <string>("Currency"),
AccountCode = _bankaccount.Field <long>("AccountCode"),
AccountName = _account.Field <string>("AccountName"),
Bank = _bankaccount.Field <string>("Bank"),
Notes = _bankaccount.Field <string>("Notes")
} into _group
select new
{
BankAccountCode = _group.Key.BankAccountCode,
BankAccountNo = _group.Key.BankAccountNo,
BankAccountName = _group.Key.BankAccountName,
Bank = _group.Key.Bank,
Currency = _group.Key.Currency,
AccountCode = _group.Key.AccountCode,
AccountName = _group.Key.AccountName,
CurrentBalance = _group.Sum(_ledger => (_ledger.Field <decimal>("In") - _ledger.Field <decimal>("Out"))),
OutstandingBalance = _group.Sum(_ledger => (_ledger.Field <decimal>("Incoming") - _ledger.Field <decimal>("Outgoing"))),
EndingBalance = _group.Sum(_ledger => (_ledger.Field <decimal>("In") - _ledger.Field <decimal>("Out") + _ledger.Field <decimal>("Incoming") - _ledger.Field <decimal>("Outgoing"))),
Notes = _group.Key.Notes
};
_datasource = new DataTable();
_datasource.TableName = "bankaccounts";
DataColumnCollection _cols = _datasource.Columns;
DataColumn _pk = _cols.Add("BankAccountCode", typeof(string));
_cols.Add("BankAccountNo", typeof(string));
_cols.Add("BankAccountName", typeof(string));
_cols.Add("Bank", typeof(string));
_cols.Add("Currency", typeof(string));
_cols.Add("AccountCode", typeof(long));
_cols.Add("AccountName", typeof(string));
_cols.Add("Current", typeof(decimal));
_cols.Add("Outstanding", typeof(decimal));
_cols.Add("Ending", typeof(decimal));
_cols.Add("Notes", typeof(string));
_datasource.Constraints.Add("PK", _pk, true);
try
{
foreach (var _row in _query)
{
_datasource.Rows.Add(new object[] {
_row.BankAccountCode, _row.BankAccountNo, _row.BankAccountName,
_row.Bank, _row.Currency, _row.AccountCode,
_row.AccountName, _row.CurrentBalance, _row.OutstandingBalance,
_row.EndingBalance, _row.Notes
});
}
_datasource.AcceptChanges();
}
catch { }
}
return(_datasource);
}
private void CreateInitialUser(IDbConnection connection)
{
string _path = Application.StartupPath + "\\Xml\\defaultusers.xml";
DataTable _table = SCMS.XmlToTable(_path);
if (_table != null)
{
DataRow[] _rows = _table.Select("[ComputerName] = '" + Environment.MachineName.ToSqlValidString(true) + "' AND " +
"[UserAccount] = '" + Environment.UserName.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
Cache.SyncTable(connection, "users");
bool _exists = false;
DataTable _cachedtable = Cache.GetCachedTable("users");
if (_cachedtable != null)
{
DataRow[] _exrows = _cachedtable.Select("[Username] LIKE '" + _rows[0]["Username"].ToString().ToSqlValidString(true) + "'");
if (_exrows.Length > 0)
{
_exists = true;
}
}
if (!_exists)
{
Cache.SyncTable(connection, "departments");
Cache.SyncTable(connection, "positions");
string _query = ""; _exists = false;
DataTable _depttable = Cache.GetCachedTable("departments");
if (_depttable != null)
{
DataRow[] _exrows = _depttable.Select("[Department] LIKE '" + _rows[0]["Department"].ToString().ToSqlValidString(true) + "'");
if (_exrows.Length > 0)
{
_exists = true;
}
}
if (!_exists)
{
_query += "INSERT INTO `departments`\n" +
"(`Department`, `DateCreated`)\n" +
"VALUES\n" +
"('" + _rows[0]["Department"].ToString().ToSqlValidString() + "', NOW());\n";
object[] _values = new object[_depttable.Columns.Count];
DataColumnCollection _cols = _depttable.Columns;
_values[_cols["Department"].Ordinal] = _rows[0]["Department"];
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_depttable.Rows.Add(_values);
}
_exists = false;
DataTable _postntable = Cache.GetCachedTable("positions");
if (_postntable != null)
{
DataRow[] _exrows = _postntable.Select("[Position] LIKE '" + _rows[0]["Position"].ToString().ToSqlValidString(true) + "'");
if (_exrows.Length > 0)
{
_exists = true;
}
}
if (!_exists)
{
_query += "INSERT INTO `positions`\n" +
"(`Position`, `DateCreated`)\n" +
"VALUES\n" +
"('" + _rows[0]["Position"].ToString().ToSqlValidString() + "', NOW());\n";
object[] _values = new object[_postntable.Columns.Count];
DataColumnCollection _cols = _postntable.Columns;
_values[_cols["Position"].Ordinal] = _rows[0]["Position"];
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_postntable.Rows.Add(_values);
}
StringBuilder _privileges = new StringBuilder();
for (int i = 1; i <= 700; i++)
{
_privileges.Append("1");
}
_query += "INSERT INTO `users`\n" +
"(`Username`, `Password`, `FirstName`, `MiddleName`, `LastName`, `Department`, `Position`, `Active`, `Role`, `Privileges`, `AllCustomers`, `AllCompanies`, `DateCreated`)\n" +
"VALUES\n" +
"('" + _rows[0]["Username"].ToString().ToSqlValidString() + "', '" + _rows[0]["Password"].ToString().Encrypt(SCMS.EncryptionKey).ToSqlValidString() + "', " +
"'" + _rows[0]["FirstName"].ToString().ToSqlValidString() + "', '" + _rows[0]["MiddleName"].ToString().ToSqlValidString() + "', '" + _rows[0]["LastName"].ToString().ToSqlValidString() + "', " +
"'" + _rows[0]["Department"].ToString().ToSqlValidString() + "', '" + _rows[0]["Position"].ToString().ToSqlValidString() + "', 1, '" + SystemUserInfo.SuperUserRole.ToSqlValidString() + "', " +
"'" + _privileges.ToString() + "', 1, 1, NOW());";
QueResult _result = Que.Execute(connection, _query);
if (String.IsNullOrEmpty(_result.Error.RLTrim()))
{
if (_depttable != null)
{
_depttable.AcceptChanges();
}
if (_postntable != null)
{
_postntable.AcceptChanges();
}
if (_cachedtable != null)
{
object[] _values = new object[_cachedtable.Columns.Count];
DataColumnCollection _cols = _cachedtable.Columns;
_values[_cols["Username"].Ordinal] = _rows[0]["Username"];
_values[_cols["Password"].Ordinal] = _rows[0]["Password"].ToString().Encrypt(SCMS.EncryptionKey);
_values[_cols["FirstName"].Ordinal] = _rows[0]["FirstName"];
_values[_cols["MiddleName"].Ordinal] = _rows[0]["MiddleName"];
_values[_cols["LastName"].Ordinal] = _rows[0]["LastName"];
_values[_cols["Position"].Ordinal] = _rows[0]["Position"];
_values[_cols["Department"].Ordinal] = _rows[0]["Department"];
_values[_cols["Role"].Ordinal] = SystemUserInfo.SuperUserRole;
_values[_cols["Privileges"].Ordinal] = _privileges.ToString();
_values[_cols["AllCustomers"].Ordinal] = 1;
_values[_cols["AllCompanies"].Ordinal] = 1;
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["DateVoided"].Ordinal] = DBNull.Value;
_cachedtable.Rows.Add(_values); _cachedtable.AcceptChanges();
}
Cache.Save();
}
else
{
if (_depttable != null)
{
_depttable.RejectChanges();
}
if (_postntable != null)
{
_postntable.RejectChanges();
}
if (_cachedtable != null)
{
_cachedtable.RejectChanges();
}
}
_result.Dispose(); Materia.RefreshAndManageCurrentProcess();
}
}
_table.Dispose(); _table = null;
Materia.RefreshAndManageCurrentProcess();
}
}
