public static void ImportRaw() { bool exists, saved, updated; int id, allocationID, allocationGroupID; int systemID = 0; string outMsg; string sqlStatement; //string AllocationAssignment = ""; try { DataTable dtImportTable = new DataTable(); WriteToLogTable("About to start Raw Import."); sqlStatement = "SELECT DISTINCT [System] FROM Steve_Allocation WHERE [System] NOT IN (SELECT WTS_SYSTEM from WTS_SYSTEM)"; dtImportTable = GetGeneric(WTSCommon.WTS_ConnectionString, sqlStatement, null); // Add any missing WTS_SYSTEM entries. foreach (DataRow drImportTable in dtImportTable.Rows) { //=============================== saved = MasterData.WTS_System_Add(drImportTable["System"].ToString(), drImportTable["System"].ToString(), 0, out exists, newID: out id); //=============================== } WriteToLogTable("WTS_System entries done."); // Add all Allocation Groups sqlStatement = "SELECT DISTINCT [Allocation Group] FROM Steve_Allocation WHERE [Allocation Group] NOT IN (SELECT ALLOCATIONGROUP FROM ALLOCATIONGROUP)"; dtImportTable = GetGeneric(WTSCommon.WTS_ConnectionString, sqlStatement, null); foreach (DataRow drImportTable in dtImportTable.Rows) { //==================================== saved = MasterData.AllocationGroup_Add(drImportTable["Allocation Group"].ToString(), drImportTable["Allocation Group"].ToString(), "", 0, true, false, out exists, out allocationGroupID, out outMsg); //==================================== } WriteToLogTable("Allocation Groups done."); // Add all Allocations & Allocation_Systems sqlStatement = "SELECT [System], [Allocation Group], [Allocation Assignment] FROM Steve_Allocation"; dtImportTable = GetGeneric(WTSCommon.WTS_ConnectionString, sqlStatement, null); foreach (DataRow drImportTable in dtImportTable.Rows) { sqlStatement = "SELECT ALLOCATIONGROUPID FROM ALLOCATIONGROUP WHERE ALLOCATIONGROUP = '" + drImportTable["Allocation Group"].ToString() + "'"; allocationGroupID = GetScalarInt(sqlStatement); //================================ saved = MasterData.Allocation_Add(0, allocationGroupID, allocation: drImportTable["Allocation Assignment"].ToString(), description: drImportTable["Allocation Assignment"].ToString() , defaultAssignedToID: 67, defaultSMEID: 68, defaultBusinessResourceID: 68, defaultTechnicalResourceID: 67 , sortOrder: 0, archive: false, exists: out exists, newID: out allocationID, errorMsg: out outMsg); //================================ // Update any missed in prior run. sqlStatement = "UPDATE Allocation SET AllocationGroupID = allocationGroupID WHERE Allocation = '" + drImportTable["Allocation Assignment"].ToString() + "' AND AllocationGroupID IS NULL"; updated = ExecuteCommand(sqlStatement); sqlStatement = "SELECT AllocationID FROM Allocation WHERE Allocation = '" + drImportTable["Allocation Assignment"].ToString() + "'"; allocationID = GetScalarInt(sqlStatement); sqlStatement = "SELECT WTS_SYSTEMID FROM WTS_SYSTEM WHERE WTS_SYSTEM = '" + drImportTable["System"].ToString() + "'"; systemID = GetScalarInt(sqlStatement); if (allocationID > 0 & systemID > 0) { try { //====================================== saved = MasterData.Allocation_System_Add(allocationID: allocationID, systemID: systemID, description: "", proposedPriority: 0, approvedPriority: 0 , exists: out exists, newID: out id, errorMsg: out outMsg); //====================================== } catch (Exception e) { WriteToLogTable("Error in Allocation_System_Add. Error: " + e.Message); } } else { if (allocationID < 1) { WriteToLogTable("AllocationID returned a 0 for Allocation Assignment " + drImportTable["Allocation Assignment"].ToString()); } WriteToLogTable("SystemID returned a 0 for System " + drImportTable["System"].ToString()); if (systemID < 1) { } } } WriteToLogTable("Allocations and Allocation_Systems done."); } catch (Exception e) { WriteToLogTable("Raw Import error: " + e.Message); } }
public static string SaveChanges(string rows) { Dictionary <string, string> result = new Dictionary <string, string>() { { "saved", "" }, { "ids", "" }, { "error", "" } }; bool exists = false, saved = false; string ids = string.Empty, errorMsg = string.Empty, tempMsg = string.Empty; try { DataTable dtjson = (DataTable)JsonConvert.DeserializeObject(rows, (typeof(DataTable))); if (dtjson.Rows.Count == 0) { errorMsg = "Unable to save. An invalid list of changes was provided."; saved = false; } int id = 0, priorty = 0, dailyMeetings = 0, archive = 0; string allocationGroup = string.Empty, description = string.Empty, notes = string.Empty; HttpServerUtility server = HttpContext.Current.Server; //save foreach (DataRow dr in dtjson.Rows) { id = 0; allocationGroup = string.Empty; description = string.Empty; notes = string.Empty; priorty = 0; dailyMeetings = 0; archive = 0; tempMsg = string.Empty; int.TryParse(dr["ALLOCATIONGROUPID"].ToString(), out id); allocationGroup = Uri.UnescapeDataString(dr["ALLOCATIONGROUP"].ToString()); description = Uri.UnescapeDataString(dr["DESCRIPTION"].ToString()); notes = Uri.UnescapeDataString(dr["NOTES"].ToString()); int.TryParse(dr["PRIORTY"].ToString(), out priorty); int.TryParse(dr["DAILYMEETINGS"].ToString(), out dailyMeetings); int.TryParse(dr["ARCHIVE"].ToString(), out archive); if (string.IsNullOrWhiteSpace(allocationGroup)) { tempMsg = "You must specify a value for allocation group."; saved = false; } else { if (id == 0) { exists = false; saved = MasterData.AllocationGroup_Add(allocationGroup, description, notes, priorty, dailyMeetings == 1, archive == 1, out exists, out id, out tempMsg); if (exists) { saved = false; tempMsg = string.Format("{0}{1}{2}", tempMsg, tempMsg.Length > 0 ? Environment.NewLine : "", "Cannot add duplicate Allocation Group record [" + allocationGroup + "]."); } } else { saved = MasterData.AllocationGroup_Update(id, allocationGroup, description, notes, priorty, dailyMeetings == 1, archive == 1, out tempMsg); } } if (saved) { ids += string.Format("{0}{1}", ids.Length > 0 ? "," : "", id.ToString()); } if (tempMsg.Length > 0) { errorMsg = string.Format("{0}{1}{2}", errorMsg, errorMsg.Length > 0 ? Environment.NewLine : "", tempMsg); } } } catch (Exception ex) { LogUtility.LogException(ex); saved = false; errorMsg = ex.Message; } result["saved"] = saved.ToString(); result["error"] = errorMsg; return(JsonConvert.SerializeObject(result, Formatting.None)); }