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", "0" }
, { "failed", "0" }
, { "savedIds", "" }
, { "failedIds", "" }
, { "error", "" }
};
bool exists = false, saved = false;
int savedQty = 0, failedQty = 0;
string ids = string.Empty, failedIds = 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;
}
else
{
int id = 0, allocationID = 0, proposedPriority = 0, approvedPriority = 0, systemID = 0, archive = 0;
bool duplicate = false;
string description = string.Empty;
HttpServerUtility server = HttpContext.Current.Server;
//save
foreach (DataRow dr in dtjson.Rows)
{
id = systemID = allocationID = 0;
proposedPriority = approvedPriority = archive = 0;
description = string.Empty;
archive = 0;
duplicate = false;
tempMsg = string.Empty;
int.TryParse(dr["Allocation_SystemID"].ToString(), out id);
int.TryParse(dr["ALLOCATION"].ToString(), out allocationID);
int.TryParse(dr["WTS_SYSTEM"].ToString(), out systemID);
description = server.UrlDecode(dr["DESCRIPTION"].ToString());
int.TryParse(dr["ProposedPriority"].ToString(), out proposedPriority);
int.TryParse(dr["ApprovedPriority"].ToString(), out approvedPriority);
int.TryParse(dr["ARCHIVE"].ToString(), out archive);
if (allocationID == 0)
{
tempMsg = "You must specify a Allocation Assignment.";
saved = false;
}
else
{
if (id == 0)
{
exists = false;
saved = MasterData.Allocation_System_Add(allocationID: allocationID
, systemID: systemID
, description: description
, proposedPriority: proposedPriority
, approvedPriority: approvedPriority
, exists: out exists
, newID: out id
, errorMsg: out tempMsg);
if (exists)
{
saved = false;
tempMsg = string.Format("{0}{1}{2}", tempMsg, tempMsg.Length > 0 ? Environment.NewLine : "", "Cannot add duplicate WorkType - Status record.");
}
}
else
{
saved = MasterData.Allocation_System_Update(allocationSystemID: id
, allocationID: allocationID
, systemID: systemID
, description: description
, proposedPriority: proposedPriority
, approvedPriority: approvedPriority
, archive: (archive == 1)
, duplicate: out duplicate
, errorMsg: out tempMsg);
}
}
if (saved)
{
ids += string.Format("{0}{1}", ids.Length > 0 ? "," : "", id.ToString());
savedQty += 1;
}
else
{
failedQty += 1;
}
if (tempMsg.Length > 0)
{
errorMsg = string.Format("{0}{1}{2}", errorMsg, errorMsg.Length > 0 ? Environment.NewLine : "", tempMsg);
}
}
}
}
catch (Exception ex)
{
saved = false;
errorMsg = ex.Message;
LogUtility.LogException(ex);
}
result["savedIds"] = ids;
result["failedIds"] = failedIds;
result["saved"] = savedQty.ToString();
result["failed"] = failedQty.ToString();
result["error"] = errorMsg;
return(JsonConvert.SerializeObject(result, Formatting.None));
}