private void newDropDown(EntryItem edit, int fromTable, int link, int Top, int Left, int Width, string text)
{
using (AutoCursor.DatabaseCall)
{
try
{
edit.Combo(Top, Left, Width, text);
DBHandler db = new DBHandler(connect);
int tno = pcLinks[link].tableNo;
TPCTable pcTable = pcTables[tno];
int noFields = pcTable.noFields;
int offsetFields = pcTable.offsetFields;
int noShowFields = pcTable.noShowFields;
int offsetShowFields = pcTable.offsetShowFields;
int noOrderFields = pcTable.noOrderFields;
int noLinkPairs = pcLinks[link].noLinkPairs;
int offsetOrderFields = pcTable.offsetOrderFields;
if (noShowFields > 0)
db.GetTable(pcTable.name, pcFields, offsetFields, noFields, pcShowFields, offsetShowFields, noShowFields);
else
db.GetTable(pcTable.name, pcFields, offsetFields, noFields, pcOrderFields, offsetOrderFields, noOrderFields);
DataTable table = db.GetDataTable();
System.Data.DataRowCollection rows = table.Rows;
for (int row = 0; row < rows.Count; row++)
{
string[] values = new string[noLinkPairs];
string[] descr = new string[noShowFields == 0 ? noLinkPairs : noShowFields];
for (int field = 0; field < noFields; field++)
{
int offset = pcLinks[link].offsetLinkPairs;
int offset2 = pcTable.offsetShowFields;
for (int pair = 0; pair < noLinkPairs; pair++)
{
if (field == pcLinkPairs[pair + offset].toNo)
{
values[pair] = rows[row][field].ToString();
if (noShowFields == 0)
descr[pair] = values[pair];
}
}
for (int show = 0; show < noShowFields; show++)
{
if (field == pcShowFields[offset2 + show].index)
descr[show] = rows[row][field].ToString();
}
}
edit.combo.Items.Add(new ListItem(values, descr, row));
}
int no = getIndex(edit.combo, text);
edit.combo.SelectedIndex = no;
}
catch (Exception ex)
{
showException(ex);
}
}
}
private void parmExecute(EHow how)
{
if (allowUpdates == false && how != EHow.DISPLAY)
return;
string where = "", c = "", query = "";
int row = -1;
int field;
DataTable data = null;
int offset = pcTable.offsetFields;
if (tablesDataGrid.CurrentRow != null)
{
where = "WHERE ";
row = tablesDataGrid.CurrentRow.Index;
data = tablesDataGrid.DataSource as DataTable;
for (int k = 0; k < pcTable.noKeyFields; k++)
{
int okfield = k + pcTable.offsetKeyFields;
field = pcKeyFields[okfield].index;
string value = data.Rows[row][field].ToString();
TPCField pcField = pcFields[offset + field];
string ready = sqlReady(pcField, value);
where += c + pcFields[offset + field].name + " = " + ready;
c = " AND ";
}
}
string oldUsId = "";
string oldTmStamp = "";
string newUsId = "";
string newTmStamp = "";
if (row != -1)
{
int no = pcTable.noFields;
oldUsId = data.Rows[row][no].ToString();
oldTmStamp = DateTime.Parse(data.Rows[row][no + 1].ToString()).ToString("yyyyMMddhhmmss");
}
List<EntryItem> edits = new List<EntryItem>(pcTable.noFields);
ScaffoldForm form = scaffoldForm = new ScaffoldForm(this, how, edits);
int comboWidth = form.ClientSize.Width - 132;
int editWidth = comboWidth - 18;
// The next 3 lines are indicative of why properties are at best second class.
Size size = form.MinimumSize;
size.Height = pcTable.noFields * 24 + 66;
form.MinimumSize = size;
Dictionary<int, ComboBox> combos = new Dictionary<int, ComboBox>();
for (field = 0; field < pcTable.noFields; field++)
{
EntryItem edit = new EntryItem();
edits.Add(edit);
TPCField pcField = pcFields[field + offset];
edit.ofType = pcField.type;
edit.isNull = pcField.isNull == 1;
if (how != EHow.ADD && (how == EHow.DELETE || how == EHow.DISPLAY || isKey(pcTable, field))
|| (how == EHow.ADD && pcField.type == DBHandler.PC_SEQUENCE))
edit.isReadOnly = true;
if (pcField.uppercase == 1 || isKey(pcTable, field) == true)
edit.uppercase = true;
Label label = edit.label = new Label();
label.Text = pcField.name;
label.Top = field * 24 + 4;
label.Left = 4;
form.CapturePanel.Controls.Add(label);
if (showSetup.Checked == true)
LogDebug = label.Text + " " + label.Top + " " + label.Left;
string setupWork = pcField.name;
string value = "";
if (how == EHow.ADD || (how == EHow.CHANGE && isKey(pcTable, field) == false))
{
value = row != -1 ? data.Rows[row][field].ToString() : "";
if (clearAdd.Checked == true && how == EHow.ADD) value = "";
int link; int pair;
if (setupLink(pcTable, field, out link, out pair))
{
setupWork += " Is Link Field";
if (sizeIsOk(link) == true)
{
setupWork += " and is not too large";
if (pair == 0)
{
newDropDown(edit, pcTable.index, link, field * 24 + 4, 124, comboWidth, value);
combos.Add(link, edit.combo);
edit.pair = pair;
form.CapturePanel.Controls.Add(edit.combo);
if (showSetup.Checked == true)
LogDebug = setupWork;
}
else
{
setupWork += " is extra link pair field";
edit.isReadOnly = true;
edit.combo = combos[link];
edit.pair = pair;
edit.combo.SelectedIndexChanged += new EventHandler(editComboSelectedIndexChanged);
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
List<EntryItem> list = (List<EntryItem>)edit.combo.Tag;
list.Add(edit);
form.CapturePanel.Controls.Add(edit.text);
}
continue;
}
}
else if (pcField.noEnums > 0)
{
setupWork += " Is Enum Field";
enumDropDown(edit, field + offset, field * 24 + 4, 124, comboWidth, value);
form.CapturePanel.Controls.Add(edit.combo);
if (showSetup.Checked == true)
LogDebug = setupWork;
continue;
}
}
value = row != -1 ? data.Rows[row][field].ToString() : "";
if (clearAdd.Checked == true && how == EHow.ADD) value = "";
switch (pcField.type)
{
case DBHandler.PC_CHAR:
case DBHandler.PC_USERSTAMP:
{
setupWork += " Is Text Field";
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
case DBHandler.PC_DATE:
{
setupWork += " Is Date Field";
edit.Date(field * 24 + 4, 124, comboWidth, value);
form.CapturePanel.Controls.Add(edit.date);
}
break;
case DBHandler.PC_DATETIME:
case DBHandler.PC_TIMESTAMP:
{
setupWork += " Is Date Time Field";
edit.Date(field * 24 + 4, 124, comboWidth * 8 / 14, value);
form.CapturePanel.Controls.Add(edit.date);
edit.Time(field * 24 + 4, 124 + edit.date.Width + 4, comboWidth * 6 / 14 - 4, value);
form.CapturePanel.Controls.Add(edit.time);
}
break;
case DBHandler.PC_TIME:
{
setupWork += " Is Time Field";
edit.Time(field * 24 + 4, 124, comboWidth, value);
form.CapturePanel.Controls.Add(edit.time);
}
break;
case DBHandler.PC_BOOLEAN:
{
setupWork += " Is Boolean";
edit.Combo(field * 24 + 4, 124, comboWidth, value);
form.CapturePanel.Controls.Add(edit.combo);
}
break;
case DBHandler.PC_BYTE:
{
setupWork += " Is Byte";
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
case DBHandler.PC_SHORT:
{
setupWork += " Is Short";
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
case DBHandler.PC_DOUBLE:
{
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
case DBHandler.PC_SEQUENCE:
case DBHandler.PC_INT:
{
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
case DBHandler.PC_LONG:
{
edit.Text(field * 24 + 4, 124, editWidth, maxLength(pcField), value);
form.CapturePanel.Controls.Add(edit.text);
}
break;
}
if (showSetup.Checked == true)
LogDebug = setupWork;
}
switch (how)
{
case EHow.ADD:
form.Text = "Add Record for " + pcTable.name;
form.isAdd = true;
form.isChange = false;
form.isDelete = false;
form.isDisplay = false;
break;
case EHow.CHANGE:
form.Text = "Change Record on " + pcTable.name;
form.isAdd = false;
form.isChange = true;
form.isDelete = false;
form.isDisplay = false;
break;
case EHow.DELETE:
form.Text = "Delete Record in " + pcTable.name;
form.isAdd = false;
form.isChange = false;
form.isDelete = true;
form.isDisplay = false;
break;
case EHow.DISPLAY:
form.Text = "Display Record of " + pcTable.name;
form.isAdd = false;
form.isChange = false;
form.isDelete = false;
form.isDisplay = true;
break;
}
DialogResult rc = form.ShowDialog();
if (rc != DialogResult.OK || how == EHow.DISPLAY)
return;
List<string> cells = new List<string>(pcTable.noFields);
for (field = 0; field < pcTable.noFields; field++)
cells.Add(edits[field].Value);
connect.BeginTransaction();
try
{
DBHandler db = new DBHandler(connect);
string sysdate = DateTime.Now.ToString("yyyyMMddhhmmss");
switch (how)
{
case EHow.ADD:
newUsId = userName;
newTmStamp = sysdate;
query = "INSERT INTO " + pcTable.name;
query += " VALUES ";
c = "( ";
for (field = 0; field < pcTable.noFields; field++)
{
query += c + sqlReady(pcFields[offset + field], cells[field], true, pcTable.name);
c = ", ";
}
query += ", '" + userName + "', Sysdate)";
break;
case EHow.CHANGE:
newUsId = userName;
newTmStamp = sysdate;
query = "UPDATE " + pcTable.name;
query += " SET ";
c = "";
for (field = 0; field < pcTable.noFields; field++)
{
if (isKey(pcTable, field) == true)
continue;
query += c + pcFields[offset + field].name + " = "
+ sqlReady(pcFields[offset + field], cells[field]);
c = ", ";
}
query += ", " + db.UsId + " = '" + userName + "', " + db.TmStamp + " = Sysdate";
query += " " + where;
break;
case EHow.DELETE:
query = "DELETE FROM " + pcTable.name;
query += " " + where;
break;
}
using (AutoCursor.DatabaseCall)
{
try
{
if (showSQL.Checked == true)
LogDebug = query;
db.Execute(query);
if (BinTables.HasValidation() == true)
{
if (validateAuditPython(pcTable, how, edits, oldUsId, newUsId, oldTmStamp, newTmStamp) == false)
{
connect.FlagRollback();
return;
}
showTableGrid();
}
}
catch (Exception ex)
{
connect.FlagRollback();
showException(ex);
}
}
}
finally
{
connect.EndTransaction();
}
}
private void fromSelectedClick(object sender, EventArgs e)
{
using (AutoCursor.DatabaseCall)
{
connect.BeginTransaction();
try
{
lastErrorStripLabel.Text = "";
int fromCol = relationFromCol == 0 ? relationFromCol : relationToCol;
int toCol = relationToCol == 0 ? relationFromCol : relationToCol;
int noFromCols = relationFromCol == 0 ? relationNoFromCols : relationNoToCols;
int noToCols = relationToCol == 0 ? relationNoFromCols : relationNoToCols;
int offsetFields = pcRelation.offsetFields;
string fromValue = relationFromValue;
string[] toValues = new string[selectedRelationList.SelectedIndices.Count];
int i = 0;
foreach (int index in selectedRelationList.SelectedIndices)
toValues[i++] = selectedRelationList.Items[index].ToString();
foreach (string toValue in toValues)
{
string[] fromList = relationFromCol == 0 ? fromValue.Split(('|')) : toValue.Split(('|'));
string[] toList = relationToCol == 0 ? fromValue.Split(('|')) : toValue.Split(('|'));
string fieldNames = "[";
string values = "[";
string comma = "";
string command = "DELETE FROM " + pcRelation.name;
string conj = " WHERE ";
for (int c = 0; c < noFromCols; c++)
{
fieldNames += comma + "'" + pcFields[c + offsetFields + fromCol].name + "'";
values += comma + "'" + fromList[c] + "'";
comma = ", ";
command += conj + pcFields[c + offsetFields + fromCol].name + " = '" + fromList[c] + "'";
conj = " AND ";
}
for (int c = 0; c < noToCols; c++)
{
fieldNames += comma + "'" + pcFields[c + offsetFields + toCol].name + "'";
values += comma + "'" + fromList[c] + "'";
command += conj + pcFields[c + offsetFields + toCol].name + " = '" + toList[c] + "'";
conj = " AND ";
}
fieldNames += "]";
values += "]";
DBHandler db = new DBHandler(connect);
db.Execute(command);
if (BinTables.HasValidation() == true)
{
if (validatePython(pcRelation, EHow.DELETE, fieldNames, values) == false)
{
connect.FlagRollback();
return;
}
}
}
listOfRelationsDoubleClick(sender, e);
Refresh();
if (relationFromCol == 0)
{
leftRelationCombo.SelectedIndex = leftRelationCombo.Items.IndexOf(fromValue);
leftRelationComboChange(sender, e);
}
else
{
rightRelationCombo.SelectedIndex = rightRelationCombo.Items.IndexOf(fromValue);
rightRelationComboChange(sender, e);
}
}
catch (Exception ex)
{
showException(ex);
connect.FlagRollback();
}
finally
{
connect.EndTransaction();
}
}
}
private void lookupChange(object sender, EventArgs e)
{
using (AutoCursor.DatabaseCall)
{
try
{
lastErrorStripLabel.Text = "";
DBHandler db = new DBHandler(connect);
setDB(db, pcTable.name);
db.Lookup = "";
string C = " WHERE ";
string MR = "5000";
if (maxRowsEdit.Value > 0)
MR = maxRowsEdit.Value.ToString();
db.Lookup += C + "RowNum <= " + MR;
C = "AND ";
string value = likeEdit.Text.Trim();
if (value.Length > 0)
{
int o = pcTable.offsetFields;
int k = pcTable.offsetKeyFields;
int f = pcKeyFields[k].index + o;
value = value.Replace("'", "''");
int p = value.IndexOf('%');
if (p == 0) p = value.IndexOf('_');
db.Lookup += string.Format("{0} {1} {2} '{3}'", C, pcFields[f].name, p == 0 ? "=" : "LIKE", value);
}
dbLookupLabel.Text = registry["Lookup", pcTable.name] = db.Lookup;
db.GetTable(pcTable.name,
pcFields, pcTable.offsetFields, pcTable.noFields,
pcOrderFields, pcTable.offsetOrderFields, pcTable.noOrderFields);
db.Grid = tablesDataGrid;
}
catch (Exception ex)
{
showException(ex);
}
}
}
private void valueComboChange(object sender, EventArgs e)
{
using (AutoCursor.DatabaseCall)
{
try
{
registry["Value", pcTable.name] = valueCombo.Text;
DBHandler db = new DBHandler(connect);
setDB(db, pcTable.name);
db.Lookup = "";
string C = " WHERE ";
string MR = "5000";
if (maxRowsEdit.Value > 0)
MR = maxRowsEdit.Value.ToString();
db.Lookup += C + "RowNum <= " + MR;
C = "AND ";
string value = valueCombo.Text.Trim();
value = value.Replace("'", "''");
int p = value.IndexOf('%');
if (p == 0) p = value.IndexOf('_');
if (value.Length > 0)
db.Lookup += string.Format("{0} {1} {2} '{3}'", C, lookupCombo.Text, p == 0 ? "=" : "LIKE", value);
db.GetTable(pcTable.name,
pcFields, pcTable.offsetFields, pcTable.noFields,
pcOrderFields, pcTable.offsetOrderFields, pcTable.noOrderFields);
db.Grid = tablesDataGrid;
}
catch (Exception ex)
{
showException(ex);
}
}
}
private void valueComboDropDown(object sender, EventArgs e)
{
using (AutoCursor.DatabaseCall)
{
try
{
lastErrorStripLabel.Text = "";
if (valueCombo.Items.Count > 0) return;
int field;
int noFields = pcTable.noFields;
int offsetFields = pcTable.offsetFields;
for (field = 0; field < noFields; field++)
if (lookupCombo.Text.CompareTo(pcFields[field + offsetFields].name) == 0)
break;
if (field > noFields) return;
DBHandler db = new DBHandler(connect);
db.GetDistinctList(pcTable.name, pcFields[field + offsetFields]);
db.PopulateCombo(valueCombo);
}
catch (Exception ex)
{
showException(ex);
}
}
}
public static void SQLAction(string code)
{
if (connect == null) return;
DBHandler db = new DBHandler(connect);
db.Execute(code);
}
public static string SQLQuery(string code)
{
if (connect == null) return "";
DBHandler db = new DBHandler(connect);
return db.Query(code);
}
private bool sizeIsOk(int link)
{
using (AutoCursor.DatabaseCall)
{
try
{
DBHandler db = new DBHandler(connect);
int tno = pcLinks[link].tableNo;
string name = pcTables[tno].name;
int size = db.GetCount(name);
return size < int.Parse(maxDropdownEdit.Text);
}
catch (Exception ex)
{
showException(ex);
return false;
}
}
}
private void showTableGrid()
{
using (AutoCursor.DatabaseCall)
{
try
{
lastErrorStripLabel.Text = "";
maxRowsEdit.Value = Registry.IntOf(registry["MaxRows", pcTable.name, "0"]);
allowUpdates = pcTable.viewOnly == 0;
setUpdateActions();
DBHandler db = new DBHandler(connect);
setDB(db, pcTable.name);
db.GetTable(pcTable.name,
pcFields, pcTable.offsetFields, pcTable.noFields,
pcOrderFields, pcTable.offsetOrderFields, pcTable.noOrderFields);
tablesDataGrid.Columns.Clear();
db.Grid = tablesDataGrid;
clearAdd.Checked = registry["ClearAdd", pcTable.name, "False"] == "True" ? true : false;
if (pcTable.noOrderFields > 1)
{
lookupCombo.Visible = true;
lookupLabel.Visible = true;
valueCombo.Visible = true;
valueLabel.Visible = true;
doValueButton.Visible = true;
likeLabel.Visible = false;
likeEdit.Visible = false;
doButton.Visible = false;
lookupCombo.Items.Clear();
valueCombo.Items.Clear();
int o = pcTable.offsetFields;
for (int j = 0; j < pcTable.noOrderFields; j++)
{
int k = j + pcTable.offsetOrderFields;
int f = pcOrderFields[k].index + o;
lookupCombo.Items.Add(pcFields[f].name);
}
lookupCombo.Text = registry["LookupKey", pcTable.name, ""];
valueCombo.Text = registry["LookupValue", pcTable.name, ""];
}
else
{
lookupCombo.Visible = false;
lookupLabel.Visible = false;
valueCombo.Visible = false;
valueLabel.Visible = false;
doValueButton.Visible = false;
likeLabel.Visible = true;
likeEdit.Visible = true;
doButton.Visible = true;
likeEdit.Text = registry["Like", pcTable.name, ""];
}
}
catch (Exception ex)
{
showException(ex);
}
}
}
private void showRelationsGrid()
{
using (AutoCursor.DatabaseCall)
{
try
{
lastErrorStripLabel.Text = "";
DBHandler db = new DBHandler(connect);
int offsetFields = pcRelation.offsetFields;
int noFields = pcRelation.noFromFields + pcRelation.noToFields;
db.GetTable(pcRelation.name,
pcFields, offsetFields, noFields,
pcOrderFields, 0, 0);
db.Grid = relationDataGrid;
populateCombo(leftRelationCombo, pcRelation.fromTable);
leftTableLabel.Text = pcTables[pcRelation.fromTable].name;
populateCombo(rightRelationCombo, pcRelation.toTable);
rightTableLabel.Text = pcTables[pcRelation.toTable].name;
availableRelationList.Items.Clear();
selectedRelationList.Items.Clear();
}
catch (Exception ex)
{
showException(ex);
}
}
}
private void setDB(DBHandler db, string tableName)
{
dbLookupLabel.Text = db.Lookup = registry["Lookup", tableName, ""];
db.UsId = registry["UsId", tableName, "UsId"];
db.TmStamp = registry["TmStamp", tableName, "TmStamp"];
}
private void populateCombo(ComboBox combo, int tableIndex)
{
using (AutoCursor.DatabaseCall)
{
try
{
TPCTable pcTable = pcTables[tableIndex];
lastErrorStripLabel.Text = "";
DBHandler db = new DBHandler(connect);
setDB(db, pcTable.name);
db.Lookup = "";
db.GetTable(pcTable.name,
pcFields, pcTable.offsetFields, pcTable.noFields,
pcOrderFields, pcTable.offsetOrderFields, pcTable.noOrderFields);
db.PopulateCombo(combo, pcKeyFields, pcTable.offsetKeyFields, pcTable.noKeyFields);
}
catch (Exception ex)
{
showException(ex);
}
}
}
