/// <summary>
/// 產生PDF檔案
/// </summary>
private void RrintToPDF()
{
// 匯出檔名的設定
string sReportFileName = @"\rpt\T022_rpt.rpt";
string sMapPath = Server.MapPath("");
string sHost = Request.Url.Host;
string sApplicationPath = Request.ApplicationPath;
string reportID = DBCenter.GetSystemID();
string sExportFileName = Server.MapPath("../../") + @"PDF\T022_rpt" + reportID + ".pdf";
string strPath = sMapPath + sReportFileName;
string ReportPath = "http://" + sHost + sApplicationPath + "/PDF/T022_rpt" + reportID + ".pdf";
DataSet dsLot = (DataSet)Session["T022View"];
rptdoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rptdoc.Load(strPath);
rptdoc.SetDataSource(dsLot);
// 設定匯出路徑及檔名
DiskFileDestinationOptions df = new DiskFileDestinationOptions();
df.DiskFileName = sExportFileName;
rptdoc.ExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
rptdoc.ExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;
rptdoc.ExportOptions.DestinationOptions = df;
rptdoc.Export();
rptdoc.Dispose();
//打開文件
Response.Write("<script>window.open('" + ReportPath + "','_blank','resizable,scrollbars=no,menubar=no,toolbar=no,location=no,status=no',false);</script> ");
Response.Write("<script>window.close();</script>");
}
/// <summary>
/// 病区病人欠费一览表
/// </summary>
public void m_mthShowInHospitalAdviceCharge()
{
if (this.m_objViewer.m_txtAREAID_CHR.Tag == null && this.m_objViewer.radioButton3.Checked)
{
return;
}
if (this.m_objViewer.m_txtPatientName.Text == "")
{
MessageBox.Show("先查询病人");
return;
}
this.m_objViewer.Cursor = Cursors.WaitCursor;
DataTable dtbResult = new DataTable();
rptInHospitalLog = new ReportDocument();
rptInHospitalLog.Load(@"Report\rptPatientChargeInAdvice.rpt");
string groupid = "";
if (this.m_objViewer.m_cboChargeType.SelectedIndex >= 0)
{
if (this.m_objViewer.m_cboChargeType.SelectedIndex == 0)
{
groupid = "";
}
else
{
groupid = ((DataTable)this.m_objViewer.m_cboChargeType.Tag).Rows[this.m_objViewer.m_cboChargeType.SelectedIndex - 1]["groupid_chr"].ToString();
}
}
long lngRes = 0;
if (groupid == "")
{
lngRes = objSvc.m_lngGetPatientDebtDetail(this.m_objViewer.m_dtpStatDate.Value, this.m_objViewer.m_dtpEnd.Value, Registerid, out dtbResult);
}
else
{
string[] types;
lngRes = objSvc.m_lngGetChargeItemTypesByConfigGroupID("0003", groupid, out types);
lngRes = objSvc.m_lngGetPatientDebtDetail(types, this.m_objViewer.m_dtpStatDate.Value, this.m_objViewer.m_dtpEnd.Value, Registerid, out dtbResult);
}
if (lngRes > 0)
{
rptInHospitalLog.SetDataSource(dtbResult);
}
else
{
return;
}
rptInHospitalLog.DataDefinition.FormulaFields["AreaName"].Text = "'" + this.m_objViewer.m_txtAREAID_CHR.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["BedNo"].Text = "'" + this.m_objViewer.m_cboBedNo.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["InhospitalNo"].Text = "'" + this.m_objViewer.m_txtInpatientNo.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["Name"].Text = "'" + this.m_objViewer.m_txtPatientName.Text + "'";
rptInHospitalLog.DataDefinition.FormulaFields["OperatorName"].Text = "'" + this.m_objViewer.LoginInfo.m_strEmpName + "'";
rptInHospitalLog.DataDefinition.FormulaFields["Date"].Text = "'" + this.m_objViewer.m_dtpStatDate.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["EndDate"].Text = "'" + this.m_objViewer.m_dtpEnd.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["ReportName"].Text = "'" + this.ReportName + "'";
this.m_objViewer.m_crvPatientCharheDetail.ReportSource = rptInHospitalLog;
this.m_objViewer.Cursor = Cursors.Default;
}
protected void generarrpt(object sender, EventArgs e)
{
string select = "SELECT MEMP.MEMP_CODIEMPL,MNIT.MNIT_NOMBRES,MNIT.MNIT_NOMBRE2,MNIT.MNIT_APELLIDOS,MNIT.MNIT_APELLIDO2,MNIT.MNIT_DIGITO,MNIT.MNIT_DIRECCION,MNIT.MNIT_EMAIL,MNIT.MNIT_TELEFONO,MNIT.MNIT_CELULAR, MEMP.MEMP_FECHNACI,PCIU.PCIU_NOMBRE,MNIT.MNIT_NIT,MNIT.MNIT_DIGITO,MEMP.MEMP_NUMELIBRMILI,MEMP.MEMP_CLASELIBRMILI,DBXSCHEMA.TSEXO.TSEX_NOMBRE,YEAR(CURRENT DATE) - YEAR(MEMP.MEMP_FECHNACI),MEMP.MEMP_NUMEHIJOS,MEMP.TRES_VIVIENDA,MEMP.MEMP_FECHINGRESO,MEMP.MEMP_SUELACTU,MEMP.MEMP_SUELANTER,TCON.TCON_NOMBRE,TSUBT.TSUB_NOMBRE, PDEPTO.PDEP_NOMBDPTO,TESTEMP.TEST_NOMBRE,DBXSCHEMA.TFORMAPAGO.TFOR_DESCRIPCION,DBXSCHEMA.PBANCO.PBAN_NOMBRE,MEMP.MEMP_CUENNOMI,DBXSCHEMA.PEPS.PEPS_NOMBEPS,DBXSCHEMA.PARP.PARP_NOMBARP,PFCESA.PFON_NOMBFOND,PFPENS.PFON_NOMBPENS,DBXSCHEMA.TRETEFTE.TEST_DESCRIPCION, MEMP.MEMP_PORCRETE,TSANGRE.TTIP_TIPOSANG ,PCAR.pcar_nombcarg,TCIVIL.test_nombre,MEMP.memp_fecsuelanter,memp_peripago,TPAGO.TPER_DESCP,TFPAGO.tfor_descripcion,MEMP.memp_cuennomi,PFPENS2.pfon_nombpens as pensvol";
string from = " FROM DBXSCHEMA.MEMPLEADO MEMP,DBXSCHEMA.MNIT MNIT,DBXSCHEMA.PCIUDAD PCIU,DBXSCHEMA.TSEXO,DBXSCHEMA.TCONTRATONOMINA TCON,DBXSCHEMA.TSUBSIDIOTRANSPORTE TSUBT ,DBXSCHEMA.PDEPARTAMENTOEMPRESA PDEPTO,DBXSCHEMA.TESTADOEMPLEADO TESTEMP, DBXSCHEMA.TFORMAPAGO,DBXSCHEMA.PBANCO,DBXSCHEMA.PEPS,DBXSCHEMA.PARP,DBXSCHEMA.PFONDOPENSION PFPENS,DBXSCHEMA.PFONDOPENSION PFPENS2, DBXSCHEMA.PFONDOCESANTIAS PFCESA,DBXSCHEMA.TRETEFTE,DBXSCHEMA.TTIPOSANGRE TSANGRE,DBXSCHEMA.PCARGOEMPLEADO PCAR,dbxschema.testadocivil TCIVIL,dbxschema.TPERIPAGO TPAGO,DBXSCHEMA.TFORMAPAGO TFPAGO";
string where = " WHERE MEMP.MEMP_CODIEMPL='" + DDLEMPLEADO.SelectedValue + "' AND PFPENS.pfon_codipens=MEMP.pfon_codipens AND PFPENS2.pfon_codipens=MEMP.pfon_codipensvolu AND TFPAGO.tfor_pago=memp.memp_formpago AND TPAGO.tper_peri=MEMP.memp_peripago AND TCIVIL.test_estacivil=memp.test_estacivil AND MEMP.PCAR_CODICARGO=PCAR.PCAR_CODICARGO AND MEMP.MNIT_NIT=MNIT.MNIT_NIT and memp.ttip_secuencia= TSANGRE.ttip_secuencia AND MEMP.PCIU_LUGANACI=PCIU.PCIU_CODIGO AND MEMP.TSEX_CODIGO=DBXSCHEMA.TSEXO.TSEX_CODIGO AND MEMP.TCON_CONTRATO= TCON.TCON_CONTRATO AND MEMP.TSUB_CODIGO=TSUBT.TSUB_CODIGO AND MEMP.PDEP_CODIDPTO= PDEPTO.PDEP_CODIDPTO AND MEMP.TEST_ESTADO= TESTEMP.TEST_ESTADO AND MEMP.MEMP_FORMPAGO=DBXSCHEMA.TFORMAPAGO.TFOR_PAGO AND MEMP.PBAN_CODIGO=DBXSCHEMA.PBANCO.PBAN_CODIGO AND MEMP.PEPS_CODIEPS=DBXSCHEMA.PEPS.PEPS_CODIEPS AND MEMP.PARP_CODIARP=DBXSCHEMA.PARP.PARP_CODIARP AND MEMP.PFON_CODICESA=PFCESA.PFON_CODIFOND AND MEMP.MEMP_TESTRETE=DBXSCHEMA.TRETEFTE.TEST_RETE";
string nomEmpleado = DBFunctions.SingleData("SELECT NOMBRE FROM VMNIT VM, MEMPLEADO ME WHERE VM.MNIT_NIT = ME.MNIT_NIT AND ME.MEMP_CODIEMPL = '" + DDLEMPLEADO.SelectedValue + "'");
DataSet ds = new DataSet();
DBFunctions.Request(ds, IncludeSchema.NO, select + from + where + ";select cemp_nombre,cemp_nombcome,mnit_nit from dbxschema.cempresa");
if (ds.Tables[0].Rows.Count > 0)
{
ds.WriteXmlSchema(Path.Combine(Request.PhysicalApplicationPath, "schemas/Nomina.InfyConsultas.rpte_ImpHojaVida.xsd"));
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/Nomina.InfyConsultas.rpte_ImpHojaVida.rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.InfyConsultas.rpte_ImpHojaVida.doc"));
Response.Clear();
Response.ContentType = "application/msword";
Response.AddHeader("Content-Disposition", "attachment; filename= " + nomEmpleado + ".doc");
Response.ContentType = "application/msword";
Response.WriteFile(Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.InfyConsultas.rpte_ImpHojaVida.doc"));
Response.Flush();
Response.Close();
}
else
{
Utils.MostrarAlerta(Response, "Existe un prolema con la consulta, el vendedor no tiene la información completa");
}
}
/// <summary>
/// 病区病人欠费一览表
/// </summary>
public void m_mthShowInHospitalDebtLog()
{
if (this.m_objViewer.m_txtAREAID_CHR.Tag == null)
{
this.m_objViewer.m_txtAREAID_CHR.Tag = "";
this.m_objViewer.m_txtAREAID_CHR.Text = "全院";
}
this.m_objViewer.Cursor = Cursors.WaitCursor;
DataTable dtbResult = new DataTable();
rptInHospitalLog = new ReportDocument();
rptInHospitalLog.Load(@"Report\rptDebtView.rpt");
rptInHospitalLog.DataDefinition.FormulaFields["AreaName"].Text = "'" + this.m_objViewer.m_txtAREAID_CHR.Text.Trim() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["StatDate"].Text = "'" + this.m_objViewer.m_StatDate.Value.ToShortDateString() + "'";
rptInHospitalLog.DataDefinition.FormulaFields["operatorname"].Text = "'" + this.m_objViewer.LoginInfo.m_strEmpName + "'";
int type = -1;
if (this.m_objViewer.radioButton1.Checked)
{
type = 1;
}
if (this.m_objViewer.radioButton2.Checked)
{
type = 0;
}
long lngRes = objSvc.m_lngGetPatientDebt(type, (string)this.m_objViewer.m_txtAREAID_CHR.Tag, "", this.m_objViewer.m_StatDate.Value, "", "", out dtbResult);
if (lngRes > 0)
{
rptInHospitalLog.SetDataSource(dtbResult);
this.m_objViewer.m_ctrvPatientDebtReport.ReportSource = rptInHospitalLog;
}
this.m_objViewer.Cursor = Cursors.Default;
}
/// <summary>
/// 按账务期统计
/// </summary>
public void m_mthButtonClickToStatistic()
{
string p_strPriodId = "";
clsPeriod_VO[] objPriodItems = this.m_objViewer.m_cboSelPeriod.Tag as clsPeriod_VO[];
if (this.m_objViewer.m_cboSelPeriod.SelectedItem == null)
{
MessageBox.Show("请选择账务期");
return;
}
if (this.m_objViewer.m_cboSelPeriod.Text != "所有财务期的数据")
{
p_strPriodId = objPriodItems[this.m_objViewer.m_cboSelPeriod.SelectedIndex - 1].m_strPeriodID;
}
DataTable dtbStatistic;
long lngRes = 0;
lngRes = this.m_objManage.m_lngGetStatiticsData(out dtbStatistic, p_strPriodId);
System.Data.DataColumn dc = new DataColumn("SEQUENCEID");
dtbStatistic.Columns.Add(dc);
if (dtbStatistic.Rows.Count != 0)
{
for (int j1 = 0; j1 < dtbStatistic.Rows.Count; j1++)
{
dtbStatistic.Rows[j1]["SEQUENCEID"] = j1 + 1;
}
}
if (lngRes < 0)
{
return;
}
else
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rpt.Load("Report\\CrystalReportHisMedInOrderStaticstic.rpt");
// HISMedTypeManage.Rpt.CrystalReportHisMedInOrderStaticstic rpt = new HISMedTypeManage.Rpt.CrystalReportHisMedInOrderStaticstic();
((TextObject)rpt.ReportDefinition.ReportObjects["Text11"]).Text = this.m_objViewer.m_cboSelPeriod.SelectedText;
double totalMoney = 0;
if (dtbStatistic.Rows.Count > 0)
{
for (int i1 = 0; i1 < dtbStatistic.Rows.Count; i1++)
{
totalMoney += Convert.ToDouble(dtbStatistic.Rows[i1]["TOLMNY_MNY"].ToString().Trim());
}
//((TextObject)rpt.ReportDefinition.ReportObjects["Text9"]).Text = totalMoney.ToString("######.00");
}
else
{
//((TextObject)rpt.ReportDefinition.ReportObjects["Text9"]).Text = "0.00";
}
((TextObject)rpt.ReportDefinition.ReportObjects["Text11"]).Text = this.m_objViewer.m_cboSelPeriod.Text;
rpt.SetDataSource(dtbStatistic);
rpt.Refresh();
this.m_objViewer.m_crystalReportViewer1.ReportSource = rpt;
}
}
private void SelectDataProcessing()
{
try
{
cr.Load(Server.MapPath("Crpt_CheckPlantReceivingData.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
CrystalDecisions.CrystalReports.Engine.TextObject t5;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
t5 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Title"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
if (chk_DataProcessing.Checked == true)
{
t5.Text = "Data Receiving Report";
//str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurementimport WHERE Company_Code='"+ ccode +"' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' ORDER BY Prdate,Sessions";
str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions,Sample_No,ISNULL(RNotupdate,0) AS RNotupdate,ISNULL(Rupdate,0) AS Rupdate FROM (SELECT Prdate,Sessions,ISNULL(Sample_No,0) AS Sample_No,ISNULL(RNotupdate,0) AS RNotupdate FROM (SELECT COUNT(Agent_id) AS Sample_No,(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' GROUP BY Sessions,Prdate ) AS t1 LEFT JOIN (SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate1,Sessions AS sess1,('1') AS RNotupdate FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' AND Remarkstatus=1)AS t2 ON t1.Prdate=t2.Prdate1 AND t1.Sessions=t2.sess1) AS t3 LEFT JOIN (SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate2,Sessions AS sess2,('2') AS Rupdate FROM Procurementimport WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' AND Remarkstatus=2)t4 ON t3.Prdate=t4.Prdate2 AND t3.Sessions=t4.sess2 ORDER BY Prdate,Sessions";
}
else
{
t5.Text = "Data Processing Report";
str = "SELECT DISTINCT(Convert(nvarchar, Prdate,103)) AS Prdate,Sessions FROM Procurement WHERE Plant_Code='" + pcode + "' and Prdate between '" + d1 + "' and '" + d2 + "' ORDER BY Prdate,Sessions";
}
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
/// <summary>
/// Запуск формы отчета
/// </summary>
public void ReportGenerate()
{
DataTable DT = new DataTable();
Config cConf = new Config();
string DirPath = cConf.getappSettings(Config.SettingField.DataSyncDir.ToString());
string fileName = DirPath + "/rep_" + _repName + ".csv"; //DirPath +
try
{
DT = UniTerm.Program.GetDataTableFromCsv(fileName, false);
myReportDocument.SetDataSource(DT);
ReportViewer.ReportSource = myReportDocument;
}
catch (Exception e)
{
MessageBox.Show(_repName + "\n" + e.Message + "\n(" + fileName + ")", "Ошибка при считывании файа отчета", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
//}
}
protected void MostrarReporte(string ruta)
{
//mostrar el reporte en la pagina.
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/" + ruta + ".rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/" + ruta + ".doc"));
}
protected void Page_Init(object sender, EventArgs e)
{
if (Session["CurrentSchoolYearId"] == null)
{
Response.Redirect("~/Login.aspx");
}
crdata.Report = rpt;
rpt1 = crdata.ReportDocument;
DataSet dsReport = new xmlClassWiseStudentAttendTime();
DataSet ds = new DataSet();
string SearchText = "";
rpt.FileName = Server.MapPath("rptDeposit.rpt");
DayCareBAL.FamilyPaymentService proxyPayment = new DayCareBAL.FamilyPaymentService();
if (!String.IsNullOrEmpty(Request.QueryString["StartDate"]) && String.IsNullOrEmpty(Request.QueryString["EndDate"]))
{
SearchText += " l.transactiondate>='" + Request.QueryString["StartDate"].ToString() + "'";
}
if (String.IsNullOrEmpty(Request.QueryString["StartDate"]) && !String.IsNullOrEmpty(Request.QueryString["EndDate"]))
{
SearchText += " l.transactiondate<='" + Request.QueryString["EndDate"].ToString().Replace("00:00:00 AM", "11:59:58 PM") + "'";
}
else if (!String.IsNullOrEmpty(Request.QueryString["EndDate"]) && !String.IsNullOrEmpty(Request.QueryString["StartDate"]))
{
SearchText += " l.transactiondate>='" + Request.QueryString["StartDate"].ToString() + "' and l.transactiondate<='" + Request.QueryString["EndDate"].ToString().Replace("00:00:00 AM", "11:59:58 PM") + "'";
}
string StartDate = string.Empty;
string EndDate = string.Empty;
if (Request.QueryString["StartDate"] != "")
{
StartDate = Convert.ToDateTime(Request.QueryString["StartDate"].ToString()).ToString("MM/dd/yyyy");
}
if (Request.QueryString["EndDate"] != "")
{
EndDate = Convert.ToDateTime(Request.QueryString["EndDate"].ToString()).ToString("MM/dd/yyyy");
}
CrystalDecisions.CrystalReports.Engine.TextObject titleText = (CrystalDecisions.CrystalReports.Engine.TextObject)rpt1.ReportDefinition.ReportObjects["Text14"];
CrystalDecisions.CrystalReports.Engine.TextObject titleTextSchool = (CrystalDecisions.CrystalReports.Engine.TextObject)rpt1.ReportDefinition.ReportObjects["Text4"];
CrystalDecisions.CrystalReports.Engine.TextObject footer = (CrystalDecisions.CrystalReports.Engine.TextObject)rpt1.ReportDefinition.ReportObjects["txtfooter"];
footer.Text = Common.GetSchoolWiseAddress(new Guid(Session["SchoolId"].ToString()));
titleText.Text = "Deposit Report From " + StartDate + " To " + EndDate;
titleTextSchool.Text = Session["SchoolName"].ToString().ToUpper();
ds = proxyPayment.LoadPaymentDeposits(SearchText, new Guid(Session["CurrentSchoolYearId"].ToString()));
dsReport.Tables["dtDiposit"].Merge(ds.Tables[0]);
rpt1.SetDataSource(dsReport.Tables["dtDiposit"]);
crp.DisplayGroupTree = false;
crp.ReportSource = rpt1;
crp.RefreshReport();
crp.DataBind();
}
private void NillBillAmountCheck1()
{
try
{
cr.Load(Server.MapPath("Crpt_NegativeBillAmountCheck.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
//str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SRNetAmt, FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound, cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS Routewiseagent INNER JOIN (SELECT G1.ARid, CAST(SUM(G1.Smkg) AS DECIMAL(18,2)) AS GSmkg, CAST(SUM(G1.Smltr) AS DECIMAL(18,2)) AS GSmltr, CAST(AVG(G1.AvgFat) AS DECIMAL(18,1)) AS GAvgFat, CAST(AVG(G1.AvgSnf) AS DECIMAL(18,1)) AS GAvgSnf, CAST(AVG(G1.AvgRate) AS DECIMAL(18,1)) AS GAvgRate, CAST(AVG(G1.Avgclr) AS DECIMAL(18,1)) AS GAvgclr, CAST(SUM(G1.Scans) AS DECIMAL(18,2)) AS GScans, CAST(SUM(G1.SAmt) AS DECIMAL(18,2)) AS GSAmt, CAST(SUM(G1.ScommAmt) AS DECIMAL(18,2)) AS GScommAmt, CAST(SUM(G1.Scatamt) AS DECIMAL(18,2)) AS GScatamt, CAST(SUM(G1.Ssplbonamt) AS DECIMAL(18,2)) AS GSsplbonamt, CAST(AVG(G1.AvgcRate) AS DECIMAL(18,2)) AS GAvgcRate, CAST(SUM(G1.Sfatkg) AS DECIMAL(18,2)) AS GSfatkg, CAST(SUM(G1.Ssnfkg) AS DECIMAL(18,2)) AS GSsnfkg, CAST(SUM(G1.SBilladv) AS DECIMAL(18,2)) AS GSBilladv, CAST(SUM(G1.SAiamt) AS DECIMAL(18,2)) AS GSAiamt, CAST(SUM(G1.SFeedamt) AS DECIMAL(18,2)) AS GSFeedamt, CAST(SUM(G1.Scanamt) AS DECIMAL(18,2)) AS GScanamt, CAST(SUM(G1.SRecoveryamt) AS DECIMAL(18,2)) AS GSRecoveryamt, CAST(SUM(G1.Sothers) AS DECIMAL(18,2)) AS GSothers, CAST(SUM(G1.Sinstamt) AS DECIMAL(18,2)) AS GSinstamt, CAST(SUM(G1.Sbalance) AS DECIMAL(18,2)) AS GSbalance, CAST(SUM(G1.SLoanAmount) AS DECIMAL(18,2)) AS GSLoanAmount, CAST(SUM(G1.SNetAmt) AS DECIMAL(18,2)) AS GSNetAmt, CAST(SUM(G1.SRound) AS DECIMAL(18,2)) AS GSround FROM (SELECT cart.ARid, ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, FLOOR (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )-( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0)) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound FROM ( SELECT * FROM ( SELECT * FROM ( SELECT agent_id AS SproAid, CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg, CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr, CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat, CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf, CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate, CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr, CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans, CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt, CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt, CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt, CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt, CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate, CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg, CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS G1 GROUP BY G1.ARid) AS Route ON Routewiseagent.Rid=Route.ARid and SNetAmt<0 and Route.ARid IS NOT NULL ORDER BY Routewiseagent.Rid ,Routewiseagent.Aid";
//loan recovery
str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,cart.Agent_Name,ISNULL(prdelo.Smkg,0) AS Smkg,ISNULL(prdelo.Smltr,0) AS Smltr,ISNULL(prdelo.AvgFat ,0) AS AvgFat,ISNULL(prdelo.AvgSnf,0) AS AvgSnf,ISNULL(prdelo.AvgRate,0) AS AvgRate,ISNULL(prdelo.Avgclr,0) AS Avgclr,ISNULL(prdelo.Scans,0) AS Scans,ISNULL(prdelo.SAmt,0) AS SAmt,ISNULL(prdelo.ScommAmt,0) AS ScommAmt,ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt,ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate,ISNULL(prdelo.Sfatkg,0) AS Sfatkg,ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg,ISNULL(prdelo.Billadv,0) AS SBilladv,ISNULL(prdelo.Ai,0) AS SAiamt,ISNULL(prdelo.Feed,0) AS SFeedamt,ISNULL(prdelo.Can,0) AS Scanamt,ISNULL(prdelo.Recovery,0) AS SRecoveryamt,ISNULL(prdelo.others,0) AS Sothers,ISNULL(prdelo.instamt,0) AS Sinstamt,ISNULL(prdelo.balance,0) AS Sbalance,ISNULL(prdelo.LoanAmount,0) AS SLoanAmount,ISNULL(prdelo.VouAmount,0) AS Sclaim,CAST( ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS DECIMAL(18,2)) AS SRNetAmt,FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt,( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) +ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound,cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (select Agent_Id AS VouAid,CAST(SUM(Amount) AS DECIMAL(18,2)) AS VouAmount from Voucher_Clear where Plant_Code='" + pcode + "' AND Clearing_Date BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_Id) AS vou ON proded.SproAid=vou.VouAid) AS pdv LEFT JOIN (SELECT ISNULL(LoAid,0) AS LoAid,ISNULL(balance,0) AS balance,ISNULL(LoanAmount,0) AS LoanAmount,(ISNULL(loanRecAmount1,0)+ ISNULL(0,0)) AS instamt FROM (SELECT LoAid1 AS LoAid,balance1 AS balance,LoanAmount1 AS LoanAmount,loanRecAmount1 FROM (SELECT Agent_id AS LoAid1,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance1,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount1 FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY Agent_id) AS Lonn LEFT JOIN (SELECT Agent_id AS LoRecAid,CAST(SUM(Paid_Amount) AS DECIMAL(18,2)) AS loanRecAmount1 FROM Loan_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND Paid_date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id) AS LonRec ON Lonn.LoAid1=LonRec.LoRecAid ) AS LoF LEFT JOIN (SELECT Agent_Id AS LoDuAid,CAST(SUM(LoanDueRecovery_Amount) AS DECIMAL(18,2)) AS loanDueRecAmount1 FROM LoanDue_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND LoanRecovery_Date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id ) AS LonDRec ON LoF.LoAid=LonDRec.LoDuAid ) AS Lon ON pdv.SproAid=Lon.LoAid ) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid ) AS fin INNER JOIN (SELECT Route_ID AS Rrid from Route_Master WHERE Plant_Code='" + pcode + "' ) AS Rp ON fin.Rid=Rp.Rrid AND fin.SNetAmt<0 order by rid,Aid ";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
public CrystalDecisions.CrystalReports.Engine.ReportDocument GetReportDocument(string repFile, DataSet pDSet)
{
try
{
if (pDSet.Tables.Count <= 0)
{
if (Config.GetConfigValue("TestRegime") == "true")
{
Log.Write(this, "DataSet Tables Count 0");
}
return(null);
}
CrystalDecisions.CrystalReports.Engine.ReportDocument oRepDoc =
new CrystalDecisions.CrystalReports.Engine.ReportDocument();
oRepDoc.Load(repFile);
if (Config.GetConfigValue("TestRegime") == "true")
{
Log.Write(this, "Report File: " + repFile + " loaded");
}
// if (m_DBLogonInfo.Server != String.Empty)
// oRepDoc.SetDatabaseLogon(m_DBLogonInfo.User, m_DBLogonInfo.Password, m_DBLogonInfo.Server, m_DBLogonInfo.Database);
/*
* crConnectionInfo.ServerName = m_DBLogonInfo.Server;
* crConnectionInfo.DatabaseName = m_DBLogonInfo.Database;
* crConnectionInfo.UserID = m_DBLogonInfo.User;
* crConnectionInfo.Password = m_DBLogonInfo.Password;
* crDatabase = oRepDoc.Database;
* crTables = crDatabase.Tables;
*
* foreach(CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
* {
* crTableLogOnInfo = crTable.LogOnInfo;
* crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
* crTable.ApplyLogOnInfo(crTableLogOnInfo);
* }
*/
oRepDoc.SetDataSource(pDSet);
if (Config.GetConfigValue("TestRegime") == "true")
{
Log.Write(this, "DataSource was set");
}
return(oRepDoc);
}
catch (Exception ex)
{
Log.Write(this, ex);
return(null);
}
}
private void Period_Despatchreport2()
{
try
{
cr.Load(Server.MapPath("Report\\Silo2.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
// CrystalDecisions.CrystalReports.Engine.TextObject t5;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
// t5 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName1"];
t1.Text = companycode + "_" + cname;
t2.Text = ddl_PlantName.SelectedItem.Value;
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t3.Text = "From " + txt_FromDate.Text.Trim();
t4.Text = "To " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
// t5.Text = rid;
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
// for acknowledgement
// str = " SELECT t1.pr_MILKKG, t1.pr_FAT,t1.pr_SNF,t1.pr_FATKG,t1.pr_SNFKG,t1.pr_RATE,t1.pr_AMOUNT, t2.dp_MILKKG, t2.dp_FAT,t2.dp_SNF,t2.dp_FATKG,t2.dp_SNFKG,t2.dp_RATE,t2.dp_AMOUNT,t3.op_MILKKG,t3.op_FAT,t3.op_SNF,t3.op_FATKG,t3.op_SNFKG,t3.op_RATE,t3.op_AMOUNT,c.cl_MILKKG,c.cl_FAT,c.cl_SNF,c.cl_FATKG,c.cl_SNFKG,c.cl_RATE,c.cl_AMOUNT FROM ( SELECT Sum(Milk_kg) as pr_MILKKG,Avg(fat) as pr_FAT,AVG(snf) as pr_SNF,sum(fat_kg)as pr_FATKG,sum(snf_kg) as pr_SNFKG,sum(rate) as pr_RATE,sum(AMOUNT) as pr_AMOUNT FROM Procurement Where prdate between '" + d1 + "' AND '" + d2 + "' and Plant_Code='" + plantcode + "')t1,(SELECT Sum(Ack_milkkg) as dp_MILKKG,Avg(Ack_fat) as dp_FAT,AVG(Ack_snf) as dp_SNF,SUM(Fat_Kg) as dp_FATKG,sum(Snf_Kg) as dp_SNFKG, sum(RATE) as dp_RATE,sum(Amount) as dp_AMOUNT FROM DespatchNew Where date between '" + d1 + "' AND '" + d2 + "' and Plant_Code='" + plantcode + "' and Type='ack' )t2,(SELECT Sum(Milkkg) as op_MILKKG,Avg(fat) as op_FAT,AVG(snf) as op_SNF,SUM(Fat_Kg) as op_FATKG,sum(Snf_Kg) as op_SNFKG, sum(RATE) as op_RATE,sum(Amount) as op_AMOUNT FROM Stock_openingmilk Where datee = '" + d1 + "' and Plant_Code='" + plantcode + "')t3,(SELECT Sum(Milkkg) as cl_MILKKG,Avg(fat) as cl_FAT,AVG(snf) as cl_SNF,SUM(Fat_Kg) as cl_FATKG,sum(Snf_Kg) as cl_SNFKG, sum(RATE) as cl_RATE,sum(Amount) as cl_AMOUNT FROM Stock_Milk Where date = '" + d2 + "' and Plant_Code='" + plantcode + "') C";
str = " select * from( SELECT t1.pcode,t1.pr_MILKKG, t1.pr_FAT,t1.pr_SNF,t1.pr_FATKG,t1.pr_SNFKG,t1.pr_RATE,t1.pr_AMOUNT, t2.dp_MILKKG, t2.dp_FAT,t2.dp_SNF,t2.dp_FATKG,t2.dp_SNFKG,t2.dp_RATE,t2.dp_AMOUNT,t3.op_MILKKG,t3.op_FAT,t3.op_SNF,t3.op_FATKG,t3.op_SNFKG,t3.op_RATE,t3.op_AMOUNT,c.cl_MILKKG,c.cl_FAT,c.cl_SNF,c.cl_FATKG,c.cl_SNFKG,c.cl_RATE,c.cl_AMOUNT FROM ( SELECT isnull(Sum(Milk_kg),0) as pr_MILKKG,isnull(Avg(fat),0) as pr_FAT,isnull(AVG(snf),0) as pr_SNF,isnull(sum(fat_kg),0)as pr_FATKG,isnull(sum(snf_kg),0) as pr_SNFKG,isnull(sum(rate),0) as pr_RATE,isnull(sum(AMOUNT),0) as pr_AMOUNT,Plant_Code as pcode FROM Procurement Where prdate between '" + d1 + "' AND '" + d2 + "' and Plant_Code='" + plantcode + "' group by Plant_Code)t1,(SELECT isnull(Sum(Milkkg),0) as dp_MILKKG,isnull(Avg(fat),0) as dp_FAT,isnull(AVG(snf),0) as dp_SNF,isnull(SUM(Fat_Kg),0) as dp_FATKG,isnull(sum(Snf_Kg),0) as dp_SNFKG,isnull(sum(RATE),0) as dp_RATE,isnull(sum(Amount),0) as dp_AMOUNT FROM DespatchEntry Where date between '" + d1 + "' AND '" + d2 + "' and Plant_Code='" + plantcode + "')t2,(SELECT isnull(Sum(Milkkg),0) as op_MILKKG,isnull(Avg(fat),0) as op_FAT,isnull(AVG(snf),0) as op_SNF,isnull(SUM(Fat_Kg),0) as op_FATKG,isnull(sum(Snf_Kg),0) as op_SNFKG, isnull(sum(RATE),0) as op_RATE,isnull(sum(Amount),0) as op_AMOUNT FROM Stock_openingmilk Where datee = '" + d1 + "' and Plant_Code='" + plantcode + "')t3,(SELECT isnull(Sum(Milkkg),0) as cl_MILKKG,isnull(Avg(fat),0) as cl_FAT,isnull(AVG(snf),0) as cl_SNF,isnull(SUM(Fat_Kg),0) as cl_FATKG,isnull(sum(Snf_Kg),0) as cl_SNFKG,isnull( sum(RATE),0) as cl_RATE,isnull(sum(Amount),0) as cl_AMOUNT FROM Stock_Milk Where date = '" + d2 + "' and Plant_Code='" + plantcode + "') C) as ak left join ( SELECT convert(decimal(18,2),isnull((SUM(amount +comrate+cartageamount +splbonusamount)/SUM(milk_ltr)),0)) as Rate,Plant_Code FROM Procurement WHERE prdate between '" + d1 + "' AND '" + d2 + "' group by Plant_Code) as nn on ak.pcode=nn.Plant_Code ";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
private void NillpaymentCheck1()
{
try
{
cr.Load(Server.MapPath("Crpt_Nillpaymentcheck.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
str = "SELECT * FROM (SELECT Route_id AS pRid,Agent_id,Milk_kg,Milk_ltr,Fat,Snf,Clr,NoofCans,Sessions,Milk_Nature,Rate,CONVERT(NVARCHAR(35), Prdate,103) AS Prdate FROM Procurement where Prdate between '" + d1.ToString() + "' and '" + d2.ToString() + "' and Plant_Code='" + pcode + "' and Rate=0 ) AS pro LEFT JOIN (SELECT ccode,pcode,Rid,Agent_Id,Rname,Aname FROM (SELECT Route_ID AS Rid,Route_Name AS Rname,Plant_Code AS pcode,Company_Code AS ccode FROM Route_Master WHERE Plant_code='" + pcode + "') AS Rm INNER JOIN (SELECT Route_id,Agent_Id,Agent_Name AS Aname FROM Agent_Master WHERE Plant_code='" + pcode + "' and Type=0) AS Am ON Rm.Rid=Am.Route_id ) AS t1 ON pro.pRid=t1.Rid and pro.Agent_id=t1.Agent_Id ORDER BY t1.Rid,t1.Agent_Id";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
protected void generarreporte()
{
ds = new DataSet();
DBFunctions.Request(ds, IncludeSchema.NO, "select distinct D.mqui_codiquin,D.memp_codiempl,D.pcon_concepto,P.pcon_nombconc,D.dqui_valevento,D.dqui_canteventos,D.dqui_apagar,D.dqui_adescontar,D.dqui_saldo,M.mnit_nombres,M.mnit_apellidos,M.mnit_nit,E.memp_suelactu,mqui_anoquin ,mqui_mesquin , mqui_tpernomi from dbxschema.dquincena D,dbxschema.mnit M,dbxschema.mempleado E, dbxschema.mquincenas MQUI , dbxschema.tmes T ,dbxschema.pconceptonomina P where D.mqui_codiquin=" + DDLQUINCENA.SelectedValue.ToString() + " and E.mnit_nit=M.mnit_nit and E.memp_codiempl=D.memp_codiempl and D.mqui_codiquin=MQUI.mqui_codiquin and P.pcon_concepto=D.pcon_concepto");
ds.WriteXmlSchema(Path.Combine(Request.PhysicalApplicationPath, "schemas/Nomina.Impresion.rpte_comppagogen.xsd"));
//mostrar el reporte en la pagina.
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/Nomina.Impresion.rpte_comppagogen.rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.Impresion.rpte_comppagogen.doc"));
}
private void Period_Closing()
{
try
{
plantcode = ddl_PlantID.SelectedItem.Value;
cr.Load(Server.MapPath("Report\\ClosingStockCrystal.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
t1.Text = companycode + "_" + cname;
t2.Text = ddl_PlantName.SelectedItem.Value;
t3.Text = "From " + txt_FromDate.Text.Trim();
t4.Text = "To " + txt_ToDate.Text.Trim();
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
// str = "SELECT t1.*,ISNULL(Agnt.CarAmt,0) AS CarAmt FROM (SELECT pcode,ISNULL(Smltr,0) AS Smltr,ISNULL(Smkg,0) AS Smkg,ISNULL(AvgFat,0) AS AvgFat,ISNULL(AvgSnf,0) AS AvgSnf,ISNULL(AvgRate,0) AS AvgRate,ISNULL(Avgclr,0) AS Avgclr,ISNULL(Scans,0) AS Scans,ISNULL(SAmt,0) AS SAmt,ISNULL(Avgcrate,0) AS Avgcrate,ISNULL(Sfatkg,0) AS Sfatkg,ISNULL(Ssnfkg,0) AS Ssnfkg,ISNULL(Billadv,0) AS Billadv,ISNULL(Ai,0) AS Ai,ISNULL(feed,0) AS feed,ISNULL(Can,0) AS Can,ISNULL(Recovery,0) AS Recovery,ISNULL(Others,0)AS Others FROM (SELECT spropcode AS pcode,CAST(Smltr AS DECIMAL(18,2)) AS Smltr,CAST(Smkg AS DECIMAL(18,2))AS Smkg,CAST(AvgFat AS DECIMAL(18,2)) AS AvgFat,CAST(AvgSnf AS DECIMAL(18,2)) AS AvgSnf,CAST(AvgRate AS DECIMAL(18,2)) AS AvgRate,CAST(Avgclr AS DECIMAL(18,2))AS Avgclr,CAST(Scans AS DECIMAL(18,2)) AS Scans,CAST(SAmt AS DECIMAL(18,2)) AS SAmt,CAST(Avgcrate AS DECIMAL(18,2)) AS Avgcrate,CAST(Sfatkg AS DECIMAL(18,2)) AS Sfatkg,CAST(Ssnfkg AS DECIMAL(18,2)) AS Ssnfkg,Billadv,Ai,feed,Can,Recovery,Others FROM (SELECT plant_Code AS spropcode,SUM(Milk_ltr) AS Smltr,SUM(Milk_kg) AS Smkg,AVG(FAT) AS AvgFat,AVG(SNF) AS AvgSnf,AVG(Rate) AS AvgRate,AVG(Clr) AS Avgclr,SUM(NoofCans) AS Scans,SUM(Amount) AS SAmt,AVG(ComRate) AS Avgcrate,SUM(fat_kg) AS Sfatkg,SUM(snf_kg) AS SSnfkg FROM Procurement WHERE Company_Code='1' AND Prdate BETWEEN '08-17-2012' AND '01-18-2013' GROUP BY plant_Code ) AS spro LEFT JOIN (SELECT Plant_code AS dedupcode,SUM(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,SUM(CAST((Ai) AS DECIMAL(18,2))) AS Ai,SUM(CAST((Feed) AS DECIMAL(18,2))) AS Feed,SUM(CAST((can) AS DECIMAL(18,2))) AS can,SUM(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,SUM(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '08-17-2012' AND '01-18-2013' AND Company_Code='1' GROUP BY Plant_code ) AS Dedu ON spro.spropcode=Dedu.dedupcode) AS produ LEFT JOIN (SELECT Plant_Code AS LonPcode,SUM(CAST(inst_amount AS DECIMAL(18,2))) AS instamt FROM LoanDetails WHERE Company_Code='1' AND Balance>0 GROUP BY Plant_Code) AS londed ON produ.pcode=londed.LonPcode) AS t1 LEFT JOIN (SELECT Plant_Code AS cartPCode,SUM(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt FROM Agent_Master WHERE Type=0 AND Company_Code='1' GROUP BY Plant_Code) AS Agnt ON t1.pcode=Agnt.cartPCode ";
str = "select MilkKg as MILKKG,Fat as FAT,Snf as SNF,FAT_KG,SNF_KG,Amount as AMOUNT,Rate as RATE,convert(varchar(10),Date,101) as date from Stock_Milk where date between '" + txt_FromDate.Text + "' and '" + txt_ToDate.Text + "' and Plant_code='" + plantcode + "' order by date asc";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
//System.IO.MemoryStream stream = (System.IO.MemoryStream)cr.ExportToStream(ExportFormatType.PortableDocFormat);
//BinaryReader Bin = new BinaryReader(cr.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat));
//Response.ClearContent();
//Response.ClearHeaders();
//Response.ContentType = "application/pdf";
//Response.BinaryWrite(Bin.ReadBytes(Convert.ToInt32(Bin.BaseStream.Length)));
//Response.Flush();
//Response.Close();
CrystalReportViewer1.RefreshReport();
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
private void CartageSplBonusCheck1()
{
try
{
cr.Load(Server.MapPath("Crpt_CaratgeSplBonusCheck.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
t1.Text = ccode + "_" + cname;
t2.Text = pname;
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
str = "Select Route_id,Agent_Id,Agent_Name,Cartage_Amt,SplBonus_Amt from Agent_Master where Company_code='" + ccode + "' AND Plant_code='" + pcode + "' and (Cartage_Amt>0 OR SplBonus_Amt>0 ) ORDER BY Route_id,Agent_Id ";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
private string generatefiles(DataSet ds, string fromdate, string todate, string FirstPath, DataTable mailsetting)
{
try
{
DataTable dt1 = ds.Tables[0];
DataView dv = new DataView(ds.Tables[1]);
string strAttachFirst = string.Empty;
string strAttachSecond = string.Empty;
bool isSendData = false;
System.Collections.ArrayList objList = new ArrayList();
objList.Add(MailSetting(mailsetting));
CrystalDecisions.CrystalReports.Engine.ReportDocument crystalReport;
foreach (DataRow Row in dt1.Rows)
{
MailDetails objMails = new MailDetails();
dv.RowFilter = "CustID = " + Row["custid"].ToString();
crystalReport = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
crystalReport.Load(Server.MapPath("../Report/AutoOSMailCustomer.rpt"));
crystalReport.SetDataSource(dv.ToTable());
isSendData = dv.ToTable().Rows.Count > 0 ? true : false;
strAttachFirst = Server.MapPath(FirstPath + "CustomerOS_" + Convert.ToDateTime(fromdate).ToString("dd-MMM-yyyy") + "_" + Convert.ToDateTime(todate).ToString("dd-MMM-yyyy") + "_" + Row["custid"].ToString() + ".pdf");
crystalReport.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, strAttachFirst);
crystalReport.Close();
crystalReport.Dispose();
crystalReport = null;
objMails.Attachment = strAttachSecond != "" ? strAttachFirst + "," + strAttachSecond : strAttachFirst;
objMails.Name = Row["custName"].ToString();
objMails.EmailId = Row["EmailID"].ToString();
string makesubject = "Chetana Publications :: Outstanding Statement :: from " + Convert.ToDateTime(fromdate).ToString("dd-MMM-yyyy") + " to " + Convert.ToDateTime(todate).ToString("dd-MMM-yyyy");
objMails.Subject = makesubject;
objMails.Desc = makesubject;
if (isSendData)
{
objList.Add(objMails);
}
objMails = null;
}
return(JsonConvert.SerializeObject(objList));
}
catch (Exception ex)
{
throw;
}
}
public static CrystalDecisions.CrystalReports.Engine.ReportDocument CryStal_pdf2(DataTable dt, string Rpt)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
try
{
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Search data not found");
}
else
{
// rpt.Load(Path.Combine(@"C:\Program Files\GBarcode", Rpt));
// SetDataSourceConnection(rpt);
rpt.SetDataSource(dt);
}
}
catch (Exception ex) { MessageBox.Show("Error Report;01" + ex.Message); }
return(rpt);
}
protected void btnmostrar(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DBFunctions.Request(ds, IncludeSchema.NO, "SELECT DQUI.pcon_concepto,MQUI.mqui_mesquin,SUM(DQUI.dqui_apagar),SUM(DQUI.dqui_adescontar),MQUI.mqui_anoquin FROM dbxschema.mempleado MEMP,dbxschema.dquincena DQUI,dbxschema.mquincenas MQUI,dbxschema.pconceptonomina PCON,dbxschema.pmes PMES WHERE MEMP.memp_codiempl=DQUI.memp_codiempl AND DQUI.pcon_concepto=PCON.pcon_concepto AND MQUI.mqui_codiquin=DQUI.mqui_codiquin AND MQUI.mqui_mesquin=PMES.pmes_mes and mqui_anoquin=" + DDLANO.SelectedValue + " GROUP BY DQUI.pcon_concepto,MQUI.mqui_mesquin,MQUI.mqui_anoquin;select pmes_mes, pmes_nombre from dbxschema.pmes;select cemp_nombre,cemp_nombcome,mnit_nit from dbxschema.cempresa");
ds.WriteXmlSchema(Path.Combine(Request.PhysicalApplicationPath, "schemas/Nomina.Acumulado.rpte_ImpAcumuladoConceptoGeneral.xsd"));
reporte = new ReportDocument();
reporte.Load(Path.Combine(Request.PhysicalApplicationPath, "rpt/Nomina.Acumulado.rpte_ImpAcumuladoConceptoGeneral.rpt"));
reporte.SetDataSource(ds);
visor.ReportSource = reporte;
visor.DataBind();
reporte.ExportToDisk(ExportFormatType.WordForWindows, Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.Acumulado.rpte_ImpAcumuladoConceptoGeneral.doc"));
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/msword";
Response.WriteFile(Path.Combine(Request.PhysicalApplicationPath, "rptgen/Nomina.Acumulado.rpte_ImpAcumuladoConceptoGeneral.doc"));
}
public static CrystalDecisions.CrystalReports.Engine.ReportDocument CryStal_pdf(DataTable dt, string Rpt)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
try
{
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Search data not found");
}
else
{
rpt.Load(Rpt);
//SetDataSourceConnection(rpt);
rpt.SetDataSource(dt);
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
return(rpt);
}
protected void Page_Init(object sender, EventArgs e)
{
string SearchStr = string.Empty;
try
{
if (Session["CurrentSchoolYearId"] == null)
{
Response.Redirect("~/Login.aspx");
}
crdata.Report = rpt;
rpt1 = crdata.ReportDocument;
DataSet dsReport = new xmlClassWiseStudentAttendTime();
DataSet ds = new DataSet();
if (Session["ChildList"] != null)
{
//rpt.FileName = Server.MapPath("rptFamilyChildListReport.rpt");
rpt.FileName = Server.MapPath("rptChildDataListReport.rpt");
DayCareBAL.ChildListService proxy = new DayCareBAL.ChildListService();
SearchStr += Session["ChildList"].ToString();
SearchStr = Session["ChildList"].ToString().Replace("'00000000-0000-0000-0000-000000000000',", "");
CrystalDecisions.CrystalReports.Engine.TextObject titleTextSchool = (CrystalDecisions.CrystalReports.Engine.TextObject)rpt1.ReportDefinition.ReportObjects["Text1"];
CrystalDecisions.CrystalReports.Engine.TextObject footer = (CrystalDecisions.CrystalReports.Engine.TextObject)rpt1.ReportDefinition.ReportObjects["txtfooter"];
footer.Text = Common.GetSchoolWiseAddress(new Guid(Session["SchoolId"].ToString()));
titleTextSchool.Text = Session["SchoolName"].ToString().ToUpper();
//ds = proxy.GetAccountReceiable(new Guid(Session["CurrentSchoolYearId"].ToString(), new Guid(Request.QueryString["StartDate"].ToString())));
ds = proxy.GetChildList(new Guid(Session["SchoolId"].ToString()), new Guid(Session["CurrentSchoolYearId"].ToString()), SearchStr);
dsReport.Tables["dtChildDataList"].Merge(ds.Tables[0]);
rpt1.SetDataSource(dsReport.Tables["dtChildDataList"]);
}
crp.DisplayGroupTree = false;
crp.ReportSource = rpt1;
crp.RefreshReport();
crp.DataBind();
}
catch (Exception ex)
{
DayCarePL.Logger.Write(DayCarePL.LogType.EXCEPTION, DayCarePL.ModuleToLog.Ledger, "rptChildDataListReport.rpt Page_Load", ex.Message.ToString(), DayCarePL.Common.GUID_DEFAULT);
}
}
public void m_mthFindByDateReport()
{
str_firstDate = m_objViewer.m_daFinDate.Value.ToShortDateString();
str_lasttDate = m_objViewer.m_daFinDateLast.Value.ToShortDateString();
m_rptRpt.Load("Report\\AllOPREMP_month.rpt");
DataTable m_dtRpt = new DataTable();
DataTable m_dtRptDitial = new DataTable();
long lngRes;
lngRes = clsDomain.m_lngChargeMnothReport(str_firstDate, str_lasttDate, out m_dtRpt);
if (lngRes >= 1)
{
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["txtReportTitle"]).Text = this.m_objComInfo.m_strGetHospitalTitle() + "收费处月结算表";
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["dateArear"]).Text = "统计日期: " + str_firstDate + " 至 " + str_lasttDate;
m_rptRpt.SetDataSource(m_dtRpt);
m_rptRpt.Refresh();
m_objViewer.cryReportViewer.ReportSource = m_rptRpt;
}
}
public void m_mthFindByDateReport()
{
str_firstDate = m_objViewer.m_daFinDate.Value.ToShortDateString();
str_lastDate = m_objViewer.m_daFinDateLast.Value.ToShortDateString();
m_rptRpt.Load("Report\\rpt_medicineProtect.rpt");
DataTable m_dtRpt = new DataTable();
DataTable m_dtRptDitial = new DataTable();
long lngRes;
lngRes = clsDomain.m_lngMeditionProtectReport(str_firstDate, str_lastDate, out m_dtRpt);
if (lngRes >= 1)
{
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["acceptDate"]).Text = "就诊年月: " + Convert.ToDateTime(str_lastDate).Year + "-" + Convert.ToDateTime(str_lastDate).Month;
//((TextObject)m_rptRpt.ReportDefinition.ReportObjects["txtReportTitle"]).Text = this.m_objComInfo.m_strGetHospitalTitle() + "医保月结算表";
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["txtReportTitle"]).Text = "城镇职工基本医疗月报表";
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["unitName"]).Text = "单位名称: " + this.m_objComInfo.m_strGetHospitalTitle();
m_rptRpt.SetDataSource(m_dtRpt);
m_rptRpt.Refresh();
m_objViewer.cryReportViewer.ReportSource = m_rptRpt;
}
}
/// <summary>
/// 统计操作
/// </summary>
public void m_mthButtonClickToStatistic()
{
DataTable dtbStatistic;
string dtmStart = this.m_objViewer.m_dateTimePickerbegin.Value.ToShortDateString();
string dtmEnd = this.m_objViewer.m_dateTimePickerEnd.Value.ToShortDateString();
long lngRes = this.m_objManage.m_lngGetStatiticsData(dtmStart, dtmEnd, out dtbStatistic);
if (lngRes < 0)
{
return;
}
else
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rpt.Load("Report\\CrystalReportOutPatient.rpt");
// HISMedTypeManage.Rpt.CrystalReportOutPatient rpt = new HISMedTypeManage.Rpt.CrystalReportOutPatient();
((TextObject)rpt.ReportDefinition.ReportObjects["Text7"]).Text = this.m_objViewer.m_dateTimePickerbegin.Value.ToShortDateString();
((TextObject)rpt.ReportDefinition.ReportObjects["Text8"]).Text = this.m_objViewer.m_dateTimePickerEnd.Value.ToShortDateString();
if (dtbStatistic.Rows.Count > 0)
{
double totalMoney = 0;
for (int i1 = 0; i1 < dtbStatistic.Rows.Count; i1++)
{
totalMoney += Convert.ToDouble(dtbStatistic.Rows[i1]["TOTALMONEY"].ToString().Trim());
}
((TextObject)rpt.ReportDefinition.ReportObjects["Text13"]).Text = totalMoney.ToString();
}
else
{
MessageBox.Show("无统计数据！");
return;
}
//((TextObject)rpt.ReportDefinition.ReportObjects["Text13"]).Text =
rpt.SetDataSource(dtbStatistic);
rpt.Refresh();
this.m_objViewer.m_crystalReportViewer1.ReportSource = rpt;
}
}
public void m_mthFindByDateReport()
{
str_firstDate = m_objViewer.m_daFinDate.Value.ToShortDateString();
str_toDate = m_objViewer.m_toDate.Value.ToShortDateString();
m_rptRpt.Load("Report\\rptPublicPay.rpt");
((TextObject)m_rptRpt.ReportDefinition.ReportObjects["txtReportTitle"]).Text = this.m_objComInfo.m_strGetHospitalTitle() + "公费费用报表";
DataTable m_dtRpt = new DataTable();
DataTable m_dtRptDitial = new DataTable();
if (m_objViewer.m_chbPatienName.Checked == true && m_objViewer.m_chbDate.Checked == true)
{
m_intFindType = 1;
}
else if (m_objViewer.m_chbPatienName.Checked == true && m_objViewer.m_chbDate.Checked == false)
{
m_intFindType = 2;
}
else if (m_objViewer.m_chbPatienName.Checked == false && m_objViewer.m_chbDate.Checked == true)
{
m_intFindType = 3;
}
else
{
m_intFindType = 4;
}
long lngRes;
lngRes = clsDomain.m_lngPublicPayReport(m_intFindType, m_objViewer.m_txtName.Text.Trim(), str_firstDate, str_toDate, out m_dtRpt);
if (lngRes >= 1)
{
m_rptRpt.SetDataSource(m_dtRpt);
m_rptRpt.Refresh();
m_objViewer.cryReportViewer.ReportSource = m_rptRpt;
}
}
private void CurentRateChartCheck1()
{
try
{
cr.Load(Server.MapPath("Crpt_CurrentRateChartCheck.rpt"));
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Chartname"];
t1.Text = ccode + "_" + cname;
t2.Text = pname;
t3.Text = ddl_ChartName.SelectedItem.Value;
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
str = "SELECT Table_ID,From_Rangevalue,To_Rangevalue,CAST(Rate AS DECIMAL(18,2)) AS Rate,CAST(Comission_Amount AS DECIMAL(18,2)) AS Comission_Amount,CAST(Bouns_Amount AS DECIMAL(18,2)) AS Bouns_Amount FROM Rate_Chart WHERE Chart_Name='" + ddl_ChartName.SelectedItem.Text + "' AND Plant_code='" + pcode + "' and Company_code='" + ccode + "' ORDER BY Rate";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
public void m_mthFindByDateReport()
{
for (int i = 0; i < 17; i++)
{
Doe_[i] = 0;
}
str_firstDate = m_objViewer.m_daFinDate.Value.ToShortDateString();
str_lasttDate = m_objViewer.m_daFinDateLast.Value.ToShortDateString();
m_rptRpt.Load("Report\\rptDepWork.rpt");
DataTable m_dtRpt = new DataTable();
DataTable m_dtRptDitial = new DataTable();
long lngRes;
lngRes = clsDomain.m_lngChargeMnothReport(str_firstDate, str_lasttDate, out m_dtRpt);
if (lngRes >= 1)
{
m_rptRpt.SetDataSource(m_dtRpt);
m_rptRpt.Refresh();
m_objViewer.cryReportViewer.ReportSource = m_rptRpt;
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
crvReporte.Visible = true;
string fechainicial = Request.QueryString["fechainicial"].ToString();
string fechafinal = Request.QueryString["fechafinal"].ToString();
string idsede = Request.QueryString["idsede"].ToString();
string idproyecto = Request.QueryString["idproyecto"].ToString();
string idestado = Request.QueryString["idestado"].ToString();
ds = objPedidoDAO.ListarReportePedidos(AppUtilidad.stringToDateTime(fechainicial, "DD/MM/YYYY"),
AppUtilidad.stringToDateTime(fechafinal, "DD/MM/YYYY"),
Convert.ToInt32(idsede),
Convert.ToInt32(idproyecto),
idestado);
dt = ds.Tables[0];
CrystalDecisions.CrystalReports.Engine.ReportDocument rDoc;
rDoc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
rDoc.Load(Server.MapPath("rptReportePedidos.rpt")); // Your .rpt file path
rDoc.SetDataSource(dt); //set dataset to the report viewer.
rDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, false, "Listado");
}
private void RoutewisePaymentAbstract()
{
try
{
pcode = ddl_Plantcode.SelectedItem.Value;
Plantmilktype = dbaccess.GetPlantMilktype(pcode);
if (Plantmilktype == 2)
{
cr.Load(Server.MapPath("Report//Crpt_DRoutewiseabstractBuff.rpt"));
}
else
{
cr.Load(Server.MapPath("Report//Crpt_DRoutewiseabstract.rpt"));
}
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
CrystalDecisions.CrystalReports.Engine.TextObject t1;
CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
t1.Text = ccode + "_" + cname;
t2.Text = ddl_Plantname.SelectedItem.Value;
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
string d1 = dt1.ToString("MM/dd/yyyy");
string d2 = dt2.ToString("MM/dd/yyyy");
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To" + txt_ToDate.Text.Trim();
string str = string.Empty;
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
if (chk_Allroute.Checked == true)
{
//160
if (pcode == "11")
{
str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SRNetAmt, FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound, cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Route_id<>8 GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS Routewiseagent LEFT JOIN (SELECT G1.ARid, CAST(SUM(G1.Smkg) AS DECIMAL(18,2)) AS GSmkg, CAST(SUM(G1.Smltr) AS DECIMAL(18,2)) AS GSmltr, CAST(AVG(G1.AvgFat) AS DECIMAL(18,1)) AS GAvgFat, CAST(AVG(G1.AvgSnf) AS DECIMAL(18,1)) AS GAvgSnf, CAST(AVG(G1.AvgRate) AS DECIMAL(18,1)) AS GAvgRate, CAST(AVG(G1.Avgclr) AS DECIMAL(18,1)) AS GAvgclr, CAST(SUM(G1.Scans) AS DECIMAL(18,2)) AS GScans, CAST(SUM(G1.SAmt) AS DECIMAL(18,2)) AS GSAmt, CAST(SUM(G1.ScommAmt) AS DECIMAL(18,2)) AS GScommAmt, CAST(SUM(G1.Scatamt) AS DECIMAL(18,2)) AS GScatamt, CAST(SUM(G1.Ssplbonamt) AS DECIMAL(18,2)) AS GSsplbonamt, CAST(AVG(G1.AvgcRate) AS DECIMAL(18,2)) AS GAvgcRate, CAST(SUM(G1.Sfatkg) AS DECIMAL(18,2)) AS GSfatkg, CAST(SUM(G1.Ssnfkg) AS DECIMAL(18,2)) AS GSsnfkg, CAST(SUM(G1.SBilladv) AS DECIMAL(18,2)) AS GSBilladv, CAST(SUM(G1.SAiamt) AS DECIMAL(18,2)) AS GSAiamt, CAST(SUM(G1.SFeedamt) AS DECIMAL(18,2)) AS GSFeedamt, CAST(SUM(G1.Scanamt) AS DECIMAL(18,2)) AS GScanamt, CAST(SUM(G1.SRecoveryamt) AS DECIMAL(18,2)) AS GSRecoveryamt, CAST(SUM(G1.Sothers) AS DECIMAL(18,2)) AS GSothers, CAST(SUM(G1.Sinstamt) AS DECIMAL(18,2)) AS GSinstamt, CAST(SUM(G1.Sbalance) AS DECIMAL(18,2)) AS GSbalance, CAST(SUM(G1.SLoanAmount) AS DECIMAL(18,2)) AS GSLoanAmount, CAST(SUM(G1.SNetAmt) AS DECIMAL(18,2)) AS GSNetAmt, CAST(SUM(G1.SRound) AS DECIMAL(18,2)) AS GSround FROM (SELECT cart.ARid, ISNULL(prdelo.Smkg,0) AS Smkg, ISNULL(prdelo.Smltr,0) AS Smltr, ISNULL(prdelo.AvgFat ,0) AS AvgFat, ISNULL(prdelo.AvgSnf,0) AS AvgSnf, ISNULL(prdelo.AvgRate,0) AS AvgRate, ISNULL(prdelo.Avgclr,0) AS Avgclr, ISNULL(prdelo.Scans,0) AS Scans, ISNULL(prdelo.SAmt,0) AS SAmt, ISNULL(prdelo.ScommAmt,0) AS ScommAmt, ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt, ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate, ISNULL(prdelo.Sfatkg,0) AS Sfatkg, ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg, ISNULL(prdelo.Billadv,0) AS SBilladv, ISNULL(prdelo.Ai,0) AS SAiamt, ISNULL(prdelo.Feed,0) AS SFeedamt, ISNULL(prdelo.Can,0) AS Scanamt, ISNULL(prdelo.Recovery,0) AS SRecoveryamt, ISNULL(prdelo.others,0) AS Sothers, ISNULL(prdelo.instamt,0) AS Sinstamt, ISNULL(prdelo.balance,0) AS Sbalance, ISNULL(prdelo.LoanAmount,0) AS SLoanAmount, FLOOR (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) AS SNetAmt, ( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )-( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0)) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound FROM ( SELECT * FROM ( SELECT * FROM ( SELECT agent_id AS SproAid, CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg, CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr, CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat, CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf, CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate, CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr, CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans, CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt, CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt, CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt, CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt, CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate, CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg, CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' and Route_id<>8 GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (SELECT Agent_id AS LoAid,CAST(SUM(inst_amount) AS DECIMAL(18,2)) AS instamt,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' AND Balance>0 GROUP BY Agent_id) AS Lon ON proded.SproAid=Lon.LoAid) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid) AS G1 GROUP BY G1.ARid) AS Route ON Routewiseagent.Rid=Route.ARid ORDER BY Routewiseagent.Rid ,Routewiseagent.Aid ";
}
else
{
pcode = ddl_Plantcode.SelectedItem.Value;
str = "SELECT pay .*,Gpay.* FROM " + " (SELECT Route_id,Route_name,CONVERT(VARCHAR(13),(CONVERT(NVARCHAR(10),Agent_id)+'_'+Agent_name)) AS Agent_name,Smkg,Smltr,SAmt,CAST((SAmt/Smltr) AS DECIMAL(18,2)) AS Mrate,SInsentAmt,Scaramt,SSplBonus,TotAdditions+SAmt AS TotAdditions,CAST(((SAmt+SInsentAmt+Scaramt+SSplBonus)/Smltr) AS DECIMAL(18,2)) AS Arate,ClaimAount,Sinstamt,Billadv,Ai,Feed,can,Roundoff,NetAmount,CAST(((Sfatkg*100)/Smkg) AS DECIMAL(18,1)) AS Afat,CAST(((SSnfkg*100)/Smkg) AS DECIMAL(18,1)) AS Asnf,Sfatkg,SSnfkg,CAST(((SAmt+SInsentAmt+Scaramt+SSplBonus)/Sfatkg) AS DECIMAL(18,2)) AS kgFatrate FROM Paymentdata Where Plant_code='" + pcode + "' AND Frm_date='" + d1.Trim() + "' AND To_date='" + d2.Trim() + "') AS pay " + " LEFT JOIN " + " (SELECT Route_id,SUM(Smkg) AS GSmkg,SUM(Smltr) AS GSmltr,SUM(SAmt) AS GSAmt,CAST((SUM(SAmt)/SUM(Smltr)) AS DECIMAL(18,2)) AS GMrate,SUM(SInsentAmt) AS GSInsentAmt,SUM(Scaramt) AS GScaramt,SUM(SSplBonus) AS GSSplBonus,SUM(TotAdditions)+SUM(SAmt) AS GTotAdditions,CAST(((SUM(SAmt)+SUM(SInsentAmt)+SUM(Scaramt)+SUM(SSplBonus))/SUM(Smltr)) AS DECIMAL(18,2)) AS GArate,SUM(ClaimAount) AS GClaimAount,SUM(Sinstamt) AS GSinstamt,SUM(Billadv) AS GBilladv,SUM(Ai) AS GAi,SUM(Feed) AS GFeed,SUM(can) AS Gcan,SUM(Roundoff) AS GRoundoff,SUM(NetAmount) AS GNetAmount,CAST(((SUM(Sfatkg)*100)/SUM(Smkg)) AS DECIMAL(18,1)) AS GAfat,CAST(((SUM(SSnfkg)*100)/SUM(Smkg)) AS DECIMAL(18,1)) AS GAsnf,SUM(Sfatkg) AS GSfatkg,SUM(SSnfkg) AS GSSnfkg,CAST(((SUM(SAmt)+SUM(SInsentAmt)+SUM(Scaramt)+SUM(SSplBonus))/SUM(Sfatkg)) AS DECIMAL(18,2)) AS GkgFatrate FROM Paymentdata Where Plant_code='" + pcode + "' AND Frm_date='" + d1.Trim() + "' AND To_date='" + d2.Trim() + "' GROUP BY Route_id) AS Gpay ON pay.Route_id=Gpay.Route_id order by pay.Agent_name ";
}
}
else
{
str = "SELECT * FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,cart.Agent_Name,ISNULL(prdelo.Smkg,0) AS Smkg,ISNULL(prdelo.Smltr,0) AS Smltr,ISNULL(prdelo.AvgFat ,0) AS AvgFat,ISNULL(prdelo.AvgSnf,0) AS AvgSnf,ISNULL(prdelo.AvgRate,0) AS AvgRate,ISNULL(prdelo.Avgclr,0) AS Avgclr,ISNULL(prdelo.Scans,0) AS Scans,ISNULL(prdelo.SAmt,0) AS SAmt,ISNULL(prdelo.ScommAmt,0) AS ScommAmt,ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt,ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate,ISNULL(prdelo.Sfatkg,0) AS Sfatkg,ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg,ISNULL(prdelo.Billadv,0) AS SBilladv,ISNULL(prdelo.Ai,0) AS SAiamt,ISNULL(prdelo.Feed,0) AS SFeedamt,ISNULL(prdelo.Can,0) AS Scanamt,ISNULL(prdelo.Recovery,0) AS SRecoveryamt,ISNULL(prdelo.others,0) AS Sothers,ISNULL(prdelo.instamt,0) AS Sinstamt,ISNULL(prdelo.balance,0) AS Sbalance,ISNULL(prdelo.LoanAmount,0) AS SLoanAmount,ISNULL(prdelo.VouAmount,0) AS Sclaim,CAST( ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS DECIMAL(18,2)) AS SRNetAmt,FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt,( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) +ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound,cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (select Agent_Id AS VouAid,CAST(SUM(Amount) AS DECIMAL(18,2)) AS VouAmount from Voucher_Clear where Plant_Code='" + pcode + "' AND Clearing_Date BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_Id) AS vou ON proded.SproAid=vou.VouAid) AS pdv LEFT JOIN (SELECT ISNULL(LoAid,0) AS LoAid,ISNULL(balance,0) AS balance,ISNULL(LoanAmount,0) AS LoanAmount,(ISNULL(loanRecAmount1,0)+ ISNULL(0,0)) AS instamt FROM (SELECT LoAid1 AS LoAid,balance1 AS balance,LoanAmount1 AS LoanAmount,loanRecAmount1 FROM (SELECT Agent_id AS LoAid1,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance1,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount1 FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY Agent_id) AS Lonn LEFT JOIN (SELECT Agent_id AS LoRecAid,CAST(SUM(Paid_Amount) AS DECIMAL(18,2)) AS loanRecAmount1 FROM Loan_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND Paid_date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id) AS LonRec ON Lonn.LoAid1=LonRec.LoRecAid ) AS LoF LEFT JOIN (SELECT Agent_Id AS LoDuAid,CAST(SUM(LoanDueRecovery_Amount) AS DECIMAL(18,2)) AS loanDueRecAmount1 FROM LoanDue_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND LoanRecovery_Date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id ) AS LonDRec ON LoF.LoAid=LonDRec.LoDuAid ) AS Lon ON pdv.SproAid=Lon.LoAid ) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid ) AS fin LEFT JOIN (SELECT G1.Rid,CAST(SUM(G1.Smkg) AS DECIMAL(18,2)) AS GSmkg, CAST(SUM(G1.Smltr) AS DECIMAL(18,2)) AS GSmltr, CAST(AVG(G1.AvgFat) AS DECIMAL(18,1)) AS GAvgFat, CAST(AVG(G1.AvgSnf) AS DECIMAL(18,1)) AS GAvgSnf, CAST(AVG(G1.AvgRate) AS DECIMAL(18,1)) AS GAvgRate, CAST(AVG(G1.Avgclr) AS DECIMAL(18,1)) AS GAvgclr, CAST(SUM(G1.Scans) AS DECIMAL(18,2)) AS GScans, CAST(SUM(G1.SAmt) AS DECIMAL(18,2)) AS GSAmt, CAST(SUM(G1.ScommAmt) AS DECIMAL(18,2)) AS GScommAmt, CAST(SUM(G1.Scatamt) AS DECIMAL(18,2)) AS GScatamt, CAST(SUM(G1.Ssplbonamt) AS DECIMAL(18,2)) AS GSsplbonamt, CAST(AVG(G1.AvgcRate) AS DECIMAL(18,2)) AS GAvgcRate, CAST(SUM(G1.Sfatkg) AS DECIMAL(18,2)) AS GSfatkg, CAST(SUM(G1.Ssnfkg) AS DECIMAL(18,2)) AS GSsnfkg, CAST(SUM(G1.SBilladv) AS DECIMAL(18,2)) AS GSBilladv, CAST(SUM(G1.SAiamt) AS DECIMAL(18,2)) AS GSAiamt, CAST(SUM(G1.SFeedamt) AS DECIMAL(18,2)) AS GSFeedamt, CAST(SUM(G1.Scanamt) AS DECIMAL(18,2)) AS GScanamt, CAST(SUM(G1.SRecoveryamt) AS DECIMAL(18,2)) AS GSRecoveryamt, CAST(SUM(G1.Sothers) AS DECIMAL(18,2)) AS GSothers, CAST(SUM(G1.Sinstamt) AS DECIMAL(18,2)) AS GSinstamt, CAST(SUM(G1.Sbalance) AS DECIMAL(18,2)) AS GSbalance, CAST(SUM(G1.SLoanAmount) AS DECIMAL(18,2)) AS GSLoanAmount, CAST(SUM(G1.SNetAmt) AS DECIMAL(18,2)) AS GSNetAmt, CAST(SUM(G1.SRound) AS DECIMAL(18,2)) AS GSround ,CAST(SUM(G1.SClaim) AS DECIMAL(18,2)) AS GSClaim FROM (SELECT cart.ARid AS Rid,cart.cartAid AS Aid,cart.Agent_Name,ISNULL(prdelo.Smkg,0) AS Smkg,ISNULL(prdelo.Smltr,0) AS Smltr,ISNULL(prdelo.AvgFat ,0) AS AvgFat,ISNULL(prdelo.AvgSnf,0) AS AvgSnf,ISNULL(prdelo.AvgRate,0) AS AvgRate,ISNULL(prdelo.Avgclr,0) AS Avgclr,ISNULL(prdelo.Scans,0) AS Scans,ISNULL(prdelo.SAmt,0) AS SAmt,ISNULL(prdelo.ScommAmt,0) AS ScommAmt,ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) AS Scatamt,ISNULL(prdelo.Ssplbonamt,0) AS Ssplbonamt, ISNULL(prdelo.AvgcRate,0) AS AvgcRate,ISNULL(prdelo.Sfatkg,0) AS Sfatkg,ISNULL(prdelo.Ssnfkg,0) AS Ssnfkg,ISNULL(prdelo.Billadv,0) AS SBilladv,ISNULL(prdelo.Ai,0) AS SAiamt,ISNULL(prdelo.Feed,0) AS SFeedamt,ISNULL(prdelo.Can,0) AS Scanamt,ISNULL(prdelo.Recovery,0) AS SRecoveryamt,ISNULL(prdelo.others,0) AS Sothers,ISNULL(prdelo.instamt,0) AS Sinstamt,ISNULL(prdelo.balance,0) AS Sbalance,ISNULL(prdelo.LoanAmount,0) AS SLoanAmount,ISNULL(prdelo.VouAmount,0) AS Sclaim,CAST( ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) +(ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS DECIMAL(18,2)) AS SRNetAmt,FLOOR ((ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0)+ ISNULL(prdelo.VouAmount,0) + (ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0))) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) AS SNetAmt,( ( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(prdelo.VouAmount,0) +ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0)) )- ( FLOOR( (ISNULL(prdelo.SAmt,0) + ISNULL(prdelo.ScommAmt,0) + ISNULL(prdelo.Ssplbonamt,0) + ISNULL(CAST((ISNULL(prdelo.Smltr,0) * ISNULL(cart.CarAmt,0)) AS DECIMAL(18,2)),0) ) - (ISNULL(prdelo.Billadv,0)+ISNULL(prdelo.Ai,0)+ISNULL(prdelo.Feed,0)+ISNULL(prdelo.Can,0)+ISNULL(prdelo.Recovery,0)+ISNULL(prdelo.others,0)+ISNULL(prdelo.instamt,0))) ) ) AS SRound,cart.Bank_Id,cart.Payment_mode,cart.Agent_AccountNo FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT agent_id AS SproAid,CAST(SUM(Milk_kg) AS DECIMAL(18,2)) AS Smkg,CAST(SUM(Milk_ltr) AS DECIMAL(18,2)) AS Smltr,CAST(AVG(FAT) AS DECIMAL(18,2)) AS AvgFat,CAST(AVG(SNF) AS DECIMAL(18,2)) AS AvgSnf,CAST(AVG(Rate) AS DECIMAL(18,2)) AS AvgRate,CAST(AVG(Clr) AS DECIMAL(18,2)) AS Avgclr,CAST(SUM(NoofCans) AS DECIMAL(18,2)) AS Scans,CAST(SUM(Amount) AS DECIMAL(18,2)) AS SAmt,CAST(SUM(Comrate) AS DECIMAL(18,2)) AS ScommAmt,CAST(SUM(ComRate) AS DECIMAL(18,2)) AS Scatamt,CAST(SUM(SplBonusAmount) AS DECIMAL(18,2)) AS Ssplbonamt,CAST(AVG(ComRate) AS DECIMAL(18,2)) AS Avgcrate,CAST(SUM(fat_kg) AS DECIMAL(18,2)) AS Sfatkg,CAST(SUM(snf_kg) AS DECIMAL(18,2)) AS SSnfkg FROM Procurement WHERE prdate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY agent_id ) AS Spro LEFT JOIN (SELECT Agent_id AS DAid ,(CAST((Billadvance) AS DECIMAL(18,2))) AS Billadv,(CAST((Ai) AS DECIMAL(18,2))) AS Ai,(CAST((Feed) AS DECIMAL(18,2))) AS Feed,(CAST((can) AS DECIMAL(18,2))) AS can,(CAST((Recovery) AS DECIMAL(18,2))) AS Recovery,(CAST((others) AS DECIMAL(18,2))) AS others FROM Deduction_Details WHERE deductiondate BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS dedu ON Spro.SproAid=dedu.DAid) AS proded LEFT JOIN (select Agent_Id AS VouAid,CAST(SUM(Amount) AS DECIMAL(18,2)) AS VouAmount from Voucher_Clear where Plant_Code='" + pcode + "' AND Clearing_Date BETWEEN '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_Id) AS vou ON proded.SproAid=vou.VouAid) AS pdv LEFT JOIN (SELECT ISNULL(LoAid,0) AS LoAid,ISNULL(balance,0) AS balance,ISNULL(LoanAmount,0) AS LoanAmount,(ISNULL(loanRecAmount1,0)+ ISNULL(0,0)) AS instamt FROM (SELECT LoAid1 AS LoAid,balance1 AS balance,LoanAmount1 AS LoanAmount,loanRecAmount1 FROM (SELECT Agent_id AS LoAid1,CAST(SUM(balance) AS DECIMAL(18,2)) AS balance1,CAST(SUM(LoanAmount) AS DECIMAL(18,2)) AS LoanAmount1 FROM LoanDetails WHERE Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "' GROUP BY Agent_id) AS Lonn LEFT JOIN (SELECT Agent_id AS LoRecAid,CAST(SUM(Paid_Amount) AS DECIMAL(18,2)) AS loanRecAmount1 FROM Loan_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND Paid_date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id) AS LonRec ON Lonn.LoAid1=LonRec.LoRecAid ) AS LoF LEFT JOIN (SELECT Agent_Id AS LoDuAid,CAST(SUM(LoanDueRecovery_Amount) AS DECIMAL(18,2)) AS loanDueRecAmount1 FROM LoanDue_Recovery WHERE Company_Code='" + ccode + "' AND Plant_code ='" + pcode + "' AND LoanRecovery_Date between '" + d1.Trim() + "' AND '" + d2.Trim() + "' GROUP BY Agent_id ) AS LonDRec ON LoF.LoAid=LonDRec.LoDuAid ) AS Lon ON pdv.SproAid=Lon.LoAid ) AS prdelo INNER JOIN (SELECT Agent_Id AS cartAid,(CAST((Cartage_Amt) AS DECIMAL(18,2)))AS CarAmt,Agent_Name,Bank_Id,Payment_mode,Agent_AccountNo,Route_id AS ARid FROM Agent_Master WHERE Type=0 AND Company_Code='" + ccode + "' AND Plant_Code='" + pcode + "') AS cart ON prdelo.SproAid=cart.cartAid ) AS G1 GROUP BY G1.Rid ) AS gf ON fin.Rid=gf.Rid ORDER BY fin.Rid,fin.Aid";
}
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
/// <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, string type)
{
try
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM121/VAM12" + type + "R.rpt"));
report.SetDataSource(dt);
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
string strParFDate = string.Empty;
string strParFSType = string.Empty;
string strParFTaxType = string.Empty;
Program_ID = this.PageCode.Substring(0, 5) + "R0" + type;
LoginUser = Session["UID"].ToString();
//report.SetParameterValue("par_Program_ID", Program_ID);
//report.SetParameterValue("par_LoginUser", LoginUser);
//report.SetParameterValue("par_LoginName", Login_Name);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = string.Empty;
rpt_name = "進項憑證明細表_折讓單套表.PDF";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 顯示報表
/// </summary>
private void ShowReport(DataSet ds)
{
#region 利用CrystalReport列印報表
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
string strFileName = "";
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ReportDocument SubReport = new ReportDocument();
report.Load(Server.MapPath("./REPORT/CAA271/CAA27R01.rpt"));
strFileName = HttpUtility.UrlEncode("結帳與開立憑證檢核表.PDF", System.Text.Encoding.UTF8);
SubReport = report.OpenSubreport("CAA27R01_1");
SubReport.SetDataSource(ds);
report.SetDataSource(ds);
report.SetParameterValue("par_Program_ID", "CAA27R01");
report.SetParameterValue("par_LoginUser", Session["UID"].ToString() + Login_Name);
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);//pdf檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
#endregion
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
string s_par_Date = string.Empty;
//組報表名稱
string type = "";
if (radDealGoodsList.Checked) type = "1";
else if (radDealTotal.Checked) type = "2";
else type = "3";
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
s_par_Date = SLP_SLPDate.Text;
//取得門市對象及資料來源中文名稱
string s_par_DELIV_EARLY = "", s_par_DATA_SOURCE = "";
if (radTotal.Checked) s_par_DELIV_EARLY = radTotal.Text;
else if (radViceStore.Checked) s_par_DELIV_EARLY = radViceStore.Text;
else s_par_DELIV_EARLY = radAheadDealStore.Text;
if (radPay.Checked) s_par_DATA_SOURCE = radPay.Text;
else s_par_DATA_SOURCE = radStoreIn.Text;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
report.SetParameterValue("par_DELIV_EARLY", s_par_DELIV_EARLY);
report.SetParameterValue("par_DATA_SOURCE", s_par_DATA_SOURCE);
if (radDealTotal.Checked)//理貨彙總表
{
report.SetParameterValue("par_ST_ACCEPT_DATE", s_par_Date);//門市進貨日
report.SetParameterValue("par_PICK_BACTH", txtProcBatch.Text);//理貨批次
}
else if (radOutRpt.Checked)
{
report.SetParameterValue("par_ST_ACCEPT_DATE", s_par_Date);//門市進貨日
report.SetParameterValue("par_PICK_BACTH", txtProcBatch.Text);//理貨批次
report.SetParameterValue("par_TRANS_NO_FROM", ddlTransSt_S.SelectedItem.Text);//轉運站起
report.SetParameterValue("par_TRANS_NO_TO", ddlTransSt_E.SelectedItem.Text);//轉運站迄
report.SetParameterValue("par_ROUTE_FROM", SLP_Route_S.Text);//路線起
report.SetParameterValue("par_ROUTE_TO", SLP_Route_E.Text);//路線迄
}
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ProgramID = string.Empty;
string par_LoginUser = string.Empty;
DropDownList ReasonS = (DropDownList)this.SLP_CodeFileS.FindControl("D1");
DropDownList ReasonE = (DropDownList)this.SLP_CodeFileE.FindControl("D1");
string[] fileds ={ "STORE" };
DataTable Dt = SelectDistinct(dt_Source, fileds);
#region 前置檢查與參數過濾
string par_S_PDate = this.SLP_SLPDateRangeOut.StartDate;
string par_E_PDate = this.SLP_SLPDateRangeOut.EndDate;
string par_S_ChanNo = this.SLP_StoreChainS.Text + " " + this.SLP_StoreChainS.Name;
string par_E_ChanNo = this.SLP_StoreChainE.Text + " " + this.SLP_StoreChainE.Name;
string par_AcceptDate = this.SLP_SLPDate.Text;
string par_S_Route = this.SLP_RouteS.Text + " " + this.SLP_RouteS.Name;
string par_E_Route = this.SLP_RouteE.Text + " " + this.SLP_RouteE.Name;
string par_S_ReasonNo = ReasonS.SelectedValue + " " + ReasonS.SelectedItem.Text;
string par_E_ReasonNo = ReasonE.SelectedValue + " " + ReasonE.SelectedItem.Text;
string par_S_Store = this.SLP_StoreS.Text + " " + this.SLP_StoreS.Name;
string par_E_Store = this.SLP_StoreE.Text + " " + this.SLP_StoreE.Name;
par_ProgramID = this.PageCode;
par_LoginUser = Session["UID"].ToString();
string par_STORE_CNT = Dt.Rows.Count.ToString();
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("par_S_PDate", par_S_PDate);
report.SetParameterValue("par_E_PDate", par_E_PDate);
report.SetParameterValue("par_S_ChanNo", par_S_ChanNo);
report.SetParameterValue("par_E_ChanNo", par_E_ChanNo);
report.SetParameterValue("par_AcceptDate", par_AcceptDate);
report.SetParameterValue("par_S_Route", par_S_Route);
report.SetParameterValue("par_E_Route", par_E_Route);
report.SetParameterValue("par_S_ReasonNo", par_S_ReasonNo);
report.SetParameterValue("par_E_ReasonNo", par_E_ReasonNo);
report.SetParameterValue("par_S_Store", par_S_Store);
report.SetParameterValue("par_E_Store", par_E_Store);
report.SetParameterValue("par_ProgramID", par_ProgramID);
report.SetParameterValue("par_LoginUser", par_LoginUser);
report.SetParameterValue("par_STORE_CNT", par_STORE_CNT);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// ////////////////////////////////////////////////////////////////////////
/// EVENTS
///
protected void Page_Load(object sender, System.EventArgs e)
{
//--- Security check
if (!Convert.ToBoolean(Session["sgLFS_APP_VIEW"]))
{
Response.Redirect("./../error_page.aspx?error=" + "You are not authorized to view this page. Contact your system administrator.");
}
//--- Validate query string
if ((Request.QueryString["target_report"] == null) || (Request.QueryString["format"] == null))
{
Response.Redirect("./../error_page.aspx?error=" + "Invalid query string in viewer.aspx");
}
//--- Initialize
bool empty = false;
LFSRecordForReportsGateway lfsRecordForReportsGateway = new LFSRecordForReportsGateway();
//--- Get report data
#region Get report data
switch (Request.QueryString["target_report"])
{
//--- CXIRemovedReport
case "CXIRemovedReport":
TDSCXIRemovedReport dataSet = lfsRecordForReportsGateway.GetCXIRemovedReportByCompanyId(Convert.ToInt32(Session["companyID"]));
if (dataSet.CXIRemovedReport.Rows.Count > 0)
{
report = new rCXIRemovedReport();
report.SetDataSource(dataSet);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- ReadyForLining
case "ReadyForLining":
TDSReadyForLining tdsReadyForLining = lfsRecordForReportsGateway.GetReadyForLiningByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsReadyForLining.ReadyForLining.Rows.Count > 0)
{
report = new rReadyForLining();
report.SetDataSource(tdsReadyForLining);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- ToBePrepped
case "ToBePrepped":
TDSToBePrepped tdsToBePrepped = lfsRecordForReportsGateway.GetToBePreppedByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsToBePrepped.ToBePrepped.Rows.Count > 0)
{
report = new rToBePrepped();
report.SetDataSource(tdsToBePrepped);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- ToBeMeasured
case "ToBeMeasured":
TDSToBeMeasured tdsToBeMeasured = lfsRecordForReportsGateway.GetToBeMeasuredByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsToBeMeasured.ToBeMeasured.Rows.Count > 0)
{
report = new rToBeMeasured();
report.SetDataSource(tdsToBeMeasured);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- LiningCompleted
case "LiningCompleted":
TDSLiningCompleted tdsLiningCompleted = lfsRecordForReportsGateway.GetLiningCompletedByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]), Convert.ToDateTime(Request.QueryString["start_date"]), Convert.ToDateTime(Request.QueryString["end_date"]));
if (tdsLiningCompleted.LiningCompleted.Rows.Count > 0)
{
report = new rLiningCompleted();
report.SetDataSource(tdsLiningCompleted);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- OverviewReport
case "OverviewReport":
TDSOverviewReport tdsOverviewReport = lfsRecordForReportsGateway.GetOverviewReportByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOverviewReport.OverviewReport.Rows.Count > 0)
{
report = new rOverviewReport();
report.SetDataSource(tdsOverviewReport);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- RehabAssessmentAreas
case "RehabAssessmentAreas":
TDSRehabAssessmentAreas tdsRehabAssessmentAreas = lfsRecordForReportsGateway.GetRehabAssessmentAreasByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsRehabAssessmentAreas.RehabAssessmentAreas.Rows.Count > 0)
{
report = new rRehabAssessmentAreas();
report.SetDataSource(tdsRehabAssessmentAreas);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- AllOutstandingIssues
case "AllOutstandingIssues":
TDSAllOutstandingIssues tdsAllOutstandingIssues = lfsRecordForReportsGateway.GetAllOutstandingIssuesByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsAllOutstandingIssues.AllOutstandingIssues.Rows.Count > 0)
{
report = new rAllOutstandingIssues();
report.SetDataSource(tdsAllOutstandingIssues);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- OutstandingClientIssues
case "OutstandingClientIssues":
TDSOutstandingClientIssues tdsOutstandingClientIssues = lfsRecordForReportsGateway.GetOutstandingClientIssuesByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOutstandingClientIssues.OutstandingClientIssues.Rows.Count > 0)
{
report = new rOutstandingClientIssues();
report.SetDataSource(tdsOutstandingClientIssues);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- OutstandingLFSIssues
case "OutstandingLFSIssues":
TDSOutstandingLFSIssues tdsOutstandingLFSIssues = lfsRecordForReportsGateway.GetOutstandingLFSIssuesByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOutstandingLFSIssues.OutstandingLFSIssues.Rows.Count > 0)
{
report = new rOutstandingLFSIssues();
report.SetDataSource(tdsOutstandingLFSIssues);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- OutstandingInvestigationIssues
case "OutstandingInvestigationIssues":
TDSOutstandingInvestigationIssues tdsOutstandingInvestigationIssues = lfsRecordForReportsGateway.GetOutstandingInvestigacionIssuesByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOutstandingInvestigationIssues.OutstandingInvestigationUssue.Rows.Count > 0)
{
report = new rOutstandingInvestigationIssues();
report.SetDataSource(tdsOutstandingInvestigationIssues);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- OutstandingSalesIssues
case "OutstandingSalesIssues":
TDSOutstandingSalesIssues tdsOutstandingSalesIssues = lfsRecordForReportsGateway.GetOutstandingSalesIssuesByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOutstandingSalesIssues.OutstandingSalesIssues.Rows.Count > 0)
{
report = new rOutstandingSalesIssues();
report.SetDataSource(tdsOutstandingSalesIssues);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- ClientInvestigationIssuesCityCopy
case "ClientInvestigationIssuesCityCopy":
TDSClientInvestigationIssuesCityCopy tdsClientInvestigationIssuesCityCopy = lfsRecordForReportsGateway.GetClientInvestigationIssuesCityCopyByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsClientInvestigationIssuesCityCopy.ClientInvestigationIssuesCityCopy.Rows.Count > 0)
{
report = new rClientInvestigationIssuesCityCopy();
report.SetDataSource(tdsClientInvestigationIssuesCityCopy);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- PointLinerReport
case "PointLinerReport":
TDSPointLinerReport tdsPointLinerReport = lfsRecordForReportsGateway.GetPointLinerReportByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsPointLinerReport.PointLinerReport.Rows.Count > 0)
{
report = new rPointLinerReport();
report.SetDataSource(tdsPointLinerReport);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- PointLinerScopeSheet
case "PointLinerScopeSheet":
TDSPointLinerScopeSheet tdsPointLinerScopeSheet = lfsRecordForReportsGateway.GetPointLinerScopeSheetByCompanyId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsPointLinerScopeSheet.PointLinerScopeSheet.Rows.Count > 0)
{
report = new rPointLinerScopeSheet();
report.SetDataSource(tdsPointLinerScopeSheet);
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- OutstandingPointRepairs
case "OutstandingPointRepairsReport":
TDSOutstandingPointRepairs tdsOutstandingPointRepairs = lfsRecordForReportsGateway.GetOutstandingPointRepairsCompanyIdCompaniesId(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsOutstandingPointRepairs.OutstandingPointRepairs.Rows.Count > 0)
{
//--- Report creation and data binding
report = new rOutstandingPointRepairs();
report.SetDataSource(tdsOutstandingPointRepairs);
//--- Format control
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
//--- Report format
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- M1ReportByClient
case "M1ReportByClient":
TDSM1ReportByClient tdsM1ReportByClient;
if ((Request.QueryString["id"] != "0") && (Request.QueryString["companies"] == "0") && (Request.QueryString["date"] == "0"))
{
tdsM1ReportByClient = lfsRecordForReportsGateway.GetM1ReportByClientByCompanyIdByID(Convert.ToInt32(Session["companyID"]), Request.QueryString["id"]);
}
else if ((Request.QueryString["id"] == "0") && (Convert.ToInt32(Request.QueryString["companies"]) != 0) && (Request.QueryString["date"] == "0"))
{
tdsM1ReportByClient = lfsRecordForReportsGateway.GetM1ReportByClientByCompanyIdByCompanies(Convert.ToInt32(Session["companyID"]), Convert.ToInt32(Request.QueryString["companies"]));
}
else
{
tdsM1ReportByClient = lfsRecordForReportsGateway.GetM1ReportByClientByCompanyIdByDate(Convert.ToInt32(Session["companyID"]), Request.QueryString["date"]);
}
if (tdsM1ReportByClient.M1ReportByClient.Rows.Count > 0)
{
report = new rM1ReportByClient();
report.SetDataSource(tdsM1ReportByClient);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- M2ReportByID
case "M2ReportByID":
TDSM2ReportByID tdsM2ReportByID;
if ((Request.QueryString["id"] != "0") && (Request.QueryString["companies"] == "0") && (Request.QueryString["date"] == "0"))
{
tdsM2ReportByID = lfsRecordForReportsGateway.GetM2ReportByIDByCompanyIdById(Convert.ToInt32(Session["companyID"]), Request.QueryString["id"]);
}
else if ((Request.QueryString["id"] == "0") && (Convert.ToInt32(Request.QueryString["companies"]) != 0) && (Request.QueryString["date"] == "0"))
{
tdsM2ReportByID = lfsRecordForReportsGateway.GetM2ReportByIDByCompanyIdByCompanies(Convert.ToInt32(Session["companyID"]), Convert.ToInt32(Request.QueryString["companies"]));
}
else
{
tdsM2ReportByID = lfsRecordForReportsGateway.GetM2ReportByIDByCompanyIdByDate(Convert.ToInt32(Session["companyID"]), Request.QueryString["date"]);
}
if (tdsM2ReportByID.LFS_MASTER_AREA.Rows.Count > 0)
{
report = new rM2ReportByID();
report.SetDataSource(tdsM2ReportByID);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- WorkAhead
case "WorkAhead":
TDSWorkAhead tdsWorkAhead = lfsRecordForReportsGateway.GetWorkAheadByCompanyId(Convert.ToInt32(Session["companyID"]));
if (tdsWorkAhead.WorkAhead1.Rows.Count > 0)
{
report = new rWorkAhead();
report.SetDataSource(tdsWorkAhead);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
//--- JLinerOverviewReport
case "JLinerOverviewReport":
TDSJLinerOverviewReport tdsJLinerOverviewReport = lfsRecordForReportsGateway.GetJLinerOverviewReportByCompanyIDCompaniesID(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsJLinerOverviewReport.JLinerOverviewReport.Rows.Count > 0)
{
//---Report creation and data binding
if (Convert.ToBoolean(Session["sgLFS_APP_ADMIN"]))
{
report = new rJLinerOverviewReport();
}
else
{
report = new rJLinerOverviewReportSimple();
}
report.SetDataSource(tdsJLinerOverviewReport);
//---Format control
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
//---Report format
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- JLinersReadyToLine
case "JLinersReadyToLine":
TDSJLinersReadyToLine tdsJLinersReadyToLine = lfsRecordForReportsGateway.GetJLinersReadyToLineByCompanyIDCompaniesID(Convert.ToInt32(Session["companyID"]), Convert.ToBoolean(Request.QueryString["all_clients"]), Convert.ToInt32(Request.QueryString["companies_id"]));
if (tdsJLinersReadyToLine.JLinersReadyToLine.Rows.Count > 0)
{
//---Report creation and data binding
report = new rJLinersReadyToLine();
report.SetDataSource(tdsJLinersReadyToLine);
//---Format control
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sReportHeader"]).SectionFormat.EnableSuppress = false;
((Section)report.ReportDefinition.Sections["sPageHeader"]).SectionFormat.EnableSuppress = true;
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
//---Report format
report.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
report.PrintOptions.PaperSize = PaperSize.PaperLegal;
}
else
{
empty = true;
}
break;
//--- JLinersToBuild
case "JLinersToBuild":
TDSJLinersToBuild tdsJLinersToBuild = lfsRecordForReportsGateway.GetJLinersToBuildByCompanyId(Convert.ToInt32(Session["companyID"]));
if (tdsJLinersToBuild.JLinersToBuild.Rows.Count > 0)
{
report = new rJLinersToBuild();
report.SetDataSource(tdsJLinersToBuild);
if (Request.QueryString["format"] == "pdf")
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = false;
}
else
{
((Section)report.ReportDefinition.Sections["sPageFooter"]).SectionFormat.EnableSuppress = true;
}
report.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
report.PrintOptions.PaperSize = PaperSize.PaperLetter;
}
else
{
empty = true;
}
break;
}
#endregion
if (!empty)
{
//--- Configure report
//--- ... Disk options
string physicalApplicationPath = Request.PhysicalApplicationPath;
if (Request.PhysicalApplicationPath.Substring(Request.PhysicalApplicationPath.Length-1, 1) != "\\")
{
physicalApplicationPath += "\\";
}
string fName = "";
switch (Request.QueryString["format"])
{
case "pdf":
fName = physicalApplicationPath + "export\\" + Guid.NewGuid().ToString() + ".pdf";
Session["fName"] = fName;
break;
case "excel":
fName = physicalApplicationPath + "export\\" + Guid.NewGuid().ToString() + ".xls";
Session["fName"] = fName;
break;
case "word":
break;
}
DiskFileDestinationOptions diskOptions = new DiskFileDestinationOptions();
diskOptions.DiskFileName = fName;
//--- ... Export options
report.ExportOptions.DestinationOptions = diskOptions;
report.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
switch (Request.QueryString["format"])
{
case "pdf":
report.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
break;
case "excel":
ExcelFormatOptions excelOptions = new ExcelFormatOptions();
excelOptions.ExcelUseConstantColumnWidth = false;
excelOptions.ExcelTabHasColumnHeadings = false;
report.ExportOptions.ExportFormatType = ExportFormatType.Excel;
report.ExportOptions.FormatOptions = excelOptions;
break;
case "word":
break;
}
//--- Export report
try
{
report.Export();
}
catch(Exception ex)
{
Response.Redirect("./../error_page.aspx?error=" + ex.Message);
}
//--- Preview report
Response.Redirect("viewer2.aspx?target_report=" + Request.QueryString["target_report"] + "&format=" + Request.QueryString["format"], true);
}
else
{
Response.Write("<br> No records found for your report.");
}
}
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>
/// 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);
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;
}
}
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 { }
}
private void GenerarCodigoBarras(string code)
{
if (code != null)
{
string codigo = code + CodigoBarrasCalculoDelDigitoVerificador(code);
Code39 c39 = new Code39();
MemoryStream ms = new MemoryStream();
c39.FontFamilyName = "Free 3 of 9";
c39.FontFileName = "Facturacion\\Electronica\\Reportes\\FREE3OF9.TTF";
c39.FontSize = 30;
c39.ShowCodeString = true;
System.Drawing.Bitmap objBitmap = c39.GenerateBarcode(codigo);
objBitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
codigobarrasRpt = facturaRpt.OpenSubreport("CodigoBarra.rpt");
CrearTabla();
DataRow dr = this.dsImages.Tables["images"].NewRow();
dr["path"] = "ninguno";
dr["image"] = ms.ToArray();
this.dsImages.Tables["images"].Rows.Add(dr);
codigobarrasRpt.SetDataSource(this.dsImages);
ms.Close();
ms = null;
}
}
/// <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;
}
}
// 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 { }
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source)
{
if (s_FileName.ToLower().IndexOf(".xls") < 0)
{
s_FileName = s_FileName + ".xls";
}
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
System.IO.Stream stream = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
stream.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("BIG5");
Response.OutputStream.Write(bytes, 0, bytes.Length);
Response.Flush();
Response.Close();
report.Close();
}
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
break;
case "PDF":
string s_par_Program_ID = string.Empty;
string s_par_LoginUser = string.Empty;
s_par_Program_ID = this.PageCode;
s_par_LoginUser = Session["UID"].ToString();
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
break;
default:
break;
}
}
/// <summary>
/// 產生報表
/// </summary>
/// <param name="dt">DataTable資料</param>
/// <param name="exp_type">產生PDF或Excel</param>
private void ShowReport(DataTable dt, string exp_type)
{
try
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM111/VAM11R.rpt"));
report.SetDataSource(dt);
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
string strParFDate = string.Empty;
string strParFSType = string.Empty;
string strParFTaxType = string.Empty;
Program_ID = this.PageCode + "R";
LoginUser = Session["UID"].ToString();
string V_REPORT_TYPE = ""; //資料選擇
if (rdoType1.Checked) V_REPORT_TYPE = "全部";
if (rdoType2.Checked) V_REPORT_TYPE = "廠商進貨";
if (rdoType3.Checked) V_REPORT_TYPE = "廠商退貨";
report.SetParameterValue("par_Program_ID", Program_ID);
report.SetParameterValue("par_LoginUser", LoginUser);
report.SetParameterValue("par_LoginName", Login_Name);
string inDate = (SLP_SLPDateRange.StartDate + ((SLP_SLPDateRange.StartDate.Length == 0 && SLP_SLPDateRange.EndDate.Length == 0) ? "" : "～") + SLP_SLPDateRange.EndDate);
if (inDate == "") inDate = "全部";
report.SetParameterValue("par_Date", "進/退貨日期：" + inDate);
//string vendor1 = ((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase1).FindControl("TextBoxName")).Text;
string vendor1 = GetVendorName(SLP_VendorBase1.Text.Trim());
//string vendor2 = ((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase2).FindControl("TextBoxName")).Text;
string vendor2 = GetVendorName(SLP_VendorBase2.Text.Trim());
if (SLP_VendorBase1.Text.Trim().Length != 0 && vendor1 == "") vendor1 = Page.Request.Form[((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase1).FindControl("TextBoxName")).UniqueID];
if (SLP_VendorBase2.Text.Trim().Length != 0 && vendor2 == "") vendor2 = Page.Request.Form[((TextBox)((ASP.vdm_slp_slp_vendorbase_ascx)SLP_VendorBase2).FindControl("TextBoxName")).UniqueID];
string vendor = (SLP_VendorBase1.Text + vendor1 + ((SLP_VendorBase1.Text.Trim().Length == 0 && SLP_VendorBase2.Text.Trim().Length == 0) ? "" : "～") + SLP_VendorBase2.Text + vendor2);
if (SLP_VendorBase1.Text.Trim().Length == 0 && SLP_VendorBase2.Text.Trim().Length == 0) vendor = "全部";
report.SetParameterValue("par_Vendor", "供應商：" + vendor);
report.SetParameterValue("par_Tax", "課稅別：" + ((DropDownList)SLP_EnumBase.FindControl("D1")).SelectedItem.Text);
report.SetParameterValue("par_Type", "資料選擇：" + V_REPORT_TYPE);
//檔案匯出
if (exp_type == "btnExport")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = "供應商暫估貨款明細表.xls";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
string rpt_name = "供應商暫估貨款明細表.PDF";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(rpt_name, System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
}
Response.Flush();
Response.Close();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.Message;
}
finally { }
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
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 列印報表
/// </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();
}
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;
}
}
/// <summary>
/// show報表
/// </summary>
//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/CGD251R01.rpt"));
// report.SetDataSource(Dt);
// s_par_Program_ID = this.PageCode;
// s_par_LoginUser = Session["UID"].ToString();
// //ParameterFields pars = new ParameterFields();
// //AddParameter("par_Program_ID", s_par_Program_ID, pars);
// //AddParameter("par_LoginUser", s_par_LoginUser, pars);
// this.CrystalReportViewer1.ReportSource = report;
// //this.CrystalReportViewer1.ParameterFieldInfo.Add(pars[0]);
// //this.CrystalReportViewer1.ParameterFieldInfo.Add(pars[1]);
// this.CrystalReportViewer1.Visible = true;
// Session["Rpt_Data_1" + PageTimeStamp.Value] = Dt;
// Session["ButtonSelect"] = "ShowReport";
// }
// catch (Exception ex)
// {
// ErrorMsgLabel.Text = ex.Message;
// }
// finally { }
//}
/// <summary>
/// Show未列印報表
/// </summary>
/// <param name="Dt"></param>
//private void ShowUnprintReport(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/CGD251R02.rpt"));
// report.SetDataSource(Dt);
// s_par_Program_ID = this.PageCode;
// s_par_LoginUser = Session["UID"].ToString();
// ParameterFields pars = new ParameterFields();
// AddParameter("par_Program_ID", s_par_Program_ID, pars);
// AddParameter("par_LoginUser", s_par_LoginUser, pars);
// this.CrystalReportViewer1.ReportSource = report;
// this.CrystalReportViewer1.ParameterFieldInfo.Add(pars[0]);
// this.CrystalReportViewer1.ParameterFieldInfo.Add(pars[1]);
// this.CrystalReportViewer1.Visible = true;
// Session["Rpt_Data_2" + PageTimeStamp.Value] = Dt;
// Session["ButtonSelect"] = "ShowUnprintReport";
// }
// 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>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable Dt)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(Dt);
string s_par_Program_ID = this.PageCode;
string s_par_LoginUser = Session["UID"].ToString();
this.CrystalReportViewer1.ReportSource = report;
report.SetParameterValue("par_Program_ID", s_par_Program_ID);
report.SetParameterValue("par_LoginUser", s_par_LoginUser);
System.IO.Stream stream = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
stream.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytes, 0, bytes.Length);
Response.Flush();
Response.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);
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>
/// 利用 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 前置檢查與參數過濾
DropDownList ddlCodeFile1 = (DropDownList)(this.SLP_CodeFile1.FindControl("D1"));
string V_LOCATE_NO = this.SLP_CodeFile1.Text + " " + ddlCodeFile1.SelectedItem.Text;
string V_LOCATE_SECTOR_S = this.ddlS.Text;
string V_LOCATE_SECTOR_E = this.ddlE.Text;
par_ProgramID = this.PageCode;
par_LoginUser = Session["UID"].ToString() + " " + Get_LoginName();
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("V_LOCATE_NO", V_LOCATE_NO);
report.SetParameterValue("V_LOCATE_SECTOR_S", V_LOCATE_SECTOR_S);
report.SetParameterValue("V_LOCATE_SECTOR_E", V_LOCATE_SECTOR_E);
report.SetParameterValue("par_Program_ID", 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();
report.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
break;
default:
break;
}
}
private void 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;
}
}
/// <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, 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
}
//Crystal Report 直接輸出 PDF 檔
private void ExportReport(DataTable dt)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/CGR021R01.rpt"));
report.SetDataSource(dt);
//CrystalReport Parameter 參數設定
string Program_ID = string.Empty;
string LoginUser = string.Empty;
Program_ID = this.PageCode.Substring(0, 5) + "R01";
LoginUser = Session["UID"].ToString();
report.SetParameterValue("par_Program_ID", this.PageCode);
report.SetParameterValue("par_LoginUser", LoginUser);
//檔案匯出
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("退貨結轉檢核異常報表.PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
}
private void GetDiffdata()
{
try
{
string str = string.Empty;
if (Chk_All.Checked == true)
{
cr.Load(Server.MapPath("Crpt_CCdifferenceALL.rpt"));
}
else
{
cr.Load(Server.MapPath("Crpt_DpuDifference.rpt"));
}
cr.SetDatabaseLogon("onlinemilktest.in", "AMPS");
//CrystalDecisions.CrystalReports.Engine.TextObject t1;
//CrystalDecisions.CrystalReports.Engine.TextObject t2;
CrystalDecisions.CrystalReports.Engine.TextObject t3;
CrystalDecisions.CrystalReports.Engine.TextObject t4;
//CrystalDecisions.CrystalReports.Engine.TextObject t5;
//t1 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_CompanyName"];
//t2 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_plantName"];
t3 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Fromdate"];
t4 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_Todate"];
//t5 = (TextObject)cr.ReportDefinition.Sections[0].ReportObjects["txt_phoneno"];
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
dt1 = DateTime.ParseExact(txt_FromDate.Text, "dd/MM/yyyy", null);
dt2 = DateTime.ParseExact(txt_ToDate.Text, "dd/MM/yyyy", null);
//t1.Text = ccode + "_" + cname;
//t2.Text = ddl_Plantname.SelectedItem.Value + "_PhoneNo :" + txt_PlantPhoneNo.Text.Trim();
t3.Text = txt_FromDate.Text.Trim();
t4.Text = "To : " + txt_ToDate.Text.Trim();
// t5.Text = managmobNo;
//string d1 = dt1.ToString("MM/dd/yyyy");
string d1 = dt1.ToString("MM/dd/yyyy");
string d11 = dt1.ToString("MM/dd/yyyy hh:mm:ss");
string d2 = dt2.ToString("MM/dd/yyyy");
string d22 = dt2.ToString("MM/dd/yyyy hh:mm:ss");
SqlConnection con = null;
string connection = ConfigurationManager.ConnectionStrings["AMPSConnectionString"].ConnectionString;
con = new SqlConnection(connection);
//
// str = "SELECT f1.Smilkkg AS pMilkkg,f1.Afat AS PAfat,f1.Asnf AS PAsnf,f1.Sfatkg AS Pfatkg,f1.Ssnfkg AS Psnfkg,f1.Samount/(f1.Smilkkg*1.03) AS Arate,f2.Smilkkg AS CMilkkg,f2.Afat AS CAfat,f2.Asnf AS CAsnf,f2.Sfatkg AS Cfatkg,f2.Ssnfkg AS Csnfkg,(f1.Smilkkg-f2.Smilkkg) AS Diffmkg,(f1.Afat-f2.Afat) AS Difffat,(f1.Asnf-f2.Asnf) AS Diffsnf,(f1.Sfatkg-f2.Sfatkg) AS Difffatkg,(f1.Ssnfkg-f2.Ssnfkg) AS Diffsnfkg FROM " +
//" (SELECT SUM(Milk_kg)AS Smilkkg,(SUM(fat_kg)*100)/SUM(milk_kg) AS Afat,(SUM(snf_kg)*100)/SUM(milk_kg) AS Asnf,SUM(fat_kg) AS Sfatkg,SUM(snf_kg) AS Ssnfkg ,SUM(Amount) AS Samount,Agent_id FROM procurement Where Plant_Code='156' AND Sessions='AM' AND prdate='"+d1.ToString().Trim()+"' AND Agent_id='1621' GROUP BY agent_id,sessions) AS f1 " +
//" LEFT JOIN " +
//" (SELECT SUM(Milkkg) AS Smilkkg,((SUM(fatkg)*100)/SUM(Milkkg)) AS Afat,((SUM(snfkg)*100)/SUM(Milkkg)) AS Asnf,SUM(fatkg) AS Sfatkg,Sum(snfkg) AS Ssnfkg,agent_code FROM " +
//" (SELECT CONVERT(float,milk_kg) AS Milkkg,CONVERT(float,fat) AS fat,CONVERT(float,snf) AS snf,(CONVERT(float,fat)* CONVERT(float,milk_kg))/100 AS fatkg,(CONVERT(float,snf)* CONVERT(float,milk_kg))/100 AS snfkg,plant_code,agent_code,shift,prdate FROM THIRUMALABILLSNEW WHERE plant_code='156' AND agent_code='1621' AND shift='AM' AND prdate='" + d11.ToString().Trim() + "' ) AS t2 Group by t2.agent_code) AS f2 ON f1.Agent_id=f2.agent_code";
if (Chk_All.Checked == true)
{
str = "SELECT * FROM " +
" (SELECT f1.Smilkkg AS pMilkkg,f1.Afat AS PAfat,f1.Asnf AS PAsnf,f1.Sfatkg AS Pfatkg,f1.Ssnfkg AS Psnfkg,f1.Samount/(f1.Smilkkg*1.03) AS Arate,f2.Smilkkg AS CMilkkg,f2.Afat AS CAfat,f2.Asnf AS CAsnf,f2.Sfatkg AS Cfatkg,f2.Ssnfkg AS Csnfkg,(f1.Smilkkg-f2.Smilkkg) AS Diffmkg,(f1.Afat-f2.Afat) AS Difffat,(f1.Asnf-f2.Asnf) AS Diffsnf,(f1.Sfatkg-f2.Sfatkg) AS Difffatkg,(f1.Ssnfkg-f2.Ssnfkg) AS Diffsnfkg,f1.Agent_id FROM " +
" (SELECT SUM(Milk_kg)AS Smilkkg,(SUM(fat_kg)*100)/SUM(milk_kg) AS Afat,(SUM(snf_kg)*100)/SUM(milk_kg) AS Asnf,SUM(fat_kg) AS Sfatkg,SUM(snf_kg) AS Ssnfkg ,SUM(Amount) AS Samount,Agent_id FROM procurement Where Plant_Code='" + pcode + "' AND prdate BETWEEN '" + d1.ToString().Trim() + "' AND '" + d2.ToString().Trim() + "' GROUP BY agent_id) AS f1 " +
" LEFT JOIN " +
" (SELECT SUM(Milkkg) AS Smilkkg,((SUM(fatkg)*100)/SUM(Milkkg)) AS Afat,((SUM(snfkg)*100)/SUM(Milkkg)) AS Asnf,SUM(fatkg) AS Sfatkg,Sum(snfkg) AS Ssnfkg,agent_code FROM " +
" (SELECT CONVERT(float,milk_kg) AS Milkkg,CONVERT(float,fat) AS fat,CONVERT(float,snf) AS snf,(CONVERT(float,fat)* CONVERT(float,milk_kg))/100 AS fatkg,(CONVERT(float,snf)* CONVERT(float,milk_kg))/100 AS snfkg,plant_code,agent_code FROM VMCCDPU WHERE plant_code='" + pcode + "' AND prdate BETWEEN '" + d11.ToString().Trim() + "' AND '" + d22.ToString().Trim() + "') AS t2 Group by t2.agent_code) AS f2 ON f1.Agent_id=f2.agent_code ) AS f3 WHERE f3.CMilkkg IS NOT NULL ORDER BY f3.Agent_id ";
}
else
{
str = "SELECT f1.Smilkkg AS pMilkkg,f1.Afat AS PAfat,f1.Asnf AS PAsnf,f1.Sfatkg AS Pfatkg,f1.Ssnfkg AS Psnfkg,f1.Samount/(f1.Smilkkg*1.03) AS Arate,f2.Smilkkg AS CMilkkg,f2.Afat AS CAfat,f2.Asnf AS CAsnf,f2.Sfatkg AS Cfatkg,f2.Ssnfkg AS Csnfkg,(f1.Smilkkg-f2.Smilkkg) AS Diffmkg,(f1.Afat-f2.Afat) AS Difffat,(f1.Asnf-f2.Asnf) AS Diffsnf,(f1.Sfatkg-f2.Sfatkg) AS Difffatkg,(f1.Ssnfkg-f2.Ssnfkg) AS Diffsnfkg,f1.Prdate AS pdate,f1.Sessions AS sess,f1.Agent_id FROM " +
" (SELECT SUM(Milk_kg)AS Smilkkg,(SUM(fat_kg)*100)/SUM(milk_kg) AS Afat,(SUM(snf_kg)*100)/SUM(milk_kg) AS Asnf,SUM(fat_kg) AS Sfatkg,SUM(snf_kg) AS Ssnfkg ,SUM(Amount) AS Samount,Agent_id,Sessions,CONVERT(nvarchar(12),prdate,103) AS Prdate FROM procurement Where Plant_Code='" + pcode + "' AND prdate BETWEEN '" + d1.ToString().Trim() + "' AND '" + d2.ToString().Trim() + "' AND Agent_id='" + agent_id.Trim() + "' GROUP BY agent_id,sessions,Prdate) AS f1 " +
" LEFT JOIN " +
" (SELECT SUM(Milkkg) AS Smilkkg,((SUM(fatkg)*100)/SUM(Milkkg)) AS Afat,((SUM(snfkg)*100)/SUM(Milkkg)) AS Asnf,SUM(fatkg) AS Sfatkg,Sum(snfkg) AS Ssnfkg,agent_code,shift,prdate FROM " +
" (SELECT CONVERT(float,milk_kg) AS Milkkg,CONVERT(float,fat) AS fat,CONVERT(float,snf) AS snf,(CONVERT(float,fat)* CONVERT(float,milk_kg))/100 AS fatkg,(CONVERT(float,snf)* CONVERT(float,milk_kg))/100 AS snfkg,plant_code,agent_code,shift,CONVERT(nvarchar(12),prdate,103) AS Prdate FROM VMCCDPU WHERE plant_code='" + pcode + "' AND agent_code='" + agent_id.Trim() + "' AND prdate BETWEEN '" + d11.ToString().Trim() + "' AND '" + d22.ToString().Trim() + "') AS t2 Group by t2.agent_code,t2.shift,t2.prdate) AS f2 ON f1.Agent_id=f2.agent_code AND f1.Sessions=f2.shift AND f1.Prdate=f2.prdate";
}
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
da.Fill(dt);
cr.SetDataSource(dt);
CrystalReportViewer1.ReportSource = cr;
}
catch (Exception ex)
{
Lbl_Errormsg.Visible = true;
Lbl_Errormsg.Text = ex.ToString();
}
}
/// <summary>
/// 利用 CrystalReport 列印 Excel 報表
/// </summary>
/// <param name="s_FileName">Excel報表的檔名</param>
/// <param name="s_rptFilePath">rpt檔的路徑</param>
/// <param name="dt_Source">要列印的資料</param>
/// /// <param name="reportFormatType">檔案型態</param>
private void LoadCrystalReport(string s_FileName, string s_rptFilePath, DataTable dt_Source, string reportFormatType)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(s_rptFilePath);
report.SetDataSource(dt_Source);
string par_ChanNo = string.Empty;
string par_Store = string.Empty;
#region 前置檢查與參數過濾
par_ChanNo = Server.UrlDecode(Request.QueryString["Chan"].ToString());
par_Store = Server.UrlDecode(Request.QueryString["Store"].ToString());
#endregion
#region 組合查詢條件至ArrayList
ArrayList returnList = new ArrayList();
returnList.Clear();
report.SetParameterValue("ChanNo", par_ChanNo);
report.SetParameterValue("Store", par_Store);
#endregion
//判斷輸出檔案型態
switch (reportFormatType)
{
case "EXCEL":
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
report.Close();
break;
case "PDF":
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//export file
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + s_FileName);//excel檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
report.Close();
break;
default:
break;
}
}
private void ShowReportA(DataTable dtA, DataTable dtB, string V_FORM_TYPE)
{
#region 利用CrystalReport列印報表
//使用者名稱
DataTable dt1 = new DataTable();
dt1 = (DataTable)Session["UserInfo"];
string Login_Name = dt1.Rows[0]["Name"].ToString();
CrystalDecisions.CrystalReports.Engine.ReportDocument report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
report.Load(Server.MapPath("./REPORT/VAM051/VAM052R01.rpt"));
DataSet ds = new DataSet();
dtA.TableName = "VAM052_R1";
ds.Tables.Add(dtA.Copy());
dtB.TableName = "VAM052_R2";
ds.Tables.Add(dtB.Copy());
report.SetDataSource(ds);
report.SetParameterValue("par_Count", Convert.ToString(dtA.Rows.Count + dtB.Rows.Count));
if (this.hidTYPE.Value == "1")
{
report.SetParameterValue("par_Program_ID", "註記解除");
report.SetParameterValue("par_YM", this.Label7.Text);
report.SetParameterValue("par_Source_date", "註記解除日期/時間：" + System.DateTime.Now.ToLongDateString());
report.SetParameterValue("par_LoginName", "註記解除人員：" + Session["UID"].ToString() + Login_Name);
}
else
{
report.SetParameterValue("par_Program_ID", "註記確認");
report.SetParameterValue("par_YM", this.SLP_YearMonth1.Text);
report.SetParameterValue("par_Source_date", "註記日期/時間：" + System.DateTime.Now.ToLongDateString());
report.SetParameterValue("par_LoginName", "註記人員：" + Session["UID"].ToString() + Login_Name);
}
//檔案匯出
if (V_FORM_TYPE == "XLS")
{
System.IO.Stream streamXLS = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
byte[] bytesXLS = new byte[streamXLS.Length];
streamXLS.Read(bytesXLS, 0, bytesXLS.Length);
streamXLS.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("供應商單品結帳.XLS", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/xls";
Response.OutputStream.Write(bytesXLS, 0, bytesXLS.Length);
Response.Flush();
Response.Close();
}
else
{
System.IO.Stream streamPDF = report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byte[] bytesPDF = new byte[streamPDF.Length];
streamPDF.Read(bytesPDF, 0, bytesPDF.Length);
streamPDF.Seek(0, System.IO.SeekOrigin.Begin);
//Export File
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("供應商單品結帳.PDF", System.Text.Encoding.UTF8));//匯出檔名
Response.ContentType = "application/pdf";
Response.OutputStream.Write(bytesPDF, 0, bytesPDF.Length);
Response.Flush();
Response.Close();
}
report.Close();
#endregion
}
/// <summary>
/// 匯出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
}
