demo20230512/Service/RiskAssmt/ItemPurchaseDataService.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Weee.DAL;
using Weee.Models;

namespace Weee.Service
{
    public class ItemPurchaseDataService : WeeeLCADataService
    {
        public ItemPurchaseDataService(WeeeDataContext db)
            : base(db)
        {
        }

        public IQueryable<LCARiskAssmtSurveyForm_ItemPurchase> GetList(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorized");
            var qry = _db.Set<LCARiskAssmtSurveyForm_ItemPurchase>().Where(x => x.LCAID == LCAID)
                .OrderBy(x=>x.MaterialNo).ThenBy(x => x.ID);
            return qry;
        }
        /// <summary>
        /// 將物料同步到物料運輸處
        /// </summary>
        /// <param name="LCAID"></param>
        /// <exception cref="Exception"></exception>
        public void Sync(int LCAID)
        {
            string sql = @"
insert into LCARiskAssmtSurveyForm_ItemPurchase
	(LCAID, MaterialNo, ElementName, ElementWeight
	, ElementWeightUnit, PurchaseAmount, ActivityIntensity
    , ShippingAmt, ShippingDistance, TKM, TransportLand
    , TransportSea, TransportAir, TransportLand3, TransportTkmland
    , TransportTkmsea, TransportTkmair, TransportTkmland3, TotalTkmland
    , TransportLandCo2e, TransportSeaCo2e, TransportAirCo2e
    )
select a.LCAID, a.MaterialNo, a.MaterialName, a.MaterialSpec
	, a.Unit, a.AnnualPurchaseAmount, a.ActivityIntensity
    , 0, 0, 0, 0
    , 0, 0, 0, 0
    , 0, 0, 0, 0
    , 0, 0, 0
from LCARiskAssmtSurveyForm_MaterialC3Emission a
left join LCARiskAssmtSurveyForm_ItemPurchase b on a.LCAID=b.LCAID and a.MaterialNo=b.MaterialNo
where ( b.LCAID is null or b.MaterialNo is null) and a.LCAID=@LCAID
";
            int rows = _db.Database.ExecuteSqlCommand(sql, new SqlParameter("@LCAID", LCAID));
            //var qry = (from a in _db.LCARiskAssmtSurveyForm_MaterialC3Emission
            //           join b in _db.LCARiskAssmtSurveyForm_ItemPurchase
            //                on new { LCAID = a.LCAID, MaterialNo = a.MaterialNo }
            //                equals new { LCAID = b.LCAID, MaterialNo = b.MaterialNo }
            //                into ab
            //           from x in ab.DefaultIfEmpty()
            //           where (x.MaterialNo==null /*|| x.LCAID==null*/) && a.MaterialNo !=null
            //           select new
            //           {
            //               LCAID = a.LCAID,
            //               MaterialNo = a.MaterialNo,
            //               MaterialName = a.MaterialName,
            //               MaterialSpec = a.MaterialSpec,
            //               Unit = a.Unit,
            //               AnnualPurchaseAmount = a.AnnualPurchaseAmount,
            //               ActivityIntensity = a.ActivityIntensity
            //           }).AsQueryable();
            //if (qry.Any())
            //{
            //    foreach(var rec in qry.ToList())
            //    {
            //        LCARiskAssmtSurveyForm_ItemPurchase entity =
            //            new LCARiskAssmtSurveyForm_ItemPurchase();
            //        entity.LCAID = inLCAID;
            //        entity.MaterialNo = rec.MaterialNo;
            //        entity.ElementName = rec.MaterialName;
            //        entity.ElementWeight = rec.MaterialSpec;
            //        entity.ElementWeightUnit=rec.Unit;
            //        entity.PurchaseAmount = rec.AnnualPurchaseAmount;
            //        entity.ActivityIntensity = rec.ActivityIntensity;
            //        _db.LCARiskAssmtSurveyForm_ItemPurchase.Add(entity);
            //    }
            //    _db.SaveChanges();
            //}

            //var user = GetUserContext();
            //var list = GetList(LCAID).ToList(); // destination
            //var analyses = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.LCAID == LCAID).ToList();
            //var factors = _db.Set<LCARiskAssmtSurveyForm_AssmtFactor>().Where(x => x.LCAID == LCAID).ToList();

            //if (analyses.Count > 0)
            //{
            //    foreach (var lifecycleAssmt in analyses) // source
            //    {
            //        if (!list.Any(s => s.LifeCycleStage == lifecycleAssmt.LifeCycleStage && s.GHGEvaluateItem == lifecycleAssmt.GHGEvaluateItem))
            //        {
            //            var toBeAdd = new LCARiskAssmtSurveyForm_SignificanceAssmt()
            //            {
            //                LCAID = LCAID,
            //                LifeCycleStage = lifecycleAssmt.LifeCycleStage,
            //                GHGEvaluateItem = lifecycleAssmt.GHGEvaluateItem,
            //                CreatedBy = user.Id,
            //                CreatedDate = DateTime.Now
            //            };

            //            SetCategory(toBeAdd);

            //            foreach (var factor in factors)
            //            {
            //                toBeAdd.FactorScores.Add(new LCARiskAssmtSurveyForm_SignificanceAssmtFactor()
            //                {
            //                    AssmtFactorID = factor.ID
            //                });
            //            }

            //            _db.Set<LCARiskAssmtSurveyForm_SignificanceAssmt>().Add(toBeAdd);
            //            _db.Entry(toBeAdd).State = EntityState.Added;
            //        }
            //    }

            //    _db.SaveChanges();
            //}
            //else
            //{
            //    throw new Exception("从生命週期流程圖及評估複製資料失败！");
            //}
        }

        public LCARiskAssmtSurveyForm_ItemPurchase Save(LCARiskAssmtSurveyForm_ItemPurchase toBeSave)
        {
            var user = GetUserContext();

            if (!AuthorizedLCAs.Contains(toBeSave.LCAID)) throw new Exception("not authorized");
            var entry = _db.Entry(toBeSave);
            if (toBeSave.ID == 0)
            {
                toBeSave.CreatedBy = user.Id;
                toBeSave.CreatedDate = DateTime.Now;
                entry.State = EntityState.Added;
            }
            else
            {
                toBeSave.ModifiedBy = user.Id;
                toBeSave.ModifiedDate = DateTime.Now;
                entry.State = EntityState.Modified;
                entry.Property(x => x.LCAID).IsModified = false;
            }

            _db.SaveChanges();
            return toBeSave;
        }

        public void Delete(int ID)
        {
            var entry = _db.Set<LCARiskAssmtSurveyForm_ItemPurchase>().Where(x => x.ID == ID).Single();
            var LCA = GetLCA(entry.LCAID);
            if (LCA.Status != LCAStatus.New && LCA.Status != LCAStatus.Processing) throw new Exception("Business logic error , should not delete data in this status: " + LCA.Status.ToString());

            var ToBeDelete = _db.Entry(entry);
            ToBeDelete.State = EntityState.Deleted;

            _db.SaveChanges();
        }
    }
}