public static IssueType GetListOfIssueTypes(string startdate = "", string enddate = "", string Filter = "", int issue = 0)
{
CBDB db = new CBDB();
IssueType objI = new IssueType();
List <tbl_WeCareReactive> wclist = db.tbl_WeCareReactive.ToList();
try
{
if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
{
DateTime stdt = Convert.ToDateTime(startdate).Date, Tdt = Convert.ToDateTime(enddate).Date;
wclist = db.tbl_WeCareReactive.Where(x => x.BotEntryTime >= stdt && x.BotEntryTime <= Tdt).ToList();
objI.datelbl = "from " + startdate + " to " + enddate;
}
else
{
wclist = db.tbl_WeCareReactive.Where(x => x.BotEntryTime >= DateTime.Today.AddDays(-1) && x.BotEntryTime <= DateTime.Today).ToList();
objI.datelbl = "for " + DateTime.Today.AddDays(-1).ToString("dd-MMM-yyyy");
}
if (!string.IsNullOrEmpty(Filter))
{
wclist = wclist.Where(x => x.Issue.Equals(Filter)).ToList();
}
List <string> issues = wclist.Select(x => x.Issue).Distinct().ToList();
objI.Issuetypes = issues;
objI.Issuetypesfigures = new List <string>();
foreach (string item in objI.Issuetypes)
{
objI.Issuetypesfigures.Add(wclist.Where(x => x.Issue.Equals(item)).ToList().Count.ToString());
}
objI = GetColorOnIssueType(objI, issue);
}
catch { }
return(objI);
}
public static List <tbl_UnassignedTickets> GetCaseReadyTable()
{
CBDB db = new CBDB();
List <tbl_UnassignedTickets> list = db.tbl_UnassignedTickets.SqlQuery("select * from tbl_UnassignedTickets where Status in ('processed','businessruleexception');").ToList();
return(list);
}
public static List <tbl_AuthCode> getRoutingPortalTable(string fromdate = "", string todate = "", string filter = "")
{
CBDB db = new CBDB();
List <tbl_AuthCode> list = db.tbl_AuthCode.SqlQuery(GetQueryForTblAuthCode(fromdate, todate, filter)).ToList();
return(list);
}
public static Tbl_User_Detail GetUser(string uname, string pwd)
{
using (CBDB db = new CBDB())
{
var obj = db.Tbl_User_Detail.Where(x => x.UserName.Equals(uname) && x.Password.Equals(pwd)).FirstOrDefault();
return(obj);
}
}
public static List <Tbl_User_Detail> GetUsersForProfile()
{
List <Tbl_User_Detail> list = new List <Tbl_User_Detail>();
CBDB db = new CBDB();
list = db.Tbl_User_Detail.Where(x => !x.Usergroup.Equals(Constants.UserGroups.UserManager)).ToList();
return(list);
}
public static void SaveUserPages(int userid, string pages)
{
CBDB db = new CBDB();
var user = db.Tbl_User_Detail.Where(x => x.ID == userid).FirstOrDefault();
user.GroupPages = pages;
db.SaveChanges();
}
public static List <string> WCScaseStatusFigure(ref List <tbl_WeCareReactive> tblWC, string startdate = "", string enddate = "", string Filter = "")
{
CBDB db = new CBDB();
List <tbl_WeCareReactive> wclist = new List <tbl_WeCareReactive>();
List <string> wccount = new List <string>();
string daterange = string.Empty;
try
{
if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
{
DateTime stdt = Convert.ToDateTime(startdate).Date, Tdt = Convert.ToDateTime(enddate).Date;
wclist = db.tbl_WeCareReactive.Where(x => x.BotEntryTime >= stdt && x.BotEntryTime <= Tdt).ToList();
daterange = "from " + startdate + " to" + enddate;
}
else
{
wclist = db.tbl_WeCareReactive.ToList();
var wc = wclist.OrderByDescending(x => x.BotEntryTime).LastOrDefault();
if (wc != null && wc.BotEntryTime != null)
{
daterange = Convert.ToDateTime(wc.BotEntryTime).ToString("dd-MMM-yyyy");
}
daterange = "from " + daterange + " till date.";
}
wccount.Add(wclist.Where(x => x.Stage.Contains("Closed") || x.Stage.Contains("Resolved")).ToList().Count.ToString());
wccount.Add(wclist.Where(x => x.Stage.ToLower().Replace(" ", "").Contains("InProgress".ToLower()) && x.AssignedUserID != null).ToList().Count.ToString());
wccount.Add(wclist.Where(x => x.Stage.ToLower().Replace(" ", "").Contains("InProgress".ToLower()) && x.AssignedUserID == null).ToList().Count.ToString());
wccount = getpercentagefigure(wccount);
wccount.Add(daterange);
//-----------------------------------------------Table----------------------------------------------------------------
if (!string.IsNullOrEmpty(Filter))
{
if (Filter.Equals("Closed"))
{
wclist = wclist.Where(x => x.Stage.Contains("Closed") || x.Stage.Contains("Resolved")).ToList();//Closed
}
else if (Filter.Equals("In Progress"))
{
wclist = wclist.Where(x => x.Stage.Contains("In Progress") && x.AssignedUserID != null).ToList();//Inprogress
}
else
{
wclist = wclist.Where(x => x.Stage.Contains("In Progress") && x.AssignedUserID == null).ToList();//Unactioned
}
}
else
{//by default table must show Inprogress and unactioned
wclist = wclist.Where(x => (x.Stage.ToLower().Replace(" ", "").Contains("InProgress".ToLower()) && x.AssignedUserID != null) ||
(x.Stage.ToLower().Replace(" ", "").Contains("InProgress".ToLower()) && x.AssignedUserID == null)).ToList();
}
tblWC = wclist;
}
catch { }
return(wccount);
}
public static List <tbl_IssuingIncomingVISA> AcceptedCaseClosureReportDefaultData()
{
List <tbl_IssuingIncomingVISA> list = new List <tbl_IssuingIncomingVISA>();
try
{
CBDB db = new CBDB();
list = db.tbl_IssuingIncomingVISA.SqlQuery("select * from tbl_IssuingIncomingVISA where CaseSubmissionStatus In ('open','close')").ToList();
}
catch (Exception ex) { throw ex; }
return(list);
}
public static List <tbl_IssuingIncomingVISA> AcceptedCaseClosureReportFilter(ViewModelClass.ClosureReportFilter filter)
{
List <tbl_IssuingIncomingVISA> list = new List <tbl_IssuingIncomingVISA>();
try
{
CBDB db = new CBDB();
list = db.tbl_IssuingIncomingVISA.SqlQuery(GetQueryForAcceptedClosureReport(filter)).ToList();
}
catch (Exception ex) { throw ex; }
return(list);
}
public static List <NonCustom_GLReconciliationTable> GetReconsiledReportFilterd(ViewModelClass.ReconciliationFilter filter)
{
List <NonCustom_GLReconciliationTable> list = new List <NonCustom_GLReconciliationTable>();
try
{
CBDB db = new CBDB();
list = db.NonCustom_GLReconciliationTable.SqlQuery(GetQueryForReconciliedData(filter)).ToList();
}
catch (Exception ex) { throw ex; }
return(list);
}
public static List <tbl_UnassignedTickets> GetCaseHistoryFilterd(ViewModelClass.FilterClass filter)
{
List <tbl_UnassignedTickets> list = new List <tbl_UnassignedTickets>();
try
{
CBDB db = new CBDB();
list = db.tbl_UnassignedTickets.SqlQuery(GetQueryForTblUnassigned(filter)).ToList();
}
catch (Exception ex) { throw ex; }
return(list.OrderByDescending(x => x.BotDataEntryTime).ToList());
}
public static List <tbl_WeCareReactive> GetSla(SLAFilter obj)
{
CBDB db = new CBDB();
List <tbl_WeCareReactive> list = new List <tbl_WeCareReactive>();
try
{
if (!string.IsNullOrEmpty(obj.Filter))
{
list = db.tbl_WeCareReactive.Where(x => x.AssignedUserID.Equals(obj.Filter)).ToList();
}
else
{
list = db.tbl_WeCareReactive.ToList();
}
}
catch { }
return(list);
}
public static void MachineLogout(string MName)
{
try
{
// WriteToLogFile.writeMessage("MachineLogout Method [Started]");
using (CBDB db = new CBDB())
{
var obj = db.tbl_MachineInfo.Where(x => x.MachineName.Equals(MName)).FirstOrDefault();
obj.IsActive = false;
db.SaveChanges();
}
// WriteToLogFile.writeMessage("MachineLogout Method [Ended] successfully");
}
catch (Exception Ex)
{
//WriteToLogFile.writeMessage("MachineLogout Method [Ended] UNsuccessfully Error = " +Ex.Message.ToString());
throw Ex;
}
}
public static List <Unmatched_FinancialTransaction> GetUnMatchedTransaction(ViewModelClass.FilterClass filter)
{
List <Unmatched_FinancialTransaction> list = new List <Unmatched_FinancialTransaction>();
try
{
CBDB db = new CBDB();
if (filter.FeedbackID != null && filter.Fromdate != null && filter.Todate != null)
{
DateTime fdt = Convert.ToDateTime(filter.Fromdate), tdt = Convert.ToDateTime(filter.Todate);
list = db.Unmatched_FinancialTransaction.Where(x => x.BotEntryTime >= fdt && x.BotEntryTime <= tdt).ToList();
}
else
{
list = db.Unmatched_FinancialTransaction.ToList();
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static void AddRow_NonCustom_GLReconciliationTable(NonCustom_GLReconciliationTable obj)
{
NonCustom_GLReconciliationTable addObj = new NonCustom_GLReconciliationTable();
if (obj != null)
{
CBDB db = new CBDB();
CaseStatisticsDataLayer objdata = new CaseStatisticsDataLayer();
addObj.ValueDate = obj.ValueDate;
addObj.PostDate = obj.PostDate;
addObj.MemberCase = obj.MemberCase;
addObj.Name = obj.Name;
addObj.Reference = obj.CardNumber;
addObj.Credit = obj.Credit;
addObj.Debit = obj.Debit;
addObj.IsActive = true;
db.NonCustom_GLReconciliationTable.Add(addObj);
db.SaveChanges();
}
}
public static Tbl_User_Detail SaveUser(Tbl_User_Detail obj)
{
Tbl_User_Detail tbl = new Tbl_User_Detail();
using (CBDB db = new CBDB())
{
tbl = db.Tbl_User_Detail.Where(x => x.UserName.Equals(obj.UserName)).FirstOrDefault();
if (tbl == null)
{
if (obj.Usergroup.ToLower().Contains("admin"))
{
obj.GroupPages = "CaseStat ,WCStat ,SLA ,CaseHistory ,CaseClosure ,MtchedTran ,UnmtchedTran ,Recon,RobotConfig";
}
else
{
obj.GroupPages = "CaseStat ,WCStat ,SLA ,CaseHistory ,CaseClosure ,MtchedTran ,UnmtchedTran ,Recon";
}
db.Tbl_User_Detail.Add(obj);
db.SaveChanges();
tbl = obj;
}
}
return(tbl);
}
public static bool CheckMachine(string Machine)
{
if ((String.IsNullOrEmpty(Machine)))
{
WriteToLogFile.writeMessage("Machine Name Received null");
}
bool Check = false;
try
{
//WriteToLogFile.writeMessage("Check Machine [Started]");
using (CBDB db = new CBDB())
{
//WriteToLogFile.writeMessage("Connection With DB Created");
var mobj = db.tbl_MachineInfo.Where(x => x.MachineName.Equals(Machine)).FirstOrDefault();
if (mobj == null)
{
//WriteToLogFile.writeMessage("No machine is present with this name");
//WriteToLogFile.writeMessage("Creating New Machine Entry in DB");
tbl_MachineInfo obj = new tbl_MachineInfo();
obj.MachineName = Machine;
obj.CreatedTs = DateTime.Now;
obj.IsActive = true;
///WriteToLogFile.writeMessage("Adding Entry in Table");
db.tbl_MachineInfo.Add(obj);
// WriteToLogFile.writeMessage("Entry Added Successfully in Table");
db.SaveChanges();
//WriteToLogFile.writeMessage("Changes Saved Successfully");
Check = true;
}
else if (mobj.IsActive == false)
{
// WriteToLogFile.writeMessage("Machine Already present but with is asctive as false");
mobj.IsActive = true;
mobj.CreatedTs = DateTime.Now;
//WriteToLogFile.writeMessage("Saving Changes to DB");
db.SaveChanges();
//WriteToLogFile.writeMessage("Changes Saved Successfully");
Check = true;
}
else
{
//WriteToLogFile.writeMessage("Machine Already present and with is asctive as True");
//WriteToLogFile.writeMessage("Checking Time Difference");
int TimeDiff = Convert.ToInt32(ConfigurationManager.AppSettings["TimeDiff"].ToString());
//WriteToLogFile.writeMessage("Time Diff = "+TimeDiff.ToString());
int diff = (DateTime.Now - Convert.ToDateTime(mobj.CreatedTs)).Minutes;
///WriteToLogFile.writeMessage("diff in minutes "+diff.ToString());
if (diff >= TimeDiff)
{
// WriteToLogFile.writeMessage("Machine Time out reached to maximun updating is active to true");
mobj.IsActive = true;
mobj.CreatedTs = DateTime.Now;
//WriteToLogFile.writeMessage("Saving Changes to DB");
db.SaveChanges();
//WriteToLogFile.writeMessage("Changes Saved Successfully");
Check = true;
}
else
{
// WriteToLogFile.writeMessage("Cannot activate machine because time out not reached");
}
}
}
}
catch (Exception Ex)
{
//throw Ex;
// WriteToLogFile.writeMessage("Exception Occured While processing this method exception msg = " + Ex.Message.ToString());
}
//WriteToLogFile.writeMessage("Returned Check Value "+Check.ToString());
return(Check);
}
