コード例 #1
0
        /// <summary>
        /// R01 每日菜單報表
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        public IHttpActionResult GetDailyMealInfo([FromUri]ParmGetDailyMealInfo parm)
        {
            db0 = getDB0();
            try
            {
                R01_DailyMeal data = new R01_DailyMeal();
                Matters matters = new Matters();

                //取得正在用餐日期內的客戶生產編號
                var all_born_id = db0.RecordDetail.Where(x => x.product_type == (int)ProdyctType.PostnatalMeal & x.real_meal_start <= parm.meal_day & x.real_meal_end >= parm.meal_day & x.is_release == false & x.company_id == this.companyId)
                                                      .OrderBy(x => x.meal_id)
                                                      .Select(x => new { x.born_id, x.meal_id, x.real_meal_start, x.real_meal_end }).ToList();

                MealDay pause_meal = new MealDay();
                MealDay start_meal = new MealDay();
                MealDay end_meal = new MealDay();
                MealDaybyTryout tryout_meal = new MealDaybyTryout();
                #region 塞空資料
                pause_meal.breakfast = new List<string>();
                pause_meal.lunch = new List<string>();
                pause_meal.dinner = new List<string>();
                start_meal.breakfast = new List<string>();
                start_meal.lunch = new List<string>();
                start_meal.dinner = new List<string>();
                end_meal.breakfast = new List<string>();
                end_meal.lunch = new List<string>();
                end_meal.dinner = new List<string>();
                #endregion
                foreach (var born_id in all_born_id)
                {
                    #region 停餐
                    var pause_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                                                x.product_type == (int)ProdyctType.PostnatalMeal &
                                                                x.meal_day == parm.meal_day).FirstOrDefault();
                    if (pause_DailyMeal == null)
                    {
                        pause_meal.breakfast.Add(born_id.meal_id);
                        pause_meal.lunch.Add(born_id.meal_id);
                        pause_meal.dinner.Add(born_id.meal_id);
                    }
                    else
                    {
                        if (pause_DailyMeal.breakfast_state <= 0)
                            pause_meal.breakfast.Add(born_id.meal_id);
                        if (pause_DailyMeal.lunch_state <= 0)
                            pause_meal.lunch.Add(born_id.meal_id);
                        if (pause_DailyMeal.dinner_state <= 0)
                            pause_meal.dinner.Add(born_id.meal_id);
                    }
                    #endregion

                    #region 開始
                    var start_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                                                   x.product_type == (int)ProdyctType.PostnatalMeal &
                                                                  (x.breakfast_state > 0 || x.lunch_state > 0 || x.dinner_state > 0))
                                                       .OrderBy(x => x.meal_day).FirstOrDefault();
                    if (start_DailyMeal.meal_day == parm.meal_day)//開始用餐日期為當日
                    {
                        if (start_DailyMeal.breakfast_state > 0)
                        {
                            start_meal.breakfast.Add(born_id.meal_id);
                        }
                        else if (start_DailyMeal.lunch_state > 0)
                        {
                            start_meal.lunch.Add(born_id.meal_id);

                        }
                        else if (start_DailyMeal.dinner_state > 0)
                        {
                            start_meal.dinner.Add(born_id.meal_id);
                        }
                    }
                    #endregion

                    #region 結束
                    var end_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                            x.product_type == (int)ProdyctType.PostnatalMeal &
                                            (x.breakfast_state > 0 || x.lunch_state > 0 || x.dinner_state > 0))
                                            .OrderByDescending(x => x.meal_day).FirstOrDefault();
                    if (end_DailyMeal.meal_day == parm.meal_day)//結束用餐日期為當日
                    {
                        if (end_DailyMeal.dinner_state > 0)
                        {
                            end_meal.dinner.Add(born_id.meal_id);
                        }
                        else if (end_DailyMeal.lunch_state > 0)
                        {
                            end_meal.lunch.Add(born_id.meal_id);
                        }
                        else if (end_DailyMeal.breakfast_state > 0)
                        {
                            end_meal.breakfast.Add(born_id.meal_id);
                        }
                    }
                    #endregion
                }
                #region 試吃
                var tryout_DailyMeal = db0.DailyMeal.Where(x => x.product_type == (int)ProdyctType.Tryout &
                                                                 x.meal_day == parm.meal_day & x.company_id == this.companyId);
                tryout_meal.breakfast = tryout_DailyMeal.Where(x => x.breakfast_state > 0).Count();
                tryout_meal.lunch = tryout_DailyMeal.Where(x => x.lunch_state > 0).Count();
                tryout_meal.dinner = tryout_DailyMeal.Where(x => x.dinner_state > 0).Count();
                #endregion
                matters.pause_meal = pause_meal;
                matters.start_meal = start_meal;
                matters.end_meal = end_meal;
                matters.tryout_meal = tryout_meal;


                //取得今天用餐排程
                var getDailyMeal = db0.DailyMeal.Where(x => x.meal_day == parm.meal_day && x.product_type == (int)ProdyctType.PostnatalMeal & x.company_id == this.companyId).OrderBy(x => x.meal_id).ToList();

                List<Require> special_diet = new List<Require>();
                MealDiet breakfast = new MealDiet();
                MealDiet lunch = new MealDiet();
                MealDiet dinner = new MealDiet();
                breakfast.dishs = new List<Dish>();
                lunch.dishs = new List<Dish>();
                dinner.dishs = new List<Dish>();

                #region 取得三餐菜單
                //取得當日菜單
                var getDailyMenu = db0.DailyMenu.Where(x => x.day == parm.meal_day & x.company_id == this.companyId).ToList();
                foreach (var DailyMenu_item in getDailyMenu)
                {
                    #region 取得對應組合菜單
                    List<Dish> dishs = new List<Dish>();
                    var constitute_id = db0.DailyMenuOfConstitute.Where(x => x.dail_menu_id == DailyMenu_item.dail_menu_id & x.company_id == this.companyId)
                                           .OrderByDescending(x => x.ConstituteFood.All_Category_L2.sort).Select(x => new { x.constitute_id, x.ConstituteFood.constitute_name }).ToList();
                    foreach (var id in constitute_id)
                    {
                        List<Require> Empty_RequireData = new List<Require>();
                        var dish = new Dish()
                        {
                            constitute_id = id.constitute_id,
                            dish_name = id.constitute_name,
                            meal_diet = Empty_RequireData//暫時塞空資料
                        };
                        dishs.Add(dish);
                    }
                    #endregion
                    if (DailyMenu_item.meal_type == (int)MealType.Breakfast)
                    {
                        breakfast.dishs = dishs;
                        breakfast.isHaveData = true;
                        breakfast.count = getDailyMeal.Where(x => x.breakfast_state > 0).Count();
                    }
                    if (DailyMenu_item.meal_type == (int)MealType.Lunch)
                    {
                        lunch.dishs = dishs;
                        lunch.isHaveData = true;
                        lunch.count = getDailyMeal.Where(x => x.lunch_state > 0).Count();
                    }
                    if (DailyMenu_item.meal_type == (int)MealType.Dinner)
                    {
                        dinner.dishs = dishs;
                        dinner.isHaveData = true;
                        dinner.count = getDailyMeal.Where(x => x.dinner_state > 0).Count();
                    }
                }
                #endregion

                foreach (var DailyMeal_Item in getDailyMeal)
                {
                    if (DailyMeal_Item.breakfast_state > 0 || DailyMeal_Item.lunch_state > 0 || DailyMeal_Item.dinner_state > 0)
                    {//只要三餐有一餐有,就列特殊飲食
                     //取得該客戶需求元素id
                        var dietary_need_id = db0.CustomerOfDietaryNeed.Where(x => x.CustomerNeed.born_id == DailyMeal_Item.born_id & x.company_id == this.companyId).Select(x => x.dietary_need_id);

                        #region 無對應特殊飲食習慣
                        //未對應
                        var no_correspond = db0.DietaryNeed.Where(x => dietary_need_id.Contains(x.dietary_need_id) & !x.is_correspond & x.company_id == this.companyId).ToList();
                        foreach (var dn_item in no_correspond)
                        {
                            //檢查此特殊飲食是否出現過
                            bool check_r = special_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                            if (check_r)
                            {
                                var s = special_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                s.count = s.count + 1;
                                s.meal_id.Add(DailyMeal_Item.meal_id);
                            }
                            else
                            {
                                List<string> meal_id = new List<string>();
                                meal_id.Add(DailyMeal_Item.meal_id);
                                var s = new Require()
                                {
                                    dietary_need_id = dn_item.dietary_need_id,
                                    require_name = dn_item.short_name,
                                    count = 1,
                                    meal_id = meal_id
                                };
                                special_diet.Add(s);
                            }
                        }
                        #endregion

                        #region 有對應特殊飲食習慣
                        //有對應
                        var correspond = db0.DietaryNeed.Where(x => dietary_need_id.Contains(x.dietary_need_id) & x.is_correspond & x.company_id == this.companyId).ToList();
                        foreach (var dn_item in correspond)
                        {
                            #region 早餐
                            if (DailyMeal_Item.breakfast_state > 0 && dn_item.is_breakfast && breakfast.isHaveData)
                            {

                                foreach (var b in breakfast.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var b_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == b.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => b_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_b = b.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_b)
                                        {
                                            var s = b.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            b.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                            #region 午餐
                            if (DailyMeal_Item.lunch_state > 0 && dn_item.is_lunch && lunch.isHaveData)
                            {

                                foreach (var l in lunch.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var l_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == l.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => l_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_l = l.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_l)
                                        {
                                            var s = l.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            l.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                            #region 晚餐
                            if (DailyMeal_Item.dinner_state > 0 && dn_item.is_dinner && dinner.isHaveData)
                            {

                                foreach (var d in dinner.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var d_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == d.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => d_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_d = d.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_d)
                                        {
                                            var s = d.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            d.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                        }
                        #endregion

                    }
                }

                data.matters = matters;
                data.special_diet = special_diet;
                data.breakfast = breakfast;
                data.lunch = lunch;
                data.dinner = dinner;


                return Ok(data);
            }
            catch (Exception ex)
            {
                string test = ex.ToString();
                return Ok(test);
            }
            finally
            {
                db0.Dispose();
            }
        }
コード例 #2
0
        // GET: ExcelReport
        public FileResult downloadExcel_DailyMeal(ParmGetDailyMealInfo parm)
        {
            ExcelPackage excel = null;
            MemoryStream fs = null;
            var db0 = getDB0();
            try
            {

                fs = new MemoryStream();
                excel = new ExcelPackage(fs);
                excel.Workbook.Worksheets.Add("DailyMealData");
                ExcelWorksheet sheet = excel.Workbook.Worksheets["DailyMealData"];

                sheet.View.TabSelected = true;
                #region 取得用餐排程
                R01_DailyMeal data = new R01_DailyMeal();
                Matters matters = new Matters();

                //取得正在用餐日期內的客戶生產編號
                var all_born_id = db0.RecordDetail.Where(x => x.product_type == (int)ProdyctType.PostnatalMeal & x.real_meal_start <= parm.meal_day & x.real_meal_end >= parm.meal_day & x.is_release == false)
                                                      .OrderBy(x => x.meal_id)
                                                      .Select(x => new { x.born_id, x.meal_id, x.real_meal_start, x.real_meal_end }).ToList();

                MealDay pause_meal = new MealDay();
                MealDay start_meal = new MealDay();
                MealDay end_meal = new MealDay();
                MealDaybyTryout tryout_meal = new MealDaybyTryout();
                #region 塞空資料
                pause_meal.breakfast = new List<string>();
                pause_meal.lunch = new List<string>();
                pause_meal.dinner = new List<string>();
                start_meal.breakfast = new List<string>();
                start_meal.lunch = new List<string>();
                start_meal.dinner = new List<string>();
                end_meal.breakfast = new List<string>();
                end_meal.lunch = new List<string>();
                end_meal.dinner = new List<string>();
                #endregion
                foreach (var born_id in all_born_id)
                {
                    #region 停餐
                    var pause_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                                                x.product_type == (int)ProdyctType.PostnatalMeal &
                                                                x.meal_day == parm.meal_day).FirstOrDefault();
                    if (pause_DailyMeal == null)
                    {
                        pause_meal.breakfast.Add(born_id.meal_id);
                        pause_meal.lunch.Add(born_id.meal_id);
                        pause_meal.dinner.Add(born_id.meal_id);
                    }
                    else
                    {
                        if (pause_DailyMeal.breakfast_state <= 0)
                            pause_meal.breakfast.Add(born_id.meal_id);
                        if (pause_DailyMeal.lunch_state <= 0)
                            pause_meal.lunch.Add(born_id.meal_id);
                        if (pause_DailyMeal.dinner_state <= 0)
                            pause_meal.dinner.Add(born_id.meal_id);
                    }
                    #endregion

                    #region 開始
                    var start_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                                                   x.product_type == (int)ProdyctType.PostnatalMeal &
                                                                  (x.breakfast_state > 0 || x.lunch_state > 0 || x.dinner_state > 0))
                                                       .OrderBy(x => x.meal_day).FirstOrDefault();
                    if (start_DailyMeal.meal_day == parm.meal_day)//開始用餐日期為當日
                    {
                        if (start_DailyMeal.breakfast_state > 0)
                        {
                            start_meal.breakfast.Add(born_id.meal_id);
                        }
                        else if (start_DailyMeal.lunch_state > 0)
                        {
                            start_meal.lunch.Add(born_id.meal_id);

                        }
                        else if (start_DailyMeal.dinner_state > 0)
                        {
                            start_meal.dinner.Add(born_id.meal_id);
                        }
                    }
                    #endregion

                    #region 結束
                    var end_DailyMeal = db0.DailyMeal.Where(x => x.born_id == born_id.born_id &
                                            x.product_type == (int)ProdyctType.PostnatalMeal &
                                            (x.breakfast_state > 0 || x.lunch_state > 0 || x.dinner_state > 0))
                                            .OrderByDescending(x => x.meal_day).FirstOrDefault();
                    if (end_DailyMeal.meal_day == parm.meal_day)//結束用餐日期為當日
                    {
                        if (end_DailyMeal.dinner_state > 0)
                        {
                            end_meal.dinner.Add(born_id.meal_id);
                        }
                        else if (end_DailyMeal.lunch_state > 0)
                        {
                            end_meal.lunch.Add(born_id.meal_id);
                        }
                        else if (end_DailyMeal.breakfast_state > 0)
                        {
                            end_meal.breakfast.Add(born_id.meal_id);
                        }
                    }
                    #endregion
                }
                #region 試吃
                var tryout_DailyMeal = db0.DailyMeal.Where(x => x.product_type == (int)ProdyctType.Tryout &
                                                                 x.meal_day == parm.meal_day);
                tryout_meal.breakfast = tryout_DailyMeal.Where(x => x.breakfast_state > 0).Count();
                tryout_meal.lunch = tryout_DailyMeal.Where(x => x.lunch_state > 0).Count();
                tryout_meal.dinner = tryout_DailyMeal.Where(x => x.dinner_state > 0).Count();
                #endregion
                matters.pause_meal = pause_meal;
                matters.start_meal = start_meal;
                matters.end_meal = end_meal;
                matters.tryout_meal = tryout_meal;


                //取得今天用餐排程
                var getDailyMeal = db0.DailyMeal.Where(x => x.meal_day == parm.meal_day && x.product_type == (int)ProdyctType.PostnatalMeal).OrderBy(x => x.meal_id).ToList();

                List<Require> special_diet = new List<Require>();
                MealDiet breakfast = new MealDiet();
                MealDiet lunch = new MealDiet();
                MealDiet dinner = new MealDiet();
                breakfast.dishs = new List<Dish>();
                lunch.dishs = new List<Dish>();
                dinner.dishs = new List<Dish>();

                #region 取得三餐菜單
                //取得當日菜單
                var getDailyMenu = db0.DailyMenu.Where(x => x.day == parm.meal_day).ToList();
                foreach (var DailyMenu_item in getDailyMenu)
                {
                    #region 取得對應組合菜單
                    List<Dish> dishs = new List<Dish>();
                    var constitute_id = db0.DailyMenuOfConstitute.Where(x => x.dail_menu_id == DailyMenu_item.dail_menu_id)
                                                                 .OrderByDescending(x => x.ConstituteFood.All_Category_L2.sort).Select(x => new { x.constitute_id, x.ConstituteFood.constitute_name }).ToList();
                    foreach (var id in constitute_id)
                    {
                        List<Require> Empty_RequireData = new List<Require>();
                        var dish = new Dish()
                        {
                            constitute_id = id.constitute_id,
                            dish_name = id.constitute_name,
                            meal_diet = Empty_RequireData//暫時塞空資料
                        };
                        dishs.Add(dish);
                    }
                    #endregion
                    if (DailyMenu_item.meal_type == (int)MealType.Breakfast)
                    {
                        breakfast.dishs = dishs;
                        breakfast.isHaveData = true;
                        breakfast.count = getDailyMeal.Where(x => x.breakfast_state > 0).Count();
                    }
                    if (DailyMenu_item.meal_type == (int)MealType.Lunch)
                    {
                        lunch.dishs = dishs;
                        lunch.isHaveData = true;
                        lunch.count = getDailyMeal.Where(x => x.lunch_state > 0).Count();
                    }
                    if (DailyMenu_item.meal_type == (int)MealType.Dinner)
                    {
                        dinner.dishs = dishs;
                        dinner.isHaveData = true;
                        dinner.count = getDailyMeal.Where(x => x.dinner_state > 0).Count();
                    }
                }
                #endregion

                foreach (var DailyMeal_Item in getDailyMeal)
                {
                    if (DailyMeal_Item.breakfast_state > 0 || DailyMeal_Item.lunch_state > 0 || DailyMeal_Item.dinner_state > 0)
                    {//只要三餐有一餐有,就列特殊飲食
                        //取得該客戶需求元素id
                        var dietary_need_id = db0.CustomerOfDietaryNeed.Where(x => x.CustomerNeed.born_id == DailyMeal_Item.born_id).Select(x => x.dietary_need_id);

                        #region 無對應特殊飲食習慣
                        //未對應
                        var no_correspond = db0.DietaryNeed.Where(x => dietary_need_id.Contains(x.dietary_need_id) & !x.is_correspond).ToList();
                        foreach (var dn_item in no_correspond)
                        {
                            //檢查此特殊飲食是否出現過
                            bool check_r = special_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                            if (check_r)
                            {
                                var s = special_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                s.count = s.count + 1;
                                s.meal_id.Add(DailyMeal_Item.meal_id);
                            }
                            else
                            {
                                List<string> meal_id = new List<string>();
                                meal_id.Add(DailyMeal_Item.meal_id);
                                var s = new Require()
                                {
                                    dietary_need_id = dn_item.dietary_need_id,
                                    require_name = dn_item.short_name,
                                    count = 1,
                                    meal_id = meal_id
                                };
                                special_diet.Add(s);
                            }
                        }
                        #endregion

                        #region 有對應特殊飲食習慣
                        //有對應
                        var correspond = db0.DietaryNeed.Where(x => dietary_need_id.Contains(x.dietary_need_id) & x.is_correspond).ToList();
                        foreach (var dn_item in correspond)
                        {
                            #region 早餐
                            if (DailyMeal_Item.breakfast_state > 0 && dn_item.is_breakfast && breakfast.isHaveData)
                            {

                                foreach (var b in breakfast.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var b_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == b.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => b_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_b = b.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_b)
                                        {
                                            var s = b.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            b.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                            #region 午餐
                            if (DailyMeal_Item.lunch_state > 0 && dn_item.is_lunch && lunch.isHaveData)
                            {

                                foreach (var l in lunch.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var l_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == l.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => l_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_l = l.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_l)
                                        {
                                            var s = l.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            l.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                            #region 晚餐
                            if (DailyMeal_Item.dinner_state > 0 && dn_item.is_dinner && dinner.isHaveData)
                            {

                                foreach (var d in dinner.dishs)
                                {
                                    //取得菜單元素對應元素
                                    var d_element_id = db0.ConstituteOfElement.Where(x => x.constitute_id == d.constitute_id).Select(x => x.element_id).ToList();
                                    //如果和需求元素對應元素d有重疊
                                    bool check_element_id = db0.DietaryNeedOfElement.Any(x => d_element_id.Contains(x.element_id) && x.dietary_need_id == dn_item.dietary_need_id);
                                    #region 有重疊就加入
                                    if (check_element_id)
                                    {
                                        //檢查此飲食是否在此餐出現過
                                        bool check_d = d.meal_diet.Any(x => x.dietary_need_id == dn_item.dietary_need_id);
                                        if (check_d)
                                        {
                                            var s = d.meal_diet.Where(x => x.dietary_need_id == dn_item.dietary_need_id).FirstOrDefault();
                                            s.count = s.count + 1;
                                            s.meal_id.Add(DailyMeal_Item.meal_id);
                                        }
                                        else
                                        {
                                            List<string> meal_id = new List<string>();
                                            meal_id.Add(DailyMeal_Item.meal_id);
                                            var s = new Require()
                                            {
                                                dietary_need_id = dn_item.dietary_need_id,
                                                require_name = dn_item.short_name,
                                                count = 1,
                                                meal_id = meal_id
                                            };
                                            d.meal_diet.Add(s);
                                        }
                                    }
                                    #endregion
                                }
                            }
                            #endregion
                        }
                        #endregion

                    }
                }

                data.matters = matters;
                data.special_diet = special_diet;
                data.breakfast = breakfast;
                data.lunch = lunch;
                data.dinner = dinner;

                #endregion


                #region Excel Handle

                int detail_row = 8;

                #region 標題
                sheet.Cells[1, 1].Value = "R01每日菜單報表-" + parm.meal_day.ToString("yyyy-MM-dd");
                sheet.Cells[1, 1, 1, 6].Merge = true;
                #region 當日事項
                sheet.Cells[2, 1].Value = "[當日事項]";
                sheet.Cells[2, 1, 2, 6].Merge = true;
                setFontColor_green(sheet, 2, 1);
                setGreenTitle(sheet, 2, 1, 6);
                #endregion
                #region 特殊飲食
                sheet.Cells[detail_row, 1].Value = "[特殊飲食]";
                sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                setFontColor_red(sheet, detail_row, 1);
                setRedTitle(sheet, detail_row, 1, 6);
                #endregion
                #endregion

                #region 內容
                #region 當日事項
                #region 停餐
                #region 早
                string p_breakfast = "停早(" + data.matters.pause_meal.breakfast.Count() + "):";
                foreach (var i in data.matters.pause_meal.breakfast)
                {
                    p_breakfast += i + "、";
                }
                sheet.Cells[3, 1].Value = p_breakfast;
                sheet.Cells[3, 1, 3, 2].Merge = true;
                #endregion
                #region 午
                string p_lunch = "停午(" + data.matters.pause_meal.lunch.Count() + "):";
                foreach (var i in data.matters.pause_meal.lunch)
                {
                    p_lunch += i + "、";
                }
                sheet.Cells[3, 3].Value = p_lunch;
                sheet.Cells[3, 3, 3, 4].Merge = true;
                #endregion
                #region 晚
                string p_dinner = "停晚(" + data.matters.pause_meal.dinner.Count() + "):";
                foreach (var i in data.matters.pause_meal.dinner)
                {
                    p_dinner += i + "、";
                }
                sheet.Cells[3, 5].Value = p_dinner;
                sheet.Cells[3, 5, 3, 6].Merge = true;
                #endregion
                #endregion

                #region 開始用餐
                #region 早
                string s_breakfast = "早開始(" + data.matters.start_meal.breakfast.Count() + "):";
                foreach (var i in data.matters.start_meal.breakfast)
                {
                    s_breakfast += i + "、";
                }
                sheet.Cells[4, 1].Value = s_breakfast;
                sheet.Cells[4, 1, 4, 2].Merge = true;
                #endregion
                #region 午
                string s_lunch = "午開始(" + data.matters.start_meal.lunch.Count() + "):";
                foreach (var i in data.matters.start_meal.lunch)
                {
                    s_lunch += i + "、";
                }
                sheet.Cells[4, 3].Value = s_lunch;
                sheet.Cells[4, 3, 4, 4].Merge = true;
                #endregion
                #region 晚
                string s_dinner = "晚開始(" + data.matters.start_meal.dinner.Count() + "):";
                foreach (var i in data.matters.start_meal.dinner)
                {
                    s_dinner += i + "、";
                }
                sheet.Cells[4, 5].Value = s_dinner;
                sheet.Cells[4, 5, 4, 6].Merge = true;
                #endregion
                #endregion

                #region 結束用餐
                #region 早
                string e_breakfast = "早結束(" + data.matters.end_meal.breakfast.Count() + "):";
                foreach (var i in data.matters.end_meal.breakfast)
                {
                    e_breakfast += i + "、";
                }
                sheet.Cells[5, 1].Value = e_breakfast;
                sheet.Cells[5, 1, 5, 2].Merge = true;
                #endregion
                #region 午
                string e_lunch = "午結束(" + data.matters.end_meal.lunch.Count() + "):";
                foreach (var i in data.matters.end_meal.lunch)
                {
                    e_lunch += i + "、";
                }
                sheet.Cells[5, 3].Value = e_lunch;
                sheet.Cells[5, 3, 5, 4].Merge = true;
                #endregion
                #region 晚
                string e_dinner = "晚結束(" + data.matters.end_meal.dinner.Count() + "):";
                foreach (var i in data.matters.end_meal.dinner)
                {
                    e_dinner += i + "、";
                }
                sheet.Cells[5, 5].Value = e_dinner;
                sheet.Cells[5, 5, 5, 6].Merge = true;
                #endregion
                #endregion


                #region 試吃
                #region 早
                string t_breakfast = "早試吃(" + data.matters.tryout_meal.breakfast + ")";
                sheet.Cells[6, 1].Value = t_breakfast;
                sheet.Cells[6, 1, 6, 2].Merge = true;
                #endregion
                #region 午
                string t_lunch = "午試吃(" + data.matters.tryout_meal.lunch + ")";
                sheet.Cells[6, 3].Value = t_lunch;
                sheet.Cells[6, 3, 6, 4].Merge = true;
                #endregion
                #region 晚
                string t_dinner = "晚試吃(" + data.matters.tryout_meal.dinner + ")";
                sheet.Cells[6, 5].Value = t_dinner;
                sheet.Cells[6, 5, 6, 6].Merge = true;
                #endregion
                #endregion
                #endregion

                #region 特殊飲食
                detail_row += 1;
                foreach (var i in data.special_diet)
                {
                    sheet.Cells[detail_row, 1].Value = i.require_name + "(" + i.count + "):";
                    string tmp = "";
                    foreach (var id in i.meal_id)
                    {
                        tmp += id + "、";
                    }

                    sheet.Cells[detail_row, 2].Value = tmp;
                    sheet.Cells[detail_row, 2, detail_row, 6].Merge = true;
                    detail_row++;
                }
                #endregion

                #region 早餐
                detail_row += 1;
                #region 標題
                sheet.Cells[detail_row, 1].Value = "[早餐(" + data.breakfast.count + ")]";
                sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                setFontColor_orange(sheet, detail_row, 1);
                setYellowTitle(sheet, detail_row, 1, 6);
                #endregion

                detail_row += 1;
                foreach (var i in data.breakfast.dishs)
                {
                    int start = detail_row;
                    sheet.Cells[detail_row, 1].Value = i.dish_name;
                    foreach (var j in i.meal_diet)
                    {
                        sheet.Cells[detail_row, 2].Value = j.require_name + "(" + j.count + "):";
                        string tmp = "";
                        foreach (var id in j.meal_id)
                        {
                            tmp += id + "、";
                        }

                        sheet.Cells[detail_row, 3].Value = tmp;
                        sheet.Cells[detail_row, 3, detail_row, 6].Merge = true;
                        detail_row++;
                    }
                    if (i.meal_diet.Count() == 0)
                    {
                        sheet.Cells[detail_row, 2, detail_row, 6].Merge = true;
                        detail_row++;
                    }
                    else
                    {
                        sheet.Cells[start, 1, detail_row - 1, 1].Merge = true;
                        sheet.Cells[start, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    }
                }
                if (data.breakfast.dishs.Count() == 0)
                {
                    sheet.Cells[detail_row, 1].Value = "目前暫無排餐!";
                    sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                    sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    detail_row++;
                }

                #endregion
                #region 午餐
                detail_row += 1;
                #region 標題
                sheet.Cells[detail_row, 1].Value = "[午餐(" + data.lunch.count + ")]";
                sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                setFontColor_orange(sheet, detail_row, 1);
                setYellowTitle(sheet, detail_row, 1, 6);
                #endregion

                detail_row += 1;
                foreach (var i in data.lunch.dishs)
                {
                    int start = detail_row;
                    sheet.Cells[detail_row, 1].Value = i.dish_name;
                    foreach (var j in i.meal_diet)
                    {
                        sheet.Cells[detail_row, 2].Value = j.require_name + "(" + j.count + "):";
                        string tmp = "";
                        foreach (var id in j.meal_id)
                        {
                            tmp += id + "、";
                        }

                        sheet.Cells[detail_row, 3].Value = tmp;
                        sheet.Cells[detail_row, 3, detail_row, 6].Merge = true;
                        detail_row++;
                    }

                    if (i.meal_diet.Count() == 0)
                    {
                        sheet.Cells[detail_row, 2, detail_row, 6].Merge = true;
                        detail_row++;
                    }
                    else
                    {
                        sheet.Cells[start, 1, detail_row - 1, 1].Merge = true;
                        sheet.Cells[start, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    }
                }
                if (data.lunch.dishs.Count() == 0)
                {
                    sheet.Cells[detail_row, 1].Value = "目前暫無排餐!";
                    sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                    sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    detail_row++;
                }

                #endregion
                #region 晚餐
                detail_row += 1;
                #region 標題
                sheet.Cells[detail_row, 1].Value = "[晚餐(" + data.dinner.count + ")]";
                sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                setFontColor_orange(sheet, detail_row, 1);
                setYellowTitle(sheet, detail_row, 1, 6);
                #endregion

                detail_row += 1;
                foreach (var i in data.dinner.dishs)
                {
                    int start = detail_row;
                    sheet.Cells[detail_row, 1].Value = i.dish_name;
                    foreach (var j in i.meal_diet)
                    {
                        sheet.Cells[detail_row, 2].Value = j.require_name + "(" + j.count + "):";
                        string tmp = "";
                        foreach (var id in j.meal_id)
                        {
                            tmp += id + "、";
                        }

                        sheet.Cells[detail_row, 3].Value = tmp;
                        sheet.Cells[detail_row, 3, detail_row, 6].Merge = true;
                        detail_row++;
                    }

                    if (i.meal_diet.Count() == 0)
                    {
                        sheet.Cells[detail_row, 2, detail_row, 6].Merge = true;
                        detail_row++;
                    }
                    else
                    {
                        sheet.Cells[start, 1, detail_row - 1, 1].Merge = true;
                        sheet.Cells[start, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    }
                }
                if (data.dinner.dishs.Count() == 0)
                {
                    sheet.Cells[detail_row, 1].Value = "目前暫無排餐!";
                    sheet.Cells[detail_row, 1, detail_row, 6].Merge = true;
                    sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    detail_row++;
                }

                #endregion
                #endregion

                #region excel排版
                int startColumn = sheet.Dimension.Start.Column;
                int endColumn = sheet.Dimension.End.Column;
                for (int j = startColumn; j <= endColumn; j++)
                {
                    //sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
                    //sheet.Column(j).Width = 30;//固定寬度寫法
                    sheet.Column(j).AutoFit();//依內容fit寬度
                }//End for
                #endregion
                //sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的

                #endregion

                string filename = "R01每日菜單報表" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
                excel.Save();
                fs.Position = 0;
                return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            finally
            {
                db0.Dispose();
            }
        }