Example #1
0
        private void GetData(bool ReloadFields)
        {
            try
            {
                if (ddlDataSource.EditValue.ToString() != "")
                {
                    using (new CDS.Client.Desktop.Essential.UTL.WaitCursor())
                    {
                        SetWaitFormDescription("Retrieving Data");
                        string datefilter = "";
                        if (((DB.VW_AnalyticSource)ddlDataSource.GetSelectedDataRow()).DateFilter)
                        {
                            if (chkDateDays.Checked)
                            {
                                datefilter = String.Format("where Date BETWEEN '{0}' AND '{1}'", ((DateTime)dtDateStart.EditValue).ToString("yyyy-MM-dd HH:mm:ss.fff"), ((DateTime)dtDateEnd.EditValue).ToString("yyyy-MM-dd 23:59:59"));
                            }
                            else if (chkDatePeriod.Checked)
                            {
                                datefilter = String.Format("where FinancialPeriod IN ('{0}')", ddlDatePeriodSelect.Properties.GetCheckedItems().ToString().Replace(", ", "','"));
                            }
                            else if (chkDateQuarter.Checked && (ddlDateQuarterSelect.Properties.GetCheckedItems().ToString().Length > 0))
                            {
                                datefilter = String.Format("where CAST(FinancialYear AS NVARCHAR(4))+' '+CAST(FinancialQuarter AS NVARCHAR(1)) IN ('{0}')", ddlDateQuarterSelect.Properties.GetCheckedItems().ToString().Replace(", ", "','"));
                            }
                            else if (chkDateYear.Checked && (ddlDateYearSelect.Properties.GetCheckedItems().ToString().Length > 0))
                            {
                                datefilter = String.Format("where FinancialYear IN ({0})", ddlDateYearSelect.Properties.GetCheckedItems());
                            }
                        }
                        string query = "";

                        if (((DB.VW_AnalyticSource)ddlDataSource.GetSelectedDataRow()).DateFilter)
                        {
                            query = String.Format("select {0} * from {1} {2}", ReloadFields ? "TOP 1" : "", ((DB.VW_AnalyticSource)ddlDataSource.GetSelectedDataRow()).Source, datefilter);
                        }
                        else
                        {
                            query = String.Format("select {0} * from {1}", ReloadFields ? "TOP 1" : "", ((DB.VW_AnalyticSource)ddlDataSource.GetSelectedDataRow()).Source);
                        }

                        //switch (ddlDataSource.EditValue.ToString())
                        //{
                        //    case "Sales Analysis":
                        //        query = String.Format("select {1} * from VW_Sales where {0}", datefilter, ReloadFields ? "TOP 1" : "");
                        //        break;
                        //    case "Purchases Analysis":
                        //        query = String.Format("select {1} * from VW_Purchases where {0}", datefilter, ReloadFields ? "TOP 1" : "");
                        //        break;
                        //    case "Inventory Analysis":
                        //        query = String.Format("select {1} * from VW_Inventory", datefilter, ReloadFields ? "TOP 1" : "");
                        //        break;
                        //    case "Finance Analysis":
                        //        query = String.Format("select {1} * from VW_Finance where {0}", datefilter, ReloadFields ? "TOP 1" : "");
                        //        break;
                        //    case "Workshop Analysis":
                        //        query = String.Format("select {1} * from VW_Workshop", datefilter, ReloadFields ? "TOP 1" : "");
                        //        break;
                        //}

                        try
                        {
                            DataTable data = new DataTable();
                            adapter = BL.ApplicationDataContext.Instance.GetAdapter(query);
                            adapter.Fill(data);
                            adapter.Dispose();
                            BindingSourcePivot.DataSource = data;
                            System.IO.MemoryStream layoutStream = new System.IO.MemoryStream();
                            Pivot.SaveLayoutToStream(layoutStream);
                            List <PivotGridField> calculatedFields = new List <PivotGridField>();
                            List <DevExpress.Utils.FormatInfo> calculatedFieldsFormat = new List <DevExpress.Utils.FormatInfo>();

                            foreach (PivotGridField field in Pivot.Fields)
                            {
                                if (field.UnboundExpression != String.Empty)
                                {
                                    calculatedFields.Add(field);
                                    calculatedFieldsFormat.Add(field.CellFormat);
                                }
                            }

                            //if (ReloadFields)
                            {
                                Pivot.RetrieveFields(PivotArea.DataArea, false);
                                foreach (PivotGridField f in Pivot.Fields)
                                {
                                    if (f.Caption.EndsWith("Id"))
                                    {
                                        f.Options.ShowInCustomizationForm = false;
                                    }

                                    f.Options.AllowRunTimeSummaryChange = true;
                                    f.Options.ShowSummaryTypeName       = true;
                                }
                                layoutStream.Seek(0, System.IO.SeekOrigin.Begin);
                                Pivot.RestoreLayoutFromStream(layoutStream);
                            }

                            Pivot.BestFitRowArea(); Pivot.BestFitDataHeaders(true);

                            //Pivot.Fields.AddRange(calculatedFields.ToArray());

                            for (int i = 0; i < calculatedFields.Count; i++)
                            {
                                PivotGridField field    = calculatedFields[i];
                                PivotGridField newField = new DevExpress.XtraPivotGrid.PivotGridField();
                                BL.ApplicationDataContext.DeepClone(field, newField);
                                newField.Options.ShowUnboundExpressionMenu = true;
                                newField.UnboundType = UnboundColumnType.Decimal;
                                Pivot.Fields.Add(newField);
                            }
                        }
                        catch (SqlException ex)
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
                {
                    throw ex;
                }
            }
        }