private void LoadReport()
{
string foldername = MapPath("~/CS_REPORT/rpt/MTODPexemptionletter.rpt");
CrystalDecisions.Web.Parameter myparamss2 = new CrystalDecisions.Web.Parameter();
string tmp_acctcode = "";
string cardname = StringHelper.ReCodeCharacters(DropDownList1.Text);
DataTable user_counterid = SqlDbHelper.getDataDT("SELECT cardcode FROM SAPSERVER.MATIMCO.dbo.OCRD WHERE cardname='" + cardname + "' GROUP BY cardcode");
foreach (DataRow acctCode in user_counterid.Rows)
{
tmp_acctcode = acctCode["cardcode"].ToString();
}
myparamss2.DefaultValue = tmp_acctcode;
myparamss2.Name = "Customer Code";
ReportDocument rpt1 = new ReportDocument();
// CrystalReportSource1.ReportDocument.SetDatabaseLogon("sa", "p@ssw0rd", "192.168.10.15", "MATIMCO");
rpt1.Load(foldername);
CrystalDecisions.Web.Report rpt_web = new CrystalDecisions.Web.Report();
rpt_web.FileName = foldername;
rpt_web.Parameters.Add(myparamss2);
CrystalReportSource1.Report = rpt_web;
CrystalReportSource1.Report.FileName = foldername;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "MATIMCO";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.15";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportSource1.Report.Parameters.Add(myparamss2);
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
Label2.Visible = false;
}
protected void Page_Load(object sender, EventArgs e)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/Credit Investigation Tab Info.rpt");
try
{
CrystalDecisions.Web.Parameter acctcode = new CrystalDecisions.Web.Parameter();
string tmp_acctcode = "";
string cardcode = StringHelper.ReCodeCharacters(DropDownList1.Text);
DataTable user_counterid = SqlDbHelper.getDataDT("SELECT acctcode FROM customerheader WHERE acctcode='" + cardcode + "' GROUP BY acctcode");
foreach (DataRow acctCode in user_counterid.Rows)
{
tmp_acctcode = acctCode["acctcode"].ToString();
}
acctcode.Name = "Account Code";
acctcode.DefaultValue = tmp_acctcode;
ReportDocument rpt1 = new ReportDocument();
rpt1.Load(foldername);
CrystalDecisions.Web.Report rpt_web = new CrystalDecisions.Web.Report();
rpt_web.FileName = foldername;
rpt_web.Parameters.Add(acctcode);
CrystalReportSource1.Report = rpt_web;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "ARMS";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.13";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportSource1.Report.Parameters.Add(acctcode);
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
Label2.Visible = false;
}
catch (Exception ex)
{
Label2.Visible = true;
Label2.Text = "Error: " + ex.Message;
}
}
private void Period_Closing()
{
try
{
plantcode = ddl_PlantID.SelectedItem.Value;
cr.Load(Server.MapPath("Report\\ClosingStockCrystal.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
t1.Text = companycode + "_" + cname;
t2.Text = ddl_PlantName.SelectedItem.Value;
t3.Text = "From " + txt_FromDate.Text.Trim();
t4.Text = "To " + txt_ToDate.Text.Trim();
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
// str = "SELECT t1.*,ISNULL(Agnt.CarAmt,0) AS CarAmt FROM (SELECT pcode,ISNULL(Smltr,0) AS Smltr,ISNULL(Smkg,0) AS Smkg,ISNULL(AvgFat,0) AS AvgFat,ISNULL(AvgSnf,0) AS AvgSnf,ISNULL(AvgRate,0) AS AvgRate,ISNULL(Avgclr,0) AS Avgclr,ISNULL(Scans,0) AS Scans,ISNULL(SAmt,0) AS SAmt,ISNULL(Avgcrate,0) AS Avgcrate,ISNULL(Sfatkg,0) AS Sfatkg,ISNULL(Ssnfkg,0) AS Ssnfkg,ISNULL(Billadv,0) AS Billadv,ISNULL(Ai,0) AS Ai,ISNULL(feed,0) AS feed,ISNULL(Can,0) AS Can,ISNULL(Recovery,0) AS Recovery,ISNULL(Others,0)AS Others FROM (SELECT spropcode AS pcode,CAST(Smltr AS DECIMAL(18,2)) AS Smltr,CAST(Smkg AS DECIMAL(18,2))AS Smkg,CAST(AvgFat AS DECIMAL(18,2)) AS AvgFat,CAST(AvgSnf AS DECIMAL(18,2)) AS AvgSnf,CAST(AvgRate AS DECIMAL(18,2)) AS AvgRate,CAST(Avgclr AS DECIMAL(18,2))AS Avgclr,CAST(Scans AS DECIMAL(18,2)) AS Scans,CAST(SAmt AS DECIMAL(18,2)) AS SAmt,CAST(Avgcrate AS DECIMAL(18,2)) AS Avgcrate,CAST(Sfatkg AS DECIMAL(18,2)) AS Sfatkg,CAST(Ssnfkg AS DECIMAL(18,2)) AS Ssnfkg,Billadv,Ai,feed,Can,Recovery,Others FROM (SELECT plant_Code AS spropcode,SUM(Milk_ltr) AS Smltr,SUM(Milk_kg) AS Smkg,AVG(FAT) AS AvgFat,AVG(SNF) AS AvgSnf,AVG(Rate) AS AvgRate,AVG(Clr) AS Avgclr,SUM(NoofCans) AS Scans,SUM(Amount) AS SAmt,AVG(ComRate) AS Avgcrate,SUM(fat_kg) AS Sfatkg,SUM(snf_kg) AS SSnfkg FROM Procurement WHERE Company_Code='1' AND Prdate BETWEEN '08-17-2012' AND '01-18-2013' GROUP BY plant_Code ) AS spro LEFT JOIN (SELECT Plant_code AS dedupcode,SUM(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,SUM(CAST((Ai) AS DECIMAL(18,2))) AS Ai,SUM(CAST((Feed) AS DECIMAL(18,2))) AS Feed,SUM(CAST((can) AS DECIMAL(18,2))) AS can,SUM(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,SUM(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '08-17-2012' AND '01-18-2013' AND Company_Code='1' GROUP BY Plant_code ) AS Dedu ON spro.spropcode=Dedu.dedupcode) AS produ LEFT JOIN (SELECT Plant_Code AS LonPcode,SUM(CAST(inst_amount AS DECIMAL(18,2))) AS instamt FROM LoanDetails WHERE Company_Code='1' AND Balance>0 GROUP BY Plant_Code) AS londed ON produ.pcode=londed.LonPcode) AS t1 LEFT JOIN (SELECT Plant_Code AS cartPCode,SUM(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt FROM Agent_Master WHERE Type=0 AND Company_Code='1' GROUP BY Plant_Code) AS Agnt ON t1.pcode=Agnt.cartPCode ";
str = "select MilkKg as MILKKG,Fat as FAT,Snf as SNF,FAT_KG,SNF_KG,Amount as AMOUNT,Rate as RATE,convert(varchar(10),Date,101) as date from Stock_Milk where date between '" + txt_FromDate.Text + "' and '" + txt_ToDate.Text + "' and Plant_code='" + plantcode + "' order by date asc";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
//System.IO.MemoryStream stream = (System.IO.MemoryStream)cr.ExportToStream(ExportFormatType.PortableDocFormat);
//BinaryReader Bin = new BinaryReader(cr.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat));
//Response.ClearContent();
//Response.ClearHeaders();
//Response.ContentType = "application/pdf";
//Response.BinaryWrite(Bin.ReadBytes(Convert.ToInt32(Bin.BaseStream.Length)));
//Response.Flush();
//Response.Close();
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
string type = Request.QueryString["type"];
string nid = Request.QueryString["nid"];
//Label1.Text = type + "\n" + nid;
//Label1.Text = Label1.Text+"\n"+ Page.ResolveUrl(@"~/Reports/CrystalReport1.rpt");
SqlConnection dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings["_IntrinsicData"].ToString());
SqlCommand cmd = new SqlCommand("CRF_nocLetter", dbCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@pLetterGuid", nid));
try
{
dbCon.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet("DataSet1");
DataTable dt = new DataTable("NOCLetter");
da.Fill(dt);
dt.Columns.Add("qrCode", System.Type.GetType("System.Byte[]"));
dt.Rows[0]["qrCode"] = qrGenerator(nid);
if (dt.Rows.Count == 1)
{
ds.Tables.Add(dt);
bool flag = File.Exists(Server.MapPath(@"~/Reports/CRF_NoC.rpt"));
//Label1.Text = Label1.Text + "\nFile Exists? " + flag.ToString();
//Label1.Text = Label1.Text + "\n" + Server.MapPath(@"~/Reports/CRF_NoC.rpt");
//if (flag)
{
//Label1.Text = Label1.Text + "\n" + CRSource.ReportDocument.DataSourceConnections;
ReportDocument reportDocument34 = new ReportDocument();
reportDocument34.Load(Server.MapPath(@"~\\Reports\\CRF_NoC.rpt"));
//reportDocument34.Database.Tables[0].SetDataSource(dt);
reportDocument34.SetDataSource(ds);
//reportDocument34.ExportToHttpResponse(ExportFormatType.PortableDocFormat, this.Response, true, "Test");
CrystalReportViewer1.ReportSource = (object)reportDocument34;
CrystalReportViewer1.RefreshReport();
CrystalReportViewer1.DataBind();
CrystalReportViewer1.Visible = true;
}
}
}
catch (Exception ex)
{
//Label1.Text = ex.Message;
}
}
protected void fillReports()
{
DataSet1 ds = new DataSet1();
ds = csfee.fncGetStudentDailyAllFeeCollectioReport(Int32.Parse(ddlyear.SelectedValue), ddlAdmission.SelectedValue, ddlclass.SelectedValue);
reportfilepath = Server.MapPath("CR_StudentWiseAllFeecollectionReport.rpt");
CrystalReportViewer1.EnableDatabaseLogonPrompt = false;
ReportDocument FeeDetails = new ReportDocument();
FeeDetails.Load(reportfilepath);
FeeDetails.SetDataSource(ds.Tables["StudentWiseAllFeeCollection"]);
CrystalReportViewer1.ReportSource = FeeDetails;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}
public void LoadReport()
{
ReportDocument reportDocument = new ReportDocument();
string queryString1 = Session["sSql"].ToString();
string reportPath = Server.MapPath("~/CrystalReports/CrystalReport1.rpt");
reportDocument.Load(reportPath);
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(queryString1, sqlConnection);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
adapter.Fill(ds, "EmployeeView");
dt = ds.Tables["EmployeeView"]; // // ds.Tables[0]
int departmentID = 0;
foreach (DataRow dr in dt.Rows)
{
departmentID = (int)dr["DepartmentID"];
}
DataSet ds1 = new DataSet();
string queryString2 = "SELECT * FROM tblDepartment WHERE ID = 1";
SqlDataAdapter adapter1 = new SqlDataAdapter(queryString2, sqlConnection);
adapter1.Fill(ds1, "tblDepartment");
reportDocument.SetDataSource(ds.Tables["EmployeeView"]);
reportDocument.Subreports["CrystalSubReport1"].SetDataSource(ds1.Tables["tblDepartment"]); // Subreports[0] // ds1.Tables[0]
//reportDocument.SetDatabaseLogon("sa", "manager", sqlConnection.DataSource, sqlConnection.Database);
//reportDocument.Subreports[0].SetDatabaseLogon("sa", "manager", sqlConnection.DataSource, sqlConnection.Database);
//reportDocument.SetParameterValue("MyParameter", "Hello World");
//reportDocument.SetParameterValue("MyParameterSub", "Hello World Sub", reportDocument.Subreports[0].Name.ToString());
//reportDocument.SetParameterValue("MyParameterSub", "Hello World Sub", "CrystalSubReport1");
reportDocument.DataDefinition.FormulaFields["ReportName"].Text = "{EmployeeView.Gender}";
reportDocument.Subreports[0].DataDefinition.FormulaFields["ReportNameSub"].Text = "{tblDepartment.Location}";
CrystalReportViewer1.ReportSource = reportDocument;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}
}
protected void Page_Load(object sender, EventArgs e)
{
CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
CrystalReport1 crystalReport = new CrystalReport1();
if (Request["page"] != null && Request["page"].ToString() == "page1")
{
crystalReport.SetDataSource(SampleData.Persons.Where(r => r.PersonName.StartsWith("廖")));
}
else
{
crystalReport.SetDataSource(SampleData.Persons.Where(r => r.PersonAddress.Contains("台中市")));
}
CrystalReportViewer1.ReportSource = crystalReport;
CrystalReportViewer1.RefreshReport();
}
protected void Button1_Click(object sender, EventArgs e)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/mrktprogactivitiesupdate.rpt");
try
{
CrystalDecisions.Web.Parameter myparamss = new CrystalDecisions.Web.Parameter();
myparamss.DefaultValue = TextBox1.Text;
myparamss.Name = "Marketing Program No";
ReportDocument rpt1 = new ReportDocument();
rpt1.Load(foldername);
CrystalDecisions.Web.Report rpt_web = new CrystalDecisions.Web.Report();
rpt_web.FileName = foldername;
rpt_web.Parameters.Add(myparamss);
CrystalReportSource1.Report = rpt_web;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
//CrystalReportSource1.Report.FileName = foldername;
//CrystalReportSource1.Report.Parameters.Add(myparamss);
//CrystalReportSource1.Report.Parameters.Add(myparamss2);
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "MATIMCO";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.15";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
Label2.Text = "Error-" + ex.Message;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack == false)
{
txtDateFrom.Text = DateTime.Now.AddDays(-3).ToString("dd-MMM-yyyy");
txtDateTo.Text = DateTime.Now.ToString("dd-MMM-yyyy");
CrystalReportViewer1.Zoom(Convert.ToInt16(ConfigurationManager.AppSettings["ReportZoom"]));
}
//conne.ServerName = ConfigurationManager.AppSettings["ServerName"];
// conne.DatabaseName = ConfigurationManager.AppSettings["DatabaseName"];
// conne.UserID = ConfigurationManager.AppSettings["UserID"];
// conne.Password = "******";//ConfigurationManager.AppSettings["Password"];
ConnectionInfo conne = new ConnectionInfo();
conne.ServerName = ConfigurationManager.AppSettings["ServerName"].ToString();
conne.DatabaseName = ConfigurationManager.AppSettings["DatabaseName"].ToString();
conne.UserID = ConfigurationManager.AppSettings["UserID"].ToString();
conne.Password = ConfigurationManager.AppSettings["Password"].ToString();
TableLogOnInfo tablelogs = new TableLogOnInfo();
Database db = rd.Database;
Tables tbls = db.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table tab in tbls)
{
tablelogs = tab.LogOnInfo;
tablelogs.ConnectionInfo = conne;
tab.ApplyLogOnInfo(tablelogs);
tab.Location = ConfigurationManager.AppSettings["DatabaseName"] + ".dbo." + tab.Name; //most important line whole report is depend on this line
}
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.SelectionFormula = "{Rep_SummaryOfDeposit.TranDate} >= cdate('" + txtDateFrom.Text + "') and {Rep_SummaryOfDeposit.TranDate} <=cdate('" + txtDateTo.Text + "')";
CrystalReportViewer1.RefreshReport();
}
private void LoadReport()
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/Disc-RunningMTD-IndividualAccount.rpt");
try
{
CrystalDecisions.Web.Parameter customercode = new CrystalDecisions.Web.Parameter();
string tmp_acctcode = "";
string cardname = StringHelper.ReCodeCharacters(DropDownList1.Text);
DataTable user_counterid = SqlDbHelper.getDataDT("SELECT cardcode FROM SAPSERVER.MATIMCO.dbo.arms_vw_discount WHERE cardname='" + cardname + "' GROUP BY cardcode");
foreach (DataRow acctCode in user_counterid.Rows)
{
tmp_acctcode = acctCode["cardcode"].ToString();
}
customercode.Name = "CustomerCode";
customercode.DefaultValue = tmp_acctcode;
ReportDocument rpt1 = new ReportDocument();
CrystalReportSource1.Report.FileName = foldername;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "MATIMCO";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.15";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportSource1.Report.Parameters.Add(customercode);
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
Label2.Text = "Error-" + ex.Message;
}
}
protected void fillReports()
{
try
{
string Fromdate = string.Empty;
string Todate = string.Empty;
DataSet1 ds = new DataSet1();
if (txtFrom.Text != string.Empty)
{
Fromdate = txtFrom.Text.Substring(6, 4) + txtFrom.Text.Substring(3, 2) + txtFrom.Text.Substring(0, 2);
}
else
{
Fromdate = string.Empty;
}
if (txtTo.Text != string.Empty)
{
Todate = txtTo.Text.Substring(6, 4) + txtTo.Text.Substring(3, 2) + txtTo.Text.Substring(0, 2);
}
else
{
Todate = string.Empty;
}
ds = csfee.fncStudentDateWisePaymentReport(Int32.Parse(ddlyear.SelectedValue), ddlAdmission.SelectedValue, ddlclass.SelectedValue, Fromdate, Todate, ddlPayMode.SelectedValue);
if (ddlclass.SelectedIndex == 0 && (ddlAdmission.SelectedIndex > 0 || ddlAdmission.SelectedIndex > 0) && ds.Tables["StudentDateWisePayment"].Rows.Count > 0)
{
ddlclass.SelectedValue = ds.Tables["StudentDateWisePayment"].Rows[0]["Class"].ToString();
}
reportfilepath = Server.MapPath("CR_StudentDateWisePayment.rpt");
CrystalReportViewer1.EnableDatabaseLogonPrompt = false;
ReportDocument FeeDetails = new ReportDocument();
FeeDetails.Load(reportfilepath);
FeeDetails.SetDataSource(ds.Tables["StudentDateWisePayment"]);
CrystalReportViewer1.ReportSource = FeeDetails;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}
catch
{
txtFrom.Text = string.Empty;
txtTo.Text = string.Empty;
}
}
protected void Page_Load(object sender, EventArgs e)
{
CrystalReportViewer1.DisplayPage = true;
if (Environment.MachineName.ToString().Trim() != "ANN-LIN")
{
db1 = "[EIPB].[dbo]."; //如果不是在Ann本機, 則指定資料庫
db2 = "[EIPA].[dbo]."; //如果不是在Ann本機, 則指定資料庫
db3 = "[barcode].[dbo]."; //如果不是在Ann本機, 則指定資料庫
}
SmoothEnterprise.Database.DataSet rs = new SmoothEnterprise.Database.DataSet(SmoothEnterprise.Database.DataSetType.OpenRead);
rs.Open("select min(item) minitem,packing from " + db1 + "shipment_body where aid='" + Request.QueryString["id"] + "' group by packing");
if (!rs.EOF)
{
this.QUERY_ITEM.Text = rs["minitem"].ToString();
}
if (!IsPostBack)
{
CrystalReportViewer1.DisplayPage = false;
}
else
{
query();
}
myReportDocument.Load(Server.MapPath("shipment_print.rpt"));
TableLogOnInfo logoninfo;
foreach (CrystalDecisions.CrystalReports.Engine.Table mytable in myReportDocument.Database.Tables)
{
logoninfo = mytable.LogOnInfo;
logoninfo.ConnectionInfo.ServerName = "192.168.0.238";
logoninfo.ConnectionInfo.DatabaseName = "EIPB";
logoninfo.ConnectionInfo.UserID = "smooth";
logoninfo.ConnectionInfo.Password = "******";
mytable.ApplyLogOnInfo(logoninfo);
}
CrystalReportViewer1.ReportSource = myReportDocument;
CrystalReportViewer1.RefreshReport();
}
protected void Page_Load(object sender, EventArgs e)
{
int userId = Convert.ToInt32(Session["UserId"]); //Convert.ToInt32(Request.QueryString["userId"].ToString());
int type = Convert.ToInt32(Request.QueryString["type"].ToString());
string status = Request.QueryString["status"].ToString();
//string json = Json(projectLists, JsonRequestBehavior.AllowGet);
DataTable dt = new DataTable();
if (type == 1)
{
int batchName = Convert.ToInt32(Request.QueryString["batch_name"].ToString());
dt = ReportManager.GetProjectListsindt("all", batchName, 1);
}
else if (type == 2)
{
dt = ReportManager.GetProjectListsindt("all", userId, 2);
}
Session["rpt_path"] = "~/Reports/ProjectLstSupervisor.rpt";
Session["rpt_dt"] = dt;
//GridView1.DataSource = dt;
//GridView1.DataBind();
string pppp = Server.MapPath(Session["rpt_path"].ToString());
doc1.Load(Server.MapPath(Session["rpt_path"].ToString()));
doc1.SetDataSource((DataTable)Session["rpt_dt"]);
if (doc1 != null)
{
Response.Write("Calling this section");
CrystalReportViewer1.ReportSource = doc1;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
CrystalReportViewer1.Visible = true;
}
else
{
Response.Write("Error");
}
}
protected void Page_Load(object sender, EventArgs e)
{
//string foldername = Server.MapPath("~/CS_REPORT/rpt/cmperaccount.rpt");
try
{
string strpath = MapPath("~/CS_REPORT/rpt/cmperaccount.rpt");
//string strpath = Server.MapPath("~/CS_REPORT/rpt/ftmandytdsellinperareareport.rpt");
ReportDocument report = new ReportDocument();
CrystalDecisions.Shared.TableLogOnInfos crtableLogoninfos = new CrystalDecisions.Shared.TableLogOnInfos();
CrystalDecisions.Shared.TableLogOnInfo crtableLogoninfo = new CrystalDecisions.Shared.TableLogOnInfo();
CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
Tables CrTables;
CrystalReportSource1.Report.FileName = strpath;
report.Load(strpath);
crConnectionInfo.ServerName = "192.168.10.15";
crConnectionInfo.DatabaseName = "MATIMCO";
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "******";
CrTables = report.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
{
crtableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.RefreshReport();
Label2.Text = CrystalReportViewer1.ParameterFieldInfo[0].Name + "-" + CrystalReportViewer1.ParameterFieldInfo[1].Name;
}
catch (Exception ex)
{
Label2.Text = "Error-" + ex.Message;
}
}
protected void Button7_Click(object sender, EventArgs e)
{
Label1.Visible = false;
Label2.Visible = false;
Label3.Visible = false;
if (((TextBox1.Text != "") && (TextBox2.Text != "")))
{
CrystalReportViewer1.SelectionFormula = "{tbl_srvmast1.Com_Type}='" + DropDownList1.SelectedItem.Text + "'" + "AND{tbl_srvmast1.Rec_Date}>=CDate('" + TextBox1.Text + "')" + "AND {tbl_srvmast1.Rec_Date}<=CDate('" + TextBox2.Text + "')" + "AND {tbl_srvmast1.Act_Email}='" + DropDownList5.SelectedItem.Text + "'" + "AND{tbl_srvmast1.Req_Status}='" + DropDownList2.SelectedItem.Text + "'";
CrystalReportViewer1.RefreshReport();
}
else if ((((TextBox3.Text != "") && (TextBox4.Text != ""))))
{
CrystalReportViewer1.SelectionFormula = "{tbl_srvmast1.Com_Type}='" + DropDownList1.SelectedItem.Text + "'" + "AND {tbl_srvmast1.Com_Date}>=CDate('" + TextBox3.Text + "')" + "AND {tbl_srvmast1.Com_Date}<=CDate('" + TextBox4.Text + "')" + DropDownList3.SelectedItem.Value + "AND {tbl_srvmast1.Act_Email}='" + DropDownList5.SelectedItem.Text + "'" + "AND{tbl_srvmast1.Req_Status}='" + DropDownList2.SelectedItem.Text + "'";
CrystalReportViewer1.RefreshReport();
}
else
{
CrystalReportViewer1.SelectionFormula = "{tbl_srvmast1.Com_Type}='" + DropDownList1.SelectedItem.Text + "'" + "AND {tbl_srvmast1.Remarks} = '" + DropDownList3.SelectedItem.Value + "'" + "AND {tbl_srvmast1.Act_Email}='" + DropDownList5.SelectedItem.Text + "'" + "AND{tbl_srvmast1.Req_Status}='" + DropDownList2.SelectedItem.Text + "'";
CrystalReportViewer1.RefreshReport();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string var = Convert.ToString(Session["USUARIO"]);
if (String.IsNullOrEmpty(var))
{
Response.Redirect("Login.aspx");
}
if (!IsPostBack)
{
}
RptPersonal rp = new RptPersonal();
string server = @"JAVIRAMPC\BDDJAVIERAM";
string BDD = "HospitalAdonai";
rp.DataSourceConnections[0].SetConnection(server, BDD, string.Empty, string.Empty);
CrystalReportViewer1.ReportSource = rp;
CrystalReportViewer1.RefreshReport();
}
protected void Button1_Click(object sender, EventArgs e)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/quotation.rpt");
try
{
CrystalDecisions.Web.Parameter CashOnDelivery = new CrystalDecisions.Web.Parameter();
CrystalDecisions.Web.Parameter SalesQuotationNumber = new CrystalDecisions.Web.Parameter();
CashOnDelivery.Name = "Cash On Delivery";
SalesQuotationNumber.Name = "Sales Quotation Number";
SalesQuotationNumber.DefaultValue = TextBox1.Text;
CashOnDelivery.DefaultValue = DropDownList1.Text;
ReportDocument rpt1 = new ReportDocument();
CrystalReportSource1.Report.FileName = foldername;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "MATIMCO";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.15";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportSource1.Report.Parameters.Add(SalesQuotationNumber);
CrystalReportSource1.Report.Parameters.Add(CashOnDelivery);
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
Label2.Text = CrystalReportViewer1.ParameterFieldInfo[0].Name + "-" + CrystalReportViewer1.ParameterFieldInfo[1].Name;
}
catch (Exception ex)
{
//Label2.Text = "Error-" + ex.Message;
}
}
public void MostrarReporte()
{
DateTime fechainicial = Convert.ToDateTime(TxtFechaInicial.Value);
DateTime fechafinal = Convert.ToDateTime(TxtFechaFinal.Value);
DateTime fechaactual = DateTime.Now;
if (string.IsNullOrEmpty(TxtFechaInicial.Value) || string.IsNullOrEmpty(TxtFechaFinal.Value))
{
fechainicial = fechaactual;
fechafinal = fechaactual;
}
RptPacientesAtendidos rp = new RptPacientesAtendidos();
rp.SetDatabaseLogon("dafm", "Maquina123");
rp.SetParameterValue("@prmFInicial", fechainicial);
rp.SetParameterValue("@prmFFinal", fechafinal);
CrystalReportViewer1.ReportSource = rp;
CrystalReportViewer1.RefreshReport();
}
public void GetReportData()
{
string RptName = string.Empty;
RptName = Request.QueryString["ReportName"].ToString();
switch (RptName)
{
case "rptHandicapEmpList":
{
ReportObj = new crHandicapEmpList();
dataset = new DataSet();
dataset = (DataSet)HttpContext.Current.Session["ReportData"];
ReportObj.SetDataSource(dataset.Tables[0]);
break;
}
default:
break;
}
ReportObj.Database.Tables[0].ApplyLogOnInfo(ReportLog());
CrystalReportViewer1.ReportSource = ReportObj;
CrystalReportViewer1.RefreshReport();
//Session["ReportFormat"] = "PDF";
if (Session["ReportFormat"] != null)
{
if (Session["ReportFormat"].ToString() == "PDF")
{
System.IO.MemoryStream oStream;
oStream = (System.IO.MemoryStream)
ReportObj.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.BinaryWrite(oStream.ToArray());
Response.End();
}
}
}
public void VisualizaRelatorio()
{
CrystalReportViewer1.ReportSource = null;
InicializaRpt();
CrystalReportViewer1.RefreshReport();
ParameterFields paramFields = new ParameterFields();
foreach (var p in _relatorio.parametros)
{
ParameterField pfItemYr = new ParameterField();
pfItemYr.ParameterFieldName = p.parametro;
ParameterDiscreteValue dcItemYr = new ParameterDiscreteValue();
dcItemYr.Value = p.valor;
pfItemYr.CurrentValues.Add(dcItemYr);
paramFields.Add(pfItemYr);
CrystalReportViewer1.ParameterFieldInfo = paramFields;
}
relatorio.Refresh();
//CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
CrystalReportViewer1.ReportSource = relatorio;
}
protected void SubmitButton_OnClick(object sender, EventArgs e)
{
Utility ut = new Utility();
DateTimeOffset sDate = ut.FromString(txtStartDate.Text);
DateTimeOffset eDate = ut.FromString(txtEndDate.Text);
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("CrystalReport1.rpt"));
DataTable dataTable1 = PayrollByJobcodeReportSample(sDate, eDate);
DataSet1 ds = new DataSet1();
ds.Tables.Add(dataTable1);
crystalReport.SetDataSource(ds.Tables[1]);
CrystalReportViewer1.ReportSource = crystalReport;
CrystalReportViewer1.RefreshReport();
//throw new NotImplementedException();
}
protected void Page_Load(object sender, EventArgs e)
{
string ReportPath = "~/Stock/" + Session["ReportName"] + "";
string sql = Session["Qurey"].ToString();
// string sql = @"SELECT * FROM VMushok_6_1";
SqlCommand cmd = new SqlCommand(sql, con.conn);
con.conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.conn.Close();
DataTable Formula = dt;
ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report
crystalReport.Load(Server.MapPath(ReportPath));
crystalReport.SetDatabaseLogon("", "", "Localhost", "SBMS");
crystalReport.SetDataSource(Formula); // binding datatable
//crystalReport.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "Balance Sheet Report");
CrystalReportViewer1.ReportSource = crystalReport;
CrystalReportViewer1.RefreshReport();
}
protected void Button1_Click(object sender, EventArgs e)
{
Session[" text1 "] = TextBox1.Text;
//Connection string replace 'databaseservername' with your db server name
string sqlCon = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\userside.mdf;Integrated Security=True;User Instance=True";
SqlConnection con = new SqlConnection(sqlCon);
String str;
str = "select * from Booking_master where bookingdate = '" + TextBox1.Text + "' ";
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataSet ds;
ds = new DataSet();
da.Fill(ds);
ReportDocument report = new ReportDocument();
string reportPath = Server.MapPath("datewise.rpt");
report.Load(reportPath);
report.SetDataSource(ds.Tables[0]);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
MyClass my = new MyClass();
string fld = DropDownList1.SelectedValue.ToString();
string txt = TextBox1.Text.Trim();
if (Session["College_id"] != null)
{
cid = int.Parse(Session["College_id"].ToString());
sql = "select * from " + Tbl_Name + " where College_id=" + cid + " and " + fld + " like '" + txt + "%' ";
Label1.Text = cid.ToString();
Label2.Text = my.Get_College(cid);
}
else
{
Label1.Visible = false;
Label2.Visible = false;
sql = "select * from " + Tbl_Name + " where " + fld + " like '" + txt + "%' ";
}
if (TextBox2.Text == "" && TextBox3.Text == "")
{
sql = "select * from VReservation where " + fld + " like '" + txt + "%' ";
}
else
{
DateTime sdate = DateTime.Parse(TextBox2.Text);
DateTime edate = DateTime.Parse(TextBox3.Text);
sql = "select *from VReservation where Res_date >='" + sdate + "' and Res_date <='" + edate + "' ";
}
dt = my.GetTable(sql);
report1.SetDataSource(dt);
CrystalReportViewer1.ReportSource = report1;
CrystalReportViewer1.RefreshReport();
}
protected void Button1_Click(object sender, EventArgs e)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/List of Customers.rpt");
try
{
ReportDocument rpt1 = new ReportDocument();
CrystalDecisions.Web.Report rpt_web = new CrystalDecisions.Web.Report();
CrystalDecisions.Web.Parameter par_date = new CrystalDecisions.Web.Parameter();
par_date.Name = "AsOfDate";
par_date.DefaultValue = txt_date.Text;
rpt_web.Parameters.Add(par_date);
CrystalReportSource1.Report.Parameters.Add(par_date);
rpt1.Load(foldername);
rpt_web.FileName = foldername;
CrystalReportSource1.Report = rpt_web;
CrystalReportViewer1.ReportSource = CrystalReportSource1;
TableLogOnInfos tblinfos = new TableLogOnInfos();
TableLogOnInfo tblinfo = new TableLogOnInfo();
tblinfo.ConnectionInfo.DatabaseName = "ARMS";
tblinfo.ConnectionInfo.UserID = "sa";
tblinfo.ConnectionInfo.Password = "******";
tblinfo.ConnectionInfo.ServerName = "192.168.10.13";
tblinfo.ConnectionInfo.IntegratedSecurity = false;
tblinfo.ConnectionInfo.AllowCustomConnection = true;
CrystalReportViewer1.LogOnInfo.Add(tblinfo);
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
}
}
void ThongKeHoaDON()
{
sqlCnn = new SqlConnection(@"Data Source=DESKTOP-899GKH5\THEHAPROK;Initial Catalog=phukiennu;Integrated Security=True");
try
{
dt = new DataTable();
sqlCnn.Open();
sqlAdap = new SqlDataAdapter("select ct.*,h.Email from HOADON h join CHITIETDH ct on h.MaHD=ct.MaHD", sqlCnn);
sqlAdap.Fill(dt);
Report report = new Report();
report.SetDataSource(dt);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
Response.Write("<script>alert('Bi loi du lieu. " + ex.Message + "')</script>");
}
finally
{
sqlCnn.Close();
sqlAdap.Dispose();
}
}
protected void ButtonSearch_Click(object sender, ImageClickEventArgs e)
{
string collStatusSrchCond = "";
string instTypeSrchCond = "";
string productTypeSrchCond = "";
PrepareSrchCond(ref collStatusSrchCond, ref instTypeSrchCond, ref productTypeSrchCond);
_crystalReport = new ReportDocument();
_crystalReport.Load(Server.MapPath("~/Reports/rptCollectionStatment.rpt"));
int userTypeId = int.Parse(Session[MtbBillCollection.Global.Definitions.SessionVariable.Value.UserTypeId].ToString());
string branchCode = (String)Session[MtbBillCollection.Global.Definitions.SessionVariable.Value.BranchCode];
DataTable rptTable = _bilCollectionManager.GetCollectionList(Convert.ToInt32(ClientList.SelectedValue),
txtFromDate.Text, txtToDate.Text, collStatusSrchCond, instTypeSrchCond, productTypeSrchCond, userTypeId, branchCode);
//this.programmaticModalPopup.Show();
rptTable.TableName = "CollectionStatement";
if (rptTable.Rows.Count == 0)
{
ShowPopup(true, true, "No data available.");
CrystalReportViewer1.ReportSource = null;
CrystalReportViewer1.RefreshReport();
}
else
{
_crystalReport.SetDataSource(rptTable);
Session.Add(MtbBillCollection.Global.Definitions.SessionVariable.Value.CollectionReport, rptTable);
CrystalReportViewer1.ReportSource = _crystalReport;
CrystalReportViewer1.RefreshReport();
}
}
protected void btnReport_Click1(object sender, EventArgs e)
{
DataTable dt = new DataTable();
MyClass my = new MyClass();
string fld = DropDownList1.SelectedValue.ToString();
string txt = TextBox1.Text.Trim();
if (TextBox2.Text == "" && TextBox3.Text == "")
{
sql = "select * from VBook_Issue_Return where " + fld + " like '" + txt + "%' ";
}
else
{
DateTime sdate = DateTime.Parse(TextBox2.Text);
DateTime edate = DateTime.Parse(TextBox3.Text);
sql = "select *from VBook_Issue_Return where Issue_dt >='" + sdate + "' and Issue_dt <='" + edate + "' ";
}
dt = my.GetTable(sql);
report1.SetDataSource(dt);
CrystalReportViewer1.ReportSource = report1;
CrystalReportViewer1.RefreshReport();
}
private void query()
{
string tempsql = "";
string sql = "";
string PCS1 = this.QUERY_PCS1.Text;
string CARTON1 = this.QUERY_CARTNO1.Text;
string PCS2 = this.QUERY_PCS2.Text;
string CARTON2 = this.QUERY_CARTNO2.Text;
if (QUERY_PCS1.Text.Trim() == "")
{
PCS1 = "0";
}
if (QUERY_CARTNO1.Text.Trim() == "")
{
CARTON1 = "0";
}
if (QUERY_PCS2.Text.Trim() == "")
{
PCS2 = "0";
}
if (QUERY_CARTNO2.Text.Trim() == "")
{
CARTON2 = "0";
}
try
{
sql = "declare @tab table ( " +
"loc nvarchar(200), " +
"c_po nvarchar(50), " +
"dwg nvarchar(50), " +
"component nvarchar(70), " +
"qty float, " +
"purpose2 nvarchar(100), " +
"customer nvarchar(50), " +
"c_demand2 datetime, " +
"NUM int) " +
"insert @tab " +
"select loc,c_po,dwg,component,'" + PCS1.ToString() + "',purpose2,customer,c_demand2 ," + CARTON1.ToString() + " " +
"from" + db1 + "shipment_head a left join" + db1 + "shipment_body b on a.id=b.aid " +
"where id='" + Request.QueryString["id"] + "' and item='" + this.QUERY_ITEM.Text.ToString() + "' " +
"union all " +
"select loc,c_po,dwg,component,'" + PCS2.ToString() + "',purpose2,customer,c_demand2 ," + CARTON2.ToString() + " " +
"from" + db1 + "shipment_head a left join" + db1 + "shipment_body b on a.id=b.aid " +
"where id='" + Request.QueryString["id"] + "' and item='" + this.QUERY_ITEM.Text.ToString() + "'; " +
"WITH CTE AS ( " +
"select loc,c_po,dwg,component,qty,purpose2,customer,c_demand2 ,NUM=NUM-1 " +
"FROM @TAB " +
"WHERE NUM>0 " +
"UNION ALL " +
"select loc,c_po,dwg,component,qty,purpose2,customer,c_demand2 ,NUM=NUM-1 " +
"FROM CTE " +
"WHERE NUM>0 ) " +
"SELECT loc,c_po,dwg,component,qty,purpose2,customer,CONVERT(varchar(12) , c_demand2, 111 ) c_demand2,'" + this.QUERY_Attn.Text + "' attn FROM CTE " +
"ORDER BY qty desc ";
tempsql = sql;
if (this.CurrentUser.LogonID.ToString().ToUpper().Contains("A3409158-8CFA-4857-9B54-1962306E0010"))
{
Response.Write(sql);
}
string DBConfig_sql = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["EIPAConnectionString"].ConnectionString;
DataSet ds = new DataSet();
SqlConnection sqlCon = new SqlConnection(DBConfig_sql);
SqlDataAdapter sqlAd = new SqlDataAdapter();
SqlCommand sqlCmd = new SqlCommand(sql, sqlCon);
sqlAd.SelectCommand = sqlCmd;
sqlAd.Fill(ds, "sql");
myReportDocument.Load(Server.MapPath("shipment_print.rpt"));
myReportDocument.SetDataSource(ds.Tables["sql"]);
CrystalReportViewer1.ReportSource = myReportDocument;
CrystalReportViewer1.RefreshReport();
}
catch (SqlException ex)
{
Response.Write(sql + "<br>");
Response.Write(ex.Message + "<br>");
throw ex;
}
catch (Exception ex)
{
Response.Write(ex.Message + "<br>");
throw ex;
}
}
private void PrintReport()
{
DateTime From = Convert.ToDateTime(datepickerfrom.Text);
DateTime To = Convert.ToDateTime(datepickerTo.Text);
string User = Session["username"].ToString();
if (Session["access"].ToString() == "4")
{
//dsCustomers = GetData("SELECT FI_ClientCode as Client,convert(varchar, ReceivedDate, 105) as ReceivedDate,UserName,count (FI_OriginalName) as TotalInvoicesProcessed,count(IND_Error) as Error,SUM([Duplicate]) as Duplicate, SUM([IP_Issue]) as Issue, SUM([EDI]) as EDI, SUM([DNP]) as DNP, SUM([Expedite]) as Expedite " +
// "FROM (select FI_ClientCode,IND_IP_Processed_By as UserName,cast(FI_CreatedOn as date ) as ReceivedDate,FI_OriginalName,IND_Error,IND_Status from dbo.EMSDB_FileInfo left join dbo.EMSDB_InvDetails " +
// "on IND_FI=FI_ID where cast(FI_CreatedOn as date ) >='" + From + "' and cast(FI_CreatedOn as date ) <='" + To + "' and IND_IP_Processed_By !='null'and IND_IP_Processed_By ='" + User + "') as s PIVOT (count(IND_Status) for IND_Status in ([Duplicate],[IP_Issue],[EDI],[DNP],[Expedite]) )AS pvt " +
// "group by ReceivedDate,FI_ClientCode,UserName");
dsCustomers = GetData("SELECT Client,RecieveDate,ProcessedDate,UserName,AssignedInvoices,ProcessedInvoice,CompletedInvoice,Duplicate,Issue,EDI,DNP,Expedite,Statement,Error,(AssignedInvoices-CompletedInvoice) as pending,case when Error<>0 then (CAST(ProcessedInvoice as float)/CAST(Error as float)*100.0) else 100 END as Accuracy FROM(SELECT FI_ClientCode as Client,convert(varchar, RecieveDate, 105) as RecieveDate,convert(varchar, ProcessedDate, 105) as ProcessedDate,UserName, count (IND_FI) as AssignedInvoices,sum([QC_Inp]+[QC_Idle]+[QC_Comp]+[QC_Asg]+[Duplicate]+[IP_Issue]+[EDI]+[DNP]+[Expedite])CompletedInvoice,SUM([QC_Inp]+[QC_Idle]+[QC_Comp]+[QC_Asg]) as ProcessedInvoice, SUM([Duplicate]) as Duplicate, SUM([IP_Issue]) as Issue, SUM([EDI]) as EDI, " +
" SUM([DNP]) as DNP, SUM([Expedite]) as Expedite,sum([Statement]) as Statement, count(IND_Error) as Error FROM ( select IND_FI, FI_ClientCode,IND_IP_Processed_By as UserName,cast(FI_CreatedOn as date ) as RecieveDate, cast(FI_CreatedOn as date ) as ProcessedDate, FI_OriginalName,IND_Status,IND_Error from dbo.EMSDB_FileInfo left join dbo.EMSDB_InvDetails on IND_FI=FI_ID where cast(FI_CreatedOn as date ) >='" + From + "' and cast(FI_CreatedOn as date ) <='" + To + "' and FI_Source not in ('EDI') and IND_IP_Processed_By !='null' and " +
" IND_IP_Processed_By ='" + User + "') as s PIVOT (count(IND_Status) for IND_Status in ([IP_Inp],[QC_Inp],[QC_Idle],[QC_Comp],[QC_Asg],[Duplicate],[IP_Issue],[EDI],[DNP],[Expedite],[Statement]))AS pvt group by ProcessedDate,RecieveDate,FI_ClientCode,UserName)a");
}
else
{
dsCustomers = GetData("SELECT Client,RecieveDate,ProcessedDate,UserName,AssignedInvoices,ProcessedInvoice,CompletedInvoice,Duplicate,Issue,EDI,DNP,Expedite,Statement,Error,(AssignedInvoices-CompletedInvoice) as pending,case when Error<>0 then (CAST(ProcessedInvoice as float)/CAST(Error as float)*100.0) else 100 END as Accuracy FROM(SELECT FI_ClientCode as Client,convert(varchar, RecieveDate, 105) as RecieveDate,convert(varchar, ProcessedDate, 105) as ProcessedDate,UserName, count (IND_FI) as AssignedInvoices,sum([QC_Inp]+[QC_Idle]+[QC_Comp]+[QC_Asg]+[Duplicate]+[IP_Issue]+[EDI]+[DNP]+[Expedite])CompletedInvoice,SUM([QC_Inp]+[QC_Idle]+[QC_Comp]+[QC_Asg]) as ProcessedInvoice, SUM([Duplicate]) as Duplicate, SUM([IP_Issue]) as Issue, SUM([EDI]) as EDI, " +
" SUM([DNP]) as DNP, SUM([Expedite]) as Expedite,sum([Statement]) as Statement, count(IND_Error) as Error FROM ( select IND_FI, FI_ClientCode,IND_IP_Processed_By as UserName,cast(FI_CreatedOn as date ) as RecieveDate, cast(FI_CreatedOn as date ) as ProcessedDate, FI_OriginalName,IND_Status,IND_Error from dbo.EMSDB_FileInfo left join dbo.EMSDB_InvDetails on IND_FI=FI_ID where cast(FI_CreatedOn as date ) >='" + From + "' and cast(FI_CreatedOn as date ) <='" + To + "' and FI_Source not in ('EDI') and IND_IP_Processed_By !='null' " +
") as s PIVOT (count(IND_Status) for IND_Status in ([IP_Inp],[QC_Inp],[QC_Idle],[QC_Comp],[QC_Asg],[Duplicate],[IP_Issue],[EDI],[DNP],[Expedite],[Statement]))AS pvt group by ProcessedDate,RecieveDate,FI_ClientCode,UserName)a");
}
if (dsCustomers.Rows.Count < 1)
{
lblmsg.Visible = true;
return;
}
lblmsg.Visible = false;
// string strScript;
Session["IpUserReportDocument"] = dsCustomers;
//CustomerReport.SetParameterValue("From", From);
//CustomerReport.SetParameterValue("To", To);
if (Session["access"].ToString() == "4")
{
CustomerReport.Load(Server.MapPath(Request.ApplicationPath + "/CR_InvoiceUserReport.rpt"));
CustomerReport.SetDatabaseLogon(dbcls.DatabaseUserID(), dbcls.DatabasePwd(), dbcls.DataSource(), dbcls.DatabaseName());
CustomerReport.SetDataSource(dsCustomers);
CustomerReport.SetParameterValue("From", From.ToString("yyyy-MM-dd"));
CustomerReport.SetParameterValue("To", To.ToString("yyyy-MM-dd"));
CustomerReport.SetParameterValue("User", User);
}
else
{
CustomerReport.Load(Server.MapPath(Request.ApplicationPath + "/CR_InvoiceAdminReport.rpt"));
CustomerReport.SetDatabaseLogon(dbcls.DatabaseUserID(), dbcls.DatabasePwd(), dbcls.DataSource(), dbcls.DatabaseName());
CustomerReport.SetDataSource(dsCustomers);
CustomerReport.SetParameterValue("From", From.ToString("yyyy-MM-dd"));
CustomerReport.SetParameterValue("To", To.ToString("yyyy-MM-dd"));
}
CrystalReportViewer1.ReportSource = CustomerReport;
//CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}