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