/// <summary>
/// this is used to generate the next ID auto .
/// </summary>
public void GetNextschemeID()
{
try
{
PartiesClass obj = new PartiesClass();
SqlDataReader SqlDtr = null;
#region Fetch Next scheme ID
dbobj.SelectQuery("Select max(sch_id)+1 from stktSchDiscount", ref SqlDtr);
if (SqlDtr.Read())
{
if (SqlDtr.GetValue(0).ToString() != null && SqlDtr.GetValue(0).ToString() != "")
{
lblschid.Text = SqlDtr.GetValue(0).ToString();
}
else
{
lblschid.Text = "600001";
}
}
else
{
lblschid.Text = "600001";
}
SqlDtr.Close();
#endregion
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:StockiestDiscountEntry.aspx,Class:PartiesClass.cs: Method:GetNextschemeID(). EXCEPTION " + ex.Message + " User " + uid);
}
}
/// <summary>
/// Put user code to initialize the page here
/// This method is used for setting the Session variable for userId and
/// after that filling the required dropdowns with database values
/// and also check accessing priviledges for particular user
/// and generate the next ID also.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
//string User_ID;
uid = (Session["User_Name"].ToString());
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:TaxEntry_Master.aspx,Method:pageLoad" + " EXCEPTION " + ex.Message + " userid " + uid);
Response.Redirect("../../Sysitem/ErrorPage.aspx", false);
return;
}
if (!IsPostBack)
{
checkPrivileges();
// The code below fills the combo with products names of type fuel.
System.Data.SqlClient.SqlDataReader rdr = null;
obj.SelectQuery("select Prod_Name from products where Category = 'Fuel'", ref rdr);
drp_pname.DataSource = rdr;
drp_pname.DataValueField = "Prod_Name";
drp_pname.DataBind();
drp_pname.Items.Insert(0, "Select");
obj.Dispose();
}
}
/// <summary>
/// This method is used to delete the partucular Employee record select from datagrid.
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
private void GridSearch_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
checkPrivileges();
if (Del_Flag == "0")
{
Response.Redirect("../../Sysitem/AccessDeny.aspx", false);
return;
}
SqlConnection sqlConn = new SqlConnection();
try
{
int Count = 0;
SqlDataReader rdr = null;
dbobj.SelectQuery("select count(*) from AccountsLedgerTable where Ledger_ID='" + e.Item.Cells[0].Text + "'", ref rdr);
if (rdr.Read())
{
Count = int.Parse(rdr.GetValue(0).ToString());
}
if (Count > 1)
{
MessageBox.Show("Please Remove The All Transaction Concerning Employee");
return;
}
string strCon = System.Configuration.ConfigurationSettings.AppSettings["Servosms"];
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "Delete from Employee Where Emp_ID='" + e.Item.Cells[1].Text + "'";
sqlConn.ConnectionString = strCon;
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.ExecuteNonQuery();
//*********
sqlConn.Close();
sqlCmd.Dispose();
sqlCmd.CommandText = "Delete from Ledger_Master Where Ledger_ID='" + e.Item.Cells[0].Text + "'";
sqlConn.ConnectionString = strCon;
sqlConn.Open();
sqlCmd.Connection = sqlConn;
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
sqlCmd.Dispose();
CreateLogFiles.ErrorLog("Form:EmployeeList.aspx,Class:Employee.cs,Method:GridSearch_DeleteCommand" + " Employee " + e.Item.Cells[1].Text + " IS DELETED " + " " + " USER ID " + uid);
MessageBox.Show("Employee Deleted");
Response.Redirect("Employee_List.aspx", false);
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:EmployeeList.aspx,Class:Employee.cs,Method:GridSearch_DeleteCommand" + " Employee " + e.Item.Cells[1].Text + " IS DELETED " + " " + ex.Message + " USERID " + uid);
}
}
/// <summary>
/// This is used to get the party name and city.
/// </summary>
public void getParties()
{
SqlDataReader SqlDtr = null;
dbobj.SelectQuery("Select Cust_Name+':'+City from Customer", ref SqlDtr);
DropCustomerName.Items.Clear();
DropCustomerName.Items.Add("Select");
while (SqlDtr.Read())
{
DropCustomerName.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
}
/// <summary>
/// This Method fetch the Vehcile No and ID and Fill the Vehicle No. Combo from Vehicleentry table.
/// </summary>
public void fillDriverDetails()
{
txtLicenseValidity.Text = System.DateTime.Now.Day + "/" + System.DateTime.Now.Month + "/" + System.DateTime.Now.Year;
txtLICvalidity.Text = System.DateTime.Now.Day + "/" + System.DateTime.Now.Month + "/" + System.DateTime.Now.Year;
DropVehicleNo.Items.Clear();
DropVehicleNo.Items.Add("Select");
SqlDataReader SqlDtr = null;
dbobj.SelectQuery("Select vehicle_no from vehicleentry", ref SqlDtr);
while (SqlDtr.Read())
{
DropVehicleNo.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
}
/// <summary>
/// This method is used for setting the Session variable for userId and
/// after that filling the required dropdowns with database values
/// and also check accessing priviledges for particular user
/// and generate the next ID also.
/// and also fatch the employee information according to select employee ID in comes from url.
/// </summary>
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
uid = (Session["User_Name"].ToString());
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:Employee_Update.aspx,Method:page_load" + " EXCEPTION " + ex.Message + uid);
Response.Redirect("../../Sysitem/ErrorPage.aspx", false);
return;
}
if (!Page.IsPostBack)
{
#region Check Privileges
int i;
string View_flag = "0", Add_Flag = "0", Edit_Flag = "0", Del_Flag = "0";
string Module = "2";
string SubModule = "2";
string[,] Priv = (string[, ])Session["Privileges"];
for (i = 0; i < Priv.GetLength(0); i++)
{
if (Priv[i, 0] == Module && Priv[i, 1] == SubModule)
{
View_flag = Priv[i, 2];
Add_Flag = Priv[i, 3];
Edit_Flag = Priv[i, 4];
Del_Flag = Priv[i, 5];
break;
}
}
if (View_flag == "0")
{
// string msg="UnAthourized Visit to Enployee Entry Page";
//dbobj.LogActivity(msg,Session["User_Name"].ToString());
Response.Redirect("../../Sysitem/AccessDeny.aspx", false);
return;
}
//************
if (Edit_Flag == "0")
{
btnUpdate.Enabled = false;
}
//************
#endregion
try
{
LblEmployeeID.Text = Request.QueryString.Get("ID");
MasterClass obj1 = new MasterClass();
EmployeeClass obj = new EmployeeClass();
SqlDataReader SqlDtr;
string sql;
getbeat();
#region Fetch Extra Cities,Designation,country and State from Database and add to the ComboBox
sql = "select distinct Country from Beat_Master";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
DropCountry.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
sql = "select distinct City from Beat_Master order by city";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
DropCity.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
string sql1;
sql1 = "select distinct State from Beat_Master";
SqlDtr = obj.GetRecordSet(sql1);
while (SqlDtr.Read())
{
DropState.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
DropVehicleNo.Items.Clear();
DropVehicleNo.Items.Add("Select");
SqlDtr = obj.GetRecordSet("Select vehicle_no from vehicleentry");
while (SqlDtr.Read())
{
DropVehicleNo.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
txtLicenseNo.Text = "";
txtLicenseValidity.Text = "";
txtLICNo.Text = "";
txtLICvalidity.Text = "";
DropVehicleNo.SelectedIndex = 0;
#endregion
SqlDtr = obj.EmployeeList(LblEmployeeID.Text.ToString(), "", "");
while (SqlDtr.Read())
{
lblName.Text = SqlDtr.GetValue(1).ToString();
TempEmpName.Text = SqlDtr.GetValue(1).ToString();
DropDesig.SelectedIndex = DropDesig.Items.IndexOf(DropDesig.Items.FindByValue(SqlDtr.GetValue(2).ToString()));
txtAddress.Text = SqlDtr.GetValue(3).ToString();
DropCity.SelectedIndex = DropCity.Items.IndexOf(DropCity.Items.FindByValue(SqlDtr.GetValue(4).ToString()));
DropState.SelectedIndex = DropState.Items.IndexOf(DropState.Items.FindByValue(SqlDtr.GetValue(5).ToString()));
// If the designation is driver then it shows the extra fields related to driver , else hide that fields.
if (SqlDtr.GetValue(2).ToString().Trim().Equals("Driver"))
{
lblDrLicense.Visible = true;
lblLicenseVali.Visible = true;
lblLICPolicy.Visible = true;
lblLICValid.Visible = true;
lblVehicleNo.Visible = true;
txtLicenseNo.Visible = true;
txtLicenseValidity.Visible = true;
txtLICNo.Visible = true;
txtLICvalidity.Visible = true;
DropVehicleNo.Visible = true;
}
else
{
lblDrLicense.Visible = false;
lblLicenseVali.Visible = false;
lblLICPolicy.Visible = false;
lblLICValid.Visible = false;
lblVehicleNo.Visible = false;
txtLicenseNo.Visible = false;
txtLicenseValidity.Visible = false;
txtLICNo.Visible = false;
txtLICvalidity.Visible = false;
DropVehicleNo.Visible = false;
}
DropCountry.SelectedIndex = DropCountry.Items.IndexOf(DropCountry.Items.FindByValue(SqlDtr.GetValue(6).ToString()));
txtContactNo.Text = SqlDtr.GetValue(7).ToString();
if (txtContactNo.Text == "0")
{
txtContactNo.Text = "";
}
txtMobile.Text = SqlDtr.GetValue(8).ToString();
if (txtMobile.Text == "0")
{
txtMobile.Text = "";
}
txtEMail.Text = SqlDtr.GetValue(9).ToString();
txtSalary.Text = SqlDtr.GetValue(10).ToString();
txtOT_Comp.Text = SqlDtr.GetValue(11).ToString();
txtLicenseNo.Text = SqlDtr.GetValue(12).ToString();
txtLicenseValidity.Text = GenUtil.str2DDMMYYYY(trimDate(SqlDtr.GetValue(13).ToString()));
txtLICNo.Text = SqlDtr.GetValue(14).ToString();
txtLICvalidity.Text = GenUtil.str2DDMMYYYY(trimDate(SqlDtr.GetValue(15).ToString()));
//Response.Write(SqlDtr.GetValue(16).ToString());
SqlDataReader rdr = null;
dbobj.SelectQuery("Select vehicle_no from vehicleentry where vehicledetail_id = " + SqlDtr.GetValue(16).ToString(), ref rdr);
if (rdr.Read())
{
//Response.Write(rdr.GetValue(0).ToString ());
DropVehicleNo.SelectedIndex = DropVehicleNo.Items.IndexOf(DropVehicleNo.Items.FindByValue(rdr.GetValue(0).ToString().Trim()));
}
rdr.Close();
rdr = null;
dbobj.SelectQuery("Select Op_Balance,Bal_Type from Ledger_Master where Ledger_Name = '" + SqlDtr.GetValue(1).ToString() + "'", ref rdr);
if (rdr.Read())
{
txtopbal.Text = rdr.GetValue(0).ToString();
DropType.SelectedIndex = DropVehicleNo.Items.IndexOf(DropVehicleNo.Items.FindByValue(rdr.GetValue(1).ToString().Trim()));
}
rdr.Close();
/********Add by vikas 27.10.2012*********************/
if (SqlDtr["Status"].ToString().Trim() != null && SqlDtr["Status"].ToString().Trim() != "")
{
if (SqlDtr["Status"].ToString().Trim() == "1")
{
RbtnActive.Checked = true;
}
else
{
RbtnNone.Checked = true;
}
}
else
{
RbtnActive.Checked = false;
RbtnNone.Checked = false;
}
/********End*********************/
}
SqlDtr.Close();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:Employee_Update.aspx,Method:Page_Load() " + "EmployeeID. EXCEPTION" + ex.Message + " userid " + uid);
}
}
}
/// <summary>
/// This is used to fetch the customers/scheme corrosponding to the FO/OeID.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void dropschid_SelectedIndexChanged(object sender, System.EventArgs e)
{
SqlConnection con;
SqlCommand cmd;
InventoryClass obj = new InventoryClass();
//SqlDataReader rdr2=null;
SqlDataReader rdr1 = null;
SqlDataReader rdr3 = null;
try
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
con.Open();
SqlDataReader SqlDtr;
//***
string scheme = dropschid.SelectedItem.Text.Trim().ToString();
string[] schid = scheme.Split(new char[] { ':' }, scheme.Length);
//****
cmd = new SqlCommand("select * from foe WHERE foid='" + schid[0] + "'", con);
SqlDtr = cmd.ExecuteReader();
ListEmpAssigned.Items.Clear();
Listprodassign.Items.Clear();
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
DropShiftID.SelectedIndex = (DropShiftID.Items.IndexOf((DropShiftID.Items.FindByValue(SqlDtr.GetValue(7).ToString()))));
dropdiscount.SelectedIndex = (dropdiscount.Items.IndexOf((dropdiscount.Items.FindByValue(SqlDtr.GetValue(4).ToString()))));
if (SqlDtr.GetValue(8).Equals("") || SqlDtr.GetValue(8).Equals("NULL"))
{
txtschname.Text = "";
}
else
{
txtschname.Text = SqlDtr.GetValue(8).ToString();
}
txtrs.Text = SqlDtr.GetValue(3).ToString();
txtDateFrom.Text = GenUtil.str2DDMMYYYY(GenUtil.trimDate(SqlDtr.GetValue(5).ToString()));
txtDateTo.Text = GenUtil.str2DDMMYYYY(GenUtil.trimDate(SqlDtr.GetValue(6).ToString()));
dbobj.SelectQuery("select cust_name+':'+cust_type from customer where cust_ID=" + SqlDtr.GetValue(1).ToString() + " ", ref rdr1);
if (rdr1.Read())
{
int x = ListEmpAssigned.Items.IndexOf((ListEmpAssigned.Items.FindByValue(rdr1.GetValue(0).ToString())));
//if(ListEmpAssigned.Items.Contains(rdr1.GetValue(0)))
if (x >= 0)
{
}
else
{
ListEmpAssigned.Items.Add(rdr1.GetValue(0).ToString());
}
}
//Checkfoe.Checked=true;
//Panel1.Visible=true;
int i = System.Convert.ToInt32(SqlDtr.GetValue(2).ToString());
if (i != 0)
{
Checkfoe.Checked = true;
Panel1.Visible = true;
dbobj.SelectQuery("select prod_name+':'+pack_type from products where prod_ID=" + SqlDtr.GetValue(2).ToString() + " ", ref rdr3);
if (rdr3.Read())
{
int y = Listprodassign.Items.IndexOf((Listprodassign.Items.FindByValue(rdr3.GetValue(0).ToString())));
if (y >= 0)
{
}
// if(Listprodassign.Items.Contains(rdr3.GetValue(0)))
// {}
else
{
Listprodassign.Items.Add(rdr3.GetValue(0).ToString());
}
}
//**dbobj.SelectQuery("select prod_Name, prod_Name + ':' + pack_type from products order by prod_Name", ref rdr3);
//**while(rdr3.Read())
//**{
//** Listprodavail.Items.Add(rdr3.GetValue(1).ToString());
//**}
btnall.Text = ">>";
}
else
{
Checkfoe.Checked = false;
Panel1.Visible = false;
//**dbobj.SelectQuery("select prod_Name, prod_Name + ':' + pack_type from products order by prod_Name", ref rdr3);
//**while(rdr3.Read())
//**{
//Listprodassign.Items.Add(rdr3.GetValue(1).ToString());
//** Listprodavail.Items.Add(rdr3.GetValue(1).ToString());
//***}
//Listprodavail.Items.Clear();
Listprodassign.Items.Clear();
btnall.Text = "<<";
}
}
}
rdr1.Close();
//rdr2.Close();
rdr3.Close();
dropschid.Visible = true;
btschid.Visible = false;
SqlDtr.Close();
con.Close();
CreateLogFiles.ErrorLog("Form:fleet/oe discountEntry.aspx,Method:dropschid_SelectedIndexChange, Userid= " + uid);
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:fleet/oe discountEntry.aspx,Method:dropschid_SelectedIndexChange" + " EXCEPTION " + ex.Message + "Userid= " + uid);
//MessageBox.Show(ex.Message);
}
}
//This is used to make report for printing with making a file.txt.
public void reportmaking()
{
/*
* ======================
* CREDIT BILL REPORT
* ======================
* M/s :
* Bill No :
* Bill Date :
+----------+-----+-------------------------+----+-------+---------+-------------+
|Inv.Date |Slip | Product Name |Qty | Rate | Amount | Vehicle.No |
+----------+-----+-------------------------+----+-------+---------+-------------+
* 18/7/2006 12345 1234567890123456789012345 1235 1234567 123456.00 1234567890123 DD/MM/YYYY XXX 1234567890123456789012345 123456.78 123456.00 1234567.00 xxxxxxxxxx
*/
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\CreditBillReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
string strDate = "";
string abc = DropCustID.SelectedItem.Text;
string billno = lblBillNo.Text;
string billdate = lblDate.Text;
//sql="select sm.invoice_no, slip_no, invoice_date, vehicle_no, prod_Name+ ' ' +Pack_Type Prod_Name, qty, rate, amount from sales_master sm, sales_details sd, products p where sm.invoice_date between '"+ ToMMddYYYY(txtDateFrom.Text) +"' and dateadd(day,1,'"+ ToMMddYYYY(txtDateTO.Text) + "') and sm.cust_id in ( select cust_id from customer where cust_name=substring('"+ DropCustID.SelectedItem.Value +"',1,charindex(':','"+ DropCustID.SelectedItem.Value +"')-1) and city=substring('"+ DropCustID.SelectedItem.Value +"',charindex(':','"+ DropCustID.SelectedItem.Value +"')+1,len('"+ DropCustID.SelectedItem.Value +"'))) and sm.invoice_no = sd.invoice_no and sd.prod_id = p.prod_id";
if (DropVehicleNo.SelectedIndex == 0)
{
sql = "select sm.invoice_no, slip_no, invoice_date, vehicle_no, prod_Name+ ' ' +Pack_Type Prod_Name, qty, rate, amount from sales_master sm, sales_details sd, products p where sm.invoice_date between '" + ToMMddYYYY(txtDateFrom.Text) + "' and dateadd(day,1,'" + ToMMddYYYY(txtDateTO.Text) + "') and sm.cust_id in ( select cust_id from customer where cust_name=substring('" + DropCustID.SelectedItem.Value + "',1,charindex(':','" + DropCustID.SelectedItem.Value + "')-1) and city=substring('" + DropCustID.SelectedItem.Value + "',charindex(':','" + DropCustID.SelectedItem.Value + "')+1,len('" + DropCustID.SelectedItem.Value + "'))) and sm.sales_type = 'credit' and sm.invoice_no = sd.invoice_no and sd.prod_id = p.prod_id";
}
else
{
sql = "select sm.invoice_no, slip_no, invoice_date, vehicle_no, prod_Name+ ' ' +Pack_Type Prod_Name, qty, rate, amount from sales_master sm, sales_details sd, products p where sm.invoice_date between '" + ToMMddYYYY(txtDateFrom.Text) + "' and dateadd(day,1,'" + ToMMddYYYY(txtDateTO.Text) + "') and sm.cust_id in ( select cust_id from customer where cust_name=substring('" + DropCustID.SelectedItem.Value + "',1,charindex(':','" + DropCustID.SelectedItem.Value + "')-1) and city=substring('" + DropCustID.SelectedItem.Value + "',charindex(':','" + DropCustID.SelectedItem.Value + "')+1,len('" + DropCustID.SelectedItem.Value + "'))) and sm.sales_type = 'credit' and sm.invoice_no = sd.invoice_no and sd.prod_id = p.prod_id and Vehicle_No = '" + DropVehicleNo.SelectedItem.Text + "'";
}
dbobj.SelectQuery(sql, ref rdr);
// Condensed
sw.Write((char)27);
sw.Write((char)15);
sw.WriteLine(" ======================");
sw.WriteLine(" CREDIT BILL REPORT ");
sw.WriteLine(" ====================== ");
sw.WriteLine("M/s : " + abc);
sw.WriteLine("Bill No : " + billno);
sw.WriteLine("Bill Date : " + billdate);
sw.WriteLine("+----------+--------+------------------------------+-----+--------+----------+--------------+");
sw.WriteLine("|Inv.Date |Slip No | Product Name | Qty | Rate | Amount | Vehicle.No |");
sw.WriteLine("+----------+--------+------------------------------+-----+--------+----------+--------------+");
// 18/7/2006 12345678 123456789012345678901234567890 12345 12345678 1234567.00 1234567890123
if (rdr.HasRows)
{
info = " {0,-10:S} {1,8:S} {2,-30:S} {3,5:F} {4,8:F} {5,10:F} {6,-14:S}";
while (rdr.Read())
{
// Trim Date
strDate = rdr["invoice_date"].ToString().Trim();
int pos = strDate.IndexOf(" ");
if (pos != -1)
{
strDate = strDate.Substring(0, pos);
}
else
{
strDate = "";
}
sw.WriteLine(info, GenUtil.str2DDMMYYYY(strDate),
rdr["Slip_no"].ToString().Trim(),
strTrim(rdr["prod_Name"].ToString()),
rdr["qty"].ToString().Trim(),
rdr["rate"].ToString().Trim(),
GenUtil.strNumericFormat(rdr["amount"].ToString().Trim()),
rdr["Vehicle_no"].ToString().Trim());
}
}
sw.WriteLine("+----------+--------+------------------------------+-----+--------+----------+--------------+");
// deselect Condensed
sw.Write((char)18);
sw.Write((char)12);
dbobj.Dispose();
sw.Close();
rdr.Close();
}