//----------------------------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();
}
void SetTrinhky(CrystalDecisions.Shared.ParameterFields p)
{
try
{
CrystalDecisions.Shared.ParameterFields p0 = new CrystalDecisions.Shared.ParameterFields();
for (int i = 0; i <= p.Count - 1; i++)
{
if (p[i].ParameterFieldName.ToUpper() == "txtTrinhky".ToUpper())
{
if (mv_bSetContent)
{
string NoidungTK = mv_oNguoiKy.mv_NOI_DUNG;
NoidungTK = NoidungTK.Replace("&DIADIEM", Utility.GetRtfUnicodeEscapedString(Utility.Laygiatrithamsohethong("DIA_DIEM", "Địa điểm", false)));
NoidungTK = NoidungTK.Replace("&NHANVIEN", Utility.GetRtfUnicodeEscapedString(globalVariables.gv_strTenNhanvien));
NoidungTK = NoidungTK.Replace("&NGAYIN", Utility.GetRtfUnicodeEscapedString(Utility.FormatDateTimeWithLocation(globalVariables.SysDate, globalVariables.gv_strDiadiem)));
NoidungTK = NoidungTK.Replace("&NGUOIIN", Utility.GetRtfUnicodeEscapedString(globalVariables.gv_strTenNhanvien));
NoidungTK = NoidungTK.Replace("&NGAY", Strings.Right("0" + NGAY.Day.ToString(), 2)).Replace("&THANG", Strings.Right("0" + NGAY.Month.ToString(), 2)).Replace("&NAM", NGAY.Year.ToString());
RptDoc.SetParameterValue(p[i].ParameterFieldName, NoidungTK);
}
else
{
RptDoc.SetParameterValue(p[i].ParameterFieldName, "");
}
break;
}
}
}
catch (Exception ex)
{
Utility.ShowMsg("SetTrinhky-->" + ex.Message);
}
}
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();
}
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 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();
}
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();
}
}
//----------------------------End Report parameters declare---------------------------------------------
public R_PR()
{
InitializeComponent();
Load += (s, e) =>
{
////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
rpt.Load(Path + "/../../RPT/Rpt_PR.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("@PRNO", PRNO);
crvReport.ReportSource = rpt;
//}
//else
// XtraMessageBox.Show("No record can be displayed... ", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
};
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
}
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 report_WHTransfer()
{
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryWHTransfer.rpt");
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT Company.Company_Name FROM Company;");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
Report.SetParameterValue("txtFrom", lblWHA.Text);
Report.SetParameterValue("txtTo", lblWHB.Text);
Report.SetParameterValue("txtPerson", My.MyProject.Forms.frmMenu.lblUser);
sql = "SELECT HandheldWHTransfer.HandHeldID, StockItem.StockItem_Name, HandheldWHTransfer.Quantity";
sql = sql + " FROM HandheldWHTransfer INNER JOIN StockItem ON HandheldWHTransfer.HandHeldID = StockItem.StockItemID WHERE (((HandheldWHTransfer.WHouseID)=" + lWHB + "));";
Debug.Print(sql);
rs = modRecordSet.getRS(ref sql);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(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(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();
}
public static void PostaviParametreIzvjestaja(ref CrystalDecisions.CrystalReports.Engine.ReportDocument rpt)
{
string str = string.Empty;
string str2 = string.Empty;
string str3 = string.Empty;
string str4 = string.Empty;
string str5 = string.Empty;
string str6 = string.Empty;
string str7 = string.Empty;
SqlConnection connection = new SqlConnection(Mipsed7.Core.ApplicationDatabaseInformation.ConnectionString);
string cmdText = "SELECT TOP 1 korisnik1naziv,korisnik1adresa,korisnik1mjesto,kontakttelefon,kontaktfax,email,korisnikoib FROM KORISNIK";
try
{
connection.Open();
SqlDataReader reader = new SqlCommand(cmdText)
{
CommandType = CommandType.Text, Connection = connection
}.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
str6 = Conversions.ToString(reader["KORISNIK1NAZIV"]);
str4 = Conversions.ToString(reader["KORISNIK1ADRESA"]);
str5 = Conversions.ToString(reader["KORISNIK1MJESTO"]);
str3 = Conversions.ToString(reader["KONTAKTTELEFON"]);
str2 = Conversions.ToString(reader["KONTAKTFAX"]);
str = Conversions.ToString(reader["EMAIL"]);
str7 = Conversions.ToString(reader["KORISNIKOIB"]);
}
reader.Close();
}
catch (System.Exception exception1)
{
throw exception1;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
rpt.SetParameterValue("naziv", str6);
rpt.SetParameterValue("adresa", str4 + ", " + str5);
rpt.SetParameterValue("telefonfaxmail", "Telefon: " + str3 + " Fax: " + str2 + " Email: " + str);
rpt.SetParameterValue("OIB", "Osobni identifikacijski broj: " + str7);
}
private void stockitemGroup()
{
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
Report.Load("cryStockItemGrouping.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"));
Report.SetParameterValue("txtTitle", this.Text);
Report.SetParameterValue("txtFilter", this.lblHeading.Text);
rs.Close();
sql = "SELECT StockItem.StockItemID, StockItem.StockItem_Name, StockItem.StockItem_ReceiptName, StockItem.StockItem_Quantity, StockItem.StockItem_ListCost , Deposit.Deposit_Name, StockGroup.StockGroup_Name, Shrink.Shrink_Name, Supplier.Supplier_Name, PricingGroup.PricingGroup_Name FROM (((Deposit INNER JOIN (StockItem INNER JOIN Supplier ON StockItem.StockItem_SupplierID = Supplier.SupplierID) ON Deposit.DepositID = StockItem.StockItem_DepositID) INNER JOIN PricingGroup ON StockItem.StockItem_PricingGroupID = PricingGroup.PricingGroupID) INNER JOIN StockGroup ON StockItem.StockItem_StockGroupID = StockGroup.StockGroupID) INNER JOIN Shrink ON StockItem.StockItem_ShrinkID = Shrink.ShrinkID ";
sql = sql + gFilterSQL + " ORDER BY StockItem.StockItem_Name;";
rs = modRecordSet.getRS(ref sql);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(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.SetDataSource(rs, 3)
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();
}
public static void AddReportParameters(CrystalDecisions.CrystalReports.Engine.ReportDocument rpt, DataSet ds, bool CompanyName, string ReportName, bool Address1, bool Address2, bool City, bool phone, bool Email)
{
rpt.SetDataSource(ds);
if (CompanyName == true)
{
rpt.SetParameterValue("pCompanyName", CurrentCompany.Name);
}
if (ReportName != "")
{
rpt.SetParameterValue("pReportName", ReportName);
}
if (Address1 == true)
{
rpt.SetParameterValue("pAddress1", CurrentCompany.Address1);
}
if (Address2 == false)
{
rpt.SetParameterValue("pAddress2", CurrentCompany.Address2);
}
if (City == true)
{
rpt.SetParameterValue("pCity", CurrentCompany.City);
}
if (phone == true)
{
rpt.SetParameterValue("pPhone", CurrentCompany.Phone);
}
if (Email == true)
{
rpt.SetParameterValue("pEmail", CurrentCompany.Email);
}
//if (State == true)
//{
// rpt.SetParameterValue("pState", "");
//}
//if (PinCode == true)
//{
// rpt.SetParameterValue("pPinCode", CurrentCompany.Pincode);
//}
//if (Phone2 == true)
//{
// rpt.SetParameterValue("pPhone2", CurrentCompany.Phone2);
//}
//if (Fax == true)
//{
// rpt.SetParameterValue("pFax", CurrentCompany.Fax);
//}
}
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();
}
//----------------------------End Report parameters declare---------------------------------------------
public R_AI_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");
//rpt.SetParameterValue("PicPath", "D:\\Temp_Xml\\AI_Graph.jpeg");
//rpt.Load(Path + "/RPT/_LAB/Rpt_AI_RESULT_LAB.rpt");
rpt.Load(Path + "/RPT/_LAB/Rpt_AI_RESULT_Lines_AnalysisReport_LAB_VN.rpt");
//CHEK PC Name
string PCname = System.Environment.MachineName;
if (PCname == "vpv-lab-sample")
{
rpt.SetParameterValue("PicPath", @"D:\SYNC_NUTRICIEL_IMG\Temp_Xml\AI_Graph.jpeg");
}
else
{
rpt.SetParameterValue("PicPath", @"X:\Temp_Xml\AI_Graph.jpeg");
}
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
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();
}
}
void SetTrinhky(CrystalDecisions.Shared.ParameterFields p)
{
try
{
CrystalDecisions.Shared.ParameterFields p0 = new CrystalDecisions.Shared.ParameterFields();
for (int i = 0; i <= p.Count - 1; i++)
{
if (p[i].ParameterFieldName.ToUpper() == "txtTrinhky".ToUpper())
{
if (mv_bSetContent)
{
RptDoc.SetParameterValue(p[i].ParameterFieldName, mv_oNguoiKy.mv_NOI_DUNG.Replace("&NHANVIEN", globalVariables.gv_strTenNhanvien));
}
else
{
RptDoc.SetParameterValue(p[i].ParameterFieldName, "");
}
break;
}
}
}
catch (Exception ex)
{
}
}
private void SetParamAgain(CrystalDecisions.Shared.ParameterFields p)
{
try {
CrystalDecisions.Shared.ParameterFields p0 = new CrystalDecisions.Shared.ParameterFields();
for (int i = 0; i <= p.Count - 1; i++)
{
if (p[i].ParameterFieldName.ToUpper() == "TXTNGUOIKY1")
{
RptDoc.SetParameterValue(p[i].ParameterFieldName, mv_oNguoiKy.mv_NOI_DUNG.Replace("&NHANVIEN", globalVariables.gv_sStaffName));
}
else if (p[i].ParameterFieldName.ToUpper() == "TXTNGAYQT")
{
}
else
{
RptDoc.SetParameterValue(p[i].ParameterFieldName, ((CrystalDecisions.Shared.ParameterDiscreteValue)p[i].CurrentValues[0]).Value);
}
}
}
catch (Exception ex) {
}
}
private void stockitemOrderLevels()
{
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
ADODB.Recordset rs = default(ADODB.Recordset);
string sql = null;
Report.Load("cryStockItemORderLevels.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"));
Report.SetParameterValue("txtTitle", this.Text);
Report.SetParameterValue("txtFilter", this.lblHeading.Text);
rs.Close();
sql = "SELECT StockItem.StockItemID, StockItem.StockItem_Name, StockItem.StockItem_Quantity, StockItem.StockItem_MinimumStock, [StockItem_MinimumStock]/[StockItem_Quantity] AS minimumCase, StockItem.StockItem_OrderQuantity, StockItem.StockItem_OrderRounding, StockItem.StockItem_OrderDynamic, [StockItem_Quantity]=[StockItem_OrderQuantity] AS brokenPack FROM StockItem ";
sql = sql + gFilterSQL + " ORDER BY StockItem.StockItem_Name;";
rs = modRecordSet.getRS(ref sql);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(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.CRViewer1.Refresh();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
My.MyProject.Forms.frmReportShow.ShowDialog();
return;
}
//Report.Database.SetDataSource(rs, 3)
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.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();
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
ADODB.Recordset rs = 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 Report1 = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("cryNegativeProfit.rpt");
Report1.Load("crysqZero.rpt");
string lOrder = null;
string lWhere = null;
if (gRS.RecordCount)
{
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.VerifyOnEveryPrint = True
Report.Database.Tables(1).SetDataSource(gRS);
//Report.Database.SetDataSource(gRS, 3)
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 (gRSsq.RecordCount)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report1.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
rs.Close();
//Report1.VerifyOnEveryPrint = True
Report1.Database.Tables(1).SetDataSource(gRSsq);
//Report1.Database.SetDataSource(gRSsq, 3)
My.MyProject.Forms.frmReportShow.Text = Report1.ParameterFields("txtTitle").ToString;
My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report1;
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 rptPDF2(String Reporte, string Evento, string Matricula)
{
ConnectionInfo connectionInfo = new ConnectionInfo();
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, Matricula);
report.SetParameterValue(1, Evento);
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
connectionInfo.ServerName = "dsia";
connectionInfo.UserID = "ingresos";
connectionInfo.Password = "******";
SetDBLogonForReport(connectionInfo, report);
report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "FichaReferenciada");
//CR_Reportes.ReportSource = report;
//report.Close();
//report.Dispose();
}
catch (Exception ex)
{
}
finally
{
//CR_Reportes.ReportSource = report;
//CR_Reportes.Dispose();
CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
CR_Reportes.Dispose();
GC.Collect();
}
}
//----------------------------End Report parameters declare---------------------------------------------
public R_BaoCao_HuyetThanhHoc()
{
InitializeComponent();
Load += (s, e) =>
{
//txtFrDate.Text = FrDate;
//txtToDate.Text = ToDate;
dt_BaoCao_HuyetThanhHoc = BUS.BaoCao_HuyetThanhHoc_IBD(year, CTXN_ID);
//XtraMessageBox.Show("dt.Rows.Count" + dt.Rows.Count.ToString());
if (dt_BaoCao_HuyetThanhHoc.Rows.Count > 0)
{
dt_BaoCao_HuyetThanhHoc.WriteXml(XmlPath + "/dt_BaoCao_HuyetThanhHoc.xml", System.Data.XmlWriteMode.IgnoreSchema);
}
rpt.Load(Path + "/RPT/Rpt_BaoCao_HuyetThanhHoc.rpt");
rpt.SetParameterValue("CTXN_ID", CTXN_ID);
rpt.SetParameterValue("year", year);
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
//----------------------------End Report parameters declare---------------------------------------------
public R_FG_Date()
{
InitializeComponent();
Load += (s, e) =>
{
txtFrDate.Text = FrDate;
txtToDate.Text = ToDate;
dt = OFB.OF_Report_ByDate(FrDate, ToDate);
//XtraMessageBox.Show("dt.Rows.Count" + dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
dt.WriteXml(Path + "/../../Xml/OF_Report_ByDate.xml", System.Data.XmlWriteMode.IgnoreSchema);
}
rpt.Load(Path + "/../../RPT/Rpt_FG_by_Date.rpt");
rpt.SetParameterValue("P_FrDate", FrDate);
rpt.SetParameterValue("P_ToDate", ToDate);
crvReport.ReportSource = rpt;
};
action1.Print(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Print));
action1.Close(new DevExpress.XtraBars.ItemClickEventHandler(ItemClickEventHandler_Close));
}
private void rptReporte_FE(String Reporte, object[] Valores)
{
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 <= Valores.Length - 1; i++)
{
report.SetParameterValue(i, Valores[i]);
}
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
connectionInfo.ServerName = "dsia";
connectionInfo.UserID = "felectronica";
connectionInfo.Password = "******";
SetDBLogonForReport(connectionInfo, report);
report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "RepComprobanteFiscal");
//CR_Reportes.ReportSource = report;
//report.Close();
//report.Dispose();
}
catch (Exception ex)
{
}
finally
{
//CR_Reportes.Dispose();
CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
CR_Reportes.Dispose();
GC.Collect();
}
}
private void rptPDFAdjunto(String Reporte, String Nombre, String Referencia, Double 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
{
// 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, 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;
//connectionInfo.ServerName = "ucad";
//connectionInfo.UserID = "ingresos";
//connectionInfo.Password = "******";
//SetDBLogonForReport(connectionInfo, report);
string archivo = p.Server.MapPath("~") + "/ArchivoReferencia/Referencia - " + Referencia + ".PDF";
//report.SaveAs(archivo);
report.ExportToDisk(ExportFormatType.PortableDocFormat, archivo); // "FichaReferenciada-" + Nombre.Substring(0, 15));
//report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "Referencia - " + Referencia);
}
catch (Exception ex)
{
}
finally
{
CR_Reportes.ReportSource = report;
report.Close();
report.Dispose();
CR_Reportes.Dispose();
GC.Collect();
}
}
private void SupplierStatement(ref int id)
{
ADODB.Connection lConn = new ADODB.Connection();
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
ADODB.Recordset rsTransaction = default(ADODB.Recordset);
string lAddress = null;
string lNumber = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
Report.Load("crySupplierStatement.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"));
lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", ");
if (Strings.Right(lAddress, 2) == ", ")
{
lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2);
}
Report.Database.Tables(1).SetDataSource(rs);
Report.SetParameterValue("txtAddress", lAddress);
lNumber = "";
if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value))
{
lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value;
}
if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value))
{
if (!string.IsNullOrEmpty(lNumber))
{
lNumber = lNumber + " / ";
}
lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value;
}
if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value))
{
if (!string.IsNullOrEmpty(lNumber))
{
lNumber = lNumber + " / ";
}
lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value;
}
Report.SetParameterValue("txtNumbers", lNumber);
lConn = modRecordSet.openConnectionInstance(ref "month" + gMonth - this.cmbMonth.SelectedIndex + ".mdb");
if (lConn == null)
{
return;
}
rsCompany = new ADODB.Recordset();
rsCompany.Open("SELECT * FROM Supplier Where SupplierID = " + id, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(1).SetDataSource(rsCompany);
rsTransaction = new ADODB.Recordset();
rsTransaction.Open("SELECT SupplierTransaction.SupplierTransaction_SupplierID, SupplierTransaction.SupplierTransaction_Date, SupplierTransaction.SupplierTransaction_Reference, TransactionType.TransactionType_Name, IIf([SupplierTransaction_Amount]<0,[SupplierTransaction_Amount],0) AS credit, IIf([SupplierTransaction_Amount]>=0,[SupplierTransaction_Amount],0) AS debit, SupplierTransaction.SupplierTransaction_Amount FROM SupplierTransaction INNER JOIN TransactionType ON SupplierTransaction.SupplierTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((SupplierTransaction.SupplierTransaction_SupplierID)=" + id + "));", lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(2).SetDataSource(rsTransaction);
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
ReportNone.Load("cryNoRecords.rpt");
if (rsTransaction.BOF | rsTransaction.EOF)
{
ReportNone.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
ReportNone.SetParameterValue("txtTitle", "Statement");
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;
}
My.MyProject.Forms.frmReportShow.Text = "Supplier Statement";
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"'
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);
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;
}
}
/// <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);
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;
}
}
//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();
}
public void RPT_Sub(Int64 QuotationID, string Code, Boolean _IsList)
{
DataTable dt = new DataTable();
LogoBind(dt);
mpdfFile = CurrentUser.DocumentPath + @"pdf\Quotation.pdf";
DataTable dtReport = new DataTable();
dtReport = CommSelect.SelectRecord(QuotationID, "rpt_Quotation", "Quotation - Report");
DataTable dtTNC = new DataTable();
NameValueCollection para = new NameValueCollection();
para.Add("@i_Code", Code);
para.Add("@i_TNC_Sub", "Quotation");
dtTNC = objDA.ExecuteDataTableSP("rpt_Quotation_TNC", para, false, ref mException, ref mErrorMsg, "Quotation TNC");
DataTable dtCompany = new DataTable();
dtCompany = objDA.ExecuteDataTableSP("rpt_Company", null, false, ref mException, ref mErrorMsg, "Quotation TNC");
//dtReport.TableName = "Quotation";
//dtReport.WriteXmlSchema(@"D:\Quotation.xsd");
if (CommSelect.Exception == null)
{
//if (TypeOFSale == "AMC")
//{
// if (System.IO.File.Exists(CurrentUser.ReportPath + "rptQuotation_AMC.rpt"))
// {
// CrystalDecisions.CrystalReports.Engine.ReportDocument rptDoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
// rptDoc.Load(CurrentUser.ReportPath + "rptQuotation_AMC.rpt");
// //rptDoc.SetDatabaseLogon("sa", "Un!ek3RP");
// CurrentUser.AddReportParameters(rptDoc, dtReport, "", false, false, false, false, false, false, false, false, false, false, false);
// rptDoc.Database.Tables[1].SetDataSource(dtCompany);
// rptDoc.Subreports[0].DataSourceConnections.Clear();
// rptDoc.Subreports[0].Database.Tables[0].SetDataSource(dtTNC);
// rptDoc.Refresh();
// Reports.frmReportViewer fRptView = new Reports.frmReportViewer();
// fRptView.Text = "Quotation - [Page Size: A4]";
// fRptView.crViewer.ReportSource = rptDoc;
// if (_IsList == true)
// {
// fRptView.ShowDialog();
// }
// else if (_IsList == false)
// {
// ExportOptions CrExportOptions;
// DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
// PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
// CrDiskFileDestinationOptions.DiskFileName = mpdfFile;
// CrExportOptions = rptDoc.ExportOptions;//Report document object has to be given here
// CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
// CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
// CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
// CrExportOptions.FormatOptions = CrFormatTypeOptions;
// rptDoc.Export();
// }
// }
// else
// {
// MessageBox.Show("File is not exist...");
// }
//}
//else if (TypeOFSale != "AMC")
//{
if (System.IO.File.Exists(CurrentUser.ReportPath + "rptQuotation.rpt"))
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rptDoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rptDoc.Load(CurrentUser.ReportPath + "rptQuotation.rpt");
//rptDoc.Subreports[0].DataSourceConnections.Clear();
rptDoc.Subreports[0].Database.Tables[0].SetDataSource(dtTNC);
rptDoc.Database.Tables[1].SetDataSource(dtCompany);
rptDoc.Database.Tables[2].SetDataSource(dt);
rptDoc.Refresh();
CurrentUser.AddReportParameters(rptDoc, dtReport, "", false, false, false, false, false, false, false, false, false, false, false);
CurrentUser.AddExtraParameter(rptDoc);
if (CurrentCompany.Com_Profile != null || CurrentCompany.Com_Profile.Trim() != "")
{
rptDoc.SetParameterValue("pCompanyProfile", CurrentCompany.Com_Profile);
}
Reports.frmReportViewer fRptView = new Reports.frmReportViewer();
fRptView.Text = "Quotation - [Page Size: A4]";
fRptView.crViewer.ReportSource = rptDoc;
if (_IsList == true)
{
fRptView.ShowDialog();
}
else if (_IsList == false)
{
ExportOptions CrExportOptions;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = mpdfFile;
CrExportOptions = rptDoc.ExportOptions;//Report document object has to be given here
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
rptDoc.Export();
}
}
else
{
MessageBox.Show("File is not exist...");
}
//}
}
}
/// <summary>
/// show報表
/// </summary>
private void ShowReport(DataTable Dt)
{
try
{
string strFileName = "";
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
if (TabContainer2.ActiveTabIndex == 0)
{
report.Load(Server.MapPath("./REPORT/MKT07/CRMKT07R01.rpt"));
strFileName = HttpUtility.UrlEncode("PMA(大分類)銷售查詢.PDF", System.Text.Encoding.UTF8);
}
if (TabContainer2.ActiveTabIndex == 1)
{
report.Load(Server.MapPath("./REPORT/MKT07/CRMKT07R02.rpt"));
strFileName = HttpUtility.UrlEncode("PMA(中分類)銷售查詢.PDF", System.Text.Encoding.UTF8);
}
if (TabContainer2.ActiveTabIndex == 2)
{
report.Load(Server.MapPath("./REPORT/MKT07/CRMKT07R03.rpt"));
strFileName = HttpUtility.UrlEncode("PMA(小分類)銷售查詢.PDF", System.Text.Encoding.UTF8);
}
report.SetDataSource(Dt);
string Login_Name = ((DataTable)Session["UserInfo"]).Rows[0]["Name"].ToString();
report.SetParameterValue("par_Program_ID", "MKT07");
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
report.SetParameterValue("par_StAcceptDate", SLP_MDC_START_DATE.StartDate + "~" + SLP_MDC_START_DATE.EndDate);
if (TabContainer2.ActiveTabIndex == 0)
{
report.SetParameterValue("par_ROOTDate", SLP_ROOT_NO_S.Text + Page.Request.Form[SLP_ROOT_NO_S.TextBox_Name.UniqueID] + "~" + SLP_ROOT_NO_E.Text + Page.Request.Form[SLP_ROOT_NO_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_PMADate", SLP_PMA_S.Text + Page.Request.Form[SLP_PMA_S.TextBox_Name.UniqueID] + "~" + SLP_PMA_E.Text + Page.Request.Form[SLP_PMA_E.TextBox_Name.UniqueID]);
}
if (TabContainer2.ActiveTabIndex == 1)
{
report.SetParameterValue("par_ROOTDate", SLP_ROOT_NO_S.Text + Page.Request.Form[SLP_ROOT_NO_S.TextBox_Name.UniqueID] + "~" + SLP_ROOT_NO_E.Text + Page.Request.Form[SLP_ROOT_NO_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_PMADate", SLP_PMA_S.Text + Page.Request.Form[SLP_PMA_S.TextBox_Name.UniqueID] + "~" + SLP_PMA_E.Text + Page.Request.Form[SLP_PMA_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_CATEGORYDate", SLP_CATEGORY_S.Text + Page.Request.Form[SLP_CATEGORY_S.TextBox_Name.UniqueID] + "~" + SLP_CATEGORY_E.Text + Page.Request.Form[SLP_CATEGORY_E.TextBox_Name.UniqueID]);
}
if (TabContainer2.ActiveTabIndex == 2)
{
report.SetParameterValue("par_ROOTDate", SLP_ROOT_NO_S.Text + Page.Request.Form[SLP_ROOT_NO_S.TextBox_Name.UniqueID] + "~" + this.SLP_ROOT_NO_E.Text + Page.Request.Form[SLP_ROOT_NO_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_PMADate", SLP_PMA_S.Text + Page.Request.Form[SLP_PMA_S.TextBox_Name.UniqueID] + "~" + SLP_PMA_E.Text + Page.Request.Form[SLP_PMA_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_CATEGORYDate", SLP_CATEGORY_S.Text + Page.Request.Form[SLP_CATEGORY_S.TextBox_Name.UniqueID] + "~" + SLP_CATEGORY_E.Text + Page.Request.Form[SLP_CATEGORY_E.TextBox_Name.UniqueID]);
report.SetParameterValue("par_SORTOUTDate", SLP_SORTOUT_S.Text + Page.Request.Form[SLP_SORTOUT_S.TextBox_Name.UniqueID] + "~" + SLP_SORTOUT_E.Text + Page.Request.Form[SLP_SORTOUT_E.TextBox_Name.UniqueID]);
}
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 { }
}
/// <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;
}
}
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>
/// 匯出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
}
/// <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_StAcceptDate = string.Empty;
string s_par_PickBatch = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_StAcceptDate = this.SLP_SLPDate1.Text.Trim();
s_par_PickBatch = this.txtPickBatch.Text.Trim();
if (SLP_SLPDate1.Text.Trim() != "")
{
s_par_StAcceptDate = Convert.ToDateTime(SLP_SLPDate1.Text.Trim()).ToShortDateString();
}
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_StAcceptDate", s_par_StAcceptDate);
report.SetParameterValue("par_PickBatch", s_par_PickBatch);
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();
}
/// <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
}
private void cmdPrintNew_Click(System.Object eventSender, System.EventArgs eventArgs)
{
string sql = null;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument);
ADODB.Recordset rs = default(ADODB.Recordset);
bool ltype = false;
Report.Load("cryPOSupdate.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();
Report.SetParameterValue("txtTitle", "New Catalogue Updates");
Report.SetParameterValue("txtTitle1", _lbl_0.Text + " " + this.lblInstruction.Text + ". the new POS update Instruction will be " + this.lblInstructionNew.Text + ".");
rs = modRecordSet.getRS(ref "SELECT * FROM Channel WHERE ChannelID <> 9 ORDER BY ChannelID");
while (!(rs.EOF))
{
switch (rs.Fields("ChannelID").Value)
{
case 1:
Report.SetParameterValue("txtChannel1", rs.Fields("Channel_Code"));
break;
case 2:
Report.SetParameterValue("txtChannel2", rs.Fields("Channel_Code"));
break;
case 3:
Report.SetParameterValue("txtChannel3", rs.Fields("Channel_Code"));
break;
case 4:
Report.SetParameterValue("txtChannel4", rs.Fields("Channel_Code"));
break;
case 5:
Report.SetParameterValue("txtChannel5", rs.Fields("Channel_Code"));
break;
case 6:
Report.SetParameterValue("txtChannel6", rs.Fields("Channel_Code"));
break;
case 7:
Report.SetParameterValue("txtChannel7", rs.Fields("Channel_Code"));
break;
case 8:
Report.SetParameterValue("txtChannel8", rs.Fields("Channel_Code"));
break;
}
rs.moveNext();
}
rs.Close();
sql = "TRANSFORM Sum(newItems.CatalogueChannelLnk_Price) AS SumOfCatalogueChannelLnk_Price SELECT newItems.StockItemID, newItems.StockItem_Name, newItems.CatalogueChannelLnk_Quantity FROM [SELECT StockItem.StockItemID, StockItem.StockItem_Name, POSUpdate_PriceNewSummary.CatalogueChannelLnk_ChannelID, POSUpdate_PriceNewSummary.CatalogueChannelLnk_Quantity, CatalogueChannelLnk.CatalogueChannelLnk_Price FROM CatalogueChannelLnk INNER JOIN (POSUpdate_PriceNewSummary INNER JOIN StockItem ON POSUpdate_PriceNewSummary.CatalogueChannelLnk_StockItemID = StockItem.StockItemID) ON (CatalogueChannelLnk.CatalogueChannelLnk_Quantity = POSUpdate_PriceNewSummary.CatalogueChannelLnk_Quantity) AND (CatalogueChannelLnk.CatalogueChannelLnk_ChannelID = POSUpdate_PriceNewSummary.CatalogueChannelLnk_ChannelID) AND (CatalogueChannelLnk.CatalogueChannelLnk_StockItemID = POSUpdate_PriceNewSummary.CatalogueChannelLnk_StockItemID) ";
sql = sql + "ORDER BY StockItem.StockItem_Name, POSUpdate_PriceNewSummary.CatalogueChannelLnk_ChannelID, POSUpdate_PriceNewSummary.CatalogueChannelLnk_Quantity]. AS newItems GROUP BY newItems.StockItemID, newItems.StockItem_Name, newItems.CatalogueChannelLnk_Quantity PIVOT newItems.CatalogueChannelLnk_ChannelID;";
rs = modRecordSet.getRS(ref sql);
if (rs.BOF | rs.EOF)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
Interaction.MsgBox("No items in report", MsgBoxStyle.Exclamation, _4PosBackOffice.NET.My.MyProject.Application.Info.Title);
return;
}
//Report.Database.SetDataSource(rs, 3)
//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);
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);
//判斷輸出檔案型態
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;
}
}
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs)
{
update_Renamed();
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 cryIncrement
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryIncrement.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 * 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 Increment.* FROM Increment;");
//ReportNone.Load("cryNoRecords.rpt")
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.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;
}
rsQty = modRecordSet.getRS(ref "SELECT IncrementQuantity.* From IncrementQuantity ORDER BY IncrementQuantity.IncrementQuantity_Quantity;");
RSitem = modRecordSet.getRS(ref "SELECT IncrementStockItemLnk.IncrementStockItemLnk_IncrementID, StockItem.StockItem_Name FROM IncrementStockItemLnk INNER JOIN StockItem ON IncrementStockItemLnk.IncrementStockItemLnk_StockItemID = StockItem.StockItemID ORDER BY StockItem.StockItem_Name;");
Report.Database.Tables(1).SetDataSource(rs);
Report.Database.Tables(2).SetDataSource(rsQty);
Report.Database.Tables(3).SetDataSource(RSitem);
//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();
}
// 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 cmdInv_Click(System.Object eventSender, System.EventArgs eventArgs)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
int personID = 0;
int posID = 0;
string[] lArray = null;
string lAddress = null;
//On Error Resume Next
ADODB.Connection cn = default(ADODB.Connection);
short x = 0;
string databaseName = null;
int lID = 0;
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ADODB.Recordset rs = default(ADODB.Recordset);
ADODB.Recordset rsItems = default(ADODB.Recordset);
string sql = null;
//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 (lvTransaction.FocusedItem == null)
return;
//lID = Mid(Split(Me.lvTransaction.SelectedItem.Key, "_")(0), 2)
if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]) == 0) {
Interaction.MsgBox("There is no Sale document attached!", MsgBoxStyle.Information);
System.Windows.Forms.Cursor.Current = Cursors.Default;
return;
}
lID = Convert.ToInt32(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]);
//If openConnection() Then
// frmMain.lblPath.Caption = serverPath
// closeConnection
//End If
//Set cn = openConnectionInstance()
//lMonth = cmbMonth.ItemData(cmbMonth.ListIndex)
//Dim lLineitem As lineitem
//If lMonth = gMonthEnd Then
// databaseName = ""
//Else
// databaseName = "Month" & lMonth & ".mdb"
//End If
databaseName = Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[1];
cn = modRecordSet.openConnectionInstance(ref databaseName);
if (cn == null) {
return;
}
transaction lTransaction = new transaction();
lineItem lLineitem = null;
customer lCustomer = new customer();
transactionSpecial lSpecial = new transactionSpecial();
if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 2) {
//sale
//Dim Report As New cryReceipt
//Set Report = New cryReceipt
rs = new ADODB.Recordset();
sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
lTransaction.cashierID = rs.Fields("Sale_PersonID").Value;
lTransaction.channelID = rs.Fields("Sale_ChannelID").Value;
lTransaction.managerID = rs.Fields("Sale_ManagerID").Value;
lTransaction.paymentDiscount = rs.Fields("Sale_Discount").Value;
lTransaction.paymentSlip = rs.Fields("Sale_Slip").Value;
lTransaction.paymentSubTotal = rs.Fields("Sale_SubTotal").Value;
lTransaction.paymentTender = rs.Fields("Sale_Tender").Value;
lTransaction.paymentTotal = rs.Fields("Sale_Total").Value;
lTransaction.paymentType = rs.Fields("Sale_PaymentType").Value;
lTransaction.posID = rs.Fields("Sale_POSID").Value;
lTransaction.transactionDate = rs.Fields("Sale_DatePOS").Value;
lTransaction.transactionID = rs.Fields("Sale_Reference").Value + "";
lTransaction.transactionType = "Sale";
//If prPrevSerial_p = True Then strSerial = rs("Sale_Serialref")
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
lTransaction.companyName = rs.Fields("Company_Name").Value;
//gParameters.companyName
lTransaction.footer = " ";
//gParameters.footer & ""
lTransaction.heading1 = rs.Fields("Company_PhysicalAddress").Value;
//gParameters.heading1 & ""
lTransaction.heading2 = " ";
//gParameters.heading2 & ""
lTransaction.heading3 = " ";
//gParameters.heading3 & ""
lTransaction.taxNumber = rs.Fields("Company_TaxNumber").Value;
//gParameters.taxNumber & ""
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.cashierID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.cashierName = rs.Fields("personName").Value + "";
}
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.managerID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.managerName = rs.Fields("personName").Value + "";
}
rs.Close();
sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + lTransaction.posID + "));";
rs = modRecordSet.getRS(ref sql);
//rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
if (rs.RecordCount) {
lTransaction.posName = rs.Fields("POS_Name").Value + "";
}
rs.Close();
//sql = "SELECT SaleItem.*, StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName, Catalogue.Catalogue_Barcode AS code,'Sale' as saleType FROM (StockItem INNER JOIN SaleItem ON StockItem.StockItemID = SaleItem.SaleItem_StockItemID) INNER JOIN Catalogue ON (Catalogue.Catalogue_Quantity = SaleItem.SaleItem_ShrinkQuantity) AND (SaleItem.SaleItem_StockItemID = Catalogue.Catalogue_StockItemID) WHERE (((SaleItem.SaleItem_DepositType)=0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "))"
//sql = sql & " UNION "
//sql = sql & "SELECT SaleItem.*, Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code,'Deposit' as saleType FROM Deposit INNER JOIN SaleItem ON Deposit.DepositID = SaleItem.SaleItem_StockItemID WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "));"
sql = "SELECT SaleItem.*, 'Sale' AS saleType From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_SaleID) = " + lID + "))";
sql = sql + " UNION ";
sql = sql + "SELECT SaleItem.*, 'Deposit' AS saleType From SaleItem WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
while (!(rs.EOF)) {
lLineitem = new lineItem();
lLineitem.build = 0;
lLineitem.depositType = rs.Fields("SaleItem_DepositType").Value;
lLineitem.id = rs.Fields("SaleItem_StockItemID").Value;
lLineitem.lineNo = rs.Fields("SaleItem_LineNo").Value;
lLineitem.originalPrice = rs.Fields("SaleItem_PriceOriginal").Value;
lLineitem.price = rs.Fields("SaleItem_Price").Value;
lLineitem.quantity = rs.Fields("SaleItem_Quantity").Value;
lLineitem.reversal = rs.Fields("SaleItem_Reversal").Value;
lLineitem.revoke = rs.Fields("SaleItem_Revoke").Value;
lLineitem.setBuild = rs.Fields("SaleItem_SetID").Value;
lLineitem.shrink = rs.Fields("SaleItem_ShrinkQuantity").Value;
lLineitem.vat = rs.Fields("SaleItem_Vat").Value;
lLineitem.transactionType = rs.Fields("SaleType").Value + "";
//lLineitem.code = rs("Code") & ""
switch (lLineitem.depositType) {
case 0:
sql = "SELECT StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName From StockItem WHERE (((StockItemID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("SaleItem_ShrinkQuantity").Value + " x " + rsItems.Fields("longName").Value;
lLineitem.receiptName = rs.Fields("SaleItem_ShrinkQuantity").Value + "x" + rsItems.Fields("receiptName").Value;
}
rsItems.Close();
sql = "SELECT Catalogue.Catalogue_Barcode AS code FROM Catalogue WHERE (Catalogue.Catalogue_Quantity = " + rs.Fields("SaleItem_ShrinkQuantity").Value + ") AND (Catalogue.Catalogue_StockItemID = " + rs.Fields("SaleItem_StockItemID").Value + ");";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 1:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Unit";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(U)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 2:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Empty Crate";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(E)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
case 3:
sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));";
rsItems = modRecordSet.getRS(ref sql);
if (rsItems.RecordCount) {
lLineitem.name = rs.Fields("longName").Value + "-Full Case";
lLineitem.receiptName = rs.Fields("receiptName").Value + "(F)";
lLineitem.code = rsItems.Fields("Code").Value + "";
}
rsItems.Close();
break;
}
lTransaction.lineItems.Add(ref lLineitem);
rs.MoveNext();
}
rs.Close();
sql = "SELECT Customer.*, CustomerTransaction.* FROM (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) INNER JOIN Customer ON CustomerTransaction.CustomerTransaction_CustomerID = Customer.CustomerID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
lCustomer.channelID = lTransaction.channelID;
lCustomer.creditLimit = rs.Fields("Customer_ChannelID").Value;
lCustomer.department = rs.Fields("Customer_DepartmentName").Value + "";
lCustomer.fax = rs.Fields("Customer_Fax").Value + "";
lCustomer.Key = rs.Fields("CustomerID").Value;
lCustomer.name = rs.Fields("Customer_InvoiceName").Value + "";
lCustomer.outstanding = 0;
lCustomer.person = rs.Fields("Customer_FirstName").Value + " " + rs.Fields("Customer_Surname").Value;
lCustomer.physical = rs.Fields("Customer_PhysicalAddress").Value + "";
lCustomer.postal = rs.Fields("Customer_PostalAddress").Value + "";
lCustomer.signed_Renamed = rs.Fields("CustomerTransaction_PersonName").Value;
lCustomer.telephone = rs.Fields("Customer_Telephone").Value + "";
lCustomer.terms = Convert.ToInt16(rs.Fields("Customer_Terms").Value + "");
lCustomer.tax = rs.Fields("Customer_VatNumber").Value + "";
if (rs.Fields("CustomerTransaction_TransactionTypeID").Value == 3) {
lTransaction.transactionType = "Payment";
lTransaction.paymentDiscount = 0;
}
lTransaction.customer_Renamed = lCustomer;
}
rs.Close();
sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_SaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.BOF & rs.EOF) {
} else {
lSpecial.address = rs.Fields("Consignment_Address").Value;
lSpecial.mobile = rs.Fields("Consignment_Mobile").Value;
lSpecial.name = rs.Fields("Consignment_Name").Value;
lSpecial.quote = 0;
lSpecial.saleID = lID;
lSpecial.telephone = rs.Fields("Consignment_Number").Value;
lSpecial.transactionType = "Consignment";
lTransaction.transactionSpecial_Renamed = lSpecial;
}
rs.Close();
sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_CompleteSaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.BOF & rs.EOF) {
} else {
lSpecial.address = rs.Fields("Consignment_Address").Value;
lSpecial.mobile = rs.Fields("Consignment_Mobile").Value;
lSpecial.name = rs.Fields("Consignment_Name").Value;
lSpecial.quote = 0;
lSpecial.saleID = lID;
lSpecial.telephone = rs.Fields("Consignment_Number").Value;
lSpecial.transactionType = "Consignment Complete";
lTransaction.transactionSpecial_Renamed = lSpecial;
}
printTransactionA4(ref lTransaction);
}
} else if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 3) {
//payment
//Dim Report As New cryPayment
Report.Load("cryPayment.rpt");
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
if (Information.IsDBNull(rs.Fields("Company_PhysicalAddress").Value)) {
} else {
lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", ");
if (Strings.Right(lAddress, 2) == ", ") {
lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2);
}
}
Report.SetParameterValue("txtCompanyDetails1", lAddress);
if (Information.IsDBNull(rs.Fields("Company_Telephone").Value)) {
} else {
Report.SetParameterValue("txtCompanyDetails2", rs.Fields("Company_Telephone"));
}
if (Information.IsDBNull(rs.Fields("Company_TaxNumber").Value)) {
} else {
Report.SetParameterValue("txtCompanyDetails4", "TAX NO :" + rs.Fields("Company_TaxNumber").Value);
}
//If lTransaction.heading2 <> "" Then
// Report.txtCompanyDetails2.SetText lTransaction.heading2
// If lTransaction.heading3 <> "" Then
// Report.txtCompanyDetails3.SetText lTransaction.heading3
//
// Else
// If lTransaction.taxNumber <> "" Then
// Report.txtCompanyDetails4.SetText "TAX NO :" & lTransaction.taxNumber
// End If
// End If
//Else
// If lTransaction.heading3 <> "" Then
// Report.txtCompanyDetails2.SetText lTransaction.heading3
// Else
// If lTransaction.taxNumber <> "" Then
// Report.txtCompanyDetails2.SetText "TAX NO :" & lTransaction.taxNumber
// End If
// End If
//End If
rs.Close();
Report.SetParameterValue("txtCustomer", _txtFields_2.Text);
if (!string.IsNullOrEmpty(_txtFields_6.Text)) {
lArray = Strings.Split(_txtFields_6.Text, Constants.vbCrLf);
Report.SetParameterValue("txtCustAddress1", lArray[0]);
if (Information.UBound(lArray) >= 1)
Report.SetParameterValue("txtCustAddress2", lArray[1]);
if (Information.UBound(lArray) >= 2)
Report.SetParameterValue("txtCustAddress3", lArray[2]);
if (Information.UBound(lArray) >= 3)
Report.SetParameterValue("txtCustAddress4", lArray[3]);
}
if (!string.IsNullOrEmpty(_txtFields_0.Text))
Report.SetParameterValue("txtCustAddress5", "TAX NO: " + _txtFields_0.Text);
//If LCase(databaseName) = "pricing.mdb" Then
// sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
//Else
// sql = "SELECT Sale.SaleID, POS.POS_Name, M_Person.Person_FirstName, M_Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN M_Person ON Sale.Sale_PersonID = M_Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
//End If
//sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));"
sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));";
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
posID = rs.Fields("Sale_PosID").Value;
personID = rs.Fields("Sale_PersonID").Value;
Report.SetParameterValue("txtInvoiceNumber", rs.Fields("Sale_Reference"));
Report.SetParameterValue("txtInvoiceDate", Strings.Format(rs.Fields("Sale_DatePOS").Value, "dd mmm yyyy hh:mm"));
//Report.txtPOS.SetText rs("POS_Name")
//Report.txtCashier.SetText rs("Person_FirstName") & rs("Person_LastName")
Report.SetParameterValue("txtAmount", Strings.FormatNumber(rs.Fields("Sale_Total").Value, 2));
Report.SetParameterValue("txtChange", Strings.FormatNumber(rs.Fields("Sale_Tender").Value - rs.Fields("Sale_Total").Value, 2));
// lTransaction.paymentTotal - lTransaction.paymentTender, 2)
Report.SetParameterValue("txtTender", Strings.FormatNumber(rs.Fields("Sale_Tender").Value, 2));
// lTransaction.paymentTender, 2)
//Report.txtPaidBy.SetText lTransaction.customer.signed
Report.ReportDefinition.Sections("txtPaidBy").SectionFormat.EnableSuppress = true;
rs.Close();
sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + personID + "));";
rs = modRecordSet.getRS(ref sql);
if (rs.RecordCount) {
Report.SetParameterValue("txtCashier", rs.Fields("personName").Value + "");
}
rs.Close();
sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + posID + "));";
rs = modRecordSet.getRS(ref sql);
if (rs.RecordCount) {
Report.SetParameterValue("txtPOS", rs.Fields("POS_Name").Value + "");
}
rs.Close();
sql = "SELECT CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_ReferenceID From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_TransactionTypeID)=8) AND ((CustomerTransaction.CustomerTransaction_ReferenceID)=" + lID + "));";
Debug.Print(sql);
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
if (rs.RecordCount) {
Report.SetParameterValue("txtSettlement", Strings.FormatNumber(rs.Fields("CustomerTransaction_Amount").Value, 2));
} else {
Report.SetParameterValue("txtSettlement", Strings.FormatNumber("0.00", 2));
}
My.MyProject.Forms.frmReportShow.Text = "Customer Statement";
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();
} else {
ReportNone.Load("cryNoRecords.rpt");
ReportNone.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
ReportNone.SetParameterValue("txtTitle", "Customer Statement");
//UPGRADE_WARNING: Couldn't resolve default property of object ReportNone.txtTitle. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
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;
}
}
}
/// <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;
}
}
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 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
}
/// <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>
/// 產生報表
/// </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 { }
}
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();
}
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();
}
private void CustomerStatement(ref int id)
{
ADODB.Recordset rsInterest = default(ADODB.Recordset);
ADODB.Recordset rsTransaction = default(ADODB.Recordset);
ADODB.Recordset rsCompany = default(ADODB.Recordset);
int lNumber = 0;
string lAddress = null;
ADODB.Recordset rs = new ADODB.Recordset();
string sql = null;
//Dim Report As New cryCustomerStatement
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Report.Load("cryCustomerStatement.rpt");
System.DateTime lDate = default(System.DateTime);
rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));");
//rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
//Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy")
Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "dd mmm yyyy"));
lDate = rs.Fields("MonthEnd_Date").Value;
rs.Close();
rs = modRecordSet.getRS(ref "SELECT * FROM Company");
lDate = System.Date.FromOADate(lDate.ToOADate() + 10);
lDate = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1);
lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1);
//Report.txtPaymentDate.SetText Format(lDate, "dd mmm yyyy")
lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", ");
if (Strings.Right(lAddress, 2) == ", ")
{
lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2);
}
Report.Database.Tables(1).SetDataSource(rs);
Report.SetParameterValue("txtAddress", lAddress);
lNumber = "";
if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value))
{
lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value;
}
if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value))
{
if (!string.IsNullOrEmpty(lNumber))
{
lNumber = lNumber + " / ";
}
lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value;
}
if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value))
{
if (!string.IsNullOrEmpty(lNumber))
{
lNumber = lNumber + " / ";
}
lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value;
}
Report.SetParameterValue("txtNumbers", lNumber);
//New banking details
if (Information.IsDBNull(rs.Fields("Company_BankName").Value))
{
}
else
{
Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName"));
}
if (Information.IsDBNull(rs.Fields("Company_BranchName").Value))
{
}
else
{
Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName"));
}
if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value))
{
}
else
{
Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode"));
}
if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value))
{
}
else
{
Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber"));
}
//...................
rsCompany = new ADODB.Recordset();
rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(2).SetDataSource(rsCompany);
rsTransaction = new ADODB.Recordset();
//rsTransaction.Open "SELECT CustomerTransaction.*, TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//Report.Database.Tables(3).SetDataSource rsTransaction, 3
if (rsTransaction.BOF | rsTransaction.EOF)
{
rsTransaction = new ADODB.Recordset();
rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
Report.Database.Tables(3).SetDataSource(rsTransaction);
//Exit Sub
}
else
{
Report.Database.Tables(3).SetDataSource(rsTransaction);
}
if (rsTransaction.BOF | rsTransaction.EOF)
{
return;
}
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//If rsInterest.BOF Or rsInterest.EOF Then
if (rsInterest.RecordCount > 0)
{
//Report.Field20.Top = 280
//Report.Field21.Top = 280
//Report.Field22.Top = 280
//Report.Field23.Top = 280
Report.Database.Tables(4).SetDataSource(rsInterest);
}
else
{
rsInterest = new ADODB.Recordset();
rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
//Report.Field20.Suppress = True
//Report.Field21.Suppress = True
//Report.Field22.Suppress = True
//Report.Field23.Suppress = True
Report.Database.Tables(4).SetDataSource(rsInterest);
//Exit Sub
//Set rsInterest = New Recordset
//rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText
}
//Report.PrintOut(False, 1)
Report.PrintToPrinter(1, false, 0, 0);
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
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 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;
}
}
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;
}
}
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>
/// 利用 CrystalReport 列印報表
/// </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);
#region 取得各輸入值
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_ST_Accept_Date = string.Empty;
string s_par_Trans_No_S = string.Empty;
string s_par_Trans_No_E = string.Empty;
string s_par_Trans_Vendor_S = string.Empty;
string s_par_Trans_Vendor_E = string.Empty;
string s_par_Route_S = string.Empty;
string s_par_Route_E = string.Empty;
string s_par_StoreChain_S = string.Empty;
string s_par_StoreChain_E = string.Empty;
string s_par_Report = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_ST_Accept_Date = SLP_SLPDate.Text;
s_par_Trans_No_S = SLP_Trans_No_S.Text + " " + ((DropDownList)SLP_Trans_No_S.FindControl("D1")).SelectedItem.Text;
s_par_Trans_No_E = SLP_Trans_No_E.Text + " " + ((DropDownList)SLP_Trans_No_E.FindControl("D1")).SelectedItem.Text;
s_par_Trans_Vendor_S = SLP_Trans_Vendor_S.Text + " " + ((DropDownList)SLP_Trans_Vendor_S.FindControl("D1")).SelectedItem.Text;
s_par_Trans_Vendor_E = SLP_Trans_Vendor_E.Text + " " + ((DropDownList)SLP_Trans_Vendor_E.FindControl("D1")).SelectedItem.Text;
if (((TextBox)SLP_Route_S.FindControl("TextBoxCode")).Text != "")
{
s_par_Route_S = ((TextBox)SLP_Route_S.FindControl("TextBoxCode")).Text + " " + Request[SLP_Route_S.TextBox_Name.UniqueID].ToString();
}
else
{
s_par_Route_S = "";
}
if (((TextBox)SLP_Route_E.FindControl("TextBoxCode")).Text != "")
{
s_par_Route_E = ((TextBox)SLP_Route_E.FindControl("TextBoxCode")).Text + " " + Request[SLP_Route_E.TextBox_Name.UniqueID].ToString();
}
else
{
s_par_Route_E = "";
}
if (((TextBox)SLP_StoreChain_S.FindControl("TextBoxCode")).Text != "")
{
s_par_StoreChain_S = ((TextBox)SLP_StoreChain_S.FindControl("TextBoxCode")).Text + " " + Request[SLP_StoreChain_S.TextBox_Name.UniqueID].ToString();
}
else
{
s_par_StoreChain_S = "";
}
if (((TextBox)SLP_StoreChain_E.FindControl("TextBoxCode")).Text != "")
{
s_par_StoreChain_E = ((TextBox)SLP_StoreChain_E.FindControl("TextBoxCode")).Text + " " + Request[SLP_StoreChain_E.TextBox_Name.UniqueID].ToString();
}
else
{
s_par_StoreChain_E = "";
}
s_par_Report = rblPrintType.SelectedItem.Text;
#endregion
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_ST_Accept_Date", s_par_ST_Accept_Date);
report.SetParameterValue("par_Trans_No", s_par_Trans_No_S + " ～ " + s_par_Trans_No_E);
report.SetParameterValue("par_Trans_Vendor", s_par_Trans_Vendor_S + " ～ " + s_par_Trans_Vendor_E);
report.SetParameterValue("par_Routee", s_par_Route_S + " ～ " + s_par_Route_E);
report.SetParameterValue("par_StoreChain", s_par_StoreChain_S + " ～ " + s_par_StoreChain_E);
report.SetParameterValue("par_Report", s_par_Report);
#region 判斷輸出檔案型態並輸出
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;
#endregion
}
report.Close();
}
/// <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 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 { }
}
