public void run(string regionCode, string subtype)
{
var desa = dbContext.SidekaDesa.FirstOrDefault(s => s.Kode == regionCode);
var pbdt = dbContext.Pbdt2015.FirstOrDefault(s => s.RegionCode == regionCode);
var content = new SidekaContent()
{
DesaId = desa.BlogId,
ApiVersion = configuration.GetValue <string>("ApiVersion"),
ChangeId = 1,
Type = "Kemiskinan",
Content = pbdt.Content,
DiffSize = 0,
Subtype = subtype,
ContentSize = ASCIIEncoding.Unicode.GetByteCount(pbdt.Content)
};
dbContext.Set <SidekaContent>().Add(content);
dbContext.SaveChanges();
}
public void Run(string rootDirectory, string fileName, string kabupatenCode)
{
dbContext.SidekaRegion.Take(10);
var idvFile = Path.Combine(rootDirectory, "1. JATENG_IDV", fileName + "_IDV.xlsx");
if (!File.Exists(idvFile))
{
throw new Exception(idvFile + "Not exists");
}
var rtFile = Path.Combine(rootDirectory, "2. JATENG_RT", fileName + "_RT.xlsx");
if (!File.Exists(rtFile))
{
throw new Exception(rtFile + "Not exists");
}
var byDesa = new Dictionary <String, Tuple <List <object[]>, List <object[]> > >();
var desaTuples = new Dictionary <String, Tuple <String, String> >();
using (var package = new ExcelPackage(new FileInfo(idvFile)))
{
var sheet = package.Workbook.Worksheets.FirstOrDefault(s => s.Name == "Sheet1");
int emptyRows = 0;
for (int i = sheet.Cells.Start.Row, endRow = sheet.Cells.End.Row; i < endRow; i++)
{
Console.WriteLine("idv {0}\t of \t{1}", i, endRow);
var objs = new List <object>();
for (int j = sheet.Cells.Start.Column, endColumn = Math.Min(sheet.Cells.End.Column, 40); j < endColumn; j++)
{
objs.Add(sheet.Cells[i, j].Value);
}
var idDesa = objs[0] as String;
if (idDesa == null || idDesa.Trim().ToUpper() == "KODE WILAYAH")
{
emptyRows++;
if (emptyRows > 10)
{
break;
}
continue;
}
emptyRows = 0;
if (!byDesa.ContainsKey(idDesa))
{
byDesa[idDesa] = Tuple.Create(new List <object[]>(), new List <object[]>());
}
byDesa[idDesa].Item1.Add(objs.ToArray());
desaTuples[idDesa] = Tuple.Create((String)objs[3], (String)objs[4]);
}
}
using (var package = new ExcelPackage(new FileInfo(rtFile)))
{
var sheet = package.Workbook.Worksheets.FirstOrDefault(s => s.Name == "Sheet1");
int emptyRows = 0;
for (int i = sheet.Cells.Start.Row, endRow = sheet.Cells.End.Row; i < endRow; i++)
{
Console.WriteLine("rt {0}\t of \t{1}", i, endRow);
var objs = new List <object>();
for (int j = sheet.Cells.Start.Column, endColumn = Math.Min(sheet.Cells.End.Column, 80); j < endColumn; j++)
{
objs.Add(sheet.Cells[i, j].Value);
}
var idDesa = objs[0] as String;
if (idDesa == null || idDesa.Trim().ToUpper() == "KODE WILAYAH")
{
emptyRows++;
if (emptyRows > 10)
{
break;
}
continue;
}
emptyRows = 0;
if (!byDesa.ContainsKey(idDesa))
{
byDesa[idDesa] = Tuple.Create(new List <object[]>(), new List <object[]>());
}
byDesa[idDesa].Item2.Add(objs.ToArray());
desaTuples[idDesa] = Tuple.Create((String)objs[3], (String)objs[4]);
}
}
foreach (var idDesa in byDesa.Keys)
{
var desaTuple = desaTuples[idDesa];
var kecamatan = dbContext.Set <SidekaRegion>()
.FirstOrDefault(r => r.RegionName.ToUpper() == desaTuple.Item1.ToUpper() && r.ParentCode == kabupatenCode);
if (kecamatan == null)
{
Console.WriteLine("Kecamatan " + idDesa + " " + desaTuple.Item1 + "not exists");
continue;
}
var desa = dbContext.Set <SidekaRegion>()
.FirstOrDefault(r => r.RegionName.ToUpper() == desaTuple.Item2.ToUpper() && r.ParentCode == kecamatan.RegionCode);
if (desa == null)
{
Console.WriteLine("Desa " + idDesa + " " + desaTuple + "not exists");
continue;
}
var existingData = dbContext.Pbdt2015
.FirstOrDefault(s => s.RegionCode == desa.RegionCode);
if (existingData == null || existingData.IsImported == false)
{
Console.WriteLine("Writing new PBDT...");
var json = ConvertToJson(byDesa[idDesa].Item1, byDesa[idDesa].Item2);
Pbdt2015 data = new Pbdt2015
{
RegionCode = desa.RegionCode,
Content = json,
IsImported = true
};
dbContext.Set <Pbdt2015>().Add(data);
Console.WriteLine("Desa " + desaTuple + " finished");
}
}
dbContext.SaveChanges();
}
public async Task <IActionResult> PostContentV2([FromBody] JObject contentJObject, int desaId, string contentType, string contentSubtype = null)
{
Stopwatch sw = new Stopwatch();
sw.Start();
string lockName = desaId + "_" + contentType + "_" + contentSubtype;
object writeLock = writeLocks.GetOrAdd(lockName, new object());
lock (writeLock){
var auth = GetAuth(desaId);
if (auth == null)
{
return(StatusCode((int)HttpStatusCode.Forbidden, new Dictionary <string, string>()
{
{ "message", "Invalid or no token" }
}));
}
var permission = contentType;
if (new string[] { "perencanaan", "penganggaran", "spp", "penerimaan" }.Contains(contentType))
{
permission = "keuangan";
}
var roles = (List <string>)auth["roles"];
if (!roles.Contains("administrator") && !roles.Contains(permission))
{
return(StatusCode((int)HttpStatusCode.Forbidden, new Dictionary <string, string>()
{
{ "message", "Your account doesn't have the permission" }
}));
}
var content = new SidekaContentViewModel(contentJObject);
// Validate
foreach (var column in content.Columns)
{
if (content.Diffs != null && content.Diffs.ContainsKey(column.Key))
{
var index = 0;
foreach (var diff in content.Diffs[column.Key])
{
var location = string.Format("Diff {0} ({1}) tab {2}", index, "added", column.Key);
var invalid = Validate(column.Value, diff.Added, location);
if (invalid != null)
{
return(invalid);
}
location = string.Format("Diff {0} ({1}) tab {2}", index, "modified", column.Key);
invalid = Validate(column.Value, diff.Modified, location);
if (invalid != null)
{
return(invalid);
}
location = string.Format("Diff {0} ({1}) tab {2}", index, "deleted", column.Key);
invalid = Validate(column.Value, diff.Deleted, location, false);
if (invalid != null)
{
return(invalid);
}
}
}
if (content.Data != null && content.Data.ContainsKey(column.Key))
{
var location = string.Format("Data tab {0}", column.Key);
var invalid = Validate(column.Value, content.Data[column.Key], location);
if (invalid != null)
{
return(invalid);
}
}
}
var clientChangeId = 0;
var changeId = QueryStringHelper.GetQueryString <int>(Request.Query, "changeId", 0);
if (changeId > 0)
{
clientChangeId = changeId;
}
// Find max change id
var maxChangeIdQuery = dbContext.SidekaContent
.Where(sc => sc.DesaId == desaId)
.Where(sc => sc.Type == contentType);
if (!string.IsNullOrWhiteSpace(contentSubtype))
{
maxChangeIdQuery = maxChangeIdQuery.Where(sc => sc.Subtype == contentSubtype);
}
var maxChangeId = maxChangeIdQuery.Select(sc => sc.ChangeId).DefaultIfEmpty(0).Max();
// TODO: This is risky!! Consider changing change_id column to serial or autoincrement
var newContent = new SidekaContentViewModel();
// Initialize new content to be saved
foreach (var column in content.Columns)
{
newContent.Data[column.Key] = new List <object>().ToArray();
newContent.Columns[column.Key] = column.Value;
if (content.Diffs != null && content.Diffs.ContainsKey(column.Key))
{
newContent.Diffs[column.Key] = content.Diffs[column.Key];
}
else
{
newContent.Diffs[column.Key] = new List <SidekaDiff>().ToArray();
}
}
var latestContentQuery = dbContext.SidekaContent
.Where(sc => sc.DesaId == desaId)
.Where(sc => sc.Type == contentType);
if (!string.IsNullOrWhiteSpace(contentSubtype))
{
latestContentQuery = latestContentQuery.Where(sc => sc.Subtype == contentSubtype);
}
var latestContentString = latestContentQuery
.OrderByDescending(sc => sc.ChangeId)
.Select(sc => sc.Content)
.FirstOrDefault();
JObject latestContentJObject = null;
if (string.IsNullOrWhiteSpace(latestContentString))
{
latestContentJObject = new JObject
{
{ "data", new JObject() },
{ "columns", contentJObject["columns"] }
};
}
else
{
latestContentJObject = JsonConvert.DeserializeObject <JObject>(latestContentString);
}
var diffs = GetDiffsNewerThanClient(desaId, contentType, contentSubtype, clientChangeId, (JObject)contentJObject["columns"]);
if (latestContentJObject["data"] is JArray && contentType == "penduduk")
{
newContent.Data["penduduk"] = MergeDiffs(newContent.Columns["penduduk"], newContent.Diffs["penduduk"], new List <object>().ToArray());
}
else
{
var latestContent = new SidekaContentViewModel(latestContentJObject);
foreach (var column in content.Columns)
{
// Initialize so the latest content have the same tab with the posted content
if (!latestContent.Columns.ContainsKey(column.Key))
{
latestContent.Columns[column.Key] = column.Value;
}
if (!latestContent.Data.ContainsKey(column.Key))
{
latestContent.Data[column.Key] = new List <object>().ToArray();
}
if (content.Data != null && content.Data[column.Key] != null &&
new string[] { "perencanaan", "penganggaran", "penerimaan", "spp" }.Contains(contentType))
{
var invalid = ValidateDuplicatesData(column.Key, column.Value, content.Data[column.Key]);
if (invalid != null)
{
return(invalid);
}
// Special case for client who posted data instead of diffs
newContent.Data[column.Key] = content.Data[column.Key];
// Add new diffs to show that the content is rewritten
var sidekaDiff = new SidekaDiff
{
Added = new List <object>().ToArray(),
Modified = new List <object>().ToArray(),
Deleted = new List <object>().ToArray(),
Total = 0,
Rewritten = true
};
newContent.Diffs[column.Key].Append(sidekaDiff);
}
else if (newContent.Diffs[column.Key].Length > 0)
{
// There's diffs in the posted content for this tab, apply them to latest data
var latestColumns = latestContent.Columns[column.Key];
var transformedLatestData = TransformData(
latestContentJObject["columns"][column.Key],
contentJObject["columns"][column.Key],
latestContent.Data[column.Key]);
var invalid = ValidateDuplicatesDiffs(column.Key, column.Value, content.Diffs[column.Key], transformedLatestData);
if (invalid != null)
{
return(invalid);
}
var mergedData = MergeDiffs(column.Value, content.Diffs[column.Key], transformedLatestData);
newContent.Data[column.Key] = mergedData;
newContent.Columns[column.Key] = column.Value;
}
else
{
// There's no diffs in the posted content for this tab, use the old data
newContent.Data[column.Key] = latestContent.Data[column.Key];
}
}
}
var contentSize = ASCIIEncoding.Unicode.GetByteCount(JsonConvert.SerializeObject(newContent.Data));
var diffSize = ASCIIEncoding.Unicode.GetByteCount(JsonConvert.SerializeObject(newContent.Diffs));
int newChangeId = GetNextChangeId(desaId, contentType, contentSubtype);
var sidekaContent = new SidekaContent
{
DesaId = desaId,
Type = contentType,
Subtype = contentSubtype,
Content = JsonConvert.SerializeObject(newContent),
DateCreated = DateTime.Now,
CreatedBy = (int)auth["user_id"],
ChangeId = newChangeId,
ApiVersion = configuration.GetValue <string>("ApiVersion"),
ContentSize = contentSize,
DiffSize = diffSize
};
dbContext.Add(sidekaContent);
dbContext.SaveChanges();
var result = new Dictionary <string, object>()
{
{ "success", true },
{ "changeId", newChangeId },
{ "change_id", newChangeId },
{ "diffs", diffs },
{ "columns", content.Columns },
};
sw.Stop();
Logs((int)auth["user_id"], desaId, "", "save_content", contentType, contentSubtype, sw.Elapsed.Milliseconds);
return(Ok(result));
}
}