using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using sgt_objects;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;
using System.IO;
public partial class Reports_ProjectsReportBySupervisor_ProjectsReportBySupervisor : System.Web.UI.Page
{
private string sql = System.Configuration.ConfigurationManager.AppSettings["SQL"].ToString();
private string sqla = System.Configuration.ConfigurationManager.AppSettings["SQLa"].ToString();
private string startdate = System.Configuration.ConfigurationManager.AppSettings["StartDate"];
sgt_objects.SGTDataSet ds1 = new SGTDataSet();
sgt_objects.components com = new components();
sgt_objects.SGTCommand cmd = new SGTCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
fillSupervisor();
fillBusinessUnit();
tedate.Text = DateTime.Parse(System.DateTime.Now.ToShortDateString()).ToString("dd/MMM/yyyy");
}
}
private void fillSupervisor()
{
if (ddl_Supervisor.SelectedIndex == 0)
{
ddl_Supervisor.Items.Insert(0, new System.Web.UI.WebControls.ListItem("All", "ALL"));
}
else
{
string query = "SELECT DISTINCT Upper(ProjectSupervisor) AS ProjectSupervisor from PROJECT where (ProjectSupervisor <>' ') order by ProjectSupervisor";
com.sgtSQL_makeDropDownList(ddl_Supervisor,sqla, query, "ProjectSupervisor", "ProjectSupervisor");
ddl_Supervisor.Items.Insert(0, new System.Web.UI.WebControls.ListItem("All", "%"));
}
}
private void fillBusinessUnit()
{
if (ddl_businessUnit.SelectedIndex == 0)
{
ddl_businessUnit.Items.Insert(0, new System.Web.UI.WebControls.ListItem("ALL","All"));
}
else
{
string query = "SELECT DISTINCT CLIENT.CompanyName,Code FROM CLIENT";
com.sgtSQL_makeDropDownList(ddl_businessUnit, sqla, query,"CompanyName","Code");
ddl_businessUnit.Items.Insert(0, new System.Web.UI.WebControls.ListItem("All", "%")); ;
}
}
private DataTable gettable1()
{
string query = "select CLIENT.CompanyName,CLIENT.CODE,CLIENT.ClientID,ProjectName,M_EstimateHours,E_EstimateHours,C_EstimateHours,M_RevisedEstimateHours,E_RevisedEstimateHours" +
" C_RevisedEstimateHours,ProjectSupervisor,ProjectCode from PROJECT inner join CLIENT on" +
" PROJECT.ClientID=CLIENT.CODE";
if (ddl_Supervisor.SelectedItem.Text != "All")
{
query += " where ProjectSupervisor = " + ddl_Supervisor.SelectedValue;
}
if (ddl_businessUnit.SelectedItem.Text != "All")
{
query += " where CLIENT.CODE = " + ddl_businessUnit.SelectedValue;
}
DataTable table = new DataTable("PROJECT");
table.Columns.Add("ProjectSupervisor",typeof(System.String));
table.Columns.Add("CODE", typeof(System.String));
table.Columns.Add("ClientID", typeof(System.String));
table.Columns.Add("CompanyName",typeof(System.String));
table.Columns.Add("ProjectName",typeof(System.String));
table.Columns.Add("ProjectCode", typeof(System.String));
table.Columns.Add("M_EstimateHours",typeof(System.Double));
table.Columns.Add("E_EstimateHours",typeof(System.Double));
table.Columns.Add("C_EstimateHours",typeof(System.Double));
table.Columns.Add("M_RevisedEstimateHours",typeof(System.Double));
table.Columns.Add("E_RevisedEstimateHours",typeof(System.Double));
table.Columns.Add("C_RevisedEstimateHours",typeof(System.Double));
//Reading data from database.
SqlDataReader dr = null;
SqlCommand cm = new SqlCommand();
cm = cmd.SQLReadCommand(sqla, query, cm);
dr = cm.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["ProjectSupervisor"] = dr["ProjectSupervisor"].ToString();
row["CompanyName"] = dr["CompanyName"].ToString();
row["ProjectName"] = dr["ProjectName"].ToString();
row["ProjectCode"] = dr["ProjectCode"].ToString();
row["CODE"] = dr["CODE"].ToString();
row["ClientID"] = dr["ClientID"].ToString();
if (dr["M_EstimateHours"].ToString() != "")
{
row["M_EstimateHours"] = Convert.ToDouble(dr["M_EstimateHours"]);
}
if (dr["E_EstimateHours"].ToString() != "")
{
row["E_EstimateHours"] = Convert.ToDouble(dr["E_EstimateHours"]);
}
if (dr["C_EstimateHours"].ToString() != "")
{
row["C_EstimateHours"] = Convert.ToDouble(dr["C_EstimateHours"]);
}
if (dr["M_RevisedEstimateHours"].ToString() != "")
{
row["M_RevisedEstimateHours"] = Convert.ToDouble(dr["M_RevisedEstimateHours"]);
}
if (dr["E_RevisedEstimateHours"].ToString() != "")
{
row["E_RevisedEstimateHours"] = Convert.ToDouble(dr["E_RevisedEstimateHours"]);
}
if (dr["C_RevisedEstimateHours"].ToString() != "")
{
row["C_RevisedEstimateHours"] = Convert.ToDouble(dr["C_RevisedEstimateHours"]);
}
table.Rows.Add(row);
}
dr.Close();
cm.Connection.Close();
return table;
}
private DataTable gettable2()
{
string query = "select WbsProjectno as project,sum(WbsHours) as hoursworked from wbstimecard where wbsprojectno = '" + ddl_ProjectStatus.SelectedValue + "' and" + " (Convert(datetime,WbsDateCaptured,103)< " +
" Convert(DateTime," + "'" + tedate.Text + "'" + " ,103)" +
" Group by WbsProjectNo";
if (ddl_businessUnit.SelectedItem.Text != "All")
{
query += " and WbsHours=" + ddl_businessUnit.SelectedValue;
}
DataTable table = new DataTable("v_hoursworkedforeachproject");
table.Columns.Add("hoursworked",typeof(System.Double));
table.Columns.Add("project", typeof(System.String));
//Reading data from database.
SqlDataReader dr = null;
SqlCommand cm = new SqlCommand();
cm = cmd.SQLReadCommand(sqla, query, cm);
dr = cm.ExecuteReader();
while (dr.Read())
{
DataRow row = table.NewRow();
row["project"] = dr.GetValue(0);
if (dr["hoursworked"].ToString() != "")
{
row["hoursworked"] = Convert.ToDouble(dr["hoursworked"]);
}
table.Rows.Add(row);
}
dr.Close();
cm.Connection.Close();
return table;
}
private void getdata()
{
DataSet ds1 = new DataSet();
ds1.Tables.Add(gettable1());
ds1.Tables.Add(gettable2());
string temproot = System.Configuration.ConfigurationManager.AppSettings["PDFdirectory"].ToString();
string oldtemp = System.Environment.GetEnvironmentVariable("TMP");
System.Environment.SetEnvironmentVariable("TMP", temproot);
ReportDocument rep = new ReportDocument();
rep.Load(Server.MapPath("ProjectsReportBySupervisor.rpt"));
System.Environment.SetEnvironmentVariable("TMP", oldtemp);
rep.SetDataSource(ds1);
rep.SetParameterValue("
@p_Status",ddl_ProjectStatus.SelectedItem.Text);
rep.SetParameterValue("
@p_Supervisor",ddl_Supervisor.SelectedItem.Text);
rep.SetParameterValue("
@p_BusinessUnit", ddl_businessUnit.SelectedItem.Text);
rep.SetParameterValue("
@edate", tedate.Text);
exportreport(rep);
rep.Close();
rep.Dispose();
}
private void exportreport(ReportDocument rep)
{
string filename = Session.SessionID.ToString() + ".pdf";
string Exportpath = System.Configuration.ConfigurationManager.AppSettings["PDFDirectory"].ToString();
if (!Directory.Exists(Exportpath))
{
Directory.CreateDirectory(Exportpath);
}
DiskFileDestinationOptions df = new DiskFileDestinationOptions();
ExportOptions options = new ExportOptions(); ;
df.DiskFileName = Exportpath + filename;
options.ExportDestinationOptions = df;
options.ExportDestinationType = ExportDestinationType.DiskFile;
options.ExportFormatType = ExportFormatType.PortableDocFormat;
rep.Export(options);
displayreport(this, Exportpath + filename);
System.IO.File.Delete(Exportpath + filename);
}
private void displayreport(System.Web.UI.Page page, string path)
{
page.Response.ClearContent();
page.Response.ClearHeaders();
page.Response.ContentType = "application/pdf";
page.Response.WriteFile(path);
page.Response.Flush();
page.Response.Close();
}
#region calendar
protected void iedate_Click(object sender, ImageClickEventArgs e)
{
Calendar1.Visible = !Calendar1.Visible;
tbit.Text = "0";
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
if (tbit.Text == "0")
{
tedate.Text = DateTime.Parse(Calendar1.SelectedDate.ToShortDateString()).ToString("dd/MMM/yyyy");
Calendar1.Visible = false;
}
}
#endregion protected void ddlSupervisor_SelectedIndexChanged(object sender, EventArgs e)
{
fillSupervisor();
}
protected void btnPreviewReport_Click(object sender, EventArgs e)
{
getdata();
}
}
Comments
Can you maybe point out where the debugger is saying the error is or include the error and a stack trace?
I think that might help someone figure out where to look for the problem.
><//~Psightoplasm`~