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, int?DistrictID)
        {
            string url        = "https://kc-dtm.iom.int/api/v1/data/2547?format=json";
            var    returnJson = HelperController.DownloadJsonString(url);
            List <MarketSurveyModels> mapped = JsonConvert.DeserializeObject <List <MarketSurveyModels> >(returnJson);
            var query = mapped.AsQueryable();
            var test  = mapped.ToList();

            if (!string.IsNullOrWhiteSpace(regionCode))
            {
                query = query.Where(m => m.Region == regionCode);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                query = query.Where(m => m.ProvinceCode == provinceCode);
            }
            if (DistrictID != null && DistrictID != 0)
            {
                query = query.Where(m => m.DistrictID == DistrictID);
            }
            var marketList = query.ToList();

            using (XLWorkbook wb = new XLWorkbook(Server.MapPath("~/WinterizationSupport/Market_Survey_Dec-2019.xlsx")))
            {
                IXLWorksheet marketsSheet = wb.Worksheet(@"Markets");
                var          rowStart     = 3;
                if (mapped.Count > 0)
                {
                    int counter = 0;
                    foreach (var market in query)
                    {
                        //For cell index change
                        counter += 1;
                        //Insert record to master list
                        marketsSheet.Cell(rowStart + counter, 1).Value  = counter;
                        marketsSheet.Cell(rowStart + counter, 2).Value  = Lists.Regions.Where(r => r.Code == market.Region).Select(r => r.Name).FirstOrDefault();
                        marketsSheet.Cell(rowStart + counter, 3).Value  = Lists.Provinces.Where(p => p.Code == market.ProvinceCode).Select(p => p.Name).FirstOrDefault();
                        marketsSheet.Cell(rowStart + counter, 4).Value  = Lists.Districts.Where(d => d.DistrictID == market.DistrictID).Select(d => d.DistrictName).FirstOrDefault();
                        marketsSheet.Cell(rowStart + counter, 5).Value  = market.Village;
                        marketsSheet.Cell(rowStart + counter, 6).Value  = market.MarketType.Replace('_', ' ');
                        marketsSheet.Cell(rowStart + counter, 7).Value  = market.DistanceToMarket.Replace('_', ' ');
                        marketsSheet.Cell(rowStart + counter, 8).Value  = market.DoesTheMarketMeetDemands;
                        marketsSheet.Cell(rowStart + counter, 9).Value  = market.AvailableHeatingDevices.Replace(' ', ',').Replace('_', ' ');
                        marketsSheet.Cell(rowStart + counter, 10).Value = market.AvailableHeatingDevicesOthers;
                        marketsSheet.Cell(rowStart + counter, 11).Value = market.AvailableHeatingMaterials.Replace(' ', ',');
                        marketsSheet.Cell(rowStart + counter, 12).Value = market.AvailableHeatingMaterialsOthers;
                        marketsSheet.Cell(rowStart + counter, 13).Value = market.HeatingMaterialsUsed.Replace(' ', ',');
                        marketsSheet.Cell(rowStart + counter, 14).Value = market.HeatingMaterialsUsedOthers;
                        marketsSheet.Cell(rowStart + counter, 15).Value = market.Market_Have_Jacket;
                        marketsSheet.Cell(rowStart + counter, 16).Value = market.Market_Have_Sweater;
                        marketsSheet.Cell(rowStart + counter, 17).Value = market.Market_Have_Shawl;
                        marketsSheet.Cell(rowStart + counter, 18).Value = market.Market_Have_Caps;
                        marketsSheet.Cell(rowStart + counter, 19).Value = market.Market_Have_Clothes_Other;
                        marketsSheet.Cell(rowStart + counter, 20).Value = market.Wood_Price;
                        marketsSheet.Cell(rowStart + counter, 21).Value = market.Gas_Price;
                        marketsSheet.Cell(rowStart + counter, 22).Value = market.Coal_Price;
                        marketsSheet.Cell(rowStart + counter, 23).Value = market.Heating_Materials_Price_Other;
                        marketsSheet.Cell(rowStart + counter, 24).Value = market.Jacket_Price;
                        marketsSheet.Cell(rowStart + counter, 25).Value = market.Shawl_Price;
                        marketsSheet.Cell(rowStart + counter, 26).Value = market.Caps_Price;
                        marketsSheet.Cell(rowStart + counter, 27).Value = market.Patto_Price;
                        marketsSheet.Cell(rowStart + counter, 28).Value = market.Clothes_Price_Other;
                        marketsSheet.Cell(rowStart + counter, 29).Value = market.Market_Have_Flour;
                        marketsSheet.Cell(rowStart + counter, 30).Value = market.Market_Have_Nuts;
                        marketsSheet.Cell(rowStart + counter, 31).Value = market.Market_Have_Beans;
                        marketsSheet.Cell(rowStart + counter, 32).Value = market.Market_Have_Oil;
                        marketsSheet.Cell(rowStart + counter, 33).Value = market.Market_Have_Salt;
                        marketsSheet.Cell(rowStart + counter, 34).Value = market.Market_Have_InfantFood;
                        marketsSheet.Cell(rowStart + counter, 35).Value = market.M_Have_HH_CookingPots;
                        marketsSheet.Cell(rowStart + counter, 36).Value = market.M_Have_HH_Jerrycan;
                        marketsSheet.Cell(rowStart + counter, 37).Value = market.M_Have_HH_KitchenKnife;
                        marketsSheet.Cell(rowStart + counter, 38).Value = market.M_Have_HH_ServingSpoon;
                        marketsSheet.Cell(rowStart + counter, 39).Value = market.M_have_HH_StainlessSteelMugs;
                        marketsSheet.Cell(rowStart + counter, 40).Value = market.M_Have_HH_PlasticBucket;
                        marketsSheet.Cell(rowStart + counter, 41).Value = market.M_Have_HH_SteelPlates;
                        marketsSheet.Cell(rowStart + counter, 42).Value = market.M_Mave_HH_TableSizeSpoon;
                        marketsSheet.Cell(rowStart + counter, 43).Value = market.M_Have_HH_HandSoapBar;
                        marketsSheet.Cell(rowStart + counter, 44).Value = market.M_Have_HH_LaundrySoap;
                        marketsSheet.Cell(rowStart + counter, 45).Value = market.M_Have_HH_AplasticSoapDish_SoapCase;
                        marketsSheet.Cell(rowStart + counter, 46).Value = market.M_Have_HH_PlasticFoodCover;
                        marketsSheet.Cell(rowStart + counter, 47).Value = market.M_Have_HH_Broom;
                        marketsSheet.Cell(rowStart + counter, 48).Value = market.M_have_HH_GasCylinderWithAnAttachedStove;
                        marketsSheet.Cell(rowStart + counter, 49).Value = market.HeatingMaterialPriceHikeOnMonth.Replace(' ', ',');
                        marketsSheet.Cell(rowStart + counter, 50).Value = market.EnumeratorName;
                        marketsSheet.Cell(rowStart + counter, 51).Value = market.EnumeratorsObservation;
                    }
                }

                using (MemoryStream stream = new MemoryStream())
                {
                    wb.SaveAs(stream);
                    return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "HAP Market Survey.xlsx"));
                }
            }
        }
示例#3
0
        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"));
                }
            }
        }