예제 #1
0
        protected void Page_Load(object sender, EventArgs e)
        {
            QueryBuilderManager qb = new QueryBuilderManager();

            Trace.Warn("pageload", "pageload");

            if (Session["report"] != null)
            {
                ArrayList re = new ArrayList();
                re = (ArrayList)Session["report"];

                foreach (object re1 in re)
                {
                    CompanyReport cr = new CompanyReport();
                    cr = (CompanyReport)re1;
                    string    qbdesc = String.Empty;
                    DataTable d      = qb.QBManager(cr.idfield, cr.Params);

                    qbdesc = DatabaseConnection.SqlScalar("SELECT DESCRIPTION FROM QB_CUSTOMERQUERY WHERE ID=" + cr.idfield);

                    if (d.Rows.Count > 0)
                    {
                        switch (cr.Type)
                        {
                        case 0:
                            htmlcontact(d, cr.Finalize, cr.itemPage, qbdesc, cr.morerecord);
                            break;

                        case 1:
                            HtmlList(d, cr.Finalize, qbdesc, cr.morerecord);
                            break;
                        }
                    }
                    else
                    {
                        report = "No data";
                    }

                    Session["report"] = null;
                }


                Response.Write("<body onload=\"printpage();\">");
                Response.Write(report);
                Response.Write("</body>");
            }
        }
