public static List <String> WorkoutDeltaForItemUpdateSqlString(FinanceDocumentUIViewModel oldDoc, FinanceDocumentUIViewModel newDoc)
{
var diffs = WorkoutDeltaForItemUpdate(oldDoc, newDoc);
List <String> listItemSqls = new List <string>();
foreach (var diff in diffs)
{
if (diff.Value == null)
{
var oitem = oldDoc.Items.Find(o => o.ItemID == diff.Key);
listItemSqls.Add(@"DELETE FROM [dbo].[t_fin_document_item] WHERE [DOCID] = " + oldDoc.ID.ToString() + " AND [ITEMID] = " + diff.Key.ToString());
// If there are tags, need delete them too
if (oitem.TagTerms.Count > 0)
{
listItemSqls.Add(@"DELETE FROM [dbo].[t_tag] WHERE [HID] = " + oldDoc.HID.ToString()
+ " AND [TagType] = " + ((Int32)(HIHTagTypeEnum.FinanceDocumentItem)).ToString()
+ " AND [TagID] = " + oldDoc.ID.ToString()
+ " AND [TagSubID] = " + diff.Key.ToString());
}
}
else if (diff.Value is FinanceDocumentItemUIViewModel)
{
listItemSqls.Add((diff.Value as FinanceDocumentItemUIViewModel).GetDocItemInsertString());
// Tags
if ((diff.Value as FinanceDocumentItemUIViewModel).TagTerms.Count > 0)
{
listItemSqls.AddRange((diff.Value as FinanceDocumentItemUIViewModel).GetDocItemTagInsertString(oldDoc.HID));
}
}
else
{
// listItemSqls
listItemSqls.AddRange(FinanceDocumentItemUIViewModel.WorkoutDeltaUpdateSqlStrings(oldDoc.Items.Find(o => o.ItemID == diff.Key),
newDoc.Items.Find(o => o.ItemID == diff.Key), oldDoc.HID));
}
}
return(listItemSqls);
}
public static List <String> WorkoutDeltaUpdateSqlStrings(FinanceDocumentItemUIViewModel oldItem, FinanceDocumentItemUIViewModel newItem, Int32 hid)
{
List <String> listRst = new List <string>();
List <String> listProp = new List <string>();
var diffs = WorkoutDeltaUpdate(oldItem, newItem);
foreach (var diff in diffs)
{
if (diff.Key != "TagTerms")
{
if (diff.Value is DateTime)
{
listProp.Add("[" + diff.Key.ToString() + "] = " + ((DateTime)diff.Value).ToString("YYYY-MM-SS"));
}
else if (diff.Value is Boolean)
{
listProp.Add("[" + diff.Key.ToString() + "] = " + (((Boolean)diff.Value) ? "1" : "0"));
}
else if (diff.Value is String)
{
listProp.Add("[" + diff.Key.ToString() + "] = N'" + diff.Value + "'");
}
else if (diff.Value is Int32)
{
if ((diff.Key == "ControlCenterID" || diff.Key == "OrderID") && (Int32)diff.Value == 0)
{
listProp.Add("[" + diff.Key.ToString() + "] = NULL");
}
else
{
listProp.Add("[" + diff.Key.ToString() + "] = " + diff.Value.ToString());
}
}
else
{
listProp.Add("[" + diff.Key.ToString() + "] = " + diff.Value.ToString());
}
}
else
{
if (diff.Value == null)
{
// Delete
listRst.Add(@"DELETE FROM [dbo].[t_tag] WHERE [HID] = " + hid.ToString()
+ " AND [TagType] = " + ((Int32)(HIHTagTypeEnum.FinanceDocumentItem)).ToString()
+ " AND [TagID] = " + oldItem.DocID.ToString()
+ " AND [TagSubID] = " + oldItem.ItemID.ToString());
}
else if (diff.Value is List <String> )
{
// Insert
foreach (var term in (List <String>)diff.Value)
{
listRst.Add(@"INSERT INTO [dbo].[t_tag] ([HID],[TagType],[TagID],[TagSubID],[Term]) VALUES ("
+ string.Join(",", new string[] {
hid.ToString(),
((Int32)(HIHTagTypeEnum.FinanceDocumentItem)).ToString(),
oldItem.DocID.ToString(),
oldItem.ItemID.ToString(),
"N'" + term + "'"
})
+ ")");
}
}
else
{
var dictStrs = (Dictionary <String, Object>)diff.Value;
foreach (var dictstr in dictStrs)
{
if (dictstr.Key == "D")
{
var listterms = (List <String>)dictstr.Value;
foreach (var term in listterms)
{
listRst.Add(@"DELETE FROM [dbo].[t_tag] WHERE [HID] = " + hid.ToString()
+ " AND [TagType] = " + ((Int32)(HIHTagTypeEnum.FinanceDocumentItem)).ToString()
+ " AND [TagID] = " + oldItem.DocID.ToString()
+ " AND [TagSubID] = " + oldItem.ItemID.ToString()
+ " AND [Term] = N'" + term + "')");
}
}
else if (dictstr.Key == "I")
{
var listterms = (List <String>)dictstr.Value;
foreach (var term in listterms)
{
listRst.Add(@"INSERT INTO [dbo].[t_tag] ([HID],[TagType],[TagID],[TagSubID],[Term]) VALUES ("
+ string.Join(",", new string[] {
hid.ToString(),
((Int32)(HIHTagTypeEnum.FinanceDocumentItem)).ToString(),
oldItem.DocID.ToString(),
oldItem.ItemID.ToString(),
"N'" + term + "'"
})
+ ")");
}
}
}
}
}
}
if (listProp.Count > 0)
{
listRst.Add(@"UPDATE [dbo].[t_fin_document_item] SET " + string.Join(", ", listProp)
+ " WHERE [DocID] = " + oldItem.DocID.ToString() + " AND [ItemID] = " + oldItem.ItemID.ToString());
}
return(listRst);
}
public static Dictionary <String, Object> WorkoutDeltaUpdate(FinanceDocumentItemUIViewModel oldItem, FinanceDocumentItemUIViewModel newItem)
{
Dictionary <String, Object> dictDelta = new Dictionary <string, Object>();
if (oldItem == null || newItem == null ||
Object.ReferenceEquals(oldItem, newItem) ||
oldItem.DocID != newItem.DocID ||
oldItem.ItemID != newItem.ItemID)
{
throw new ArgumentException("Invalid inputted parameters Or DocID/ItemID is different!");
}
Type t = typeof(FinanceDocumentItemUIViewModel);
Type parent = typeof(FinanceDocumentItemViewModel);
PropertyInfo[] parentProperties = parent.GetProperties();
Dictionary <String, Object> dictParentProperties = new Dictionary <string, object>();
foreach (var prop in parentProperties)
{
dictParentProperties.Add(prop.Name, null);
}
PropertyInfo[] listProperties = t.GetProperties();
var listSortedProperties = listProperties.OrderBy(o => o.Name);
foreach (PropertyInfo item in listSortedProperties)
{
// Only care about the properties in the parent class
if (!dictParentProperties.ContainsKey(item.Name))
{
continue;
}
if (item.Name == "DocID")
{
continue;
}
if (item.Name != "TagTerms")
{
object oldValue = item.GetValue(oldItem);
object newValue = item.GetValue(newItem);
if (item.PropertyType == typeof(Decimal))
{
if (Decimal.Compare((Decimal)oldValue, (Decimal)newValue) != 0)
{
dictDelta.Add(item.Name, newValue);
}
}
else if (item.PropertyType == typeof(String))
{
if (String.CompareOrdinal((string)oldValue, (string)newValue) != 0)
{
dictDelta.Add(item.Name, newValue);
}
}
else
{
if (!Object.Equals(oldValue, newValue))
{
dictDelta.Add(item.Name, newValue);
}
}
}
else
{
// TagTerms
if (oldItem.TagTerms.Count == 0 && newItem.TagTerms.Count > 0)
{
// Just add the new tags
dictDelta.Add(item.Name, newItem.TagTerms);
}
else if (oldItem.TagTerms.Count > 0 && newItem.TagTerms.Count == 0)
{
// Just delete the existing tags
dictDelta.Add(item.Name, null);
}
else if (oldItem.TagTerms.Count > 0 && newItem.TagTerms.Count > 0)
{
Dictionary <String, Int32> tagids = new Dictionary <String, Int32>();
oldItem.TagTerms.ForEach(o => tagids.Add(o, 1));
newItem.TagTerms.ForEach(o =>
{
if (tagids.ContainsKey(o))
{
tagids[o] = 2;
}
else
{
tagids.Add(o, 3);
}
});
Dictionary <String, Object> dictTagDelta = new Dictionary <string, Object>();
List <String> listDeletes = new List <string>();
List <String> listInserts = new List <string>();
foreach (var tagid in tagids)
{
if (tagid.Value == 1)
{
// Need be delete
listDeletes.Add(tagid.Key);
}
else if (tagid.Value == 3)
{
// Need be insert
listInserts.Add(tagid.Key);
}
}
if (listInserts.Count > 0 || listDeletes.Count > 0)
{
if (listDeletes.Count > 0)
{
dictTagDelta.Add("D", listDeletes);
}
if (listInserts.Count > 0)
{
dictTagDelta.Add("I", listInserts);
}
dictDelta.Add(item.Name, dictTagDelta);
}
}
}
}
return(dictDelta);
}
public static Dictionary <Int32, Object> WorkoutDeltaForItemUpdate(FinanceDocumentUIViewModel oldDoc, FinanceDocumentUIViewModel newDoc)
{
Dictionary <Int32, Object> dictDelta = new Dictionary <Int32, Object>();
if (oldDoc == null || newDoc == null || Object.ReferenceEquals(oldDoc, newDoc) ||
oldDoc.ID != newDoc.ID || oldDoc.HID != newDoc.HID || oldDoc.DocType != newDoc.DocType)
{
throw new ArgumentException("Invalid inputted parameter Or ID/HID/DocType change is not allowed");
}
if (!oldDoc.IsValid() || !newDoc.IsValid())
{
throw new Exception("Document is invalid");
}
// Items
Dictionary <Int32, Int32> itemids = new Dictionary <int, Int32>();
oldDoc.Items.ForEach(o => itemids.Add(o.ItemID, 1));
newDoc.Items.ForEach(o =>
{
if (itemids.ContainsKey(o.ItemID))
{
itemids[o.ItemID] = 2;
}
else
{
itemids.Add(o.ItemID, 3);
}
});
// Only left: 1
// Both: 2
// Only right: 3
foreach (var itemid in itemids)
{
if (itemid.Value == 1)
{
// ONLY left, DELETE
var item = oldDoc.Items.Find(o => o.ItemID == itemid.Key);
dictDelta.Add(itemid.Key, null);
}
else if (itemid.Value == 2)
{
var item1 = oldDoc.Items.Find(o => o.ItemID == itemid.Key);
var item2 = newDoc.Items.Find(o => o.ItemID == itemid.Key);
var diffs = FinanceDocumentItemUIViewModel.WorkoutDeltaUpdate(item1, item2);
if (diffs.Count > 0)
{
dictDelta.Add(itemid.Key, diffs);
}
}
else if (itemid.Value == 3)
{
// Only right, INSERT!
var item = newDoc.Items.Find(o => o.ItemID == itemid.Key);
dictDelta.Add(itemid.Key, item);
}
}
return(dictDelta);
}