public CityMunicipality GetCityMunicipality(int cityMunicipalityId)
{
_cityMunicipality = new CityMunicipality()
{
MunicipalityId = cityMunicipalityId
};
try
{
int operationType = Convert.ToInt32(OperationType.SelectSpecific);
using (IDbConnection con = new SqlConnection(AppSettings.ConnectionStrings))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
var oCityMunicipality = con.Query <CityMunicipality>("sp_CityMunicipality",
_cityMunicipality.SetParameters(_cityMunicipality, operationType),
commandType: CommandType.StoredProcedure).ToList();
if (oCityMunicipality != null && oCityMunicipality.Count() > 0)
{
_cityMunicipality = oCityMunicipality.SingleOrDefault();
}
}
}
catch (Exception ex)
{
_psgc.Message = ex.Message;
}
return(_cityMunicipality);
}
public CityMunicipality UpdateMunicipality(int cityMunicipalityId, CityMunicipality cityMunicipalities)
{
cityMunicipalities.MunicipalityId = cityMunicipalityId;
try
{
int operationType = Convert.ToInt32(OperationType.Update);
using (IDbConnection con = new SqlConnection(AppSettings.ConnectionStrings))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
var oCityMunicipality = con.Query <CityMunicipality>("sp_CityMunicipality",
_cityMunicipality.SetParameters(cityMunicipalities, operationType),
commandType: CommandType.StoredProcedure);
if (oCityMunicipality != null && oCityMunicipality.Count() > 0)
{
_cityMunicipality = oCityMunicipality.FirstOrDefault();
}
}
}
catch (Exception ex)
{
_psgc.Message = ex.Message;
}
return(_cityMunicipality);
}
public void DeleteCityMunicipality(CityMunicipality cmy)
{
if (cmy == null)
{
throw new ArgumentNullException(nameof(cmy));
}
_context.CitiesMunicipalities.Remove(cmy);
}
public void CreateCityMunicipality(CityMunicipality cmy)
{
if (cmy == null)
{
throw new ArgumentNullException(nameof(cmy));
}
cmy.CityMunicipalityId = Guid.NewGuid();
_context.CitiesMunicipalities.Add(cmy);
}
public CityMunicipality UpdateCityMunicipality(int cityMunicipalityId, [FromBody] CityMunicipality oCityMunicipality)
{
if (ModelState.IsValid)
{
return(_iCityMunicipalityService.UpdateMunicipality(cityMunicipalityId, oCityMunicipality));
}
else
{
return(null);
}
}
public CityMunicipality AddNewCityMunicipality([FromBody] CityMunicipality oCityMunicipality)
{
if (ModelState.IsValid)
{
return(_iCityMunicipalityService.AddCityMunicipality(oCityMunicipality));
}
else
{
return(null);
}
}
public string Delete(int cityMunicipalityId)
{
string message = "";
try
{
_cityMunicipality = new CityMunicipality()
{
MunicipalityId = cityMunicipalityId
};
using (IDbConnection con = new SqlConnection(AppSettings.ConnectionStrings))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
var oCityMunicipality = con.Query <CityMunicipality>("sp_CityMunicipality",
_cityMunicipality.SetParameters(_cityMunicipality, (int)OperationType.Delete),
commandType: CommandType.StoredProcedure);
if (oCityMunicipality != null && oCityMunicipality.Count() > 0)
{
_cityMunicipality = oCityMunicipality.FirstOrDefault();
message = "Data Deleted!";
}
}
}
catch (Exception ex)
{
message = ex.Message;
}
return(message);
}
public ActionResult UploadFile(IFormFile psgc)
{
var tempreglist = new List <Region>();
var temprovlist = new List <ProvinceDistrict>();
var tempcitylist = new List <CityMunicipality>();
var tempbara = new List <Barangay>();
using (var reader = new StreamReader(psgc.OpenReadStream()))
{
while (reader.Peek() >= 0)
{
var psgcrow = reader.ReadLine().Split(",");
if (psgcrow.Length != 3)
{
return(BadRequest());
}
if (psgcrow[0].Length != 9)
{
return(BadRequest());
}
var regcode = psgcrow[0].Substring(0, 2) + "0000000";
var provdistcode = psgcrow[0].Substring(0, 4) + "00000";
var citymunisubmunicode = psgcrow[0].Substring(0, 6) + "000";
if (ShareLib.CheckIfRegion(psgcrow[0]))
{
Region newreg = new Region
{
RegionId = Guid.NewGuid(),
RegionCode = psgcrow[0],
RegionName = psgcrow[1]
};
tempreglist.Add(newreg);
}
if (ShareLib.CheckIfProviceDistrict(psgcrow[0]))
{
ProvinceDistrict newprov = new ProvinceDistrict
{
ProvinceDistrictId = Guid.NewGuid(),
ProvinceDistrictCode = psgcrow[0],
ProvinceDistrictName = psgcrow[1],
ProvinceDistrictOldName = psgcrow[2],
Region = tempreglist.Where(w => w.RegionCode == regcode).First()
};
temprovlist.Add(newprov);
}
if (ShareLib.CheckIfCityMunicipalitySub(psgcrow[0]))
{
CityMunicipality newcitmun = new CityMunicipality
{
CityMunicipalityId = Guid.NewGuid(),
CityMunicipalityCode = psgcrow[0],
CityMunicipalityName = psgcrow[1],
CityMunicipalityOldname = psgcrow[2],
ProvinceDistrict = temprovlist.Where(w => w.ProvinceDistrictCode == provdistcode).First()
};
tempcitylist.Add(newcitmun);
}
if (ShareLib.CheckIfBarangay(psgcrow[0]))
{
Barangay newbrg = new Barangay
{
BarangayId = Guid.NewGuid(),
BarangayCode = psgcrow[0],
BarangayName = psgcrow[1],
BarangayOldname = psgcrow[2],
CityMunicipality = tempcitylist.Where(w => w.CityMunicipalityCode == citymunisubmunicode).First(),
CityMunicipalityId = tempcitylist.Where(w => w.CityMunicipalityCode == citymunisubmunicode).First().CityMunicipalityId
};
tempbara.Add(newbrg);
}
}
}
_repo.Import(tempreglist, temprovlist, tempcitylist, tempbara);
_repo.SaveChanges();
return(Ok());
}
public void UpdateCityMunicipality(CityMunicipality cmy)
{
// Done by automapper
}
public static void Initialize(ApiContext context)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
if (context.Barangays.Any())
{
return;
}
var tempreglist = new List <Region>();
var temprovlist = new List <ProvinceDistrict>();
var tempcitylist = new List <CityMunicipality>();
var tempbara = new List <Barangay>();
using (var reader = new StreamReader("PSGC_3Q_2020_Publication.csv"))
{
while (reader.Peek() >= 0)
{
var psgcrow = reader.ReadLine().Split(",");
var regcode = psgcrow[0].Substring(0, 2) + "0000000";
var provdistcode = psgcrow[0].Substring(0, 4) + "00000";
var citymunisubmunicode = psgcrow[0].Substring(0, 6) + "000";
if (ShareLib.CheckIfRegion(psgcrow[0]))
{
Region newreg = new Region
{
RegionId = Guid.NewGuid(),
RegionCode = psgcrow[0],
RegionName = psgcrow[1]
};
tempreglist.Add(newreg);
}
if (ShareLib.CheckIfProviceDistrict(psgcrow[0]))
{
ProvinceDistrict newprov = new ProvinceDistrict
{
ProvinceDistrictId = Guid.NewGuid(),
ProvinceDistrictCode = psgcrow[0],
ProvinceDistrictName = psgcrow[1],
ProvinceDistrictOldName = psgcrow[2],
Region = tempreglist.Where(w => w.RegionCode == regcode).First()
};
temprovlist.Add(newprov);
}
if (ShareLib.CheckIfCityMunicipalitySub(psgcrow[0]))
{
CityMunicipality newcitmun = new CityMunicipality
{
CityMunicipalityId = Guid.NewGuid(),
CityMunicipalityCode = psgcrow[0],
CityMunicipalityName = psgcrow[1],
CityMunicipalityOldname = psgcrow[2],
ProvinceDistrict = temprovlist.Where(w => w.ProvinceDistrictCode == provdistcode).First()
};
tempcitylist.Add(newcitmun);
}
if (ShareLib.CheckIfBarangay(psgcrow[0]))
{
Barangay newbrg = new Barangay
{
BarangayId = Guid.NewGuid(),
BarangayCode = psgcrow[0],
BarangayName = psgcrow[1],
BarangayOldname = psgcrow[2],
CityMunicipality = tempcitylist.Where(w => w.CityMunicipalityCode == citymunisubmunicode).First()
};
tempbara.Add(newbrg);
}
}
}
context.Regions.AddRange(tempreglist);
context.ProvincesDistricts.AddRange(temprovlist);
context.CitiesMunicipalities.AddRange(tempcitylist);
context.Barangays.AddRange(tempbara);
Console.WriteLine("CSV Imported");
context.SaveChanges();
}