예제 #2
0
        private void FillResult()
        {
            bool   ViewOn    = (RadioResult.SelectedValue == "HTML");
            string delimiter = String.Empty;

            if (!ViewOn)
            {
                HttpContext.Current.Items.Add("render", "no");
            }

            QueryBuilderManager qb     = new QueryBuilderManager();
            Hashtable           Params = new Hashtable();
            string sqlHash             = "SELECT QB_CUSTOMERQUERYPARAMFIELDS.IDFIELD,QB_ALL_FIELDS.FIELD,QB_ALL_FIELDS.FIELDTYPE,QB_CUSTOMERQUERYPARAMFIELDS.IDTABLE " +
                                         "FROM QB_CUSTOMERQUERYPARAMFIELDS " +
                                         "INNER JOIN QB_ALL_FIELDS ON QB_CUSTOMERQUERYPARAMFIELDS.IDFIELD = QB_ALL_FIELDS.ID " +
                                         "WHERE QB_CUSTOMERQUERYPARAMFIELDS.IDQUERY=" + int.Parse(Session["QueryID"].ToString());
            DataSet dsQB = DatabaseConnection.CreateDataset(sqlHash);

            foreach (DataRow s in dsQB.Tables[0].Rows)
            {
                if ((int)s[3] > 0)
                {
                    switch (s[2].ToString())
                    {
                    case "8":
                        Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()] + "|" + Request.Form["Param2_" + s[0].ToString()]);
                        break;

                    case "12":
                        Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()] + "|" + Request.Form["Paramt_" + s[0].ToString()]);
                        break;

                    case "1":
                    case "13":
                    case "2":
                        if (Request.Form["Param_" + s[0].ToString()].Length > 0)
                        {
                            if (Request.Form["Param_" + s[0].ToString()].IndexOf("-1") < 0)
                            {
                                Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
                            }
                        }
                        else
                        {
                            if (Request.Form["SParam_" + s[0].ToString()].IndexOf("-1") < 0)
                            {
                                Params.Add(s[1].ToString().ToLower(), Request.Form["SParam_" + s[0].ToString()]);
                            }
                        }

                        break;

                    case "4":
                        Params.Add(s[1].ToString().ToLower(), (Request.Form["Param_" + s[0].ToString()] != null) ? Request.Form["SParam_-" + s[0].ToString()] : "false");
                        break;

                    case "14":
                        Params.Add(s[1].ToString().ToLower(), Request.Form["Paramt_" + s[0].ToString()]);
                        break;

                    default:
                        Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
                        break;
                    }
                    Trace.Warn("params-" + s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
                }
                else
                {
                    DataRow freeParameter = DatabaseConnection.CreateDataset("SELECT * FROM ADDEDFIELDS WHERE ID=" + s[0].ToString()).Tables[0].Rows[0];
                    switch (freeParameter["Type"].ToString())
                    {
                    case "4":
                        Params.Add("-" + freeParameter["Name"].ToString(), (Request.Form["SParam_-" + s[0].ToString()] != null) ? Request.Form["SParam_-" + s[0].ToString()] : "false");
                        break;

                    case "1":
                    case "2":
                        Params.Add("-" + freeParameter["Name"].ToString(), Request.Form["Param_-" + s[0].ToString()]);
                        break;
                    }
                    Trace.Warn("params-" + "-" + freeParameter["Name"].ToString(), Request.Form["Param_" + s[0].ToString()]);
                }
            }

            DataTable d = qb.QBManager(Convert.ToInt32(Session["QueryID"]), Params);



            if (d.Rows.Count > 0)
            {
                StringBuilder sb = new StringBuilder();

                if (!ViewOn)
                {
                    switch (RadioResult.SelectedValue)
                    {
                    case "CSV1":
                        delimiter = ",";
                        break;

                    case "CSV2":
                        delimiter = ";";
                        break;

                    case "PDF":
                    case "RTF":
                        delimiter = ((char)2) + "";
                        break;

                    default:
                        delimiter = ",";
                        break;
                    }
                }

                if (ViewOn)
                {
                    sb.Append("<table class=\"normal\" id=\"unique_id\" width=\"98%\" border=1 cellspacing=0 bgcolor=#ffffff><tr>");
                }
                int errocount = 0;
                try
                {
                    foreach (DataColumn cc in d.Columns)
                    {
                        if (ViewOn)
                        {
                            sb.AppendFormat("<th>{0}</th>", (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}")) ? cc.ColumnName.Substring(3, cc.ColumnName.Length - 3) : cc.ColumnName);
                        }
                        else
                        {
                            sb.AppendFormat("\"{0}\"{1}", (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}")) ? cc.ColumnName.Substring(3, cc.ColumnName.Length - 3) : cc.ColumnName, delimiter);
                        }
                        errocount++;
                    }
                }catch (Exception ex)
                {
                    string error = "DEBUG CHECK\r\n" + Session["QueryID"].ToString() + "\r\n";
                    foreach (DictionaryEntry de in  Params)
                    {
                        error += de.Key + " - " + de.Value + "\r\n";
                    }
                    foreach (DataColumn cc in d.Columns)
                    {
                        error += ">" + cc.ColumnName + "\r\n";
                    }
                    error += errocount;
                    throw new Exception(ex.Message + "\r\n" + error);
                }

                if (ViewOn)
                {
                    sb.Append("</tr>");
                }
                else
                {
                    if (RadioResult.SelectedValue == "PDF" || RadioResult.SelectedValue == "RTF")
                    {
                        if (sb.Length > 0)
                        {
                            sb.Remove(sb.Length - 1, 1);
                        }
                        sb.Append((char)1);
                    }
                    else
                    {
                        if (sb.Length > 0)
                        {
                            sb.Remove(sb.Length - 1, 1);
                        }
                        sb.Append("\r\n");
                    }
                }

                double[] sum     = new double[d.Columns.Count];
                bool[]   sumType = new bool[d.Columns.Count];
                bool     boolSum = false;
                foreach (DataRow dr in d.Rows)
                {
                    int indexSum = 0;
                    if (ViewOn)
                    {
                        sb.Append("<tr>");
                    }
                    foreach (DataColumn cc in d.Columns)
                    {
                        if (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}"))
                        {
                            double sumtoadd = 0;
                            try
                            {
                                sumtoadd = Convert.ToDouble(dr[cc.ColumnName]);
                            }
                            catch
                            {
                                sumtoadd = 0;
                            }
                            sum[indexSum]    += sumtoadd;
                            sumType[indexSum] = (cc.ColumnName.StartsWith("{t}"));                             // true = tempo , false = numerico
                            boolSum           = true;
                        }
                        string timeToPrint = String.Empty;
                        if (cc.ColumnName.StartsWith("{t}"))
                        {
                            int duration = 0;
                            try
                            {
                                duration = Convert.ToInt32(dr[cc.ColumnName]);
                            }
                            catch
                            {
                                duration = 0;
                            }

                            if (duration > 0)
                            {
                                if (duration < 60)
                                {
                                    timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
                                }
                                else
                                {
                                    timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
                                                  ((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
                                }
                            }
                        }
                        if (ViewOn)
                        {
                            if (cc.ColumnName.StartsWith("{t}"))
                            {
                                sb.AppendFormat("<td valign=top>{0}&nbsp;</td>", timeToPrint);
                            }
                            else
                            if (dr[cc.ColumnName].ToString().StartsWith("Mottxt"))
                            {
                                sb.AppendFormat("<td valign=top>{0}&nbsp;</td>", Root.rm.GetString(dr[cc.ColumnName].ToString()));
                            }
                            else
                            {
                                sb.AppendFormat("<td valign=top>{0}&nbsp;</td>", dr[cc.ColumnName]);
                            }
                        }
                        else
                        {
                            if (dr[cc.ColumnName].ToString().Length > 0)
                            {
                                if (cc.ColumnName.StartsWith("{t}"))
                                {
                                    sb.AppendFormat("\"{0}\"{1}", timeToPrint, delimiter);
                                }
                                else
                                {
                                    sb.AppendFormat("\"{0}\"{1}", dr[cc.ColumnName], delimiter);
                                }
                            }
                            else
                            {
                                sb.AppendFormat("{0}", delimiter);
                            }
                        }
                        indexSum++;
                    }
                    if (ViewOn)
                    {
                        sb.Append("</tr>");
                    }
                    else
                    {
                        if (RadioResult.SelectedValue == "PDF" || RadioResult.SelectedValue == "RTF")
                        {
                            sb.Remove(sb.Length - 1, 1);
                            sb.Append((char)1);
                        }
                        else
                        {
                            sb.Remove(sb.Length - 1, 1);
                            sb.Append("\r\n");
                        }
                    }
                }

                if (boolSum)
                {
                    if (ViewOn)
                    {
                        sb.Append("<tr>");

                        for (int i = 0; i < sum.Length; i++)
                        {
                            sb.Append("<td>");
                            if (sum[i] > 0)
                            {
                                if (sumType[i])
                                {
                                    int    duration    = Convert.ToInt32(sum[i]);
                                    string timeToPrint = String.Empty;
                                    if (duration > 0)
                                    {
                                        if (duration < 60)
                                        {
                                            timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
                                        }
                                        else
                                        {
                                            timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
                                                          ((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
                                        }
                                    }
                                    sb.Append("<b>" + timeToPrint + "</b>");
                                }
                                else
                                {
                                    sb.Append("<b>" + sum[i].ToString() + "</b>");
                                }
                            }
                            else
                            {
                                sb.Append("&nbsp;");
                            }
                            sb.Append("</td>");
                        }
                        sb.Append("</tr>");
                    }
                    else
                    {
                        for (int i = 0; i < sum.Length; i++)
                        {
                            if (sum[i] > 0)
                            {
                                if (sumType[i])
                                {
                                    int    duration    = Convert.ToInt32(sum[i]);
                                    string timeToPrint = String.Empty;
                                    if (duration > 0)
                                    {
                                        if (duration < 60)
                                        {
                                            timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
                                        }
                                        else
                                        {
                                            timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
                                                          ((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
                                        }
                                    }
                                    sb.AppendFormat("\"{0}\"{1}", timeToPrint, delimiter);
                                }
                                else
                                {
                                    sb.AppendFormat("\"{0}\"{1}", sum[i].ToString(), delimiter);
                                }
                            }
                            else
                            {
                                sb.AppendFormat("{0}", delimiter);
                            }
                        }
                        sb.Remove(sb.Length - 1, 1);
                        sb.Append("\r\n");
                    }
                }

                if (ViewOn)
                {
                    sb.Append("</table>");
                    if (RadioResult.SelectedValue == "HTML")
                    {
                        sb.Append("</table><script src=\"/js/reporthtml.js\"></script>");
                        QBResult.Text = sb.ToString();
                    }
                }
                else if (RadioResult.SelectedValue == "Excel")
                {
                    ExportUtils eu = new ExportUtils();
                    DataSet     dt = new DataSet();
                    dt.Tables.Add(d);
                    eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.ExcelXML, HttpContext.Current);
                    return;
                }
                else if (RadioResult.SelectedValue == "XML")
                {
                    ExportUtils eu = new ExportUtils();
                    DataSet     dt = new DataSet();
                    dt.Tables.Add(d);
                    eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.XML, HttpContext.Current);
                    return;
                }
                else if (RadioResult.SelectedValue == "ADO")
                {
                    ExportUtils eu = new ExportUtils();
                    DataSet     dt = new DataSet();
                    dt.Tables.Add(d);
                    eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.ADORecordSet, HttpContext.Current);
                    return;
                }
                else
                {
                    if (RadioResult.SelectedValue != "PDF" && RadioResult.SelectedValue != "RTF")
                    {
                        Response.AddHeader("Content-Disposition", "attachment; filename=Result.csv");
                        Response.AddHeader("Expires", "Thu, 01 Dec 1994 16:00:00 GMT ");
                        Response.AddHeader("Pragma", "nocache");
                        Response.ContentType = "application/vnd.ms-excel";
                        Response.Write(sb.ToString());
                        return;
                    }
                    else if (RadioResult.SelectedValue == "PDF")
                    {
                        PDFReport    p        = new PDFReport();
                        MemoryStream MyStream = p.PDFRender(d);
                        Response.AddHeader("Content-Disposition", "attachment; filename=report.pdf");
                        Response.CacheControl = "Private";
                        Response.AddHeader("Pragma", "No-Cache");
                        Response.ContentType = "application/pdf";
                        Response.BinaryWrite(MyStream.GetBuffer());
                        Response.End();
                        return;
                    }
                    else
                    {
                        PDFReport    p        = new PDFReport();
                        MemoryStream MyStream = p.RTFRender(d);
                        Response.AddHeader("Content-Disposition", "attachment; filename=report.rtf");
                        Response.CacheControl = "Private";
                        Response.AddHeader("Pragma", "No-Cache");
                        Response.ContentType = "application/rtf";
                        Response.BinaryWrite(MyStream.GetBuffer());
                        Response.End();
                        return;
                    }
                }
            }
            else
            {
                QBResult.ForeColor = Color.Red;
                QBResult.CssClass  = "normal";
                QBResult.Text      = Root.rm.GetString("QBUtxt26");
            }
        }