private bool FixPastelCustomerCategories(string dataPath, List <CustomerCategory> categories)
{
try
{
var allowedIds = categories.Select(a => a.CategoryId).ToArray();
string ids = "(" + string.Join(",", allowedIds) + ")";
var catListOnDB = GetCustomerCategories(dataPath);
var listToUpdate = new List <CustomerCategory>();
var listToInsert = new List <CustomerCategory>();
foreach (var cat in categories)
{
var curr = catListOnDB.FirstOrDefault(a => a.CategoryId == cat.CategoryId);
if (curr == null)
{
listToInsert.Add(cat);
}
else if (curr.CategoryName != cat.CategoryName)
{
listToUpdate.Add(cat);
}
}
foreach (var i in listToInsert)
{
string q = "Insert into [DataSet].CustomerCategories (CCCode,CCDesc) values (" + i.CategoryId.ToString() + ",'" + i.CategoryName + "')";
q = PervasiveSqlUtilities.SetDataSource(q, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(q);
}
foreach (var i in listToUpdate)
{
Console.WriteLine("Update " + i.CategoryName + " for " + dataPath);
string q = "Update [DataSet].CustomerCategories set CCDesc = '" + i.CategoryName + "' where CCCode = " + i.CategoryId.ToString();
q = PervasiveSqlUtilities.SetDataSource(q, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(q);
}
string odbcQuery = "update [DataSet].CustomerMaster set Category = 0 where Category not in " + ids;
odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery);
odbcQuery = "delete from [DataSet].CustomerCategories where CCCode not in " + ids;
odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath);
PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery);
return(true);
}
catch (Exception e)
{
_Context.SystemLogSet.Add(new Data.Log.SystemLog()
{
EventTime = DateTime.Now,
Message = e.Message,
StackTrace = e.StackTrace
});
}
return(false);
}
public void UpdateNoneCategory(string sql)
{
PervasiveSqlUtilities.ExecuteSQLCommand(sql);
}