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_Ve rsions_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 partnodescmodif ied,
CASE WHEN lneeddate <rneeddate THEN 1
ELSE 0
END as needdatemodifie d,
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.externalballo on,
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.externalballo on from engversion A
INNER JOIN engboms B
ON A.aguid = B.aguid
WHERE B.sono = @sono
AND
A.bomversion = @leftversion
UNION
SELECT B.externalballo on 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.externalballo on, 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.externalballo on = Y.externalballo on
LEFT JOIN
(
SELECT B.externalballo on, 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.externalballo on = X.externalballo on
)
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 uxbtnCADBOMVers ionDiffCompare_ Click(object sender, System.EventArg s e)
{
//tool_Get_CadBom _Versions_for_D iff
int leftaguid = 0;
int rightaguid = 0;
if (uxlistviewCADB OMVersionDiffLe ftPicker.Select edItems.Count == 0 || uxlistviewCADBO MVersionDiffRig htPicker.Select edItems.Count
== 0)
{
MessageBox.Show ("highlight two versions...\n\n ...one in each list");
}
else
{
leftaguid = Convert.ToInt32 (uxlistviewCADB OMVersionDiffLe ftPicker.Select edItems[0].Text.ToString( ));
Debug.WriteLine (leftaguid);
rightaguid = Convert.ToInt32 (uxlistviewCADB OMVersionDiffRi ghtPicker.Selec tedItems[0].Text.ToString( ));
Debug.WriteLine (rightaguid);
uxlistviewCADBO MVersionDiff.It ems.Clear();
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .Parameters["@sono"].Value = bomroot.strsono ;
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .Parameters["@leftversi on"].Value = leftaguid;
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .Parameters["@rightvers ion"].Value = rightaguid;
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .Connection.Ope n();
System.Data.Sql Client.SqlDataR eader dr =
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .ExecuteReader( CommandBehavior .CloseConnectio n);
DataUtils.DataR eaderAdapter dra = new DataUtils.DataR eaderAdapter();
DataTable dtDiff = new DataTable();
dra.FillFromRea der(dtDiff,dr);
dr.Close();
if (cadbomsProcs.c md_Get_CadBom_V ersions_for_Dif f.Connection.St ate.ToString() == "Open")
{
cadbomsProcs.cm d_Get_CadBom_Ve rsions_for_Diff .Connection.Clo se();
}
foreach(DataRow row in dtDiff.Rows)
{
ListViewItem item4 = new ListViewItem(ro w["xxxID"].ToString());
item4.UseItemSt yleForSubItems = false;
item4.SubItems. Add(row["lprocess"].ToString());
item4.SubItems. Add(row["lqty"].ToString());
item4.SubItems. Add(row["lpartno"].ToString());
item4.SubItems. Add(row["lpartnodes c"].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["rpartnodes c"].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["processmodifie d"].ToString() == "1")
{
item4.SubItems[1].BackColor = Color.LightGree n;
item4.SubItems[7].BackColor = Color.LightGree n;
}
if(row["qtymodifie d"].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["partnodescmodi fied"].ToString() == "1")
{
item4.SubItems[4].BackColor = Color.LightPink ;
item4.SubItems[10].BackColor = Color.LightPink ;
}
if(row["needdatemodifi ed"].ToString() == "1")
{
item4.SubItems[5].BackColor = Color.Yellow;
item4.SubItems[11].BackColor = Color.Yellow;
}
uxlistviewCADBO MVersionDiff.It ems.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**@mailinato r.comwrote in message news:11******** **************@ e3g2000cwe.goog legroups.com...
any other ideas or suggestions, folks?
thanks!
matt
