private void GeneratePDF()
{
ReportDocument rpt = new ReportDocument();
switch (cboView.SelectedItem.Value)
{
case "0":
rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/ChartOfAccountsDetails.rpt"));
break;
case "1":
rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/ChartOfAccountsSummary.rpt"));
break;
}
ExportOptions exportop = new ExportOptions();
DiskFileDestinationOptions dest = new DiskFileDestinationOptions();
string strPath = Server.MapPath(@"\RetailPlus\temp\");
string strFileName = "chartofacc_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmssff") + ".pdf";
if (System.IO.File.Exists(strPath + strFileName))
System.IO.File.Delete(strPath + strFileName);
dest.DiskFileName = strPath + strFileName;
exportop = rpt.ExportOptions;
SetDataSource(rpt);
exportop.DestinationOptions = dest;
exportop.ExportDestinationType = ExportDestinationType.DiskFile;
exportop.ExportFormatType = ExportFormatType.PortableDocFormat;
rpt.Export(); rpt.Close(); rpt.Dispose();
fraViewer.Attributes.Add("src","/RetailPlus/temp/" + strFileName);
}
public void ShowGenericRpt()
{
try
{
bool isValid = true;
string strReportName = System.Web.HttpContext.Current.Session["ReportName"].ToString(); // Setting ReportName
if (string.IsNullOrEmpty(strReportName))
{
isValid = false;
}
if (isValid)
{
ReportDocument rd = new ReportDocument();
string strRptPath = System.Web.HttpContext.Current.Server.MapPath("~/") + "Report//" + strReportName;
rd.Load(strRptPath);
rd.VerifyDatabase();
rd.ExportToHttpResponse(ExportFormatType.PortableDocFormat, System.Web.HttpContext.Current.Response, false, "crReport");
// Clear all sessions value
Session["ReportName"] = null;
}
else
{
Response.Write("<H2>Nothing Found; No Report name found</H2>");
}
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
public ReportDocument GetVendorListByActivationStatus(string sortedBy, string sortingOrder, string isActive, LumexDBPlayer db)
{
try
{
LumexSessionManager.Add("rptName", "Vendor_List");
ReportDocument reportDocument = new ReportDocument();
db.AddParameters("@SortedBy", sortedBy.Trim());
db.AddParameters("@SortingOrder", sortingOrder.Trim());
db.AddParameters("@IsActive", isActive.Trim());
if (isActive == "All")
{
reportDocument.Load(reportPath + "GET_VENDORS.rpt");
}
else
{
reportDocument.Load(reportPath + "GET_VENDORS_BY_ACTIVATION_STATUS.rpt");
}
reportDocument.SetDataSource(db.ExecuteDataTable("REPORT_GET_VENDORS_BY_ACTIVATION_STATUS", true));
return reportDocument;
}
catch (Exception)
{
throw;
}
}
private void CrystalReportViewer1_Loaded(object sender, RoutedEventArgs e)
{
ReportDocument report = new ReportDocument();
if (m_isurduvisible)
{
report.Load("../../Reports/SaleReceipt_u.rpt");
}
if (!m_isurduvisible)
{
report.Load("../../Reports/SaleReceipt_e.rpt");
}
ArrayList reportdata = new ArrayList();
reportdata.Add(productSold);
using(var db = new HCSMLEntities1())
{
try
{
report.SetDataSource(from c in db.saleproducts where c.seqid == productSold.seqid select c);
}
catch (NotSupportedException ex)
{}
catch (Exception ex)
{}
}
crystalReportsViewer1.ViewerCore.ReportSource = report;
report.Dispose();
}
private void btnGenerar_Click(object sender, EventArgs e)
{
if (rbgTipoConsulta.SelectedIndex == 0)
{
ReportDocument rpt = new ReportDocument();
rpt.Load(Application.StartupPath+ "//Taller//crptOrdenesTrabajosG.rpt");
rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt);
report.ShowDialog();
}
if (rbgTipoConsulta.SelectedIndex == 1)
{
ReportDocument rpt = new ReportDocument();
rpt.Load(Application.StartupPath + "//Taller//crptOrdenesTrabajosC.rpt");
rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt);
report.ShowDialog();
}
if (rbgTipoConsulta.SelectedIndex == 2)
{
ReportDocument rpt = new ReportDocument();
rpt.Load(Application.StartupPath + "//Taller//crptOrdenesTrabajosV.rpt");
rpt.Load(@"C:\Users\Mario\Desktop\Proyecto\taller\SistemasIntegrados\datos\Taller\crptOrdenesTrabajosV.rpt");
rptOrdenesTrabajos report = new rptOrdenesTrabajos(rpt);
report.ShowDialog();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
int DoctorID;
//string DoctorName;
string StartDate;
string EndDate;
DoctorID = Convert.ToInt32(cbDoctor.SelectedValue.ToString());
// DoctorName=Convert.ToString(cbDoctor.SelectedText.ToString());
StartDate = Convert.ToString(dtpStartDate.Text);
EndDate = Convert.ToString(dtpEndDate.Text);
ReportDocument reportDocument = new ReportDocument();
ParameterField paramField = new ParameterField();
ParameterFields paramFields = new ParameterFields();
ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
string ReportPath = ConfigurationManager.AppSettings["ReportPath"];
paramField.Name = "@DoctorID";
//paramField.Name = "@DoctorName";
paramField.Name = "@StartDate";
paramField.Name = "@EndDate";
paramDiscreteValue.Value = 1;
if (chkdailyreport.Checked == true)
{
reportDocument.Load(ReportPath + "Report\\DoctorPay_CrystalReportNew1.rpt");
}
else
{
reportDocument.Load(ReportPath + "Report\\DoctorPayment_ReportNew.rpt");
}
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.DatabaseName = "DB_MedicalShop_02Sept20159PM";
//connectionInfo.UserID = "wms";
//connectionInfo.Password = "******";
connectionInfo.IntegratedSecurity = true;
SetDBLogonForReport(connectionInfo, reportDocument);
reportDocument.SetParameterValue("@DoctorID", DoctorID);
//reportDocument.SetParameterValue("@DoctorName", DoctorName);
reportDocument.SetParameterValue("@StartDate", StartDate);
reportDocument.SetParameterValue("@EndDate", EndDate);
SupplierCrystalRpt.ReportSource = reportDocument;
SupplierCrystalRpt.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private ReportDocument getReportDocument()
{
ReportDocument rpt = new ReportDocument();
if (!chkGroupItems.Checked)
rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/_MostSalableItemsReport.rpt"));
else
rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/_MostSalableItemsReportGrouped.rpt"));
return rpt;
}
protected void Page_Init(object sender, EventArgs e)
{
//if (!IsPostBack)
//{
int temp;
if (Request["tipo"] != null && int.TryParse(Request["tipo"].ToString(), out temp)
&& (int.Parse(Request["tipo"].ToString()) == 1 || int.Parse(Request["tipo"].ToString()) == 2)
&& Request["codigo"] != null && int.TryParse(Request["codigo"].ToString(), out temp))
{
ReportDocument doc = new ReportDocument();
Hashtable hash = null;
if (int.Parse(Request["tipo"].ToString()) == 1) //Movimentação
{
IList<Movimento> lm = new List<Movimento>();
lm.Add(Factory.GetInstance<IMovimentacao>().BuscarPorCodigo<Movimento>(int.Parse(Request["codigo"].ToString())));
hash = Factory.GetInstance<IMovimentacao>().RetornaHashMovimentacaoRemanejamento<IList<Movimento>>(int.Parse(Request["tipo"].ToString()), lm);
DSCorpoMovimentacao dsc = new DSCorpoMovimentacao();
DSCabecalhoMovimentacao dscab = new DSCabecalhoMovimentacao();
dsc.Tables.Add((DataTable)hash["corpo"]);
dscab.Tables.Add((DataTable)hash["cabecalho"]);
doc.Load(Server.MapPath("RelatoriosCrystal/RelMovimentacao.rpt"));
doc.SetDataSource(dscab.Tables[1]);
doc.Subreports[0].SetDataSource(dsc.Tables[1]);
CrystalReportViewer_Relatorio.ReportSource = doc;
CrystalReportViewer_Relatorio.DataBind();
}
else //Remanejamento
{
IList<RemanejamentoMedicamento> lrm = new List<RemanejamentoMedicamento>();
lrm.Add(Factory.GetInstance<IMovimentacao>().BuscarPorCodigo<RemanejamentoMedicamento>(int.Parse(Request["codigo"].ToString())));
hash = Factory.GetInstance<IMovimentacao>().RetornaHashMovimentacaoRemanejamento<IList<RemanejamentoMedicamento>>(int.Parse(Request["tipo"].ToString()),lrm);
DSCorpoRemanejamento dsr = new DSCorpoRemanejamento();
DSCabecalhoRemanejamento dsc = new DSCabecalhoRemanejamento();
dsr.Tables.Add((DataTable)hash["corpo"]);
dsc.Tables.Add((DataTable)hash["cabecalho"]);
doc.Load(Server.MapPath("RelatoriosCrystal/RelRemanejamento.rpt"));
doc.SetDataSource(dsc.Tables[1]);
doc.Subreports[0].SetDataSource(dsr.Tables[1]);
CrystalReportViewer_Relatorio.ReportSource = doc;
CrystalReportViewer_Relatorio.DataBind();
}
}
//}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
DataTable _dtCabeceraOper = null;
DataTable _dtDetalleOper = null;
_dtCabeceraOper = (DataTable)Session["dtCabeceraOperacion"];
_dtDetalleOper = g_dtDetOperacion;
if (_dtDetalleOper != null && _dtCabeceraOper != null)
{
if (_dtDetalleOper.Rows.Count != 0)
{
ReportDocument rpt = new ReportDocument();
rpt.Load(Server.MapPath("rpt/crFactura.rpt"));
rpt.FileName = Server.MapPath("rpt/crFactura.rpt");
rpt.SetDataSource(_dtCabeceraOper);
rpt.Subreports[0].SetDataSource(_dtDetalleOper);
CrystalReportViewer1.ReportSource = rpt;
CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private void CustomerTransactionReportForm_Load(object sender, EventArgs e)
{
ReportDocument cryRpt;
using (Devart.Data.SQLite.SQLiteConnection myConn = new Devart.Data.SQLite.SQLiteConnection(myConfig.connstr))
{
using (SQLiteCommand myCmd = new SQLiteCommand(sqlcmd, myConn))
{
myConn.Open();
using (SQLiteDataReader myReader = myCmd.ExecuteReader())
{
dataSet2.customer_trans.Load(myReader);
myConn.Close();
}
}
}
this.customerTableAdapter1.Fill(this.dataSet2.customer);
this.configurationTableAdapter1.Fill(this.dataSet2.configuration);
CustomerTransactionReport rpt = new CustomerTransactionReport();
rpt.SetDataSource(this.dataSet2);
cryRpt = new ReportDocument();
cryRpt.Load(rpt.FileName.ToString());
cryRpt.SetDataSource(this.dataSet2);
cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, ReportFolder.reportFolderName + @"\CustomerTransactions.pdf");
}
//结帐查询
public static ReportDocument LoadReport(string rptfilename, string sql, string tablename)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
SqlDataAdapter dbDA = new SqlDataAdapter();
DataSet dataSet = new DataSet();
conn.ConnectionString = rms_var.ConnStr;
try
{
SqlCommand selectCMD = new SqlCommand(sql, conn);
selectCMD.CommandTimeout = 30;
dbDA.SelectCommand = selectCMD;
conn.Open();
dbDA.Fill(dataSet, tablename);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
ReportDocument cr_doc;
cr_doc = new ReportDocument();
cr_doc.Load(rptfilename);
cr_doc.SetDataSource(dataSet);
return cr_doc;
}
private void btnSearch_Click(object sender, EventArgs e)
{
int DoctorID;
DoctorID = Convert.ToInt32(cbDoctor.SelectedValue.ToString());
ReportDocument reportDocument = new ReportDocument();
ParameterField paramField = new ParameterField();
ParameterFields paramFields = new ParameterFields();
ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
string ReportPath = ConfigurationManager.AppSettings["ReportPath"];
paramField.Name = "@DoctorID";
paramDiscreteValue.Value = DoctorID;
reportDocument.Load(ReportPath + "Report\\DoctorCrystalReport.rpt");
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.DatabaseName = "DB_MedicalShop_02Sept20159PM";
//connectionInfo.UserID = "wms";
//connectionInfo.Password = "******";
connectionInfo.IntegratedSecurity = true;
SetDBLogonForReport(connectionInfo, reportDocument);
reportDocument.SetParameterValue("@DoctorID", DoctorID);
DoctorCrystalRpt.ReportSource = reportDocument;
DoctorCrystalRpt.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None;
}
private void FrmProjectReportViewer_Load(object sender, EventArgs e)
{
try
{
if (FrmFlagProject.ProjectFlag == 0)
{
lblProjectFlag.Text = "لیست طرح های پژوهشی در دست اجرا در سال" + " " + FrmFlagProject.year;
}
else if (FrmFlagProject.ProjectFlag == 1)
{
lblProjectFlag.Text = "لیست طرح های پژوهشی پایان یافته در سال" + " " + FrmFlagProject.year;
}
DataSet dataset = new DataSet();
ReportDocument RepDoc = new ReportDocument();
string dir = Environment.CurrentDirectory;
dir = dir + "\\CrtMainProject.rpt";
RepDoc.Load(dir);
RepDoc.SetDataSource(FrmFlagProject.objTbl);
crystalReportViewer1.ReportSource = RepDoc;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void cboYear_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string RepPath;
DataSet dataset = new DataSet();
ReportDocument RepDoc = new ReportDocument();
RepPath = @"D:\Project\Industrial College\Project1\Source23\Source22\Source21\Pazhohesh\Pazhohesh\Main\CrsThesis1.rpt";
RepDoc.Load(RepPath);
objDataAdapter.SelectCommand = new SqlCommand();
SqlConnection objConnection = new SqlConnection("Data Source=User-PC;Initial Catalog=ResearchProje1;Integrated Security=True");
objDataAdapter.SelectCommand.Connection = objConnection;
DataTable objGozaresh = new DataTable();
objGozaresh.Clear();
objDataAdapter.SelectCommand.Parameters.AddWithValue("@DeliveryDate" , cboYear.Text + "%");
objDataAdapter.SelectCommand.CommandText = "Select * from VwThesisGozaresh1 where DeliveryDate Like @DeliveryDate";
objDataAdapter.Fill(objGozaresh);
RepDoc.SetDataSource(objGozaresh);
crystalReportViewer1.ReportSource = RepDoc;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void LoadReportFile(string rptName, string procName, string rptTitle, Dictionary<string, string> paraValues, Dictionary<string, string> procValues)
{
try
{
string rptPath = Server.MapPath("/ReportPages/" + rptName);
_rptDoc = new ReportDocument();
_rptDoc.Load(rptPath);
_rptDoc.SummaryInfo.ReportTitle = rptTitle;
_rptDoc.SetDataSource(ExcuteQuery(procName, procValues));
if (paraValues.Count > 0)
foreach (KeyValuePair<string, string> par in paraValues)
{
ParameterDiscreteValue pValue = new ParameterDiscreteValue();
pValue.Value = par.Value;
ParameterFieldDefinition pDef = _rptDoc.DataDefinition.ParameterFields[par.Key];
pDef.CurrentValues.Clear();
pDef.CurrentValues.Add(pValue);
pDef.ApplyCurrentValues(pDef.CurrentValues);
}
crystalReportViewer.ReportSource = _rptDoc;
}
catch (Exception e)
{
label.Text = "Report Error: " + e.Message;
}
finally
{
GC.Collect();
}
}
public void Exportar(string key)
{
try
{
ReportDocument objRpt = new ReportDocument();
DataSet ds = (DataSet)this.GetDataReport(key);
string reportPath = "C:\\Reportes\\CRTejTicket_rpt.rpt";
objRpt.Load(reportPath);
ExportOptions crExportOptions = new ExportOptions();
objRpt.SetDataSource(ds.Tables[0]);
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = Environment.SpecialFolder.MyDocuments.ToString();
saveFileDialog.Filter = "Document (*.pdf)|*.PDF";
saveFileDialog.FilterIndex = 1;
saveFileDialog.FileName = "Etiqueta.pdf";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
objRpt.ExportToDisk(ExportFormatType.PortableDocFormat, saveFileDialog.FileName); ;
}
crExportOptions = objRpt.ExportOptions;
objRpt.Export();
}
catch (Exception ex)
{
ex.ToString();
}
}
public RptDefinitionWriter(string filename)
{
Report = new ReportDocument();
Report.Load(filename, OpenReportMethod.OpenReportByTempCopy);
Trace.WriteLine("Loaded report");
}
private void btnGerar_Click(object sender, EventArgs e)
{
ReportDocument crDocument = new ReportDocument();
crDocument.Load(Application.StartupPath + @"\..\..\RelLocalizacaoProcesso.rpt");
if (rbSemFiltro.Checked)
{
crDocument.SetDataSource(new RelProcesso(new Banco()).semfiltro());
}
else if (ckbNome.Checked && !ckbSituacao.Checked)
{
crDocument.SetDataSource(new RelProcesso(new Banco()).porNome(ttbNome.Text));
}
else if (!ckbNome.Checked && ckbSituacao.Checked)
{
//if(cbbSituacao.SelectedText == "Em Pasta")
// crDocument.SetDataSource(new RelProcesso(new Banco()).emPasta()));
//else
// crDocument.SetDataSource(new RelProcesso(new Banco()).arquivado()));
}
else if (ckbNome.Checked && ckbSituacao.Checked)
{
}
frmVisualizador f = new frmVisualizador();
f.carregar(crDocument);
f.ShowDialog();
}
protected void GenerateReport()
{
System.Console.Beep();
//Create report document
ReportDocument crystalReport = new ReportDocument();
if (txtStart.Text != "" && txtEnd.Text != "" )
{
//Load crystal report made in design view
crystalReport.Load(Server.MapPath("Reports/Notifications.rpt"));
//Set DataBase Login Info
crystalReport.SetDatabaseLogon("root", "123", @"localhost", "nsis");
//Provide parameter values
crystalReport.SetParameterValue("start", txtStart.Text.ToString());
crystalReport.SetParameterValue("end", txtEnd.Text.ToString());
//Set Report in to Report Viewer
crvReportViewer.ReportSource = crystalReport;
}
else
{
// lblError.ForeColor = Color.Red;
// lblError.Text = "Please Provide Valid Details";
}
}
private void frmReamMoneyPrint_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
ReamMoney reamMoney = new ReamMoney();
reamMoney = reamMoneyService.getReamMoneyByID(rid);
List<ReamMoneyReport> reamMoneyReports = new List<ReamMoneyReport>();
ReamMoneyReport reamMoneyReport = new ReamMoneyReport();
Person p = new Person();
p = personService.getPersonBypsnCode(reamMoney.person.psnCode);
reamMoneyReport.fullName = p.psnPreFix + " " + p.psnName + " " + p.psnLastName;
reamMoneyReport.amount =Convert.ToString(reamMoney.amount);
reamMoneyReport.reamDate = reamMoney.reamdate;
reamMoneyReports.Add(reamMoneyReport);
ReportDocument rp = new ReportDocument();
string path = Application.StartupPath + @"\report\rptReamMoney.rpt";
rp.Load(path);
rp.SetDataSource(reamMoneyReports);
PV.ReportSource = rp;
}
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = Session["uname"].ToString();
ReportDocument cryRpt = new ReportDocument();
cryRpt.Load(Server.MapPath("SoldProduct.rpt"));
/* ParameterFieldDefinitions PFDS;
ParameterFieldDefinition PFD;
ParameterValues PVS = new ParameterValues();
ParameterDiscreteValue PDV = new ParameterDiscreteValue();
PVS.Clear();
PDV.Value = "";
PFDS = cryRpt.DataDefinition.ParameterFields;
PFD = PFDS["@sDate"];
PVS = PFD.CurrentValues;
//
PVS.Add(PDV);
PFD.ApplyCurrentValues(PVS);
PDV.Value = "";
PFDS = cryRpt.DataDefinition.ParameterFields;
PFD = PFDS["@eDate"];
PVS = PFD.CurrentValues;
//PVS.Clear();
PVS.Add(PDV);
PFD.ApplyCurrentValues(PVS);*/
cryRpt.SetDatabaseLogon("sa", "123", "server", "auction");
CrystalReportViewer1.ReportSource = cryRpt;
CrystalReportViewer1.RefreshReport();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
ReportDocument rd;
rd = new ReportDocument();
rd.Load(@"C:\Users\PLABON\Documents\Visual Studio 2013\Projects\SalesAndInventorySystem\SalesAndInventorySystemUI\Report\a.rpt");
List<PersonType> company = companyGateway.GetCompanies();
var companyX = company.Select(x => new {x.ID, x.Name});
rd.SetDataSource(companyX);
crystalReportViewer1.ReportSource = rd;
crystalReportViewer1.Refresh();
int a = 10;
if (File.Exists(@"D:\" + "AAAA" + a +".pdf"))
File.Delete(@"D:\" + a++ + ".pdf");
rd.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, @"D:\" + a + ".pdf");
}
catch (Exception)
{
throw;
}
}
public ActionResult ExportReport()
{
List<Investment> allInvestment = new List<Investment>();
using (efarmingEntities dc = new efarmingEntities())
{
allInvestment = dc.Investments.ToList();
}
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "rpt_InvestmentList.rpt"));
rd.SetDataSource(allInvestment);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return File(stream, "application/pdf", "InvestmentList.pdf");
}
catch (Exception)
{
throw;
}
}
private void frminhoadon_Load(object sender, EventArgs e)
{
DataTable ds = hdbus.getHoaDon(KHAIBAO.mabenhnhan, KHAIBAO.ngaykham);
ds.TableName = "HoaDon";
DataTable dt2 = ctkbus.getDonThuoc(KHAIBAO.mabenhnhan + KHAIBAO.ngaykham);
dt2.TableName = "DonThuoc";
DataTable dt3 = cdbus.getCachDung();
dt3.TableName = "CachDung";
DataTable dt4 = ltbus.getLoaiThuoc();
dt4.TableName = "LoaiThuoc";
DataTable dt5 = dvbus.getDonVi();
dt5.TableName = "DonVi";
DataSet dts = new DataSet();
dts.Tables.Add(ds);
dts.Tables.Add(dt2);
dts.Tables.Add(dt3);
dts.Tables.Add(dt4);
dts.Tables.Add(dt5);
if (float.Parse(ds.Rows[0]["TienThuoc"].ToString()) == 0)
{
ReportDocument report = new ReportDocument();
report.Load("..\\..\\inhoadon1.rpt");
report.SetDataSource(dts);
crystalReportViewer.ReportSource = report;
inhoadon1 dt = new inhoadon1();
}
else
{
ReportDocument report = new ReportDocument();
report.Load("..\\..\\inhoadon.rpt");
report.SetDataSource(dts);
crystalReportViewer.ReportSource = report;
inhoadon dt = new inhoadon();
}
}
/// <summary>
/// Erzeugt aus einer CrystalReportsDatei oder einem PDF einen PDF Stream und sendet ihn an den Client
/// </summary>
/// <param name="pin_ReportVorlage">Pfad der Vorlage</param>
/// <param name="pin_ReportAuswahl">reportspezifische Daten</param>
/// <returns>Stream der ein PDF enthält</returns>
public Stream ErzeugeReport(string pin_ReportVorlage, string pin_ReportAuswahl)
{
if (pin_ReportVorlage.Substring(pin_ReportVorlage.LastIndexOf(".") + 1) == "pdf")
{
// Umwandeln des PDF Vordrucks in einen Stream
FileStream Report = new FileStream(Directory.GetCurrentDirectory() + pin_ReportVorlage, System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] Buffer = new byte[(int)Report.Length];
Report.Read(Buffer, 0, (int)Report.Length);
Report.Close();
// Versenden der Daten als MemoryStream
System.Windows.Forms.MessageBox.Show("Schicke Raus !!");
return new MemoryStream(Buffer, 0, Buffer.Length);
}
else
{
// Erstellen eines Reports
ReportDocument Report = new ReportDocument();
// Lade Reportvorlage
Report.Load(Directory.GetCurrentDirectory() + pin_ReportVorlage);
// Besondere SELECT Anfrage auf den Datensatz anwenden
if (pin_ReportAuswahl != String.Empty)
Report.DataDefinition.RecordSelectionFormula = pin_ReportAuswahl;
// muss als Stream übertragen werden, da ReportDocuments nicht Serializable sind
return Report.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
}
}
/// <summary>
/// 產生報表
/// </summary>
private void ConfigureCrystalReports()
{
try
{
string strDate = Session[string.Format("{0}QueryDateRange", Request["TimeStamp"].ToString())].ToString();
if (strDate == "~") strDate = "";
string strTime = Session[string.Format("{0}QueryTimeRange", Request["TimeStamp"].ToString())].ToString();
if (strTime == "~") strTime = "";
customerReport = new ReportDocument();
//取得報表格式檔RPT
customerReport.Load(Server.MapPath("ITM061R.rpt"));
customerReport.SetDataSource((DataTable)Session[string.Format("{0}ITM061", Request["TimeStamp"].ToString())]);
ParameterFields par1 = new ParameterFields();
AddParameter("ProgramName", PageProgramName, par1);
ParameterFields par2 = new ParameterFields();
AddParameter("UserName", Session["UID"].ToString(), par2);
ParameterFields par3 = new ParameterFields();
AddParameter("QueryDateRange", strDate, par3);
ParameterFields par4 = new ParameterFields();
AddParameter("QueryTime", strTime, par4);
CRV1.ParameterFieldInfo.Add(par1[0]);
CRV1.ParameterFieldInfo.Add(par2[0]);
CRV1.ParameterFieldInfo.Add(par3[0]);
CRV1.ParameterFieldInfo.Add(par4[0]);
CRV1.ReportSource = customerReport;
}
catch (Exception ex)
{
lblErrorMessage.Text = ex.Message;
}
}//ConfigureCrystalReports
private void GenerateWord()
{
ReportDocument rpt = new ReportDocument();
rpt.Load(Server.MapPath(Constants.ROOT_DIRECTORY + "/Reports/GeneralLedger.rpt"));
ExportOptions exportop = new ExportOptions();
DiskFileDestinationOptions dest = new DiskFileDestinationOptions();
string strPath = Server.MapPath(@"\retailplus\temp\");
string strFileName = "generalledger_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmssff") + ".doc";
if (System.IO.File.Exists(strPath + strFileName))
System.IO.File.Delete(strPath + strFileName);
dest.DiskFileName = strPath + strFileName;
exportop = rpt.ExportOptions;
SetDataSource(rpt);
exportop.DestinationOptions = dest;
exportop.ExportDestinationType = ExportDestinationType.DiskFile;
exportop.ExportFormatType = ExportFormatType.WordForWindows;
rpt.Export(); rpt.Close(); rpt.Dispose();
fraViewer.Attributes.Add("src","/retailplus/temp/" + strFileName);
}
private void CriaPDF()
{
Hashtable hashtable = (Hashtable)Session["HashAgendaMontadaPublicada"];
DSCabecalhoAgendaMontadaPublicada cabecalho = new DSCabecalhoAgendaMontadaPublicada();
cabecalho.Tables.Add((DataTable)hashtable["cabecalho"]);
DSRelatorioAgendaMontadaPublicada dados = new DSRelatorioAgendaMontadaPublicada();
dados.Tables.Add((DataTable)hashtable["dados"]);
ReportDocument repDoc = new ReportDocument();
repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_AgendaMontadaPublicada.rpt"));
repDoc.SetDataSource(dados.Tables[1]);
repDoc.Subreports["CrystalReportViewer_CabecalhoAgendaMontadaPublicada.rpt"].SetDataSource(cabecalho.Tables[1]);
System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "applicattion/octect-stream";
Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioDeAgendasMontadasPublicadas.pdf");
Response.AddHeader("Content-Length", s.Length.ToString());
Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray());
Response.End();
Session.Remove("HashAgendaMontadaPublicada");
}
protected void CriaPDF()
{
Hashtable hash = (Hashtable)Session["HashVagasDisponivel"];
//CrystalReportViewer_AgendaPrestador.DisplayToolbar = true;
//CrystalReportViewer_AgendaPrestador.EnableDatabaseLogonPrompt = false;
DSRelatorioVagasDisponivel conteudo = new DSRelatorioVagasDisponivel();
conteudo.Tables.Add((DataTable)hash["corpo"]);
ReportDocument repDoc = new ReportDocument();
repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_VagasDisponivel.rpt"));
repDoc.SetDataSource(conteudo.Tables[1]);
//repDoc.Database.Tables["CabecalhoAgendaPrestador"].SetDataSource((DataTable)hash["cabecalho"]);
//repDoc.Database.Tables["RelatorioAgendaPrestador"].SetDataSource((DataTable)hash["corpo"]);
System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "applicattion/octect-stream";
Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioVagasDisponivel.pdf");
Response.AddHeader("Content-Length", s.Length.ToString());
Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray());
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
//
string showreport = Request.QueryString["showreport"] == null ? "0" : Request.QueryString["showreport"].ToString();
if (showreport == "1")
{
try
{
this.Button1.Visible = false;
string PARM = Request.QueryString["PARM"] == null ? "0" : Request.QueryString["PARM"].ToString();
if (PARM != "0")
{
string data_index = GlobeAtt.DATA_INDEX;
string strdata = data_index + DES.DESDecrypt(PARM, DES.Key);
strdata = strdata.Trim();
report_file = BaseFun.getStrByIndex(strdata, data_index + "REPORT_FILE|", data_index);
report_sql = BaseFun.getStrByIndex(strdata, data_index + "REPORT_SQL|", data_index);
report_parm = BaseFun.getStrByIndex(strdata, data_index + "REPORT_PARM|", data_index);
}
else
{
report_file = Session["REPORT_FILE"].ToString();
report_sql = Session["REPORT_SQL"].ToString();
report_parm = Session["REPORT_PARM"].ToString();
}
// Response.Write(report_sql);
ReportDocument rd = new ReportDocument();
string file_ = Server.MapPath("../Report/" + report_file);
try
{
rd.Load(file_);
// cs.ReportDocument.Load(rd.FileName);
PageMargins pm = new PageMargins();
rd.PrintOptions.ApplyPageMargins(pm);
}
catch (Exception ex)
{
Response.Write(report_file + ex.Message);
return;
}
if (report_sql.Length > 10)
{
Oracle db = new Oracle();
DataTable dt_report = new DataTable();
db.ExcuteDataTable(dt_report, report_sql, CommandType.Text);
rd.SetDataSource(dt_report);
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.DataBind();
}
else
{
Oracle db = new Oracle();
ConnectionInfo ConnectionInfo1 = new ConnectionInfo();
ConnectionInfo1 = rd.Database.Tables[0].LogOnInfo.ConnectionInfo;
rd.SetDatabaseLogon(db.user_id, db.password);
ConnectionInfo1.DatabaseName = "";
ConnectionInfo1.UserID = db.user_id;
ConnectionInfo1.Password = db.password;
ConnectionInfo1.ServerName = db.data_source;
SetDBLogonForReport(ConnectionInfo1, rd);
if (rd.HasSavedData == true)
{
rd.Refresh();
}
string[] parm = report_parm.Split(',');
ParameterFields ParamFields = new ParameterFields();
for (int i = 0; i < rd.ParameterFields.Count; i++)
{
ParameterDiscreteValue ParamDiscreteValue = new ParameterDiscreteValue();
if (parm[i] != "[]")
{
ParamDiscreteValue.Value = parm[i];
if (i < parm.Length && parm[i] != "")
{
rd.ParameterFields[i].CurrentValues.Add(ParamDiscreteValue);
}
}
}
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.DataBind();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
return;
}
}
else
{
// string a002_key = Request.QueryString["A002KEY"] == null ? "-1" : Request.QueryString["A002KEY"].ToString();
// string A00201KEY = Request.QueryString["A00201KEY"] == null ? "-1" : Request.QueryString["A00201KEY"].ToString();
string report_conditon = Request.QueryString["reportconditon"] == null ? "1" : Request.QueryString["reportconditon"].ToString();
string key = Request.QueryString["JUMP_KEY"] == null ? "-1" : Request.QueryString["JUMP_KEY"].ToString();
string a002_key = Request.QueryString["JUMP_A002_KEY"] == null ? "-1" : Request.QueryString["JUMP_A002_KEY"].ToString();
string IF_JUMP = Request.QueryString["IF_JUMP"] == null ? "1" : Request.QueryString["IF_JUMP"].ToString();
// dt_a002 = Fun.getDtBySql("Select t.* from A002 t where menu_id='" + a002_key +"'");
dt_main = Fun.getDtBySql("Select t.*,pkg_a.getmenuname(t.a002_key,'" + GlobeAtt.A007_KEY + "') as show_name from A002_v01 t where a002_key='" + a002_key + "'");
try
{
report_file = dt_main.Rows[0]["datawindow_name"].ToString();
}
catch (Exception ex)
{
Response.Write("report_file" + ex.Message);
return;
}
try
{
menu_name = dt_main.Rows[0]["show_name"].ToString();
}
catch (Exception ex)
{
menu_name = "";
Response.Write("title" + ex.Message);
}
try
{
if (report_conditon == "1")
{
report_sql = dt_main.Rows[0]["query_table"].ToString();
if (report_sql.Length < 20)
{
report_sql = "Select t.* from " + report_sql + "t where 1=1 ";
}
Session["REPORT_SQL"] = report_sql;
}
else
{
report_sql = Session["REPORT_SQL"].ToString();
}
}
catch (Exception ex)
{
Response.Write("report_sql" + ex.Message);
return;
}
string file_ = Server.MapPath("../report/" + report_file);
try
{
rd.Load(file_);
ConnectionInfo ConnectionInfo1 = new ConnectionInfo();
ConnectionInfo1 = rd.Database.Tables[0].LogOnInfo.ConnectionInfo;
ConnectionInfo1.DatabaseName = "";
ConnectionInfo1.UserID = Fun.db.user_id;
ConnectionInfo1.Password = Fun.db.password;
ConnectionInfo1.ServerName = Fun.db.data_source;
SetDBLogonForReport(ConnectionInfo1, rd);
if (rd.HasSavedData == true)
{
rd.Refresh();
}
CrystalReportViewer1.ReportSource = rd;
CrystalReportViewer1.DataBind();
}
catch (Exception ex)
{
Response.Write(report_file + ex.Message);
return;
}
t_con.Visible = false;
Button1.Visible = false;
//else
//{
// cs = (CrystalReportSource) CrystalReportViewer1.ReportSource;
//}
/*
*
* if (report_conditon == "1")
* {
* set_condition();
* }
* else
* {
* // Response.Write(report_sql);
* t_con.Visible = false;
* Button1.Visible = false;
* //cs.ReportDocument.SetDatabaseLogon(Fun.db.user_id, Fun.db.password, Fun.db.data_source, Fun.db.data_source);
* //cs.ReportDocument.Refresh();
* // CrystalReportViewer1.ReportSource = cs;
* //CrystalReportViewer1.DataBind();
* // cs.DataBind();
* return;
* Oracle db = new Oracle();
* DataTable dt_report = new DataTable();
* db.ExcuteDataTable(dt_report, report_sql, CommandType.Text);
*
* cs.ReportDocument.SetDataSource(dt_report);
* cs.DataBind();
*
* CrystalReportViewer1.ReportSource = cs;
* CrystalReportViewer1.DataBind();
*
*
* }
*
*
*
* }
*
* // CryReport.get_report_condition(CrystalReportViewer1, Server.MapPath("../report/" + report_file), t_con);
*/
}
}
private void GenerateReport(string Title, string From, string To, string group, string way, string strcond)
{
try
{
DL_DBAccess = new DatabaseAccessLayer();
string Query = "";
// Query = "SELECT CR_GIN_NO,CONVERT(VARCHAR,CR_GIN_DATE,106) AS CR_GIN_DATE,CR_P_CODE,P_NAME,CR_NET_AMT,CD_I_CODE,I_NAME,I_UOM_NAME,CD_UOM,CD_PO_CODE,CPOM_PONO,cast(CD_RATE as numeric(20,2)) as Rate,cast(CD_ORIGIONAL_QTY as numeric(10,3)) as OriginalQty,cast(CD_CHALLAN_QTY as numeric(10,3)) as ChallanQty,cast(CD_RECEIVED_QTY as numeric(10,3)) as ReceivedQty,cast(CD_AMOUNT as numeric(20,2)) as Amount FROM CUSTREJECTION_MASTER,CUSTREJECTION_DETAIL,CUSTPO_MASTER,ITEM_UNIT_MASTER,ITEM_MASTER,PARTY_MASTER where CUSTREJECTION_MASTER.CR_P_CODE=P_CODE and CUSTREJECTION_DETAIL.CD_CR_CODE=CR_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=CD_UOM and CPOM_CODE=CD_PO_CODE and I_CODE=CD_I_CODE";
if (way == "Direct")
{
Query = "SELECT DISTINCT ITEM_MASTER.I_CODENO, ITEM_MASTER.I_CODE, ISSUE_MASTER.IM_CODE, ISSUE_MASTER.IM_MATERIAL_REQ, ISSUE_MASTER.IM_NO, CONVERT(varchar, ISSUE_MASTER.IM_DATE, 106) AS IM_DATE, (CASE WHEN IM_TYPE = 1 THEN 'As Per Material Req' ELSE 'Direct' END) AS IM_TYPE ,IM_ISSUEBY,IM_REQBY, ISNULL(MATERIAL_REQUISITION_MASTER.MR_BATCH_NO, '') AS MR_BATCH_NO, ITEM_MASTER.I_NAME, ITEM_UNIT_MASTER.I_UOM_NAME, ISNULL(ITEM_MASTER.I_INV_RATE, 0.00) AS I_CURRENT_BAL, ISSUE_MASTER_DETAIL.IMD_REQ_QTY, ISSUE_MASTER_DETAIL.IMD_ISSUE_QTY, ISSUE_MASTER_DETAIL.IMD_REMARK FROM MATERIAL_REQUISITION_MASTER RIGHT OUTER JOIN ISSUE_MASTER INNER JOIN ISSUE_MASTER_DETAIL INNER JOIN ITEM_MASTER ON ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE AND ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE INNER JOIN ITEM_UNIT_MASTER ON ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE AND ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE ON ISSUE_MASTER.IM_CODE = ISSUE_MASTER_DETAIL.IM_CODE ON MATERIAL_REQUISITION_MASTER.MR_CODE = ISSUE_MASTER.IM_MATERIAL_REQ WHERE " + strcond + " (ISSUE_MASTER.ES_DELETE = 0) AND (ISSUE_MASTER.IM_COMP_ID = " + Session["CompanyCode"] + ") AND (ISSUE_MASTER_DETAIL.ES_DELETE = 0) and IM_TYPE=2 ";
// Query = "SELECT distinct(I_CODENO) as I_CODENO,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM ISSUE_MASTER LEFT OUTER JOIN MATERIAL_REQUISITION_MASTER ON ISSUE_MASTER.IM_MATERIAL_REQ = MATERIAL_REQUISITION_MASTER.MR_CODE,ITEM_MASTER,MATERIAL_REQUISITION_DETAIL,ITEM_UNIT_MASTER,ISSUE_MASTER_DETAIL where ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=1 and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 ";
}
else
{
Query = " SELECT distinct(I_CODENO) as I_CODENO,ITEM_MASTER.I_CODE,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE ,IM_ISSUEBY,IM_REQBY,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,isnull(I_CURRENT_BAL,0.00)as I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM MATERIAL_REQUISITION_MASTER , ISSUE_MASTER INNER JOIN ISSUE_MASTER_DETAIL INNER JOIN ITEM_MASTER ON ISSUE_MASTER_DETAIL.IMD_I_CODE = ITEM_MASTER.I_CODE INNER JOIN ITEM_UNIT_MASTER ON ISSUE_MASTER_DETAIL.IMD_UOM = ITEM_UNIT_MASTER.I_UOM_CODE ON ISSUE_MASTER.IM_CODE = ISSUE_MASTER_DETAIL.IM_CODE where " + strcond + " ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=" + Session["CompanyCode"] + " and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 and MATERIAL_REQUISITION_MASTER.MR_CODE = ISSUE_MASTER.IM_MATERIAL_REQ ";
//Query = "SELECT distinct(I_CODENO) as I_CODENO,ISSUE_MASTER.IM_CODE,IM_MATERIAL_REQ,IM_NO,convert(varchar,IM_DATE,106)as IM_DATE,(case when IM_TYPE=1 then 'As Per Material Req' else 'Direct' end) as IM_TYPE,isnull(MR_BATCH_NO,'') as MR_BATCH_NO ,I_NAME,I_UOM_NAME,I_CURRENT_BAL,IMD_REQ_QTY,IMD_ISSUE_QTY,IMD_REMARK FROM ISSUE_MASTER , MATERIAL_REQUISITION_MASTER,ITEM_MASTER,MATERIAL_REQUISITION_DETAIL,ITEM_UNIT_MASTER,ISSUE_MASTER_DETAIL where ISSUE_MASTER.ES_DELETE=0 and IM_COMP_ID=1 and I_CODE=IMD_I_CODE and ITEM_UNIT_MASTER.I_UOM_CODE=ISSUE_MASTER_DETAIL.IMD_UOM and ISSUE_MASTER_DETAIL.ES_DELETE=0 and ISSUE_MASTER.IM_MATERIAL_REQ = MATERIAL_REQUISITION_MASTER.MR_CODE ";
}
#region MyRegion
/*
#region Detail
* if (way == "Direct")
* {
#region Datewise
* if (group == "Datewise")
* {
*
* if (date1 == "All" && item == "All")
* {
* Query = Query;
*
*
* }
* if (date1 != "All" && item != "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "' ";
* }
* if (date1 != "All" && item == "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'";
*
* }
* if (date1 == "All" && item != "All")
* {
* Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' ";
*
* }
*
* }
#endregion
#region ItemWise
* if (group == "ItemWise")
* {
* if (date1 == "All" && item == "All")
* {
* Query = Query;
*
*
* }
* if (date1 != "All" && item != "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "'";
*
* }
* if (date1 != "All" && item == "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'";
*
* }
* if (date1 == "All" && item != "All")
* {
* Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' ";
*
* }
*
* }
#endregion
*
* }
#endregion
*
#region AsPerReq
* if (way == "AsperReq")
* {
#region Datewise
* if (group == "Datewise")
* {
*
* if (date1 == "All" && item == "All")
* {
* Query = Query;
*
*
* }
* if (date1 != "All" && item != "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "' ";
* }
* if (date1 != "All" && item == "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'";
*
* }
* if (date1 == "All" && item != "All")
* {
* Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' ";
*
* }
* }
#endregion
#region ItemWise
* if (group == "ItemWise")
* {
*
* if (date1 == "All" && item == "All")
* {
* Query = Query;
*
*
* }
* if (date1 != "All" && item != "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "' and ITEM_MASTER.I_CODE='" + i_name + "'";
*
* }
* if (date1 != "All" && item == "All")
* {
* Query = Query + " and IM_DATE between '" + Convert.ToDateTime(From).ToString("yyyy/MM/dd") + "' and '" + Convert.ToDateTime(To).ToString("yyyy/MM/dd") + "'";
*
* }
* if (date1 == "All" && item != "All")
* {
* Query = Query + " and ITEM_MASTER.I_CODE='" + i_name + "' ";
*
* }
* }
#endregion
*
*
* }
#endregion
*
*
*/
#endregion
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt = CommonClasses.Execute(Query);
if (dt.Rows.Count > 0)
{
ReportDocument rptname = null;
rptname = new ReportDocument();
if (group == "Datewise")
{
rptname.Load(Server.MapPath("~/Reports/rptIssueToProductionDateWise.rpt"));
rptname.FileName = Server.MapPath("~/Reports/rptIssueToProductionDateWise.rpt");
rptname.Refresh();
rptname.SetDataSource(dt);
//if (way == "AsPerReg")
//{
// rptname.SetParameterValue("txtType", "1");
//}
//else
//{
// rptname.SetParameterValue("txtType", "0");
//}
rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString());
rptname.SetParameterValue("txtPeriod", "From " + From + " to " + To);
CrystalReportViewer1.ReportSource = rptname;
}
if (group == "ItemWise")
{
rptname.Load(Server.MapPath("~/Reports/rptIssueToProductionItemWise.rpt"));
rptname.FileName = Server.MapPath("~/Reports/rptIssueToProductionItemWise.rpt");
//rptname.Load(Server.MapPath("~/Reports/rptQtnRegDatewise.rpt"));
//rptname.FileName = Server.MapPath("~/Reports/rptQtnRegDatewise.rpt");
rptname.Refresh();
rptname.SetDataSource(dt);
//if (way == "Summary")
//{
// rptname.SetParameterValue("txtType", "1");
//}
//else
//{
// rptname.SetParameterValue("txtType", "0");
//}
rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString());
rptname.SetParameterValue("txtPeriod", "From " + From + " to " + To);
CrystalReportViewer1.ReportSource = rptname;
}
}
else
{
PanelMsg.Visible = true;
lblmsg.Text = "Record Not Found";
return;
}
}
catch (Exception Ex)
{
}
}
public ActionResult Print(int Id)
{
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "CustomerInvoice.rpt"));
DataSet ds = new DataSet();
ds.Tables.Add("Head");
ds.Tables.Add("Items");
// //-------HEAD
ds.Tables["Head"].Columns.Add("DocumentNo");
ds.Tables["Head"].Columns.Add("DocumentDate");
ds.Tables["Head"].Columns.Add("Location");
ds.Tables["Head"].Columns.Add("InsuredName");
ds.Tables["Head"].Columns.Add("SpecialRemarks");
//-------DT
ds.Tables["Items"].Columns.Add("EffectiveDate");
ds.Tables["Items"].Columns.Add("InsuranceType");
ds.Tables["Items"].Columns.Add("PolicyOrEndorseNo");
ds.Tables["Items"].Columns.Add("Company");
ds.Tables["Items"].Columns.Add("Premium");
ds.Tables["Items"].Columns.Add("Remarks");
CustomerInvoiceRepository repo = new CustomerInvoiceRepository();
var Head = repo.GetCustomerInvoiceHdDetails(Id);
DataRow dr = ds.Tables["Head"].NewRow();
dr["DocumentNo"] = Head.CusInvoiceRefNo;
dr["DocumentDate"] = Head.CusInvoiceDate.Value.ToString("dd-MMM-yyyy");
dr["InsuredName"] = Head.CusName;
dr["SpecialRemarks"] = Head.SpecialRemarks;
ds.Tables["Head"].Rows.Add(dr);
var Items = repo.GetCustomerInvoicePrint(Id);
foreach (var item in Items)
{
var CustomerInvoiceItem = new CustomerInvoiceItem
{
EffectiveDate = item.EffectiveDate,
TranType = item.TranType,
PolicyNo = item.PolicyNo,
InsCmpName = item.InsCmpName,
TotalPremium = item.TotalPremium,
Remarks = item.Remarks
};
DataRow dri = ds.Tables["Items"].NewRow();
dri["EffectiveDate"] = CustomerInvoiceItem.EffectiveDate.Value.ToString("dd-MMM-yyyy");
dri["InsuranceType"] = CustomerInvoiceItem.TranType;
dri["PolicyOrEndorseNo"] = CustomerInvoiceItem.PolicyNo;
dri["Company"] = CustomerInvoiceItem.InsCmpName;
dri["Premium"] = CustomerInvoiceItem.TotalPremium;
dri["Remarks"] = CustomerInvoiceItem.Remarks;
ds.Tables["Items"].Rows.Add(dri);
}
ds.WriteXml(Path.Combine(Server.MapPath("~/XML"), "CustomerInvoice.xml"), XmlWriteMode.WriteSchema);
rd.SetDataSource(ds);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/pdf"));
}
catch (Exception ex)
{
throw;
}
}
public void Print(bool Quick, string printerName)
{
string path = "";// =SystemReports.GetPathReport(StrCode);
try
{
if (Crpt == null && !string.IsNullOrEmpty(StrCode))
{
ReportDocument crptDocument = new ReportDocument();
crptDocument.Load(path);
Crpt = crptDocument; //GetCrptFromAssembly(StrCode);
// Crpt = GetCrptFromAssembly(StrCode);
}
if (ValidData())
{
bool hasSubReport = CheckHasSubReport(Crpt);
var oForm = new frmPrintPreview("Phiếu KQ Xét nghiệm", Crpt, true, ReportSourceTable.Rows.Count <= 0 ? false : true);
oForm.crptTrinhKyName = Path.GetFileName(path);
if (!hasSubReport)
{
Crpt.SetDataSource(ReportSourceTable);
SetReportParameter(Crpt);
// var oForm = new frmPrintPreview(FormPreviewTitle, Crpt, true, true);
if (SysPara.ParamaterReport != null)
{
if (SysPara.ParamaterReport == "THANHNHAN")
{
var patientId = Utility.Int32Dbnull(ReportSourceTable.Rows[0]["Patient_ID"]);
var dtAssignDoctor =
new Select(TTestInfo.Columns.AssignId, LUser.Columns.UserName).From(TTestInfo.Schema.Name).InnerJoin
(LUser.UserIdColumn, TTestInfo.AssignIdColumn).Where(TTestInfo.Columns.PatientId).IsEqualTo(patientId).And(TTestInfo.Columns.TestTypeId).In(testTypeList).ExecuteDataSet().Tables[0];
if (dtAssignDoctor.Rows.Count > 0)
{
Crpt.SetParameterValue("sBacSyChiDinh", "BÁC SỸ CHỈ ĐỊNH");
Crpt.SetParameterValue("sTenBacSyChiDinh", Utility.sDbnull(dtAssignDoctor.Rows[0]["User_Name"]));
}
//else
//{
// Crpt.SetParameterValue("sBacSyChiDinh", "");
// Crpt.SetParameterValue("sTenBacSyChiDinh", "");
//}
var sFullName =
new Select(SysUser.Columns.SFullName).From(SysUser.Schema.Name).Where(SysUser.Columns.PkSuid)
.IsEqualTo(globalVariables.UserName).ExecuteScalar();
if (!string.IsNullOrEmpty(Utility.sDbnull(sFullName)))
{
Crpt.SetParameterValue("sTenUser", sFullName);
}
//else
//{
// Crpt.SetParameterValue("sTenUser","");
//}
}
}
oForm.crptViewer.ReportSource = Crpt;
// FieldObject mv_oRptFieldObj = Crpt.ReportDefinition.ReportObjects["Field150181"] as FieldObject;
// Crpt.PrintToPrinter();
if (Quick)
{
// mv_oNguoiKy = new VietBaIT.LABLink.Reports.Class.cls_SignInfor(mv_oRptFieldObj, "", Crpt.ToString(), Crpt.DataDefinition.FormulaFields["Formula_1"].Text);
// mv_oNguoiKy = new cls_SignInfor(Crpt.ToString(), "", ReportSourceTable);
// mv_oNguoiKy = newcls_SignInfor(string.IsNullOrEmpty(oForm.crptTrinhKyName) , Crpt.ToString(),oForm.crptTrinhKyName, "", "");
mv_oNguoiKy = new cls_SignInfor_new(string.IsNullOrEmpty(oForm.crptTrinhKyName) ? oForm.crptTrinhKyName : oForm.crptTrinhKyName, "");
// mv_oNguoiKy = newcls_SignInfor(" ", "", Crpt, ReportSourceTable);
//chkPrint_CheckedChanged(chkPrint, New System.EventArgs)
if (mv_oNguoiKy._TonTai)
{
Crpt.DataDefinition.FormulaFields["Formula_1"].Text = Strings.Chr(34) +
mv_oNguoiKy.mv_NOI_DUNG.Replace(
"&NHANVIEN",
globalVariables.UserName)
.Replace("\t",
Strings.Chr(34) +
"&Chr(13)&" +
Strings.Chr(34)) +
Strings.Chr(34);
}
else
{
Crpt.DataDefinition.FormulaFields["Formula_1"].Text = "";
}
Crpt.PrintOptions.PrinterName = printerName;
Crpt.PrintToPrinter(0, true, 0, 0);
Crpt.Dispose();
CleanTemporaryFolders();
}
else
{
oForm.ShowDialog();
oForm.Dispose();
}
}
else
{
//DataTable dt = new Select().From(TblTrinhky.Schema.Name).Where(TblTrinhky.Columns.ReportName).
// IsEqualTo(Crpt.GetClassName()).ExecuteDataSet().Tables[0];
string sql = string.Format("SELECT * \n"
+ "FROM Sys_Trinhky With (NOLOCK) \n"
+ "WHERE ReportName = N'{0}'", oForm.crptTrinhKyName);
var x = new InlineQuery().ExecuteAsCollection <SysTrinhkyCollection>(sql);
if (x.Count > 0)
{
Utility.GetDataRow(ReportParameterTable, colName, "Formula_1")[colValue] = Strings.Chr(34) +
Utility.sDbnull(
x[0]
.ObjectContent) +
Strings.Chr(34);
}
Crpt.SetDataSource(ReportSourceTable);
SetReportParameter(Crpt);
if (!ReportSourceTable.Columns.Contains("ParentBranchName"))
{
ReportSourceTable.Columns.Add("ParentBranchName");
}
if (!ReportSourceTable.Columns.Contains("BranchName"))
{
ReportSourceTable.Columns.Add("BranchName");
}
DataTable dtHematology = ReportSourceTable.Clone();
DataTable dtOther = ReportSourceTable.Clone();
DataTable dtImages = ReportSourceTable.Clone();
string sql2 = string.Format("SELECT Sys_Users.sFullName \n"
+ "FROM Sys_Users WITH (NOLOCK) \n"
+ "WHERE Sys_Users.PK_sUID = '{0}'", globalVariables.UserName);
var objuserName = new InlineQuery().ExecuteScalar <string>(sql2);
string sNguoiDung = objuserName == null ? "" : objuserName;
foreach (DataRow row in ReportSourceTable.Rows)
{
row["ParentBranchName"] = globalVariables.ParentBranch_Name;
row["BranchName"] = globalVariables.Branch_Name;
row["Nguoidung"] = sNguoiDung;
if (row["isHematology"].ToString() == "1")
{
if (row["Data_Sequence"].ToString().Contains("-"))
{
string pvSImgPath = row["Test_Result"].ToString();
if (File.Exists(pvSImgPath))
{
//row["BarcodeImg"] = Utility.bytGetImage(pvSImgPath);
var bitmap = new Bitmap(pvSImgPath);
Invert(ref bitmap);
row["BarcodeImg"] = Utility.ConvertImageToByteArray(bitmap, ImageFormat.Tiff);
dtImages.ImportRow(row);
}
}
else
{
dtHematology.ImportRow(row);
}
}
else
{
dtOther.ImportRow(row);
}
}
// đẹt me lam rieng cho noi tiet to su bo no(.)
// cho cai kho A5
if (StrCode == "A5")
{
Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt"
].SetDataSource(dtImages);
Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]
.SetDataSource(dtHematology);
Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]
.SetDataSource(dtOther);
SetReportParameter(
Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]);
SetReportParameter(
Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]);
//Crpt.SetParameterValue("PrintDate", printDateTime, Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"].Name);
//Crpt.SetParameterValue("PrintDate", printDateTime, Crpt.Subreports["NoiTiet_A5_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"].Name);
SetReportParameter(Crpt);
}
// cho cai kho A4
else if (StrCode == "A4")
{
SetReportParameter(
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]);
SetReportParameter(
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt"
].SetDataSource(dtImages);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]
.SetDataSource(dtHematology);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]
.SetDataSource(dtOther);
SetReportParameter(Crpt);
// Crpt.SetParameterValue("Nguoidung", sNguoiDung);
}
//neu deo khai bao A4 hay A5 thi mac dinh lay cai nay nhung pai dung cai bao cao ko an cut no chay dc.
else if (StrCode == "LABREPORT")
{
SetReportParameter(
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]);
SetReportParameter(
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology_Images.rpt"
].SetDataSource(dtImages);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Hematology.rpt"]
.SetDataSource(dtHematology);
Crpt.Subreports["NoiTiet_A4_General_crpt_DetailTestReport_ALL_New_Sub_Other.rpt"]
.SetDataSource(dtOther);
SetReportParameter(Crpt);
}
// if (Quick) Crpt.PrintToPrinter(0, false, 0, 0);
if (Quick)
{
// mv_oNguoiKy = new VietBaIT.LABLink.Reports.Class.cls_SignInfor(mv_oRptFieldObj, "", Crpt.ToString(), Crpt.DataDefinition.FormulaFields["Formula_1"].Text);
// mv_oNguoiKy = new cls_SignInfor(Crpt.ToString(), "", ReportSourceTable);
// mv_oNguoiKy = newcls_SignInfor(string.IsNullOrEmpty(oForm.crptTrinhKyName) , Crpt.ToString(),oForm.crptTrinhKyName, "", "");
mv_oNguoiKy =
new cls_SignInfor_new(
string.IsNullOrEmpty(oForm.crptTrinhKyName)
? oForm.crptTrinhKyName
: oForm.crptTrinhKyName, "");
// mv_oNguoiKy = newcls_SignInfor(" ", "", Crpt, ReportSourceTable);
//chkPrint_CheckedChanged(chkPrint, New System.EventArgs)
if (mv_oNguoiKy._TonTai)
{
Crpt.DataDefinition.FormulaFields["Formula_1"].Text = Strings.Chr(34) +
mv_oNguoiKy.mv_NOI_DUNG.Replace(
"&NHANVIEN",
globalVariables.UserName)
.Replace("\t",
Strings.Chr(34) +
"&Chr(13)&" +
Strings.Chr(34)) +
Strings.Chr(34);
}
else
{
Crpt.DataDefinition.FormulaFields["Formula_1"].Text = "";
}
Crpt.PrintOptions.PrinterName = printerName;
Crpt.PrintToPrinter(0, true, 0, 0);
Crpt.Dispose();
CleanTemporaryFolders();
}
else
{
// var oForm = newfrmPrintPreview("Phiếu KQ Xét nghiệm", Crpt, true, ReportSourceTable.Rows.Count <= 0 ? false : true);
// oForm.crptTrinhKyName = Path.GetFileName(path);
oForm.ShowDialog();
oForm.Dispose();
}
}
}
}
catch (Exception ex)
{
Utility.ShowMsg(ex.Message);
}
}
void DoReport()
{
try
{
CrystalDecisions.CrystalReports.Engine.ReportDocument oRpt = new ReportDocument();
//c_strRptFileName = "";
//if (MemoryData.c_dic_report.ContainsKey(gv_strRptName.ToUpper()) == true)
//{
// // lấy tên file báo cáo
// c_strRptFileName = MemoryData.c_dic_report[gv_strRptName.ToUpper()].Rpt_File_Name;
//}
//else return;
c_strRptFileName = "TM_PLB01SDD.rpt";
// lấy thông tin về đơn chung
Get_AppHeaderbyId();
Create_Each_Report(gv_strRptName);
string mv_strLocation = Server.MapPath("~/Report/") + c_strRptFileName;
if (System.IO.File.Exists(mv_strLocation) == false)
{
ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", "alert(\"Không tồn tại File báo cáo!\");", true);
return;
}
// load rpt
oRpt.Load(mv_strLocation);
if (mv_dsData != null)
{
oRpt.SetDataSource(mv_dsData);
}
// tạo mới báo cáo .
oRpt.Refresh();
// Truyền các tham số Formular vào báo cáo
Puth_Data2_Fomulator(ref oRpt);
DiskFileDestinationOptions crDiskFileDestinationOptions = new DiskFileDestinationOptions();
ExportOptions crExportOptions = oRpt.ExportOptions;
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
crExportOptions.FormatOptions = new PdfFormatOptions();
// xoay dọc hay ngang
if (cboOrientation.SelectedValue.ToString() == "1")
{
oRpt.PrintOptions.PaperOrientation = PaperOrientation.Portrait;
}
else
{
oRpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
}
// Kết xuất không dùng chữ ký
string _FileName = gv_strRptName + "_" + DateTime.Now.ToString("yyyyMMdd");
Export_PDF_Non_Signal(oRpt, _FileName);
//// View báo cáo
//rptViewer.ReportSource = oRpt;
//rptViewer.ShowFirstPage();
//// phần này lâu rồi ko nhớ để làm gì
//if (SessionData.GetDataSession("oRpt") == null)
//{
// Hashtable _hs_opt_khoitao = new Hashtable();
// SessionData.SetDataSession("oRpt", _hs_opt_khoitao);
//}
//Hashtable _hs_opt = (Hashtable)SessionData.GetDataSession("oRpt");
//Hashtable _hs_tem = _hs_opt;
//_hs_tem[gv_strRptName] = oRpt;
//SessionData.SetDataSession("oRpt", _hs_tem);
}
catch (CrystalDecisions.CrystalReports.Engine.LogOnException ex1)
{
rptViewer.Dispose();
Logger.Log().Error(ex1.ToString());
}
catch (Exception ex)
{
rptViewer.Dispose();
Logger.LogException(ex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Session["ReportData"] != null && Session["type"] != null)
{
string rptFullPath = "";
if (Session["type"].ToString() == "bridge")
{
rptFullPath = Server.MapPath(@".\RptFiles\Descriptive\rptBridges.rpt");
}
else if (Session["type"].ToString() == "tunnel")
{
rptFullPath = Server.MapPath(@".\RptFiles\Descriptive\rptTunnels.rpt");
}
DataTable dt = (DataTable)Session["ReportData"];
ReportDocument rpt = new ReportDocument();
rpt.Load(rptFullPath);
rpt.SetDataSource(dt);
Session.Remove("ReportData");
Stream memStream;
Response.Buffer = false;
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
if (Request.QueryString["type"] == "x")
{
ExcelFormatOptions excelOptions = new ExcelFormatOptions();
excelOptions.ExcelUseConstantColumnWidth = false;
rpt.ExportOptions.FormatOptions = excelOptions;
memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord);
Response.ContentType = "application/vnd.ms-excel";
}
else if (Request.QueryString["type"] == "w")
{
memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
Response.ContentType = "application/doc";
}
else
{
memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.ContentType = "application/pdf";
//memStream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
//Response.ContentType = "application/msword";
}
byte[] ArryStream = new byte[memStream.Length + 1];
memStream.Read(ArryStream, 0, System.Convert.ToInt32(memStream.Length));
Response.BinaryWrite(ArryStream);
Response.End();
memStream.Flush();
memStream.Close();
memStream.Dispose();
rpt.Close();
rpt.Dispose();
GC.Collect();
}
else
{
Response.Redirect("ReportSectionsInfo.aspx", false);
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
Session["ReportData"] = null;
}
}
private void btnSelect_Click(object sender, EventArgs e)
{
//MessageBox.Show("shuangji");
if (this.txtYear.Text.Trim() == "" || this.txtMonth.Text.Trim() == "" || this.txtFcurnumber.Text.Trim() == "")
{
MessageBox.Show("请输入查询年份和期间以及客户代码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
ds = k3db.GetDataSet("SELECT fitemid FROM t_Organization WHERE FNumber='" + this.txtFcurnumber.Text.Trim() + "'", "cur");
if (ds.Tables[0].Rows.Count != 1)
{
MessageBox.Show("请输入准确并且存在的客户代码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string strReportPath = Application.StartupPath;
// Application.StartupPath.Substring(0, Application.StartupPath.Substring(0,
// Application.StartupPath.LastIndexOf("\\")).LastIndexOf("\\"));
//strReportPath += @"\RP\RPT\" + strReportFileName;
strReportPath += @"\RPT\dzpyxtj.rpt";
//得到dt数据源
SqlParameter param1 = new SqlParameter("@year", SqlDbType.VarChar);
param1.Value = this.txtYear.Text.Trim();
SqlParameter param2 = new SqlParameter("@month", SqlDbType.VarChar);
param2.Value = this.txtMonth.Text.Trim();
SqlParameter param3 = new SqlParameter("@fcurnumber", SqlDbType.VarChar);
param3.Value = this.txtFcurnumber.Text.Trim();
//创建泛型
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(param1);
parameters.Add(param2);
parameters.Add(param3);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
DataTable dt = k3db.GetDataTable("sp_DzpYsTJ_one", inputParameters);
//ReportDocument对象加载rpt文件并绑定到数据源dt
//明细
ReportDocument reportDoc = new ReportDocument();
reportDoc.Load(strReportPath);
reportDoc.SetDataSource(dt.DefaultView); //DataView是接口IEnumerable的实现子类,此处使用了“接口”的多态特性
// crystalReportViewer1.ReportSource = reportDoc;
//对账单
string strReportPath2 = Application.StartupPath;
strReportPath2 += @"\RPT\dzpysdzd.rpt";
//dzpysdzd.rpt
//SELECT '林水凉' AS 客户名称, 'AAAA' 片区 '2014-9' AS 期间,'2014-09-30 '截止日期,-4037.35 期初余额,0 本期借方,10000 本期贷方,-6549.3 期末余额
string Sqlstr = " select '" + dt.Rows[0]["客户名称"].ToString() + "' AS 客户名称, '" +
dt.Rows[0]["片区"].ToString() + "' 片区,'" +
dt.Rows[0]["年份"].ToString() + "-" + dt.Rows[0]["期间"].ToString() + "' AS 期间,'" +
dt.Rows[0]["年份"].ToString() + "-" + dt.Rows[0]["期间"].ToString() + "-" + dt.Rows[dt.Rows.Count - 2]["日期"].ToString() + "' as 截止日期," +
dt.Rows[dt.Rows.Count - 1]["期初余额"].ToString() + " as 期初余额," +
dt.Rows[dt.Rows.Count - 1]["出货"].ToString() + "-(" + dt.Rows[dt.Rows.Count - 1]["退货"].ToString() + ") as 本期借方," +
dt.Rows[dt.Rows.Count - 1]["回款"].ToString().Trim() + " as 本期贷方," +
dt.Rows[dt.Rows.Count - 1]["期末余额"].ToString() + " as 期末余额 ";
// " 0 as 期末余额 ";
DataTable dtdzd = k3db.GetDataTable(Sqlstr, "tab");
ReportDocument reportDoc2 = new ReportDocument();
reportDoc2.Load(strReportPath2);
reportDoc2.SetDataSource(dtdzd.DefaultView); //DataView是接口IEnumerable的实现子类,此处使用了“接口”的多态特性
crystalReportViewer2.ReportSource = reportDoc2;
}
public ActionResult CRInvoice(int id)
{
//Lijsten maken en vullen
List <SingleInvoiceCRViewModel> CRSingleInvoiceListVM = new List <SingleInvoiceCRViewModel>();
SingleInvoiceCRViewModel CRSO = new SingleInvoiceCRViewModel();
Invoice invoice = db.Invoices.Find(id);
Company company = db.Companies.Find(1);
Customer cus = db.Customers.Find(invoice.CustomerId);
//vulling Header info
//company
CRSO.BIC = company.BIC;
CRSO.CompanyId = company.CompanyId;
CRSO.CompanyName = company.CompanyName;
CRSO.Country = company.Country;
CRSO.District = company.District;
CRSO.EmailCompany = company.Email;
CRSO.Iban = company.Iban;
CRSO.Mobile = company.Mobile;
CRSO.Phone = company.Phone;
CRSO.Postalcode = company.Postalcode;
CRSO.Street = company.Street;
CRSO.VatNumber = company.VatNumber;
CRSO.Website = company.Website;
//quotation
CRSO.Annotation = invoice.Annotation;
CRSO.Box = invoice.Box;
CRSO.CellPhone = invoice.CellPhone;
CRSO.CustomerId = invoice.CustomerId;
CRSO.Date = invoice.Date;
CRSO.EmailCustomer = invoice.Email;
CRSO.FirstName = invoice.FirstName;
CRSO.LastName = invoice.LastName;
CRSO.PostalCodeNumber = invoice.PostalCodeNumber;
CRSO.InvoiceID = invoice.InvoiceId;
CRSO.InvoiceNumber = invoice.InvoiceNumber;
CRSO.StreetName = invoice.StreetName;
CRSO.StreetNumber = invoice.StreetNumber;
CRSO.TotalPrice = invoice.TotalPrice;
CRSO.Town = invoice.Town;
CRSO.VATnumberCustomer = cus.VATNumber;
//customer contact info
CRSO.ContactCellPhone = cus.ContactCellPhone;
CRSO.ContactEmail = cus.ContactEmail;
CRSO.ContactName = cus.ContactName;
//delivery info
CRSO.DeliveryAddressInfo = invoice.customerDeliveryAddress.DeliveryAddressInfo;
CRSO.PostalCodeNumberTown = invoice.customerDeliveryAddress.PostalCodeNumber + " " + invoice.customerDeliveryAddress.Town;
CRSO.StreetNameNumberBox = invoice.customerDeliveryAddress.StreetName + " " + invoice.customerDeliveryAddress.StreetNumber + " " + invoice.customerDeliveryAddress.Box;
//vulling details info
foreach (var item in invoice.InvoiceDetail)
{
CRSO.ProductCode = item.ProductCode;
CRSO.PriceExVAT = item.PriceExVAT;
CRSO.Quantity = item.Quantity;
CRSO.Description = item.Description;
CRSO.VATValue = Convert.ToInt16(item.VAT.VATValue);
CRSO.Auvibel = item.Auvibel;
CRSO.Recupel = item.Recupel;
CRSO.Reprobel = item.Reprobel;
CRSO.Bebat = item.Bebat;
CRSO.ProductCode = item.ProductCode;
CRSO.ProductName = item.ProductName;
CRSingleInvoiceListVM.Add(CRSO);
SingleInvoiceCRViewModel empty = new SingleInvoiceCRViewModel();
CRSO = empty;
}
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports/Invoice"), "SingleInvoice.rpt"));
rd.SetDataSource(CRSingleInvoiceListVM);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
string pdfName = "Factuur - " + invoice.InvoiceNumber + " - " + invoice.FullName.ToString() + ".pdf";
return(File(stream, "application/pdf", pdfName));
}
catch (Exception ex)
{
if (ex.Data == null)
{
throw;
}
else
{
throw;
}
}
}
public void LoadReport()
{
System.IO.MemoryStream stream1 = new System.IO.MemoryStream();
Processing p = new Processing();
try
{
if (Request.QueryString["Type"] == "S")
{
string Department = Request.QueryString["Department"].ToString();
string Semester = Request.QueryString["Semester"].ToString();
string Year = Request.QueryString["Year"].ToString();
string ItemType = Request.QueryString["ItemType"].ToString();
string Path = Server.MapPath("CalenderAuditSummary.rpt");
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path + "\\Reports\\CalenderAuditSummary.rpt";
myReportDocument.Load(Path);
//myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents");
myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea");
myReportDocument.SetParameterValue("@department", Department);
myReportDocument.SetParameterValue("@semester", Semester);
myReportDocument.SetParameterValue("@year", Year);
myReportDocument.SetParameterValue("@ItemType", ItemType);
}
else if (Request.QueryString["Type"] == "C")
{
string Department = Request.QueryString["Department"].ToString();
string Semester = Request.QueryString["Semester"].ToString();
string Year = Request.QueryString["Year"].ToString();
string ItemType = Request.QueryString["ItemType"].ToString();
string Path = Server.MapPath("CalenderauditCummulative.rpt");
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path + "\\Reports\\CalenderauditCummulative.rpt";
myReportDocument.Load(Path);
//myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents");
myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea");
myReportDocument.SetParameterValue("@department", Department);
myReportDocument.SetParameterValue("@semester", Semester);
myReportDocument.SetParameterValue("@year", Year);
myReportDocument.SetParameterValue("@ItemType", ItemType);
}
else
{
string Department = Request.QueryString["Department"].ToString();
string Semester = Request.QueryString["Semester"].ToString();
string Year = Request.QueryString["Year"].ToString();
string ItemType = Request.QueryString["ItemType"].ToString();
string Path = Server.MapPath("calendarFeedback.rpt");
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path.Substring(0, Path.LastIndexOf('\\'));
Path = Path + "\\Reports\\calendarFeedback.rpt";
myReportDocument.Load(Path);
//myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea", "192.168.167.207", "DB_SkylineCalendarEvents");
myReportDocument.SetDatabaseLogon("software", "DelFirMENA$idea");
myReportDocument.SetParameterValue("@department", Department);
myReportDocument.SetParameterValue("@semester", Semester);
myReportDocument.SetParameterValue("@year", Year);
myReportDocument.SetParameterValue("@ItemType", ItemType);
}
System.IO.Stream oStream = null;
byte[] byteArray = null;
oStream = myReportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
byteArray = new byte[oStream.Length];
oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1));
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
Response.BinaryWrite(byteArray);
Response.Flush();
Response.Close();
myReportDocument.Close();
myReportDocument.Dispose();
}
catch (Exception Ex)
{
//LogFile L = new LogFile();
//L.LogError("CalendarAuditSummaryPrintReport.aspx");
//L.LogError(Ex.Message.ToString());
//L.LogError(Session["User"].ToString());
//Response.Write(Ex.Message);
}
}
public ActionResult CRAllQuotations(int?id)
{
List <Quotation> allQuotations = new List <Quotation>();
allQuotations = db.Quotations.ToList();
List <Company> company = new List <Company>();
company = db.Companies.ToList();
List <AllQuotationsCRViewModel> allQuotationsCR = new List <AllQuotationsCRViewModel>();
foreach (var item in allQuotations)
{
var allQuotationCR = new AllQuotationsCRViewModel();
allQuotationCR.QuotationId = item.QuotationId;
allQuotationCR.QuotationNumber = item.QuotationNumber;
allQuotationCR.Active = item.Active;
allQuotationCR.ExpirationDate = item.ExpirationDate;
allQuotationCR.Date = item.Date;
allQuotationCR.CustomerId = item.CustomerId;
allQuotationCR.Annotation = item.Annotation;
allQuotationCR.FirstName = item.FirstName;
allQuotationCR.LastName = item.LastName;
allQuotationCR.StreetName = item.StreetName;
allQuotationCR.StreetNumber = item.StreetNumber;
allQuotationCR.Town = item.Town;
allQuotationCR.PostalCodeNumber = item.PostalCodeNumber;
allQuotationCR.Box = item.Box;
allQuotationCR.TotalPrice = item.TotalPrice;
allQuotationsCR.Add(allQuotationCR);
}
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "AllQuotationsMain.rpt"));
rd.OpenSubreport("Header.rpt").SetDataSource(company);
rd.OpenSubreport("allQuotationsSub.rpt").SetDataSource(allQuotationsCR);
//rd.SetDataSource(company);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/pdf", "All_Quotations.pdf"));
}
catch (Exception ex)
{
if (ex.Data == null)
{
throw;
}
else
{
throw;
}
}
}
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
try
{
int int_pinicio;
int int_pfin;
string strServer;
string strDataBase;
string strUid;
string strPwd;
string pf = "ctl00$CPHC$";
// pf = "";
// obtengo de la cadena de conexión los parámetros para luego
// modificar localizaciones
string strconexion = Utilidades.CadenaConexion;
int_pfin = strconexion.IndexOf(";database=", 0);
strServer = strconexion.Substring(7, int_pfin - 7);
int_pinicio = int_pfin + 10;
int_pfin = strconexion.IndexOf(";uid=", int_pinicio);
strDataBase = strconexion.Substring(int_pinicio, int_pfin - int_pinicio);
int_pinicio = int_pfin + 5;
int_pfin = strconexion.IndexOf(";pwd=", int_pinicio);
strUid = strconexion.Substring(int_pinicio, int_pfin - int_pinicio);
int_pinicio = int_pfin + 5;
int_pfin = strconexion.IndexOf(";Trusted_Connection=", int_pinicio);
strPwd = strconexion.Substring(int_pinicio, int_pfin - int_pinicio);
//creo un objeto ReportDocument
ReportDocument rdInforme = new ReportDocument();
try
{
rdInforme.Load(Server.MapPath(".") + @"\sup_parteactividad_mod_b.rpt");
}
catch (Exception ex)
{
rdInforme.Close();
rdInforme.Dispose();
Response.Write("Error al abrir el report: " + ex.Message);
}
try
{
rdInforme.SetDatabaseLogon(strUid, strPwd, strServer, strDataBase);
}
catch (Exception ex)
{
rdInforme.Close();
rdInforme.Dispose();
Response.Write("Error al logarse al report: " + ex.Message);
}
//creo un objeto logon .
CrystalDecisions.Shared.TableLogOnInfo tliCurrent;
try
{
foreach (CrystalDecisions.CrystalReports.Engine.Table tbCurrent in rdInforme.Database.Tables)
{
//obtengo el logon por tabla
tliCurrent = tbCurrent.LogOnInfo;
tliCurrent.ConnectionInfo.DatabaseName = strDataBase;
tliCurrent.ConnectionInfo.UserID = strUid;
tliCurrent.ConnectionInfo.Password = strPwd;
tliCurrent.ConnectionInfo.ServerName = strServer;
//aplico los cambios hechos al objeto TableLogonInfo
tbCurrent.ApplyLogOnInfo(tliCurrent);
}
}
catch (Exception ex)
{
rdInforme.Close();
rdInforme.Dispose();
Response.Write("Error al actualizar la localización: " + ex.Message);
}
bool bReducida = false;
string strTipoFormato = "";
if (Request.Form["FORMATO"] != null)//Por si se llama desde la versión reducida (sin Máster)
{
bReducida = true;
strTipoFormato = Request.Form["FORMATO"];
}
else
{
strTipoFormato = Request.Form[pf + "FORMATO"]; // ESTO LO RECOGERE COMO PARAMETRO
}
strControl = strTipoFormato;
DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();
ExportOptions exportOpts = new ExportOptions();
try
{
if (!bReducida)
{
rdInforme.SetParameterValue("@sConsumos", Request.Form[pf + "hdnConsumos"]);
}
else
{
rdInforme.SetParameterValue("@sConsumos", Request.Form["hdnConsumos"]);
}
}
catch (Exception ex)
{
rdInforme.Close();
rdInforme.Dispose();
Response.Write("Error al actualizar los parámetros del report: " + ex.Message);
}
try
{
System.IO.Stream oStream;
byte[] byteArray = null;
//switch (strTipoFormato)
//{
// // PDF
// case "PDF":
oStream = rdInforme.ExportToStream(ExportFormatType.PortableDocFormat);
byteArray = new byte[oStream.Length];
oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1));
// break;
// case "EXC":
// oStream = rdInforme.ExportToStream(ExportFormatType.Excel);
// byteArray = new byte[oStream.Length];
// oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1));
// break;
// case "EXC2":
// //
// rdInforme.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Exportacion");
// return;
// break;
//}
// FIN
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
//String nav = HttpContext.Current.Request.Browser.Browser.ToString();
//if (nav.IndexOf("IE") == -1)
//{
switch (strTipoFormato)
{
case "PDF":
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "Filename.pdf"));
break;
case "EXC":
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "Filename.xls"));
break;
}
//}
switch (strTipoFormato)
{
// PDF
case "PDF":
Response.ContentType = "application/pdf";
break;
case "EXC":
// EXCEL
Response.ContentType = "application/xls";
break;
}
Response.BinaryWrite(byteArray);
//Response.Flush();
//Response.Close();
//HttpContext.Current.Response.End();
Response.End();
}
catch (Exception ex)
{
rdInforme.Close();
rdInforme.Dispose();
Response.Write("Error al exportar el report: " + ex.Message);
}
}
//handle any exceptions
catch (Exception ex)
{
Response.Write("No se puede crear el report: " + ex.Message);
}
}
public ActionResult Print(string Itmid = "", string PartNo = "")
{
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "GINRegister.rpt"));
DataSet ds = new DataSet();
ds.Tables.Add("Head");
ds.Tables.Add("Items");
//-------HEAD
ds.Tables["Head"].Columns.Add("Item");
ds.Tables["Head"].Columns.Add("OrganizationName");
ds.Tables["Head"].Columns.Add("Image1");
ds.Tables["Head"].Columns.Add("PartNo");
//-------DT
ds.Tables["Items"].Columns.Add("MaterialCode");
ds.Tables["Items"].Columns.Add("Material");
ds.Tables["Items"].Columns.Add("PartNo");
ds.Tables["Items"].Columns.Add("Stock");
ds.Tables["Items"].Columns.Add("Unit");
ds.Tables["Items"].Columns.Add("WR.No");
ds.Tables["Items"].Columns.Add("RequiredQty");
ds.Tables["Items"].Columns.Add("IssuedQty");
ds.Tables["Items"].Columns.Add("BalanceQty");
OrganizationRepository repo = new OrganizationRepository();
var Head = repo.GetOrganization(OrganizationId);
DataRow dr = ds.Tables["Head"].NewRow();
dr["Item"] = Itmid;
dr["OrganizationName"] = Head.OrganizationName;
dr["Image1"] = Server.MapPath("~/App_images/") + Head.Image1;
dr["PartNo"] = PartNo;
ds.Tables["Head"].Rows.Add(dr);
SalesRegisterRepository repo1 = new SalesRegisterRepository();
//var Items = repo1.GetSOVarianceDataDTPrint(from, to, itmid, itmName, SupId, SupName);
var Items = repo1.GetGINRegisterDataDetailsPrint(id: Itmid, OrganizationId: OrganizationId, partno: PartNo);
foreach (var item in Items)
{
var SupplyOrderRegItem = new GINRegister
{
ItemRefNo = item.ItemRefNo,
ItemName = item.ItemName,
PartNo = item.PartNo,
STOCK = item.STOCK,
UnitName = item.UnitName,
WorkShopRequestRefNo = item.WorkShopRequestRefNo,
Quantity = item.Quantity,
ISSQTY = item.ISSQTY,
BALQTY = item.BALQTY,
};
DataRow dri = ds.Tables["Items"].NewRow();
dri["MaterialCode"] = SupplyOrderRegItem.ItemRefNo;
dri["Material"] = SupplyOrderRegItem.ItemName;
dri["PartNo"] = SupplyOrderRegItem.PartNo;
dri["Stock"] = SupplyOrderRegItem.STOCK;
dri["Unit"] = SupplyOrderRegItem.UnitName;
dri["WR.No"] = SupplyOrderRegItem.WorkShopRequestRefNo;
dri["RequiredQty"] = SupplyOrderRegItem.Quantity;
dri["IssuedQty"] = SupplyOrderRegItem.ISSQTY;
dri["BalanceQty"] = SupplyOrderRegItem.BALQTY;
ds.Tables["Items"].Rows.Add(dri);
}
ds.WriteXml(Path.Combine(Server.MapPath("~/XML"), "GINRegister.xml"), XmlWriteMode.WriteSchema);
rd.SetDataSource(ds);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/pdf", String.Format("GINRegister.pdf")));
}
catch (Exception ex)
{
throw;
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
IMPRESION_REPORTES VEN = new IMPRESION_REPORTES();
ReportParameter[] parametros = new ReportParameter[2];
if (comboBox1.Text.Equals("LIQUIDOS - JARABE"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQJARA.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/LIQJARA.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else if (comboBox1.Text.Equals("LIQUIDOS - AMPOLLA"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQAMPOLLAS.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/LIQAMPOLLAS.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else if (comboBox1.Text.Equals("LIQUIDOS - PRODUCTOS SANITARIOS"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\LIQPROSANIT.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/LIQPROSANIT.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else if (comboBox1.Text.Equals("SOLIDOS - TABLETAS"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLTAB.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/SOLTAB.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else if (comboBox1.Text.Equals("SOLIDOS - OVULOS Y CREMAS"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLOVCREMA.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/SOLOVCREMA.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else if (comboBox1.Text.Equals("SOLIDOS - VIALES"))
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLTVIALES.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/SOLTVIALES.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
else
{
timer1.Start();
VISRO frm = new VISRO(); //formulario con CrystalReportViewer
ReportDocument rpt = new ReportDocument();
//rpt.Load(@"C:\Users\costos\Desktop\SISTEMA DE GESTION ALFARO\ALFARO CAJA\REPORTES\SOLCAP.rpt");
rpt.Load("C:/SI_Gestion/REPORTES/SOLCAP.rpt");
DataSet ds = new DataSet();
DataTable dt_c = new DataTable(); //ResultSet de DB
dt_c = insp.VER_REPORTE_X_INSPECCION(Convert.ToInt32(dataGridView1.CurrentRow.Cells["COD"].Value));
ds.Tables.Add(dt_c);
rpt.Database.Tables[0].SetDataSource(ds.Tables[0]);
rpt.SetParameterValue("CODIGO", dataGridView1.CurrentRow.Cells["COD"].Value.ToString());
rpt.SetParameterValue("FECHA", dataGridView1.CurrentRow.Cells["FECHA"].Value.ToString());
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).ReportSource = rpt;
((CrystalDecisions.Windows.Forms.CrystalReportViewer)frm.Controls["crystalReportViewer1"]).EnableDrillDown = false;
frm.Refresh(); //se refresca
timer1.Stop();
progressBar1.Visible = false;
progressBar1.Value = 0;
//rpt.PrintOptions.PrinterName = "PRIMER_PISO";
//rpt.PrintToPrinter(1, false, 0, 0); //se imprime
frm.Text = "IMPRESION DE FORMATOS DE INSPECCION";
frm.Show();
}
}
private void PrintDeptCodeClickHandler(object sender, EventArgs e)
{
//RptDept rptDeptNameList = new RptDept();
//rptDeptNameList.WindowState = FormWindowState.Maximized;
//rptDeptNameList.rptName = "DeptCode";
//rptDeptNameList.rptLabel = "DEPARTMENTS REFERENCE LIST SORTED BY CODE";
//rptDeptNameList.Show();
string rpt = "";
string strServer = "PSSQL01";
string strDBName = "PTS";
ReportDocument crDoc = new ReportDocument();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
rpt = @"\\gblnj4\GIS\Reports\" + "DepartmentNames.rpt";
//rpt = Application.StartupPath + @"\Reports\" + "DepartmentsRef.rpt";
crConnectionInfo.Type = ConnectionInfoType.SQL;
crConnectionInfo.ServerName = strServer;
crConnectionInfo.DatabaseName = strDBName;
crConnectionInfo.IntegratedSecurity = true;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
crDoc.Load(rpt);
DataTable dt = new DataTable();
if (rptName == "DeptCode")
{
dt = PSSClass.Departments.DepartmentsMaster(1);
}
else
{
dt = PSSClass.Departments.DepartmentsMaster(2);
}
crDoc.Load(rpt);
crDoc.SetDataSource(dt);
////Open the PrintDialog
//this.printDialog1.Document = this.printDocument1;
//DialogResult dr = this.printDialog1.ShowDialog();
//if (dr == DialogResult.OK)
//{
// //Get the Copy times
// int nCopy = this.printDocument1.PrinterSettings.Copies;
// //Get the number of Start Page
// int sPage = this.printDocument1.PrinterSettings.FromPage;
// //Get the number of End Page
// int ePage = this.printDocument1.PrinterSettings.ToPage;
// //Get the printer name
// string PrinterName = this.printDocument1.PrinterSettings.PrinterName;
// try
// {
// //Set the printer name to print the report to. By default the sample
// //report does not have a defult printer specified. This will tell the
// //engine to use the specified printer to print the report. Print out
// //a test page (from Printer properties) to get the correct value.
// crDoc.PrintOptions.PrinterName = PrinterName;
// //Start the printing process. Provide details of the print job
// //using the arguments.
// crDoc.PrintToPrinter(nCopy, false, sPage, ePage);
// }
// catch (Exception err)
// {
// MessageBox.Show(err.ToString());
// }
//}
crDoc.PrintOptions.PrinterName = PSSClass.Users.UserPrinterName(LogIn.nUserID); //@"\\it5\46 IT Brother Printer";// @"\\it5\46 Brother FAX";
crDoc.PrintToPrinter(1, false, 0, 0);
//crDoc.PrintOptions.PrinterName = @"\\it5\46 IT Brother Printer";
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//记录页面使用次数
DB.RecordPageUseCount("Plant_BLOOMCOSTCON");
date1 = "1910-01";
newdate = false;
int CurrentYear = Convert.ToInt32(DateTime.Now.Year);
int i;
for (i = CurrentYear - 5; i <= CurrentYear; i++)
{
ListItem Year = new ListItem();
Year.Text = Convert.ToString(i);
Year.Value = Convert.ToString(i);
if (i == CurrentYear)
{
Year.Selected = true;
}
else
{
Year.Selected = false;
}
ddlYear.Items.Add(Year);
//EndYear.Items.Add(Year);
}
string CurrentDate = Convert.ToString(DateTime.Now.Month);
if (CurrentDate.Length == 1)
{
CurrentDate = "0" + CurrentDate;
}
foreach (ListItem Month in ddlMonth.Items)
{
if (Month.Text == CurrentDate)
{
Month.Selected = true;
ddlMonth.Text = Month.Text;
//EndMonth.Text = Month.Text;
break;
}
else
{
Month.Selected = false;
}
}
AddDate(sender, e);
CurrentDate = Convert.ToString(DateTime.Now.Day);
if (CurrentDate.Length == 1)
{
CurrentDate = "0" + CurrentDate;
}
foreach (ListItem day in ddlDay.Items)
{
if (day.Text == CurrentDate)
{
day.Selected = true;
ddlDay.Text = day.Text;
//EndDay.Text = day.Text;
break;
}
else
{
day.Selected = false;
}
}
}
else
{
date1 = ddlYear.SelectedValue.ToString() + '-' + ddlMonth.SelectedValue.ToString() + '-' + ddlDay.SelectedValue.ToString();
string strCurrentDate = date1;
OracleConnection conn = DB.createConn();
try
{
OracleDataAdapter da = new OracleDataAdapter();
if (newdate)
{
CrystalReportViewer1.ResetReportPartNavigation();
newdate = false;
}
conn.Open();
DataSet ds = new DataSet();
string strSQL = "Select * from TP_BLOOM_COST_CON where LOGTIME='" + strCurrentDate + "'AND RPTID='" + ddlBloomCostcon.SelectedValue + "' ORDER BY mat_order";
da.SelectCommand = new OracleCommand(strSQL, conn);
da.Fill(ds, "TP_BLOOM_COST_CON");
rdt.Load(this.Server.MapPath("").ToString() + "\\BLOOMCOSTCON.rpt");
rdt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = rdt;
CrystalReportViewer1.DataBind();
ds.Dispose();
}
catch (Exception ee)
{
Response.Write(ee.ToString());//"出错");
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// Init_pages this instance.
/// </summary>
private void init_page()
{
ReceiptType defaultType = ReceiptType.BillPayment;
if (Request.QueryString.Count > 0)
{
if (this.Request.QueryString["ReceiptTrxCode"] != null)
{
//Application.Common.Utility objUtil = new Application.Common.Utility();
Session["transactionID"] = this.Request.QueryString["ReceiptTrxCode"];// objUtil.Decrypt(this.Request.QueryString["ReceiptTrxCode"]);
}
if (this.Request.QueryString["TranXORType"] != null)
{
string transOX = this.Request.QueryString["TranXORType"].ToString();
if (transOX == "9")
{
defaultType = ReceiptType.DepositTransaction;
}
else if (transOX == "17")
{
defaultType = ReceiptType.ReversalTransaction;
}
}
}
IBilling BManager = (IBilling)ObjectFactory.CreateInstance("BusinessProcess.SCM.BBilling, BusinessProcess.SCM");
DataSet theDataSet = BManager.GetReceipt(Convert.ToInt32(Session["transactionID"]), Convert.ToInt32(Session["AppLocationId"]), defaultType);
this.rptDocument = new ReportDocument();
DataTable theDT = theDataSet.Tables[1];
if (this.PrintOnThermal)
{
rptDocument.Load(MapPath("..\\Reports\\Billing\\rptBillingRecieptThermal.rpt"));
}
else
{
rptDocument.Load(MapPath("..\\Reports\\Billing\\rptBillingReciept.rpt"));
}
String facilityName = (String)theDT.Rows[0]["FacilityName"];
String dupl;
if (Request.QueryString["reprint"] != null && Request.QueryString["RePrint"] == "true")
{
dupl = "DUPLICATE";
}
else
{
dupl = "";
}
rptDocument.SetDataSource(theDataSet);
rptDocument.SetParameterValue("FacilityName", facilityName);
rptDocument.SetParameterValue("Currency", "KES");
rptDocument.SetParameterValue("DuplicateReceipt", dupl);
if (!this.PrintOnThermal)
{
String facilityLogo = (String)theDT.Rows[0]["FacilityLogo"];
string f = GblIQCare.GetPath() + facilityLogo;
string p = Server.MapPath("~/Images/" + facilityLogo);
rptDocument.SetParameterValue("PicturePath", p);
}
billingRptViewer.EnableParameterPrompt = false;
billingRptViewer.ReportSource = rptDocument;
billingRptViewer.DataBind();
}
private void CreateReport()
{
Tables CrTables;
ConnectionInfo crConnectionInfo = new ConnectionInfo();
TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
crConnectionInfo.Type = ConnectionInfoType.SQL;
crConnectionInfo.ServerName = "172.16.4.12";
crConnectionInfo.DatabaseName = "PTS";
crConnectionInfo.IntegratedSecurity = false;
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "******";
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
SqlConnection sqlcnn = PSSClass.DBConnection.PSSConnection();
if (sqlcnn == null)
{
MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
SqlCommand sqlcmd = new SqlCommand();
SqlDataReader sqldr;
crDoc = new ReportDocument();
if (rptName == "InvYrTotal")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "InvYrTotal.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spInvYrTotRpt", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "LogYrTotal")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "LogYrTotal.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cGBL"].Text = "" + nGBL + "";
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spLogYrTotRpt", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
sqlcmd.Parameters.AddWithValue("@GBL", nGBL);
}
else if (rptName == "RptYrTotal")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "RptYrTotal.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cGBL"].Text = "" + nGBL + "";
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spRptYrTot", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
sqlcmd.Parameters.AddWithValue("@GBL", nGBL);
}
else if (rptName == "NewSponsors")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "NewSponsors.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE YEAR " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spNewCustomers", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "InactiveSponsors")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "InactiveSponsors.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE YEAR " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spInactiveCustomers", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "MgmtGraph")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtSummary.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spMgmtTotRpt", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "MgmtQuoteRpt")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "QuotesByDept.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spQuoteAnaDept", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
sqlcmd.Parameters.AddWithValue("@Sort", 1);
}
else if (rptName == "ProformaRev")
{
string rpt = "";
if (nMgmtRev >= 1 && nMgmtRev <= 4)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "ProformaRev.rpt";
}
else
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "ProformaRevSC.rpt";
}
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'";
if (nMgmtRev == 1)
{
crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'ALL SPONSORS'";
sqlcmd = new SqlCommand("spProformaRevAll", sqlcnn);
}
//else if (nMgmtRev == 2)
//{
// crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'NON-INGREDION SPONSORS'";
// sqlcmd = new SqlCommand("spProformaRev", sqlcnn);
//}
//else if (nMgmtRev == 3)
//{
// crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'INGREDION'";
// sqlcmd = new SqlCommand("spProformaRevIng", sqlcnn);
//}
else if (nMgmtRev == 4)
{
crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'STABILITY'";
sqlcmd = new SqlCommand("spProformaRevSta", sqlcnn);
}
//else if (nMgmtRev == 5)
//{
// crDoc.DataDefinition.FormulaFields["cCategory"].Text = "'STERILIZATION'";
// sqlcmd = new SqlCommand("spProformaRevSter", sqlcnn);
//}
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Year", nYr);
sqlcmd.Parameters.AddWithValue("@SpID", SpID);
sqlcmd.Parameters.AddWithValue("@SC", SC);
sqlcmd.CommandTimeout = 60;
}
else if (rptName == "IngredionRevenue")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "IngredionRevenue.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYear"].Text = "'" + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spIngredionRevRpt", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "IngredionProfDtls")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "IngredionProfDtls.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYear"].Text = "'" + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spIngredionProfDtls", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "MgmtRevenue")
{
string rpt = "";
if (nMgmtRev == 1 || nMgmtRev == 2)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevenue.rpt";
}
else if (nMgmtRev == 3 || nMgmtRev == 4)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSC.rpt";
}
else if (nMgmtRev >= 5 && nMgmtRev <= 8)
{
if (nMgmtRev == 5 || nMgmtRev == 6)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSpSumm.rpt";
}
else
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSpDtls.rpt";
}
}
else if (nMgmtRev >= 9 && nMgmtRev <= 12)
{
if (nMgmtRev == 9 || nMgmtRev == 10)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSCSumm.rpt";
}
else
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "MgmtRevSCDtls.rpt";
}
}
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FY " + nYr.ToString() + "'";
sqlcmd = new SqlCommand("spRevPivotRpt", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Year", nYr);
if (nMgmtRev == 1 || nMgmtRev == 3 || nMgmtRev == 5 || nMgmtRev == 7 || nMgmtRev == 9 || nMgmtRev == 11)
{
sqlcmd.Parameters.AddWithValue("@SpID", 0);
sqlcmd.Parameters.AddWithValue("@SC", 0);
}
else if (nMgmtRev == 2 || nMgmtRev == 4 || nMgmtRev == 6 || nMgmtRev == 8 || nMgmtRev == 10 || nMgmtRev == 12)
{
sqlcmd.Parameters.AddWithValue("@SpID", SpID);
sqlcmd.Parameters.AddWithValue("@SC", SC);
}
}
else if (rptName == "PSSFinancial")
{
sqlcnn.Close(); sqlcmd.Dispose();
sqlcnn = PSSClass.DBConnection.MDFConnection("172.16.4.12", "PTSFinancials", false, "sa", "Pass2018", "");
if (sqlcnn == null)
{
MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
sqlcmd = new SqlCommand();
crConnectionInfo.DatabaseName = "PTSFinancials";
crConnectionInfo.IntegratedSecurity = false;
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "******";
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
this.Text = "FINANCIAL REPORT";
string strDay = "";
decimal nIT = PSSClass.Financials.IncomeTax(nYr);
string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
int nR = nYr % 4;
if (nR == 0)
{
strDay = nLYMonths[nMo - 1].ToString();
}
else
{
strDay = nNLYMonths[nMo - 1].ToString();
}
string rpt = "";
if (nFSFormat == 0)
{
//rpt = Application.StartupPath + @"\Reports\" + "PSSFinancial.rpt";
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial.rpt";
}
else if (nFSFormat == 1)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFSSummary.rpt";
}
else if (nFSFormat == 2)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialNoPer.rpt";
}
else if (nFSFormat == 3)
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialGL.rpt";
}
crDoc.Load(rpt);
CrTables = crDoc.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
{
crtableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE PERIOD ENDED " + strMonths[nMo - 1].ToUpper() + " " + strDay + ", " + nYr.ToString() + "'";
crDoc.DataDefinition.FormulaFields["strMo"].Text = "'" + nMo.ToString() + "'";
crDoc.DataDefinition.FormulaFields["cTaxes"].Text = nIT.ToString();
sqlcmd = new SqlCommand("spLinkFinancial", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName == "PSSFinancial_12Month")
{
sqlcnn.Close(); sqlcmd.Dispose();
sqlcnn = PSSClass.DBConnection.MDFConnection("172.16.4.12", "PTSFinancials", false, "sa", "Pass2018", "");
if (sqlcnn == null)
{
MessageBox.Show("Connection problems encountered." + Environment.NewLine + "Please contact the IT Department.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
sqlcmd = new SqlCommand();
crConnectionInfo.DatabaseName = "PTSFinancials";
crConnectionInfo.IntegratedSecurity = false;
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "******";
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
this.Text = "FINANCIAL REPORT::12 MONTH OVERVIEW";
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial_12Month.rpt";
//string strDay = "";
//decimal nIT = PSSClass.Financials.IncomeTax(nYr);
//string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
//int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
//int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
//int nR = nYr % 4;
//if (nR == 0)
// strDay = nLYMonths[nMo - 1].ToString();
//else
// strDay = nNLYMonths[nMo - 1].ToString();
//string rpt = "";
//if (nFSFormat == 0)
// rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancial_12Month.rpt";
//else if (nFSFormat == 1)
// rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFSSummary.rpt";
//else if (nFSFormat == 2)
// rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialNoPer.rpt";
//else if (nFSFormat == 3)
// rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "PSSFinancialGL.rpt";
crDoc.Load(rpt);
CrTables = crDoc.Database.Tables;
//foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
//{
// crtableLogoninfo = CrTable.LogOnInfo;
// crtableLogoninfo.ConnectionInfo = crConnectionInfo;
// CrTable.ApplyLogOnInfo(crtableLogoninfo);
//}
//crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'FOR THE PERIOD ENDED " + strMonths[nMo - 1].ToUpper() + " " + strDay + ", " + nYr.ToString() + "'";
//crDoc.DataDefinition.FormulaFields["strMo"].Text = "'" + nMo.ToString() + "'";
//crDoc.DataDefinition.FormulaFields["cTaxes"].Text = nIT.ToString();
sqlcmd = new SqlCommand("spLinkFinancial", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
}
else if (rptName.IndexOf("Variance") != -1)
{
this.Text = "VARIANCE REPORT - BUDGET VS. ACTUAL";
string strDay = "";
string rpt = "";
decimal nIT = PSSClass.Financials.IncomeTax(nYr);
string[] strMonths = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
int[] nLYMonths = new int[] { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
int[] nNLYMonths = new int[] { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
int nR = nYr % 4;
if (nR == 0)
{
strDay = nLYMonths[nMo - 1].ToString();
}
else
{
strDay = nNLYMonths[nMo - 1].ToString();
}
if (rptName == "VarianceYTD")
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "Variance.rpt";
}
else if (rptName == "VarianceMonthly")
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "VarianceMonthly.rpt";
}
else
{
rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "VarianceQtrly.rpt";
}
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cMo"].Text = "'" + nMo.ToString() + "'";
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString() + "'";
crDoc.DataDefinition.FormulaFields["cDay"].Text = "'" + strDay + "'";
crDoc.DataDefinition.FormulaFields["cQtr"].Text = "'" + nQtr.ToString() + "'";
}
else if (rptName == "TestsCompleted")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "TestsCompleted.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'";
crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptTestsCompleted", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
sqlcmd.Parameters.AddWithValue("@DeptList", strDept); //nDeptID
}
else if (rptName == "OutstandingTests")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "OutstandingTests.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'";
crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptOutstandingGBLTesting", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
sqlcmd.Parameters.AddWithValue("@DeptList", strDept); //nDeptID
}
else if (rptName == "TestsForCompletion")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "TestsForCompletion.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'";
crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptTestsForCompletion", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
}
else if (rptName == "StabilityReport")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "StabilityReport-New.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'RANGE PERIOD: " + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spStabilityReport", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@SDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EDate", dteEnd);
}
else if (rptName == "StudyDirReport")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "StudyDirRpt.rpt";
crDoc.Load(rpt);
if (dteStart == dteEnd)
{
crDoc.DataDefinition.FormulaFields["strPeriod"].Text = "'FOR THE PERIOD: " + dteStart.ToShortDateString() + "'";
}
else
{
crDoc.DataDefinition.FormulaFields["strPeriod"].Text = "'FOR THE PERIOD: " + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'";
}
sqlcmd = new SqlCommand("spStudyDirReport", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
sqlcmd.Parameters.AddWithValue("@SDID", nSDID);
}
else if (rptName == "UnmailedReports")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "UnmailedReports.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'";
crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptUnmailedReports", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
}
else if (rptName == "EqptServiceSched")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "EquipmentServiceSched.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["dteStart"].Text = "'" + dteStart.ToShortDateString() + "'";
crDoc.DataDefinition.FormulaFields["dteEnd"].Text = "'" + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptEqptSrvcSched", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
}
else if (rptName == "SCDepartments")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "SCDepartments.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'" + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString() + "'";
sqlcmd = new SqlCommand("spRptDeptSCSP", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@StartDate", dteStart);
sqlcmd.Parameters.AddWithValue("@EndDate", dteEnd);
sqlcmd.Parameters.AddWithValue("@DeptID", nDeptID);
sqlcmd.Parameters.AddWithValue("@SC", SC);
}
else if (rptName == "DocExpiring")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "DocExpiring.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cPeriod"].Text = "'As of " + DateTime.Now.ToString("MMMM dd, yyyy") + "'";
sqlcmd = new SqlCommand("spDocExpiringAlert", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
}
else if (rptName == "SpeedResponse")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "SpeedResponse.rpt";
string strSpName = PSSClass.Sponsors.SpName(SpID);
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString("###0") + "'";
crDoc.DataDefinition.FormulaFields["cMo"].Text = "'" + nMo.ToString("###0") + "'";
crDoc.DataDefinition.FormulaFields["cSpID"].Text = "'" + SpID.ToString() + "'";
crDoc.DataDefinition.FormulaFields["cSpName"].Text = "'" + strSpName + "'";
}
else if (rptName == "RptGBLErrors")
{
string rpt = @"\\PSAPP01\IT Files\PTS\Crystal Reports\" + "RptGBLErrors.rpt";
crDoc.Load(rpt);
crDoc.DataDefinition.FormulaFields["cYr"].Text = "'" + nYr.ToString() + "'";
crDoc.DataDefinition.FormulaFields["cGBL"].Text = "'" + nGBL.ToString() + "'";
sqlcmd = new SqlCommand("spRptGBLErrors", sqlcnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Yr", nYr);
sqlcmd.Parameters.AddWithValue("@IncGBL", nGBL);
sqlcmd.CommandTimeout = 60;
}
DataTable dTable = new DataTable();
if (rptName == "PSSFinancial")
{
dTable = PSSClass.Financials.AcctSummary(nYr);
}
else if (rptName == "PSSFinancial_12Month")
{
dTable = PSSClass.Financials.AcctSummary(nYr);
}
else if (rptName.IndexOf("Variance") != -1)
{
dTable = dtRpt;
}
else if (rptName == "SpeedResponse")
{
dTable.Columns.Add("Category", typeof(int));
dTable.Columns.Add("NoDays", typeof(int));
dTable.Columns.Add("DayCount", typeof(int));
//LogTests Start Date
DataTable dtX = PSSClass.ManagementReports.SpeedResponse(1, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "LT.StartDate", Convert.ToInt16(SpID));
for (int i = 0; i < dtX.Rows.Count; i++)
{
DataRow dR = dTable.NewRow();
dR["Category"] = dtX.Rows[i]["Category"];
dR["NoDays"] = dtX.Rows[i]["NoDays"];
dR["DayCount"] = dtX.Rows[i]["DayCount"];
dTable.Rows.Add(dR);
}
//FinalRptLog DateOn LogTests End Date
dtX = PSSClass.ManagementReports.SpeedResponse(2, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "FL.DateOn", Convert.ToInt16(SpID));
for (int i = 0; i < dtX.Rows.Count; i++)
{
DataRow dR = dTable.NewRow();
dR["Category"] = dtX.Rows[i]["Category"];
dR["NoDays"] = dtX.Rows[i]["NoDays"];
dR["DayCount"] = dtX.Rows[i]["DayCount"];
dTable.Rows.Add(dR);
}
//LogTests End Date
dtX = PSSClass.ManagementReports.SpeedResponse(3, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "LT.EndDate", Convert.ToInt16(SpID));
for (int i = 0; i < dtX.Rows.Count; i++)
{
DataRow dR = dTable.NewRow();
dR["Category"] = dtX.Rows[i]["Category"];
dR["NoDays"] = dtX.Rows[i]["NoDays"];
dR["DayCount"] = dtX.Rows[i]["DayCount"];
dTable.Rows.Add(dR);
}
//FinalRptLog DateOff
dtX = PSSClass.ManagementReports.SpeedResponse(4, Convert.ToInt16(nYr), Convert.ToInt16(nMo), "FL.DateOff", Convert.ToInt16(SpID));
for (int i = 0; i < dtX.Rows.Count; i++)
{
DataRow dR = dTable.NewRow();
dR["Category"] = dtX.Rows[i]["Category"];
dR["NoDays"] = dtX.Rows[i]["NoDays"];
dR["DayCount"] = dtX.Rows[i]["DayCount"];
dTable.Rows.Add(dR);
}
//LogTest Start Date vs FinalRptLog DateOn
dtX = PSSClass.ManagementReports.SpeedResponseSD(4, Convert.ToInt16(nYr), Convert.ToInt16(nMo), Convert.ToInt16(SpID));
for (int i = 0; i < dtX.Rows.Count; i++)
{
DataRow dR = dTable.NewRow();
dR["Category"] = dtX.Rows[i]["Category"];
dR["NoDays"] = dtX.Rows[i]["NoDays"];
dR["DayCount"] = dtX.Rows[i]["DayCount"];
dTable.Rows.Add(dR);
}
}
else
{
sqldr = sqlcmd.ExecuteReader();
try
{
dTable.Load(sqldr);
}
catch { }
}
crDoc.SetDataSource(dTable);
CrTables = crDoc.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
{
crtableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}
crReport.ReportSource = crDoc;
crReport.ShowGroupTreeButton = false;
crReport.Refresh();
sqlcnn.Close(); sqlcnn.Dispose();
this.WindowState = FormWindowState.Maximized;
}
public FileResult ViewPDF(int?id)
{
Stream stream = null;
var nombreArchivo = "";
ReportDocument reportDocument = new ReportDocument();
byte[] pdfByte = null;
try
{
EPRTA_MOVIMIENTO solicitud = unitOfWork.MovimientoRepository.GetById(id);
EntitiesProveduria db = new EntitiesProveduria();
SqlConnectionStringBuilder builderVenta = new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString);
String pathReport = "";
if (solicitud.ID_TIPO_MOVIMIENTO == 2)
{
SP_EGRESO_BODEGATableAdapter tableAdapter = new SP_EGRESO_BODEGATableAdapter();
object objetos = new object();
DataTable dataTable = tableAdapter.GetData(id, out objetos);
pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Egreso_Bodega.rpt");
reportDocument.Load(pathReport);
reportDocument.SetDataSource(dataTable);
}
else if (solicitud.ID_TIPO_MOVIMIENTO == 4)
{
SP_INGRESO_BODEGATableAdapter tableAdapter = new SP_INGRESO_BODEGATableAdapter();
object objetos = new object();
DataTable dataTable = tableAdapter.GetData(id, out objetos);
pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Ingreso_Bodega.rpt");
reportDocument.Load(pathReport);
reportDocument.SetDataSource(dataTable);
}
else if (solicitud.ID_TIPO_MOVIMIENTO == 10 || solicitud.ID_TIPO_MOVIMIENTO == 11)
{
SP_AJUSTE_BODEGATableAdapter tableAdapter = new SP_AJUSTE_BODEGATableAdapter();
object objetos = new object();
DataTable dataTable = tableAdapter.GetData(id, out objetos);
pathReport = Path.Combine(HttpRuntime.AppDomainAppPath, "Reports\\Cr_Ajuste_Bodega.rpt");
reportDocument.Load(pathReport);
reportDocument.SetDataSource(dataTable);
}
reportDocument.SetDatabaseLogon(builderVenta.UserID, builderVenta.Password);
stream = reportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
pdfByte = ReadFully(stream);
nombreArchivo = "Movimiento_bodega";
Response.AddHeader("content-disposition", "inline; title='';" + "filename=" + nombreArchivo + ".pdf");
}
catch (Exception ex)
{
logger.Error(ex, ex.Message);
}
finally
{
if (reportDocument != null)
{
if (reportDocument.IsLoaded)
{
reportDocument.Close();
reportDocument.Dispose();
}
}
}
return(File(pdfByte, MediaTypeNames.Application.Pdf));
}
private void barButtonItem5_ItemClick(object sender, ItemClickEventArgs e)
{
if (DGvTransView.FocusedRowHandle < 0)
{
return;
}
int p_FlatId = 0; DataTable dt = new DataTable();
int p_MaintanceId = 0;
string s = ""; frmReport objReport; ReportDocument cryRpt;
decimal dBillAmount = 0, dArrear = 0;
DateTime dStartDate = DateTime.Today;; DateTime dEndDate = DateTime.Today;;
string[] DataFiles;
string sHeader = "";
int sMonth = 0;
int sYear = 0;
DateTime sFifetDate = DateTime.Today;
p_FlatId = Convert.ToInt32(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "FlatId"));
p_MaintanceId = Convert.ToInt32(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "MaintenanceId"));
dt = MaintenanceBL.GetReport(p_FlatId, p_MaintanceId);
if (dt.Rows.Count > 0)
{
dBillAmount = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[0]["BillAmount"], CommFun.datatypes.vartypenumeric));
dArrear = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[0]["Arrear"], CommFun.datatypes.vartypenumeric));
if (dArrear == 0)
{
dStartDate = Convert.ToDateTime(CommFun.IsNullCheck(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "StartDate"), CommFun.datatypes.VarTypeDate));
dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(DGvTransView.GetRowCellValue(DGvTransView.FocusedRowHandle, "EndDate"), CommFun.datatypes.VarTypeDate));
sMonth = dStartDate.Month;
sYear = dStartDate.Year;
sFifetDate = Convert.ToDateTime("15" + "/" + sMonth + "/" + sYear);
}
else
{
dStartDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["FromDate"], CommFun.datatypes.VarTypeDate));
dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["ToDate"], CommFun.datatypes.VarTypeDate));
sMonth = dEndDate.Month;
sYear = dEndDate.Year;
sFifetDate = Convert.ToDateTime("15" + "/" + sMonth + "/" + sYear);
}
//dEndDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["ToDate"], CommFun.datatypes.VarTypeDate));
}
decimal dTotalPayAmt = Convert.ToDecimal(dBillAmount + dArrear);
objReport = new frmReport();
string strReportPath = Application.StartupPath + "\\Maintenance.Rpt";
cryRpt = new ReportDocument();
cryRpt.Load(strReportPath);
s = "{MaintenanceDet.FlatId}=" + p_FlatId + " ";
DataFiles = new string[] { BsfGlobal.g_sCRMDBName, BsfGlobal.g_sCRMDBName, BsfGlobal.g_sCRMDBName,
BsfGlobal.g_sCRMDBName, BsfGlobal.g_sWorkFlowDBName,
BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sWorkFlowDBName, BsfGlobal.g_sCRMDBName };
objReport.ReportConvert(cryRpt, DataFiles);
if (s.Length > 0)
{
cryRpt.RecordSelectionFormula = s;
}
objReport.rptViewer.ReportSource = null;
objReport.rptViewer.SelectionFormula = s;
objReport.rptViewer.ReportSource = cryRpt;
cryRpt.DataDefinition.FormulaFields["Decimal"].Text = string.Format(CommFun.g_iCurrencyDigit.ToString());
cryRpt.DataDefinition.FormulaFields["MainAmt"].Text = "'" + string.Format(dBillAmount.ToString()) + "'";
cryRpt.DataDefinition.FormulaFields["Arrear"].Text = "'" + string.Format(dArrear.ToString()) + "'";
cryRpt.DataDefinition.FormulaFields["TotalPay"].Text = "'" + string.Format(dTotalPayAmt.ToString()) + "'";
if (dArrear == 0)
{
cryRpt.DataDefinition.FormulaFields["EndFiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy")));
cryRpt.DataDefinition.FormulaFields["StartfiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy")));
}
else
{
cryRpt.DataDefinition.FormulaFields["EndFiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy")));
cryRpt.DataDefinition.FormulaFields["StartfiftenDate"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} ", sFifetDate.ToString("dd-MMM-yyyy")));
}
cryRpt.DataDefinition.FormulaFields["Period"].Text = String.Format("'{0}'", sHeader = sHeader = String.Format(" {0} to {1} ", dStartDate.ToString("dd-MMM-yyyy"), dEndDate.ToString("dd-MMM-yyyy")));
objReport.WindowState = FormWindowState.Maximized;
objReport.rptViewer.Refresh();
objReport.Show();
}
private void ShowReport()
{
ReportDocument myReportDocument = new ReportDocument();
if (rdoNormalEmployee.Checked)
{
myReportDocument.Load(Server.MapPath("~/MainReport/Reports/rptNormalEmployee.rpt"));
}
else if (rdoSpecEmployee.Checked)
{
myReportDocument.Load(Server.MapPath("~/MainReport/Reports/rptSpecEmployee.rpt"));
}
myReportDocument.Refresh();
CrystalReportViewer1.ReportSource = myReportDocument;
long Code = 0;
int sex = 0;
long JobId = 0;
long Idno = 0;
long CountryId = 0;
long CityId = 0;
string FirstName = "0";
string LastName = "0";
long DepartmentId = 0;
long DivisionId = 0;
long QualificationId = 0;
if (!string.IsNullOrEmpty(txtCode.Text))
{
Code = long.Parse(txtCode.Text);
}
if (ddlSex.SelectedValue != "")
{
sex = int.Parse(ddlSex.SelectedValue);
}
if (ddlJob.SelectedValue != "")
{
JobId = long.Parse(ddlJob.SelectedValue);
}
if (!string.IsNullOrEmpty(txtIdNo.Text))
{
Idno = long.Parse(txtIdNo.Text);
}
if (ddlCountry.SelectedValue != "")
{
CountryId = long.Parse(ddlCountry.SelectedValue);
}
if (ddlCity.SelectedValue != "")
{
CityId = long.Parse(ddlCity.SelectedValue);
}
if (!string.IsNullOrEmpty(txtFirstName.Text))
{
FirstName = txtFirstName.Text;
}
if (!string.IsNullOrEmpty(txtLastName.Text))
{
LastName = txtLastName.Text;
}
if (ddlDepartment.SelectedValue != "")
{
DepartmentId = long.Parse(ddlDepartment.SelectedValue);
}
if (ddlDivision.SelectedValue != "")
{
DivisionId = long.Parse(ddlDivision.SelectedValue);
}
if (ddlQualification.SelectedValue != "")
{
QualificationId = long.Parse(ddlQualification.SelectedValue);
}
//myReportDocument.SetDatabaseLogon(csGetPermission.DBUser, csGetPermission.DBPassword, csGetPermission.DBServerName, csGetPermission.DBName);
myReportDocument.DataSourceConnections[0].SetConnection(csGetPermission.DBServerName, csGetPermission.DBName, csGetPermission.DBUser, csGetPermission.DBPassword);
myReportDocument.SetParameterValue("@Code", Code);
myReportDocument.SetParameterValue("@Sex", sex);
myReportDocument.SetParameterValue("@JobId", JobId);
myReportDocument.SetParameterValue("@IdNo", Idno);
myReportDocument.SetParameterValue("@CountryId", CountryId);
myReportDocument.SetParameterValue("@CityId", CityId);
myReportDocument.SetParameterValue("@FirstName", FirstName);
myReportDocument.SetParameterValue("@LastName", LastName);
myReportDocument.SetParameterValue("@DepartmentId", DepartmentId);
myReportDocument.SetParameterValue("@DivisionId", DivisionId);
myReportDocument.SetParameterValue("@QualificationId", QualificationId);
myReportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "");
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder sbFilter = new StringBuilder();
string fundCode = "";
string fundName = "";
string balDate = "";
string statementType = "";
if (Session["UserID"] == null)
{
Session.RemoveAll();
Response.Redirect("../../Default.aspx");
}
else
{
statementType = (string)Session["statementType"];
fundCode = (string)Session["fundCode"];
fundName = (string)Session["fundName"];
balDate = (string)Session["balDate"];
}
string strSQL, strSQLGrandTotCostVal;
string Path = "";
double cs_asset, cf_unlist, TotalAssetValue_Mar, GrandTotCostVal;
DataTable dtReprtSource = new DataTable();
DataTable dtGrandTotCostVal = new DataTable();
DataTable dtNAV = new DataTable();
DataTable dtNonListed = new DataTable();
strSQL = "select nvl(sum(n.costprice),0) total_asset_c,nvl(sum(n.marketprice),0) total_asset_m from nav.nav_details n, nav.nav_master m where " +
" n.NAVROWTYPE = 'A' and n.navfundid = " + fundCode + " and m.navfundid = " + fundCode + " and m.navno = (select max(navno) from nav.nav_master m where m.navfundid = " + fundCode + " AND m.NAVDATE <='" + balDate + "' )" +
" and n.navfundid = m.navfundid and m.navno = n.navno";
dtNAV = commonGatewayObj.Select(strSQL);
cs_asset = Convert.ToDouble(dtNAV.Rows[0]["total_asset_c"].ToString());
if (cs_asset == 0)
{
cs_asset = 1;
}
TotalAssetValue_Mar = Convert.ToDouble(dtNAV.Rows[0]["total_asset_m"].ToString());
strSQL = "select f_cd, inv_amount, inv_date FROM(SELECT f_cd, inv_amount, inv_date, " +
"rank() over (partition by f_cd order by inv_date desc) rnk FROM NON_LISTED_SECURITIES where inv_amount>0 and F_CD IN(" + fundCode + ") and inv_date<='" + balDate + "' )" +
" WHERE rnk = 1";
dtNonListed = commonGatewayObj.Select(strSQL);
if (dtNonListed.Rows.Count > 0)
{
cf_unlist = Convert.ToDouble(dtNonListed.Rows[0]["inv_amount"].ToString());
}
else
{
cf_unlist = 0;
}
if (string.Compare(statementType, "Summary (Total Asset Value)", true) == 0)
{
strSQL = "select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)," + cs_asset + " cs_asset," +
TotalAssetValue_Mar + "TotalAssetValue_Mar,p.unl_p," + cf_unlist + "NonListVal from " +
"(select trim(c.comp_nm)comp_nm, sect_maj_nm,a.f_cd, a.sect_maj_cd, trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm, " +
"trunc(tcst_aft_com, 2) tcst_aft_com,ROUND((tot_cost / tot_nos),2) c_rt, tot_cost,round((tcst_aft_com/" + cs_asset + ")*100,2) cost_percent," +
"nvl(a.dse_rt, 0) DSE_rate, nvl(a.cse_rt, 0) CSE_rate, a.adc_rt m_rt, a.dse_rt* tot_nos m_p," +
"(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ," +
"(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round((" + cf_unlist / cs_asset + ")*100,2)unl_p" +
" from pfolio_bk a, comp c , nav.nav_master n " +
" where c.comp_cd = a.comp_cd and a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
" and n.navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)p" +
" group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd order by p.sect_maj_cd";
dtReprtSource = commonGatewayObj.Select(strSQL);
strSQLGrandTotCostVal =
"select sum(TotalCost) as GrandTotCostVal from" +
"(select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)," + cs_asset + " cs_asset," +
TotalAssetValue_Mar + "TotalAssetValue_Mar,p.unl_p," + cf_unlist + "NonListVal from " +
"(select trim(c.comp_nm)comp_nm, sect_maj_nm,a.f_cd, a.sect_maj_cd, trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm, " +
"trunc(tcst_aft_com, 2) tcst_aft_com,ROUND((tot_cost / tot_nos),2) c_rt, tot_cost,round((tcst_aft_com/" + cs_asset + ")*100,2) cost_percent," +
"nvl(a.dse_rt, 0) DSE_rate, nvl(a.cse_rt, 0) CSE_rate, a.adc_rt m_rt, a.dse_rt* tot_nos m_p," +
"(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ," +
"(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round((" + cf_unlist / cs_asset + ")*100,2)unl_p" +
" from pfolio_bk a, comp c , nav.nav_master n " +
" where c.comp_cd = a.comp_cd and a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
" and n.navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)p" +
" group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd order by p.sect_maj_cd)";
dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal);
if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal"))
{
GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString());
GrandTotCostVal = GrandTotCostVal + cf_unlist;
}
else
{
GrandTotCostVal = 1;
}
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "AsstPerNAVSummaryAndPortfolio";
// dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPerNAVSummaryAndPortfolio.xsd");
Path = Server.MapPath("Report/crtAssetPercSummaryTotalAssetValueReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc;
CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true;
rdoc.SetParameterValue("prmbalDate", balDate);
rdoc.SetParameterValue("prmStatementType", statementType);
rdoc.SetParameterValue("prmFundName", fundName);
rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal);
// rdoc.SetParameterValue("prmappOrEro", appOrEro);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
else if (string.Compare(statementType, "Portfolio (Total Asset Value)", true) == 0)
{
strSQL = "select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt, trunc(tcst_aft_com / tot_nos, 2) rt_acm," + cs_asset + " cs_asset," + cf_unlist + "NonListVal, " +
"trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost," +
"a.adc_rt m_rt, a.adc_rt* tot_nos m_p,(a.adc_rt * tot_nos - tcst_aft_com) diff," +
"(round(a.adc_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round(((trunc(tot_nos) / c.no_shrs) * 100),2) paid_cap " +
" from pfolio_bk a, comp c , nav.nav_master n " +
" where c.comp_cd = a.comp_cd and a.f_cd = " + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
"and navno = (select max(navno) from nav.nav_master where navfundid =" + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd";
dtReprtSource = commonGatewayObj.Select(strSQL);
strSQLGrandTotCostVal = "select sum(tcst_aft_com) as GrandTotCostVal from " +
"(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,trunc(tot_nos) nos_t, bal_dt, trunc(tcst_aft_com / tot_nos, 2) rt_acm," + cs_asset + " cs_asset," + cf_unlist + "NonListVal, " +
"trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost," +
"a.adc_rt m_rt, a.adc_rt* tot_nos m_p,(a.adc_rt * tot_nos - tcst_aft_com) diff," +
"(round(a.adc_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain,round(((trunc(tot_nos) / c.no_shrs) * 100),2) paid_cap " +
" from pfolio_bk a, comp c , nav.nav_master n " +
" where c.comp_cd = a.comp_cd and a.f_cd = " + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
"and navno = (select max(navno) from nav.nav_master where navfundid =" + fundCode + " AND NAVDATE<='" + balDate + "' ) order by a.sect_maj_cd)";
dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal);
if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal"))
{
GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString());
GrandTotCostVal = GrandTotCostVal + cf_unlist;
}
else
{
GrandTotCostVal = 1;
}
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "AsstPercentagePortfolio";
dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPercentagePortfolio.xsd");
Path = Server.MapPath("Report/crtAssetPercentagePortfolioTotalAssetValueReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc;
CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true;
rdoc.SetParameterValue("prmbalDate", balDate);
rdoc.SetParameterValue("prmStatementType", statementType);
rdoc.SetParameterValue("prmFundName", fundName);
rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal);
// rdoc.SetParameterValue("prmappOrEro", appOrEro);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
else
{
strSQL = "select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,p.asset,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)TotalMarketPrice," + cf_unlist + " NonListVal from " +
"(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,a.f_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm," +
" trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost, round(((tcst_aft_com / n.navtotalcostprice) * 100),2) cost_percent," +
"a.adc_rt m_rt, a.dse_rt* tot_nos m_p,(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ,(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain," +
"n.navtotalcostprice asset from pfolio_bk a, comp c, nav.nav_master n where c.comp_cd = a.comp_cd and " +
"a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
" and navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "') order by c.comp_nm)p" +
" group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd,p.asset order by p.sect_maj_cd";
dtReprtSource = commonGatewayObj.Select(strSQL);
strSQLGrandTotCostVal =
"select sum(TotalCost) as GrandTotCostVal from " +
"(select p.sect_maj_nm,p.f_cd, p.sect_maj_cd, sum(p.nos_t)TotalShares, sum(p.rt_acm),sum(p.tcst_aft_com)TotalCost,p.asset,sum(p.cost_percent)TotalCostPercent,sum(p.m_p)TotalMarketPrice," + cf_unlist + " NonListVal from " +
"(select trim(c.comp_nm), sect_maj_nm, a.sect_maj_cd,a.f_cd,trunc(tot_nos) nos_t, bal_dt , trunc(tcst_aft_com / tot_nos, 2) rt_acm," +
" trunc(tcst_aft_com, 2) tcst_aft_com, round((tot_cost / tot_nos),2) c_rt, tot_cost, round(((tcst_aft_com / n.navtotalcostprice) * 100),2) cost_percent," +
"a.adc_rt m_rt, a.dse_rt* tot_nos m_p,(a.dse_rt - trunc(tcst_aft_com / tot_nos, 2)) diff ,(round(a.dse_rt, 2) - trunc(tcst_aft_com / tot_nos, 2)) * trunc(tot_nos) gain," +
"n.navtotalcostprice asset from pfolio_bk a, comp c, nav.nav_master n where c.comp_cd = a.comp_cd and " +
"a.f_cd =" + fundCode + " and a.bal_dt_ctrl ='" + balDate + "' and n.navfundid = " + fundCode +
" and navno = (select max(navno) from nav.nav_master where navfundid = " + fundCode + " AND NAVDATE<='" + balDate + "') order by c.comp_nm)p" +
" group by p.sect_maj_cd,p.sect_maj_nm,p.f_cd,p.asset order by p.sect_maj_cd)";
dtGrandTotCostVal = commonGatewayObj.Select(strSQLGrandTotCostVal);
if (!dtGrandTotCostVal.Rows[0].IsNull("GrandTotCostVal"))
{
GrandTotCostVal = Convert.ToDouble(dtGrandTotCostVal.Rows[0]["GrandTotCostVal"].ToString());
GrandTotCostVal = GrandTotCostVal + cf_unlist;
}
else
{
GrandTotCostVal = 1;
}
if (dtReprtSource.Rows.Count > 0)
{
dtReprtSource.TableName = "AsstPercentageNAV";
dtReprtSource.WriteXmlSchema(@"E:\iamclpfmsnew\amclpmfs\UI\ReportViewer\Report\xsdAsstPercentageNAV.xsd");
Path = Server.MapPath("Report/crtAssetPercNetAssetValueReport.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtReprtSource);
CRV_AssetPercNAVSummaryAndPortfolio.ReportSource = rdoc;
CRV_AssetPercNAVSummaryAndPortfolio.DisplayToolbar = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasExportButton = true;
CRV_AssetPercNAVSummaryAndPortfolio.HasPrintButton = true;
rdoc.SetParameterValue("prmbalDate", balDate);
rdoc.SetParameterValue("prmStatementType", statementType);
rdoc.SetParameterValue("prmFundName", fundName);
rdoc.SetParameterValue("prmGrandTotCostVal", GrandTotCostVal);
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No Data Found");
}
}
}
protected void Page_Init(object sender, EventArgs e)
{
DataSetAssetClass imageDataSet = new DataSetAssetClass();
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmdl;
OracleDataAdapter oradata;
DataTable dtc;
string AsOnDate = Request.QueryString["AsOnDate"].ToString();
string MakeAsOnDate = Request.QueryString["AsOnDate"].ToString();
string[] MakeAsOnDateSplit = MakeAsOnDate.Split('-');
String AsOnDateTemp = MakeAsOnDateSplit[0].Replace("/", "-");
DateTime AsOnDateNewD = DateTime.ParseExact(AsOnDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture);
string AsOnDateNew = AsOnDateNewD.ToString("dd-MM-yyyy");
DateTime curDate = AsOnDateNewD;
DateTime startDate = curDate.AddMonths(-1);
DateTime LastDateTemp = curDate.AddDays(-(curDate.Day));
string LastDate = LastDateTemp.ToString("dd-MM-yyyy");
string LastMonth = startDate.ToString("MM-yyyy");
string CurrentMonth = AsOnDateNewD.ToString("MM-yyyy");
// string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, ROUND((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)),3) AS AVAIL_SALE_AMT, ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT,ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2),3) AS END_AMT, ROUND((((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1))* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2)) / ((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0)+nvl(PSMR.ITEM_WEIGHT,0))),3) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_PURCHASE_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT_IN_FG) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID ORDER BY PI.ITEM_ID ";
// string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, nvl(PPMD.ITEM_AMOUNT,0) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS AVAIL_SALE_AMT, ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT, nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0) * ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2) AS END_AMT, nvl((nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0) * ROUND((ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) + nvl(PPMD.ITEM_AMOUNT,0) + nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND( nvl(PPMD.ITEM_AMOUNT,0) ,2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) + ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3)) / (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),2)) / nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)),0),0) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(ITEM_AMOUNT) AS ITEM_AMOUNT FROM PF_SALES_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID WHERE ( ((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0))) > 0 OR nvl(BEGPFSIH.FINAL_STOCK_WT,0) > 0 OR nvl(PSM.ITEM_WEIGHT,0) > 0) ORDER BY PI.ITEM_ID ";
string sqlString = " SELECT PI.ITEM_ID, PI.ITEM_NAME, nvl(BEGPFSIH.FINAL_STOCK_WT,0) AS BEG_FSTOCK_WT, nvl(BEGPFSIH.ITEM_AVG_RATE,0) AS BEG_AVG_RATE, ROUND(nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0),3) AS BEG_AMT, nvl(PPMD.ITEM_WEIGHT,0) AS PURCHASE_WTD, ROUND(nvl((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1 )* nvl(PPMD.ITEM_WEIGHT,0),0),2) AS PURCHASE_AMTD, nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) AS PRODUCTION_WT, nvl(ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1 )* nvl(PPMD.ITEM_WEIGHT,0),2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1) ,2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0),0) AS PRODUCTION_AMT, ROUND(nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),3) AS PROD_PRO_COST_AMT, ROUND(nvl((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / (nullif((nvl(BEGPRSIH.FINAL_STOCK_WT,0) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0),0)*1),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0),0),3) AS PROD_TOTAL_COST_AMT, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) AS AVAIL_SALE_WT, ROUND(nvl((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)),0),3) AS AVAIL_SALE_AMT, ROUND(nvl(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/ nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1,0),2) AS AVAIL_SALE_AVG_RATE, nvl(PSM.ITEM_WEIGHT,0) AS SALE_WT, nvl(PSM.ITEM_AMOUNT,0) AS SALES_AMOUNT, nvl(PSMR.ITEM_WEIGHT,0) AS SALE_RETURN_WET, (nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)) AS END_FSTOCK_WT, ROUND(nvl(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1)* nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1),2),0),3) AS END_AMT, ROUND(nvl((((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0)))* ROUND(((nvl(BEGPFSIH.FINAL_STOCK_WT,0) * nvl(BEGPFSIH.ITEM_AVG_RATE,0))+ ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1) * nvl(PPMD.ITEM_WEIGHT,0),2)+ ((ROUND(ROUND(((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) + nvl(PPM.ITEM_AMOUNT,0) ) - ROUND((((nvl(BEGPRSIH.FINAL_STOCK_WT,0) * nvl(BEGPRSIH.ITEM_AVG_RATE,0)) +nvl(PPM.ITEM_AMOUNT,0))/ nullif(nvl( nvl(BEGPRSIH.FINAL_STOCK_WT,1)+ nvl(PPM.ITEM_WEIGHT,0),1),0) * 1) * nvl(PPMD.ITEM_WEIGHT,0),2)) / ((nvl(BEGPRSIH.FINAL_STOCK_WT,1) + nvl(PPM.ITEM_WEIGHT,0)) - nvl(PPMD.ITEM_WEIGHT,0)),2) * nvl(PPMAS.ITEM_WEIGHT_IN_FG,0)) + nvl(nvl(PPMAS.ITEM_WEIGHT_IN_FG,0) * nvl(PPC.COST_RATE,0),0)))/(nullif((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)),0) * 1),2)) / nullif(((nvl(BEGPFSIH.FINAL_STOCK_WT,1) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0)+nvl(PSMR.ITEM_WEIGHT,0))),0),0),3) AS END_AVG_RATE FROM PF_ITEM PI LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_RM_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPRSIH ON PI.ITEM_ID = BEGPRSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, FINAL_STOCK_WT, ITEM_AVG_RATE FROM PF_FG_STOCK_INVENTORY_HISTORY WHERE TO_CHAR(TO_DATE(CREATE_DATE), 'dd-mm-YYYY') = '" + LastDate + "' ) BEGPFSIH ON PI.ITEM_ID = BEGPFSIH.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + LastMonth + "' GROUP BY ITEM_ID) BEGPPM ON PI.ITEM_ID = BEGPPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT, sum(nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0)) AS ITEM_AMOUNT FROM PF_PURCHASE_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPM ON PI.ITEM_ID = PPM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE PUR_TYPE_ID = 1 AND TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMD ON PI.ITEM_ID = PPMD.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT_IN_FG FROM PF_PRODUCTION_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' GROUP BY ITEM_ID) PPMAS ON PI.ITEM_ID = PPMAS.ITEM_ID LEFT JOIN (SELECT ITEM_ID, nvl(COST_RATE,0) AS COST_RATE FROM PF_PROCESSING_COST WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' AND nvl(COST_RATE,0) !=0 ORDER BY ITEM_ID) PPC ON PI.ITEM_ID = PPC.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(nvl(ITEM_WEIGHT,0)) AS ITEM_WEIGHT, SUM(CASE WHEN SALES_MODE = 'Local' THEN nvl(ITEM_AMOUNT,0)+nvl(VAT_AMOUNT,0) WHEN SALES_MODE = 'Export' THEN nvl(ITEM_AMOUNT,0) END) AS ITEM_AMOUNT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN IS NULL GROUP BY ITEM_ID) PSM ON PI.ITEM_ID = PSM.ITEM_ID LEFT JOIN (SELECT ITEM_ID, sum(ITEM_WEIGHT) AS ITEM_WEIGHT FROM PF_SALES_MASTER WHERE TO_CHAR(TO_DATE(ENTRY_DATE), 'mm-YYYY') = '" + CurrentMonth + "' AND IS_SALES_RETURN = 'Yes' GROUP BY ITEM_ID) PSMR ON PI.ITEM_ID = PSMR.ITEM_ID WHERE ( ((nvl(BEGPFSIH.FINAL_STOCK_WT,0) + nvl(PPMD.ITEM_WEIGHT,0) + nvl(ITEM_WEIGHT_IN_FG,0)) - (nvl(PSM.ITEM_WEIGHT,0) + nvl(PSMR.ITEM_WEIGHT,0))) > 0 OR nvl(BEGPFSIH.FINAL_STOCK_WT,0) > 0 OR nvl(PSM.ITEM_WEIGHT,0) > 0) ORDER BY PI.ITEM_ID ";
OracleCommand cmd = new OracleCommand(sqlString, conn);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlString;
OracleDataAdapter dt = new OracleDataAdapter(cmd);
conn.Open();
dt.Fill(imageDataSet.Tables["reportTable"]);
// for processing cost
string makeSQL = " SELECT nvl(PPC.COST_RATE,0) AS COST_RATE FROM PF_ITEM PI LEFT JOIN PF_PROCESSING_COST PPC ON PPC.ITEM_ID = PI.ITEM_ID WHERE TO_CHAR(TO_DATE(MONTH_YEAR), 'mm-YYYY') = '" + CurrentMonth + "' ORDER BY PI.ITEM_ID asc ";
cmdl = new OracleCommand(makeSQL);
oradata = new OracleDataAdapter(cmdl.CommandText, conn);
dtc = new DataTable();
oradata.Fill(dtc);
int RowCount = dtc.Rows.Count;
double[] items_cost = new double[100];
for (int i = 0; i < RowCount; i++)
{
items_cost[i] = Convert.ToDouble(dtc.Rows[i]["COST_RATE"]);
}
double HDPE = items_cost[0];
double LDPE = items_cost[1];
double PC = items_cost[2];
double PET = items_cost[3];
double PP = items_cost[4];
double PVC = items_cost[5];
double MIX_PLASTIC = items_cost[6];
double XLPE = items_cost[7];
double PS = items_cost[8];
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("~/PF/PF_Reports/Pf_Fg_Statement_Report.rpt"));
crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]);
crystalReport.SetParameterValue("AsOnDate", AsOnDateNewD);
// crystalReport.SetParameterValue("ItemCost", items_cost);
crystalReport.SetParameterValue("HDPE", HDPE);
crystalReport.SetParameterValue("LDPE", LDPE);
crystalReport.SetParameterValue("PC", PC);
crystalReport.SetParameterValue("PET", PET);
crystalReport.SetParameterValue("PP", PP);
crystalReport.SetParameterValue("PVC", PVC);
crystalReport.SetParameterValue("MIX_PLASTIC", MIX_PLASTIC);
crystalReport.SetParameterValue("XLPE", XLPE);
crystalReport.SetParameterValue("PS", PS);
string datetime = DateTime.Now.ToString("dd-MM-yyyy");
CrystalReportViewer1.ID = "FG_Statement_Report_(As_On_Date)_" + datetime + "";
CrystalReportViewer1.ReportSource = crystalReport;
conn.Close();
}
public void Labelnumbers()
{
if (ddlproduct.SelectedValue != "0")
{
int productid = Convert.ToInt32(ddlproduct.SelectedValue);
// string strPattern = "([a-z?])[_ ]?([A-Z])";
DataTable dtAudit = new DataTable();
dtAudit.Columns.Add("KeyDataName", typeof(string));
dtAudit.Columns.Add("KeyDataValue", typeof(string));
dtAudit.Columns.Add("KeyDataDesc", typeof(string));
dtAudit.Columns.Add("DataName", typeof(string));
dtAudit.Columns.Add("DataType", typeof(string));
dtAudit.Columns.Add("ActionTaken", typeof(string));
dtAudit.Columns.Add("OldValue", typeof(string));
dtAudit.Columns.Add("NewValue", typeof(string));
dtAudit.Columns.Add("DateDone", typeof(string));
dtAudit.Columns.Add("DoneBy", typeof(string));
int nProdID = productid; //Product id
//Get All Label No. Activities
DataTable dtAuLogGPLS = st_dll.ProductLabelGPLS(nProdID);
DataTable dtAuLabelNos = st_dll.LabelNosGPLS(nProdID);
if (dtAuLabelNos != null && dtAuLabelNos.Rows.Count > 0)
{
for (int i = 0; i < dtAuLabelNos.Rows.Count; i++)
{
if (dtAuLabelNos.Rows[i]["DatePrinted"] != DBNull.Value)
{
string strType = dtAuLabelNos.Columns["DatePrinted"].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "PRODUCT ID";
dR["KeyDataValue"] = nProdID.ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = "Date Printed (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" +
dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")";
dR["OldValue"] = DBNull.Value;
dR["NewValue"] = dtAuLabelNos.Rows[i]["DatePrinted"].ToString();
dR["ActionTaken"] = "2";
dR["Datedone"] = dtAuLabelNos.Rows[i]["DatePrinted"].ToString();
dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["PrintedByID"]));
dtAudit.Rows.Add(dR);
}
}
//Voided
for (int i = 0; i < dtAuLabelNos.Rows.Count; i++)
{
if (dtAuLabelNos.Rows[i]["DateVoided"] != DBNull.Value)
{
string strType = dtAuLabelNos.Columns["DateVoided"].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "PRODUCT ID";
dR["KeyDataValue"] = nProdID.ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = "DateVoided (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" +
dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")";
dR["OldValue"] = DBNull.Value;
dR["NewValue"] = dtAuLabelNos.Rows[i]["DateVoided"].ToString();
dR["ActionTaken"] = "2";
dR["Datedone"] = dtAuLabelNos.Rows[i]["DateVoided"].ToString();
dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["VoidedByID"]));
dtAudit.Rows.Add(dR);
}
}
//Approved
for (int i = 0; i < dtAuLabelNos.Rows.Count; i++)
{
if (dtAuLabelNos.Rows[i]["DateApproved"] != DBNull.Value)
{
string strType = dtAuLabelNos.Columns["DateApproved"].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "PRODUCT ID";
dR["KeyDataValue"] = nProdID.ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = "DateApproved (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" +
dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")";
dR["OldValue"] = DBNull.Value;
dR["NewValue"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString();
dR["ActionTaken"] = "2";
dR["Datedone"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString();
dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["ApprovedByID"]));
dtAudit.Rows.Add(dR);
}
}
//Added
for (int i = 0; i < dtAuLabelNos.Rows.Count; i++)
{
if (dtAuLabelNos.Rows[i]["LabelStatus"].ToString() == "2")
{
string strType = dtAuLabelNos.Columns["DateApproved"].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "PRODUCT ID";
dR["KeyDataValue"] = nProdID.ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = "DateApproved (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" +
dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")";
dR["OldValue"] = DBNull.Value;
dR["NewValue"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString();
dR["ActionTaken"] = "1";
dR["Datedone"] = dtAuLabelNos.Rows[i]["DateApproved"].ToString();
dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["ApprovedByID"]));
dtAudit.Rows.Add(dR);
}
}
//Rejected
for (int i = 0; i < dtAuLabelNos.Rows.Count; i++)
{
if (dtAuLabelNos.Rows[i]["DateRejected"] != DBNull.Value)
{
string strType = dtAuLabelNos.Columns["DateRejected"].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "PRODUCT ID";
dR["KeyDataValue"] = nProdID.ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = "DateRejected (" + dtAuLabelNos.Rows[i]["CtrlID"].ToString() + "-" +
dtAuLabelNos.Rows[i]["CategoryCode"].ToString() + "-" + Convert.ToInt16(dtAuLabelNos.Rows[i]["LabelNo"]).ToString("000") + ")";
dR["OldValue"] = DBNull.Value;
dR["NewValue"] = dtAuLabelNos.Rows[i]["DateRejected"].ToString();
dR["ActionTaken"] = "2";
dR["Datedone"] = dtAuLabelNos.Rows[i]["DateRejected"].ToString();
dR["DoneBy"] = st_dll.UserFullName(Convert.ToInt16(dtAuLabelNos.Rows[i]["RejectedByID"]));
dtAudit.Rows.Add(dR);
}
}
}
if (dtAudit == null || dtAudit.Rows.Count == 0)
{
ErrorMessage("No audit records found.");
return;
}
//AuditRpt rpt = new AuditRpt();
//rpt.rptName = "Audit Products";
//rpt.dt = dtAudit;
//rpt.WindowState = FormWindowState.Maximized;
//rpt.Show();
rptDoc = new ReportDocument();
CrystalReportViewer1.HasToggleGroupTreeButton = false;
CrystalReportViewer1.BestFitPage = false;
CrystalReportViewer1.Width = 920;
rptDoc.Load(Server.MapPath("~/Reports/AuditProducts.rpt"));
//rptDoc.SetParameterValue("@PRODUCTID", productid);
rptDoc.SetDataSource(dtAudit);
rptDoc.SetDatabaseLogon("sa", "Pass2018", "172.16.4.12", "PLSAudit");
rptDoc.DataSourceConnections[0].SetConnection("172.16.4.12", "PLSAudit", "sa", "Pass2018");
CrystalReportViewer1.Page.Title = "Audit Label Numbers";
rptDoc.SummaryInfo.ReportTitle = "Audit Label Numbers";
rptDoc.DataDefinition.FormulaFields["cTitle"].Text = "'Audit Trail : Label Numbers'";
CrystalReportViewer1.ReportSource = rptDoc;
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
rptDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat,
Response, true, "Audit Label Numbers");
CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None;
CrystalReportViewer1.SeparatePages = true;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.HasExportButton = false;
Response.Flush();
Response.Close();
}
else
{
ErrorMessage("Please select the Product/Component.");
}
}
protected void Page_Load(object sender, EventArgs e)
{
string fundCode = "";
string branchCode = "";
if (BaseContent.IsSessionExpired())
{
Response.Redirect("../../Default.aspx");
return;
}
bcContent = (BaseClass)Session["BCContent"];
userObj.UserID = bcContent.LoginID.ToString();
branchCode = (string)Session["branchCode"];
fundCode = (string)Session["fundCode"];
DataTable dtUnitHolderInfo = reportObj.getDtHolderInfo();
dtUnitHolderInfo.TableName = "UnitHolderInfo";
DataRow drUnitHolderInfo;
DataTable dtHolderInfo = (DataTable)Session["dtHolderInfo"];
if (dtHolderInfo.Rows.Count > 0)
{
for (int looper = 0; looper < dtHolderInfo.Rows.Count; looper++)
{
drUnitHolderInfo = dtUnitHolderInfo.NewRow();
drUnitHolderInfo["REG_NO"] = dtHolderInfo.Rows[looper]["REG_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["REG_NO"].ToString();
drUnitHolderInfo["REG_DT"] = dtHolderInfo.Rows[looper]["REG_DT"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["REG_DT"].ToString();
if (!dtHolderInfo.Rows[looper]["REG_TYPE"].Equals(DBNull.Value))
{
drUnitHolderInfo["REG_TYPE"] = reportObj.getRegTypeFullName(dtHolderInfo.Rows[looper]["REG_TYPE"].ToString()).ToString();
}
drUnitHolderInfo["HNAME"] = dtHolderInfo.Rows[looper]["HNAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["HNAME"].ToString();
drUnitHolderInfo["FMH_NAME"] = dtHolderInfo.Rows[looper]["FMH_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["FMH_NAME"].ToString();
drUnitHolderInfo["MO_NAME"] = dtHolderInfo.Rows[looper]["MO_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["MO_NAME"].ToString();
drUnitHolderInfo["ADDRS1"] = dtHolderInfo.Rows[looper]["ADDRS1"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ADDRS1"].ToString();
drUnitHolderInfo["ADDRS2"] = dtHolderInfo.Rows[looper]["ADDRS2"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ADDRS2"].ToString();
drUnitHolderInfo["CITY"] = dtHolderInfo.Rows[looper]["CITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["CITY"].ToString();
drUnitHolderInfo["NATIONALITY"] = dtHolderInfo.Rows[looper]["NATIONALITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["NATIONALITY"].ToString();
drUnitHolderInfo["OCC_CODE"] = dtHolderInfo.Rows[looper]["OCC_CODE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["OCC_CODE"].ToString();
drUnitHolderInfo["B_DATE"] = dtHolderInfo.Rows[looper]["B_DATE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["B_DATE"].ToString();
if (!dtHolderInfo.Rows[looper]["SEX"].Equals(DBNull.Value))
{
if (string.Compare(dtHolderInfo.Rows[looper]["SEX"].ToString(), "M", true) == 0)
{
drUnitHolderInfo["SEX"] = "MAlE";
}
else if (string.Compare(dtHolderInfo.Rows[looper]["SEX"].ToString(), "F", true) == 0)
{
drUnitHolderInfo["SEX"] = "FEMALE";
}
}
if (!dtHolderInfo.Rows[looper]["MAR_STAT"].Equals(DBNull.Value))
{
drUnitHolderInfo["MAR_STAT"] = reportObj.getMaritialFullName(dtHolderInfo.Rows[looper]["MAR_STAT"].ToString()).ToString();
}
if (!dtHolderInfo.Rows[looper]["RELIGION"].Equals(DBNull.Value))
{
drUnitHolderInfo["RELIGION"] = reportObj.getReligionFullName(dtHolderInfo.Rows[looper]["RELIGION"].ToString()).ToString();
}
drUnitHolderInfo["EDU_QUA"] = dtHolderInfo.Rows[looper]["EDU_QUA"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["EDU_QUA"].ToString();
drUnitHolderInfo["TEL_NO"] = dtHolderInfo.Rows[looper]["TEL_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["TEL_NO"].ToString();
drUnitHolderInfo["EMAIL"] = dtHolderInfo.Rows[looper]["EMAIL"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["EMAIL"].ToString();
drUnitHolderInfo["JNT_NAME"] = dtHolderInfo.Rows[looper]["JNT_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_NAME"].ToString();
drUnitHolderInfo["JNT_FMH_NAME"] = dtHolderInfo.Rows[looper]["JNT_FMH_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_FMH_NAME"].ToString();
drUnitHolderInfo["JNT_MO_NAME"] = dtHolderInfo.Rows[looper]["JNT_MO_NAME"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_MO_NAME"].ToString();
drUnitHolderInfo["JNT_OCC_CODE"] = dtHolderInfo.Rows[looper]["JNT_OCC_CODE"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_OCC_CODE"].ToString();
drUnitHolderInfo["JNT_ADDRS1"] = dtHolderInfo.Rows[looper]["JNT_ADDRS1"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_ADDRS1"].ToString();
drUnitHolderInfo["JNT_ADDRS2"] = dtHolderInfo.Rows[looper]["JNT_ADDRS2"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_ADDRS2"].ToString();
drUnitHolderInfo["JNT_NATIONALITY"] = dtHolderInfo.Rows[looper]["JNT_NATIONALITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_NATIONALITY"].ToString();
drUnitHolderInfo["JNT_CITY"] = dtHolderInfo.Rows[looper]["JNT_CITY"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_CITY"].ToString();
drUnitHolderInfo["JNT_TEL_NO"] = dtHolderInfo.Rows[looper]["JNT_TEL_NO"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_TEL_NO"].ToString();
drUnitHolderInfo["JNT_FMH_REL"] = dtHolderInfo.Rows[looper]["JNT_FMH_REL"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["JNT_FMH_REL"].ToString();
drUnitHolderInfo["CIP"] = dtHolderInfo.Rows[looper]["CIP"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["CIP"].ToString();
if (!dtHolderInfo.Rows[looper]["CIP"].Equals(DBNull.Value))
{
if (string.Compare(dtHolderInfo.Rows[looper]["CIP"].ToString(), "Y", true) == 0)
{
drUnitHolderInfo["CIP"] = "YES";
}
else if (string.Compare(dtHolderInfo.Rows[looper]["CIP"].ToString(), "N", true) == 0)
{
drUnitHolderInfo["CIP"] = "NO";
}
}
if (!dtHolderInfo.Rows[looper]["ID_FLAG"].Equals(DBNull.Value))
{
if (string.Compare(dtHolderInfo.Rows[looper]["ID_FLAG"].ToString(), "Y", true) == 0)
{
drUnitHolderInfo["ID_FLAG"] = "YES";
drUnitHolderInfo["ID_AC"] = dtHolderInfo.Rows[looper]["ID_AC"].Equals(DBNull.Value) ? "" : dtHolderInfo.Rows[looper]["ID_AC"].ToString();
drUnitHolderInfo["ID_INTSTITUTE_NAME"] = reportObj.getBankNameByBankCode(Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_NM_CD"].ToString())).ToString();
drUnitHolderInfo["ID_INTSTITUTE_BRANCH_NAME"] = reportObj.getBankBranchNameByCode(Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[0]["ID_BK_BR_NM_CD"].Equals(DBNull.Value) ? "0" : dtHolderInfo.Rows[0]["ID_BK_BR_NM_CD"].ToString())).ToString();
}
else if (string.Compare(dtHolderInfo.Rows[looper]["ID_FLAG"].ToString(), "N", true) == 0)
{
drUnitHolderInfo["ID_FLAG"] = "NO";
}
}
if (!dtHolderInfo.Rows[looper]["BK_FLAG"].Equals(DBNull.Value))
{
if (string.Compare(dtHolderInfo.Rows[looper]["BK_FLAG"].ToString(), "Y", true) == 0)
{
if (!dtHolderInfo.Rows[looper]["BK_NM_CD"].Equals(DBNull.Value) && !dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].Equals(DBNull.Value) && !dtHolderInfo.Rows[looper]["BK_AC_NO"].Equals(DBNull.Value))
{
drUnitHolderInfo["BK_AC_NO"] = dtHolderInfo.Rows[looper]["BK_AC_NO"].ToString();
drUnitHolderInfo["BANK_NAME"] = reportObj.getBankNameByBankCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString())).ToString();
drUnitHolderInfo["BRANCH_NAME"] = reportObj.getBankBranchNameByCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].ToString())).ToString();
drUnitHolderInfo["BRANCH_ADDRESS"] = reportObj.getBankBranchAddressByCode(Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_NM_CD"].ToString()), Convert.ToInt16(dtHolderInfo.Rows[looper]["BK_BR_NM_CD"].ToString())).ToString();
}
else
{
string branchAddress = "";
string BankAccInfo = dtHolderInfo.Rows[looper]["SPEC_IN1"].ToString() + dtHolderInfo.Rows[looper]["SPEC_IN2"].ToString();
string[] BankAccountInfo = BankAccInfo.Split(',');
if (BankAccountInfo.Length > 0)
{
drUnitHolderInfo["BK_AC_NO"] = BankAccountInfo[0].ToString();
if (BankAccountInfo.Length > 1)
{
drUnitHolderInfo["BANK_NAME"] = BankAccountInfo[1].ToString();
}
if (BankAccountInfo.Length > 2)
{
drUnitHolderInfo["BRANCH_NAME"] = BankAccountInfo[2].ToString();
}
if (BankAccountInfo.Length > 3)
{
for (int loop = 3; loop < BankAccountInfo.Length; loop++)
{
branchAddress = branchAddress + BankAccountInfo[loop].ToString();
}
drUnitHolderInfo["BRANCH_ADDRESS"] = branchAddress;
}
}
}
}
}
DataTable dtNominee = reportObj.dtNominee(dtHolderInfo.Rows[looper]["REG_BK"].ToString(), dtHolderInfo.Rows[looper]["REG_BR"].ToString(), Convert.ToInt32(dtHolderInfo.Rows[looper]["R_NO"].ToString()));
if (dtNominee.Rows.Count > 0)
{
for (int loop = 0; loop < dtNominee.Rows.Count; loop++)
{
if (Convert.ToInt16(dtNominee.Rows[loop]["NOMI_NO"].ToString()) == 1)
{
drUnitHolderInfo["NOMI_CTL_NO"] = dtNominee.Rows[loop]["NOMI_CTL_NO"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CTL_NO"].ToString();
drUnitHolderInfo["NOMI1_NAME"] = dtNominee.Rows[loop]["NOMI_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NAME"].ToString();
drUnitHolderInfo["NOMI1_FMH_NAME"] = dtNominee.Rows[loop]["NOMI_FMH_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_FMH_NAME"].ToString();
drUnitHolderInfo["NOMI1_OCC_CODE"] = dtNominee.Rows[loop]["DESCR"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["DESCR"].ToString();
drUnitHolderInfo["NOMI1_ADDRS1"] = dtNominee.Rows[loop]["NOMI_ADDRS1"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS1"].ToString();
drUnitHolderInfo["NOMI1_ADDRS2"] = dtNominee.Rows[loop]["NOMI_ADDRS2"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS2"].ToString();
drUnitHolderInfo["NOMI1_CITY"] = dtNominee.Rows[loop]["NOMI_CITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CITY"].ToString();
drUnitHolderInfo["NOMI1_NATIONALITY"] = dtNominee.Rows[loop]["NOMI_NATIONALITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NATIONALITY"].ToString();
drUnitHolderInfo["NOMI1_NOMI_REL"] = dtNominee.Rows[loop]["NOMI_REL"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_REL"].ToString();
drUnitHolderInfo["NOMI1_PERCENTAGE"] = dtNominee.Rows[loop]["PERCENTAGE"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["PERCENTAGE"].ToString();
}
else if (Convert.ToInt16(dtNominee.Rows[loop]["NOMI_NO"].ToString()) == 2)
{
drUnitHolderInfo["NOMI2_NAME"] = dtNominee.Rows[loop]["NOMI_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NAME"].ToString();
drUnitHolderInfo["NOMI2_FMH_NAME"] = dtNominee.Rows[loop]["NOMI_FMH_NAME"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_FMH_NAME"].ToString();
drUnitHolderInfo["NOMI2_OCC_CODE"] = dtNominee.Rows[loop]["DESCR"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["DESCR"].ToString();
drUnitHolderInfo["NOMI2_ADDRS1"] = dtNominee.Rows[loop]["NOMI_ADDRS1"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS1"].ToString();
drUnitHolderInfo["NOMI2_ADDRS2"] = dtNominee.Rows[loop]["NOMI_ADDRS2"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_ADDRS2"].ToString();
drUnitHolderInfo["NOMI2_CITY"] = dtNominee.Rows[loop]["NOMI_CITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_CITY"].ToString();
drUnitHolderInfo["NOMI2_NATIONALITY"] = dtNominee.Rows[loop]["NOMI_NATIONALITY"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_NATIONALITY"].ToString();
drUnitHolderInfo["NOMI2_NOMI_REL"] = dtNominee.Rows[loop]["NOMI_REL"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["NOMI_REL"].ToString();
drUnitHolderInfo["NOMI2_PERCENTAGE"] = dtNominee.Rows[loop]["PERCENTAGE"].Equals(DBNull.Value) ? "" : dtNominee.Rows[loop]["PERCENTAGE"].ToString();
}
}
}
dtUnitHolderInfo.Rows.Add(drUnitHolderInfo);
}
// dtUnitHolderInfo.WriteXmlSchema(@"D:\Project\Web\AMCL.OPENMF\AMCL.Web\UI\ReportViewer\Report\dtUnitHolderInfo.xsd");
string Path = Server.MapPath("Report/rptRegHolderInfo.rpt");
rdoc.Load(Path);
rdoc.SetDataSource(dtUnitHolderInfo);
CrystalReportViewer1.ReportSource = rdoc;
rdoc.SetParameterValue("fundName", opendMFDAO.GetFundName(fundCode.ToString()));
rdoc = ReportFactory.GetReport(rdoc.GetType());
}
else
{
Response.Write("No data found");
}
}
private void FilterReport(string action)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/ftmandytdsellinperchannelreport.rpt");
_User Ousr = new _User(Session["username"].ToString());
string[] No_Filter_Users = { "ceo", "vpbsm", "vptfi", "ssgm", "ssm", "brd", "admin" };
string[] User_more_roles = { "6", "14", "20", "21", "23", "31", "36", "52", "62", "64", "65", "71", "72", "77", "79", "90" };
string orig_ids = string.Join(",", User_more_roles);
string tmp_ids = "";
DataTable user_counterid = SqlDbHelper.getDataDT("SELECT counterid FROM userheader WHERE username='******'");
foreach (DataRow ids in user_counterid.Rows)
{
tmp_ids = ids["counterid"].ToString();
}
string val_region = txt_region.Text.ToUpper();
if (val_region.Trim() != "")
{
try
{
ReportDocument ReportDoc = new ReportDocument();
ReportDoc.Load(foldername);
ReportDoc.SetDatabaseLogon("sa", "p@ssw0rd");
ParameterFields PFIELDS = ReportDoc.ParameterFields;
ParameterDiscreteValue[] param_values = new ParameterDiscreteValue[] { };
ParameterDiscreteValue region_default_val = new ParameterDiscreteValue();
region_default_val.Value = "C";
ParameterDiscreteValue channel_default_val = new ParameterDiscreteValue();
channel_default_val.Value = "All";
ParameterDiscreteValue brand_default_val = new ParameterDiscreteValue();
brand_default_val.Value = "All";
PFIELDS["Region"].CurrentValues.Clear();
PFIELDS["Channel"].CurrentValues.Clear();
PFIELDS["Brand"].CurrentValues.Clear();
if (action == "button_click" && (Ousr.HasPositionsOf(No_Filter_Users) || orig_ids.IndexOf(tmp_ids) != -1))
{
if (val_region == "LUZON")
{
region_default_val.Value = "CL";
}
else if (val_region == "VISMIN")
{
region_default_val.Value = "CV";
}
else
{
region_default_val.Value = "C";
}
}
else
{
ReportHelper.GetRegions(Session["username"].ToString(), "Region", ref PFIELDS, ref param_values);
foreach (ParameterDiscreteValue pd in PFIELDS["Region"].CurrentValues)
{
if (pd.Value.ToString() == "LUZON")
{
pd.Value = "CL";
}
if (pd.Value.ToString() == "VISMIN")
{
pd.Value = "CV";
}
}
}
ReportHelper.GetChannels(Session["username"].ToString(), "Channel", ref PFIELDS, ref param_values);
ReportHelper.GetBrands(Session["username"].ToString(), "Brand", ref PFIELDS, ref param_values);
bool has_region = false, has_channel = false, has_brand = false;
foreach (ParameterField pd in PFIELDS)
{
bool has_val = pd.CurrentValues.Count > 0 ? true : false;
if (has_val)
{
switch (pd.Name.ToUpper())
{
case "REGION": has_region = true; break;
case "CHANNEL": has_channel = true; break;
case "BRAND": has_brand = true; break;
}
}
}
if (!has_region)
{
ReportDoc.ParameterFields["Region"].CurrentValues.Add(region_default_val);
}
if (!has_channel)
{
ReportDoc.ParameterFields["Channel"].CurrentValues.Add(channel_default_val);
}
if (!has_brand)
{
ReportDoc.ParameterFields["Brand"].CurrentValues.Add(brand_default_val);
}
CrystalReportViewer1.ReportSource = ReportDoc;
}
catch (Exception ex)
{
}
}
}
public void ProductLabelReport()
{
if (ddlproduct.SelectedValue != "0")
{
int productid = Convert.ToInt32(ddlproduct.SelectedValue);
string strPattern = "([a-z?])[_ ]?([A-Z])";
DataTable dtAudit = new DataTable();
dtAudit.Columns.Add("KeyDataName", typeof(string));
dtAudit.Columns.Add("KeyDataValue", typeof(string));
dtAudit.Columns.Add("KeyDataDesc", typeof(string));
dtAudit.Columns.Add("DataName", typeof(string));
dtAudit.Columns.Add("DataType", typeof(string));
dtAudit.Columns.Add("ActionTaken", typeof(string));
dtAudit.Columns.Add("OldValue", typeof(string));
dtAudit.Columns.Add("NewValue", typeof(string));
dtAudit.Columns.Add("DateDone", typeof(string));
dtAudit.Columns.Add("DoneBy", typeof(string));
int nProdID = productid;//Product id
//Products Master Table
// DataTable dtAuLogGPLS = GISClass.AuditReport.ProductsGPLS(nProdID);
// DataTable dtAuLog = GISClass.AuditReport.AuProducts(nProdID);
DataTable dtAuLogGPLS = st_dll.ProductLabelGPLS(nProdID);
DataTable dtAuLog = st_dll.AuProductLabel(nProdID);
if (dtAuLog != null && dtAuLog.Rows.Count > 0)
{
for (int j = 0; j < dtAuLog.Columns.Count; j++)
{
if (j < dtAuLog.Columns.Count - 3)
{
if (dtAuLogGPLS.Rows[0][j].ToString() != dtAuLog.Rows[0][j].ToString())
{
string strType = dtAuLog.Columns[j].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "Product ID";
dR["KeyDataValue"] = dtAuLog.Rows[0]["ProductID"].ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataName"] = Regex.Replace(dtAuLog.Columns[j].ColumnName.ToString(), strPattern, "$1 $2");
dR["DataType"] = strType;
dR["OldValue"] = dtAuLog.Rows[0][j];
dR["NewValue"] = dtAuLogGPLS.Rows[0][j];
dR["ActionTaken"] = dtAuLog.Rows[0]["FileMaintCode"].ToString();
dR["Datedone"] = dtAuLog.Rows[0]["FileMaintDate"].ToString();
dR["DoneBy"] = dtAuLog.Rows[0]["FileMaintByID"].GetType() == typeof(string) ? st_dll.GetUserFullName(dtAuLog.Rows[0]["FileMaintByID"].ToString()) : st_dll.UserFullName(Convert.ToInt16(dtAuLog.Rows[0]["FileMaintByID"]));
dtAudit.Rows.Add(dR);
}
}
}
int k = 1;
for (int i = 0; i < dtAuLog.Rows.Count; i++)
{
if (k >= dtAuLog.Rows.Count)
{
break;
}
for (int j = 0; j < dtAuLog.Columns.Count; j++)
{
if (j < dtAuLog.Columns.Count - 3)
{
if (dtAuLog.Rows[i][j].ToString() != dtAuLog.Rows[k][j].ToString())
{
string strType = dtAuLog.Columns[j].DataType.ToString();
DataRow dR = dtAudit.NewRow();
dR["KeyDataName"] = "Product ID";
dR["KeyDataValue"] = dtAuLog.Rows[i]["ProductID"].ToString();
dR["KeyDataDesc"] = dtAuLogGPLS.Rows[0]["ProductDesc"].ToString();
dR["DataType"] = strType;
dR["DataName"] = Regex.Replace(dtAuLog.Columns[j].ColumnName.ToString(), strPattern, "$1 $2");
dR["OldValue"] = dtAuLog.Rows[k][j];
dR["NewValue"] = dtAuLog.Rows[i][j];
dR["ActionTaken"] = dtAuLog.Rows[k]["FileMaintCode"].ToString();
dR["Datedone"] = dtAuLog.Rows[k]["FileMaintDate"].ToString();
dR["DoneBy"] = dtAuLog.Rows[k]["FileMaintByID"].GetType() == typeof(string) ? st_dll.GetUserFullName(dtAuLog.Rows[k]["FileMaintByID"].ToString()) : st_dll.UserFullName(Convert.ToInt16(dtAuLog.Rows[k]["FileMaintByID"]));
dtAudit.Rows.Add(dR);
}
}
}
k++;
}
}
if (dtAudit == null || dtAudit.Rows.Count == 0)
{
ErrorMessage("No audit records found.");
return;
}
//AuditRpt rpt = new AuditRpt();
//rpt.rptName = "Audit Products";
//rpt.dt = dtAudit;
//rpt.WindowState = FormWindowState.Maximized;
//rpt.Show();
rptDoc = new ReportDocument();
CrystalReportViewer1.HasToggleGroupTreeButton = false;
CrystalReportViewer1.BestFitPage = false;
CrystalReportViewer1.Width = 920;
rptDoc.Load(Server.MapPath("~/Reports/AuditProducts.rpt"));
//rptDoc.SetParameterValue("@PRODUCTID", productid);
rptDoc.SetDataSource(dtAudit);
rptDoc.SetDatabaseLogon("sa", "Pass2018", "172.16.4.12", "PLSAudit");
rptDoc.DataSourceConnections[0].SetConnection("172.16.4.12", "PLSAudit", "sa", "Pass2018");
CrystalReportViewer1.Page.Title = "Audit ProductLabels";
rptDoc.SummaryInfo.ReportTitle = "Audit ProductLabels";
rptDoc.DataDefinition.FormulaFields["cTitle"].Text = "'Audit Trail : ProductLabels Master File'";
CrystalReportViewer1.ReportSource = rptDoc;
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
rptDoc.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat,
Response, true, "Audit ProductLabels");
CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None;
CrystalReportViewer1.SeparatePages = true;
CrystalReportViewer1.DataBind();
CrystalReportViewer1.HasExportButton = false;
Response.Flush();
Response.Close();
}
else
{
ErrorMessage("Please select the Product/Component.");
}
}
private bool GenerarPDFOC(string _codEmp, int _NroOrdenCompra, string Email)
{
try
{
StaCatalina.Forms.Reports _Reporte = new Reports();
ReportDocument objReport = new ReportDocument();
String reportPath = ConfigurationManager.AppSettings["Reports"] + "\\Reporting\\" + "OrdenDeCompra.rpt";
objReport.Load(reportPath);
objReport.Refresh();
objReport.ReportOptions.EnableSaveDataWithReport = false;
// PARAMETROS DE CONEXION
TableLogOnInfo logoninfo = new TableLogOnInfo();
logoninfo.ConnectionInfo.ServerName = ConfigurationManager.AppSettings["Source"];
logoninfo.ConnectionInfo.DatabaseName = ConfigurationManager.AppSettings["CatalogSTACATALINA"];
logoninfo.ConnectionInfo.UserID = ConfigurationManager.AppSettings["User ID"];
logoninfo.ConnectionInfo.Password = ConfigurationManager.AppSettings["Password"];
logoninfo.ConnectionInfo.IntegratedSecurity = false;
Tables tables = objReport.Database.Tables;
foreach (Table table in tables)
{
table.ApplyLogOnInfo(logoninfo);
}
//PAOS LOS PARAMTROS EN ORDEN
objReport.SetParameterValue(0, _codEmp);
objReport.SetParameterValue(1, "OC");
objReport.SetParameterValue(2, _NroOrdenCompra);
objReport.SetParameterValue(3, _NroOrdenCompra);
objReport.SetParameterValue(4, null);
objReport.SetParameterValue(5, null);
objReport.SetParameterValue(6, 1);
objReport.SetParameterValue(7, "");
objReport.SetParameterValue(8, null);
objReport.SetParameterValue(9, null);
//GUARDO EN FORMATO PDF
string _path = ConfigurationManager.AppSettings["PDF"] + "\\PDF\\" + "OrdenDeCompra.PDF";
objReport.ExportToDisk(ExportFormatType.PortableDocFormat, _path);
//_codEmp = (_codEmp == "EGES") ? "EGESAC S.A." : "RSC S.A.";
if (EnviarMail(_codEmp, _path, Email))
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
static void ExportToMSMail(string _codEmp, int _NroOrdenCompra)
{
StaCatalina.Forms.Reports _Reporte = new Reports();
ReportDocument objReport = new ReportDocument();
String reportPath = ConfigurationManager.AppSettings["Reports"] + "\\Reporting\\" + "OrdenDeCompra.rpt";
objReport.Load(reportPath);
objReport.Refresh();
objReport.ReportOptions.EnableSaveDataWithReport = false;
// PARAMETROS DE CONEXION
TableLogOnInfo logoninfo = new TableLogOnInfo();
logoninfo.ConnectionInfo.ServerName = ConfigurationManager.AppSettings["Source"];
logoninfo.ConnectionInfo.DatabaseName = ConfigurationManager.AppSettings["CatalogSTACATALINA"];
logoninfo.ConnectionInfo.UserID = ConfigurationManager.AppSettings["User ID"];
logoninfo.ConnectionInfo.Password = ConfigurationManager.AppSettings["Password"];
logoninfo.ConnectionInfo.IntegratedSecurity = false;
Tables tables = objReport.Database.Tables;
foreach (Table table in tables)
{
table.ApplyLogOnInfo(logoninfo);
}
//PAOS LOS PARAMTROS EN ORDEN
objReport.SetParameterValue(0, _codEmp);
objReport.SetParameterValue(1, "OC");
objReport.SetParameterValue(2, _NroOrdenCompra);
objReport.SetParameterValue(3, _NroOrdenCompra);
objReport.SetParameterValue(4, null);
objReport.SetParameterValue(5, null);
objReport.SetParameterValue(6, 1);
//PdfRtfWordFormatOptions pdfOpts = ExportOptions.CreatePdfRtfWordFormatOptions();
PdfFormatOptions pdfOpts = new PdfFormatOptions();
MicrosoftMailDestinationOptions mailOpts = ExportOptions.CreateMicrosoftMailDestinationOptions();
ExportOptions exportOpts = new ExportOptions();
pdfOpts.UsePageRange = false;
exportOpts.ExportFormatOptions = pdfOpts;
mailOpts.MailCCList = "*****@*****.**";
mailOpts.MailMessage = "PRUEBA DE CORREO OC HECTOR";
mailOpts.MailSubject = "PRUEBA";
mailOpts.MailToList = "*****@*****.**";
mailOpts.Password = "******";
mailOpts.UserName = "******";
exportOpts.ExportDestinationOptions = mailOpts;
exportOpts.ExportDestinationType = ExportDestinationType.MicrosoftMail;
exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat;
objReport.Export(exportOpts);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Session["ReportData"] != null && Session["option"] != null)
{
string rptFullPath = "";
if (Session["option"].ToString() == "radByMainLanes" || Session["option"].ToString() == "radByServiceLanes")
{
rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VMIDMAINT_DECI.RPT");
}
else if (Session["option"].ToString() == "radByIntersections")
{
rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VMIDIntSecMaint_Deci.RPT");
}
else if (Session["option"].ToString() == "radByRegionNo" && Session["details"].ToString() == "details")
{
rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\JPMMS_VSecst_Maint_deci_Subdist_udi.RPT");
}
else if (Session["option"].ToString() == "radByRegionNo" && Session["details"].ToString() != "details")
{
rptFullPath = Server.MapPath(@".\RptFiles\MD_Prio_cost\rptMaintDecisionRegionWise.rpt");
}
else
{
throw new Exception("Invalid report option!");
}
DataTable dt = (DataTable)Session["ReportData"];
ReportDocument rpt = new ReportDocument();
rpt.Load(rptFullPath);
rpt.SetDataSource(dt);
if (Session["option"].ToString() == "radByMainLanes")
{
if (!string.IsNullOrEmpty(Session["title"].ToString()))
{
rpt.SetParameterValue("Title", Session["title"].ToString());
}
else
{
rpt.SetParameterValue("Title", "");
}
}
Session.Remove("ReportData");
Session.Remove("title");
MemoryStream memStream;
Response.Buffer = false;
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
if (Request.QueryString["type"] == "x")
{
ExcelFormatOptions excelOptions = new ExcelFormatOptions();
excelOptions.ExcelUseConstantColumnWidth = false;
rpt.ExportOptions.FormatOptions = excelOptions;
memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.ExcelRecord);
Response.ContentType = "application/vnd.ms-excel";
}
else
{
memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.ContentType = "application/pdf";
//memStream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
//Response.ContentType = "application/msword";
}
Response.BinaryWrite(memStream.ToArray());
Response.End();
memStream.Flush();
memStream.Close();
memStream.Dispose();
rpt.Close();
rpt.Dispose();
GC.Collect();
}
else
{
Response.Redirect("MaintenanceDecisionsReport.aspx", false);
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
Session["ReportData"] = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
// since we are using Crystal to do everything, the web page is built here
if (!IsPostBack)
{
Session["dtPrint"] = null;
if (Session["UserName"] == null)
{
UserPrefix = "NoUser";
}
else
{
UserPrefix = Session["UserName"].ToString().Trim();
}
if ((Request.QueryString["InvoiceNo"] != null) || (Request.QueryString["OrderNo"] != null))
{
// clear out any previous exports first
DirectoryInfo OldPages = new DirectoryInfo(Server.MapPath(DocName));
if (OldPages.Exists)
{
// Get a reference to each file in that directory.
FileInfo[] AllOldPages = OldPages.GetFiles();
// go through the files
foreach (FileInfo OldPage in AllOldPages)
{
if (OldPage.Name.Substring(0, UserPrefix.Length) == UserPrefix)
{
OldPage.Delete();
}
}
}
// get the TEST watermark indicator
DataSet dsAppPref = new DataSet();
dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]",
new SqlParameter("@tableName", "SystemMaster with (NOLOCK)"),
new SqlParameter("@displayColumns", "PrintRqst"),
new SqlParameter("@whereCondition", " SystemMasterID = 0"));
PrintRequest = dsAppPref.Tables[0].Rows[0]["PrintRqst"].ToString().Trim();
// load the crystal report using the path in an app pref record
dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]",
new SqlParameter("@tableName", "AppPref with (NOLOCK)"),
new SqlParameter("@displayColumns", "AppOptionValue"),
new SqlParameter("@whereCondition", " (ApplicationCd = 'SOE') AND (AppOptionType = 'InvoiceLocation')"));
CrystalPath = dsAppPref.Tables[0].Rows[0]["AppOptionValue"].ToString();
RGRDoc.Load(CrystalPath + DocName + ".rpt", OpenReportMethod.OpenReportByTempCopy);
// set the database connections for the report
SqlConnectionStringBuilder ConnectBuilder = new SqlConnectionStringBuilder();
ConnectBuilder.ConnectionString = connectionString;
ConnectionInfo ConnectInfo = new ConnectionInfo();
ConnectInfo.DatabaseName = ConnectBuilder["Initial Catalog"].ToString();
ConnectInfo.UserID = ConnectBuilder["User Id"].ToString();
ConnectInfo.Password = ConnectBuilder["Password"].ToString();
ConnectInfo.ServerName = ConnectBuilder["Data Source"].ToString();
// AutoDataBind="true"
foreach (CrystalDecisions.CrystalReports.Engine.Table RGRTable in RGRDoc.Database.Tables)
{
TableLogOnInfo RGRTableLogonInfo = RGRTable.LogOnInfo;
RGRTableLogonInfo.ConnectionInfo = ConnectInfo;
RGRTable.ApplyLogOnInfo(RGRTableLogonInfo);
}
// set the selection criteria
if (Request.QueryString["InvoiceNo"] != null)
{
RGRDoc.RecordSelectionFormula = "{SOHeaderHist.InvoiceNo}=\"" + Request.QueryString["InvoiceNo"].ToString() + "\" and isnull({SODetailHist.DeleteDt})";
}
if (Request.QueryString["OrderNo"] != null)
{
RGRDoc.RecordSelectionFormula = "{SOHeaderHist.OrderNo}=" + Request.QueryString["OrderNo"].ToString() + " and isnull({SODetailHist.DeleteDt})";
}
// we are ready to go
RGRDoc.Refresh();
// Now set up the export
DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();
ExportOptions exportOpts = new ExportOptions();
exportOpts.ExportFormatType = ExportFormatType.HTML40;
exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;
exportOpts.ExportDestinationOptions = diskOpts;
HTMLFormatOptions htmlFormatOpts = new HTMLFormatOptions();
htmlFormatOpts.FirstPageNumber = 1;
htmlFormatOpts.HTMLEnableSeparatedPages = true;
htmlFormatOpts.HTMLHasPageNavigator = false;
htmlFormatOpts.HTMLBaseFolderName = Server.MapPath("");
htmlFormatOpts.HTMLFileName = UserPrefix + "InvoiceExport.htm";
exportOpts.ExportFormatOptions = htmlFormatOpts;
// set the parameters
if (PrintRequest == "T")
{
RGRDoc.SetParameterValue("IsTest", true);
}
else
{
RGRDoc.SetParameterValue("IsTest", false);
}
// Export the report to separate html files
RGRDoc.Export(exportOpts);
RGRDoc.Close();
// now that we have created the individual pages, make one big page
DirectoryInfo PageFiles = new DirectoryInfo(Server.MapPath(DocName));
// Get a reference to each file in that directory.
FileInfo[] AllPages = PageFiles.GetFiles();
// start the HTML page
Response.Write("<head>\n<title>RGR Document</title>\n");
if ((Request.QueryString["ScriptX"] != null) && (Request.QueryString["ScriptX"] == "YES"))
{
// Embed ScriptX objects
using (StreamReader sr = new StreamReader(Server.MapPath("common/include/ScriptX.inc")))
{
String incline;
// Read and display lines from the Spriptx include file
while ((incline = sr.ReadLine()) != null)
{
Response.Write(incline + "\n");
}
}
// Load ScriptX javascript function
Response.Write("<script src=\"" + ConfigurationManager.AppSettings["SOESiteURL"].ToString() + "Common/JavaScript/ScriptX.js\" type=\"text/javascript\"></script>\n");
}
Response.Write("</head>\n<body style=\"margin: 0px\" >\n");
PagePosition = (decimal)0.0;
foreach (FileInfo InvPage in AllPages)
{
// get only the files for the current user
if (InvPage.Name.Substring(0, UserPrefix.Length) == UserPrefix)
{
WriteOK = false;
Response.Write("<DIV style=\"position:absolute; top:" + PagePosition.ToString() + "pt; height:1000px \">\n");
//Response.Write("");
using (StreamReader sr = new StreamReader(InvPage.FullName))
{
String line;
// Read and display lines from the file until the end of
// the file is reached.
while ((line = sr.ReadLine()) != null)
{
if (line.Contains("</BODY>"))
{
WriteOK = false;
}
if (line.Contains("<style>"))
{
WriteOK = true;
}
if (line.Contains("class=\"crystalstyle\""))
{
line = line.Replace("31", "0");
}
if (line.Contains("images/"))
{
line = line.Replace("images/", ConfigurationManager.AppSettings["SOESiteURL"].ToString() + DocName + "/images/");
}
if (line.Contains("<DIV style=\"position:absolute; top:"))
{
WriteOK = false;
}
if (line.Contains("</TR></TABLE></CENTER></Div>"))
{
WriteOK = false;
}
// write the line
if (WriteOK)
{
Response.Write(line + "\n");
}
if (line.Contains("</style>"))
{
WriteOK = false;
}
if (line.Contains("<BODY"))
{
WriteOK = true;
}
}
}
Response.Write("</DIV>\n");
if (PagePosition == (decimal)0.0)
{
PagePosition += (decimal)757;
}
}
}
Response.Write("</body>\n");
if ((Request.QueryString["ScriptX"] != null) && (Request.QueryString["ScriptX"] == "YES"))
{
Response.Write("<script language=\"javascript\">\n");
Response.Write("SetPrintSettings(true, 0.25, 0.25, 0.25, 0.25);\n");
Response.Write("</script>\n");
}
Response.Write("</html>\n");
}
}
}
protected void CriaPDF()
{
Hashtable hash = (Hashtable)Session["HashProducaoMedicoRegulador"];
DSCabecalhoProducaoMedicoRegulador cabecalho = new DSCabecalhoProducaoMedicoRegulador();
cabecalho.Tables.Add((DataTable)hash["cabecalho"]);
DSRelatorioProducaoMedicoRegulador conteudo = new DSRelatorioProducaoMedicoRegulador();
conteudo.Tables.Add((DataTable)hash["corpo"]);
ReportDocument repDoc = new ReportDocument();
repDoc.Load(Server.MapPath("RelatoriosCrystal/CrystalReportViewer_RelatorioProducaoMedicoRegulador.rpt"));
repDoc.SetDataSource(conteudo.Tables[1]);
repDoc.Subreports["CrystalReportViewer_CabecalhoProducaoMedicoRegulador.rpt"].SetDataSource(cabecalho.Tables[1]);
//repDoc.Database.Tables["CabecalhoAgendaPrestador"].SetDataSource((DataTable)hash["cabecalho"]);
//repDoc.Database.Tables["RelatorioAgendaPrestador"].SetDataSource((DataTable)hash["corpo"]);
System.IO.Stream s = repDoc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "applicattion/octect-stream";
Response.AddHeader("Content-Disposition", "attachment;filename=RelatorioProducaoMedicosReguladores.pdf");
Response.AddHeader("Content-Length", s.Length.ToString());
Response.BinaryWrite(((System.IO.MemoryStream)s).ToArray());
Response.End();
}
