public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() { rptscustomerst rpt = new rptscustomerst(); rpt.Site = this.Site; return(rpt); }
private void button1ok_Click(object sender, EventArgs e) { try { Cursor.Current = Cursors.WaitCursor; SqlCommand cmd = new SqlCommand(); SqlConnection myConnection = default(SqlConnection); SqlDataAdapter myDA = new SqlDataAdapter(); DataSet myDS = new DataSet(); myConnection = new SqlConnection(cs.DBConn); cmd.Connection = myConnection; myConnection.Open(); // coolect all previous balances........................................................from both debit and credit....... string cmdStringbunit1 = "SELECT sum(FDebit) as mysum from TFinancials " + " where FDate<=@a4 and CID=@a3 "; SqlCommand sqlCommandbunit1 = new SqlCommand(cmdStringbunit1, myConnection); sqlCommandbunit1.Parameters.AddWithValue("@a3", SqlDbType.NVarChar).Value = comboBox1acct.SelectedValue; sqlCommandbunit1.Parameters.AddWithValue("@a4", SqlDbType.NVarChar).Value = dateTimePicker1.Value.Date.AddDays(-1); //sqlCommandbunit1.Parameters.AddWithValue("@a4", SqlDbType.NVarChar).Value = dateTimePicker4.Value.Date; SqlDataReader comp2 = sqlCommandbunit1.ExecuteReader(); comp2.Read(); string deb = comp2[0].ToString(); decimal deb1 = 0; if (deb != "") { deb1 = Convert.ToDecimal(deb); } comp2.Close(); string cmdStringbunit12 = "SELECT sum(FCredit) as mysum from TFinancials " + " where FDate<=@a4 and CID=@a3"; sqlCommandbunit1 = new SqlCommand(cmdStringbunit12, myConnection); //sqlCommandbunit1.Parameters.AddWithValue("@a1", SqlDbType.Int).Value = Convert.ToInt16(val); sqlCommandbunit1.Parameters.AddWithValue("@a3", SqlDbType.NVarChar).Value = comboBox1acct.SelectedValue; sqlCommandbunit1.Parameters.AddWithValue("@a4", SqlDbType.NVarChar).Value = dateTimePicker1.Value.Date.AddDays(-1); comp2 = sqlCommandbunit1.ExecuteReader(); comp2.Read(); string deb2 = comp2[0].ToString(); decimal creditdeb12 = 0; if (deb2 != "") { creditdeb12 = Convert.ToDecimal(deb2); } comp2.Close(); //now add the result to the next query string cmdStrinzz = "SELECT Customers.Customer_name, Customers.Phone, Customers.Address, TFinancials.Particular," + " TFinancials.FCredit, TFinancials.FDate, TFinancials.FDebit,TFinancials.cid," + deb1 + " as debts," + creditdeb12 + " as credits " + " FROM Customers INNER JOIN " + " TFinancials ON Customers.Cus_id = TFinancials.CID where " + " (TFinancials.FDate >= @a2) AND (TFinancials.FDate <= @a3) and (TFinancials.cid = @a1) "; cmd = new SqlCommand(cmdStrinzz, myConnection); cmd.Parameters.AddWithValue("@a2", SqlDbType.Date).Value = (dateTimePicker1.Value.Date); cmd.Parameters.AddWithValue("@a3", SqlDbType.Date).Value = (dateTimePicker2.Value.Date); cmd.Parameters.AddWithValue("@a1", SqlDbType.NVarChar).Value = comboBox1acct.SelectedValue; myDA.SelectCommand = cmd; myDA.Fill(myDS, "S_And_C_statment"); rptscustomerst rpt3 = new rptscustomerst(); rpt3.SetDataSource(myDS); ParameterFieldDefinitions crParameterFieldDefinitions; ParameterFieldDefinition crParameterFieldDefinition; ParameterValues crParameterValues = new ParameterValues(); ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue(); crParameterDiscreteValue.Value = "BIG LTD Customer/Supplier Statement " + Environment.NewLine + " For the period of " + dateTimePicker1.Value.ToShortDateString() + " To " + dateTimePicker2.Value.ToShortDateString(); crParameterFieldDefinitions = rpt3.DataDefinition.ParameterFields; crParameterFieldDefinition = crParameterFieldDefinitions["My Parameter"]; crParameterValues = crParameterFieldDefinition.CurrentValues; crParameterValues.Clear(); crParameterValues.Add(crParameterDiscreteValue); crParameterFieldDefinition.ApplyCurrentValues(crParameterValues); //frmcashsummary frm2 = new frmcashsummary(); crystalReportViewer1.ReportSource = rpt3; //frm2.ShowDialog(); myConnection.Close(); Cursor = Cursors.Default; } catch (Exception ex) { Cursor.Current = Cursors.Default; MessageBox.Show("Error " + ex); } }