public FileResult Exportxls()
{
string url = "https://kc-dtm.iom.int/api/v1/data/536?format=json";
var returnJson = HelperController.DownloadJsonString(url);
List <WinterizationSupportModel> mapped = JsonConvert.DeserializeObject <List <WinterizationSupportModel> >(returnJson);
mapped = mapped.OrderBy(b => b.SubmissionTime).ToList();
using (XLWorkbook wb = new XLWorkbook(Server.MapPath("~/WinterizationSupport/WinterizationsuportKunduz.xlsx")))
{
IXLWorksheet masterList = wb.Worksheet(@"Master list");
IXLWorksheet Token = wb.Worksheet(@"Token");
int rowStart = 4;
float cashAmountUSD = 200;
float cashAmountAFN = 15038;
int noBlanket = 1;
string typeOfEmergency = "Winterization";
if (mapped.Count > 0)
{
for (int i = 0; i < mapped.Count; i++)
{
//Inserting sr will be asked
//masterList.Cell(rowStart + i, 1).Value = SR;
masterList.Cell(rowStart + i, 2).Value = mapped[i].Name;
masterList.Cell(rowStart + i, 3).Value = mapped[i].FatherName;
masterList.Cell(rowStart + i, 4).Value = mapped[i].Gender;
masterList.Cell(rowStart + i, 5).Value = mapped[i].TazkiraNo;
masterList.Cell(rowStart + i, 6).Value = KunduzHelperController.Districts[(int)mapped[i].DistrictID];
masterList.Cell(rowStart + i, 7).Value = mapped[i].Village;
masterList.Cell(rowStart + i, 8).Value = mapped[i].ContactNumber.ToString();
masterList.Cell(rowStart + i, 9).Value = KunduzHelperController.getTotalMale(mapped[i]);
masterList.Cell(rowStart + i, 10).Value = KunduzHelperController.getTotalFemale(mapped[i]);
masterList.Cell(rowStart + i, 11).Value = mapped[i].M0;
masterList.Cell(rowStart + i, 12).Value = mapped[i].F0;
masterList.Cell(rowStart + i, 13).Value = mapped[i].M1To4;
masterList.Cell(rowStart + i, 14).Value = mapped[i].F1To4;
masterList.Cell(rowStart + i, 15).Value = mapped[i].M5To11;
masterList.Cell(rowStart + i, 16).Value = mapped[i].F5To11;
masterList.Cell(rowStart + i, 17).Value = mapped[i].M12To17;
masterList.Cell(rowStart + i, 18).Value = mapped[i].F12To17;
masterList.Cell(rowStart + i, 19).Value = mapped[i].M18To59;
masterList.Cell(rowStart + i, 20).Value = mapped[i].F18To59;
masterList.Cell(rowStart + i, 21).Value = mapped[i].M60Plus;
masterList.Cell(rowStart + i, 22).Value = mapped[i].F60Plus;
masterList.Cell(rowStart + i, 23).Value = KunduzHelperController.getTotalMale(mapped[i]) + KunduzHelperController.getTotalFemale(mapped[i]);
masterList.Cell(rowStart + i, 24).Value = cashAmountUSD;
masterList.Cell(rowStart + i, 25).Value = cashAmountAFN;
masterList.Cell(rowStart + i, 26).Value = noBlanket;
masterList.Cell(rowStart + i, 27).Value = typeOfEmergency;
masterList.Cell(rowStart + i, 28).Value = KunduzHelperController.CurrentLivingSituation[mapped[i].CurrentLivingSituation];
masterList.Cell(rowStart + i, 29).Value = mapped[i].CurrentLivingSituationOther;
masterList.Cell(rowStart + i, 30).Value = HelperController.addSpace(mapped[i].ShelterConditions);
masterList.Cell(rowStart + i, 31).Value = mapped[i].HouseholdItems;
masterList.Cell(rowStart + i, 32).Value = HelperController.addSpace(mapped[i].HouseHoldItemsDamaged);
masterList.Cell(rowStart + i, 33).Value = mapped[i].WorkToolsDescription;
//masterList.Cell(rowStart + i, 34).Value = mapped[i].RAF Number;
masterList.Cell(rowStart + i, 35).Value = mapped[i].Comment;
masterList.Cell(rowStart + i, 36).Value = mapped[i].SubmissionTime;
}
}
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "HAP Winterization Support Beneficiary List.xlsx"));
}
}
}
public FileResult Dec_2019_Exportxls(string regionCode, string provinceCode)
{
string url = "https://kc-dtm.iom.int/api/v1/data/2614?format=json";
string baseURL = ConfigurationManager.AppSettings["BaseURL"];
var returnJson = HelperController.DownloadJsonString(url);
List <AllCountry_Dec_2019_Model> mapped = JsonConvert.DeserializeObject <List <AllCountry_Dec_2019_Model> >(returnJson);
var query = mapped.AsQueryable();
if (!string.IsNullOrWhiteSpace(regionCode))
{
query = query.Where(m => m.Region == regionCode);
}
if (!string.IsNullOrWhiteSpace(provinceCode))
{
query = query.Where(m => m.ProvinceCode == provinceCode);
}
var byProvince = query.OrderBy(b => b._submission_time).GroupBy(a => a.ProvinceCode).ToList();
using (XLWorkbook wb = new XLWorkbook(Server.MapPath("~/WinterizationSupport/Winterization_Assessment_All_Country_Dec-2019.xlsx")))
{
IXLWorksheet masterList = wb.Worksheet(@"Master list");
masterList.Style.Border.InsideBorder = XLBorderStyleValues.Dashed;
var rowStart = 5;
if (mapped.Count > 0)
{
int counter = 0;
foreach (var provinceData in byProvince)
{
int number = 0;
foreach (var beneficiary in provinceData)
{
//For cell index chage
counter += 1;
//For beneficiary number like KBL-1
number += 1;
//Insert record to master list
masterList.Cell(rowStart + counter, 1).Value = provinceData.Key + "-" + number;
masterList.Cell(rowStart + counter, 2).Value = beneficiary.Name;
masterList.Cell(rowStart + counter, 3).Value = beneficiary.FatherName;
masterList.Cell(rowStart + counter, 4).Value = beneficiary.Gender;
masterList.Cell(rowStart + counter, 5).Value = beneficiary.TazkiraNo;
masterList.Cell(rowStart + counter, 6).Value = beneficiary.ContactNumber;
masterList.Cell(rowStart + counter, 7).Value = Lists.Regions.Where(r => r.Code == beneficiary.Region).Select(r => r.Name).FirstOrDefault();
masterList.Cell(rowStart + counter, 8).Value = Lists.Provinces.Where(p => p.Code == beneficiary.ProvinceCode).Select(p => p.Name).FirstOrDefault();
masterList.Cell(rowStart + counter, 9).Value = Lists.Districts.Where(d => d.DistrictID == beneficiary.DistrictID).Select(d => d.DistrictName).FirstOrDefault();
masterList.Cell(rowStart + counter, 10).Value = beneficiary.VillageOrPD;
masterList.Cell(rowStart + counter, 11).Value = beneficiary.M0;
masterList.Cell(rowStart + counter, 12).Value = beneficiary.F0;
masterList.Cell(rowStart + counter, 13).Value = beneficiary.M1To4;
masterList.Cell(rowStart + counter, 14).Value = beneficiary.F1To4;
masterList.Cell(rowStart + counter, 15).Value = beneficiary.M5To11;
masterList.Cell(rowStart + counter, 16).Value = beneficiary.F5To11;
masterList.Cell(rowStart + counter, 17).Value = beneficiary.M12To17;
masterList.Cell(rowStart + counter, 18).Value = beneficiary.F12To17;
masterList.Cell(rowStart + counter, 19).Value = beneficiary.M18To59;
masterList.Cell(rowStart + counter, 20).Value = beneficiary.F18To59;
masterList.Cell(rowStart + counter, 21).Value = beneficiary.M60Plus;
masterList.Cell(rowStart + counter, 22).Value = beneficiary.F60Plus;
masterList.Cell(rowStart + counter, 23).Value = beneficiary.TotalFamilyMember;
masterList.Cell(rowStart + counter, 24).Value = beneficiary.FamilyIncome.Replace('_', ' ');
masterList.Cell(rowStart + counter, 25).Value = beneficiary.UrgentNeed.Split(' ')[0].Replace('_', ' ');
if (beneficiary.UrgentNeed.Split(' ').Length > 1)
{
masterList.Cell(rowStart + counter, 26).Value = beneficiary.UrgentNeed.Split(' ')[1].Replace('_', ' ');
}
if (beneficiary.UrgentNeed.Split(' ').Length > 2)
{
masterList.Cell(rowStart + counter, 27).Value = beneficiary.UrgentNeed.Split(' ')[2].Replace('_', ' ');
}
masterList.Cell(rowStart + counter, 28).Value = beneficiary.UrgentNeedOther;
masterList.Cell(rowStart + counter, 29).Value = beneficiary.TypeOfBeneficiary.Replace('_', ' ');
if (!string.IsNullOrWhiteSpace(beneficiary.CategoryOfBeneficiary))
{
masterList.Cell(rowStart + counter, 30).Value = beneficiary.CategoryOfBeneficiary.Replace('_', ' ');
}
if (!string.IsNullOrWhiteSpace(beneficiary.ReturneeType))
{
masterList.Cell(rowStart + counter, 31).Value = beneficiary.ReturneeType.Replace('_', ' ');
}
if (!string.IsNullOrWhiteSpace(beneficiary.PlaceOfOriginProvince) && (beneficiary.PlaceOfOriginDistrict != 0 || beneficiary.PlaceOfOriginDistrict != null))
{
masterList.Cell(rowStart + counter, 32).Value = Lists.Provinces.Where(p => p.Code == beneficiary.PlaceOfOriginProvince).Select(p => p.Name).FirstOrDefault();
masterList.Cell(rowStart + counter, 33).Value = Lists.Districts.Where(d => d.DistrictID == beneficiary.PlaceOfOriginDistrict).Select(d => d.DistrictName).FirstOrDefault();
}
masterList.Cell(rowStart + counter, 34).Value = beneficiary.PlaceOfOriginVillageOrPD;
masterList.Cell(rowStart + counter, 35).Value = beneficiary.PlaceOfReturn;
masterList.Cell(rowStart + counter, 36).Value = beneficiary.PlaceOfReturnOther;
masterList.Cell(rowStart + counter, 37).Value = beneficiary.DateDisplacementOrIncidentOrReturn;
if (!string.IsNullOrWhiteSpace(beneficiary.VulnerabilityFactor))
{
masterList.Cell(rowStart + counter, 38).Value = beneficiary.VulnerabilityFactor.Replace('_', ' ');
}
masterList.Cell(rowStart + counter, 39).Value = beneficiary.VulnerabilityFactorOther;
masterList.Cell(rowStart + counter, 40).Value = beneficiary.CurrentLivingSituation.Replace('_', ' ');
masterList.Cell(rowStart + counter, 41).Value = beneficiary.CurrentLivingSituationOther;
masterList.Cell(rowStart + counter, 42).Value = beneficiary.ShelterConditions.Replace('_', ' ');
masterList.Cell(rowStart + counter, 43).Value = beneficiary.HouseholdItems;
masterList.Cell(rowStart + counter, 44).Value = HelperController.addSpace(beneficiary.HouseHoldItemsCondition);
masterList.Cell(rowStart + counter, 45).Value = beneficiary.WorkToolsDescription;
masterList.Cell(rowStart + counter, 46).Value = beneficiary.CanCameToReceiveAssistance;
masterList.Cell(rowStart + counter, 47).Value = beneficiary.AlternateName;
masterList.Cell(rowStart + counter, 48).Value = beneficiary.AlternateFName;
masterList.Cell(rowStart + counter, 49).Value = beneficiary.AlternateRelationship;
masterList.Cell(rowStart + counter, 50).Value = beneficiary.AlternateTazkiraNo;
masterList.Cell(rowStart + counter, 51).Value = beneficiary.AlternateContactNumber;
masterList.Cell(rowStart + counter, 52).Value = beneficiary.EnumeratorName;
masterList.Cell(rowStart + counter, 53).Value = beneficiary.AssessmentAgencies;
masterList.Cell(rowStart + counter, 54).Value = beneficiary.Comment;
//Add hyper link for face and tazkira images
masterList.Cell(rowStart + counter, 55).Value = "Face Image link";
var faceImageUrl = beneficiary.Attachments.Where(a => a.Download_url.Contains(beneficiary._uuid + "/" + beneficiary.FaceImage)).Select(a => a.Download_url).FirstOrDefault();
masterList.Cell(rowStart + counter, 55).Hyperlink = new XLHyperlink(baseURL + faceImageUrl, "Face image link");
masterList.Cell(rowStart + counter, 56).Value = "Tazkira Image link";
var tazkiraUrl = beneficiary.Attachments.Where(a => a.Download_url.Contains(beneficiary._uuid + "/" + beneficiary.TazkiraImage)).Select(a => a.Download_url).FirstOrDefault();
masterList.Cell(rowStart + counter, 56).Hyperlink = new XLHyperlink(baseURL + tazkiraUrl, "Takira link");
if (beneficiary.CanCameToReceiveAssistance == "No")
{
masterList.Cell(rowStart + counter, 57).Value = "Alternate Face Image link";
var alternateFaceImageUrl = beneficiary.Attachments.Where(a => a.Download_url.Contains(beneficiary._uuid + "/" + beneficiary.AlternateFaceImage)).Select(a => a.Download_url).FirstOrDefault();
masterList.Cell(rowStart + counter, 57).Hyperlink = new XLHyperlink(baseURL + alternateFaceImageUrl, "Alternate face image");
masterList.Cell(rowStart + counter, 58).Value = "Alternate Tazkira Image link";
var alternateTazkiraImageUrl = beneficiary.Attachments.Where(a => a.Download_url.Contains(beneficiary._uuid + "/" + beneficiary.AlternateTazkiraImage)).Select(a => a.Download_url).FirstOrDefault();
masterList.Cell(rowStart + counter, 58).Hyperlink = new XLHyperlink(baseURL + alternateTazkiraImageUrl, "Alternate tazkira image");
}
}
}
}
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "HAP Winterization all country assessment List.xlsx"));
}
}
}