/// <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(); }