private void btcreate_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "";
//qry = "select * FROM CollectionMaster WHERE Date between'" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' AND BusName='" + cbbusname.SelectedItem.ToString() + "' order by Date";
/*qry = "select BusName,sum(convert(decimal(7,2),Collection)) Collection,sum(convert(decimal(7,2),DAmount)) DAmount,sum(convert(decimal(7,2),Expenses)) Expenses,";
* qry = qry + "(select sum(convert(decimal(7,2),mem.Amount))";
* qry = qry + "from MonthlyExpenseMaster mem,BusMaster bm ";
* qry = qry + "where bm.Id=mem.BusName and bm.Name=cm.BusName and mem.ExpenseType='1') Bank_Due,";
* qry = qry + "(select sum(convert(decimal(7,2),mem.Amount))";
* qry = qry + "from MonthlyExpenseMaster mem,BusMaster bm ";
* qry = qry + "where bm.Id=mem.BusName and bm.Name=cm.BusName and mem.ExpenseType='2') Toll_Fee,";
* qry = qry + "(select sum(convert(decimal(7,2),mem.Amount))";
* qry = qry + "from MonthlyExpenseMaster mem,BusMaster bm ";
* qry = qry + "where bm.Id=mem.BusName and bm.Name=cm.BusName and mem.ExpenseType='3') Workshop_Exp,";
* qry = qry + "(select sum(convert(decimal(7,2),mem.Amount))";
* qry = qry + "from MonthlyExpenseMaster mem,BusMaster bm ";
* qry = qry + "where bm.Id=mem.BusName and bm.Name=cm.BusName and mem.ExpenseType='4') Other_Exp ";
* qry = qry + "from CollectionMaster cm ";
* qry = qry + "where Date between '" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' and '" + dtptodate.Value.ToString("yyyy-MM-dd") + "'";
* qry = qry + "group by BusName"; */
qry = "up_GetMonthlyReport @StartDate='" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "',@endDate='" + dtptodate.Value.ToString("yyyy-MM-dd") + "'";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "SELECT * FROM MonthlyReport";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.MONTHLYREPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
crvcollection.ReportSource = cryRptDoc;
//cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
}
private void btcreate_Click(object sender, EventArgs e)
{
if (cbmaintance.SelectedIndex == 0)
{
MessageBox.Show("Select Maintanence");
}
else
{
String selectedItem = cbmaintance.SelectedItem.ToString();
if (selectedItem == "Tyre Service")
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "TyreReport '" + cbbusname.Text + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "' ";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.TYREREPORT);
//string reportpath = @"E:\Live Projects\Bus Double Wages and Bata 11092015\BUS\BUS\Report\NewTyreReport.rpt";
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
cryRptDoc.SetParameterValue("BusName", cbbusname.Text);
crvbus.ReportSource = cryRptDoc;
}
else if (selectedItem == "Oil Service")
{
try
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "OilReport '" + cbbusname.SelectedItem.ToString() + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "'";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.OILSERVICE);
//string reportpath = @"E:\Karthick\Bus\Erode backup\BUS V2.0\BUS\BUS\Report\NewOilServiceReport.rpt";
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
//cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
//cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
//cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
crvbus.ReportSource = cryRptDoc;
}
catch (Exception e1)
{
throw (e1);
}
}
else if (selectedItem == "Break Service")
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "BreakReport '" + cbbusname.SelectedItem.ToString() + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "'";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.BREAKREPORT);
//string reportpath = @"E:\Karthick\Bus\Erode backup\BUS V2.0\BUS\BUS\Report\NewBreakServiceReport.rpt";
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
crvbus.ReportSource = cryRptDoc;
}
else
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "ClutchReport '" + cbbusname.SelectedItem.ToString() + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "'";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.CLUTCHREPORT);
//string reportpath = @"E:\Karthick\Bus\Erode backup\BUS V2.0\BUS\BUS\Report\NewCluthchServiceReport.rpt";
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
crvbus.ReportSource = cryRptDoc;
}
}
}
private void btcreate_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "";
if (cbbusname.Text == "__All__")
{
qry = "select * FROM CollectionMaster WHERE Date between'" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' order by Date";
}
else
{
qry = "select * FROM CollectionMaster WHERE Date between'" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' AND BusName='" + cbbusname.SelectedItem.ToString() + "' order by Date";
}
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.EXPORTWAGES_REPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
crvcollection.ReportSource = cryRptDoc;
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
}
private void btnLoad_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "procGetExpenseTypeReport '" + cbCompany.SelectedValue + "','" + cbBus.SelectedValue + "','" + cbExpenseType.SelectedValue + "','" + dtpFromDate.Value.ToString("yyyy-MM-dd") + "','" + dtpToDate.Value.ToString("yyyy-MM-dd") + "' ";
// string qry = "select * FROM CollectionMaster WHERE Date between '" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' AND Driver='" + cbdrivername.SelectedItem.ToString() + "' order by Date";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.EXPENSETYPEREPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("Company", cbCompany.Text);
cryRptDoc.SetParameterValue("BusName", cbBus.Text);
cryRptDoc.SetParameterValue("ExpenseName", cbExpenseType.Text);
rptViewer.ReportSource = cryRptDoc;
}
private void btcreate_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "";
//string date = DateTime.Today.ToString("dd/MM/yyyy");
qry = "select * FROM CollectionMaster WHERE Date='" + dtpdate.Value.ToString("yyyy-MM-dd") + "' order by Date";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
DataTable dt = new DataTable();
dt.Columns.Add("Date");
dt.Columns.Add("Driver1");
dt.Columns.Add("Conductor1");
dt.Columns.Add("BusName");
dt.Columns.Add("Collection");
dt.Columns.Add("DLit");
dt.Columns.Add("DAmount");
dt.Columns.Add("Bata");
dt.Columns.Add("Wages");
dt.Columns.Add("Cleaner");
dt.Columns.Add("Stand");
dt.Columns.Add("Gtc");
dt.Columns.Add("Pooja");
dt.Columns.Add("Extra");
dt.Columns.Add("Greese");
dt.Columns.Add("Sparse");
dt.Columns.Add("Expenses");
dt.Columns.Add("Total");
dt.Columns.Add("NetTotal");
int length = ds.Tables[0].Rows.Count;
string FromDate = "";
string ToDate = "";
string BusName = "";
for (int i = 0; i < length; i++)
{
DataRow dr = dt.NewRow();
//dr["ID"] = ds.Tables[0].Rows[i]["ID"].ToString();
//dr["BusName"] = ds.Tables[0].Rows[i]["BusName"].ToString();
//dr["Total"] = ds.Tables[0].Rows[i]["Total"].ToString();
dr["Date"] = ds.Tables[0].Rows[i]["Date"].ToString().Substring(0, 11);
dr["Driver1"] = ds.Tables[0].Rows[i]["Driver1"].ToString();
dr["Conductor1"] = ds.Tables[0].Rows[i]["Conductor1"].ToString();
dr["BusName"] = ds.Tables[0].Rows[i]["BusName"].ToString();
dr["Collection"] = ds.Tables[0].Rows[i]["Collection"].ToString();
dr["DLit"] = ds.Tables[0].Rows[i]["DLit"].ToString();
dr["DAmount"] = ds.Tables[0].Rows[i]["DAmount"].ToString();
dr["Bata"] = ds.Tables[0].Rows[i]["Bata"].ToString();
dr["Wages"] = ds.Tables[0].Rows[i]["Wages"].ToString();
dr["Cleaner"] = ds.Tables[0].Rows[i]["Cleaner"].ToString();
dr["Stand"] = ds.Tables[0].Rows[i]["Stand"].ToString();
dr["Gtc"] = ds.Tables[0].Rows[i]["Gtc"].ToString();
dr["Pooja"] = ds.Tables[0].Rows[i]["Pooja"].ToString();
dr["Extra"] = ds.Tables[0].Rows[i]["Extra"].ToString();
dr["Greese"] = ds.Tables[0].Rows[i]["Greese"].ToString();
dr["Sparse"] = ds.Tables[0].Rows[i]["Sparse"].ToString();
dr["Expenses"] = ds.Tables[0].Rows[i]["Expenses"].ToString();
dr["Total"] = ds.Tables[0].Rows[i]["Total"].ToString();
//dr["ID"] = ds.Tables[0].Rows[i]["ID"].ToString();
BusName = ds.Tables[0].Rows[i]["BusName"].ToString();
//FromDate = DateTime.Now.ToString("yyyy")+"-"+ DateTime.Now.ToString("MM") +"-"+"01";
FromDate = dtpdate.Value.ToString("yyyy") + "-" + dtpdate.Value.ToString("MM") + "-" + "01";
//ToDate = DateTime.Today.AddDays(-1).ToString("dd/MM/yyyy");
DateTime todate = dtpdate.Value;
//ToDate = todate.AddDays(-1).ToString("yyyy-MM-dd");
ToDate = todate.ToString("yyyy-MM-dd");
string NetTotal = General.GetNetTotal(BusName, FromDate, ToDate).ToString();
dr["NetTotal"] = NetTotal;
dt.Rows.Add(dr);
}
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.DAYCOLLECTION);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(dt.DefaultView);
crvcollection.ReportSource = cryRptDoc;
//cryRptDoc.SetParameterValue("BusName", cbbusname.SelectedItem.ToString());
}
private void btcreate_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "select * FROM CollectionMaster WHERE Date between'" + dtpfromdate.Value.ToString("yyyy-MM-dd") +
"' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") +
"' AND BusName='" + cbbusname.SelectedItem.ToString() +
"'AND Trip='" + cbtrip.Text.ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
//BusCrystalReport objRpt = new BusCrystalReport();
//objRpt.Parameter_FromDate.PromptText=dtptodate.Text;
//objRpt.Parameter_ToDate.PromptText = dtptodate.Text;
//objRpt.SetDataSource(ds.Tables[0]);
//crvbus.ReportSource = objRpt;
//crvbus.Refresh();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.BUSTRIPREPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
// cryRptDoc.SetParameterValue("Trip", cbtrip.Text);
crvbus.ReportSource = cryRptDoc;
// crvbus.PrintReport();
}
private void btnLoad_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "procGetProfitReport '" + cbBus.SelectedValue + "','" + dtpFromDate.Value.ToString("yyyy-MM-dd") + "','" + dtpToDate.Value.ToString("yyyy-MM-dd") + "' ";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.PROFIT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("BusName", cbBus.Text);
rptViewer.ReportSource = cryRptDoc;
}
private void btnCreateRpt_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "procSalary '" + cbEmployee.SelectedValue + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "' ";
// string qry = "select * FROM CollectionMaster WHERE Date between '" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' AND Driver='" + cbdrivername.SelectedItem.ToString() + "' order by Date";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
//DriverCrystalReport objRpt = new DriverCrystalReport();
//objRpt.SetDataSource(ds.Tables[0]);
//crvdriver.ReportSource = objRpt;
//crvdriver.Refresh();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.SALARY);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("emp", cbEmployee.Text);
//cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
rptviewer.ReportSource = cryRptDoc;
}
private void btnReport_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry = "Get_SUpplierWiseReport '" + cbSupplier.SelectedValue + "','" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "','" + dtptodate.Value.ToString("yyyy-MM-dd") + "' ";
// string qry = "select * FROM CollectionMaster WHERE Date between '" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' AND Driver='" + cbdrivername.SelectedItem.ToString() + "' order by Date";
SqlDataAdapter da = new SqlDataAdapter(qry, conn);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.SUPPLIERWISEREPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
//cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Text);
//cryRptDoc.SetParameterValue("ToDate", dtptodate.Text);
rptViewer.ReportSource = cryRptDoc;
}
private void btcreate_Click(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
//int busid = General.GetBusID(cmbBusName.Text, General.BUS);
conn.Open();
string qry1 = "";
//qry1 = "SELECT LastDate,ReminderDate,ScheduleType+'-'+Name+'-'+RegNo AS Name,Color FROM ScheduleMaster S INNER JOIN BusMaster B ON S.BusMaster_ID = B.ID INNER JOIN ScheduleType ST On ST.ID = S.Type_ID";
qry1 = "SELECT RegNo AS RegNo,Name As BusName,Owner As Owner,ScheduleType As ScheduleType,LastDate As Lastdate FROM ScheduleMaster S INNER JOIN BusMaster B ON S.BusMaster_ID = B.ID INNER JOIN ScheduleType ST On ST.ID = S.Type_ID WHERE Lastdate between '" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' order by Lastdate";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(qry1, conn);
da.Fill(ds);
conn.Close();
ReportDocument cryRptDoc = new ReportDocument();
string reportpath = Path.GetDirectoryName(Application.ExecutablePath).ToString() + General.GetReportPath(General.SCHEDULEFULLREPORT);
cryRptDoc.Load(reportpath);
cryRptDoc.SetDataSource(ds.Tables[0].DefaultView);
cryRptDoc.SetParameterValue("FromDate", dtpfromdate.Value.ToString("dd-MM-yyyy"));
cryRptDoc.SetParameterValue("ToDate", dtptodate.Value.ToString("dd-MM-yyyy"));
crvschedule.ReportSource = cryRptDoc;
}
