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); } }