public string ClientMarketBase(int id)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(id) == 0)
{
return("");
}
DataTable clientMarketBase = new DataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("GetClientMarketBase", conn))
{
cmd.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int));
cmd.Parameters["@ClientId"].Value = id; //hardcoded for now
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(clientMarketBase);
}
}
}
var json = JsonConvert.SerializeObject(clientMarketBase, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(json);
}
public HttpResponseMessage deleteClientMarketDef(int ClientId, int MarketDefId)
{
var UserId = Convert.ToInt32(((ClaimsIdentity)User.Identity).Claims.FirstOrDefault(c => c.Type == "userid").Value.ToString());
try
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to delete this market"));
}
var entity = _db.MarketDefinitions.FirstOrDefault(m => m.Id == MarketDefId);
if (entity == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "This market is not available, Please try another."));
}
else
{
//to clean Market definitionbase map
SqlParameter paramMarketID = new SqlParameter("@MarketDefID", MarketDefId);
SqlParameter paramUserId = new SqlParameter("@userId", UserId);
// _db.Database.ExecuteSqlCommand("exec DeleteMarketDefinition @MarketDefID", paramMarketID);
_db.Database.ExecuteSqlCommand("exec DeleteMarketDefinition_WithAudit @MarketDefID,@userId", paramMarketID, paramUserId);
return(Request.CreateResponse(HttpStatusCode.OK, "Market has deleted successfully."));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex));
}
}
public HttpResponseMessage getClientMarketDef(int ClientId, int MarketDefId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit this market"));
}
var entity = _db.MarketDefinitions.FirstOrDefault(m => m.Id == MarketDefId && m.ClientId == ClientId);
if (entity == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "This market is not available, please try another."));
}
else
{
MarketDefinition marketDef = _db.Database.SqlQuery <MarketDefinition>("Select * from MarketDefinitions Where ID=" + MarketDefId).FirstOrDefault();
var marketDefBaseMaps = _db.MarketDefinitionBaseMaps.Where(MD => MD.MarketDefinitionId == MarketDefId).ToList();
//List<MarketDefinitionPack> marketDefinitionPacks = _db.Database.SqlQuery<MarketDefinitionPack>("Select * from MarketDefinitionPacks Where MarketDefinitionID="+ MarketDefId).ToList();
marketDef.MarketDefinitionBaseMaps = marketDefBaseMaps;
//marketDef.MarketDefinitionPacks = marketDefinitionPacks;
var json = JsonConvert.SerializeObject(marketDef, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(Request.CreateResponse(HttpStatusCode.OK, marketDef));
}
}
public HttpResponseMessage GetClient(int id)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(id) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view market definitions."));
}
var objMarketDefinition = _db.Database.SqlQuery <MarketDefinition>("Select MarketDefinitions.Id as Id, MarketDefinitions.DimensionId as DimensionId, MarketDefinitions.Name as Name, MarketDefinitions.Description as Description, MarketDefinitions.ClientId as ClientId, MarketDefinitions.GuiId as GuiId,LastSaved, LastModified, ModifiedBy from MarketDefinitions Where clientId=" + id + " order by name").ToList();
var objMarketDefinitionBaseMap = _db.Database.SqlQuery <MarketDefinitionBaseMap>("Select MarketDefinitionBaseMaps.Id as Id, MarketDefinitionBaseMaps.Name as Name, MarketDefinitionBaseMaps.DataRefreshType as DataRefreshType,(Select count(*) from AdditionalFilters Where MarketDefinitionBaseMapId=MarketDefinitionBaseMaps.Id) as MarketBaseId, MarketDefinitionId from MarketDefinitionBaseMaps Where marketDefinitionId in (Select MarketDefinitions.Id from MarketDefinitions Where clientId = " + id + ")").ToList();
if (objMarketDefinition.Count() > 0)
{
objMarketDefinition.ForEach(record =>
{
if (record.Id != null)
{
//for technical issue filter count value put in MarketDefinitionId
//var objMarketDefinitionBaseMap = _db.Database.SqlQuery<MarketDefinitionBaseMap>("Select MarketDefinitionBaseMaps.Id as Id, MarketDefinitionBaseMaps.Name as Name,MarketDefinitionBaseMaps.MarketBaseId as MarketBaseId, MarketDefinitionBaseMaps.DataRefreshType as DataRefreshType,(Select count(*) from AdditionalFilters Where MarketDefinitionBaseMapId=MarketDefinitionBaseMaps.Id) as MarketDefinitionId from MarketDefinitionBaseMaps Where marketDefinitionId=" + record.Id + "").ToList();
if (objMarketDefinitionBaseMap.Count() > 0)
{
record.MarketDefinitionBaseMaps = objMarketDefinitionBaseMap.Where(bm => bm.MarketDefinitionId == record.Id).ToList();
}
}
});
}
return(Request.CreateResponse(HttpStatusCode.OK, objMarketDefinition));
}
public HttpResponseMessage GetMarketBasePacks(int id, int clientId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit marketbase."));
}
using (var db = new EverestPortalContext())
{
var marketBase = db.MarketBases.Where(m => m.Id == id).ToList <MarketBase>();
if (marketBase == null || marketBase.Count() < 1)
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "This marketbase is not available, please try another."));
}
//to get dimension list
var dimensionList = _db.Database.SqlQuery <DimensionBaseMap>("dbo.GetDimensionBaseMaps @pMarketBaseId", new SqlParameter("pMarketBaseId", id)).ToList();
//to get PxR List
var pxrList = _db.Database.SqlQuery <PxR>("select Id, MktCode as MarketCode, MktName as MarketName,MarketBaseId as MarketbaseId from MIP.MKTPxRBaseMap Where MarketBaseId=" + id).ToList();
var marketbaseDetails = new { MarketBase = marketBase, Dimension = dimensionList, PxRList = pxrList };
var json = JsonConvert.SerializeObject(marketbaseDetails, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(Request.CreateResponse(HttpStatusCode.OK, json));
}
}
public HttpResponseMessage GetMarketBases(int ClientId, int MarketDefId, string Action)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit marketbases."));
}
DataTable clientMarketBase = new DataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("prGetMarketBase", conn))
{
cmd.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int)).Value = ClientId;
cmd.Parameters.Add(new SqlParameter("@MarketDefId", SqlDbType.Int)).Value = MarketDefId;
cmd.Parameters.Add(new SqlParameter("@Type", SqlDbType.NVarChar, 100)).Value = Action;
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(clientMarketBase);
}
}
}
/* var json = JsonConvert.SerializeObject(clientMarketBase, Formatting.Indented,
* new JsonSerializerSettings
* {
* ReferenceLoopHandling = ReferenceLoopHandling.Ignore
* });*/
return(Request.CreateResponse(HttpStatusCode.OK, clientMarketBase));
}
public async Task <HttpResponseMessage> DeleteMarketbase(int ClientId, int MarketbaseId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to edit/delete this marketbase"));
}
// place the request in queue
try
{
var result = _db.Database.SqlQuery <string>("exec PutInMarketBaseDeleteQueue " + MarketbaseId + "," + 1).FirstOrDefault();
var json = JsonConvert.SerializeObject(result, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(Request.CreateResponse(HttpStatusCode.OK, json));
}
catch (Exception ex)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to edit/delete this marketbase"));
}
}
public async Task <string> SaveMarketBase(int ClientId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return("");
}
HttpContent requestContent = Request.Content;
var jsonContent = requestContent.ReadAsStringAsync().Result;
Guid Id = Guid.NewGuid();
if (jsonContent.Trim() != string.Empty)
{
var marketBaseDetails = JsonConvert.DeserializeObject <MarketbaseDetails>(jsonContent);
List <DimensionBaseMap> Dimension = marketBaseDetails.Dimension; //to get dimension seperatly.
List <PxR> PxRCollection = marketBaseDetails.PxR; //to get dimension seperatly.
MarketBase marketBase = marketBaseDetails.MarketBase;
marketBase.GuiId = Id.ToString();
marketBase.LastSaved = DateTime.Now;
using (var db = new EverestPortalContext())
{
db.MarketBases.Add(marketBase);
db.SaveChanges();
//to update new marketbase Id
await SaveDimensionBaseMap(marketBase.Id, Dimension);
await SavePxRBaseMap(marketBase.Id, PxRCollection);
}
}
var objClient = _db.MarketBases.Where(u => u.GuiId == Id.ToString()).ToList <MarketBase>();
ClientMarketBases clientMarketBases = new ClientMarketBases();
clientMarketBases.ClientId = ClientId;
clientMarketBases.MarketBaseId = objClient.First().Id;
using (var db = new EverestPortalContext())
{
db.ClientMarketBases.Add(clientMarketBases);
db.SaveChanges();
}
//to create new version
SubmitMarketbaseDetailsInformation(clientMarketBases.MarketBaseId);
var json = JsonConvert.SerializeObject(objClient, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(json);
}
public HttpResponseMessage getMarketDefinitionPacks(int ClientId, int MarketDefId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit Market definition packs."));
}
//JsonConvert.SerializeObject(marketDefinitionPacks)
// List<MarketDefinitionPack> marketDefinitionPacks = _db.Database.SqlQuery<MarketDefinitionPack>("Select * from MarketDefinitionPacks Where MarketDefinitionID=" + MarketDefId).ToList();
List <Packs> marketDefinitionPacks = _db.Database.SqlQuery <Packs>("Select * from vwMarketDefinitionPacks Where MarketDefinitionID=" + MarketDefId).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, marketDefinitionPacks));
}
public HttpResponseMessage GetMarketDefByClient(int id)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(id) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view market definitions."));
}
var marketDef = (from mar in _db.MarketDefinitions
where mar.ClientId == id
select mar).ToList();
var marketDefHistory = (from sub in _db.MarketDefinitions_History
where sub.ClientId == id
group sub by sub.MarketDefId into tempGrp
let maxVersion = tempGrp.Max(x => x.Version)
select new
{
MarketDefId = tempGrp.Key,
LastSaved = tempGrp.FirstOrDefault(y => y.Version == maxVersion).LastSaved,
MaxVer = maxVersion
}).ToList();
MarketDefDTO objDTO;
List <MarketDefDTO> lstMarketDTO = new List <MarketDefDTO>();
foreach (MarketDefinition marDef in marketDef)
{
objDTO = new MarketDefDTO();
marDef.CopyProperties(objDTO);
objDTO.Id = marDef.Id;
var res = marketDefHistory.FirstOrDefault(i => i.MarketDefId == marDef.Id);
objDTO.Submitted = (res == null || res.LastSaved != marDef.LastSaved) ? "No" : "Yes";
lstMarketDTO.Add(objDTO);
}
List <MarketDefDTO> notSubmitted = lstMarketDTO.Where(t => t.Submitted == "No").ToList();
notSubmitted.Sort(CompareByName);
List <MarketDefDTO> submitted = lstMarketDTO.Where(t => t.Submitted == "Yes").ToList();
submitted.Sort(CompareByName);
List <MarketDefDTO> finalList = new List <MarketDefDTO>();
finalList.AddRange(notSubmitted); finalList.AddRange(submitted);
return(Request.CreateResponse(HttpStatusCode.OK, finalList));
}
public string ValidatedeleteMarket(int subscriptionId, int markettbaseid, int clientid)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientid) == 0)
{
return("");
}
string result = string.Empty;
List <string> LstDelName = new List <string>();
List <string> LstMktName = new List <string>();
if (subscriptionId > 0 && markettbaseid > 0)
{
//int subscriptionId = 0; int markettbaseid = 0;
//int.TryParse(subId, out subscriptionId);
//int.TryParse(mktbaseid, out markettbaseid);
var delMktList = dbContext.DeliveryMarkets.Where(p => p.deliverables.SubscriptionId == subscriptionId).ToList();
List <DeliveryMarket> lstDeleteDelMkt = new List <DeliveryMarket>();
//loop through delivery market
foreach (var objDel in delMktList)
{
var mktBaseidList = dbContext.MarketDefinitionBaseMaps.Where(x => x.MarketDefinitionId == objDel.MarketDefId).ToList();
foreach (var oMktBase in mktBaseidList)
{
if (oMktBase.MarketBaseId == markettbaseid)
{
string delName = getSubscriptionName(objDel.deliverables);
string mrkDefName = objDel.marketDefinition.Name;
LstDelName.Add(delName);
LstMktName.Add(mrkDefName);
}
}
}
// dbContext.DeliveryMarkets.RemoveRange(lstDeleteDelMkt);
}
string res1 = String.Join(",", LstDelName);
string res2 = String.Join(",", LstMktName);
if (res1 != "")
{
result = res1 + "|" + res2;
}
return(result);
}
public string GetEffectedMarketDefName(int MarketBaseId, int ClientId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return("");
}
DataTable marketDefName = new DataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("GetEffectedMarketDefName", conn))
{
cmd.Parameters.Add(new SqlParameter("@MarketBaseId", SqlDbType.Int));
cmd.Parameters["@MarketBaseId"].Value = MarketBaseId; //hardcoded for now
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(marketDefName);
}
}
}
string name = "";
for (var x = 0; x < marketDefName.Rows.Count; x++)
{
name = name + marketDefName.Rows[x]["NAME"] + ", ";
}
if (name.Length < 1)
{
return(name);
}
else
{
return(name.Substring(0, name.Length - 2));
}
}
public string GetListOfMarketForMarketbase(int subscriptionId, int marketbaseId, int clientId)
{
List <MarketDefinition> MarektDefList = new List <MarketDefinition>();
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientId) == 0)
{
return(JsonConvert.SerializeObject(MarektDefList, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
}));
}
string result = string.Empty;
List <string> LstDelName = new List <string>();
List <string> LstMktName = new List <string>();
if (subscriptionId > 0 && marketbaseId > 0)
{
/*var MBList = dbContext.MarketDefinitionBaseMaps.Where(p => p.MarketBaseId == marketbaseId);
*
* MarektDefList = dbContext.MarketDefinitions
* .Join(MBList, m => m.Id, mb => mb.MarketDefinitionId, (m, mb) => m)
* .OrderBy(m => m.Name)
* .ToList();*/
MarektDefList = (from M in dbContext.MarketDefinitions
where (from MB in dbContext.MarketDefinitionBaseMaps
where MB.MarketBaseId == marketbaseId
select MB.MarketDefinitionId).Contains(M.Id)
orderby M.Name
select M).ToList();
}
var json = JsonConvert.SerializeObject(MarektDefList, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(json);
}
public HttpResponseMessage addMarketBase([FromBody] JObject request)
{
if (request != null)
{
int clientID = 0;
if (request["clientid"] != null)
{
string clientid = Convert.ToString(request["clientid"]);
int.TryParse(clientid, out clientID);
}
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientID) == 0)
{
return(Request.CreateResponse(HttpStatusCode.NoContent));
}
string subId = request["subscriptionid"].ToString();
var MktId = request["mktbaseid"].ToList();
int subscriptionId = 0;
int.TryParse(subId, out subscriptionId);
for (int i = 0; i < MktId.Count; i++)
{
int mktBaseid = 0;
int.TryParse(MktId[i].ToString(), out mktBaseid);
int recCnt = dbContext.subscriptionMarket.Count(p => p.SubscriptionId == subscriptionId && p.MarketBaseId == mktBaseid);
if (recCnt <= 0)
{
dbContext.subscription.Where(x => x.SubscriptionId == subscriptionId).FirstOrDefault().LastModified = DateTime.Now;
dbContext.subscriptionMarket.Add(new SubscriptionMarket {
SubscriptionId = subscriptionId, MarketBaseId = mktBaseid
});
}
}
dbContext.SaveChanges();
}
return(Request.CreateResponse(HttpStatusCode.Created));
}
public string GetClientName(int ClientId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return("");
}
var objClient = _db.Clients
.Where(u => u.Id == ClientId)
.Select(u => u.Name);
var json = JsonConvert.SerializeObject(objClient, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(json);
}
public HttpResponseMessage updateSubscription([FromBody] JObject request)
{
if (request != null)
{
int clientID = 0;
if (request["clientid"] != null)
{
string clientid = Convert.ToString(request["clientid"]);
int.TryParse(clientid, out clientID);
}
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientID) == 0)
{
return(Request.CreateResponse(HttpStatusCode.NoContent));
}
string subId = request["subscriptionid"].ToString();
string fromDate = request["fromdate"].ToString();
string toDate = request["todate"].ToString();
int subscriptionId = 0;
DateTime fDate, tDate;
int.TryParse(subId, out subscriptionId);
DateTime.TryParse(fromDate, out fDate);
DateTime.TryParse(toDate, out tDate);
Subscription sObj = dbContext.subscription.Where(p => p.SubscriptionId == subscriptionId).SingleOrDefault();
if (sObj != null)
{
sObj.StartDate = fDate;
sObj.EndDate = tDate;
sObj.LastModified = DateTime.Now;
dbContext.SaveChanges();
}
}
return(Request.CreateResponse(HttpStatusCode.Created));
}
public async Task <HttpResponseMessage> EditMarketBase(int ClientId, int MarketBaseId)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(ClientId) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit this marketbase"));
}
HttpContent requestContent = Request.Content;
var jsonContent = requestContent.ReadAsStringAsync().Result;
// var marketBase = JsonConvert.DeserializeObject<MarketBase>(jsonContent);
var marketBaseDetails = JsonConvert.DeserializeObject <MarketbaseDetails>(jsonContent);
List <DimensionBaseMap> Dimension = marketBaseDetails.Dimension; //to get dimension seperatly.
List <PxR> PxRCollection = marketBaseDetails.PxR; //to get PxR seperatly.
MarketBase marketBase = marketBaseDetails.MarketBase;
var identity = (ClaimsIdentity)User.Identity;
int uid = Convert.ToInt32(identity.Claims.FirstOrDefault(c => c.Type == "userid").Value.ToString());
Guid Id = Guid.NewGuid();
if (jsonContent.Trim() != string.Empty)
{
MarketBase MBObj = null;
using (var db = new EverestPortalContext())
{
var oldBaseFilter = db.BaseFilters.Where(i => i.MarketBaseId == MarketBaseId).ToList();
if (oldBaseFilter != null)
{
db.BaseFilters.RemoveRange(oldBaseFilter);
db.SaveChanges();
}
MBObj = db.MarketBases.Where(i => i.Id == MarketBaseId).FirstOrDefault();
if (MBObj != null)
{
MBObj.BaseType = marketBase.BaseType;
MBObj.Description = marketBase.Description;
MBObj.DurationFrom = marketBase.DurationFrom;
MBObj.DurationTo = marketBase.DurationTo;
MBObj.Name = marketBase.Name;
MBObj.Suffix = marketBase.Suffix;
MBObj.LastSaved = DateTime.Now;
foreach (BaseFilter fil in marketBase.Filters)
{
BaseFilter obj = fil;
obj.MarketBaseId = MarketBaseId;
MBObj.Filters.Add(obj);
}
MBObj.LastModified = DateTime.Now;
MBObj.ModifiedBy = uid;
}
else
{
marketBase.GuiId = Id.ToString();
// marketBase.LastSaved = DateTime.Now;
marketBase.LastModified = DateTime.Now;
marketBase.ModifiedBy = uid;
db.MarketBases.Add(marketBase);
}
db.SaveChanges();
//to save dimension base map
await SaveDimensionBaseMap(MarketBaseId, Dimension);
await SavePxRBaseMap(MarketBaseId, PxRCollection);
}
}
var objClient = _db.MarketBases.Where(u => u.Id == MarketBaseId).ToList <MarketBase>().FirstOrDefault();
// place the request in queue
var result = _db.Database.SqlQuery <string>("exec PutInMarketBaseQueue " + MarketBaseId + "," + uid).FirstOrDefault();
var json = JsonConvert.SerializeObject(result, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(Request.CreateResponse(HttpStatusCode.OK, json));
}
public HttpResponseMessage GetClientSubscriptions(int clientid)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientid) == 0)
{
return(Request.CreateResponse(HttpStatusCode.Unauthorized, "You have no permission to view/edit subscription."));
}
//List<SubscriptionMarket> lstsubmkt = new List<SubscriptionMarket>();
//List<ClientSubscriptionDTO> sublist = new List<ClientSubscriptionDTO>();
//List<MarketBase> mktlist = new List<MarketBase>();
//var objSub = dbContext.subscription.Where(u => u.ClientId == clientid && u.EndDate.Year >= DateTime.Now.Year).ToList();
//var subscriptionIdList = (from o in objSub select o.SubscriptionId).ToList();
//var subscriptionMarket = (from subMkt in dbContext.subscriptionMarket
// where subscriptionIdList.Contains(subMkt.SubscriptionId)
// select subMkt).ToList();
//var subhistory = (from sub in dbContext.Subscription_History
// where sub.ClientId == clientid && sub.EndDate.Year >= DateTime.Now.Year
// group sub by sub.SubscriptionId into tempGrp
// let maxVersion = tempGrp.Max(x => x.Version)
// select new
// {
// SubscriptionId = tempGrp.Key,
// LastSaved = tempGrp.FirstOrDefault(y => y.Version == maxVersion).LastSaved,
// MaxVer = maxVersion
// }).ToList();
////var subHistory = dbContext.Database.SqlQuery<Subscription_History>("select MarketDefId,max(Version) as [Version],Name,Description,ClientId,GuiId,DimensionId,ModifiedDate,UserId,IsSentToTDW,TDWTransferDate,TDWUserId,LastSaved from MarketDefinitions_History where ClientId=" + clientid + " group by MarketDefId,Name");
//if (objSub != null && objSub.Count > 0)
//{
// foreach (var sub in objSub)
// {
// ClientSubscriptionDTO dto = new ClientSubscriptionDTO();
// Models.Subscription.SubscriptionDTO sDto = new Models.Subscription.SubscriptionDTO();
// dto.ClientId = sub.ClientId;
// //sub.client.MarketDefinitions = new List<MarketDefinition>();
// //dto.subscription = sub;
// sDto.SubscriptionId = sub.SubscriptionId;
// sDto.Name = sub.Name;
// sDto.ClientId = sub.ClientId;
// sDto.CountryId = sub.CountryId;
// sDto.ServiceId = sub.ServiceId;
// sDto.DataTypeId = sub.DataTypeId;
// sDto.SourceId = sub.SourceId;
// sDto.Country = sub.country.Name;
// sDto.Service = sub.service.Name;
// sDto.Data = sub.dataType.Name;
// sDto.Source = sub.source.Name;
// sDto.StartDate = sub.StartDate;
// sDto.EndDate = sub.EndDate;
// sDto.ServiceTerritoryId = sub.ServiceTerritoryId;
// sDto.Active = sub.Active;
// sDto.LastModified = sub.LastModified;
// sDto.ModifiedBy = sub.ModifiedBy;
// sDto.serviceTerritory = sub.serviceTerritory;
// var res = subhistory.FirstOrDefault(i => i.SubscriptionId == sub.SubscriptionId);
// sDto.Submitted = (res == null || res.LastSaved != sDto.LastModified) ? "No" : "Yes";
// dto.subscription = sDto;
// //var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList();
// var submket = subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId)
// .Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList();
// if (submket != null && submket.Count() > 0)
// {
// dto.MarketBase = submket;
// //dto.MarketBase = submket.Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList();
// }
// //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>();
// sublist.Add(dto);
// //ClientSubscriptionDTO dto = new ClientSubscriptionDTO();
// //dto.ClientId = sub.ClientId;
// //sub.client.MarketDefinitions = new List<MarketDefinition>();
// //dto.subscription = sub;
// //var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList();
// //if (submket != null && submket.Count() > 0)
// //{
// // dto.MarketBase = submket.Select(a => a.marketBase).ToList();
// //}
// //dto.subscription.client.MarketDefinitions = new List<MarketDefinition>();
// //sublist.Add(dto);
// }
//}
// New code
var clientSubscription = dbContext.subscription
.GroupJoin(dbContext.subscriptionMarket,
s => s.SubscriptionId,
m => m.SubscriptionId,
(s, m) => new { S = s, M = m })
.SelectMany(
m => m.M.DefaultIfEmpty()
, (x, y) => new { Subscription = x.S, SubscriptionMarket = y })
.GroupJoin(dbContext.Subscription_History,
s => s.Subscription.SubscriptionId,
h => h.SubscriptionId,
(s, h) => new { S = s, H = h })
.SelectMany(h => h.H.DefaultIfEmpty(),
(x, y) => new { Subscription = x.S.Subscription, SubscriptionMarket = x.S.SubscriptionMarket, SubscriptionHistory = y })
.Where(u => u.Subscription.ClientId == clientid && u.Subscription.EndDate.Year >= DateTime.Now.Year)
.Select(x => new
{
ClientId = x.Subscription.ClientId,
ClientName = x.Subscription.Name,
subscription = new Models.Subscription.SubscriptionDTO()
{
SubscriptionId = x.Subscription.SubscriptionId,
Name = x.Subscription.Name,
ClientId = x.Subscription.ClientId,
CountryId = x.Subscription.CountryId,
ServiceId = x.Subscription.ServiceId,
DataTypeId = x.Subscription.DataTypeId,
SourceId = x.Subscription.SourceId,
Country = x.Subscription.country.Name,
Service = x.Subscription.service.Name,
Data = x.Subscription.dataType.Name,
Source = x.Subscription.source.Name,
StartDate = x.Subscription.StartDate,
EndDate = x.Subscription.EndDate,
ServiceTerritoryId = x.Subscription.ServiceTerritoryId,
Active = x.Subscription.Active,
LastModified = x.Subscription.LastModified,
ModifiedBy = x.Subscription.ModifiedBy,
serviceTerritory = x.Subscription.serviceTerritory,
Submitted = null
},
SubscriptionMarket = x.SubscriptionMarket,
SubscriptionHistory = x.SubscriptionHistory
})
.GroupBy(x => new { x.ClientId, x.ClientName, x.subscription })
.ToList();
var sublist = clientSubscription.Select(x => new ClientSubscriptionDTO()
{
ClientId = x.Key.ClientId,
ClientName = x.Key.ClientName,
subscription = new Models.Subscription.SubscriptionDTO()
{
SubscriptionId = x.Key.subscription.SubscriptionId,
Name = x.Key.subscription.Name,
ClientId = x.Key.subscription.ClientId,
CountryId = x.Key.subscription.CountryId,
ServiceId = x.Key.subscription.ServiceId,
DataTypeId = x.Key.subscription.DataTypeId,
SourceId = x.Key.subscription.SourceId,
Country = x.Key.subscription.Country,
Service = x.Key.subscription.Service,
Data = x.Key.subscription.Data,
Source = x.Key.subscription.Source,
StartDate = x.Key.subscription.StartDate,
EndDate = x.Key.subscription.EndDate,
ServiceTerritoryId = x.Key.subscription.ServiceTerritoryId,
Active = x.Key.subscription.Active,
LastModified = x.Key.subscription.LastModified,
ModifiedBy = x.Key.subscription.ModifiedBy,
serviceTerritory = x.Key.subscription.serviceTerritory,
Submitted = x.Select(y => y.SubscriptionHistory).First() == null ? "No" : (x.Select(y => y.SubscriptionHistory).OrderBy(o => o.Version).LastOrDefault().LastSaved < x.Key.subscription.LastModified ? "No" : "Yes")
},
MarketBase = (x.Select(y => y.SubscriptionMarket).First() == null || x.Select(y => y.SubscriptionMarket.marketBase) == null) ? null : x.Select(y => GetMarketBase(y.SubscriptionMarket)).Distinct().ToList()
}).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, sublist));
}
public HttpResponseMessage DownloadSubscriptions(int clientid)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientid) == 0)
{
return(new HttpResponseMessage(HttpStatusCode.NotFound));
}
List <SubscriptionMarket> lstsubmkt = new List <SubscriptionMarket>();
List <ClientSubscriptionDTO> sublist = new List <ClientSubscriptionDTO>();
List <MarketBase> mktlist = new List <MarketBase>();
DataTable subdcriptionDT = new DataTable();
var objSub = dbContext.subscription.Where(u => u.ClientId == clientid && u.EndDate.Year >= DateTime.Now.Year).ToList();
if (objSub != null && objSub.Count > 0)
{
foreach (var sub in objSub)
{
ClientSubscriptionDTO dto = new ClientSubscriptionDTO();
Models.Subscription.SubscriptionDTO sDto = new Models.Subscription.SubscriptionDTO();
dto.ClientId = sub.ClientId;
//sub.client.MarketDefinitions = new List<MarketDefinition>();
//dto.subscription = sub;
sDto.SubscriptionId = sub.SubscriptionId;
sDto.Name = sub.Name;
sDto.ClientId = sub.ClientId;
var cList = dbContext.Clients.Where(z => z.Id == sub.ClientId).ToList().FirstOrDefault();
dto.ClientName = cList.Name;
//dto.ClientName =
sDto.CountryId = sub.CountryId;
sDto.ServiceId = sub.ServiceId;
sDto.DataTypeId = sub.DataTypeId;
sDto.SourceId = sub.SourceId;
sDto.Country = sub.country.Name;
sDto.Service = sub.service.Name;
sDto.Data = sub.dataType.Name;
sDto.Source = sub.source.Name;
sDto.StartDate = sub.StartDate;
sDto.EndDate = sub.EndDate;
sDto.ServiceTerritoryId = sub.ServiceTerritoryId;
sDto.Active = sub.Active;
sDto.LastModified = sub.LastModified;
sDto.ModifiedBy = sub.ModifiedBy;
sDto.serviceTerritory = sub.serviceTerritory;
dto.subscription = sDto;
var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList();
if (submket != null && submket.Count() > 0)
{
dto.MarketBase = submket.Select(a => a.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList();
}
//dto.subscription.client.MarketDefinitions = new List<MarketDefinition>();
sublist.Add(dto);
////subdcriptionDT = ListToDataTable(sublist);
//string xmlString = SerializeXml(sublist);
//DataSet ds = new DataSet("New_DataSet");
//using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(new StringReader(xmlString)))
//{
// ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
// ds.ReadXml(reader);
// subdcriptionDT = ds.Tables[0];
//}
subdcriptionDT = BuildsubscriptionDataTable(sublist);
//ClientSubscriptionDTO dto = new ClientSubscriptionDTO();
//dto.ClientId = sub.ClientId;
//sub.client.MarketDefinitions = new List<MarketDefinition>();
//dto.subscription = sub;
//var submket = dbContext.subscriptionMarket.Where(p => p.SubscriptionId == sub.SubscriptionId).ToList();
//if (submket != null && submket.Count() > 0)
//{
// dto.MarketBase = submket.Select(a => a.marketBase).ToList();
//}
//dto.subscription.client.MarketDefinitions = new List<MarketDefinition>();
//sublist.Add(dto);
}
}
//foreach (var xx in sublist)
//{
// xx.subscription.client.MarketDefinitions.RemoveRange(0, xx.subscription.client.MarketDefinitions.Count);
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
MemoryStream stream = new MemoryStream();
stream = GetExcelStream(subdcriptionDT);
// Reset Stream Position
stream.Position = 0;
result.Content = new StreamContent(stream);
// Generic Content Header
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
return(result);
}
public string GetSubscriptions(int clientid)
{
AuthController objAuth = new AuthController();
if (objAuth.CheckUserClients(clientid) == 0)
{
return("");
}
List <SubscriptionMarket> lstsubmkt = new List <SubscriptionMarket>();
lstsubmkt = dbContext.subscriptionMarket.Where(q => q.subscription.ClientId == clientid).ToList();
List <ClientSubscriptionDTO> sublist = new List <ClientSubscriptionDTO>();
List <MarketBase> mktlist = new List <MarketBase>();
int subid = 0;
int newSubid = 0;
foreach (var r in lstsubmkt)
{
ClientSubscriptionDTO dto;
Models.Subscription.SubscriptionDTO sDto;
List <MarketBase> lstMkt;
//MarketBase objMbase;
subid = r.SubscriptionId;
if (subid != newSubid)
{
dto = new ClientSubscriptionDTO();
sDto = new Models.Subscription.SubscriptionDTO();
dto.ClientId = r.subscription.ClientId;
// dto.subscription = r.subscription;
sDto.SubscriptionId = r.SubscriptionId;
sDto.Name = r.subscription.Name;
sDto.ClientId = r.subscription.ClientId;
sDto.CountryId = r.subscription.CountryId;
sDto.ServiceId = r.subscription.ServiceId;
sDto.DataTypeId = r.subscription.DataTypeId;
sDto.SourceId = r.subscription.SourceId;
sDto.Country = r.subscription.country.Name;
sDto.Service = r.subscription.service.Name;
sDto.Data = r.subscription.dataType.Name;
sDto.Source = r.subscription.source.Name;
sDto.StartDate = r.subscription.StartDate;
sDto.EndDate = r.subscription.EndDate;
sDto.ServiceTerritoryId = r.subscription.ServiceTerritoryId;
sDto.Active = r.subscription.Active;
sDto.LastModified = r.subscription.LastModified;
sDto.ModifiedBy = r.subscription.ModifiedBy;
sDto.serviceTerritory = r.subscription.serviceTerritory;
dto.subscription = sDto;
var listOfmktid = lstsubmkt.Where(r1 => r1.SubscriptionId == subid && r1.subscription.EndDate.Year >= DateTime.Now.Year).Select(t => t.MarketBaseId);
lstMkt = new List <MarketBase>();
lstMkt = lstsubmkt.Where(t2 => t2.SubscriptionId == subid).Select(t3 => t3.marketBase).OrderBy(p => p.Name).ThenBy(q => q.Suffix).ToList();
dto.MarketBase = lstMkt;
sublist.Add(dto);
//dto = new ClientSubscriptionDTO();
//dto.subscription = r.subscription;
//var listOfmktid = lstsubmkt.Where(r1 => r1.SubscriptionId == subid && r1.subscription.EndDate.Year >= DateTime.Now.Year).Select(t => t.MarketBaseId);
//lstMkt = new List<MarketBase>();
//lstMkt = lstsubmkt.Where(t2 => t2.SubscriptionId == subid).Select(t3 => t3.marketBase).ToList();
//dto.MarketBase = lstMkt;
//sublist.Add(dto);
}
newSubid = r.SubscriptionId;
}
var json = JsonConvert.SerializeObject(sublist, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(json);
}