private List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne> GetLast6MonthQuote()
{
var comList = new List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne>();
var dateLastMonth = DateTime.Now.AddMonths(-6).ToString("yyyy-MM-dd");
var qtCat = 0;//status new
// var query = @"SELECT QuoteID,QuoteByID,QuoteDateTime,QuoteBy,Total,Status,Flag FROM QUOTE
// where convert(varchar(10),QuoteDateTime, 120) >@last6month and QuoteCategory=@qtCat ";
var query = @"SELECT QT.QuoteID,QT.QuoteByID, QT.QuoteDateTime as QuoteDate, CO.CompanyName,CO.CompanyID ,CT.ContactID,'Existing Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
"dbo.Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='LiveDB' and QT.Status<>'CANCELLED' and convert(varchar(10),QuoteDateTime, 120) >@last6month and QuoteCategory=@qtCat " +
" Union SELECT QT.QuoteID, QT.QuoteByID, QT.QuoteDateTime as QuoteDate, CO.CompanyName,CO.CompanyID , CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
"dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.Status<>'CANCELLED' and convert(varchar(10),QuoteDateTime, 120) >@last6month and QuoteCategory=@qtCat ";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.AddWithValue("@last6month", dateLastMonth);
cmd.Parameters.AddWithValue("@qtCat", qtCat);
cmd.CommandText = query;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne();
sbj.QuoteId = Convert.ToInt32(sdr["QuoteID"].ToString());
sbj.QuotedById = Convert.ToInt32(sdr["QuoteByID"].ToString());
sbj.QuotedDate = sdr["QuoteDate"].ToString();
sbj.QuotedByName = sdr["QuoteBy"].ToString();
sbj.QuoteTotal = sdr["Total"].ToString();
sbj.CompanyName = sdr["CompanyName"].ToString();
sbj.ContactName = sdr["ContactName"].ToString();
sbj.Status = sdr["Status"].ToString();
sbj.CustomerType = sdr["Type"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
private static List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne> GetRepQuote(string quoteOwnerId)
{
var comList = new List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne>();
string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
var query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteByID=@createdBy and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' ";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.AddWithValue("@createdBy", quoteOwnerId);
cmd.CommandText = query;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne();
sbj.QuoteId = Convert.ToInt32(sdr["QuoteID"].ToString());
sbj.QuotedById = Convert.ToInt32(sdr["QuoteByID"].ToString());
sbj.QuotedDate = sdr["QuoteDate"].ToString();
sbj.QuotedByName = sdr["QuoteBy"].ToString();
sbj.QuoteTotal = sdr["Total"].ToString();
sbj.CompanyName = sdr["CompanyName"].ToString();
sbj.ContactName = sdr["ContactName"].ToString();
sbj.Status = sdr["Status"].ToString();
sbj.CustomerType = sdr["Type"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
private static List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne> GetRepQuote(string quoteOwnerId, string startDate,
string endDate)
{
var comList = new List <DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne>();
string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = CONNSTRING;
using (SqlCommand cmd = new SqlCommand())
{
var query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' AND NOT EXISTS (SELECT * FROM [Contacts] od WHERE od.Email = CT.Email ) ";
if (Convert.ToInt32(quoteOwnerId) > 0)
{
query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteByID=@createdBy and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' AND NOT EXISTS (SELECT * FROM [Contacts] od WHERE od.Email = CT.Email )";
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteByID=@createdBy and QT.QuoteDateTime between @startDate and @endDate QT.QuoteByID=@createdBy and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' AND NOT EXISTS (SELECT * FROM [Contacts] od WHERE od.Email = CT.Email )";
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.Parameters.AddWithValue("@createdBy", quoteOwnerId);
}
else
{
query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' AND NOT EXISTS (SELECT * FROM [Contacts] od WHERE od.Email = CT.Email )";
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
query = @" SELECT QT.QuoteID, QT.QuoteDateTime as QuoteDate,QT.QuoteByID, CO.CompanyName,CO.CompanyID ,QT.CallBackDate, CT.ContactID,'New Customer' as Type," +
" CT.FirstName + ' ' + CT.LastName AS ContactName, QT.Total, QT.Status, QT.QuoteBy FROM dbo.Quote QT INNER JOIN " +
" dbo.Quote_Contacts CT ON QT.ContactID = CT.ContactID INNER JOIN dbo.Quote_Companies CO ON CO.CompanyID=QT.CompanyID where QT.Flag='QuoteDB' and QT.QuoteDateTime between @startDate and @endDate and QT.QuoteCategory=0 and QT.Status<>'CANCELLED' AND NOT EXISTS (SELECT * FROM [Contacts] od WHERE od.Email = CT.Email )";
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.CommandText = query;
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
var sbj = new DeltoneCRM_DAL.QuoteDAL.QuoteAssignOne();
sbj.QuoteId = Convert.ToInt32(sdr["QuoteID"].ToString());
sbj.QuotedById = Convert.ToInt32(sdr["QuoteByID"].ToString());
sbj.QuotedDate = sdr["QuoteDate"].ToString();
sbj.QuotedByName = sdr["QuoteBy"].ToString();
sbj.QuoteTotal = sdr["Total"].ToString();
sbj.CompanyName = sdr["CompanyName"].ToString();
sbj.CompanyId = Convert.ToInt32(sdr["CompanyID"].ToString());
sbj.ContactName = sdr["ContactName"].ToString();
sbj.Status = sdr["Status"].ToString();
sbj.CustomerType = sdr["Type"].ToString();
comList.Add(sbj);
}
}
}
}
}
return(comList);
}
