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