Have you considered using a wiki? They natively support versioning.
failing that.....
you basically need a diff display,
you can't do that with either the whole previous document stored somewhere,
or if each item on a snapshot can maintain a unique identifier
then it is very easy to use SQL and just keep versions.
But when it comes to parts lists there isn't a whole lot of experience out there with
"In Process" identifiers being different than "balloon numbers" on drawings or part numbers
on exploded views.
Anyways if you can save versions AND maintain unique line item identifiers you can actually use a simple
list view to display the delta(diff).
I did one with two list views as version pickers and one list view as the DIFF display
Then call a stored procedure that accepts as parameters a @leftversion and @rightversion
CREATE PROC tool_Get_Bom_Versions_for_Diff
(@leftversion int = 1, @rightversion int = 1, @sono varchar(6) = 'C9999')
AS SET NOCOUNT ON
-- DECLARE @leftversion int
-- DECLARE @rightversion int
-- DECLARE @sono varchar(6)
-- SET @leftversion = 10
-- SET @rightversion = 11
-- SET @sono = 'C2729'
SELECT
externalballoon,
CASE WHEN laguid is null OR raguid is null THEN 1
ELSE 0
END
as deleteaddflag,
CASE WHEN lprocess <rprocess THEN 1
ELSE 0
END
as processmodified,
CASE WHEN lqty <rqty THEN 1
ELSE 0
END as qtymodified,
CASE WHEN lpartno <rpartno THEN 1
ELSE 0
END as partnomodified,
CASE WHEN lpartnodesc <rpartnodesc THEN 1
ELSE 0
END as partnodescmodified,
CASE WHEN lneeddate <rneeddate THEN 1
ELSE 0
END as needdatemodified,
lprocess, lqty, lpartno, lpartnodesc, CONVERT(char(10),lneeddate, 101) as lneeddate, laguid,
rprocess, rqty, rpartno, rpartnodesc, convert(char(10),rneeddate, 101) as rneeddate, raguid
FROM
(
SELECT Z.externalballoon,
Y.process as lprocess, Y.qty as lqty, Y.partno as lpartno, Y.partnodesc as lpartnodesc, Y.needdate as lneeddate, Y.aguid as laguid,
X.process as rprocess, X.qty as rqty, X.partno as rpartno, X.partnodesc as rpartnodesc, X.needdate as rneeddate, X.aguid as raguid
FROM
(
SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion
UNION
SELECT B.externalballoon from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
Z
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion
)
Y
ON
Z.externalballoon = Y.externalballoon
LEFT JOIN
(
SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @rightversion
)
X
ON
Z.externalballoon = X.externalballoon
)
J
The idea is to include a double wide result set, the Y and Z alias tables where Z is the In process unique ID.
Then across all the In process unique ID's in BOTH versions, do a conditional comparison for each column that represents
an attribute of that part. Then each row has a binary flag that represents a change has happened across that attribute.
then in the Windows form
a click to display
which sets up the call to the double wide result set and
sets the grid display iteratively.
then bild a lillte legend with some labels at the bottom that explain what each little colored cell means
like deleted, or qty change etc.
private void uxbtnCADBOMVersionDiffCompare_Click(object sender, System.EventArgs e)
{
//tool_Get_CadBom_Versions_for_Diff
int leftaguid = 0;
int rightaguid = 0;
if (uxlistviewCADBOMVersionDiffLeftPicker.SelectedIte ms.Count == 0 || uxlistviewCADBOMVersionDiffRightPicker.SelectedIte ms.Count
== 0)
{
MessageBox.Show("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffLeftPic ker.SelectedItems[0].Text.ToString());
Debug.WriteLine(leftaguid);
rightaguid = Convert.ToInt32(uxlistviewCADBOMVersionDiffRightPi cker.SelectedItems[0].Text.ToString());
Debug.WriteLine(rightaguid);
uxlistviewCADBOMVersionDiff.Items.Clear();
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@sono"].Value = bomroot.strsono;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@leftversion"].Value = leftaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Para meters["@rightversion"].Value = rightaguid;
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.Open();
System.Data.SqlClient.SqlDataReader dr =
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Exec uteReader(CommandBehavior.CloseConnection);
DataUtils.DataReaderAdapter dra = new DataUtils.DataReaderAdapter();
DataTable dtDiff = new DataTable();
dra.FillFromReader(dtDiff,dr);
dr.Close();
if (cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Con nection.State.ToString() == "Open")
{
cadbomsProcs.cmd_Get_CadBom_Versions_for_Diff.Conn ection.Close();
}
foreach(DataRow row in dtDiff.Rows)
{
ListViewItem item4 = new ListViewItem(row["xxxID"].ToString());
item4.UseItemStyleForSubItems = false;
item4.SubItems.Add(row["lprocess"].ToString());
item4.SubItems.Add(row["lqty"].ToString());
item4.SubItems.Add(row["lpartno"].ToString());
item4.SubItems.Add(row["lpartnodesc"].ToString());
item4.SubItems.Add(row["lneeddate"].ToString());
item4.SubItems.Add("-");
item4.SubItems.Add(row["rprocess"].ToString());
item4.SubItems.Add(row["rqty"].ToString());
item4.SubItems.Add(row["rpartno"].ToString());
item4.SubItems.Add(row["rpartnodesc"].ToString());
item4.SubItems.Add(row["rneeddate"].ToString());
if(row["deleteflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}
if(row["addflag"].ToString() == "1")
{
item4.SubItems[0].BackColor = Color.Red;
item4.SubItems[6].BackColor = Color.Red;
}
if(row["processmodified"].ToString() == "1")
{
item4.SubItems[1].BackColor = Color.LightGreen;
item4.SubItems[7].BackColor = Color.LightGreen;
}
if(row["qtymodified"].ToString() == "1")
{
//item4.BackColor = Color.LightBlue;
item4.SubItems[2].BackColor = Color.LightBlue;
item4.SubItems[8].BackColor = Color.LightBlue;
}
if(row["partnomodified"].ToString() == "1")
{
item4.SubItems[3].BackColor = Color.HotPink;
item4.SubItems[9].BackColor = Color.HotPink;
}
if(row["partnodescmodified"].ToString() == "1")
{
item4.SubItems[4].BackColor = Color.LightPink;
item4.SubItems[10].BackColor = Color.LightPink;
}
if(row["needdatemodified"].ToString() == "1")
{
item4.SubItems[5].BackColor = Color.Yellow;
item4.SubItems[11].BackColor = Color.Yellow;
}
uxlistviewCADBOMVersionDiff.Items.Add(item4);
}
}
}
Maybe this can give you some ideas, But keep in mind. as the parts list changes over time, each line item
has to be unique. That can be done only two ways.
With multiple whole documents where line position and content
define it, That is exactly what the diff view in Visual Source Safe displays to you.
OR
by making a commitment that once a line item is included on a parts list it has it's own identity forever.
Subsequent versions of that parts list can then be compared.
<ma**@mailinator.comwrote in message news:11**********************@e3g2000cwe.googlegro ups.com...
any other ideas or suggestions, folks?
thanks!
matt
