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)
{
if (cbCompanyName.SelectedIndex == 0)
{
MessageBox.Show("Please Select Company");
return;
}
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 CompanyName = '" + cbCompanyName.SelectedItem.ToString() + "' and Date between'" + dtpfromdate.Value.ToString("yyyy-MM-dd") + "' AND '" + dtptodate.Value.ToString("yyyy-MM-dd") + "' order by Date";
}
else
{
qry = "select * FROM CollectionMaster WHERE CompanyName = '" + cbCompanyName.SelectedItem.ToString() + "' and 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.COLLECTION);
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());
//cryRptDoc.SetParameterValue("Name", cbCompanyName.SelectedItem);
crvcollection.ReportSource = cryRptDoc;
}
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 bAdd_Click(object sender, EventArgs e)
{
try
{
int busid = General.GetBusID(cmbBusName.Text, General.BUS);
int type = General.GetBusID(cmbScheduleType.Text, General.SCHEDULETYPE);
int day = Convert.ToInt32(tbDay.Text);
int month = Convert.ToInt32(tbMonth.Text);
int year = Convert.ToInt32(tbYear.Text);
int recurring = 1;
int reminderday = Convert.ToInt32(tbRemind.Text);
DateTime olddate = Convert.ToDateTime(dateTimePicker1.Text);
DateTime lastdate = olddate;
string format = "";
if (rbDay.Checked)
{
lastdate = lastdate.AddDays(Convert.ToInt32(tbDay.Text));
format = General.DAY;
}
else if (rbMonth.Checked)
{
lastdate = lastdate.AddMonths(Convert.ToInt32(tbMonth.Text));
format = General.MONTH;
}
else if (rbYear.Checked)
{
lastdate = lastdate.AddYears(Convert.ToInt32(tbYear.Text));
format = General.YEAR;
}
DateTime reminderdate = lastdate.AddDays((-1 * Convert.ToDouble(tbRemind.Text)));
string qry = "INSERT INTO ScheduleMaster VALUES( ";
qry += busid + ",";
qry += type + ",";
qry += day + ",";
qry += month + ",";
qry += year + ",";
qry += recurring + ",";
qry += reminderday + ",";
qry += "'" + olddate.ToString("yyyy-MM-dd") + "',";
qry += "'" + lastdate.ToString("yyyy-MM-dd") + "',";
qry += "'" + reminderdate.ToString("yyyy-MM-dd") + "',";
qry += "'" + format + "')";
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show(General.SUCCESS_MSG);
BindDetails(1);
ClearResult();
this.SelectNextControl(cmbBusName, true, true, true, true);
}
catch (Exception ex)
{
if (ex.Message.IndexOf("IX_ScheduleMaster") > 0)
{
MessageBox.Show("Data Already Exists");
}
}
}