/// <summary>
/// this method is used to fill the customer with customer type in a list.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DropShiftID_SelectedIndexChanged(object sender, System.EventArgs e)
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr = null;
string sql = "";
if (DropShiftID.SelectedIndex == 0)
{
sql = "select cust_Name, cust_Name + ':' + cust_type from customer where cust_type like'OE%' order by cust_Name";
SqlDtr = obj.GetRecordSet(sql);
ListEmpAvailable.Items.Clear();
while (SqlDtr.Read())
{
ListEmpAvailable.Items.Add(SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
}
else
{
sql = "select cust_Name, cust_Name + ':' + cust_type from customer where cust_type like'Fleet%' order by cust_Name";
SqlDtr = obj.GetRecordSet(sql);
ListEmpAvailable.Items.Clear();
while (SqlDtr.Read())
{
ListEmpAvailable.Items.Add(SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
}
}
public IHttpActionResult FetchCustomerNames()
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
ArrayList CustName = new ArrayList();
sql = "Select Cust_Name,city from Customer";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
CustName.Add(SqlDtr.GetValue(0).ToString() + ";" + SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
sql = "Select Ledger_Name+':'+cast(Ledger_ID as varchar) Ledger_Name from Ledger_Master lm,Ledger_master_sub_grp lmsg where lm.sub_grp_id = lmsg.sub_grp_id and lmsg.sub_grp_name not like 'Bank%' and lmsg.sub_grp_name != 'Cash in hand' and lmsg.sub_grp_name not like 'Discount%' and lmsg.sub_grp_name != 'Sundry Debtors' Order by Ledger_Name";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
CustName.Add(SqlDtr.GetValue(0).ToString() + "; ");
}
SqlDtr.Close();
sql = "Select Ledger_Name+':'+cast(Ledger_ID as varchar) Ledger_Name,city from Ledger_Master, Employee where Emp_Name=Ledger_Name Order by Ledger_Name";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
CustName.Add(SqlDtr.GetValue(0).ToString() + ";" + SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
CustName.Sort();
if (CustName == null || CustName.Count == 0)
{
return(Content(HttpStatusCode.NotFound, "Customer Names Not Found"));
}
return(Ok(CustName));
}
/// <summary>
/// This method is used to fatch the ssr information from employee table and also fatch the
/// city from beat Master table and fill into the dropdownlist.
/// </summary>
public void FillList()
{
try
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql = "select emp_name from employee where Designation='Servo Sales Representative' order by emp_name";
SqlDtr = obj.GetRecordSet(sql);
DropSSRname.Items.Clear();
DropSSRname.Items.Add("Select");;
while (SqlDtr.Read())
{
DropSSRname.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
sql = "select city from beat_master order by city";
SqlDtr = obj.GetRecordSet(sql);
Listbeat.Items.Clear();
while (SqlDtr.Read())
{
Listbeat.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form : SelesPersionAssignment.aspx,Method : FillList()" + ex.Message + " EXCEPTION " + uid);
}
}
/// <summary>
/// This method is used to fatch the Customer name with type and fill the list.
/// and also fatch the product name with pack type and fill the list.
/// </summary>
public void getCustandProd()
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
//sql="select cust_Name, cust_Name + ':' + cust_type from customer where cust_type='Fleet' or cust_type like('Oe%') order by cust_Name";
sql = "select cust_Name, cust_Name + ':' + cust_type from customer where cust_type like'Oe%' order by cust_Name";
SqlDtr = obj.GetRecordSet(sql);
ListEmpAvailable.Items.Clear();
while (SqlDtr.Read())
{
ListEmpAvailable.Items.Add(SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
//*********************************
sql = "select prod_Name, prod_Name + ':' + pack_type from products order by prod_Name";
SqlDtr = obj.GetRecordSet(sql);
Listprodavail.Items.Clear();
while (SqlDtr.Read())
{
Listprodavail.Items.Add(SqlDtr.GetValue(1).ToString());
}
SqlDtr.Close();
//***********************************
}
public string SaveAttandance(string Att_Date, string lblEmpID)
{
string str = "";
SqlDataReader SqlDtr;
string sql = "select Status from Attandance_Register where Att_Date='" + Att_Date + "' and Emp_ID=" + lblEmpID + "";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
str = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
return(str);
}
/// <summary>
/// This method is used to save the beat information in SSR_Beat table.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, System.EventArgs e)
{
try
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
SqlCommand cmd;
cn.Open();
InventoryClass obj = new InventoryClass();
InventoryClass obj1 = new InventoryClass();
SqlDataReader sdtr = null;
int emp_id = 0;
string sql = "Select emp_id from employee where emp_name='" + DropSSRname.SelectedItem.Value.ToString() + "'";
sdtr = obj.GetRecordSet(sql);
if (sdtr.Read())
{
emp_id = Convert.ToInt32(sdtr.GetValue(0).ToString());
}
sdtr.Close();
sql = "delete from ssr_beat where ssr_id=" + emp_id;
cmd = new SqlCommand(sql, cn);
cmd.ExecuteNonQuery();
for (int i = 0; i < Listassibeat.Items.Count; i++)
{
int beat_id = 0;
string beat_name = Listassibeat.Items[i].Value;
sql = "Select Beat_no from beat_master where city='" + beat_name.ToString().Trim() + "'";
sdtr = obj.GetRecordSet(sql);
if (sdtr.Read())
{
beat_id = Convert.ToInt32(sdtr.GetValue(0).ToString());
}
sdtr.Close();
sql = "Insert into SSR_Beat(SSR_ID,Beat_Id) values(@SSR,@Beat)";
cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("@SSR", emp_id);
cmd.Parameters.Add("@Beat", beat_id);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Record Save Succesfully");
DropSSRname.SelectedIndex = 0;
Listassibeat.Items.Clear();
FillList();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form : SelesPersionAssignment.aspx, Method : btnSubmit_Click EXCEPTION " + ex.Message + " User " + uid);
}
}
public string GetSalaryIncentive(string Inc, string Emp_ID)
{
double ssrinc = Convert.ToDouble(Cache["ssrinc"].ToString());
double totInc = double.Parse(Inc) * ssrinc / 100;
double BasicSalary = 0; double TotalSalInc = 0;
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = obj.GetRecordSet("select salary from employee where emp_id=" + Emp_ID);
if (rdr.Read())
{
if (rdr.GetValue(0).ToString() != "")
{
BasicSalary = double.Parse(rdr.GetValue(0).ToString());
}
else
{
BasicSalary = 0;
}
}
rdr.Close();
TotalSalInc = BasicSalary + totInc;
TotalAmount[8] += TotalSalInc;
return(GenUtil.strNumericFormat(TotalSalInc.ToString()));
/**********End***************/
//TotalAmount[6]+=totInc;
//return GenUtil.strNumericFormat(totInc.ToString());
}
public IHttpActionResult GetStockAdjustmentIds()
{
List <string> dropStockAdjustmentIds = new List <string>();
try
{
SqlDataReader SqlDtr = null;
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = obj.GetRecordSet("select distinct sav_id from stock_adjustment order by sav_id");
if (rdr.HasRows)
{
while (rdr.Read())
{
dropStockAdjustmentIds.Add(rdr.GetValue(0).ToString());
}
}
rdr.Close();
if (dropStockAdjustmentIds == null)
{
return(Content(HttpStatusCode.NotFound, "Could not get data to fill Stock Adjustment combobox"));
}
return(Ok(dropStockAdjustmentIds));
}
catch (Exception ex)
{
return(Content(HttpStatusCode.NotFound, "Could not get data to fill Stock Adjustment combobox"));
}
}
public string DiscAmt(string Prod_id, string Qty)
{
double ltr = double.Parse(Qty.ToString());
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = obj.GetRecordSet("select distinct discount from oilscheme where schname='Secondry(LTR Scheme)' and prodid=" + Prod_id + " and (cast(floor(cast(datefrom as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(dateto as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103) or cast(floor(cast(datefrom as float)) as datetime) between Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103) or cast(floor(cast(dateto as float)) as datetime) between Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103))");
string sub = "";
if (rdr.Read())
{
//if(rdr.GetValue(0).ToString().IndexOf(" ")>0)
if (rdr.GetValue(0).ToString() != null && rdr.GetValue(0).ToString() != "")
{
sub = rdr.GetValue(0).ToString();
ltr = Math.Round(ltr * double.Parse(sub));
sub = ltr.ToString();
TotClaim += double.Parse(sub);
return(sub);
}
else
{
return(rdr.GetValue(0).ToString());
}
}
else
{
return("0");
}
rdr.Close();
}
public void GetSSRInc()
{
/******* Start add by vikas 02.06.09 ******************/
SqlDataReader dtr = null;
string sql = "select * from setDis";
InventoryClass obj = new InventoryClass();
dtr = obj.GetRecordSet(sql);
while (dtr.Read())
{
if (dtr["SSRincentiveStatus"].ToString() != "0")
{
Cache["ssrinc"] = dtr["SSRincentive"].ToString();
}
else
{
Cache["ssrinc"] = 0;
}
}
dtr.Close();
/**********End***************/
}
/// <summary>
/// Method to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\StockReorderingReport.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
sql = "select Prod_ID,Prod_Name+':'+Pack_Type Prod_Name,Prod_Code,minlabel,reorderlable,maxlabel from Products";
sql = sql + " order by " + Cache["strOrderBy"];
rdr = obj.GetRecordSet(sql);
sw.WriteLine("Product Code\tSKU Name With Pack\tStock as On\tMin Level\tReorder Level\tMax Level");
while (rdr.Read())
{
sw.WriteLine(rdr["Prod_Code"].ToString() + "\t" +
rdr["Prod_Name"].ToString().Trim() + "\t" +
GetStock(rdr["Prod_ID"].ToString()) + "\t" +
rdr["minlabel"].ToString() + "\t" +
rdr["reorderlable"].ToString() + "\t" +
rdr["maxlabel"].ToString()
);
}
rdr.Close();
sw.Close();
}
public string GetNextPeriodID()
{
try
{
Next_Period_Id = "1";
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql = " Select max(Period_Id)+1 Next_Id from Cust_Sale_MS_HSD";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
if (SqlDtr["Next_Id"].ToString() != null && SqlDtr["Next_Id"].ToString() != "")
{
Next_Period_Id = SqlDtr["Next_Id"].ToString();
}
else
{
Next_Period_Id = "1";
}
}
SqlDtr.Close();
return(Next_Period_Id);
}
catch (Exception ex)
{
return(Next_Period_Id);
}
}
/// <summary>
/// This is used to retrieve Fo/oeID in dropdown.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btschid_Click(object sender, System.EventArgs e)
{
btnSubmit.Visible = false;
btnsave.Visible = false;
lblschid.Visible = false;
//txtDateFrom.Text="";
//txtDateTo.Text="";
ListEmpAssigned.Items.Clear();
Listprodassign.Items.Clear();
ListEmpAvailable.Enabled = true;
DropShiftID.SelectedIndex = 0;
dropschid.Visible = true;
txtschname.Text = "";
btnSubmit.Enabled = false;
txtrs.Text = "";
btnupdate.Visible = true;
btschid.Visible = false;
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr1;
string sql;
#region Fetch the All Invoice Number and fill in Combo
dropschid.Items.Clear();
dropschid.Items.Add("Select");
//**sql="select distinct foid ,discription from fleetoe_discount order by foid";
sql = "select distinct foid ,discription from foe order by foid";
SqlDtr1 = obj.GetRecordSet(sql);
while (SqlDtr1.Read())
{
//**dropschid.Items.Add(SqlDtr1.GetValue(0).ToString());
dropschid.Items.Add(SqlDtr1.GetValue(0).ToString() + ':' + SqlDtr1.GetValue(1).ToString());
}
SqlDtr1.Close();
#endregion
}
/// <summary>
/// This method return the total secondary sales in liter in given period
/// </summary>
public string GetSecSalesGen(string Month)
{
try
{
InventoryClass obj = new InventoryClass();
double Tot = 0;
string DT = GetDate(Month);
string[] FTDT = DT.Split(new char[] { ':' }, DT.Length);
SqlDataReader rdr = obj.GetRecordSet("select sum(sd.qty*p.Total_Qty) from products p,sales_master sm,sales_details sd where p.Prod_id=sd.prod_id and sm.invoice_no=sd.invoice_no and (p.category = 'Maruti oil' or p.category = 'Swaraj' or p.category = 'Htm') and cast(floor(cast(cast(Invoice_date as datetime) as float)) as datetime)>='" + FTDT[0] + "' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<='" + FTDT[1] + "'");
if (rdr.Read())
{
if (rdr.GetValue(0).ToString() != "")
{
Tot = double.Parse(rdr.GetValue(0).ToString());
}
}
rdr.Close();
//TotalSecSales+=Tot;
return(GenUtil.strNumericFormat(Tot.ToString()));
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:PrimarySecSalesAnalysis.aspx,Method:GetSecSales , EXCEPTION " + ex.Message + ", userid " + uid);
return("0.00");
}
}
/// <summary>
/// This method is used to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\LeaveReport.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
sql = "select lr.Emp_ID,e.Emp_Name,lr.Date_From,lr.Date_To,lr.Reason,lr.isSanction from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(lr.Date_To as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["Textbox1"].ToString()) + "',103)";
sql = sql + " order by " + Cache["strOrderBy"];
rdr = obj.GetRecordSet(sql);
sw.WriteLine("Employee ID\tEmployee Name\tFrom Date\tTo Date\tReason\tApproved");
while (rdr.Read())
{
sw.WriteLine(rdr["Emp_ID"].ToString() + "\t" +
rdr["Emp_Name"].ToString() + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_From"].ToString())) + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_To"].ToString())) + "\t" +
rdr["Reason"].ToString() + "\t" +
Approved(rdr["isSanction"].ToString())
);
}
sw.Close();
}
public IHttpActionResult DeleteStockAdj(string sav_id)
{
try
{
string sql;
SqlDataReader SqlDtr;
sql = "delete from stock_adjustment where sav_id = '" + sav_id + "'";
SqlDtr = obj.GetRecordSet(sql);
SqlDtr.Close();
return(Ok());
}
catch (Exception ex)
{
return(Content(HttpStatusCode.NotFound, "Route could not update."));
}
}
public string GetInvoiceNo1()
{
//int no = 0;
string InNo = "";
try
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
sql = "select max(Invoice_No) from Purchase_Master";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
InNo = SqlDtr.GetValue(0).ToString();
//no = Int32.Parse(InNo);
//no = no + 1;
}
SqlDtr.Close();
//InNo = no.ToString();
return(InNo);
}
catch (Exception)
{
return(InNo);
throw;
}
}
/// <summary>
/// This is used to fill state country according to select city from dropdownlist with the
/// help of java script.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DropCity_SelectedIndexChanged(object sender, System.EventArgs e)
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
// This is used to fetch the state and country according to selected city. This method set the coressponding values of state and country in there respective combos.
sql = "select State,Country from Beat_Master where City='" + DropCity.SelectedItem.Value + "'";
SqlDtr = obj.GetRecordSet(sql);
try
{
//CreateLogFiles.ErrorLog("Form:Employee_Entry.aspx,Method:DropCity_SelectedIndexChanged "+uid);
while (SqlDtr.Read())
{
DropState.SelectedIndex = (DropState.Items.IndexOf((DropState.Items.FindByValue(SqlDtr.GetValue(0).ToString()))));
DropCountry.SelectedIndex = (DropCountry.Items.IndexOf((DropCountry.Items.FindByValue(SqlDtr.GetValue(1).ToString()))));
}
SqlDtr.Close();
//CreateLogFiles.ErrorLog("Form:Mechanic.aspx,Method:DropCity_SelectedIndexChanged "+uid);
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:Mechanic.aspx,Method:DropCity_SelectedIndexChanged ," + " state and country is select for city :" + DropCity.SelectedItem.Value + " EXCEPTION" + ex.Message + " userid " + uid);
}
}
/// <summary>
/// This method is used to fatch the organization id from organization table and fill the
/// dropdownlist on edit time.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, System.EventArgs e)
{
CreateLogFiles Err = new CreateLogFiles();
try
{
Label1.Visible = true;
//txtFileTitle.Enabled=false;
txtFileContents.EnableViewState = false;
Button1.Visible = false;
Drop.Visible = true;
LblCompanyID.Visible = false;
txtFileContents.Visible = false;
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
sql = "select max(CompanyID) from Organisation";
SqlDtr = obj.GetRecordSet(sql);
while (SqlDtr.Read())
{
Drop.Items.Add(SqlDtr.GetValue(0).ToString());
}
SqlDtr.Close();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:OrganisationDetails.aspx,Class:InventoryClass.cs ,Method:saveButton EXCEPTION: " + ex.Message + " User_ID: " + uid);
}
}
/// <summary>
/// This method is used to save or update the customer according to ssr in customer table.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, System.EventArgs e)
{
try
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr = null;
int x = 0;
string EmpID = "";
string sql = "select Emp_ID from Employee where Emp_Name='" + DropSSRName.SelectedItem.Text + "'";
SqlDtr = obj.GetRecordSet(sql);
if (SqlDtr.Read())
{
EmpID = SqlDtr["Emp_ID"].ToString();
}
SqlDtr.Close();
for (int i = 0; i < ListassignCustomer.Items.Count; ++i)
{
ListassignCustomer.SelectedIndex = i;
string pname = ListassignCustomer.SelectedItem.Value;
dbobj.Insert_or_Update("update Customer set ssr='" + EmpID + "' where Cust_Name='" + pname + "'", ref x);
}
MessageBox.Show("Customer Mapping Update");
FillList();
DropSSRName.SelectedIndex = 0;
ListassignCustomer.Items.Clear();
CreateLogFiles.ErrorLog("Form:CustomerMapping.aspx,Method:btnSubmit_Click, Update Customer Mapping, User " + uid);
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:CustomerMapping.aspx,Method:btnSubmit_Click EXCEPTION " + ex.Message + " User " + uid);
}
}
// This method displays the Dealers Logo.
public void show()
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
string filePath = "";
sql = "select Logo from Organisation where CompanyID='1001'";
SqlDtr = obj.GetRecordSet(sql);
if (SqlDtr.Read())
{
filePath = SqlDtr["Logo"].ToString();
}
else
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
filePath = home_drive + @"\Inetpub\wwwroot\Servosms\CompanyLogo\Logo.jpg";
}
if (filePath.Trim().Equals(""))
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
filePath = home_drive + @"\Inetpub\wwwroot\Servosms\CompanyLogo\Logo.jpg";
}
imgSample.ImageUrl = filePath;
SqlDtr.Close();
}
public CustomerModels GetCustmerName(string CustomerID)
{
InventoryClass obj = new InventoryClass();
CustomerModels custmer = new CustomerModels();
string sql = "";
sql = "select Ledger_Name,city,Ledger_ID from Ledger_Master l,Employee e where l.ledger_name=e.Emp_name and Ledger_ID='" + CustomerID + "'";
var SqlDtr = obj.GetRecordSet(sql);
if (SqlDtr.Read())
{
custmer.CustomerName = SqlDtr.GetValue(0).ToString() + ":" + SqlDtr.GetValue(2).ToString();
if (SqlDtr.GetValue(1).ToString().Equals(""))
{
custmer.City = "";
}
else
{
custmer.City = SqlDtr.GetValue(1).ToString();
}
custmer.Flag = 1;
}
SqlDtr.Close();
return(custmer);
}
/// <summary>
/// This method is used to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\MechanicReport.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
sql = "select distinct b.state r1,me.mccd r2,me.mcname r3,me.mctype r4,me.place r5,cme.customername r6,c.cust_type r7 from beat_master b,machanic_entry me, customermechanicentry cme,customer c where b.state in(select state from beat_master where city =me.place) and cme.customername in(select customername from customermechanicentry where customermechid=me.custid) and c.cust_type in(select cust_type from customer where cust_name=cme.customername)";
sql = sql + " order by " + Cache["strorderby"];
rdr = obj.GetRecordSet(sql);
sw.WriteLine("District\tMechanic Code\tName\tType\tUnder Firm\tFirm Type\tPlace");
while (rdr.Read())
{
sw.WriteLine(rdr["r1"].ToString() + "\t" +
rdr["r2"].ToString() + "\t" +
rdr["r3"].ToString() + "\t" +
rdr["r4"].ToString() + "\t" +
rdr["r6"].ToString() + "\t" +
rdr["r7"].ToString() + "\t" +
rdr["r5"].ToString());
}
rdr.Close();
sw.Close();
}
/// <summary>
/// this is used to fill the id in the dropdown for update the scheme.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btschid_Click(object sender, System.EventArgs e)
{
btnSubmit.Visible = false;
lblschid.Visible = false;
ListEmpAssigned.Items.Clear();
ListEmpAvailable.Enabled = true;
//DropSchType.SelectedIndex=0;
dropschid.Visible = true;
btschid.Visible = false;
btnSubmit.Enabled = false;
btnupdate.Visible = true;
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr1;
string sql;
#region Fetch the All Invoice Number and fill in Combo
dropschid.Items.Clear();
dropschid.Items.Add("Select");
sql = "select distinct sch_id ,schname from StktSchDiscount order by sch_id";
SqlDtr1 = obj.GetRecordSet(sql);
while (SqlDtr1.Read())
{
dropschid.Items.Add(SqlDtr1.GetValue(0).ToString() + ':' + SqlDtr1.GetValue(1).ToString());
}
SqlDtr1.Close();
#endregion
}
/// <summary>
/// This method is used to show the data on form.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnView_Click(object sender, System.EventArgs e)
{
txtDiscription.Text = "";
if (DropYearFrom.SelectedIndex == DropYearTo.SelectedIndex)
{
MessageBox.Show("Year Can Not Be Same");
View = 0;
return;
}
else if (DropYearFrom.SelectedIndex + 1 == DropYearTo.SelectedIndex)
{
View = 1;
}
else
{
MessageBox.Show("Invalid Year Selection");
View = 0;
return;
}
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = obj.GetRecordSet("select discription from ly_ps_sale where ly_ps_sales='" + DropYearFrom.SelectedItem.Text + DropYearTo.SelectedItem.Text + "'");
if (rdr.Read())
{
txtDiscription.Text = rdr["Discription"].ToString();
}
else
{
txtDiscription.Text = "";
}
}
public IHttpActionResult GetNextRouteID()
{
string Route_ID = string.Empty;
try
{
InventoryClass obj = new InventoryClass();
SqlDataReader SqlDtr;
string sql;
#region Fetch the Next Route ID
sql = "select Max(Route_ID)+1 from Route";
SqlDtr = obj.GetRecordSet(sql);
if (SqlDtr.Read())
{
Route_ID = SqlDtr.GetValue(0).ToString();
if (Route_ID == "")
{
Route_ID = "1";
}
}
else
{
Route_ID = "1";
}
SqlDtr.Close();
#endregion
return(Ok(Route_ID));
}
catch (Exception ex)
{
return(Content(HttpStatusCode.NotFound, "Failed to get Next Route ID"));
}
}
/// <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.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
uid = (Session["User_Name"].ToString());
if (!Page.IsPostBack)
{
Stock_Ledger.Visible = false;
getProducts();
txtDateFrom.Text = GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString());
txtDateTo.Text = GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString());
#region Check Privileges
int i;
string View_flag = "0", Add_Flag = "0", Edit_Flag = "0", Del_Flag = "0";
string Module = "5";
string SubModule = "5";
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")
{
Response.Redirect("../../Sysitem/AccessDeny.aspx", false);
return;
}
#endregion
#region Get FromDate and ToDate From Organisation
InventoryClass obj1 = new InventoryClass();
SqlDataReader rdr1 = null;
rdr1 = obj1.GetRecordSet("select * from organisation");
if (rdr1.Read())
{
FromDate = GetYear(GenUtil.trimDate(rdr1["Acc_date_from"].ToString()));
ToDate = GetYear(GenUtil.trimDate(rdr1["Acc_date_To"].ToString()));
}
else
{
MessageBox.Show("Please Fill The Organisation Details First");
return;
}
#endregion
}
txtDateFrom.Text = Request.Form["txtDateFrom"] == null?GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString()) : Request.Form["txtDateFrom"].ToString().Trim();
txtDateTo.Text = Request.Form["txtDateTo"] == null?GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString()) : Request.Form["txtDateTo"].ToString().Trim();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:BatchWiseStockLedger.aspx,Method:Page_Load" + " EXCEPTION " + ex.Message + " userid " + uid);
}
}
/// <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.
/// </summary>
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
uid = (Session["User_Name"].ToString());
if (!Page.IsPostBack)
{
GrdDayBook.Visible = false;
txtDateFrom.Text = DateTime.Now.Day + "/" + DateTime.Now.Month + "/" + DateTime.Now.Year;
txtDateTo.Text = DateTime.Now.Day + "/" + DateTime.Now.Month + "/" + DateTime.Now.Year;
}
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:DayBookReport.aspx,Method:Pageload " + " EXCEPTION " + ex.Message + " " + uid);
Response.Redirect("../../Sysitem/ErrorPage.aspx", false);
return;
}
if (!Page.IsPostBack)
{
#region Get FromDate and ToDate From Organisation
InventoryClass obj1 = new InventoryClass();
SqlDataReader rdr1 = null;
rdr1 = obj1.GetRecordSet("select * from organisation");
if (rdr1.Read())
{
FromDate = GetYear(GenUtil.trimDate(rdr1["Acc_date_from"].ToString()));
ToDate = GetYear(GenUtil.trimDate(rdr1["Acc_date_To"].ToString()));
}
#endregion
// To checks the user privileges from session.
#region Check Privileges
int i;
string View_flag = "0", Add_Flag = "0", Edit_Flag = "0", Del_Flag = "0";
string Module = "5";
string SubModule = "11";
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")
{
Response.Redirect("../../Sysitem/AccessDeny.aspx", false);
return;
}
#endregion
}
txtDateFrom.Text = Request.Form["txtDateFrom"] == null?GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString()) : Request.Form["txtDateFrom"].ToString().Trim();
txtDateTo.Text = Request.Form["txtDateTo"] == null?GenUtil.str2DDMMYYYY(System.DateTime.Now.ToShortDateString()) : Request.Form["txtDateTo"].ToString().Trim();
}
/// <summary>
/// This method is used to fill the searchable combo box when according to select value
/// from dropdownlist with the help of java script.
/// </summary>
public void GetMultiValue()
{
try
{
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = null;
string strName = "", strType = "", strDistrict = "", strPlace = "", strSSR = "";
string strGroup = "", strSubGroup = ""; //Add by vikas 16.11.2012
//coment by vikas 25.05.09 strName = "select distinct c.cust_name from vw_cust_ageing a,customer c where c.cust_id=a.cust_id order by c.cust_name";
strName = "select distinct c.cust_name,c.city from vw_cust_ageing a,customer c where c.cust_id=a.cust_id order by c.cust_name,c.city";
//strType = "select distinct case when cust_type like 'oe%' then 'Oe' else cust_type end as cust_type from customer order by cust_type";
//coment by vikas 17.11.2012 strType = "select distinct cust_type from customer union select distinct case when cust_type like 'oe%' then 'OE' when cust_type like 'ro%' then 'RO' when cust_type like 'ksk%' then 'KSK' when cust_type like 'N-ksk%' then 'N-KSK' when cust_type like 'Nksk%' then 'NKSK' else 'RO' end as cust_type from customer";
strDistrict = "select distinct state from vw_cust_ageing a,customer c where c.cust_id=a.cust_id order by state";
strPlace = "select distinct c.city from vw_cust_ageing a,customer c where c.cust_id=a.cust_id order by c.city";
strSSR = "select emp_name from employee where emp_id in(select ssr from vw_cust_ageing a,customer c where c.cust_id=a.cust_id) and status=1 order by emp_name";
strGroup = "select distinct Group_Name from customertype"; //Add by vikas 16.11.2012
strSubGroup = "select distinct Sub_Group_Name from customertype"; //Add by vikas 16.11.2012
//comentby vikas 17.11.2012 string[] arrStr = {strName,strType,strDistrict,strPlace,strSSR};
//comentby vikas 17.11.2012 HtmlInputHidden[] arrCust = {tempCustName,tempCustType,tempDistrict,tempPlace,tempSSR};
string[] arrStr = { strName, strDistrict, strPlace, strSSR, strGroup, strSubGroup };
HtmlInputHidden[] arrCust = { tempCustName, tempDistrict, tempPlace, tempSSR, tempGroup, tempSubGroup };
for (int i = 0; i < arrStr.Length; i++)
{
rdr = obj.GetRecordSet(arrStr[i].ToString());
if (rdr.HasRows)
{
arrCust[i].Value = "All,";
while (rdr.Read())
{
//Coment by vikas 25.05.09 arrCust[i].Value+=rdr.GetValue(0).ToString()+",";
if (rdr.GetValue(0).ToString() != null && rdr.GetValue(0).ToString() != "")
{
if (i == 0)
{
arrCust[i].Value += rdr.GetValue(0).ToString() + ":" + rdr.GetValue(1).ToString() + ",";
}
else
{
arrCust[i].Value += rdr.GetValue(0).ToString() + ",";
}
}
}
}
rdr.Close();
}
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:Salesreport1.aspx,Class:PetrolPumpClass.cs,Method:getMultiValue() Customer Sales Report Viewed " + ex.Message + " EXCEPTION " + " userid " + UID);
}
}
/// <summary>
/// This method is used to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\ProductReport.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
//Coment by vikas 23.10.09 sql="select Category,Prod_Name,Pack_Type,Prod_Code,Unit,Total_Qty,Store_In from Products";
sql = "select Category,Prod_Name,Pack_Type,Prod_Code,Unit,Total_Qty,Store_In,prod_id from Products";
/*******Add by vikas 18.07.09***********************/
if (DropValue.Value != "All")
{
if (DropSearchBy.SelectedIndex == 1)
{
string[] pname = DropValue.Value.Split(new char[] { ':' }, DropValue.Value.Length);
sql += " where prod_name='" + pname[0].ToString() + "' and pack_type='" + pname[1].ToString() + "'";
}
else if (DropSearchBy.SelectedIndex == 2)
{
sql += " where Prod_Name='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 3)
{
sql += " where Pack_Type='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 4)
{
sql += " where Category='" + DropValue.Value + "'";
}
}
/********End**********************/
sql = sql + " order by " + Cache["strOrderBy"];
rdr = obj.GetRecordSet(sql);
sw.WriteLine("Product ID\tProduct Code\tCategory\tProduct Name\tPack Type\tunit\tUnit Qty\tStore In");
while (rdr.Read())
{
sw.WriteLine(rdr["Prod_id"].ToString() + "\t" +
rdr["Prod_Code"].ToString() + "\t" +
rdr["Category"].ToString() + "\t" +
rdr["Prod_Name"].ToString().Trim() + "\t" +
rdr["Pack_Type"].ToString() + "\t" +
rdr["Unit"].ToString() + "\t" +
rdr["Total_Qty"].ToString() + "\t" +
rdr["Store_In"].ToString()
);
}
rdr.Close();
sw.Close();
}
