public string GetMaxDate(Guid?Supplier_Id)
{
string maxdt = "";
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var result = (from dlr in context.DistributionLayerRefresh_Log
where dlr.Supplier_Id == Supplier_Id
select new DataContracts.DC_RefreshDistributionDataLog
{
Create_Date = dlr.Create_Date,
Status = dlr.Status
}).ToList();
if (result != null && result.Count > 0)
{
maxdt = result.Max(a => a.Create_Date).Value.ToString();
}
maxdt = maxdt == null ? "" : maxdt;
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while fetching date", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
return(maxdt);
}
public DC_Message SyncHotelRoomTypeMapping(Guid LogId, Guid Supplier_Id)
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var iScheduledCount = (from dlr in context.DistributionLayerRefresh_Log
where (dlr.Status == "Scheduled" || dlr.Status == "Running") && dlr.Element.ToUpper() == "ROOMTYPE" && dlr.Type.ToUpper() == "MAPPING"
select true).Count();
if (iScheduledCount > 0)
{
return(new DC_Message {
StatusMessage = "RoomType Mapping sync has already been scheduled.", StatusCode = ReadOnlyMessage.StatusCode.Information
});
}
}
using (DHSVCProxyAsync DHP = new DHSVCProxyAsync())
{
string strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_RoomTypeMapping"], LogId.ToString(), Supplier_Id.ToString());
DHP.GetAsync(ProxyFor.SqlToMongo, strURI);
}
return(new DC_Message {
StatusMessage = "RoomType Mapping sync has been scheduled successfully.", StatusCode = ReadOnlyMessage.StatusCode.Success
});
}
catch (Exception ex)
{
return(new DC_Message {
StatusMessage = ex.Message, StatusCode = ReadOnlyMessage.StatusCode.Failed
});
}
}
public List <DataContracts.STG.DC_Stg_Kafka> SelectKafkaInfo(Guid row_id)
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var result = (from a in context.Stg_Kafka
where a.Row_Id == row_id
select new DataContracts.STG.DC_Stg_Kafka
{
Status = a.Status,
Topic = a.Topic,
PayLoad = a.PayLoad,
Create_Date = a.Create_Date,
Create_User = a.Create_User,
Process_Date = a.Process_Date,
Process_User = a.Process_User
}).ToList();
return(result);
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while selecting Kafka details", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public bool CheckExistingSupplierSchedule(DataContracts.Schedulers.DC_Supplier_Schedule_RQ RQ)
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
//var search = from ac in context.Supplier_Schedule where ac.Supplier_ID equals RQ.sup
var search = (from ac in context.Supplier_Schedule
//join r in RQ on ac.Supplier_ID equals r.Suppllier_ID
where ac.Supplier_ID == RQ.Suppllier_ID && RQ.Entities.Contains(ac.Entity)
select ac).ToList();
if (search.Count > 0)
{
return(false);
}
return(true);
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while updating accomodation contacts", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
private DC_Message InsertDistributionLogNewEntryForMLDataAPI(DC_Distribution_MLDataRQ obj)
{
DC_Message msg = new DC_Message();
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
IncomingWebRequestContext woc = WebOperationContext.Current.IncomingRequest;
string CallingAgent = woc.Headers["CallingAgent"];
string CallingUser = woc.Headers["CallingUser"];
DataLayer.DistributionLayerRefresh_Log objNew = new DistributionLayerRefresh_Log();
objNew.Id = (obj.Logid ?? Guid.NewGuid());
objNew.Element = obj.Element.Trim();
objNew.Type = obj.Type.Trim();
objNew.Create_Date = DateTime.Now;
objNew.Create_User = CallingUser;
objNew.Status = obj.Status;
context.DistributionLayerRefresh_Log.Add(objNew);
context.SaveChanges();
msg.StatusCode = ReadOnlyMessage.StatusCode.Success;
msg.StatusMessage = "Sync has been scheduled successfully.";
}
}
catch (Exception)
{
throw;
}
return(msg);
}
//comment it
public List <DC_RefreshDistributionDataLog> GetRefreshStaticHotelLog()
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var UpdatedDate = (from dlr in context.DistributionLayerRefresh_Log
// join b in context.SupplierEntity on dlr.Status_Id equals b.Supplier_Id.ToString()
group dlr by new { dlr.Element, dlr.Type, dlr.Supplier_Id } into d
select new DC_RefreshDistributionDataLog
{
Create_User = string.Empty,
Element = d.Key.Element,
Status = string.Empty,
Type = d.Key.Type,
Create_Date = d.Max(t => t.Create_Date),
SupplierId = d.Key.Supplier_Id.ToString()
}).ToList();
return(UpdatedDate);
}
}
catch
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while fetching date", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public DC_Message SyncGeographyData(DataContracts.DC_MongoDbSyncRQ RQ)
{
DC_Message _msg = new DC_Message();
try
{
Guid LogId = new Guid();
IncomingWebRequestContext woc = WebOperationContext.Current.IncomingRequest;
string strURI = null;
using (ConsumerEntities context = new ConsumerEntities())
{
var iScheduledCount = context.DistributionLayerRefresh_Log.AsNoTracking()
.Where(w => (w.Status.ToUpper().Trim() == "RUNNING" || w.Status.ToUpper().Trim() == "SCHEDULED") && w.Element.ToUpper().Trim() == RQ.Element.ToUpper().Trim() && w.Type.ToUpper().Trim() == RQ.Type.ToUpper().Trim()).Count();
if (iScheduledCount > 0)
{
_msg.StatusMessage = RQ.Element + " " + RQ.Type + " sync has already been scheduled.";
_msg.StatusCode = ReadOnlyMessage.StatusCode.Information;
return(_msg);
}
else
{
LogId = Guid.NewGuid();
_msg = InsertDistributionLogNewEntry(LogId, RQ.Element, RQ.Type, "Scheduled", woc.Headers["CallingUser"]);
}
}
using (DHSVCProxyAsync DHP = new DHSVCProxyAsync())
{
string NewElement = RQ.Element.ToUpper().Trim();
string newType = RQ.Type.ToUpper().Trim();
if (NewElement == "HOTEL" && newType == "MAPPING")
{
strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_ProductMapping"], LogId.ToString(), (Guid.Empty).ToString());
}
else if (NewElement == "HOTEL" && newType == "MAPPINGLITE")
{
strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_ProductMappingLite"], LogId.ToString(), (Guid.Empty).ToString());
}
else if (NewElement == "ZONE" && newType == "MASTER")
{
strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_ZoneMaster"], LogId.ToString());
}
else if (NewElement == "ZONETYPE" && newType == "MASTER")
{
strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_ZoneTypeMaster"], LogId.ToString());
}
if (strURI != null)
{
DHP.GetAsync(ProxyFor.SqlToMongo, strURI);
}
}
return(_msg);
}
catch (Exception ex)
{
return(new DC_Message {
StatusMessage = ex.Message, StatusCode = ReadOnlyMessage.StatusCode.Failed
});
}
}
public IList <DataContracts.Schedulers.Supplier_Task_Logs> GetScheduleTaskLogList(string Task_Id)
{
try
{
if (Task_Id != string.Empty)
{
using (ConsumerEntities context = new ConsumerEntities())
{
Guid _newId = Guid.Parse(Task_Id);
var result = from a in context.Supplier_Scheduled_Task_Log
where a.Task_Id == _newId
select new Supplier_Task_Logs
{
Log_id = a.Log_Id,
Task_id = a.Task_Id,
StatusMessage = a.Status_Message,
LogType = a.Log_Type,
Remarks = a.Remarks,
CreateDate = a.Create_Date
};
return(result.ToList());
}
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw new System.ServiceModel.FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while fetching Supplier Scheduled task", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public DC_Message SyncSupplierStaticHotel(Guid log_id, Guid supplier_Id, string CreatedBy)
{
try
{
Guid LogId;
using (ConsumerEntities context = new ConsumerEntities())
{
var iScheduledCount = (from dlr in context.DistributionLayerRefresh_Log
where (dlr.Status == "Scheduled" || dlr.Status == "Running") && dlr.Element == "Hotels" && dlr.Type == "Static"
select true).Count();
if (iScheduledCount > 0)
{
return(new DC_Message {
StatusMessage = "Supplier Static Hotel sync has already been scheduled.", StatusCode = ReadOnlyMessage.StatusCode.Information
});
}
else
{
LogId = Guid.NewGuid();
DataLayer.DistributionLayerRefresh_Log objNew = new DistributionLayerRefresh_Log();
objNew.Id = LogId;
objNew.Element = "Hotels";
objNew.Type = "Static";
objNew.Create_Date = DateTime.Now;
objNew.Create_User = CreatedBy;// System.Web.HttpContext.Current.User.Identity.Name;
objNew.Status = "Scheduled";
objNew.Supplier_Id = supplier_Id;
objNew.Edit_Date = DateTime.Now;
objNew.Edit_User = CreatedBy;
context.DistributionLayerRefresh_Log.Add(objNew);
context.SaveChanges();
}
if (log_id == Guid.Empty)
{
using (DHSVCProxyAsync DHP = new DHSVCProxyAsync())
{
string strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_SupplierStaticHotels"], LogId.ToString(), supplier_Id.ToString());
DHP.GetAsync(ProxyFor.SqlToMongo, strURI);
}
}
else
{
//Code goes here for Indert Update or Delete of a specific country
}
return(new DC_Message {
StatusMessage = "Supplier Static Hotel sync has been scheduled successfully.", StatusCode = ReadOnlyMessage.StatusCode.Success
});
}
}
catch (Exception ex)
{
return(new DC_Message {
StatusMessage = ex.Message, StatusCode = ReadOnlyMessage.StatusCode.Failed
});
}
}
public List <DataContracts.STG.DC_Stg_Kafka> GetPollData()
{
try
{
List <DataContracts.STG.DC_Stg_Kafka> _lstresult = new List <DataContracts.STG.DC_Stg_Kafka>();
using (ConsumerEntities context = new ConsumerEntities())
{
_lstresult = (from a in context.Stg_Kafka
where a.Status != "Read"
select new DataContracts.STG.DC_Stg_Kafka
{
Row_Id = a.Row_Id,
Topic = a.Topic,
PayLoad = a.PayLoad
}).ToList();
return(_lstresult);
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while selecting Kafka details", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public DC_Message Update_Scheduled_Logs(DC_SchedulerServicesLogs param)
{
DataContracts.DC_Message _msg = new DataContracts.DC_Message();
bool IsExist = false;
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
//To check dupicate state
DC_SchedulerServicesLogs RQ = new DC_SchedulerServicesLogs();
RQ.Log_Id = param.Log_Id;
RQ.Operation = "LOGID";
var result = Get_Scheduled_Logs(RQ);
if (result != null)
{
if (result.Count > 0)
{
var search = context.Supplier_Scheduled_Task_Log.Find(param.Log_Id);
if (search != null)
{
search.Status_Message = param.Status_Message;
search.Log_Type = param.Log_Type;
search.Remarks = param.Remarks;
search.Edit_User = param.Create_User;
search.Edit_Date = DateTime.Now;
context.SaveChanges();
}
_msg.StatusCode = DataContracts.ReadOnlyMessage.StatusCode.Success;
_msg.StatusMessage = param.Task_Id + DataContracts.ReadOnlyMessage.strUpdatedSuccessfully;
return(_msg);
}
else
{
_msg.StatusCode = DataContracts.ReadOnlyMessage.StatusCode.Warning;
_msg.StatusMessage = param.Task_Id + DataContracts.ReadOnlyMessage.strnotExist;
return(_msg);
}
}
else
{
_msg.StatusCode = DataContracts.ReadOnlyMessage.StatusCode.Warning;
_msg.StatusMessage = param.Task_Id + DataContracts.ReadOnlyMessage.strnotExist;
return(_msg);
}
}
}
catch
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while updating Supplier Scheduled Logs.", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public List <DC_LoggerData> getLoggerTasks()
{
List <DC_UnprocessedData> lstUnprocessedData = new List <DC_UnprocessedData>();
try
{
StringBuilder sb = new StringBuilder();
sb.Append(" ; with CTE AS(");
sb.Append(" select ROW_NUMBER() over(partition by SupplierScheduleID order by tsk.Create_date Desc) RowNum, SupplierScheduleID, tsk.Status,");
sb.Append(" case when tsk.Status = 'Pending' then tsk.Schedule_Datetime else Getdate() end ScheduleDate, sch.CronExpression, Api_Call_Log_Id As Api_Call_Log_Id,");
sb.Append(" isnull(sch.ISXMLSupplier, 0) ISXMLSupplier, sch.Supplier_ID, spr.Name, sch.Entity from Supplier_Schedule sch with(nolock)");
sb.Append(" inner join Supplier spr with(nolock) on sch.Supplier_ID = spr.Supplier_Id");
sb.Append(" left join Supplier_Scheduled_Task tsk with(nolock) on sch.SupplierScheduleID = tsk.Schedule_Id");
sb.Append(" where spr.StatusCode = 'ACTIVE' and sch.Entity is not null and len(CronExpression) > 0");
sb.Append(" ) , ");
sb.Append(" CTE1 AS(");
sb.Append(" select mav.AttributeValue, sa.API_Path, ss.SupplierScheduleID, sa.Supplier_APILocation_Id from Supplier_Schedule ss with(nolock)");
sb.Append(" inner join m_masterattributevalue mav with(nolock) on LOWER(LTRIM(RTRIM(mav.AttributeValue))) = LOWER(LTRIM(RTRIM(ss.Entity)))");
sb.Append(" inner join m_masterattribute ma with(nolock) on ma.MasterAttribute_Id = mav.MasterAttribute_Id");
sb.Append(" left join Supplier_APILocation sa with(nolock) on sa.Supplier_Id = ss.Supplier_ID and sa.Entity_Id = mav.MasterAttributeValue_Id");
sb.Append(" where ma.Name = 'MappingEntity' and ma.MasterFor = 'MappingFileConfig'");
sb.Append(" ) ,");
sb.Append(" CTE2 AS(");
sb.Append(" select* from (select tsk.Status TaskStatus, replace(t.Status,'FAILED','ERROR') PentahoStatus, tsk.Schedule_Id, SupplierApiCallLog_Id, ");
sb.Append(" SupplierApiLocation_Id, PentahoCall_Id from(select ROW_NUMBER() over(partition by sa.Supplier_APILocation_Id order by api.Create_Date desc) rowNum,");
sb.Append(" mav.AttributeValue, sa.API_Path,ss.SupplierScheduleID,api.Status,api.SupplierApiCallLog_Id,api.SupplierApiLocation_Id,api.PentahoCall_Id");
sb.Append(" from Supplier_Schedule ss with(nolock)");
sb.Append(" inner join m_masterattributevalue mav with(nolock) on LOWER(LTRIM(RTRIM(mav.AttributeValue))) = LOWER(LTRIM(RTRIM(ss.Entity)))");
sb.Append(" inner join m_masterattribute ma with(nolock) on ma.MasterAttribute_Id = mav.MasterAttribute_Id");
sb.Append(" left join Supplier_APILocation sa with(nolock) on sa.Supplier_Id = ss.Supplier_ID and sa.Entity_Id = mav.MasterAttributeValue_Id");
sb.Append(" inner join Supplier_ApiCallLog api with(nolock) on sa.Supplier_APILocation_Id = api.SupplierApiLocation_Id");
sb.Append(" where ma.Name = 'MappingEntity' and ma.MasterFor = 'MappingFileConfig' ) t");
sb.Append(" left join Supplier_Scheduled_Task tsk with(nolock) on t.SupplierScheduleID = tsk.Schedule_Id where t.rowNum = 1 AND tsk.Status = 'Running'");
sb.Append(" ) t )");
sb.Append(" select Supplier_ID, Name, Entity, CTE.SupplierScheduleID, ScheduleDate, CronExpression,Convert(varchar(max), CTE1.API_Path) API_Path, ISXMLSupplier, Status ,");
sb.Append(" CTE2.PentahoStatus, CTE2.SupplierApiCallLog_Id api_Call_Log_Id, CTE2.SupplierApiLocation_Id Supplier_APILocation_Id, CTE2.PentahoCall_Id PentahoCall_Id from CTE");
sb.Append(" LEFT JOIN CTE1 On CTE.SupplierScheduleID = CTE1.SupplierScheduleID");
sb.Append(" left join CTE2 on cte.SupplierScheduleID = CTE2.Schedule_Id");
sb.Append(" where RowNum = 1 And ISXMLSupplier = 1 AND Convert(Date, ScheduleDate) <= Convert(Date, GETDATE())");
using (ConsumerEntities context = new ConsumerEntities())
{
context.Database.CommandTimeout = 0;
lstUnprocessedData = context.Database.SqlQuery <DC_UnprocessedData>(sb.ToString()).ToList();
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus
{
ErrorMessage = "Error while fetching Logger Tasks",
ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
return(new List <DC_LoggerData>());
}
public DC_Message InsertKafkaInfo(DataContracts.STG.DC_Stg_Kafka KafkaInfo)
{
DC_Message _msg = new DC_Message();
using (ConsumerEntities context = new ConsumerEntities())
{
try
{
if (KafkaInfo != null)
{
Stg_Kafka sk = new Stg_Kafka()
{
Row_Id = KafkaInfo.Row_Id,
Status = KafkaInfo.Status,
Topic = KafkaInfo.Topic,
PayLoad = KafkaInfo.PayLoad,
Error = KafkaInfo.Error,
Key = KafkaInfo.Key,
Offset = KafkaInfo.Offset,
Partion = KafkaInfo.Partion,
//TimeStamp = KafkaInfo.TimeStamp.get,
TopicPartion = KafkaInfo.TopicPartion,
TopicPartionOffset = KafkaInfo.TopicPartionOffset,
Create_User = KafkaInfo.Create_User,
Create_Date = KafkaInfo.Create_Date,
Process_User = KafkaInfo.Process_User,
Process_Date = KafkaInfo.Process_Date,
};
context.Stg_Kafka.Add(sk);
if (context.SaveChanges() == 1)
{
_msg.StatusMessage = ReadOnlyMessage.strAddedSuccessfully;
_msg.StatusCode = ReadOnlyMessage.StatusCode.Success;
}
else
{
_msg.StatusMessage = ReadOnlyMessage.strFailed;
_msg.StatusCode = ReadOnlyMessage.StatusCode.Failed;
}
}
else
{
_msg.StatusMessage = ReadOnlyMessage.strFailed;
_msg.StatusCode = ReadOnlyMessage.StatusCode.Failed;
}
return(_msg);
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while inserting Kafka details", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
}
public List <DataContracts.Masters.DC_Supplier_ApiLocation> Pentaho_SupplierApiLocation_Get(Guid SupplierId, Guid EntityId)
{
try
{
if (SupplierId == Guid.Empty || EntityId == Guid.Empty)
{
return(new List <DataContracts.Masters.DC_Supplier_ApiLocation>());
}
else
{
using (ConsumerEntities context = new ConsumerEntities())
{
var search = (from supApi in context.Supplier_APILocation
select supApi).AsQueryable();
search = from sup in search
where sup.Supplier_Id == SupplierId
select sup;
search = from sup in search
where sup.Entity_Id == EntityId
select sup;
var result = from a in search
join mav in context.m_masterattributevalue on a.Entity_Id equals mav.MasterAttributeValue_Id
join sup in context.Supplier on a.Supplier_Id equals sup.Supplier_Id
select new DataContracts.Masters.DC_Supplier_ApiLocation
{
Supplier_Id = a.Supplier_Id ?? Guid.Empty,
ApiEndPoint = a.API_Path,
ApiLocation_Id = a.Supplier_APILocation_Id,
Create_Date = a.CREATE_DATE,
Create_User = a.CREATE_USER,
Edit_Date = a.EDIT_DATE,
Edit_User = a.EDIT_USER,
Entity = mav.AttributeValue,
Entity_Id = a.Entity_Id,
IsActive = false,
Status = a.STATUS,
Supplier_Name = sup.Name
};
return(result.ToList());
}
}
}
catch (Exception e)
{
return(new List <DataContracts.Masters.DC_Supplier_ApiLocation>());
}
}
public DataContracts.DC_Message UpdateTaskLog(DataContracts.Schedulers.DC_SupplierScheduledTaskRQ obj)
{
try
{
DC_Message _msg = new DC_Message();
using (ConsumerEntities context = new ConsumerEntities())
{
if (obj.TaskId != Guid.Empty)
{
//var result = (from a in context.Supplier_Scheduled_Task_Log
// join b in context.Supplier_Scheduled_Task on a.Task_Id equals b.Task_Id
// where a.Log_Id == obj.LogId
// select b).FirstOrDefault();
var result = (from a in context.Supplier_Scheduled_Task
where a.Task_Id == obj.TaskId
select a).FirstOrDefault();
if (result != null)
{
var unprocessedtask = from a in context.SupplierImportFileDetails
where a.Supplier_Id == obj.Supplier_Id && a.Entity == obj.Entity && a.STATUS != "PROCESSED" && result.Schedule_Datetime < a.CREATE_DATE
select a;
if (unprocessedtask.Count() == 0)
{
result.Edit_Date = obj.Edit_Date;
result.Edit_User = obj.Edit_User;
result.Status = "Completed";
if (context.SaveChanges() == 1)
{
_msg.StatusMessage = ReadOnlyMessage.strUpdatedSuccessfully;
_msg.StatusCode = ReadOnlyMessage.StatusCode.Success;
}
else
{
_msg.StatusMessage = ReadOnlyMessage.strFailed;
_msg.StatusCode = ReadOnlyMessage.StatusCode.Failed;
}
}
}
}
}
return(_msg);
}
catch (Exception ex)
{
throw new FaultException <DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while saving Supplier Schedule", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public List <string> Pentaho_SupplierApiCall_Status()
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var result = (from log in context.Supplier_ApiCallLog select log.Status).Distinct().ToList();
return(result);
}
}
catch (Exception e)
{
return(new List <string>());
}
}
public DC_GetUsersToNotify getUsersToNotify(DC_GetUsersToNotify rqTask_Id)
{
DC_GetUsersToNotify RunningData = new DC_GetUsersToNotify();
try
{
StringBuilder sb = new StringBuilder();
sb.Append(" DECLARE @TaskID uniqueIdentifier = '" + rqTask_Id.Task_ID.ToString() + "';");
sb.Append(" IF EXISTS(select top 1 1 from AspNetUsers aspnt ");
sb.Append(" Inner join AspNetUserRoles roles on aspnt.Id = roles.UserId");
sb.Append(" inner join Supplier_Schedule ss on roles.RoleId = ss.User_Role_Id");
sb.Append(" inner Join Supplier_Scheduled_Task tsk on ss.SupplierScheduleID = tsk.Schedule_Id where tsk.Task_Id = @TaskID");
sb.Append(" and aspnt.IsActive = 1 AND aspnt.UserName like '%@%.com')");
sb.Append(" begin");
sb.Append(" select 1 UsersFound,STUFF((select ';' + CONVERT(varchar(50), LTRIM(RTRIM(aspnt.UserName))) from AspNetUsers aspnt");
sb.Append(" Inner join AspNetUserRoles roles on aspnt.Id = roles.UserId");
sb.Append(" inner join Supplier_Schedule ss on roles.RoleId = ss.User_Role_Id");
sb.Append(" inner Join Supplier_Scheduled_Task tsk on ss.SupplierScheduleID = tsk.Schedule_Id where tsk.Task_Id = @TaskID");
sb.Append(" and aspnt.IsActive = 1 AND aspnt.UserName like '%@%.com' group by aspnt.UserName for xml path('') ), 1, 1, '') as EmailAddress");
sb.Append(" end");
sb.Append(" else");
sb.Append(" begin");
sb.Append(" select 0 UsersFound,STUFF((select ';' + CONVERT(varchar(50), LTRIM(RTRIM(aspnt.UserName))) from AspNetUsers aspnt");
sb.Append(" Inner join AspNetUserRoles roles on aspnt.Id = roles.UserId");
sb.Append(" inner join Supplier_Schedule ss on roles.RoleId = ss.User_Role_Id");
sb.Append(" inner Join Supplier_Scheduled_Task tsk on ss.SupplierScheduleID = tsk.Schedule_Id where tsk.Task_Id = @TaskID");
sb.Append(" and aspnt.IsActive = 1 AND aspnt.UserName like '%@%.com' group by aspnt.UserName for xml path('') ), 1, 1, '') as EmailAddress");
sb.Append(" end");
using (ConsumerEntities context = new ConsumerEntities())
{
context.Database.CommandTimeout = 0;
RunningData = context.Database.SqlQuery <DC_GetUsersToNotify>(sb.ToString()).ToList().FirstOrDefault();
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus
{
ErrorMessage = "Error while fetching Email Addess.",
ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
return(RunningData);
}
public DC_Message SyncAccommodationMaster(Guid LogId, Guid Accommodation_Id, string CreatedBy)
{
if ((Accommodation_Id == Guid.Empty && LogId == Guid.Empty) || (Accommodation_Id != Guid.Empty && LogId != Guid.Empty))
{
return(new DC_Message {
StatusMessage = "Invalid Request.", StatusCode = ReadOnlyMessage.StatusCode.Warning
});;
}
try
{
if (Accommodation_Id == Guid.Empty && LogId != Guid.Empty)
{
using (ConsumerEntities context = new ConsumerEntities())
{
var iScheduledCount = (from dlr in context.DistributionLayerRefresh_Log
where (dlr.Status == "Scheduled" || dlr.Status == "Running") && dlr.Element.ToUpper() == "ACCOMMODATION" && dlr.Type.ToUpper() == "MASTER"
select true).Count();
if (iScheduledCount > 0)
{
return(new DC_Message {
StatusMessage = "Accommodation master sync has already been scheduled.", StatusCode = ReadOnlyMessage.StatusCode.Information
});
}
}
}
using (DHSVCProxyAsync DHP = new DHSVCProxyAsync())
{
string strURI = string.Format(System.Configuration.ConfigurationManager.AppSettings["Load_AccommodationMaster"], LogId.ToString(), Accommodation_Id.ToString());
DHP.GetAsync(ProxyFor.SqlToMongo, strURI);
}
return(new DC_Message {
StatusMessage = "Accommodation master sync has been scheduled successfully.", StatusCode = ReadOnlyMessage.StatusCode.Success
});
}
catch (Exception ex)
{
return(new DC_Message {
StatusMessage = ex.Message, StatusCode = ReadOnlyMessage.StatusCode.Failed
});
}
}
public List <DC_SupplierEntity> LoadSupplierActivityStatusData()
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
context.Database.CommandTimeout = 0;
context.Configuration.AutoDetectChangesEnabled = false;
using (var transaction = context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
var distribution = context.DistributionLayerRefresh_Log.AsNoTracking().Where(x => x.Element == "Activities" && x.Type == "Mapping").GroupBy(x => x.Supplier_Id).Select(g => g.OrderByDescending(x => x.Create_Date).FirstOrDefault()).ToList();
var supplier = (from sup in context.Supplier
join supcat in context.Supplier_ProductCategory on sup.Supplier_Id equals supcat.Supplier_Id
where supcat.ProductCategory.ToLower() == "activities"
select sup).ToList();
var SupplierData = (from s in supplier
join b in distribution on s.Supplier_Id equals b.Supplier_Id into c
from subset in c.DefaultIfEmpty()
where s.StatusCode == "ACTIVE"
orderby s.Name ascending
select new DC_SupplierEntity
{
Supplier_Id = s.Supplier_Id,
Supplier_Name = s.Name,
Element = string.Empty,
Type = string.Empty,
Status = (subset == null) ? string.Empty : subset.Status ?? string.Empty,
LastUpdated = subset == null ? string.Empty : subset.Create_Date.ToString(),
TotalCount = subset == null ? 0 : subset.TotalCount,
MongoPushCount = subset == null ? 0 : subset.MongoPushCount,
}).Distinct().ToList();
return(SupplierData);
}
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while fetching date", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
/// <summary>
/// Below Function will return the Tasks that will be scheduled for today to Execute.
/// </summary>
/// <returns> List of UnprocessedData</returns>
public List <DC_UnprocessedExecuterData> getExecutableTasks()
{
List <DC_UnprocessedExecuterData> lstUnprocessedData = new List <DC_UnprocessedExecuterData>();
try
{
StringBuilder sb = new StringBuilder();
sb.Append(" with CTE AS( ");
sb.Append(" select ROW_NUMBER() over(partition by SupplierScheduleID order by tsk.Create_date Desc) RowNum, SupplierScheduleID, tsk.Status, tsk.Schedule_Datetime ScheduleDate, tsk.Task_Id, ");
sb.Append(" sch.CronExpression, Api_Call_Log_Id As Api_Call_Log_Id, isnull(sch.ISXMLSupplier, 1) ISXMLSupplier from Supplier_Schedule sch with(nolock) ");
sb.Append(" inner join Supplier spr with(nolock) on sch.Supplier_ID = spr.Supplier_Id ");
sb.Append(" left join Supplier_Scheduled_Task tsk with(nolock) on sch.SupplierScheduleID = tsk.Schedule_Id ");
sb.Append(" where spr.StatusCode = 'ACTIVE' and sch.Entity is not null and len(CronExpression) > 0) , ");
sb.Append(" CTE1 AS( ");
sb.Append(" select mav.AttributeValue, sa.API_Path, ss.SupplierScheduleID,sa.Supplier_APILocation_Id from Supplier_Schedule ss with(nolock) ");
sb.Append(" inner join m_masterattributevalue mav with(nolock) on LOWER(LTRIM(RTRIM(mav.AttributeValue))) = LOWER(LTRIM(RTRIM(ss.Entity))) ");
sb.Append(" inner join m_masterattribute ma with(nolock) on ma.MasterAttribute_Id = mav.MasterAttribute_Id ");
sb.Append(" left join Supplier_APILocation sa with(nolock) on sa.Supplier_Id = ss.Supplier_ID and sa.Entity_Id = mav.MasterAttributeValue_Id ");
sb.Append(" where ma.Name = 'MappingEntity' and ma.MasterFor = 'MappingFileConfig' ");
sb.Append(" ) ");
sb.Append(" select CTE.SupplierScheduleID, ScheduleDate, CTE1.API_Path Api_Call_Log_Id, Status,Task_Id,Supplier_APILocation_Id from CTE LEFT JOIN CTE1 On CTE.SupplierScheduleID = CTE1.SupplierScheduleID ");
sb.Append(" where RowNum = 1 And status = 'Pending' And ISXMLSupplier = 1 And Convert(Date, ScheduleDate) <= Convert(Date, GETDATE()) order by ScheduleDate; ");
using (ConsumerEntities context = new ConsumerEntities())
{
context.Database.CommandTimeout = 0;
lstUnprocessedData = context.Database.SqlQuery <DC_UnprocessedExecuterData>(sb.ToString()).ToList();
//return lstUnprocessedData;
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus
{
ErrorMessage = "Error while fetching Scheduled Tasks",
ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
return(lstUnprocessedData);
}
public DC_Message Pentaho_SupplierApiCall_Remove(Guid PentahoCall_Id, string CalledBy)
{
try
{
DC_Message dc = new DataContracts.DC_Message();
if (PentahoCall_Id != Guid.Empty)
{
string endpointurl = "removeTrans?id=" + PentahoCall_Id.ToString();
object result;
DHSVCProxy.GetData(ProxyFor.Pentaho, endpointurl, typeof(void), out result);
using (ConsumerEntities context = new ConsumerEntities())
{
List <Supplier_ApiCallLog> results = (from p in context.Supplier_ApiCallLog
where p.PentahoCall_Id == PentahoCall_Id
select p).ToList();
foreach (Supplier_ApiCallLog p in results)
{
p.Status = "REMOVED";
p.Edit_User = CalledBy;
p.Edit_Date = DateTime.Now;
p.Message = "Transformation Queue has been removed from Server.";
}
context.SaveChanges();
}
dc.StatusMessage = "The transformation was removed successfully.";
dc.StatusCode = ReadOnlyMessage.StatusCode.Success;
}
else
{
dc.StatusMessage = "Invalid Transformation Call Id !";
dc.StatusCode = ReadOnlyMessage.StatusCode.Danger;
}
return(dc);
}
catch (Exception e)
{
return(new DC_Message {
StatusCode = ReadOnlyMessage.StatusCode.Danger, StatusMessage = e.Message
});
}
}
public IList <DataContracts.Schedulers.DC_Supplier_Schedule_RS> GetScheduleBySupplier(DataContracts.Schedulers.DC_Supplier_Schedule_RQ RQ)
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var search = from sup in context.Supplier_Schedule
where sup.Supplier_ID == RQ.Suppllier_ID //&& sup.Status=="Active"
select sup;
int total;
total = search.Count();
if (RQ.PageSize == 0)
{
RQ.PageSize = 10;
}
int skip = (RQ.PageNo ?? 0) * (RQ.PageSize ?? 0);
var canPage = skip < total;
var result = from a in search
select new DataContracts.Schedulers.DC_Supplier_Schedule_RS
{
SupplierScheduleID = a.SupplierScheduleID,
Suppllier_ID = a.Supplier_ID,
Entity = a.Entity,
FrequencyTypeCode = a.FrequencyTypeCode,
TotalSize = total,
Status = a.Status,
};
return(result.OrderBy(p => p.Suppllier_ID).Skip(skip).Take((RQ.PageSize ?? total)).ToList());
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while fetching City Master", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public static List <DataContracts.DC_SqlTableColumnInfo> GetSqlTableColumnInfo(string tblname, List <string> datatypes)
{
using (ConsumerEntities context = new ConsumerEntities())
{
string sqlquery = string.Empty;
string formatstring = string.Empty;
if (datatypes.Count > 0)
{
formatstring = string.Format("{0}", string.Join("','", datatypes.Select(i => i.Replace("\"", "'"))));
sqlquery = "SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='" + tblname + "'and DATA_TYPE in ('" + formatstring + "');";
}
else
{
sqlquery = "SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tblname + "';";
}
var result = context.Database.SqlQuery <DataContracts.DC_SqlTableColumnInfo>(sqlquery).ToList();
return(result);
}
}
public List <DataContracts.DC_Master_City> GetCityMaster()
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var city = from c in context.m_CityMaster
orderby c.Name
select new DataContracts.DC_Master_City {
City_Id = c.City_Id, City_Name = c.Name, City_Code = c.Code, Country_Name = c.CountryName, Country_Id = c.Country_Id, State_Code = c.StateCode, State_Name = c.StateName
};
return(city.ToList());
}
}
catch (Exception ex)
{
//throw new FaultException<DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus { ErrorMessage = "Error while fetching city master", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError });
throw ex;
}
}
public List <DataContracts.DC_Master_Region> GetRegionMaster()
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var regions = (from c in context.m_CountryMaster
orderby c.RegionName
where c.Status == "ACTIVE" && c.RegionCode != null
select new DataContracts.DC_Master_Region {
RegionName = c.RegionName, RegionCode = c.RegionCode
}).Distinct();
return(regions.ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool UpdateSupplierSchedule(DataContracts.Schedulers.DC_Supplier_Schedule_RQ RQ)
{
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var search = (from ac in context.Supplier_Schedule
where ac.SupplierScheduleID == RQ.SupplierScheduleID
select ac).First();
if (RQ.IsActive == true)
{
var Activecount = (from a in context.Supplier_Schedule
where a.Supplier_ID == RQ.Suppllier_ID && a.Entity == RQ.Enitity && a.IsActive == true
select a).Count();
if (Activecount == 0)
{
search.IsActive = RQ.IsActive;
search.Edit_Date = RQ.Edit_Date;
search.Edit_User = RQ.Edit_User;
context.SaveChanges();
}
}
else if (search != null)
{
search.IsActive = RQ.IsActive;
search.Edit_Date = RQ.Edit_Date;
search.Edit_User = RQ.Edit_User;
context.SaveChanges();
}
return(true);
}
}
catch
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while updating accomodation contacts", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public DC_Message UpdateKafkaInfo(DataContracts.STG.DC_Stg_Kafka KafkaInfo)
{
DC_Message _msg = new DC_Message();
try
{
using (ConsumerEntities context = new ConsumerEntities())
{
var search = context.Stg_Kafka.Find(KafkaInfo.Row_Id);
if (search != null)
{
if (search.Status != KafkaInfo.Status && KafkaInfo.Status != "")
{
search.Status = KafkaInfo.Status;
}
;
search.Key = KafkaInfo.Key;
search.Offset = KafkaInfo.Offset;
//search.TimeStamp = KafkaInfo.TimeStamp;
search.Partion = KafkaInfo.Partion;
search.TopicPartion = KafkaInfo.TopicPartion;
search.TopicPartionOffset = KafkaInfo.TopicPartionOffset;
search.Process_User = KafkaInfo.Process_User;
search.Process_Date = DateTime.Now;
context.SaveChanges();
}
return(_msg);
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = "Error while updating Kafka details", ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
public DC_UnprocessedExecuterData getScalerCount(DC_UnprocessedExecuterData QueryID)
{
DC_UnprocessedExecuterData RunningData = new DC_UnprocessedExecuterData();
try
{
StringBuilder sb = new StringBuilder();
switch (QueryID.TotalCount)
{
case 1:
sb.Append(" DECLARE @CountOfRunning int;DECLARE @default uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier);select @CountOfRunning = count(0) from Supplier_ApiCallLog where status = 'RUNNING';");
sb.Append(" select @CountOfRunning TotalCount; ");
break;
default:
break;
}
using (ConsumerEntities context = new ConsumerEntities())
{
context.Database.CommandTimeout = 0;
RunningData = context.Database.SqlQuery <DC_UnprocessedExecuterData>(sb.ToString()).ToList().FirstOrDefault();
}
}
catch (Exception ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus
{
ErrorMessage = "Error while fetching Logger Tasks",
ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
return(RunningData);
}
private DC_Message InsertDistributionLogNewEntry(Guid LogId, string elementName, string Type, string status, string CreatedBy)
{
DC_Message msg = new DC_Message();
DataLayer.DistributionLayerRefresh_Log objNew = new DistributionLayerRefresh_Log();
objNew.Id = LogId;
objNew.Element = elementName;
objNew.Type = Type;
objNew.Create_Date = DateTime.Now;
objNew.Create_User = CreatedBy;
objNew.Status = status;
using (ConsumerEntities context = new ConsumerEntities())
{
context.DistributionLayerRefresh_Log.Add(objNew);
context.SaveChanges();
}
msg.StatusCode = ReadOnlyMessage.StatusCode.Success;
msg.StatusMessage = elementName + " " + Type + "Sync has been scheduled successfully.";
return(msg);
}
public DataContracts.DC_GeoLocation GetGeoLocationByAddress(DataContracts.DC_Address.DC_Address_Physical PA)
{
try
{
string Address = String.Empty;
if (!string.IsNullOrEmpty(PA.Street))
{
Address = Address + PA.Street.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
if (!string.IsNullOrEmpty(PA.CityAreaOrDistrict))
{
Address = Address + PA.CityAreaOrDistrict.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
if (!string.IsNullOrEmpty(PA.CityOrTownOrVillage))
{
Address = Address + PA.CityOrTownOrVillage.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
if (!string.IsNullOrEmpty(PA.CountyOrState))
{
Address = Address + PA.CountyOrState.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
if (!string.IsNullOrEmpty(PA.PostalCode))
{
Address = Address + PA.PostalCode.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
if (!string.IsNullOrEmpty(PA.Country))
{
Address = Address + PA.Country.Trim().TrimEnd(',').Replace(" ", "+") + ",";
}
Address = Address.TrimEnd(',').Trim();
DataContracts.DC_GeoLocation mapdata = null;
if (!string.IsNullOrEmpty(Address))
{
var request = (HttpWebRequest)WebRequest.Create("https://maps.googleapis.com/maps/api/geocode/json?address=" + Address + "&key=" + System.Configuration.ConfigurationManager.AppSettings["GoogleKey"].ToString());
var proxyAddress = System.Configuration.ConfigurationManager.AppSettings["ProxyUri"];
if (System.Configuration.ConfigurationManager.AppSettings["ProxyUri"] != null)
{
WebProxy myProxy = new WebProxy();
Uri newUri = new Uri(proxyAddress);
// Associate the newUri object to 'myProxy' object so that new myProxy settings can be set.
myProxy.Address = newUri;
// Create a NetworkCredential object and associate it with the
// Proxy property of request object.
//myProxy.Credentials = new NetworkCredential(username, password);
request.Proxy = myProxy;
}
request.KeepAlive = false;
//request.Credentials = CredentialCache.DefaultCredentials;
HttpWebResponse response = request.GetResponse() as HttpWebResponse;
if (response.StatusCode == HttpStatusCode.OK) //response.StatusDescription
{
Stream dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);
string responseFromServer = reader.ReadToEnd();
reader.Close();
mapdata = JsonConvert.DeserializeObject <DataContracts.DC_GeoLocation>(responseFromServer);
if (mapdata != null)
{
using (ConsumerEntities context = new ConsumerEntities())
{
GoogleGeoCode GC = new GoogleGeoCode();
GC.GoogleGeoCode_Id = Guid.NewGuid();
GC.Product_Id = PA.Product_Id;
GC.JobType = "ProductGeoLookup_ByAddress";
GC.Input = request.Address.AbsoluteUri;
GC.OutPut = responseFromServer;
context.GoogleGeoCodes.Add(GC);
context.SaveChanges();
}
}
}
//DataContractJsonSerializer obj = new DataContractJsonSerializer(typeof(DataContracts.DC_GeoLocation));
//var result = obj.ReadObject(dataStream) as DataContracts.DC_GeoLocation;
response.Close();
}
return(mapdata);
}
catch (WebException ex)
{
throw new FaultException <DataContracts.DC_ErrorStatus>(new DataContracts.DC_ErrorStatus {
ErrorMessage = ex.Message, ErrorStatusCode = System.Net.HttpStatusCode.InternalServerError
});
}
}
