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 convert(varchar, FI_ReceiptDate, 105) as FI_ReceiptDate, IND_IP_Processed_By as ProcessedBy, CAST(Log_NewValue AS VARCHAR(40)) as InvoiceNumber,CAST(DateDiff(MI, Invoice_processing_start_time, process_end_time)/60 AS varchar)+':'+Cast(DateDiff(MI, Invoice_processing_start_time, process_end_time)%60 AS varchar)+':'+cast(DateDiff(s, Invoice_processing_start_time, process_end_time)%60 AS varchar) as TotalTime " +
// "FROM (SELECT * FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By,Log_ModifiedOn as 'Invoice_processing_start_time',Log_NewValue , Log_OldValue,IND_ID FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By, IND_ID FROM (SELECT FI_ReceiptDate,FI_ID FROM dbo.EMSDB_FileInfo " +
// "where cast(FI_ReceiptDate as date ) >='" + From + "' and cast(FI_ReceiptDate as date ) <='" + To + "')a Left Join EMSDB_InvDetails ON IND_FI=FI_ID where IND_IP_Processed_By='" + User + "' )b left join dbo.EMSDB_Log ON Log_FileId=IND_ID WHERE Log_Field='InvoiceNo')c LEFT JOIN (SELECT Log_FileId, max(Log_ModifiedOn) AS 'process_end_time' FROM dbo.EMSDB_Log " +
// "WHERE Log_NewValue = 'QC_Idle' GROUP BY Log_FileId ) d ON IND_ID=Log_FileId)aaa ");
dsCustomers = GetData(" SELECT convert(varchar, FI_ReceiptDate, 105) as FI_ReceiptDate, FI_ClientCode, IND_IP_Processed_By, Log_NewValue, CAST(DateDiff(MI, Invoice_processing_start_time, process_end_time)/60 AS varchar) +':'+Cast(DateDiff(MI, Invoice_processing_start_time, process_end_time)%60 AS varchar) +':'+cast(DateDiff(s, Invoice_processing_start_time, process_end_time)%60 AS varchar)as TotalTime FROM (SELECT * FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By,Log_ModifiedOn as 'Invoice_processing_start_time',Log_NewValue , Log_OldValue,IND_ID,IND_FI,FI_ClientCode FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By, IND_ID,IND_FI,FI_ClientCode FROM (SELECT FI_ReceiptDate,FI_ID,FI_ClientCode FROM dbo.EMSDB_FileInfo where (FI_ReceiptDate >= '" + From + "' and FI_ReceiptDate <= '" + To + "' ) )a Left Join EMSDB_InvDetails ON IND_FI=FI_ID )b left join dbo.EMSDB_Log ON Log_FileId=IND_FI Where Log_ModifiedOn is not null and IND_IP_Processed_By ='" + User + "'" +
"and Log_Field='InvoiceNo')c LEFT JOIN (SELECT Log_FileId, max(Log_ModifiedOn) AS 'process_end_time' FROM dbo.EMSDB_Log WHERE (Log_NewValue = 'QC_Idle' or Log_NewValue='IP_Issue') GROUP BY Log_FileId ) d ON IND_FI=Log_FileId)aaa ");
}
else
{
dsCustomers = GetData(" SELECT convert(varchar, FI_ReceiptDate, 105) as FI_ReceiptDate, FI_ClientCode, IND_IP_Processed_By, Log_NewValue, CAST(DateDiff(MI, Invoice_processing_start_time, process_end_time)/60 AS varchar) +':'+Cast(DateDiff(MI, Invoice_processing_start_time, process_end_time)%60 AS varchar) +':'+cast(DateDiff(s, Invoice_processing_start_time, process_end_time)%60 AS varchar)as TotalTime FROM (SELECT * FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By,Log_ModifiedOn as 'Invoice_processing_start_time',Log_NewValue , Log_OldValue,IND_ID,IND_FI,FI_ClientCode FROM (SELECT FI_ReceiptDate, IND_IP_Processed_By, IND_ID,IND_FI,FI_ClientCode FROM (SELECT FI_ReceiptDate,FI_ID,FI_ClientCode FROM dbo.EMSDB_FileInfo where (FI_ReceiptDate >= '" + From + "' and FI_ReceiptDate <= '" + To + "' ) )a Left Join EMSDB_InvDetails ON IND_FI=FI_ID )b left join dbo.EMSDB_Log ON Log_FileId=IND_FI Where Log_ModifiedOn is not null " +
"and Log_Field='InvoiceNo')c LEFT JOIN (SELECT Log_FileId, max(Log_ModifiedOn) AS 'process_end_time' FROM dbo.EMSDB_Log WHERE (Log_NewValue = 'QC_Idle' or Log_NewValue='IP_Issue') GROUP BY Log_FileId ) d ON IND_FI=Log_FileId)aaa ");
}
if (dsCustomers.Rows.Count < 1)
{
lblmsg.Visible = true;
return;
}
lblmsg.Visible = false;
// string strScript;
Session["IpDetailReportDocument"] = dsCustomers;
if (Session["access"].ToString() == "4")
{
CustomerReport.Load(Server.MapPath(Request.ApplicationPath + "/CR_DetailedUserReport.rpt"));
CustomerReport.SetDatabaseLogon(dbcls.DatabaseUserID(), dbcls.DatabasePwd(), dbcls.DataSource(), dbcls.DatabaseName());
CustomerReport.SetDataSource(dsCustomers);
CustomerReport.SetParameterValue("From", From);
CustomerReport.SetParameterValue("To", To);
CustomerReport.SetParameterValue("User", User);
}
else
{
CustomerReport.Load(Server.MapPath(Request.ApplicationPath + "/Test.rpt"));
CustomerReport.SetDatabaseLogon(dbcls.DatabaseUserID(), dbcls.DatabasePwd(), dbcls.DataSource(), dbcls.DatabaseName());
CustomerReport.SetDataSource(dsCustomers);
CustomerReport.SetParameterValue("From", From);
CustomerReport.SetParameterValue("To", To);
}
//CrystalReportViewer1.ParameterFieldInfo.Clear();
CrystalReportViewer1.ReportSource = CustomerReport;
//CrystalReportViewer1.RefreshReport();
}
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();
}
private void PrintReport()
{
Session["Count"] = "1";
DateTime From = Convert.ToDateTime(datepickerfrom.Text);
DateTime To = Convert.ToDateTime(datepickerTo.Text);
// dsCustomers = GetData("select Client,convert(varchar, ReceivedDate, 105) as ReceivedDate, Source,TotalInvoicesReceived,(TotalInvoicesReceived - (Invoicesassigned + QCInprogress +InvoiceInProgress+QCCompleted+QCAssigned+Duplicate+Issue+EDI+DNP)) as UnAssigned,Invoicesassigned ,QCUnassinged, QCInprogress,InvoiceInProgress, QCCompleted,QCAssigned,Duplicate,Issue,EDI,DNP,Expedite from (select FI_ClientCode as Client, cast(FI_CreatedOn as date )as ReceivedDate,FI_Source as Source,count (FI_OriginalName) as TotalInvoicesReceived,sum([IP_Asg]) as Invoicesassigned,sum ([QC_Idle]) as QCUnassinged, sum([QC_Inp])as QCInprogress,sum([IP_Inp])InvoiceInProgress,sum([QC_Comp])as QCCompleted,sum([QC_Asg])as QCAssigned,sum([Duplicate])as Duplicate,sum([IP_Issue])as Issue, SUM([EDI]) as EDI, SUM([DNP]) as DNP, SUM([Expedite]) as Expedite from (select * from (select FI_ClientCode,FI_Source,FI_OriginalName,FI_CreatedOn ,IND_Status,IND_FI from dbo.EMSDB_FileInfo left join dbo.EMSDB_InvDetails on IND_FI=FI_ID ) src pivot (count(IND_Status) for IND_Status in ([IP_Asg],[QC_Inp],[QC_Idle],[IP_Inp],[QC_Comp],[QC_Asg],[Duplicate],[IP_Issue],[EDI],[DNP],[Expedite]) ) piv) aaa where cast(FI_CreatedOn as date ) >='" + From + "' and cast(FI_CreatedOn as date ) <='" + To + "' group by FI_ClientCode, cast(FI_CreatedOn as date ),FI_Source )a order by Client");
//dsCustomers = GetData("select Client,convert(varchar, ReceivedDate, 105) as ReceivedDate, Source,(TotalInvoicesReceived - (Invoicesassigned+InvoiceInProgress+QCUnassinged+QCAssigned+QCInprogress+QCCompleted+Duplicate+Issue+EDI+DNP+Expedite)) as invoiceUnassigned,(QCUnassinged+QCAssigned+QCInprogress+QCCompleted) as InvoiceCompleted,(QCUnassinged+QCAssigned+QCInprogress+QCCompleted) - (QCAssigned+QCInprogress+QCCompleted) as QCUnassigned,QCAssigned,QCInprogress,QCCompleted,Duplicate,Issue,EDI,DNP,Expedite from (select FI_ClientCode as Client, cast(FI_CreatedOn as date )as ReceivedDate,FI_Source as Source, " +
// "count (FI_OriginalName) as TotalInvoicesReceived,sum([IP_Asg]) as Invoicesassigned,sum([IP_Inp])InvoiceInProgress,sum ([QC_Idle]) as QCUnassinged,sum([QC_Asg])as QCAssigned,sum([QC_Inp])as QCInprogress,sum([QC_Comp])as QCCompleted,sum([Duplicate])as Duplicate,sum([IP_Issue])as Issue, SUM([EDI]) as EDI, SUM([DNP]) as DNP, SUM([Expedite]) as Expedite from (select * from (select FI_ClientCode,FI_Source,FI_OriginalName,FI_CreatedOn ,IND_Status,IND_FI from dbo.EMSDB_FileInfo " +
// " left join dbo.EMSDB_InvDetails on IND_FI=FI_ID ) src pivot (count(IND_Status) for IND_Status in ([IP_Asg],[QC_Inp],[QC_Idle],[IP_Inp],[QC_Comp],[QC_Asg],[Duplicate],[IP_Issue],[EDI],[DNP],[Expedite]) ) piv) aaa where cast(FI_CreatedOn as date ) >='"+From+"' and cast(FI_CreatedOn as date ) <='"+To+"'group by FI_ClientCode, cast(FI_CreatedOn as date ) ,FI_Source )a order by Client");
dsCustomers = GetData("select Client,convert(varchar, ReceivedDate, 105) as ReceivedDate, Source,TotalInvoicesReceived,(TotalInvoicesReceived - (Invoicesassigned+InvoiceInProgress+QCUnassinged+QCAssigned+QCInprogress+QCCompleted+Duplicate+Issue+EDI+DNP+Expedite+Statement)) as invoiceUnassigned,InvoiceInProgress,(QCUnassinged+QCAssigned+QCInprogress+QCCompleted) as InvoiceCompleted,(QCUnassinged+QCAssigned+QCInprogress+QCCompleted) - (QCAssigned+QCInprogress+QCCompleted) as QCUnassigned,QCAssigned,QCInprogress,QCCompleted,Duplicate,Issue,EDI,DNP,Expedite,Statement from (select FI_ClientCode as Client, cast(FI_CreatedOn as date )as ReceivedDate,FI_Source as Source,count (FI_OriginalName) as TotalInvoicesReceived,sum([IP_Asg]) as Invoicesassigned,sum([IP_Inp])InvoiceInProgress,sum ([QC_Idle]) as QCUnassinged,sum([QC_Asg])as QCAssigned,sum([QC_Inp])as QCInprogress,sum([QC_Comp])as QCCompleted,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 from (select * from (select FI_ClientCode,FI_Source,FI_OriginalName,FI_CreatedOn ,IND_Status,IND_FI from dbo.EMSDB_FileInfo left join dbo.EMSDB_InvDetails on IND_FI=FI_ID ) src pivot (count(IND_Status) for IND_Status in ([IP_Asg],[QC_Inp],[QC_Idle],[IP_Inp],[QC_Comp],[QC_Asg],[Duplicate],[IP_Issue],[EDI],[DNP],[Expedite],[Statement]) ) piv) aaa where cast(FI_CreatedOn as date ) >='" + From + "' and cast(FI_CreatedOn as date ) <='" + To + "' and FI_Source !='EDI' group by FI_ClientCode, cast(FI_CreatedOn as date ) ,FI_Source )a order by Client");
if (dsCustomers.Rows.Count < 1)
{
// ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Select field", "alert('No Records Found.')", true);
lblmsg.Visible = true;
return;
}
lblmsg.Visible = false;
// string strScript;
Session["ReportDocument"] = dsCustomers;
// strScript = "<script language=javascript>window.open('SampleReport.aspx')</script>";
//ClientScript.RegisterClientScriptBlock(this.GetType(), "strScript", strScript);
CustomerReport.Load(Server.MapPath(Request.ApplicationPath + "/CR_SummaryReport.rpt"));
CustomerReport.SetDatabaseLogon(dbcls.DatabaseUserID(), dbcls.DatabasePwd(), dbcls.DataSource(), dbcls.DatabaseName());
CustomerReport.SetDataSource(dsCustomers);
CustomerReport.SetParameterValue("From", From);
CustomerReport.SetParameterValue("To", To);
CrystalReportViewer1.ReportSource = CustomerReport;
//CrystalReportViewer1.DataBind();
CrystalReportViewer1.RefreshReport();
}