private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetEventListCompany(string month, string yearV)
{
var listCompany = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
var conn = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
var reminderList = new CalendarEventDAL(conn).GetAllCAllBackEvents();
var monthtype = month;
var year = yearV;
reminderList = GetAllCurrentMonthValid(reminderList, monthtype, year);
if (reminderList.Count() > 0)
{
foreach (var item in reminderList)
{
if (item.companyId != "0")
{
var comView = new DeltoneCRM_DAL.CompanyDAL.CompanyView();
comView.CompanyID = Convert.ToInt32(item.companyId);
listCompany.Add(comView);
}
}
}
return(listCompany);
}
private List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetReALlocationCompany()
{
var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
// var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName,
// CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode
// ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP
// join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7";
var query = @"SELECT Distinct CP.CompanyName,
CP.CompanyID FROM dbo.Companies CP
inner join LeadCompany lo on CP.CompanyID=lo.CompanyId inner join
CalendarEvent ce on lo.CompanyId=ce.CompanyId and ce.IsLeadEvent=1 and
convert(varchar(10),ce.EventStart, 120) >= CAST(getdate() as date) and
convert(varchar(10),lo.ExpiryDate, 120) < CAST(getdate() as date) ";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = query;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView();
sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString());
sbj.CompanyName = sdr["CompanyName"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> DoFilterForCompany(List <ComReAssign> coms, string mode, string month, string year)
{
var listCompanyReminders = GetEventListCompany(month, year);
var listObj = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
if (mode == "2")
{
foreach (var item in coms)
{
var obj = new DeltoneCRM_DAL.CompanyDAL.CompanyView();
obj.CompanyID = Convert.ToInt32(item.ComId);
listObj.Add(obj);
}
}
else
{
return(listCompanyReminders);
}
return(listObj);
}
private static List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetCompaniesBYRep(string repId)
{
var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
// var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName,
// CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode
// ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP
// join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7";
string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
var query = @"SELECT CP.CompanyName,
CP.CompanyID ,Hold FROM dbo.Companies CP
where CP.OwnershipAdminID=@ownerID and CP.Active='Y' and (Hold IS NULL OR Hold<>'Y') and (IsSupperAcount IS NULL OR IsSupperAcount<>'1')";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ownerID", repId);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView();
sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString());
sbj.CompanyName = sdr["CompanyName"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
private List <DeltoneCRM_DAL.CompanyDAL.CompanyView> GetCompanyBYHouseAccount()
{
var comList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
// var query = @"SELECT Distinct CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName,
// CP.CompanyID ,lo.FirstName + ' ' + lo.LastName AS createdUser ,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode
// ,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM dbo.Companies CP
// join dbo.Contacts CT on CP.CompanyID = CT.CompanyID where CP.OwnershipAdminID=7";
var query = @"SELECT CP.CompanyName,
CP.CompanyID ,Hold FROM dbo.Companies CP
where CP.OwnershipAdminID=7 and CP.Active='Y' and (Hold IS NULL OR Hold<>'Y') and (LeadLocked IS NULL OR LeadLocked<>'Y')";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = query;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.CompanyDAL.CompanyView();
sbj.CompanyID = Convert.ToInt32(sdr["CompanyID"].ToString());
sbj.CompanyName = sdr["CompanyName"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
protected List <DeltoneCRM_DAL.CompanyDAL.CompanyView> LoadCompanyForAllocation(string rep, string startDate,
string endDate)
{
List <DeltoneCRM_DAL.CompanyDAL.CompanyView> sampleComList = new List <DeltoneCRM_DAL.CompanyDAL.CompanyView>();
var count = 0;
//var searchQuer=searche+'%';
var orderByString = "Order By OrderedDateTime desc";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = @"SELECT Distinct CP.CompanyID,ROW_NUMBER() OVER(ORDER BY OrderedDateTime ASC) AS Row# , CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName,
CP.Active,CP.Hold,CP.IsSupperAcount,CP.CreatedDateTime,CP.Notes ,MBG.OrderedDateTime ,
lc.FirstName + ' ' + lc.LastName
AS createdUser,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode
,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM
[dbo].Companies CP
inner join [dbo].Contacts CT on CP.CompanyID = CT.CompanyID
inner join [dbo].Logins lc on lc.LoginID=CP.OwnershipAdminID
OUTER APPLY
(SELECT Top 1 ORC.OrderedDateTime,ORC.OrderID
From [dbo].Orders ORC
Where ORC.CompanyID=CP.CompanyID
Order By OrderID Desc
) AS MBG
WHERE CP.OwnershipAdminID = " + rep + " and (CP.IsSupperAcount IS NULL OR CP.IsSupperAcount<>'1') " +
" and (CP.Hold IS NULL OR CP.Hold<>'Y') " + orderByString;
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
cmd.CommandText = @"SELECT Distinct CP.CompanyID,ROW_NUMBER() OVER(ORDER BY OrderedDateTime ASC) AS Row# , CP.CompanyName, CT.FirstName + ' ' + CT.LastName AS FullName,
CP.Active,CP.Hold,CP.IsSupperAcount,CP.CreatedDateTime,CP.Notes ,MBG.OrderedDateTime ,
lc.FirstName + ' ' + lc.LastName
AS createdUser,DEFAULT_Number ,DEFAULT_AreaCode, DEFAULT_CountryCode
,MOBILE_AreaCode, MOBILE_CountryCode ,MOBILE_Number ,Email FROM
[dbo].Companies CP
inner join [dbo].Contacts CT on CP.CompanyID = CT.CompanyID
inner join [dbo].Logins lc on lc.LoginID=CP.OwnershipAdminID
OUTER APPLY
(SELECT Top 1 ORC.OrderedDateTime,ORC.OrderID
From [dbo].Orders ORC
Where ORC.CompanyID=CP.CompanyID
Order By OrderID Desc
) AS MBG
WHERE CP.OwnershipAdminID = " + rep + " and MBG.OrderedDateTime between @startDate and @endDate and (CP.IsSupperAcount IS NULL OR CP.IsSupperAcount<>'1') " +
" and (CP.Hold IS NULL OR CP.Hold<>'Y') " + orderByString;;
var starDateTimeContver = Convert.ToDateTime(startDate).ToString("yyyy-MM-dd");
var endDateTimeContver = Convert.ToDateTime(endDate).ToString("yyyy-MM-dd");
cmd.Parameters.AddWithValue("@startDate", starDateTimeContver);
cmd.Parameters.AddWithValue("@endDate", endDateTimeContver);
}
cmd.Connection = conn;
conn.Open();
DataTable comTabldata = new DataTable();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
comTabldata.Load(sdr);
var query = comTabldata.AsEnumerable().ToList();
count = query.Count();
foreach (var row in query)
{
DateTime?renewalDate = row.Field <DateTime?>("OrderedDateTime");
var obj = new DeltoneCRM_DAL.CompanyDAL.CompanyView()
{
CompanyID = row.Field <int>("CompanyID"),
CompanyName = row.Field <string>("CompanyName"),
};
sampleComList.Add(obj);
}
}
conn.Close();
}
}
return(sampleComList);
}
