//----------------------------End Report parameters declare---------------------------------------------
public R_RM_Used()
{
InitializeComponent();
Load += (s, e) =>
{
//XtraMessageBox.Show("Path : " + Path);
if (RptType == "D")
{
dt_RMUsed = RMB.RMUsed_Report(Prefix_RM);
if (dt_RMUsed.Rows.Count > 0)
{
dt_RMUsed.WriteXml(Path + "/Xml/dt_RMUsed.xml", System.Data.XmlWriteMode.IgnoreSchema);
rpt.Load(Path + "/RPT/Rpt_RMUsed.rpt");
}
}
else
{
dt_RMUsed = RMB.RMUsed_Report_Simple(Prefix_RM);
//XtraMessageBox.Show(dt_RMUsed.Rows.Count.ToString());
if (dt_RMUsed.Rows.Count > 0)
{
dt_RMUsed.WriteXml(Path + "/Xml/dt_RMUsed_Simple.xml", System.Data.XmlWriteMode.IgnoreSchema);
rpt.Load(Path + "/RPT/Rpt_RMUsed_Simple.rpt");
}
}
crvReport.ReportSource = rpt;
};
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
}
private void Retive_Rep_open01()
{
try
{
string strPath = "~/reports/" + ViewState["report_code"].ToString() + ".rpt";
oRpt.Load(Server.MapPath(strPath));
TableLogOnInfo logOnInfo = new TableLogOnInfo();
TableLogOnInfos tableLogOnInfos = new TableLogOnInfos();
string strUsername = Session["username"].ToString();
string strCompanyname = ViewState["companyname"].ToString();
string strServername = System.Configuration.ConfigurationSettings.AppSettings["servername"];
string strDbname = System.Configuration.ConfigurationSettings.AppSettings["dbname"];
string strDbuser = System.Configuration.ConfigurationSettings.AppSettings["dbuser"];
string strDbpassword = System.Configuration.ConfigurationSettings.AppSettings["dbpassword"];
this.Title = ViewState["report_title"].ToString();
logOnInfo.ConnectionInfo.ServerName = strServername;
logOnInfo.ConnectionInfo.DatabaseName = strDbname;
logOnInfo.ConnectionInfo.UserID = strDbuser;
logOnInfo.ConnectionInfo.Password = strDbpassword;
tableLogOnInfos.Add(logOnInfo);
oRpt.SetParameterValue("@vc_criteria", ViewState["criteria"].ToString());
oRpt.SetParameterValue("UserName", strUsername);
oRpt.SetParameterValue("CompanyName", ViewState["companyname"].ToString());
oRpt.SetParameterValue("OpenToDesc", ViewState["open_to_desc"].ToString());
CrystalReportViewer1.LogOnInfo = tableLogOnInfos;
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
update_Renamed();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rsCompany = modRecordSet.getRS(ref "SELECT * FROM Company");
if (this.chkChannel.CheckState)
{
Report.Load("cryPriceList.rpt");
sql = "SELECT Pricelist.Pricelist_Name, StockItem.StockItem_Name, Max(codCase.CatalogueChannelLnk_Price) AS codCase, Max(codCase.CatalogueChannelLnk_Quantity) AS codQuantity, codSingle.CatalogueChannelLnk_Price AS codSingle, Max(deliveryCase.CatalogueChannelLnk_Quantity) AS delQuantity, Max(deliveryCase.CatalogueChannelLnk_Price) AS delCase, deliverySingle.CatalogueChannelLnk_Price AS delSingle";
sql = sql + " FROM ShrinkItem INNER JOIN";
sql = sql + " (CatalogueChannelLnk AS deliverySingle INNER JOIN (CatalogueChannelLnk AS deliveryCase INNER JOIN ((CatalogueChannelLnk AS codCase INNER JOIN (StockItem INNER JOIN (PricelistStockItemLnk INNER JOIN Pricelist ON PricelistStockItemLnk.PricelistStockitemLnk_PricelistID = Pricelist.PricelistID) ON StockItem.StockItemID = PricelistStockItemLnk.PricelistStockitemLnk_StockitemID) ON (codCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_CODChannelID) AND (codCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID)) INNER JOIN CatalogueChannelLnk AS codSingle ON (StockItem.StockItemID = codSingle.CatalogueChannelLnk_StockItemID) AND (Pricelist.Pricelist_CODChannelID = codSingle.CatalogueChannelLnk_ChannelID)) ON (deliveryCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID) AND (deliveryCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_DeliveryChannelID)) ON (deliverySingle.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_DeliveryChannelID)";
sql = sql + " AND (deliverySingle.CatalogueChannelLnk_StockItemID = StockItem.StockItemID)) ON (ShrinkItem.ShrinkItem_Quantity = codCase.CatalogueChannelLnk_Quantity) AND (ShrinkItem.ShrinkItem_Quantity = deliveryCase.CatalogueChannelLnk_Quantity) AND (ShrinkItem.ShrinkItem_ShrinkID = StockItem.StockItem_ShrinkID)";
sql = sql + " Where (((codSingle.CatalogueChannelLnk_Quantity) = 1) And ((Pricelist.Pricelist_Disabled) = 0) And ((deliverySingle.CatalogueChannelLnk_Quantity) = 1))";
sql = sql + " GROUP BY Pricelist.Pricelist_Name, StockItem.StockItem_Name, codSingle.CatalogueChannelLnk_Price, deliverySingle.CatalogueChannelLnk_Price";
sql = sql + " ORDER BY Pricelist.Pricelist_Name, StockItem.StockItem_Name;";
}
else
{
Report.Load("cryPriceListSingle.rpt");
sql = "SELECT Pricelist.Pricelist_Name, StockItem.StockItem_Name, Max(codCase.CatalogueChannelLnk_Price) AS codCase, Max(codCase.CatalogueChannelLnk_Quantity) AS codQuantity, codSingle.CatalogueChannelLnk_Price AS codSingle, StockItem.StockItemID ";
sql = sql + "FROM ((CatalogueChannelLnk AS codCase INNER JOIN (StockItem INNER JOIN (PricelistStockItemLnk INNER JOIN Pricelist ON PricelistStockItemLnk.PricelistStockitemLnk_PricelistID = Pricelist.PricelistID) ON StockItem.StockItemID = PricelistStockItemLnk.PricelistStockitemLnk_StockitemID) ON (codCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID) AND (codCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_CODChannelID)) INNER JOIN CatalogueChannelLnk AS codSingle ON (Pricelist.Pricelist_CODChannelID = codSingle.CatalogueChannelLnk_ChannelID) AND (StockItem.StockItemID = codSingle.CatalogueChannelLnk_StockItemID)) INNER JOIN ShrinkItem ON (ShrinkItem.ShrinkItem_Quantity = codCase.CatalogueChannelLnk_Quantity) AND (StockItem.StockItem_ShrinkID = ShrinkItem.ShrinkItem_ShrinkID) ";
sql = sql + "Where (((codSingle.CatalogueChannelLnk_Quantity) = 1) And ((Pricelist.Pricelist_Disabled) = 0)) GROUP BY Pricelist.Pricelist_Name, StockItem.StockItem_Name, codSingle.CatalogueChannelLnk_Price, StockItem.StockItemID ORDER BY Pricelist.Pricelist_Name, StockItem.StockItem_Name;";
}
rs = modRecordSet.getRS(ref sql);
if (rs.BOF | rs.EOF)
{
Interaction.MsgBox("No Price allocated!", MsgBoxStyle.Exclamation, "PRICE LIST");
return;
}
if (this.chkChannel.CheckState)
{
Report.SetParameterValue("Text3", cmbCOD.Text);
Report.SetParameterValue("Text4", cmbDelivery.Text);
}
else
{
Report.SetParameterValue("Text3", cmbCOD.Text);
}
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsCompany);
Report.SetParameterValue("txtCompanyName", rsCompany.Fields("Company_Name"));
My.MyProject.Forms.frmReportShow.Text = "Price List";
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
//----------------------------End Report parameters declare---------------------------------------------
public R_COA()
{
InitializeComponent();
Load += (s, e) =>
{
//XtraMessageBox.Show(Path);
TDCOA = COB.TDCOA_Search(SoCOA);
TDCOA.WriteXml(Path + "/Xml/TDCOA.xml", System.Data.XmlWriteMode.IgnoreSchema);
KQCOA_Phy = COB.KQCOA_Search(SoCOA.ToString(), "Physical");
KQCOA_Phy.WriteXml(Path + "/Xml/KQCOA_Phy.xml", System.Data.XmlWriteMode.IgnoreSchema);
KQCOA_Che = COB.KQCOA_Search(SoCOA.ToString(), "Chemical");
KQCOA_Che.WriteXml(Path + "/Xml/KQCOA_Che.xml", System.Data.XmlWriteMode.IgnoreSchema);
////Export data to datatable
//dt = cyn_.Report_DEPT_CM(textEdit1.Text.ToString().Trim(), dateEdit1.Text, dateEdit2.Text);
////loop via datatable row to XML
//if (dt.Rows.Count > 0)
//{
// val = true;
// dt.WriteXml(Path1 + "/../../Xml/CM.xml", System.Data.XmlWriteMode.IgnoreSchema);
//}
//if (val == true)
//{
//XtraMessageBox.Show("Path :" + Path.ToString());
//Load rpt
if (ReportLanguage == "English")
{
rpt.Load(Path + "/RPT/Rpt_COA_EN.rpt");
}
else
{
rpt.Load(Path + "/RPT/Rpt_COA_VN.rpt");
}
//rpt.Load("C:/CM/Production/Report/Rpt_CM_2_2.rpt");
//rpt.SetDatabaseLogon("netika", "bsvn", "192.168.0.249", "SYNC_NUTRICIEL");
//rpt.SetParameterValue("@FromDate", FrDate);
//rpt.SetParameterValue("@ToDate", ToDate);
//rpt.SetParameterValue("@Keywords", textEdit1.Text);
//rpt.SetParameterValue("@SoPKN", SoPKN);
crvReport.ReportSource = rpt;
//}
//else
// XtraMessageBox.Show("No record can be displayed... ", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
update_Renamed();
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rsCompany = modRecordSet.getRS(ref "SELECT * FROM Company");
if (this.chkChannel.CheckState)
{
Report.Load("cryPriceList.rpt");
sql = "SELECT Pricelist.Pricelist_Name, StockItem.StockItem_Name, Max(codCase.CatalogueChannelLnk_Price) AS codCase, Max(codCase.CatalogueChannelLnk_Quantity) AS codQuantity, codSingle.CatalogueChannelLnk_Price AS codSingle, Max(deliveryCase.CatalogueChannelLnk_Quantity) AS delQuantity, Max(deliveryCase.CatalogueChannelLnk_Price) AS delCase, deliverySingle.CatalogueChannelLnk_Price AS delSingle";
sql = sql + " FROM ShrinkItem INNER JOIN";
sql = sql + " (CatalogueChannelLnk AS deliverySingle INNER JOIN (CatalogueChannelLnk AS deliveryCase INNER JOIN ((CatalogueChannelLnk AS codCase INNER JOIN (StockItem INNER JOIN (PricelistStockItemLnk INNER JOIN Pricelist ON PricelistStockItemLnk.PricelistStockitemLnk_PricelistID = Pricelist.PricelistID) ON StockItem.StockItemID = PricelistStockItemLnk.PricelistStockitemLnk_StockitemID) ON (codCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_CODChannelID) AND (codCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID)) INNER JOIN CatalogueChannelLnk AS codSingle ON (StockItem.StockItemID = codSingle.CatalogueChannelLnk_StockItemID) AND (Pricelist.Pricelist_CODChannelID = codSingle.CatalogueChannelLnk_ChannelID)) ON (deliveryCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID) AND (deliveryCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_DeliveryChannelID)) ON (deliverySingle.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_DeliveryChannelID)";
sql = sql + " AND (deliverySingle.CatalogueChannelLnk_StockItemID = StockItem.StockItemID)) ON (ShrinkItem.ShrinkItem_Quantity = codCase.CatalogueChannelLnk_Quantity) AND (ShrinkItem.ShrinkItem_Quantity = deliveryCase.CatalogueChannelLnk_Quantity) AND (ShrinkItem.ShrinkItem_ShrinkID = StockItem.StockItem_ShrinkID)";
sql = sql + " Where (((codSingle.CatalogueChannelLnk_Quantity) = 1) And ((Pricelist.Pricelist_Disabled) = 0) And ((deliverySingle.CatalogueChannelLnk_Quantity) = 1))";
sql = sql + " GROUP BY Pricelist.Pricelist_Name, StockItem.StockItem_Name, codSingle.CatalogueChannelLnk_Price, deliverySingle.CatalogueChannelLnk_Price";
sql = sql + " ORDER BY Pricelist.Pricelist_Name, StockItem.StockItem_Name;";
}
else
{
Report.Load("cryPriceListSingle.rpt");
sql = "SELECT Pricelist.Pricelist_Name, StockItem.StockItem_Name, Max(codCase.CatalogueChannelLnk_Price) AS codCase, Max(codCase.CatalogueChannelLnk_Quantity) AS codQuantity, codSingle.CatalogueChannelLnk_Price AS codSingle, StockItem.StockItemID ";
sql = sql + "FROM ((CatalogueChannelLnk AS codCase INNER JOIN (StockItem INNER JOIN (PricelistStockItemLnk INNER JOIN Pricelist ON PricelistStockItemLnk.PricelistStockitemLnk_PricelistID = Pricelist.PricelistID) ON StockItem.StockItemID = PricelistStockItemLnk.PricelistStockitemLnk_StockitemID) ON (codCase.CatalogueChannelLnk_StockItemID = StockItem.StockItemID) AND (codCase.CatalogueChannelLnk_ChannelID = Pricelist.Pricelist_CODChannelID)) INNER JOIN CatalogueChannelLnk AS codSingle ON (Pricelist.Pricelist_CODChannelID = codSingle.CatalogueChannelLnk_ChannelID) AND (StockItem.StockItemID = codSingle.CatalogueChannelLnk_StockItemID)) INNER JOIN ShrinkItem ON (ShrinkItem.ShrinkItem_Quantity = codCase.CatalogueChannelLnk_Quantity) AND (StockItem.StockItem_ShrinkID = ShrinkItem.ShrinkItem_ShrinkID) ";
sql = sql + "Where (((codSingle.CatalogueChannelLnk_Quantity) = 1) And ((Pricelist.Pricelist_Disabled) = 0)) GROUP BY Pricelist.Pricelist_Name, StockItem.StockItem_Name, codSingle.CatalogueChannelLnk_Price, StockItem.StockItemID ORDER BY Pricelist.Pricelist_Name, StockItem.StockItem_Name;";
}
rs = modRecordSet.getRS(ref sql);
if (rs.BOF | rs.EOF)
{
Interaction.MsgBox("No Price allocated!", MsgBoxStyle.Exclamation, "PRICE LIST");
return;
}
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsCompany);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = "Price List";
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
/// <summary>
/// 病区病人欠费一览表
/// </summary>
public void m_mthShowInHospitalDebtLog()
{
if (this.m_objViewer.m_txtAREAID_CHR.Tag == null)
{
this.m_objViewer.m_txtAREAID_CHR.Tag = "";
this.m_objViewer.m_txtAREAID_CHR.Text = "全院";
}
this.m_objViewer.Cursor = Cursors.WaitCursor;
DataTable dtbResult = new DataTable();
rptInHospitalLog = new ReportDocument();
rptInHospitalLog.Load(@"Report\rptDebtView.rpt");
rptInHospitalLog.DataDefinition.FormulaFields["AreaName"].Text = "'" + this.m_objViewer.m_txtAREAID_CHR.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["StatDate"].Text = "'" + this.m_objViewer.m_StatDate.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["operatorname"].Text = "'" + this.m_objViewer.LoginInfo.m_strEmpName + "'";
int type = -1;
if (this.m_objViewer.radioButton1.Checked)
{
type = 1;
}
if (this.m_objViewer.radioButton2.Checked)
{
type = 0;
}
long lngRes = objSvc.m_lngGetPatientDebt(type, (string)this.m_objViewer.m_txtAREAID_CHR.Tag, "", this.m_objViewer.m_StatDate.Value, "", "", out dtbResult);
if (lngRes > 0)
{
rptInHospitalLog.SetDataSource(dtbResult);
this.m_objViewer.m_ctrvPatientDebtReport.ReportSource = rptInHospitalLog;
}
this.m_objViewer.Cursor = Cursors.Default;
}
private void rptPDF_Ingresos(String Reporte, object[] Parametros, string NombreReporte)
{
System.Web.UI.Page p = new System.Web.UI.Page();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
try
{
ConnectionInfo connectionInfo = new ConnectionInfo();
p = new System.Web.UI.Page();
report.Load(p.Server.MapPath("~") + "\\" + Reporte);
for (int i = 0; i <= Parametros.Length - 1; i++)
{
report.SetParameterValue(i, Parametros[i]);
}
connectionInfo.ServerName = "dsia";
connectionInfo.UserID = "ingresos";
connectionInfo.Password = "******";
SetDBLogonForReport(connectionInfo, report);
report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, NombreReporte);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
CR_Reportes.Dispose();
GC.Collect();
}
}
private void rptPDFReferencia(String Reporte, String Nombre, String Referencia)
{
System.Web.UI.Page p = new System.Web.UI.Page();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
try
{
// ConnectionInfo connectionInfo = new ConnectionInfo();
//string J = System.AppDomain.CurrentDomain.BaseDirectory + Reporte;
report.Load(p.Server.MapPath("~") + Reporte);
//report.Load(System.AppDomain.CurrentDomain.BaseDirectory + Reporte);
report.SetParameterValue(0, Referencia);
report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "FichaReferenciada-" + Nombre.Substring(0, 15));
}
catch (Exception ex)
{
}
finally
{
CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
CR_Reportes.Dispose();
GC.Collect();
}
}
private void ReportViewer_Load(object sender, EventArgs e)
{
myReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
string strDirPath = Application.StartupPath + "/Reports/" + _repName + ".rpt";
try
{
myReportDocument.Load(strDirPath);
}
catch
{
MessageBox.Show("Нет файла отчета!/n" + strDirPath, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
SetDataReport(_repName);
// @TODO Заменить на общий датасет DataSetReport
//if (_repDBProcName == "Rep_GetReportLast")
//{
// SetDataReportLast();
//}
//else
//{
// SetDataReport();
//}
}
public frmRelBasicos(string pArquivo, string pTitulo)
{
InitializeComponent();
relArquivo = pArquivo;
relTitulo = pTitulo;
this.Text = pTitulo;
//Objeto com informações de conexão para as tabelas do rpt
conexaoCrystal = new CrystalDecisions.Shared.ConnectionInfo();
conexaoCrystal.ServerName = @"(local)\SQLEXPRESS";
conexaoCrystal.UserID = "sa";
conexaoCrystal.Password = "******";
conexaoCrystal.DatabaseName = "Copa2010";
//Carregar o arquivo do rpt pArquivo
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Application.StartupPath + @"..\..\..\Relatorios\" + relArquivo);
//Informe o rpt com as informações de conexão
CrystalDecisions.Shared.TableLogOnInfo logonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
logonInfo.ConnectionInfo = conexaoCrystal;
foreach (CrystalDecisions.CrystalReports.Engine.Table table in report.Database.Tables)
{
table.LogOnInfo.ConnectionInfo = conexaoCrystal;
table.ApplyLogOnInfo(logonInfo);
}
//Liga ao Visualizador
crvRel.ReportSource = report;
}
private void Form1_Load(object sender, EventArgs e)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(@"C:\Users\Willmack\source\repos\WindowsFormsApp1\WindowsFormsApp1\CrystalReport1.rpt");
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.Refresh();
}
private void frmOPLog_Load(object sender, System.EventArgs e)
{
this.strAppPatch = Application.StartupPath + "\\";
this.txtDepartment.m_lsvList.Width = this.txtDepartment.Width;
this.txtDepartment.m_lsvList.Columns[0].Width = this.txtDepartment.Width;
this.txtReportDoctor.m_lsvList.Width = this.txtReportDoctor.Width;
dtpFrom.Value = DateTime.Now;
dtpTo.Value = DateTime.Now;
objReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
objReportDocument.Load(strAppPatch + "Report\\rptOPLog.rpt");
TextObject txtReportTitle = objReportDocument.ReportDefinition.ReportObjects["Text10"] as TextObject;
txtReportTitle.Text = ((clsCtl_OPLog)this.objController).m_objComInfo.m_strGetHospitalTitle() + "门诊工作日志报表";
if (this.chkCheckDate.Checked)
{
((TextObject)objReportDocument.ReportDefinition.ReportObjects["TextDate"]).Text = dtpFrom.Value.ToString("yyyy-MM-dd") + " ~ " + dtpTo.Value.ToString("yyyy-MM-dd");
}
// this.crystalReportViewer1.ReportSource =objReportDocument;
if (strDoctorID != "")
{
((clsCtl_OPLog)this.objController).m_mthLogData();
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryStockItemPrice.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
Report.SetParameterValue("txtFilter", lblHeading.Text);
Report.SetParameterValue("txtTitle1", "Where Sales Channel = " + cmbChannel.CtlText);
//Report.Database.SetDataSource(adoPrimaryRS, 3)
Report.Database.Tables(1).SetDataSource(adoPrimaryRS);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
//----------------------------End Report parameters declare---------------------------------------------
public R_InternalTransfer_FN()
{
InitializeComponent();
Load += (s, e) =>
{
dt_InternalTransfer_Header = internal_TransferTableAdapter.GetDataBy(int.Parse(DocNum));
dt_InternalTransfer_Detail = internal_Transfer_DetailTableAdapter.GetDataBy(int.Parse(DocNum));
//XtraMessageBox.Show("Path : " +Path);
//
//if (dt_InternalTransfer_Header.Rows.Count > 0)
//{
dt_InternalTransfer_Header.WriteXml(Path + "/Xml/dt_InternalTransfer_Header.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_InternalTransfer_Detail.WriteXml(Path + "/Xml/dt_InternalTransfer_Details.xml", System.Data.XmlWriteMode.IgnoreSchema);
//}
//XtraMessageBox.Show("rpt.Load ");
rpt.Load(Path + "/RPT/Rpt_InternalTransfer.rpt");
//XtraMessageBox.Show("crvReport.ReportSource ");
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
/// <summary>
/// 產生PDF檔案
/// </summary>
private void RrintToPDF()
{
// 匯出檔名的設定
string sReportFileName = @"\rpt\T022_rpt.rpt";
string sMapPath = Server.MapPath("");
string sHost = Request.Url.Host;
string sApplicationPath = Request.ApplicationPath;
string reportID = DBCenter.GetSystemID();
string sExportFileName = Server.MapPath("../../") + @"PDF\T022_rpt" + reportID + ".pdf";
string strPath = sMapPath + sReportFileName;
string ReportPath = "http://" + sHost + sApplicationPath + "/PDF/T022_rpt" + reportID + ".pdf";
DataSet dsLot = (DataSet)Session["T022View"];
rptdoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rptdoc.Load(strPath);
rptdoc.SetDataSource(dsLot);
// 設定匯出路徑及檔名
DiskFileDestinationOptions df = new DiskFileDestinationOptions();
df.DiskFileName = sExportFileName;
rptdoc.ExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
rptdoc.ExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
rptdoc.ExportOptions.DestinationOptions = df;
rptdoc.Export();
rptdoc.Dispose();
//打開文件
Response.Write("<script>window.open('" + ReportPath + "','_blank','resizable,scrollbars=no,menubar=no,toolbar=no,location=no,status=no',false);</script> ");
Response.Write("<script>window.close();</script>");
}
//----------------------------End Report parameters declare---------------------------------------------
public R_MYCOTOXIN_RESULT()
{
InitializeComponent();
Load += (s, e) =>
{
dt_MYCOTOXIN_RESULT_Header = BUS2.MYCOTOXIN_RESULT_Header_SELECT(ID);
dt_MYCOTOXIN_RESULT_StandardCurve = BUS.MYCOTOXIN_RESULT_StandardCurve_SELECT(ID, acr);
dt_MYCOTOXIN_RESULT_ACR_Lines = BUS1.MYCOTOXIN_RESULT_Lines_ACR_SELECT(ID, acr);
dt_MYCOTOXIN_RESULT_STD_Lines = BUS1.MYCOTOXIN_RESULT_Lines_STD_SELECT(ID, acr);
dt_MYCOTOXIN_RESULT_StandardCurve_Graph = BUS1.MYCOTOXIN_RESULT_Lines_StandardCurve_Graph(ID, acr);
dt_MYCOTOXIN_RESULT_Lines = BUS1.MYCOTOXIN_RESULT_Lines_SELECT(ID);
dt_MYCOTOXIN_RESULT_Header.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_Header_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_MYCOTOXIN_RESULT_StandardCurve.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_StandardCurve_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_MYCOTOXIN_RESULT_ACR_Lines.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_ACR_Lines_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_MYCOTOXIN_RESULT_STD_Lines.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_STD_Lines_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_MYCOTOXIN_RESULT_StandardCurve_Graph.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_StandardCurve_Graph_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_MYCOTOXIN_RESULT_Lines.WriteXml(XmlPath + "/dt_MYCOTOXIN_RESULT_Lines_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
//rpt.Load(Path + "/RPT/Rpt_MYCOTOXIN_RESULT_LAB.rpt");
//XtraMessageBox.Show(Path);
rpt.Load(Path + "/RPT/_LAB/" + RptName + ".rpt");
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
protected void generarrpt(object sender, EventArgs e)
{
string select = "SELECT MEMP.MEMP_CODIEMPL,MNIT.MNIT_NOMBRES,MNIT.MNIT_NOMBRE2,MNIT.MNIT_APELLIDOS,MNIT.MNIT_APELLIDO2,MNIT.MNIT_DIGITO,MNIT.MNIT_DIRECCION,MNIT.MNIT_EMAIL,MNIT.MNIT_TELEFONO,MNIT.MNIT_CELULAR, MEMP.MEMP_FECHNACI,PCIU.PCIU_NOMBRE,MNIT.MNIT_NIT,MNIT.MNIT_DIGITO,MEMP.MEMP_NUMELIBRMILI,MEMP.MEMP_CLASELIBRMILI,DBXSCHEMA.TSEXO.TSEX_NOMBRE,YEAR(CURRENT DATE) - YEAR(MEMP.MEMP_FECHNACI),MEMP.MEMP_NUMEHIJOS,MEMP.TRES_VIVIENDA,MEMP.MEMP_FECHINGRESO,MEMP.MEMP_SUELACTU,MEMP.MEMP_SUELANTER,TCON.TCON_NOMBRE,TSUBT.TSUB_NOMBRE, PDEPTO.PDEP_NOMBDPTO,TESTEMP.TEST_NOMBRE,DBXSCHEMA.TFORMAPAGO.TFOR_DESCRIPCION,DBXSCHEMA.PBANCO.PBAN_NOMBRE,MEMP.MEMP_CUENNOMI,DBXSCHEMA.PEPS.PEPS_NOMBEPS,DBXSCHEMA.PARP.PARP_NOMBARP,PFCESA.PFON_NOMBFOND,PFPENS.PFON_NOMBPENS,DBXSCHEMA.TRETEFTE.TEST_DESCRIPCION, MEMP.MEMP_PORCRETE,TSANGRE.TTIP_TIPOSANG ,PCAR.pcar_nombcarg,TCIVIL.test_nombre,MEMP.memp_fecsuelanter,memp_peripago,TPAGO.TPER_DESCP,TFPAGO.tfor_descripcion,MEMP.memp_cuennomi,PFPENS2.pfon_nombpens as pensvol";
string from = " FROM DBXSCHEMA.MEMPLEADO MEMP,DBXSCHEMA.MNIT MNIT,DBXSCHEMA.PCIUDAD PCIU,DBXSCHEMA.TSEXO,DBXSCHEMA.TCONTRATONOMINA TCON,DBXSCHEMA.TSUBSIDIOTRANSPORTE TSUBT ,DBXSCHEMA.PDEPARTAMENTOEMPRESA PDEPTO,DBXSCHEMA.TESTADOEMPLEADO TESTEMP, DBXSCHEMA.TFORMAPAGO,DBXSCHEMA.PBANCO,DBXSCHEMA.PEPS,DBXSCHEMA.PARP,DBXSCHEMA.PFONDOPENSION PFPENS,DBXSCHEMA.PFONDOPENSION PFPENS2, DBXSCHEMA.PFONDOCESANTIAS PFCESA,DBXSCHEMA.TRETEFTE,DBXSCHEMA.TTIPOSANGRE TSANGRE,DBXSCHEMA.PCARGOEMPLEADO PCAR,dbxschema.testadocivil TCIVIL,dbxschema.TPERIPAGO TPAGO,DBXSCHEMA.TFORMAPAGO TFPAGO";
string where = " WHERE MEMP.MEMP_CODIEMPL='" + DDLEMPLEADO.SelectedValue + "' AND PFPENS.pfon_codipens=MEMP.pfon_codipens AND PFPENS2.pfon_codipens=MEMP.pfon_codipensvolu AND TFPAGO.tfor_pago=memp.memp_formpago AND TPAGO.tper_peri=MEMP.memp_peripago AND TCIVIL.test_estacivil=memp.test_estacivil AND MEMP.PCAR_CODICARGO=PCAR.PCAR_CODICARGO AND MEMP.MNIT_NIT=MNIT.MNIT_NIT and memp.ttip_secuencia= TSANGRE.ttip_secuencia AND MEMP.PCIU_LUGANACI=PCIU.PCIU_CODIGO AND MEMP.TSEX_CODIGO=DBXSCHEMA.TSEXO.TSEX_CODIGO AND MEMP.TCON_CONTRATO= TCON.TCON_CONTRATO AND MEMP.TSUB_CODIGO=TSUBT.TSUB_CODIGO AND MEMP.PDEP_CODIDPTO= PDEPTO.PDEP_CODIDPTO AND MEMP.TEST_ESTADO= TESTEMP.TEST_ESTADO AND MEMP.MEMP_FORMPAGO=DBXSCHEMA.TFORMAPAGO.TFOR_PAGO AND MEMP.PBAN_CODIGO=DBXSCHEMA.PBANCO.PBAN_CODIGO AND MEMP.PEPS_CODIEPS=DBXSCHEMA.PEPS.PEPS_CODIEPS AND MEMP.PARP_CODIARP=DBXSCHEMA.PARP.PARP_CODIARP AND MEMP.PFON_CODICESA=PFCESA.PFON_CODIFOND AND MEMP.MEMP_TESTRETE=DBXSCHEMA.TRETEFTE.TEST_RETE";
string nomEmpleado = DBFunctions.SingleData("SELECT NOMBRE FROM VMNIT VM, MEMPLEADO ME WHERE VM.MNIT_NIT = ME.MNIT_NIT AND ME.MEMP_CODIEMPL = '" + DDLEMPLEADO.SelectedValue + "'");
DataSet ds = new DataSet();
DBFunctions.Request(ds, IncludeSchema.NO, select + from + where + ";select cemp_nombre,cemp_nombcome,mnit_nit from dbxschema.cempresa");
if (ds.Tables[0].Rows.Count > 0)
{
ds.WriteXmlSchema(Path.Combine(Request.PhysicalApplicationPath, "schemas/Nomina.InfyConsultas.rpte_ImpHojaVida.xsd"));
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/Nomina.InfyConsultas.rpte_ImpHojaVida.rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.InfyConsultas.rpte_ImpHojaVida.doc"));
Response.Clear();
Response.ContentType = "application/msword";
Response.AddHeader("Content-Disposition", "attachment; filename= " + nomEmpleado + ".doc");
Response.ContentType = "application/msword";
Response.WriteFile(Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.InfyConsultas.rpte_ImpHojaVida.doc"));
Response.Flush();
Response.Close();
}
else
{
Utils.MostrarAlerta(Response, "Existe un prolema con la consulta, el vendedor no tiene la información completa");
}
}
private void rptPDFAdjunto(String Reporte, String Nombre, String Referencia, String Importe, String Vigencia, String Concepto, String Observaciones)
{
System.Web.UI.Page p = new System.Web.UI.Page();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
try
{
report.Load(p.Server.MapPath("~") + Reporte);
report.SetParameterValue(0, Nombre);
report.SetParameterValue(1, Referencia);
report.SetParameterValue(2, Importe);
report.SetParameterValue(3, Vigencia);
report.SetParameterValue(4, Concepto);
report.SetParameterValue(5, Observaciones);
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
string archivo = p.Server.MapPath("~") + "/ArchivoReferencia/Referencia - " + Referencia + ".PDF";
report.ExportToDisk(ExportFormatType.PortableDocFormat, archivo); // "FichaReferenciada-" + Nombre.Substring(0, 15));
}
catch (Exception ex)
{
lblMensajeCorreo.Text = ex.Message;
}
finally
{
//CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
//CR_Reportes.Dispose();
}
}
public ReportDocument GetReportDocument()
{
try
{
var informe = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
informe.Load("Impresion Factura.rpt");
informe.SetParameterValue("Nombre", nombre);
informe.SetParameterValue("Folio", folio);
informe.SetParameterValue("Año", año);
informe.SetParameterValue("Mes", mes);
informe.SetParameterValue("Dia", dia);
informe.SetParameterValue("Cantidad", cantidad);
informe.SetParameterValue("Ciudad", ciudad);
informe.SetParameterValue("Domicilio", domicilio);
informe.SetParameterValue("IVA", iva);
informe.SetParameterValue("Lugar", lugar);
informe.SetParameterValue("PrecioLetra", precioLetra);
informe.SetParameterValue("PrecioUnitario", precioU);
informe.SetParameterValue("Previsualizacion", previsualisacion);
informe.SetParameterValue("Retencion", retencion);
informe.SetParameterValue("RFC", rfc);
informe.SetParameterValue("Total", total);
informe.SetParameterValue("Importe", importe);
informe.SetParameterValue("Descripcion", descripcion);
return(informe);
}
catch (Exception ex)
{
throw;
}
}
//----------------------------End Report parameters declare---------------------------------------------
public R_OF()
{
InitializeComponent();
Load += (s, e) =>
{
dt_OFHeader = OFB.OF_Report_OFHeader(OF);
dt_OFListBatchDetails = OFB.OF_Report_OFListBatchDetails(OF);
dt_OFListBatchDetailsPREP = OFB.OF_Report_OFListBatchDetails_PREP(OF);
//if (OFB.OF_Report_OFHeader_Visible(OF) == false)
//{
// foreach (DataRow dr in dt_OFHeader.Rows)
// {
// OFB.OF_Report_OFHeader_Insert(dr);
// }
// foreach (DataRow dr in dt_OFListBatchDetails.Rows)
// {
// OFB.OF_Report_OFListBatchDetails_Insert(OF, dr);
// }
//}
if (dt_OFHeader.Rows.Count > 0)
{
dt_OFHeader.WriteXml(Path + "/Xml/dt_OFHeader.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_OFListBatchDetails.WriteXml(Path + "/Xml/dt_OFListBatchDetails.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_OFListBatchDetailsPREP.WriteXml(Path + "/Xml/dt_OFListBatchDetailsPREP.xml", System.Data.XmlWriteMode.IgnoreSchema);
}
rpt.Load(Path + "/RPT/Rpt_OF1.rpt");
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
/// <summary>
/// 病区病人欠费一览表
/// </summary>
public void m_mthShowInHospitalAdviceCharge()
{
if (this.m_objViewer.m_txtAREAID_CHR.Tag == null && this.m_objViewer.radioButton3.Checked)
{
return;
}
if (this.m_objViewer.m_txtPatientName.Text == "")
{
MessageBox.Show("先查询病人");
return;
}
this.m_objViewer.Cursor = Cursors.WaitCursor;
DataTable dtbResult = new DataTable();
rptInHospitalLog = new ReportDocument();
rptInHospitalLog.Load(@"Report\rptPatientChargeInAdvice.rpt");
string groupid = "";
if (this.m_objViewer.m_cboChargeType.SelectedIndex >= 0)
{
if (this.m_objViewer.m_cboChargeType.SelectedIndex == 0)
{
groupid = "";
}
else
{
groupid = ((DataTable)this.m_objViewer.m_cboChargeType.Tag).Rows[this.m_objViewer.m_cboChargeType.SelectedIndex - 1]["groupid_chr"].ToString();
}
}
long lngRes = 0;
if (groupid == "")
{
lngRes = objSvc.m_lngGetPatientDebtDetail(this.m_objViewer.m_dtpStatDate.Value, this.m_objViewer.m_dtpEnd.Value, Registerid, out dtbResult);
}
else
{
string[] types;
lngRes = objSvc.m_lngGetChargeItemTypesByConfigGroupID("0003", groupid, out types);
lngRes = objSvc.m_lngGetPatientDebtDetail(types, this.m_objViewer.m_dtpStatDate.Value, this.m_objViewer.m_dtpEnd.Value, Registerid, out dtbResult);
}
if (lngRes > 0)
{
rptInHospitalLog.SetDataSource(dtbResult);
}
else
{
return;
}
rptInHospitalLog.DataDefinition.FormulaFields["AreaName"].Text = "'" + this.m_objViewer.m_txtAREAID_CHR.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["BedNo"].Text = "'" + this.m_objViewer.m_cboBedNo.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["InhospitalNo"].Text = "'" + this.m_objViewer.m_txtInpatientNo.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["Name"].Text = "'" + this.m_objViewer.m_txtPatientName.Text + "'";
rptInHospitalLog.DataDefinition.FormulaFields["OperatorName"].Text = "'" + this.m_objViewer.LoginInfo.m_strEmpName + "'";
rptInHospitalLog.DataDefinition.FormulaFields["Date"].Text = "'" + this.m_objViewer.m_dtpStatDate.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["EndDate"].Text = "'" + this.m_objViewer.m_dtpEnd.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["ReportName"].Text = "'" + this.ReportName + "'";
this.m_objViewer.m_crvPatientCharheDetail.ReportSource = rptInHospitalLog;
this.m_objViewer.Cursor = Cursors.Default;
}
//----------------------------End Report parameters declare---------------------------------------------
public R_BaoCaoCongNo_LAB()
{
InitializeComponent();
Load += (s, e) =>
{
txtFrDate.Text = FrDate.ToString();
txtToDate.Text = ToDate.ToString();
dt = BUS.BaoCaoCongNo(FrDate, ToDate);
//XtraMessageBox.Show("dt.Rows.Count" + dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
dt.WriteXml(Path + "/Xml/BaoCaoCongNo_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
}
//dt.WriteXml(Path + "/../../Xml/BaocaoPXN_Nhan_TrongTuan_LAB.xml", System.Data.XmlWriteMode.IgnoreSchema);
rpt.Load(Path + "/RPT/Rpt_BaoCaoCongNo_LAB.rpt");
//rpt.Load(Path + "/../../RPT/Rpt_BaoCaoPXN_Nhan_TrongTuan_LAB");
rpt.SetParameterValue("P_FrDate", FrDate);
rpt.SetParameterValue("P_ToDate", ToDate);
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Excel(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Excel));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
private void loadStockSales()
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsStock = default(ADODB.Recordset);
ADODB.Recordset rsGroup = default(ADODB.Recordset);
string sql = null;
ADODB.Recordset rsData = default(ADODB.Recordset);
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("crySalesByShrink.rpt");
ReportNone.Load("cryNoRecords.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading"));
rs.Close();
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1");
if (string.IsNullOrEmpty(rs.Fields("Link_Name").Value))
{
Report.ReportDefinition.Sections("Section2").SectionFormat.EnableSuppress = true;
}
else
{
Report.SetParameterValue("txtFilter", Strings.Replace(rs.Fields("Link_Name").Value, "''", "'"));
}
sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, thejoin.shrink, thejoin.quantity, thejoin.price, thejoin.list, thejoin.actual FROM aStockItem INNER JOIN (SELECT SaleItem.SaleItem_StockItemID AS StockItem, SaleItem.SaleItem_ShrinkQuantity AS shrink, Sum(IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity])) AS quantity, Sum(([SaleItem_Price]*[SaleItem_Quantity])) AS price, Sum((([SaleItem_ListCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS list, Sum((([SaleItem_ActualCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS actual From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_Revoke) = 0)) GROUP BY SaleItem.SaleItem_StockItemID, SaleItem.SaleItem_ShrinkQuantity ) AS thejoin ON aStockItem.StockItemID = thejoin.StockItem ";
sql = sql + rs.Fields("Link_SQL").Value + " ORDER BY aStockItem.StockItem_Name, thejoin.shrink;";
rs = modReport.getRSreport(ref sql);
if (rs.BOF | rs.EOF)
{
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(1).SetDataSource(rs);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
//----------------------------End Report parameters declare---------------------------------------------
public R_OF_Summary()
{
InitializeComponent();
Load += (s, e) =>
{
//XtraMessageBox.Show("Path : " + Path);
dt_OFHeader = OFB.OF_Report_OFHeader(OF);
//dt_OFListBatchs = OFB.OF_Report_OFListBatchs(OF);
dt_OFListBatchDetails = OFB.OF_Report_OFSummary(OF);
dt_OF_Report_OFSummary_PREP = OFB.OF_Report_OFSummary_PREP(OF);
if (dt_OFHeader.Rows.Count > 0)
{
//Khong can chay lai OFHeader vi da in roi
//dt_OFHeader.WriteXml(Path + "/Xml/dt_OFHeader.xml", System.Data.XmlWriteMode.IgnoreSchema);
//dt_OFListBatchs.WriteXml(Path + "/Xml/dt_OFListBatchs.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_OFListBatchDetails.WriteXml(Path + "/Xml/dt_OF_Summary.xml", System.Data.XmlWriteMode.IgnoreSchema);
dt_OF_Report_OFSummary_PREP.WriteXml(Path + "/Xml/dt_OF_Report_OFSummary_PREP.xml", System.Data.XmlWriteMode.IgnoreSchema);
}
rpt.Load(Path + "/RPT/Rpt_OF_Summary.rpt");
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
/// <summary>
///病人出院单统计表
/// </summary>
public void m_mthShowBIHLeftStatistics()
{
this.m_objViewer.Cursor = Cursors.WaitCursor;
DataTable dtbResult = new DataTable();
rptBIHRegisterStatistics = new ReportDocument();
//rptBIHRegisterStatistics.Load(@"Report\rptLeaveHospital.rpt");
rptBIHRegisterStatistics.Load(m_strStartPatch + "Report\\rptLeaveHospital.rpt");
rptBIHRegisterStatistics.DataDefinition.FormulaFields["dtStartTime"].Text = "'" + this.m_objViewer.m_dtpStatDate.Text + "'";
rptBIHRegisterStatistics.DataDefinition.FormulaFields["dtEndTime"].Text = "'" + this.m_objViewer.m_dtpEnd.Text + "'";
//change 2007-4-18
//long lngRes = objSvc.GetPatientLeftStatistics(DateTime.Parse(m_objViewer.m_dtpStatDate.Text + " 00:00:00"), DateTime.Parse(m_objViewer.m_dtpEnd.Text + " 23:59:59"), out dtbResult);
// ---------------------------->
long lngRes = objSvc.GetPatientLeftStatistics(DateTime.Parse(m_objViewer.m_dtpStatDate.Text + " 00:00:00"), DateTime.Parse(m_objViewer.m_dtpEnd.Text + " 23:59:59"), m_objViewer.m_txtProtectType.Value, out dtbResult);
// <------------------------------
if (lngRes > 0)
{
this.rptBIHRegisterStatistics.SetDataSource(dtbResult);
this.m_objViewer.crvBIHRegisterStat.ReportSource = rptBIHRegisterStatistics;
this.m_objViewer.btnPrintRpt.Enabled = true;
}
this.m_objViewer.Cursor = Cursors.Default;
}
private void mmuMailingLabel_Click(object sender, EventArgs e)
{
try
{
if (System.IO.File.Exists(CurrentUser.ReportPath + "rptCustomerMailingLabel1.rpt"))
{
//dtblCustomer.TableName = "CustomerRegister";
//dtblCustomer.WriteXmlSchema(@"D:\report\CustomerRegister.xsd");
//DataView DVReport;
//DVReport = dtblCustomer.DefaultView;
//DVReport.RowFilter = StrFilter;
CrystalDecisions.CrystalReports.Engine.ReportDocument rptDoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rptDoc.Load(CurrentUser.ReportPath + "rptCustomerMailingLabel1.rpt");
//CurrentUser.AddReportParameters(rptDoc, DVReport.ToTable(), "Customer Mailing Label", false, false, false, false, false, false, false, false, false, false, false);
Reports.frmReportViewer fRptView = new Reports.frmReportViewer();
fRptView.Text = "Customer Mailing Label - [Page Size: A4]";
fRptView.crViewer.ReportSource = rptDoc;
fRptView.ShowDialog();
}
else
{
MessageBox.Show("File is not exist...");
}
}
catch (Exception exc)
{
Utill.Common.ExceptionLogger.writeException("Customer- Mailing Label", exc.StackTrace);
MessageBox.Show(Utill.Common.CommonMessage.ExceptionMesg, "Exception");
}
}
/// <summary>
/// 按账务期统计
/// </summary>
public void m_mthButtonClickToStatistic()
{
string p_strPriodId = "";
clsPeriod_VO[] objPriodItems = this.m_objViewer.m_cboSelPeriod.Tag as clsPeriod_VO[];
if (this.m_objViewer.m_cboSelPeriod.SelectedItem == null)
{
MessageBox.Show("请选择账务期");
return;
}
if (this.m_objViewer.m_cboSelPeriod.Text != "所有财务期的数据")
{
p_strPriodId = objPriodItems[this.m_objViewer.m_cboSelPeriod.SelectedIndex - 1].m_strPeriodID;
}
DataTable dtbStatistic;
long lngRes = 0;
lngRes = this.m_objManage.m_lngGetStatiticsData(out dtbStatistic, p_strPriodId);
System.Data.DataColumn dc = new DataColumn("SEQUENCEID");
dtbStatistic.Columns.Add(dc);
if (dtbStatistic.Rows.Count != 0)
{
for (int j1 = 0; j1 < dtbStatistic.Rows.Count; j1++)
{
dtbStatistic.Rows[j1]["SEQUENCEID"] = j1 + 1;
}
}
if (lngRes < 0)
{
return;
}
else
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rpt.Load("Report\\CrystalReportHisMedInOrderStaticstic.rpt");
// HISMedTypeManage.Rpt.CrystalReportHisMedInOrderStaticstic rpt = new HISMedTypeManage.Rpt.CrystalReportHisMedInOrderStaticstic();
((TextObject)rpt.ReportDefinition.ReportObjects["Text11"]).Text = this.m_objViewer.m_cboSelPeriod.SelectedText;
double totalMoney = 0;
if (dtbStatistic.Rows.Count > 0)
{
for (int i1 = 0; i1 < dtbStatistic.Rows.Count; i1++)
{
totalMoney += Convert.ToDouble(dtbStatistic.Rows[i1]["TOLMNY_MNY"].ToString().Trim());
}
//((TextObject)rpt.ReportDefinition.ReportObjects["Text9"]).Text = totalMoney.ToString("######.00");
}
else
{
//((TextObject)rpt.ReportDefinition.ReportObjects["Text9"]).Text = "0.00";
}
((TextObject)rpt.ReportDefinition.ReportObjects["Text11"]).Text = this.m_objViewer.m_cboSelPeriod.Text;
rpt.SetDataSource(dtbStatistic);
rpt.Refresh();
this.m_objViewer.m_crystalReportViewer1.ReportSource = rpt;
}
}
private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
//Dim Report As New cryItemGroupCompare
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryItemGroupCompare.rpt");
ReportNone.Load("cryNoRecords");
modReport.cnnDBreport.Execute("DELETE LinkItem.* FROM LinkItem;");
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=1 AND Link_SectionID=1");
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT 1, DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales FROM DayEndStockItemLnk INNER JOIN aStockItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = aStockItem.StockItemID WHERE " + rs.Fields("Link_SQL").Value + ";");
rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1");
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT 2, DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum(DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales) AS SumOfDayEndStockItemLnk_QuantitySales FROM DayEndStockItemLnk INNER JOIN aStockItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = aStockItem.StockItemID " + rs.Fields("Link_SQL").Value + " GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading"));
rs.Close();
rs = modReport.getRSreport(ref "SELECT Link.* From Link Where (((Link.Link_SectionID) = 1)) ORDER BY Link.Link_SectionID;");
if (rs.BOF | rs.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
rsData = modReport.getRSreport(ref "SELECT LinkItem.*, Format([DayEnd_Date],'ddd dd mmm\", \"yyyy') AS dateName, DayEnd.DayEnd_Date FROM DayEnd INNER JOIN LinkItem ON DayEnd.DayEndID = LinkItem.LinkItem_DayEndID;");
if (rsData.BOF | rsData.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsData);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
private void SelectDataProcessing()
{
try
{
cr.Load(Server.MapPath("Crpt_CheckPlantReceivingData.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;
CrystalDecisions.CrystalReports.Engine.TextObject t5;
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"];
t5 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Title"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
if (chk_DataProcessing.Checked == true)
{
t5.Text = "Data Receiving Report";
//str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurementimport WHERE Company_Code='"+ ccode +"' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' ORDER BY Prdate,Sessions";
str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions,Sample_No,ISNULL(RNotupdate,0) AS RNotupdate,ISNULL(Rupdate,0) AS Rupdate FROM (SELECT Prdate,Sessions,ISNULL(Sample_No,0) AS Sample_No,ISNULL(RNotupdate,0) AS RNotupdate FROM (SELECT COUNT(Agent_id) AS Sample_No,(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' GROUP BY Sessions,Prdate ) AS t1 LEFT JOIN (SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate1,Sessions AS sess1,('1') AS RNotupdate FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' AND Remarkstatus=1)AS t2 ON t1.Prdate=t2.Prdate1 AND t1.Sessions=t2.sess1) AS t3 LEFT JOIN (SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate2,Sessions AS sess2,('2') AS Rupdate FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' AND Remarkstatus=2)t4 ON t3.Prdate=t4.Prdate2 AND t3.Sessions=t4.sess2 ORDER BY Prdate,Sessions";
}
else
{
t5.Text = "Data Processing Report";
str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurement WHERE Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' ORDER BY Prdate,Sessions";
}
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
protected void MostrarReporte(string ruta)
{
//mostrar el reporte en la pagina.
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/" + ruta + ".rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/" + ruta + ".doc"));
}
private void NillBillAmountCheck1()
{
try
{
cr.Load(Server.MapPath("Crpt_NegativeBillAmountCheck.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"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
//str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SRNetAmt, FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound, cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS Routewiseagent INNER JOIN (SELECT G1.ARid, CAST(SUM(G1.Smkg) AS DECIMAL(18,2)) AS GSmkg, CAST(SUM(G1.Smltr) AS DECIMAL(18,2)) AS GSmltr, CAST(AVG(G1.AvgFat) AS DECIMAL(18,1)) AS GAvgFat, CAST(AVG(G1.AvgSnf) AS DECIMAL(18,1)) AS GAvgSnf, CAST(AVG(G1.AvgRate) AS DECIMAL(18,1)) AS GAvgRate, CAST(AVG(G1.Avgclr) AS DECIMAL(18,1)) AS GAvgclr, CAST(SUM(G1.Scans) AS DECIMAL(18,2)) AS GScans, CAST(SUM(G1.SAmt) AS DECIMAL(18,2)) AS GSAmt, CAST(SUM(G1.ScommAmt) AS DECIMAL(18,2)) AS GScommAmt, CAST(SUM(G1.Scatamt) AS DECIMAL(18,2)) AS GScatamt, CAST(SUM(G1.Ssplbonamt) AS DECIMAL(18,2)) AS GSsplbonamt, CAST(AVG(G1.AvgcRate) AS DECIMAL(18,2)) AS GAvgcRate, CAST(SUM(G1.Sfatkg) AS DECIMAL(18,2)) AS GSfatkg, CAST(SUM(G1.Ssnfkg) AS DECIMAL(18,2)) AS GSsnfkg, CAST(SUM(G1.SBilladv) AS DECIMAL(18,2)) AS GSBilladv, CAST(SUM(G1.SAiamt) AS DECIMAL(18,2)) AS GSAiamt, CAST(SUM(G1.SFeedamt) AS DECIMAL(18,2)) AS GSFeedamt, CAST(SUM(G1.Scanamt) AS DECIMAL(18,2)) AS GScanamt, CAST(SUM(G1.SRecoveryamt) AS DECIMAL(18,2)) AS GSRecoveryamt, CAST(SUM(G1.Sothers) AS DECIMAL(18,2)) AS GSothers, CAST(SUM(G1.Sinstamt) AS DECIMAL(18,2)) AS GSinstamt, CAST(SUM(G1.Sbalance) AS DECIMAL(18,2)) AS GSbalance, CAST(SUM(G1.SLoanAmount) AS DECIMAL(18,2)) AS GSLoanAmount, CAST(SUM(G1.SNetAmt) AS DECIMAL(18,2)) AS GSNetAmt, CAST(SUM(G1.SRound) AS DECIMAL(18,2)) AS GSround FROM (SELECT cart.ARid, ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, FLOOR (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )-( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0)) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound FROM ( SELECT * FROM ( SELECT * FROM ( SELECT agent_id AS SproAid, CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg, CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr, CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat, CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf, CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate, CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr, CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans, CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt, CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt, CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt, CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt, CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate, CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg, CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS G1 GROUP BY G1.ARid) AS Route ON Routewiseagent.Rid=Route.ARid and SNetAmt<0 and Route.ARid IS NOT NULL ORDER BY Routewiseagent.Rid ,Routewiseagent.Aid";
//loan recovery
str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,cart.Agent_Name,ISNULL(prdelo.Smkg,0) AS Smkg,ISNULL(prdelo.Smltr,0) AS Smltr,ISNULL(prdelo.AvgFat ,0) AS AvgFat,ISNULL(prdelo.AvgSnf,0) AS AvgSnf,ISNULL(prdelo.AvgRate,0) AS AvgRate,ISNULL(prdelo.Avgclr,0) AS Avgclr,ISNULL(prdelo.Scans,0) AS Scans,ISNULL(prdelo.SAmt,0) AS SAmt,ISNULL(prdelo.ScommAmt,0) AS ScommAmt,ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt,ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate,ISNULL(prdelo.Sfatkg,0) AS Sfatkg,ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg,ISNULL(prdelo.Billadv,0) AS SBilladv,ISNULL(prdelo.Ai,0) AS SAiamt,ISNULL(prdelo.Feed,0) AS SFeedamt,ISNULL(prdelo.Can,0) AS Scanamt,ISNULL(prdelo.Recovery,0) AS SRecoveryamt,ISNULL(prdelo.others,0) AS Sothers,ISNULL(prdelo.instamt,0) AS Sinstamt,ISNULL(prdelo.balance,0) AS Sbalance,ISNULL(prdelo.LoanAmount,0) AS SLoanAmount,ISNULL(prdelo.VouAmount,0) AS Sclaim,CAST( ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS DECIMAL(18,2)) AS SRNetAmt,FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt,( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) +ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound,cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (select Agent_Id AS VouAid,CAST(SUM(Amount) AS DECIMAL(18,2)) AS VouAmount from Voucher_Clear where Plant_Code='" + pcode + "' AND Clearing_Date BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_Id) AS vou ON proded.SproAid=vou.VouAid) AS pdv LEFT JOIN (SELECT ISNULL(LoAid,0) AS LoAid,ISNULL(balance,0) AS balance,ISNULL(LoanAmount,0) AS LoanAmount,(ISNULL(loanRecAmount1,0)+ ISNULL(0,0)) AS instamt FROM (SELECT LoAid1 AS LoAid,balance1 AS balance,LoanAmount1 AS LoanAmount,loanRecAmount1 FROM (SELECT Agent_id AS LoAid1,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance1,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount1 FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY Agent_id) AS Lonn LEFT JOIN (SELECT Agent_id AS LoRecAid,CAST(SUM(Paid_Amount) AS DECIMAL(18,2)) AS loanRecAmount1 FROM Loan_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND Paid_date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id) AS LonRec ON Lonn.LoAid1=LonRec.LoRecAid ) AS LoF LEFT JOIN (SELECT Agent_Id AS LoDuAid,CAST(SUM(LoanDueRecovery_Amount) AS DECIMAL(18,2)) AS loanDueRecAmount1 FROM LoanDue_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND LoanRecovery_Date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id ) AS LonDRec ON LoF.LoAid=LonDRec.LoDuAid ) AS Lon ON pdv.SproAid=Lon.LoAid ) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid ) AS fin INNER JOIN (SELECT Route_ID AS Rrid from Route_Master WHERE Plant_Code='" + pcode + "' ) AS Rp ON fin.Rid=Rp.Rrid AND fin.SNetAmt<0 order by rid,Aid ";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
crvReporte.Visible = true;
string fechainicial = Request.QueryString["fechainicial"].ToString();
string fechafinal = Request.QueryString["fechafinal"].ToString();
string idsede = Request.QueryString["idsede"].ToString();
string idproyecto = Request.QueryString["idproyecto"].ToString();
string idestado = Request.QueryString["idestado"].ToString();
ds = objPedidoDAO.ListarReportePedidos(AppUtilidad.stringToDateTime(fechainicial, "DD/MM/YYYY"),
AppUtilidad.stringToDateTime(fechafinal, "DD/MM/YYYY"),
Convert.ToInt32(idsede),
Convert.ToInt32(idproyecto),
idestado);
dt = ds.Tables[0];
CrystalDecisions.CrystalReports.Engine.ReportDocument rDoc;
rDoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rDoc.Load(Server.MapPath("rptReportePedidos.rpt")); // Your .rpt file path
rDoc.SetDataSource(dt); //set dataset to the report viewer.
rDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, false, "Listado");
}
//Crystal Report 直接輸出 PDF 檔
private void ExportReport(DataTable dt)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/CGR021R01.rpt"));
report.SetDataSource(dt);
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
Program_ID = this.PageCode.Substring(0, 5) + "R01";
LoginUser = Session["UID"].ToString();
report.SetParameterValue("par_Program_ID", this.PageCode);
report.SetParameterValue("par_LoginUser", LoginUser);
//檔案匯出
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("退貨結轉檢核異常報表.PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
}
private void ShowReport(DataTable Dt)
{
try
{
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/INV021R0" + rblREPORT.SelectedValue + ".rpt"));
report.SetDataSource(Dt);
DataTable userName = (DataTable)Session["UserInfo"];
string UName = userName.Rows[0]["Name"].ToString();
report.SetParameterValue("par_Program_ID", this.PageCode);
report.SetParameterValue("par_LoginUser", Session["UID"].ToString());
report.SetParameterValue("par_LoginUser_Name", UName);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = "";
if (rblREPORT.SelectedValue == "1")
rpt_name = "儲區庫存匯總表_" + DateTime.Now.ToString("yyyyMMdd") + ".PDF";
else
rpt_name = "儲位庫存明細表_" + DateTime.Now.ToString("yyyyMMdd") + ".PDF";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ProgramID = string.Empty;
string par_LoginUser = string.Empty;
#region 前置檢查與參數過濾
string par_S_Vendor = this.SLP_VendorBaseS.Text + " " + this.SLP_VendorBaseS.Name;
string par_E_Vendor = this.SLP_VendorBaseE.Text + " " + this.SLP_VendorBaseE.Name;
string par_S_PMA = this.SLP_PMAS.Text + " " + this.SLP_PMAS.Name;
string par_E_PMA = this.SLP_PMAE.Text + " " + this.SLP_PMAE.Name;
string par_S_RootNo = this.SLP_RootNoS.Text + " " + this.SLP_RootNoS.Name;
string par_E_RootNo = this.SLP_RootNoE.Text + " " + this.SLP_RootNoE.Name;
string par_S_LocateNo = this.SLP_SimpleLocateSec1.Text1 + " " + this.SLP_SimpleLocateSec1.Name;
string par_E_LocateNo = this.SLP_SimpleLocateSec2.Text1 + " " + this.SLP_SimpleLocateSec2.Name;
string par_S_LocateSec = SLP_SimpleLocateSec1.Text2.Trim() != "" ? this.SLP_SimpleLocateSec1.Text1 + SLP_SimpleLocateSec1.Text2 : "";
string par_E_LocateSec = SLP_SimpleLocateSec2.Text2.Trim() != "" ? this.SLP_SimpleLocateSec2.Text1 + SLP_SimpleLocateSec2.Text2 : "";
string par_Item = this.SLP_SKU.Text + " " + this.SLP_SKU.Name;
string par_Period = this.SLP_ItemPeriod.Text + " " + this.SLP_ItemPeriod.Name;
par_ProgramID = this.PageCode;
par_LoginUser = Session["UID"].ToString();
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("par_S_Vendor", par_S_Vendor);
report.SetParameterValue("par_E_Vendor", par_E_Vendor);
report.SetParameterValue("par_S_PMA", par_S_PMA);
report.SetParameterValue("par_E_PMA", par_E_PMA);
report.SetParameterValue("par_S_RootNo", par_S_RootNo);
report.SetParameterValue("par_E_RootNo", par_E_RootNo);
report.SetParameterValue("par_S_LocateNo", par_S_LocateNo);
report.SetParameterValue("par_E_LocateNo", par_E_LocateNo);
report.SetParameterValue("par_S_LocateSec", par_S_LocateSec);
report.SetParameterValue("par_E_LocateSec", par_E_LocateSec);
report.SetParameterValue("par_Item", par_Item);
report.SetParameterValue("par_Period", par_Period);
report.SetParameterValue("par_ProgramID", par_ProgramID);
report.SetParameterValue("par_LoginUser", par_LoginUser);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 匯出PDF
/// </summary>
/// <param name="dt">列印資料</param>
/// <param name="s_Status">狀態，"Detail"明細表，"Gather"彙總表</param>
private void Show_Report2(DataTable dt, string s_Status)
{
#region
//string s_FileName = txt_Out_FileName.Text;
string s_FileName = "客服線上紀錄表(明細).pdf";
string s_RptName = "./REPORT/CRM131/CRM131_Report_Detl.rpt";
if (s_Status == "Detail")
{
s_FileName = "客服線上紀錄表(明細).pdf";
s_RptName = "./REPORT/CRM131/CRM131_Report_Detl.rpt";
}
else if (s_Status == "Gather")
{
s_FileName = "客服線上紀錄表(彙總).pdf";
s_RptName = "./REPORT/CRM131/CRM131_Report_Main.rpt";
}
s_FileName = HttpUtility.UrlEncode(s_FileName, System.Text.Encoding.UTF8);
report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
#region Load 報表
report.Load(Server.MapPath(s_RptName));
report.SetDataSource(dt);
// Set Parameter
report.SetParameterValue("par_Program_ID", this.PageCode);
report.SetParameterValue("par_LoginUser", Session["UID"].ToString());
report.SetParameterValue("par_DateRange", this.slp_DateRange.StartDate + "~" + this.slp_DateRange.EndDate);
report.SetParameterValue("par_StoreChain", this.slp_StoreChain_B.Text + "~" + this.slp_StoreChain_E.Text);
report.SetParameterValue("par_Z_O", this.slp_Z_O_B.Text + "~" + this.slp_Z_O_E.Text);
#endregion
#region 轉出PDF
System.IO.Stream stream = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
stream.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("BIG5");
Response.OutputStream.Write(bytes, 0, bytes.Length);
Response.Flush();
Response.Close();
report.Close();
#endregion
#endregion
}
private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string sql = null;
ADODB.Connection lConn = default(ADODB.Connection);
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsPurchase = default(ADODB.Recordset);
ADODB.Recordset rsSales = default(ADODB.Recordset);
ADODB.Recordset rsStock = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
//Dim Report As New cryIncomeExpense
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryIncomeExpense.rpt");
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
if (cmbMonthEnd.SelectedIndex) {
lConn = modRecordSet.openConnectionInstance(ref "month" + gMonth - cmbMonthEnd.SelectedIndex + ".mdb");
if (lConn == null)
return;
rsStock = new ADODB.Recordset();
rsStock.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
sql = "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;";
rsStock.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
rsSales = new ADODB.Recordset();
rsSales.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
//UPGRADE_WARNING: Couldn't resolve default property of object sql. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
sql = "SELECT [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date], Sum([Declaration].[Declaration_Total]) AS SumOfSale_Total FROM Declaration INNER JOIN M_DayEnd ON [Declaration].[Declaration_DayEndID]=[M_DayEnd].[DayEndID] GROUP BY [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date];";
rsSales.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
} else {
modApplication.modUpdate = 3;
modApplication.updateStockMovement();
sql = "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(Declaration.Declaration_Total) AS SumOfSale_Total FROM Company, Declaration INNER JOIN DayEnd ON Declaration.Declaration_DayEndID = DayEnd.DayEndID Where (((DayEnd.DayEndID) <> [Company]![Company_DayEndID])) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date Union SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum([SaleItem_Price]*[SaleItem_Quantity]) AS SumOfSale_Total FROM Consignment AS Consignment_1 RIGHT JOIN (Consignment RIGHT JOIN ((Sale INNER JOIN (Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID) ON Sale.Sale_DayEndID = DayEnd.DayEndID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleItem_SaleID) ON Consignment.Consignment_SaleID = Sale.SaleID) ON Consignment_1.Consignment_ReversalSaleID = Sale.SaleID Where (((SaleItem.SaleItem_Revoke) = 0)) GROUP BY Consignment.ConsignmentID, Consignment_1.ConsignmentID, DayEnd.DayEndID, DayEnd.DayEnd_Date ";
sql = sql + "HAVING (((Consignment.ConsignmentID) Is Null) AND ((Consignment_1.ConsignmentID) Is Null));";
rsSales = modRecordSet.getRS(ref sql);
rsStock = modRecordSet.getRS(ref "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;");
}
rsPurchase = modRecordSet.getRS(ref "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(grvPosted.GRV_InvoiceInclusive) AS SumOfGRV_InvoiceInclusive, ([MonthEnd_BudgetSales]/[MonthEnd_Days]) AS saleBudget, ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]) AS purchaseBudget FROM [SELECT GRV.* From GRV WHERE (((GRV.GRV_GRVStatusID)=3))]. AS grvPosted RIGHT JOIN (DayEnd INNER JOIN MonthEnd ON DayEnd.DayEnd_MonthEndID = MonthEnd.MonthEndID) ON grvPosted.GRV_DayEndID = DayEnd.DayEndID Where (((MonthEnd.MonthEndID) = " + gMonth - cmbMonthEnd.SelectedIndex + ")) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date, ([MonthEnd_BudgetSales]/[MonthEnd_Days]), ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]);");
rsCompany = modRecordSet.getRS(ref "SELECT MonthEnd.* From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - cmbMonthEnd.SelectedIndex + "));");
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportNone.Load("cryNoRecords.rpt");
if (rsPurchase.BOF | rsPurchase.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
//Report.Database.SetDataSource(rs)
Report.Database.Tables(1).SetDataSource(rsPurchase);
Report.Database.Tables(2).SetDataSource(rsSales);
Report.Database.Tables(3).SetDataSource(rsStock);
Report.Database.Tables(4).SetDataSource(rsCompany);
//UPGRADE_WARNING: Couldn't resolve default property of object Report.VerifyOnEveryPrint. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
if (lConn == null) {
} else {
lConn.Close();
}
}
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
private void ShowReport(DataTable dt, string type)
{
try
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM121/VAM12" + type + "R.rpt"));
report.SetDataSource(dt);
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
string strParFDate = string.Empty;
string strParFSType = string.Empty;
string strParFTaxType = string.Empty;
Program_ID = this.PageCode.Substring(0, 5) + "R0" + type;
LoginUser = Session["UID"].ToString();
//report.SetParameterValue("par_Program_ID", Program_ID);
//report.SetParameterValue("par_LoginUser", LoginUser);
//report.SetParameterValue("par_LoginName", Login_Name);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = string.Empty;
rpt_name = "進項憑證明細表_折讓單套表.PDF";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 顯示報表
/// </summary>
private void ShowReport(DataSet ds)
{
#region 利用CrystalReport列印報表
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
string strFileName = "";
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportDocument SubReport = new ReportDocument();
report.Load(Server.MapPath("./REPORT/CAA271/CAA27R01.rpt"));
strFileName = HttpUtility.UrlEncode("結帳與開立憑證檢核表.PDF", System.Text.Encoding.UTF8);
SubReport = report.OpenSubreport("CAA27R01_1");
SubReport.SetDataSource(ds);
report.SetDataSource(ds);
report.SetParameterValue("par_Program_ID", "CAA27R01");
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);//pdf檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
#endregion
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string s_par_Reqno = string.Empty;
string s_par_Rolecode = string.Empty;
string s_par_UserID = string.Empty;
string s_par_UseName = string.Empty;
string s_par_Signdate = string.Empty;
string s_par_ReqLevel = string.Empty;
string s_par_ReqType = string.Empty;
string s_par_ReqStatus = string.Empty;
string s_par_SysDesc = string.Empty;
string s_par_WantDate = string.Empty;
string s_par_ReqGist = string.Empty;
string s_par_ReqDesc = string.Empty;
s_par_Reqno = dt_Source.Rows[0]["REQ_NO"].ToString();
s_par_Rolecode = dt_Source.Rows[0]["ROLECODE"].ToString();
s_par_UseName = dt_Source.Rows[0]["USER_NAME"].ToString();
if (dt_Source.Rows[0]["SIGN_DATE"].ToString() != "")
{
s_par_Signdate = DateTime.Parse(dt_Source.Rows[0]["SIGN_DATE"].ToString()).ToString("yyyy/MM/dd");
}
s_par_ReqLevel = dt_Source.Rows[0]["REQ_LEVEL"].ToString();
s_par_ReqType = dt_Source.Rows[0]["REQ_TYPE"].ToString();
s_par_ReqStatus = dt_Source.Rows[0]["REQ_STATUS"].ToString();
s_par_SysDesc = dt_Source.Rows[0]["SYSDESC"].ToString();
if (dt_Source.Rows[0]["WANT_DATE"].ToString() != "")
{
s_par_WantDate = DateTime.Parse(dt_Source.Rows[0]["WANT_DATE"].ToString()).ToString("yyyy/MM/dd");
}
s_par_ReqGist = dt_Source.Rows[0]["REQ_GIST"].ToString();
s_par_ReqDesc = dt_Source.Rows[0]["REQ_DESC"].ToString();
report.SetParameterValue("par_Reqno", s_par_Reqno);
report.SetParameterValue("par_Rolecode", s_par_Rolecode);
report.SetParameterValue("par_UseName", s_par_UseName);
report.SetParameterValue("par_Signdate", s_par_Signdate);
report.SetParameterValue("par_ReqLevel", s_par_ReqLevel);
report.SetParameterValue("par_ReqType", s_par_ReqType);
report.SetParameterValue("par_ReqStatus", s_par_ReqStatus);
report.SetParameterValue("par_SysDesc", s_par_SysDesc);
report.SetParameterValue("par_WantDate", s_par_WantDate);
report.SetParameterValue("par_ReqGist", s_par_ReqGist);
report.SetParameterValue("par_ReqDesc", s_par_ReqDesc);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ChanNo = string.Empty;
string par_Store = string.Empty;
#region 前置檢查與參數過濾
par_ChanNo = Server.UrlDecode(Request.QueryString["Chan"].ToString());
par_Store = Server.UrlDecode(Request.QueryString["Store"].ToString());
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("ChanNo", par_ChanNo);
report.SetParameterValue("Store", par_Store);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
report.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
break;
default:
break;
}
}
private void ShowReportA(DataTable dtA, DataTable dtB, string V_FORM_TYPE)
{
#region 利用CrystalReport列印報表
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM051/VAM052R01.rpt"));
DataSet ds = new DataSet();
dtA.TableName = "VAM052_R1";
ds.Tables.Add(dtA.Copy());
dtB.TableName = "VAM052_R2";
ds.Tables.Add(dtB.Copy());
report.SetDataSource(ds);
report.SetParameterValue("par_Count", Convert.ToString(dtA.Rows.Count + dtB.Rows.Count));
if (this.hidTYPE.Value == "1")
{
report.SetParameterValue("par_Program_ID", "註記解除");
report.SetParameterValue("par_YM", this.Label7.Text);
report.SetParameterValue("par_Source_date", "註記解除日期/時間：" + System.DateTime.Now.ToLongDateString());
report.SetParameterValue("par_LoginName", "註記解除人員：" + Session["UID"].ToString() + Login_Name);
}
else
{
report.SetParameterValue("par_Program_ID", "註記確認");
report.SetParameterValue("par_YM", this.SLP_YearMonth1.Text);
report.SetParameterValue("par_Source_date", "註記日期/時間：" + System.DateTime.Now.ToLongDateString());
report.SetParameterValue("par_LoginName", "註記人員：" + Session["UID"].ToString() + Login_Name);
}
//檔案匯出
if (V_FORM_TYPE == "XLS")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("供應商單品結帳.XLS", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/xls";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("供應商單品結帳.PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
report.Close();
#endregion
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_LoginName = string.Empty;
string s_par_1 = string.Empty;
string s_par_2 = string.Empty;
string s_par_3 = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_LoginName = ((DataTable)Session["UserInfo"]).Rows[0]["Name"].ToString();
s_par_1 = SLP_PICK_DATE.StartDate + " ~ " + SLP_PICK_DATE.EndDate;
s_par_2 = ddlLineStart.Text + " ~ " + ddlLineEnd.Text;
s_par_3 = txtSTATION_NO_S.Text + " ~ " + txtSTATION_NO_E.Text;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_LoginName", s_par_LoginName);
report.SetParameterValue("par_1", s_par_1);
report.SetParameterValue("par_2", s_par_2);
report.SetParameterValue("par_3", s_par_3);
report.SetParameterValue("par_Type", reportFormatType);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ProgramID = string.Empty;
string par_LoginUser = string.Empty;
#region 前置檢查與參數過濾
string par_S_PDate = this.SLP_SLPDateRange.StartDate;
string par_E_PDate = this.SLP_SLPDateRange.EndDate;
string par_BatchNo = this.txtBatchNo.Text;
string par_ChanNo = this.SLP_StoreChain.Text + " " + this.SLP_StoreChain.Name;
string par_ChanSourceNo = this.txtChanSourceNo.Text;
string par_S_SignDate = this.SLP_SignDate.StartDate;
string par_E_SignDate = this.SLP_SignDate.EndDate;
string par_Item = this.SLP_SKU.Text + " " + this.SLP_SKU.Name;
string par_Period = this.SLP_ItemPeriod.Text + " " + this.SLP_ItemPeriod.Name;
string par_ItemMap = this.txtItemMap.Text;
par_ProgramID = this.PageCode;
par_LoginUser = Session["UID"].ToString();
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("par_S_PDate", par_S_PDate);
report.SetParameterValue("par_E_PDate", par_E_PDate);
report.SetParameterValue("par_BatchNo", par_BatchNo);
report.SetParameterValue("par_ChanNo", par_ChanNo);
report.SetParameterValue("par_ChanSourceNo", par_ChanSourceNo);
report.SetParameterValue("par_S_SignDate", par_S_SignDate);
report.SetParameterValue("par_E_SignDate", par_E_SignDate);
report.SetParameterValue("par_Item", par_Item);
report.SetParameterValue("par_Period", par_Period);
report.SetParameterValue("par_ItemMap", par_ItemMap);
report.SetParameterValue("par_ProgramID", par_ProgramID);
report.SetParameterValue("par_LoginUser", par_LoginUser);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_Date = string.Empty;
//組報表名稱
string type = "";
if (radDealGoodsList.Checked) type = "1";
else if (radDealTotal.Checked) type = "2";
else type = "3";
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_Date = SLP_SLPDate.Text;
//取得門市對象及資料來源中文名稱
string s_par_DELIV_EARLY = "", s_par_DATA_SOURCE = "";
if (radTotal.Checked) s_par_DELIV_EARLY = radTotal.Text;
else if (radViceStore.Checked) s_par_DELIV_EARLY = radViceStore.Text;
else s_par_DELIV_EARLY = radAheadDealStore.Text;
if (radPay.Checked) s_par_DATA_SOURCE = radPay.Text;
else s_par_DATA_SOURCE = radStoreIn.Text;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_DELIV_EARLY", s_par_DELIV_EARLY);
report.SetParameterValue("par_DATA_SOURCE", s_par_DATA_SOURCE);
if (radDealTotal.Checked)//理貨彙總表
{
report.SetParameterValue("par_ST_ACCEPT_DATE", s_par_Date);//門市進貨日
report.SetParameterValue("par_PICK_BACTH", txtProcBatch.Text);//理貨批次
}
else if (radOutRpt.Checked)
{
report.SetParameterValue("par_ST_ACCEPT_DATE", s_par_Date);//門市進貨日
report.SetParameterValue("par_PICK_BACTH", txtProcBatch.Text);//理貨批次
report.SetParameterValue("par_TRANS_NO_FROM", ddlTransSt_S.SelectedItem.Text);//轉運站起
report.SetParameterValue("par_TRANS_NO_TO", ddlTransSt_E.SelectedItem.Text);//轉運站迄
report.SetParameterValue("par_ROUTE_FROM", SLP_Route_S.Text);//路線起
report.SetParameterValue("par_ROUTE_TO", SLP_Route_E.Text);//路線迄
}
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ProgramID = string.Empty;
string par_LoginUser = string.Empty;
DropDownList ReasonS = (DropDownList)this.SLP_CodeFileS.FindControl("D1");
DropDownList ReasonE = (DropDownList)this.SLP_CodeFileE.FindControl("D1");
string[] fileds ={ "STORE" };
DataTable Dt = SelectDistinct(dt_Source, fileds);
#region 前置檢查與參數過濾
string par_S_PDate = this.SLP_SLPDateRangeOut.StartDate;
string par_E_PDate = this.SLP_SLPDateRangeOut.EndDate;
string par_S_ChanNo = this.SLP_StoreChainS.Text + " " + this.SLP_StoreChainS.Name;
string par_E_ChanNo = this.SLP_StoreChainE.Text + " " + this.SLP_StoreChainE.Name;
string par_AcceptDate = this.SLP_SLPDate.Text;
string par_S_Route = this.SLP_RouteS.Text + " " + this.SLP_RouteS.Name;
string par_E_Route = this.SLP_RouteE.Text + " " + this.SLP_RouteE.Name;
string par_S_ReasonNo = ReasonS.SelectedValue + " " + ReasonS.SelectedItem.Text;
string par_E_ReasonNo = ReasonE.SelectedValue + " " + ReasonE.SelectedItem.Text;
string par_S_Store = this.SLP_StoreS.Text + " " + this.SLP_StoreS.Name;
string par_E_Store = this.SLP_StoreE.Text + " " + this.SLP_StoreE.Name;
par_ProgramID = this.PageCode;
par_LoginUser = Session["UID"].ToString();
string par_STORE_CNT = Dt.Rows.Count.ToString();
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("par_S_PDate", par_S_PDate);
report.SetParameterValue("par_E_PDate", par_E_PDate);
report.SetParameterValue("par_S_ChanNo", par_S_ChanNo);
report.SetParameterValue("par_E_ChanNo", par_E_ChanNo);
report.SetParameterValue("par_AcceptDate", par_AcceptDate);
report.SetParameterValue("par_S_Route", par_S_Route);
report.SetParameterValue("par_E_Route", par_E_Route);
report.SetParameterValue("par_S_ReasonNo", par_S_ReasonNo);
report.SetParameterValue("par_E_ReasonNo", par_E_ReasonNo);
report.SetParameterValue("par_S_Store", par_S_Store);
report.SetParameterValue("par_E_Store", par_E_Store);
report.SetParameterValue("par_ProgramID", par_ProgramID);
report.SetParameterValue("par_LoginUser", par_LoginUser);
report.SetParameterValue("par_STORE_CNT", par_STORE_CNT);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
private void loadValue()
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
//Dim Report As New cryItemItemCompareValue
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryItemItemCompareValue.rpt");
ReportNone.Load("cryNoRecords.rpt");
modReport.cnnDBreport.Execute("DELETE LinkItem.* FROM LinkItem;");
rs = modReport.getRSreport(ref "SELECT * FROM Link Where Link_SectionID=3");
while (!(rs.EOF)) {
if (!string.IsNullOrEmpty(rs.Fields("Link_SQL").Value)) {
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT " + rs.Fields("LinkID").Value + ", Sale.Sale_DayEndID, Sum([SaleItem_Quantity]*[SaleItem_Price]) FROM (SaleItem INNER JOIN aStockItem ON SaleItem.SaleItem_StockItemID = aStockItem.StockItemID) INNER JOIN Sale ON SaleItem.SaleItem_SaleID = Sale.SaleID Where " + rs.Fields("Link_SQL").Value + " GROUP BY Sale.Sale_DayEndID;");
}
rs.moveNext();
}
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT theJoin.LinkID, theJoin.DayEndID, 0 FROM LinkItem RIGHT JOIN [SELECT Link.LinkID, DayEnd.DayEndID From Link, DayEnd WHERE (((Link.Link_SQL)<>'') AND ((Link.Link_SectionID)=3))]. AS theJoin ON (LinkItem.LinkItem_DayEndID = theJoin.DayEndID) AND (LinkItem.LinkItem_LinkID = theJoin.LinkID) WHERE (((LinkItem.LinkItem_LinkID) Is Null));");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading"));
rs.Close();
rs = modReport.getRSreport(ref "SELECT [Link].[LinkID], [Link].[Link_Name], Sum([LinkItem].[LinkItem_Value]) AS SumOfLinkItem_Value FROM Link INNER JOIN LinkItem ON [Link].[LinkID]=[LinkItem].[LinkItem_LinkID] WHERE ((([Link].[Link_SQL])<>'') And (([Link].[Link_SectionID])=3)) GROUP BY [Link].[LinkID], [Link].[Link_Name] ORDER BY [Link].[LinkID];");
if (rs.BOF | rs.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
rsData = modReport.getRSreport(ref "SELECT LinkItem.*, Format([DayEnd_Date],'yyyy mm dd ddd') AS dateName, DayEnd.DayEnd_Date FROM DayEnd INNER JOIN LinkItem ON DayEnd.DayEndID = LinkItem.LinkItem_DayEndID ORDER BY DayEnd.DayEnd_Date;");
if (rsData.BOF | rsData.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsData);
System.Windows.Forms.Application.DoEvents();
//Report.VerifyOnEveryPrint = True
System.Windows.Forms.Application.DoEvents();
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source)
{
if (s_FileName.ToLower().IndexOf(".xls") < 0)
{
s_FileName = s_FileName + ".xls";
}
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
System.IO.Stream stream = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
stream.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("BIG5");
Response.OutputStream.Write(bytes, 0, bytes.Length);
Response.Flush();
Response.Close();
report.Close();
}
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
//string s_par_Program_ID = this.PageCode;
string Login_Name = ((DataTable)Session["UserInfo"]).Rows[0]["Name"].ToString();
string s_par_Program_ID = "MKT10";
string s_par_LoginUser = Session["UID"].ToString() + Login_Name;
string s_par_Busdate = SLP_MDC_START_DATE.StartDate + " ~ " + SLP_MDC_START_DATE.EndDate;
string s_par_Z_O = SLP_Z_O_S.Text + " " + SLP_Z_O_S.Name + " ~ " + SLP_Z_O_E.Text + " " + SLP_Z_O_E.Name;
string s_par_Chan_No = SLP_CHAIN_S.Text + " " + SLP_CHAIN_S.Name + " ~ " + SLP_CHAIN_E.Text + " " + SLP_CHAIN_E.Name;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_Busdate", s_par_Busdate);
report.SetParameterValue("par_Z_O", s_par_Z_O);
report.SetParameterValue("par_Chan_No", s_par_Chan_No);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
//Dim Report As New cryKeyboardName
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryKeyboardName.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
rs = modRecordSet.getRS(ref "SELECT KeyboardLayout.KeyboardLayout_Name, keyboard.keyboard_Name, KeyboardKeyboardLayoutLnk.KeyboardKeyboardLayoutLnk_Description, keyboard.keyboard_Order, keyboard.keyboard_Show FROM (KeyboardKeyboardLayoutLnk INNER JOIN keyboard ON KeyboardKeyboardLayoutLnk.KeyboardKeyboardLayoutLnk_KeyboardID = keyboard.KeyboardID) INNER JOIN KeyboardLayout ON KeyboardKeyboardLayoutLnk.KeyboardKeyboardLayoutLnk_KeyboardLayoutID = KeyboardLayout.KeyboardLayoutID Where (((KeyboardLayout.KeyboardLayoutID) = " + gParentID + ")) ORDER BY keyboard.keyboard_Order, keyboard.keyboard_Name;");
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportNone.Load("cryNoRecords.rpt");
if (rs.BOF | rs.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(0).SetDataSource(rs);
Report.Database.Tables(1).SetDataSource(rs);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsQty = default(ADODB.Recordset);
ADODB.Recordset RSitem = default(ADODB.Recordset);
bool ltype = false;
//Dim Report As New cryCashTransaction
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryCashTransaction.rpt");
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
//UPGRADE_WARNING: Couldn't resolve default property of object Report.txtCompanyName. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Channel ORDER BY ChannelID");
while (!(rs.EOF)) {
switch (rs.Fields("ChannelID").Value) {
case 1:
Report.SetParameterValue("txtCS1", rs.Fields("Channel_Code"));
break;
case 2:
Report.SetParameterValue("txtCS2", rs.Fields("Channel_Code"));
break;
case 3:
Report.SetParameterValue("txtCS3", rs.Fields("Channel_Code"));
break;
case 4:
Report.SetParameterValue("txtCS4", rs.Fields("Channel_Code"));
break;
case 5:
Report.SetParameterValue("txtCS5", rs.Fields("Channel_Code"));
break;
case 6:
Report.SetParameterValue("txtCS6", rs.Fields("Channel_Code"));
break;
case 7:
Report.SetParameterValue("txtCS7", rs.Fields("Channel_Code"));
break;
case 8:
Report.SetParameterValue("txtCS8", rs.Fields("Channel_Code"));
break;
}
rs.moveNext();
}
rs.Close();
rs = modRecordSet.getRS(ref "SELECT CashTransaction.*, StockItem.StockItem_Name FROM StockItem INNER JOIN CashTransaction ON StockItem.StockItemID = CashTransaction.CashTransaction_StockItemID;");
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportNone.Load("cryNoRecords.rpt");
if (rs.BOF | rs.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(1).SetDataSource(rs);
//Report.VerifyOnEveryPrint = True
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_DateRange = string.Empty;
string s_par_StoreChan = string.Empty;
String s_par_Route = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_DateRange = slpStAcceptDate.StartDate.ToString().Trim() + " ~ " + slpStAcceptDate.EndDate.ToString().Trim();
s_par_StoreChan = SLP_StoreChain.Text + " " + ((TextBox)this.SLP_StoreChain.FindControl("TextBoxName")).Text;
s_par_Route = this.txtRouteStart.Text + " " + ((TextBox)this.txtRouteStart.FindControl("TextBoxName")).Text + " ~ " +
this.txtRouteEnd.Text + " " + ((TextBox)this.txtRouteEnd.FindControl("TextBoxName")).Text;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_DateRange", s_par_DateRange);
report.SetParameterValue("par_StoreChan", s_par_StoreChan);
report.SetParameterValue("par_Route", s_par_Route);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
// show報表
private void ShowReport(DataTable Dt)
{
try
{
string strFileName = "";
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/PUR09/CRPUR09R01.rpt"));
strFileName = HttpUtility.UrlEncode("每日出貨品項維護.PDF", System.Text.Encoding.UTF8);
report.SetDataSource(Dt);
string Login_Name = ((DataTable)Session["UserInfo"]).Rows[0]["Name"].ToString();
report.SetParameterValue("par_Program_ID", "PUR09");
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
report.SetParameterValue("par_StAcceptDate", this.txtPLAN_ACCEPT_DATE.Text);
report.SetParameterValue("par_PickBatch", this.txtPICK_BATCH.Text);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);//pdf檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
//string s_par_Program_ID = this.PageCode;
string Login_Name = ((DataTable)Session["UserInfo"]).Rows[0]["Name"].ToString();
string s_par_Program_ID = "MKT11";
string s_par_LoginUser = Session["UID"].ToString() + " " + Login_Name;
string s_par_Vendor = SLP_Vendor.Text + " " + SLP_Vendor.Name;
string s_par_RootNo = SLP_RootNo_S.Text + " " + SLP_RootNo_S.Name +" ~ " + SLP_RootNo_E.Text + " " + SLP_RootNo_E.Name;
string s_par_PMA = SLP_PMA_S.Text + " " + SLP_PMA_S.Name + " ~ " + SLP_PMA_E.Text + " " + SLP_PMA_E.Name;
string s_par_Category = SLP_Category_S.Text + " " + SLP_Category_S.Name + " ~ " + SLP_Category_E.Text + " " + SLP_Category_E.Name;
string s_par_SortOut = SLP_SortOut_S.Text + " " + SLP_SortOut_S.Name + " ~ " + SLP_SortOut_E.Text + " " + SLP_SortOut_E.Name;
string s_par_Item = SLP_SKU.Text + " " + SLP_SKU.Name;
string s_par_Period = SLP_ItemPeriod.Text;
string s_par_PeriodCount = SLP_SLPNumber.Text;
string s_par_Order ="";
if ( ddl_Order.SelectedValue=="1"){
s_par_Order = "期別降冪";
}
else if ( ddl_Order.SelectedValue=="2"){
s_par_Order = "期別升冪";
}
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_Vendor", s_par_Vendor);
report.SetParameterValue("par_RootNo", s_par_RootNo);
report.SetParameterValue("par_PMA", s_par_PMA);
report.SetParameterValue("par_Category", s_par_Category);
report.SetParameterValue("par_SortOut", s_par_SortOut);
report.SetParameterValue("par_Item", s_par_Item);
report.SetParameterValue("par_Period", s_par_Period);
report.SetParameterValue("par_PeriodCount", s_par_PeriodCount);
report.SetParameterValue("par_Order", s_par_Order);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
private void ShowReport(DataTable dt,string V_FORM_TYPE,DateTime D_START_DATE,DateTime D_END_DATE,String V_ADJ_IN_DESC,String V_ADJ_OUT_DESC)
{
#region 利用CrystalReport列印報表
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/PSS031/PSS031R01.rpt"));
report.SetDataSource(dt);
report.SetParameterValue("par_Program_ID", "PSS031R");
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
report.SetParameterValue("par_YEAR_MONTH", this.SLP_YearMonth1.Text.Trim());
if (rblRptType.Items[0].Selected)
report.SetParameterValue("par_TYPE", "差異");
else
report.SetParameterValue("par_TYPE", "全部");
if (this.SLP_SKU_B.Text.Trim() == "" && this.SLP_SKU_E.Text.Trim() == "")
{
report.SetParameterValue("par_ITEM", "全部");
}
else
{
report.SetParameterValue("par_ITEM", this.SLP_SKU_B.Text.Trim() + "~" + this.SLP_SKU_E.Text.Trim());
}
if (this.SLP_ItemPeriod_B.Text.Trim() == "" && this.SLP_ItemPeriod_E.Text.Trim() == "")
{
report.SetParameterValue("par_PERIOD", "全部");
}
else
{
report.SetParameterValue("par_PERIOD", this.SLP_ItemPeriod_B.Text.Trim() + "~" + this.SLP_ItemPeriod_E.Text.Trim());
}
if (this.slp_TAX_TYPE.Text.Trim() == "" )
{
report.SetParameterValue("par_TAX_TYPE", "全部");
}
else if (this.slp_TAX_TYPE.Text.Trim() == "0")
{
report.SetParameterValue("par_TAX_TYPE", "應稅");
}
else if (this.slp_TAX_TYPE.Text.Trim() == "1")
{
report.SetParameterValue("par_TAX_TYPE", "免稅");
}
else if (this.slp_TAX_TYPE.Text.Trim() == "2")
{
report.SetParameterValue("par_TAX_TYPE", "零稅");
}
report.SetParameterValue("par_START_DATE", D_START_DATE);
report.SetParameterValue("par_END_DATE", D_END_DATE);
report.SetParameterValue("par_ADJ_IN_DESC", V_ADJ_IN_DESC);
report.SetParameterValue("par_ADJ_OUT_DESC", V_ADJ_OUT_DESC);
//檔案匯出
if (V_FORM_TYPE == "XLS")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("進銷存差異明細表.XLS", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/xls";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("進銷存差異明細表.PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
report.Close();
#endregion
}
private void ShowReportA(DataTable dtA, DataTable dtB, string V_FORM_TYPE, string V_RPT)
{
#region 利用CrystalReport列印報表
//匯出檔案名稱
string FileName = "";
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM121/" + V_RPT + "01.rpt"));
DataSet ds = new DataSet();
dtA.TableName = "VAM122_R1";
ds.Tables.Add(dtA.Copy());
dtB.TableName = "VAM122_R2";
ds.Tables.Add(dtB.Copy());
report.SetDataSource(ds);
if (V_RPT == "VAM121R")
{
FileName = "進項憑證明細表(發票)";
report.SetParameterValue("par_Program_ID", V_RPT);
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
if (this.SLP_SLPDateRange.StartDate.ToString() == "" && this.SLP_SLPDateRange.EndDate.ToString() == "")
report.SetParameterValue("par_Source_date", "全部");
else
report.SetParameterValue("par_Source_date", this.SLP_SLPDateRange.StartDate.ToString() + "-" + this.SLP_SLPDateRange.EndDate.ToString());
if (this.SLP_SLPDateRange1.StartDate.ToString() == "" && this.SLP_SLPDateRange1.EndDate.ToString() == "")
report.SetParameterValue("par_Keyin_date", "全部");
else
report.SetParameterValue("par_Keyin_date", this.SLP_SLPDateRange1.StartDate.ToString() + "-" + this.SLP_SLPDateRange1.EndDate.ToString());
if (this.SLP_VendorBase1.Text.Trim() == "" && this.SLP_VendorBase2.Text.Trim() == "")
{
report.SetParameterValue("par_Vandor", "全部");
}
else
{
report.SetParameterValue("par_Vandor", this.SLP_VendorBase1.Text.Trim() + "-" + this.SLP_VendorBase2.Text.Trim());
}
if (((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text == "ALL")
report.SetParameterValue("par_Tax_Type", "全部");
else
report.SetParameterValue("par_Tax_Type", ((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
}
else if (V_RPT == "VAM122R")
{
FileName = "進項憑證明細表(折讓)";
report.SetParameterValue("par_Program_ID_B", V_RPT);
report.SetParameterValue("par_LoginUser_B", Session["UID"].ToString() + Login_Name);
if (this.SLP_SLPDateRange.StartDate.ToString() == "" && this.SLP_SLPDateRange.EndDate.ToString() == "")
report.SetParameterValue("par_Source_date_B", "全部");
else
report.SetParameterValue("par_Source_date_B", this.SLP_SLPDateRange.StartDate.ToString() + "-" + this.SLP_SLPDateRange.EndDate.ToString());
if (this.SLP_SLPDateRange1.StartDate.ToString() == "" && this.SLP_SLPDateRange1.EndDate.ToString() == "")
report.SetParameterValue("par_Keyin_date_B", "全部");
else
report.SetParameterValue("par_Keyin_date_B", this.SLP_SLPDateRange1.StartDate.ToString() + "-" + this.SLP_SLPDateRange1.EndDate.ToString());
if (this.SLP_VendorBase1.Text.Trim() == "" && this.SLP_VendorBase2.Text.Trim() == "")
{
report.SetParameterValue("par_Vandor_B", "全部");
}
else
{
report.SetParameterValue("par_Vandor_B", this.SLP_VendorBase1.Text.Trim() + "-" + this.SLP_VendorBase2.Text.Trim());
}
if (((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text == "ALL")
report.SetParameterValue("par_Tax_Type_B", "全部");
else
report.SetParameterValue("par_Tax_Type_B", ((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
//report.SetParameterValue("par_Tax_Type_B", ((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
}
else if (V_RPT == "VAM123R")
{
FileName = "進項憑證明細表(發票與折讓)";
report.SetParameterValue("par_Program_ID", V_RPT);
report.SetParameterValue("par_Program_ID_B", V_RPT);
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
report.SetParameterValue("par_LoginUser_B", Session["UID"].ToString() + Login_Name);
if (this.SLP_SLPDateRange.StartDate.ToString() == "" && this.SLP_SLPDateRange.EndDate.ToString() == "")
{
report.SetParameterValue("par_Source_date", "全部");
report.SetParameterValue("par_Source_date_B", "全部");
}
else
{
report.SetParameterValue("par_Source_date", this.SLP_SLPDateRange.StartDate.ToString() + "-" + this.SLP_SLPDateRange.EndDate.ToString());
report.SetParameterValue("par_Source_date_B", this.SLP_SLPDateRange.StartDate.ToString() + "-" + this.SLP_SLPDateRange.EndDate.ToString());
}
if (this.SLP_SLPDateRange1.StartDate.ToString() == "" && this.SLP_SLPDateRange1.EndDate.ToString() == "")
{
report.SetParameterValue("par_Keyin_date", "全部");
report.SetParameterValue("par_Keyin_date_B", "全部");
}
else
{
report.SetParameterValue("par_Keyin_date", this.SLP_SLPDateRange1.StartDate.ToString() + "-" + this.SLP_SLPDateRange1.EndDate.ToString());
report.SetParameterValue("par_Keyin_date_B", this.SLP_SLPDateRange1.StartDate.ToString() + "-" + this.SLP_SLPDateRange1.EndDate.ToString());
}
if (this.SLP_VendorBase1.Text.Trim() == "" && this.SLP_VendorBase2.Text.Trim() == "")
{
report.SetParameterValue("par_Vandor", "全部");
report.SetParameterValue("par_Vandor_B", "全部");
}
else
{
report.SetParameterValue("par_Vandor", this.SLP_VendorBase1.Text.Trim() + "-" + this.SLP_VendorBase2.Text.Trim());
report.SetParameterValue("par_Vandor_B", this.SLP_VendorBase1.Text.Trim() + "-" + this.SLP_VendorBase2.Text.Trim());
}
if (((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text == "ALL")
{
report.SetParameterValue("par_Tax_Type", "全部");
report.SetParameterValue("par_Tax_Type_B", "全部");
}
else
{
report.SetParameterValue("par_Tax_Type", ((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
report.SetParameterValue("par_Tax_Type_B", ((DropDownList)this.SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
}
}
//檔案匯出
if (V_FORM_TYPE == "XLS")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName + ".XLS", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/xls";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName + ".PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
report.Close();
#endregion
}
/// <summary>
/// 產生報表
/// </summary>
/// <param name="dt">DataTable資料</param>
/// <param name="exp_type">產生PDF或Excel</param>
private void ShowReport(DataTable dt, string exp_type)
{
try
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM111/VAM11R.rpt"));
report.SetDataSource(dt);
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
string strParFDate = string.Empty;
string strParFSType = string.Empty;
string strParFTaxType = string.Empty;
Program_ID = this.PageCode + "R";
LoginUser = Session["UID"].ToString();
string V_REPORT_TYPE = ""; //資料選擇
if (rdoType1.Checked) V_REPORT_TYPE = "全部";
if (rdoType2.Checked) V_REPORT_TYPE = "廠商進貨";
if (rdoType3.Checked) V_REPORT_TYPE = "廠商退貨";
report.SetParameterValue("par_Program_ID", Program_ID);
report.SetParameterValue("par_LoginUser", LoginUser);
report.SetParameterValue("par_LoginName", Login_Name);
string inDate = (SLP_SLPDateRange.StartDate + ((SLP_SLPDateRange.StartDate.Length == 0 && SLP_SLPDateRange.EndDate.Length == 0) ? "" : "～") + SLP_SLPDateRange.EndDate);
if (inDate == "") inDate = "全部";
report.SetParameterValue("par_Date", "進/退貨日期：" + inDate);
//string vendor1 = ((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase1).FindControl("TextBoxName")).Text;
string vendor1 = GetVendorName(SLP_VendorBase1.Text.Trim());
//string vendor2 = ((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase2).FindControl("TextBoxName")).Text;
string vendor2 = GetVendorName(SLP_VendorBase2.Text.Trim());
if (SLP_VendorBase1.Text.Trim().Length != 0 && vendor1 == "") vendor1 = Page.Request.Form[((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase1).FindControl("TextBoxName")).UniqueID];
if (SLP_VendorBase2.Text.Trim().Length != 0 && vendor2 == "") vendor2 = Page.Request.Form[((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase2).FindControl("TextBoxName")).UniqueID];
string vendor = (SLP_VendorBase1.Text + vendor1 + ((SLP_VendorBase1.Text.Trim().Length == 0 && SLP_VendorBase2.Text.Trim().Length == 0) ? "" : "～") + SLP_VendorBase2.Text + vendor2);
if (SLP_VendorBase1.Text.Trim().Length == 0 && SLP_VendorBase2.Text.Trim().Length == 0) vendor = "全部";
report.SetParameterValue("par_Vendor", "供應商：" + vendor);
report.SetParameterValue("par_Tax", "課稅別：" + ((DropDownList)SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
report.SetParameterValue("par_Type", "資料選擇：" + V_REPORT_TYPE);
//檔案匯出
if (exp_type == "btnExport")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = "供應商暫估貨款明細表.xls";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = "供應商暫估貨款明細表.PDF";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
}
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_Trans = string.Empty;
string s_par_TruckNo = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_Trans = this.SLP_TRANS_NO_S.Text + " " + ((DropDownList)SLP_TRANS_NO_S.FindControl("D1")).SelectedItem.Text + " ~ " +
this.SLP_TRANS_NO_E.Text + " " + ((DropDownList)SLP_TRANS_NO_E.FindControl("D1")).SelectedItem.Text;
s_par_TruckNo = this.TRUCK_NO_S.Text + " ~ " + this.TRUCK_NO_E.Text;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_Trans", s_par_Trans);
report.SetParameterValue("par_TruckNo", s_par_TruckNo);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
report.Close();
}
private void loadQTY()
{
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsData = default(ADODB.Recordset);
//Dim Report As New cryItemItemCompareQty
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryItemItemCompareQty.rpt");
ReportNone.Load("cryNoRecords.rpt");
modReport.cnnDBreport.Execute("DELETE LinkItem.* FROM LinkItem;");
rs = modReport.getRSreport(ref "SELECT * FROM Link Where Link_SectionID=3");
while (!(rs.EOF)) {
if (!string.IsNullOrEmpty(rs.Fields("Link_SQL").Value)) {
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT " + rs.Fields("LinkID").Value + ", DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales FROM DayEndStockItemLnk INNER JOIN aStockItem ON DayEndStockItemLnk.DayEndStockItemLnk_StockItemID = aStockItem.StockItemID WHERE " + rs.Fields("Link_SQL").Value + ";");
}
rs.moveNext();
}
modReport.cnnDBreport.Execute("INSERT INTO LinkItem ( LinkItem_LinkID, LinkItem_DayEndID, LinkItem_Value ) SELECT theJoin.LinkID, theJoin.DayEndID, 0 FROM LinkItem RIGHT JOIN [SELECT Link.LinkID, DayEnd.DayEndID From Link, DayEnd WHERE (((Link.Link_SQL)<>'') AND ((Link.Link_SectionID)=3))]. AS theJoin ON (LinkItem.LinkItem_DayEndID = theJoin.DayEndID) AND (LinkItem.LinkItem_LinkID = theJoin.LinkID) WHERE (((LinkItem.LinkItem_LinkID) Is Null));");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading"));
rs.Close();
rs = modReport.getRSreport(ref "SELECT [Link].[LinkID], [Link].[Link_Name], Sum([LinkItem].[LinkItem_Value]) AS SumOfLinkItem_Value FROM Link INNER JOIN LinkItem ON [Link].[LinkID]=[LinkItem].[LinkItem_LinkID] WHERE ((([Link].[Link_SQL])<>'') And (([Link].[Link_SectionID])=3)) GROUP BY [Link].[LinkID], [Link].[Link_Name] ORDER BY [Link].[LinkID];");
if (rs.BOF | rs.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
rsData = modReport.getRSreport(ref "SELECT LinkItem.*, Format([DayEnd_Date],'yyyy mm dd ddd') AS dateName, DayEnd.DayEnd_Date FROM DayEnd INNER JOIN LinkItem ON DayEnd.DayEndID = LinkItem.LinkItem_DayEndID ORDER BY DayEnd.DayEnd_Date;");
if (rsData.BOF | rsData.EOF) {
ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString);
ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString);
My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone;
My.MyProject.Forms.frmReportShow.mReport = ReportNone;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
//UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"'
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsData);
System.Windows.Forms.Application.DoEvents();
//Report.VerifyOnEveryPrint = True
System.Windows.Forms.Application.DoEvents();
My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report;
My.MyProject.Forms.frmReportShow.mReport = Report;
My.MyProject.Forms.frmReportShow.sMode = "0";
My.MyProject.Forms.frmReportShow.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
}
