public Agent(int iRepNo)
{
if (iRepNo == -1)
{
_sCompany = "All Agents";
_iRepNo = -1;
_sAgentCode = "All Agents";
}
else
{
_iRepNo = iRepNo;
_ds = DataLink.Select("select * from Agents where RepNo = " + iRepNo, DataLinkCon.bpss);
if (_ds.Tables.Count > 0)
{
IEnumerable <DataRow> rows = _ds.Tables[0].AsEnumerable();
_sAgentCode = rows.Select(r => r["AgentCode"].ToString()).First();
_sCompany = rows.Select(r => r["Company"].ToString()).First();
_sAddressCity = rows.Select(r => r["AddressCity"].ToString()).First();
_sAddressState = rows.Select(r => r["AddressState"].ToString()).First();
_sAddressStreet = rows.Select(r => r["AddressStreet"].ToString()).First();
_sAddressZip = rows.Select(r => r["AddressZip"].ToString()).First();
_sCompanyEMail = rows.Select(r => r["CompanyEmail"].ToString()).First();
_sDefaultContactName = rows.Select(r => r["DefaultContactName"].ToString()).First();
_sPhone = rows.Select(r => r["Phone"].ToString()).First();
}
}
}
/// <summary>
/// Logs that this user opened Rep Portal.
/// </summary>
/// <param name="sUserName"></param>
protected void WriteToLog(string sUserName)
{
string sSql = "INSERT INTO LogUserActivity(UserName) " + Environment.NewLine +
"VALUES ('" + sUserName + "')";
DataLinkCon dlc = DataLinkCon.mgmuser;
DataLink.Update(sSql, dlc);
}
/// <summary>
/// Write a message to the error log: mgmuser.LogError
/// </summary>
/// <param name="sErrorMsg"></param>
public static void Write(string sErrorMsg)
{
sErrorMsg = sErrorMsg.Replace("'", "''");
string sQuery = "INSERT INTO LogErrors(ErrorMsg) VALUES('" + sErrorMsg + "')";
DataLink.Update(sQuery, DataLinkCon.mgmuser);
}
static public List <StockInventoryItem> GetAllItems()
{
_ssSQL = "select CatalogNumber from Stock";
_sds = DataLink.Select(_ssSQL, DataLinkCon.mgmuser);
return(_sds.Tables[0].AsEnumerable().Select(r => new StockInventoryItem(r["CatalogNumber"].ToString(),
InventoryDescType.CatalogNumber)).Cast <StockInventoryItem>().ToList());
}
static public List <string> GetAltNumber(string sWindings, string sStockVoltageDisplay, string sKVA)
{
_ssSQL = "select s.AltNumber from Stock s join StockVoltages sv on s.Configuration = sv.StockVoltage where s.Windings = '" +
sWindings + "' and s.KVA = " + sKVA + " and sv.StockVoltageDisplay = '" + sStockVoltageDisplay + "'";
_sds = DataLink.Select(_ssSQL, DataLinkCon.mgmuser);
return(_sds.Tables[0].AsEnumerable().Select(r => r["AltNumber"].ToString()).ToList());
}
static public string GetCategory(string sStockVoltageDisplay, string sWindings)
{
if (sStockVoltageDisplay == "ALL")
{
return("ALL");
}
_sds = DataLink.Select("select VoltageCategory from StockVoltages where StockVoltageDisplay = '" + sStockVoltageDisplay + "' and Windings = '" + sWindings + "'",
DataLinkCon.mgmuser);
return(_sds.Tables[0].AsEnumerable().Select(r => r["VoltageCategory"].ToString()).First());
}
static public List <StockVoltages> GetAllItems()
{
List <StockVoltages> retLst;
_ssSQL = "select * from StockVoltages";
_sds = DataLink.Select(_ssSQL, DataLinkCon.mgmuser);
retLst = _sds.Tables[0].AsEnumerable().Select(r => new StockVoltages(r["StockVoltageDisplay"].ToString(),
StockVoltagesType.StockVoltageDisplay)).Cast <StockVoltages>().ToList();
return(retLst);
}
public StockVoltages(string sDesc, StockVoltagesType svt)
{
if (svt == StockVoltagesType.StockVoltageDisplay)
{
_sSQL = "select * from StockVoltages where StockVoltageDisplay = '" + sDesc + "'";
}
_ds = DataLink.Select(_sSQL, DataLinkCon.mgmuser);
_sStockVoltage = _ds.Tables[0].AsEnumerable().Select(r => r["StockVoltage"].ToString()).First();
_sStockVoltageDisplay = _ds.Tables[0].AsEnumerable().Select(r => r["StockVoltageDisplay"].ToString()).First();
_sVoltageCategory = _ds.Tables[0].AsEnumerable().Select(r => r["VoltageCategory"].ToString()).First();
_sWindings = _ds.Tables[0].AsEnumerable().Select(r => r["Windings"].ToString()).First();
}
static public int GetAgentNoFromRepID(int iRepID)
{
int iRetVal = -1;
DataSet dsAgentNo;
dsAgentNo = DataLink.Select("select MGMAgentNo from Rep where RepID = " + iRepID, DataLinkCon.mgmuser);
if (dsAgentNo.Tables.Count > 0)
{
if (dsAgentNo.Tables[0].Rows.Count > 0)
{
iRetVal = Convert.ToInt32(dsAgentNo.Tables[0].Rows[0]["MGMAgentNo"]);
}
}
return(iRetVal);
}
static public int GetQuoteEmailID(int iQuoteID)
{
int iQuoteEmailID = -1;
DataSet dsQuote = DataLink.Select("select max(QuoteEmailID) as QuoteEmailID from QuoteEmails where QuoteID = " + iQuoteID, DataLinkCon.mgmuser);
if (dsQuote.Tables.Count > 0)
{
if (dsQuote.Tables[0].Rows.Count > 0)
{
if (!DBNull.Value.Equals(dsQuote.Tables[0].Rows[0]["QuoteEmailID"]))
{
iQuoteEmailID = Convert.ToInt32(dsQuote.Tables[0].Rows[0]["QuoteEmailID"]);
}
}
}
return(iQuoteEmailID);
}
private string SendSQLMail(string sPDF, bool bNoPrice, bool bSubmittal)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mgmdb"].ToString()))
{
SqlCommand cmd = new SqlCommand("usp_Email_Send_20190117", con);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@quote_id", QuoteId);
cmd.Parameters.AddWithValue("@to_name", txtToName.Text.ToString());
cmd.Parameters.AddWithValue("@to_email", txtToEmail.Text.ToString());
cmd.Parameters.AddWithValue("@cc_name", txtCCName.Text.ToString());
cmd.Parameters.AddWithValue("@cc_email", txtCCEmail.Text.ToString());
cmd.Parameters.AddWithValue("@subj", txtSubject.Text.ToString());
cmd.Parameters.AddWithValue("@body_text", txtBody.Text.ToString());
cmd.Parameters.AddWithValue("@attach", sPDF);
cmd.Parameters.AddWithValue("@send", 1);
cmd.Parameters.AddWithValue("@is_no_price", bNoPrice);
cmd.Parameters.AddWithValue("@is_submittal", bSubmittal);
con.Open();
int numrows = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
DataLink.Update("insert into EMailPendingLog (Attempts,DateTimeAdded,DateTimeVerified,EmailTypeCode,ErrorText,QuoteEmailID,[Status],UserEmail,UserName) values (" +
"1,GetDate(),null,'Email Screen','" + ex.Message + "',-1,',0,'" + HttpContext.Current.Session["Email"].ToString() +
"','" + HttpContext.Current.Session["UserName"].ToString() + "')", DataLinkCon.mgmuser);
return("Failed to create log entry.");
}
finally
{
cmd.Dispose();
con.Close();
}
DataLink.Update("insert into EMailPendingLog (Attempts,DateTimeAdded,DateTimeVerified,EmailTypeCode,ErrorText,QuoteEmailID,[Status],UserEmail,UserName) values (" +
"1,GetDate(),GetDate(),'Email Screen',''," + Utility.GetQuoteEmailID(QuoteId) + ",1,'" + HttpContext.Current.Session["Email"].ToString() +
"','" + HttpContext.Current.Session["UserName"] + "')", DataLinkCon.mgmuser);
}
return("");
}
public static int GetMGMAgentNo(string sRepName)
{
int iRetValue = -1;
DataSet ds = DataLink.Select("select MGMAgentNo from Rep where Display_Name = '" + sRepName + "'", DataLinkCon.mgmuser);
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
iRetValue = ds.Tables[0].AsEnumerable().Select(r => Convert.ToInt32(r["MGMAgentNo"])).First();
}
}
}
return(iRetValue);
}
public StockInventoryItem(string sDesc, InventoryDescType descType)
{
if (descType == InventoryDescType.AltNumber)
{
_sSQL = "select s.CatalogNumber,s.AltNumber,s.Height,s.Width,s.Depth,s.PrimaryVoltageDW,s.SecondaryVoltageDW,s.Hertz,s.Temperature,s.Enclosure," +
"s.PrimaryVoltage,s.SecondaryVoltage,s.TP1,s.SoundLevel,s.KVA,s.Weight,s.Windings,s.Configuration,sv.StockVoltageDisplay,sv.VoltageCategory " +
"from Stock s join StockVoltages sv on " +
"s.Configuration = sv.StockVoltage and s.Windings = sv.Windings where AltNumber = '" + sDesc + "'";
}
if (descType == InventoryDescType.CatalogNumber)
{
_sSQL = "select s.CatalogNumber,s.AltNumber,s.Height,s.Width,s.Depth,s.PrimaryVoltageDW,s.SecondaryVoltageDW,s.Hertz,s.Temperature,s.Enclosure," +
"s.PrimaryVoltage,s.SecondaryVoltage,s.TP1,s.SoundLevel,s.KVA,s.Weight,s.Windings,s.Configuration,sv.StockVoltageDisplay,sv.VoltageCategory " +
"from Stock s join StockVoltages sv on " +
"s.Configuration = sv.StockVoltage and s.Windings = sv.Windings where CatalogNumber = '" + sDesc + "'";
}
_ds = DataLink.Select(_sSQL, DataLinkCon.mgmuser);
_sCatalogNumber = _ds.Tables[0].AsEnumerable().Select(r => r["CatalogNumber"].ToString()).First();
_sAltNumber = _ds.Tables[0].AsEnumerable().Select(r => r["AltNumber"].ToString()).First();
_dHeight = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToDecimal(r["Height"])).First();
_dWidth = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToDecimal(r["Width"])).First();
_dDepth = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToDecimal(r["Depth"])).First();
_sPrimaryVoltageDW = _ds.Tables[0].AsEnumerable().Select(r => r["PrimaryVoltageDW"].ToString()).First();
_sSecondaryVoltageDW = _ds.Tables[0].AsEnumerable().Select(r => r["SecondaryVoltageDW"].ToString()).First();
_iHertz = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToInt32(r["Hertz"])).First();
_iTemperature = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToInt32(r["Temperature"])).First();
_sEnclosure = _ds.Tables[0].AsEnumerable().Select(r => r["Enclosure"].ToString()).First();
_sPrimaryVoltage = _ds.Tables[0].AsEnumerable().Select(r => r["PrimaryVoltage"].ToString()).First();
_sSecondaryVoltage = _ds.Tables[0].AsEnumerable().Select(r => r["SecondaryVoltage"].ToString()).First();
_sTP1 = _ds.Tables[0].AsEnumerable().Select(r => r["TP1"].ToString()).First();
_sSoundLevel = _ds.Tables[0].AsEnumerable().Select(r => r["SoundLevel"].ToString()).First();
_dKVA = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToDouble(r["KVA"])).First();
_iWeight = _ds.Tables[0].AsEnumerable().Select(r => Convert.ToInt32(r["Weight"])).First();
_sWindings = _ds.Tables[0].AsEnumerable().Select(r => r["Windings"].ToString()).First();
_sConfiguration = _ds.Tables[0].AsEnumerable().Select(r => r["Configuration"].ToString()).First();
_sStockVoltageDisplay = _ds.Tables[0].AsEnumerable().Select(r => r["StockVoltageDisplay"].ToString()).First();
_sVoltageCat = _ds.Tables[0].AsEnumerable().Select(r => r["VoltageCategory"].ToString()).First();
_bUseSelectString = false;
}
public static string GetAgentCode(int iMGMAgentNo, string sDisplayName)
{
int iRepID = -1;
string sAgentCode = "";
DataSet ds = DataLink.Select("select RepID from Rep where MGMAgentNo = " +
iMGMAgentNo + " and Display_Name = '" + sDisplayName + "'", DataLinkCon.mgmuser);
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
iRepID = ds.Tables[0].AsEnumerable().Select(r => Convert.ToInt32(r["RepID"])).First();
}
}
}
if (iRepID != -1)
{
ds = DataLink.Select("select AgentCode from Agents where RepIDWeb = " + iRepID, DataLinkCon.bpss);
if (ds != null)
{
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
sAgentCode = ds.Tables[0].AsEnumerable().Select(r => r["AgentCode"].ToString()).First();
}
}
}
}
return(sAgentCode);
}
protected string Save(bool bNoPrice)
{
bool bRetValue = false;
bool bSubmittal = rblQuoteOrSubmittal.SelectedValue == "1" ? true : false;
string sUserName = Session["UserName"].ToString();
string sUserEmail = Session["Email"].ToString();
bool bOEMSuccess = false;
MailAddress from = null;
QuoteId = Convert.ToInt32(Request.QueryString["QuoteID"]);
Quotes q = new Quotes();
int iQuoteID = QuoteId;
if (bSubmittal == true)
{
if (q.SubmittalValid(iQuoteID) == false)
{
return("Submittal not set up.");
}
}
bool bSuccess = false;
if (bNoPrice == true || bSubmittal == true)
{
bSuccess = q.CreatePDF(iQuoteID, bNoPrice, false, true, bSubmittal, out bOEMSuccess);
// Don't continue if unsuccessful either in generating a PDF or Word document.
if (!bSuccess || !bOEMSuccess)
{
return("Unable to create PDF.");
}
}
string sPDF = GetPDFPath(bNoPrice, bSubmittal);
if (Convert.ToBoolean(WebConfigurationManager.AppSettings["UseSQLMail"]))
{
SendSQLMail(sPDF, bNoPrice, bSubmittal);
}
if (Convert.ToBoolean(WebConfigurationManager.AppSettings["UseSMTPMail"]))
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mgmdb"].ToString()))
{
SqlCommand cmd = new SqlCommand("usp_Email_Send_20190117", con);
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@quote_id", QuoteId);
cmd.Parameters.AddWithValue("@to_name", txtToName.Text.ToString());
cmd.Parameters.AddWithValue("@to_email", txtToEmail.Text.ToString());
cmd.Parameters.AddWithValue("@cc_name", txtCCName.Text.ToString());
cmd.Parameters.AddWithValue("@cc_email", txtCCEmail.Text.ToString());
cmd.Parameters.AddWithValue("@subj", txtSubject.Text.ToString());
cmd.Parameters.AddWithValue("@body_text", txtBody.Text.ToString());
cmd.Parameters.AddWithValue("@attach", sPDF);
cmd.Parameters.AddWithValue("@send", 0);
cmd.Parameters.AddWithValue("@is_no_price", bNoPrice);
cmd.Parameters.AddWithValue("@is_submittal", bSubmittal);
con.Open();
int numrows = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
System.Console.WriteLine("Exception: " + ex.ToString());
return("Unable to send email.");
}
finally
{
bRetValue = true;
cmd.Dispose();
con.Close();
}
if (bRetValue)
{
try
{
if (!Convert.ToBoolean(WebConfigurationManager.AppSettings["UpdateEmailTablesOnly"]))
{
SmtpClient client = new SmtpClient(WebConfigurationManager.AppSettings["ExchangeServerIPAddress"]);
client.UseDefaultCredentials = true;
MailAddress to = new MailAddress(Session["Email"].ToString(), Session["RepName"].ToString(), System.Text.Encoding.UTF8);
if (Session["Internal"].ToString() == "1")
{
from = new MailAddress(Session["Email"].ToString(), "MGM Quote System", System.Text.Encoding.UTF8);
}
else
{
from = new MailAddress("*****@*****.**", "MGM Quote System", System.Text.Encoding.UTF8);
}
MailMessage message = new MailMessage(from, to);
message.Body = txtBody.Text.ToString();
message.BodyEncoding = System.Text.Encoding.UTF8;
message.Subject = txtSubject.Text.ToString();
message.SubjectEncoding = System.Text.Encoding.UTF8;
if (string.IsNullOrWhiteSpace(sPDF) == false)
{
message.Attachments.Add(new Attachment(sPDF));
}
foreach (string sEmail in txtCCEmail.Text.Split(';'))
{
if (Utility.IsValidEmail(sEmail))
{
message.CC.Add(sEmail);
}
}
if (Utility.IsValidEmail(txtToEmail.Text.ToString()))
{
message.CC.Add(txtToEmail.Text.ToString());
}
client.Send(message);
message.Dispose();
}
DataLink.Update("insert into EMailPendingLog (Attempts,DateTimeAdded,DateTimeVerified,EmailTypeCode,ErrorText,QuoteEmailID,[Status],UserEmail,UserName) values (" +
"1,GetDate(),GetDate(),'Email Screen',''," + Utility.GetQuoteEmailID(QuoteId) + ",1,'" + HttpContext.Current.Session["Email"].ToString() +
"','" + HttpContext.Current.Session["UserName"] + "')", DataLinkCon.mgmuser);
}
catch (Exception ex)
{
DataLink.Update("insert into EMailPendingLog (Attempts,DateTimeAdded,DateTimeVerified,EmailTypeCode,ErrorText,QuoteEmailID,[Status],UserEmail,UserName) values (" +
"1,GetDate(),null,'Email Screen','" + ex.Message + "',-1,',0,'" + HttpContext.Current.Session["Email"].ToString() +
"','" + HttpContext.Current.Session["UserName"].ToString() + "')", DataLinkCon.mgmuser);
return(ex.Message + Environment.NewLine + "Failed to create log entry.");
}
}
}
}
return("");
}