public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, Sql.ToInteger(txtCASE_NUMBER.Text), "CASE_NUMBER", Sql.IsEmptyString(txtCASE_NUMBER.Text));
Sql.AppendParameter(cmd, txtNAME.Text, 255, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, txtACCOUNT_NAME.Text, 100, Sql.SqlFilterMode.StartsWith, "ACCOUNT_NAME");
Sql.AppendParameter(cmd, lstSTATUS.SelectedValue, 25, Sql.SqlFilterMode.Exact, "STATUS");
Sql.AppendParameter(cmd, lstPRIORITY.SelectedValue, 25, Sql.SqlFilterMode.Exact, "PRIORITY");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, txtNAME.Text, 255, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, lstSTATUS.SelectedValue, 25, Sql.SqlFilterMode.Exact, "STATUS");
Sql.AppendParameter(cmd, lstCAMPAIGN_TYPE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "CAMPAIGN_TYPE");
// 07/09/2006 Paul. Date is no longer converted in the DatePicker control, so convert it here to server time.
Sql.AppendParameter(cmd, T10n.ToServerTime(ctlSTART_DATE.Value), "START_DATE");
Sql.AppendParameter(cmd, T10n.ToServerTime(ctlEND_DATE.Value), "END_DATE");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public override void SqlSearchClause(IDbCommand cmd)
{
// 07/18/2006 Paul. SqlFilterMode.Contains behavior has be deprecated. It is now the same as SqlFilterMode.StartsWith.
Sql.AppendParameter(cmd, txtNAME.Text, 255, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, txtACCOUNT_NAME.Text, 100, Sql.SqlFilterMode.StartsWith, "ACCOUNT_NAME");
Sql.AppendParameter(cmd, lstSTATUS.SelectedValue, 25, Sql.SqlFilterMode.Exact, "STATUS");
// 07/09/2006 Paul. Date is no longer converted in the DatePicker control, so convert it here to server time.
Sql.AppendParameter(cmd, T10n.ToServerTime(ctlSTART_DATE.Value), "START_DATE");
Sql.AppendParameter(cmd, T10n.ToServerTime(ctlEND_DATE.Value), "END_DATE");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, Sql.ToInteger(txtBUG_NUMBER.Text), "BUG_NUMBER", Sql.IsEmptyString(txtBUG_NUMBER.Text));
Sql.AppendParameter(cmd, txtNAME.Text, 255, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, lstRESOLUTION.SelectedValue, 25, Sql.SqlFilterMode.Exact, "RESOLUTION");
Sql.AppendParameter(cmd, lstRELEASE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "RELEASE");
Sql.AppendParameter(cmd, lstSTATUS.SelectedValue, 25, Sql.SqlFilterMode.Exact, "STATUS");
Sql.AppendParameter(cmd, lstTYPE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "TYPE");
Sql.AppendParameter(cmd, lstPRIORITY.SelectedValue, 25, Sql.SqlFilterMode.Exact, "PRIORITY");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public static Stack FilterByACL_Stack(string sMODULE_NAME, string sACCESS_TYPE, string[] arrID, string sTABLE_NAME)
{
Stack stk = new Stack();
StringBuilder sb = new StringBuilder();
int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, sACCESS_TYPE);
if (nACLACCESS >= 0 && arrID.Length > 0)
{
if (nACLACCESS == ACL_ACCESS.OWNER)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
// 09/26/2006 Paul. The connection needed to be opened.
con.Open();
string sSQL;
sSQL = "select ID " + ControlChars.CrLf
+ " from vw" + sTABLE_NAME + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AppendGuids(cmd, arrID, "ID");
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
stk.Push(Sql.ToString(rdr["ID"]));
}
}
}
}
if (stk.Count == 0)
{
L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
}
}
else
{
foreach (string sID in arrID)
{
if (sID.Length > 0)
{
stk.Push(sID);
}
}
}
}
return(stk);
}
public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, Sql.ToInteger(txtQUOTE_NUM.Text), "QUOTE_NUM", Sql.IsEmptyString(txtQUOTE_NUM.Text));
Sql.AppendParameter(cmd, Sql.ToDecimal(txtTOTAL.Text), "TOTAL", Sql.IsEmptyString(txtTOTAL.Text));
// 07/18/2006 Paul. SqlFilterMode.Contains behavior has be deprecated. It is now the same as SqlFilterMode.StartsWith.
Sql.AppendParameter(cmd, txtNAME.Text, 50, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, txtACCOUNT_NAME.Text, 150, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ACCOUNT_NAME", "BILLING_ACCOUNT_NAME" });
Sql.AppendParameter(cmd, txtNEXT_STEP.Text, 255, Sql.SqlFilterMode.StartsWith, "NEXT_STEP");
Sql.AppendParameter(cmd, lstQUOTE_TYPE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "QUOTE_TYPE");
Sql.AppendParameter(cmd, lstLEAD_SOURCE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "LEAD_SOURCE");
Sql.AppendParameter(cmd, lstQUOTE_STAGE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "QUOTE_STAGE");
// 07/09/2006 Paul. Date is no longer converted in the DatePicker control, so convert it here to server time.
Sql.AppendParameter(cmd, T10n.ToServerTime(ctlDATE_QUOTE_EXPECTED_CLOSED.Value), "DATE_QUOTE_EXPECTED_CLOSED");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public override void SqlSearchClause(IDbCommand cmd)
{
// 09/13/2006 Paul. Change FIRST_NAME to NAME.
Sql.AppendParameter(cmd, txtNAME.Text, 25, Sql.SqlFilterMode.StartsWith, "NAME");
Sql.AppendParameter(cmd, Sql.ToDecimal(txtAMOUNT.Text), "AMOUNT", Sql.IsEmptyString(txtAMOUNT.Text));
Sql.AppendParameter(cmd, T10n.ToServerTime(Sql.ToDateTime(txtDATE_CLOSED.Text)), "DATE_CLOSED");
Sql.AppendParameter(cmd, txtNEXT_STEP.Text, 25, Sql.SqlFilterMode.StartsWith, "NEXT_STEP");
Sql.AppendParameter(cmd, txtACCOUNT_NAME.Text, 150, Sql.SqlFilterMode.StartsWith, "ACCOUNT_NAME");
// 09/01/2006 Paul. Add PROBABILITY.
Sql.AppendParameter(cmd, Sql.ToFloat(txtPROBABILITY.Text), "PROBABILITY", Sql.IsEmptyString(txtPROBABILITY.Text));
Sql.AppendParameter(cmd, lstOPPORTUNITY_TYPE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "OPPORTUNITY_TYPE");
Sql.AppendParameter(cmd, lstLEAD_SOURCE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "LEAD_SOURCE");
// 09/01/2006 Paul. Change STATUS to SALES_STAGE.
Sql.AppendParameter(cmd, lstSALES_STAGE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "SALES_STAGE");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public static string FilterByACL(string sMODULE_NAME, string sACCESS_TYPE, string[] arrID, string sTABLE_NAME)
{
StringBuilder sb = new StringBuilder();
int nACLACCESS = Security.GetUserAccess(sMODULE_NAME, sACCESS_TYPE);
if (nACLACCESS >= 0 && arrID.Length > 0)
{
if (nACLACCESS == ACL_ACCESS.OWNER)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select ID " + ControlChars.CrLf
+ " from vw" + sTABLE_NAME + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AppendGuids(cmd, arrID, "ID");
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
while (rdr.Read())
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(Sql.ToString(rdr["ID"]));
}
}
}
}
if (sb.Length == 0)
{
L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
throw(new Exception(L10n.Term("ACL.LBL_INSUFFICIENT_ACCESS")));
}
}
else
{
return(String.Join(",", arrID));
}
}
return(sb.ToString());
}
public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, txtFIRST_NAME.Text, 25, Sql.SqlFilterMode.StartsWith, "FIRST_NAME");
// 07/18/2006 Paul. SqlFilterMode.Contains behavior has be deprecated. It is now the same as SqlFilterMode.StartsWith.
Sql.AppendParameter(cmd, txtPHONE.Text, 25, Sql.SqlFilterMode.StartsWith, new string[] { "PHONE_HOME", "PHONE_MOBILE", "PHONE_WORK", "PHONE_OTHER", "PHONE_FAX", "ASSISTANT_PHONE" });
Sql.AppendParameter(cmd, txtLAST_NAME.Text, 25, Sql.SqlFilterMode.StartsWith, "LAST_NAME");
Sql.AppendParameter(cmd, txtEMAIL.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "EMAIL1", "EMAIL2" });
Sql.AppendParameter(cmd, txtASSISTANT.Text, 75, Sql.SqlFilterMode.StartsWith, "ASSISTANT");
Sql.AppendParameter(cmd, txtADDRESS_STREET.Text, 150, Sql.SqlFilterMode.StartsWith, new string[] { "PRIMARY_ADDRESS_STREET", "ALT_ADDRESS_STREET" });
Sql.AppendParameter(cmd, txtADDRESS_CITY.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "PRIMARY_ADDRESS_CITY", "ALT_ADDRESS_CITY" });
Sql.AppendParameter(cmd, txtADDRESS_STATE.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "PRIMARY_ADDRESS_STATE", "ALT_ADDRESS_STATE" });
Sql.AppendParameter(cmd, txtADDRESS_POSTALCODE.Text, 20, Sql.SqlFilterMode.StartsWith, new string[] { "PRIMARY_ADDRESS_POSTALCODE", "ALT_ADDRESS_POSTALCODE" });
Sql.AppendParameter(cmd, txtADDRESS_COUNTRY.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "PRIMARY_ADDRESS_COUNTRY", "ALT_ADDRESS_COUNTRY" });
Sql.AppendParameter(cmd, chkDO_NOT_CALL.Checked, "DO_NOT_CALL");
Sql.AppendParameter(cmd, chkEMAIL_OPT_OUT.Checked, "EMAIL_OPT_OUT");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
public override void SqlSearchClause(IDbCommand cmd)
{
Sql.AppendParameter(cmd, txtNAME.Text, 150, Sql.SqlFilterMode.StartsWith, "NAME");
// 07/18/2006 Paul. SqlFilterMode.Contains behavior has be deprecated. It is now the same as SqlFilterMode.StartsWith.
Sql.AppendParameter(cmd, txtPHONE.Text, 25, Sql.SqlFilterMode.StartsWith, new string[] { "PHONE_OFFICE", "PHONE_FAX", "PHONE_ALTERNATE" });
Sql.AppendParameter(cmd, txtWEBSITE.Text, 255, Sql.SqlFilterMode.StartsWith, "WEBSITE");
Sql.AppendParameter(cmd, txtEMAIL.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "EMAIL1", "EMAIL2" });
Sql.AppendParameter(cmd, txtANNUAL_REVENUE.Text, 25, Sql.SqlFilterMode.StartsWith, "ANNUAL_REVENUE");
Sql.AppendParameter(cmd, txtEMPLOYEES.Text, 10, Sql.SqlFilterMode.StartsWith, "EMPLOYEES");
Sql.AppendParameter(cmd, txtOWNERSHIP.Text, 100, Sql.SqlFilterMode.StartsWith, "OWNERSHIP");
Sql.AppendParameter(cmd, txtTICKER_SYMBOL.Text, 10, Sql.SqlFilterMode.StartsWith, "TICKER_SYMBOL");
Sql.AppendParameter(cmd, txtRATING.Text, 25, Sql.SqlFilterMode.StartsWith, "RATING");
Sql.AppendParameter(cmd, txtSIC_CODE.Text, 10, Sql.SqlFilterMode.StartsWith, "SIC_CODE");
Sql.AppendParameter(cmd, txtADDRESS_STREET.Text, 150, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ADDRESS_STREET", "BILLING_ADDRESS_STREET" });
Sql.AppendParameter(cmd, txtADDRESS_CITY.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ADDRESS_CITY", "BILLING_ADDRESS_CITY" });
Sql.AppendParameter(cmd, txtADDRESS_STATE.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ADDRESS_STATE", "BILLING_ADDRESS_STATE" });
Sql.AppendParameter(cmd, txtADDRESS_POSTALCODE.Text, 20, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ADDRESS_POSTALCODE", "BILLING_ADDRESS_POSTALCODE" });
Sql.AppendParameter(cmd, txtADDRESS_COUNTRY.Text, 100, Sql.SqlFilterMode.StartsWith, new string[] { "SHIPPING_ADDRESS_COUNTRY", "BILLING_ADDRESS_COUNTRY" });
Sql.AppendParameter(cmd, lstINDUSTRY.SelectedValue, 25, Sql.SqlFilterMode.Exact, "INDUSTRY");
Sql.AppendParameter(cmd, lstACCOUNT_TYPE.SelectedValue, 25, Sql.SqlFilterMode.Exact, "ACCOUNT_TYPE");
Sql.AppendGuids(cmd, lstASSIGNED_USER_ID, "ASSIGNED_USER_ID");
}
private void Page_Load(object sender, System.EventArgs e)
{
XmlDocument xml = new XmlDocument();
try
{
// 09/15/2005 Paul. Values will always be in the query string.
int nCHART_LENGTH = Sql.ToInteger(Request.QueryString["CHART_LENGTH"]);
int nYEAR = Sql.ToInteger(Request.QueryString["YEAR"]);
nYEAR = Math.Max(1900, nYEAR);
nYEAR = Math.Min(2100, nYEAR);
DateTime dtDATE_START = new DateTime(nYEAR, 1, 1);
DateTime dtDATE_END = new DateTime(nYEAR, 12, 31);
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrASSIGNED_USER_ID = Request.QueryString.GetValues("ASSIGNED_USER_ID");
xml.LoadXml(SplendidCache.XmlFile(Server.MapPath(Session["themeURL"] + "BarChart.xml")));
XmlNode nodeRoot = xml.SelectSingleNode("graphData");
XmlNode nodeXData = xml.CreateElement("xData");
XmlNode nodeYData = xml.CreateElement("yData");
XmlNode nodeColorLegend = xml.CreateElement("colorLegend");
XmlNode nodeGraphInfo = xml.CreateElement("graphInfo");
XmlNode nodeChartColors = nodeRoot.SelectSingleNode("chartColors");
nodeRoot.InsertBefore(nodeGraphInfo, nodeChartColors);
nodeRoot.InsertBefore(nodeColorLegend, nodeGraphInfo);
nodeRoot.InsertBefore(nodeYData, nodeColorLegend);
nodeRoot.InsertBefore(nodeXData, nodeYData);
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "length", "20");
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "min", "0");
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "max", "0");
if (nCHART_LENGTH < 4)
{
nCHART_LENGTH = 4;
}
else if (nCHART_LENGTH > 10)
{
nCHART_LENGTH = 10;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "length", nCHART_LENGTH.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "prefix", Sql.ToString(Session["USER_SETTINGS/CURRENCY_SYMBOL"]));
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "suffix", "");
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "defaultAltText", L10n.Term("Dashboard.LBL_ROLLOVER_DETAILS"));
nodeGraphInfo.InnerText = L10n.Term("Dashboard.LBL_DATE_RANGE") + " " + Sql.ToDateString(T10n.FromServerTime(dtDATE_START)) + " " + L10n.Term("Dashboard.LBL_DATE_RANGE_TO") + Sql.ToDateString(T10n.FromServerTime(dtDATE_END)) + "<BR/>"
+ L10n.Term("Dashboard.LBL_OPP_SIZE") + " " + Strings.FormatCurrency(1, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS");
Hashtable hashOUTCOME = new Hashtable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
// 09/19/2005 Paul. Prepopulate the outcome.
string[] arrOUTCOME = new string[] { "Closed Lost", "Closed Won", "Other" };
foreach (string sOUTCOME in arrOUTCOME)
{
if (!hashOUTCOME.ContainsKey(sOUTCOME))
{
XmlNode nodeMapping = xml.CreateElement("mapping");
nodeColorLegend.AppendChild(nodeMapping);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "id", sOUTCOME);
if (sOUTCOME == "Other")
{
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", L10n.Term("Dashboard.LBL_LEAD_SOURCE_OTHER"));
}
else
{
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", Sql.ToString(L10n.Term(".sales_stage_dom.", sOUTCOME)));
}
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "color", SplendidDefaults.generate_graphcolor(sOUTCOME, hashOUTCOME.Count));
hashOUTCOME.Add(sOUTCOME, sOUTCOME);
}
}
sSQL = "select SALES_STAGE " + ControlChars.CrLf
+ " , MONTH_CLOSED " + ControlChars.CrLf
+ " , sum(AMOUNT_USDOLLAR/1000) as TOTAL " + ControlChars.CrLf
+ " , count(*) as OPPORTUNITY_COUNT" + ControlChars.CrLf
+ " from vwOPPORTUNITIES_PipelineMonth " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "Opportunities", "list");
cmd.CommandText += " and DATE_CLOSED >= @DATE_START" + ControlChars.CrLf;
cmd.CommandText += " and DATE_CLOSED <= @DATE_END " + ControlChars.CrLf;
// 09/14/2005 Paul. Use add because <= and >= are not supported.
Sql.AddParameter(cmd, "@DATE_START", dtDATE_START);
Sql.AddParameter(cmd, "@DATE_END", dtDATE_END);
// 09/14/2005 Paul. Use append because it supports arrays using the IN clause.
Sql.AppendGuids(cmd, arrASSIGNED_USER_ID, "ASSIGNED_USER_ID");
cmd.CommandText += ""
+ " group by SALES_STAGE " + ControlChars.CrLf
+ " , MONTH_CLOSED " + ControlChars.CrLf
+ " order by MONTH_CLOSED, SALES_STAGE desc " + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader())
{
double dMAX_TOTAL = 0;
double dPIPELINE_TOTAL = 0;
string sMONTHYEAR_FORMAT = m_sDATEFORMAT;
// 09/21/2005 Paul. Remove day from format.
sMONTHYEAR_FORMAT = sMONTHYEAR_FORMAT.Replace("dd", "");
sMONTHYEAR_FORMAT = sMONTHYEAR_FORMAT.Replace("--", "-");
sMONTHYEAR_FORMAT = sMONTHYEAR_FORMAT.Replace("//", "/");
sMONTHYEAR_FORMAT = sMONTHYEAR_FORMAT.Replace(" ", " ");
while (rdr.Read())
{
int nMONTH_CLOSED = Sql.ToInteger(rdr["MONTH_CLOSED"]);
string sSALES_STAGE = Sql.ToString(rdr["SALES_STAGE"]);
double dTOTAL = Sql.ToDouble(rdr["TOTAL"]);
int nOPPORTUNITY_COUNT = Sql.ToInteger(rdr["OPPORTUNITY_COUNT"]);
DateTime dtMONTH_CLOSED = new DateTime(nYEAR, nMONTH_CLOSED, 1);
string sMONTH_CLOSED = dtMONTH_CLOSED.ToString(sMONTHYEAR_FORMAT);
dPIPELINE_TOTAL += dTOTAL;
if (dTOTAL > dMAX_TOTAL)
{
dMAX_TOTAL = dTOTAL;
}
XmlNode nodeRow = nodeXData.SelectSingleNode("dataRow[@title=\'" + L10n.Term(sMONTH_CLOSED).Replace("'", "\'") + "\']");
if (nodeRow == null)
{
nodeRow = xml.CreateElement("dataRow");
nodeXData.AppendChild(nodeRow);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", sMONTH_CLOSED);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dTOTAL.ToString("0"));
}
else
{
if (nodeRow.Attributes.GetNamedItem("endLabel") != null)
{
double dEND_LABEL = Sql.ToDouble(nodeRow.Attributes.GetNamedItem("endLabel").Value);
dEND_LABEL += dTOTAL;
if (dEND_LABEL > dMAX_TOTAL)
{
dMAX_TOTAL = dEND_LABEL;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dEND_LABEL.ToString("0"));
}
}
XmlNode nodeBar = xml.CreateElement("bar");
nodeRow.AppendChild(nodeBar);
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "id", sSALES_STAGE);
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "totalSize", dTOTAL.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "altText", sMONTH_CLOSED + ": " + nOPPORTUNITY_COUNT.ToString() + " " + L10n.Term("Dashboard.LBL_OPPS_WORTH") + " " + dTOTAL.ToString("0") + L10n.Term("Dashboard.LBL_OPP_THOUSANDS") + " " + L10n.Term("Dashboard.LBL_OPPS_OUTCOME") + " " + Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "url", Sql.ToString(Application["rootURL"]) + "Opportunities/default.aspx?DATE_CLOSED=" + Server.UrlEncode(Sql.ToDateString(T10n.FromServerTime(dtMONTH_CLOSED))) + "&SALES_STAGE=" + Server.UrlEncode(sSALES_STAGE));
}
int nNumLength = Math.Floor(dMAX_TOTAL).ToString("0").Length - 1;
double dWhole = Math.Pow(10, nNumLength);
double dDecimal = 1 / dWhole;
double dMAX_ROUNDED = Math.Ceiling(dMAX_TOTAL * dDecimal) * dWhole;
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "max", dMAX_ROUNDED.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeRoot, "title", L10n.Term("Dashboard.LBL_TOTAL_PIPELINE") + Strings.FormatCurrency(dPIPELINE_TOTAL, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS"));
}
}
}
Response.ContentType = "text/xml";
Response.Write(xml.OuterXml);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
Response.Write(ex.Message);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
XmlDocument xml = new XmlDocument();
try
{
// 09/15/2005 Paul. Values will always be in the query string.
int nCHART_LENGTH = Sql.ToInteger(Request.QueryString["CHART_LENGTH"]);
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrASSIGNED_USER_ID = Request.QueryString.GetValues("ASSIGNED_USER_ID");
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrLEAD_SOURCE = Request.QueryString.GetValues("LEAD_SOURCE");
xml.LoadXml(SplendidCache.XmlFile(Server.MapPath(Session["themeURL"] + "BarChart.xml")));
XmlNode nodeRoot = xml.SelectSingleNode("graphData");
XmlNode nodeXData = xml.CreateElement("xData");
XmlNode nodeYData = xml.CreateElement("yData");
XmlNode nodeColorLegend = xml.CreateElement("colorLegend");
XmlNode nodeGraphInfo = xml.CreateElement("graphInfo");
XmlNode nodeChartColors = nodeRoot.SelectSingleNode("chartColors");
nodeRoot.InsertBefore(nodeGraphInfo, nodeChartColors);
nodeRoot.InsertBefore(nodeColorLegend, nodeGraphInfo);
nodeRoot.InsertBefore(nodeXData, nodeColorLegend);
nodeRoot.InsertBefore(nodeYData, nodeXData);
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "defaultAltText", L10n.Term("Dashboard.LBL_ROLLOVER_DETAILS"));
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "min", "0");
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "max", "0");
if (nCHART_LENGTH < 4)
{
nCHART_LENGTH = 4;
}
else if (nCHART_LENGTH > 10)
{
nCHART_LENGTH = 10;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "length", nCHART_LENGTH.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "prefix", Sql.ToString(Session["USER_SETTINGS/CURRENCY_SYMBOL"]));
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "suffix", "");
nodeGraphInfo.InnerText = L10n.Term("Dashboard.LBL_OPP_SIZE") + " " + Strings.FormatCurrency(1, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS");
Hashtable hashOUTCOME = new Hashtable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
// 09/19/2005 Paul. Prepopulate the stage rows so that empty rows will appear. The SQL query will not return empty rows.
if (arrLEAD_SOURCE != null)
{
foreach (string sLEAD_SOURCE in arrLEAD_SOURCE)
{
XmlNode nodeRow = xml.CreateElement("dataRow");
nodeYData.AppendChild(nodeRow);
// 05/27/2007 Paul. LBL_NONE is --None--, so create a new term LBL_NONE_VALUE.
if (sLEAD_SOURCE == String.Empty)
{
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", Sql.ToString(L10n.Term(".LBL_NONE_VALUE")));
}
else
{
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", Sql.ToString(L10n.Term(".lead_source_dom.", sLEAD_SOURCE)));
}
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", "0");
}
}
// 09/19/2005 Paul. Prepopulate the outcome.
string[] arrOUTCOME = new string[] { "Closed Lost", "Closed Won", "Other" };
foreach (string sOUTCOME in arrOUTCOME)
{
if (!hashOUTCOME.ContainsKey(sOUTCOME))
{
XmlNode nodeMapping = xml.CreateElement("mapping");
nodeColorLegend.AppendChild(nodeMapping);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "id", sOUTCOME);
if (sOUTCOME == "Other")
{
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", L10n.Term("Dashboard.LBL_LEAD_SOURCE_OTHER"));
}
else
{
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", Sql.ToString(L10n.Term(".sales_stage_dom.", sOUTCOME)));
}
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "color", SplendidDefaults.generate_graphcolor(sOUTCOME, hashOUTCOME.Count));
hashOUTCOME.Add(sOUTCOME, sOUTCOME);
}
}
sSQL = "select LEAD_SOURCE " + ControlChars.CrLf
+ " , SALES_STAGE " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " , sum(AMOUNT_USDOLLAR/1000) as TOTAL " + ControlChars.CrLf
+ " , count(*) as OPPORTUNITY_COUNT" + ControlChars.CrLf
+ " from vwOPPORTUNITIES_ByLeadOutcome " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "Opportunities", "list");
// 09/14/2005 Paul. Use append because it supports arrays using the IN clause.
Sql.AppendGuids(cmd, arrASSIGNED_USER_ID, "ASSIGNED_USER_ID");
Sql.AppendParameter(cmd, arrLEAD_SOURCE, "LEAD_SOURCE");
#if false
if (arrLEAD_SOURCE != null)
{
nodeGraphInfo.InnerText = "LEAD_SOURCE = " + String.Join(", ", arrLEAD_SOURCE);
}
#endif
cmd.CommandText += ""
+ " group by LEAD_SOURCE " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " , SALES_STAGE " + ControlChars.CrLf
+ " order by LIST_ORDER " + ControlChars.CrLf
+ " , SALES_STAGE " + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader())
{
double dMAX_TOTAL = 0;
double dPIPELINE_TOTAL = 0;
while (rdr.Read())
{
string sLEAD_SOURCE = Sql.ToString(rdr["LEAD_SOURCE"]);
string sSALES_STAGE = Sql.ToString(rdr["SALES_STAGE"]);
double dTOTAL = Sql.ToDouble(rdr["TOTAL"]);
int nOPPORTUNITY_COUNT = Sql.ToInteger(rdr["OPPORTUNITY_COUNT"]);
dPIPELINE_TOTAL += dTOTAL;
if (dTOTAL > dMAX_TOTAL)
{
dMAX_TOTAL = dTOTAL;
}
// 05/27/2007 Paul. LBL_NONE is --None--, so create a new term LBL_NONE_VALUE.
string sLEAD_SOURCE_TERM = String.Empty;
if (sLEAD_SOURCE == String.Empty)
{
sLEAD_SOURCE_TERM = L10n.Term(".LBL_NONE_VALUE");
}
else
{
sLEAD_SOURCE_TERM = Sql.ToString(L10n.Term(".lead_source_dom.", sLEAD_SOURCE));
}
XmlNode nodeRow = nodeYData.SelectSingleNode("dataRow[@title=\'" + sLEAD_SOURCE_TERM.Replace("'", "\'") + "\']");
if (nodeRow == null)
{
nodeRow = xml.CreateElement("dataRow");
nodeYData.AppendChild(nodeRow);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", sLEAD_SOURCE_TERM);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dTOTAL.ToString("0"));
}
else
{
if (nodeRow.Attributes.GetNamedItem("endLabel") != null)
{
double dEND_LABEL = Sql.ToDouble(nodeRow.Attributes.GetNamedItem("endLabel").Value);
dEND_LABEL += dTOTAL;
if (dEND_LABEL > dMAX_TOTAL)
{
dMAX_TOTAL = dEND_LABEL;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dEND_LABEL.ToString("0"));
}
}
XmlNode nodeBar = xml.CreateElement("bar");
nodeRow.AppendChild(nodeBar);
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "id", sSALES_STAGE);
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "totalSize", dTOTAL.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "altText", nOPPORTUNITY_COUNT.ToString() + " " + L10n.Term("Dashboard.LBL_OPPS_WORTH") + " " + dTOTAL.ToString("0") + L10n.Term("Dashboard.LBL_OPP_THOUSANDS") + " " + L10n.Term("Dashboard.LBL_OPPS_OUTCOME") + " " + Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "url", Sql.ToString(Application["rootURL"]) + "Opportunities/default.aspx?LEAD_SOURCE=" + Server.UrlEncode(sLEAD_SOURCE) + "&SALES_STAGE=" + Server.UrlEncode(sSALES_STAGE));
}
int nNumLength = Math.Floor(dMAX_TOTAL).ToString("0").Length - 1;
double dWhole = Math.Pow(10, nNumLength);
double dDecimal = 1 / dWhole;
double dMAX_ROUNDED = Math.Ceiling(dMAX_TOTAL * dDecimal) * dWhole;
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "max", dMAX_ROUNDED.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeRoot, "title", L10n.Term("Dashboard.LBL_TOTAL_PIPELINE") + Strings.FormatCurrency(dPIPELINE_TOTAL, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS"));
}
}
}
Response.ContentType = "text/xml";
Response.Write(xml.OuterXml);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
Response.Write(ex.Message);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
XmlDocument xml = new XmlDocument();
try
{
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrASSIGNED_USER_ID = Request.QueryString.GetValues("ASSIGNED_USER_ID");
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrLEAD_SOURCE = Request.QueryString.GetValues("LEAD_SOURCE");
xml.LoadXml(SplendidCache.XmlFile(Server.MapPath(Session["themeURL"] + "PieChart.xml")));
XmlNode nodeRoot = xml.SelectSingleNode("graphData");
XmlNode nodePie = xml.CreateElement("pie");
XmlNode nodeGraphInfo = xml.CreateElement("graphInfo");
XmlNode nodeChartColors = nodeRoot.SelectSingleNode("chartColors");
nodeRoot.InsertBefore(nodeGraphInfo, nodeChartColors);
nodeRoot.InsertBefore(nodePie, nodeGraphInfo);
XmlUtil.SetSingleNodeAttribute(xml, nodePie, "defaultAltText", L10n.Term("Dashboard.LBL_ROLLOVER_WEDGE_DETAILS"));
Hashtable hashLEAD_SOURCE = new Hashtable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
sSQL = "select LEAD_SOURCE " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " , sum(AMOUNT_USDOLLAR/1000) as TOTAL " + ControlChars.CrLf
+ " , count(*) as OPPORTUNITY_COUNT" + ControlChars.CrLf
+ " from vwOPPORTUNITIES_ByLeadSource " + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 09/14/2005 Paul. Use append because it supports arrays using the IN clause.
Sql.AppendGuids(cmd, arrASSIGNED_USER_ID, "ASSIGNED_USER_ID");
Sql.AppendParameter(cmd, arrLEAD_SOURCE, "LEAD_SOURCE");
#if false
if (arrLEAD_SOURCE != null)
{
nodeGraphInfo.InnerText = "LEAD_SOURCE = " + String.Join(", ", arrLEAD_SOURCE);
}
#endif
cmd.CommandText += ""
+ " group by LEAD_SOURCE " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " order by LIST_ORDER " + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader())
{
double dMAX_TOTAL = 0;
double dPIPELINE_TOTAL = 0;
while (rdr.Read())
{
string sLEAD_SOURCE = Sql.ToString(rdr["LEAD_SOURCE"]);
double dTOTAL = Sql.ToDouble(rdr["TOTAL"]);
int nOPPORTUNITY_COUNT = Sql.ToInteger(rdr["OPPORTUNITY_COUNT"]);
dPIPELINE_TOTAL += dTOTAL;
if (dTOTAL > dMAX_TOTAL)
{
dMAX_TOTAL = dTOTAL;
}
if (sLEAD_SOURCE == String.Empty)
{
sLEAD_SOURCE = "None";
}
XmlNode nodeWedge = xml.CreateElement("bar");
nodePie.AppendChild(nodeWedge);
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "title", Sql.ToString(L10n.Term(".lead_source_dom.", sLEAD_SOURCE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "value", dTOTAL.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "color", SplendidDefaults.generate_graphcolor(sLEAD_SOURCE, hashLEAD_SOURCE.Count));
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "labelText", Strings.FormatCurrency(dTOTAL, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault));
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "url", Sql.ToString(Application["rootURL"]) + "Opportunities/default.aspx?LEAD_SOURCE=" + Server.UrlEncode(sLEAD_SOURCE));
XmlUtil.SetSingleNodeAttribute(xml, nodeWedge, "altText", nOPPORTUNITY_COUNT.ToString() + " " + L10n.Term("Dashboard.LBL_OPPS_IN_LEAD_SOURCE") + " " + Sql.ToString(L10n.Term(".lead_source_dom.", sLEAD_SOURCE)));
hashLEAD_SOURCE.Add(sLEAD_SOURCE, sLEAD_SOURCE);
}
XmlUtil.SetSingleNodeAttribute(xml, nodeRoot, "title", L10n.Term("Dashboard.LBL_TOTAL_PIPELINE") + Strings.FormatCurrency(dPIPELINE_TOTAL, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS"));
XmlUtil.SetSingleNodeAttribute(xml, nodeRoot, "subtitle", L10n.Term("Dashboard.LBL_OPP_SIZE") + " " + Strings.FormatCurrency(1.0, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS"));
}
}
}
Response.ContentType = "text/xml";
Response.Write(xml.OuterXml);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
Response.Write(ex.Message);
}
}
protected void BindGrid()
{
plcWeekRows.Controls.Clear();
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
DataTable dtUsers = new DataTable();
DateTime dtDATE_START = new DateTime(Math.Max(1753, dtCurrentWeek.Year), dtCurrentWeek.Month, dtCurrentWeek.Day, 0, 0, 0);
DateTime dtDATE_END = dtDATE_START.AddDays(7);
sSQL = "select distinct " + ControlChars.CrLf
+ " ASSIGNED_USER_ID " + ControlChars.CrLf
+ " , ASSIGNED_FULL_NAME " + ControlChars.CrLf
+ " from vwACTIVITIES_List " + ControlChars.CrLf
+ " where ( DATE_START >= @DATE_START and DATE_START < @DATE_END" + ControlChars.CrLf
+ " or DATE_END >= @DATE_START and DATE_END < @DATE_END" + ControlChars.CrLf
+ " or DATE_START < @DATE_START and DATE_END > @DATE_END" + ControlChars.CrLf
+ " ) " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@DATE_START", dtDATE_START);
Sql.AddParameter(cmd, "@DATE_END", dtDATE_END);
Sql.AppendGuids(cmd, lstUSERS, "ASSIGNED_USER_ID");
cmd.CommandText += " order by ASSIGNED_FULL_NAME" + ControlChars.CrLf;
#if DEBUG
Page.RegisterClientScriptBlock("vwACTIVITIES_List.Users", Sql.ClientScriptBlock(cmd));
#endif
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtUsers);
}
}
sSQL = "select * " + ControlChars.CrLf
+ " from vwACTIVITIES_List " + ControlChars.CrLf
+ " where ( DATE_START >= @DATE_START and DATE_START < @DATE_END" + ControlChars.CrLf
+ " or DATE_END >= @DATE_START and DATE_END < @DATE_END" + ControlChars.CrLf
+ " or DATE_START < @DATE_START and DATE_END > @DATE_END" + ControlChars.CrLf
+ " ) " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@DATE_START", dtDATE_START);
Sql.AddParameter(cmd, "@DATE_END", dtDATE_END);
Sql.AppendGuids(cmd, lstUSERS, "ASSIGNED_USER_ID");
cmd.CommandText += " order by ASSIGNED_FULL_NAME asc, DATE_START asc, NAME asc" + ControlChars.CrLf;
#if DEBUG
Page.RegisterClientScriptBlock("vwACTIVITIES_List.Data", Sql.ClientScriptBlock(cmd));
#endif
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 07/24/2005 Paul. Since this is not a dynamic grid, we must convert the status manually.
foreach (DataRow row in dt.Rows)
{
switch (Sql.ToString(row["ACTIVITY_TYPE"]))
{
case "Calls": row["STATUS"] = L10n.Term("Call") + " " + L10n.Term(".call_status_dom.", row["STATUS"]); break;
case "Meetings": row["STATUS"] = L10n.Term("Meeting") + " " + L10n.Term(".meeting_status_dom.", row["STATUS"]); break;
}
}
foreach (DataRow rowUser in dtUsers.Rows)
{
Guid gASSIGNED_USER_ID = Sql.ToGuid(rowUser["ASSIGNED_USER_ID"]);
string sASSIGNED_FULL_NAME = Sql.ToString(rowUser["ASSIGNED_FULL_NAME"]);
HtmlGenericControl h5User = new HtmlGenericControl("h5");
h5User.Attributes.Add("class", "calSharedUser");
h5User.Controls.Add(new LiteralControl(sASSIGNED_FULL_NAME));
plcWeekRows.Controls.Add(h5User);
HtmlTable tblUserWeek = new HtmlTable();
plcWeekRows.Controls.Add(tblUserWeek);
tblUserWeek.Border = 0;
tblUserWeek.CellPadding = 0;
tblUserWeek.CellSpacing = 1;
tblUserWeek.Width = "100%";
HtmlTableRow tr = new HtmlTableRow();
tblUserWeek.Rows.Add(tr);
CultureInfo ciEnglish = CultureInfo.CreateSpecificCulture("en-US");
for (int iDay = 0; iDay < 7; iDay++)
{
DataView vwMain = new DataView(dt);
DateTime dtDAY_START = dtCurrentWeek;
dtDAY_START = dtDAY_START.AddDays(iDay);
DateTime dtDAY_END = dtDAY_START.AddDays(1);
HtmlTableCell cell = new HtmlTableCell();
tr.Cells.Add(cell);
cell.Width = "14%";
cell.VAlign = "top";
cell.Attributes.Add("class", "dailyCalBodyItems");
cell.Controls.Add(new LiteralControl(dtDAY_START.ToString("ddd d")));
// 09/27/2005 Paul. System.Data.DataColumn.Expression documentation has description how to define dates and strings.
// 01/21/2006 Paul. Brazilian culture is having a problem with date formats. Try using the european format.
// 06/13/2006 Paul. Italian has a problem with the time separator. Use the value from the culture from CalendarControl.SqlDateTimeFormat.
// 06/14/2006 Paul. The Italian problem was that it was using the culture separator, but DataView only supports the en-US format.
string sDAY_START = dtDAY_START.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
string sDAY_END = dtDAY_END.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
vwMain.RowFilter = "ASSIGNED_USER_ID = '" + gASSIGNED_USER_ID.ToString() + "'" + ControlChars.CrLf
+ "and ( DATE_START >= #" + sDAY_START + "# and DATE_START < #" + sDAY_END + "#" + ControlChars.CrLf
+ " or DATE_END >= #" + sDAY_START + "# and DATE_END <= #" + sDAY_END + "#" + ControlChars.CrLf
+ " or DATE_START < #" + sDAY_START + "# and DATE_END > #" + sDAY_END + "#" + ControlChars.CrLf
+ " )" + ControlChars.CrLf;
#if DEBUG
// Page.RegisterClientScriptBlock("vwACTIVITIES_List" + dtDAY_START.ToOADate().ToString(), Sql.EscapeJavaScript(vwMain.RowFilter));
#endif
if (vwMain.Count > 0)
{
SharedCell ctlSharedCell = LoadControl("SharedCell.ascx") as SharedCell;
ctlSharedCell.DataSource = vwMain;
cell.Controls.Add(ctlSharedCell);
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
XmlDocument xml = new XmlDocument();
try
{
// 09/15/2005 Paul. Values will always be in the query string.
int nCHART_LENGTH = Sql.ToInteger(Request.QueryString["CHART_LENGTH"]);
DateTime dtDATE_START = T10n.ToServerTime(Sql.ToDateTime(Request.QueryString["DATE_START"]));
DateTime dtDATE_END = T10n.ToServerTime(Sql.ToDateTime(Request.QueryString["DATE_END"]));
if (dtDATE_START == DateTime.MinValue)
{
// 09/14/2005 Paul. SugarCRM uses a max date of 01/01/2100.
dtDATE_START = DateTime.Today;
}
if (dtDATE_END == DateTime.MinValue)
{
// 09/14/2005 Paul. SugarCRM uses a max date of 01/01/2100.
dtDATE_END = new DateTime(2100, 1, 1);
}
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrASSIGNED_USER_ID = Request.QueryString.GetValues("ASSIGNED_USER_ID");
// 09/15/2005 Paul. Values will always be in the query string.
string[] arrSALES_STAGE = Request.QueryString.GetValues("SALES_STAGE");
xml.LoadXml(SplendidCache.XmlFile(Server.MapPath(Session["themeURL"] + "BarChart.xml")));
XmlNode nodeRoot = xml.SelectSingleNode("graphData");
XmlNode nodeXData = xml.CreateElement("xData");
XmlNode nodeYData = xml.CreateElement("yData");
XmlNode nodeColorLegend = xml.CreateElement("colorLegend");
XmlNode nodeGraphInfo = xml.CreateElement("graphInfo");
XmlNode nodeChartColors = nodeRoot.SelectSingleNode("chartColors");
nodeRoot.InsertBefore(nodeGraphInfo, nodeChartColors);
nodeRoot.InsertBefore(nodeColorLegend, nodeGraphInfo);
nodeRoot.InsertBefore(nodeXData, nodeColorLegend);
nodeRoot.InsertBefore(nodeYData, nodeXData);
XmlUtil.SetSingleNodeAttribute(xml, nodeYData, "defaultAltText", L10n.Term("Dashboard.LBL_ROLLOVER_DETAILS"));
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "min", "0");
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "max", "0");
if (nCHART_LENGTH < 4)
{
nCHART_LENGTH = 4;
}
else if (nCHART_LENGTH > 10)
{
nCHART_LENGTH = 10;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "length", nCHART_LENGTH.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "prefix", Sql.ToString(Session["USER_SETTINGS/CURRENCY_SYMBOL"]));
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "suffix", "");
nodeGraphInfo.InnerText = L10n.Term("Dashboard.LBL_DATE_RANGE") + " " + Sql.ToDateString(T10n.FromServerTime(dtDATE_START)) + " " + L10n.Term("Dashboard.LBL_DATE_RANGE_TO") + Sql.ToDateString(T10n.FromServerTime(dtDATE_END)) + "<BR/>"
+ L10n.Term("Dashboard.LBL_OPP_SIZE") + " " + Strings.FormatCurrency(1, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS");
Hashtable hashUSER = new Hashtable();
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
// 09/19/2005 Paul. Prepopulate the stage rows so that empty rows will appear. The SQL query will not return empty rows.
if (arrSALES_STAGE != null)
{
foreach (string sSALES_STAGE in arrSALES_STAGE)
{
XmlNode nodeRow = xml.CreateElement("dataRow");
nodeYData.AppendChild(nodeRow);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", "0");
}
}
// 09/19/2005 Paul. Prepopulate the user key with all the users specified.
if (arrASSIGNED_USER_ID != null)
{
sSQL = "select ID " + ControlChars.CrLf
+ " , USER_NAME " + ControlChars.CrLf
+ " from vwUSERS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AppendGuids(cmd, arrASSIGNED_USER_ID, "ID");
cmd.CommandText += " order by USER_NAME" + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Guid gUSER_ID = Sql.ToGuid(rdr["ID"]);
string sUSER_NAME = Sql.ToString(rdr["USER_NAME"]);
if (!hashUSER.ContainsKey(gUSER_ID.ToString()))
{
XmlNode nodeMapping = xml.CreateElement("mapping");
nodeColorLegend.AppendChild(nodeMapping);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "id", gUSER_ID.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", sUSER_NAME);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "color", SplendidDefaults.generate_graphcolor(gUSER_ID.ToString(), hashUSER.Count));
hashUSER.Add(gUSER_ID.ToString(), sUSER_NAME);
}
}
}
}
}
sSQL = "select SALES_STAGE " + ControlChars.CrLf
+ " , ASSIGNED_USER_ID " + ControlChars.CrLf
+ " , USER_NAME " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " , sum(AMOUNT_USDOLLAR/1000) as TOTAL " + ControlChars.CrLf
+ " , count(*) as OPPORTUNITY_COUNT" + ControlChars.CrLf
+ " from vwOPPORTUNITIES_Pipeline " + ControlChars.CrLf
+ " where DATE_CLOSED >= @DATE_START " + ControlChars.CrLf
+ " and DATE_CLOSED <= @DATE_END " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 09/14/2005 Paul. Use add because <= and >= are not supported.
Sql.AddParameter(cmd, "@DATE_START", dtDATE_START);
Sql.AddParameter(cmd, "@DATE_END", dtDATE_END);
// 09/14/2005 Paul. Use append because it supports arrays using the IN clause.
Sql.AppendGuids(cmd, arrASSIGNED_USER_ID, "ASSIGNED_USER_ID");
Sql.AppendParameter(cmd, arrSALES_STAGE, "SALES_STAGE");
#if false
if (arrSALES_STAGE != null)
{
nodeGraphInfo.InnerText = "SALES_STAGE = " + String.Join(", ", arrSALES_STAGE);
}
#endif
cmd.CommandText += ""
+ " group by SALES_STAGE " + ControlChars.CrLf
+ " , LIST_ORDER " + ControlChars.CrLf
+ " , ASSIGNED_USER_ID " + ControlChars.CrLf
+ " , USER_NAME " + ControlChars.CrLf
+ " order by LIST_ORDER " + ControlChars.CrLf
+ " , USER_NAME " + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader())
{
double dMAX_TOTAL = 0;
double dPIPELINE_TOTAL = 0;
while (rdr.Read())
{
string sSALES_STAGE = Sql.ToString(rdr["SALES_STAGE"]);
double dTOTAL = Sql.ToDouble(rdr["TOTAL"]);
int nOPPORTUNITY_COUNT = Sql.ToInteger(rdr["OPPORTUNITY_COUNT"]);
Guid gASSIGNED_USER_ID = Sql.ToGuid(rdr["ASSIGNED_USER_ID"]);
string sUSER_NAME = Sql.ToString(rdr["USER_NAME"]);
dPIPELINE_TOTAL += dTOTAL;
if (dTOTAL > dMAX_TOTAL)
{
dMAX_TOTAL = dTOTAL;
}
XmlNode nodeRow = nodeYData.SelectSingleNode("dataRow[@title=\'" + Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)).Replace("'", "\'") + "\']");
if (nodeRow == null)
{
nodeRow = xml.CreateElement("dataRow");
nodeYData.AppendChild(nodeRow);
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "title", Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dTOTAL.ToString("0"));
}
else
{
if (nodeRow.Attributes.GetNamedItem("endLabel") != null)
{
double dEND_LABEL = Sql.ToDouble(nodeRow.Attributes.GetNamedItem("endLabel").Value);
dEND_LABEL += dTOTAL;
if (dEND_LABEL > dMAX_TOTAL)
{
dMAX_TOTAL = dEND_LABEL;
}
XmlUtil.SetSingleNodeAttribute(xml, nodeRow, "endLabel", dEND_LABEL.ToString("0"));
}
}
if (!hashUSER.ContainsKey(gASSIGNED_USER_ID.ToString()))
{
XmlNode nodeMapping = xml.CreateElement("mapping");
nodeColorLegend.AppendChild(nodeMapping);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "id", gASSIGNED_USER_ID.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "name", sUSER_NAME);
XmlUtil.SetSingleNodeAttribute(xml, nodeMapping, "color", SplendidDefaults.generate_graphcolor(gASSIGNED_USER_ID.ToString(), hashUSER.Count));
hashUSER.Add(gASSIGNED_USER_ID.ToString(), sUSER_NAME);
}
XmlNode nodeBar = xml.CreateElement("bar");
nodeRow.AppendChild(nodeBar);
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "id", gASSIGNED_USER_ID.ToString());
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "totalSize", dTOTAL.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "altText", sUSER_NAME + ": " + nOPPORTUNITY_COUNT.ToString() + " " + L10n.Term("Dashboard.LBL_OPPS_WORTH") + " " + dTOTAL.ToString("0") + L10n.Term("Dashboard.LBL_OPP_THOUSANDS") + " " + L10n.Term("Dashboard.LBL_OPPS_IN_STAGE") + " " + Sql.ToString(L10n.Term(".sales_stage_dom.", sSALES_STAGE)));
XmlUtil.SetSingleNodeAttribute(xml, nodeBar, "url", Sql.ToString(Application["rootURL"]) + "Opportunities/default.aspx?SALES_STAGE=" + Server.UrlEncode(sSALES_STAGE) + "&ASSIGNED_USER_ID=" + gASSIGNED_USER_ID.ToString());
}
int nNumLength = Math.Floor(dMAX_TOTAL).ToString("0").Length - 1;
double dWhole = Math.Pow(10, nNumLength);
double dDecimal = 1 / dWhole;
double dMAX_ROUNDED = Math.Ceiling(dMAX_TOTAL * dDecimal) * dWhole;
XmlUtil.SetSingleNodeAttribute(xml, nodeXData, "max", dMAX_ROUNDED.ToString("0"));
XmlUtil.SetSingleNodeAttribute(xml, nodeRoot, "title", L10n.Term("Dashboard.LBL_TOTAL_PIPELINE") + Strings.FormatCurrency(dPIPELINE_TOTAL, 0, TriState.UseDefault, TriState.UseDefault, TriState.UseDefault) + L10n.Term("Dashboard.LBL_OPP_THOUSANDS"));
}
}
}
Response.ContentType = "text/xml";
Response.Write(xml.OuterXml);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
Response.Write(ex.Message);
}
}
