private void LogActivityViewerDialog_Load(object sender, EventArgs e)
{
CheckForIllegalCrossThreadCalls = false; this.ManageOnDispose();
SCMS.Validators.Add(this);
exppnlActions.Expanded = false; exppnlUsers.Expanded = false;
exppnlDateRanges.Expanded = true;
grdUsers.InitializeAppearance(); grdActions.InitializeAppearance();
grdLogs.InitializeAppearance(); pctLoad.Hide();
grdActions.Styles.Normal.Border.Style = BorderStyleEnum.None;
grdLogs.Styles.Normal.Border.Style = BorderStyleEnum.None;
grdUsers.Styles.Normal.Border.Style = BorderStyleEnum.None;
grdUsers.AttachMouseHoverPointer(); grdActions.AttachMouseHoverPointer();
dtpDateFrom.CustomFormat = "dd-MMM-yyyy hh:mm tt"; dtpDateFrom.Format = DevComponents.Editors.eDateTimePickerFormat.Custom;
dtpDateFrom.ShowCheckBox = true; dtpDateFrom.ButtonDropDown.Visible = false;
dtpDateFrom.ShowUpDown = true; dtpDateFrom.AllowEmptyState = false;
dtpDateFrom.Value = VisualBasic.CDate(DateTime.Now.ToShortDateString() + " 12:00:00 AM");
dtpDateFrom.MaxDate = DateTime.Now.AddDays(1); dtpDateFrom.LockUpdateChecked = false;
dtpDateTo.CustomFormat = "dd-MMM-yyyy hh:mm tt"; dtpDateTo.Format = DevComponents.Editors.eDateTimePickerFormat.Custom;
dtpDateTo.ShowCheckBox = true; dtpDateTo.ButtonDropDown.Visible = false;
dtpDateTo.ShowUpDown = true; dtpDateTo.AllowEmptyState = false;
dtpDateTo.Value = DateTime.Now;
dtpDateTo.MaxDate = DateTime.Now.AddDays(1); dtpDateTo.LockUpdateChecked = false;
InitializeActions(); InitializeUsers();
}
/// <summary>
/// Creates a new instance of RestorePointInfo.
/// </summary>
/// <param name="restorepointid"></param>
public RestorePointInfo(long restorepointid)
{
_id = restorepointid;
string _path = Application.StartupPath + "\\Xml\\restorepoints.xml";
DataTable _table = SCMS.XmlToTable(_path);
if (_table != null)
{
DataRow[] _rows = _table.Select("[DetailId] = " + _id.ToString());
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
if (VisualBasic.IsDate(_row["DateAndTime"]))
{
_asof = VisualBasic.CDate(_row["DateAndTime"]);
}
if (!Materia.IsNullOrNothing(_row["Filename"]))
{
_filename = _row["Filename"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Server"]))
{
_server = _row["Server"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Database"]))
{
_database = _row["Database"].ToString();
}
}
_table.Dispose(); _table = null;
Materia.RefreshAndManageCurrentProcess();
}
}
private void ClearInfo()
{
_author = ""; _datecreated = VisualBasic.CDate("1/1/900");
_description = ""; _requiresapprestartafterexecution = false;
_requiresbackupbeforeexecution = false; _requirespcrestartafterexecution = false;
_sqlstatement = ""; _title = "";
}
/// <summary>
/// Creates a new instance of DatabaseScriptInfo.
/// </summary>
/// <param name="table"></param>
public DatabaseScriptInfo(DataTable table)
{
ClearInfo();
if (table != null)
{
if (table.TableName != "scripts")
{
return;
}
if (table.Rows.Count > 0)
{
DataRow _row = table.Rows[0];
if (!Materia.IsNullOrNothing(_row["Author"]))
{
_author = _row["Author"].ToString();
}
if (VisualBasic.IsDate(_row["DateCreated"]))
{
_datecreated = VisualBasic.CDate(_row["DateCreated"]);
}
if (!Materia.IsNullOrNothing(_row["Description"]))
{
_description = _row["Description"].ToString();
}
if (!Materia.IsNullOrNothing(_row["ReferenceNo"]))
{
_referenceno = _row["ReferenceNo"].ToString();
}
if (VisualBasic.IsNumeric(_row["RequireAppRestart"]))
{
_requiresapprestartafterexecution = VisualBasic.CBool(_row["RequireAppRestart"]);
}
if (VisualBasic.IsNumeric(_row["RequireBackup"]))
{
_requiresbackupbeforeexecution = VisualBasic.CBool(_row["RequireBackup"]);
}
if (VisualBasic.IsNumeric(_row["RequirePcRestart"]))
{
_requirespcrestartafterexecution = VisualBasic.CBool(_row["RequirePcRestart"]);
}
if (!Materia.IsNullOrNothing(_row["Script"]))
{
_sqlstatement = _row["Script"].ToString();
}
if (!Materia.IsNullOrNothing(_row["SystemVersion"]))
{
_systemversion = _row["SystemVersion"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Title"]))
{
_title = _row["Title"].ToString();
}
}
}
}
/// <summary>
/// Creates a new instance of DatabaseScriptInfo.
/// </summary>
/// <param name="refno"></param>
public DatabaseScriptInfo(string refno)
{
_referenceno = refno; ClearInfo();
DataTable _scripts = Cache.GetCachedTable("scripts");
if (_scripts != null)
{
DataRow[] _rows = _scripts.Select("[ReferenceNo] LIKE '" + refno.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
if (!Materia.IsNullOrNothing(_row["Author"]))
{
_author = _row["Author"].ToString();
}
if (VisualBasic.IsDate(_row["DateCreated"]))
{
_datecreated = VisualBasic.CDate(_row["DateCreated"]);
}
if (!Materia.IsNullOrNothing(_row["Description"]))
{
_description = _row["Description"].ToString();
}
if (VisualBasic.IsNumeric(_row["RequireAppRestart"]))
{
_requiresapprestartafterexecution = VisualBasic.CBool(_row["RequireAppRestart"]);
}
if (VisualBasic.IsNumeric(_row["RequireBackup"]))
{
_requiresbackupbeforeexecution = VisualBasic.CBool(_row["RequireBackup"]);
}
if (VisualBasic.IsNumeric(_row["RequirePcRestart"]))
{
_requirespcrestartafterexecution = VisualBasic.CBool(_row["RequirePcRestart"]);
}
if (!Materia.IsNullOrNothing(_row["Script"]))
{
_sqlstatement = _row["Script"].ToString();
}
if (!Materia.IsNullOrNothing(_row["SystemVersion"]))
{
_systemversion = _row["SystemVersion"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Title"]))
{
_title = _row["Title"].ToString();
}
}
}
}
/// <summary>
/// Gets the current server's date and time using the specified database connection.
/// </summary>
/// <param name="connection">Database connection</param>
/// <returns></returns>
public static DateTime GetServerDateAndTime(IDbConnection connection)
{
DateTime _datetime = DateTime.Now;
if (connection != null)
{
string _query = "SELECT NOW()";
DataTable _table = null; _table = _table.LoadData(connection, _query);
if (_table != null)
{
if (_table.Rows.Count > 0)
{
_datetime = VisualBasic.CDate(_table.Rows[0][0]);
}
_table.Dispose(); _table = null;
Materia.RefreshAndManageCurrentProcess();
}
}
return(_datetime);
}
private void InitializeInfo()
{
DataTable _scripts = Cache.GetCachedTable("scripts");
if (_scripts != null)
{
DataRow[] _rows = _scripts.Select("[ReferenceNo] LIKE '" + _referenceno.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
if (!Materia.IsNullOrNothing(_row["ReferenceNo"]))
{
txtReferenceNo.Text = _row["ReferenceNo"].ToString();
}
if (!Materia.IsNullOrNothing(_row["SystemVersion"]))
{
txtSystemVersion.Text = _row["SystemVersion"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Author"]))
{
txtAuthor.Text = _row["Author"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Title"]))
{
txtTitle.Text = _row["Title"].ToString();
}
if (!Materia.IsNullOrNothing(_row["Script"]))
{
txtScript.Text = _row["Script"].ToString();
}
if (VisualBasic.IsNumeric(_row["RequireBackup"]))
{
chkBackup.Checked = VisualBasic.CBool(_row["RequireBackup"]);
}
if (VisualBasic.IsNumeric(_row["RequireAppRestart"]))
{
chkRestartApp.Checked = VisualBasic.CBool(_row["RequireAppRestart"]);
}
if (VisualBasic.IsNumeric(_row["RequirePcRestart"]))
{
chkRestartPc.Checked = VisualBasic.CBool(_row["RequirePcRestart"]);
}
if (!Materia.IsNullOrNothing(_row["Description"]))
{
txtDescription.Text = _row["Description"].ToString();
}
if (VisualBasic.IsNumeric(_row["Executed"]))
{
if (VisualBasic.CBool(_row["Executed"]))
{
if (VisualBasic.IsDate(_row["DateExecuted"]))
{
lblExecuted.Text = "Last Executed : " + VisualBasic.Format(VisualBasic.CDate(_row["DateExecuted"]), "dd-MMM-yyyy");
lblExecuted.Show(); lblExecuted.BringToFront();
}
else
{
lblExecuted.Hide();
}
}
else
{
lblExecuted.Hide();
}
}
if (VisualBasic.IsNumeric(_row["AutoScript"]))
{
if (VisualBasic.CBool(_row["AutoScript"]))
{
lblAutoScript.Show(); lblAutoScript.BringToFront();
}
else
{
lblAutoScript.Hide();
}
}
else
{
lblAutoScript.Hide();
}
}
}
}
/// <summary>
/// Applies table updates from the specified DataTable object into the current DataObjectMap's table.
/// </summary>
/// <param name="table"></param>
public void ApplyUpdatesFromTable(DataTable table)
{
if (_table != null &&
table != null)
{
DataTable _updates = table.GetChanges();
if (_updates != null)
{
foreach (DataRow _row in _updates.Rows)
{
string _pk = "";
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
if (String.IsNullOrEmpty(_pk.RLTrim()))
{
_pk = _table.Columns[0].ColumnName;
}
object _pkvalue = null;
if (_row.RowState == DataRowState.Deleted ||
_row.RowState == DataRowState.Detached ||
_row.RowState == DataRowState.Modified)
{
try { _pkvalue = _row[_pk, DataRowVersion.Original]; }
catch { _pkvalue = null; }
}
else
{
_pkvalue = _row[_pk];
}
if (!Materia.IsNullOrNothing(_pkvalue))
{
string _filter = "[" + _pk + "] = ";
DataColumn _pkcol = _table.Columns[_pk];
object[] _values = null;
switch (_row.RowState)
{
case DataRowState.Added:
_values = new object[_table.Columns.Count];
foreach (DataColumn _col in _table.Columns)
{
if (!_col.AutoIncrement)
{
if (table.Columns.Contains(_col.ColumnName))
{
_values[_col.Ordinal] = _row[_col.ColumnName];
}
}
}
AddRow(_values);
break;
case DataRowState.Deleted:
case DataRowState.Detached:
case DataRowState.Modified:
if (_pkcol.DataType.Name == typeof(string).Name ||
_pkcol.DataType.Name == typeof(String).Name)
{
_filter += "'" + _pkvalue.ToString().ToSqlValidString(true) + "'";
}
else if (_pkcol.DataType.Name == typeof(DateTime).Name)
{
if (VisualBasic.IsDate(_pkvalue))
{
_filter += "#" + VisualBasic.Format(VisualBasic.CDate(_pkvalue), "MM/dd/yyyy hh:mm:ss tt") + "#";
}
else
{
_filter = "";
}
}
else if (_pkcol.DataType.Name == typeof(bool).Name ||
_pkcol.DataType.Name == typeof(Boolean).Name)
{
bool _value = VisualBasic.CBool(_pkvalue);
_filter += _value.ToString();
}
else if (_pkcol.DataType.Name == typeof(byte).Name ||
_pkcol.DataType.Name == typeof(Byte).Name ||
_pkcol.DataType.Name == typeof(decimal).Name ||
_pkcol.DataType.Name == typeof(Decimal).Name ||
_pkcol.DataType.Name == typeof(double).Name ||
_pkcol.DataType.Name == typeof(Double).Name ||
_pkcol.DataType.Name == typeof(int).Name ||
_pkcol.DataType.Name == typeof(Int16).Name ||
_pkcol.DataType.Name == typeof(Int32).Name ||
_pkcol.DataType.Name == typeof(Int64).Name ||
_pkcol.DataType.Name == typeof(long).Name ||
_pkcol.DataType.Name == typeof(sbyte).Name ||
_pkcol.DataType.Name == typeof(SByte).Name ||
_pkcol.DataType.Name == typeof(short).Name ||
_pkcol.DataType.Name == typeof(Single).Name)
{
if (VisualBasic.IsNumeric(_pkvalue))
{
_filter += _pkvalue.ToString();
}
else
{
_filter = "";
}
}
if (!String.IsNullOrEmpty(_filter.RLTrim()))
{
if (_row.RowState == DataRowState.Deleted ||
_row.RowState == DataRowState.Detached)
{
DeleteRow(_filter);
}
else
{
DataRow[] _rows = Select(_filter);
if (_rows.Length > 0)
{
DataRow _actualrow = _rows[0];
if (_actualrow.RowState != DataRowState.Deleted &&
_actualrow.RowState != DataRowState.Detached)
{
foreach (DataColumn _col in _table.Columns)
{
if (!_col.AutoIncrement)
{
if (table.Columns.Contains(_col.ColumnName))
{
_actualrow[_col.ColumnName] = _row[_col.ColumnName];
}
}
}
}
}
else
{
_values = new object[_table.Columns.Count];
foreach (DataColumn _col in _table.Columns)
{
if (!_col.AutoIncrement)
{
if (table.Columns.Contains(_col.ColumnName))
{
_values[_col.Ordinal] = _row[_col.ColumnName];
}
}
}
AddRow(_values);
}
}
}
break;
default: break;
}
}
}
}
}
}
private string Generate()
{
StringBuilder _sql = new StringBuilder();
if (_table != null)
{
if (_table.Columns.Count > 0)
{
string _pk = _primarykey.Field;
if (String.IsNullOrEmpty(_pk.RLTrim()))
{
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
}
if (!String.IsNullOrEmpty(_pk.RLTrim()))
{
string _tablename = _table.TableName;
if (String.IsNullOrEmpty(_tablename.RLTrim()))
{
_tablename = "table";
}
string _insert = ""; string _update = "";
string _pkvalue = "{" + _table.Columns[_pk].Ordinal.ToString() + "}";
if (!String.IsNullOrEmpty(_primarykey.Value.RLTrim()))
{
_pkvalue = _primarykey.Value;
}
string _delete = "DELETE FROM `" + _tablename + "` WHERE (`" + _pk + "` = " + _pkvalue + ");";
string _insertfields = ""; string _insertparameters = ""; string _updatefield = "";
foreach (DataColumn _column in _table.Columns)
{
if (!_column.AutoIncrement &&
!_excludedfields.Contains(_column.ColumnName))
{
_insertfields += (String.IsNullOrEmpty(_insertfields.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "`";
if (_column.ColumnName != _foreignkey.Field)
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + "{" + _column.Ordinal.ToString() + "}";
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = {" + _column.Ordinal.ToString() + "}";
}
else
{
if (!String.IsNullOrEmpty(_foreignkey.Value.RLTrim()))
{
if (_foreignkey.HeaderTable != null)
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
else
{
if (!String.IsNullOrEmpty(_foreignkey.HeaderPrimaryKey.RLTrim()))
{
if (_foreignkey.HeaderTable.Columns.Contains(_foreignkey.HeaderPrimaryKey))
{
DataColumn _headercolumn = _foreignkey.HeaderTable.Columns[_foreignkey.HeaderPrimaryKey];
if (_headercolumn.AutoIncrement)
{
if (_foreignkey.HeaderTable.Rows.Count > 0)
{
DataRow rw = null;
foreach (DataRow row in _foreignkey.HeaderTable.Rows)
{
if (row.RowState != DataRowState.Deleted &&
row.RowState != DataRowState.Detached)
{
rw = row; break;
}
}
if (rw != null)
{
if (rw.RowState == DataRowState.Added)
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
else
{
if (!Materia.IsNullOrNothing(rw[_foreignkey.HeaderPrimaryKey]))
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + "'" + rw[_foreignkey.HeaderPrimaryKey].ToString().ToSqlValidString() + "'";
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + "'" + rw[_foreignkey.HeaderPrimaryKey].ToString().ToSqlValidString() + "'";
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
}
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
}
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + _foreignkey.Value;
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = " + _foreignkey.Value;
}
}
}
else
{
_insertparameters += (String.IsNullOrEmpty(_insertparameters.RLTrim()) ? "" : ", ") + "{" + _column.Ordinal.ToString() + "}";
_updatefield += (String.IsNullOrEmpty(_updatefield.RLTrim()) ? "" : ", ") + "`" + _column.ColumnName + "` = {" + _column.Ordinal.ToString() + "}";
}
}
}
}
_insert = "INSERT INTO `" + _tablename + "`\n" +
"(" + _insertfields + ")\n" +
"VALUES\n" +
"(" + _insertparameters + ");";
string _query = "";
foreach (DataRow row in _table.Rows)
{
if (row.RowState != DataRowState.Unchanged)
{
_query = ""; _update = "UPDATE `" + _tablename + "` SET " + _updatefield + " WHERE (`" + _pk + "` = " + _pkvalue + ");";
string[] _values = new string[_table.Columns.Count];
foreach (DataColumn _column in _table.Columns)
{
string _value = "NULL";
object _currentvalue = null;
if (row.RowState == DataRowState.Deleted ||
row.RowState == DataRowState.Detached)
{
try { _currentvalue = row[_column.ColumnName, DataRowVersion.Original]; }
catch { }
}
else
{
_currentvalue = row[_column.ColumnName];
}
if (row.RowState == DataRowState.Modified)
{
if (_column.ColumnName == _pk)
{
object _originalvalue = null;
try
{
_originalvalue = row[_column.ColumnName, DataRowVersion.Original];
if (_originalvalue != _currentvalue)
{
string _originalpk = "NULL";
if (_column.DataType.Name == typeof(string).Name ||
_column.DataType.Name == typeof(String).Name)
{
_originalpk = "'" + _originalvalue.ToString().ToSqlValidString() + "'";
}
else if (_column.DataType.Name == typeof(DateTime).Name)
{
if (VisualBasic.IsDate(_originalvalue))
{
_originalpk = "'" + VisualBasic.CDate(_originalvalue).ToSqlValidString(true) + "'";
}
}
else if (_column.DataType.Name == typeof(byte).Name ||
_column.DataType.Name == typeof(Byte).Name ||
_column.DataType.Name == typeof(decimal).Name ||
_column.DataType.Name == typeof(Decimal).Name ||
_column.DataType.Name == typeof(double).Name ||
_column.DataType.Name == typeof(Double).Name ||
_column.DataType.Name == typeof(float).Name ||
_column.DataType.Name == typeof(int).Name ||
_column.DataType.Name == typeof(Int16).Name ||
_column.DataType.Name == typeof(Int32).Name ||
_column.DataType.Name == typeof(Int64).Name ||
_column.DataType.Name == typeof(long).Name ||
_column.DataType.Name == typeof(sbyte).Name ||
_column.DataType.Name == typeof(SByte).Name ||
_column.DataType.Name == typeof(short).Name ||
_column.DataType.Name == typeof(Single).Name)
{
if (VisualBasic.IsNumeric(_originalvalue))
{
_originalpk = _originalvalue.ToString();
}
}
else if (_column.DataType.Name == typeof(bool).Name ||
_column.DataType.Name == typeof(Boolean).Name)
{
try
{
if (VisualBasic.CBool(_originalvalue))
{
_originalpk = "1";
}
else
{
_originalpk = "0";
}
}
catch { }
}
else
{
if (_column.DataType.Name.ToLower().Contains("byte[]") ||
_column.DataType.Name.ToLower().Contains("byte()") ||
_column.DataType.Name.ToLower().Contains("bytes[]") ||
_column.DataType.Name.ToLower().Contains("bytes()"))
{
try
{ _originalpk = "x'" + ((byte[])_originalvalue).ToHexadecimalString().ToSqlValidString() + "'"; }
catch { _originalpk = "NULL"; }
}
}
_update = "UPDATE `" + _tablename + "` SET " + _updatefield + " WHERE (`" + _pk + "` = " + _originalpk + ");";
}
}
catch { }
}
}
if (!Materia.IsNullOrNothing(_currentvalue))
{
if (_column.DataType.Name == typeof(string).Name ||
_column.DataType.Name == typeof(String).Name)
{
_value = "'" + _currentvalue.ToString().ToSqlValidString() + "'";
}
else if (_column.DataType.Name == typeof(DateTime).Name)
{
if (VisualBasic.IsDate(_currentvalue))
{
_value = "'" + VisualBasic.CDate(_currentvalue).ToSqlValidString(true) + "'";
}
}
else if (_column.DataType.Name == typeof(byte).Name ||
_column.DataType.Name == typeof(Byte).Name ||
_column.DataType.Name == typeof(decimal).Name ||
_column.DataType.Name == typeof(Decimal).Name ||
_column.DataType.Name == typeof(double).Name ||
_column.DataType.Name == typeof(Double).Name ||
_column.DataType.Name == typeof(float).Name ||
_column.DataType.Name == typeof(int).Name ||
_column.DataType.Name == typeof(Int16).Name ||
_column.DataType.Name == typeof(Int32).Name ||
_column.DataType.Name == typeof(Int64).Name ||
_column.DataType.Name == typeof(long).Name ||
_column.DataType.Name == typeof(sbyte).Name ||
_column.DataType.Name == typeof(SByte).Name ||
_column.DataType.Name == typeof(short).Name ||
_column.DataType.Name == typeof(Single).Name)
{
if (VisualBasic.IsNumeric(_currentvalue))
{
_value = _currentvalue.ToString();
}
}
else if (_column.DataType.Name == typeof(bool).Name ||
_column.DataType.Name == typeof(Boolean).Name)
{
try
{
if (VisualBasic.CBool(_currentvalue))
{
_value = "1";
}
else
{
_value = "0";
}
}
catch { }
}
else
{
if (_column.DataType.Name.ToLower().Contains("byte[]") ||
_column.DataType.Name.ToLower().Contains("byte()") ||
_column.DataType.Name.ToLower().Contains("bytes[]") ||
_column.DataType.Name.ToLower().Contains("bytes()"))
{
try
{ _value = "x'" + ((byte[])_currentvalue).ToHexadecimalString().ToSqlValidString() + "'"; }
catch { _value = "NULL"; }
}
}
}
_values[_column.Ordinal] = _value;
}
switch (row.RowState)
{
case DataRowState.Added:
_query = _insert; break;
case DataRowState.Modified:
_query = _update; break;
case DataRowState.Deleted:
_query = _delete; break;
case DataRowState.Detached:
default: break;
}
_query = String.Format(_query, _values);
string _currentsql = _sql.ToString();
if (!String.IsNullOrEmpty(_currentsql.RLTrim()))
{
_sql.Append("\n");
}
_sql.Append(_query);
}
}
}
}
}
return(_sql.ToString());
}
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 void btnSave_Click(object sender, EventArgs e)
{
if (!btnSave.Enabled)
{
return;
}
Validator _validator = SCMS.Validators[this];
if (!Materia.Valid(_validator, txtAddress, !string.IsNullOrEmpty(txtAddress.Text.RLTrim()), "Please specify company address."))
{
tbctrl.SelectedTab = tbCompany; return;
}
if (!Materia.Valid(_validator, cboCountry, cboCountry.SelectedIndex >= 0, "Please specify a valid country."))
{
tbctrl.SelectedTab = tbCompany; return;
}
if (!Materia.Valid(_validator, cboCashAdvance, cboCashAdvance.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboUnallocatedPayments, cboUnallocatedPayments.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboRawMaterials, cboRawMaterials.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboStockConsumption, cboStockConsumption.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboStockAdjustment, cboStockAdjustment.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboRollForward, cboRollForward.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (chkAutoBackup.Checked)
{
if (!Materia.Valid(_validator, lblPath, !string.IsNullOrEmpty(lblPath.Text.RLTrim()), "Please specify backup output destination."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
if (!Materia.Valid(_validator, lblPath, Directory.Exists(lblPath.Text), "Please specify backup output destination."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
if (dtpBackUpTime2.LockUpdateChecked)
{
DateTime _date1 = VisualBasic.CDate(DateTime.Now.ToShortDateString() + " " + VisualBasic.Format(dtpBackUpTime1.Value, "hh:mm tt"));
DateTime _date2 = VisualBasic.CDate(DateTime.Now.ToShortDateString() + " " + VisualBasic.Format(dtpBackUpTime2.Value, "hh:mm tt"));
if (!Materia.Valid(_validator, dtpBackUpTime2, _date2 >= _date1, "Please specify a time equal or higher than the first instance."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
}
}
DataTable _settings = Cache.GetCachedTable("settings");
if (_settings != null)
{
DataRow[] _rows = _settings.Select("[Company] LIKE '" + SCMS.CurrentCompany.Company.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
_row["Address"] = txtAddress.Text; _row["Country"] = cboCountry.SelectedValue.ToString();
_row["Phone"] = txtPhone.Text; _row["Mobile"] = txtMobile.Text;
_row["Fax"] = txtFax.Text; _row["Email"] = txtEmail.Text;
try { _row["CompanyLogo"] = pctCompanyLogo.Image.ToByteArray(); }
catch { }
try { _row["ReportLogo"] = pctReportLogo.Image.ToByteArray(); }
catch { }
_row["CashAdvanceAccountCode"] = cboCashAdvance.SelectedValue;
_row["RawMaterialAccountCode"] = cboRawMaterials.SelectedValue;
_row["StockConsumptionAccountCode"] = cboStockConsumption.SelectedValue;
_row["StockAdjustmentAccountCode"] = cboStockAdjustment.SelectedValue;
_row["UnallocatedPaymentAccountCode"] = cboUnallocatedPayments.SelectedValue;
_row["RollForwardAccountCode"] = cboRollForward.SelectedValue;
QueryGenerator _generator = new QueryGenerator(_settings);
string _query = _generator.ToString();
_generator = null; Materia.RefreshAndManageCurrentProcess();
if (string.IsNullOrEmpty(_query.RLTrim()))
{
GlobalSettings.AutomaticBackupEnabled = chkAutoBackup.Checked;
if (chkAutoBackup.Checked)
{
GlobalSettings.AutomaticBackupEnabled2 = dtpBackUpTime2.LockUpdateChecked;
}
else
{
GlobalSettings.AutomaticBackupEnabled2 = false;
}
GlobalSettings.AutomaticBackupPath = lblPath.Text;
GlobalSettings.AutomaticBackupTime1 = dtpBackUpTime1.Value;
if (chkAutoBackup.Checked &&
dtpBackUpTime2.LockUpdateChecked)
{
GlobalSettings.AutomaticBackupTime2 = dtpBackUpTime2.Value;
}
if (txtIdleTime.LockUpdateChecked)
{
GlobalSettings.AutomaticLockTime = txtIdleTime.Value;
}
else
{
GlobalSettings.AutomaticLockTime = 0;
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(UserAction.Edit, "Updated the application settings.");
while (!_logresult.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_logresult.IsCompleted)
{
try { _logresult = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
return;
}
btnSave.Enabled = false;
IAsyncResult _result = Que.BeginExecution(SCMS.Connection, _query);
while (!_result.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
_settings.RejectChanges();
if (!_result.IsCompleted)
{
try { _result = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
else
{
QueResult _queresult = Que.EndExecution(_result);
if (string.IsNullOrEmpty(_queresult.Error.RLTrim()))
{
_settings.AcceptChanges(); Cache.Save(); _updated = false;
GlobalSettings.AutomaticBackupEnabled = chkAutoBackup.Checked;
if (chkAutoBackup.Checked)
{
GlobalSettings.AutomaticBackupEnabled2 = dtpBackUpTime2.LockUpdateChecked;
}
else
{
GlobalSettings.AutomaticBackupEnabled2 = false;
}
GlobalSettings.AutomaticBackupPath = lblPath.Text;
GlobalSettings.AutomaticBackupTime1 = dtpBackUpTime1.Value;
if (chkAutoBackup.Checked &&
dtpBackUpTime2.LockUpdateChecked)
{
GlobalSettings.AutomaticBackupTime2 = dtpBackUpTime2.Value;
}
if (txtIdleTime.LockUpdateChecked)
{
GlobalSettings.AutomaticLockTime = txtIdleTime.Value;
}
else
{
GlobalSettings.AutomaticLockTime = 0;
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(UserAction.Edit, "Updated the application settings.");
while (!_logresult.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_logresult.IsCompleted)
{
try { _logresult = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
else
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
else
{
_settings.RejectChanges(); SCMS.LogError(this.GetType().Name, new Exception(_queresult.Error));
MsgBoxEx.Alert("Failed to save application settings.", "Save Settings"); btnSave.Enabled = true;
}
}
}
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!grdSearch.Redraw)
{
return;
}
if (grdSearch.DataSource == null)
{
return;
}
DataTable _table = null;
try { _table = (DataTable)grdSearch.DataSource; }
catch { }
if (_table != null)
{
if (_multiselect)
{
_selectedrows = _table.Select("[Select] = 1");
}
else
{
if (grdSearch.RowSel < grdSearch.Rows.Fixed)
{
return;
}
string _pk = "";
for (int i = 0; i <= (_table.Columns.Count - 1); i++)
{
if (_table.Columns[i].Unique)
{
_pk = _table.Columns[i].ColumnName; break;
}
}
if (!string.IsNullOrEmpty(_pk.RLTrim()))
{
object _value = grdSearch[grdSearch.RowSel, _pk];
_selectedrows = _table.Select("CONVERT([" + _pk + "], System.String) LIKE '" + _value.ToString().ToSqlValidString(true) + "'");
}
else
{
string _filter = "";
for (int i = grdSearch.Cols.Fixed; i <= (grdSearch.Cols.Count - 1); i++)
{
Type _datatype = grdSearch.Cols[i].DataType;
if (_datatype != null)
{
if (_datatype.Name == typeof(string).Name ||
_datatype.Name == typeof(String).Name)
{
_filter += (!string.IsNullOrEmpty(_filter.RLTrim()) ? " AND\n" : "") + "[" + grdSearch.Cols[i].Name + "] = '" + grdSearch[grdSearch.RowSel, i].ToString().ToSqlValidString(true) + "'";
}
else if (_datatype.Name == typeof(byte).Name ||
_datatype.Name == typeof(Byte).Name ||
_datatype.Name == typeof(decimal).Name ||
_datatype.Name == typeof(Decimal).Name ||
_datatype.Name == typeof(double).Name ||
_datatype.Name == typeof(Double).Name ||
_datatype.Name == typeof(float).Name ||
_datatype.Name == typeof(int).Name ||
_datatype.Name == typeof(Int16).Name ||
_datatype.Name == typeof(Int32).Name ||
_datatype.Name == typeof(Int64).Name ||
_datatype.Name == typeof(long).Name ||
_datatype.Name == typeof(sbyte).Name ||
_datatype.Name == typeof(SByte).Name ||
_datatype.Name == typeof(short).Name ||
_datatype.Name == typeof(Single).Name)
{
_filter += (!string.IsNullOrEmpty(_filter.RLTrim()) ? " AND\n" : "") + "[" + grdSearch.Cols[i].Name + "] = " + grdSearch[grdSearch.RowSel, i].ToString();
}
else if (_datatype.Name == typeof(DateTime).Name)
{
_filter += (!string.IsNullOrEmpty(_filter.RLTrim()) ? " AND\n" : "") + "[" + grdSearch.Cols[i].Name + "] = #" + VisualBasic.CDate(grdSearch[grdSearch.RowSel, i]).ToShortDateString() + "#";
}
else
{
}
}
}
_selectedrows = _table.Select(_filter);
}
}
}
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
/// <summary>
/// Synchronizes the specified database table using the supplied database connection.
/// </summary>
/// <param name="connection"></param>
/// <param name="tablename"></param>
/// <param name="primarykey"></param>
public static void SyncTable(IDbConnection connection, string tablename, string primarykey)
{
if (_cacheddataset == null)
{
_cacheddataset = SCMS.XmlToDataSet(CachePath);
}
if (_cacheddataset == null)
{
if (connection != null)
{
_cacheddataset = new DataSet();
_cacheddataset.DataSetName = connection.ConnectionString.ConnectionStringValue(ConnectionStringSection.Database);
}
}
if (_cacheddataset != null)
{
DataTable _table = null;
if (_cacheddataset.Tables.Contains(tablename))
{
_table = _cacheddataset.Tables[tablename];
DataTable _updated = null;
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
DateTime _lastupdate = VisualBasic.CDate("1/1/1900");
string _datetimefield = "DateAndTime";
if (_table.Columns.Contains("LastModified"))
{
_datetimefield = "LastModified";
}
object _maxdate = _table.Compute("MAX([" + _datetimefield + "])", "");
if (VisualBasic.IsDate(_maxdate))
{
_lastupdate = VisualBasic.CDate(_maxdate);
}
_updated = _updated.LoadData(SCMS.Connection, "SELECT `OldValue`, `NewValue` FROM `updateditems` AS `u` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "') AND (`LastModified` >= '" + _lastupdate.ToSqlValidString(true) + "')");
}
else
{
_updated = _updated.LoadData(SCMS.Connection, "SELECT `OldValue`, `NewValue` FROM `updateditems` AS `u` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "')");
}
if (_updated != null)
{
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
for (int i = 0; i <= (_table.Columns.Count - 1); i++)
{
if (_table.Columns[i].Unique)
{
_pk = _table.Columns[i].ColumnName; break;
}
}
}
if (!string.IsNullOrEmpty(_pk.RLTrim()))
{
for (int i = 0; i <= (_updated.Rows.Count - 1); i++)
{
DataRow _row = _updated.Rows[i];
DataRow[] _rows = _table.Select("CONVERT([" + _pk + "], System.String) = '" + _row["OldValue"].ToString().ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow[] _exists = _table.Select("CONVERT([" + _pk + "], System.String) = '" + _row["NewValue"].ToString().ToSqlValidString(true) + "'");
if (_exists.Length <= 0)
{
_rows[0][_pk] = _row["NewValue"];
}
}
}
_table.AcceptChanges();
}
_updated.Dispose(); _updated = null;
Materia.RefreshAndManageCurrentProcess();
}
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
DateTime _lastupdate = VisualBasic.CDate("1/1/1900");
string _datetimefield = "DateAndTime";
if (_table.Columns.Contains("LastModified"))
{
_datetimefield = "LastModified";
}
object _maxdate = _table.Compute("MAX([" + _datetimefield + "])", "");
if (VisualBasic.IsDate(_maxdate))
{
_lastupdate = VisualBasic.CDate(_maxdate);
}
DataTable _updates = null;
_updates = _updates.LoadData(connection, "SELECT * FROM `" + tablename + "` WHERE (`" + _datetimefield + "` >= '" + _lastupdate.ToSqlValidString(true) + "')");
if (_updates != null)
{
if (_updates.Rows.Count > 0)
{
try { _table.Merge(_updates, false, MissingSchemaAction.Add); }
catch (Exception ex) { SCMS.LogError("Cache", ex); }
if (_table.Columns.Contains("Voided"))
{
DataRow[] _rows = _table.Select("[Voided] = 1");
if (_rows.Length > 0)
{
System.Collections.IEnumerator _enumerators = _rows.GetEnumerator();
while (_enumerators.MoveNext())
{
((DataRow)_enumerators.Current).Delete();
}
}
}
_table.AcceptChanges();
}
try { _updates.Dispose(); }
catch { }
finally { _updates = null; Materia.RefreshAndManageCurrentProcess(); }
}
DataTable _deleted = null;
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "') AND (`LastModified` >= '" + _lastupdate.ToSqlValidString(true) + "');");
}
else
{
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "');");
}
if (_deleted != null)
{
if (_deleted.Rows.Count > 0)
{
for (int i = 0; i <= (_deleted.Rows.Count - 1); i++)
{
DataRow _delrow = _deleted.Rows[i];
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
}
if (!String.IsNullOrEmpty(_pk.RLTrim()))
{
DataRow[] _delrows = _table.Select("CONVERT([" + _pk + "], System.String) LIKE '" + _delrow["Value"].ToString().ToSqlValidString(true) + "'");
if (_delrows.Length > 0)
{
System.Collections.IEnumerator _selrows = _delrows.GetEnumerator();
while (_selrows.MoveNext())
{
((DataRow)_selrows.Current).Delete();
}
_table.AcceptChanges(); Materia.RefreshAndManageCurrentProcess();
}
}
}
}
_deleted.Dispose(); _deleted = null;
Materia.RefreshAndManageCurrentProcess();
}
}
}
else
{
if (connection != null)
{
_table = _table.LoadData(connection, "SELECT * FROM `" + tablename + "`");
if (_table != null)
{
if (!string.IsNullOrEmpty(primarykey.RLTrim()))
{
if (_table.Constraints.Count > 0 &&
_table.Columns.Contains(primarykey))
{
_table.Constraints.Clear();
}
if (_table.Constraints.Count <= 0 &&
_table.Columns.Contains(primarykey))
{
_table.Constraints.Add("PK", _table.Columns[primarykey], true);
}
}
if (_table.Columns.Contains("Voided"))
{
DataRow[] _rows = _table.Select("[Voided] = 1");
if (_rows.Length > 0)
{
System.Collections.IEnumerator _enumerators = _rows.GetEnumerator();
while (_enumerators.MoveNext())
{
((DataRow)_enumerators.Current).Delete();
}
}
}
DataTable _deleted = null;
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "');");
if (_deleted != null)
{
if (_deleted.Rows.Count > 0)
{
for (int i = 0; i <= (_deleted.Rows.Count - 1); i++)
{
DataRow _delrow = _deleted.Rows[i];
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
}
if (!String.IsNullOrEmpty(_pk.RLTrim()))
{
DataRow[] _delrows = _table.Select("CONVERT([" + _pk + "], System.String) LIKE '" + _delrow["Value"].ToString().ToSqlValidString(true) + "'");
if (_delrows.Length > 0)
{
System.Collections.IEnumerator _selrows = _delrows.GetEnumerator();
while (_selrows.MoveNext())
{
((DataRow)_selrows.Current).Delete();
}
_table.AcceptChanges(); Materia.RefreshAndManageCurrentProcess();
}
}
}
}
_deleted.Dispose(); _deleted = null;
Materia.RefreshAndManageCurrentProcess();
}
_table.AcceptChanges(); _cacheddataset.Tables.Add(_table);
}
}
}
Save();
}
}
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);
}