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