public string UploadLokasi(IEnumerable <HttpPostedFileBase> files)
{
ResponeModel response = new ResponeModel();
string msgResult = "";
//algoritma
if (files != null)
{
foreach (var file in files)
{
try
{
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 2].Value != null && workSheet.Cells[rowIterator, 3].Value != null && workSheet.Cells[rowIterator, 4].Value != null)
{
try
{
int id = 0;
int resId;
Context.Location dbitem = new Context.Location();
//if (workSheet.Cells[rowIterator, 5].Value != null)
if (workSheet.Cells[rowIterator, 8].Value != null)
{
if (int.TryParse(workSheet.Cells[rowIterator, 8].Value.ToString(), out resId))
{
id = resId;
}
}
string code = workSheet.Cells[rowIterator, 3].Value.ToString().Replace(',', '.');
if (RepoLocation.IsExist(code, id))
{
continue;
}
if (id != 0)
{
dbitem = RepoLocation.FindByCode(code);
}
//parent
if (workSheet.Cells[rowIterator, 2].Value.ToString() != "PROV")
{
dbitem.ParentId = RepoLocation.FindByCode(workSheet.Cells[rowIterator, 1].Value.ToString()).Id;
}
//code
dbitem.Code = code;
//cek type (PROV, KOTA, KAB)
//PROV
if (workSheet.Cells[rowIterator, 2].Value.ToString() == "PROV")
{
dbitem.Type = "Provinsi";
dbitem.Nama = workSheet.Cells[rowIterator, 4].Value.ToString();
}
else if (workSheet.Cells[rowIterator, 2].Value.ToString() == "KOTA")
{
dbitem.Type = "Kab/Kota";
dbitem.Nama = "Kota " + workSheet.Cells[rowIterator, 4].Value.ToString();
}
else if (workSheet.Cells[rowIterator, 2].Value.ToString() == "KAB")
{
dbitem.Type = "Kab/Kota";
dbitem.Nama = "Kabupaten " + workSheet.Cells[rowIterator, 4].Value.ToString();
}
else if (workSheet.Cells[rowIterator, 2].Value.ToString() == "KEC")
{
dbitem.Type = "Kecamatan";
dbitem.Nama = "Kecamatan " + workSheet.Cells[rowIterator, 4].Value.ToString();
}
else if (workSheet.Cells[rowIterator, 2].Value.ToString() == "DES")
{
dbitem.Type = "Kelurahan";
dbitem.Nama = "Kelurahan " + workSheet.Cells[rowIterator, 4].Value.ToString();
}
Task t = Task.Run(() => RepoLocation.save(dbitem));
t.Wait();
}
catch (Exception e)
{
string msg = e.Message.ToString();
}
}
}
}
response.Success = true;
}
catch (Exception e)
{
response.Success = false;
response.Message = e.Message.ToString();
}
}
}
return(new JavaScriptSerializer().Serialize(new { Response = response }));
}
public string Upload(IEnumerable <HttpPostedFileBase> fileRute)
{
ResponeModel response = new ResponeModel();
if (fileRute != null)
{
foreach (var file in fileRute)
{
try
{
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 1].Value != null && workSheet.Cells[rowIterator, 2].Value != null &&
workSheet.Cells[rowIterator, 3].Value != null && workSheet.Cells[rowIterator, 4].Value != null &&
workSheet.Cells[rowIterator, 5].Value != null && workSheet.Cells[rowIterator, 7].Value != null)
{
int id = 0;
int resId;
if (workSheet.Cells[rowIterator, 14].Value != null)
{
if (int.TryParse(workSheet.Cells[rowIterator, 14].Value.ToString(), out resId))
{
id = resId;
}
}
Context.Rute db = new Context.Rute();
try
{
if (id != 0)
{
db = RepoRute.FindByPK(id);
db.RuteCheckPoint.Clear();
}
//parent
db.Urutan = RepoRute.getUrutan() + 1;
db.Kode = RepoRute.generateCode(db.Urutan);
db.Nama = workSheet.Cells[rowIterator, 1].Value.ToString();
db.IdAsal = RepoLocation.FindByCode(workSheet.Cells[rowIterator, 2].Value.ToString()).Id;
db.IdAreaAsal = RepoArea.FindByCode(workSheet.Cells[rowIterator, 3].Value.ToString()).Id;
db.IdTujuan = RepoLocation.FindByCode(workSheet.Cells[rowIterator, 4].Value.ToString()).Id;
db.IdAreaTujuan = RepoArea.FindByCode(workSheet.Cells[rowIterator, 5].Value.ToString()).Id;
if (workSheet.Cells[rowIterator, 6].Value != null && workSheet.Cells[rowIterator, 6].Value.ToString() != "")
{
db.IdMultiDrop = RepoMultidrop.FindByTujuan(workSheet.Cells[rowIterator, 6].Value.ToString()).Id;
}
db.WaktuKerja = int.Parse(workSheet.Cells[rowIterator, 7].Value.ToString());
if (workSheet.Cells[rowIterator, 8].Value != null)
{
db.Toleransi = int.Parse(workSheet.Cells[rowIterator, 8].Value.ToString());
}
db.IsAreaPulang = workSheet.Cells[rowIterator, 9].Value == null ? false : bool.Parse(workSheet.Cells[rowIterator, 9].Value.ToString());
db.IsKotaKota = workSheet.Cells[rowIterator, 10].Value == null ? false : bool.Parse(workSheet.Cells[rowIterator, 10].Value.ToString());
// child;
int idx = 0;
for (idx = rowIterator; idx <= noOfRow; idx++)
{
if (workSheet.Cells[idx, 1].Value == null || idx == rowIterator)
{
if (workSheet.Cells[idx, 10].Value != null && workSheet.Cells[idx, 11].Value != null &&
workSheet.Cells[idx, 12].Value != null && workSheet.Cells[idx, 13].Value != null)
{
Context.RuteCheckPoint item = new Context.RuteCheckPoint();
item.longitude = workSheet.Cells[idx, 10].Value.ToString();
item.langitude = workSheet.Cells[idx, 11].Value.ToString();
item.radius = int.Parse(workSheet.Cells[idx, 12].Value.ToString());
item.toleransi = int.Parse(workSheet.Cells[idx, 13].Value.ToString());
db.RuteCheckPoint.Add(item);
}
}
else
{
break;
}
}
if (idx != 0)
{
rowIterator = idx - 1;
}
RepoRute.save(db);
}
catch (Exception)
{
}
}
}
}
response.Success = true;
}
catch (Exception e)
{
response.Success = false;
response.Message = e.Message.ToString();
}
}
}
return(new JavaScriptSerializer().Serialize(new { Response = response }));
}
public string Upload(IEnumerable <HttpPostedFileBase> files)
{
ResponeModel response = new ResponeModel();
//algoritma
if (files != null)
{
foreach (var file in files)
{
try
{
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 1].Value != null && workSheet.Cells[rowIterator, 2].Value != null)
{
int id = 0;
int resId;
if (workSheet.Cells[rowIterator, 3].Value != null)
{
if (int.TryParse(workSheet.Cells[rowIterator, 3].Value.ToString(), out resId))
{
id = resId;
}
}
Context.MasterArea dbitem = new Context.MasterArea();
try
{
if (id != 0)
{
dbitem = RepoArea.FindByPK(id);
}
else
{
dbitem.Urutan = RepoArea.getUrutan() + 1;
dbitem.Kode = RepoArea.generateCode(dbitem.Urutan);
}
// parent
if (RepoArea.IsExist(workSheet.Cells[rowIterator, 1].Value.ToString(), id))
{
continue;
}
dbitem.Nama = workSheet.Cells[rowIterator, 1].Value.ToString();
//child
int x = 0;
List <int> listIdData = new List <int>();
List <Context.Rute> listRute = RepoRute.FindAll();
List <Context.ListLocationArea> listAreaAdd = new List <Context.ListLocationArea>();
//cek data existing dengan excell
for (x = rowIterator; x <= noOfRow; x++)
{
if (workSheet.Cells[x, 1].Value == null || x == rowIterator)
{
try
{
Context.ListLocationArea listLocationArea = new Context.ListLocationArea();
int?idLoc = RepoLocation.FindByCode(workSheet.Cells[x, 2].Value.ToString()).Id;
if (idLoc.HasValue)
{
if (id != 0)
{
if (dbitem.ListLocationArea.Any(d => d.LocationId == idLoc))
{
listIdData.Add(idLoc.Value);
}
else
{
listLocationArea.LocationId = idLoc;
listAreaAdd.Add(listLocationArea);
}
}
else
{
listLocationArea.LocationId = idLoc;
listAreaAdd.Add(listLocationArea);
}
}
}
catch (Exception)
{
}
}
else
{
rowIterator = x - 1;
break;
}
}
List <Context.ListLocationArea> dbremove = dbitem.ListLocationArea.Where(d => !listIdData.Contains(d.Id) && d.Id != 0).ToList();
//remove
foreach (Context.ListLocationArea item in dbremove)
{
if (listRute.Any(r => r.IdAsal == item.LocationId && r.IdTujuan == item.LocationId))
{
}
else
{
dbitem.ListLocationArea.Remove(item);
}
}
//add
foreach (Context.ListLocationArea item in listAreaAdd)
{
dbitem.ListLocationArea.Add(item);
}
RepoArea.save(dbitem);
}
catch (Exception)
{
}
}
}
}
response.Success = true;
}
catch (Exception e)
{
response.Success = false;
response.Message = e.Message.ToString();
}
}
}
return(new JavaScriptSerializer().Serialize(new { Response = response }));
}
public string Upload(IEnumerable <HttpPostedFileBase> filesMultidrop)
{
ResponeModel response = new ResponeModel();
if (filesMultidrop != null)
{
foreach (var file in filesMultidrop)
{
try
{
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
bool isSaved = true;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 1].Value != null && workSheet.Cells[rowIterator, 2].Value != null &&
workSheet.Cells[rowIterator, 3].Value != null && workSheet.Cells[rowIterator, 4].Value != null)
{
int id = 0;
int resId;
if (workSheet.Cells[rowIterator, 5].Value != null)
{
if (int.TryParse(workSheet.Cells[rowIterator, 5].Value.ToString(), out resId))
{
id = resId;
}
}
List <String> listLocation = new List <string>();
Context.Multidrop dbMultiDrop = new Context.Multidrop();
try
{
if (id != 0)
{
dbMultiDrop = RepoMultidrop.FindByPK(id);
}
dbMultiDrop.JumlahKota = int.Parse(workSheet.Cells[rowIterator, 1].Value.ToString());
dbMultiDrop.WaktuTempuh = int.Parse(workSheet.Cells[rowIterator, 2].Value.ToString());
dbMultiDrop.WaktuKerja = int.Parse(workSheet.Cells[rowIterator, 3].Value.ToString());
Context.Location revLocation = RepoLocation.FindByCode(workSheet.Cells[rowIterator, 4].Value.ToString());
if (revLocation != null)
{
listLocation.Add(revLocation.Nama);
}
for (int x = rowIterator + 1; x <= noOfRow; x++)
{
if (workSheet.Cells[x, 4].Value != null && workSheet.Cells[x, 1].Value == null &&
workSheet.Cells[x, 2].Value == null && workSheet.Cells[x, 3].Value == null)
{
Context.Location loc = RepoLocation.FindByCode(workSheet.Cells[x, 4].Value.ToString());
if (loc != null)
{
listLocation.Add(loc.Nama);
}
else
{
isSaved = false;
break;
}
}
else
{
//isSaved = false;
break;
}
}
if (listLocation.Count > 1)
{
dbMultiDrop.tujuan = string.Join(" - ", listLocation);
}
if (RepoMultidrop.FindByTujuan(dbMultiDrop.tujuan.ToString()) != null)
{
continue;
}
if (isSaved)
{
RepoMultidrop.save(dbMultiDrop);
}
}
catch (Exception)
{
}
}
}
}
response.Success = true;
}
catch (Exception e)
{
response.Success = false;
response.Message = e.Message.ToString();
}
}
}
return(new JavaScriptSerializer().Serialize(new { Response = response }));
}