public TransList getUser(int pg, int tk, string fnd)
{
try
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
var user = (from u in entPrince.User select new { u.Id, u.Code, u.EmailAdd, u.FirstName, u.LastName, u.Status })
.OrderBy(od => od.LastName)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = entPrince.User.AsNoTracking().Count();
transList.data = user;
return(transList);
}
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public TransList getPrice(int pg, int tk)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var priceband = (from s in enBi.STG_DIM_PRICE_BAND
select new {
s.ID,
s.PRICE_BAND_CODE,
s.PRICE_BAND_DESC,
s.PROD_LEVEL1_CODE,
s.PRICE_BAND_LOWER,
s.PRICE_BAND_UPPER,
s.PRICE_BAND_SEQ,
s.ARC_DATE
})
.OrderBy(od => od.PRICE_BAND_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_DIM_PRICE_BAND.AsNoTracking().Count();
transList.data = priceband;
return(transList);
}
}
public HttpResponseMessage addFootfall(dynamic pFootfall)
{
try
{
using (PRINCE_STGEntities dbCtx = new PRINCE_STGEntities())
{
STG_STR_FOOTFALL foot = new STG_STR_FOOTFALL();
foot.FOOTFALL_DATE = pFootfall.FOOTFALL_DATE;
foot.STORE_CODE = pFootfall.STORE_CODE;
foot.FOOTFALL_COUNT = pFootfall.FOOTFALL_COUNT;
foot.ARC_DATE = DateTime.Now;
dbCtx.STG_STR_FOOTFALL.Add(foot);
dbCtx.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.Created, "Footfall successfully created."));
}
}
//catch (DbEntityValidationException ex)
//{
// ExceptionEntity exDesc = new ExceptionEntity(ex);
// throw new ApiException() { HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException() };
//}
//catch (Exception ex)
//{
// ExceptionDescription exDesc = new ExceptionDescription(ex);
// throw new ApiException() { HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException() };
//}
catch (Exception ex)
{
Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
public HttpResponseMessage getSite()
{
try
{
using (PRINCE_STGEntities enBI = new PRINCE_STGEntities())
{
enBI.Configuration.LazyLoadingEnabled = false;
List <Site> siteList = enBI.Site.Where(a => a.Status == "A").OrderBy(od => od.Description).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, siteList));
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public HttpResponseMessage getSiteById(int id)
{
try
{
using (PRINCE_STGEntities enCarina = new PRINCE_STGEntities())
{
enCarina.Configuration.LazyLoadingEnabled = false;
Site site = enCarina.Site.Where(wr => wr.SiteId == id).SingleOrDefault();
return(Request.CreateResponse(HttpStatusCode.OK, site));
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public List <User> getUser()
{
try {
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
List <User> user = (from u in entPrince.User select u).ToList();
return(user);
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public UserMenu getUserMenu(int pUserCode, int pMenuId)
{
try {
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
UserMenu menu = (from m in entPrince.UserMenu where m.UserId == pUserCode && m.MenuId == pMenuId select m).SingleOrDefault();
return(menu);
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public HttpResponseMessage updateSite(dynamic pSite)
{
try
{
using (var dbCtx = new PRINCE_STGEntities())
{
int tempSite = pSite.SiteId;
String tempSiteDesc = pSite.Description;
Site site = dbCtx.Site.AsNoTracking().Where(wr => wr.SiteId == tempSite).SingleOrDefault();
site.Description = tempSiteDesc;
site.Status = pSite.Status;
dbCtx.Entry(site).State = System.Data.Entity.EntityState.Modified;
dbCtx.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "Site " + site.Description.Trim() + " is successfully Updated."));
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public HttpResponseMessage addTarget(dynamic pTarget)
{
try
{
using (PRINCE_STGEntities dbCtx = new PRINCE_STGEntities())
{
STG_TARGET target = new STG_TARGET();
target.TARGET_DATE = pTarget.TARGET_DATE;
target.GEO_LEVEL1_CODE = "99999";
target.GEO_LEVEL2_CODE = "99999";
target.GEO_LEVEL3_CODE = "99999";
target.STORE_CODE = pTarget.STORE_CODE;
target.PROD_LEVEL1_CODE = pTarget.PROD_LEVEL1_CODE;
target.PROD_LEVEL2_CODE = pTarget.PROD_LEVEL2_CODE;
target.PROD_LEVEL3_CODE = "99999";
target.PROD_LEVEL4_CODE = "99999";
target.PROD_LEVEL5_CODE = "99999";
target.PRODUCT_CODE = "99999";
target.TARGET_SALE_QTY = 99999;
target.TARGET_SALE_VAL_AT_PRICE = pTarget.TARGET_SALE_VAL_AT_PRICE;
target.TARGET_SALE_VAL_AT_COST = 99999;
target.ARC_DATE = DateTime.Now;
dbCtx.STG_TARGET.Add(target);
dbCtx.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.Created, "Target successfully created."));
}
}
catch (Exception ex)
{
Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
public List <TEMP_DC> getStoreDc()
{
try
{
TransList trans = new TransList();
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
List <TEMP_DC> listDc = enBi.TEMP_DC.AsNoTracking().ToList();
// trans.data = listDc;
return(listDc);
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public TransList getStockAgeByCode(int pg, int tk, string fnd, string pCode)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var stockage = (from s in enBi.STG_DIM_STOCKAGE_BAND
where s.STKAGE_PROD_LEVEL1_CODE == pCode
select new
{
s.ID,
s.STOCKAGE_BAND_DESC,
s.STKAGE_PROD_LEVEL1_CODE,
s.STOCKAGE_BAND_LOWER,
s.STOCKAGE_BAND_UPPER,
s.STOCKAGE_BAND_SEQ,
s.LATEST,
s.ARC_DATE
})
.OrderBy(od => od.STOCKAGE_BAND_DESC)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_DIM_STOCKAGE_BAND.AsNoTracking().Where(st => st.STKAGE_PROD_LEVEL1_CODE == pCode).Count();
transList.data = stockage;
return(transList);
}
}
public List <Menu> getMenu(int id)
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
int iId = id;
int?[] arMenu = (from a in enBi.UserMenu.AsNoTracking() where a.UserId == iId && a.Status == "A" select a.MenuId).ToArray();
var menu = (from a in enBi.Menu.AsNoTracking() where a.Status == "A" && arMenu.Contains(a.Id) select a).OrderBy(c => c.LevelNo).ThenBy(d => d.SortNo).ToList();
return(menu);
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public TransList getPriceByDate(int pg, int tk, string fnd, string pCode)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var priceband = (from p in enBi.STG_DIM_PRICE_BAND
where p.PROD_LEVEL1_CODE == pCode
select new
{
p.ID,
p.PRICE_BAND_CODE,
p.PRICE_BAND_DESC,
p.PROD_LEVEL1_CODE,
p.PRICE_BAND_LOWER,
p.PRICE_BAND_UPPER,
p.PRICE_BAND_SEQ,
p.ARC_DATE
})
.OrderBy(od => od.PRICE_BAND_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_DIM_PRICE_BAND.AsNoTracking().Where(st => st.PROD_LEVEL1_CODE == pCode).Count();
transList.data = priceband;
return(transList);
}
}
public TransList getUserActive()
{
try
{
TransList transList = new TransList();
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
var user = (from u in entPrince.User where u.Status == "A"
select new {
u.Id,
u.Code,
u.EmailAdd,
u.FirstName,
u.LastName,
u.Status
})
.OrderBy(od => od.LastName)
.ToList();
transList.totalCount = entPrince.User.AsNoTracking().Count();
transList.data = user;
return(transList);
}
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
//public HttpResponseMessage addStoreMerch(dynamic sStoreMerch)
//{
// try
// {
// using (PRINCE_STGEntities dbCtx = new PRINCE_STGEntities())
// {
// STG_merch merch = new STG_merch();
// merch.merch_DATE = sStoreMerch.merch_DATE;
// merch.GEO_LEVEL1_CODE = "99999";
// merch.GEO_LEVEL2_CODE = "99999";
// merch.GEO_LEVEL3_CODE = "99999";
// merch.STORE_CODE = sStoreMerch.STORE_CODE;
// merch.PROD_LEVEL1_CODE = sStoreMerch.PROD_LEVEL1_CODE;
// merch.PROD_LEVEL2_CODE = sStoreMerch.PROD_LEVEL2_CODE;
// merch.PROD_LEVEL3_CODE = sStoreMerch.PROD_LEVEL3_CODE;
// merch.PROD_LEVEL4_CODE = "99999";
// merch.PROD_LEVEL5_CODE = "99999";
// merch.PRODUCT_CODE = "99999";
// merch.merch_VERSION = 99999;
// merch.merch_SALE_QTY = 99999;
// merch.merch_SALE_VAL_AT_PRICE = sStoreMerch.merch_SALE_VAL_AT_PRICE;
// merch.merch_SALE_VAL_AT_COST = 99999;
// merch.merch_MARKDOWN_QTY = 0;
// merch.merch_MARKDOWN_VAL = 0;
// merch.merch_SHRINKAGE_VAL = 0;
// merch.merch_PURCHASE_QTY = 0;
// merch.merch_PURCHASE_VAL = 0;
// merch.merch_INV_OPENING_QTY = 0;
// merch.merch_OPENING_VAL_AT_COST = 0;
// merch.merch_OPENING_VAL_AT_PRICE = 0;
// merch.merch_OTH1 = 99999;
// merch.merch_OTH2 = 99999;
// merch.merch_OTH3 = 99999;
// merch.merch_OTH4 = 99999;
// merch.merch_OTH5 = 99999;
// merch.ARC_DATE = DateTime.Now;
// dbCtx.STG_merch.Add(merch);
// dbCtx.SaveChanges();
// return Request.CreateResponse(HttpStatusCode.Created, "merch successfully created.");
// }
// }
// catch (Exception ex)
// {
// Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
// }
// return Request.CreateResponse(HttpStatusCode.OK);
//}
public HttpResponseMessage updateStoreMerch(dynamic sStoreMerch)
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
using (var dbCtxTran = enBi.Database.BeginTransaction())
{
//DateTime merchdate = sStoreMerch.merch_DATE;
int id = sStoreMerch.ID;
//string storecode = sStoreMerch.STORE_CODE;
STG_STR_MERCH_DET merch = enBi.STG_STR_MERCH_DET.Find(id);
if (merch != null)
{
merch.ID = sStoreMerch.ID;
merch.STRMRGRP_DATE = sStoreMerch.STRMRGRP_DATE;
merch.STORE_CODE = sStoreMerch.STORE_CODE;
merch.PROD_LEVEL1_CODE = sStoreMerch.PROD_LEVEL1_CODE;
merch.PROD_LEVEL2_CODE = sStoreMerch.PROD_LEVEL2_CODE;
merch.PROD_LEVEL3_CODE = sStoreMerch.PROD_LEVEL3_CODE;
merch.PROD_LEVEL4_CODE = sStoreMerch.PROD_LEVEL4_CODE;
merch.SELLING_AREA = sStoreMerch.SELLING_AREA;
merch.TOTAL_AREA = sStoreMerch.TOTAL_AREA;
merch.PERMENANT_FTE = sStoreMerch.PERMENANT_FTE;
merch.CONTRACT_FTE = sStoreMerch.CONTRACT_FTE;
merch.OTH_DET1 = sStoreMerch.OTH_DET1;
merch.OTH_DET2 = sStoreMerch.OTH_DET2;
merch.OTH_DET3 = sStoreMerch.OTH_DET3;
merch.OTH_DET4 = sStoreMerch.OTH_DET4;
merch.OTH_DET5 = sStoreMerch.OTH_DET5;
merch.ARC_DATE = sStoreMerch.ARC_DATE;
enBi.Entry(merch).State = System.Data.Entity.EntityState.Modified;
enBi.SaveChanges();
dbCtxTran.Commit();
return(Request.CreateResponse(HttpStatusCode.OK, "Store Merch successfully Updated."));
}
}
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
return(Request.CreateResponse(HttpStatusCode.OK, "Storemerch successfully Updated."));
}
public HttpResponseMessage getUserMenuList(int pUserCode)
{
try
{
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
// List<UserMenu> menu = (from m in entPrince.UserMenu where m.UserId == pUserCode select m).ToList();
var menuList = from um in entPrince.UserMenu join m in entPrince.Menu on um.MenuId equals m.Id where um.UserId == pUserCode
select new {
m.Code,
m.Name,
m.ParentId,
m.LevelNo,
m.SortNo,
um.CanAdd,
um.CanDelete,
um.CanEdit,
um.CanView,
um.UserId
};
List <TempUserMenu> tempUserMenu = new List <TempUserMenu>();
foreach (var m in menuList)
{
TempUserMenu tu = new TempUserMenu();
tu.Code = m.Code;
tu.Name = m.Name;
tu.ParentId = m.ParentId;
tu.LevelNo = m.LevelNo;
tu.SortNo = m.SortNo;
tu.canAdd = m.CanAdd;
tu.canDelete = m.CanDelete;
tu.canEdit = m.CanEdit;
tu.canView = m.CanView;
tu.UserId = m.UserId;
tempUserMenu.Add(tu);
}
return(Request.CreateResponse(HttpStatusCode.OK, tempUserMenu));
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public HttpResponseMessage updateTarget(dynamic pTarget)
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
using (var dbCtxTran = enBi.Database.BeginTransaction())
{
//DateTime merchdate = sStoreMerch.merch_DATE;
int id = pTarget.ID;
//string storecode = sStoreMerch.STORE_CODE;
STG_TARGET target = enBi.STG_TARGET.Find(id);
if (target != null)
{
target.ID = pTarget.ID;
target.TARGET_DATE = pTarget.TARGET_DATE;
target.GEO_LEVEL1_CODE = pTarget.GEO_LEVEL1_CODE;
target.GEO_LEVEL2_CODE = pTarget.GEO_LEVEL2_CODE;
target.GEO_LEVEL3_CODE = pTarget.GEO_LEVEL3_CODE;
target.STORE_CODE = pTarget.STORE_CODE;
target.PROD_LEVEL1_CODE = pTarget.PROD_LEVEL1_CODE;
target.PROD_LEVEL2_CODE = pTarget.PROD_LEVEL2_CODE;
target.PROD_LEVEL3_CODE = pTarget.PROD_LEVEL3_CODE;
target.PROD_LEVEL4_CODE = pTarget.PROD_LEVEL4_CODE;
target.PROD_LEVEL5_CODE = pTarget.PROD_LEVEL5_CODE;
target.PRODUCT_CODE = pTarget.PRODUCT_CODE;
target.TARGET_SALE_QTY = pTarget.TARGET_SALE_QTY;
target.TARGET_SALE_VAL_AT_PRICE = pTarget.TARGET_SALE_VAL_AT_PRICE;
target.TARGET_SALE_VAL_AT_COST = pTarget.TARGET_SALE_VAL_AT_COST;
target.ARC_DATE = pTarget.ARC_DATE;
enBi.Entry(target).State = System.Data.Entity.EntityState.Modified;
enBi.SaveChanges();
dbCtxTran.Commit();
return(Request.CreateResponse(HttpStatusCode.OK, "Target successfully Updated."));
}
}
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
return(Request.CreateResponse(HttpStatusCode.OK, "Target successfully Updated."));
}
public TransListMulti getPlanByDate(int pg, int tk, string pSite, string pPlandate)
{
int skip = tk * (pg - 1); // skip the record
TransListMulti transList1 = new TransListMulti();
CultureInfo culture = new CultureInfo("en-US");
DateTime Plandate = Convert.ToDateTime(pPlandate, culture);
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var plan = (from s in enBi.STG_PLAN
where s.STORE_CODE == pSite && s.PLAN_DATE == Plandate
select new
{
s.ID,
s.PLAN_DATE,
s.GEO_LEVEL1_CODE,
s.GEO_LEVEL2_CODE,
s.GEO_LEVEL3_CODE,
s.STORE_CODE,
s.PROD_LEVEL1_CODE,
s.PROD_LEVEL2_CODE,
s.PROD_LEVEL3_CODE,
s.PROD_LEVEL4_CODE,
s.PROD_LEVEL5_CODE,
s.PRODUCT_CODE,
s.PLAN_VERSION,
s.PLAN_SALE_QTY,
s.PLAN_SALE_VAL_AT_PRICE,
s.PLAN_SALE_VAL_AT_COST,
s.PLAN_MARKDOWN_QTY,
s.PLAN_MARKDOWN_VAL,
s.PLAN_SHRINKAGE_VAL,
s.PLAN_PURCHASE_QTY,
s.PLAN_PURCHASE_VAL,
s.PLAN_INV_OPENING_QTY,
s.PLAN_OPENING_VAL_AT_COST,
s.PLAN_OPENING_VAL_AT_PRICE,
s.PLAN_OTH1,
s.PLAN_OTH2,
s.PLAN_OTH3,
s.PLAN_OTH4,
s.PLAN_OTH5,
s.ARC_DATE
})
.OrderBy(od => od.STORE_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList1.total_Count = enBi.STG_PLAN.AsNoTracking().Where(wr => wr.STORE_CODE == pSite && wr.PLAN_DATE == Plandate).Count();
transList1.data = plan;
return(transList1);
}
}
public HttpResponseMessage updateStockAge(dynamic sStockAge)
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
using (var dbCtxTran = enBi.Database.BeginTransaction())
{
//DateTime merchdate = sStoreMerch.merch_DATE;
int id = sStockAge.ID;
string bndDesc = sStockAge.PRICE_BAND_CODE;
string lvlCode = sStockAge.PROD_LEVEL1_CODE;
//string storecode = sStoreMerch.STORE_CODE;
STG_DIM_STOCKAGE_BAND stockage = enBi.STG_DIM_STOCKAGE_BAND.Find(id, bndDesc, lvlCode);
if (stockage != null)
{
stockage.ID = sStockAge.ID;
stockage.STOCKAGE_BAND_DESC = sStockAge.PRICE_BAND_DESC;
stockage.STKAGE_PROD_LEVEL1_CODE = sStockAge.PROD_LEVEL1_CODE;
stockage.STOCKAGE_BAND_LOWER = sStockAge.PRICE_BAND_LOWER;
stockage.STOCKAGE_BAND_UPPER = sStockAge.PRICE_BAND_UPPER;
stockage.STOCKAGE_BAND_SEQ = sStockAge.STOCKAGE_BAND_SEQ;
stockage.LATEST = sStockAge.LATEST;
stockage.ARC_DATE = sStockAge.ARC_DATE;
enBi.Entry(stockage).State = System.Data.Entity.EntityState.Modified;
enBi.SaveChanges();
dbCtxTran.Commit();
return(Request.CreateResponse(HttpStatusCode.OK, "Price Band successfully Updated."));
}
}
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
return(Request.CreateResponse(HttpStatusCode.OK, "Price Band successfully Updated."));
}
public TransList getSite(int pg, int tk, string fnd)
{
try
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities db = new PRINCE_STGEntities())
{
db.Configuration.LazyLoadingEnabled = false;
if (string.IsNullOrEmpty(fnd))
{
List <Site> site = db.Site.AsNoTracking()
.OrderBy(od => od.Description)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = db.Site.AsNoTracking().Count();
transList.data = site;
}
else
{
List <Site> site = db.Site.AsNoTracking()
.Where(wr => wr.Description.Contains(fnd) || wr.Status.Contains(fnd))
.OrderBy(od => od.Description)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = db.Site.AsNoTracking().Where(wr => wr.Description.Contains(fnd) || wr.Status.Contains(fnd)).Count();
transList.data = site;
}
}
return(transList);
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public TransList getPlan(int pg, int tk, string fnd)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var plan = (from s in enBi.STG_PLAN
select new {
s.ID
, s.PLAN_DATE
, s.GEO_LEVEL1_CODE
, s.GEO_LEVEL2_CODE
, s.GEO_LEVEL3_CODE
, s.STORE_CODE
, s.PROD_LEVEL1_CODE
, s.PROD_LEVEL2_CODE
, s.PROD_LEVEL3_CODE
, s.PROD_LEVEL4_CODE
, s.PROD_LEVEL5_CODE
, s.PRODUCT_CODE
, s.PLAN_VERSION
, s.PLAN_SALE_QTY
, s.PLAN_SALE_VAL_AT_PRICE
, s.PLAN_SALE_VAL_AT_COST
, s.PLAN_MARKDOWN_QTY
, s.PLAN_MARKDOWN_VAL
, s.PLAN_SHRINKAGE_VAL
, s.PLAN_PURCHASE_QTY
, s.PLAN_PURCHASE_VAL
, s.PLAN_INV_OPENING_QTY
, s.PLAN_OPENING_VAL_AT_COST
, s.PLAN_OPENING_VAL_AT_PRICE
, s.PLAN_OTH1
, s.PLAN_OTH2
, s.PLAN_OTH3
, s.PLAN_OTH4
, s.PLAN_OTH5
, s.ARC_DATE
})
.OrderBy(od => od.STORE_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_PLAN.AsNoTracking().Count();
transList.data = plan;
return(transList);
}
}
public HttpResponseMessage updateStore(StoreDc pStore)
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
using (var dbCtxTran = enBi.Database.BeginTransaction())
{
TEMP_STORE store = enBi.TEMP_STORE.Where(wr => wr.STORE_CODE == pStore.STORE_CODE).FirstOrDefault();
if (store != null)
{
store.STORE_DESCRIPTION = pStore.STORE_DESCRIPTION;
store.AREA_MANAGER = pStore.AREA_MANAGER;
store.AREA_MANAGER_DESC = pStore.AREA_MANAGER_DESC;
store.REGIONAL_MANAGER = pStore.REGIONAL_MANAGER;
store.REGIONAL_MANAGER_DESC = pStore.REGIONAL_MANAGER_DESC;
store.SOM = pStore.SOM;
store.SOM_DESC = pStore.SOM_DESC;
store.SENIOR_SOM = pStore.SENIOR_SOM;
store.SENIOR_SOM_DESC = pStore.SENIOR_SOM_DESC;
store.STORE_OPENING_HOUR = pStore.STORE_OPENING_HOUR;
store.STORE_CLOSING_HOUR = pStore.STORE_CLOSING_HOUR;
store.STORE_LATITUDE = pStore.STORE_LATITUDE;
store.STORE_LONGITUDE = pStore.STORE_LONGITUDE;
enBi.Entry(store).State = EntityState.Modified;
enBi.SaveChanges();
dbCtxTran.Commit();
}
}
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
public HttpResponseMessage saveUserMenu(List <TempUserMenu> pUser)
{
try
{
if (pUser.Count > 0)
{
int?userId = pUser[0].UserId;
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
List <UserMenu> listUserMenu = (from um in entPrince.UserMenu where um.UserId == userId select um).ToList();
foreach (UserMenu uMenu in listUserMenu)
{
foreach (TempUserMenu tUmenu in pUser)
{
if (uMenu.Code == tUmenu.Code)
{
uMenu.CanAdd = tUmenu.canAdd;
uMenu.CanEdit = tUmenu.canEdit;
uMenu.CanDelete = tUmenu.canDelete;
uMenu.CanView = tUmenu.canView;
break;
}
}
entPrince.Entry(uMenu).State = EntityState.Modified;
}
entPrince.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "User Access Successfully Updated!"));
}
}
return(Request.CreateResponse(HttpStatusCode.NotModified));
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public HttpResponseMessage updateUser(TempUser pUser)
{
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
User user = entPrince.User.Where(wr => wr.Code == pUser.Code).SingleOrDefault();
user.FirstName = pUser.FirstName;
user.LastName = pUser.LastName;
user.EmailAdd = pUser.EmailAdd;
user.Status = pUser.Status;
entPrince.Entry(user).State = EntityState.Modified;
entPrince.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "User successfully updated!"));
}
}
public HttpResponseMessage addPlan(dynamic pPlan)
{
try
{
using (PRINCE_STGEntities dbCtx = new PRINCE_STGEntities())
{
STG_PLAN plan = new STG_PLAN();
plan.PLAN_DATE = pPlan.PLAN_DATE;
plan.GEO_LEVEL1_CODE = "99999";
plan.GEO_LEVEL2_CODE = "99999";
plan.GEO_LEVEL3_CODE = "99999";
plan.STORE_CODE = pPlan.STORE_CODE;
plan.PROD_LEVEL1_CODE = pPlan.PROD_LEVEL1_CODE;
plan.PROD_LEVEL2_CODE = pPlan.PROD_LEVEL2_CODE;
plan.PROD_LEVEL3_CODE = pPlan.PROD_LEVEL3_CODE;
plan.PROD_LEVEL4_CODE = "99999";
plan.PROD_LEVEL5_CODE = "99999";
plan.PRODUCT_CODE = "99999";
plan.PLAN_VERSION = 99999;
plan.PLAN_SALE_QTY = 99999;
plan.PLAN_SALE_VAL_AT_PRICE = pPlan.PLAN_SALE_VAL_AT_PRICE;
plan.PLAN_SALE_VAL_AT_COST = 99999;
plan.PLAN_MARKDOWN_QTY = 0;
plan.PLAN_MARKDOWN_VAL = 0;
plan.PLAN_SHRINKAGE_VAL = 0;
plan.PLAN_PURCHASE_QTY = 0;
plan.PLAN_PURCHASE_VAL = 0;
plan.PLAN_INV_OPENING_QTY = 0;
plan.PLAN_OPENING_VAL_AT_COST = 0;
plan.PLAN_OPENING_VAL_AT_PRICE = 0;
plan.PLAN_OTH1 = 99999;
plan.PLAN_OTH2 = 99999;
plan.PLAN_OTH3 = 99999;
plan.PLAN_OTH4 = 99999;
plan.PLAN_OTH5 = 99999;
plan.ARC_DATE = DateTime.Now;
dbCtx.STG_PLAN.Add(plan);
dbCtx.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.Created, "Plan successfully created."));
}
}
catch (Exception ex)
{
Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
public HttpResponseMessage saveUser(TempUser pUser)
{
using (PRINCE_STGEntities entPrince = new PRINCE_STGEntities())
{
int lastIdNo = (from u in entPrince.User.OrderByDescending(p => p.Id) select u.Id).FirstOrDefault();
User user = new User();
user.Code = lastIdNo + 1;
user.FirstName = pUser.FirstName;
user.LastName = pUser.LastName;
user.EmailAdd = pUser.EmailAdd;
user.Status = pUser.Status;
user.Password = "******";
user.STORE_CODE = pUser.STORE_CODE;
entPrince.User.Add(user);
entPrince.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "User successfully save!"));
}
}
public TransList getStoreMerchByDate(int pg, int tk, string fnd, string pSite, string sStoreMerchdate)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
CultureInfo culture = new CultureInfo("en-US");
DateTime StoreMerchdate = Convert.ToDateTime(sStoreMerchdate, culture);
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var Storemerch = (from s in enBi.STG_STR_MERCH_DET
where s.STORE_CODE == pSite && s.STRMRGRP_DATE == StoreMerchdate
select new
{
s.ID,
s.STRMRGRP_DATE
, s.STORE_CODE
, s.PROD_LEVEL1_CODE
, s.PROD_LEVEL2_CODE
, s.PROD_LEVEL3_CODE
, s.PROD_LEVEL4_CODE
, s.SELLING_AREA
, s.TOTAL_AREA
, s.PERMENANT_FTE
, s.CONTRACT_FTE
, s.OTH_DET1
, s.OTH_DET2
, s.OTH_DET3
, s.OTH_DET4
, s.OTH_DET5
, s.ARC_DATE
})
.OrderBy(od => od.STORE_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_STR_MERCH_DET.AsNoTracking().Where(st => st.STRMRGRP_DATE == StoreMerchdate && st.STORE_CODE == pSite).Count();
transList.data = Storemerch;
return(transList);
}
}
public dynamic getAllMenu()
{
try
{
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
int?[] arMenu = (from a in enBi.UserMenu.AsNoTracking() where a.Status == "A" select a.MenuId).ToArray();
var menu = (from a in enBi.Menu.AsNoTracking()
where a.Status == "A" && arMenu.Contains(a.Id) && a.ParentId != 0
select new {
a.Code,
a.Name,
canAdd = false,
canEdit = false,
canDelete = false,
canView = false,
a.LevelNo,
a.SortNo
}
).OrderBy(c => c.LevelNo).ThenBy(d => d.SortNo).ToList();
return(menu);
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}
public TransList getTargetByDate(int pg, int tk, string fnd, string pSite, string sTargetdate)
{
int skip = tk * (pg - 1); // skip the record
TransList transList = new TransList();
CultureInfo culture = new CultureInfo("en-US");
DateTime Targetdate = Convert.ToDateTime(sTargetdate, culture);
using (PRINCE_STGEntities enBi = new PRINCE_STGEntities())
{
var Target = (from t in enBi.STG_TARGET
where t.STORE_CODE == pSite && t.TARGET_DATE == Targetdate
select new
{
t.ID,
t.TARGET_DATE,
t.GEO_LEVEL1_CODE,
t.GEO_LEVEL2_CODE,
t.GEO_LEVEL3_CODE,
t.STORE_CODE,
t.PROD_LEVEL1_CODE,
t.PROD_LEVEL2_CODE,
t.PROD_LEVEL3_CODE,
t.PROD_LEVEL4_CODE,
t.PROD_LEVEL5_CODE,
t.PRODUCT_CODE,
t.TARGET_SALE_QTY,
t.TARGET_SALE_VAL_AT_PRICE,
t.TARGET_SALE_VAL_AT_COST,
t.ARC_DATE
})
.OrderBy(od => od.STORE_CODE)
.Skip(skip)
.Take(tk)
.ToList();
transList.totalCount = enBi.STG_TARGET.AsNoTracking().Where(st => st.TARGET_DATE == Targetdate && st.STORE_CODE == pSite).Count();
transList.data = Target;
return(transList);
}
}
public HttpResponseMessage addSite(dynamic pSite)
{
try
{
using (PRINCE_STGEntities dbCtx = new PRINCE_STGEntities())
{
int tempSite = pSite.SiteId;
String tempSiteDesc = pSite.Description;
Site site = dbCtx.Site.AsNoTracking().Where(wr => wr.SiteId == tempSite).SingleOrDefault();
if (site != null)
{
return(Request.CreateResponse(HttpStatusCode.Conflict, "Site Id: " + tempSite.ToString() + " already exist."));
}
Site objSite = new Site();
objSite.SiteId = tempSite;
objSite.Description = tempSiteDesc.Trim();
objSite.Status = "A";
dbCtx.Site.Add(objSite);
dbCtx.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.Created, "Site " + objSite.Description.Trim() + " is successfully created."));
}
}
catch (DbEntityValidationException ex)
{
ExceptionEntity exDesc = new ExceptionEntity(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
catch (Exception ex)
{
ExceptionDescription exDesc = new ExceptionDescription(ex);
throw new ApiException()
{
HttpStatus = HttpStatusCode.BadRequest, ErrorCode = (int)HttpStatusCode.BadRequest, ErrorDescription = exDesc.GetDescException()
};
}
}